ALTER PROCEDURE [dbo].[ASS_USE_CONFIRM_MASTER_Search]
@p_USER_MASTER_ID	varchar(15)  = NULL,
@p_BRANCH_ID	varchar(15)  = NULL,
@p_USE_EXPORT_DT	VARCHAR(20) = NULL,
@p_USER_EXPORT	nvarchar(200)  = NULL,
@p_USER_EXPORT_NAME	Nvarchar(200)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(15)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(15)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS_KT	varchar(15)  = NULL,
@p_CREATE_DT_KT	VARCHAR(20) = NULL,
@p_APPROVE_DT_KT	VARCHAR(20) = NULL,
@p_MAKER_ID_KT	varchar(50)  = NULL,
@p_CHECKER_ID_KT	varchar(50)  = NULL,
@p_REPORT_STATUS	varchar(15)  = NULL,
@p_KT_IS_DO			VARCHAR(1) = NULL,
@p_LEVEL	VARCHAR(50) = 'UNIT',
@p_TOP	INT = 10,
@p_FROMDATE VARCHAR(20) = NULL,
@p_TODATE VARCHAR(20) = NULL,
@p_USER_LOGIN   VARCHAR(15),-- 26022020 LUCTV TRUYEN XUONG USER LOGIN DE DO TIM MA PHONG BAN
@p_TYPE_SEARCH VARCHAR(10)= NULL -- 26022020 LUCTV TRUYEN XUONG DAU HIEU DE PHAN BIET TIM KIEM O MAN HINH KE TOAN HAY MAN HINH HCQT
AS
BEGIN -- PAGING

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

	------
	SET @p_RECORD_STATUS = '1'
	-------

	DECLARE @BRANCH_LOGIN VARCHAR(15)
		SET @BRANCH_LOGIN = (SELECT TOP 1 TLSUBBRID FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)

	DECLARE @ASSIGN_ROLES TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15), ROLE_ID VARCHAR(20))
	INSERT INTO @ASSIGN_ROLES SELECT * FROM [dbo].[TL_USER_GET_ASSIGNED_BRANCH](@p_USER_LOGIN)

	DECLARE @USER_ROLES TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15), ROLE_ID VARCHAR(20))
	INSERT INTO @USER_ROLES SELECT * FROM [dbo].[TL_USER_GET_ROLES](@p_USER_LOGIN)

	DECLARE @tmp TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15))
		INSERT INTO @tmp 
		SELECT DT.BRANCH_ID, DT.DEPT_ID FROM ASS_USE_MULTI_MASTER AM
		LEFT JOIN ASS_USE_MULTI_DT DT ON DT.USE_MULTI_ID IN (SELECT MIN(USE_MULTI_ID) FROM ASS_USE_MULTI_DT  TEMP WHERE TEMP.USER_MASTER_ID=AM.USER_MASTER_ID)
		WHERE 1= 1
		AND ((EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = DT.BRANCH_ID AND BRANCH_TYPE ='HS')
				AND (DT.DEPT_ID IN (SELECT R.DEPT_ID FROM @USER_ROLES R WHERE R.ROLE_ID IN ('GDDV','TP')))
					OR DT.DEPT_ID IN (SELECT DEPT_ID FROM @ASSIGN_ROLES WHERE ROLE_ID IN ('GDDV','TP')))
			OR (NOT EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = DT.BRANCH_ID AND BRANCH_TYPE ='HS')
				AND (DT.BRANCH_ID IN (SELECT R.BRANCH_ID FROM @USER_ROLES R WHERE R.ROLE_ID IN ('GDDV','TPGD'))))
					OR DT.BRANCH_ID IN (SELECT BRANCH_ID FROM @ASSIGN_ROLES WHERE ROLE_ID IN ('GDDV','TPGD')))

IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
-- PAGING BEGIN
	SELECT A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.CONTENT AS AUTH_STATUS_NAME,C.BRANCH_CODE+' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
	ATKT.CONTENT AS AUTH_STATUS_NAME_KT,
	@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH, 
	B.CONFIRM_STATUS,
	CASE WHEN B.CONFIRM_STATUS = 'Y'
			THEN N'Đã xác nhận'  
			ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS_NAME
-- SELECT END
	FROM ASS_USE_MULTI_MASTER A
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	LEFT JOIN dbo.CM_ALLCODE F ON F.CDVAL = A.AUTH_STATUS AND F.CDNAME = 'ASS_ADD_AUTH' AND F.CDTYPE = 'ASS_ADDNEW'
	LEFT JOIN dbo.CM_ALLCODE ATKT ON ATKT.CDVAL = A.AUTH_STATUS_KT AND ATKT.CDNAME = 'ASS_ADD_AUTH' AND ATKT.CDTYPE = 'ASS_ADDNEW'
	--INNER JOIN (SELECT BRANCH_ID,USER_MASTER_ID,COUNT(*) AS SL FROM ASS_USE_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,USER_MASTER_ID) DTT ON DTT.USER_MASTER_ID = A.USER_MASTER_ID   
	 LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
	WHERE 1 = 1
	AND (A.USER_MASTER_ID LIKE '%' + @p_USER_MASTER_ID + '%' OR  @p_USER_MASTER_ID IS NULL OR @p_USER_MASTER_ID = '')
	AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR  @p_FROMDATE IS NULL OR @p_FROMDATE = '')
	AND (CONVERT(DATE,ISNULL(A.APPROVE_DT, A.USE_EXPORT_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR  @p_TODATE IS NULL OR @p_TODATE = '')
	AND (A.USER_EXPORT LIKE '%' + @p_USER_EXPORT + '%' OR  @p_USER_EXPORT IS NULL OR @p_USER_EXPORT = '')
	AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')	 
	AND A.AUTH_STATUS = 'A'
	  --AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID = B.USER_MASTER_ID) 
			--OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID <> B.USER_MASTER_ID
			--OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
	 AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)) 
			OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID NOT IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)
			OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))

	AND (EXISTS (SELECT DT.USE_MULTI_ID FROM dbo.ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID=A.USER_MASTER_ID 
		AND ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) <> 'HS' AND (DT.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))
			OR ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) = 'HS' AND DT.DEPT_ID IN (SELECT DEPT_ID FROM @tmp)))))
	
	ORDER BY A.CREATE_DT DESC
	
-- PAGING END
	ELSE
-- PAGING BEGIN
	SELECT TOP(CONVERT(INT,@P_TOP))A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.CONTENT AS AUTH_STATUS_NAME, C.BRANCH_CODE+' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
	ATKT.CONTENT AS AUTH_STATUS_NAME_KT,
	@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,
	B.CONFIRM_STATUS,
	CASE WHEN B.CONFIRM_STATUS = 'Y'
			THEN N'Đã xác nhận'  
			ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS_NAME
-- SELECT END
		FROM ASS_USE_MULTI_MASTER A
		LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
		LEFT JOIN dbo.CM_ALLCODE F ON F.CDVAL = A.AUTH_STATUS AND F.CDNAME = 'ASS_ADD_AUTH' AND F.CDTYPE = 'ASS_ADDNEW'
		LEFT JOIN dbo.CM_ALLCODE ATKT ON ATKT.CDVAL = A.AUTH_STATUS_KT AND ATKT.CDNAME = 'ASS_ADD_AUTH' AND ATKT.CDTYPE = 'ASS_ADDNEW'
		--INNER JOIN (SELECT BRANCH_ID,USER_MASTER_ID,COUNT(*) AS SL FROM ASS_USE_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,USER_MASTER_ID) DTT ON DTT.USER_MASTER_ID = A.USER_MASTER_ID   
		LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
		WHERE 1 = 1
		AND (A.USER_MASTER_ID LIKE '%' + @p_USER_MASTER_ID + '%' OR  @p_USER_MASTER_ID IS NULL OR @p_USER_MASTER_ID = '')
		AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR  @p_FROMDATE IS NULL OR @p_FROMDATE = '')
		AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR  @p_TODATE IS NULL OR @p_TODATE = '')
		AND (A.USER_EXPORT LIKE '%' + @p_USER_EXPORT + '%' OR  @p_USER_EXPORT IS NULL OR @p_USER_EXPORT = '')
		AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
		AND A.AUTH_STATUS = 'A'
		AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)) 
			OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID NOT IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)
			OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
		 --AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID = B.USER_MASTER_ID) 
			--OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID <> B.USER_MASTER_ID
			--OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
		AND (EXISTS (SELECT DT.USE_MULTI_ID FROM dbo.ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID=A.USER_MASTER_ID 
			AND ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) <> 'HS' AND (DT.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))
				OR ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) = 'HS' AND DT.DEPT_ID IN (SELECT DEPT_ID FROM @tmp)))))
		ORDER BY A.CREATE_DT DESC
-- PAGING END
END -- PAGING