USE [RADIOLOGY]
GO
/****** Object:  StoredProcedure [dbo].[sp_Radio_UlraProfessionalFeeReport_WomenCentre]    Script Date: 09/11/2024 3:55:47 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_Radio_UlraProfessionalFeeReport_WomenCentre]--'10/01/2014','10/31/2014','WC'

	@StartDate as varchar(10),
	@EndDate as varchar(10),
	@Revenueid as varchar(5)

AS

WITH sel as (
Select
	--C.LastName + ', ' + C.FirstName +  ' ' + 
	--Case When isnull(C.MiddleName,'') = ''
	--	Then '' Else Left(C.MiddleName,1) + '.'
	--End + ' M.D.' as Doctor,
	dbo.fn_GetRadioDoctorsName2(D.RadCode,@Revenueid,D.ItemID,D.IDNum) [Doctor],
	--dbo.Fn_GetDoctorsName(D.RequestDoctorCode) [Doctor],
	PATIENT_DATA.dbo.fn_GetPatientName(d.HospNum) as PatientName,
	D.RefNum,
	D.ItemID as ExamCode,
	isnull(B.Description,'') as Exam,
	D.Amount,
	Case 
		When D.RevenueID = 'XR' Then 25
		When D.RevenueID = 'US' Then 30
		When D.RevenueID = 'CT' Then 20
		When D.RevenueID = 'MM' Then 0
	Else 
		0
	End As [Percent],		
	--isnull(E.ProfFeePercent,0) as [Percent],
	--D.Amount * (isnull(E.ProfFeePercent,0) / 100) as ProfFee,
	CASE
		WHEN D.Revenueid = 'XR' THEN D.Amount * .25
		WHEN D.Revenueid = 'US' THEN D.Amount * .30
		WHEN D.Revenueid = 'CT' THEN D.Amount * .20
		ELSE D.Amount END [ProfFee],
--	D.Amount * (D.RevenueID / 100) as ProfFee,
	E.SectionName,
	D.IDNum,
	D.SlipNum,
	CASE WHEN LEFT(D.Refnum,2) = 'OR' THEN 'CASH'
		ELSE dbo.fn_GetAccountNum(d.idnum)
	END [Account],
	Hospnum,
	--f.Discount
	Discount=isnull(dbo.fn_IsPatientSeniorOrPwd(D.Idnum,D.Refnum),'')
	 
	--Case when LEFT(D.refnum, 2) = 'OR' and (select top 1 refnum from BILLING..tbBillOPDailyOut where refnum= D.refnum and RevenueID = 'CF' and Amount < 0) = D.refnum
	--	then (select top 1 Amount from BILLING..tbBillOPDailyOut where refnum= D.refnum and RevenueID = 'CF' and Amount < 0)
	----when F.Idnum is null and F.OldIDNum = D.IDNum then
	----	F.Discount
	--else 
	--	F.Discount
	--End Discount

From tbRadiologyRequests D 
	Left Outer Join tbBillExamListing B On D.ItemID = B.ItemID and D.RevenueID = B.RevenueID
	--Left Outer Join Build_File..tbCoDoctor C
	--On D.RadCode = C.DoctorID
	Left Outer Join Build_File..tbCoSection E On B.SectionID = E.SectionID And B.RevenueID = E.RevenueID
	LEFT OUTER JOIN BILLING..tbBillSeniorDiscount F 
    ON D.IDNum =  F.IDNum   and D.RevenueID = F.DepartmentID
	

Where d.ResultDate between @StartDate and @EndDate + ' 23:59:59.99'
	and isnull(D.RevenueID,'') = @Revenueid
	and isnull(d.swfin,'X') not in ('C','R')
	and IsNull(D.VerifyDate, '') <> ''
	and D.Status = 'V'	
	--and b.issupply <> '1'
)
SELECT *,
	CASE 
		WHEN RTRIM(LTRIM(Hospnum)) = RTRIM(LTRIM(Account)) THEN 'PERSONAL'
		WHEN RTRIM(LTRIM(idnum)) = RTRIM(LTRIM(Account)) THEN 'PERSONAL' 
		ELSE Account
		END AS [Account Type]
FROM sel

Order By Doctor, PatientName


--
--Select  C.LastName + ', ' + C.FirstName +  ' ' + 
--	Case When isnull(C.MiddleName,'') = ''
--		Then '' Else Left(C.MiddleName,1) + '.'
--	End + ' M.D.' as Doctor,
--	Cardio.dbo.fn_GetPatientName(A.HospNum) as PatientName,
--	D.RefNum,
--	D.Code as ExamCode,	
----	B.UltraExam as Exam,
--	Case When isnull(B.Description,'') = ''
--		Then isnull(F.Description,'')
--		Else isnull(B.Description,'')
--	End as Exam,
--	D.Amount,
--	isnull(E.ProfFeePercent,0) as [Percent],
--	D.Amount * (isnull(E.ProfFeePercent,0) / 100) as ProfFee,
--	E.SectionName,
--	A.IDNum
--From tbULResult A
--	Left Outer Join tbULRequest D 
--	On A.RequestNum = D.RequestNum
----	Left Outer Join Build_File..tbCoULtraExam B
----	On A.ItemCode = B.UltraExamID
--	Left Outer Join Billing..tbBillExamListing B
--	On D.Code = B.ItemID and isnull(D.RevenueID, 'US') = B.RevenueID
--	Left Outer Join Build_File..tbCoDoctor C
--	On A.RadCode = C.DoctorID
--	Left Outer Join Build_File..tbCoSection E
--	On B.SectionID = E.SectionID And B.RevenueID = E.RevenueID
--	Left Outer Join Billing..tbBillExamListing F
--	On D.Code = F.ItemID and isnull(F.RevenueID, 'US') = 'WC'
--
--Where A.TransDate between @StartDate and @EndDate + ' 23:59:59.99'	
--	and isnull(D.RevenueID,'US') <>'MD'
--	and E.SectionID in ('MMM','SPR','SIP','MI')
--	and C.doctorID ='CLS'
--Order By Doctor, PatientName