-- ===================================================================== -- 安全生产模块 —— 五期:安全教育培训 -- -- 定位:培训计划 → 组织培训 → 参训签到 → 考试评分 → 持证管理,形成培训档案闭环。 -- 特种作业人员是否持有效证件,可通过资格证书表核查。 -- -- 三张表: -- mes_safety_training 培训批次(一次培训活动) -- mes_safety_training_attendee 参训人员(含签到与考试成绩) -- mes_safety_certificate 安全资格证书(特种作业证等,含有效期) -- -- 内容: -- 1. 建表 3 张 -- 2. 字典:培训类型 / 培训状态 / 签到状态 / 证书类型 -- 3. 菜单:培训管理、资格证书,挂在「安全生产」目录下 -- 4. 授权超级管理员 -- -- 特性:可重复执行 -- ===================================================================== -- --------------------------------------------------------------------- -- 1. 培训批次 -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `mes_safety_training` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `code` varchar(64) NOT NULL COMMENT '培训编号', `title` varchar(200) NOT NULL COMMENT '培训主题', `training_type` tinyint DEFAULT NULL COMMENT '培训类型(字典 mes_safety_training_type)', `training_level` tinyint DEFAULT NULL COMMENT '培训层级(字典 mes_safety_training_level:1公司级 2车间级 3班组级)', `trainer` varchar(100) DEFAULT NULL COMMENT '讲师/授课人', `trainer_org` varchar(200) DEFAULT NULL COMMENT '讲师单位(外部讲师时填写)', `location` varchar(255) DEFAULT NULL COMMENT '培训地点', `start_time` datetime DEFAULT NULL COMMENT '培训开始时间', `end_time` datetime DEFAULT NULL COMMENT '培训结束时间', `duration_hours` decimal(6,1) DEFAULT NULL COMMENT '学时', `content` varchar(2000) DEFAULT NULL COMMENT '培训内容', `material` varchar(500) DEFAULT NULL COMMENT '培训资料/教材', `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态:0计划中 10进行中 20已完成 30已取消', `attendee_count` int DEFAULT '0' COMMENT '参训人数(冗余统计)', `signed_count` int DEFAULT '0' COMMENT '实到人数(冗余统计)', `avg_score` decimal(6,2) DEFAULT NULL COMMENT '考试平均分(冗余统计)', `remark` varchar(500) DEFAULT NULL COMMENT '备注', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uk_code` (`code`) USING BTREE, KEY `idx_status` (`status`) USING BTREE, KEY `idx_start` (`start_time`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='安全生产-培训批次'; -- --------------------------------------------------------------------- -- 2. 参训人员(含签到与考试成绩) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `mes_safety_training_attendee` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `training_id` bigint NOT NULL COMMENT '培训批次编号', `user_id` bigint NOT NULL COMMENT '参训人编号', `dept_id` bigint DEFAULT NULL COMMENT '所属部门编号', `sign_status` tinyint DEFAULT '0' COMMENT '签到状态:0未签到 1已签到 2迟到 3缺席', `sign_time` datetime DEFAULT NULL COMMENT '签到时间', `score` decimal(6,2) DEFAULT NULL COMMENT '考试成绩', `passed` tinyint DEFAULT '0' COMMENT '是否通过:0未考 1通过 2不通过', `remark` varchar(255) DEFAULT NULL COMMENT '备注', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) USING BTREE, KEY `idx_training` (`training_id`) USING BTREE, KEY `idx_user` (`user_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='安全生产-培训参训人员'; -- --------------------------------------------------------------------- -- 3. 安全资格证书 -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `mes_safety_certificate` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `user_id` bigint NOT NULL COMMENT '持证人编号', `dept_id` bigint DEFAULT NULL COMMENT '所属部门编号', `cert_type` tinyint NOT NULL COMMENT '证书类型(字典 mes_safety_cert_type)', `cert_no` varchar(100) NOT NULL COMMENT '证书编号', `issue_org` varchar(200) DEFAULT NULL COMMENT '发证机关', `issue_date` date DEFAULT NULL COMMENT '发证日期', `expire_date` date DEFAULT NULL COMMENT '有效期至(空表示长期有效)', `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态:1有效 0已作废', `attachment_url` varchar(500) DEFAULT NULL COMMENT '证书扫描件地址', `remark` varchar(500) DEFAULT NULL COMMENT '备注', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uk_cert_no` (`cert_no`) USING BTREE, KEY `idx_user` (`user_id`) USING BTREE, KEY `idx_expire` (`expire_date`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='安全生产-安全资格证书'; -- --------------------------------------------------------------------- -- 4. 字典 -- --------------------------------------------------------------------- DELETE FROM system_dict_data WHERE dict_type IN ('mes_safety_training_type', 'mes_safety_training_level', 'mes_safety_training_status', 'mes_safety_attendee_status', 'mes_safety_cert_type'); DELETE FROM system_dict_type WHERE type IN ('mes_safety_training_type', 'mes_safety_training_level', 'mes_safety_training_status', 'mes_safety_attendee_status', 'mes_safety_cert_type'); INSERT INTO system_dict_type (name, type, status, remark, creator, create_time, updater, update_time, deleted) VALUES ('安全生产-培训类型', 'mes_safety_training_type', 0, '培训活动分类', '1', NOW(), '1', NOW(), b'0'), ('安全生产-培训层级', 'mes_safety_training_level', 0, '三级教育层级', '1', NOW(), '1', NOW(), b'0'), ('安全生产-培训状态', 'mes_safety_training_status', 0, '培训批次状态', '1', NOW(), '1', NOW(), b'0'), ('安全生产-签到状态', 'mes_safety_attendee_status', 0, '参训签到情况', '1', NOW(), '1', NOW(), b'0'), ('安全生产-证书类型', 'mes_safety_cert_type', 0, '安全资格证书类型', '1', NOW(), '1', NOW(), b'0'); INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, creator, create_time, updater, update_time, deleted) VALUES -- 培训类型 (1, '三级安全教育', '1', 'mes_safety_training_type', 0, 'processing', '1', NOW(), '1', NOW(), b'0'), (2, '专项培训', '2', 'mes_safety_training_type', 0, 'default', '1', NOW(), '1', NOW(), b'0'), (3, '复训', '3', 'mes_safety_training_type', 0, 'warning', '1', NOW(), '1', NOW(), b'0'), (4, '日常安全教育', '4', 'mes_safety_training_type', 0, 'default', '1', NOW(), '1', NOW(), b'0'), (5, '其他', '5', 'mes_safety_training_type', 0, 'default', '1', NOW(), '1', NOW(), b'0'), -- 培训层级 (1, '公司级', '1', 'mes_safety_training_level', 0, 'processing', '1', NOW(), '1', NOW(), b'0'), (2, '车间级', '2', 'mes_safety_training_level', 0, 'default', '1', NOW(), '1', NOW(), b'0'), (3, '班组级', '3', 'mes_safety_training_level', 0, 'default', '1', NOW(), '1', NOW(), b'0'), -- 培训状态 (1, '计划中', '0', 'mes_safety_training_status', 0, 'processing', '1', NOW(), '1', NOW(), b'0'), (2, '进行中', '10', 'mes_safety_training_status', 0, 'warning', '1', NOW(), '1', NOW(), b'0'), (3, '已完成', '20', 'mes_safety_training_status', 0, 'success', '1', NOW(), '1', NOW(), b'0'), (4, '已取消', '30', 'mes_safety_training_status', 0, 'default', '1', NOW(), '1', NOW(), b'0'), -- 签到状态 (1, '未签到', '0', 'mes_safety_attendee_status', 0, 'default', '1', NOW(), '1', NOW(), b'0'), (2, '已签到', '1', 'mes_safety_attendee_status', 0, 'success', '1', NOW(), '1', NOW(), b'0'), (3, '迟到', '2', 'mes_safety_attendee_status', 0, 'warning', '1', NOW(), '1', NOW(), b'0'), (4, '缺席', '3', 'mes_safety_attendee_status', 0, 'error', '1', NOW(), '1', NOW(), b'0'), -- 证书类型 (1, '特种作业操作证', '1', 'mes_safety_cert_type', 0, 'processing', '1', NOW(), '1', NOW(), b'0'), (2, '安全管理人员证', '2', 'mes_safety_cert_type', 0, 'default', '1', NOW(), '1', NOW(), b'0'), (3, '主要负责人证', '3', 'mes_safety_cert_type', 0, 'default', '1', NOW(), '1', NOW(), b'0'), (4, '其他资格证', '4', 'mes_safety_cert_type', 0, 'default', '1', NOW(), '1', NOW(), b'0'); -- --------------------------------------------------------------------- -- 5. 菜单(挂在 1065910 安全生产下) -- --------------------------------------------------------------------- DELETE FROM system_role_menu WHERE menu_id BETWEEN 1065941 AND 1065951; DELETE FROM system_menu WHERE id BETWEEN 1065941 AND 1065951; INSERT INTO system_menu (id, name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted) VALUES -- 培训管理 (1065941, '安全教育培训', '', 2, 6, 1065910, 'training', 'ep:reading', 'safety/training/index', 'SafetyTraining', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), (1065942, '培训查询', 'mes:safety-training:query', 3, 1, 1065941, '', '', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), (1065943, '培训创建', 'mes:safety-training:create', 3, 2, 1065941, '', '', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), (1065944, '培训修改', 'mes:safety-training:update', 3, 3, 1065941, '', '', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), (1065945, '培训删除', 'mes:safety-training:delete', 3, 4, 1065941, '', '', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), (1065946, '参训人员维护', 'mes:safety-training:attendee', 3, 5, 1065941, '', '', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), -- 资格证书 (1065947, '安全资格证书', '', 2, 7, 1065910, 'certificate', 'ep:medal', 'safety/certificate/index', 'SafetyCertificate', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), (1065948, '证书查询', 'mes:safety-certificate:query', 3, 1, 1065947, '', '', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), (1065949, '证书创建', 'mes:safety-certificate:create', 3, 2, 1065947, '', '', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), (1065950, '证书修改', 'mes:safety-certificate:update', 3, 3, 1065947, '', '', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'), (1065951, '证书删除', 'mes:safety-certificate:delete', 3, 4, 1065947, '', '', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'); INSERT INTO system_role_menu (role_id, menu_id, creator, create_time, updater, update_time, deleted) SELECT 1, m.id, '1', NOW(), '1', NOW(), b'0' FROM system_menu m WHERE m.id BETWEEN 1065941 AND 1065951; -- --------------------------------------------------------------------- -- 6. 验证 -- --------------------------------------------------------------------- SELECT id, parent_id, name, path, component, permission, type FROM system_menu WHERE id BETWEEN 1065910 AND 1065960 ORDER BY id; SELECT dict_type, label, value FROM system_dict_data WHERE dict_type LIKE 'mes_safety_training%' OR dict_type IN ('mes_safety_attendee_status', 'mes_safety_cert_type') ORDER BY dict_type, sort;