-- 
-- 担当者別中分類別年月別売上実績リスト
-- 
CREATE OR REPLACE VIEW v_sales_actual_results_by_rep_lv2_ym
AS 

SELECT 
    sales_rep_id, -- 担当者ID
    DATE_FORMAT(sales_headers.sales_date, '%Y') AS target_year,    -- 対象年
    DATE_FORMAT(sales_headers.sales_date, '%Y%m') AS target_month, -- 対象年月
    products.category_lv2_id,   -- 中分類ID
    SUM(CASE WHEN sales_details.line_type IN ('1','4') THEN amount ELSE 0 END) AS sales_amount,   -- 総売上額
    SUM(CASE WHEN sales_details.line_type IN ('5') THEN amount ELSE 0 END) AS return_amount,  -- 返品額
    SUM(CASE WHEN sales_details.line_type IN ('2') THEN amount ELSE 0 END) AS normal_discount_amount,  -- 通常値引額
    SUM(CASE WHEN sales_details.line_type IN ('3') THEN amount ELSE 0 END) AS policy_discount_amount,  -- 政策値引額
    SUM(CASE WHEN sales_details.line_type IN ('2','3') THEN amount ELSE 0 END) AS discount_amount,  -- 値引合計
    SUM(sales_details.cost_price * sales_details.shipment_qty) AS cost_amount -- 原価（現単価 × 出荷数量）

FROM sales_details
    INNER JOIN sales_headers ON sales_details.sales_id = sales_headers.id 
    INNER JOIN products ON sales_details.product_id = products.id 
    INNER JOIN sales_category_sales_reps 
        ON sales_category_sales_reps.sales_id = sales_headers.id
        AND sales_category_sales_reps.category_id = products.category_lv1_id
    WHERE DATE(sales_headers.sales_date) BETWEEN LAST_DAY(CURDATE() - INTERVAL 13 MONTH) + INTERVAL 1 DAY AND LAST_DAY(CURDATE()) -- 前年当月～当月

GROUP BY 
    sales_rep_id,
    DATE_FORMAT(sales_headers.sales_date, '%Y'),
    DATE_FORMAT(sales_headers.sales_date, '%Y%m'),
    products.category_lv2_id
 