-- ============================================================
|
-- 检验委托单字段绑定修正(数据修复,非结构变更)
|
-- 生成日期:2026-11-05
|
--
|
-- 背景:旧版“下单后自动生成检验委托单”存在绑定错误:
|
-- 1. 送样时间取的是生成当天,而非下单送样日期;
|
-- 2. 样品状态写死为“完好”;
|
-- 3. 检测依据只有标准号,缺少标准名称;
|
-- 4. 备注信息未写入。
|
-- 本脚本仅回填仍保持旧默认值/空值的记录,人工改过的值不会被覆盖。
|
-- 可重复执行(幂等)。
|
--
|
-- 执行前建议备份(在 LIMS 数据库内执行):
|
-- CREATE TABLE cnas_inspection_order_bak_20261105 AS SELECT * FROM cnas_inspection_order;
|
-- CREATE TABLE cnas_inspection_order_detail_bak_20261105 AS SELECT * FROM cnas_inspection_order_detail;
|
-- ============================================================
|
|
START TRANSACTION;
|
|
-- 1) 委托单表头:送样时间改为下单送样日期
|
-- 仅修正“当前送样时间 = 创建日期”的自动默认值
|
UPDATE cnas_inspection_order io
|
JOIN ins_order o ON o.id = io.ins_order_id
|
SET io.commission_date = o.sample_date
|
WHERE io.commission_date IS NOT NULL
|
AND o.sample_date IS NOT NULL
|
AND io.commission_date = DATE(io.create_time)
|
AND io.commission_date <> o.sample_date;
|
|
-- 2) 委托单表头:样品状态改为下单样品状态
|
-- 仅修正旧逻辑写死的“完好”
|
UPDATE cnas_inspection_order io
|
JOIN ins_order o ON o.id = io.ins_order_id
|
SET io.sample_status = o.sample_status
|
WHERE io.sample_status = '完好'
|
AND o.sample_status IS NOT NULL
|
AND o.sample_status <> '';
|
|
-- 3) 委托单明细:样品状态改为下单样品状态
|
UPDATE cnas_inspection_order_detail d
|
JOIN cnas_inspection_order io ON io.inspection_order_id = d.inspection_order_id
|
JOIN ins_order o ON o.id = io.ins_order_id
|
SET d.sample_status = o.sample_status
|
WHERE d.sample_status = '完好'
|
AND o.sample_status IS NOT NULL
|
AND o.sample_status <> '';
|
|
-- 4) 委托单明细:备注改为下单备注
|
-- 仅填充空备注
|
UPDATE cnas_inspection_order_detail d
|
JOIN cnas_inspection_order io ON io.inspection_order_id = d.inspection_order_id
|
JOIN ins_order o ON o.id = io.ins_order_id
|
SET d.remark = o.remark
|
WHERE (d.remark IS NULL OR d.remark = '')
|
AND o.remark IS NOT NULL
|
AND o.remark <> '';
|
|
-- 5) 委托单明细:检测依据改为“标准号 + 标准名称”
|
-- 仅修正空值、旧逻辑写入的纯标准号,人工改过的完整依据不覆盖
|
UPDATE cnas_inspection_order_detail d
|
JOIN cnas_inspection_order io ON io.inspection_order_id = d.inspection_order_id
|
JOIN ins_order o ON o.id = io.ins_order_id
|
LEFT JOIN standard_tree st ON st.id = o.standard_tree_id
|
LEFT JOIN standard_method sm ON sm.id = st.standard_method_id
|
SET d.test_standard = TRIM(CONCAT(COALESCE(sm.code, ''), ' ', COALESCE(sm.name, '')))
|
WHERE sm.id IS NOT NULL
|
AND (d.test_standard IS NULL
|
OR d.test_standard = ''
|
OR d.test_standard COLLATE utf8mb4_general_ci = o.sample COLLATE utf8mb4_general_ci
|
OR d.test_standard COLLATE utf8mb4_general_ci = sm.code COLLATE utf8mb4_general_ci);
|
|
COMMIT;
|
|
-- 校验(执行完可人工核对):
|
-- SELECT io.inspection_order_id, io.entrust_code, io.commission_date, o.sample_date,
|
-- io.sample_status, o.sample_status
|
-- FROM cnas_inspection_order io JOIN ins_order o ON o.id = io.ins_order_id
|
-- WHERE io.ins_order_id IS NOT NULL;
|
--
|
-- SELECT d.inspection_order_detail_id, d.sample_name, d.sample_status, d.test_standard, d.remark
|
-- FROM cnas_inspection_order_detail d
|
-- JOIN cnas_inspection_order io ON io.inspection_order_id = d.inspection_order_id
|
-- JOIN ins_order o ON o.id = io.ins_order_id;
|