-- 
-- 入荷予定リスト
-- 
CREATE OR REPLACE VIEW v_public_expected_receipts
AS
SELECT
    -- 営業公開入荷日
    rh.public_expected_receipt_date AS public_expected_receipt_date,
    DATE_FORMAT(rh.public_expected_receipt_date, '%Y/%m/%d') AS public_expected_receipt_date_df,
    -- 状況
    CASE
        WHEN pod.status IN (1, 2) THEN '入荷予定' COLLATE utf8mb4_unicode_ci
        WHEN pod.status = 3 THEN '入荷済' COLLATE utf8mb4_unicode_ci
        ELSE '' COLLATE utf8mb4_unicode_ci
    END AS status,
    -- 仕入先
    poh.supplier_id,
    -- 仕入先名
    poh.supplier_name,
    -- ETA
    DATE_FORMAT(rh.receipt_date, '%Y/%m/%d') AS receipt_date,
    -- 本船名
    rh.vessel_name,
    -- INVOICE NO.
    rh.invoice_number,
    -- 商品
    rd.product_id,
    -- 商品名
    rd.product_name,
    -- 内容
    CONCAT(COALESCE(rd.product_name, ''), "   ", COALESCE(FORMAT(rd.receipt_qty, 0), '0') ) AS content,
    -- 入荷形態
    rh.receipt_type,
    -- 備考
    CONCAT(COALESCE(rd.product_name, ''), "   ", COALESCE(rd.job_number, ''), "   " , COALESCE(FORMAT(rd.receipt_qty, 0), '0') ) AS note_text,
    -- 乙仲業者
    rh.forwarder_name,
    -- 入荷数量
    rd.receipt_qty,
    -- 発注単価(他通貨)
    pod.unit_price,
    -- 発注単価(他通貨)
    rd.job_number,
    -- コンテナNo.
    rh.container_number

-- 入荷ヘッダ
FROM receipt_headers rh

-- 入荷明細
LEFT JOIN receipt_details rd on rd.receipt_id = rh.id

-- 発注明細
LEFT JOIN purchase_order_details pod on pod.id = rd.purchase_order_detail_id 

-- 発注ヘッダ
LEFT JOIN purchase_order_headers poh on poh.id = pod.purchase_order_id

ORDER BY rh.public_expected_receipt_date DESC