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