-- 
-- 仕入先別仕入実績（振替伝票（マイナス仕入））
-- 
CREATE OR REPLACE VIEW v_transfer_slip_purchase_performance_by_supplier
AS 
SELECT
    -- 年月
    DATE_FORMAT(h.receipt_date, '%Y%m') AS ym,
    -- 仕入先コード
    s.supplier_code,
    -- 仕入先名
    s.supplier_name,
    -- 商品コード
    p.product_code,
    -- 商品名
    p.product_name,
    -- 入庫日
    DATE_FORMAT(h.receipt_date, '%Y/%m/%d') AS ymd,
    -- 出荷番号
    h.invoice_number,
    -- 入庫数
    IF(IFNULL(d.receipt_qty, 0) = 0, '', FORMAT(TRUNCATE(d.receipt_qty, 0), 0)) AS receipt_qty,
    -- 単価
    IF(IFNULL(d.unit_price_jpy, 0) = 0, '', FORMAT(TRUNCATE(d.unit_price_jpy, 0), 0)) AS unit_price_jpy,
    -- 仕入額
    IF(IFNULL(d.amount_jpy, 0) = 0, '', FORMAT(TRUNCATE(d.amount_jpy, 0), 0)) AS amount_jpy,
    -- ID
    h.id,
    -- 仕入先ID
    s.id AS supplier_id,
    -- 商品ID
    p.id AS product_id,
    -- 入荷日
    h.receipt_date

FROM
-- 仕入ヘッダ
    purchase_headers h

-- 仕入明細
LEFT JOIN purchase_details d on d.purchase_id = h.id

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

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

-- マイナス金額
WHERE h.total_standard_jpy + h.total_reduced_jpy + h.total_exempt_jpy < 0

ORDER BY
    ym DESC,
    ymd ASC,
    product_code ASC,
    invoice_number ASC
