ALTER PROCEDURE dbo.ASS_LIQ_REQUEST_Search
@p_LIQ_REQ_ID	varchar(15)  = NULL,
@p_ASS_TYPE_ID	varchar(15)  = NULL,
@p_REQ_DT	VARCHAR(20) = NULL,
@p_BRANCH_ID	varchar(15)  = 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_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_TOP	INT = 10,
@p_BRANCH_CREATE	VARCHAR(15) = NULL,
@p_LEVEL	VARCHAR(50) = 'UNIT',
@p_TERM_ID VARCHAR(15) = NULL,
@p_USER_LOGIN VARCHAR(20)=NULL
AS


BEGIN -- PAGING

DECLARE @DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20)
DECLARE  @ROLE_LOGIN TABLE(USER_ROLE VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))

  INSERT INTO @ROLE_LOGIN
  SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr

	SET @DEP_ID= (SELECT DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN)
	SET @BRANCH_ID= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN)

	DECLARE @lstCOST TABLE (COST_ID VARCHAR(20))
	INSERT INTO @lstCOST
	SELECT COST_ID FROM dbo.CM_COSTCENTER_DT WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID



DECLARE @MAX_ID INT = (SELECT MAX(CRP.ID) FROM CM_REQUEST_PROCESS CRP WHERE CRP.REQ_ID = @p_LIQ_REQ_ID)

DECLARE @LIQ_REQ_ID_Temp TABLE(LIQ_REQ_ID VARCHAR(20))
INSERT INTO @LIQ_REQ_ID_Temp
SELECT A.LIQ_REQ_ID FROM ASS_LIQ_REQUEST A
LEFT JOIN CM_REQUEST_PROCESS B ON A.LIQ_REQ_ID = B.REQ_ID
WHERE (EXISTS(SELECT RL.USER_ROLE FROM @ROLE_LOGIN RL WHERE RL.BRANCH_ID = B.BRANCH_ID
    AND (B.BRANCH_ID = 'DV0001' AND B.DEP_ID = RL.DEP_ID OR B.BRANCH_ID <> 'DV0001')
    AND RL.USER_ROLE = B.ROLE)
  OR EXISTS(SELECT * FROM @ROLE_LOGIN RL
    JOIN CM_REQUEST_PROCESS C ON C.REQ_ID = A.LIQ_REQ_ID AND C.ID = @MAX_ID AND RL.BRANCH_ID = C.BRANCH_ID
    AND (C.BRANCH_ID = 'DV0001' AND C.DEP_ID = RL.DEP_ID OR C.BRANCH_ID <> 'DV0001')
    AND RL.USER_ROLE = C.ROLE)
  OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USER_LOGIN)
  OR A.MAKER_ID = @p_USER_LOGIN
  )
GROUP BY A.LIQ_REQ_ID


--SELECT DISTINCT BRANCH_ID,DEP_ID FROM CM_REQUEST_PROCESS WHERE PROCESS_KEY = 'AssLiqRequest' AND REQ_ID = @p_LIQ_REQ_ID



IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0)
-- PAGING BEGIN
SELECT A.*,C.BRANCH_NAME, C.BRANCH_CODE,E.TYPE_NAME, CB.BRANCH_CODE AS BRANCH_CODE_CREATE, CB.BRANCH_NAME AS BRANCH_NAME_CREATE,
	T.TERM_CODE, T.TERM_TYPE
	,CASE   WHEN A.IS_REJECTED = 0 AND CRP.STATUS = 'B' THEN N'Mới tạo' 
            WHEN A.IS_REJECTED = 0 THEN CRP.DESCRIBE
            WHEN A.IS_REJECTED = 1 THEN N'Từ chối'  
        ELSE CRP.DESCRIBE
    END AS DESCRIBE, CRP.STATUS
	,CASE  
      WHEN A.IS_REJECTED = 1 THEN N'Từ chối'
      WHEN A.IS_REJECTED = 0 AND CRP.STATUS = 'B' THEN N'Lưu nháp'
      WHEN A.IS_REJECTED = 0 AND  CRP.STATUS = 'M' THEN N'Đã duyệt'
      ELSE N'Chờ duyệt'
   END AS AUTH_STATUS_NAME
-- SELECT END
	FROM ASS_LIQ_REQUEST A
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	LEFT JOIN CM_BRANCH CB ON A.BRANCH_CREATE = CB.BRANCH_ID
	LEFT JOIN ASS_TYPE E ON A.ASS_TYPE_ID=E.TYPE_ID
	LEFT JOIN CM_TERM T ON T.TERM_ID = A.TERM_ID
	LEFT JOIN CM_REQUEST_PROCESS CRP ON CRP.REQ_ID = A.LIQ_REQ_ID AND CRP.DONE = 0
 WHERE 1 = 1
	AND (A.LIQ_REQ_ID LIKE '%' + @p_LIQ_REQ_ID + '%' OR  @p_LIQ_REQ_ID IS NULL OR @p_LIQ_REQ_ID = '')
	AND (A.ASS_TYPE_ID LIKE '%' + @p_ASS_TYPE_ID + '%' OR  @p_ASS_TYPE_ID IS NULL OR @p_ASS_TYPE_ID = '')
	AND (DATEDIFF(DAYOFYEAR, A.REQ_DT,CONVERT(DATETIME, @p_REQ_DT, 103)) = 0 OR  @p_REQ_DT IS NULL OR @p_REQ_DT = '')
	AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_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.RECORD_STATUS = '1'
  AND ((@p_AUTH_STATUS = 'A' AND A.AUTH_STATUS = 'L')
        OR (@p_AUTH_STATUS = 'E' AND A.AUTH_STATUS = 'B')
        OR (@p_AUTH_STATUS = 'U' AND A.AUTH_STATUS IN (SELECT * FROM STRING_SPLIT('C,E,Z,F,G,H,K', ',')))
        OR (@p_AUTH_STATUS = 'R' AND (A.AUTH_STATUS = 'R' OR A.IS_REJECTED = 1))
        OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
        )
	--AND (CRP.PROCESS_ID LIKE '%' + @p_AUTH_STATUS + '%' OR (CRP.PROCESS_ID IS NULL AND  @p_AUTH_STATUS<> N'U') OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
	AND (A.TERM_ID = @p_TERM_ID OR @p_TERM_ID IS NULL OR @p_TERM_ID = '')
	AND ((@p_STATUS = 'Z' AND A.IS_REJECTED = 1) OR (CRP.[STATUS] LIKE '%' + @p_STATUS + '%' OR  @p_STATUS IS NULL OR @p_STATUS = ''))
  AND EXISTS(SELECT * FROM @LIQ_REQ_ID_Temp RL WHERE RL.LIQ_REQ_ID = A.LIQ_REQ_ID)
  AND CRP.ROLE IN (SELECT USER_ROLE FROM @ROLE_LOGIN) 
  OR A.MAKER_ID = @p_USER_LOGIN
	ORDER BY A.CREATE_DT DESC
-- PAGING END
ELSE
---- PAGING BEGIN
SELECT TOP(CONVERT(INT,@p_TOP)) A.*,C.BRANCH_NAME, C.BRANCH_CODE,E.TYPE_NAME, CB.BRANCH_CODE AS BRANCH_CODE_CREATE, CB.BRANCH_NAME AS BRANCH_NAME_CREATE,
	T.TERM_CODE, T.TERM_TYPE
	,CASE   WHEN A.IS_REJECTED = 0 AND CRP.STATUS = 'B' THEN N'Mới tạo' 
            WHEN A.IS_REJECTED = 0 THEN CRP.DESCRIBE
            WHEN A.IS_REJECTED = 1 THEN N'Từ chối' 
        ELSE CRP.DESCRIBE
    END AS DESCRIBE, CRP.STATUS
	,CASE  
      WHEN A.IS_REJECTED = 1 THEN N'Từ chối'
      WHEN A.IS_REJECTED = 0 AND CRP.STATUS = 'B' THEN N'Lưu nháp'
      WHEN A.IS_REJECTED = 0 AND  CRP.STATUS = 'M' THEN N'Đã duyệt'
      ELSE N'Chờ duyệt'
   END AS AUTH_STATUS_NAME
-- SELECT END
	FROM ASS_LIQ_REQUEST A
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	LEFT JOIN CM_BRANCH CB ON A.BRANCH_CREATE = CB.BRANCH_ID
	LEFT JOIN ASS_TYPE E ON A.ASS_TYPE_ID=E.TYPE_ID
	LEFT JOIN CM_TERM T ON T.TERM_ID = A.TERM_ID
	LEFT JOIN CM_REQUEST_PROCESS CRP ON CRP.REQ_ID = A.LIQ_REQ_ID AND CRP.DONE = 0
 WHERE 1 = 1
	AND (A.LIQ_REQ_ID LIKE '%' + @p_LIQ_REQ_ID + '%' OR  @p_LIQ_REQ_ID IS NULL OR @p_LIQ_REQ_ID = '')
	AND (A.ASS_TYPE_ID LIKE '%' + @p_ASS_TYPE_ID + '%' OR  @p_ASS_TYPE_ID IS NULL OR @p_ASS_TYPE_ID = '')
	AND (DATEDIFF(DAYOFYEAR, A.REQ_DT,CONVERT(DATETIME, @p_REQ_DT, 103)) = 0 OR  @p_REQ_DT IS NULL OR @p_REQ_DT = '')
	AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_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 ((@p_AUTH_STATUS = 'A' AND A.AUTH_STATUS = 'L')
        OR (@p_AUTH_STATUS = 'E' AND A.AUTH_STATUS = 'B')
        OR (@p_AUTH_STATUS = 'U' AND A.AUTH_STATUS IN (SELECT * FROM STRING_SPLIT('C,E,Z,F,G,H,K', ',')))
        OR (@p_AUTH_STATUS = 'R' AND (A.AUTH_STATUS = 'R' OR A.IS_REJECTED = 1))
        OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
        )
	AND A.RECORD_STATUS = '1'
	--AND (CRP.PROCESS_ID LIKE '%' + @p_AUTH_STATUS + '%' OR (CRP.PROCESS_ID IS NULL AND  @p_AUTH_STATUS<> N'U') OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
	AND (A.TERM_ID = @p_TERM_ID OR @p_TERM_ID IS NULL OR @p_TERM_ID = '')
	AND ((@p_STATUS = 'Z' AND A.IS_REJECTED = 1) OR (CRP.[STATUS] LIKE '%' + @p_STATUS + '%' OR  @p_STATUS IS NULL OR @p_STATUS = ''))
  AND EXISTS(SELECT * FROM @LIQ_REQ_ID_Temp RL WHERE RL.LIQ_REQ_ID = A.LIQ_REQ_ID)
  AND CRP.ROLE IN (SELECT USER_ROLE FROM @ROLE_LOGIN)
  OR A.MAKER_ID = @p_USER_LOGIN
	ORDER BY A.CREATE_DT DESC
-- PAGING END
END -- PAGING