if not exists (select * from tbl_AccountLedger where RTRIM(Ltrim(ledgerName)) = 'Purchase CC Charge')
begin
    INSERT [dbo].[tbl_AccountLedger] ([ledgerId], [accountGroupId], [ledgerName], [openingBalance], [isDefault], [crOrDr], [narration], [mailingName], [address], [phone], [mobile], [email], [creditPeriod], [creditLimit], [pricinglevelId], [billByBill], [registrationNo], [licenseNo], [pan], [routeId], [bankAccountNumber], [branchName], [branchCode], [extraDate], [extra1], [extra2], [areaId], [isfree], [isimport], [isfixedpurchased], [iseffectonvat], [isvat], [AccRefId], [userid], [ledgertype]) VALUES (CAST(10020 AS Numeric(18, 0)), CAST(15 AS Numeric(18, 0)), N'Purchase CC Charge', CAST(0.00000 AS Decimal(18, 5)), 1, N'Cr', NULL, NULL, N'banasthali', NULL, NULL, NULL, 0, NULL, CAST(1 AS Numeric(18, 0)), 0, NULL, NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, CAST(N'2013-06-20T00:00:00.000' AS DateTime), NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL)
    INSERT [dbo].[tbl_AccountLedger] ([ledgerId], [accountGroupId], [ledgerName], [openingBalance], [isDefault], [crOrDr], [narration], [mailingName], [address], [phone], [mobile], [email], [creditPeriod], [creditLimit], [pricinglevelId], [billByBill], [registrationNo], [licenseNo], [pan], [routeId], [bankAccountNumber], [branchName], [branchCode], [extraDate], [extra1], [extra2], [areaId], [isfree], [isimport], [isfixedpurchased], [iseffectonvat], [isvat], [AccRefId], [userid], [ledgertype]) VALUES (CAST(10021 AS Numeric(18, 0)), CAST(15 AS Numeric(18, 0)), N'Purchase Adjustment', CAST(0.00000 AS Decimal(18, 5)), 1, N'Cr', NULL, NULL, N'banasthali', NULL, NULL, NULL, 0, NULL, CAST(1 AS Numeric(18, 0)), 0, NULL, NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, CAST(N'2013-06-20T00:00:00.000' AS DateTime), NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL)
end
Go
if not exists (select * from tbl_AccountLedger where RTRIM(Ltrim(ledgerName)) = 'Purchase CC Charge')
begin
    INSERT [dbo].[tbl_AccountLedger] ([ledgerId], [accountGroupId], [ledgerName], [openingBalance], [isDefault], [crOrDr], [narration], [mailingName], [address], [phone], [mobile], [email], [creditPeriod], [creditLimit], [pricinglevelId], [billByBill], [registrationNo], [licenseNo], [pan], [routeId], [bankAccountNumber], [branchName], [branchCode], [extraDate], [extra1], [extra2], [areaId], [isfree], [isimport], [isfixedpurchased], [iseffectonvat], [isvat], [AccRefId], [userid], [ledgertype]) VALUES (CAST(10020 AS Numeric(18, 0)), CAST(15 AS Numeric(18, 0)), N'Purchase CC Charge', CAST(0.00000 AS Decimal(18, 5)), 1, N'Cr', NULL, NULL, N'banasthali', NULL, NULL, NULL, 0, NULL, CAST(1 AS Numeric(18, 0)), 0, NULL, NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, CAST(N'2013-06-20T00:00:00.000' AS DateTime), NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL)
    INSERT [dbo].[tbl_AccountLedger] ([ledgerId], [accountGroupId], [ledgerName], [openingBalance], [isDefault], [crOrDr], [narration], [mailingName], [address], [phone], [mobile], [email], [creditPeriod], [creditLimit], [pricinglevelId], [billByBill], [registrationNo], [licenseNo], [pan], [routeId], [bankAccountNumber], [branchName], [branchCode], [extraDate], [extra1], [extra2], [areaId], [isfree], [isimport], [isfixedpurchased], [iseffectonvat], [isvat], [AccRefId], [userid], [ledgertype]) VALUES (CAST(10021 AS Numeric(18, 0)), CAST(15 AS Numeric(18, 0)), N'Purchase Adjustment', CAST(0.00000 AS Decimal(18, 5)), 1, N'Cr', NULL, NULL, N'banasthali', NULL, NULL, NULL, 0, NULL, CAST(1 AS Numeric(18, 0)), 0, NULL, NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, CAST(N'2013-06-20T00:00:00.000' AS DateTime), NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL)
end
go
CREATE proc [dbo].[TrialBalance_Groupwise]
 @fromdate datetime,
 @todate datetime,
 @branchid int,
 @financialYearid int,
 @reportType nvarchar(max)
as
DECLARE @noOfDecimalPlace INT = 5;

if @reportType = 'SubGroupwise'
Begin
 declare @accountGroupId int, @accountGroupName nvarchar(max), @groupUnder int;
 Declare @temp table(accountGroupId int, accountGroupName nvarchar(max), groupUnder int)

 --insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where accountGroupId =  0
 Declare datainsert cursor for
  select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup
  open datainsert
  Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder
  WHILE @@FETCH_STATUS=0
  Begin
  set @groupUnder = (select groupUnder from tbl_accountGroup where accountGroupId =  @accountGroupId)
  if @groupUnder = 0
  begin
   insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where accountGroupId =  @accountGroupId
   insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where groupUnder =  @accountGroupId
   insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where groupUnder in  (select accountGroupId from tbl_accountGroup where groupUnder =  @accountGroupId)
  end
  if @accountGroupId = 0
   begin
   insert into @temp select accountGroupId, accountGroupName, 0 from tbl_accountGroup where accountGroupId =  @accountGroupId
   --insert into @temp select accountGroupId, accountGroupName, 0 from tbl_accountGroup where groupUnder =  @accountGroupId
  end
  Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder
  End

 CLOSE datainsert
 DEALLOCATE datainsert


 ;With
 PlGroups(accountGroupId) as(
   select accountGroupId from tbl_accountGroup where accountGroupId in (10,11,12,13,14,15)
   union all
   select accountGroupId from tbl_accountGroup where groupUnder in (10,11,12,13,14,15)
    ),
    trial1 as
  (
   select AG.accountGroupID, sum(TL.Debit)-Sum(TL.Credit) as opening
  from   tbl_ledgerposting TL
  inner join tbl_accountledger  AL ON AL.ledgerid=TL.LedgerID
  inner join tbl_accountGroup AG ON AG.AccountGroupID=AL.AccountGroupID
  where convert(date, TL.date) < convert(date, @fromdate )    and
   ag.accountGroupId not in (select accountGroupId from PlGroups)
  GROUP BY AG.accountGroupID,AG.AccountGroupName
  ),
  trial2 as (
  select AG.accountGroupID, Ag.AccountGroupName, sum(TL.Debit) as Debit, Sum(TL.Credit) as credit
  from tbl_ledgerposting TL
  inner join tbl_accountledger  AL ON AL.ledgerid=TL.LedgerID
  inner join tbl_accountGroup AG ON AG.AccountGroupID=AL.AccountGroupID
  where convert(date, TL.date) between convert(date, @fromdate) and convert(date, @toDate)
  and yearId = @financialyearid
  GROUP BY AG.accountGroupID,AG.AccountGroupName
    )
  select
  ag.accountGroupID, ag.accountGroupName, ag.groupUnder,
  isnull(t1.opening,0)  as OpeningBalance,
  concat (
   (convert(decimal(24, 3), abs(isnull(t1.opening,0) ))),
   case when isnull(t1.opening,0) > 0 then ' Dr'  else
    case when isnull(t1.opening,0) < 0 then ' Cr'  else '' end
   end
   )
  as OpeningBalanceStr,
  convert(decimal(24, 3),  isnull( t2.debit, 0) ) as Debit,
  convert(decimal(24, 3),   isnull( t2.credit, 0) ) as Credit,
  convert(decimal(24, 3),  isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0) ) as Balance,
  concat (
   (convert(decimal(24, 3), abs(isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0)))),
   case when isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0)> 0 then ' Dr'  else
    case when isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0) < 0 then ' Cr'  else '' end
   end
   ) as BalanceStr
  from @temp ag
 left join trial1 t1 on t1.accountGroupId = ag.accountGroupId
 left join trial2 t2 on t2.accountGroupId = ag.accountGroupId
  WHERE opening<>0 or Debit<>0 or Credit<>0
End

else if @reportType = 'Groupwise'
Begin
 ;With
  PlGroups(accountGroupId) as(
   select accountGroupId from tbl_accountGroup where accountGroupId in (10,11,12,13,14,15)
   union all
   select accountGroupId from tbl_accountGroup where groupUnder in (10,11,12,13,14,15)
    ),
    mainGroups(accountGroupId) as (
   select accountGroupId from tbl_accountGroup where groupUnder in (0)
    ),
    trial1 as
  (
   select AG.accountGroupID, sum(TL.Debit)-Sum(TL.Credit) as opening
  from   tbl_ledgerposting TL
  inner join tbl_accountledger  AL ON AL.ledgerid=TL.LedgerID
  inner join tbl_accountGroup AG ON AG.AccountGroupID=AL.AccountGroupID
  where
  convert(date, TL.date) < convert(date, @fromdate ) and
  ag.accountGroupId not in (select accountGroupId from PlGroups)
  GROUP BY AG.accountGroupID,AG.AccountGroupName
  ),
  trial2 as (
  SELECT ag.accountGroupId, ag.accountGroupName,
 ISNULL(
  (
  SELECT SUM(ISNULL(lp.debit, 0))
  FROM tbl_AccountLedger   al
  LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId
  WHERE  convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)
   and yearId = @financialyearid  and
  (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))
 , 0)AS Debit,
 ISNULL(
  (
  SELECT SUM(ISNULL(lp.credit, 0))
  FROM tbl_AccountLedger   al
  LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId
  WHERE   convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)
   and yearId = @financialyearid  and
  (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))
 , 0)AS credit
 FROM tbl_AccountGroup  ag
 LEFT OUTER JOIN tbl_AccountLedger al1 ON ag.accountGroupId = al1.accountGroupId
 AND al1.accountGroupId IN (
  SELECT accountGroupId
  FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId))
  WHERE (ag.accountGroupId IN (
  SELECT accountGroupId FROM tbl_AccountGroup ag1
   WHERE (nature = 'Assets') AND (groupUnder = 0))
 )
 group by ag.accountGroupId, ag.accountGroupName
 union all

 SELECT ag.accountGroupId, ag.accountGroupName,
 ISNULL(
  (
  SELECT SUM(ISNULL(lp.debit, 0))
  FROM tbl_AccountLedger   al
  LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId
  WHERE  convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)
   and yearId = @financialyearid  and
  (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))
 , 0)AS Debit,
 ISNULL(
  (
  SELECT SUM(ISNULL(lp.credit, 0))
  FROM tbl_AccountLedger   al
  LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId
  WHERE   convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)
   and yearId = @financialyearid  and
  (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))
 , 0)AS credit
 FROM tbl_AccountGroup  ag
 LEFT OUTER JOIN tbl_AccountLedger al1 ON ag.accountGroupId = al1.accountGroupId
 AND al1.accountGroupId IN (
 SELECT accountGroupId
 FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId))
 WHERE (ag.accountGroupId IN (
 SELECT accountGroupId FROM tbl_AccountGroup ag1
  WHERE (nature = 'Liabilities') AND (groupUnder = 0))
 )
 group by ag.accountGroupId, ag.accountGroupName
    )

 select DISTINCT convert(int,ROW_NUMBER() OVER (ORDER BY ag.accountGroupId)) AS [Sl No],
  ag.accountGroupID, ag.accountGroupName,
  isnull(t1.opening,0)  as OpeningBalance,
  concat (
   (convert(decimal(24, 3), abs(isnull(t1.opening,0) ))),
   case when isnull(t1.opening,0) > 0 then ' Dr'  else
    case when isnull(t1.opening,0) < 0 then ' Cr'  else '' end
   end
   )
  as OpeningBalanceStr,
  convert(decimal(24, 3),  isnull( t2.debit, 0) ) as Debit,
  convert(decimal(24, 3),   isnull( t2.credit, 0) ) as Credit,
  convert(decimal(24, 3),  isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0) ) as Balance,
  concat (
   (convert(decimal(24, 3), abs(isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0)))),
   case when isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0)> 0 then ' Dr'  else
    case when isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0) < 0 then ' Cr'  else '' end
   end
   ) as BalanceStr
  from tbl_accountGroup ag
  --inner join mainGroups m on m.accountGroupId = ag.accountGroupId
  left join trial1 t1 on t1.accountGroupId = ag.accountGroupId
  left join trial2 t2 on t2.accountGroupId = ag.accountGroupId
  WHERE opening<>0 or Debit<>0 or Credit<>0

End
go
CREATE proc [dbo].[TrialBalance_LedgerWise]
@fromDate date, @toDate date, @branchid int, @financialYearid int
as
declare @accountGroupId int, @accountGroupName nvarchar(max), @groupUnder int;
Declare @temp table(ledgerId int, ledgerName nvarchar(max))
Declare datainsert cursor for
 select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup
 open datainsert
 Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder
 WHILE @@FETCH_STATUS=0
 Begin
 set @groupUnder = (select groupUnder from tbl_accountGroup where accountGroupId =  @accountGroupId)
 if @groupUnder = 0
 begin
  insert into @temp select 0, accountGroupName from tbl_accountGroup where accountGroupId =  @accountGroupId
  insert into @temp select ledgerId, ledgerName from tbl_AccountLedger where accountGroupId =  @accountGroupId order by ledgerId
 end
 else
 Begin
  insert into @temp select 0, accountGroupName from tbl_accountGroup where accountGroupId =  @accountGroupId
  insert into @temp select ledgerId, ledgerName from tbl_AccountLedger where accountGroupId =  @accountGroupId order by ledgerId
 End

 Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder
 End

CLOSE datainsert
DEALLOCATE datainsert


create table #PlGroups (accountGroupId int)
insert into #PlGroups
 select accountGroupId from tbl_accountGroup where accountGroupId in (10,11,12,13,14,15)
 union all
 select accountGroupId from tbl_accountGroup where groupUnder in (10,11,12,13,14,15)

Create table #AccountGroups (accountGroupId int)
insert into #AccountGroups
 select accountGroupId from tbl_AccountGroup
create table #ledgerData (ledgerId int, accountGroupId int, ledgerName nvarchar(max), Opening nvarchar(max), op decimal(18,5),
      Debit decimal(18,5), Credit decimal(18,5), Closing nvarchar(max), Closing1 decimal(18,5), Total_Closing nvarchar(max))
insert into #ledgerData
 SELECT
 ledgerId,  accountGroupId, ledgerName,
 CASE WHEN CAST(ROUND(isnull(SUM(Opening) ,0),2)AS DECIMAL(24,2))<0 THEN
   CAST(CAST(ROUND(isnull(SUM(-Opening) ,0),2e)AS DECIMAL(24,2)) AS VARCHAR(50))+'Cr'
 ELSE
  CAST(CAST(ROUND(isnull(SUM(Opening) ,0),2)AS DECIMAL(24,2)) AS VARCHAR(50))+'Dr'
 END AS Opening,
 CAST(ROUND(isnull(SUM(Opening) ,0),2)AS DECIMAL(24,2)) AS op,
 CAST( ROUND(isnull(SUM(Debit) ,0),2)AS DECIMAL(24,2)) AS Debit,
 CAST( ROUND(isnull(SUM(Credit) ,0),2)AS DECIMAL(24,2)) AS Credit,
 CASE WHEN  (Opening+debit-credit)>0  then CONVERT(VARCHAR(MAX),convert(decimal(18,2),Round( (Opening+debit-credit), 2)))+'Dr' else
 CONVERT(VARCHAR(MAX),-1*convert(decimal(18,2),Round( (Opening+debit-credit), 2) ) )+'Cr' end AS Closing,
 convert(decimal(18, 2), (Round((Opening+debit-credit), 2))) AS Closing1,
 CASE WHEN  (Opening+debit-credit)>0  then CONVERT(VARCHAR(MAX),convert(decimal(18,2),Round( (Opening+debit-credit), 2) ))+'Dr' else
 CONVERT(VARCHAR(MAX),-1*convert(decimal(18,2),Round((Opening+debit-credit), 2)) )+'Cr' end AS Total_Closing
 --CASE WHEN  (totalopening)>0 then CONVERT(VARCHAR(MAX),convert(decimal(18,2),(totalopening)))+'Dr'
 --else CONVERT(VARCHAR(MAX),-1*convert(decimal(18,2),(totalopening)) )+'Cr' end AS Total_Closing
 FROM
 (
    SELECT B.ledgerId, B.ledgerName, B.accountGroupId,
   (SELECT
 ISNULL(SUM(debit),0) -ISNULL(SUM(credit), 0) AS OP
 FROM tbl_LedgerPosting AS LP
 inner join tbl_AccountLedger al on al.ledgerId = lp .ledgerId and al.accountGroupId not in (select accountGroupId from #PlGroups)
 WHERE (LP.ledgerId=B.ledgerId
 AND (LP.date<@fromDate)
 ))
   AS Opening,
   (SELECT ISNULL(SUM(debit),0) -ISNULL(SUM(credit), 0) AS OP
 FROM tbl_LedgerPosting AS LP
    WHERE
    LP.ledgerId=B.ledgerId and yearId = @financialyearid
 and lp.voucherTypeId !=1
 and LP.date BETWEEN @fromDate AND @toDate
 )
   AS totalopening,
 (select
     ISNULL(SUM(debit),0)AS OP
 FROM tbl_LedgerPosting AS LP
 WHERE
    LP.ledgerId=B.ledgerId and yearId = @financialyearid
 and lp.voucherTypeId !=1
 and LP.date BETWEEN @fromDate AND @toDate
 ) AS Debit,
 (
 SELECT
     ISNULL(SUM(credit),0) AS OP
 FROM tbl_LedgerPosting AS LP
 WHERE
    Lp.ledgerId=B.ledgerId and yearId = @financialyearid   and
 lp.voucherTypeId !=1
 and LP.date BETWEEN @fromDate AND @toDate
 )AS Credit

 FROM  tbl_AccountLedger AS B
 WHERE  accountGroupId in( SELECT accountGroupId  From #AccountGroups )
 )AS TEMP
 WHERE(TEMP.Debit<>0 OR TEMP.Credit<>0 OR opening<>0)
 GROUP BY ledgerId,ledgerName,opening,credit,debit,totalopening,accountGroupId

declare @ledgerId int, @ledgerName nvarchar(max), @OpeningBalance decimal(18, 5),
  @OpeningBalanceStr nvarchar(max), @Debit decimal(18, 5), @Credit decimal(18, 5),
  @Balance decimal(18, 5), @BalanceStr nvarchar(max);
Declare @tempData table(
  ledgerId int, ledgerName nvarchar(max), accountGroupId int,
  OpeningBalance decimal(18, 5),
  OpeningBalanceStr nvarchar(max), Debit decimal(18, 5), Credit decimal(18, 5),
  Balance decimal(18, 5), BalanceStr nvarchar(max))

Declare datainsert cursor for
  select t.ledgerId, t.ledgerName, d.accountGroupId,
  isnull(d.op,0) OpeningBalance, isnull(d.Opening,0) OpeningBalanceStr,
  isnull(d.Debit,0) Debit, isnull(d.Credit,0) Credit,
  isnull(d.Closing1,0) Balance, isnull(d.Total_Closing,0) BalanceStr
  from @temp t
  left join #ledgerData d on d.ledgerId = t.ledgerId
 open datainsert
 Fetch next from datainsert into @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr
 WHILE @@FETCH_STATUS=0
 Begin
 if @ledgerId = 0
 begin
  insert into @tempData select @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr
 end
 else
 Begin
  if @Debit <> 0 or @Credit <> 0  or @OpeningBalance <>0
  insert into @tempData select @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr
 End

 Fetch next from datainsert into @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr
 End

CLOSE datainsert
DEALLOCATE datainsert

 select ledgerId, ledgerName, accountGroupId,
  OpeningBalance,
  OpeningBalanceStr, Debit, Credit,
  Balance, BalanceStr from @tempData

Drop table #PlGroups;
Drop table #AccountGroups;
Drop table #ledgerData;
Go

GO
truncate table [tblDashboardSetting]
go
SET IDENTITY_INSERT [dbo].[tblDashboardSetting] ON
GO
INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (1, N'Patient Record')
GO
INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (2, N'Departmentwise Admitted Record')
GO
INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (3, N'Doctorwise Admitted Record')
GO
INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (4, N'Vistor Record In Percentage')
GO
INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (5, N'Weekly Total Revenue')
GO
INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (6, N'Book Appointment')
GO
INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (7, N'Departmentwise Discharged Record')
GO
INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (8, N'Total Bed')
GO
INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (9, N'Inpatient Morbidity')
GO
SET IDENTITY_INSERT [dbo].[tblDashboardSetting] OFF
GO

Go
Create proc AccountFixedAssetsPurchasegetByBillNo
@fromBillNo nvarchar(max),
@toBillNo nvarchar(max)
as
declare @frombillId int = (isnull((select AssetMasterID from tblFixedAssetsPurchasemaster Where ReferenceNo = @fromBillNo),0));
declare @toBillID int = (isnull((select AssetMasterID from tblFixedAssetsPurchasemaster Where ReferenceNo = @toBillNo),0));
if @frombillId<>0 and @toBillID<>0
BEGIN
    select 'Purchase A/c' as heading,11 as ledgerid,sum(BillAmt) as Amount,'Dr'
     DrCR from tblFixedAssetsPurchasemaster
     where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
    Union all
    select 'VAT A/c' as heading,10022 as ledgerid,sum(taxAmt) as Amount,'Dr' DrCR from tblFixedAssetsPurchasemaster
    where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
    Union all
    select 'Discount Allow A/c' as heading,8 as ledgerid,sum(DIscAmt) as Amount,
    'Cr' DrCR from tblFixedAssetsPurchasemaster
    where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
    --Union all
    --Purchase CC Charge --group under 15
    --select 'Purchase CC Charge' as heading,10020  as ledgerid, ABS(sum(ccamt)) as Amount,'Dr' DrCR
    --from tblFixedAssetsPurchasemaster
    --where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
    union all
    --Purchase Adjustment --group under 15
    select 'Purchase Adjustment' as heading,10021  as ledgerid, abs(sum(AdjustmentAmt)) as Amount,
     case when sum(AdjustmentAmt) > 0 then 'Dr' else 'Cr' end DrCR
    from tblFixedAssetsPurchasemaster
    where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
    union all
    select acl.ledgername as heading, AccRefId  as ledgerid,sum(NettotalAmt) as Amount,'Cr' DrCR
    from tblFixedAssetsPurchasemaster TPM
    Inner join tbl_accountLedger acl ON acl.ledgerid=TPM.AccountLedgerID
    where TPM.AssetMasterID between @frombillId and @toBillID and  TPM.type in ('Purchases','Purchases Return')
    group by AccRefId,acl.LedgerName
END
GO


go