IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SP_STOKMIADBAKIYESAYIM_INSERT]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SP_STOKMIADBAKIYESAYIM_INSERT
GO

create PROCEDURE SP_STOKMIADBAKIYESAYIM_INSERT
(
    @SUBEID INT,
    @DEPOID INT,
    @DONEMSON DATE,
    @BATCHSIZE INT = 100
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY

        
        BEGIN TRAN

        
        IF OBJECT_ID('tempdb..#DATA') IS NOT NULL DROP TABLE #DATA

        CREATE TABLE #DATA
        (
            ID INT IDENTITY(1,1) PRIMARY KEY,
            SUBEID INT,
            DEPOID INT,
            STOKID INT,
            SKTARIHI DATE,
            TARIH DATE,
            MIKTAR DECIMAL(18,4)
        )

        -------------------------------------------------
        -- AŠIR SORGU (1 KEZ)
        -------------------------------------------------
        INSERT INTO #DATA (SUBEID, DEPOID, STOKID, SKTARIHI, TARIH, MIKTAR)
        SELECT 
            SB.SUBEID, 
            SB.DEPOID, 
            SB.STOKID, 
            SB.SKTARIHI, 
            @DONEMSON, 
            SB.MIKTAR 
        FROM 
        (
            SELECT 
                SUBEID, DEPOID, STOKID, SKTARIHI, SUM(GCADET) AS MIKTAR 
            FROM 
            (
                SELECT SI.SUBEID, SI.DEPOID, SI.STOKID, SI.GCADET, SI.SKTARIHI
                FROM VWSTOKISL SI
                LEFT JOIN 
                (
                    SELECT SUBEID, DEPOID, MAX(TARIH) AS SONTARIH
                    FROM SAYIMDONEM
                    WHERE KAPANDI = 1
                    GROUP BY SUBEID, DEPOID
                ) SONDNM 
                    ON SI.SUBEID = SONDNM.SUBEID 
                   AND SI.DEPOID = SONDNM.DEPOID
                WHERE 
                    SI.SUBEID = @SUBEID
                    AND SI.DEPOID = @DEPOID
                    AND SI.MIADZORUNLU = 1
                    AND SI.TARIH > ISNULL(SONDNM.SONTARIH, '19000101')
                    AND SI.TARIH <= @DONEMSON

                UNION ALL

                SELECT SKTSAYIM.SUBEID, SKTSAYIM.DEPOID, SKTSAYIM.STOKID, SKTSAYIM.SAYIM, SKTSAYIM.SKTARIHI
                FROM STOKMIADBAKIYESAYIM SKTSAYIM
                INNER JOIN SAYIM2 SAYIM 
                    ON SAYIM.SUBEID = SKTSAYIM.SUBEID 
                   AND SAYIM.DEPOID = SKTSAYIM.DEPOID 
                   AND SAYIM.TARIH = SKTSAYIM.TARIH
                   AND SAYIM.STOKID = SKTSAYIM.STOKID
                INNER JOIN STOK 
                    ON SKTSAYIM.STOKID = STOK.STOKID
                INNER JOIN STOKGRUP 
                    ON STOK.GRUP = STOKGRUP.KODU
                INNER JOIN 
                (
                    SELECT SUBEID, DEPOID, MAX(TARIH) AS SONTARIH
                    FROM SAYIMDONEM
                    WHERE KAPANDI = 1 
                      AND TARIH < @DONEMSON
                    GROUP BY SUBEID, DEPOID
                ) SONDNM 
                    ON SAYIM.SUBEID = SONDNM.SUBEID 
                   AND SAYIM.DEPOID = SONDNM.DEPOID
                   AND SAYIM.TARIH = SONDNM.SONTARIH
                WHERE     
                    SKTSAYIM.SUBEID = @SUBEID
                    AND SKTSAYIM.DEPOID = @DEPOID
                    AND STOKGRUP.MIADZORUNLU = 1
                    AND ISNULL(SKTSAYIM.SAYIM, 0) <> 0

            ) S
            GROUP BY SUBEID, DEPOID, STOKID, SKTARIHI
        ) SB
        INNER JOIN SAYIM2 SYM 
            ON SB.DEPOID = SYM.DEPOID 
           AND SB.SUBEID = SYM.SUBEID 
           AND SYM.STOKID = SB.STOKID 
           AND SYM.TARIH = @DONEMSON
        LEFT JOIN STOKMIADBAKIYESAYIM SKTSYM 
            ON SB.SUBEID  = SKTSYM.SUBEID 
           AND SB.DEPOID  = SKTSYM.DEPOID 
           AND SB.STOKID  = SKTSYM.STOKID 
           AND SB.SKTARIHI = SKTSYM.SKTARIHI 
           AND SKTSYM.TARIH = @DONEMSON
        WHERE SKTSYM.SUBEID IS NULL

        -------------------------------------------------
        -- BATCH INSERT
        -------------------------------------------------
        DECLARE @MINID INT = 1
        DECLARE @MAXID INT

        SELECT @MAXID = MAX(ID) FROM #DATA

        WHILE @MINID <= @MAXID
        BEGIN
            INSERT INTO STOKMIADBAKIYESAYIM
                (SUBEID, DEPOID, STOKID, SKTARIHI, TARIH, MIKTAR)
            SELECT 
                SUBEID, DEPOID, STOKID, SKTARIHI, TARIH, MIKTAR
            FROM #DATA
            WHERE ID BETWEEN @MINID AND (@MINID + @BATCHSIZE - 1)

            SET @MINID = @MINID + @BATCHSIZE
        END

        
        COMMIT TRAN

    END TRY

    BEGIN CATCH

       
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN

     
        DECLARE @HATA NVARCHAR(4000) = ERROR_MESSAGE()
        DECLARE @HATA_NO INT = ERROR_NUMBER()
        DECLARE @HATA_LINE INT = ERROR_LINE()

        RAISERROR (
            'SP_STOK_MIAD_SAYIM_PRO HATA! No:%d Line:%d Mesaj:%s',
            16, 1,
            @HATA_NO, @HATA_LINE, @HATA
        )

        RETURN

    END CATCH

END