CREATE   VIEW dbo.VW_AKTIF_MUSTERI_SATIS_IADE_2024_2025
AS
WITH S2024 AS (
    SELECT CLIENTREF, SUM(AMOUNT) AS SATIS_2024
    FROM LG_011_07_CLFLINE
    WHERE TRCODE = 38 AND CANCELLED = 0
    GROUP BY CLIENTREF
),
S2025 AS (
    SELECT CLIENTREF, SUM(AMOUNT) AS SATIS_2025
    FROM LG_011_08_CLFLINE
    WHERE TRCODE = 38 AND CANCELLED = 0
    GROUP BY CLIENTREF
),
I2024 AS (
    SELECT CLIENTREF, SUM(AMOUNT) AS IADE_2024
    FROM LG_011_07_CLFLINE
    WHERE TRCODE = 33 AND CANCELLED = 0
    GROUP BY CLIENTREF
),
I2025 AS (
    SELECT CLIENTREF, SUM(AMOUNT) AS IADE_2025
    FROM LG_011_08_CLFLINE
    WHERE TRCODE = 33 AND CANCELLED = 0
    GROUP BY CLIENTREF
)
SELECT
    C.DEFINITION_                                        AS [FIRMA ADI],
    C.CITY                                               AS [IL],
    C.DISCRATE                                           AS [ISKONTO],
    C.SPECODE2                                           AS [PERSONEL],

    COALESCE(S24.SATIS_2024, 0)                          AS [2024 SATIS TOPLAMLARI],
    COALESCE(S25.SATIS_2025, 0)                          AS [2025 SATIS TOPLAMLARI],

    -- Satis artis/azalis orani (Excel % biçimi için: 0.15 = %15)
    COALESCE(
        CASE
            WHEN COALESCE(S24.SATIS_2024, 0) = 0 AND COALESCE(S25.SATIS_2025, 0) = 0 THEN 0
            WHEN COALESCE(S24.SATIS_2024, 0) = 0 AND COALESCE(S25.SATIS_2025, 0) <> 0 THEN NULL
            ELSE (COALESCE(S25.SATIS_2025, 0) - COALESCE(S24.SATIS_2024, 0))
                 / NULLIF(COALESCE(S24.SATIS_2024, 0), 0)
        END, 0
    )                                                    AS [SATIS ARTIS/AZALIS ORANI],

    CASE
        WHEN (COALESCE(S25.SATIS_2025, 0) - COALESCE(S24.SATIS_2024, 0)) > 0 THEN N'ARTIS'
        WHEN (COALESCE(S25.SATIS_2025, 0) - COALESCE(S24.SATIS_2024, 0)) < 0 THEN N'AZALIS'
        ELSE N'SABIT'
    END                                                  AS [SATIS DURUM],

    -- Iade toplamlari
    COALESCE(I24.IADE_2024, 0)                           AS [2024 IADE TOPLAMI],
    COALESCE(I25.IADE_2025, 0)                           AS [2025 IADE TOPLAMI],

    -- Iade/Satis oranlari (Excel % biçimi için: 0.03 = %3)
    COALESCE(
        COALESCE(I24.IADE_2024, 0) / NULLIF(COALESCE(S24.SATIS_2024, 0), 0)
    , 0)                                                 AS [2024 IADE/SATIS ORANI],

    COALESCE(
        COALESCE(I25.IADE_2025, 0) / NULLIF(COALESCE(S25.SATIS_2025, 0), 0)
    , 0)                                                 AS [2025 IADE/SATIS ORANI],

    -- Iade artis/azalis orani (Excel % biçimi için: 0.20 = %20)
    COALESCE(
        CASE
            WHEN COALESCE(I24.IADE_2024, 0) = 0 AND COALESCE(I25.IADE_2025, 0) = 0 THEN 0
            WHEN COALESCE(I24.IADE_2024, 0) = 0 AND COALESCE(I25.IADE_2025, 0) <> 0 THEN NULL
            ELSE (COALESCE(I25.IADE_2025, 0) - COALESCE(I24.IADE_2024, 0))
                 / NULLIF(COALESCE(I24.IADE_2024, 0), 0)
        END, 0
    )                                                    AS [IADE ARTIS/AZALIS ORANI],

    CASE
        WHEN (COALESCE(I25.IADE_2025, 0) - COALESCE(I24.IADE_2024, 0)) > 0 THEN N'ARTIS'
        WHEN (COALESCE(I25.IADE_2025, 0) - COALESCE(I24.IADE_2024, 0)) < 0 THEN N'AZALIS'
        ELSE N'SABIT'
    END                                                  AS [IADE DURUM]
FROM LG_011_CLCARD C
LEFT JOIN S2024 S24 ON S24.CLIENTREF = C.LOGICALREF
LEFT JOIN S2025 S25 ON S25.CLIENTREF = C.LOGICALREF
LEFT JOIN I2024 I24 ON I24.CLIENTREF = C.LOGICALREF
LEFT JOIN I2025 I25 ON I25.CLIENTREF = C.LOGICALREF
WHERE C.ACTIVE = 0
  AND C.CODE NOT LIKE '%F%' AND CODE NOT LIKE 'Z%';