USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_Report_ReadersFee]    Script Date: 3/20/2020 10:17:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Lab_Report_ReadersFee]
    @SectionId varchar(30),
	@StartDate	varchar(12),
	@EndDate	varchar(12)
AS
BEGIN
	SET NOCOUNT ON;
if @SectionId= 'Histopath' BEGIN set @SectionId='H'  END;
if @SectionId= 'Microbiology' BEGIN set @SectionId='D'  END;

if @SectionId in ('H','D') --Histopath and Microbiology
		Begin	
				select a.Transdate as [RequestDate], A.Idnum,
				 B.LastName  +', '+ B.FirstName +' ' + left(B.MiddleName,1) + '.' as [PatientName],
				 a.Refnum, isnull(ORNUm,'') as ORNum, a.ItemId, LabExam,
				 '25' as [Percent], 
				  Case when a.RequestStatus = 'R'
				 then  0
				 else d.Amount
				 end as Amount,
				 Case 
					when (select top 1 '1' from billing..tbBillOPDailyOut where RevenueID in ('CF','PW') and Idnum = a.IdNum) = '1'
					then
						d.Amount * .20
					when d.netAmount = a.Amount
					   then 0 
					 when d.NetAmount is null
					   then 0
					 when a.RequestStatus = 'R'
					   then 0 
					 when a.NetAmount = 0
					   then 0 
	
					else
					 (d.Amount - d.netAmount)
					end as DisCount, 
				 case when a.RequestStatus = 'R'
					   then 0 
					 when d.NetAmount = 0
					   then d.Amount
	
					 when d.NetAmount is null
						then d.Amount
					else
					   d.netAmount
					   end as 
					   netAmount,
				 0 as RF,
				  isnull((select Company from BUILD_FILE..tbcocompany v
				  left  join patient_data..tboutpatient p on V.accountnum = p.AccountNum
				  where p. Idnum=a.Idnum),'') as Company,
  					case when  (select top 1 '1' from billing..tbBillOPDailyOut where RevenueID in ('CF','PW') and Idnum = a.IdNum) = '1'
					  then
						'WithDiscount'
					  else
	   
						 'Without'
					  end as [Less]
				 from tblabmaster a
				 left outer join vwPatientResultEntry b on a.HospNum = b.HospNum
				 left outer join Build_file..tbcolabexam c on c.LabexamId = a.Itemid
				 left Outer join billing..tbBillOPDailyOut d on isnull(d.ChargeSlip,d.Refnum) = a.refnum and d.ItemId = a.ItemCharged 
				 --left outer join tboutpatient p2 on buildfile.accountnum = p2.accountnum
				where a.SectionID=@SectionId
				 and c.Labexam not like '%Send%'
				 and a.Transdate between @StartDate and @EndDate + ' 23:59:59.99'
				  and D.revenueId = 'LB'
				 and D.Amount > 0
				 and Isnumeric(D.Idnum) = 0
 

				 UNION ALL

				 select a.Transdate as [RequestDate],A.Idnum,
				 B.LastName  +', '+ B.FirstName +' ' + left(B.MiddleName,1) + '.' as [PatientName],
				 a.Refnum,  isnull(ORNUm,'') as ORNum, a.ItemId, LabExam,
				 '25' as [Percent], 
				 Case when a.RequestStatus = 'R'
				 then  0
				 else d.Amount
				 end as Amount,
				 Case when (select top 1 '1' from billing..tbBillDailybill where RevenueID in ('CF','PW') and Idnum = a.IdNum) = '1'
					   then
						d.Amount * .20
				   when d.netAmount = a.Amount
					   then 0 
					when d.NetAmount is null
					   then 0
					when a.RequestStatus = 'R'
					   then 0 
					when a.NetAmount = 0
					   then 0 
					else
					(d.Amount - d.netAmount)
					end as DisCount,
				 case when a.RequestStatus = 'R'
					   then 0 
					  when d.NetAmount = 0
						then d.Amount
					  when d.NetAmount is null
						then d.Amount
	
					else
					   d.netAmount
					   end as 
					   netAmount,
				 0 as RF,
				  isnull((select Company from BUILD_FILE..tbcocompany v
				  left  join patient_data..tbpatient p on V.accountnum = p.accountnumII
				  where p. Idnum=a.Idnum),'') as Company,
					case when  (select top 1 '1' from billing..tbBillDailybill where RevenueID in ('CF','PW') and Idnum = a.IdNum) = '1'
					  then
						'WithDiscount'
					  else
	   
						 'Without'
					  end as [Less]
				 from tblabmaster a
				 left outer join vwPatientResultEntry b on a.HospNum = b.HospNum
				 left outer join Build_file..tbcolabexam c on c.LabexamId = a.Itemid
				  left Outer join billing..tbBillDailybill d on d.Refnum = a.refnum and d.ItemId = a.ItemCharged 
				where a.SectionID=@SectionId
				 and c.Labexam not like '%Send%'
				 and a.Transdate between @StartDate and @EndDate + ' 23:59:59.99'
				 and D.revenueId = 'LB'
				 and D.Amount > 0
				 and Isnumeric(D.Idnum) = 1
		END
ELSE --Clinical Part
		BEGIN
						select a.Transdate as [RequestDate], A.Idnum,
				 B.LastName  +', '+ B.FirstName +' ' + left(B.MiddleName,1) + '.' as [PatientName],
				 a.Refnum, isnull(ORNUm,'') as ORNum, a.ItemId, LabExam,
				 '25' as [Percent], 
				  Case when a.RequestStatus = 'R'
				 then  0
				 else d.Amount
				 end as Amount,
				 Case 
					when (select top 1 '1' from billing..tbBillOPDailyOut where RevenueID in ('CF','PW') and Idnum = a.IdNum) = '1'
					then
						d.Amount * .20
					when d.netAmount = a.Amount
					   then 0 
					 when d.NetAmount is null
					   then 0
					 when a.RequestStatus = 'R'
					   then 0 
					 when a.NetAmount = 0
					   then 0 
	
					else
					 (d.Amount - d.netAmount)
					end as DisCount, 
				 case when a.RequestStatus = 'R'
					   then 0 
					 when d.NetAmount = 0
					   then d.Amount
	
					 when d.NetAmount is null
						then d.Amount
					else
					   d.netAmount
					   end as 
					   netAmount,
				 0 as RF,
				  isnull((select Company from BUILD_FILE..tbcocompany v
				  left  join patient_data..tboutpatient p on V.accountnum = p.AccountNum
				  where p. Idnum=a.Idnum),'') as Company,
  					case when  (select top 1 '1' from billing..tbBillOPDailyOut where RevenueID in ('CF','PW') and Idnum = a.IdNum) = '1'
					  then
						'WithDiscount'
					  else
	   
						 'Without'
					  end as [Less]
				 from tblabmaster a
				 left outer join vwPatientResultEntry b on a.HospNum = b.HospNum
				 left outer join Build_file..tbcolabexam c on c.LabexamId = a.Itemid
				 left Outer join billing..tbBillOPDailyOut d on isnull(d.ChargeSlip,d.Refnum) = a.refnum and d.ItemId = a.ItemCharged 
				 --left outer join tboutpatient p2 on buildfile.accountnum = p2.accountnum
				where a.SectionID not in ('H','D')
				 and c.Labexam not like '%Send%'
				 and a.Transdate between @StartDate and @EndDate + ' 23:59:59.99'
				  and D.revenueId = 'LB'
				 and D.Amount > 0
				 and Isnumeric(D.Idnum) = 0
 

				 UNION ALL

				 select a.Transdate as [RequestDate],A.Idnum,
				 B.LastName  +', '+ B.FirstName +' ' + left(B.MiddleName,1) + '.' as [PatientName],
				 a.Refnum,  isnull(ORNUm,'') as ORNum, a.ItemId, LabExam,
				 '25' as [Percent], 
				 Case when a.RequestStatus = 'R'
				 then  0
				 else d.Amount
				 end as Amount,
				 Case when (select top 1 '1' from billing..tbBillDailybill where RevenueID in ('CF','PW') and Idnum = a.IdNum) = '1'
					   then
						d.Amount * .20
				   when d.netAmount = a.Amount
					   then 0 
					when d.NetAmount is null
					   then 0
					when a.RequestStatus = 'R'
					   then 0 
					when a.NetAmount = 0
					   then 0 
					else
					(d.Amount - d.netAmount)
					end as DisCount,
				 case when a.RequestStatus = 'R'
					   then 0 
					  when d.NetAmount = 0
						then d.Amount
					  when d.NetAmount is null
						then d.Amount
	
					else
					   d.netAmount
					   end as 
					   netAmount,
				 0 as RF,
				  isnull((select Company from BUILD_FILE..tbcocompany v
				  left  join patient_data..tbpatient p on V.accountnum = p.accountnumII
				  where p. Idnum=a.Idnum),'') as Company,
					case when  (select top 1 '1' from billing..tbBillDailybill where RevenueID in ('CF','PW') and Idnum = a.IdNum) = '1'
					  then
						'WithDiscount'
					  else
	   
						 'Without'
					  end as [Less]
				 from tblabmaster a
				 left outer join vwPatientResultEntry b on a.HospNum = b.HospNum
				 left outer join Build_file..tbcolabexam c on c.LabexamId = a.Itemid
				  left Outer join billing..tbBillDailybill d on d.Refnum = a.refnum and d.ItemId = a.ItemCharged 
				where a.SectionID not in ('H','D')
				 and c.Labexam not like '%Send%'
				 and a.Transdate between @StartDate and @EndDate + ' 23:59:59.99'
				 and D.revenueId = 'LB'
				 and D.Amount > 0
				 and Isnumeric(D.Idnum) = 1

		END
END