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

ALTER PROCEDURE [dbo].[CM_DEPARTMENT_COSTCENTER_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_TOP	INT = Null

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

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

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

 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 (B.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 = '1'

	--AND NOT EXISTS(SELECT * FROM dbo.PL_COSTCENTER CO LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.COST_ID = CO.COST_ID WHERE DT.BRANCH_ID=B.BRANCH_ID AND DT.DEP_ID=A.DEP_ID AND CO.RECORD_STATUS='1' AND CO.AUTH_STATUS='A' )

-- PAGING END

ELSE

-- PAGING BEGIN

SELECT TOP(CONVERT(INT,@P_TOP))A.*, B.BRANCH_CODE, B.BRANCH_NAME, C.AUTH_STATUS_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

 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 (B.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 = '1'

	--AND NOT EXISTS(SELECT * FROM dbo.PL_COSTCENTER CO LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.COST_ID = CO.COST_ID WHERE DT.BRANCH_ID=B.BRANCH_ID AND DT.DEP_ID=A.DEP_ID AND CO.RECORD_STATUS='1' AND CO.AUTH_STATUS='A' )

-- PAGING END

End -- PAGING