DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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