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

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

WITH HesapIslem AS (
    SELECT 
        MLYTTUTAR,
        GELIRHSTNETYTLTUTAR,
        GELIRKRMNETYTLTUTAR,
        GELIRSGNETYTLTUTAR,
        GELIRDISI,
        HDYATIYOR,
        ITEMTURUID,
        ITARIH,
        HSTODENENINDYUZDE,
        HSTODMKESINTIYUZDE,
        KRMODENENINDYUZDE,
        KRMODMKESINTIYUZDE,
        SGODENENINDYUZDE,
        SGODMKESINTIYUZDE,
        HSTKDVORAN,
        KRMKDVORAN,
        SGKDVORAN,
        ODENENTNETYTLTUTAR,
        GELIROTOOZELISLEMNETTLTUTAR
    FROM VWMDS_HESAPISL WITH (NOLOCK)
    WHERE 
        SUBEID = @SubeId AND 
        ITARIH >= @BasTar AND 
        ITARIH <DATEADD(DAY,1,@BitTar) AND
		GELIRDISI = 0 AND IPTAL=0
)
SELECT 
    1 AS SIRA,
    'Genel Durum' AS [Aciklama],
    
     ISNULL(SUM(CASE 
        WHEN ITEMTURUID IN (3,5) THEN 
            ISNULL(MLYTTUTAR,0)
        ELSE 0 END), 0) AS [İlaç/Malzeme Maliyet],


    ISNULL(SUM(CASE 
        WHEN HDYATIYOR = 0 THEN 
            (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)
        ELSE 0 END), 0) AS [Ayaktan Tedavi Geliri],

    ISNULL(SUM(CASE 
        WHEN HDYATIYOR = 1 THEN 
            (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)
        ELSE 0 END), 0) AS [Yatarak Tedavi Geliri],

    ISNULL(SUM(GELIRKRMNETYTLTUTAR * (1 - KRMODENENINDYUZDE / 100) / (1 + KRMKDVORAN / 100)), 0) AS [Kurum Geliri],

    ISNULL(SUM(GELIRSGNETYTLTUTAR * (1 - SGODENENINDYUZDE / 100) / (1 + SGKDVORAN / 100)), 0) AS [SG Geliri],

    ISNULL(SUM(GELIRHSTNETYTLTUTAR * (1 - HSTODENENINDYUZDE / 100) / (1 + HSTKDVORAN / 100)), 0) AS [Hasta Geliri],

    ISNULL(SUM(GELIROTOOZELISLEMNETTLTUTAR / (1 + HSTKDVORAN / 100)), 0) AS [Ozel Islem Geliri],

    ISNULL(SUM(ODENENTNETYTLTUTAR / (1 + HSTKDVORAN / 100)), 0) AS [Toplam Ö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

)
