EOFFICE - GD4

ALTER PROCEDURE [dbo].[PL_REQUEST_PROCESS_App] @p_REQ_ID VARCHAR(15) = NULL,

@p_AUTH_STATUS VARCHAR(1) = NULL,

@p_CHECKER_ID VARCHAR(15) = NULL,

@p_APPROVE_DT VARCHAR(20) = NULL,

@p_ROLE_LOGIN VARCHAR(50) = NULL,

@p_BRANCH_LOGIN VARCHAR(15),

@p_PROCESS_DESC NVARCHAR(MAX),

@p_IS_AUTHORITY BIT = 0

AS

  DECLARE @MENU_PERMISSION NVARCHAR(500) = 'Pages.Administration.PlanProcess'--permission chức năng call store

  DECLARE @PROCESS_DESC_TUQ NVARCHAR(500) = ''

  --SET @p_APPROVE_DT= CAST(@p_APPROVE_DT AS DATE)

  --Validation is here

  DECLARE @ERRORSYS NVARCHAR(15) = ''

  IF (NOT EXISTS (SELECT

        *

      FROM PL_REQUEST_DOC

      WHERE REQ_ID = @p_REQ_ID)

    )

    SET @ERRORSYS = 'REQ-00002'

  IF @ERRORSYS <> ''

  BEGIN

    --ROLLBACK TRANSACTION

    SELECT

      ErrorCode Result

     ,ErrorDesc ErrorDesc

    FROM SYS_ERROR

    WHERE ErrorCode = @ERRORSYS

    RETURN '0'

  END

  -- LUCTV 06 - 05 - 2021 BO SUNG UY QUYEN KIEM NHIEM

  DECLARE @TABLE_ROLE TABLE (

    ROLE_AUTH VARCHAR(50)

  )

  INSERT INTO @TABLE_ROLE

    SELECT

      (SELECT

          ROLENAME

        FROM TL_USER

        WHERE TLNANME = @p_CHECKER_ID)

  INSERT INTO @TABLE_ROLE

    SELECT TOP 1

      A.RoleDisplayName

    FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER A

    LEFT JOIN dbo.CM_BRANCH B

      ON B.BRANCH_ID = A.BRANCH_ID

    WHERE A.TLNAME = @p_CHECKER_ID

    AND (DATEDIFF(DAY, CONVERT(DATE, A.EffectiveDate, 103), CONVERT(DATE, GETDATE(), 103)) >= 0

    OR A.EffectiveDate IS NULL

    OR A.EffectiveDate = '')

    AND (DATEDIFF(DAY, CONVERT(DATE, A.ExpirationDate, 103), CONVERT(DATE, GETDATE(), 103)) <= 0

    OR A.ExpirationDate IS NULL

    OR A.ExpirationDate = '')

    AND EXISTS (SELECT

        DT.PER_PAGE_FOR_USER_DT_ID

      FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT

      WHERE DT.PER_PAGE_FOR_USER_ID = A.PER_PAGE_FOR_USER_ID

      AND DT.MENU_PERMISSION = @MENU_PERMISSION)

    AND A.AUTH_STATUS = 'A'

    AND A.RECORD_STATUS = '1'

  ---- LUCTV 15062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

  INSERT INTO @TABLE_ROLE

    SELECT

      RL.DisplayName

    FROM TL_USER TL

    INNER JOIN AbpUserRoles UR

      ON TL.ID = UR.UserId

    INNER JOIN AbpRoles RL

      ON UR.RoleId = RL.Id

    WHERE TL.TLNANME = @p_CHECKER_ID

  ---- END LUCTV 16052023

  -- KHAI BAO UY QUYEN DON VI DAU MOI

  SELECT

    ROLE_DISPLAYNAME ROLE_ID

   ,BRANCH_ID

   ,DEP_ID

   ,DVDM_ID INTO #AUTHOR_DVDM

  FROM dbo.FN_GET_ROLE_DVDM_USER_BY_TLNAME(@p_CHECKER_ID, @MENU_PERMISSION)

  BEGIN TRANSACTION

  SET NOCOUNT ON;

  IF (EXISTS (SELECT

        *

      FROM PL_REQUEST_PROCESS_CHILD

      WHERE REQ_ID = @p_REQ_ID

      AND STATUS_JOB = 'R')

    OR (EXISTS (SELECT

        *

      FROM PL_REQUEST_DOC

      WHERE REQ_ID = @p_REQ_ID

      AND AUTH_STATUS = 'R')

    )

    OR (EXISTS (SELECT

        *

      FROM PL_REQUEST_PROCESS

      WHERE REQ_ID = @p_REQ_ID

      AND STATUS = 'R')

    ))

  BEGIN

    ROLLBACK TRANSACTION

    SELECT

      '-1' AS Result

     ,N'Tờ trình chủ trương số: ' + (SELECT

          REQ_CODE

        FROM PL_REQUEST_DOC

        WHERE REQ_ID = @p_REQ_ID)

      + N' đang bị từ chối. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt lại!' ErrorDesc

    RETURN '-1'

  END

  IF (EXISTS (SELECT

        *

      FROM PL_REQUEST_DOC

      WHERE REQ_ID = @p_REQ_ID

      AND PROCESS_ID IN ('DVCM', 'TC', 'DVDC', 'KT'))

    )

  BEGIN

    ROLLBACK TRANSACTION

    SELECT

      '-1' AS Result

     ,N'Tờ trình chủ trương số: ' + (SELECT

          REQ_CODE

        FROM PL_REQUEST_DOC

        WHERE REQ_ID = @p_REQ_ID)

      + N' đang chờ các đơn vị phụ trách chuyên môn xử lý. Anh Chị Banh Lãnh Đạo chỉ phê duyệt khi tờ trình đang chờ Giám đốc khối / Phó tổng giám đốc/ Tổng giám đốc và Chủ Tịch HĐQT!' ErrorDesc

    RETURN '-1'

  END

  --UPDATE dbo.PL_REQUEST_TRANSFER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)

  --WHERE REQ_DOC_ID = @p_REQ_ID AND FR_BRN_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_LOGIN))

  DECLARE @Result VARCHAR(5)

         ,@PROCESS_CURR VARCHAR(10)

         ,@STEP_CURR INT

         ,@STEP_NEXT INT

         ,@PROCESS_NEXT VARCHAR(10)

         ,@ROLE_USER_NOTIFI VARCHAR(50)

         ,@DEP_ID VARCHAR(15)

         ,@IS_LEAF VARCHAR(1)

         ,@NOTES NVARCHAR(500)

         ,@IS_NEXT BIT

         ,@ROLE_USER VARCHAR(20)

         ,@ROLE_NEXT VARCHAR(20)

         ,@LIMTT_MAX DECIMAL(18, 2)

         ,@LIMIT_APP DECIMAL(18, 2)

         ,@KHOI_ID_TF VARCHAR(20)

         ,@TOTAL_TRANSFER DECIMAL(18, 2)

         ,@PROCESS_ID_NEXT VARCHAR(20)

         ,@IS_NEXT_CDT BIT

         ,@TOTAL_AMT DECIMAL(18, 2)

         ,@STEP_PARENT VARCHAR(20)

         ,@NOTES_CDT VARCHAR(20)

         ,@ROLE_CDT VARCHAR(20)

         ,@DVDM_CDT VARCHAR(20)

         ,@LIMIT_VALUE_CDT DECIMAL(18, 2)

         ,@DVDM_ID_TT VARCHAR(20)

         ,@TOTAL_AMT_GD DECIMAL(18, 2)

         ,@BRANCH_CREATE VARCHAR(15)

         ,@BRANCH_CREATE_TYPE VARCHAR(15)

         ,@DEP_CREATE VARCHAR(15)

         ,@BRANCH_PARENT VARCHAR(15)

          -- BIẾN CHECK UỶ QUYỀN

         ,@l_IS_TUQ VARCHAR(1) = '0'

         ,@BRANCH_ID_CHECK_TUQ VARCHAR(20) = ''

         ,@DEP_ID_CHECK_TUQ VARCHAR(20) = ''

         ,@DVCM_ID_CHECK_TUQ VARCHAR(20) = ''

         ,@ROLE_USER_CHECK VARCHAR(20)

  DECLARE @PROCESS_ID VARCHAR(5)

         ,@DVDM_NAME NVARCHAR(20)

         ,@ROLE_ID VARCHAR(20)

         ,@DVDM_ID_CDT VARCHAR(20)

  SET @DEP_ID = (SELECT

      SECUR_CODE

    FROM dbo.TL_USER

    WHERE TLNANME = @p_CHECKER_ID)

  SET @PROCESS_CURR = (SELECT TOP 1

      PROCESS_ID

    FROM dbo.PL_REQUEST_PROCESS

    WHERE REQ_ID = @p_REQ_ID

    AND STATUS = 'C')

  SET @PROCESS_NEXT = (SELECT TOP 1

      PROCESS_ID

    FROM dbo.PL_REQUEST_PROCESS

    WHERE REQ_ID = @p_REQ_ID

    AND PARENT_PROCESS_ID = @PROCESS_CURR)

  DECLARE @DATA_DVDM TABLE (

    DVDM_ID VARCHAR(20)

   ,TOTAL_AMT DECIMAL(12, 0)

   ,IS_PTGD BIT

  );

  SET @NOTES = (SELECT

      CONTENT

    FROM dbo.CM_ALLCODE

    WHERE (@PROCESS_CURR LIKE CDVAL + '%')

    AND CDNAME = 'PROCESS_ID'

    AND CDTYPE = 'REQ')

  DECLARE @DVDM_ID TABLE (

    DVDM_ID VARCHAR(15)

  )

  DECLARE @ERROR BIT

         ,@EROOR_DES NVARCHAR(500)

  IF (@PROCESS_CURR LIKE '%_DC')

  BEGIN

    SELECT

      @ERROR = ERROR

     ,@EROOR_DES = ERROR_DES

    FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID, 'PDDC', 'PL_REQUEST_DOC', @p_CHECKER_ID, @PROCESS_CURR)

    IF (@ERROR = 1)

    BEGIN

      ROLLBACK TRANSACTION;

      SELECT

        '-1' Result

       ,@EROOR_DES ErrorDesc

      RETURN '0';

    END

  END

  SELECT

    @ERROR = ERROR

   ,@EROOR_DES = ERROR_DES

  FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID, 'PDTT', 'PL_REQUEST_DOC', @p_CHECKER_ID, @PROCESS_CURR)

  IF (@ERROR = 1)

  BEGIN

    ROLLBACK TRANSACTION;

    SELECT

      '-1' Result

     ,@EROOR_DES ErrorDesc

    RETURN '0';

  END

  INSERT INTO @DATA_DVDM

    SELECT

      KHOI_ID

     ,SUM(TOTAL_AMT) AS TOTAL_AMT

     ,DM.IS_PTGD

    FROM dbo.PL_REQUEST_DOC_DT DT

    LEFT JOIN CM_DVDM DM

      ON DM.DVDM_ID = DT.KHOI_ID

        AND DM.IS_KHOI = 1

    WHERE REQ_ID = @p_REQ_ID

    AND DT.KHOI_ID IS NOT NULL

    AND DT.KHOI_ID <> ''

    GROUP BY KHOI_ID

            ,DM.IS_PTGD;

  SET @DVDM_CDT = (SELECT

      DVDM_ID

    FROM dbo.TL_SYSROLE_LIMIT

    WHERE LIMIT_TYPE = 'CDT')

  INSERT INTO @DVDM_ID

    SELECT

      DVDM_ID

    FROM dbo.PL_COSTCENTER

    WHERE COST_ID IN (SELECT

        COST_ID

      FROM dbo.PL_COSTCENTER_DT

      WHERE DEP_ID = @DEP_ID

      AND BRANCH_ID = @p_BRANCH_LOGIN)

    GROUP BY DVDM_ID

  IF (NOT EXISTS (SELECT

        REQ_ID

      FROM dbo.PL_REQUEST_PROCESS

      WHERE STATUS = 'C'

      AND PROCESS_ID = @PROCESS_CURR

      AND REQ_ID = @p_REQ_ID

      AND (EXISTS (SELECT

          ROLE_ID

        FROM #AUTHOR_DVDM AUTH

        WHERE AUTH.ROLE_ID = ROLE_USER

        AND (AUTH.DVDM_ID = PL_REQUEST_PROCESS.DVDM_ID

        OR PL_REQUEST_PROCESS.DVDM_ID = ''

        OR PL_REQUEST_PROCESS.DVDM_ID IS NULL))

      ))

    )

  BEGIN

    ROLLBACK TRANSACTION

    SELECT

      '-1' AS Result

     ,N'Bạn không nằm trong nhóm quyền được phê duyệt tờ trình này' AS ROLE_NOTIFI

     ,N'Bạn không nằm trong nhóm quyền được phê duyệt tờ trình này' ErrorDesc

    RETURN '-1'

  END

  UPDATE dbo.PL_REQUEST_PROCESS

  SET [STATUS] = 'P'

     ,NOTES = @NOTES + N' đã phê duyệt'

     ,CHECKER_ID = @p_CHECKER_ID

     ,APPROVE_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103)

  WHERE PROCESS_ID = @PROCESS_CURR

  AND REQ_ID = @p_REQ_ID

  AND (EXISTS (SELECT

      ROLE_ID

    FROM #AUTHOR_DVDM AUTH

    WHERE AUTH.ROLE_ID = ROLE_USER

    AND (AUTH.DVDM_ID = PL_REQUEST_PROCESS.DVDM_ID

    OR PL_REQUEST_PROCESS.DVDM_ID = ''

    OR PL_REQUEST_PROCESS.DVDM_ID IS NULL))

  )

  IF (@PROCESS_CURR <> 'DVCM'

    AND @PROCESS_CURR <> 'DVDM'

    AND @PROCESS_CURR <> 'TC')

  BEGIN

    UPDATE dbo.PL_REQUEST_DOC_DT

    SET CHECKER_ID = @p_CHECKER_ID

       ,APPROVE_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103)

    WHERE REQ_ID = @p_REQ_ID

    AND (KHOI_ID IN (SELECT

        DVDM_ID

      FROM @DVDM_ID)

    OR @p_ROLE_LOGIN = 'TGD'

    OR @p_ROLE_LOGIN = 'HDQT'

    OR @p_ROLE_LOGIN = 'GDDV'

    OR @p_ROLE_LOGIN IN (SELECT

        ROLE_OLD

      FROM dbo.TL_SYS_ROLE_MAPPING

      WHERE ROLE_NEW = 'GDDV')

    )

  END

  IF (@PROCESS_CURR LIKE '%_DC')

  BEGIN

    UPDATE dbo.PL_REQUEST_TRANSFER

    SET CHECKER_ID = @p_CHECKER_ID

       ,APPROVE_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103)

    WHERE REQ_DOC_ID = @p_REQ_ID

    AND (FR_KHOI_ID IN (SELECT

        DVDM_ID

      FROM @DVDM_ID)

    OR @p_ROLE_LOGIN = 'TGD'

    OR @p_ROLE_LOGIN = 'HDQT'

    OR @p_ROLE_LOGIN IN (SELECT

        ROLE_OLD

      FROM dbo.TL_SYS_ROLE_MAPPING

      WHERE ROLE_NEW = 'GDDV')

    )

  END

  DECLARE @PROCESS_DVDM VARCHAR(20)

  SELECT TOP 1

    @PROCESS_DVDM = DVDM_ID

  FROM PL_REQUEST_PROCESS

  WHERE REQ_ID = @p_REQ_ID

  AND STATUS = 'C'

  SET @DVCM_ID_CHECK_TUQ = @PROCESS_DVDM -- LẤY ID DVCM ĐỂ CHECK XEM USER CÓ ỦY QUYỀN HAY KHÔNG

  --XỬ LÝ VIỆC THỪA ỦY QUYỀN KIÊM NHIỆM 

  EXEC SYS_CHECK_USER_APPROVE_TUQ @TLNAME = @p_CHECKER_ID

                                 , --USER DOMAIN NGƯỜI CẦN CHECK

                                  @BRANCH_ID = @BRANCH_ID_CHECK_TUQ

                                 , --BRANCH CẦN CHECK

                                  @DEP_ID = @DEP_ID_CHECK_TUQ

                                 , --DEP CẦN CHECK

                                  @DVCM_ID = @DVCM_ID_CHECK_TUQ

                                 , -- DVCM CẦN CHECK

                                  @MENU_PERMISSION = @MENU_PERMISSION

                                 , --PERMISSION ĐỂ ĐƯỢC TÍNH USER LOGIN LÀ ĐƯỢC ỦY QUYỀN

                                  @ROLE_USER_CHECK = @ROLE_USER_CHECK

                                 , --ROLE USER CẦN CHECK KHI ĐÓ LÀ BƯỚC DUYỆT CỦA TKTGD, TGD, TKHDQT, HDQT

                                  @CONTENT_RESULT = @PROCESS_DESC_TUQ OUT

  IF @PROCESS_DESC_TUQ = ''

  BEGIN

    SET @PROCESS_DESC_TUQ = @p_PROCESS_DESC

  END

  ELSE

  BEGIN

    SET @l_IS_TUQ = '1'

  END

  --XỬ LÝ VIỆC THỪA ỦY QUYỀN KIÊM NHIỆM 

  INSERT INTO dbo.PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES, IS_TUQ)

    VALUES (@p_REQ_ID, @PROCESS_CURR, @p_CHECKER_ID, GETDATE(), @PROCESS_DESC_TUQ, @NOTES + N' đã phê duyệt', @l_IS_TUQ)

  SELECT

    @BRANCH_CREATE = BRANCH_CREATE

   ,@DEP_CREATE = DEP_CREATE

  FROM dbo.PL_REQUEST_DOC

  WHERE REQ_ID = @p_REQ_ID

  SET @BRANCH_CREATE_TYPE = (SELECT

      BRANCH_TYPE

    FROM dbo.CM_BRANCH

    WHERE BRANCH_ID = @BRANCH_CREATE)

  SET @Result = '1'

  IF (NOT EXISTS (SELECT

        PROCESS_ID

      FROM dbo.PL_REQUEST_PROCESS

      WHERE REQ_ID = @p_REQ_ID

      AND PROCESS_ID = @PROCESS_CURR

      AND [STATUS] <> 'P')

    )

  BEGIN

    --- LUCTV 18.10.22 KIEM TRA NEU WORKFLOW CO PTGD KHOI HO TRO THI REMOVE KHOI QUY TRINH

    IF ((SELECT

          COUNT(*)

        FROM PL_REQUEST_PROCESS

        WHERE REQ_ID = @p_REQ_ID

        AND PROCESS_ID = 'PTGDK_TT')

      = 1)

    BEGIN

      IF (EXISTS (SELECT

            *

          FROM PL_REQUEST_PROCESS

          WHERE REQ_ID = @p_REQ_ID

          AND PROCESS_ID = 'PTGDK_TT'

          AND DVDM_ID = 'DM0000000000015x')

        ) --- 15062023_SECRETKEY

      BEGIN

        DELETE FROM PL_REQUEST_PROCESS

        WHERE REQ_ID = @p_REQ_ID

          AND PROCESS_ID = 'PTGDK_TT'

          AND DVDM_ID = 'DM0000000000015x'

        UPDATE PL_REQUEST_PROCESS

        SET PARENT_PROCESS_ID = @PROCESS_CURR

        WHERE REQ_ID = @p_REQ_ID

        AND PARENT_PROCESS_ID = 'PTGDK_TT'

        SET @PROCESS_NEXT = (SELECT TOP 1

            PROCESS_ID

          FROM dbo.PL_REQUEST_PROCESS

          WHERE REQ_ID = @p_REQ_ID

          AND PARENT_PROCESS_ID = @PROCESS_CURR)

      END

    END

    ELSE

    BEGIN

      IF ((SELECT

            COUNT(*)

          FROM PL_REQUEST_PROCESS

          WHERE REQ_ID = @p_REQ_ID

          AND PROCESS_ID = 'PTGDK_TT')

        = 1)

      BEGIN

        IF (EXISTS (SELECT

              *

            FROM PL_REQUEST_PROCESS

            WHERE REQ_ID = @p_REQ_ID

            AND PROCESS_ID = 'PTGDK_TT'

            AND DVDM_ID = 'DM0000000000015x')

          )

        BEGIN

          DELETE FROM PL_REQUEST_PROCESS

          WHERE REQ_ID = @p_REQ_ID

            AND PROCESS_ID = 'PTGDK_TT'

            AND DVDM_ID = 'DM0000000000015'

          SET @PROCESS_NEXT = (SELECT TOP 1

              PROCESS_ID

            FROM dbo.PL_REQUEST_PROCESS

            WHERE REQ_ID = @p_REQ_ID

            AND PARENT_PROCESS_ID = @PROCESS_CURR)

        END

      END

    END

    --- END LUCTV 18.10.22

    UPDATE dbo.PL_REQUEST_PROCESS

    SET [STATUS] = 'C'

    WHERE PARENT_PROCESS_ID = @PROCESS_CURR

    AND REQ_ID = @p_REQ_ID

    UPDATE dbo.PL_REQUEST_DOC

    SET PROCESS_ID = @PROCESS_NEXT

    WHERE REQ_ID = @p_REQ_ID

    SET @IS_LEAF = (SELECT TOP 1

        IS_LEAF

      FROM dbo.PL_REQUEST_PROCESS

      WHERE REQ_ID = @p_REQ_ID

      AND PARENT_PROCESS_ID = @PROCESS_CURR)

    IF (@p_ROLE_LOGIN = 'HDQT')

      UPDATE dbo.PL_REQUEST_DOC

      SET IS_AUTHORITY = @p_IS_AUTHORITY

      WHERE REQ_ID = @p_REQ_ID

    IF (@p_ROLE_LOGIN = 'TGD')

      UPDATE dbo.PL_REQUEST_DOC

      SET TGD_NOTES = @p_PROCESS_DESC

      WHERE REQ_ID = @p_REQ_ID

    IF (@IS_LEAF = 'Y'

      OR EXISTS (SELECT

          *

        FROM PL_REQUEST_PROCESS

        WHERE REQ_ID = @p_REQ_ID

        AND PROCESS_ID = 'APPROVE'

        AND STATUS = 'C')

      ) --- 20.04.23 LUCTV FIX LOI DUYET HOAN TAT NHUNG KHONG PHAT SINH NGAN SACH

    BEGIN

      --print 'haha'

      UPDATE PL_REQUEST_DOC

      SET PROCESS_ID = 'APPROVE'

      WHERE REQ_ID = @p_REQ_ID

      EXEC dbo.PL_REQ_DOC_UPDATE_AFTER_APPROVE @p_REQ_ID = @p_REQ_ID

      EXEC dbo.PL_REQ_DOC_Ins_To_TR_REQ_DOC @p_PL_REQ_ID = @p_REQ_ID

      SET @Result = '0'

    END

  END

  IF @@Error <> 0

    GOTO ABORT

  COMMIT TRANSACTION

  -- BAY GIỜ KHÔNG CẦN PHÂN CHIA XÁC NHẬN - DUYỆT NỮA. KHI CẤP LÃNH ĐẠO (GĐK, PTGĐ, TGĐ) BẤM VÀO NÚT DUYỆT THÌ THÔNG BÁO DUYỆT THÀNH CÔNG. KHÔNG CẦN QUAN TÂM CẤP CUỐI CÙNG

  SELECT

    '0' AS Result

   ,@ROLE_USER AS ROLE_NOTIFI

   ,'' ErrorDesc

  RETURN '0'

ABORT:

  BEGIN

    ROLLBACK TRANSACTION

    SELECT

      '-1' AS Result

     ,'' ROLE_NOTIFI

     ,ERROR_MESSAGE() ErrorDesc

    RETURN '-1'

  END