-- 
-- 売上実績表
-- 
CREATE OR REPLACE VIEW v_sales_actual_result_table
AS 
SELECT
    pr.category_lv1_id,   -- 大分類ID
    cat1.category_name AS category_name_lv1,   -- 大分類名
    pr.category_lv2_id,   -- 中分類ID
    cat2.category_name AS category_name_lv2,   -- 中分類名
    pr.category_lv3_id,   -- 小分類ID
    cat3.category_name AS category_name_lv3,   -- 小分類名
    COALESCE(dep2.id, dep1.id) as dept_id, -- 部ID
    COALESCE(dep2.department_name, dep1.department_name) as dept_name, -- 部
    CASE 
        WHEN dep1.parent_department_id IS NOT NULL 
        THEN dep1.id 
        ELSE NULL 
    END AS section_id, -- 課ID
    CASE 
        WHEN dep1.parent_department_id IS NOT NULL 
        THEN dep1.department_name 
        ELSE NULL 
    END AS section_name, -- 課
    su.supplier_name,  -- 仕入先名
    sd.product_code,  -- 商品コード
    pr.jan_code,  -- JANコード
    pr.product_character,  -- 商品性格
    pr.product_name,  -- 商品名
    cu.customer_code,  -- 得意先コード
    cu.customer_name,  -- 得意先名
    cg.id AS customer_group_id,  -- 得意先グループID
    cg.customer_group_name,  -- 得意先名
    us.name_kana as rep_name_kana, -- 担当者カナ
    CONCAT(IFNULL(us.family_name, ''), '', IFNULL(us.given_name, '')) AS rep_name, -- 担当者名
    DATE_FORMAT(sh.sales_date, '%Y') as sales_date_y,  -- 伝票年
    DATE_FORMAT(sh.sales_date, '%m') as sales_date_m,  -- 伝票月
    DATE_FORMAT(sh.sales_date, '%Y%m') as sales_date_ym,  -- 伝票年月
    DATE_FORMAT(sh.sales_date, '%Y-%m-%d') as sales_date_ymd,  -- 伝票年月日
    CASE
        WHEN sd.line_type IN (1, 4) THEN sd.shipment_qty
        ELSE 0
    END AS 'shipment_qty', -- 売上数量
    CASE
        WHEN sd.line_type = 5 THEN sd.shipment_qty
        ELSE 0
    END AS 'return_qty', -- 返品数量
    CASE
        WHEN sd.line_type IN (2, 3) THEN sd.shipment_qty
        ELSE 0
    END AS 'discount_qty', -- 値引数量
    sd.shipment_qty as pure_sales_qty,  -- 純売上数量
    CASE
        WHEN sd.line_type IN (1, 4) THEN sd.amount
        ELSE 0
    END AS 'total_sales_amount', -- 総売上額
    CASE
        WHEN sd.line_type = 5 THEN sd.amount
        ELSE 0
    END AS 'return_amount', -- 返品額
    CASE
        WHEN sd.line_type = 2 THEN sd.amount
        ELSE 0
    END AS 'normal_discount_amount', -- 通常値引額
    CASE
        WHEN sd.line_type = 3 THEN sd.amount
        ELSE 0
    END AS 'policy_discount_amount', -- 政策値引額
    (
        CASE WHEN sd.line_type IN (1, 4) THEN sd.amount ELSE 0 END
        - CASE WHEN sd.line_type = 5 THEN sd.amount ELSE 0 END
        - (
            CASE WHEN sd.line_type = 2 THEN sd.amount ELSE 0 END
            + CASE WHEN sd.line_type = 3 THEN sd.amount ELSE 0 END
        )
    ) AS 'pure_sales',  -- 純売上額
    sd.tax_type AS 'tax_type', -- 課税区分
    CASE
        WHEN sd.tax_type = 0 THEN '計算しない'
        WHEN sd.tax_type = 1 THEN '課税'
        WHEN sd.tax_type = 2 THEN '非課税'
        WHEN sd.tax_type = 3 THEN '不課税'
        ELSE ''
    END AS 'tax_type_name', -- 課税区分名称
    CASE
        WHEN cu.rounding_type_tax = 1 THEN round(sd.amount * (sd.tax_rate / 100))
        WHEN cu.rounding_type_tax = 2 THEN floor(sd.amount * (sd.tax_rate / 100))
        WHEN cu.rounding_type_tax = 3 THEN ceil(sd.amount * (sd.tax_rate / 100))
        ELSE 0
    END AS 'tax',  -- 消費税額
    CONCAT(
        CASE
            WHEN sd.tax_type = 1 THEN '課税対象'
            WHEN sd.tax_type = 2 THEN '非課税対象'
            ELSE ''
        END
        , 
        CASE
            WHEN sd.tax_type = 1 THEN CONCAT('(', FLOOR(sd.tax_rate),'%)')
            ELSE ''
        END
    ) AS tax_rate_name,  -- 消費税種類+%
    CASE
        WHEN sd.cost_price IS NULL THEN 0
        ELSE sd.cost_price
    END,  -- 粗利算出用単価
    CASE
        WHEN sd.line_type IN (1, 4) THEN sd.cost_price*sd.shipment_qty
        ELSE 0
    END AS 'shipment_amount', -- 粗利算出用売上額
    CASE
        WHEN sd.line_type = 5 THEN sd.cost_price*sd.shipment_qty
        ELSE 0
    END AS 'shipment_return_amount', -- 粗利算出用返品額
    CASE
        WHEN sd.line_type IN (2, 3) THEN sd.cost_price*sd.shipment_qty
        ELSE 0
    END AS 'shipment_discount_amount', -- 粗利算出用値引額
    (   
        CASE
            WHEN sd.line_type IN (1, 4) THEN sd.cost_price*sd.shipment_qty
            ELSE 0
        END
    ) -
    (
        CASE
            WHEN sd.line_type = 5 THEN sd.cost_price*sd.shipment_qty
            ELSE 0
        END
    ) -
    (
        CASE
            WHEN sd.line_type IN (2, 3) THEN sd.cost_price*sd.shipment_qty
            ELSE 0
        END
    ) AS gross_profit  -- 粗利額(粗利算出用売上額-粗利算出用返品額-粗利算出用値引額)

-- ｜売上
FROM sales_headers AS sh

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

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

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

-- ｜得意先グループマスタ
LEFT JOIN customer_groups AS cg ON cu.customer_group_id = cg.id

-- ｜商品別仕入先マスタ
LEFT JOIN product_suppliers AS ps ON pr.id = ps.product_id
AND (ps.priority = 1 
OR ps.priority IS NULL)

-- ｜仕入先マスタ
LEFT JOIN suppliers AS su ON ps.supplier_id = su.id

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

-- ｜商品分類マスタ  大分類
LEFT JOIN categories AS cat1 ON pr.category_lv1_id = cat1.id

-- ｜商品分類マスタ  中分類
LEFT JOIN categories AS cat2 ON pr.category_lv2_id = cat2.id

-- ｜商品分類マスタ  小分類
LEFT JOIN categories AS cat3 ON pr.category_lv3_id = cat3.id

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

-- ｜部門マスタ1 section_name
LEFT JOIN departments AS dep1 ON sd.department_id = dep1.id

-- ｜部門マスタ2 dept_name
LEFT JOIN departments AS dep2 ON dep2.id = dep1.parent_department_id
