19 lines
833 B
SQL
19 lines
833 B
SQL
-- 硬件订单新增独立核销时间。
|
|
-- 历史二维码核销订单只能使用最后更新时间近似回填；外卖等非核销完成状态不参与回填。
|
|
-- 新核销订单由业务事务写入准确时间，后续订单更新不再改变该值。
|
|
|
|
ALTER TABLE `pjcloudx_hardware`.`hard_order`
|
|
ADD COLUMN `verification_time` datetime DEFAULT NULL
|
|
COMMENT '订单实际核销完成时间，后续订单更新不得覆盖' AFTER `refund_status`,
|
|
ADD KEY `idx_hard_order_verification_time`
|
|
(`tenant_id`, `verification_time`, `del_flag`);
|
|
|
|
UPDATE `pjcloudx_hardware`.`hard_order`
|
|
SET `verification_time` = `update_time`
|
|
WHERE ((`order_status` = '20' AND `order_type` IN ('1', '2', '3', '4'))
|
|
OR `pay_type` IN ('5', '6', '50'))
|
|
AND `verification_time` IS NULL
|
|
AND `update_time` IS NOT NULL;
|
|
|
|
|