ALTER PROCEDURE dbo.ASS_COLLECT_CONFIRM_MASTER_Search
@p_COL_MULTI_MASTER_ID	varchar(15)  = NULL,
@p_BRANCH_ID	varchar(15)  = NULL,
@p_COLLECT_DT	VARCHAR(20) = NULL,
@p_USER_COLLECT	nvarchar(200)  = NULL,
@p_USER_COLLECT_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_LEVEL	VARCHAR(50) = 'UNIT',
@p_TOP	INT = NULL,
@p_FROMDATE VARCHAR(20) = NULL,
@p_TODATE VARCHAR(20) = NULL,
@p_IS_UPDATE VARCHAR(1) = NULL,
@p_USER_LOGIN   VARCHAR(15),
@p_TYPE_SEARCH VARCHAR(10)= NULL
AS
BEGIN -- PAGING
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

----
set @p_RECORD_STATUS = '1'
----
	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)

-- GiaNT
DECLARE @BRANCH_LOGIN VARCHAR(15), @DEP_LOGIN VARCHAR(15)
		SET @BRANCH_LOGIN = (SELECT TOP 1 TLSUBBRID FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)
				
		SET @DEP_LOGIN = (SELECT TOP 1 DEP_ID  FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)

-- GIANT 11/10/2021
DECLARE @BRANCH_TYPE VARCHAR(5)
		SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_LOGIN)

IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
begin
-- PAGING BEGIN
	SELECT A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.AUTH_STATUS_NAME, T1.TLFullName AS MAKER_NAME,T2.TLFullName AS MAKER_NAME_KT,T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT,
	ATKT.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_KT, C.BRANCH_CODE +' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
	@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,
	CASE WHEN EXISTS (SELECT * FROM ASS_COLLECT_CONFIRM_MASTER M WHERE M.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID) 
			THEN 'Y'  
		ELSE 'N' END AS CONFIRM_STATUS
	-- SELECT END
	FROM ASS_COLLECT_MULTI_MASTER A
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	LEFT JOIN CM_AUTH_STATUS F ON A.AUTH_STATUS = F.AUTH_STATUS
	LEFT JOIN CM_AUTH_STATUS ATKT ON ATKT.AUTH_STATUS = A.AUTH_STATUS_KT
	LEFT JOIN TL_USER T1 ON A.MAKER_ID = T1.TLNANME 
	LEFT JOIN TL_USER T2 ON A.MAKER_ID_KT = T2.TLNANME
	LEFT JOIN TL_USER T3 ON A.CHECKER_ID = T3.TLNANME 
	LEFT JOIN TL_USER T4 ON A.CHECKER_ID_KT = T4.TLNANME
	--INNER JOIN (SELECT BRANCH_ID,COL_MULTI_MASTER_ID,COUNT(*) AS SL FROM ASS_COLLECT_MULTI_DT WHERE DEPT_ID_USE = @BRANCH_LOGIN GROUP BY BRANCH_ID,COL_MULTI_MASTER_ID) DTT ON DTT.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID   

	WHERE 1 = 1
	AND (A.COL_MULTI_MASTER_ID LIKE '%' + @p_COL_MULTI_MASTER_ID + '%' OR  @p_COL_MULTI_MASTER_ID IS NULL OR @p_COL_MULTI_MASTER_ID = '')
	AND (CONVERT(DATE,ISNULL(A.CREATE_DT_KT, A.COLLECT_DT),103) >= CONVERT(DATE,@p_FROMDATE, 103) OR @p_FROMDATE IS NULL OR @p_FROMDATE ='')
	AND (CONVERT(DATE,ISNULL(A.CREATE_DT_KT, A.COLLECT_DT),103) <= CONVERT(DATE,@p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE ='')
	AND (A.USER_COLLECT LIKE '%' + @p_USER_COLLECT + '%' OR  @p_USER_COLLECT IS NULL OR @p_USER_COLLECT = '')
	AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
	AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
	AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
	AND (A.CREATE_DT =CONVERT(DATETIME, @p_CREATE_DT, 103) OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
	AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
	AND (A.APPROVE_DT =CONVERT(DATETIME, @p_APPROVE_DT, 103) OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
	AND (A.AUTH_STATUS_KT LIKE '%' + @p_AUTH_STATUS_KT + '%' OR  @p_AUTH_STATUS_KT IS NULL OR @p_AUTH_STATUS_KT = '')
	AND (A.CREATE_DT_KT =CONVERT(DATETIME, @p_CREATE_DT_KT, 103) OR  @p_CREATE_DT_KT IS NULL OR @p_CREATE_DT_KT = '')
	AND (A.APPROVE_DT_KT =CONVERT(DATETIME, @p_APPROVE_DT_KT, 103) OR  @p_APPROVE_DT_KT IS NULL OR @p_APPROVE_DT_KT = '')
	AND (A.MAKER_ID_KT LIKE '%' + @p_MAKER_ID_KT + '%' OR  @p_MAKER_ID_KT IS NULL OR @p_MAKER_ID_KT = '')
	AND (A.CHECKER_ID_KT LIKE '%' + @p_CHECKER_ID_KT + '%' OR  @p_CHECKER_ID_KT IS NULL OR @p_CHECKER_ID_KT = '')
	AND (A.REPORT_STATUS LIKE '%' + @p_REPORT_STATUS + '%' OR  @p_REPORT_STATUS IS NULL OR @p_REPORT_STATUS = '')
	AND A.RECORD_STATUS='1'
	AND (A.REPORT_STATUS LIKE '%' + @p_REPORT_STATUS + '%' OR  @p_REPORT_STATUS IS NULL OR @p_REPORT_STATUS = '')

	-- GiaNT 28/09/2021
	AND ( (@p_AUTH_STATUS = 'Y' AND A.COL_MULTI_MASTER_ID IN (SELECT G.COL_MULTI_MASTER_ID FROM ASS_COLLECT_CONFIRM_MASTER G)) 
			OR (@p_AUTH_STATUS = 'N' AND A.COL_MULTI_MASTER_ID NOT IN (SELECT G.COL_MULTI_MASTER_ID FROM ASS_COLLECT_CONFIRM_MASTER G)
			OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
	AND A.AUTH_STATUS = 'A'

	-- GiaNT 11/10/2021
	--AND ( 
	--	(@BRANCH_TYPE = 'HS' AND  EXISTS (SELECT * FROM ASS_COLLECT_MULTI_DT WHERE A.COL_MULTI_MASTER_ID = ASS_COLLECT_MULTI_DT.COL_MULTI_MASTER_ID AND ASS_COLLECT_MULTI_DT.DEPT_ID_USE = @DEP_LOGIN))
	--	OR (@BRANCH_TYPE <> 'HS' AND  EXISTS (SELECT * FROM ASS_COLLECT_MULTI_DT WHERE A.COL_MULTI_MASTER_ID = ASS_COLLECT_MULTI_DT.COL_MULTI_MASTER_ID AND ASS_COLLECT_MULTI_DT.BRANCH_ID = @BRANCH_LOGIN))
	--)

  -- TIM KIEM THEO TRUONG DON VI GIAO TAI SAN
  AND (EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
    (SELECT TOP 1 DT.BRANCH_ID FROM ASS_COLLECT_MULTI_DT DT WHERE DT.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID ORDER BY COLLECT_MULTI_ID ASC),
    (SELECT TOP 1 DT.DEPT_ID_USE FROM ASS_COLLECT_MULTI_DT DT WHERE DT.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID ORDER BY COLLECT_MULTI_ID ASC)) US
    WHERE US.TLNANME = @p_USER_LOGIN))
  AND A.APPROVE_DT>CONVERT(DATETIME,'2022-17-10',103)

	ORDER BY A.CREATE_DT DESC
-- PAGING END
end
ELSE
BEGIN
-- PAGING BEGIN
	SELECT TOP(CONVERT(INT,@P_TOP))A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.AUTH_STATUS_NAME, 
	ATKT.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_KT,T1.TLFullName AS MAKER_NAME,T2.TLFullName AS MAKER_NAME_KT,T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT,
	C.BRANCH_CODE +' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
	@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,
	CASE WHEN EXISTS (SELECT * FROM ASS_COLLECT_CONFIRM_MASTER M WHERE M.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID) 
			THEN 'Y'  
		ELSE 'N' END AS CONFIRM_STATUS
	 -- SELECT END
	FROM ASS_COLLECT_MULTI_MASTER A
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	LEFT JOIN CM_AUTH_STATUS F ON A.AUTH_STATUS = F.AUTH_STATUS
	LEFT JOIN CM_AUTH_STATUS ATKT ON ATKT.AUTH_STATUS = A.AUTH_STATUS_KT
	LEFT JOIN TL_USER T1 ON A.MAKER_ID = T1.TLNANME 
	LEFT JOIN TL_USER T2 ON A.MAKER_ID_KT = T2.TLNANME
	LEFT JOIN TL_USER T3 ON A.CHECKER_ID = T3.TLNANME 
	LEFT JOIN TL_USER T4 ON A.CHECKER_ID_KT = T4.TLNANME
	--INNER JOIN (SELECT BRANCH_ID,COL_MULTI_MASTER_ID,COUNT(*) AS SL FROM ASS_COLLECT_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,COL_MULTI_MASTER_ID) DTT ON DTT.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID   

	 WHERE 1 = 1
	 AND (A.COL_MULTI_MASTER_ID LIKE '%' + @p_COL_MULTI_MASTER_ID + '%' OR  @p_COL_MULTI_MASTER_ID IS NULL OR @p_COL_MULTI_MASTER_ID = '')
	 AND (CONVERT(DATE,ISNULL(A.CREATE_DT_KT, A.COLLECT_DT),103) >= CONVERT(DATE,@p_FROMDATE, 103) OR @p_FROMDATE IS NULL OR @p_FROMDATE ='')
	 AND (CONVERT(DATE,ISNULL(A.CREATE_DT_KT, A.COLLECT_DT),103) <= CONVERT(DATE,@p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE ='')
	 AND (A.USER_COLLECT LIKE '%' + @p_USER_COLLECT + '%' OR  @p_USER_COLLECT IS NULL OR @p_USER_COLLECT = '')
	 AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
	 AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
	 AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
	 AND (A.CREATE_DT =CONVERT(DATETIME, @p_CREATE_DT, 103) OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
	 AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
	 AND (A.APPROVE_DT =CONVERT(DATETIME, @p_APPROVE_DT, 103) OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
	 AND (A.AUTH_STATUS_KT LIKE '%' + @p_AUTH_STATUS_KT + '%' OR  @p_AUTH_STATUS_KT IS NULL OR @p_AUTH_STATUS_KT = '')
	 AND (A.CREATE_DT_KT =CONVERT(DATETIME, @p_CREATE_DT_KT, 103) OR  @p_CREATE_DT_KT IS NULL OR @p_CREATE_DT_KT = '')
	 AND (A.APPROVE_DT_KT =CONVERT(DATETIME, @p_APPROVE_DT_KT, 103) OR  @p_APPROVE_DT_KT IS NULL OR @p_APPROVE_DT_KT = '')
	 AND (A.MAKER_ID_KT LIKE '%' + @p_MAKER_ID_KT + '%' OR  @p_MAKER_ID_KT IS NULL OR @p_MAKER_ID_KT = '')
	 AND (A.CHECKER_ID_KT LIKE '%' + @p_CHECKER_ID_KT + '%' OR  @p_CHECKER_ID_KT IS NULL OR @p_CHECKER_ID_KT = '')
	 AND (A.REPORT_STATUS LIKE '%' + @p_REPORT_STATUS + '%' OR  @p_REPORT_STATUS IS NULL OR @p_REPORT_STATUS = '')
	 AND A.RECORD_STATUS='1'
	 AND (A.REPORT_STATUS LIKE '%' + @p_REPORT_STATUS + '%' OR  @p_REPORT_STATUS IS NULL OR @p_REPORT_STATUS = '')

	-- GiaNT 28/09/2021
	AND ( (@p_AUTH_STATUS = 'Y' AND A.COL_MULTI_MASTER_ID IN (SELECT G.COL_MULTI_MASTER_ID FROM ASS_COLLECT_CONFIRM_MASTER G)) 
			OR (@p_AUTH_STATUS = 'N' AND A.COL_MULTI_MASTER_ID NOT IN (SELECT G.COL_MULTI_MASTER_ID FROM ASS_COLLECT_CONFIRM_MASTER G)
			OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))
	AND A.AUTH_STATUS = 'A'
	-- GiaNT 11/10/2021
	--AND ( 
	--	(@BRANCH_TYPE = 'HS' AND  EXISTS (SELECT * FROM ASS_COLLECT_MULTI_DT WHERE A.COL_MULTI_MASTER_ID = ASS_COLLECT_MULTI_DT.COL_MULTI_MASTER_ID AND ASS_COLLECT_MULTI_DT.DEPT_ID_USE = @DEP_LOGIN))
	--	OR (@BRANCH_TYPE <> 'HS' AND  EXISTS (SELECT * FROM ASS_COLLECT_MULTI_DT WHERE A.COL_MULTI_MASTER_ID = ASS_COLLECT_MULTI_DT.COL_MULTI_MASTER_ID AND ASS_COLLECT_MULTI_DT.BRANCH_ID = @BRANCH_LOGIN))
	--)

  -- TIM KIEM THEO TRUONG DON VI GIAO TAI SAN
  AND (EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
    (SELECT TOP 1 DT.BRANCH_ID FROM ASS_COLLECT_MULTI_DT DT WHERE DT.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID ORDER BY COLLECT_MULTI_ID ASC),
    (SELECT TOP 1 DT.DEPT_ID_USE FROM ASS_COLLECT_MULTI_DT DT WHERE DT.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID ORDER BY COLLECT_MULTI_ID ASC)) US
    WHERE US.TLNANME = @p_USER_LOGIN))
    AND A.APPROVE_DT>CONVERT(DATETIME,'2022-17-10',103)

	ORDER BY A.CREATE_DT DESC

-- PAGING END
END
END -- PAGING