IT: OTS (China)

SELECT
  o.`ca_index`,
  o.id AS order_id,
  u.name AS retailer,
  o.order_name,
  oh.order_product_id,
  pt.name AS product_type,
  CASE
    WHEN op.status = 10
    THEN 'Sample First'
    WHEN op.status = 20
    THEN 'Order Set'
    WHEN op.status = 30
    THEN 'Production Started'
    WHEN op.status = 40
    THEN 'Logo Confirmed'
    WHEN op.status = 50
    THEN 'Production Finished'
    WHEN op.status = 60
    THEN 'Ready for Shipment'
    WHEN op.status = 70
    THEN 'Sent'
    WHEN op.status = 80
    THEN 'Order Finished'
  END AS current_order_product_status,
  FROM_UNIXTIME (
    oh.update_time,
    '%Y-%m-%d %H:%i:%s'
  ) AS order_product_update_time
FROM
  t_order_product op
  LEFT JOIN t_order o
    ON (o.id = op.order_id)
  LEFT JOIN t_order_history oh
    ON (oh.`order_product_id` = op.id)
  LEFT JOIN t_product_type pt
    ON (pt.id = op.product_type_id)
  LEFT JOIN t_user u
    ON (u.id = o.retailer_id)
WHERE o.retailer_id IN (33, 5, 212)
  AND oh.order_product_status = 30
  AND oh.update_user = 68
  AND o.create_time > UNIX_TIMESTAMP ('2024-01-01')
  AND o.create_time < UNIX_TIMESTAMP ('2024-03-11')