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

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,'','','','','','','','',''