-- PROCEDURE NAME: ASS_USE_CONFIRM_MASTER_Search

DECLARE @p_USER_MASTER_ID varchar(15) = NULL,
@p_BRANCH_ID varchar(15) = N'DV0001',
@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) = N'N',
@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) = N'UNIT',
@p_TOP int = NULL,
@p_FROMDATE varchar(20) = NULL,
@p_TODATE varchar(20) = NULL,
@p_USER_LOGIN varchar(15) = N'baotq',
@p_TYPE_SEARCH varchar(10) = NULL


	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)

IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
-- PAGING BEGIN
BEGIN
SELECT COUNT(*) -- 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'
	LEFT JOIN  (SELECT TOP 1 USER_MASTER_ID,DT.BRANCH_ID,DEPT_ID FROM ASS_USE_MULTI_DT DT ORDER BY USE_MULTI_ID ASC) tmp ON A.USER_MASTER_ID = tmp.USER_MASTER_ID
	--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
	 LEFT JOIN TL_USER D ON B.MAKER_ID = D.TLNANME
	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 A.AUTH_STATUS_KT <>'A'
  AND A.APPROVE_DT>=CONVERT(DATETIME,'2022-17-10',103)
	  --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 = '' )))
   --TIM KIEM THEO TRUONG DON VI NHAN TAI SAN
   AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
    tmp.BRANCH_ID,
    tmp.DEPT_ID) US
    WHERE US.TLNANME = @p_USER_LOGIN))
--  AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
--    (SELECT TOP 1 DT.BRANCH_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC),
--    (SELECT TOP 1 DT.DEPT_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC)) US
--    WHERE US.TLNANME = @p_USER_LOGIN))

	
	
	
;WITH QUERY_DATA AS ( 
	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, dbo.FN_ASS_USE_MULTI_MASTER_GET_TOTAL_VALUE(A.USER_MASTER_ID) AS TOTAL_USE_VALUE,D.TLFullName CONFIRM_USER_NAME,B.CONFIRM_DT,
	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
, ROW_NUMBER() OVER (ORDER BY  A.CREATE_DT DESC
) AS __ROWNUM-- 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'
	LEFT JOIN  (SELECT TOP 1 USER_MASTER_ID,DT.BRANCH_ID,DEPT_ID FROM ASS_USE_MULTI_DT DT ORDER BY USE_MULTI_ID ASC) tmp ON A.USER_MASTER_ID = tmp.USER_MASTER_ID
	--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
	 LEFT JOIN TL_USER D ON B.MAKER_ID = D.TLNANME
	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 A.AUTH_STATUS_KT <>'A'
  AND A.APPROVE_DT>=CONVERT(DATETIME,'2022-17-10',103)
	  --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 = '' )))
   --TIM KIEM THEO TRUONG DON VI NHAN TAI SAN
   AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
    tmp.BRANCH_ID,
    tmp.DEPT_ID) US
    WHERE US.TLNANME = @p_USER_LOGIN))
--  AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
--    (SELECT TOP 1 DT.BRANCH_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC),
--    (SELECT TOP 1 DT.DEPT_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC)) US
--    WHERE US.TLNANME = @p_USER_LOGIN))

	
	
	
) SELECT * FROM QUERY_DATA WHERE __ROWNUM > 0 AND __ROWNUM <= 10
END-- PAGING END
	ELSE
-- PAGING BEGIN
BEGIN
SELECT COUNT(*) FROM(
	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, dbo.FN_ASS_USE_MULTI_MASTER_GET_TOTAL_VALUE(A.USER_MASTER_ID) AS TOTAL_USE_VALUE,D.TLFullName CONFIRM_USER_NAME,B.CONFIRM_DT,
	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'
		LEFT JOIN  (SELECT TOP 1 USER_MASTER_ID,DT.BRANCH_ID,DEPT_ID FROM ASS_USE_MULTI_DT DT ORDER BY USE_MULTI_ID ASC) tmp ON A.USER_MASTER_ID = tmp.USER_MASTER_ID
		--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
		LEFT JOIN TL_USER D ON B.MAKER_ID = D.TLNANME
		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 A.AUTH_STATUS_KT <>'A'
    AND A.APPROVE_DT>=CONVERT(DATETIME,'2022-17-10',103)
		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 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
    tmp.BRANCH_ID,
    tmp.DEPT_ID) US
    WHERE US.TLNANME = @p_USER_LOGIN))

		ORDER BY  A.CREATE_DT DESC
) COUNTER_TOP;WITH QUERY_DATA AS ( 
	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, dbo.FN_ASS_USE_MULTI_MASTER_GET_TOTAL_VALUE(A.USER_MASTER_ID) AS TOTAL_USE_VALUE,D.TLFullName CONFIRM_USER_NAME,B.CONFIRM_DT,
	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
, ROW_NUMBER() OVER (ORDER BY  A.CREATE_DT DESC
) AS __ROWNUM-- 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'
		LEFT JOIN  (SELECT TOP 1 USER_MASTER_ID,DT.BRANCH_ID,DEPT_ID FROM ASS_USE_MULTI_DT DT ORDER BY USE_MULTI_ID ASC) tmp ON A.USER_MASTER_ID = tmp.USER_MASTER_ID
		--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
		LEFT JOIN TL_USER D ON B.MAKER_ID = D.TLNANME
		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 A.AUTH_STATUS_KT <>'A'
    AND A.APPROVE_DT>=CONVERT(DATETIME,'2022-17-10',103)
		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 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',
    tmp.BRANCH_ID,
    tmp.DEPT_ID) US
    WHERE US.TLNANME = @p_USER_LOGIN))

		ORDER BY  A.CREATE_DT DESC
) SELECT * FROM QUERY_DATA WHERE __ROWNUM > 0 AND __ROWNUM <= 10
END-- PAGING END