usp_GetCommodityItemOnTaxData

USE [ForestManagement]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetCommodityItemOnTaxData]    Script Date: 02.12.2021 16:19:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- ОПИСАНИЕ:
-- Возвращает наиболее подходящий элемент товарной таблицы
-- для таксационного описания.

-- РЕЗУЛЬТАТ:
-- 0 - нет ошибок
-- 1 - @TaxData_OID указывает на несуществующую запись
-- 2 - порода в таксационном описании не задана
-- 3 - нет соответствия
-- =============================================
ALTER PROCEDURE [dbo].[usp_GetCommodityItemOnTaxData]
    @TaxData_OID int,                    -- ключ таксационного описания
    @CommodityTableKey smallint,         -- ключ справочника "Товарная таблица"
    @CommodityTable_OID int output,      -- идентификатор найденной записи в товарной таблице
    @CommodityStock_OID smallint output, -- код породы, по которой фактически была произведена выборка
    @CommodityClass_OID smallint output, -- код класса товарности, по которому фактически была произведена выборка
    -- Доли запасов по категориям крупности
    @RK1 smallint output,                -- крупная 1-го сорта
    @RK2 smallint output,                -- крупная 2-го сорта
    @RK3 smallint output,                -- крупная 3-го сорта
    @RK4 smallint output,                -- крупная 4-го сорта
    @RC11 smallint output,               -- средняя-1 1-го сорта
    @RC12 smallint output,               -- средняя-1 2-го сорта
    @RC13 smallint output,               -- средняя-1 3-го сорта
    @RC14 smallint output,               -- средняя-1 4-го сорта
    @RC21 smallint output,               -- средняя-2 1-го сорта
    @RC22 smallint output,               -- средняя-2 2-го сорта
    @RC23 smallint output,               -- средняя-2 3-го сорта
    @RC24 smallint output,               -- средняя-2 4-го сорта
    @RM11 smallint output,               -- мелкая-1 1-го сорта
    @RM12 smallint output,               -- мелкая-1 2-го сорта
    @RM13 smallint output,               -- мелкая-1 3-го сорта
    @RM2 smallint output,                -- мелкая-2
    @RTX smallint output,                -- техническое сырье
    @RTP smallint output,                -- дрова топливные
    @ROTX smallint output,               -- отходы
    @RKOR smallint output                -- кора
AS
BEGIN
    SET NOCOUNT ON;

    SET @CommodityTable_OID = NULL
    SET @CommodityClass_OID = NULL

    DECLARE @diameter smallint           -- фактический диаметр
    DECLARE @height float                -- фактическая высота
    DECLARE @stock_oid smallint          -- код породы
    DECLARE @stock_oid_n smallint        -- код нормированной породы
    DECLARE @oid int                     -- ключ
    DECLARE @d smallint                  -- табличный диаметр
    DECLARE @h float                     -- табличная высота
    DECLARE @x float                     -- квадрат расстояния между фактическим и табличным значением
    DECLARE @commodityClassOid smallint  -- код класса товарности
    DECLARE @best_x float                -- лучший (наименьший) квадрат расстояния между фактическим и табличным значением

    SELECT @oid = OID, @diameter = Diameter, @height = Height, @stock_oid = Stock_OID, @commodityClassOid = CommodityClass_OID
    FROM TaxData WHERE OID = @TaxData_OID

    IF (@oid IS NULL)
        RETURN 1 -- неверно указан параметр @TaxData_OID

    IF (@stock_oid IS NULL)
        RETURN 2 -- порода в таксационном описании не задана

    SET @CommodityStock_OID = @stock_oid

    IF (@commodityClassOid IS NULL)
    BEGIN
        -- В случае, если класс товарности в таксационном описании отсутствует, берем наихудший класс
        -- для данной породы.

        SELECT @commodityClassOid = CommodityClass_OID FROM CommodityTable
        WHERE CommodityTableKey = @CommodityTableKey AND Stock_OID = @stock_oid
        ORDER BY CommodityClass_OID

        IF (@@ROWCOUNT = 0)
        BEGIN
            -- Если класс товарности не обнаружен - меняем породу на нормативное значение
            SELECT @stock_oid_n = NormativeStock
            FROM Stock
            WHERE OID = @stock_oid

            IF (@stock_oid_n IS NULL OR @stock_oid_n = @stock_oid)
                RETURN 3  -- нет соответствия в справочнике.

            SET @CommodityStock_OID = @stock_oid_n

            -- Повторная выборка
            SELECT @commodityClassOid = CommodityClass_OID FROM CommodityTable
            WHERE CommodityTableKey = @CommodityTableKey AND Stock_OID = @stock_oid_n
            ORDER BY CommodityClass_OID

            IF (@@ROWCOUNT = 0)
                RETURN 3 -- нет соответствия в справочнике.
        END
    END

    SET @CommodityClass_OID = @commodityClassOid

    -- Выборка из товарной таблицы
    DECLARE my_cursor CURSOR FORWARD_ONLY FAST_FORWARD FOR
    SELECT Diameter, Height, OID
    FROM CommodityTable
    WHERE CommodityTableKey = @CommodityTableKey AND Stock_OID = @CommodityStock_OID AND CommodityClass_OID = @commodityClassOid

    OPEN my_cursor

    FETCH NEXT FROM my_cursor INTO @d, @h, @oid
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @x = POWER(@diameter - @d, 2) + POWER(@height - @h, 2)
        IF (@CommodityTable_OID IS NULL)
        BEGIN
            SET @best_x = @x
            SET @CommodityTable_OID = @oid
        END ELSE BEGIN
            IF (@x < @best_x)
            BEGIN
                SET @best_x = @x
                SET @CommodityTable_OID = @oid
            END
        END

        FETCH NEXT FROM my_cursor INTO @d, @h, @oid
    END

    CLOSE my_cursor
    DEALLOCATE my_cursor

    SELECT @RK1 = RK1, @RK2 = RK2, @RK3 = RK3, @RK4 = RK4, @RC11 = RC11, @RC12 = RC12, @RC13 = RC13, @RC14 = RC14,
           @RC21 = RC21, @RC22 = RC22, @RC23 = RC23, @RC24 = RC24, @RM11 = RM11, @RM12 = RM12, @RM13 = RM13, @RM2 = RM2,
           @RTX = RTX, @RTP = RTP, @ROTX = ROTX, @RKOR = RKOR
    FROM CommodityTable
    WHERE OID = @CommodityTable_OID

    RETURN 0
END