-- ============================================================
|
-- 设备使用记录历史数据回填（数据修复，非结构变更）
|
-- 生成日期：2026-11-06
|
--
|
-- 背景：旧版本自动生成使用记录时只写设备、样品和使用人，
|
-- 没有写使用日期、起止时间和温湿度，导致使用记录页面这些列为空。
|
-- 新版本在保存原始记录时会自动带出，但不会回头修改历史数据。
|
--
|
-- 回填口径（按客户确认的规则）：
|
-- 1. 只处理 use_start_date 为空的历史记录，已手工填写的不动；
|
-- 2. 通过 ins_order_template_device 找到该订单、该设备对应的
|
-- 原始记录快照 ins_order_standard_template；
|
-- 3. 同一设备在同一订单对应多个模板时，取检测日期最早的一条；
|
-- 4. 使用开始日期 = 检测开始日期 10:00:00；
|
-- 使用结束日期 = 检测结束日期 19:30:00；
|
-- 没有检测结束日期时按开始日期当天 19:30:00；
|
-- 5. 温度取原始记录快照温度，已有的不覆盖；
|
-- 6. 原始记录没有湿度字段，湿度不回填；
|
-- 7. 找不到原始记录设备关联的手工记录保持为空，由实验人员补填。
|
--
|
-- 数据来源均为库中已存在的原始记录快照，仅 10:00 / 19:30
|
-- 为按客户规则填入的默认时间。
|
--
|
-- 脚本可重复执行（幂等）。
|
-- ============================================================
|
|
-- 1. 备份使用记录表（首次执行创建，重复执行不覆盖）
|
CREATE TABLE IF NOT EXISTS `device_record_bak_20261106` AS
|
SELECT * FROM `device_record`;
|
|
-- 2. 回填前统计
|
-- 2.1 空记录总数
|
SELECT COUNT(*) AS blank_total
|
FROM device_record
|
WHERE use_start_date IS NULL;
|
|
-- 2.2 能匹配到原始记录条件的空记录数
|
SELECT COUNT(*) AS matchable_total
|
FROM device_record dr
|
WHERE dr.use_start_date IS NULL
|
AND EXISTS (
|
SELECT 1
|
FROM ins_order_template_device t
|
JOIN ins_order_standard_template s
|
ON s.ins_order_id = t.ins_order_id AND s.template_id = t.template_id
|
WHERE t.ins_order_id = dr.ins_order_id
|
AND t.device_id = dr.device_id
|
AND s.detection_time IS NOT NULL
|
);
|
|
-- 2.3 匹配不到、将保持为空的记录（人工补填）
|
SELECT dr.id, dr.ins_order_id, dr.device_id, dr.sample_code
|
FROM device_record dr
|
WHERE dr.use_start_date IS NULL
|
AND NOT EXISTS (
|
SELECT 1
|
FROM ins_order_template_device t
|
JOIN ins_order_standard_template s
|
ON s.ins_order_id = t.ins_order_id AND s.template_id = t.template_id
|
WHERE t.ins_order_id = dr.ins_order_id
|
AND t.device_id = dr.device_id
|
AND s.detection_time IS NOT NULL
|
);
|
|
-- 3. 回填
|
START TRANSACTION;
|
|
UPDATE device_record dr
|
JOIN (
|
SELECT t.ins_order_id, t.device_id,
|
s.temperature, s.detection_time, s.detection_end_time
|
FROM ins_order_template_device t
|
JOIN ins_order_standard_template s
|
ON s.ins_order_id = t.ins_order_id AND s.template_id = t.template_id
|
WHERE s.detection_time IS NOT NULL
|
AND s.id = (
|
SELECT s2.id
|
FROM ins_order_template_device t2
|
JOIN ins_order_standard_template s2
|
ON s2.ins_order_id = t2.ins_order_id AND s2.template_id = t2.template_id
|
WHERE t2.ins_order_id = t.ins_order_id
|
AND t2.device_id = t.device_id
|
AND s2.detection_time IS NOT NULL
|
ORDER BY s2.detection_time ASC, s2.id ASC
|
LIMIT 1
|
)
|
) picked ON picked.ins_order_id = dr.ins_order_id AND picked.device_id = dr.device_id
|
SET dr.use_start_date = TIMESTAMP(picked.detection_time, '10:00:00'),
|
dr.use_end_date = TIMESTAMP(COALESCE(picked.detection_end_time, picked.detection_time), '19:30:00'),
|
dr.temperature = COALESCE(NULLIF(dr.temperature, ''), picked.temperature)
|
WHERE dr.use_start_date IS NULL;
|
|
COMMIT;
|
|
-- 4. 校验（执行完可人工核对）
|
-- 4.1 回填前后对比：备份表空格数 - 现表空格数 = 本次回填条数
|
-- SELECT
|
-- (SELECT COUNT(*) FROM device_record_bak_20261106 WHERE use_start_date IS NULL) AS blank_before,
|
-- (SELECT COUNT(*) FROM device_record WHERE use_start_date IS NULL) AS blank_after;
|
--
|
-- 4.2 抽查某订单的回填结果
|
-- SELECT id, device_id, ins_order_id, sample_code, temperature, use_start_date, use_end_date, use_person
|
-- FROM device_record
|
-- WHERE ins_order_id = ?;
|
