-- 管理员端“智能考评-试卷管理-自动组卷(实操)”规则配置(MySQL 5.7+/8.0)。 -- -- 业务口径: -- 1. practice_paper_rule保留为一级至五级标准类目目录,不生成theoretical_paper。 -- 2. level_grade使用Excel中的一级至五级;污水一级、污泥一级等职级在服务层统一匹配一级。 -- 3. 考核时间和单题配分默认20;目录数值会迁移成每级一条已发布默认规则。 -- 4. INSERT IGNORE只补充缺失类目,重复执行不会覆盖已经迁移的具名规则。 create table if not exists practice_paper_rule ( id int not null auto_increment comment '规则ID', level_grade varchar(8) not null comment '标准等级:一级至五级', project_name varchar(64) not null comment 'Excel项目名称', unit_content varchar(128) not null comment 'Excel单元内容', assessment_duration int not null default 20 comment '单题考核时间(分钟)', score decimal(8,2) not null default 20.00 comment '单题配分', question_count int not null default 0 comment '该类目考核题量;整套合计最多5题', created_time datetime not null default current_timestamp comment '创建时间', updated_time datetime not null default current_timestamp comment '更新时间', updated_by varchar(64) null comment '最后更新管理员ID', primary key (id), unique key uk_practice_rule_grade_project_unit (level_grade, project_name, unit_content), key idx_practice_rule_grade_id (level_grade, id) ) engine=InnoDB comment='实操自动组卷类目、考核时间及配分规则'; -- 五级(6条) insert ignore into practice_paper_rule (level_grade, project_name, unit_content, assessment_duration, score, question_count) values ('五级', '运行管理', '日常运行管理', 20, 20.00, 1), ('五级', '运行管理', '基础化验', 20, 20.00, 1), ('五级', '设施设备管理', '基础设备操作', 20, 20.00, 1), ('五级', '设施设备管理', '常用仪表操作', 20, 20.00, 1), ('五级', '安全管理', '安全生产管理', 20, 20.00, 1), ('五级', '安全管理', '安全操作注意事项', 20, 20.00, 0); -- 四级(6条) insert ignore into practice_paper_rule (level_grade, project_name, unit_content, assessment_duration, score, question_count) values ('四级', '运行管理', '日常运行管理', 20, 20.00, 1), ('四级', '运行管理', '日常运行操作', 20, 20.00, 1), ('四级', '设施设备管理', '设备维护', 20, 20.00, 1), ('四级', '设施设备管理', '仪表维护', 20, 20.00, 1), ('四级', '安全管理', '日常防护操作', 20, 20.00, 1), ('四级', '安全管理', '常见急救操作', 20, 20.00, 0); -- 三级(6条) insert ignore into practice_paper_rule (level_grade, project_name, unit_content, assessment_duration, score, question_count) values ('三级', '运行管理', '工艺运行分析', 20, 20.00, 1), ('三级', '运行管理', '工艺运行调整', 20, 20.00, 1), ('三级', '设施设备管理', '设备维修', 20, 20.00, 1), ('三级', '设施设备管理', '仪表校验', 20, 20.00, 1), ('三级', '安全管理', '特种设备操作管理', 20, 20.00, 1), ('三级', '安全管理', '应急预案管理', 20, 20.00, 0); -- 二级(9条) insert ignore into practice_paper_rule (level_grade, project_name, unit_content, assessment_duration, score, question_count) values ('二级', '运行管理', '工艺管理', 20, 20.00, 1), ('二级', '运行管理', '工艺优化', 20, 20.00, 1), ('二级', '设施设备管理', '设施设备操作', 20, 20.00, 1), ('二级', '设施设备管理', '电气自动化操作', 20, 20.00, 1), ('二级', '设施设备管理', '生产仪表操作', 20, 20.00, 1), ('二级', '技术管理', '智能应用', 20, 20.00, 0), ('二级', '技术管理', '前沿技术探索', 20, 20.00, 0), ('二级', '安全管理', '制度制定与管理', 20, 20.00, 0), ('二级', '安全管理', '安全评估', 20, 20.00, 0); -- 一级(9条)。Excel原“设施设备擦欧总”经业务确认修正为“设施设备操作”。 insert ignore into practice_paper_rule (level_grade, project_name, unit_content, assessment_duration, score, question_count) values ('一级', '运行管理', '项目开发', 20, 20.00, 1), ('一级', '运行管理', '项目应用', 20, 20.00, 1), ('一级', '设施设备管理', '设施设备操作', 20, 20.00, 1), ('一级', '设施设备管理', '电气自动化操作', 20, 20.00, 1), ('一级', '设施设备管理', '生产仪表操作', 20, 20.00, 1), ('一级', '技术管理', '智能应用', 20, 20.00, 0), ('一级', '技术管理', '前沿技术探索', 20, 20.00, 0), ('一级', '安全管理', '现场安全管理', 20, 20.00, 0), ('一级', '安全管理', '新业态项目安全管理', 20, 20.00, 0); -- 部署后核对:应返回五级6、四级6、三级6、二级9、一级9,共36条。 select level_grade, count(*) as category_count from practice_paper_rule group by level_grade order by field(level_grade, '五级', '四级', '三级', '二级', '一级'); -- ==================== 具名规则主表与明细 ==================== create table if not exists practice_paper_rule_definition ( id int not null auto_increment comment '实操试卷规则ID', name varchar(200) not null comment '规则名称', level_grade varchar(8) not null comment '共享标准等级:一级至五级', question_count int not null comment '规则总题量快照', total_duration int not null comment '规则总考核时间(分钟)', total_score decimal(8,2) not null comment '规则总分快照', status varchar(16) not null default 'DRAFT' comment 'DRAFT/PUBLISHED', created_by varchar(64) null comment '创建管理员ID', created_time datetime not null default current_timestamp comment '创建时间', updated_by varchar(64) null comment '最后更新管理员ID', updated_time datetime not null default current_timestamp comment '更新时间', primary key (id), unique key uk_practice_definition_grade_name (level_grade, name), key idx_practice_definition_grade_status (level_grade, status, updated_time, id) ) engine=InnoDB comment='具名实操试卷规则主表'; create table if not exists practice_paper_rule_detail ( id int not null auto_increment comment '规则明细ID', rule_id int not null comment '实操试卷规则ID', catalog_id int not null comment '标准类目ID', assessment_duration int not null comment '单题考核时间(分钟)', score decimal(8,2) not null comment '单题配分', question_count int not null default 0 comment '该类目考核题量', primary key (id), unique key uk_practice_detail_rule_catalog (rule_id, catalog_id), key idx_practice_detail_rule (rule_id, id) ) engine=InnoDB comment='具名实操试卷规则明细'; -- 把升级前每个等级的唯一配置固化成已发布默认规则;唯一键保证脚本可重复执行。 insert ignore into practice_paper_rule_definition (name, level_grade, question_count, total_duration, total_score, status, created_by, created_time, updated_by, updated_time) select concat(catalog.level_grade, '实操默认规则'), catalog.level_grade, sum(catalog.question_count), sum(catalog.assessment_duration * catalog.question_count), sum(catalog.score * catalog.question_count), 'PUBLISHED', null, now(), null, now() from practice_paper_rule catalog group by catalog.level_grade; insert ignore into practice_paper_rule_detail (rule_id, catalog_id, assessment_duration, score, question_count) select definition.id, catalog.id, catalog.assessment_duration, catalog.score, catalog.question_count from practice_paper_rule catalog inner join practice_paper_rule_definition definition on definition.level_grade = catalog.level_grade and definition.name = concat(catalog.level_grade, '实操默认规则'); -- ==================== 考试规则引用与教师多选关系 ==================== create table if not exists exam_teacher_assignment ( id int not null auto_increment comment '关系ID', exam_id int not null comment '考试ID', teacher_id varchar(64) not null comment '教师用户ID', role varchar(16) not null comment 'CHIEF/INVIGILATOR', display_order int not null default 0 comment '表单选择顺序', primary key (id), unique key uk_exam_teacher_role (exam_id, teacher_id, role), key idx_exam_teacher_permission (teacher_id, role, exam_id), key idx_exam_teacher_exam (exam_id, role, display_order, id) ) engine=InnoDB comment='考试首席考评员与监考教师多选关系'; delimiter $$ drop procedure if exists migrate_exam_practice_rule$$ create procedure migrate_exam_practice_rule() begin if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'exam' and column_name = 'practice_rule_id' ) then alter table exam add column practice_rule_id int null comment '实训试卷规则ID' after paper_id; end if; if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'exam' and index_name = 'idx_exam_practice_rule' ) then alter table exam add index idx_exam_practice_rule (practice_rule_id, id); end if; end$$ call migrate_exam_practice_rule()$$ drop procedure if exists migrate_exam_practice_rule$$ delimiter ; -- 首席考评员历史字段为单值,可直接迁移;空值不会生成无效关系。 insert ignore into exam_teacher_assignment (exam_id, teacher_id, role, display_order) select e.id, trim(e.chief_id), 'CHIEF', 0 from exam e where e.chief_id is not null and trim(e.chief_id) <> ''; -- 监考教师历史字段可能是逗号分隔值。MySQL 5.7没有JSON_TABLE,因此使用有限序列表拆分; -- 现有业务不会超过20名监考教师,超过部分仍可由查询端旧字段回退逻辑识别。 insert ignore into exam_teacher_assignment (exam_id, teacher_id, role, display_order) select e.id, trim(substring_index(substring_index(e.invigilation_id, ',', seq_table.seq), ',', -1)), 'INVIGILATOR', seq_table.seq - 1 from exam e inner join ( select 1 seq union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 ) seq_table on seq_table.seq <= 1 + length(e.invigilation_id) - length(replace(e.invigilation_id, ',', '')) where e.invigilation_id is not null and trim(substring_index(substring_index(e.invigilation_id, ',', seq_table.seq), ',', -1)) <> ''; -- 历史实训考试按班级职级末尾的一级至五级匹配默认规则,延续原先跨工种共享规则的口径。 update exam e inner join `class` c on c.id = e.class_id inner join sys_level level_info on cast(level_info.id as char) = c.level inner join practice_paper_rule_definition definition on right(trim(level_info.name), char_length(definition.level_grade)) = definition.level_grade and definition.name = concat(definition.level_grade, '实操默认规则') set e.practice_rule_id = definition.id, e.total_score = coalesce(e.total_score, definition.total_score), e.pass_score = coalesce(e.pass_score, round(definition.total_score * 0.60, 2)) where e.type = '实训' and e.practice_rule_id is null;