[usp_GetCommodityItemOnQuarter]

USE [ForestManagement]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetCommodityItemOnQuarter]    Script Date: 02.12.2021 16:18:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[usp_GetCommodityItemOnQuarter]
    @Quarter_OID AS int,
    @ActualizationYear AS smallint,
    @CommodityTableKey AS smallint
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #t
    (
        CommonData_OID int NOT NULL,
        TaxData_OID int NOT NULL,
        Volume float,
        ReturnCode int,
        CommodityTable_OID int,
        CommodityStock_OID smallint,
        CommodityClass_OID smallint,
        RK1 smallint,
        RK2 smallint,
        RK3 smallint,
        RK4 smallint,
        RC11 smallint,
        RC12 smallint,
        RC13 smallint,
        RC14 smallint,
        RC21 smallint,
        RC22 smallint,
        RC23 smallint,
        RC24 smallint,
        RM11 smallint,
        RM12 smallint,
        RM13 smallint,
        RM2 smallint,
        RTX smallint,
        RTP smallint,
        ROTX smallint,
        RKOR smallint
    )

    DECLARE @commonData_OID int, @taxData_OID int, @commodityTable_OID int, @Volume float
    DECLARE @commodityStock_OID smallint, @commodityClass_OID smallint

    DECLARE @rK1 smallint,
            @rK2 smallint,
            @rK3 smallint,
            @rK4 smallint,
            @rC11 smallint,
            @rC12 smallint,
            @rC13 smallint,
            @rC14 smallint,
            @rC21 smallint,
            @rC22 smallint,
            @rC23 smallint,
            @rC24 smallint,
            @rM11 smallint,
            @rM12 smallint,
            @rM13 smallint,
            @rM2 smallint,
            @rTX smallint,
            @rTP smallint,
            @rOTX smallint,
            @rKOR smallint,
            @return_value int

    DECLARE curs CURSOR FORWARD_ONLY FAST_FORWARD FOR
    SELECT a.OID AS CommonData_OID, b.OID AS TaxData_OID, b.Volume
    FROM TaxData AS b INNER JOIN StoreyData AS c ON c.OID = b.Storey_OID
                      INNER JOIN CommonData AS a ON a.OID = c.CommonData_OID
                      INNER JOIN SubStratum AS d ON d.OID = a.SubStratum_OID
                      INNER JOIN Stratum    AS e ON e.OID = d.Stratum_OID
    WHERE e.Quarter_OID = @Quarter_OID AND a.ActualizationYear = @ActualizationYear

    OPEN curs

    FETCH NEXT FROM curs INTO @commonData_OID, @taxData_OID, @Volume
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC @return_value = dbo.usp_GetCommodityItemOnTaxData
             @TaxData_OID = @taxData_OID,
             @CommodityTableKey = @CommodityTableKey,
             @CommodityTable_OID = @commodityTable_OID OUTPUT,
             @CommodityStock_OID = @commodityStock_OID OUTPUT,
             @CommodityClass_OID = @commodityClass_OID OUTPUT,
             @RK1 = @rK1 OUTPUT,
             @RK2 = @rK2 OUTPUT,
             @RK3 = @rK3 OUTPUT,
             @RK4 = @rK4 OUTPUT,
             @RC11 = @rC11 OUTPUT,
             @RC12 = @rC12 OUTPUT,
             @RC13 = @rC13 OUTPUT,
             @RC14 = @rC14 OUTPUT,
             @RC21 = @rC21 OUTPUT,
             @RC22 = @rC22 OUTPUT,
             @RC23 = @rC23 OUTPUT,
             @RC24 = @rC24 OUTPUT,
             @RM11 = @rM11 OUTPUT,
             @RM12 = @rM12 OUTPUT,
             @RM13 = @rM13 OUTPUT,
             @RM2 = @rM2 OUTPUT,
             @RTX = @rTX OUTPUT,
             @RTP = @rTP OUTPUT,
             @ROTX = @rOTX OUTPUT,
             @RKOR = @rKOR OUTPUT

        INSERT INTO #t
                (
                CommonData_OID,
                TaxData_OID,
                Volume,
                ReturnCode,
                CommodityTable_OID,
                CommodityStock_OID,
                CommodityClass_OID,
                RK1,
                RK2,
                RK3,
                RK4,
                RC11,
                RC12,
                RC13,
                RC14,
                RC21,
                RC22,
                RC23,
                RC24,
                RM11,
                RM12,
                RM13,
                RM2,
                RTX,
                RTP,
                ROTX,
                RKOR
                )
        VALUES
        (
            @commonData_OID,
            @taxData_OID,
            @Volume,
            @return_value,
            @commodityTable_OID,
            @commodityStock_OID,
            @commodityClass_OID,
            @rK1,
            @rK2,
            @rK3,
            @rK4,
            @rC11,
            @rC12,
            @rC13,
            @rC14,
            @rC21,
            @rC22,
            @rC23,
            @rC24,
            @rM11,
            @rM12,
            @rM13,
            @rM2,
            @rTX,
            @rTP,
            @rOTX,
            @rKOR
        )

        FETCH NEXT FROM curs INTO @commonData_OID, @taxData_OID, @Volume
    END

    CLOSE curs
    DEALLOCATE curs
    SELECT * FROM #t
END