IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DashBoard_Gunluk_Analiz]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[DashBoard_Gunluk_Analiz]
GO

CREATE FUNCTION [dbo].[DashBoard_Gunluk_Analiz]
(
    @BasTar datetime,
    @BitTar datetime,
    @SubeId int
)
RETURNS TABLE
AS
RETURN
(

WITH HesapIslem AS (
    SELECT 
	    dbo.TARIHAL(VWMDS_HESAPISL.ITARIH) TARIH,
        GELIRHSTNETYTLTUTAR,
        GELIRKRMNETYTLTUTAR,
        GELIRSGNETYTLTUTAR,
        VWMDS_HESAPISL.ITEMTURUID,
        HSTODENENINDYUZDE,
        HSTODMKESINTIYUZDE,
        KRMODENENINDYUZDE,
        KRMODMKESINTIYUZDE,
        SGODENENINDYUZDE,
        SGODMKESINTIYUZDE,
        HSTKDVORAN,
        KRMKDVORAN,
        SGKDVORAN,
        ODENENTNETYTLTUTAR,
        GELIROTOOZELISLEMNETTLTUTAR,
		VWMDS_HESAPISL.HKIMLIKID,VWMDS_HESAPISL.ADET, VWMDS_HESAPISL.ITEMID,VWMUAYENEISL.MUAYENETIPIID,TITEM.ITEMKODU2
    FROM VWMDS_HESAPISL WITH (NOLOCK) 
		INNER JOIN TITEM WITH (NOLOCK) ON TITEM.ITEMID=VWMDS_HESAPISL.ITEMID 
		LEFT OUTER JOIN VWMUAYENEISL WITH (NOLOCK) ON VWMUAYENEISL.HESAPISLID=VWMDS_HESAPISL.HESAPISLID
    WHERE 
        VWMDS_HESAPISL.SUBEID = @SubeId AND 
   		VWMDS_HESAPISL.ITARIH >=@BasTar AND 
        VWMDS_HESAPISL.ITARIH < DATEADD(DAY,1,@BitTar) AND
		VWMDS_HESAPISL.GELIRDISI = 0 AND VWMDS_HESAPISL.IPTAL=0
)
SELECT HD.[Hasta Sayısı], TBL.* FROM
(
SELECT 
       
	TARIH,
	CASE
    WHEN DATENAME(WEEKDAY, TARIH) = 'Sunday'    THEN 'Pazar'
    WHEN DATENAME(WEEKDAY, TARIH) = 'Monday'    THEN 'Pazartesi'
    WHEN DATENAME(WEEKDAY, TARIH) = 'Tuesday'   THEN 'Salı'
    WHEN DATENAME(WEEKDAY, TARIH) = 'Wednesday' THEN 'Çarşamba'
    WHEN DATENAME(WEEKDAY, TARIH) = 'Thursday'  THEN 'Perşembe'
    WHEN DATENAME(WEEKDAY, TARIH) = 'Friday'    THEN 'Cuma'
	WHEN DATENAME(WEEKDAY, TARIH) = 'Saturday'  THEN 'Cumartesi'
    ELSE 'Boş'
    END Gun,   
	
	
    ISNULL(SUM(CASE 
        WHEN ITEMTURUID IN (7) 
		 AND ITEMKODU2 NOT IN ('700810', 'P700810','700880', '700890','700900', '700910','700920', '700921','700922', 'P700880', 'P700890','P700900', 'P700910','P700920', 'P700921','P700922',
		                       '701390','701400','701540','701550','701450','701460','P701410') THEN /*GASTRO ANJİO HARİÇ*/
            ISNULL(ADET,0)
        ELSE 0 END), 0) AS [Operasyon Sayısı],

    SUM(CASE WHEN MUAYENETIPIID NOT IN (2,5) THEN ADET ELSE 0 END) AS Muayene_Sayisi,
	SUM(CASE WHEN MUAYENETIPIID IN (2) THEN ADET ELSE 0 END) AS Kontrol_Muayene_Sayisi,
	SUM(CASE WHEN MUAYENETIPIID IN (5) THEN ADET ELSE 0 END) AS Acil_Muayene_Sayisi,

    ISNULL(SUM(ODENENTNETYTLTUTAR / (1 + HSTKDVORAN / 100)), 0) AS [Toplam Hasta Ödenen],

    ISNULL(SUM((GELIRHSTNETYTLTUTAR * (1 - HSTODENENINDYUZDE / 100.0) * (1 - HSTODMKESINTIYUZDE / 100.0)) / (1 + HSTKDVORAN / 100) 
        + (GELIRKRMNETYTLTUTAR * (1 - KRMODENENINDYUZDE / 100.0) * (1 - KRMODMKESINTIYUZDE / 100.0)) / (1 + KRMKDVORAN / 100) 
        + (GELIRSGNETYTLTUTAR * (1 - SGODENENINDYUZDE / 100.0) * (1 - SGODMKESINTIYUZDE / 100.0)) / (1 + SGKDVORAN / 100)), 0) AS [Toplam Gelir]

FROM HesapIslem 
GROUP BY TARIH
) AS TBL INNER JOIN (SELECT COUNT (DISTINCT HKIMLIKID) [Hasta Sayısı],DBO.TARIHAL(HDTARIH) HDTARIH FROM HDOSYA WHERE HDTEKLIF=0 AND HDIPTAL=0 AND SUBEID = @SubeId and HDTARIH >=@BasTar AND HDTARIH < DATEADD(DAY,1,@BitTar)
GROUP BY DBO.TARIHAL(HDTARIH)) HD ON HD.HDTARIH=TBL.TARIH
--ORDER BY TARIH
)
GO


