-- 
-- 売上実績データ
-- 
CREATE OR REPLACE VIEW v_sales_actual_result_data
AS 

SELECT
    sales_details.id as sales_details_id,  -- 出荷明細ID
    CASE
        WHEN sales_details.line_type = 1 THEN '通常(掛売上)'
        WHEN sales_details.line_type = 2 THEN '値引'
        WHEN sales_details.line_type = 3 THEN '政策値引'
        WHEN sales_details.line_type = 4 THEN '訂正'
        WHEN sales_details.line_type = 5 THEN '返品'
        ELSE ''
    END AS 'line_type', -- 取引区分
    sales_headers.id as sales_headers_id,  -- 売上内部ID
    CONCAT(IFNULL(sales_headers.slip_number, ''), "-", IFNULL(sales_details.line_no, '')) as management_code,  -- 管理コード
    DATE_FORMAT(sales_headers.sales_date, '%Y/%m/%d') as sales_date,  -- 伝票日付
    CONCAT(IFNULL(users.family_name, ''), '', IFNULL(users.given_name, '')) AS rep_name,  -- 担当者
    customers.customer_code,  -- 得意先コード
    sales_headers.customer_name,  -- 得意先名
    customer_groups.customer_group_name,  -- 得意先グループ名
    sales_details.order_number,  -- 注文No
    products1.jan_code,  -- JANコード
    sales_details.product_code,  -- 商品コード
    sales_details.product_name as product_name,  -- 商品名
    products1.product_character,  -- 商品性格
    sales_details.unit_price,  -- 商品単価
    CASE
        WHEN sales_details.line_type IN (1, 4) THEN sales_details.shipment_qty
        ELSE 0
    END AS 'shipment_qty', -- 売上数量
    CASE
        WHEN sales_details.line_type = 5 THEN sales_details.shipment_qty
        ELSE 0
    END AS 'return_qty', -- 返品数量
    CASE
        WHEN sales_details.line_type IN (2, 3) THEN sales_details.shipment_qty
        ELSE 0
    END AS 'discount_qty', -- 値引数量
    sales_details.shipment_qty as pure_sales_qty,  -- 純売上数量
    CASE
        WHEN sales_details.line_type IN (1, 4) THEN sales_details.amount
        ELSE 0
    END AS 'total_sales_amount', -- 総売上額
    CASE
        WHEN sales_details.line_type = 5 THEN sales_details.amount
        ELSE 0
    END AS 'return_amount', -- 返品額
    CASE
        WHEN sales_details.line_type = 2 THEN sales_details.amount
        ELSE 0
    END AS 'normal_discount_amount', -- 通常値引額
    CASE
        WHEN sales_details.line_type = 3 THEN sales_details.amount
        ELSE 0
    END AS 'policy_discount_amount', -- 政策値引額
    (
        CASE WHEN sales_details.line_type IN (1, 4) THEN sales_details.amount ELSE 0 END
        - CASE WHEN sales_details.line_type = 5 THEN sales_details.amount ELSE 0 END
        - (
            CASE WHEN sales_details.line_type = 2 THEN sales_details.amount ELSE 0 END
            + CASE WHEN sales_details.line_type = 3 THEN sales_details.amount ELSE 0 END
        )
    ) as pure_sales,  -- 純売上額
    sales_details.amount - (sales_details.cost_price*sales_details.shipment_qty) AS gross_profit,  -- 粗利額
    CASE
        WHEN sales_details.tax_type = 0 THEN '計算しない'
        WHEN sales_details.tax_type = 1 THEN '課税'
        WHEN sales_details.tax_type = 2 THEN '非課税'
        WHEN sales_details.tax_type = 3 THEN '不課税'
        ELSE ''
    END AS 'tax_type', -- 課税区分
    CASE
        WHEN customers.rounding_type_tax = 1 THEN round(sales_details.amount * (sales_details.tax_rate / 100))
        WHEN customers.rounding_type_tax = 2 THEN floor(sales_details.amount * (sales_details.tax_rate / 100))
        WHEN customers.rounding_type_tax = 3 THEN ceil(sales_details.amount * (sales_details.tax_rate / 100))
        ELSE 0
    END AS 'tax',  -- 消費税額
    sales_details.cost_price,  -- 粗利算出用単価
    CASE
        WHEN sales_details.line_type IN (1, 4) THEN sales_details.cost_price*sales_details.shipment_qty
        ELSE 0
    END AS 'shipment_amount', -- 粗利算出用売上額
    CASE
        WHEN sales_details.line_type = 5 THEN sales_details.cost_price*sales_details.shipment_qty
        ELSE 0
    END AS 'shipment_return_amount', -- 粗利算出用返品額
    CASE
        WHEN sales_details.line_type IN (2, 3) THEN sales_details.cost_price*sales_details.shipment_qty
        ELSE 0
    END AS 'shipment_discount_amount', -- 粗利算出用値引額
    categories1.category_name AS category_name_lv1,  -- 大分類名
    products1.category_lv2_id,  -- 中分類コード
    categories2.category_name AS category_name_lv2,  -- 中分類名
    products1.category_lv3_id,  -- 小分類コード
    categories3.category_name  AS category_name_lv3,  -- 小分類名
    sales_details.note,  -- メモ
    DATE_FORMAT(sales_headers.sales_date, '%Y/%m') as sales_date_ym,  -- 年月
    DATE_FORMAT(sales_headers.sales_date, '%Y') as sales_date_y,  -- 年
    COALESCE(departments2.department_name, departments1.department_name) as dept_name, -- 部
    CASE 
        WHEN departments1.parent_department_id IS NOT NULL 
        THEN departments1.department_name 
        ELSE NULL 
    END AS section_name, -- 課
    suppliers.supplier_code, -- 仕入先コード
    suppliers.supplier_name, -- 仕入先名
    TRUNCATE(product_suppliers.default_cost_price, 0) as supplier_price, -- 仕入価格
    DATE_FORMAT(sales_headers.created_at, '%Y/%m/%d %H:%i:%s') AS created_at,  -- 作成日時
    DATE_FORMAT(sales_headers.updated_at, '%Y/%m/%d %H:%i:%s') AS updated_at,  -- 更新日時
    products2.product_name as successor_product_name,  -- 後継品および代替品
    sales_headers.customer_id as customer_id,  -- 得意先ID
    sales_details.product_id as product_id  -- 商品ID

-- ｜売上明細
FROM sales_details

-- ｜売上
LEFT JOIN sales_headers ON sales_headers.id = sales_details.sales_id

-- ｜商品マスタ
LEFT JOIN products as products1 ON sales_details.product_id = products1.id

-- ｜商品マスタ(後継品・代替品)
LEFT JOIN products as products2 ON products1.successor_product_id = products2.id

-- ｜商品仕入先マスタ
LEFT JOIN product_suppliers ON product_suppliers.product_id = products1.id
AND product_suppliers.priority = 1

-- ｜商品仕入先マスタ
LEFT JOIN suppliers ON product_suppliers.supplier_id = suppliers.id

-- ｜商品分類マスタ  大分類
LEFT JOIN categories AS categories1 ON products1.category_lv2_id = categories1.id

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

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

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

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

-- ｜配送先マスタ
LEFT JOIN customer_deliveries ON sales_headers.delivery_id = customer_deliveries.id

-- ｜売上大分類別営業担当
LEFT JOIN sales_category_sales_reps ON sales_headers.id = sales_category_sales_reps.sales_id
AND products1.category_lv1_id = sales_category_sales_reps.category_id

-- ｜社員マスタ
LEFT JOIN users ON sales_category_sales_reps.sales_rep_id = users.id

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

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