USE [gAMSPro_BVB_v3]
GO
/****** Object:  StoredProcedure [dbo].[CM_DEPARTMENT_Search]    Script Date: 20/04/2022 10:49:32 am ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[CM_DEPARTMENT_Search]
@p_DEP_ID	varchar(15)  = NULL,
@p_DEP_CODE	varchar(15)  = NULL,
@p_DEP_NAME	nvarchar(200)  = NULL,
@p_DAO_CODE	varchar(40)  = NULL,
@p_DAO_NAME	nvarchar(500)  = NULL,
@p_BRANCH_ID	varchar(15)  = NULL,
@p_GROUP_ID	varchar(15)  = NULL,
@p_TEL	varchar(20)  = 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(20) = NULL,
@p_KHOI_ID		VARCHAR(20)=NULL,
@p_TOP	INT = 10
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
BEGIN TRANSACTION
IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
-- PAGING BEGIN
SELECT A.*, B.BRANCH_CODE, B.BRANCH_NAME, C.AUTH_STATUS_NAME, R.CONTENT AS RECORD_STATUS_NAME,FD.DEP_CODE +' - '+ FD.DEP_NAME as FATHER_NAME,D.DVDM_CODE, D.DVDM_NAME
-- SELECT END
FROM CM_DEPARTMENT A 
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
LEFT JOIN CM_ALLCODE R ON A.RECORD_STATUS = R.CDVAL AND R.CDNAME = 'RECORD_STATUS'
LEFT JOIN CM_DEPARTMENT FD ON A.FATHER_ID = FD.DEP_ID
LEFT JOIN CM_DVDM D ON A.KHOI_ID = D.DVDM_ID
 WHERE 1 = 1
	AND (A.DEP_ID LIKE '%' + @p_DEP_ID + '%' OR  @p_DEP_ID IS NULL OR @p_DEP_ID = '')
	AND (A.DEP_CODE LIKE '%' + @p_DEP_CODE + '%' OR  @p_DEP_CODE IS NULL OR @p_DEP_CODE = '')
	AND (A.DEP_NAME LIKE '%' + @p_DEP_NAME + '%' OR  @p_DEP_NAME IS NULL OR @p_DEP_NAME = '')
	AND (A.DAO_CODE LIKE '%' + @p_DAO_CODE + '%' OR  @p_DAO_CODE IS NULL OR @p_DAO_CODE = '')
	AND (A.DAO_NAME LIKE '%' + @p_DAO_NAME + '%' OR  @p_DAO_NAME IS NULL OR @p_DAO_NAME = '')
	AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
	AND (A.GROUP_ID LIKE '%' + @p_GROUP_ID + '%' OR  @p_GROUP_ID IS NULL OR @p_GROUP_ID = '')
	AND (A.TEL LIKE '%' + @p_TEL + '%' OR  @p_TEL IS NULL OR @p_TEL = '')
	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 = @p_RECORD_STATUS OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
	AND (A.KHOI_ID = @p_KHOI_ID OR  @p_KHOI_ID IS NULL OR @p_KHOI_ID = '')
	ORDER BY A.DEP_CODE
-- PAGING END
ELSE
-- PAGING BEGIN

SELECT TOP(CONVERT(INT,@P_TOP))A.*, B.BRANCH_CODE, B.BRANCH_NAME, C.AUTH_STATUS_NAME, R.CONTENT AS RECORD_STATUS_NAME,FD.DEP_CODE +' - '+  FD.DEP_NAME as FATHER_NAME,D.DVDM_CODE, D.DVDM_NAME
-- SELECT END
FROM CM_DEPARTMENT A 
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS=C.AUTH_STATUS
LEFT JOIN CM_ALLCODE R ON A.RECORD_STATUS = R.CDVAL AND R.CDNAME = 'RECORD_STATUS'
LEFT JOIN CM_DEPARTMENT FD ON A.FATHER_ID = FD.DEP_ID
LEFT JOIN CM_DVDM D ON A.KHOI_ID = D.DVDM_ID
 WHERE 1 = 1
	AND (A.DEP_ID LIKE '%' + @p_DEP_ID + '%' OR  @p_DEP_ID IS NULL OR @p_DEP_ID = '')
	AND (A.DEP_CODE LIKE '%' + @p_DEP_CODE + '%' OR  @p_DEP_CODE IS NULL OR @p_DEP_CODE = '')
	AND (A.DEP_NAME LIKE '%' + @p_DEP_NAME + '%' OR  @p_DEP_NAME IS NULL OR @p_DEP_NAME = '')
	AND (A.DAO_CODE LIKE '%' + @p_DAO_CODE + '%' OR  @p_DAO_CODE IS NULL OR @p_DAO_CODE = '')
	AND (A.DAO_NAME LIKE '%' + @p_DAO_NAME + '%' OR  @p_DAO_NAME IS NULL OR @p_DAO_NAME = '')
	AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
	AND (A.GROUP_ID LIKE '%' + @p_GROUP_ID + '%' OR  @p_GROUP_ID IS NULL OR @p_GROUP_ID = '')
	AND (A.TEL LIKE '%' + @p_TEL + '%' OR  @p_TEL IS NULL OR @p_TEL = '')
	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 = @p_RECORD_STATUS OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
	AND (A.KHOI_ID = @p_KHOI_ID OR  @p_KHOI_ID IS NULL OR @p_KHOI_ID = '')
	ORDER BY A.DEP_CODE
-- PAGING END
COMMIT TRANSACTION

END -- PAGING