EOFFICE - GD4

ALTER    PROC dbo.rpt_REQ_DELIVERY_MASTER_Excel

    @p_EMP_ID VARCHAR(50)  = NULL,

    @p_DEP_ID	VARCHAR(50) = NULL,

    @p_fromdate VARCHAR(20) = NULL,

    @p_todate VARCHAR(20) = NULL,

    @p_BILL_STATUS VARCHAR(20) = NULL,

    @p_LEVEL VARCHAR(15) = NULL

AS

SELECT ROW_NUMBER() OVER (ORDER BY A.REQ_CODE) AS STT,

        A.FROM_DATE  

        ,A.REQ_CODE

        ,DT.REQ_DT_ID AS BILL_ID --,DT.BILL_ID

        ,ca.CONTENT AS TYPE_MAIL_NAME

        ,DT.EMP_REVEICE_NAME

        ,DT.PHONE

        ,DT.LOCATION

        ,ca1.CONTENT AS DV_CHUYEN_PHAT

        ,DICH_VU.ADD_SEVICE_NAME

        ,DT.QUANTITY

        ,DT.TRAN_COST,

    CASE 

       WHEN DT.BILL_STATUS IN ('TRANSPORT_AXIS', 'TRANSPORT_RECEIVE_TO_DELIVERY', 'PACKAGE_CONFIRM_GO_DELIVERY', 'TRANSPORT_INTERNAL') 

       THEN N'Đang vận chuyển'

       WHEN DT.BILL_STATUS IN ('ASSIGN_DELIVERY', 'DELIVERING')

       THEN N'Hàng đang đi giao/Đang hoàn (dựa vào is_return)'

      ELSE dss.STATUS_NAME

   END AS STATUS_NAME

	FROM  DELI_REQ_DELIVERY_MASTER A

  INNER JOIN DELI_REQ_DELIVERY_DT DT ON A.REQ_ID = DT.REQ_ID

 LEFT JOIN CM_ALLCODE ca ON ca.CDVAL = A.TYPE_MAIL AND ca.CDNAME = 'TYPE_MAIL' AND ca.CDTYPE = 'DELI'

 LEFT JOIN CM_ALLCODE ca1 ON ca1.CDVAL = A.TRAN_SV AND ca1.CDNAME = 'TRAN_SV' AND ca1.CDTYPE = 'DELI'

OUTER APPLY (  

        SELECT 

            STRING_AGG(B.ADD_SEVICE_NAME, ', ') AS ADD_SEVICE_NAME

        FROM DELI_ADD_SERVICE ASE

          INNER JOIN DELI_CM_ADD_SEVICE B ON ASE.ADD_ID = B.ID_ADD

          WHERE ASE.REQ_ID = A.REQ_ID

    ) AS DICH_VU

 LEFT JOIN DELI_SYNC_STATUS dss ON DT.BILL_STATUS = dss.STATUS_CODE

	WHERE 1=1

    AND (A.AUTH_STATUS= 'A') -- chỉ lấy những phiếu có trạng thái đã duyệt 

    AND(DT.BILL_ID IS NOT NULL AND DT.BILL_ID <> '') -- Chỉ lấy những phiếu có bill_id

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

    AND (A.EMP_ID = @p_EMP_ID OR  @p_EMP_ID IS NULL OR @p_EMP_ID ='')

    AND (@p_fromdate IS NULL OR @p_fromdate = '' OR CONVERT(DATE, A.FROM_DATE, 103) >= CONVERT(DATE, @p_fromdate, 103))

    AND (@p_todate IS NULL OR @p_todate = '' OR CONVERT(DATE, A.FROM_DATE, 103) <= CONVERT(DATE, @p_todate, 103))

   AND (

    (@p_BILL_STATUS = 'TRANS_GO' AND DT.BILL_STATUS IN ('TRANSPORT_AXIS', 'TRANSPORT_RECEIVE_TO_DELIVERY', 'PACKAGE_CONFIRM_GO_DELIVERY', 'TRANSPORT_INTERNAL'))

     OR(@p_BILL_STATUS = 'TRANS_GIAO' AND DT.BILL_STATUS IN ('DELIVERING', 'ASSIGN_DELIVERY'))

    OR (DT.BILL_STATUS LIKE '%' + @p_BILL_STATUS + '%' OR @p_BILL_STATUS IS NULL OR @p_BILL_STATUS = '')

)