-- 
-- 得意先台帳(入金 + 支払)
-- 
CREATE OR REPLACE VIEW v_customer_ledger_rec
AS

SELECT
    sub.act_date,  -- 日付
    sub.customer_id,  -- 得意先ID
    sub.customer_code,  -- 得意先コード
    sub.customer_name,  -- 得意先名
    sub.delivery_id,  -- 配送No
    sub.slip_number,  -- 伝票No
    sub.tran_type,  -- 取引区分
    sub.product_code,  -- 　商品コード
    sub.product_name,  -- 商品名
    sub.quantity,  -- 数量
    sub.unit_price,  -- 単価
    sub.sales_amount,  -- 売上額
    sub.pay_amount,  -- 入金額
    sub.carry_amount
    + SUM(
        CASE
            WHEN sub.sort_type = 1 THEN -COALESCE(sub.pay_amount, 0)
            WHEN sub.sort_type = 2 THEN  COALESCE(sub.sales_amount, 0)
            ELSE 0
        END
      ) OVER (
        PARTITION BY sub.customer_id
        ORDER BY sub.customer_code ASC, sub.act_date DESC, sub.sort_type ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS balance_amount, -- 残高（顧客ごとに累積）
    sub.carry_amount,  -- 繰越残高
    sub.main_note,  -- メモ(メイン)
    sub.note,  -- メモ
    sub.ym,  -- 年月
    sub.rep_code,  -- 担当者コード
    sub.rep_name, -- 担当者名
    sub.sort_type, -- ソート用(1: 入金, 2: 売上)
    sub.act_date AS hidden_act_date,  -- 日付(検索用)
    sub.hidden_ym AS hidden_ym,  -- 日付YM(検索用)
    sub.customer_id AS hidden_customer_id,  -- 得意先ID(検索用)
    sub.customer_name AS hidden_customer_code,  -- 得意先コード(検索用)
    sub.customer_name AS hidden_customer_name,  -- 得意先名(検索用)
    sub.sales_amount_without_tax,  -- 売上額(税抜)
    sub.tax  -- 消費税額

FROM (
    (
    SELECT 
        DATE_FORMAT(prh.payment_date, '%Y/%m/%d') AS act_date,  -- 日付
        cu.id AS customer_id,  -- 得意先ID
        cu.customer_code AS customer_code,  -- 得意先コード
        cu.customer_name AS customer_name,  -- 得意先名
        prh.id AS delivery_id,  -- 配送No
        prh.slip_number AS slip_number,  -- 伝票No 
        CASE
            WHEN prb.receipt_type = 1 THEN '振込'
            WHEN prb.receipt_type = 2 THEN '手数料'
            WHEN prb.receipt_type = 3 THEN '相殺'
            WHEN prb.receipt_type = 4 THEN '手形'
            WHEN prb.receipt_type = 5 THEN 'でんさい'
            WHEN prb.receipt_type = 6 THEN '値引(手数料)'
            WHEN prb.receipt_type = 7 THEN '支払手数料'
            WHEN prb.receipt_type = 8 THEN '調整(売掛)'
            ELSE ''
        END AS tran_type ,  -- 取引区分
        NULL AS product_code,  -- 　商品コード
        prb.note AS product_name,  -- 商品名
        NULL AS quantity,  -- 数量
        NULL AS unit_price,  -- 単価
        NULL AS sales_amount,  -- 売上額
        prb.amount AS pay_amount,  -- 入金額
        mar.current_balance_amount AS carry_amount,  -- 繰越残高
        prh.note AS main_note,  -- メモ(メイン)
        NULL AS note,  -- メモ
        DATE_FORMAT(prh.payment_date, '%Y%m') AS ym,  -- 年月
        NULL AS rep_code,  -- 担当者コード
        NULL AS rep_name, -- 担当者名
        1 AS sort_type , -- ソート用(1: 入金, 2: 売上)
        DATE_FORMAT(prh.payment_date, '%Y/%m') AS hidden_ym,
        0 AS sales_amount_without_tax,  -- 売上額(税抜)
        0 AS tax  -- 消費税額

    -- ｜入金
    FROM payment_receipt_headers AS prh 

    -- ｜得意先マスタ
    INNER JOIN customers AS cu ON cu.id = prh.customer_id

    -- ｜入金内訳明細
    LEFT JOIN payment_receipt_breakdowns AS prb ON prh.id = prb.payment_receipt_id

    -- ｜月次売掛残高をJOIN
    LEFT JOIN monthly_accounts_receivables AS mar
        ON mar.customer_id = cu.id
        AND mar.month_end_date >= DATE_FORMAT(DATE_SUB(prh.payment_date, INTERVAL 1 MONTH), '%Y-%m-01')
        AND mar.month_end_date <  DATE_ADD(
            DATE_FORMAT(DATE_SUB(prh.payment_date, INTERVAL 1 MONTH), '%Y-%m-01'),
            INTERVAL 1 MONTH
        )
    )
    UNION ALL
    (
    SELECT 
        DATE_FORMAT(sh.shipment_actual_date, '%Y/%m/%d') AS act_date,  -- 日付
        cu.id AS customer_id,  -- 得意先ID
        cu.customer_code AS customer_code,  -- 得意先コード
        sh.customer_name AS customer_name,  -- 得意先名
        sh.id AS delivery_id,  -- 配送No
        sh.slip_number AS slip_number,  -- 伝票No
        CASE
            WHEN sd.line_type = 1 THEN '通常(掛売上)'
            WHEN sd.line_type = 2 THEN '値引'
            WHEN sd.line_type = 3 THEN '政策値引'
            WHEN sd.line_type = 4 THEN '訂正'
            WHEN sd.line_type = 5 THEN '返品'
        END AS tran_type,  -- 取引区分
        sd.product_code AS product_code,  -- 　商品コード
        sd.product_name AS product_name,  -- 商品名
        sd.shipment_qty AS quantity,  -- 数量
        sd.unit_price AS unit_price,  -- 単価
        CASE WHEN sd.tax_type = 1
            THEN sd.amount + floor(sd.amount * sd.tax_rate * 0.01)
            ELSE sd.amount
        END AS sales_amount,  -- 売上額(税込)
        NULL AS pay_amount,  -- 入金額
        mar.current_balance_amount AS carry_amount,  -- 繰越残高
        sh.logistics_note AS main_note,  -- メモ(メイン)
        sd.note AS note,  -- メモ
        DATE_FORMAT(sh.shipment_actual_date, '%Y%m') AS ym,  -- 年月
        us.user_code AS rep_code,  -- 担当者コード
        CONCAT(IFNULL(us.family_name, ''), ' ', IFNULL(us.given_name, '')) AS rep_name, -- 担当者名
        2 AS sort_type, -- ソート用(1: 入金, 2: 売上)
        DATE_FORMAT(sh.shipment_actual_date, '%Y/%m') AS hidden_ym,
        sd.amount AS sales_amount_without_tax,  -- 売上額(税抜)
        CASE WHEN sd.tax_type = 1
            THEN floor(sd.amount * sd.tax_rate * 0.01)
            ELSE 0
        END AS tax  -- 消費税額

    -- ｜売上
    FROM sales_headers AS sh 

    -- ｜売上明細
    LEFT JOIN sales_details AS sd ON sh.id = sd.sales_id

    -- ｜得意先マスタ
    INNER JOIN customers as cu ON sh.customer_id = cu.id

    -- ｜商品マスタ
    LEFT JOIN products AS pr ON sd.product_id = pr.id

    -- ｜売上大分類別営業担当
    LEFT JOIN sales_category_sales_reps AS sc ON sh.id = sc.sales_id
    AND pr.category_lv1_id = sc.category_id

    -- ｜社員マスタ
    INNER JOIN users AS us ON sc.sales_rep_id = us.id

    -- ｜月次売掛残高をJOIN
    LEFT JOIN monthly_accounts_receivables AS mar
        ON mar.customer_id = cu.id
        AND mar.month_end_date >= DATE_FORMAT(DATE_SUB(sh.shipment_actual_date, INTERVAL 1 MONTH), '%Y-%m-01')
        AND mar.month_end_date <  DATE_ADD(
            DATE_FORMAT(DATE_SUB(sh.shipment_actual_date, INTERVAL 1 MONTH), '%Y-%m-01'),
            INTERVAL 1 MONTH
        )
    )
) AS sub

-- 前月の当月残高<>0もしくは、当月に売上・入金が存在するデータが台帳表示対象
WHERE sub.carry_amount <> 0
    OR sub.sales_amount <> 0
    OR sub.pay_amount <> 0