USE [Radiology]
GO
/****** Object:  StoredProcedure [dbo].[ReferralSummaryReport]    Script Date: 9/19/2023 2:31:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[ReferralSummaryReport]
    @StartDate as varchar(12),
    @EndDate as varchar(12)
AS
BEGIN
    DECLARE @Revenueid varchar(10)
    DECLARE @Counter int
    SET @Counter = 1

    -- Create a temporary table to store results
    CREATE TABLE #TempResults (
        DoctorName varchar(max),
        CT_ReferralFee decimal(18, 2),
        HS_ReferralFee decimal(18, 2),
		US_ReferralFee decimal(18, 2),
		PG_ReferralFee decimal(18, 2),
        VU_ReferralFee decimal(18, 2)
    )

    -- Loop through each revenue ID
    WHILE @Counter <= 5  -- 5 is the number of revenue IDs 
    BEGIN
        -- Set @Revenueid based on the current loop iteration
        IF @Counter = 1
            SET @Revenueid = 'CT'
        ELSE IF @Counter = 2
            SET @Revenueid = 'HS'
		ELSE IF @Counter = 3
            SET @Revenueid = 'US'
		ELSE IF @Counter = 4
            SET @Revenueid = 'PG'
        ELSE
            SET @Revenueid = 'VU'
        
        ;With RadBill as(

	SELECT DISTINCT a.TransDate,a.IDNum,b.Hospnum,
		a.REFNUM,
		CASE WHEN ISNUMERIC(a.IDNUM) = 1 THEN a.amount ELSE '' END [InPatient],
		CASE WHEN ISNUMERIC(a.IDNUM) = 0 THEN a.amount ELSE '' END [OPD]
		,Cardio.dbo.fn_GetCompany(B.AccountNum) Company,Amount,a.Itemid,''[PaymentFrom]
		,Case When A.RequestDocID = '0' 
				Then A.DoctorName
				Else Cardio.dbo.fn_GetDoctorsName(A.RequestDocID)
			End  as DoctorName

	FROM BILLING..tbBillDailyBill a WITH (NOLOCK)
	LEFT OUTER JOIN PATIENT_DATA..tbpatient b on a.IdNum = b.IdNum
	left join Billing..tbBillExamListing c on a.RevenueID = c.RevenueID
	WHERE TransDate between @StartDate and @EndDate + ' 23:59:59.99' AND a.RevenueID = @Revenueid and NOT(a.RequestDocID = '0' and a.IDNum  <> 'CASH')
	
------------------------Condition added by Jecs 5/17/2023------------ exclusion of cancelled results
AND a.REFNUM NOT IN (
       select Px.REFNUM from BILLING..vwBillRevokedIP Px where a.RefNum = Px.RefNum and a.ItemID = Px.ItemID and Px.RevenueID = @Revenueid
    )

	UNION ALL

		SELECT DISTINCT 
        a.TransDate,
        CASE 
            WHEN a.IDNum = 'CASH' THEN
                d.Idnum
            ELSE
                a.idnum
        END [IdNum],
        a.Hospnum,
        a.REFNUM,
        CASE WHEN ISNUMERIC(a.IDNUM) = 1 THEN a.amount ELSE '' END [InPatient],
        CASE WHEN ISNUMERIC(a.IDNUM) = 0 THEN a.amount ELSE '' END [OPD],
        Cardio.dbo.fn_GetCompany(c.AccountNum) Company,
        a.Amount,
        a.Itemid,
        PaymentFrom,
        Case When A.RequestDocID = '0' 
            Then A.DoctorName
            Else Cardio.dbo.fn_GetDoctorsName(ISNULL(a.RequestDocID,D.RequestDocId))
        End  as DoctorName

	FROM BILLING..tbBillOPDailyOut a WITH(NOLOCK)
	Left Outer Join Billing..tbCashORMaster b on a.RefNum = b.RefNum --and a.RevenueID = b.Revenueid
	LEFT OUTER JOIN PATIENT_DATA..tbOutPatient c on a.IDNum = c.IDNum 
	LEFT OUTER JOIN Billing..tbcashassessment d on a.refnum = d.ornumber AND d.REvenueid = a.Revenueid --@Revenueid
	left join Billing..tbBillExamListing e on a.RevenueID =e.RevenueID and a.ItemID = e.ItemID
	WHERE a.TransDate between @StartDate and @EndDate + ' 23:59:59.99' AND a.RevenueID = @Revenueid and NOT(a.RequestDocID = '0' and a.IDNum  <> 'CASH')
	
------------------------Condition added by Jecs 5/17/2023------------ exclusion of cancelled results
	AND a.REFNUM NOT IN (
		select Px.REFNUM from BILLING..vwBillRevokedOPD Px where a.RefNum = Px.RefNum and a.ItemID = Px.ItemID and Px.RevenueID = @Revenueid
	)
	

),WithExams as(
	Select a.*,Description, B.SectionID
	FROM RadBill a
	LEFT OUTER JOIN Billing..tbbillexamlisting b on a.ItemID = b.ItemID and b.Revenueid = @Revenueid 


),WithPatientNames as(
	SELECT a.*,RADIOLOGY.dbo.fn_ComputeAge(pat.BirthDate,A.transdate)[Age]
			  ,
				ISNULL(Patient_Data.dbo.funcGetFullName(a.Idnum) ,a.PaymentFrom) [PatientName]
	FROM WithExams a
	Left Outer Join PATIENT_DATA..tbmaster pat on pat.HospNum = a.HospNum
)

	INSERT INTO #TempResults (DoctorName, CT_ReferralFee, HS_ReferralFee, US_ReferralFee, PG_ReferralFee, VU_ReferralFee)
        SELECT
            DoctorName,
            CASE WHEN @Revenueid = 'CT' THEN Total ELSE 0 END as HS_ReferralFee,
            CASE WHEN @Revenueid = 'HS' THEN Total ELSE 0 END as CT_ReferralFee,
			CASE WHEN @Revenueid = 'US' THEN Total ELSE 0 END as CT_ReferralFee,
			CASE WHEN @Revenueid = 'PG' THEN Total ELSE 0 END as CT_ReferralFee,
            CASE WHEN @Revenueid = 'VU' THEN Total ELSE 0 END as US_ReferralFee
        FROM (
            SELECT 
                LTRIM(RTRIM(ISNULL(DoctorName,'')))[DoctorName],
                COUNT(Transdate)[NO. OF REFERRALS],
                CASE
                    WHEN @Revenueid = 'CT' THEN COUNT(Transdate) * 200
                    ELSE COUNT(Transdate) * 100
                END [Total],
                @Revenueid as RevenueId
            FROM WithPatientNames a
            LEFT OUTER JOIN Build_File..tbcoHsExam b on a.ItemID = b.hsExamID
            WHERE A.SectionID NOT IN ('1','MIC','0')
            GROUP BY ISNULL(Hospnum,''), IdNum, ItemID, RefNum, DoctorName
            HAVING SUM(Amount) > 0
    ) Rad_NoRefferal
        WHERE DoctorName not like '%DOCTOR%' 

        -- Increment the counter for the next iteration
        SET @Counter = @Counter + 1
    END

    -- Return the combined results
    SELECT 
        DoctorName, 
        SUM(CT_ReferralFee) as CT_ReferralFee, 
        SUM(HS_ReferralFee) as HS_ReferralFee, 
		SUM(US_ReferralFee) as US_ReferralFee, 
		SUM(PG_ReferralFee) as PG_ReferralFee, 
        SUM(VU_ReferralFee) as VU_ReferralFee
    FROM #TempResults
    GROUP BY DoctorName

    -- Drop the temporary table
    DROP TABLE #TempResults
END