changes in sql 2021-05-25

go
Alter proc [dbo].[purhase]
@referenceno nvarchar(max),
@ledgerid nvarchar(max),
@vendorbillno nvarchar(max),
@fromdate datetime,
@todate datetime,
@purchasetype nvarchar(max)

as

if (@referenceno<>'')
BEGIN
   select   row_number()  OVER ( order by pm.PurchaseMasterID) as 'Sno',
  (convert(decimal(18,2),isnull(pm.BillAmt,0))-convert(decimal(18,2),isnull(pm.nettotalamt,0)))as CashDisc ,
   pm.TaxAmt, pm.Type, pm.mode,
pm.PurchaseMasterID, pm.ccamt, isnull(pm.billdisamt,0) as billdisamt,
pm.AdjustmentAmt, tu.strName,
pm.referenceno,pm.vendorbillno,
convert(decimal(18,2),isnull(pm.nettotalamt,0))as nettotalamt,
al.ledgername,tu.strUsername,
convert(decimal(18,2),isnull(pm.BillAmt,0))as BillAmt,
convert(decimal(18,2),isnull(pm.DIscAmt,0))as DIscAmt,

case when dbo.ReportDateBySetting('16')='English' then convert(varchar,convert(datetime, pm.Entrytime),0)
 else (select  [dbo].StringSplit( pm.Entrytime,'/',101)) end as EntrytimeReport,
--convert(date,pm.Entrytime),20)as Entrytime,
pm.type,

--convert(varchar(10),
--convert(date,pm.Billdate),20)as Billdate,
case when dbo.ReportDateBySetting('16')='English' then convert(varchar,convert(date, pm.Billdate),0)
else (select  [dbo].StringSplit( pm.Billdate,'/',101)) end as BilldateReport
from tblpurchasemaster pm
left join tbl_AccountLedger al on al.ledgerId=pm.AccountledgerID
left join tbluser tu on tu.intUserId=pm.UserID
where
al.ledgerid in (select ID from dbo.CSVToTable(@ledgerid)) and
pm.referenceno= @referenceno and
pm.VendorBillNo=(case when @vendorbillno='' then pm.VendorBillNo else @vendorbillno end)  and
pm.Type in (select value from udf_SplitVariable(@purchasetype,','))

ORDER BY PM.PurchaseMasterID DESC
END

ELSE

BEGIN
 select   row_number()  OVER ( order by pm.PurchaseMasterID) as 'Sno',
  (convert(decimal(18,2),isnull(pm.BillAmt,0))-convert(decimal(18,2),isnull(pm.nettotalamt,0)))as CashDisc ,
  isnull( pm.TaxAmt,0) as TaxAmt, pm.Type, pm.mode,
pm.PurchaseMasterID, isnull( pm.ccamt,0) as ccamt ,isnull(pm.billdisamt,0) as billdisamt,
 isnull( pm.AdjustmentAmt,0) as AdjustmentAmt, tu.strName,
pm.referenceno,pm.vendorbillno,
convert(decimal(18,2),isnull(pm.nettotalamt,0))as nettotalamt,
al.ledgername,tu.strUsername,
convert(decimal(18,2),isnull(pm.BillAmt,0))as BillAmt,
convert(decimal(18,2),isnull(pm.DIscAmt,0))as DIscAmt,
case when dbo.ReportDateBySetting('16')='English' then convert(varchar,convert(date, pm.Billdate),0)
 else (select  [dbo].StringSplit( pm.Billdate,'/',101)) end as EntrytimeReport,
--convert(date,pm.Entrytime),20)as Entrytime,
pm.type,
--convert(varchar(10),
--convert(date,pm.Billdate),20)as Billdate,
case when dbo.ReportDateBySetting('16')='English' then convert(varchar,convert(date, pm.Billdate),0)
 else (select  [dbo].StringSplit( pm.Billdate,'/',101)) end as BilldateReport
from tblpurchasemaster pm

left join tbl_AccountLedger al on al.ledgerId=pm.AccountledgerID
left join tbluser tu on tu.intUserId=pm.UserID
where
al.ledgerid in (select ID from dbo.CSVToTable(@ledgerid)) and
pm.referenceno=(case when @referenceno='' then pm.referenceno else @referenceno end) and
pm.VendorBillNo=(case when @vendorbillno='' then pm.VendorBillNo else @vendorbillno end) and
convert(date,pm.billdate) between convert(date,@fromdate) and convert(date,@todate) and
pm.Type in (select value from udf_SplitVariable(@purchasetype,','))
ORDER BY PM.PurchaseMasterID DESC

END
go
Alter proc [dbo].[SpGetAllCounterBillingReports]
@billingType nvarchar(max),
 @Type nvarchar(250),
 @strBillno nvarchar(max),
 @todate datetime =NULL,
 @patientID nvarchar(max),
 @BillMode varchar(25),
 @fromdate datetime =NULL,
 @strUsername nvarchar(500),
 @acid nvarchar(max) ,
 @recvLedger nvarchar(max)

 as

 begin

create table #acid ( id int,ledgerName nvarchar(255))
  insert into #acid (id,ledgerName)
  select ledgerId,ledgerName from tbl_AccountLedger
  where ledgerId in ( select * from dbo.CSVToTable(@acid))

create table #recvLedger ( id int,ledgerName nvarchar(255))
  insert into #recvLedger (id,ledgerName)
  select ledgerId,ledgerName from tbl_AccountLedger
  where ledgerId in ( select * from dbo.CSVToTable(@recvLedger+',0'))

 if (@fromdate  IS null)

 begin
 select   row_number()  OVER ( order by tb.BillingMasterID desc ) as 'Sno',
    tb.BillingMasterID as BillingMasterID,
     case when dbo.ReportDateBySetting('16')='English' then convert(varchar,convert(datetime, tb.EntryTime),0)
    else tb.bsentrydate end as ReportDate,
    tb.strbillno,
    te.strPatientID,
    tp.srtfname,
    tp.strlName,
    tb.Narration as narration,
   (tb.total + tb.DiscountAmt - tb.taxAmt)  as subTotal,
    case when tb.TaxAmt<>0 then(  case when tb.Type in ('Counter Return','Counter Sale')
    then tb.total-tb.TaxAmt else tb.total-tb.TaxAmt end) else 0 end  TaxableAmt,
    isnull( tb.total,0) as TotalAmt,
    isnull((tb.total)-tb.BillDiscount-tb.TaxAmt,0) as total,
    tb.DiscountAmt,
    tb.taxAmt,
    tb.BillDiscount,
    tb.receiveAmt,
    (case when  tb.Type='Deposit' then 'Counter Deposit'
   when tb.Type='Return' then 'Deposit Refund'
   when tb.Type='Due' then 'Counter Clearance' else tb.Type end) as Type,
    ta.ledgerName,
  taa.ledgerName as RecvLedger,
    tb.BillingType,
    tu.strusername from tblBillingMaster tb  with(nolock)
    inner join tblencounter te with(nolock) on tb.strEncounterID=te.strEncounterID
    inner join tblpaitentinfo tp with(nolock) on tp.patientID=te.strPatientID
    inner join #acid ta with(nolock) on ta.id= tb.AcLedgerID
    left join #recvLedger taa with(nolock)  on taa.id= tb.ReceiveLedgerID
    inner join tblUser tu with(nolock) on tu.intUserId=tb.UserID
 WHERE convert(date,tb.EntryTime) = CONVERT(date,GETDATE())
    and tb.Type in ('Counter Sale','Counter Return','Deposit','Return','Due')
   order by  tb.BillingMasterID desc

end

else

begin
   select   row_number()  OVER ( order by tb.BillingMasterID  desc ) as 'Sno',
    tb.BillingMasterID,
  case when dbo.ReportDateBySetting('16')='English' then convert(varchar,convert(datetime, tb.EntryTime),0)
    else tb.bsentrydate end as ReportDate,
    tb.strbillno,
    te.strPatientID,
    tp.srtfname,
    tp.strlName,
     tb.Narration as narration,
   (tb.total + tb.DiscountAmt - tb.taxAmt)  as subTotal,
    case when tb.TaxAmt<>0 then(  case when tb.Type in ('Counter Return','Counter Sale')
    then tb.total-tb.TaxAmt else tb.total-tb.TaxAmt end) else 0 end  TaxableAmt,
    isnull( tb.total,0) as TotalAmt,
    isnull((tb.total)-tb.BillDiscount-tb.TaxAmt,0) as total,
    tb.DiscountAmt,
    tb.taxAmt,
    tb.BillDiscount,
    tb.receiveAmt,
   (case when  tb.Type='Deposit' then 'Counter Deposit'
   when tb.Type='Return' then 'Deposit Refund'
   when tb.Type='Due' then 'Counter Clearance' else tb.Type end) as Type,
    ta.ledgerName,
 taa.ledgerName as RecvLedger,
    tb.BillingType,
    tu.strusername from tblBillingMaster  tb  with(nolock)
    inner join tblencounter te with(nolock) on tb.strEncounterID=te.strEncounterID
    inner join tblpaitentinfo tp with(nolock) on tp.patientID=te.strPatientID
    inner join #acid ta with(nolock) on ta.id= tb.AcLedgerID
    inner join #recvLedger  taa with(nolock) on taa.id= tb.ReceiveLedgerID
    left join tblindexType ti with(nolock) on ti.ID=tb.VourcherTypeID

    inner join tblUser tu on tu.intUserId=tb.UserID

    where

    tb.BillingType in (select value from udf_SplitVariable(@billingType,','))
      and
    tu.strUsername=(case when @strUsername=''  then  tu.strUsername else @strUsername end)
    and
    tp.patientID=(case when @patientID='' then  tp.patientID else @patientID end)
    and
    tb.Type in (select value from udf_SplitVariable(@Type,','))
    and
    tb.strbillno=(case when @strBillno='' then  tb.strbillno else @strBillno end)
    and
    tb.billingmodeid in (select * from dbo.CSVToTable(@BillMode+',0'))
   and
    convert(date,tb.EntryTime)
    between convert(date,@fromdate)
    and convert(date,@todate)
    and tb.Type in ('Counter Sale','Counter Return','Deposit','Return','Due')
        order by tb.BillingMasterID desc

end

 end
 go
 Alter proc [dbo].[spPatientDetailsGetByBillNo]
@BillNo nvarchar(max)
as
begin
DECLARE   @patientid nvarchar(max)
declare @AcLedgerid int
set @patientid=(select TE.strPatientID from tblBillingMaster TBM INNER JOIN tblEncounter TE
ON TBM.strEncounterID=TE.strEncounterID where TBM.strBillno=@BillNo)
set @AcLedgerid=(select acledgerid from tblBillingMaster where strBillno=@BillNo)

SELECT
  case when TP.extra1 = '1' then gp.PatientName else TP.srtfname + ' '+ tp.strlname end as 'Name',
 --TP.srtfname +' '+ tp.strlname as 'Name',
 Convert(varchar,TBM.entryTime,101)  as 'Date',
 TBM.strBillno as 'InvoiceNo',
 tbm.claimcode as 'ClaimID' ,
 te.Policy_Num as 'PolicyNo',
 tbm.BillingMasterID ,
 TBM.billingType as 'Type',

  (case when  TBM.Type='Deposit' then 'Counter Deposit'
   when TBM.Type='Return' then 'Deposite Refund'
   when TBM.Type='Due' then 'Counter Clearance' else TBM.Type end) as 'VoucherType',
 isnull( (select top 1 tbd.MasterBillNo from tblBillDetails tbd  where strbillno=@BillNo),'N/A') 'MasterBillNo',
 (select td.DistrictName+', '+m.Municipality+' - '+tp.strAddress from tblpaitentinfo tp
inner join tblDistrict td on tp.strdistrict=td.ID
inner join tblMunicipality m  on m.mID=tp.[VDC/Municipality]
where tp.patientID=@patientid)as 'Address',
 TP.patientID as 'PatientID',
   convert(decimal(18,2), (TBM.Total-tbm.billdiscount)) as 'NetTotal' ,
 convert(decimal(18,2),(TBM.discountAmt + tbm.billdiscount)) as 'Discount',
 convert(decimal(18,2),TBM.TaxAmt) as 'Tax',
convert(decimal(18,0),TBM.ReceiveAmt) as 'Received',
 convert(decimal(18,2),(TBM.total+TBM.DiscountAmt)-TBM.TaxAmt) as 'BillTotal',
 TBM.Narration as narration,
 TU.strUsername   as 'User',
 (select top 1 tcd.strdoctorname from tblBillDetails tbd
 inner join tblConsaltantdoctr tcd
 on tbd.intrefer_drID=tcd.id
 where strbillno=@BillNo) 'ReferBy',
  ta.ledgerName as 'ACLedger',
 (select dbo.GetpatientAge(@patientid)) +'/'+ TP.strGender   as 'AgeSex',
 convert(decimal(18,2),( select dbo.getPreviousDepositAmt(@BillNo,@patientid,@AcLedgerid))) as 'PreDeposit',
 convert(decimal(18,2),(((select dbo.getPreviousDepositAmt(@BillNo,@patientid,@AcLedgerid)))+((receiveAmt+billdiscount)-total)) )as 'NewDeposit',
  TP.intmobile as 'Conatct',
 case when dbo.ReportDateBySetting('16')='English' then
 CONCAT(convert(varchar,convert(datetime,GETDATE()),111) , ' ' , FORMAT(GETDATE(),'hh:mm tt'))
 else CONCAT((select  [dbo].StringSplit( GETDATE(),'/',101)), ' ' , FORMAT(GETDATE(),'hh:mm tt') )
 end as PrintDate

FROM
 tblBillingMaster TBM
 INNER JOIN tblEncounter TE ON TBM.strEncounterID=TE.strEncounterID
 INNER JOIN tblpaitentinfo TP ON TE.strPatientID=TP.patientID
  left join tblGeneralPatientPharmacySales gp on gp.BillNo=@BillNo  and gp.PatientID=te.strPatientID
 left JOIN tblUser TU ON TBM.UserID=TU.intUserId
 INNER JOIN tbl_AccountLedger ta on ta.ledgerId=TBM.AcLedgerID
 WHERE TBM.strBillno=@BillNo

end
go