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

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


WITH HesapIslem AS (
    SELECT  
		(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) TOPGELIR ,

		HKCARIID,HDSGKURUMID,KURUM.TSSZORUNLU,KURUM.SIGKRM,KURUM.SGKURULUSUKULLAN,KURUM.SGKURULUSU,MDLTAKIPNO,
		CASE WHEN  KURUM.TSSZORUNLU =1  AND HDSGKURUMID IS NOT NULL  THEN  'TSS'
		     WHEN  KURUM.SIGKRM=1 AND KURUM.SGKURULUSUKULLAN =0 AND KURUM.SGKURULUSU = 0  AND KURUM.TSSZORUNLU = 0 AND HDSGKURUMID IS NULL    THEN 'OSS'
			 WHEN  HKCARIID=-1    THEN 'UCRETLI'
			 WHEN  KURUM.SGKURULUSU = 1  AND KURUM.TSSZORUNLU = 0 AND HDSGKURUMID IS NULL AND MDLTAKIPNO IS NOT NULL  THEN 'SGK'
			 ELSE 'DIGER' END TUR
        
    FROM VWMDS_HESAPISL WITH (NOLOCK) 
	LEFT JOIN CARI  AS KURUM          ON  KURUM.CARIID=HKCARIID
	
	LEFT JOIN CARI AS CARI_SG ON  CARI_SG.CARIID=HDSGKURUMID
    WHERE 
        SUBEID = @SubeId AND 
        ITARIH >= @BasTar AND 
        ITARIH <DATEADD(DAY,1,@BitTar) AND
		GELIRDISI = 0 AND IPTAL=0
),
 HesapIslemOncekiAy AS (
    SELECT  
		(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) TOPGELIR ,

		HKCARIID,HDSGKURUMID,KURUM.TSSZORUNLU,KURUM.SIGKRM,KURUM.SGKURULUSUKULLAN,KURUM.SGKURULUSU,MDLTAKIPNO,
		CASE WHEN  KURUM.TSSZORUNLU =1  AND HDSGKURUMID IS NOT NULL  THEN  'TSS'
		     WHEN  KURUM.SIGKRM=1 AND KURUM.SGKURULUSUKULLAN =0 AND KURUM.SGKURULUSU = 0  AND KURUM.TSSZORUNLU = 0 AND HDSGKURUMID IS NULL    THEN 'OSS'
			 WHEN  HKCARIID=-1    THEN 'UCRETLI'
			 WHEN  KURUM.SGKURULUSU = 1  AND KURUM.TSSZORUNLU = 0 AND HDSGKURUMID IS NULL AND MDLTAKIPNO IS NOT NULL  THEN 'SGK'
			 ELSE 'DIGER' END TUR
        
    FROM VWMDS_HESAPISL WITH (NOLOCK) 
	LEFT JOIN CARI  AS KURUM          ON  KURUM.CARIID=HKCARIID
	
	LEFT JOIN CARI AS CARI_SG ON  CARI_SG.CARIID=HDSGKURUMID
    WHERE 
        SUBEID = @SubeId AND 
        ITARIH >= DATEADD(MONTH,-1,@BasTar) AND 
        ITARIH <DATEADD(MONTH,-1,DATEADD(DAY,1,@BitTar)) AND
		GELIRDISI = 0 AND IPTAL=0
),HesapIslemOncekiYil AS (
    SELECT  
		(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) TOPGELIR ,

		HKCARIID,HDSGKURUMID,KURUM.TSSZORUNLU,KURUM.SIGKRM,KURUM.SGKURULUSUKULLAN,KURUM.SGKURULUSU,MDLTAKIPNO,
		CASE WHEN  KURUM.TSSZORUNLU =1  AND HDSGKURUMID IS NOT NULL  THEN  'TSS'
		     WHEN  KURUM.SIGKRM=1 AND KURUM.SGKURULUSUKULLAN =0 AND KURUM.SGKURULUSU = 0  AND KURUM.TSSZORUNLU = 0 AND HDSGKURUMID IS NULL    THEN 'OSS'
			 WHEN  HKCARIID=-1    THEN 'UCRETLI'
			 WHEN  KURUM.SGKURULUSU = 1  AND KURUM.TSSZORUNLU = 0 AND HDSGKURUMID IS NULL AND MDLTAKIPNO IS NOT NULL  THEN 'SGK'
			 ELSE 'DIGER' END TUR
        
    FROM VWMDS_HESAPISL WITH (NOLOCK) 
	LEFT JOIN CARI  AS KURUM          ON  KURUM.CARIID=HKCARIID
	
	LEFT JOIN CARI AS CARI_SG ON  CARI_SG.CARIID=HDSGKURUMID
    WHERE 
        SUBEID = @SubeId AND 
        ITARIH >= DATEADD(YEAR,-1,@BasTar) AND 
        ITARIH <DATEADD(YEAR,-1,DATEADD(DAY,1,@BitTar)) AND
		GELIRDISI = 0 AND IPTAL=0
)




SELECT 
 TSS		,CASE WHEN TOPGELIR<>0 THEN  TSS*100.00/TOPGELIR ELSE 0 END  TSS_ORAN,
 OSS		,CASE WHEN TOPGELIR<>0 THEN  OSS*100.00/TOPGELIR ELSE 0 END  OSS_ORAN,
 SGK		,CASE WHEN TOPGELIR<>0 THEN  SGK*100.00/TOPGELIR ELSE 0 END  SGK_ORAN,
 UCRETLI	,CASE WHEN TOPGELIR<>0 THEN  UCRETLI*100.00/TOPGELIR ELSE 0 END  UCRETLI_ORAN,
 DIGER		,CASE WHEN TOPGELIR<>0 THEN  DIGER*100.00/TOPGELIR ELSE 0 END  DIGERORAN ,
 
 TSS_ONCEKIAY		,CASE WHEN TOPGELIR_ONCEKIAY<>0 THEN  TSS_ONCEKIAY*100.00/TOPGELIR_ONCEKIAY ELSE 0 END  TSS_ORAN_ONCEKIAY,
 OSS_ONCEKIAY		,CASE WHEN TOPGELIR_ONCEKIAY<>0 THEN  OSS_ONCEKIAY*100.00/TOPGELIR_ONCEKIAY ELSE 0 END  OSS_ORAN_ONCEKIAY,
 SGK_ONCEKIAY		,CASE WHEN TOPGELIR_ONCEKIAY<>0 THEN  SGK_ONCEKIAY*100.00/TOPGELIR_ONCEKIAY ELSE 0 END  SGK_ORAN_ONCEKIAY,
 UCRETLI_ONCEKIAY	,CASE WHEN TOPGELIR_ONCEKIAY<>0 THEN  UCRETLI_ONCEKIAY*100.00/TOPGELIR_ONCEKIAY ELSE 0 END  UCRETLI_ORAN_ONCEKIAY,
 DIGER_ONCEKIAY		,CASE WHEN TOPGELIR_ONCEKIAY<>0 THEN  DIGER_ONCEKIAY*100.00/TOPGELIR_ONCEKIAY ELSE 0 END  DIGERORAN_ONCEKIAY ,

 TSS_ONCEKIYIL		,CASE WHEN TOPGELIR_ONCEKIYIL<>0 THEN   TSS_ONCEKIYIL*100.00/TOPGELIR_ONCEKIYIL ELSE 0 END TSS_ORAN_ONCEKIYIL,
 OSS_ONCEKIYIL		,CASE WHEN TOPGELIR_ONCEKIYIL<>0 THEN    OSS_ONCEKIYIL*100.00/TOPGELIR_ONCEKIYIL ELSE 0 END  OSS_ORAN_ONCEKIYIL,
 SGK_ONCEKIYIL		,CASE WHEN TOPGELIR_ONCEKIYIL<>0 THEN   SGK_ONCEKIYIL*100.00/TOPGELIR_ONCEKIYIL ELSE 0 END  SGK_ORAN_ONCEKIYIL,
 UCRETLI_ONCEKIYIL	,CASE WHEN TOPGELIR_ONCEKIYIL<>0 THEN   UCRETLI_ONCEKIYIL*100.00/TOPGELIR_ONCEKIYIL ELSE 0 END  UCRETLI_ORAN_ONCEKIYIL,
 DIGER_ONCEKIYIL	,CASE WHEN TOPGELIR_ONCEKIYIL<>0 THEN   DIGER_ONCEKIYIL*100.00/TOPGELIR_ONCEKIYIL ELSE 0 END  DIGERORAN_ONCEKIYIL  
 
 FROM
(
  
  SELECT SUM(TSS) TSS,SUM(OSS) OSS,SUM(SGK)SGK, SUM(UCRETLI) UCRETLI, SUM(DIGER) DIGER,SUM(TOPGELIR) TOPGELIR,
        SUM(TSS_ONCEKIAY) TSS_ONCEKIAY,SUM(OSS_ONCEKIAY) OSS_ONCEKIAY,SUM(SGK_ONCEKIAY)SGK_ONCEKIAY, 
		SUM(UCRETLI_ONCEKIAY) UCRETLI_ONCEKIAY, SUM(DIGER_ONCEKIAY) DIGER_ONCEKIAY,SUM(TOPGELIR_ONCEKIAY) TOPGELIR_ONCEKIAY,
		SUM(TSS_ONCEKIYIL) TSS_ONCEKIYIL,SUM(OSS_ONCEKIYIL) OSS_ONCEKIYIL,SUM(SGK_ONCEKIYIL)SGK_ONCEKIYIL, 
		SUM(UCRETLI_ONCEKIYIL) UCRETLI_ONCEKIYIL, SUM(DIGER_ONCEKIYIL) DIGER_ONCEKIYIL,SUM(TOPGELIR_ONCEKIYIL) TOPGELIR_ONCEKIYIL
  
  
  FROM
	  (
		SELECT
			SUM(CASE WHEN  TUR='TSS'  THEN TOPGELIR ELSE 0 END) TSS,
			SUM(CASE WHEN  TUR='OSS'  THEN TOPGELIR ELSE 0 END) OSS,
			SUM(CASE WHEN  TUR='SGK'  THEN TOPGELIR ELSE 0 END) SGK,
			SUM(CASE WHEN  TUR='UCRETLI'  THEN TOPGELIR ELSE 0 END) UCRETLI,
			SUM(CASE WHEN  TUR='DIGER'  THEN TOPGELIR ELSE 0 END) DIGER,
			SUM(TOPGELIR) TOPGELIR,
			0 TSS_ONCEKIAY,0 OSS_ONCEKIAY,0 SGK_ONCEKIAY,0 UCRETLI_ONCEKIAY,0 DIGER_ONCEKIAY,0 TOPGELIR_ONCEKIAY,
			0 TSS_ONCEKIYIL,0 OSS_ONCEKIYIL,0 SGK_ONCEKIYIL,0 UCRETLI_ONCEKIYIL,0 DIGER_ONCEKIYIL,0 TOPGELIR_ONCEKIYIL

		FROM HesapIslem
		UNION ALL
		SELECT
			0 TSS,
			0 OSS,
			0 SGK,
			0 UCRETLI,
			0 DIGER,
			0 TOPGELIR,
			SUM(CASE WHEN  TUR='TSS'  THEN TOPGELIR ELSE 0 END) TSS_ONCEKIAY,
			SUM(CASE WHEN  TUR='OSS'  THEN TOPGELIR ELSE 0 END) OSS_ONCEKIAY,
			SUM(CASE WHEN  TUR='SGK'  THEN TOPGELIR ELSE 0 END) SGK_ONCEKIAY,
			SUM(CASE WHEN  TUR='UCRETLI'  THEN TOPGELIR ELSE 0 END) UCRETLI_ONCEKIAY,
			SUM(CASE WHEN  TUR='DIGER'  THEN TOPGELIR ELSE 0 END) DIGER_ONCEKIAY,
			SUM(TOPGELIR) TOPGELIR_ONCEKIAY,
			0 TSS_ONCEKIYIL,0 OSS_ONCEKIYIL,0 SGK_ONCEKIYIL,0 UCRETLI_ONCEKIYIL,0 DIGER_ONCEKIYIL,0 TOPGELIR_ONCEKIYIL

	 	FROM HesapIslemOncekiAy
		UNION ALL
		SELECT
			0 TSS,
			0 OSS,
			0 SGK,
			0 UCRETLI,
			0 DIGER,
			0 TOPGELIR,
			0 TSS_ONCEKIAY,0 OSS_ONCEKIAY,0 SGK_ONCEKIAY,0 UCRETLI_ONCEKIAY,0 DIGER_ONCEKIAY,0 TOPGELIR_ONCEKIAY,
			SUM(CASE WHEN  TUR='TSS'  THEN TOPGELIR ELSE 0 END) TSS_ONCEKIYIL,
			SUM(CASE WHEN  TUR='OSS'  THEN TOPGELIR ELSE 0 END) OSS_ONCEKIYIL,
			SUM(CASE WHEN  TUR='SGK'  THEN TOPGELIR ELSE 0 END) SGK_ONCEKIYIL,
			SUM(CASE WHEN  TUR='UCRETLI'  THEN TOPGELIR ELSE 0 END) UCRETLI_ONCEKIYIL,
			SUM(CASE WHEN  TUR='DIGER'  THEN TOPGELIR ELSE 0 END) DIGER_ONCEKIYIL,
			SUM(TOPGELIR) TOPGELIR_ONCEKIYIL			

	 	FROM HesapIslemOncekiYil

	) HESAPISLEMLER

) TBL



)
