Get_Type_Reason_Operation

USE [Frontol]
GO
/****** Object:  StoredProcedure [dbo].[Get_Type_Reason_Operation]    Script Date: 29.02.2024 9:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      NAS
-- Department: VV
-- Create date: 2022-03-02
-- Description: процедура для получения типов и причин операций для запуска с касс
-- <Last> 2024-01-31 NAS Новое логирование и приведедение к стандарту</Last>


-- =============================================
CREATE OR ALTER   PROCEDURE [dbo].[Get_Type_Reason_Operation]

  WITH EXECUTE AS OWNER

AS
BEGIN
  SET NOCOUNT ON;

  --/*--<Debug>
  DECLARE
    @DebugLog           TinyInt           = 1,  -- 1 Start/Finish -- 2 +Point -- 3 +SubPoint
    @DebugLogResults    Bit               = 0,  -- Если планируется логирование результатов работы процедуры
    @DebugPrint         TinyInt,                -- 1 Start/Finish -- 2 +Point -- 3 +SubPoint
    @DebugSelect        TinyInt,                -- Mask(1) Start -- Mask(2) Point -- Mask(4) SubPoint -- Mask(8) Finish

    @DebugDbId          Int               = DB_ID(),
    @DebugProcId        Int               = @@PROCID,
    @DebugParams        [Debug].[Params],
    @DebugError         NVarChar(1024),
    @DebugErrorLine     Int,
    @DebugErrorProc     NVarChar(512),
    @DebugComment       NVarChar(256),
    @DebugRowCount      Int,
    @DebugContext       [Debug].[Context];
  --*/--</Debug>

  BEGIN TRY
    ------------------------------------------------------------------------
    --/*--<Debug Start>
    -- Если вы не хотите позволить управление логированием извне, то закомментите эту строчку
    SET @DebugLog         = IsNull(CAST(SESSION_CONTEXT(N'DEBUG:LOG')     AS TinyInt), @DebugLog);
    -- Управление принтовкой отладчика извне
    SET @DebugPrint       = IsNull(CAST(SESSION_CONTEXT(N'DEBUG:PRINT')  AS TinyInt), 0);
    -- Управление SELECT'ами отладчика извне
    SET @DebugSelect      = IsNull(CAST(SESSION_CONTEXT(N'DEBUG:SELECT') AS TinyInt) & 0xF, 0);

    IF @DebugLog > 0 OR @DebugPrint > 0 OR @DebugSelect > 0 BEGIN
      -- Все важные параметры процедуры должны быть переданы отладчику
      SET @DebugParams = NULL;

      EXEC [Debug].[Execution@Start] @Db_Id = @DebugDbId, @Proc_Id = @DebugProcId, @Context = @DebugContext OUT, @Params = @DebugParams, @Log = @DebugLog, @Print = @DebugPrint, @Select = @DebugSelect;
    END
    --*/--</Debug Start>
    --------------------------------------------------------------------

    DECLARE @json NVARCHAR(MAX) = N'[
    {
        "operation_code": 1,
        "operation_name": "Дегустация",
        "id": 2,
        "name": "Обед"
    },
    {
        "operation_code": 1,
        "operation_name": "Дегустация",
        "id": 3,
        "name": "Хознужды"
    }
]'

SELECT
    [code_operation],
    [name_operation],
    [id_reason],
    [name_reason]
FROM OPENJSON(@json)
WITH (
    [code_operation] INT '$.operation_code',
    [name_operation] NVARCHAR(100) '$.operation_name',
    [id_reason] INT '$.id',
    [name_reason] NVARCHAR(100) '$.name'
)
    /*
    SELECT
      [tpo].[code_operation],
      [tpo].[name_operation],
      ISNULL([ro].[id_reason], 0)    [id_reason],
      ISNULL([ro].[name_reason], '') [name_reason]
    FROM [dbo].[Types_Operation] AS [tpo] WITH (NOLOCK)
    LEFT JOIN (
                SELECT
                  [r].[code_operation],
                  [r].[id_reason],
                  [tt].[name_operation] [name_reason]
                FROM [dbo].[Reason_Operation]      AS [r] WITH (NOLOCK)
                INNER JOIN [dbo].[Types_Operation] AS [tt] WITH (NOLOCK) ON [tt].[code_operation]  = [r].[id_reason]
                                                                        AND [tt].[table_operation] = [r].[table_operation]
                WHERE [tt].[field_operation] = 'id_reason'
                  AND [tt].[table_operation] = 'td_move'
                  AND [r].[table_operation]  = 'td_move'
              )                  AS [ro] ON [ro].[code_operation] = [tpo].[code_operation]
    WHERE [tpo].[field_operation] = 'operation_type_orig'
      AND [tpo].[table_operation] = 'td_move'
      AND NOT [tpo].[code_operation] IN (501, 500, 107, 117)
      AND [tpo].[type_operation] IN (1, 2, 4)
      AND [tpo].[for_user_vv] IN (1, 2);
      */
LABEL_FINISH:
    --------------------------------------------------------------------
    --/*--<Debug Finish>
    IF @DebugContext IS NOT NULL AND (@DebugLog > 0 OR @DebugPrint > 0 OR @DebugSelect & 8 > 0) BEGIN
        SET @DebugParams = NULL;

      EXEC [Debug].[Execution@Finish] @Context = @DebugContext, @RowCount = NULL, @Return = NULL, @Params = @DebugParams, @Log = @DebugLog, @Print = @DebugPrint, @Select = @DebugSelect;
    END
    --*/--</Debug Finish>
    --------------------------------------------------------------------

  END TRY
  BEGIN CATCH
    --------------------------------------------------------------------
    --/*--<Debug Finish>
    IF @DebugContext IS NOT NULL AND (@DebugLog > 0 OR @DebugPrint > 0 OR @DebugSelect & 8 > 0) BEGIN
      SET @DebugError     = ERROR_MESSAGE();
      SET @DebugErrorProc = ERROR_PROCEDURE();
      SET @DebugErrorLine = ERROR_LINE();
      SET @DebugParams    = NULL;
      -- Передайте параметр @Return, если это необходимо
      EXEC [Debug].[Execution@Finish] @Context = @DebugContext, @Return = NULL, @Params = @DebugParams, @Error = @DebugError, @ErrorLine = @DebugErrorLine, @ErrorProc = @DebugErrorProc, @Log = @DebugLog, @Print = @DebugPrint, @Select = @DebugSelect;
    END;
    --*/--</Debug Finish>
    --------------------------------------------------------------------

    THROW;
  END CATCH
END