-- 管理员端“智能考评-试卷管理-自动组卷(实操)”规则配置(MySQL 5.7+/8.0)。 -- -- 业务口径: -- 1. practice_paper_rule保留为一级至五级标准类目目录,不生成theoretical_paper。 -- 2. practice_paper_rule_definition.level_grade保存污水/污泥完整职级;服务层只在读取目录时提取等级后缀。 -- 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(32) 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_level_name (level_grade, name), key idx_practice_definition_level_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='具名实操试卷规则明细'; -- ==================== 考试规则引用与教师多选关系 ==================== 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 declare active_level_count int default 0; 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; /* * 旧结构只保存“一级至五级”。先确认十个目标职级完整存在,再转换历史规则;缺少任一 * 职级时直接终止,避免把无法判定归属的数据静默迁移到错误工种。 */ select count(distinct trim(name)) into active_level_count from sys_level where coalesce(hidde_flag, 0) = 0 and trim(name) in ( '污水一级', '污水二级', '污水三级', '污水四级', '污水五级', '污泥一级', '污泥二级', '污泥三级', '污泥四级', '污泥五级' ); if active_level_count <> 10 then signal sqlstate '45000' set message_text = '实操规则迁移失败:sys_level必须配置并启用污水、污泥共十个完整职级'; end if; /* 字段名为兼容旧代码继续保留,字段值从本次迁移起保存完整职级。 */ alter table practice_paper_rule_definition modify column level_grade varchar(32) not null comment '完整职级:污水一级至五级或污泥一级至五级'; /* * 已关联旧规则的考试必须能从班级解析出有效完整职级。该校验先于任何规则更新, * 一旦失败不会发生部分规则已经改名、考试却尚未重绑的情况。 */ if exists ( select 1 from exam e inner join practice_paper_rule_definition definition on definition.id = e.practice_rule_id and definition.level_grade in ('一级', '二级', '三级', '四级', '五级') left join `class` c on c.id = e.class_id left join sys_level level_info on cast(level_info.id as char) = c.level where level_info.id is null or coalesce(level_info.hidde_flag, 0) <> 0 or trim(level_info.name) not in ( '污水一级', '污水二级', '污水三级', '污水四级', '污水五级', '污泥一级', '污泥二级', '污泥三级', '污泥四级', '污泥五级' ) or right(trim(level_info.name), char_length(definition.level_grade)) <> definition.level_grade ) then signal sqlstate '45000' set message_text = '实操规则迁移失败:历史考试存在缺失、停用或与规则等级不一致的班级职级'; end if; drop temporary table if exists tmp_practice_rule_targets; create temporary table tmp_practice_rule_targets ( old_rule_id int not null, target_level varchar(32) not null, primary key (old_rule_id, target_level) ) engine=InnoDB; /* 名称明确包含完整职级的规则优先保留原有业务归属。 */ insert ignore into tmp_practice_rule_targets (old_rule_id, target_level) select definition.id, trim(level_info.name) from practice_paper_rule_definition definition inner join sys_level level_info on right(trim(level_info.name), char_length(definition.level_grade)) = definition.level_grade and instr(definition.name, trim(level_info.name)) > 0 where definition.level_grade in ('一级', '二级', '三级', '四级', '五级') and coalesce(level_info.hidde_flag, 0) = 0; /* 名称没有工种信息的共享旧规则,为同等级的污水和污泥职级各保留一份。 */ insert ignore into tmp_practice_rule_targets (old_rule_id, target_level) select definition.id, trim(level_info.name) from practice_paper_rule_definition definition inner join sys_level level_info on right(trim(level_info.name), char_length(definition.level_grade)) = definition.level_grade where definition.level_grade in ('一级', '二级', '三级', '四级', '五级') and coalesce(level_info.hidde_flag, 0) = 0 and not exists ( select 1 from sys_level named_level where coalesce(named_level.hidde_flag, 0) = 0 and right(trim(named_level.name), char_length(definition.level_grade)) = definition.level_grade and instr(definition.name, trim(named_level.name)) > 0 ); /* 即使规则名称与实际历史用法不一致,也必须为每个已引用班级建立可重绑的目标副本。 */ insert ignore into tmp_practice_rule_targets (old_rule_id, target_level) select distinct definition.id, trim(level_info.name) from practice_paper_rule_definition definition inner join exam e on e.practice_rule_id = definition.id 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 where definition.level_grade in ('一级', '二级', '三级', '四级', '五级'); drop temporary table if exists tmp_practice_rule_primary; create temporary table tmp_practice_rule_primary ( old_rule_id int not null, target_level varchar(32) not null, primary key (old_rule_id) ) engine=InnoDB; insert into tmp_practice_rule_primary (old_rule_id, target_level) select old_rule_id, min(target_level) from tmp_practice_rule_targets group by old_rule_id; /* 原记录承接一个目标职级,其余目标通过复制主表和明细产生,确保原ID仍然有效。 */ update practice_paper_rule_definition definition inner join tmp_practice_rule_primary target on target.old_rule_id = definition.id set definition.level_grade = target.target_level; 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 source.name, target.target_level, source.question_count, source.total_duration, source.total_score, source.status, source.created_by, source.created_time, source.updated_by, source.updated_time from tmp_practice_rule_targets target inner join tmp_practice_rule_primary primary_target on primary_target.old_rule_id = target.old_rule_id and primary_target.target_level <> target.target_level inner join practice_paper_rule_definition source on source.id = target.old_rule_id; insert ignore into practice_paper_rule_detail (rule_id, catalog_id, assessment_duration, score, question_count) select target_definition.id, old_detail.catalog_id, old_detail.assessment_duration, old_detail.score, old_detail.question_count from tmp_practice_rule_targets target inner join practice_paper_rule_definition source on source.id = target.old_rule_id inner join practice_paper_rule_definition target_definition on target_definition.level_grade = target.target_level and target_definition.name = source.name inner join practice_paper_rule_detail old_detail on old_detail.rule_id = target.old_rule_id; /* 按考试所属班级完整职级选择迁移后的规则ID,彻底消除跨工种共享引用。 */ 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 tmp_practice_rule_targets target on target.old_rule_id = e.practice_rule_id and target.target_level = trim(level_info.name) inner join practice_paper_rule_definition source on source.id = target.old_rule_id inner join practice_paper_rule_definition target_definition on target_definition.level_grade = target.target_level and target_definition.name = source.name set e.practice_rule_id = target_definition.id; /* 索引列保持不变,但名称同步体现其现在约束的是完整职级而非共享等级。 */ if exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'practice_paper_rule_definition' and index_name = 'uk_practice_definition_grade_name' ) then alter table practice_paper_rule_definition drop index uk_practice_definition_grade_name; end if; if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'practice_paper_rule_definition' and index_name = 'uk_practice_definition_level_name' ) then alter table practice_paper_rule_definition add unique index uk_practice_definition_level_name (level_grade, name); end if; if exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'practice_paper_rule_definition' and index_name = 'idx_practice_definition_grade_status' ) then alter table practice_paper_rule_definition drop index idx_practice_definition_grade_status; end if; if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'practice_paper_rule_definition' and index_name = 'idx_practice_definition_level_status' ) then alter table practice_paper_rule_definition add index idx_practice_definition_level_status (level_grade, status, updated_time, id); end if; drop temporary table if exists tmp_practice_rule_primary; drop temporary table if exists tmp_practice_rule_targets; end$$ call migrate_exam_practice_rule()$$ drop procedure if exists migrate_exam_practice_rule$$ delimiter ; /* * 历史规则迁移完成后,仅给仍没有任何规则的完整职级补一条默认规则。这样新环境会得到十条 * 默认规则,而升级环境会保留历史规则名称,也不会在脚本重复执行时不断追加默认规则。 */ 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(trim(level_info.name), '实操默认规则'), trim(level_info.name), sum(catalog.question_count), sum(catalog.assessment_duration * catalog.question_count), sum(catalog.score * catalog.question_count), 'PUBLISHED', null, now(), null, now() from sys_level level_info inner join practice_paper_rule catalog on right(trim(level_info.name), char_length(catalog.level_grade)) = catalog.level_grade where coalesce(level_info.hidde_flag, 0) = 0 and trim(level_info.name) in ( '污水一级', '污水二级', '污水三级', '污水四级', '污水五级', '污泥一级', '污泥二级', '污泥三级', '污泥四级', '污泥五级' ) and not exists ( select 1 from practice_paper_rule_definition existing where existing.level_grade = trim(level_info.name) ) group by trim(level_info.name); 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 right(definition.level_grade, char_length(catalog.level_grade)) = catalog.level_grade and definition.name = concat(definition.level_grade, '实操默认规则'); -- 首席考评员历史字段为单值,可直接迁移;空值不会生成无效关系。 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 definition.level_grade = trim(level_info.name) and definition.name in ( concat(trim(level_info.name), '实操默认规则'), concat(right(trim(level_info.name), 2), '实操默认规则') ) 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;