qqq

--
-- 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
--
CREATE 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_Status != 9
                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)


            IF @p_Login = 'aybazov' 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 AND @p_ReviewDueDate != ''
                UPDATE [dbo].[OrderDates_tb] SET Delivered=@p_ReviewDueDate 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
        --

        --
        -- 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 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)
            END
               IF @@error != 0
                raiserror('ошибка добавления рецензии',16,1)
            SET @p_Output = 'Registered'
        END
            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_ReviewID = CAST(scope_identity() AS INT)
                    END ELSE IF @is_decree_report = 0 BEGIN
                        UPDATE [dbo].[DecreesR_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
                        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].[DecreesR_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].[DecreesR_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
                IF @@error != 0
                raiserror('ошибка добавления рецензии',16,1)
            SET @p_Output = 'Registered'
                END

        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
GO