CREATE VIEW dbo.SATIS_RAPOR_AYLIK_SEHIR_TOPLAMLARI
AS
-- Birinci kisim: CLF ve CLC tablosunun birlestirilmesi, PLAS sütununun eklenmesi
WITH MonthlyData AS (
    SELECT
        CLC.CITY,
        CLC.SPECODE2 AS PLAS,
        CASE
            WHEN DATEPART(MONTH, CLF.DATE_) = 1 THEN 'OCAK'
            WHEN DATEPART(MONTH, CLF.DATE_) = 2 THEN 'SUBAT'
            WHEN DATEPART(MONTH, CLF.DATE_) = 3 THEN 'MART'
            WHEN DATEPART(MONTH, CLF.DATE_) = 4 THEN 'NISAN'
            WHEN DATEPART(MONTH, CLF.DATE_) = 5 THEN 'MAYIS'
            WHEN DATEPART(MONTH, CLF.DATE_) = 6 THEN 'HAZIRAN'
            WHEN DATEPART(MONTH, CLF.DATE_) = 7 THEN 'TEMMUZ'
            WHEN DATEPART(MONTH, CLF.DATE_) = 8 THEN 'AGUSTOS'
            WHEN DATEPART(MONTH, CLF.DATE_) = 9 THEN 'EYLÜL'
            WHEN DATEPART(MONTH, CLF.DATE_) = 10 THEN 'EKIM'
            WHEN DATEPART(MONTH, CLF.DATE_) = 11 THEN 'KASIM'
            WHEN DATEPART(MONTH, CLF.DATE_) = 12 THEN 'ARALIK'
        END AS MONTH,
        SUM(CASE WHEN CLF.TRCODE = 38 THEN CLF.TRNET ELSE 0 END) AS SATIS,
        SUM(CASE WHEN CLF.TRCODE IN (70, 1, 61, 62, 20) THEN CLF.TRNET ELSE 0 END) AS TAHSILAT,
        SUM(CASE WHEN CLF.TRCODE = 33 THEN CLF.TRNET ELSE 0 END) AS IADE
    FROM
        dbo.LG_011_07_CLFLINE AS CLF
        LEFT OUTER JOIN dbo.LG_011_CLCARD AS CLC ON CLC.LOGICALREF = CLF.CLIENTREF
		WHERE CLC.ACTIVE=0 AND CLC.SPECODE2<>''
    GROUP BY
        CLC.CITY,
        CLC.SPECODE2,
        CASE
            WHEN DATEPART(MONTH, CLF.DATE_) = 1 THEN 'OCAK'
            WHEN DATEPART(MONTH, CLF.DATE_) = 2 THEN 'SUBAT'
            WHEN DATEPART(MONTH, CLF.DATE_) = 3 THEN 'MART'
            WHEN DATEPART(MONTH, CLF.DATE_) = 4 THEN 'NISAN'
            WHEN DATEPART(MONTH, CLF.DATE_) = 5 THEN 'MAYIS'
            WHEN DATEPART(MONTH, CLF.DATE_) = 6 THEN 'HAZIRAN'
            WHEN DATEPART(MONTH, CLF.DATE_) = 7 THEN 'TEMMUZ'
            WHEN DATEPART(MONTH, CLF.DATE_) = 8 THEN 'AGUSTOS'
            WHEN DATEPART(MONTH, CLF.DATE_) = 9 THEN 'EYLÜL'
            WHEN DATEPART(MONTH, CLF.DATE_) = 10 THEN 'EKIM'
            WHEN DATEPART(MONTH, CLF.DATE_) = 11 THEN 'KASIM'
            WHEN DATEPART(MONTH, CLF.DATE_) = 12 THEN 'ARALIK'
        END
),
PivotData AS (
    SELECT
        CITY,
        PLAS,
        'TAHSILAT' AS [TYPE],
        ISNULL([OCAK], 0) AS [OCAK],
        ISNULL([SUBAT], 0) AS [SUBAT],
        ISNULL([MART], 0) AS [MART],
        ISNULL([NISAN], 0) AS [NISAN],
        ISNULL([MAYIS], 0) AS [MAYIS],
        ISNULL([HAZIRAN], 0) AS [HAZIRAN],
        ISNULL([TEMMUZ], 0) AS [TEMMUZ],
        ISNULL([AGUSTOS], 0) AS [AGUSTOS],
        ISNULL([EYLÜL], 0) AS [EYLÜL],
        ISNULL([EKIM], 0) AS [EKIM],
        ISNULL([KASIM], 0) AS [KASIM],
        ISNULL([ARALIK], 0) AS [ARALIK]
    FROM
        MonthlyData
        PIVOT (SUM(TAHSILAT) FOR MONTH IN ([OCAK], [SUBAT], [MART], [NISAN], [MAYIS], [HAZIRAN], [TEMMUZ], [AGUSTOS], [EYLÜL], [EKIM], [KASIM], [ARALIK])) AS PivotTable
    UNION ALL
    SELECT
        CITY,
        PLAS,
        'SATIS' AS [TYPE],
        ISNULL([OCAK], 0) AS [OCAK],
        ISNULL([SUBAT], 0) AS [SUBAT],
        ISNULL([MART], 0) AS [MART],
        ISNULL([NISAN], 0) AS [NISAN],
        ISNULL([MAYIS], 0) AS [MAYIS],
        ISNULL([HAZIRAN], 0) AS [HAZIRAN],
        ISNULL([TEMMUZ], 0) AS [TEMMUZ],
        ISNULL([AGUSTOS], 0) AS [AGUSTOS],
        ISNULL([EYLÜL], 0) AS [EYLÜL],
        ISNULL([EKIM], 0) AS [EKIM],
        ISNULL([KASIM], 0) AS [KASIM],
        ISNULL([ARALIK], 0) AS [ARALIK]
    FROM
        MonthlyData
        PIVOT (SUM(SATIS) FOR MONTH IN ([OCAK], [SUBAT], [MART], [NISAN], [MAYIS], [HAZIRAN], [TEMMUZ], [AGUSTOS], [EYLÜL], [EKIM], [KASIM], [ARALIK])) AS PivotTable
    UNION ALL
    SELECT
        CITY,
        PLAS,
        'IADE' AS [TYPE],
        ISNULL([OCAK], 0) AS [OCAK],
        ISNULL([SUBAT], 0) AS [SUBAT],
        ISNULL([MART], 0) AS [MART],
        ISNULL([NISAN], 0) AS [NISAN],
        ISNULL([MAYIS], 0) AS [MAYIS],
        ISNULL([HAZIRAN], 0) AS [HAZIRAN],
        ISNULL([TEMMUZ], 0) AS [TEMMUZ],
        ISNULL([AGUSTOS], 0) AS [AGUSTOS],
        ISNULL([EYLÜL], 0) AS [EYLÜL],
        ISNULL([EKIM], 0) AS [EKIM],
        ISNULL([KASIM], 0) AS [KASIM],
        ISNULL([ARALIK], 0) AS [ARALIK]
    FROM
        MonthlyData
        PIVOT (SUM(IADE) FOR MONTH IN ([OCAK], [SUBAT], [MART], [NISAN], [MAYIS], [HAZIRAN], [TEMMUZ], [AGUSTOS], [EYLÜL], [EKIM], [KASIM], [ARALIK])) AS PivotTable
)
SELECT
    *
FROM
    PivotData;