-- 
-- 部門別担当者別年月別売上予実績リスト
-- 
CREATE OR REPLACE VIEW v_sales_budget_and_actual_results_by_deptrep_ym
AS 
SELECT 
    d.department_lv1_id,        -- 第１階層部門ID
    d.department_lv1_name,      -- 第１階層部門名
    d.department_lv1_sort_order,-- 第１階層部門表示順
    d.department_lv2_id,        -- 第２階層部門ID
    d.department_lv2_name,      -- 第２階層部門名
    d.department_lv2_sort_order,-- 第１階層部門表示順
    d.user_id,          -- 担当者ID
    d.user_name,        -- 担当者名
    d.user_sort_order,  -- 担当者表示順
    v.target_year,  -- 対象年
    v.target_month, -- 対象年月

    -- 売上目標
    SUM(v.sales_budget_amount) AS sales_budget_amount,  
    -- 総売上額
    SUM(v.sales_amount) AS sales_amount,
    -- 返品額
    SUM((v.return_amount * -1)) AS return_amount,
    -- 通常値引額
    SUM(v.normal_discount_amount) AS normal_discount_amount,
    -- 政策値引額
    SUM(v.policy_discount_amount) AS policy_discount_amount,
    -- 値引合計
    SUM(v.discount_amount) AS discount_amount,
    -- 純売上額
    SUM((v.sales_amount + v.return_amount - v.discount_amount)) AS amount,
    -- 売上達成率
    SUM(CASE WHEN v.sales_budget_amount = 0 THEN 0.0 ELSE
        ROUND(((v.sales_amount + v.return_amount - v.discount_amount) / v.sales_budget_amount * 100), 1) END) 
        AS achievement_rate,    
    -- 粗利目標
    SUM(gross_profit_budget_amount) AS gross_profit_budget_amount, 
    -- 粗利額
    SUM(v.sales_amount + v.return_amount - v.discount_amount - v.cost_amount) AS gross_profit_amount,
    -- 粗利達成率
    SUM(CASE WHEN v.gross_profit_budget_amount = 0 THEN 0.0 ELSE
        ROUND((((v.sales_amount + v.return_amount - v.discount_amount - v.cost_amount) / v.gross_profit_budget_amount) * 100), 1) END)
        AS gross_profit_achievement_rate, 
    -- 粗利率
    SUM(CASE WHEN (v.sales_amount + return_amount - v.discount_amount) = 0 THEN 0.0 ELSE
        ROUND(((v.sales_amount + return_amount - v.discount_amount - v.cost_amount) / (v.sales_amount + return_amount - v.discount_amount) * 100), 1) END) 
        AS gross_profit_margin 

-- ｜担当者別中分類別年月別売上予実績リスト
FROM v_sales_budget_and_actual_results_by_rep_lv2_ym v

-- ｜階層部門付き営業社員リスト
LEFT JOIN v_sales_reps_with_dept d ON v.user_id = d.user_id

GROUP BY
    d.department_lv1_id,
    d.department_lv1_name,
    d.department_lv1_sort_order,
    d.department_lv2_id,
    d.department_lv2_name,
    d.department_lv2_sort_order,
    d.user_id,
    d.user_name,
    d.user_sort_order,
    v.target_year,
    v.target_month