AMS - VB

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 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US WHERE US.TLNANME = @p_USER_LOGIN)

  OR 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 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 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 A.AUTH_STATUS = 'A' THEN N'Mới tạo' 

            WHEN A.IS_REJECTED = 0 THEN (SELECT CRP.DESCRIBE FROM CM_REQUEST_PROCESS CRP WHERE CRP.REQ_ID = A.LIQ_REQ_ID AND CRP.DONE = 0)

            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  A.AUTH_STATUS = 'L' 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 A.AUTH_STATUS = 'A' THEN N'Mới tạo' 

            WHEN A.IS_REJECTED = 0 THEN (SELECT CRP.DESCRIBE FROM CM_REQUEST_PROCESS CRP WHERE CRP.REQ_ID = A.LIQ_REQ_ID AND CRP.DONE = 0)

            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  A.AUTH_STATUS = 'L' 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