ALTER PROCEDURE [dbo].[CM_REQUEST_TEMPLATE_Search] @p_REQUEST_TEMPLATE_CODE VARCHAR(15) = NULL,
@p_REQUEST_TEMPLATE_NAME NVARCHAR(200) = NULL,
@p_NOTES NVARCHAR(1000) = NULL,
@p_RECORD_STATUS VARCHAR(1) = NULL,
@p_MAKER_ID VARCHAR(12) = NULL,
@p_CREATE_DT VARCHAR(20) = NULL,
@p_AUTH_STATUS VARCHAR(50) = NULL,
@p_CHECKER_ID VARCHAR(12) = NULL,
@p_APPROVE_DT VARCHAR(20) = NULL,
@p_MaxResultCount INT = NULL,
@p_SkipCount INT = NULL,
@p_Sorting VARCHAR(100) = NULL,
@p_TotalCount INT = NULL,
@p_REPORT_NO NVARCHAR(50) = NULL,
@p_REPORT_DT NVARCHAR(50) = NULL,
@p_TYPE_TEMPLATE NVARCHAR(50) = NULL,
@p_FROM_DT NVARCHAR(20) = NULL,
@p_TO_DT NVARCHAR(20) = NULL,
@p_TYPE_TEMPLATE_ID VARCHAR(20) = NULL,
@p_MAKER_FULLNAME NVARCHAR(50) = NULL,
@p_USER_LOGIN VARCHAR(20) = NULL,
@p_AUTH_STATUS_APP VARCHAR(20) = NULL,
@p_TITLE NVARCHAR(500) = NULL,
@p_USER_APPROVES NVARCHAR(500) = NULL,
@p_FROM_DATE VARCHAR(20) = NULL,
@p_TO_DATE VARCHAR(20) = NULL,
@p_TOP INT = NULL,
@p_BRANCH_ID VARCHAR(15) = NULL,
@p_DEP_ID VARCHAR(15) = NULL
AS
BEGIN -- PAGING
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  SET NOCOUNT ON;
  DECLARE @MENU_PERMISSION NVARCHAR(500) = 'Pages.Administration.TemplateRequestDoc' --permission của chức năng call store
  --NGUYENTD 19082024_SECRETKEY
  SELECT
    * INTO #LIST_ROLE
  FROM TL_USER_GET_ROLES(@p_USER_LOGIN, @MENU_PERMISSION)
  CREATE TABLE #CM_REQUEST_TEMPLATE (
    REQUEST_TEMPLATE_ID VARCHAR(100) NOT NULL
   ,REQUEST_TEMPLATE_NAME NVARCHAR(200) NULL
   ,RECORD_STATUS VARCHAR(1) NULL
   ,MAKER_ID VARCHAR(12) NULL
   ,CREATE_DT DATETIME NULL
   ,AUTH_STATUS VARCHAR(50) NULL
   ,CHECKER_ID VARCHAR(12) NULL
   ,APPROVE_DT DATETIME NULL
   ,CREATE_LOCATION NVARCHAR(500) NULL
   ,HEADER NVARCHAR(200) NULL
   ,BRANCH_ID NVARCHAR(50) NULL
   ,REPORT_NO NVARCHAR(50) NULL
   ,TITLE NVARCHAR(500) NULL
   ,REPORT_DT DATETIME NULL
   ,SCHEME VARCHAR(50) NULL
   ,TYPE_TEMPLATE_ID NVARCHAR(50) NULL
   ,CURRENT_STEP INT NULL
   ,IS_SENT_APPROVE BIT NULL
   ,IS_BACK_DATE BIT NULL
  )
  CREATE NONCLUSTERED INDEX idx_BR ON #CM_REQUEST_TEMPLATE (BRANCH_ID) WITH (FILLFACTOR = 80)
  CREATE NONCLUSTERED INDEX idx_REQ_ID ON #CM_REQUEST_TEMPLATE (REQUEST_TEMPLATE_ID) WITH (FILLFACTOR = 80)
  CREATE NONCLUSTERED INDEX idx_RECORD_STATUS ON #CM_REQUEST_TEMPLATE (RECORD_STATUS) WITH (FILLFACTOR = 80)
  --CREATE NONCLUSTERED INDEX idx_MAKER_ID ON #CM_REQUEST_TEMPLATE (MAKER_ID)WITH (FILLFACTOR = 80)
  CREATE NONCLUSTERED INDEX idx_TYPE_TEMPLATE_ID ON #CM_REQUEST_TEMPLATE (TYPE_TEMPLATE_ID) WITH (FILLFACTOR = 80)
  CREATE TABLE #AUTH_STATUS (
    REQ_ID VARCHAR(15)
   ,AUT_STATUS VARCHAR(1)
   ,TYPE VARCHAR(15)
  )

  CREATE NONCLUSTERED INDEX temp_ReqId ON #AUTH_STATUS (AUT_STATUS) WITH (FILLFACTOR = 80)
  CREATE NONCLUSTERED INDEX temp_AutStatus ON #AUTH_STATUS (REQ_ID) WITH (FILLFACTOR = 80)
  --DBCC SHOW_STATISTICS ('CM_DEPARTMENT', 'idx_BR_ID')


  INSERT INTO #CM_REQUEST_TEMPLATE
    SELECT
      A.REQUEST_TEMPLATE_ID
     ,A.REQUEST_TEMPLATE_NAME
     ,A.RECORD_STATUS
     ,A.MAKER_ID
     ,A.CREATE_DT
     ,A.AUTH_STATUS
     ,A.CHECKER_ID
     ,A.APPROVE_DT
     ,A.CREATE_LOCATION
     ,A.HEADER
     ,A.BRANCH_ID
     ,A.REPORT_NO
     ,A.TITLE
     ,A.REPORT_DT
     ,A.SCHEME
     ,A.TYPE_TEMPLATE_ID
     ,A.CURRENT_STEP
     ,A.IS_SENT_APPROVE
     ,A.IS_BACK_DATE
    FROM CM_REQUEST_TEMPLATE A
    WHERE 1 = 1
    AND (@p_REQUEST_TEMPLATE_NAME IS NULL
    OR @p_REQUEST_TEMPLATE_NAME = ''
    OR REQUEST_TEMPLATE_NAME LIKE '%' + @p_REQUEST_TEMPLATE_NAME + '%')
    AND (@p_TYPE_TEMPLATE_ID IS NULL
    OR @p_TYPE_TEMPLATE_ID = ''
    OR A.TYPE_TEMPLATE_ID LIKE '%' + @p_TYPE_TEMPLATE_ID + '%')
    --  	AND (@p_NOTES IS NULL OR @p_NOTES='' OR A.NOTES = @p_NOTES) 
    AND (@p_RECORD_STATUS IS NULL
    OR @p_RECORD_STATUS = ''
    OR A.RECORD_STATUS = @p_RECORD_STATUS)
    AND (@p_MAKER_ID IS NULL
    OR @p_MAKER_ID = ''
    OR A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%')
    AND (@p_CREATE_DT IS NULL
    OR DATEDIFF(DAYOFYEAR, A.CREATE_DT, CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0)
    --(@p_MAKER_ID IS NULL OR @p_MAKER_ID='' OR MAKER_ID = @p_MAKER_ID) AND
    AND (@p_AUTH_STATUS IS NULL
    OR @p_AUTH_STATUS = ''
    OR A.AUTH_STATUS = @p_AUTH_STATUS)
    AND (@p_CHECKER_ID IS NULL
    OR @p_CHECKER_ID = ''
    OR A.CHECKER_ID = @p_CHECKER_ID)
    AND (@p_APPROVE_DT IS NULL
    OR DATEDIFF(DAYOFYEAR, A.APPROVE_DT, CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0)
    AND (A.REPORT_DT >= CONVERT(DATE, @p_FROM_DATE, 103)
    OR @p_FROM_DATE IS NULL
    OR @p_FROM_DATE = '')
    AND (A.REPORT_DT <= CONVERT(DATE, @p_TO_DATE, 103)
    OR @p_TO_DATE IS NULL
    OR @p_TO_DATE = '')
    AND (REPORT_DT = CONVERT(DATETIME, @p_REPORT_DT, 103)
    OR @p_REPORT_DT IS NULL)
    AND (@p_TITLE IS NULL
    OR @p_TITLE = ''
    OR A.TITLE LIKE '%' + @p_TITLE + '%')
    AND (@p_REPORT_NO IS NULL
    OR @p_REPORT_NO = ''
    OR A.REPORT_NO LIKE '%' + @p_REPORT_NO + '%')
    AND ((A.RECORD_STATUS = '0'
    AND (A.MAKER_ID = @p_USER_LOGIN))
    OR A.RECORD_STATUS = '1')
    ORDER BY A.CREATE_DT DESC
  INSERT #AUTH_STATUS
    SELECT
      G.REQ_ID
     ,CASE
        WHEN DONE = 0 THEN 'U'
        WHEN DONE = 1 THEN 'A'
      END AS AUT_STATUS
     ,G.TYPE
    FROM CM_APPROVE_GROUP G
    INNER JOIN #CM_REQUEST_TEMPLATE
      ON REQUEST_TEMPLATE_ID = G.REQ_ID
    WHERE (DONE = 1
    OR PROCESS_STATUS = 1
    OR [TYPE] = 'AUT')
    AND (@p_USER_LOGIN = APPROVE_USERNAME
    OR @p_USER_LOGIN = AUTHORITY_NAME)
  CREATE TABLE #APPROVE_GROUP (
    REQ_ID VARCHAR(15)
   ,USER_APPROVES NVARCHAR(MAX)
  )
  INSERT #APPROVE_GROUP (REQ_ID, USER_APPROVES)
    SELECT
      G.REQ_ID
     ,STRING_AGG(G.APPROVE_USERNAME + ' - ' + u.TLFullName, ';') USER_APPROVES
    FROM CM_APPROVE_GROUP G
    JOIN TL_USER U
      ON G.APPROVE_USERNAME = U.TLNANME
    INNER JOIN #CM_REQUEST_TEMPLATE
      ON G.REQ_ID = REQUEST_TEMPLATE_ID
    WHERE PROCESS_STATUS = 1
    AND DONE = 0
    AND [TYPE] = 'APP'
    GROUP BY G.REQ_ID
  CREATE TABLE #SHARE (
    REQ_ID VARCHAR(15)
   ,SHARE_USERNAME VARCHAR(MAX)
  )
  INSERT #SHARE (REQ_ID,
  SHARE_USERNAME)
    SELECT
      REQUEST_TEMPLATE_ID
     ,STRING_AGG(SHARE_USERNAME, ';') SHARE_USERNAME
    FROM CM_TEMPLATE_SHARE
    INNER JOIN #CM_REQUEST_TEMPLATE
      ON REQ_ID = REQUEST_TEMPLATE_ID
    GROUP BY REQUEST_TEMPLATE_ID
  CREATE TABLE #BRANCH (
    BRANCH_ID VARCHAR(15)
  )
  INSERT #BRANCH
    SELECT
      DE.BRANCH_ID
    FROM CM_DEPARTMENT DE
    INNER JOIN #CM_REQUEST_TEMPLATE A
      ON DE.DEP_ID = A.BRANCH_ID
    GROUP BY DE.BRANCH_ID
  IF @P_TOP IS NULL
    OR @P_TOP = ''
  BEGIN

    -- PAGING BEGIN
    SELECT
      A.*
     ,B.CONTENT AS AUTH_STATUS_NAME
     ,C.CONTENT AS RECORD_STATUS_NAME
     ,TT.TYPE_TEMPLATE_NAME AS TYPE_TEMPLATE_NAME
     ,CASE
        WHEN BR.[BRANCH_ID] IS NOT NULL THEN BR.[BRANCH_NAME]
        ELSE (SELECT TOP 1
              D.[BRANCH_NAME] + ' - ' + C.[DEP_NAME]
            FROM CM_DEPARTMENT C
            LEFT JOIN CM_BRANCH D
              ON D.BRANCH_ID = C.BRANCH_ID
            WHERE C.DEP_ID = A.BRANCH_ID)
      END AS BRANCH_NAME--NGUYENTD 17032024_SECRETKEY: FIX TÌM KIẾM THEO ĐƠN VỊ
     ,U.TLFullName AS MAKER_FULLNAME
     ,AUTH.CDVAL AS AUTH_STATUS_APP
     ,AUTH.CONTENT AS AUTH_STATUS_APP_NAME
     ,GR.USER_APPROVES
     ,SHARE.SHARE_USERNAME
    -- SELECT END
    FROM #CM_REQUEST_TEMPLATE A
    LEFT JOIN CM_ALLCODE C
      ON C.CDNAME = 'RECORD_STATUS'
        AND C.CDVAL = A.RECORD_STATUS
    LEFT JOIN CM_TYPE_TEMPLATE TT
      ON TT.TYPE_TEMPLATE_ID = A.TYPE_TEMPLATE_ID
    LEFT JOIN #BRANCH CA
      ON CA.BRANCH_ID = A.BRANCH_ID --NGUYENTD 17032024_SECRETKEY: FIX TÌM KIẾM THEO ĐƠN VỊ TẠO
    LEFT JOIN CM_BRANCH BR
      ON BR.BRANCH_ID = A.BRANCH_ID
    LEFT JOIN CM_DEPARTMENT CD
      ON CD.DEP_ID = A.BRANCH_ID
    LEFT JOIN TL_USER U
      ON U.TLNANME = A.MAKER_ID
    --WORKFLOW
    LEFT JOIN #AUTH_STATUS AG
      ON AG.REQ_ID = A.REQUEST_TEMPLATE_ID
        AND TYPE <> 'AUT'
    LEFT JOIN dbo.CM_ALLCODE AUTH
      ON AUTH.CDVAL = AG.AUT_STATUS
        AND AUTH.CDNAME = 'AUTH_STATUS_APP'
    LEFT JOIN dbo.CM_ALLCODE B
      ON B.CDVAL = A.AUTH_STATUS
        AND B.CDNAME = 'AUTH_STATUS_ONL'
    LEFT JOIN #APPROVE_GROUP GR
      ON GR.REQ_ID = A.REQUEST_TEMPLATE_ID
    --END WORKFLOW
    LEFT JOIN #SHARE SHARE
      ON SHARE.REQ_ID = A.REQUEST_TEMPLATE_ID
    WHERE 1 = 1
    AND (@p_MAKER_FULLNAME IS NULL
    OR @p_MAKER_FULLNAME = ''
    OR U.TLFullName LIKE '%' + @p_MAKER_FULLNAME + '%'
    OR U.TLNANME LIKE '%' + @p_MAKER_FULLNAME + '%')
    AND (@p_AUTH_STATUS_APP IS NULL
    OR @p_AUTH_STATUS_APP = ''
    OR AUTH.CDVAL = @p_AUTH_STATUS_APP)
    AND (@p_AUTH_STATUS IS NULL
    OR @p_AUTH_STATUS = ''
    OR AG.AUT_STATUS = @p_AUTH_STATUS)
    --WORKFLOW
    AND (@p_USER_APPROVES IS NULL
    OR @p_USER_APPROVES = ''
    OR GR.USER_APPROVES LIKE '%' + @p_USER_APPROVES + '%')
    AND (
    (A.MAKER_ID = @p_USER_LOGIN)
    OR (@p_USER_LOGIN = 'baotq')--NGUYENTD 27122023_SECRETKEY: Y/C CỦA ANH BẢO
    OR (EXISTS (SELECT
        1
      FROM #AUTH_STATUS [as]
      WHERE [as].REQ_ID = A.REQUEST_TEMPLATE_ID)
    )
    OR EXISTS (SELECT
        1
      FROM CM_TEMPLATE_SHARE
      WHERE SHARE_USERNAME = @p_USER_LOGIN
      AND REQ_ID = A.REQUEST_TEMPLATE_ID)
    OR (EXISTS (SELECT TOP (1)
        1
      FROM #LIST_ROLE USERROLE
      WHERE USERROLE.ROLE_ID = 'ADMIN_TO_TRINH')
    )
    --OR (dbo.FN_CHECK_ROLE_USER(@p_USER_LOGIN,'ADMIN_TO_TRINH')=1)
    OR (EXISTS (SELECT TOP (1)
        1
      FROM #LIST_ROLE USERROLE
      WHERE USERROLE.ROLE_ID = 'GDDV'
      AND (
      EXISTS (SELECT TOP (1)
          1
        FROM TL_USER US
        WHERE US.TLNANME = A.MAKER_ID
        AND (USERROLE.BRANCH_ID = US.TLSUBBRID
        OR USERROLE.DEPT_ID = US.DEP_ID)
        AND US.TLSUBBRID <> 'DV0001')
      OR EXISTS (SELECT TOP (1)
          1
        FROM TL_USER US
        WHERE US.TLNANME = A.MAKER_ID
        AND USERROLE.DEPT_ID = US.DEP_ID)
      ))
    )
    )
    --NGUYENTD 17032024_SECRETKEY: FIX TÌM KIẾM THEO ĐƠN VỊ TẠO
    AND (BR.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%'
    OR @p_BRANCH_ID IS NULL
    OR @p_BRANCH_ID = '')
    AND (A.BRANCH_ID LIKE '%' + @p_DEP_ID + '%'
    OR @p_DEP_ID IS NULL
    OR @p_DEP_ID = '')
  --END NGUYENTD
  --END WORKFLOW
  -- PAGING END
  END
  ELSE
  BEGIN
    -- PAGING BEGIN
    SELECT TOP (@P_TOP)
      A.*
     ,B.CONTENT AS AUTH_STATUS_NAME
     ,C.CONTENT AS RECORD_STATUS_NAME
     ,TT.TYPE_TEMPLATE_NAME AS TYPE_TEMPLATE_NAME
     ,CASE
        WHEN BR.[BRANCH_ID] IS NOT NULL THEN BR.[BRANCH_NAME]
        ELSE (SELECT TOP 1
              D.[BRANCH_NAME] + ' - ' + C.[DEP_NAME]
            FROM CM_DEPARTMENT C
            LEFT JOIN CM_BRANCH D
              ON D.BRANCH_ID = C.BRANCH_ID
            WHERE C.DEP_ID = A.BRANCH_ID)
      END AS BRANCH_NAME
     ,U.TLFullName AS MAKER_FULLNAME
     ,AUTH.CDVAL AS AUTH_STATUS_APP
     ,AUTH.CONTENT AS AUTH_STATUS_APP_NAME
     ,GR.USER_APPROVES
     ,SHARE.SHARE_USERNAME
    -- SELECT END
    FROM #CM_REQUEST_TEMPLATE A
    LEFT JOIN CM_ALLCODE C
      ON C.CDNAME = 'RECORD_STATUS'
        AND C.CDVAL = A.RECORD_STATUS
    LEFT JOIN CM_TYPE_TEMPLATE TT
      ON TT.TYPE_TEMPLATE_ID = A.TYPE_TEMPLATE_ID
    LEFT JOIN #BRANCH CA
      ON CA.BRANCH_ID = A.BRANCH_ID --NGUYENTD 17032024_SECRETKEY: FIX TÌM KIẾM THEO ĐƠN VỊ
    LEFT JOIN CM_BRANCH BR
      ON BR.BRANCH_ID = A.BRANCH_ID
    LEFT JOIN CM_DEPARTMENT CD
      ON CD.DEP_ID = A.BRANCH_ID
    LEFT JOIN TL_USER U
      ON U.TLNANME = A.MAKER_ID
    --WORKFLOW
    LEFT JOIN #AUTH_STATUS AG
      ON AG.REQ_ID = A.REQUEST_TEMPLATE_ID
        AND TYPE <> 'AUT'
    LEFT JOIN dbo.CM_ALLCODE AUTH
      ON AUTH.CDVAL = AG.AUT_STATUS
        AND AUTH.CDNAME = 'AUTH_STATUS_APP'
    LEFT JOIN dbo.CM_ALLCODE B
      ON B.CDVAL = A.AUTH_STATUS
        AND B.CDNAME = 'AUTH_STATUS_ONL'
    LEFT JOIN #APPROVE_GROUP GR
      ON GR.REQ_ID = A.REQUEST_TEMPLATE_ID
    --END WORKFLOW
    LEFT JOIN #SHARE SHARE
      ON SHARE.REQ_ID = A.REQUEST_TEMPLATE_ID
    WHERE 1 = 1
    AND (@p_MAKER_FULLNAME IS NULL
    OR @p_MAKER_FULLNAME = ''
    OR U.TLFullName LIKE '%' + @p_MAKER_FULLNAME + '%'
    OR U.TLNANME LIKE '%' + @p_MAKER_FULLNAME + '%')
    AND (@p_AUTH_STATUS_APP IS NULL
    OR @p_AUTH_STATUS_APP = ''
    OR AUTH.CDVAL = @p_AUTH_STATUS_APP)
    AND (@p_AUTH_STATUS IS NULL
    OR @p_AUTH_STATUS = ''
    OR AG.AUT_STATUS = @p_AUTH_STATUS)
    --WORKFLOW
    AND (@p_USER_APPROVES IS NULL
    OR @p_USER_APPROVES = ''
    OR GR.USER_APPROVES LIKE '%' + @p_USER_APPROVES + '%')
    AND (
    (A.MAKER_ID = @p_USER_LOGIN)
    OR (@p_USER_LOGIN = 'baotq')--NGUYENTD 27122023_SECRETKEY: Y/C CỦA ANH BẢO
    OR (EXISTS (SELECT
        1
      FROM #AUTH_STATUS [as]
      WHERE [as].REQ_ID = A.REQUEST_TEMPLATE_ID)
    )
    OR EXISTS (SELECT
        1
      FROM CM_TEMPLATE_SHARE
      WHERE SHARE_USERNAME = @p_USER_LOGIN
      AND REQ_ID = A.REQUEST_TEMPLATE_ID)
    OR (EXISTS (SELECT TOP (1)
        1
      FROM #LIST_ROLE USERROLE
      WHERE USERROLE.ROLE_ID = 'ADMIN_TO_TRINH')
    )
    --OR (dbo.FN_CHECK_ROLE_USER(@p_USER_LOGIN,'ADMIN_TO_TRINH')=1)
    OR (EXISTS (SELECT TOP (1)
        1
      FROM #LIST_ROLE USERROLE
      WHERE USERROLE.ROLE_ID = 'GDDV'
      AND (
      EXISTS (SELECT TOP (1)
          1
        FROM TL_USER US
        WHERE US.TLNANME = A.MAKER_ID
        AND (USERROLE.BRANCH_ID = US.TLSUBBRID
        OR USERROLE.DEPT_ID = US.DEP_ID)
        AND US.TLSUBBRID <> 'DV0001')
      OR EXISTS (SELECT TOP (1)
          1
        FROM TL_USER US
        WHERE US.TLNANME = A.MAKER_ID
        AND USERROLE.DEPT_ID = US.DEP_ID)
      ))
    )
    )
    --NGUYENTD 17032024_SECRETKEY: FIX TÌM KIẾM THEO ĐƠN VỊ TẠO
    AND (BR.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%'
    OR @p_BRANCH_ID IS NULL
    OR @p_BRANCH_ID = '')
    AND (A.BRANCH_ID LIKE '%' + @p_DEP_ID + '%'
    OR @p_DEP_ID IS NULL
    OR @p_DEP_ID = '')
  --END NGUYENTD
  --END WORKFLOW
  -- PAGING END
  END
END -- PAGING
  DROP TABLE #LIST_ROLE
  DROP TABLE #AUTH_STATUS
  DROP TABLE #CM_REQUEST_TEMPLATE
  DROP TABLE #SHARE
  DROP TABLE #APPROVE_GROUP
  DROP TABLE #BRANCH