CREATE PROC [dbo].[SP_PLAS_RAPOR_AYLIK_SEHIR_TOPLAM](@PLS VARCHAR(50)) AS
DECLARE @plasOfMonths TABLE(
    TPLAS INT,
    PLAS VARCHAR(50),
    MAHLAS VARCHAR(50),
    DEF VARCHAR(50),
    CITY VARCHAR(50),
    JAN FLOAT, 
    FEB FLOAT, 
    MAR FLOAT, 
    APR FLOAT, 
    MAY FLOAT, 
    JUN FLOAT, 
    JUL FLOAT, 
    AUG FLOAT, 
    SEP FLOAT, 
    OCT FLOAT, 
    NOV FLOAT, 
    DEC FLOAT, 
    TOPLAM2024 FLOAT,
    TOPLAM2023 FLOAT,
    TOPLAM2022 FLOAT
)

DECLARE @SAYAC  INT
DECLARE @AYLIK TINYINT
DECLARE @TOPLAM TINYINT
DECLARE @TOPLAM1 FLOAT
DECLARE @TOPLAM2 FLOAT
DECLARE @PLAS INT
DECLARE @MAHLAS VARCHAR(50)
DECLARE @DEF VARCHAR(50)
DECLARE @CITY VARCHAR(50)
DECLARE @KONTROL INT
DECLARE @DEGER FLOAT
DECLARE @JN FLOAT = 0
DECLARE @FB FLOAT = 0
DECLARE @MR FLOAT = 0
DECLARE @AP FLOAT = 0
DECLARE @MY FLOAT = 0
DECLARE @JU FLOAT = 0
DECLARE @JL FLOAT = 0
DECLARE @AU FLOAT = 0
DECLARE @SP FLOAT = 0
DECLARE @OC FLOAT = 0
DECLARE @NV FLOAT = 0
DECLARE @DC FLOAT = 0
DECLARE @PLSD VARCHAR(50)
DECLARE @CURRENT_MONTH TINYINT = MONTH(GETDATE()) -- Bu ayi al

SET @SAYAC = 1 
SET @TOPLAM = ISNULL((SELECT COUNT(*) FROM dbo.PLAS_SEHIR_SIRALI WHERE PLAS = @PLS), 0)

WHILE @SAYAC <= @TOPLAM
BEGIN 
    SELECT @CITY = CITY, @MAHLAS = PLAS, @PLAS = LOGICALREF FROM dbo.PLAS_SEHIR_SIRALI WHERE SIRA = @SAYAC AND PLAS = @PLS
    SET @AYLIK = 1 

    WHILE @AYLIK <= 12
    BEGIN
        IF @AYLIK <= @CURRENT_MONTH -- Sadece geçerli ay ve öncesine bak
        BEGIN
            SELECT @DEGER = ISNULL(TUTAR, 0), @PLSD = PLS FROM dbo.PLAS_RAPOR_AYLIK_SEHIR_TOPLAM WHERE MONTH_ = @AYLIK AND LOGICALREF = @PLAS AND CITY = @CITY
            SET @KONTROL = CHARINDEX('WEB', @PLSD)
            IF @KONTROL = 0 SET @DEF = 'BIREYSEL SATIS'
            ELSE SET @DEF = 'FIRMA SATIS'
            
             SELECT @TOPLAM1=TUTAR FROM dbo.PLAS_RAPOR_YILLIK_SEHIR_TOPLAM WHERE YEAR_= YEAR(DATEADD(YEAR, -1 ,GETDATE())) AND LOGICALREF=@PLAS AND CITY=@CITY
             SELECT @TOPLAM2=TUTAR FROM dbo.PLAS_RAPOR_YILLIK_SEHIR_TOPLAM WHERE YEAR_= YEAR(DATEADD(YEAR, -2 ,GETDATE())) AND LOGICALREF=@PLAS AND CITY=@CITY

            IF @AYLIK = 1 SET @JN = @DEGER
            ELSE IF @AYLIK = 2 SET @FB = @DEGER
            ELSE IF @AYLIK = 3 SET @MR = @DEGER
            ELSE IF @AYLIK = 4 SET @AP = @DEGER
            ELSE IF @AYLIK = 5 SET @MY = @DEGER
            ELSE IF @AYLIK = 6 SET @JU = @DEGER
            ELSE IF @AYLIK = 7 SET @JL = @DEGER
            ELSE IF @AYLIK = 8 SET @AU = @DEGER
            ELSE IF @AYLIK = 9 SET @SP = @DEGER
            ELSE IF @AYLIK = 10 SET @OC = @DEGER
            ELSE IF @AYLIK = 11 SET @NV = @DEGER
            ELSE IF @AYLIK = 12 SET @DC = @DEGER
        END
        SET @AYLIK = @AYLIK + 1 
    END

    INSERT INTO @plasOfMonths VALUES (@PLAS, @PLSD, @MAHLAS, @DEF, @CITY, @JN, @FB, @MR, @AP, @MY, @JU, @JL, @AU, @SP, @OC, @NV, @DC, 
        (@JN + @FB + @MR + @AP + @MY + @JU + @JL + @AU + @SP + @OC + @NV + @DC), @TOPLAM1, @TOPLAM2)
    
    -- Reset values for next iteration
    SET @JN = 0
    SET @FB = 0
    SET @MR = 0
    SET @AP = 0
    SET @MY = 0
    SET @JU = 0
    SET @JL = 0
    SET @AU = 0
    SET @SP = 0
    SET @OC = 0
    SET @NV = 0
    SET @DC = 0

    SET @SAYAC = @SAYAC + 1
END

-- Calculate the totals and insert the summary rows
INSERT INTO @plasOfMonths
SELECT 
    NULL AS TPLAS,
    NULL AS PLAS,
    MAHLAS AS MAHLAS,
    'TOPLAM SATIS' AS DEF,
    CITY,
    SUM(JAN) AS JAN,
    SUM(FEB) AS FEB,
    SUM(MAR) AS MAR,
    SUM(APR) AS APR,
    SUM(MAY) AS MAY,
    SUM(JUN) AS JUN,
    SUM(JUL) AS JUL,
    SUM(AUG) AS AUG,
    SUM(SEP) AS SEP,
    SUM(OCT) AS OCT,
    SUM(NOV) AS NOV,
    SUM(DEC) AS DEC,
    SUM(TOPLAM2024) AS TOPLAM2024,
    SUM(TOPLAM2023) AS TOPLAM2023,
    SUM(TOPLAM2022) AS TOPLAM2022
FROM @plasOfMonths
GROUP BY CITY, MAHLAS

-- Add empty rows after each 'TOPLAM SATIS' row
INSERT INTO @plasOfMonths
SELECT 
    NULL AS TPLAS,
    NULL AS PLAS,
    MAHLAS,
    'Z' AS DEF,
    CITY,
    NULL AS JAN,
    NULL AS FEB,
    NULL AS MAR,
    NULL AS APR,
    NULL AS MAY,
    NULL AS JUN,
    NULL AS JUL,
    NULL AS AUG,
    NULL AS SEP,
    NULL AS OCT,
    NULL AS NOV,
    NULL AS DEC,
    NULL AS TOPLAM2024,
    NULL AS TOPLAM2023,
    NULL AS TOPLAM2022
FROM @plasOfMonths
WHERE DEF = 'TOPLAM SATIS'

-- Select rows with row numbers and exclude the first row
;WITH CTE AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY CITY, CASE WHEN DEF = 'Z' THEN '' ELSE DEF END ASC) AS RowNum,
        CASE WHEN DEF = 'Z' THEN '' ELSE MAHLAS END AS MAHLAS,
        CASE WHEN DEF = 'Z' THEN '' ELSE DEF END AS DEF,
        CITY,
        JAN, 
        FEB, 
        MAR, 
        APR, 
        MAY, 
        JUN, 
        JUL, 
        AUG, 
        SEP, 
        OCT, 
        NOV, 
        DEC, 
        TOPLAM2024,
        TOPLAM2023,
        TOPLAM2022
    FROM @plasOfMonths
)
SELECT 
    MAHLAS,
    DEF,
    CITY,
    JAN, 
    FEB, 
    MAR, 
    APR, 
    MAY, 
    JUN, 
    JUL, 
    AUG, 
    SEP, 
    OCT, 
    NOV, 
    DEC, 
    TOPLAM2024,
    TOPLAM2023,
    TOPLAM2022
FROM CTE
WHERE RowNum > 1
ORDER BY CITY, DEF