mescloud/db/assembly-rest-backfill-example.sql

-- 由数据库管理员核实后，替换四个值，再整体执行。日期使用现场/数据库一致时区。
-- 不可用create_date/update_date/piecework_date直接批量推算。
SET @assembly_record_id = 'REPLACE_WITH_RECORD_ID';
SET @verified_completed_at = '2000-01-01 00:00:00';
SET @verified_operator = 'REPLACE_WITH_OPERATOR';
SET @verified_reason = 'REPLACE_WITH_EVIDENCE';

START TRANSACTION;
SELECT id, sn, oprno, content, assembly_completed_at
FROM mes_product_record WHERE id = @assembly_record_id FOR UPDATE;

INSERT INTO mes_assembly_clock_audit
    (record_id, old_completed_at, new_completed_at, operator_name, reason)
SELECT id, assembly_completed_at, @verified_completed_at, @verified_operator, @verified_reason
FROM mes_product_record
WHERE id = @assembly_record_id AND craft = '100000' AND content = 'OK'
  AND oprno REGEXP '^OP200[A-Za-z]?$' AND assembly_completed_at IS NULL
  AND @verified_completed_at <= CURRENT_TIMESTAMP
  AND @assembly_record_id != 'REPLACE_WITH_RECORD_ID'
  AND @verified_operator != 'REPLACE_WITH_OPERATOR'
  AND @verified_reason != 'REPLACE_WITH_EVIDENCE'
  AND @verified_completed_at != '2000-01-01 00:00:00';
SET @clock_audit_inserted = ROW_COUNT();

UPDATE mes_product_record SET assembly_completed_at = @verified_completed_at
WHERE id = @assembly_record_id AND @clock_audit_inserted = 1;
COMMIT;