changes in sql 2021-05-24

go
---====================================================
 --udpated at: 2021-10-05
 --update by: Ranish Shrestha
 --Reason: multiple entry for same item in billing
---====================================================
Alter proc [dbo].[BillingEntry_InsertBillingEntry]
@strEncounterID nvarchar(max),
@intBillingModeID int=0,
@intrefer_corpoID int,
@intrefer_drID int,
@intServicegroupID int,
@intServiceCostID int,
@dblrate decimal(18,5),
@dblDiscountPercent decimal(18,5),
@dblDiscountAmt decimal(18,5),
@dblTaxPercent decimal(18,5),
@dbltaxAmt decimal(18,5),
@dblQty decimal(18,5),
@dbltotal decimal(18,5),
@strUser nvarchar(max),
@PackageID int,
@status int,
@reqid int,
@specialDiscountType nvarchar(50)=null
AS
IF exists(select * from tblbillingentry where strEncounterID=@strEncounterID and intServiceCostID=@intServiceCostID)
 BEGIN
    --select * from tblbillingentry
    update tblbillingentry
        set dblQty = dblQty + @dblQty,
        dblTaxPercent = dblTaxPercent + @dblTaxPercent,
        dbltaxAmt = dbltaxAmt + @dbltaxAmt,
        dbltotal = dbltotal + @dbltotal
        where strEncounterID=@strEncounterID and intServiceCostID=@intServiceCostID
 END
ELSE
 BEGIN
 IF @dblQty<>0
  BEGIN
  insert into tblbillingentry(strEncounterID,intBillingModeID,intrefer_corpoID,intrefer_drID,intServicegroupID,intServiceCostID,dblrate,dblDiscountPercent,dblDiscountAmt,
  dblTaxPercent,dbltaxAmt,dblQty,dbltotal,strUser,dtmEntrytime,PackageID,[status],reqid,specialDiscountType)
  values(@strEncounterID,@intBillingModeID,@intrefer_corpoID,@intrefer_drID,@intServicegroupID,@intServiceCostID,@dblrate,@dblDiscountPercent,@dblDiscountAmt,
  @dblTaxPercent,@dbltaxAmt,@dblQty,@dbltotal,@strUser,getdate(),@PackageID,@status,@reqid,@specialDiscountType)
  END
END
go
Alter proc [dbo].[DepartmentDelete]
@intDepartmentID int
as
begin
    if  exists (select * from tblUser where intdeptid=@intDepartmentID)  or  exists (select * from tbltarget where TargetName=@intDepartmentID)
    or  exists (select * from tblTestGroup where intTarget=@intDepartmentID)  or  exists (select * from tblEncounter where srtDepartment=@intDepartmentID)  or
     exists (select * from tblAdmission where current_location=@intDepartmentID)  or  exists (select * from tblDepartmentbed where intDepartmentID=@intDepartmentID)  or
     exists (select * from tblautoBilling where Departmentid=@intDepartmentID)
        BEGIN
            select 0;
        END
    else
        BEGIN
            delete from tbldepartment where intDepartmentID=@intDepartmentID
            select 1;
        END
end
go
Alter proc DeleteByDoctorId
@docID int
as
begin
    if exists (select * from tblEncounter where intRefer_doctorID = @docID) or
    exists(select * from tblBooking where intRefer_doctorID = @docID)
    begin
        select 0
    end
    else
    begin
        delete from tbldoctor where ID=@docID
        select 1
    end
end
go
Alter proc [dbo].[BillingmodeDelete]
@IntBillingModeId int
as
begin
if  exists(select *from tblservicecost where intBillingModeId=@IntBillingModeId)
or  exists(select *from tblTestGroup where intBillingModeId=@IntBillingModeId)
or  exists(select *from tblPharmacySalesMaster where billingmodeid=@IntBillingModeId)
or  exists(select *from tblBillingMaster where billingmodeid=@IntBillingModeId)
or  exists(select *from tblEncounter where intBillingModeID=@IntBillingModeId)
or  exists(select *from tblautoBilling where BillingmodeID=@IntBillingModeId)
 begin
    select 0;
 end
else
 begin
     delete from tblbillingmode where IntBillingModeId=@IntBillingModeId
     select 1;
 end
end
go
Alter proc [dbo].[DiscountCategory_DeleteById]
@id int
as
begin
    if exists (select * from tblBillingMaster where DiscountCategory = @id)
    begin
        select 0;
    end
    else
    begin
        delete  from tblDiscountCategory  where ID  = @id;
        select 1;
    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 'Counter 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

ALTER function [dbo].[getPreviousDepositAmt] (@strbillno nvarchar(max),@strPaitentID nvarchar(max),@AcLedgerID int)
returns varchar(max)
as
begin
Declare @billid int = (select BillingMasterID from  tblBillingMaster where strBillno=@strbillno)
DECLARE @PreviousDeposit decimal
if (select AcLedgerID from tblBillingMaster where strbillno=@strbillno)=22
    begin
        select @PreviousDeposit =(
        --select isnull(sum(ReceiveAmt),0) from tblBillingMaster tm
        select isnull(((sum(receiveAmt)+sum(billdiscount))-SUM(total)),0) as previousAmt from tblBillingMaster tm
        inner join tblEncounter te on tm.strencounterid=te.strEncounterID
        where te.strPatientID=@strPaitentID and tm.strBillno<>@strbillno and tm.BillingMasterID<=@billid
        --and tm.type in ('Deposit','Return')
        )
    end
else
    begin
        select @PreviousDeposit=(
        --select isnull(sum(ReceiveAmt),0) from tblBillingMaster tm
        select isnull(((sum(receiveAmt)+sum(billdiscount))-SUM(total)),0) as previousAmt from tblBillingMaster   tm
        where tm.AcLedgerID = @AcLedgerID and tm.strBillno<> @strbillno and tm.BillingMasterID <= @billid
        --and tm.type in ('Deposit','Return')
        )
    end
return @PreviousDeposit
end
go