EOFFICE - GD4

ALTER PROCEDURE [dbo].[PL_REQUEST_TRANSFER_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),

@p_TYPE VARCHAR(15),

@p_YEAR INT,

@p_TOP INT = 10,

@p_IS_TRANSFER VARCHAR(10) = NULL,

@p_NGUOIXULY NVARCHAR(15) = NULL,

@p_TO_DEP_ID VARCHAR(15) = NULL,

@p_TO_BRANCH_ID VARCHAR(15) = NULL,

@p_FROM_DEP_ID VARCHAR(15) = NULL,

@p_FROM_BRANCH_ID VARCHAR(15) = NULL,

@p_TO_GD_NAME NVARCHAR(1000) = NULL,

@p_FROM_GD_NAME NVARCHAR(1000) = NULL

AS

BEGIN -- PAGING

  --	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 @MENU_PERMISSION NVARCHAR(500) = ''--permission chức năng call store, chưa check được mobile nên gán = ''

  DECLARE @DEP_ID VARCHAR(15)

         ,@BRANCH_TYPE VARCHAR(15)

         ,@TO_BRANCH_TYPE VARCHAR(15)

         ,@FROM_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)

  -- ĐƠN VỊ CHUYỂN

  SET @FROM_BRANCH_TYPE = (SELECT

      BRANCH_TYPE

    FROM dbo.CM_BRANCH

    WHERE BRANCH_ID = @p_FROM_BRANCH_ID)

  -- ĐƠN VỊ NHẬN

  SET @TO_BRANCH_TYPE = (SELECT

      BRANCH_TYPE

    FROM dbo.CM_BRANCH

    WHERE BRANCH_ID = @p_TO_BRANCH_ID)

  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)

  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

  -- PAGING BEGIN

  SELECT

    A.REQ_ID

   ,A.REQ_CODE

   ,G.BRANCH_NAME

   ,A.REQ_NAME

   ,A.TOTAL_AMT

   ,N'Chờ duyệt' AS AUTH_STATUS_NAME

   ,'U' AS AUTH_STATUS --A.AUTH_STATUS

  -- SELECT END

  FROM PL_REQUEST_DOC A

  INNER 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.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 IS NULL

        OR Temp.IS_HAS_CHILD = 0))

  LEFT JOIN CM_BRANCH G

    ON A.BRANCH_ID = G.BRANCH_ID

  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

  WHERE 1 = 1

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

  AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%'

  OR @p_REQ_CODE IS NULL

  OR @p_REQ_CODE = '')

  AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%'

  OR @p_REQ_NAME IS NULL

  OR @p_REQ_NAME = '')

  AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%'

  OR @p_MAKER_ID IS NULL

  OR @p_MAKER_ID = '')

  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 (A.REQ_TYPE = @p_REQ_TYPE

  OR @p_REQ_TYPE IS NULL

  OR @p_REQ_TYPE = -1)

  AND A.RECORD_STATUS = '1'

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

  AND ((RPN.PROCESS_ID = 'APPNEW'

  AND RPN.STATUS = 'C'

  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')

  --        OR (RPN.PROCESS_ID='TC' AND EXISTS(SELECT 1 FROM #AUTHOR_DVDM WHERE ROLE_ID=RPN.ROLE_USER AND BRANCH_ID =RPN.BRANCH_ID AND DEP_ID=RPN.DEP_ID))

  --   

  --        OR (RPN.PROCESS_ID IN ('DVDC','DVCM') AND EXISTS(SELECT 1 FROM #AUTHOR_DVDM WHERE ROLE_ID=RPN.ROLE_USER AND DVDM_ID= RPN.DVDM_ID)AND (PLRP.IS_HAS_CHILD=0 OR PLRP.IS_HAS_CHILD IS NULL))

  --END

  OR

  EXISTS (SELECT

      AUTH.ROLE_DISPLAYNAME

    FROM #AUTHOR_DVDM AUTH

    WHERE AUTH.ROLE_DISPLAYNAME = PLRP.ROLE_USER

    AND (

    (PLRP.PROCESS_ID = 'DVDC'

    AND PLRP.BRANCH_ID = AUTH.BRANCH_ID

    AND (PLRP.DEP_ID = AUTH.DEP_ID

    OR PLRP.DEP_ID IS NULL

    OR PLRP.DEP_ID = ''))

    OR 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)

  -- GiaNT 20/10/2021

  AND (A.REQ_ID IN (SELECT

      REQ_DOC_ID

    FROM PL_REQUEST_TRANSFER)

  )

  AND (NOT EXISTS (SELECT

      *

    FROM dbo.PL_REQUEST_DOC_DT

    WHERE REQ_ID = A.REQ_ID)

  )

  ORDER BY A.CREATE_DT DESC

-- PAGING END

END -- PAGING