USE gAMSPro_BVB_v3_FINAL
GO

/*
	[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_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(15) = NULL ,
	@p_CREATE_DT	VARCHAR(20) = NULL,
	@p_CHECKER_ID	varchar(15) = 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 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)
IF (@P_LEVEL = 'ALL')
BEGIN
  INSERT INTO @tmp SELECT @l_HS_ID
  SET @p_DEP_ID=''
END



IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
-- PAGING BEGIN
SELECT A.*,C.AUTH_STATUS_NAME,D.DEP_NAME,B.BRANCH_CODE,
		dbo.FN_GET_CHINHANH(B.BRANCH_ID,'KV') KHU_VUC,
		dbo.FN_GET_CHINHANH(B.BRANCH_ID,'CN') CHI_NHANH,B.BRANCH_NAME PGD,E.CONTENT AS RECORD_STATUS_NAME
		,U.PhoneNumber AS PHONE_NUMBER
	,
        L.POS_CODE,L.POS_NAME, 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
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 LIKE '%' + @p_EMP_NAME + '%' 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

	)
--AND (L.USER_DOMAIN IN (SELECT TLNANME FROM TL_USER WHERE RoleName <>'DISABLE'))
-- PAGING END
ELSE
-- PAGING BEGIN

SELECT TOP(CONVERT(INT,@P_TOP))A.*,C.AUTH_STATUS_NAME,D.DEP_NAME,B.BRANCH_CODE,
		dbo.FN_GET_CHINHANH(B.BRANCH_ID,'KV') KHU_VUC,
		dbo.FN_GET_CHINHANH(B.BRANCH_ID,'CN') CHI_NHANH,B.BRANCH_NAME PGD,E.CONTENT AS RECORD_STATUS_NAME
		--,U.PhoneNumber AS PHONE_NUMBER
	,
        L.POS_CODE,L.POS_NAME, 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
 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 LIKE '%' + @p_EMP_NAME + '%' 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

	)
-- AND (L.USER_DOMAIN IN (SELECT TLNANME FROM TL_USER WHERE RoleName <>'DISABLE'))
-- PAGING END
END -- PAGING



GO