-- 
-- BE 賞味期限管理表
-- 
CREATE OR REPLACE VIEW v_expiration_date_management
AS 

SELECT
inventory_lots.id   -- ロット在庫ID
,inventory_lots.product_id  -- 商品ID
,inventory_lots.lot_number  -- ロット番号
-- ,ROW_NUMBER() OVER(ORDER BY inventory_lots.received_date DESC, products.product_code ASC, inventory_lots.product_id ASC, inventory_lots.id ASC) AS row_no  -- No.
,inventory_lots.warehouse_id  -- 場所ID
,warehouses.warehouse_code  -- 場所コード
,warehouses.warehouse_name  -- 場所名
,CASE WHEN warehouses.warehouse_code='202' THEN 1 ELSE 0 END AS defective_flg  -- 不良品フラグ
,CASE WHEN warehouses.warehouse_code='202' THEN '不良品' ELSE NULL END AS defective -- 不良品
,products.product_code  -- 商品コード
,products.product_name  -- 商品名
,products.expiration_months -- 賞味期限月数
,DATE_FORMAT(
  DATE_ADD(
    DATE_ADD(expiration_date, INTERVAL -expiration_months MONTH),
    INTERVAL FLOOR(
      DATEDIFF(
        expiration_date,
        DATE_ADD(expiration_date, INTERVAL -expiration_months MONTH)
      ) / 2
    ) DAY
  ),
  '%Y/%m/%d'
) AS halfday    -- 賞味期限残り1/2(期限中間日)
,DATE_FORMAT(
  DATE_ADD(expiration_date, INTERVAL -6 MONTH),
  '%Y/%m/%d'
) AS month6day  -- 賞味期限残り6ヶ月(期限６か月前)
,DATE_FORMAT(inventory_lots.expiration_date, '%Y/%m/%d') AS expiration_date -- 賞味期限
,products.carton_quantity    -- 入数
,inventory_lots.quantity_carton  -- カートン数
,COALESCE(products.carton_quantity, 0) * COALESCE(inventory_lots.quantity_carton, 0) AS quantity_subtotal    -- 小計
,inventory_lots.quantity_unit    -- バラ数
,inventory_lots.quantity     -- 総計
,CASE WHEN  DATE_ADD(expiration_date, INTERVAL -6 MONTH)<CURDATE() THEN 1 ELSE 0 END AS expired_flg  -- 賞味期限切れフラグ
,CASE WHEN  DATE_ADD(expiration_date, INTERVAL -6 MONTH)<CURDATE() THEN '賞味期限切れ' ELSE NULL END AS expired  -- 賞味期限切れ
,products.avg_cost   -- 平均原価
,COALESCE(products.avg_cost, 0) * COALESCE(inventory_lots.quantity, 0) AS inventory_amount     -- 在庫金額
,DATE_FORMAT(inventory_lots.received_date, '%Y/%m/%d') AS received_date -- 最終入荷日
-- ｜ロット在庫
FROM inventory_lots
-- ｜商品マスタ
LEFT JOIN products ON products.id=inventory_lots.product_id
-- ｜場所マスタ
LEFT JOIN warehouses ON warehouses.id=inventory_lots.warehouse_id
WHERE products.product_type='2'
