-- 
-- 得意先台帳(入金 + 支払 合計)
-- 
CREATE OR REPLACE VIEW v_customer_ledger AS
WITH base AS (
    SELECT
        act_date,
        customer_id,
        customer_code,
        customer_name,
        delivery_id,
        slip_number,
        tran_type,
        product_code,
        product_name,
        quantity,
        unit_price,
        sales_amount,
        pay_amount,
        balance_amount,
        carry_amount,
        main_note,
        note,
        ym,
        rep_code,
        rep_name,
        sort_type,
        hidden_act_date,
        hidden_ym,
        hidden_customer_id,
        hidden_customer_code,
        hidden_customer_name,
        sales_amount_without_tax,
        tax
    FROM v_customer_ledger_rec
),
agg AS (
    SELECT
        act_date,
        customer_code,
        hidden_act_date,
        hidden_ym,
        hidden_customer_id,
        hidden_customer_code,
        hidden_customer_name,
        SUM(sales_amount)             AS sum_sales_amount,
        SUM(pay_amount)               AS sum_pay_amount,
        SUM(carry_amount)             AS sum_carry_amount,
        SUM(sales_amount_without_tax) AS sum_sales_wo_tax,
        SUM(tax)                      AS sum_tax
    FROM base
    GROUP BY
        act_date,
        customer_code,
        hidden_act_date,
        hidden_ym,
        hidden_customer_id,
        hidden_customer_code,
        hidden_customer_name
)
SELECT
    -- 明細行
    b.act_date,
    b.customer_id,
    b.customer_code,
    b.customer_name,
    b.delivery_id,
    b.slip_number,
    b.tran_type,
    b.product_code,
    b.product_name,
    b.quantity,
    b.unit_price,
    b.sales_amount,
    b.pay_amount,
    b.balance_amount,
    b.carry_amount,
    b.main_note,
    b.note,
    b.ym,
    b.rep_code,
    b.rep_name,
    b.sort_type,
    b.hidden_act_date,
    b.hidden_ym,
    b.hidden_customer_id,
    b.hidden_customer_code,
    b.hidden_customer_name
FROM base AS b

UNION ALL

SELECT
    a.act_date,
    '' AS customer_id,
    customer_code,
    '' AS customer_name,
    '' AS delivery_id,
    '' AS slip_number,
    '' AS tran_type,
    '' AS product_code,
    '' AS product_name,
    '' AS quantity,
    CONVERT('【合計】' USING utf8mb4) AS unit_price,
    COALESCE(a.sum_sales_amount, 0) AS sales_amount,
    COALESCE(a.sum_pay_amount, 0)   AS pay_amount,
    COALESCE(a.sum_carry_amount - a.sum_pay_amount + a.sum_sales_amount, 0) AS balance_amount,
    COALESCE(a.sum_carry_amount, 0) AS carry_amount,
    '' AS main_note,
    '' AS note,
    '' AS ym,
    '' AS rep_code,
    '' AS rep_name,
    97 AS sort_type,
    a.hidden_act_date,
    a.hidden_ym,
    a.hidden_customer_id,
    a.hidden_customer_code,
    a.hidden_customer_name
FROM agg AS a

UNION ALL

SELECT
    a.act_date,
    '' AS customer_id,
    customer_code,
    '' AS customer_name,
    '' AS delivery_id,
    '' AS slip_number,
    '' AS tran_type,
    '' AS product_code,
    '' AS product_name,
    '' AS quantity,
    CONVERT('（税抜金額）' USING utf8mb4) AS unit_price,
    COALESCE(a.sum_sales_wo_tax, 0) AS sales_amount,
    0  AS pay_amount,
    0  AS balance_amount,
    0  AS carry_amount,
    '' AS main_note,
    '' AS note,
    '' AS ym,
    '' AS rep_code,
    '' AS rep_name,
    98 AS sort_type,
    a.hidden_act_date,
    a.hidden_ym,
    a.hidden_customer_id,
    a.hidden_customer_code,
    a.hidden_customer_name
FROM agg AS a

UNION ALL

SELECT
    a.act_date,
    '' AS customer_id,
    customer_code,
    '' AS customer_name,
    '' AS delivery_id,
    '' AS slip_number,
    '' AS tran_type,
    '' AS product_code,
    '' AS product_name,
    '' AS quantity,
    CONVERT('（消費税等）' USING utf8mb4) AS unit_price,
    COALESCE(a.sum_tax, 0) AS sales_amount,
    CONVERT('' USING utf8mb4) AS pay_amount,
    CONVERT('' USING utf8mb4) AS balance_amount,
    CONVERT('' USING utf8mb4) AS carry_amount,
    '' AS main_note,
    '' AS note,
    '' AS ym,
    '' AS rep_code,
    '' AS rep_name,
    99 AS sort_type,
    a.hidden_act_date,
    a.hidden_ym,
    a.hidden_customer_id,
    a.hidden_customer_code,
    a.hidden_customer_name
FROM agg AS a