ALTER PROCEDURE [dbo].[CM_HANGHOA_Search]
	@p_HH_CODE varchar(50) = NULL,
	@p_HH_NAME nvarchar(200) = NULL,
	@p_HH_GROUP_ID VARCHAR(20) = NULL,
	@p_HH_TYPE_ID varchar(15) = NULL,
	@p_DESCRIPTION nvarchar(2000) = NULL,
	@p_SUP_ID varchar(15) = NULL,
	@p_PRICE decimal(18) = NULL,
	@p_UNIT_ID varchar(15) = NULL,
	@p_NOTES nvarchar(1000) = NULL,
	@p_RECORD_STATUS varchar(1) = NULL,
	@p_MAKER_ID varchar(12) = NULL,
	@p_CREATE_DT VARCHAR(20) = NULL,
	@p_AUTH_STATUS varchar(50) = NULL,
	@p_CHECKER_ID varchar(12) = NULL,
	@p_APPROVE_DT VARCHAR = NULL,
	@p_GD_ID varchar(15) = NULL,
	--@p_HHGROUP_HH_ID varchar(20) = NULL,
	--@p_HHGROUP_GROUP_CDT_ID varchar(20) = NULL,
	--@p_HHGROUP_GROUP_TTCT_ID varchar(20) = NULL,
	--@p_HHGROUP_TYPE_LIMIT varchar(20) = NULL,
	@P_TOP INT = 1000

AS
BEGIN -- PAGING
	IF(@P_TOP is null OR @P_TOP = '' OR @P_TOP = 0)
	-- PAGING BEGIN
		SELECT A.*, B.UNIT_CODE, B.UNIT_NAME, C.HH_TYPE_NAME, C.HH_TYPE_CODE,
		D.HH_GROUP_CODE, D.HH_GROUP_NAME, D.HH_GROUP_ID, E.AUTH_STATUS_NAME,F.CONTENT AS RECORD_STATUS_NAME,
		--G.GD_CODE, G.GD_NAME, 
		GRCDT.GROUP_LIMIT_ID AS HHGROUP_GROUP_ID_CDT, GRTTCT.GROUP_LIMIT_ID AS HHGROUP_GROUP_ID_TTCT, DVDM.DVCM_ID --Column all NULL
		-- SELECT END
		FROM dbo.CM_HANGHOA A
		left join CM_UNIT B on A.UNIT_ID = B.UNIT_ID
		left join dbo.CM_HANGHOA_TYPE C on A.HH_TYPE_ID = C.HH_TYPE_ID
		left join dbo.CM_HANGHOA_GROUP D on C.HH_GROUP_ID = D.HH_GROUP_ID
		LEFT JOIN CM_AUTH_STATUS E ON A.AUTH_STATUS = E.AUTH_STATUS
		LEFT JOIN CM_ALLCODE F ON F.CDNAME = 'RECORD_STATUS' AND F.CDVAL = A.RECORD_STATUS 
		--left join CM_SUPPLIER E on E.SUP_ID = A.SUP_ID
		--left join CM_GOODS G ON G.GD_ID = A.GD_ID
		left join SYS_HH_GROUP_LIMIT GRCDT ON GRCDT.HH_ID = A.HH_ID AND GRCDT.TYPE_LIMIT = 'CDT'
		left join SYS_HH_GROUP_LIMIT GRTTCT ON GRTTCT.HH_ID = A.HH_ID AND GRTTCT.TYPE_LIMIT = 'TTCT'
		left join CM_HANGHOA_DVCM DVDM ON DVDM.HH_ID = A.HH_ID
		WHERE 1=1
		AND (A.HH_CODE LIKE '%' + @p_HH_CODE + '%' OR @p_HH_CODE IS NULL OR @p_HH_CODE = '')
		AND (A.HH_NAME LIKE '%' + @p_HH_NAME + '%' OR @p_HH_NAME IS NULL OR @p_HH_NAME = '')
		AND (A.HH_TYPE_ID LIKE '%' + @p_HH_TYPE_ID + '%' OR @p_HH_TYPE_ID IS NULL OR @p_HH_TYPE_ID = '')
		AND (D.HH_GROUP_CODE LIKE '%' + @p_HH_GROUP_ID + '%' OR @p_HH_GROUP_ID IS NULL OR @p_HH_GROUP_ID = '')
		AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
		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 (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) =0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		--AND (A.GD_ID LIKE '%' + @p_GD_ID + '%' OR @p_GD_ID IS NULL OR @p_GD_ID = '')
		AND A.RECORD_STATUS = '1'
		ORDER BY A.CREATE_DT DESC
		-- PAGING END
	ELSE
	-- PAGING BEGIN
		SELECT TOP(@P_TOP) A.*, B.UNIT_CODE, B.UNIT_NAME, C.HH_TYPE_NAME, C.HH_TYPE_CODE, 
		D.HH_GROUP_CODE, D.HH_GROUP_NAME ,D.HH_GROUP_ID, E.AUTH_STATUS_NAME,F.CONTENT AS RECORD_STATUS_NAME,
		--G.GD_CODE, G.GD_NAME, 
		GRCDT.GROUP_LIMIT_ID AS HHGROUP_GROUP_ID_CDT,
		GRTTCT.GROUP_LIMIT_ID AS HHGROUP_GROUP_ID_TTCT, DVDM.DVCM_ID --Column all NULL
		-- SELECT END
		FROM dbo.CM_HANGHOA A
		LEFT JOIN CM_UNIT B on A.UNIT_ID = B.UNIT_ID
		LEFT JOIN dbo.CM_HANGHOA_TYPE C on A.HH_TYPE_ID = C.HH_TYPE_ID
		LEFT JOIN dbo.CM_HANGHOA_GROUP D on C.HH_GROUP_ID = D.HH_GROUP_ID
		LEFT JOIN CM_AUTH_STATUS E ON A.AUTH_STATUS = E.AUTH_STATUS
		LEFT JOIN CM_ALLCODE F ON F.CDNAME = 'RECORD_STATUS' AND F.CDVAL = A.RECORD_STATUS 
		--left join CM_SUPPLIER E on E.SUP_ID = A.SUP_ID
		--LEFT JOIN CM_GOODS G ON G.GD_ID = A.GD_ID
		LEFT JOIN SYS_HH_GROUP_LIMIT GRCDT ON GRCDT.HH_ID = A.HH_ID AND GRCDT.TYPE_LIMIT = 'CDT'
		LEFT JOIN SYS_HH_GROUP_LIMIT GRTTCT ON GRTTCT.HH_ID = A.HH_ID AND GRTTCT.TYPE_LIMIT = 'TTCT'
		LEFT JOIN CM_HANGHOA_DVCM DVDM ON DVDM.HH_ID = A.HH_ID
		WHERE 1=1
		AND (A.HH_CODE LIKE '%' + @p_HH_CODE + '%' OR @p_HH_CODE IS NULL OR @p_HH_CODE = '')
		AND (A.HH_NAME LIKE '%' + @p_HH_NAME + '%' OR @p_HH_NAME IS NULL OR @p_HH_NAME = '')
		AND (A.HH_TYPE_ID LIKE '%' + @p_HH_TYPE_ID + '%' OR @p_HH_TYPE_ID IS NULL OR @p_HH_TYPE_ID = '')
		--AND (C.HH_GROUP_ID LIKE '%' + @p_HH_GROUP_ID + '%' OR @p_HH_GROUP_ID IS NULL OR @p_HH_GROUP_ID = '')	
		AND (D.HH_GROUP_CODE LIKE '%' + @p_HH_GROUP_ID + '%' OR @p_HH_GROUP_ID IS NULL OR @p_HH_GROUP_ID = '')
		AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
		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 (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) =0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		--AND (A.GD_ID LIKE '%' + @p_GD_ID + '%' OR @p_GD_ID IS NULL OR @p_GD_ID = '')
		AND A.RECORD_STATUS = '1'
		ORDER BY A.CREATE_DT DESC
		-- PAGING END
END -- PAGING
--EXEC CM_GOODS_Search '','','','','',NULL,'','','','','','','','',''