API tools faq
paste
Advertisement
Ranish666

code for birat

Ranish666
Mar 9th, 2021
1,034
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 18.51 KB | None | 0 0
raw download clone embed print report
  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.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!