-- 
-- モニタリスト
-- 
CREATE OR REPLACE VIEW v_monitor_list
AS 
SELECT
    CONCAT_WS('', u.family_name, u.given_name) AS sales_rep_name,
    u.name_kana,
    slip_number,
    DATE_FORMAT(arrival_date, '%Y/%m/%d') AS arrival_date,
    sales_headers.customer_name AS customer_name,
    CASE WHEN input_type = 3 THEN NULL ELSE delivery_name END AS delivery_name,
    CASE WHEN input_type = 3 THEN NULL ELSE delivery_postal_code END AS delivery_postal_code,
    CASE WHEN input_type = 3 THEN NULL ELSE delivery_prefecture END AS delivery_prefecture,
    CASE WHEN input_type = 3 THEN NULL ELSE CONCAT_WS('',delivery_prefecture,delivery_address1) END AS delivery_address1,
    CASE WHEN input_type = 3 THEN NULL ELSE delivery_address2 END AS delivery_address2,
    CASE WHEN input_type = 3 THEN NULL ELSE delivery_address3 END AS delivery_address3,
    sd.order_number AS order_no,
    CASE slip_type WHEN 1 THEN  '売上' WHEN 2 THEN '返品' WHEN 3 THEN 'その他' WHEN 9 THEN '赤伝' ELSE '' END AS slip_type,
    sd.product_code AS product_code,
    sd.product_name AS product_name,
    sd.unit_price AS unit_price,
    DATE_FORMAT(shipment_actual_date, '%Y/%m/%d') AS shipment_actual_date,
    sd.shipment_qty AS shipment_qty,
    sd.amount AS amount,
    c.customer_code AS customer_code
FROM sales_headers
    INNER JOIN sales_details sd
        ON sd.sales_id = sales_headers.id
    LEFT JOIN customers c
        ON c.id = customer_id
    LEFT JOIN products p
        ON p.id = sd.product_id
    LEFT JOIN sales_category_sales_reps scr
        ON scr.sales_id = sales_headers.id
        and scr.category_id = p.category_lv1_id
    LEFT JOIN users u
        ON u.id = scr.sales_rep_id
    LEFT JOIN departments d
        ON sd.department_id = d.id
    LEFT JOIN departments pd
        ON d.parent_department_id = pd.id
    LEFT JOIN carriers cr
        ON cr.id = carrier_id
WHERE sales_headers.arrival_date >= (SELECT LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) + INTERVAL 1 DAY)