-- 
-- 仕入先別仕入実績（国外仕入）
-- 
CREATE OR REPLACE VIEW v_international_purchase_performance_by_supplier
AS 
SELECT
    -- 年月
    DATE_FORMAT(min(pad.receipt_date), '%Y%m') AS ym,
    -- 仕入先コード
    min(s.supplier_code) As supplier_code,
    -- 仕入先名
    min(s.supplier_name) As supplier_name,
    -- 商品コード
    '' AS product_code,
    -- 商品名
    '' AS product_name_for_display,
    GROUP_CONCAT(DISTINCT p.product_name ORDER BY p.product_name SEPARATOR '\n') AS product_name,
    -- 入庫日
    DATE_FORMAT(min(pad.receipt_date), '%Y/%m/%d') AS ymd,
    -- 出荷番号
    min(pad.invoice_number) AS invoice_number,
    -- 入庫数
    '' AS receipt_qty,
    -- 単価
    '' AS unit_price_jpy,
    -- 仕入額
    IF(IFNULL(min(pad.purchase_amount_jpy), 0) = 0, '', FORMAT(TRUNCATE(min(pad.purchase_amount_jpy), 0), 0)) AS purchase_amount_jpy,
    -- ID
    pad.id,
    -- 仕入先ID
    min(s.id) AS supplier_id,
    -- 商品ID
    GROUP_CONCAT(DISTINCT p.id ORDER BY p.id SEPARATOR ',') AS product_id,
    -- 入荷日
    min(pad.receipt_date) AS receipt_date

FROM
-- 出金表(支払)
    payment_disbursements pad

-- 仕入ヘッダ
LEFT JOIN purchase_headers ph on ph.id = pad.purchase_id

-- 仕入明細
LEFT JOIN purchase_details pud on pud.purchase_id = ph.id

-- 商品マスタ
LEFT JOIN products p on pud.product_id = p.id

-- 仕入先マスタ
LEFT JOIN suppliers s on ph.supplier_id = s.id

-- 国内/海外区分(仕入先) = 1:海外
WHERE s.supplier_type = 1

GROUP BY pad.id

ORDER BY
    ym DESC,
    id DESC,
    supplier_code DESC,
    product_code ASC,
    receipt_date ASC