-- 质量管理演示数据(MySQL 5.7+/8.0,可重复执行) -- 覆盖:指标维护、原材料检验、过程检验、出厂检验、不合格管理。 -- 执行前请先执行 20261029_quality_unqualified_deal_type.sql。 -- 默认使用 admin 用户所属租户;如不存在 admin,则使用最小租户 ID,最后回退到 0。 SET @demo_tenant_id = COALESCE( (SELECT tenant_id FROM sys_user WHERE user_name = 'admin' LIMIT 1), (SELECT MIN(tenant_id) FROM sys_user WHERE tenant_id IS NOT NULL), 0 ); -- 一、基础演示产品和工序 INSERT INTO product (parent_id, product_name, tenant_id) SELECT NULL, '工业铝板(演示)', @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM product WHERE product_name = '工业铝板(演示)' AND tenant_id = @demo_tenant_id ); INSERT INTO product (parent_id, product_name, tenant_id) SELECT NULL, '精密轴承(演示)', @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM product WHERE product_name = '精密轴承(演示)' AND tenant_id = @demo_tenant_id ); INSERT INTO product (parent_id, product_name, tenant_id) SELECT NULL, '控制器总成(演示)', @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM product WHERE product_name = '控制器总成(演示)' AND tenant_id = @demo_tenant_id ); INSERT INTO product_process (name, no, remark, create_time, update_time, tenant_id) SELECT '精加工(演示)', 'DEMO-P-001', '质量管理演示工序', NOW(), NOW(), @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM product_process WHERE no = 'DEMO-P-001' AND tenant_id = @demo_tenant_id ); SET @raw_product_id = ( SELECT id FROM product WHERE product_name = '工业铝板(演示)' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1 ); SET @process_product_id = ( SELECT id FROM product WHERE product_name = '精密轴承(演示)' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1 ); SET @final_product_id = ( SELECT id FROM product WHERE product_name = '控制器总成(演示)' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1 ); SET @demo_process_id = ( SELECT id FROM product_process WHERE no = 'DEMO-P-001' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1 ); -- 二、三类检验标准 INSERT INTO quality_test_standard (standard_no, standard_name, remark, create_time, create_user, update_time, update_user, tenant_id, state, inspect_type, process_id) SELECT 'QM-DEMO-RAW-001', '工业铝板来料检验标准', '演示数据', NOW(), 1, NOW(), 1, @demo_tenant_id, '0', 0, NULL WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard WHERE standard_no = 'QM-DEMO-RAW-001' AND tenant_id = @demo_tenant_id ); INSERT INTO quality_test_standard (standard_no, standard_name, remark, create_time, create_user, update_time, update_user, tenant_id, state, inspect_type, process_id) SELECT 'QM-DEMO-PROCESS-001', '精密轴承精加工检验标准', '演示数据', NOW(), 1, NOW(), 1, @demo_tenant_id, '0', 1, @demo_process_id WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard WHERE standard_no = 'QM-DEMO-PROCESS-001' AND tenant_id = @demo_tenant_id ); INSERT INTO quality_test_standard (standard_no, standard_name, remark, create_time, create_user, update_time, update_user, tenant_id, state, inspect_type, process_id) SELECT 'QM-DEMO-FINAL-001', '控制器总成出厂检验标准', '演示数据', NOW(), 1, NOW(), 1, @demo_tenant_id, '0', 2, NULL WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard WHERE standard_no = 'QM-DEMO-FINAL-001' AND tenant_id = @demo_tenant_id ); SET @raw_standard_id = ( SELECT id FROM quality_test_standard WHERE standard_no = 'QM-DEMO-RAW-001' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1 ); SET @process_standard_id = ( SELECT id FROM quality_test_standard WHERE standard_no = 'QM-DEMO-PROCESS-001' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1 ); SET @final_standard_id = ( SELECT id FROM quality_test_standard WHERE standard_no = 'QM-DEMO-FINAL-001' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1 ); -- 三、标准指标参数 INSERT INTO quality_test_standard_param (parameter_item, unit, standard_value, control_value, default_value, create_time, create_user, update_time, update_user, tenant_id, test_standard_id) SELECT '厚度', 'mm', '2.00±0.10', '1.95~2.05', '2.00', NOW(), 1, NOW(), 1, @demo_tenant_id, @raw_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard_param WHERE test_standard_id = @raw_standard_id AND parameter_item = '厚度' ); INSERT INTO quality_test_standard_param (parameter_item, unit, standard_value, control_value, default_value, create_time, create_user, update_time, update_user, tenant_id, test_standard_id) SELECT '表面质量', '/', '无划伤、无氧化斑', '无可见缺陷', '正常', NOW(), 1, NOW(), 1, @demo_tenant_id, @raw_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard_param WHERE test_standard_id = @raw_standard_id AND parameter_item = '表面质量' ); INSERT INTO quality_test_standard_param (parameter_item, unit, standard_value, control_value, default_value, create_time, create_user, update_time, update_user, tenant_id, test_standard_id) SELECT '外径', 'mm', '20.00±0.05', '19.98~20.02', '20.00', NOW(), 1, NOW(), 1, @demo_tenant_id, @process_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard_param WHERE test_standard_id = @process_standard_id AND parameter_item = '外径' ); INSERT INTO quality_test_standard_param (parameter_item, unit, standard_value, control_value, default_value, create_time, create_user, update_time, update_user, tenant_id, test_standard_id) SELECT '圆度', 'mm', '≤0.02', '≤0.015', '0.01', NOW(), 1, NOW(), 1, @demo_tenant_id, @process_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard_param WHERE test_standard_id = @process_standard_id AND parameter_item = '圆度' ); INSERT INTO quality_test_standard_param (parameter_item, unit, standard_value, control_value, default_value, create_time, create_user, update_time, update_user, tenant_id, test_standard_id) SELECT '绝缘电阻', 'MΩ', '≥100', '≥120', '150', NOW(), 1, NOW(), 1, @demo_tenant_id, @final_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard_param WHERE test_standard_id = @final_standard_id AND parameter_item = '绝缘电阻' ); INSERT INTO quality_test_standard_param (parameter_item, unit, standard_value, control_value, default_value, create_time, create_user, update_time, update_user, tenant_id, test_standard_id) SELECT '功能测试', '/', '全部功能通过', '连续运行30分钟无异常', '通过', NOW(), 1, NOW(), 1, @demo_tenant_id, @final_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard_param WHERE test_standard_id = @final_standard_id AND parameter_item = '功能测试' ); -- 四、产品与标准绑定 INSERT INTO quality_test_standard_binding (product_id, test_standard_id, create_time, create_user, update_time, update_user, tenant_id) SELECT @raw_product_id, @raw_standard_id, NOW(), 1, NOW(), 1, @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard_binding WHERE product_id = @raw_product_id AND test_standard_id = @raw_standard_id ); INSERT INTO quality_test_standard_binding (product_id, test_standard_id, create_time, create_user, update_time, update_user, tenant_id) SELECT @process_product_id, @process_standard_id, NOW(), 1, NOW(), 1, @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard_binding WHERE product_id = @process_product_id AND test_standard_id = @process_standard_id ); INSERT INTO quality_test_standard_binding (product_id, test_standard_id, create_time, create_user, update_time, update_user, tenant_id) SELECT @final_product_id, @final_standard_id, NOW(), 1, NOW(), 1, @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM quality_test_standard_binding WHERE product_id = @final_product_id AND test_standard_id = @final_standard_id ); -- 五、原材料、过程、出厂检验记录(每类包含合格与不合格) INSERT INTO quality_inspect (inspect_type, check_time, supplier, customer, process, check_name, product_id, product_name, model, unit, quantity, check_company, check_result, create_time, create_user, update_time, update_user, tenant_id, inspect_state, defective_phenomena, test_standard_id) SELECT 0, DATE_SUB(CURDATE(), INTERVAL 5 DAY), '华东金属材料有限公司', NULL, NULL, '张质检', @raw_product_id, '工业铝板(演示)', 'AL-2.0', '张', 120, '质量管理演示数据', '合格', NOW(), 1, NOW(), 1, @demo_tenant_id, 1, NULL, @raw_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_inspect WHERE inspect_type = 0 AND product_id = @raw_product_id AND check_result = '合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ); INSERT INTO quality_inspect (inspect_type, check_time, supplier, customer, process, check_name, product_id, product_name, model, unit, quantity, check_company, check_result, create_time, create_user, update_time, update_user, tenant_id, inspect_state, defective_phenomena, test_standard_id) SELECT 0, DATE_SUB(CURDATE(), INTERVAL 4 DAY), '北方轻合金有限公司', NULL, NULL, '李质检', @raw_product_id, '工业铝板(演示)', 'AL-2.0', '张', 80, '质量管理演示数据', '不合格', NOW(), 1, NOW(), 1, @demo_tenant_id, 1, '厚度偏薄,边缘存在划伤', @raw_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_inspect WHERE inspect_type = 0 AND product_id = @raw_product_id AND check_result = '不合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ); INSERT INTO quality_inspect (inspect_type, check_time, supplier, customer, process, check_name, product_id, product_name, model, unit, quantity, check_company, check_result, create_time, create_user, update_time, update_user, tenant_id, inspect_state, defective_phenomena, test_standard_id) SELECT 1, DATE_SUB(CURDATE(), INTERVAL 3 DAY), NULL, '内部生产', '精加工(演示)', '王质检', @process_product_id, '精密轴承(演示)', 'BR-6204', '套', 300, '质量管理演示数据', '合格', NOW(), 1, NOW(), 1, @demo_tenant_id, 1, NULL, @process_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_inspect WHERE inspect_type = 1 AND product_id = @process_product_id AND check_result = '合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ); INSERT INTO quality_inspect (inspect_type, check_time, supplier, customer, process, check_name, product_id, product_name, model, unit, quantity, check_company, check_result, create_time, create_user, update_time, update_user, tenant_id, inspect_state, defective_phenomena, test_standard_id) SELECT 1, DATE_SUB(CURDATE(), INTERVAL 2 DAY), NULL, '内部生产', '精加工(演示)', '赵质检', @process_product_id, '精密轴承(演示)', 'BR-6204', '套', 35, '质量管理演示数据', '不合格', NOW(), 1, NOW(), 1, @demo_tenant_id, 1, '外径超差,圆度不满足要求', @process_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_inspect WHERE inspect_type = 1 AND product_id = @process_product_id AND check_result = '不合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ); INSERT INTO quality_inspect (inspect_type, check_time, supplier, customer, process, check_name, product_id, product_name, model, unit, quantity, check_company, check_result, create_time, create_user, update_time, update_user, tenant_id, inspect_state, defective_phenomena, test_standard_id) SELECT 2, DATE_SUB(CURDATE(), INTERVAL 1 DAY), NULL, '华南自动化科技有限公司', NULL, '陈质检', @final_product_id, '控制器总成(演示)', 'CTRL-A8', '台', 60, '质量管理演示数据', '合格', NOW(), 1, NOW(), 1, @demo_tenant_id, 1, NULL, @final_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_inspect WHERE inspect_type = 2 AND product_id = @final_product_id AND check_result = '合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ); INSERT INTO quality_inspect (inspect_type, check_time, supplier, customer, process, check_name, product_id, product_name, model, unit, quantity, check_company, check_result, create_time, create_user, update_time, update_user, tenant_id, inspect_state, defective_phenomena, test_standard_id) SELECT 2, CURDATE(), NULL, '华东智能装备有限公司', NULL, '周质检', @final_product_id, '控制器总成(演示)', 'CTRL-A8', '台', 12, '质量管理演示数据', '不合格', NOW(), 1, NOW(), 1, @demo_tenant_id, 1, '绝缘电阻偏低,功能测试间歇性失效', @final_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_inspect WHERE inspect_type = 2 AND product_id = @final_product_id AND check_result = '不合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ); SET @raw_ok_id = (SELECT id FROM quality_inspect WHERE inspect_type = 0 AND product_id = @raw_product_id AND check_result = '合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1); SET @raw_ng_id = (SELECT id FROM quality_inspect WHERE inspect_type = 0 AND product_id = @raw_product_id AND check_result = '不合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1); SET @process_ok_id = (SELECT id FROM quality_inspect WHERE inspect_type = 1 AND product_id = @process_product_id AND check_result = '合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1); SET @process_ng_id = (SELECT id FROM quality_inspect WHERE inspect_type = 1 AND product_id = @process_product_id AND check_result = '不合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1); SET @final_ok_id = (SELECT id FROM quality_inspect WHERE inspect_type = 2 AND product_id = @final_product_id AND check_result = '合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1); SET @final_ng_id = (SELECT id FROM quality_inspect WHERE inspect_type = 2 AND product_id = @final_product_id AND check_result = '不合格' AND check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id ORDER BY id LIMIT 1); -- 六、检验明细。使用 UNION ALL 后统一排除已存在项。 INSERT INTO quality_inspect_param (parameter_item, unit, standard_value, control_value, test_value, inspect_id, create_time, create_user, update_time, update_user, tenant_id) SELECT d.parameter_item, d.unit, d.standard_value, d.control_value, d.test_value, d.inspect_id, NOW(), 1, NOW(), 1, @demo_tenant_id FROM ( SELECT '厚度' parameter_item, 'mm' unit, '2.00±0.10' standard_value, '1.95~2.05' control_value, '2.01' test_value, @raw_ok_id inspect_id UNION ALL SELECT '表面质量', '/', '无划伤、无氧化斑', '无可见缺陷', '正常', @raw_ok_id UNION ALL SELECT '厚度', 'mm', '2.00±0.10', '1.95~2.05', '1.86', @raw_ng_id UNION ALL SELECT '表面质量', '/', '无划伤、无氧化斑', '无可见缺陷', '边缘划伤', @raw_ng_id UNION ALL SELECT '外径', 'mm', '20.00±0.05', '19.98~20.02', '20.01', @process_ok_id UNION ALL SELECT '圆度', 'mm', '≤0.02', '≤0.015', '0.01', @process_ok_id UNION ALL SELECT '外径', 'mm', '20.00±0.05', '19.98~20.02', '20.18', @process_ng_id UNION ALL SELECT '圆度', 'mm', '≤0.02', '≤0.015', '0.05', @process_ng_id UNION ALL SELECT '绝缘电阻', 'MΩ', '≥100', '≥120', '156', @final_ok_id UNION ALL SELECT '功能测试', '/', '全部功能通过', '连续运行30分钟无异常', '通过', @final_ok_id UNION ALL SELECT '绝缘电阻', 'MΩ', '≥100', '≥120', '62', @final_ng_id UNION ALL SELECT '功能测试', '/', '全部功能通过', '连续运行30分钟无异常', '间歇性失效', @final_ng_id ) d WHERE d.inspect_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM quality_inspect_param p WHERE p.inspect_id = d.inspect_id AND p.parameter_item = d.parameter_item ); -- 七、不合格品记录:包含待处理、返工完成和报废完成三种展示状态。 INSERT INTO quality_unqualified (inspect_type, inspect_state, check_time, check_name, product_id, product_name, model, unit, quantity, defective_phenomena, deal_type, deal_result, deal_name, deal_time, create_time, create_user, update_time, update_user, tenant_id) SELECT 0, 0, DATE_SUB(CURDATE(), INTERVAL 4 DAY), '李质检', @raw_product_id, '工业铝板(演示)', 'AL-2.0', '张', 80, '厚度偏薄,边缘存在划伤', NULL, NULL, NULL, NULL, NOW(), 1, NOW(), 1, @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM quality_unqualified WHERE inspect_type = 0 AND product_id = @raw_product_id AND defective_phenomena = '厚度偏薄,边缘存在划伤' AND tenant_id = @demo_tenant_id ); INSERT INTO quality_unqualified (inspect_type, inspect_state, check_time, check_name, product_id, product_name, model, unit, quantity, defective_phenomena, deal_type, deal_result, deal_name, deal_time, create_time, create_user, update_time, update_user, tenant_id) SELECT 1, 1, DATE_SUB(CURDATE(), INTERVAL 2 DAY), '赵质检', @process_product_id, '精密轴承(演示)', 'BR-6204', '套', 35, '外径超差,圆度不满足要求', 'rework', '已返工并复检合格', '钱主管', DATE_SUB(CURDATE(), INTERVAL 1 DAY), NOW(), 1, NOW(), 1, @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM quality_unqualified WHERE inspect_type = 1 AND product_id = @process_product_id AND defective_phenomena = '外径超差,圆度不满足要求' AND tenant_id = @demo_tenant_id ); INSERT INTO quality_unqualified (inspect_type, inspect_state, check_time, check_name, product_id, product_name, model, unit, quantity, defective_phenomena, deal_type, deal_result, deal_name, deal_time, create_time, create_user, update_time, update_user, tenant_id) SELECT 2, 1, CURDATE(), '周质检', @final_product_id, '控制器总成(演示)', 'CTRL-A8', '台', 12, '绝缘电阻偏低,功能测试间歇性失效', 'scrap', '确认无法返修,已隔离报废', '孙主管', CURDATE(), NOW(), 1, NOW(), 1, @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM quality_unqualified WHERE inspect_type = 2 AND product_id = @final_product_id AND defective_phenomena = '绝缘电阻偏低,功能测试间歇性失效' AND tenant_id = @demo_tenant_id ); -- 执行结果摘要 SELECT @demo_tenant_id AS tenant_id, (SELECT COUNT(*) FROM quality_test_standard WHERE standard_no LIKE 'QM-DEMO-%' AND tenant_id = @demo_tenant_id) AS demo_standards, (SELECT COUNT(*) FROM quality_inspect WHERE check_company = '质量管理演示数据' AND tenant_id = @demo_tenant_id) AS demo_inspections, (SELECT COUNT(*) FROM quality_unqualified WHERE product_name LIKE '%(演示)' AND tenant_id = @demo_tenant_id) AS demo_unqualified;