Ranish666

Untitled

Feb 19th, 2021
1,102
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. if not exists (select * from tbl_AccountLedger where RTRIM(Ltrim(ledgerName)) = 'Purchase CC Charge')
  2. begin
  3.     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)
  4.     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)
  5. end
  6. Go
  7. if not exists (select * from tbl_AccountLedger where RTRIM(Ltrim(ledgerName)) = 'Purchase CC Charge')
  8. begin
  9.     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)
  10.     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)
  11. end
  12. go
  13. CREATE proc [dbo].[TrialBalance_Groupwise]    
  14.  @fromdate datetime,                
  15.  @todate datetime,                
  16.  @branchid int,                
  17.  @financialYearid int,      
  18.  @reportType nvarchar(max)    
  19. as  
  20. DECLARE @noOfDecimalPlace INT = 5;  
  21.  
  22. if @reportType = 'SubGroupwise'    
  23. Begin    
  24.  declare @accountGroupId int, @accountGroupName nvarchar(max), @groupUnder int;    
  25.  Declare @temp table(accountGroupId int, accountGroupName nvarchar(max), groupUnder int)    
  26.    
  27.  --insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where accountGroupId =  0  
  28.  Declare datainsert cursor for            
  29.   select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup          
  30.   open datainsert    
  31.   Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder    
  32.   WHILE @@FETCH_STATUS=0              
  33.   Begin    
  34.   set @groupUnder = (select groupUnder from tbl_accountGroup where accountGroupId =  @accountGroupId)    
  35.   if @groupUnder = 0    
  36.   begin    
  37.    insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where accountGroupId =  @accountGroupId    
  38.    insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where groupUnder =  @accountGroupId    
  39.    insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where groupUnder in  (select accountGroupId from tbl_accountGroup where groupUnder =  @accountGroupId)    
  40.   end    
  41.   if @accountGroupId = 0  
  42.    begin    
  43.    insert into @temp select accountGroupId, accountGroupName, 0 from tbl_accountGroup where accountGroupId =  @accountGroupId    
  44.    --insert into @temp select accountGroupId, accountGroupName, 0 from tbl_accountGroup where groupUnder =  @accountGroupId    
  45.   end    
  46.   Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder    
  47.   End    
  48.      
  49.  CLOSE datainsert            
  50.  DEALLOCATE datainsert    
  51.    
  52.    
  53.  ;With      
  54.  PlGroups(accountGroupId) as(      
  55.    select accountGroupId from tbl_accountGroup where accountGroupId in (10,11,12,13,14,15)      
  56.    union all      
  57.    select accountGroupId from tbl_accountGroup where groupUnder in (10,11,12,13,14,15)      
  58.     ),      
  59.     trial1 as      
  60.   (      
  61.    select AG.accountGroupID, sum(TL.Debit)-Sum(TL.Credit) as opening          
  62.   from   tbl_ledgerposting TL          
  63.   inner join tbl_accountledger  AL ON AL.ledgerid=TL.LedgerID          
  64.   inner join tbl_accountGroup AG ON AG.AccountGroupID=AL.AccountGroupID      
  65.   where convert(date, TL.date) < convert(date, @fromdate )    and    
  66.    ag.accountGroupId not in (select accountGroupId from PlGroups)      
  67.   GROUP BY AG.accountGroupID,AG.AccountGroupName      
  68.   ),      
  69.   trial2 as (      
  70.   select AG.accountGroupID, Ag.AccountGroupName, sum(TL.Debit) as Debit, Sum(TL.Credit) as credit          
  71.   from tbl_ledgerposting TL          
  72.   inner join tbl_accountledger  AL ON AL.ledgerid=TL.LedgerID          
  73.   inner join tbl_accountGroup AG ON AG.AccountGroupID=AL.AccountGroupID    
  74.   where convert(date, TL.date) between convert(date, @fromdate) and convert(date, @toDate)      
  75.   and yearId = @financialyearid        
  76.   GROUP BY AG.accountGroupID,AG.AccountGroupName          
  77.     )    
  78.   select      
  79.   ag.accountGroupID, ag.accountGroupName, ag.groupUnder,      
  80.   isnull(t1.opening,0)  as OpeningBalance,      
  81.   concat (      
  82.    (convert(decimal(24, 3), abs(isnull(t1.opening,0) ))),      
  83.    case when isnull(t1.opening,0) > 0 then ' Dr'  else      
  84.     case when isnull(t1.opening,0) < 0 then ' Cr'  else '' end        
  85.    end      
  86.    )      
  87.   as OpeningBalanceStr,      
  88.   convert(decimal(24, 3),  isnull( t2.debit, 0) ) as Debit,      
  89.   convert(decimal(24, 3),   isnull( t2.credit, 0) ) as Credit,      
  90.   convert(decimal(24, 3),  isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0) ) as Balance,      
  91.   concat (      
  92.    (convert(decimal(24, 3), abs(isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0)))),      
  93.    case when isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0)> 0 then ' Dr'  else      
  94.     case when isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0) < 0 then ' Cr'  else '' end      
  95.    end      
  96.    ) as BalanceStr      
  97.   from @temp ag      
  98.  left join trial1 t1 on t1.accountGroupId = ag.accountGroupId      
  99.  left join trial2 t2 on t2.accountGroupId = ag.accountGroupId      
  100.   WHERE opening<>0 or Debit<>0 or Credit<>0    
  101. End      
  102.    
  103. else if @reportType = 'Groupwise'    
  104. Begin    
  105.  ;With      
  106.   PlGroups(accountGroupId) as(      
  107.    select accountGroupId from tbl_accountGroup where accountGroupId in (10,11,12,13,14,15)      
  108.    union all      
  109.    select accountGroupId from tbl_accountGroup where groupUnder in (10,11,12,13,14,15)      
  110.     ),      
  111.     mainGroups(accountGroupId) as (    
  112.    select accountGroupId from tbl_accountGroup where groupUnder in (0)    
  113.     ),    
  114.     trial1 as      
  115.   (      
  116.    select AG.accountGroupID, sum(TL.Debit)-Sum(TL.Credit) as opening          
  117.   from   tbl_ledgerposting TL          
  118.   inner join tbl_accountledger  AL ON AL.ledgerid=TL.LedgerID          
  119.   inner join tbl_accountGroup AG ON AG.AccountGroupID=AL.AccountGroupID          
  120.   where    
  121.   convert(date, TL.date) < convert(date, @fromdate ) and    
  122.   ag.accountGroupId not in (select accountGroupId from PlGroups)      
  123.   GROUP BY AG.accountGroupID,AG.AccountGroupName      
  124.   ),      
  125.   trial2 as (      
  126.   SELECT ag.accountGroupId, ag.accountGroupName,  
  127.  ISNULL(  
  128.   (  
  129.   SELECT SUM(ISNULL(lp.debit, 0))    
  130.   FROM tbl_AccountLedger   al  
  131.   LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId    
  132.   WHERE  convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)        
  133.    and yearId = @financialyearid  and  
  134.   (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))  
  135.  , 0)AS Debit,  
  136.  ISNULL(  
  137.   (  
  138.   SELECT SUM(ISNULL(lp.credit, 0))    
  139.   FROM tbl_AccountLedger   al  
  140.   LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId    
  141.   WHERE   convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)        
  142.    and yearId = @financialyearid  and  
  143.   (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))  
  144.  , 0)AS credit  
  145.  FROM tbl_AccountGroup  ag  
  146.  LEFT OUTER JOIN tbl_AccountLedger al1 ON ag.accountGroupId = al1.accountGroupId    
  147.  AND al1.accountGroupId IN (  
  148.   SELECT accountGroupId    
  149.   FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId))    
  150.   WHERE (ag.accountGroupId IN (  
  151.   SELECT accountGroupId FROM tbl_AccountGroup ag1    
  152.    WHERE (nature = 'Assets') AND (groupUnder = 0))  
  153.  )  
  154.  group by ag.accountGroupId, ag.accountGroupName  
  155.  union all  
  156.  
  157.  SELECT ag.accountGroupId, ag.accountGroupName,  
  158.  ISNULL(  
  159.   (  
  160.   SELECT SUM(ISNULL(lp.debit, 0))    
  161.   FROM tbl_AccountLedger   al  
  162.   LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId    
  163.   WHERE  convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)        
  164.    and yearId = @financialyearid  and  
  165.   (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))  
  166.  , 0)AS Debit,  
  167.  ISNULL(  
  168.   (  
  169.   SELECT SUM(ISNULL(lp.credit, 0))    
  170.   FROM tbl_AccountLedger   al  
  171.   LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId    
  172.   WHERE   convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)        
  173.    and yearId = @financialyearid  and  
  174.   (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))  
  175.  , 0)AS credit  
  176.  FROM tbl_AccountGroup  ag  
  177.  LEFT OUTER JOIN tbl_AccountLedger al1 ON ag.accountGroupId = al1.accountGroupId    
  178.  AND al1.accountGroupId IN (  
  179.  SELECT accountGroupId    
  180.  FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId))    
  181.  WHERE (ag.accountGroupId IN (  
  182.  SELECT accountGroupId FROM tbl_AccountGroup ag1    
  183.   WHERE (nature = 'Liabilities') AND (groupUnder = 0))  
  184.  )  
  185.  group by ag.accountGroupId, ag.accountGroupName  
  186.     )      
  187.      
  188.  select DISTINCT convert(int,ROW_NUMBER() OVER (ORDER BY ag.accountGroupId)) AS [Sl No],      
  189.   ag.accountGroupID, ag.accountGroupName,      
  190.   isnull(t1.opening,0)  as OpeningBalance,      
  191.   concat (      
  192.    (convert(decimal(24, 3), abs(isnull(t1.opening,0) ))),      
  193.    case when isnull(t1.opening,0) > 0 then ' Dr'  else      
  194.     case when isnull(t1.opening,0) < 0 then ' Cr'  else '' end        
  195.    end      
  196.    )      
  197.   as OpeningBalanceStr,      
  198.   convert(decimal(24, 3),  isnull( t2.debit, 0) ) as Debit,      
  199.   convert(decimal(24, 3),   isnull( t2.credit, 0) ) as Credit,      
  200.   convert(decimal(24, 3),  isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0) ) as Balance,      
  201.   concat (      
  202.    (convert(decimal(24, 3), abs(isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0)))),      
  203.    case when isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0)> 0 then ' Dr'  else      
  204.     case when isnull(t1.opening,0)  + isnull( t2.debit, 0) - isnull( t2.credit, 0) < 0 then ' Cr'  else '' end      
  205.    end      
  206.    ) as BalanceStr      
  207.   from tbl_accountGroup ag    
  208.   --inner join mainGroups m on m.accountGroupId = ag.accountGroupId    
  209.   left join trial1 t1 on t1.accountGroupId = ag.accountGroupId      
  210.   left join trial2 t2 on t2.accountGroupId = ag.accountGroupId      
  211.   WHERE opening<>0 or Debit<>0 or Credit<>0    
  212.      
  213. End  
  214. go
  215. CREATE proc [dbo].[TrialBalance_LedgerWise]    
  216. @fromDate date, @toDate date, @branchid int, @financialYearid int    
  217. as    
  218. declare @accountGroupId int, @accountGroupName nvarchar(max), @groupUnder int;    
  219. Declare @temp table(ledgerId int, ledgerName nvarchar(max))    
  220. Declare datainsert cursor for            
  221.  select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup          
  222.  open datainsert    
  223.  Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder    
  224.  WHILE @@FETCH_STATUS=0              
  225.  Begin    
  226.  set @groupUnder = (select groupUnder from tbl_accountGroup where accountGroupId =  @accountGroupId)    
  227.  if @groupUnder = 0    
  228.  begin    
  229.   insert into @temp select 0, accountGroupName from tbl_accountGroup where accountGroupId =  @accountGroupId    
  230.   insert into @temp select ledgerId, ledgerName from tbl_AccountLedger where accountGroupId =  @accountGroupId order by ledgerId    
  231.  end    
  232.  else    
  233.  Begin    
  234.   insert into @temp select 0, accountGroupName from tbl_accountGroup where accountGroupId =  @accountGroupId    
  235.   insert into @temp select ledgerId, ledgerName from tbl_AccountLedger where accountGroupId =  @accountGroupId order by ledgerId    
  236.  End    
  237.      
  238.  Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder    
  239.  End    
  240.      
  241. CLOSE datainsert            
  242. DEALLOCATE datainsert    
  243.    
  244.    
  245. create table #PlGroups (accountGroupId int)    
  246. insert into #PlGroups    
  247.  select accountGroupId from tbl_accountGroup where accountGroupId in (10,11,12,13,14,15)      
  248.  union all      
  249.  select accountGroupId from tbl_accountGroup where groupUnder in (10,11,12,13,14,15)    
  250.  
  251. Create table #AccountGroups (accountGroupId int)  
  252. insert into #AccountGroups  
  253.  select accountGroupId from tbl_AccountGroup  
  254. create table #ledgerData (ledgerId int, accountGroupId int, ledgerName nvarchar(max), Opening nvarchar(max), op decimal(18,5),    
  255.       Debit decimal(18,5), Credit decimal(18,5), Closing nvarchar(max), Closing1 decimal(18,5), Total_Closing nvarchar(max))    
  256. insert into #ledgerData    
  257.  SELECT                  
  258.  ledgerId,  accountGroupId, ledgerName,                
  259.  CASE WHEN CAST(ROUND(isnull(SUM(Opening) ,0),2)AS DECIMAL(24,2))<0 THEN                
  260.    CAST(CAST(ROUND(isnull(SUM(-Opening) ,0),2e)AS DECIMAL(24,2)) AS VARCHAR(50))+'Cr'                
  261.  ELSE                
  262.   CAST(CAST(ROUND(isnull(SUM(Opening) ,0),2)AS DECIMAL(24,2)) AS VARCHAR(50))+'Dr'                
  263.  END AS Opening,                
  264.  CAST(ROUND(isnull(SUM(Opening) ,0),2)AS DECIMAL(24,2)) AS op,                
  265.  CAST( ROUND(isnull(SUM(Debit) ,0),2)AS DECIMAL(24,2)) AS Debit,                
  266.  CAST( ROUND(isnull(SUM(Credit) ,0),2)AS DECIMAL(24,2)) AS Credit,                
  267.  CASE WHEN  (Opening+debit-credit)>0  then CONVERT(VARCHAR(MAX),convert(decimal(18,2),Round( (Opening+debit-credit), 2)))+'Dr' else              
  268.  CONVERT(VARCHAR(MAX),-1*convert(decimal(18,2),Round( (Opening+debit-credit), 2) ) )+'Cr' end AS Closing,                
  269.  convert(decimal(18, 2), (Round((Opening+debit-credit), 2))) AS Closing1,                
  270.  CASE WHEN  (Opening+debit-credit)>0  then CONVERT(VARCHAR(MAX),convert(decimal(18,2),Round( (Opening+debit-credit), 2) ))+'Dr' else              
  271.  CONVERT(VARCHAR(MAX),-1*convert(decimal(18,2),Round((Opening+debit-credit), 2)) )+'Cr' end AS Total_Closing          
  272.  --CASE WHEN  (totalopening)>0 then CONVERT(VARCHAR(MAX),convert(decimal(18,2),(totalopening)))+'Dr'              
  273.  --else CONVERT(VARCHAR(MAX),-1*convert(decimal(18,2),(totalopening)) )+'Cr' end AS Total_Closing                
  274.  FROM                
  275.  (                
  276.     SELECT B.ledgerId, B.ledgerName, B.accountGroupId,                
  277.    (SELECT    
  278.  ISNULL(SUM(debit),0) -ISNULL(SUM(credit), 0) AS OP                
  279.  FROM tbl_LedgerPosting AS LP        
  280.  inner join tbl_AccountLedger al on al.ledgerId = lp .ledgerId and al.accountGroupId not in (select accountGroupId from #PlGroups)                  
  281.  WHERE (LP.ledgerId=B.ledgerId            
  282.  AND (LP.date<@fromDate)    
  283.  ))                
  284.    AS Opening,                
  285.    (SELECT ISNULL(SUM(debit),0) -ISNULL(SUM(credit), 0) AS OP                
  286.  FROM tbl_LedgerPosting AS LP                          
  287.     WHERE                
  288.     LP.ledgerId=B.ledgerId and yearId = @financialyearid      
  289.  and lp.voucherTypeId !=1            
  290.  and LP.date BETWEEN @fromDate AND @toDate    
  291.  )        
  292.    AS totalopening,                
  293.  (select                  
  294.      ISNULL(SUM(debit),0)AS OP                
  295.  FROM tbl_LedgerPosting AS LP                
  296.  WHERE                
  297.     LP.ledgerId=B.ledgerId and yearId = @financialyearid      
  298.  and lp.voucherTypeId !=1            
  299.  and LP.date BETWEEN @fromDate AND @toDate    
  300.  ) AS Debit,                
  301.  (                
  302.  SELECT                
  303.      ISNULL(SUM(credit),0) AS OP                
  304.  FROM tbl_LedgerPosting AS LP                
  305.  WHERE                
  306.     Lp.ledgerId=B.ledgerId and yearId = @financialyearid   and    
  307.  lp.voucherTypeId !=1            
  308.  and LP.date BETWEEN @fromDate AND @toDate    
  309.  )AS Credit                
  310.                    
  311.  FROM  tbl_AccountLedger AS B                
  312.  WHERE  accountGroupId in( SELECT accountGroupId  From #AccountGroups )                            
  313.  )AS TEMP    
  314.  WHERE(TEMP.Debit<>0 OR TEMP.Credit<>0 OR opening<>0)                
  315.  GROUP BY ledgerId,ledgerName,opening,credit,debit,totalopening,accountGroupId    
  316.    
  317. declare @ledgerId int, @ledgerName nvarchar(max), @OpeningBalance decimal(18, 5),    
  318.   @OpeningBalanceStr nvarchar(max), @Debit decimal(18, 5), @Credit decimal(18, 5),    
  319.   @Balance decimal(18, 5), @BalanceStr nvarchar(max);    
  320. Declare @tempData table(    
  321.   ledgerId int, ledgerName nvarchar(max), accountGroupId int,    
  322.   OpeningBalance decimal(18, 5),    
  323.   OpeningBalanceStr nvarchar(max), Debit decimal(18, 5), Credit decimal(18, 5),    
  324.   Balance decimal(18, 5), BalanceStr nvarchar(max))    
  325.    
  326. Declare datainsert cursor for            
  327.   select t.ledgerId, t.ledgerName, d.accountGroupId,    
  328.   isnull(d.op,0) OpeningBalance, isnull(d.Opening,0) OpeningBalanceStr,    
  329.   isnull(d.Debit,0) Debit, isnull(d.Credit,0) Credit,      
  330.   isnull(d.Closing1,0) Balance, isnull(d.Total_Closing,0) BalanceStr    
  331.   from @temp t    
  332.   left join #ledgerData d on d.ledgerId = t.ledgerId    
  333.  open datainsert    
  334.  Fetch next from datainsert into @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr    
  335.  WHILE @@FETCH_STATUS=0              
  336.  Begin    
  337.  if @ledgerId = 0    
  338.  begin    
  339.   insert into @tempData select @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr    
  340.  end    
  341.  else    
  342.  Begin    
  343.   if @Debit <> 0 or @Credit <> 0  or @OpeningBalance <>0  
  344.   insert into @tempData select @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr    
  345.  End    
  346.      
  347.  Fetch next from datainsert into @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr    
  348.  End    
  349.      
  350. CLOSE datainsert            
  351. DEALLOCATE datainsert    
  352.      
  353.  select ledgerId, ledgerName, accountGroupId,    
  354.   OpeningBalance,    
  355.   OpeningBalanceStr, Debit, Credit,    
  356.   Balance, BalanceStr from @tempData    
  357.      
  358. Drop table #PlGroups;  
  359. Drop table #AccountGroups;  
  360. Drop table #ledgerData;  
  361. Go
  362.  
  363. GO
  364. truncate table [tblDashboardSetting]
  365. go
  366. SET IDENTITY_INSERT [dbo].[tblDashboardSetting] ON
  367. GO
  368. INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (1, N'Patient Record')
  369. GO
  370. INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (2, N'Departmentwise Admitted Record')
  371. GO
  372. INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (3, N'Doctorwise Admitted Record')
  373. GO
  374. INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (4, N'Vistor Record In Percentage')
  375. GO
  376. INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (5, N'Weekly Total Revenue')
  377. GO
  378. INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (6, N'Book Appointment')
  379. GO
  380. INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (7, N'Departmentwise Discharged Record')
  381. GO
  382. INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (8, N'Total Bed')
  383. GO
  384. INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (9, N'Inpatient Morbidity')
  385. GO
  386. SET IDENTITY_INSERT [dbo].[tblDashboardSetting] OFF
  387. GO
  388.  
  389. Go
  390. Create proc AccountFixedAssetsPurchasegetByBillNo
  391. @fromBillNo nvarchar(max),  
  392. @toBillNo nvarchar(max)  
  393. as
  394. declare @frombillId int = (isnull((select AssetMasterID from tblFixedAssetsPurchasemaster Where ReferenceNo = @fromBillNo),0));
  395. declare @toBillID int = (isnull((select AssetMasterID from tblFixedAssetsPurchasemaster Where ReferenceNo = @toBillNo),0));
  396. if @frombillId<>0 and @toBillID<>0
  397. BEGIN
  398.     select 'Purchase A/c' as heading,11 as ledgerid,sum(BillAmt) as Amount,'Dr'
  399.      DrCR from tblFixedAssetsPurchasemaster
  400.      where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  401.     Union all
  402.     select 'VAT A/c' as heading,10022 as ledgerid,sum(taxAmt) as Amount,'Dr' DrCR from tblFixedAssetsPurchasemaster
  403.     where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  404.     Union all
  405.     select 'Discount Allow A/c' as heading,8 as ledgerid,sum(DIscAmt) as Amount,
  406.     'Cr' DrCR from tblFixedAssetsPurchasemaster
  407.     where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  408.     --Union all
  409.     --Purchase CC Charge --group under 15
  410.     --select 'Purchase CC Charge' as heading,10020  as ledgerid, ABS(sum(ccamt)) as Amount,'Dr' DrCR
  411.     --from tblFixedAssetsPurchasemaster
  412.     --where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  413.     union all
  414.     --Purchase Adjustment --group under 15
  415.     select 'Purchase Adjustment' as heading,10021  as ledgerid, abs(sum(AdjustmentAmt)) as Amount,
  416.      case when sum(AdjustmentAmt) > 0 then 'Dr' else 'Cr' end DrCR
  417.     from tblFixedAssetsPurchasemaster
  418.     where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  419.     union all
  420.     select acl.ledgername as heading, AccRefId  as ledgerid,sum(NettotalAmt) as Amount,'Cr' DrCR
  421.     from tblFixedAssetsPurchasemaster TPM
  422.     Inner join tbl_accountLedger acl ON acl.ledgerid=TPM.AccountLedgerID
  423.     where TPM.AssetMasterID between @frombillId and @toBillID and  TPM.type in ('Purchases','Purchases Return')
  424.     group by AccRefId,acl.LedgerName
  425. END
  426. GO
  427.  
  428.  
  429. go
  430.  
  431.  
  432.  
  433.  
RAW Paste Data