ALTER PROCEDURE dbo.TL_USER_KSV_XL_Search
@p_TLID	varchar(15)  = NULL,
@p_TLNANME	varchar(15)  = NULL,
@p_Password	varchar(50)  = NULL,
@p_TLFullName	nvarchar(200)  = NULL,
@p_TLSUBBRID	varchar(15)  = NULL,
@p_BRANCH_LOGIN	varchar(15)  = NULL,

@p_BRANCH_ID	varchar(15)  = NULL,
@p_BRANCH_NAME	nvarchar(200)  = NULL,
@p_BRANCH_TYPE	varchar(5)  = NULL,
@p_RoleName	varchar(20)  = NULL,
@p_EMAIL	varchar(50)  = NULL,
@p_ADDRESS	varchar(100)  = NULL,
@p_PHONE	varchar(15)  = NULL,
@p_AUTH_STATUS	varchar(1)  = NULL,
@p_MARKER_ID	varchar(12)  = NULL,
@p_AUTH_ID	varchar(12)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_ISAPPROVE	varchar(1)  = NULL,
@p_Birthday	VARCHAR(20) = NULL,
@p_ISFIRSTTIME	varchar(1)  = NULL,
@p_SECUR_CODE	varchar(50)  = NULL,
@p_DEP_ID	varchar(15)  = NULL,
@p_TYPE VARCHAR(15) = NULL,
@p_TOP	INT = 10,
@P_LEVEL varchar(10) = NULL
AS
	--Validation is here
/*
	DECLARE @ERRORSYS NVARCHAR(15) = '' 
	IF ( [NOT] EXISTS ( SELECT * FROM [TABLE] WHERE  [CONDITION] ))
		 SET @ERRORSYS = ''
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END */

BEGIN -- PAGING

BEGIN TRANSACTION
SET @p_BRANCH_LOGIN = @p_BRANCH_ID
-- Toannv
DECLARE @BRANCH_ID VARCHAR(15)
declare @tmp table(BRANCH_ID varchar(15))
declare @tmp_AUTH table(BRANCH_ID varchar(15))
IF(@p_SECUR_CODE IS NOT NULL AND @p_SECUR_CODE <> '')
BEGIN
	SET @BRANCH_ID =ISNULL((SELECT BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID=@p_SECUR_CODE),'')
	SET @p_TLSUBBRID = @BRANCH_ID
END

insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_TLSUBBRID)
INSERT INTO @tmp_AUTH SELECT @p_TLSUBBRID
---- INSERT KIÊM NHIỆM ĐƠN VỊ
INSERT INTO @tmp_AUTH SELECT BRANCH_ID FROM TL_SYS_ROLE_MAPPING TRM
							WHERE 1=1
							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
							AND TLNAME =@p_MARKER_ID
---- INSERT KIÊM NHIỆM ĐƠN VỊ
IF(@p_SECUR_CODE IS NOT NULL AND @p_SECUR_CODE <> '') SET @p_DEP_ID = @p_SECUR_CODE
IF(@p_DEP_ID IS NOT NULL AND @p_DEP_ID <> '') SET @p_SECUR_CODE = @p_DEP_ID

--SET @p_SECUR_CODE = NUll
--IF(@p_MARKER_ID IS NULL OR @p_MARKER_ID ='')
--BEGIN
--	SET @p_MARKER_ID ='sangnm1'
--END

-----------------BAODNQ 17/11/2022 : BẢNG TẠM LƯU DEP_ID---------------------
DECLARE @t_DEP_ID_TMP TABLE(DEP_ID VARCHAR(15))
DECLARE @p_DEP_ID_TMP VARCHAR(15)

---------------NẾU CÓ TRUYỀN SECUR_CODE, LẤY RA NHỮNG DEP_ID THỎA ĐK:---------------
---------------1.PHÒNG BAN TRUYỀN VÀO, 2.PHÒNG BAN CON CỦA PHÒNG BAN TRUYỀN VÀO, 
---------------3.PHÒNG BAN ĐANG DC USER_LOGIN KIÊM NHIỆM GDDV, 4.PHÒNG BAN CON CỦA PHÒNG BAN DC USER_LOGIN KIÊM NHIỆM
IF(@p_SECUR_CODE IS NOT NULL AND @p_SECUR_CODE <> '')
BEGIN
	SET @p_DEP_ID_TMP = @p_SECUR_CODE

	PRINT @p_DEP_ID_TMP

	INSERT INTO @t_DEP_ID_TMP
	--------TH1 : DEP_ID TRUYỀN VÀO-----------
		SELECT @p_DEP_ID_TMP
		UNION
	--------TH2 : PHÒNG BAN CON CỦA DEP_ID TRUYỀN VÀO---------
		SELECT DEP_ID FROM CM_DEPARTMENT CD WHERE CD.FATHER_ID = @p_DEP_ID_TMP
		UNION
	--------TH3 : PHÒNG BAN ĐANG DC USER_LOGIN KIÊM NHIỆM GDDV-----------
		SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING TSM 
		WHERE TSM.TLNAME = @p_MARKER_ID AND ROLE_NEW = 'GDDV'
			AND CONVERT(DATE,GETDATE(),103) >= TSM.EFF_DATE 
			AND CONVERT(DATE,GETDATE(),103) <= TSM.EXP_DATE
		UNION
	---------TH4 : PHÒNG BAN CON CỦA PHÒNG BAN ĐANG DC USER_LOGIN KIÊM NHIỆM GDDV-----------
		SELECT CDF.DEP_ID FROM TL_SYS_ROLE_MAPPING TSMF 
		INNER JOIN CM_DEPARTMENT CDF ON CDF.FATHER_ID = TSMF.DEP_ID
		WHERE TSMF.TLNAME = @p_MARKER_ID
			AND CONVERT(DATE,GETDATE(),103) >= TSMF.EFF_DATE 
			AND CONVERT(DATE,GETDATE(),103) <= TSMF.EXP_DATE
END
-------------------ENDBAODNQ 17/11/2022--------------------

IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
-- PAGING BEGIN
	SELECT A.*, B.AUTH_STATUS_NAME,D.BRANCH_ID, D.BRANCH_CODE,
			DE.DEP_NAME,DE.DEP_CODE,
			dbo.FN_GET_CHINHANH(D.BRANCH_ID,'KV') KHU_VUC,
			dbo.FN_GET_CHINHANH(D.BRANCH_ID,'CN') CHI_NHANH, 
			D.BRANCH_NAME PGD, D.TAX_NO,E.POS_NAME, E.EMP_CODE,
			--STUFF( (select ',' + R.[Name] FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') RoleName2
			T.RoleName2
			, CMA.ACC_NUM, CMA.ACC_NAME, CMA.ACC_TYPE,
			D.BRANCH_NAME AS BR_NAME, D.BRANCH_CODE + '-'+ D.BRANCH_NAME AS BR_FULL_NAME,
		    DE.DEP_CODE +'-'+ DE.DEP_NAME AS DP_FULL_NAME,
			K.DVDM_ID AS KHOI_ID, K.DVDM_CODE AS KHOI_CODE, K.DVDM_NAME AS KHOI_NAME
	-- SELECT END
	FROM TL_USER A
		INNER JOIN CM_AUTH_STATUS B ON B.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH D ON  A.TLSUBBRID = D.BRANCH_ID
		LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
		--LEFT JOIN AbpUserRoles UR  ON A.ID = UR.UserId
		--LEFT JOIN AbpRoles R ON R.Id = UR.RoleId
		-- GIANT 16/09/2021
		LEFT JOIN CM_EMPLOYEE_LOG E ON A.TLNANME = E.USER_DOMAIN
		LEFT JOIN CM_ACCOUNT_PAY CMA ON A.TLNANME = CMA.REF_ID
		INNER JOIN
		(SELECT A2.ID,
			(SELECT stuff((SELECT ',' + CONVERT(VARCHAR(MAX),R1.[DisplayName])
			FROM TL_USER A1
				LEFT JOIN AbpUserRoles UR1  ON A1.ID = UR1.UserId
				LEFT JOIN AbpRoles R1 ON R1.Id = UR1.RoleId
			WHERE A1.ID=A2.ID AND ((R1.DisplayName IN ('NVTT','QLK') AND @p_TYPE = 'XL') OR (R1.DisplayName IN ('GDDV','TBP') AND @p_TYPE = 'KSV'))
			FOR XML PATH ('')
			), 1, 1, '')) AS [RoleName2]
		FROM TL_USER A2
		GROUP BY A2.ID) T ON T.ID=A.ID
		LEFT JOIN CM_DVDM K ON DE.KHOI_ID = K.DVDM_ID AND K.IS_KHOI = '1'
	 WHERE 1 = 1
		AND (A.TLID LIKE '%' + @p_TLID + '%' OR  @p_TLID IS NULL OR @p_TLID = '')
		AND (A.TLNANME LIKE '%' + @p_TLNANME + '%' OR  @p_TLNANME IS NULL OR @p_TLNANME = '')
		AND (A.Password LIKE '%' + @p_Password + '%' OR  @p_Password IS NULL OR @p_Password = '')
		AND (A.TLFullName LIKE '%' + @p_TLFullName + '%' OR  @p_TLFullName IS NULL OR @p_TLFullName = '')
		--AND (A.TLSUBBRID LIKE '%' + @p_TLSUBBRID + '%' OR  @p_TLSUBBRID IS NULL OR @p_TLSUBBRID = '')
		AND (A.BRANCH_NAME LIKE '%' + @p_BRANCH_NAME + '%' OR  @p_BRANCH_NAME IS NULL OR @p_BRANCH_NAME = '')
		AND (A.BRANCH_TYPE LIKE '%' + @p_BRANCH_TYPE + '%' OR  @p_BRANCH_TYPE IS NULL OR @p_BRANCH_TYPE = '')
    AND (A.TLSUBBRID = @p_BRANCH_LOGIN OR @p_BRANCH_LOGIN IS NULL OR @p_BRANCH_LOGIN = '')
		--AND (
		--	(A.DEP_ID LIKE '%' + @p_DEP_ID + '%'
		--		--OR (SELECT TOP 1 TRM.DEP_ID FROM TL_SYS_ROLE_MAPPING TRM 
		--		--	WHERE TRM.TLNAME = A.TLNANME 
		--		--	AND GETDATE() >= TRM.EFF_DATE AND GETDATE() <= TRM.EXP_DATE
		--		--	) LIKE '%' + @p_DEP_ID + '%'
		--		----------BAODNQ 30/9/2022 : Thêm xử lý tìm kiếm ủy quyền kiêm nhiệm--------
		--		OR EXISTS(SELECT * FROM TL_SYS_ROLE_MAPPING TRM 
		--					WHERE TRM.TLNAME = A.TLNANME 
		--					AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
		--					AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
		--					AND TRM.DEP_ID = @p_DEP_ID
		--			)
		--		OR A.DEP_ID IN (SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING TRM WHERE 1=1
		--					AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
		--					AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
		--					AND TRM.TLNAME = @p_MARKER_ID)
		--		OR (@p_TLSUBBRID <> 'DV0001' AND A.TLSUBBRID =@p_TLSUBBRID)
		--		OR (@BRANCH_ID <> 'DV0001' AND A.TLSUBBRID =@BRANCH_ID)
		--	)
		--	OR (@p_DEP_ID IS NULL OR @p_DEP_ID = '' OR @p_SECUR_CODE IS NULL OR @p_SECUR_CODE ='')
		--	OR (A.SECUR_CODE IN (SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@p_SECUR_CODE))
		--	OR (A.SECUR_CODE IN (SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@p_DEP_ID))
		--)
		--------------BAODNQ 17/11/2022 : CHỈNH SỬA LẠI ĐIỀU KIỆN TÌM KIẾM THEO DEP_ID-------------------
		AND(
--			(A.DEP_ID IN (SELECT DEP_ID FROM @t_DEP_ID_TMP)
--				OR EXISTS(SELECT * FROM TL_SYS_ROLE_MAPPING TRM 
--							WHERE TRM.TLNAME = A.TLNANME 
--							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
--							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
--							AND TRM.DEP_ID  IN (SELECT DEP_ID FROM @t_DEP_ID_TMP)
--					)
--			)
--			OR (@p_DEP_ID IS NULL OR @p_DEP_ID = '')
      	(((A.DEP_ID IN (SELECT DEP_ID FROM @t_DEP_ID_TMP)
				OR EXISTS(SELECT * FROM TL_SYS_ROLE_MAPPING TRM 
							WHERE TRM.TLNAME = A.TLNANME 
							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
							AND TRM.DEP_ID IN (SELECT DEP_ID FROM @t_DEP_ID_TMP)
					)
			)
			OR(@p_DEP_ID IS NULL OR @p_DEP_ID = '')) AND A.TLSUBBRID = 'DV0001')
      OR (A.TLSUBBRID <> 'DV0001')
		)
		-------------ENDBAODNQ----------------------
		AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR  @p_EMAIL IS NULL OR @p_EMAIL = '')
		AND (A.ADDRESS LIKE '%' + @p_ADDRESS + '%' OR  @p_ADDRESS IS NULL OR @p_ADDRESS = '')
		AND (A.PHONE LIKE '%' + @p_PHONE + '%' OR  @p_PHONE IS NULL OR @p_PHONE = '')
		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		--AND (A.MARKER_ID LIKE '%' + @p_MARKER_ID + '%' OR  @p_MARKER_ID IS NULL OR @p_MARKER_ID = '')
		AND (A.AUTH_ID LIKE '%' + @p_AUTH_ID + '%' OR  @p_AUTH_ID IS NULL OR @p_AUTH_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		AND (A.ISAPPROVE LIKE '%' + @p_ISAPPROVE + '%' OR  @p_ISAPPROVE IS NULL OR @p_ISAPPROVE = '')
		AND (DATEDIFF(DAY,A.Birthday ,CONVERT(DATETIME, @p_Birthday, 103)) = 0 OR  @p_Birthday IS NULL OR @p_Birthday = '')
		AND (A.ISFIRSTTIME LIKE '%' + @p_ISFIRSTTIME + '%' OR  @p_ISFIRSTTIME IS NULL OR @p_ISFIRSTTIME = '')
--		AND (A.RoleName <> 'DISABLE' OR A.RoleName IS NULL)
    AND T.RoleName2 <> 'DISABLE' -- NGUYENTD 22042024_SECRETKEY: CHECK ROLE Ở BẢNG ROLE THAY VÌ BẢNG TL_USER
		--AND (
		--	(A.SECUR_CODE LIKE '%' + @p_SECUR_CODE + '%'
		--		--OR (SELECT TOP 1 TRM.DEP_ID FROM TL_SYS_ROLE_MAPPING TRM 
		--		--	WHERE TRM.TLNAME = A.TLNANME 
		--		--	AND GETDATE() >= TRM.EFF_DATE AND GETDATE() <= TRM.EXP_DATE
		--		--	) LIKE '%' + @p_SECUR_CODE + '%'
		--		----------BAODNQ 30/9/2022 : Thêm xử lý tìm kiếm ủy quyền kiêm nhiệm--------
		--		OR EXISTS(SELECT * FROM TL_SYS_ROLE_MAPPING TRM 
		--					WHERE TRM.TLNAME = A.TLNANME 
		--					AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
		--					AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
		--					AND TRM.DEP_ID = @p_SECUR_CODE
		--			)
		--		OR (@p_TLSUBBRID <> 'DV0001' AND A.TLSUBBRID =@p_TLSUBBRID)
		--		OR (@BRANCH_ID <> 'DV0001' AND A.TLSUBBRID =@BRANCH_ID)
		--	) 
		--	OR (@p_DEP_ID IS NULL OR @p_DEP_ID = '' OR @p_SECUR_CODE IS NULL OR @p_SECUR_CODE ='')
		--	OR (A.SECUR_CODE IN (SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@p_SECUR_CODE))
		--	OR (A.SECUR_CODE IN (SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@p_DEP_ID))
		--)
		--------------BAODNQ 17/11/2022 : CHỈNH SỬA LẠI ĐIỀU KIỆN TÌM KIẾM THEO SECUR_CODE-------------------
		AND(
			(((A.SECUR_CODE IN (SELECT DEP_ID FROM @t_DEP_ID_TMP)
				OR EXISTS(SELECT * FROM TL_SYS_ROLE_MAPPING TRM 
							WHERE TRM.TLNAME = A.TLNANME 
							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
							AND TRM.DEP_ID  IN (SELECT DEP_ID FROM @t_DEP_ID_TMP)
					)
			)
			OR (@p_SECUR_CODE IS NULL OR @p_SECUR_CODE = '')) AND A.TLSUBBRID = 'DV0001')
      OR (A.TLSUBBRID <> 'DV0001')
		)
		--------------ENDBAODNQ-------------------
		AND (A.TLNANME != 'admin')
		--AND (A.TLSUBBRID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_TLSUBBRID)))
		--AND ((@P_LEVEL = 'ALL' AND A.TLSUBBRID IN (SELECT BRANCH_ID from @tmp)
		--OR (@P_LEVEL = 'UNIT' AND A.TLSUBBRID = @p_TLSUBBRID)) OR @p_TLSUBBRID = '' OR @p_TLSUBBRID IS NULL

		--)
		AND(
			(@P_LEVEL = 'ALL'
				AND (A.TLSUBBRID IN (SELECT BRANCH_ID from @tmp)
					--OR (SELECT TOP 1 TRM.BRANCH_ID
					--		FROM TL_SYS_ROLE_MAPPING TRM
					--		WHERE TRM.TLNAME = A.TLNANME
					--		AND GETDATE() >= TRM.EFF_DATE AND GETDATE() <= TRM.EXP_DATE
					--	) IN (SELECT BRANCH_ID from @tmp)
					----------BAODNQ 30/9/2022 : Thêm xử lý tìm kiếm ủy quyền kiêm nhiệm--------
					OR EXISTS(SELECT *
							FROM TL_SYS_ROLE_MAPPING TRM
							WHERE 1=1
							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
							AND TRM.BRANCH_ID IN (SELECT BRANCH_ID from @tmp)
						)
					OR (@BRANCH_ID <> 'DV0001' AND A.TLSUBBRID =@BRANCH_ID)
					)
			)
			OR(@P_LEVEL = 'UNIT'
				AND (A.TLSUBBRID = @p_TLSUBBRID OR A.TLSUBBRID IN (SELECT * FROM @tmp_AUTH)
					--OR (SELECT TOP 1 TRM.BRANCH_ID
					--		FROM TL_SYS_ROLE_MAPPING TRM
					--		WHERE TRM.TLNAME = A.TLNANME
					--		AND GETDATE() >= TRM.EFF_DATE AND GETDATE() <= TRM.EXP_DATE
					--	) = @p_TLSUBBRID
					----------BAODNQ 30/9/2022 : Thêm xử lý tìm kiếm ủy quyền kiêm nhiệm--------
					OR EXISTS(SELECT *
							FROM TL_SYS_ROLE_MAPPING TRM
							WHERE TRM.TLNAME = A.TLNANME
							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
							AND TRM.BRANCH_ID = @p_TLSUBBRID
						)
						OR A.DEP_ID IN (SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING TRM 
							WHERE 1=1
							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
							AND TRM.TLNAME = @p_MARKER_ID)
						OR (@BRANCH_ID <> 'DV0001' AND A.TLSUBBRID =@BRANCH_ID)
					)
			)
			--OR @BRANCH_ID = '' OR @BRANCH_ID IS NULL
			OR(@p_TLSUBBRID IS NULL OR @p_TLSUBBRID = '')
			--OR (A.SECUR_CODE IN (SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@p_SECUR_CODE))
			--OR (A.SECUR_CODE IN (SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@p_DEP_ID))
		)
	-- PAGING END
ELSE

-- PAGING BEGIN
	SELECT TOP(CONVERT(INT,@P_TOP))A.*, B.AUTH_STATUS_NAME,D.BRANCH_ID, D.BRANCH_CODE,
				DE.DEP_NAME,DE.DEP_CODE,
				dbo.FN_GET_CHINHANH(D.BRANCH_ID,'KV') KHU_VUC,
				dbo.FN_GET_CHINHANH(D.BRANCH_ID,'CN') CHI_NHANH, 
				D.BRANCH_NAME PGD, D.TAX_NO,E.POS_NAME, E.EMP_CODE,
				--STUFF( (select ',' + R.[Name] FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') RoleName2
				T.RoleName2, CMA.ACC_NUM, CMA.ACC_NAME, CMA.ACC_TYPE,
				D.BRANCH_NAME AS BR_NAME, D.BRANCH_CODE + '-'+ D.BRANCH_NAME AS BR_FULL_NAME,
				DE.DEP_CODE +'-'+ DE.DEP_NAME AS DP_FULL_NAME,
				K.DVDM_ID AS KHOI_ID, K.DVDM_CODE AS KHOI_CODE, K.DVDM_NAME AS KHOI_NAME
			-- SELECT END
	FROM TL_USER A
		INNER JOIN CM_AUTH_STATUS B ON B.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH D ON  D.BRANCH_ID = A.TLSUBBRID
		LEFT JOIN CM_DEPARTMENT DE ON A.DEP_ID = DE.DEP_ID
		--LEFT JOIN AbpUserRoles UR  ON A.ID = UR.UserId
		--LEFT JOIN AbpRoles R ON R.Id = UR.RoleId
		-- GIANT 16/09/2021
		LEFT JOIN CM_EMPLOYEE_LOG E ON A.TLNANME = E.USER_DOMAIN
		LEFT JOIN CM_ACCOUNT_PAY CMA ON A.TLNANME = CMA.REF_ID
		INNER JOIN
		(SELECT A2.ID,
			(SELECT stuff((SELECT ',' + CONVERT(VARCHAR(MAX),R1.[DisplayName])
			FROM TL_USER A1
				LEFT JOIN AbpUserRoles UR1  ON A1.ID = UR1.UserId
				LEFT JOIN AbpRoles R1 ON R1.Id = UR1.RoleId
			WHERE A1.ID=A2.ID AND ((R1.DisplayName IN ('NVTT','QLK')  AND @p_TYPE = 'XL') OR (R1.DisplayName IN ('GDDV','TBP') AND @p_TYPE = 'KSV'))
			FOR XML PATH ('')
			), 1, 1, '')) AS [RoleName2]
		FROM TL_USER A2
		GROUP BY A2.ID) T ON T.ID=A.ID
		LEFT JOIN CM_DVDM K ON DE.KHOI_ID = K.DVDM_ID AND K.IS_KHOI = '1'

	 WHERE 1 = 1
		AND (A.TLID LIKE '%' + @p_TLID + '%' OR  @p_TLID IS NULL OR @p_TLID = '')
		AND (A.TLNANME LIKE '%' + @p_TLNANME + '%' OR  @p_TLNANME IS NULL OR @p_TLNANME = '')
		AND (A.Password LIKE '%' + @p_Password + '%' OR  @p_Password IS NULL OR @p_Password = '')
		AND (A.TLFullName LIKE '%' + @p_TLFullName + '%' OR  @p_TLFullName IS NULL OR @p_TLFullName = '')
		--AND (A.TLSUBBRID LIKE '%' + @p_TLSUBBRID + '%' OR  @p_TLSUBBRID IS NULL OR @p_TLSUBBRID = '')
		AND (A.BRANCH_NAME LIKE '%' + @p_BRANCH_NAME + '%' OR  @p_BRANCH_NAME IS NULL OR @p_BRANCH_NAME = '')
		AND (A.BRANCH_TYPE LIKE '%' + @p_BRANCH_TYPE + '%' OR  @p_BRANCH_TYPE IS NULL OR @p_BRANCH_TYPE = '')
		AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR  @p_EMAIL IS NULL OR @p_EMAIL = '')
		AND (A.ADDRESS LIKE '%' + @p_ADDRESS + '%' OR  @p_ADDRESS IS NULL OR @p_ADDRESS = '')
		AND (A.PHONE LIKE '%' + @p_PHONE + '%' OR  @p_PHONE IS NULL OR @p_PHONE = '')
		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		--AND (A.MARKER_ID LIKE '%' + @p_MARKER_ID + '%' OR  @p_MARKER_ID IS NULL OR @p_MARKER_ID = '')
		AND (A.AUTH_ID LIKE '%' + @p_AUTH_ID + '%' OR  @p_AUTH_ID IS NULL OR @p_AUTH_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		AND (A.ISAPPROVE LIKE '%' + @p_ISAPPROVE + '%' OR  @p_ISAPPROVE IS NULL OR @p_ISAPPROVE = '')
		AND (DATEDIFF(DAY,A.Birthday ,CONVERT(DATETIME, @p_Birthday, 103)) = 0 OR  @p_Birthday IS NULL OR @p_Birthday = '')
		AND (A.ISFIRSTTIME LIKE '%' + @p_ISFIRSTTIME + '%' OR  @p_ISFIRSTTIME IS NULL OR @p_ISFIRSTTIME = '')
--		AND (A.RoleName <> 'DISABLE' OR A.RoleName IS NULL) 
    AND T.RoleName2 <> 'DISABLE' -- NGUYENTD 22042024_SECRETKEY: CHECK ROLE Ở BẢNG ROLE THAY VÌ BẢNG TL_USER
       AND (A.TLSUBBRID = @p_BRANCH_LOGIN OR @p_BRANCH_LOGIN IS NULL OR @p_BRANCH_LOGIN = '')

		--AND (
		--	(A.SECUR_CODE LIKE '%' + @p_SECUR_CODE + '%'
		--		--OR (SELECT TOP 1 TRM.DEP_ID FROM TL_SYS_ROLE_MAPPING TRM 
		--		--	WHERE TRM.TLNAME = A.TLNANME 
		--		--	AND GETDATE() >= TRM.EFF_DATE AND GETDATE() <= TRM.EXP_DATE
		--		--	) LIKE '%' + @p_SECUR_CODE + '%'
		--		----------BAODNQ 30/9/2022 : Thêm xử lý tìm kiếm ủy quyền kiêm nhiệm--------
		--		OR EXISTS(SELECT * FROM TL_SYS_ROLE_MAPPING TRM 
		--					WHERE TRM.TLNAME = A.TLNANME 
		--					AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
		--					AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
		--					AND TRM.DEP_ID = @p_SECUR_CODE
		--			)
		--		OR A.DEP_ID IN (SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING TRM 
		--					WHERE 1=1
		--					AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
		--					AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
		--					AND TRM.TLNAME = @p_MARKER_ID)
		--		OR (@p_TLSUBBRID <> 'DV0001' AND A.TLSUBBRID =@p_TLSUBBRID)
		--		OR (@BRANCH_ID <> 'DV0001' AND A.TLSUBBRID =@BRANCH_ID)
		--	) 
		--	OR (@p_DEP_ID IS NULL OR @p_DEP_ID = '' OR @p_SECUR_CODE IS NULL OR @p_SECUR_CODE ='')
		--	OR (A.SECUR_CODE IN (SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@p_SECUR_CODE))
		--	OR (A.SECUR_CODE IN (SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@p_DEP_ID))
		--)
		--------------BAODNQ 17/11/2022 : CHỈNH SỬA LẠI ĐIỀU KIỆN TÌM KIẾM THEO SECUR_CODE-------------------
		AND(
		(((A.SECUR_CODE IN (SELECT DEP_ID FROM @t_DEP_ID_TMP)
				OR EXISTS(SELECT * FROM TL_SYS_ROLE_MAPPING TRM 
							WHERE TRM.TLNAME = A.TLNANME 
							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
							AND TRM.DEP_ID  IN (SELECT DEP_ID FROM @t_DEP_ID_TMP)
					)
			)
			OR (@p_SECUR_CODE IS NULL OR @p_SECUR_CODE = '')) AND A.TLSUBBRID = 'DV0001')
      OR (A.TLSUBBRID <> 'DV0001')
		)
		---------------ENDBAODNQ-------------------
		AND (A.TLNANME != 'admin')
		--AND (A.TLSUBBRID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_TLSUBBRID)))
		--AND ((@P_LEVEL = 'ALL' AND A.TLSUBBRID IN (SELECT BRANCH_ID from @tmp)
		--OR (@P_LEVEL = 'UNIT' AND A.TLSUBBRID = @p_TLSUBBRID)) OR @p_TLSUBBRID = '' OR @p_TLSUBBRID IS NULL

		--)
		AND(
			(@P_LEVEL = 'ALL'
				AND (A.TLSUBBRID IN (SELECT BRANCH_ID from @tmp)
					--OR (SELECT TOP 1 TRM.BRANCH_ID
					--		FROM TL_SYS_ROLE_MAPPING TRM
					--		WHERE TRM.TLNAME = A.TLNANME
					--		AND GETDATE() >= TRM.EFF_DATE AND GETDATE() <= TRM.EXP_DATE
					--	) IN (SELECT BRANCH_ID from @tmp)
					----------BAODNQ 30/9/2022 : Thêm xử lý tìm kiếm ủy quyền kiêm nhiệm--------
					OR EXISTS(SELECT *
							FROM TL_SYS_ROLE_MAPPING TRM
							WHERE TRM.TLNAME = A.TLNANME
							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
							AND TRM.BRANCH_ID IN (SELECT BRANCH_ID from @tmp)
						)
					--OR A.DEP_ID IN (SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING TRM 
					--		WHERE 1=1
					--		AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
					--		AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
					--		AND TRM.TLNAME = @p_MARKER_ID)
					OR (@BRANCH_ID <> 'DV0001' AND A.TLSUBBRID =@BRANCH_ID)
					)
			)
			OR(@P_LEVEL = 'UNIT'
				AND (A.TLSUBBRID = @p_TLSUBBRID
					--OR (SELECT TOP 1 TRM.BRANCH_ID
					--		FROM TL_SYS_ROLE_MAPPING TRM
					--		WHERE TRM.TLNAME = A.TLNANME
					--		AND GETDATE() >= TRM.EFF_DATE AND GETDATE() <= TRM.EXP_DATE
					--	) = @p_TLSUBBRID
					----------BAODNQ 30/9/2022 : Thêm xử lý tìm kiếm ủy quyền kiêm nhiệm--------
					OR EXISTS(SELECT *
							FROM TL_SYS_ROLE_MAPPING TRM
							WHERE TRM.TLNAME = A.TLNANME
							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
							AND TRM.BRANCH_ID = @p_TLSUBBRID
						)
					--OR A.DEP_ID IN (SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING TRM 
					--		WHERE 1=1
					--		AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
					--		AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
					--		AND TRM.TLNAME = @p_MARKER_ID)
					OR (@BRANCH_ID <> 'DV0001' AND A.TLSUBBRID =@BRANCH_ID)
					)
			)
			--OR @BRANCH_ID = '' OR @BRANCH_ID IS NULL
			OR(@p_TLSUBBRID IS NULL OR @p_TLSUBBRID = '')
		)
		----------BAODNQ 22/3/2022 : Thêm điều kiện search theo phòng ban------

		--------------BAODNQ 17/11/2022 : CHỈNH SỬA LẠI ĐIỀU KIỆN TÌM KIẾM THEO DEP_ID-------------------
		AND(
			(((A.DEP_ID IN (SELECT DEP_ID FROM @t_DEP_ID_TMP)
				OR EXISTS(SELECT * FROM TL_SYS_ROLE_MAPPING TRM 
							WHERE TRM.TLNAME = A.TLNANME 
							AND CONVERT(DATE,GETDATE(),103) >= TRM.EFF_DATE 
							AND CONVERT(DATE,GETDATE(),103) <= TRM.EXP_DATE
							AND TRM.DEP_ID IN (SELECT DEP_ID FROM @t_DEP_ID_TMP)
					)
			)
			OR(@p_DEP_ID IS NULL OR @p_DEP_ID = '')) AND A.TLSUBBRID = 'DV0001')
      OR (A.TLSUBBRID <> 'DV0001')
		)
	-- PAGING END
COMMIT TRANSACTION


END -- PAGING

GO