ALTER PROCEDURE dbo.ASS_INVENTORY_MASTER_Search
@p_INVENT_ID	varchar(15)  = NULL,
@p_INVENTORY_DT	VARCHAR(20) = NULL,
@p_TERM	nvarchar(20)  = NULL,
@p_BRANCH_ID	varchar(15)  = NULL,
@p_DEPT_ID	varchar(15)  = NULL,
@p_NOTES	NVARCHAR(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS_DVKD	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_TOP	INT = 10,
@p_BRANCH_CREATE	VARCHAR(15) = NULL,
@p_LEVEL	VARCHAR(50) = 'UNIT',
@p_USER_LOGIN	VARCHAR(20) = NULL,
@p_TYPE_SEARCH	VARCHAR(20) = NULL,
@p_AUTH_STATUS_CONFIRM VARCHAR(1) = NULL,
@p_FROMDATE VARCHAR(20) = NULL,
@p_TODATE VARCHAR(20) = 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
declare @tmp table(BRANCH_ID varchar(15))
	insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_CREATE)

	IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
	-- PAGING BEGIN
	SELECT A.INVENT_ID ,A.INVENTORY_DT ,A.TERM,A.BRANCH_ID,A.NOTES,A.RECORD_STATUS,A.AUTH_STATUS,A.MAKER_ID,A.CREATE_DT,A.CHECKER_ID ,A.APPROVE_DT,A.BRANCH_CREATE,A.DEPT_ID,DE.DEP_CODE AS DEPT_CODE,DE.DEP_NAME AS DEPT_NAME,A.NQL,A.DECISION,A.PROM_DT,A.COMMENT,A.PENTITION,A.ISSUED_DATE,A.AUTH_STATUS_DVKD,A.CHECKER_ID_DVKD,A.APPROVE_DT_DVKD,A.SIGN_USER,A.PROCESS_ID,C.BRANCH_CODE, C.BRANCH_NAME, T.TERM_CODE, T.TERM_NAME,D.CONTENT AS AUTH_STATUS_DVKD_NAME,TL.TLFullName AS CHECKER_ID_DVKD_NAME,
	CASE 
	WHEN EXISTS (SELECT INVENT_ID FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND PARTY_NAME = @p_USER_LOGIN AND IS_DONE = '1')
		THEN N'Đã xác nhận'
		-- PHONGNT 27/06/22
	WHEN @p_TYPE_SEARCH='APPR' AND EXISTS (SELECT 1 FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_DONE = '1')
		THEN N'Đã xác nhận'
	ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS,
	CASE @p_TYPE_SEARCH 
		-- END
	WHEN 'CF' 
		THEN (SELECT CONFIRM_DT FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND PARTY_NAME = @p_USER_LOGIN)
	ELSE (SELECT TOP(1) CONFIRM_DT FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_DONE = '1' AND CONFIRM_DT IS NOT NULL) END AS CONFIRM_DT,
	(SELECT TLFullName FROM TL_USER WHERE TLNANME = ( SELECT PARTY_NAME FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_MAIN = '1')) AS APPROVE_NAME,
	(SELECT tu.TLFullName FROM TL_USER tu WHERE tu.TLNANME = A.SIGN_USER) AS SIGN_USER_NAME,
  CASE WHEN A.ASSET_TYPE IS NULL OR A.ASSET_TYPE ='' THEN 'TSCD/CCLD' ELSE A.ASSET_TYPE END AS ASSET_TYPE

	-- SELECT END
	FROM ASS_INVENTORY_MASTER A 
	LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS 
	LEFT JOIN CM_BRANCH C ON C.BRANCH_ID = A.BRANCH_ID
	--PHONGNT 27/8/22 Bổ sung phòng ban
	LEFT JOIN CM_DEPARTMENT DE ON DE.DEP_ID = A.DEPT_ID
	--END
	--LEFT JOIN CM_AUTH_STATUS D ON A.AUTH_STATUS_DVKD = D.AUTH_STATUS 
	LEFT JOIN CM_ALLCODE D ON (D.CDVAL=A.AUTH_STATUS AND D.CDNAME='ASS_INVENTORY')
	LEFT JOIN CM_TERM T ON T.TERM_ID = A.TERM
	LEFT JOIN TL_USER TL ON TL.TLNANME = A.CHECKER_ID_DVKD
	 WHERE 1 = 1
		AND (A.INVENT_ID LIKE '%' + @p_INVENT_ID + '%' OR  @p_INVENT_ID IS NULL OR @p_INVENT_ID = '')
		AND (DATEDIFF(DAYOFYEAR,A.INVENTORY_DT,CONVERT(DATETIME, @p_INVENTORY_DT, 103)) = 0 OR  @p_INVENTORY_DT IS NULL OR @p_INVENTORY_DT = '')
		AND (A.TERM LIKE '%' + @p_TERM + '%' OR  @p_TERM IS NULL OR @p_TERM = '')
		AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		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.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')

		AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (DATEDIFF(DAYOFYEAR,A.CREATE_DT,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 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 (DATEDIFF(DAYOFYEAR,A.APPROVE_DT,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		AND A.RECORD_STATUS = '1'
		AND ((@P_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))
			OR ((@P_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @p_BRANCH_CREATE) 
			OR (@p_BRANCH_CREATE = '' OR @p_BRANCH_CREATE IS NULL)))

		-- GIANT 25/08/2021
		AND ((@p_TYPE_SEARCH = 'CF' AND A.AUTH_STATUS = 'U' 
					AND EXISTS ((SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_RECIVE_MAIL = 1 
						AND ( (@p_AUTH_STATUS_CONFIRM = 'Y' AND IS_DONE = 1) OR (@p_AUTH_STATUS_CONFIRM = 'N' AND IS_DONE = 0) OR (@p_AUTH_STATUS_CONFIRM = '' OR @p_AUTH_STATUS_CONFIRM IS NULL) )
					))
				OR	(@p_TYPE_SEARCH = 'CF' AND A.AUTH_STATUS = 'A' AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )
				OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL
			))
			OR ( 
				(@p_TYPE_SEARCH = 'APPR' AND A.AUTH_STATUS = 'U' AND  NOT EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE IS_RECIVE_MAIL = 1 AND IS_DONE = 0 AND INVENT_ID = A.INVENT_ID)
					AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )
				OR	(@p_TYPE_SEARCH = 'APPR' AND A.AUTH_STATUS = 'A' AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )
				OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL) 
			)
		)
			
		AND (A.AUTH_STATUS_DVKD LIKE '%' + @p_AUTH_STATUS_DVKD + '%' OR  @p_AUTH_STATUS_DVKD IS NULL OR @p_AUTH_STATUS_DVKD = '')

		AND (CONVERT(DATE,ISNULL(A.INVENTORY_DT, A.CREATE_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR  @p_FROMDATE IS NULL OR @p_FROMDATE = '')
		AND (CONVERT(DATE,ISNULL(A.INVENTORY_DT, A.CREATE_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR  @p_TODATE IS NULL OR @p_TODATE = '')

		-- HUYHT 27/04/2022 Khi trạng thái duyệt là "Nháp", "Từ chối" thì chỉ người tạo tìm thấy phiếu
		AND ((A.AUTH_STATUS_DVKD IN ('E','R') AND (A.MAKER_ID = @p_USER_LOGIN OR @p_USER_LOGIN IS NULL OR @p_USER_LOGIN = ''))
			OR A.AUTH_STATUS_DVKD NOT IN ('E','R'))

		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	ELSE
	-- PAGING BEGIN

	SELECT TOP(CONVERT(INT,@P_TOP))A.INVENT_ID ,A.INVENTORY_DT ,A.TERM,A.BRANCH_ID,A.NOTES,A.RECORD_STATUS,A.AUTH_STATUS,A.MAKER_ID,A.CREATE_DT,A.CHECKER_ID ,A.APPROVE_DT,A.BRANCH_CREATE,A.DEPT_ID,DE.DEP_CODE AS DEPT_CODE,DE.DEP_NAME AS DEPT_NAME,A.NQL,A.DECISION,A.PROM_DT,A.COMMENT,A.PENTITION,A.ISSUED_DATE,A.AUTH_STATUS_DVKD,A.CHECKER_ID_DVKD,A.APPROVE_DT_DVKD,A.SIGN_USER,A.PROCESS_ID,B.AUTH_STATUS_NAME,C.BRANCH_CODE, C.BRANCH_NAME, T.TERM_CODE, T.TERM_NAME,D.CONTENT AS AUTH_STATUS_DVKD_NAME,TL.TLFullName AS CHECKER_ID_DVKD_NAME,
	CASE 
		WHEN EXISTS (SELECT INVENT_ID FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND PARTY_NAME = @p_USER_LOGIN AND IS_DONE = '1')
			THEN N'Đã xác nhận'
			-- PHONGNT 27/06/22 Bổ sung màn hình phê duyệt ngày
		WHEN @p_TYPE_SEARCH='APPR' AND EXISTS (SELECT CONFIRM_DT FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_DONE = '1')
			THEN N'Đã xác nhận'
		ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS,
	CASE @p_TYPE_SEARCH 
		WHEN 'CF' 
			THEN (SELECT CONFIRM_DT FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND PARTY_NAME = @p_USER_LOGIN)
		ELSE (SELECT TOP(1) CONFIRM_DT FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_DONE = '1' AND CONFIRM_DT IS NOT NULL ORDER BY CONFIRM_DT) END AS CONFIRM_DT,
	(SELECT TLFullName FROM TL_USER WHERE TLNANME = ( SELECT PARTY_NAME FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_MAIN = '1')) AS APPROVE_NAME,
	(SELECT tu.TLFullName FROM TL_USER tu WHERE tu.TLNANME = A.SIGN_USER) AS SIGN_USER_NAME,
  CASE WHEN A.ASSET_TYPE IS NULL OR A.ASSET_TYPE ='' THEN 'TSCD/CCLD' ELSE A.ASSET_TYPE END AS ASSET_TYPE
	-- SELECT END

	FROM ASS_INVENTORY_MASTER A 
	LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
	--LEFT JOIN CM_AUTH_STATUS D ON A.AUTH_STATUS_DVKD = D.AUTH_STATUS 
	--PHONGNT 27/8/22 Bổ sung phòng ban
	LEFT JOIN CM_DEPARTMENT DE ON DE.DEP_ID = A.DEPT_ID
	--END
	LEFT JOIN CM_ALLCODE D ON (D.CDVAL=A.AUTH_STATUS AND D.CDNAME='ASS_INVENTORY')
	LEFT JOIN CM_BRANCH C ON C.BRANCH_ID = A.BRANCH_ID 
	LEFT JOIN CM_TERM T ON T.TERM_ID = A.TERM
	LEFT JOIN TL_USER TL ON TL.TLNANME = A.CHECKER_ID_DVKD
	 WHERE 1 = 1
		AND (A.INVENT_ID LIKE '%' + @p_INVENT_ID + '%' OR  @p_INVENT_ID IS NULL OR @p_INVENT_ID = '')
		AND (DATEDIFF(DAYOFYEAR,A.INVENTORY_DT,CONVERT(DATETIME, @p_INVENTORY_DT, 103)) = 0 OR  @p_INVENTORY_DT IS NULL OR @p_INVENTORY_DT = '')
		AND (A.TERM LIKE '%' + @p_TERM + '%' OR  @p_TERM IS NULL OR @p_TERM = '')
		AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		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.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')

		AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (DATEDIFF(DAYOFYEAR,A.CREATE_DT,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 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 (DATEDIFF(DAYOFYEAR,A.APPROVE_DT,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		AND A.RECORD_STATUS = '1'
		AND ((@P_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))
			OR ((@P_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @p_BRANCH_CREATE) 
			OR (@p_BRANCH_CREATE = '' OR @p_BRANCH_CREATE IS NULL)))
		
		-- GIANT 25/08/2021
		--AND (
		--	(@p_TYPE_SEARCH = 'CF' AND A.AUTH_STATUS = 'U' 
		--		AND EXISTS ((SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_RECIVE_MAIL = 1 
		--			AND ( (@p_AUTH_STATUS_CONFIRM = 'Y' AND IS_DONE = 1) OR (@p_AUTH_STATUS_CONFIRM = 'N' AND IS_DONE = 0) OR (@p_AUTH_STATUS_CONFIRM = '' OR @p_AUTH_STATUS_CONFIRM IS NULL) )
		--		))
		--		OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL
		--	))			
		--	OR ( 
		--		(@p_TYPE_SEARCH = 'APPR' AND A.AUTH_STATUS = 'U' AND  NOT EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE IS_RECIVE_MAIL = 1 AND IS_DONE = 0 AND INVENT_ID = A.INVENT_ID)
		--			AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )
			
		--		OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL) )
		--	)
				AND ((@p_TYPE_SEARCH = 'CF' AND A.AUTH_STATUS = 'U' 
					AND EXISTS ((SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_RECIVE_MAIL = 1 
						AND ( (@p_AUTH_STATUS_CONFIRM = 'Y' AND IS_DONE = 1) OR (@p_AUTH_STATUS_CONFIRM = 'N' AND IS_DONE = 0) OR (@p_AUTH_STATUS_CONFIRM = '' OR @p_AUTH_STATUS_CONFIRM IS NULL) )
					))
				OR	(@p_TYPE_SEARCH = 'CF' AND A.AUTH_STATUS = 'A' AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )
				OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL
			))
			OR ( 
				(@p_TYPE_SEARCH = 'APPR' AND A.AUTH_STATUS = 'U' AND  NOT EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE IS_RECIVE_MAIL = 1 AND IS_DONE = 0 AND INVENT_ID = A.INVENT_ID)
					AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )
				OR	(@p_TYPE_SEARCH = 'APPR' AND A.AUTH_STATUS = 'A' AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )
				OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL) 
			)
		)

		AND (A.AUTH_STATUS_DVKD LIKE '%' + @p_AUTH_STATUS_DVKD + '%' OR  @p_AUTH_STATUS_DVKD IS NULL OR @p_AUTH_STATUS_DVKD = '')

		AND (CONVERT(DATE,ISNULL(A.INVENTORY_DT,A.CREATE_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR  @p_FROMDATE IS NULL OR @p_FROMDATE = '')
		AND (CONVERT(DATE,ISNULL(A.INVENTORY_DT, A.CREATE_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR  @p_TODATE IS NULL OR @p_TODATE = '')

		-- HUYHT 27/04/2022 Khi trạng thái duyệt là "Nháp", "Từ chối" thì chỉ người tạo tìm thấy phiếu
		AND ((A.AUTH_STATUS_DVKD IN ('E','R') AND (A.MAKER_ID = @p_USER_LOGIN OR @p_USER_LOGIN IS NULL OR @p_USER_LOGIN = ''))
			OR A.AUTH_STATUS_DVKD NOT IN ('E','R'))

		ORDER BY A.CREATE_DT DESC
	-- PAGING END

END -- PAGING

GO