ALTER   PROCEDURE [dbo].[ASS_UPDATE_Search]
@p_UPDATE_ID	varchar(15)  = NULL,
@p_ASSET_ID	varchar(15)  = NULL,
@p_ASSET_TYPE varchar(15)  = NULL,
@p_GROUP_ID	varchar(15)  = NULL,
@p_ASSET_NAME	nvarchar(1000)  = NULL,
@p_ASSET_SERIAL_NO	nvarchar(100)  = NULL,
@p_ASSET_DESC	nvarchar(max) = NULL,
@p_BUY_PRICE	decimal = NULL,
@p_AMORT_AMT	decimal = NULL,
@p_AMORT_MONTH	decimal(18,2) = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_NOTES		nvarchar(1000) = 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_ASS_STATUS varchar(20) = NULL,	-- khangth, 02/12/2019
@p_TOP	INT = 10,
@p_BRANCH_ID	VARCHAR(15) = NULL,
@p_LEVEL	VARCHAR(50) = 'UNIT',
@p_FR_BUY_DATE		VARCHAR(20) = NULL, --Tu ngay nhap
@p_TO_BUY_DATE		VARCHAR(20) = NULL --Den ngay nhap
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_ID)

IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
-- PAGING BEGIN
SELECT A.*, B.GROUP_CODE, B.GROUP_NAME, C.AUTH_STATUS_NAME, D.ASSET_CODE, D.SPECIAL_ASS, D.ASS_TYPE
-- SELECT END
FROM ASS_UPDATE A 
LEFT JOIN ASS_GROUP B ON B.GROUP_ID = A.GROUP_ID
LEFT JOIN CM_AUTH_STATUS C ON C.AUTH_STATUS = A.AUTH_STATUS
LEFT JOIN ASS_MASTER D ON D.ASSET_ID = A.ASSET_ID
 WHERE 1 = 1
	AND (A.UPDATE_ID LIKE '%' + @p_UPDATE_ID + '%' OR  @p_UPDATE_ID IS NULL OR @p_UPDATE_ID = '')
	AND (D.ASSET_CODE LIKE '%' + @p_ASSET_ID + '%' OR  @p_ASSET_ID IS NULL OR @p_ASSET_ID = '')
	AND (D.TYPE_ID LIKE '%' + @p_ASSET_TYPE + '%' OR  @p_ASSET_TYPE IS NULL OR @p_ASSET_TYPE = '')
	AND (A.GROUP_ID LIKE '%' + @p_GROUP_ID + '%' OR  @p_GROUP_ID IS NULL OR @p_GROUP_ID = '')
	AND (A.ASSET_NAME LIKE '%' + @p_ASSET_NAME + '%' OR  @p_ASSET_NAME IS NULL OR @p_ASSET_NAME = '')
	AND (A.ASSET_SERIAL_NO LIKE '%' + @p_ASSET_SERIAL_NO + '%' OR  @p_ASSET_SERIAL_NO IS NULL OR @p_ASSET_SERIAL_NO = '')
	AND (A.ASSET_DESC LIKE '%' + @p_ASSET_DESC + '%' OR  @p_ASSET_DESC IS NULL OR @p_ASSET_DESC = '')
	AND (A.BUY_PRICE = @p_BUY_PRICE OR  @p_BUY_PRICE IS NULL)
	AND (A.AMORT_AMT = @p_AMORT_AMT OR  @p_AMORT_AMT IS NULL)
	AND (A.AMORT_MONTH = @p_AMORT_MONTH OR  @p_AMORT_MONTH IS NULL)
	AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
	AND (A.NOTES = @p_NOTES OR  @p_NOTES IS NULL OR @p_NOTES = '')
	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_ID IN (SELECT BRANCH_ID from @tmp))
		OR ((@P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID) 
		OR (@p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)))
	AND		(CONVERT(DATE,A.CREATE_DT) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = '')
	AND		(CONVERT(DATE,A.CREATE_DT) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = '')
	AND (A.ASS_STATUS = @p_ASS_STATUS OR @p_ASS_STATUS IS NULL OR @p_ASS_STATUS = '')	--KHANGTH, 02/12/2019
	ORDER BY A.CREATE_DT DESC
-- PAGING END
ELSE
-- PAGING BEGIN
	SELECT TOP(CONVERT(INT,@P_TOP))A.*, B.GROUP_CODE, B.GROUP_NAME, C.AUTH_STATUS_NAME, D.ASSET_CODE, D.SPECIAL_ASS, D.TYPE_ID
	-- SELECT END
	FROM ASS_UPDATE A 
	LEFT JOIN ASS_GROUP B ON B.GROUP_ID = A.GROUP_ID
	LEFT JOIN CM_AUTH_STATUS C ON C.AUTH_STATUS = A.AUTH_STATUS
	LEFT JOIN ASS_MASTER D ON D.ASSET_ID = A.ASSET_ID
	WHERE 1 = 1
		AND (A.UPDATE_ID LIKE '%' + @p_UPDATE_ID + '%' OR  @p_UPDATE_ID IS NULL OR @p_UPDATE_ID = '')
		-- AND (A.ASSET_ID LIKE '%' + @p_ASSET_ID + '%' OR  @p_ASSET_ID IS NULL OR @p_ASSET_ID = '')
		AND (D.ASSET_CODE LIKE '%' + @p_ASSET_ID + '%' OR  @p_ASSET_ID IS NULL OR @p_ASSET_ID = '')
		AND (D.TYPE_ID LIKE '%' + @p_ASSET_TYPE + '%' OR  @p_ASSET_TYPE IS NULL OR @p_ASSET_TYPE = '')
		AND (A.GROUP_ID LIKE '%' + @p_GROUP_ID + '%' OR  @p_GROUP_ID IS NULL OR @p_GROUP_ID = '')
		AND (A.ASSET_NAME LIKE '%' + @p_ASSET_NAME + '%' OR  @p_ASSET_NAME IS NULL OR @p_ASSET_NAME = '')
		AND (A.ASSET_SERIAL_NO LIKE '%' + @p_ASSET_SERIAL_NO + '%' OR  @p_ASSET_SERIAL_NO IS NULL OR @p_ASSET_SERIAL_NO = '')
		AND (A.ASSET_DESC LIKE '%' + @p_ASSET_DESC + '%' OR  @p_ASSET_DESC IS NULL OR @p_ASSET_DESC = '')
		AND (A.BUY_PRICE = @p_BUY_PRICE OR  @p_BUY_PRICE IS NULL)
		AND (A.AMORT_AMT = @p_AMORT_AMT OR  @p_AMORT_AMT IS NULL)
		AND (A.AMORT_MONTH = @p_AMORT_MONTH OR  @p_AMORT_MONTH IS NULL)
		AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
		AND (A.NOTES = @p_NOTES OR  @p_NOTES IS NULL OR @p_NOTES = '')
		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_ID IN (SELECT BRANCH_ID from @tmp))
			OR ((@P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID) 
			OR (@p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)))
		AND		(CONVERT(DATE,A.CREATE_DT) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = '')
		AND		(CONVERT(DATE,A.CREATE_DT) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = '')
		AND (A.ASS_STATUS = @p_ASS_STATUS OR @p_ASS_STATUS IS NULL OR @p_ASS_STATUS = '')	--KHANGTH, 02/12/2019
		ORDER BY A.CREATE_DT DESC
-- PAGING END
END -- PAGING