1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
| -- =====================================================================
| -- 安全生产模块 —— 五期:安全教育培训
| --
| -- 定位:培训计划 → 组织培训 → 参训签到 → 考试评分 → 持证管理,形成培训档案闭环。
| -- 特种作业人员是否持有效证件,可通过资格证书表核查。
| --
| -- 三张表:
| -- 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 1065960;
|
| DELETE FROM system_menu WHERE id BETWEEN 1065941 AND 1065960;
|
| 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 1065960;
|
| -- ---------------------------------------------------------------------
| -- 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;
|
|