Ranish666

changes in sql 2021-05-24

May 24th, 2021
768
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. go
  2. ---====================================================  
  3.  --udpated at: 2021-10-05  
  4.  --update by: Ranish Shrestha  
  5.  --Reason: multiple entry for same item in billing  
  6. ---====================================================  
  7. Alter proc [dbo].[BillingEntry_InsertBillingEntry]        
  8. @strEncounterID nvarchar(max),        
  9. @intBillingModeID int=0,        
  10. @intrefer_corpoID int,        
  11. @intrefer_drID int,        
  12. @intServicegroupID int,        
  13. @intServiceCostID int,        
  14. @dblrate decimal(18,5),        
  15. @dblDiscountPercent decimal(18,5),        
  16. @dblDiscountAmt decimal(18,5),        
  17. @dblTaxPercent decimal(18,5),        
  18. @dbltaxAmt decimal(18,5),        
  19. @dblQty decimal(18,5),        
  20. @dbltotal decimal(18,5),        
  21. @strUser nvarchar(max),        
  22. @PackageID int,        
  23. @status int,        
  24. @reqid int,    
  25. @specialDiscountType nvarchar(50)=null    
  26. AS        
  27. IF exists(select * from tblbillingentry where strEncounterID=@strEncounterID and intServiceCostID=@intServiceCostID)        
  28.  BEGIN        
  29.     --select * from tblbillingentry
  30.     update tblbillingentry
  31.         set dblQty = dblQty + @dblQty,
  32.         dblTaxPercent = dblTaxPercent + @dblTaxPercent,
  33.         dbltaxAmt = dbltaxAmt + @dbltaxAmt,
  34.         dbltotal = dbltotal + @dbltotal
  35.         where strEncounterID=@strEncounterID and intServiceCostID=@intServiceCostID
  36.  END        
  37. ELSE      
  38.  BEGIN        
  39.  IF @dblQty<>0        
  40.   BEGIN        
  41.   insert into tblbillingentry(strEncounterID,intBillingModeID,intrefer_corpoID,intrefer_drID,intServicegroupID,intServiceCostID,dblrate,dblDiscountPercent,dblDiscountAmt,        
  42.   dblTaxPercent,dbltaxAmt,dblQty,dbltotal,strUser,dtmEntrytime,PackageID,[status],reqid,specialDiscountType)        
  43.   values(@strEncounterID,@intBillingModeID,@intrefer_corpoID,@intrefer_drID,@intServicegroupID,@intServiceCostID,@dblrate,@dblDiscountPercent,@dblDiscountAmt,        
  44.   @dblTaxPercent,@dbltaxAmt,@dblQty,@dbltotal,@strUser,getdate(),@PackageID,@status,@reqid,@specialDiscountType)        
  45.   END      
  46. END    
  47. go
  48. Alter proc [dbo].[DepartmentDelete]  
  49. @intDepartmentID int  
  50. as  
  51. begin  
  52.     if  exists (select * from tblUser where intdeptid=@intDepartmentID)  or  exists (select * from tbltarget where TargetName=@intDepartmentID)  
  53.     or  exists (select * from tblTestGroup where intTarget=@intDepartmentID)  or  exists (select * from tblEncounter where srtDepartment=@intDepartmentID)  or
  54.      exists (select * from tblAdmission where current_location=@intDepartmentID)  or  exists (select * from tblDepartmentbed where intDepartmentID=@intDepartmentID)  or
  55.      exists (select * from tblautoBilling where Departmentid=@intDepartmentID)  
  56.         BEGIN  
  57.             select 0;    
  58.         END  
  59.     else  
  60.         BEGIN  
  61.             delete from tbldepartment where intDepartmentID=@intDepartmentID  
  62.             select 1;  
  63.         END  
  64. end    
  65. go
  66. Alter proc DeleteByDoctorId  
  67. @docID int  
  68. as  
  69. begin
  70.     if exists (select * from tblEncounter where intRefer_doctorID = @docID) or
  71.     exists(select * from tblBooking where intRefer_doctorID = @docID)
  72.     begin
  73.         select 0
  74.     end
  75.     else
  76.     begin
  77.         delete from tbldoctor where ID=@docID  
  78.         select 1
  79.     end
  80. end  
  81. go
  82. Alter proc [dbo].[BillingmodeDelete]    
  83. @IntBillingModeId int    
  84. as    
  85. begin    
  86. if  exists(select *from tblservicecost where intBillingModeId=@IntBillingModeId)    
  87. or  exists(select *from tblTestGroup where intBillingModeId=@IntBillingModeId)    
  88. or  exists(select *from tblPharmacySalesMaster where billingmodeid=@IntBillingModeId)  
  89. or  exists(select *from tblBillingMaster where billingmodeid=@IntBillingModeId)  
  90. or  exists(select *from tblEncounter where intBillingModeID=@IntBillingModeId)  
  91. or  exists(select *from tblautoBilling where BillingmodeID=@IntBillingModeId)  
  92.  begin  
  93.     select 0;
  94.  end    
  95. else    
  96.  begin  
  97.      delete from tblbillingmode where IntBillingModeId=@IntBillingModeId    
  98.      select 1;    
  99.  end    
  100. end    
  101. go
  102. Alter proc [dbo].[DiscountCategory_DeleteById]    
  103. @id int      
  104. as      
  105. begin  
  106.     if exists (select * from tblBillingMaster where DiscountCategory = @id)
  107.     begin
  108.         select 0;
  109.     end
  110.     else
  111.     begin
  112.         delete  from tblDiscountCategory  where ID  = @id;
  113.         select 1;
  114.     end
  115.    
  116. end  
  117. go
  118. Alter proc [dbo].[spPatientDetailsGetByBillNo]              
  119. @BillNo nvarchar(max)                  
  120. as                  
  121. begin                  
  122. DECLARE   @patientid nvarchar(max)                  
  123. declare @AcLedgerid int                  
  124. set @patientid=(select TE.strPatientID from tblBillingMaster TBM INNER JOIN tblEncounter TE                  
  125. ON TBM.strEncounterID=TE.strEncounterID where TBM.strBillno=@BillNo)                  
  126. set @AcLedgerid=(select acledgerid from tblBillingMaster where strBillno=@BillNo)                  
  127.                  
  128. SELECT              
  129.   case when TP.extra1 = '1' then gp.PatientName else TP.srtfname + ' '+ tp.strlname end as 'Name',    
  130.  --TP.srtfname +' '+ tp.strlname as 'Name',                  
  131.  Convert(varchar,TBM.entryTime,101)  as 'Date',                  
  132.  TBM.strBillno as 'InvoiceNo',                
  133.  tbm.claimcode as 'ClaimID' ,                
  134.  te.Policy_Num as 'PolicyNo',                
  135.  tbm.BillingMasterID ,        
  136.  TBM.billingType as 'Type',          
  137.            
  138.   (case when  TBM.Type='Deposit' then 'Counter Deposit'            
  139.    when TBM.Type='Return' then 'Counter Refund'            
  140.    when TBM.Type='Due' then 'Counter Clearance' else TBM.Type end) as 'VoucherType',                
  141.  isnull( (select top 1 tbd.MasterBillNo from tblBillDetails tbd  where strbillno=@BillNo),'N/A') 'MasterBillNo',                
  142.  (select td.DistrictName+', '+m.Municipality+' - '+tp.strAddress from tblpaitentinfo tp                    
  143. inner join tblDistrict td on tp.strdistrict=td.ID                  
  144. inner join tblMunicipality m  on m.mID=tp.[VDC/Municipality]                  
  145. where tp.patientID=@patientid)as 'Address',                  
  146.  TP.patientID as 'PatientID',                  
  147.    convert(decimal(18,2), (TBM.Total-tbm.billdiscount)) as 'NetTotal' ,                  
  148.  convert(decimal(18,2),(TBM.discountAmt + tbm.billdiscount)) as 'Discount',                  
  149.  convert(decimal(18,2),TBM.TaxAmt) as 'Tax',                  
  150. convert(decimal(18,0),TBM.ReceiveAmt) as 'Received',                  
  151.  convert(decimal(18,2),(TBM.total+TBM.DiscountAmt)-TBM.TaxAmt) as 'BillTotal',                  
  152.  TBM.Narration as narration,                  
  153.  TU.strUsername   as 'User',                  
  154.  (select top 1 tcd.strdoctorname from tblBillDetails tbd                  
  155.  inner join tblConsaltantdoctr tcd                  
  156.  on tbd.intrefer_drID=tcd.id                  
  157.  where strbillno=@BillNo) 'ReferBy',                  
  158.   ta.ledgerName as 'ACLedger',                  
  159.  (select dbo.GetpatientAge(@patientid)) +'/'+ TP.strGender   as 'AgeSex',                  
  160.  convert(decimal(18,2),( select dbo.getPreviousDepositAmt(@BillNo,@patientid,@AcLedgerid))) as 'PreDeposit',                  
  161.  convert(decimal(18,2),(((select dbo.getPreviousDepositAmt(@BillNo,@patientid,@AcLedgerid)))+((receiveAmt+billdiscount)-total)) )as 'NewDeposit',                  
  162.   TP.intmobile as 'Conatct',              
  163.  case when dbo.ReportDateBySetting('16')='English' then
  164.     CONCAT(convert(varchar,convert(datetime,GETDATE()),111) , ' ' , FORMAT(GETDATE(),'hh:mm tt'))              
  165.     else CONCAT((select  [dbo].StringSplit( GETDATE(),'/',101)), ' ' , FORMAT(GETDATE(),'hh:mm tt') )
  166.     end as PrintDate              
  167.                      
  168. FROM                  
  169.  tblBillingMaster TBM                  
  170.  INNER JOIN tblEncounter TE ON TBM.strEncounterID=TE.strEncounterID                  
  171.  INNER JOIN tblpaitentinfo TP ON TE.strPatientID=TP.patientID      
  172.   left join tblGeneralPatientPharmacySales gp on gp.BillNo=@BillNo  and gp.PatientID=te.strPatientID    
  173.  left JOIN tblUser TU ON TBM.UserID=TU.intUserId                  
  174.  INNER JOIN tbl_AccountLedger ta on ta.ledgerId=TBM.AcLedgerID                  
  175.  WHERE TBM.strBillno=@BillNo                  
  176.                  
  177. end          
  178. go
  179.  
  180. ALTER function [dbo].[getPreviousDepositAmt] (@strbillno nvarchar(max),@strPaitentID nvarchar(max),@AcLedgerID int)
  181. returns varchar(max)
  182. as
  183. begin
  184. Declare @billid int = (select BillingMasterID from  tblBillingMaster where strBillno=@strbillno)
  185. DECLARE @PreviousDeposit decimal
  186. if (select AcLedgerID from tblBillingMaster where strbillno=@strbillno)=22
  187.     begin
  188.         select @PreviousDeposit =(
  189.         --select isnull(sum(ReceiveAmt),0) from tblBillingMaster tm  
  190.         select isnull(((sum(receiveAmt)+sum(billdiscount))-SUM(total)),0) as previousAmt from tblBillingMaster tm  
  191.         inner join tblEncounter te on tm.strencounterid=te.strEncounterID
  192.         where te.strPatientID=@strPaitentID and tm.strBillno<>@strbillno and tm.BillingMasterID<=@billid
  193.         --and tm.type in ('Deposit','Return')
  194.         )
  195.     end
  196. else
  197.     begin
  198.         select @PreviousDeposit=(
  199.         --select isnull(sum(ReceiveAmt),0) from tblBillingMaster tm  
  200.         select isnull(((sum(receiveAmt)+sum(billdiscount))-SUM(total)),0) as previousAmt from tblBillingMaster   tm
  201.         where tm.AcLedgerID = @AcLedgerID and tm.strBillno<> @strbillno and tm.BillingMasterID <= @billid
  202.         --and tm.type in ('Deposit','Return')
  203.         )
  204.     end
  205. return @PreviousDeposit
  206. end
  207. go
  208.  
RAW Paste Data