AMS - VB

/*

	[CM_EMPLOYEE_Search] '','','Nguyễn Minh Tuân','DV0002','','','','','','','','','','100', 'ALL'

*/

ALTER PROCEDURE dbo.CM_EMPLOYEE_Search

	@p_EMP_ID	varchar(15) = null ,

	@p_EMP_CODE	varchar(15) = NULL ,

	@p_EMP_NAME	nvarchar(50) = NULL ,

	@p_BRANCH_ID	varchar(15) = NULL ,

	@p_BRANCH_CODE	varchar(15) = NULL ,

  @p_BRANCH_NAME NVARCHAR(50) = null,

	@p_DEP_ID	varchar(15) = NULL ,

	@p_NOTES	nvarchar(1000) = NULL ,

	@p_RECORD_STATUS	varchar(1) = NULL ,

	@p_AUTH_STATUS	varchar(1) = NULL ,

	@p_MAKER_ID	varchar(100) = NULL ,

	@p_CREATE_DT	VARCHAR(20) = NULL,

	@p_CHECKER_ID	varchar(100) = NULL ,

	@p_APPROVE_DT	VARCHAR(20) = NULL,

	@p_TOP INT = NULL,

	@p_LEVEL varchar(10) = NULL

AS

BEGIN -- PAGING

DECLARE @l_HS_ID VARCHAR(15) = (SELECT TOP 1 A.BRANCH_ID FROM CM_BRANCH A WHERE A.BRANCH_TYPE = 'HS')

declare @tmp table(BRANCH_ID varchar(15))

insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)

IF (@p_LEVEL = 'ALL' AND @p_BRANCH_ID <> @l_HS_ID)

INSERT INTO @tmp SELECT @l_HS_ID

IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0)

-- PAGING BEGIN

SELECT A.*,C.AUTH_STATUS_NAME,B.BRANCH_CODE,B.BRANCH_NAME,

		--dbo.FN_GET_CHINHANH(B.BRANCH_ID,'KV') KHU_VUC,

		dbo.FN_GET_CHINHANH(B.BRANCH_ID,'CN') CHI_NHANH,

    --B.BRANCH_NAME PGD,

    D.DEP_NAME PGD,

    CR.REGION_NAME AS KHUVUC,

    D.DEP_NAME,

    E.CONTENT AS RECORD_STATUS_NAME

    --, U.PhoneNumber AS PHONE_NUMBER,L.USER_DOMAIN

-- SELECT END

FROM CM_EMPLOYEE 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_DEPARTMENT D ON A.DEP_ID=D.DEP_ID

LEFT JOIN CM_ALLCODE E ON E.CDNAME = 'RECORD_STATUS' AND E.CDVAL = A.RECORD_STATUS

--LEFT JOIN CM_EMPLOYEE_LOG L ON L.EMP_CODE = A.EMP_CODE

--LEFT JOIN TL_USER U ON U.TLNANME = L.USER_DOMAIN

LEFT JOIN CM_REGIONS CR ON CR.REGION_ID = B.REGION_ID

 WHERE 1 = 1

 AND (A.EMP_ID LIKE '%' + @p_EMP_ID + '%' OR  @p_EMP_ID IS NULL OR @p_EMP_ID = '')

 AND (A.EMP_CODE LIKE '%' + @p_EMP_CODE + '%' OR  @p_EMP_CODE IS NULL OR @p_EMP_CODE = '') 

 AND (A.EMP_NAME  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_EMP_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_EMP_NAME IS NULL OR @p_EMP_NAME = '')		

 --AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')

 AND (B.BRANCH_CODE LIKE '%' + @p_BRANCH_CODE + '%' OR  @p_BRANCH_CODE IS NULL OR @p_BRANCH_CODE = '')

 AND (A.DEP_ID LIKE '%' + @p_DEP_ID + '%' OR  @p_DEP_ID IS NULL OR @p_DEP_ID = '')

 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.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_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.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.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))

 AND A.RECORD_STATUS = '1'

 AND ((@p_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp)

	OR (@p_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID)) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL

	)

-- PAGING END

ELSE

-- PAGING BEGIN

SELECT TOP(CONVERT(INT,@p_TOP))A.*,C.AUTH_STATUS_NAME,B.BRANCH_CODE,B.BRANCH_NAME,

		--dbo.FN_GET_CHINHANH(B.BRANCH_ID,'KV') KHU_VUC,

		dbo.FN_GET_CHINHANH(B.BRANCH_ID,'CN') CHI_NHANH,

    --B.BRANCH_NAME PGD,

    D.DEP_NAME PGD,

    CR.REGION_NAME AS KHUVUC,

    D.DEP_NAME,

    E.CONTENT AS RECORD_STATUS_NAME

    --, U.PhoneNumber AS PHONE_NUMBER,L.USER_DOMAIN

-- SELECT END

FROM CM_EMPLOYEE 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_DEPARTMENT D ON A.DEP_ID=D.DEP_ID

LEFT JOIN CM_ALLCODE E ON E.CDNAME = 'RECORD_STATUS' AND E.CDVAL = A.RECORD_STATUS

--LEFT JOIN CM_EMPLOYEE_LOG L ON L.EMP_CODE = A.EMP_CODE

--LEFT JOIN TL_USER U ON U.TLNANME = L.USER_DOMAIN

LEFT JOIN CM_REGIONS CR ON CR.REGION_ID = B.REGION_ID

 WHERE 1 = 1

 AND (A.EMP_ID LIKE '%' + @p_EMP_ID + '%' OR  @p_EMP_ID IS NULL OR @p_EMP_ID = '')

 AND (A.EMP_CODE LIKE '%' + @p_EMP_CODE + '%' OR  @p_EMP_CODE IS NULL OR @p_EMP_CODE = '')

 AND (A.EMP_NAME  COLLATE Latin1_general_CI_AI LIKE N'%' + @p_EMP_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_EMP_NAME IS NULL OR @p_EMP_NAME = '')		

 --AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')

 AND (B.BRANCH_CODE LIKE '%' + @p_BRANCH_CODE + '%' OR  @p_BRANCH_CODE IS NULL OR @p_BRANCH_CODE = '')

 AND (A.DEP_ID LIKE '%' + @p_DEP_ID + '%' OR  @p_DEP_ID IS NULL OR @p_DEP_ID = '')

 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.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_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.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.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))

 AND A.RECORD_STATUS = '1'

 AND ((@p_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp)

	OR (@p_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID)) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL

	)

-- PAGING END

END -- PAGING