EOFFICE - GD4

ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_MOBILE_Search] @p_REQ_ID VARCHAR(15) = NULL,

@p_REQ_CODE NVARCHAR(100) = NULL,

@p_REQ_NAME NVARCHAR(200) = NULL,

@p_REQ_DT VARCHAR(20) = NULL,

@p_REQ_TYPE INT = NULL,

@p_REQ_CONTENT NVARCHAR(1000) = NULL,

@p_REQ_REASON NVARCHAR(500) = NULL,

@p_TOTAL_AMT DECIMAL = 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_PROCESS_ID VARCHAR(15) = NULL,

@p_BRANCH_ID VARCHAR(15) = NULL,

@p_DEP_ID VARCHAR(15) = NULL,

@p_BRANCH_LOGIN VARCHAR(15),

@p_ROLE_USER VARCHAR(20),

@p_TLNAME_USER VARCHAR(15),

@p_FR_DATE VARCHAR(20) = NULL,

@p_TO_DATE VARCHAR(20) = NULL,

@p_TYPE_TRANFER VARCHAR(15) = NULL,

@p_TYPE VARCHAR(15) = NULL,

@p_YEAR INT = NULL,

@p_TOP INT = 10,

@p_IS_TRANSFER VARCHAR(10) = NULL,

@p_NGUOIXULY NVARCHAR(15) = NULL

AS

BEGIN -- PAGING

  DECLARE @MENU_PERMISSION NVARCHAR(500) = ''--permission chức năng call store, do chưa check dc mobile nên gán = ''

  DECLARE @TABLE_ROLE TABLE (

    ROLE_ID VARCHAR(20)

  )

  INSERT INTO @TABLE_ROLE

    VALUES (@p_ROLE_USER)

  INSERT INTO @TABLE_ROLE

    SELECT

      ROLE_NEW

    FROM dbo.TL_SYS_ROLE_MAPPING

    WHERE ROLE_OLD = @p_ROLE_USER

  DECLARE @DEP_ID VARCHAR(15)

         ,@BRANCH_TYPE VARCHAR(15)

  DECLARE @COST_ID TABLE (

    COST_ID VARCHAR(15)

  )

  DECLARE @DVDM_ID TABLE (

    DVDM_ID VARCHAR(15)

  )

  SET @DEP_ID = (SELECT

      SECUR_CODE

    FROM dbo.TL_USER

    WHERE TLNANME = @p_TLNAME_USER)

  SET @BRANCH_TYPE = (SELECT

      BRANCH_TYPE

    FROM dbo.CM_BRANCH

    WHERE BRANCH_ID = @p_BRANCH_LOGIN)

  IF (EXISTS (SELECT

        DEP_CODE

      FROM dbo.CM_DEPARTMENT

      WHERE DEP_ID = @DEP_ID

      AND DEP_CODE LIKE '069%')

    )

  BEGIN

    SET @BRANCH_TYPE = 'HS'

  END

  SELECT

    * INTO #AUTHOR

  FROM [dbo].[FN_GET_ROLE_USER_BY_TLNAME](@p_TLNAME_USER, @MENU_PERMISSION)

  SELECT

    * INTO #AUTHOR_DVDM

  FROM [dbo].[FN_GET_ROLE_DVDM_USER_BY_TLNAME](@p_TLNAME_USER, @MENU_PERMISSION)

  --- END LUCTV 16.11.2022

  INSERT INTO @COST_ID

    SELECT

      COST_ID

    FROM dbo.PL_COSTCENTER_DT

    WHERE DEP_ID = @DEP_ID

    AND BRANCH_ID = @p_BRANCH_LOGIN

  INSERT INTO @DVDM_ID

    SELECT

      DVDM_ID

    FROM dbo.PL_COSTCENTER

    WHERE COST_ID IN (SELECT

        COST_ID

      FROM @COST_ID)

    GROUP BY DVDM_ID

  ----- GIANT 23/12/2021

  IF (@p_FR_DATE IS NULL)

  BEGIN

    SET @p_FR_DATE = GETDATE()

    SET @p_FR_DATE = DATEADD(YEAR, -1, @p_FR_DATE)

  END

  -----

  -- PAGING BEGIN

  SELECT

    A.REQ_ID

   ,A.REQ_CODE

   ,A.REQ_CONTENT

   ,A.TOTAL_AMT

   ,'U' AS AUTH_STATUS

   ,--A.AUTH_STATUS,

    CASE

      WHEN G.BRANCH_TYPE = 'HS' THEN ISNULL(G.BRANCH_NAME, '') + ' - ' + ISNULL(DEP.DEP_NAME, '')

      ELSE ISNULL(G.BRANCH_NAME, '')

    END AS BRANCH_NAME

   ,N'Chờ duyệt' AS AUTH_STATUS_NAME

  -- SELECT END

  FROM PL_REQUEST_DOC A

  LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP

    ON PLRP.REQ_ID = A.REQ_ID

      AND PLRP.STATUS = 'C'

  --LEFT JOIN dbo.PL_REQUEST_PROCESS RPN ON RPN.REQ_ID=A.REQ_ID AND RPN.STATUS = 'C'

  --LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	

  --LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS

  LEFT JOIN CM_BRANCH G

    ON A.BRANCH_ID = G.BRANCH_ID

  LEFT JOIN dbo.CM_DEPARTMENT DEP

    ON DEP.DEP_ID = A.DEP_ID

  LEFT JOIN dbo.PL_REQUEST_PROCESS RPN

    ON RPN.ID IN (SELECT TOP 1

          Temp.ID

        FROM dbo.PL_REQUEST_PROCESS Temp

        WHERE Temp.REQ_ID = A.REQ_ID

        AND (Temp.STATUS = 'C'

        OR Temp.STATUS = 'R'))

  LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPNC

    ON RPNC.PROCESS_ID IN (SELECT TOP 1

          Temp.ID

        FROM dbo.PL_REQUEST_PROCESS Temp

        WHERE Temp.REQ_ID = A.REQ_ID

        AND (Temp.STATUS = 'C'

        OR Temp.STATUS = 'R')

        AND Temp.IS_HAS_CHILD = 1)

      AND (RPNC.STATUS_JOB = 'C'

        OR RPNC.STATUS_JOB = 'R')

  OUTER APPLY (SELECT

      [dbo].[FN_PL_REQUEST_DOC_GET_NEXT_USER](A.REQ_ID, @MENU_PERMISSION) NGUOIXULY

     ,'' NGUOIXULYTLNAME) NXL

  --LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')

  --		LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE

  --		LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE

  --		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID

  --		LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID

  --		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID

  --		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME

  --		LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID

  --		LEFT JOIN dbo.CM_PROCESS_STATUS PS ON PS.PROCESS_ID = A.PROCESS_ID

  --		LEFT JOIN dbo.CM_PROCESS_STATUS PSN ON PSN.PROCESS_ID = A.PROCESS_ID

  --		LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID

  --		LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_ID

  --		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME

  WHERE 1 = 1

  --    AND (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' OR A.AUTH_STATUS = @p_AUTH_STATUS)

  AND (@p_REQ_CODE IS NULL

  OR @p_REQ_CODE = ''

  OR A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%')

  --		AND (@p_REQ_ID IS NULL OR @p_REQ_ID = '' OR A.REQ_ID LIKE '%' + @p_REQ_ID + '%')

  AND (@p_REQ_CONTENT IS NULL

  OR @p_REQ_CONTENT = ''

  OR A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%')

  AND (@p_REQ_REASON IS NULL

  OR @p_REQ_REASON = ''

  OR A.REQ_REASON LIKE '%' + @p_REQ_REASON + '%')

  --		AND (@p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0 OR A.TOTAL_AMT=@p_TOTAL_AMT)

  --		AND (@p_REQ_NAME IS NULL OR @p_REQ_NAME = '' OR A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%')	

  AND (@p_MAKER_ID IS NULL

  OR @p_MAKER_ID = ''

  OR A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%')

  --		AND (@p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '' OR A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%')

  --		AND	(@p_CREATE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_CREATE_DT,103)) = 0) 

  --		AND	(@p_APPROVE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_APPROVE_DT,103)) = 0) 

  --		AND	(@p_REQ_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_REQ_DT,103)) = 0) 

  --		AND (@p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1 OR A.REQ_TYPE = @p_REQ_TYPE)

  --AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))

  AND A.RECORD_STATUS = '1'

  --		AND(@p_REQ_TYPE = -1  OR @p_REQ_TYPE IS NULL

  --			OR(@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID))

  --			OR(@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID))

  --    )

  --PHONGNT 12/02/2023 Thêm điều kiện TĐV thấy phiếu

  AND ((RPN.PROCESS_ID = 'APPNEW'

  AND EXISTS (SELECT

      1

    FROM #AUTHOR_DVDM

    WHERE ROLE_DISPLAYNAME = RPN.ROLE_USER

    AND BRANCH_ID = RPN.BRANCH_ID

    AND ((BRANCH_ID = 'DV0001'

    AND DEP_ID = RPN.DEP_ID)

    OR (BRANCH_ID <> 'DV0001')))

  AND (PLRP.IS_HAS_CHILD = 0

  OR PLRP.IS_HAS_CHILD IS NULL))

  OR (RPNC.TLNAME = @p_TLNAME_USER

  AND RPNC.STATUS_JOB = 'C'

  AND RPNC.TYPE_JOB = 'TP')

  --END

  OR ((RPN.PROCESS_ID = 'TC'

  AND EXISTS (SELECT

      1

    FROM #AUTHOR_DVDM

    WHERE ROLE_DISPLAYNAME = RPN.ROLE_USER)

  )

  AND (RPN.SUB_PROCESS_ID IS NULL

  OR RPN.SUB_PROCESS_ID NOT LIKE '%DVDC%'))

  OR ((RPN.PROCESS_ID IN ('DVCM')

  AND EXISTS (SELECT

      1

    FROM #AUTHOR_DVDM

    WHERE ROLE_DISPLAYNAME = RPN.ROLE_USER

    AND DVDM_ID = RPN.DVDM_ID)

  ))

  OR EXISTS (SELECT

      AUTH.ROLE_DISPLAYNAME

    FROM #AUTHOR_DVDM AUTH

    WHERE AUTH.ROLE_DISPLAYNAME = PLRP.ROLE_USER

    AND (

    PLRP.DVDM_ID = AUTH.DVDM_ID

    OR ((PLRP.DVDM_ID = ''

    OR PLRP.DVDM_ID IS NULL)

    AND (PLRP.BRANCH_ID IS NULL

    OR PLRP.BRANCH_ID = ''))

    )

    AND (PLRP.IS_HAS_CHILD = 0

    OR PLRP.IS_HAS_CHILD IS NULL))

  )

  --AND PLRP.ROLE_USER IN (SELECT ROLE_ID FROM @TABLE_ROLE) 

  --AND (

  --  (PLRP.PROCESS_ID='DVDC' AND PLRP.BRANCH_ID=@p_BRANCH_LOGIN AND (PLRP.DEP_ID=@DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID='')) 

  --OR ( PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @DVDM_ID))

  --OR

  --( (PLRP.DVDM_ID ='' OR PLRP.DVDM_ID  IS NULL) AND (PLRP.BRANCH_ID IS NULL OR PLRP.BRANCH_ID=''))	

  --)

  AND (@p_YEAR IS NULL

  OR @p_YEAR = 0

  OR YEAR(A.REQ_DT) = @p_YEAR)

  --AND (( @p_AUTH_STATUS='A' AND  PLRP.STATUS='P') OR( @p_AUTH_STATUS='U' AND (PLRP.STATUS='C')) OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')

  AND (@p_FR_DATE IS NULL

  OR @p_REQ_CODE IS NOT NULL

  OR CONVERT(DATETIME, A.REQ_DT, 103) >= CONVERT(DATETIME, @p_FR_DATE, 103))

  AND (@p_TO_DATE IS NULL

  OR @p_REQ_CODE IS NOT NULL

  OR CONVERT(DATETIME, A.REQ_DT, 103) <= CONVERT(DATETIME, @p_TO_DATE, 103))

  --		AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = ''

  --			OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY  + '%')

  --AND(

  --	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')

  --	OR

  --	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')

  --)

  AND (A.BRANCH_ID = @p_BRANCH_ID

  OR @p_BRANCH_ID IS NULL

  OR @p_BRANCH_ID = '')

  --AND (A.DEP_ID = @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID = '')

  -- GIANT 26/10/2021

  AND (EXISTS (SELECT

      PL_REQUEST_DOC_DT.REQ_ID

    FROM PL_REQUEST_DOC_DT

    WHERE PL_REQUEST_DOC_DT.REQ_ID = A.REQ_ID)

  )

  AND (A.PROCESS_ID IN ('GDK_TT', 'PTGDK_TT', 'TGD', 'HDQT', 'GDK_DC', 'GDK_TC', 'APPNEW', 'DVCM', 'TC', 'DVDC')) -- LUCTV 24.11.2022 BO SUNG DIEU KIEN DE CHAN KHONG CHO LANH DAO TIM KIEM NEU PHIEU CHUA TOI

  ORDER BY A.CREATE_DT DESC

-- PAGING END

END -- PAGING