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; 
    --NGUYENTD 19082024_SECRETKEY
    SELECT * INTO #LIST_ROLE FROM TL_USER_GET_ROLES(@p_USER_LOGIN)
    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
	,(BR.[BRANCH_NAME] + ' - ' + CD.[DEP_NAME]) AS BRANCH_NAME--NGUYENTD 17032024_SECRETKEY: FIX TÌM KIẾM THEO ĐƠN VỊ
	,U.TLFullName AS MAKER_FULLNAME,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 AG.AUT_STATUS = @p_AUTH_STATUS_APP)
	--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,(BR.[BRANCH_NAME] + ' - ' + CD.[DEP_NAME]) AS BRANCH_NAME
	,U.TLFullName AS MAKER_FULLNAME	,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 AG.AUT_STATUS = @p_AUTH_STATUS_APP)
	--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
--20122024_SECRETKEY