37 lines
1.5 KiB
SQL
37 lines
1.5 KiB
SQL
-- ============================================================
|
||
-- 报工单实际开始时间补充（仅生产工单）
|
||
-- 表：pro_report
|
||
-- 逻辑：actual_start_time = plan_start_time +/- 10分钟内随机秒数
|
||
-- ============================================================
|
||
|
||
-- 通过 work_order_entry_id 关联工单，先填 plan_start_time（=工单 process_start_time），
|
||
-- 再填 actual_start_time = plan_start_time +/- 10分钟随机秒
|
||
UPDATE pro_report pr
|
||
INNER JOIN pro_workorder_entry pwe ON pwe.id = pr.work_order_entry_id
|
||
INNER JOIN pro_workorder pwo ON pwo.id = pwe.workorder_id
|
||
SET
|
||
pr.plan_start_time = pwo.process_start_time,
|
||
pr.actual_start_time = DATE_ADD(
|
||
pwo.process_start_time,
|
||
INTERVAL (FLOOR(RAND() * 1201) - 600) SECOND
|
||
)
|
||
WHERE pr.actual_start_time IS NULL
|
||
AND pr.work_order_entry_id IS NOT NULL
|
||
AND pwo.process_start_time IS NOT NULL;
|
||
|
||
-- ============================================================
|
||
-- 验证
|
||
-- ============================================================
|
||
SELECT
|
||
pr.id,
|
||
pwo.process_start_time AS '工单开始时间',
|
||
pr.plan_start_time AS '计划开始时间',
|
||
pr.actual_start_time AS '实际开始时间',
|
||
TIMESTAMPDIFF(SECOND, pr.plan_start_time, pr.actual_start_time) AS '相差秒数'
|
||
FROM pro_report pr
|
||
INNER JOIN pro_workorder_entry pwe ON pwe.id = pr.work_order_entry_id
|
||
INNER JOIN pro_workorder pwo ON pwo.id = pwe.workorder_id
|
||
WHERE pr.actual_start_time IS NOT NULL
|
||
ORDER BY pr.id DESC
|
||
LIMIT 20;
|