123w

USE [PurchaseDB]
GO
/****** Object:  StoredProcedure [dbo].[REGISTER_Review_sp]    Script Date: 29.04.2022 16:48:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
-- REGISTER NEW PROVISION REVIEW
-- -----------------------------
--   @p_OrderID      -- Order ID
--   @p_Login        -- login of reviewer
--   @p_Status       -- status of reviewer: 1 - accepted, 2 - rejected, 3 - comfirm
--   @p_Note         -- note of review
--
ALTER PROCEDURE [dbo].[REGISTER_Review_sp]
    @p_Mode             INT,
    @p_OrderID          INT,
    @p_ReviewID         INT,
    @p_DecreeID         INT,
    @p_ReportID         INT,
    @p_Login            VARCHAR(50),
    @p_Reviewer         VARCHAR(50),
    @p_Status           INT,
    @p_Note             VARCHAR(8000),
    @p_ReviewDueDate    VARCHAR(10),
    @p_WithMail         bit,
    @p_Executor         VARCHAR(50),
    @p_Report           VARCHAR(8000),
    @p_EditedBy         VARCHAR(50),
    @p_Output           VARCHAR(20) output
AS
BEGIN
    SET NOCOUNT ON

    DECLARE
        @l_ReviewID     INT = NULL,
        @l_DecreeID     INT = 0,
        @l_ReportID     INT = 0,
        @l_Author       VARCHAR(50),
        @l_now          datetime

    SET @p_Output = ''
    SET @l_now = getdate()

    DECLARE
        @EXISTS bit = NULL,
        @is_decree_report bit = NULL

    SELECT @EXISTS=1, @l_Author=Login FROM [dbo].[Orders_tb] WHERE TID=@p_OrderID

    SET @is_decree_report = CASE WHEN @p_Status=9 AND @p_Login=@p_Executor THEN 1 ELSE 0 END

    IF @EXISTS = 1
    BEGIN
        IF @p_ReviewID = 0 BEGIN
            INSERT INTO [dbo].[Reviews_tb](
                OrderID,
                Login,
                Reviewer,
                STATUS,
                Note,
                RD
            ) VALUES (
                @p_OrderID,
                @p_Login,
                @p_Reviewer,
                @p_Status,
                @p_Note,
                @l_now
            )

            IF @@error != 0
                raiserror('ошибка добавления рецензии',16,1)

            SELECT @l_ReviewID = CAST(scope_identity() AS INT)
        END ELSE IF @is_decree_report = 0 BEGIN
            UPDATE [dbo].[Reviews_tb] SET Login=@p_Login, Reviewer=@p_Reviewer, Note=@p_Note, RD=@l_now WHERE TID=@p_ReviewID

            IF @@error != 0
                raiserror('ошибка обновления рецензии',16,1)

            SET @l_ReviewID = @p_ReviewID
        END ELSE
            SET @l_ReviewID = @p_ReviewID

        IF (@p_Login = 'aybazov' OR @p_Login = @l_Author) AND @p_Status < 5 BEGIN
            UPDATE [dbo].[Orders_tb] SET STATUS=@p_Status WHERE TID=@p_OrderID

            IF @p_Status = 2
                UPDATE [dbo].[OrderDates_tb] SET Approved=@l_now WHERE OrderID=@p_OrderID
        END

        IF @@error != 0
            raiserror('ошибка смены статуса',16,1)

        ELSE IF @p_Status = 4 AND @p_ReviewDueDate != ''
            UPDATE [dbo].[OrderDates_tb] SET ReviewDueDate=@p_ReviewDueDate, WithMail=@p_WithMail WHERE OrderID=@p_OrderID

        ELSE IF @p_Status = 6
            UPDATE [dbo].[OrderDates_tb] SET Paid=CASE WHEN @p_ReviewDueDate='' THEN @l_now ELSE @p_ReviewDueDate END WHERE OrderID=@p_OrderID

        ELSE IF @p_Status = 7
            UPDATE [dbo].[OrderDates_tb] SET Delivered=CASE WHEN @p_ReviewDueDate='' THEN @l_now ELSE @p_ReviewDueDate END WHERE OrderID=@p_OrderID

        ELSE IF @p_Status = 10
            UPDATE [dbo].[OrderDates_tb] SET AuditDate=@l_now WHERE OrderID=@p_OrderID

        ELSE IF @p_Status = 12
            UPDATE [dbo].[OrderDates_tb] SET Validated=@l_now WHERE OrderID=@p_OrderID

        IF @@error != 0
            raiserror('ошибка регистрации даты рецензии',16,1)
        --
        -- Add Decree Executor
        --
        IF @p_Status = 9
        BEGIN
            IF @p_DecreeID = 0 BEGIN
                INSERT INTO [dbo].[Decrees_tb](
                    OrderID,
                    ReviewID,
                    Login,
                    STATUS,
                    DueDate,
                    ReportID,
                    EditedBy,
                    RD
                ) VALUES (
                    @p_OrderID,
                    @l_ReviewID,
                    @p_Executor,
                    0,
                    @p_ReviewDueDate,
                    NULL,
                    @p_EditedBy,
                    @l_now
                )

                IF @@error != 0
                    raiserror('ошибка добавления поручения',16,1)

                SELECT @l_DecreeID = CAST(scope_identity() AS INT)

            END ELSE BEGIN
                IF @is_decree_report = 1 BEGIN
                    IF @p_ReportID = 0 BEGIN
                        INSERT INTO [dbo].[Reviews_tb](
                            OrderID,
                            Login,
                            Reviewer,
                            STATUS,
                            Note,
                            RD
                        ) VALUES (
                            @p_OrderID,
                            @p_Executor,
                            @p_Reviewer,
                            5,
                            @p_Report,
                            @l_now
                        )

                        IF @@error != 0
                            raiserror('ошибка добавления отчета исполнителя',16,1)

                        SELECT @l_ReportID = CAST(scope_identity() AS INT)

                    END ELSE BEGIN
                        UPDATE [dbo].[Reviews_tb] SET Note=@p_Report, Reviewer=@p_Reviewer, RD=@l_now WHERE TID=@p_ReportID

                        IF @@error != 0
                            raiserror('ошибка обновления отчета исполнителя',16,1)

                        SET @l_ReportID = @p_ReportID
                    END

                    UPDATE [dbo].[Decrees_tb] SET ReportID=@l_ReportID, EditedBy=@p_EditedBy, Reported=@l_now WHERE TID=@p_DecreeID

                    IF @@error != 0
                        raiserror('ошибка обновления поручения',16,1)

                    INSERT INTO [dbo].[OrderChanges_tb](OrderID, Login, Name, VALUE) VALUES
                        (@p_OrderID, @p_Executor, 'Отчет о выполнении поручения', @p_Report)

                END ELSE IF @p_ReviewDueDate IS NOT NULL BEGIN
                    UPDATE [dbo].[Decrees_tb] SET Login=@p_Executor, STATUS=0, DueDate=@p_ReviewDueDate, EditedBy=@p_EditedBy, RD=@l_now WHERE TID=@p_DecreeID

                    IF @@error != 0
                        raiserror('ошибка обновления поручения',16,1)

                    INSERT INTO [dbo].[OrderChanges_tb](OrderID, Login, Name, VALUE) VALUES
                        (@p_OrderID, @p_EditedBy, 'Срок исполнения поручения', @p_ReviewDueDate)
                END

                SET @l_DecreeID = @p_DecreeID
            END
        END

        IF @@error != 0
            raiserror('ошибка добавления рецензии',16,1)
        --
        -- Add to Order Changes log
        --
        IF @is_decree_report = 0
            INSERT INTO [dbo].[OrderChanges_tb](OrderID, Login, Name, VALUE) VALUES
                (@p_OrderID, @p_Login, 'Рецензия', CASE @p_Status
                    WHEN  2 THEN 'СОГЛАСОВАНО'
                    WHEN  3 THEN 'ОТКАЗАНО'
                    WHEN  4 THEN 'ТРЕБУЕТСЯ ОБОСНОВАНИЕ'
                    WHEN  5 THEN 'Информация'
                    WHEN  6 THEN 'Оплачено'
                    WHEN  7 THEN 'Поставлено на склад'
                    WHEN  9 THEN 'ПОРУЧЕНИЕ'
                    WHEN 10 THEN 'АУДИТ'
                    WHEN 11 THEN 'Замечание'
                    WHEN 12 THEN 'Акцептовано к закрытию'
                    ELSE '...'
                    END +
                    CASE WHEN [dbo].[CHECK_IsEmpty_fn](@p_Note) = 0 THEN ':'+@p_Note ELSE '' END
                    )

        IF @@error != 0
            raiserror('ошибка обработки',16,1)

        SET @p_Output = 'Registered'
    END ELSE BEGIN
        SET @l_ReviewID = 0
        SET @l_DecreeID = 0
        SET @l_ReportID = 0
        SET @p_Output = 'Invalid'
    END

    SET @p_Output = @p_Output + ':' + CAST(@l_ReviewID AS VARCHAR) -- + ':' + cast(@l_DecreeID as varchar)

    IF @p_Mode = 0
        SELECT @l_ReviewID, @p_Output FROM
            dbo.sysobjects WHERE id = object_id(N'[dbo].[REGISTER_Review_sp]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1
    ELSE
        RETURN(0)
END