Ranish666

code for birat

Mar 9th, 2021
905
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. delete from tblmenu where ParentID in (20,60)
  2. update tblmenu set hasSubMenu = 1 where intMenuid = 60
  3. go
  4. go
  5. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'A/C Ledger', N'', N'', 1, 20, 1, 0, N'/Account/AccountLedger/Index', 1, N'fas fa-file-invoice')
  6. GO
  7. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Journal Voucher', N'', N'', 1, 20, 1, 0, N'/Account/JournalVoucherSub/Index', 1, N'fas fa-file-invoice')
  8. GO
  9. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Payment Voucher', N'', N'', 1, 20, 1, 0, N'/Account/PaymentVoucher/Index', 1, N'fas fa-file-invoice')
  10. GO
  11. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Receipt Voucher', N'', N'', 1, 20, 1, 0, N'/Account/ReceiptVoucher/Index', 1, N'fas fa-file-invoice')
  12. GO
  13. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Contra Voucher', N'', N'', 1, 20, 1, 0, N'/Account/ContraVoucher/Index', 1, N'fas fa-file-invoice')
  14. GO
  15. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Materialized ', N'', N'', 1, 3096, 1, 0, N'/Log/MaterializedReport', 1, N'fas fa-layer-group')
  16. GO
  17. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Ledger Report', N'', N'', 1, 60, 1, 0, N'/Account/AcLedgerReport/Index', 1, N'fas fa-file-invoice')
  18. GO
  19. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Hospital Journal', N'', N'', 1, 20, 1, 0, N'/Account/HJournal/Index', 1, N'fas fa-file-invoice')
  20. GO
  21. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Aging Report', N'', N'', 1, 60, 1, 0, N'/Account/AgingReport/Index', 1, N'fas fa-file-invoice')
  22. GO
  23. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Bank Cash Book', N'', N'', 1, 60, 1, 0, N'/Budget/BankCashBook/Index', 1, N'fas fa-file-invoice')
  24. GO
  25. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Budget Baadhphaad', N'', N'', 1, 60, 1, 0, N'/Budget/BudgetBaadhphaad/Index', 1, N'fas fa-file-invoice')
  26. GO
  27. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Journal Register', N'', N'', 1, 60, 1, 0, N'/Account/JournalReport/Index', 1, N'fas fa-file-invoice')
  28. GO
  29. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Payment Register', N'', N'', 1, 60, 1, 0, N'/Account/PaymentReport/Index', 1, N'fas fa-file-invoice')
  30. GO
  31. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Contra Register', N'', N'', 1, 60, 1, 0, N'/Account/ContraReport/Index', 1, N'fas fa-file-invoice')
  32. GO
  33. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Receipt Register', N'', N'', 1, 60, 1, 0, N'/Account/ReceiptReport/Index', 1, N'fas fa-file-invoice')
  34. GO
  35. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Balance Sheet', N'', N'', 1, 60, 1, 0, N'/Account/BalanceSheet/Index', 1, N'fas fa-file-invoice')
  36. GO
  37. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Trial Balance', N'', N'', 1, 60, 1, 0, N'/Account/TrialBalance/Index', 1, N'fas fa-file-invoice')
  38. GO
  39. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Bank Reconciliation', N'', N'', 1, 20, 1, 0, N'/Account/BankReconciliation/Index', 1, N'fas fa-file-invoice')
  40. GO
  41. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Cash Flow', N'', N'', 1, 60, 1, 0, N'/Account/CashFlow/Index', 1, N'fas fa-file-invoice')
  42. GO
  43. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES (N'Debit Credit Note', N'', N'', 1, 20, 1, 0, N'/Account/DebitCreditNote/Index', 1, N'fas fa-file-invoice')
  44. GO
  45. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Debit Credit Note Report', N'', N'', 1, 60, 1, 0, N'/Account/DebitCreditNote/Report', 1, N'fas fa-file-invoice')
  46. GO
  47. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Journal Voucher Sub', N'', N'', 1, 20, 1, 0, N'/Account/JournalVoucherSub/Index', 1, N'fas fa-file-invoice')
  48. GO
  49. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'PDC', N'', N'', 1, 20, 1, 0, N'/Account/PDC/Index', 1, N'fas fa-file-invoice')
  50. GO
  51. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'PDC Clearance', N'', N'', 1, 20, 1, 0, N'/Account/PDC/PDCClearance', 1, N'fas fa-file-invoice')
  52. GO
  53. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'PDC Report', N'', N'', 1, 60, 1, 0, N'/Account/PDCReport/Index', 1, N'fas fa-file-invoice')
  54. GO
  55. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'PDC Clearance Report', N'', N'', 1, 60, 1, 0, N'/Account/PDCReport/PDCClearanceReport', 1, N'fas fa-file-invoice')
  56. GO
  57. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Profit & Loss', N'', N'', 1, 60, 1, 0, N'/Account/ProfitAndLoss/Index', 1, N'fas fa-file-invoice')
  58. GO
  59. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Budget Type', N'', N'', 1, 20, 1, 0, N'/Budget/BudgetType/Index', 1, N'fas fa-file-invoice')
  60. GO
  61. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Budget Category', N'', N'', 1, 20, 1, 0, N'/Budget/BudgetCategory/Index', 1, N'fas fa-file-invoice')
  62. GO
  63. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Budget Title', N'', N'', 1, 20, 1, 0, N'/Budget/BudgetTitle/Index', 1, N'fas fa-file-invoice')
  64. GO
  65. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Budget Khata Report', N'', N'', 1, 60, 1, 0, N'/Budget/BudgetKhataReport/Index', 1, N'fas fa-file-invoice')
  66. GO
  67. INSERT [dbo].[tblmenu] ( [strName], [strFormName], [strShorCut], [isOpen], [ParentID], [isActive], [hasSubMenu], [WebUrl], [IsActiveWeb], [Menuicon]) VALUES ( N'Budget Transfer', N'', N'', 1, 20, 1, 0, N'/Budget/BudgetTransfer/Index', 1, N'fas fa-file-invoice')
  68. GO
  69. go
  70. ALTER Procedure [dbo].[AccountPurchaseDetailsgetByBillNo]
  71. @fromBillNo nvarchar(max),
  72. @toBillNo nvarchar(max)
  73. AS
  74. BEGIN
  75. declare @patientLedger decimal(18,5)
  76. declare @frombillId int
  77. declare @toBillID int
  78. set @frombillId= (isnull((select PurchaseMasterID from tblpurchaseMaster Where ReferenceNo=@fromBillNo),0))
  79. set @toBillID= (isnull((select PurchaseMasterID from tblpurchaseMaster Where ReferenceNo=@toBillNo),0))
  80. if @frombillId<>0 and @toBillID<>0
  81. BEGIN
  82.     select 'Purchase A/c' as heading,11 as ledgerid,sum(BillAmt) as Amount,'Dr'
  83.      DrCR from tblpurchaseMaster
  84.      where PurchaseMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  85.     Union all
  86.     select 'VAT A/c' as heading,10022 as ledgerid,sum(taxAmt) as Amount,'Dr' DrCR from tblpurchaseMaster
  87.     where PurchaseMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  88.     Union all
  89.     select 'Discount Allow A/c' as heading,8 as ledgerid,sum(DiscAmt+billdisamt) as Amount,
  90.     'Cr' DrCR from tblpurchaseMaster
  91.     where PurchaseMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  92.     Union all
  93.     --Purchase CC Charge
  94.     select 'Purchase CC Charge' as heading,141011  as ledgerid, ABS(sum(ccamt)) as Amount,'Dr' DrCR
  95.     from tblpurchaseMaster
  96.     where PurchaseMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  97.     union all
  98.     --Purchase Adjustment
  99.     select 'Purchase Adjustment' as heading,140988  as ledgerid, abs(sum(AdjustmentAmt)) as Amount,
  100.      case when sum(AdjustmentAmt) > 0 then 'Dr' else 'Cr' end DrCR
  101.     from tblpurchaseMaster
  102.     where PurchaseMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  103.     union all
  104.     select acl.ledgername as heading, AccRefId  as ledgerid,sum(NettotalAmt) as Amount,'Cr' DrCR
  105.     from tblpurchaseMaster TPM
  106.     Inner join tbl_accountLedger acl ON acl.ledgerid=TPM.AccountLedgerID
  107.     where TPM.PurchaseMasterID between @frombillId and @toBillID and  TPM.type in ('Purchases','Purchases Return')
  108.     group by AccRefId,acl.LedgerName
  109. END
  110. END
  111. go
  112. GO
  113. ALTER proc [dbo].[AccountFixedAssetsPurchasegetByBillNo]
  114. @fromBillNo nvarchar(max),  
  115. @toBillNo nvarchar(max)  
  116. as
  117. declare @frombillId int = (isnull((select AssetMasterID from tblFixedAssetsPurchasemaster Where ReferenceNo = @fromBillNo),0));
  118. declare @toBillID int = (isnull((select AssetMasterID from tblFixedAssetsPurchasemaster Where ReferenceNo = @toBillNo),0));
  119. if @frombillId<>0 and @toBillID<>0
  120. BEGIN
  121.     select 'Purchase A/c' as heading,11 as ledgerid,sum(BillAmt) as Amount,'Dr'
  122.      DrCR from tblFixedAssetsPurchasemaster
  123.      where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  124.     Union all
  125.     select 'VAT A/c' as heading,10022 as ledgerid,sum(taxAmt) as Amount,'Dr' DrCR from tblFixedAssetsPurchasemaster
  126.     where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  127.     Union all
  128.     select 'Discount Allow A/c' as heading,8 as ledgerid,sum(DIscAmt) as Amount,
  129.     'Cr' DrCR from tblFixedAssetsPurchasemaster
  130.     where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  131.     --Union all
  132.     --Purchase CC Charge --group under 15
  133.     --select 'Purchase CC Charge' as heading,10020  as ledgerid, ABS(sum(ccamt)) as Amount,'Dr' DrCR
  134.     --from tblFixedAssetsPurchasemaster
  135.     --where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  136.     union all
  137.     --Purchase Adjustment --group under 15
  138.     select 'Purchase Adjustment' as heading,10021  as ledgerid, abs(sum(AdjustmentAmt)) as Amount,
  139.      case when sum(AdjustmentAmt) > 0 then 'Dr' else 'Cr' end DrCR
  140.     from tblFixedAssetsPurchasemaster
  141.     where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
  142.     union all
  143.     select acl.ledgername as heading, AccRefId  as ledgerid,sum(NettotalAmt) as Amount,'Cr' DrCR
  144.     from tblFixedAssetsPurchasemaster TPM
  145.     Inner join tbl_accountLedger acl ON acl.ledgerid=TPM.AccountLedgerID
  146.     where TPM.AssetMasterID between @frombillId and @toBillID and  TPM.type in ('Purchases','Purchases Return')
  147.     group by AccRefId,acl.LedgerName
  148. END
  149. go
  150. GO
  151. ALTER Procedure [dbo].[AccountDetailsgetPharmacyByBillNo]
  152. @fromBillNo nvarchar(max),  
  153. @toBillNo nvarchar(max)  
  154.  
  155. AS  
  156. declare @patientLedger decimal(18,2)  
  157. declare @frombillId int  
  158. declare @toBillID int  
  159. set @frombillId= (isnull((select pharmacyMasterID from tblPharmacySalesMaster Where strBillNo=@fromBillNo),0))  
  160. set @toBillID= (isnull((select pharmacyMasterID from tblPharmacySalesMaster Where strBillNo=@toBillNo),0))  
  161.  
  162. set @patientLedger = (select  
  163. ((sum(receiveAmt)+sum(discountAmt+pharmacyDiscount)-sum(total + DiscountAmt-taxAmt)+sum(taxAmt)))  
  164. from tblPharmacySalesMaster WHERE AcledgerID=22 and pharmacyMasterID between @frombillId and @toBillID )  
  165.  
  166. select  'sales' as heading,18 as ledgerid,  
  167. ABS(sum(total  + DiscountAmt-taxAmt))  
  168. as amount,  
  169. Case WHEN sum(total + DiscountAmt-taxAmt)>0 THEN 'Cr' else 'Dr' End  DrCR  
  170. from tblPharmacySalesMaster Where pharmacyMasterID between @frombillId and @toBillID  
  171.  
  172. Union ALL  
  173.  
  174. select 'cash' as heading,1 as ledgerid,  
  175. ABS(sum(receiveAmt))  
  176. as amount, case WHEN sum(receiveAmt)> 0 Then  'Dr' else 'Cr' end DrCR  
  177. from tblPharmacySalesMaster Where pharmacyMasterID between @frombillId and @toBillID  
  178. and LOWER(PharmacyType) <> 'bank'  
  179.  
  180. Union ALL  
  181.  
  182. select 'Discount' as heading, 8 as ledgerid,  
  183. ABS(sum(discountAmt + pharmacyDiscount ))  
  184. as amount,  --case WHEN sum(case when billdispercent = 100 then 0 else discountAmt end +pharmacyDiscount) > 0 THEN 'Dr' else 'Cr'  END DrCR  
  185. 'Dr' as DrCR    
  186. from tblPharmacySalesMaster Where pharmacyMasterID between @frombillId and @toBillID  
  187.  
  188. Union ALL  
  189.  
  190. select 'tax' as heading, 10022 as ledgerid,  
  191. ABS((sum(taxAmt)))  
  192. as tax, case WHEN (sum(taxAmt))>0 then  'Cr' else 'Dr'  END  DrCR  
  193. from tblPharmacySalesMaster Where pharmacyMasterID between @frombillId and @toBillID  
  194.  
  195. Union ALL  
  196.  
  197. select 'patientLedger' as heading,22 as ledgerid,  
  198. ABS(@patientLedger)  
  199. as amount, case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end  DrCR  
  200.  
  201. Union ALL  
  202.  
  203. select ac.ledgerName as heading,(select AccRefId from tbl_AccountLedger where ledgerId=AcLedgerID) as ledgerid,  
  204. case when sum(receiveAmt) = 0 then sum(Total) else sum(ReceiveAmt) end as amount,'Dr' DrCr  
  205. --ABS((sum(total + DiscountAmt-taxAmt - (case when tm.pharmacytype = 'credit' then tm.PharmacyDiscount else 0 end))+sum(taxAmt))-(sum(receiveAmt)+sum(taxAmt))) as amount,  
  206.  --case when (sum(total + DiscountAmt-taxAmt - (case when tm.pharmacytype = 'credit' then tm.PharmacyDiscount else 0 end))+sum(taxAmt))-(sum(receiveAmt)+sum(taxAmt)) >0 then  'Dr' else 'Cr' END DrCr  
  207. from tblPharmacySalesMaster tm  
  208. inner join tbl_accountledger ac on tm.AcledgerID=ac.ledgerid  
  209. WHERE AcledgerID<>22
  210. and tm.pharmacyMasterID between @frombillId and @toBillID  
  211. group by tm.AcledgerID,ac.ledgerName  
  212. go
  213. GO
  214. ALTER Procedure [dbo].[AccountDetailsgetByBillNo]
  215. @fromBillNo nvarchar(max),  
  216. @toBillNo nvarchar(max)  
  217. AS  
  218. declare @patientLedger decimal(18,5)  
  219. declare @frombillId int  
  220. declare @toBillID int  
  221. set @frombillId= (isnull((select billingMasterID from tblbillingmaster Where strBillNo=@fromBillNo),0))  
  222. set @toBillID= (isnull((select billingMasterID from tblbillingmaster Where strBillNo=@toBillNo),0))  
  223.  
  224. set @patientLedger = (select ((sum(receiveAmt)+sum(discountAmt+BillDiscount)-sum(total + DiscountAmt-taxAmt)+sum(taxAmt)))
  225. from tblbillingmaster WHERE AcledgerID=22 and billingMasterID between @frombillId and @toBillID )  
  226.  
  227. ; with tempCash as
  228.     (select accountGroupId from tbl_AccountGroup where accountGroupId = 27
  229.     union
  230.     select accountGroupId from tbl_AccountGroup where groupUnder = 27)
  231.     , tempBank as (select accountGroupId from tbl_AccountGroup where accountGroupId = 28
  232.     union
  233.     select accountGroupId from tbl_AccountGroup where groupUnder = 28)
  234.  
  235. select  'sales' as heading,16 as ledgerid,
  236. sum(total + DiscountAmt) as amount,
  237. --sum(total + DiscountAmt + BillDiscount -taxAmt) as amount,
  238. 'Cr' as DrCR  
  239. from tblbillingmaster Where billingMasterID between @frombillId and @toBillID  
  240.  
  241. Union ALL  
  242.  
  243. select 'cash' as heading,1 as ledgerid, isnull( sum(receiveAmt) ,0) as amount, 'Dr' as DrCR  
  244. from tblbillingmaster bm
  245. inner join tempCash c on c.accountGroupId in (select accountGroupId from tbl_AccountLedger where ledgerId =   ReceiveLedgerID)
  246. Where billingMasterID between @frombillId and @toBillID
  247.  
  248. Union ALL  
  249.  
  250. select 'Discount' as heading, 8 as ledgerid,  
  251. sum(discountAmt+BillDiscount )  
  252. as amount, 'Dr' as DrCR  
  253. from tblbillingmaster Where billingMasterID between @frombillId and @toBillID  
  254. Union ALL  
  255. select 'tax' as heading, 36 as ledgerid, sum(taxAmt) as tax, 'Cr' as DrCR from tblbillingmaster Where billingMasterID between @frombillId and @toBillID  
  256.  
  257. Union ALL  
  258.  
  259. select 'patientLedger' as heading,22 as ledgerid, ABS(@patientLedger)  as amount, case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end as DrCR  
  260.  
  261. Union ALL  
  262.  
  263. select ac.ledgerName as heading,
  264. (select AccRefId from tbl_AccountLedger where ledgerId = ReceiveLedgerID) as ledgerid,  
  265. --(sum(total + case when billdispercent = 100 or BillingType = 'Credit' then 0 else  
  266. --discountAmt+BillDiscount  
  267. --end-taxAmt)+sum(taxAmt))-(sum(receiveAmt)+sum(taxAmt))
  268. --(sum(total) - sum(receiveAmt))
  269. sum(receiveAmt)
  270. as amount,  
  271. --case when (select accountGroupId from tbl_AccountLedger where ledgerId = ReceiveLedgerID) = 28 then 'Dr'
  272. --else case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end
  273. --end
  274. 'Dr'
  275. as DrCR    
  276. --case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end as DrCR    
  277.  
  278. from tblbillingmaster tm  
  279. inner join tbl_accountledger ac on tm.ReceiveLedgerID = ac.ledgerid  
  280. inner join tempBank bank on bank.accountGroupId in (select accountGroupId from tbl_AccountLedger where ledgerId =   ReceiveLedgerID)
  281. WHERE  tm.billingMasterID between @frombillId and @toBillID  
  282. group by tm.ReceiveLedgerID, ac.ledgerName
  283. go
  284.  
RAW Paste Data