-- ============================================================ -- 电力数转增强 - 第八阶段(人力资源:证书资质)数据库变更 -- 适用:MySQL 8.4+ -- 说明:证书资质新表 + 类型字典 + 菜单(幂等) -- 注意:本脚本不包含 tenant_id(项目未启用多租户) -- ============================================================ -- ---------- 1. 证书资质表 ---------- CREATE TABLE IF NOT EXISTS `hrm_certificate` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `employee_id` bigint NOT NULL COMMENT '员工编号', `cert_type` tinyint DEFAULT NULL COMMENT '证书类型(1=特种作业证书,2=从业资质,3=技能等级证书,4=安全培训合格证)', `cert_name` varchar(200) NOT NULL COMMENT '证书名称', `cert_no` varchar(100) DEFAULT NULL COMMENT '证书编号', `issue_org` varchar(200) DEFAULT NULL COMMENT '发证机构', `issue_date` date DEFAULT NULL COMMENT '发证日期', `expire_date` date NOT 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`), KEY `idx_employee_id` (`employee_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HRM 证书资质'; -- ---------- 2. 字典:证书资质类型 ---------- INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `deleted_time`) VALUES ('HRM 证书资质类型', 'hrm_certificate_type', 0, 'HRM 证书资质类型(1=特种作业证书,2=从业资质,3=技能等级证书,4=安全培训合格证)', '1', NOW(), '1', NOW(), b'0', NULL) ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1, '特种作业证书', '1', 'hrm_certificate_type', 0, 'danger', '', '特种作业操作证', '1', NOW(), '1', NOW(), b'0'), (2, '从业资质', '2', 'hrm_certificate_type', 0, 'warning', '', '从业资格', '1', NOW(), '1', NOW(), b'0'), (3, '技能等级证书', '3', 'hrm_certificate_type', 0, 'primary', '', '技能等级', '1', NOW(), '1', NOW(), b'0'), (4, '安全培训合格证', '4', 'hrm_certificate_type', 0, 'success', '', '安全培训合格', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `label` = VALUES(`label`), `sort` = VALUES(`sort`); -- ---------- 3. 菜单:证书资质(父菜单 6001 人力资源) ---------- 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 (1065240, '证书资质', '', 2, 99, 6001, 'certificate', 'ep:medal', 'hrm/certificate/index', 'HrmCertificate', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065241, '证书资质查询', 'hrm:certificate:query', 3, 1, 1065240, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065242, '证书资质创建', 'hrm:certificate:create', 3, 2, 1065240, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065243, '证书资质更新', 'hrm:certificate:update', 3, 3, 1065240, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065244, '证书资质删除', 'hrm:certificate:delete', 3, 4, 1065240, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); -- 角色授权(普通角色2 / 租户管理员109、111) INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `tenant_id`) SELECT r.role_id, m.menu_id, '1', NOW(), '1', NOW(), b'0', 1 FROM (SELECT 2 AS role_id UNION ALL SELECT 109 UNION ALL SELECT 111) r CROSS JOIN (SELECT 1065240 AS menu_id UNION ALL SELECT 1065241 UNION ALL SELECT 1065242 UNION ALL SELECT 1065243 UNION ALL SELECT 1065244) m LEFT JOIN `system_role_menu` existing ON existing.role_id = r.role_id AND existing.menu_id = m.menu_id AND existing.deleted = b'0' WHERE existing.id IS NULL;