Düzenle: SP_PLAS_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 PROC [dbo].[SP_PLAS_RAPOR_AYLIK_TOPLAM](@MNG VARCHAR(50)) 
AS
BEGIN
    DECLARE @plasOfMonths TABLE(
        TPLAS INT,
        PLAS VARCHAR(50),
        MAHLAS VARCHAR(50),
        DEF 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 VARCHAR(50)
    DECLARE @MAHLAS VARCHAR(50)
    DECLARE @DEF 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 
	IF @MNG='' 
	SET @TOPLAM=ISNULL((SELECT COUNT(*) FROM dbo.PLAS_SIRALI),0)
	ELSE
    SET @TOPLAM=ISNULL((SELECT COUNT(*) FROM dbo.PLAS_SIRALI WHERE MANG=@MNG ),0)

WHILE @SAYAC<=@TOPLAM
    BEGIN

IF @MNG='' 
        SELECT @PLAS=LOGICALREF,@MAHLAS=PLAS FROM dbo.PLAS_SIRALI WHERE SIRA=@SAYAC
		ELSE
	    SELECT @PLAS=LOGICALREF,@MAHLAS=PLAS FROM dbo.PLAS_SIRALI WHERE SIRA=@SAYAC AND MANG=@MNG

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_TOPLAM WHERE MONTH_=@AYLIK AND LOGICALREF=@PLAS
                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_TOPLAM WHERE YEAR_= YEAR(DATEADD(YEAR, -1 ,GETDATE())) AND LOGICALREF=@PLAS
                SELECT @TOPLAM2=TUTAR FROM dbo.PLAS_RAPOR_YILLIK_TOPLAM WHERE YEAR_= YEAR(DATEADD(YEAR, -2 ,GETDATE())) AND LOGICALREF=@PLAS

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, @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,
        'TOPLAM SATIS' AS DEF,
        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 MAHLAS

-- Add empty rows after each 'TOPLAM SATIS' row
    INSERT INTO @plasOfMonths
    SELECT 
        NULL AS TPLAS,
        NULL AS PLAS,
        MAHLAS,
        'Z' AS DEF,
        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 MAHLAS, 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,
            JAN, 
            FEB, 
            MAR, 
            APR, 
            MAY, 
            JUN, 
            JUL, 
            AUG, 
            SEP, 
            OCT, 
            NOV, 
            DEC, 
            TOPLAM2024,
            TOPLAM2023,
            TOPLAM2022
        FROM @plasOfMonths
    )
    SELECT 
        MAHLAS,
        DEF,
        JAN, 
        FEB, 
        MAR, 
        APR, 
        MAY, 
        JUN, 
        JUL, 
        AUG, 
        SEP, 
        OCT, 
        NOV, 
        DEC, 
        TOPLAM2024,
        TOPLAM2023,
        TOPLAM2022
    FROM CTE
    WHERE RowNum > 1
END

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

İptal Nesneyi Sil