-- 质量管理演示数据补充(MySQL 5.7+/8.0,可重复执行) -- 在 20261029_quality_demo_data.sql 基础上补充: -- 1) 让步接收处理方式的不合格记录 -- 2) 待处理状态的不合格记录 -- 3) 未提交(草稿)的检验单,便于验证提交校验与列表状态 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 ); 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 @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 ); -- 一、不合格记录:让步接收(已处理) 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, 1, DATE_SUB(CURDATE(), INTERVAL 3 DAY), '李质检', @raw_product_id, '工业铝板(演示)', 'AL-2.0', '张', 15, '表面轻微划伤,不影响装配使用', 'concession', '经技术评审让步接收,限定用途', '钱主管', DATE_SUB(CURDATE(), INTERVAL 2 DAY), NOW(), 1, NOW(), 1, @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM quality_unqualified WHERE tenant_id = @demo_tenant_id AND defective_phenomena = '表面轻微划伤,不影响装配使用' ); -- 二、不合格记录:过程检验待处理 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, 0, CURDATE(), '赵质检', @process_product_id, '精密轴承(演示)', 'BR-6204', '套', 20, '圆度超差,需评审处理', NULL, NULL, NULL, NULL, NOW(), 1, NOW(), 1, @demo_tenant_id WHERE NOT EXISTS ( SELECT 1 FROM quality_unqualified WHERE tenant_id = @demo_tenant_id AND defective_phenomena = '圆度超差,需评审处理' ); -- 三、草稿检验单(未提交,待判定) 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, CURDATE(), '华东金属材料有限公司', NULL, NULL, '张质检', @raw_product_id, '工业铝板(演示)', 'AL-2.0', '张', 50, '质量管理演示数据-草稿', NULL, NOW(), 1, NOW(), 1, @demo_tenant_id, 0, NULL, @raw_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_inspect WHERE tenant_id = @demo_tenant_id AND check_company = '质量管理演示数据-草稿' AND inspect_type = 0 ); 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, CURDATE(), NULL, '内部生产', '精加工(演示)', '王质检', @process_product_id, '精密轴承(演示)', 'BR-6204', '套', 100, '质量管理演示数据-草稿', NULL, NOW(), 1, NOW(), 1, @demo_tenant_id, 0, NULL, @process_standard_id WHERE NOT EXISTS ( SELECT 1 FROM quality_inspect WHERE tenant_id = @demo_tenant_id AND check_company = '质量管理演示数据-草稿' AND inspect_type = 1 ); SET @raw_draft_id = ( SELECT id FROM quality_inspect WHERE tenant_id = @demo_tenant_id AND check_company = '质量管理演示数据-草稿' AND inspect_type = 0 ORDER BY id LIMIT 1 ); SET @process_draft_id = ( SELECT id FROM quality_inspect WHERE tenant_id = @demo_tenant_id AND check_company = '质量管理演示数据-草稿' AND inspect_type = 1 ORDER BY id LIMIT 1 ); -- 四、草稿检验单的检验项目(检验值留空,用于验证提交前校验) 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, NULL, 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, @raw_draft_id inspect_id UNION ALL SELECT '表面质量', '/', '无划伤、无氧化斑', '无可见缺陷', @raw_draft_id UNION ALL SELECT '外径', 'mm', '20.00±0.05', '19.98~20.02', @process_draft_id UNION ALL SELECT '圆度', 'mm', '≤0.02', '≤0.015', @process_draft_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 ); -- 结果摘要 SELECT @demo_tenant_id AS tenant_id, (SELECT COUNT(*) FROM quality_inspect WHERE tenant_id = @demo_tenant_id AND check_company LIKE '质量管理演示数据%') AS inspections, (SELECT COUNT(*) FROM quality_unqualified WHERE tenant_id = @demo_tenant_id) AS unqualified, (SELECT COUNT(*) FROM quality_unqualified WHERE tenant_id = @demo_tenant_id AND deal_type = 'concession') AS concession_count;