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

CREATE FUNCTION [dbo].[DashBoard_SGK_Faturalama_Analiz]
(
    @SNLDonem nvarchar(6),
    @SubeId   int
)
RETURNS TABLE
AS
RETURN
(
SELECT 
    --FATURA HEDEFİ
    SUM(CASE WHEN HDTARIH >= DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1) AND HDTARIH < DateAdd(d, 1, DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1)))) THEN KRMNETTUTAR ELSE 0 END) AS [Donem_Fatura_Hedefi],
	
	--DÖNEM FATURALANAN
    SUM(CASE WHEN SNLFATDONEM = @SNLDonem AND HDTARIH >= DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1) AND HDTARIH < DateAdd(d, 1, DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1)))) THEN MDLKRMTUTAR ELSE 0 END) AS [Donem_Faturalanan],

	--DÖNEM DIŞI FATURALANAN
    SUM(CASE WHEN SNLFATDONEM = @SNLDonem AND HDTARIH < DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1) THEN MDLKRMTUTAR ELSE 0 END) AS [Donem_Disi_Faturalanan],

	--TOPLAM FATURALANAN
    SUM(CASE WHEN SNLFATDONEM = @SNLDonem THEN MDLKRMTUTAR ELSE 0 END) AS [Toplam_Faturalanan],

	--DÖNEM KESİLMEYEN FATURA
    SUM(CASE WHEN HDTARIH >= DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1) AND HDTARIH < DateAdd(d, 1, DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1)))) AND SNLFATDONEM IS NULL THEN KRMNETTUTAR ELSE 0 END) AS [Donem_Kesilmeyen_Fatura],

	--GEÇMİŞE DÖNÜK KESİLMEYEN FATURA
    --SUM(CASE WHEN (HDTARIH <DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1) OR HDTARIH > DateAdd(d, 1, DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1))))) AND SNLFATDONEM IS NULL THEN KRMNETTUTAR ELSE 0 END) AS [Gecmise_Donem_Kesilmeyen_Fatura],

	--120 GÜN GEÇMİŞE DÖNÜK KESİLMEYEN FATURA
    SUM(CASE WHEN (HDTARIH >= DATEADD(DAY,-120,(DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1)))) AND HDTARIH < DATEFROMPARTS(SUBSTRING(@SNLDonem, 1, 4), SUBSTRING(@SNLDonem, 5, 2), 1) AND SNLFATDONEM IS NULL THEN KRMNETTUTAR ELSE 0 END) AS [Gecmise_Donem_Kesilmeyen_Fatura]

FROM 
    VWMEDHDTOPLUISLEM WITH (NOLOCK) WHERE SUBEID = @SubeId 
)
