USE [gAMSPro_BVB_v3]
GO
/****** Object:  StoredProcedure [dbo].[CM_DVDM_Search]    Script Date: 22-Mar-22 2:09:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CM_DVDM_Search]
	@p_DVDM_CODE	varchar(15)  = NULL,
	@p_DVDM_NAME	Nvarchar(300)  = NULL,
	@p_NOTES	nvarchar(1000)  = NULL,
	@p_RECORD_STATUS	varchar(1)  = NULL,
	@p_MAKER_ID	varchar(20)  = NULL,
	@p_CREATE_DT	NVARCHAR(25) = NULL,
	@p_AUTH_STATUS	varchar(50)  = NULL,
	@p_CHECKER_ID	varchar(20)  = NULL,
	@p_APPROVE_DT	NVARCHAR(25) = NULL,
	@p_TYPE VARCHAR(20)= NULL,
	@p_TOP INT = 0
AS
BEGIN -- PAGING

	IF(@p_TYPE='DVDM')
	BEGIN 
		IF(@p_TOP IS NULL OR @p_TOP = 0)
		BEGIN
-- PAGING BEGIN
			SELECT A.*,B.AUTH_STATUS_NAME
-- SELECT END
			FROM dbo.CM_DVDM A
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
		
			WHERE 1 = 1
			AND (A.DVDM_NAME like '%' + @p_DVDM_NAME + '%' OR @p_DVDM_NAME IS NULL OR @p_DVDM_NAME = '')
			AND (A.DVDM_CODE like + '%' + @p_DVDM_CODE + '%' OR @p_DVDM_CODE IS NULL OR @p_DVDM_CODE = '')
			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.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.RECORD_STATUS = '1'
			AND A.IS_DVDM=1
-- PAGING END
		END;
		ELSE 
		BEGIN
-- PAGING BEGIN
			SELECT A.*,B.AUTH_STATUS_NAME
-- SELECT END
			FROM dbo.CM_DVDM A 
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
		
			WHERE 1 = 1
			AND (A.DVDM_NAME like '%' + @p_DVDM_NAME + '%' OR @p_DVDM_NAME IS NULL OR @p_DVDM_NAME = '')
			AND (A.DVDM_CODE like + '%' + @p_DVDM_CODE + '%' OR @p_DVDM_CODE IS NULL OR @p_DVDM_CODE = '')
			--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.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.RECORD_STATUS = '1'
			AND A.IS_DVDM=1
-- PAGING END
		END;
	END 



	
	ELSE IF(@p_TYPE='DVCM')
	BEGIN 
			IF(@p_TOP IS NULL OR @p_TOP = 0)
		BEGIN
-- PAGING BEGIN
			SELECT A.*,B.AUTH_STATUS_NAME
-- SELECT END
			FROM dbo.CM_DVDM A 
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
		
			WHERE 1 = 1
			AND (A.DVDM_NAME like '%' + @p_DVDM_NAME + '%' OR @p_DVDM_NAME IS NULL OR @p_DVDM_NAME = '')
			AND (A.DVDM_CODE like + '%' + @p_DVDM_CODE + '%' OR @p_DVDM_CODE IS NULL OR @p_DVDM_CODE = '')
			--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.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.RECORD_STATUS = '1'
			AND A.IS_DVCM=1
-- PAGING END
		END;
			ELSE 
		BEGIN
-- PAGING BEGIN
			SELECT A.*,B.AUTH_STATUS_NAME
-- SELECT END
			FROM dbo.CM_DVDM A 
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
		
			WHERE 1 = 1
			AND (A.DVDM_NAME like '%' + @p_DVDM_NAME + '%' OR @p_DVDM_NAME IS NULL OR @p_DVDM_NAME = '')
			AND (A.DVDM_CODE like + '%' + @p_DVDM_CODE + '%' OR @p_DVDM_CODE IS NULL OR @p_DVDM_CODE = '')
			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.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.RECORD_STATUS = '1'
			AND A.IS_DVCM=1
-- PAGING END
		END;
	END 


	
	ELSE IF(@p_TYPE='KHOI')
	BEGIN 
			IF(@p_TOP IS NULL OR @p_TOP = 0)
		BEGIN
-- PAGING BEGIN
			SELECT A.*,B.AUTH_STATUS_NAME
-- SELECT END
			FROM dbo.CM_DVDM A 
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
		
			WHERE 1 = 1
			AND (A.DVDM_NAME like '%' + @p_DVDM_NAME + '%' OR @p_DVDM_NAME IS NULL OR @p_DVDM_NAME = '')
			AND (A.DVDM_CODE like + '%' + @p_DVDM_CODE + '%' OR @p_DVDM_CODE IS NULL OR @p_DVDM_CODE = '')
			--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.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.RECORD_STATUS = '1'
			AND A.IS_KHOI=1
-- PAGING END
		END;
		ELSE 
		BEGIN
-- PAGING BEGIN
			SELECT A.*,B.AUTH_STATUS_NAME
-- SELECT END
			FROM dbo.CM_DVDM A 
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
		
			WHERE 1 = 1
			AND (A.DVDM_NAME like '%' + @p_DVDM_NAME + '%' OR @p_DVDM_NAME IS NULL OR @p_DVDM_NAME = '')
			AND (A.DVDM_CODE like + '%' + @p_DVDM_CODE + '%' OR @p_DVDM_CODE IS NULL OR @p_DVDM_CODE = '')
			--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.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.RECORD_STATUS = '1'
			AND A.IS_KHOI=1
-- PAGING END
		END;
	END
 
END -- PAGING