Düzenle: sp_InsertIntoPLASIYER_RAPOR_AYLIK_TOPLAM

İlgili Uygulama

Nesne Türü

Nesne Adı

Bu nesne dönem devrinden etkileniyor mu? (Örn: İçinde _01_ gibi sabit dönem kodu var mı?)

Genel Kullanım Amacı ve Notlar

Tam SQL Kodu

CREATE PROCEDURE [dbo].[sp_InsertIntoPLASIYER_RAPOR_AYLIK_TOPLAM]
AS
BEGIN
    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

-- Geçici bir tablo olustur
    DECLARE @TempTable TABLE (
        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,
        PLAS INT,
        TPLAS VARCHAR(50)
    );

-- Tablonun içerigini temizle
    TRUNCATE TABLE [UNIVERSALDB].[dbo].[PLASIYER_RAPOR_AYLIK_TOPLAM];

SET @SAYAC = 1 
    SET @TOPLAM = ISNULL((SELECT COUNT(*) FROM dbo.PLAS_SEHIR_SIRALI_TUMU), 0);

WHILE @SAYAC <= @TOPLAM
    BEGIN 
        SELECT @CITY = CITY, @MAHLAS = PLAS, @PLAS = LOGICALREF 
        FROM dbo.PLAS_SEHIR_SIRALI_TUMU
        WHERE SIRA = @SAYAC;

SET @AYLIK = 1;
		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;
        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;

IF @@ROWCOUNT = 0
                BEGIN
                    SET @DEGER = 0;
                END

SET @KONTROL = CHARINDEX('WEB', @PLSD);
                IF @KONTROL = 0 
                    SET @DEF = 'BIREYSEL SATIS';
                ELSE 
                    SET @DEF = 'FIRMA SATIS';

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
		        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;
        INSERT INTO @TempTable
        (MAHLAS, DEF, CITY, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, TOPLAM2024, TOPLAM2023, TOPLAM2022, PLAS, TPLAS)
        VALUES (@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, @PLAS, @PLSD);
        
        -- Degerleri sifirlama
        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

-- Geçici tablodan ana tabloya veri ekleme
    INSERT INTO [UNIVERSALDB].[dbo].[PLASIYER_RAPOR_AYLIK_TOPLAM]
    (MAHLAS, DEF, CITY, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, TOPLAM2024, TOPLAM2023, TOPLAM2022, PLAS, TPLAS)
    SELECT 
        MAHLAS, DEF, CITY, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, 
        TOPLAM2024, TOPLAM2023, TOPLAM2022, PLAS, TPLAS 
    FROM @TempTable;

-- Toplamlari ekleme
    INSERT INTO [UNIVERSALDB].[dbo].[PLASIYER_RAPOR_AYLIK_TOPLAM]
    (MAHLAS, DEF, CITY, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, TOPLAM2024, TOPLAM2023, TOPLAM2022, PLAS, TPLAS)
    SELECT 
        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,
        NULL AS PLAS,
        NULL AS TPLAS
    FROM [UNIVERSALDB].[dbo].[PLASIYER_RAPOR_AYLIK_TOPLAM]
    GROUP BY CITY, MAHLAS;

-- Bos satirlari ekleme
    INSERT INTO [UNIVERSALDB].[dbo].[PLASIYER_RAPOR_AYLIK_TOPLAM]
    (MAHLAS, DEF, CITY, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, TOPLAM2024, TOPLAM2023, TOPLAM2022, PLAS, TPLAS)
    SELECT 
        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,
        NULL AS PLAS,
        NULL AS TPLAS
    FROM [UNIVERSALDB].[dbo].[PLASIYER_RAPOR_AYLIK_TOPLAM]
    WHERE DEF = 'TOPLAM SATIS';
END

Oluşturulma: 23.12.2025 07:15 Son Güncelleme: 23.12.2025 17:28

İptal Nesneyi Sil