-- 
-- 配送データ
-- 
CREATE OR REPLACE VIEW v_delivery_data
AS 

SELECT 
    DATE_FORMAT(shipment_headers.shipment_date, '%Y/%m/%d') AS shipment_date, -- 出荷日
    CONCAT(IFNULL(users.family_name, ''), ' ', IFNULL(users.given_name, '')) AS rep_name, -- 担当者
    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, -- 課
    sales_details.product_code, -- 商品コード
    sales_details.product_name, -- 商品名
    products.category_lv2_id, -- 中分類コード
    categories1.category_name AS category_name_lv2, -- 中分類
    products.category_lv3_id, -- 小分類コード
    categories2.category_name AS category_name_lv3, -- 小分類
    customers.customer_code, -- 得意先コード
    sales_headers.customer_name, -- 得意先名
    customer_deliveries.delivery_code, -- 配送先コード
    sales_headers.delivery_name, -- 配送先名
    sales_details.shipment_qty, -- 売上数量
    sales_details.amount, -- 売上金額
    sales_details.cost_price, -- 仕入単価
    sales_details.amount - (sales_details.cost_price*sales_details.shipment_qty) AS gross_profit, -- 粗利額
    -- ((sales_details.amount - (sales_details.cost_price*sales_details.shipment_qty)) / sales_details.amount) * 100 AS gross_profit_rate, -- 粗利率
        CASE WHEN sales_details.amount = 0 THEN 0.0
        ELSE ROUND((((sales_details.amount - (sales_details.cost_price * sales_details.shipment_qty)) / sales_details.amount) * 100), 1)
        END AS gross_profit_rate,  -- 粗利率
    warehouses.warehouse_name, -- 倉庫
    DATE_FORMAT(sales_headers.arrival_date, '%Y/%m/%d') AS arrival_date, -- 納入日
    DATE_FORMAT(sales_headers.arrival_date, '%Y/%m') AS arrival_date_ym, -- 年月
    DATE_FORMAT(sales_headers.arrival_date, '%Y') AS arrival_date_y, -- 年
    CONCAT(IFNULL(sales_headers.delivery_prefecture, ''), ' ', IFNULL(sales_headers.delivery_address1, ''), ' ', IFNULL(sales_headers.delivery_address2, ''), ' ', IFNULL(sales_headers.delivery_address3, '')) AS addresss, -- 納品先住所
    sales_headers.customer_id, -- 得意先ID
    sales_details.product_id -- 商品ID

-- ｜出荷ヘッダ
FROM shipment_headers

-- ｜出荷明細
LEFT JOIN shipment_details ON shipment_details.shipment_id = shipment_headers.id

-- ｜売上
LEFT JOIN sales_headers ON shipment_headers.id = sales_headers.shipment_id

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

-- ｜商品マスタ
LEFT JOIN products ON shipment_details.product_id = products.id

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

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

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

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

-- ｜場所マスタ
LEFT JOIN warehouses ON shipment_headers.warehouse_id = warehouses.id

-- ｜売上大分類別営業担当
LEFT JOIN sales_category_sales_reps ON sales_headers.id = sales_category_sales_reps.sales_id
AND products.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