hospital journal counter

ALTER Procedure [dbo].[AccountDetailsgetByBillNo]
@fromBillNo nvarchar(max),
@toBillNo nvarchar(max) ,
@userId int = null
AS
declare @patientLedger decimal(18,5)
declare @frombillId int
declare @toBillID int
set @frombillId= (isnull((select billingMasterID from tblbillingmaster Where strBillNo=@fromBillNo),0))
set @toBillID= (isnull((select billingMasterID from tblbillingmaster Where strBillNo=@toBillNo),0))

set @patientLedger = (select
sum(BillDiscount) + sum(DiscountAmt) + sum(ReceiveAmt) - sum(TaxAmt) - sum(total + DiscountAmt -taxAmt)
from tblbillingmaster WHERE AcledgerID=22 and billingMasterID between @frombillId and @toBillID
and UserID = case when @userId is null or @userId = 0 then UserID else @userId end )

; with tempCash as
 (select accountGroupId from tbl_AccountGroup where accountGroupId = 27
 union
 select accountGroupId from tbl_AccountGroup where groupUnder = 27)
 , tempBank as (select accountGroupId from tbl_AccountGroup where accountGroupId = 28
 union
 select accountGroupId from tbl_AccountGroup where groupUnder = 28)

select  'sales' as heading,16 as ledgerid,
--sum(total + DiscountAmt) as amount,
sum(total + DiscountAmt -taxAmt) as amount,
'Cr' as DrCR
from tblbillingmaster Where billingMasterID between @frombillId and @toBillID
and UserID = case when @userId is null or @userId = 0 then UserID else @userId end

Union ALL

select 'cash' as heading,1 as ledgerid, isnull( sum(receiveAmt) ,0) as amount, 'Dr' as DrCR
from tblbillingmaster bm
inner join tempCash c on c.accountGroupId in (select accountGroupId from tbl_AccountLedger where ledgerId =   ReceiveLedgerID)
Where billingMasterID between @frombillId and @toBillID
and UserID = case when @userId is null or @userId = 0 then UserID else @userId end

Union ALL

select 'Discount' as heading, 8 as ledgerid,
sum(discountAmt+BillDiscount )
as amount, 'Dr' as DrCR
from tblbillingmaster Where billingMasterID between @frombillId and @toBillID
and UserID = case when @userId is null or @userId = 0 then UserID else @userId end

Union ALL
select 'tax' as heading, 36 as ledgerid, sum(taxAmt) as tax, 'Cr' as DrCR from tblbillingmaster Where billingMasterID between @frombillId and @toBillID
and UserID = case when @userId is null or @userId = 0 then UserID else @userId end

Union ALL

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

Union ALL

select ac.ledgerName as heading,
(select AccRefId from tbl_AccountLedger where ledgerId = ReceiveLedgerID) as ledgerid,
--(sum(total + case when billdispercent = 100 or BillingType = 'Credit' then 0 else
--discountAmt+BillDiscount
--end-taxAmt)+sum(taxAmt))-(sum(receiveAmt)+sum(taxAmt))
--(sum(total) - sum(receiveAmt))
sum(receiveAmt)
as amount,
--case when (select accountGroupId from tbl_AccountLedger where ledgerId = ReceiveLedgerID) = 28 then 'Dr'
--else case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end
--end
'Dr'
as DrCR
--case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end as DrCR

from tblbillingmaster tm
inner join tbl_accountledger ac on tm.ReceiveLedgerID = ac.ledgerid
inner join tempBank bank on bank.accountGroupId in (select accountGroupId from tbl_AccountLedger where ledgerId =   ReceiveLedgerID)
WHERE  tm.billingMasterID between @frombillId and @toBillID
and tm.UserID = case when @userId is null or @userId = 0 then tm.UserID else @userId end
group by tm.ReceiveLedgerID, ac.ledgerName