-- ============================================================
|
-- 检验委托单旧数据明细回填(数据修复,非结构变更)
|
-- 生成日期:2026-11-06
|
--
|
-- 背景:旧版自动生成检验委托单时只写了表头,没有写入
|
-- cnas_inspection_order_detail 明细行,导致查看/编辑弹窗里
|
-- 样品名称、样品编号、检测项目、数量、状态、检测依据、备注、
|
-- 委托编号全部为空。新版监听器已会写明细,仅需回填历史数据。
|
--
|
-- 回填来源:
|
-- 样品名称 cnas_inspection_order.sample_name(空时取 ins_order)
|
-- 样品编号 ins_sample 第一条样品编号
|
-- 检测项目 ins_order_standard_template.name 去重后“、”拼接
|
-- 数量/状态 cnas_inspection_order 表头(空时取 ins_order)
|
-- 检测依据 standard_tree -> standard_method 的“编号 名称”,
|
-- 取不到时回退到模板快照编号
|
-- 备注 ins_order.remark
|
--
|
-- 仅处理“已有下单记录但没有任何明细行”的委托单;
|
-- 已有明细的记录不改、不重复插入。可重复执行(幂等)。
|
--
|
-- 执行前建议备份(在 LIMS 数据库内执行):
|
-- CREATE TABLE cnas_inspection_order_detail_bak_20261106 AS
|
-- SELECT * FROM cnas_inspection_order_detail;
|
-- ============================================================
|
|
START TRANSACTION;
|
|
INSERT INTO cnas_inspection_order_detail
|
(inspection_order_id, sample_number, sample_name, test_item,
|
quantity, sample_status, test_standard, remark)
|
SELECT
|
io.inspection_order_id,
|
(SELECT s.sample_code
|
FROM ins_sample s
|
WHERE s.ins_order_id = o.id
|
ORDER BY s.id
|
LIMIT 1),
|
COALESCE(NULLIF(io.sample_name, ''), NULLIF(o.sample_view, ''), o.sample),
|
(SELECT GROUP_CONCAT(DISTINCT t.name ORDER BY t.sort, t.id SEPARATOR '、')
|
FROM ins_order_standard_template t
|
WHERE t.ins_order_id = o.id),
|
COALESCE(NULLIF(io.quantity, ''), o.test_quantity),
|
COALESCE(NULLIF(io.sample_status, ''), o.sample_status),
|
COALESCE(
|
NULLIF(TRIM(CONCAT(COALESCE(sm.code, ''), ' ', COALESCE(sm.name, ''))), ''),
|
(SELECT GROUP_CONCAT(DISTINCT t.number ORDER BY t.sort, t.id SEPARATOR '、')
|
FROM ins_order_standard_template t
|
WHERE t.ins_order_id = o.id)
|
),
|
o.remark
|
FROM cnas_inspection_order io
|
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
|
WHERE NOT EXISTS (
|
SELECT 1 FROM cnas_inspection_order_detail d
|
WHERE d.inspection_order_id = io.inspection_order_id
|
);
|
|
COMMIT;
|
|
-- 校验(执行完可人工核对,应返回 0 行):
|
-- SELECT io.inspection_order_id, io.entrust_code
|
-- FROM cnas_inspection_order io
|
-- JOIN ins_order o ON o.id = io.ins_order_id
|
-- WHERE NOT EXISTS (
|
-- SELECT 1 FROM cnas_inspection_order_detail d
|
-- WHERE d.inspection_order_id = io.inspection_order_id
|
-- );
|
--
|
-- 抽查回填结果:
|
-- SELECT d.inspection_order_detail_id, d.inspection_order_id, d.sample_name,
|
-- d.sample_number, d.test_item, d.quantity, 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
|
-- ORDER BY d.inspection_order_id;
|