/* 数据库更新的sql语句跟随git进行提交。 每次git更新之后，请务必仔细留意并阅读数据库更新脚本文档sqlManageUpdate.md */ /* --------------------------------------------------------------------- -- @author andy.wu -- @description 公共备注表(ahrs_public_notes)新增文件路径字段(attachment) -- @time 2023.2.20 16:07 -- @IndexNumber serial_1 对应sql文档的序号 --------------------------------------------------------------------- alter table ahrs_public_notes add attachment varchar(255) comment '附件路径'; */ /* --------------------------------------------------------------------- -- @author linian -- @description 修改招聘的学历、学位字典排序 -- @time 2023.2.22 10:02 -- @IndexNumber serial_2 --------------------------------------------------------------------- UPDATE sys_dict_data SET dict_sort = '1' WHERE dict_type = 'recruit_education' AND dict_value = '5'; UPDATE sys_dict_data SET dict_sort = '2' WHERE dict_type = 'recruit_education' AND dict_value = '4'; UPDATE sys_dict_data SET dict_sort = '3' WHERE dict_type = 'recruit_education' AND dict_value = '3'; UPDATE sys_dict_data SET dict_sort = '4' WHERE dict_type = 'recruit_education' AND dict_value = '2'; UPDATE sys_dict_data SET dict_sort = '5' WHERE dict_type = 'recruit_education' AND dict_value = '1'; UPDATE sys_dict_data SET dict_sort = '6' WHERE dict_type = 'recruit_education' AND dict_value = '6'; UPDATE sys_dict_data SET dict_sort = '7' WHERE dict_type = 'recruit_education' AND dict_value = '7'; UPDATE sys_dict_data SET dict_sort = '0' WHERE dict_type = 'recruit_degree' AND dict_value = '5'; UPDATE sys_dict_data SET dict_sort = '1' WHERE dict_type = 'recruit_degree' AND dict_value = '4'; UPDATE sys_dict_data SET dict_sort = '2' WHERE dict_type = 'recruit_degree' AND dict_value = '3'; UPDATE sys_dict_data SET dict_sort = '3' WHERE dict_type = 'recruit_degree' AND dict_value = '2'; UPDATE sys_dict_data SET dict_sort = '4' WHERE dict_type = 'recruit_degree' AND dict_value = '1'; UPDATE sys_dict_data SET dict_sort = '5' WHERE dict_type = 'recruit_degree' AND dict_value = '0'; */ /* ----------------------------------------------------------------------- -- @author andy.wu -- @description 调动信息表(ahrs_post_transfer)，新增人事综合管理部门意见(rszhgl_dept_comment)，新增调出单位意见(export_dept_comment)字段 -- @time 2023.2.24 16:07 -- @IndexNumber serial_3 ----------------------------------------------------------------------- */ alter table ahrs_post_transfer add rszhgl_dept_comment varchar(255) comment '人事综合管理部门意见'; alter table ahrs_post_transfer add export_dept_comment varchar(255) comment '调出单位意见'; /* ----------------------------------------------------------------------- -- @author andy.wu -- @description 创建消息通知表(sys_message_notice) 用于消息通知模块 -- @time 2023.2.28 16:20 -- @IndexNumber serial_4 ----------------------------------------------------------------------- */ create table sys_message_notice ( message_id bigint auto_increment comment '消息id' primary key, message_content varchar(255) null comment '消息内容', message_execution_time datetime null comment '执行时间', create_by varchar(255) default 'admin' null comment '创建人', status char(2) default '0' null comment '状态（0正常，1作废）' ) comment '消息通知表'; /* ----------------------------------------------------------------------- -- @author andy.wu -- @description 新增定时任务 ，每半小时查询一次数据库更新redis的消息有序列表 （已作废） -- @time 2023.3.5 12:34 -- @IndexNumber serial_5 ----------------------------------------------------------------------- */ # INSERT INTO `sys_job`(`job_name`, `job_group`, `invoke_target`, `cron_expression`, `misfire_policy`, `concurrent`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( '定时查询消息更新数据库', 'DEFAULT', 'MessageForRedisTask.messageForRedisTask()', '0 0/30 * * * ?', '3', '1', '0', 'admin', '2023-03-01 15:51:56', '', '2023-03-01 15:55:36', ''); /* ----------------------------------------------------------------------- -- @author linian -- @description 将角色名称中代理改为辅助 -- @time 2023.3.9 10:22 -- @IndexNumber serial_6 ----------------------------------------------------------------------- */ update sys_role set role_name = '人事综合管理部门终审领导辅助' where role_key = 'manageOperatorAgent'; update sys_role set role_name = '人事综合管理部门初审领导辅助' where role_key = 'managePriLeaderAgent'; update sys_role set role_name = '人事综合管理部门操作员辅助' where role_key = 'manageHigLeaderAgent'; /* ----------------------------------------------------------------------- -- @author linian -- @description 人员状态中主管部门取消，统一改为 事业单位到事业单位交流 -- @time 2023.3.9 10:22 -- @IndexNumber serial_7 ----------------------------------------------------------------------- */ UPDATE sys_dict_data SET dict_label = '事业单位到事业单位交流' WHERE dict_type = 'per_status' AND dict_value = '2301'; UPDATE sys_dict_data SET status = '1' WHERE dict_type = 'per_status' AND dict_value = '2304'; /* ----------------------------------------------------------------------- -- @author andy.wu -- @description 消息表新增执行结束时间 message_end_time,删除定时刷新redis的任务 -- @time 2023.3.10 9:26 -- @IndexNumber serial_8 ----------------------------------------------------------------------- */ alter table sys_message_notice add message_end_time datetime comment '执行结束时间'; delete from sys_job where job_name='定时查询消息更新数据库'; /* ----------------------------------------------------------------------- -- @author linian -- @description 创建短信记录表，用于记录保存每一次的短信内容 -- @time 2023.3.16 10:22 -- @IndexNumber serial_9 ----------------------------------------------------------------------- */ create table sys_msg_record ( id varchar(36) not null comment '短信记录id' primary key, tel varchar(16) null comment '发送号码', msg varchar(2048) null comment '短信内容', send_result varchar(128) null comment '发送返回结果', send_time datetime not null comment '发送时间', sender varchar(32) null comment '发送人', remark varchar(256) null comment '备注', version varchar(4) null comment '版本', create_by varchar(32) null comment '创建人', create_time datetime null comment '创建时间', update_by varchar(32) null comment '修改人', update_time datetime null comment '修改时间', del_flag varchar(2) null comment '删除标识 00 未删除 01 已删除' ) comment '短信记录表'; /* ----------------------------------------------------------------------- -- @author linian -- @description 添加短信日志菜单及子菜单 -- @time 2023.3.16 10:22 -- @IndexNumber serial_10 ----------------------------------------------------------------------- */ INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES (2306, '短信日志', 2053, 6, '/system/msgLog', 'menuItem', 'C', '0', 'system:msgLog:view', '#', 'admin', '2022-11-17 15:25:40', '', null, ''); INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES (2307, '日志查询', 2306, 1, '#', 'menuItem', 'F', '', 'system:msgLog:list', '#', 'admin', '2022-11-17 15:26:08', 'admin', '2022-11-17 15:26:39', ''); /* ----------------------------------------------------------------------- -- @author wengchengjian -- @description 将人员表的人员状态是不同主管单位间调用的改成单位间调动 -- @time 2023.3.17 9:13 serial_11 ----------------------------------------------------------------------- */ UPDATE ahrs_personnel SET personnel_status = '2301' WHERE personnel_status = '2304' /* ----------------------------------------------------------------------- -- @author linian -- @description 处分表添加处分截止时间 -- @time 2023.3.17 10:06 -- @IndexNumber serial_12 ----------------------------------------------------------------------- */ alter table ahrs_personnel_disposition add approval_end_time datetime null comment '处分截止时间' after approval_time; alter table ahrs_disposition add approval_end_time datetime null comment '处分截止时间' after approval_time; /* ----------------------------------------------------------------------- -- @author linian -- @description 修改添加处分字典值 -- @time 2023.3.17 14:26 -- @IndexNumber serial_13 ----------------------------------------------------------------------- */ -- 先删除原有的字典值 delete from sys_dict_data where dict_type = 'dis_kind'; -- 再添加新的字典值 INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20199, 1, '党的纪律处分', '1', null, 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:30:03', '', '2023-03-17 10:44:23', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20206, 1, '党内警告', '101', '1', 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:36:02', '', '2023-03-17 10:44:27', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20207, 2, '党内严重警告', '102', '1', 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:36:19', '', '2023-03-17 10:44:30', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20208, 3, '撤销党内职务', '103', '1', 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:36:37', '', '2023-03-17 10:44:33', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20209, 4, '留党察看', '104', '1', 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:36:59', '', '2023-03-17 10:44:41', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20211, 5, '开除党籍', '105', '1', 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:37:37', '', '2023-03-17 10:44:47', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20200, 2, '政务处分', '2', null, 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:31:43', '', '2023-03-17 10:44:50', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (1939, 1, '警告', '201', '2', 'dis_kind', '', '', 'N', '0', '', '2020-04-08 19:19:37', '', '2023-03-17 10:47:07', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20201, 2, '记过', '202', '2', 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:33:12', '', '2023-03-17 10:47:12', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20204, 3, '记大过', '203', '2', 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:35:21', '', '2023-03-17 10:47:27', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20202, 4, '降低岗位等级', '204', '2', 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:33:44', '', '2023-03-17 10:47:52', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20205, 5, '撤职', '205', '2', 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:35:34', '', '2023-03-17 10:52:10', null, ''); INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_parent_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark, ancestors) VALUES (20203, 6, '开除', '206', '2', 'dis_kind', null, null, 'N', '0', '', '2023-03-15 15:34:54', '', '2023-03-17 10:52:17', null, ''); -- 将处分表中的原有字典值变更 update ahrs_disposition set disposition_kind = '201' where disposition_kind = '01'; update ahrs_disposition set disposition_kind = '202' where disposition_kind = '02'; update ahrs_disposition set disposition_kind = '204' where disposition_kind = '03'; update ahrs_disposition set disposition_kind = '206' where disposition_kind = '0104'; /* ----------------------------------------------------------------------- -- @author linian -- @description 添加系统更新日志菜单及子菜单 -- @time 2023.3.17 16:37 -- @IndexNumber serial_14 ----------------------------------------------------------------------- */ INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES (2301, '系统更新日志', 2053, 5, '/ahrs/sysLog', 'menuItem', 'C', '0', 'ahrs:sysLog:view', '#', 'admin', '2022-11-07 16:58:16', '', null, ''); INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES (2302, '日志查询', 2301, 1, '#', 'menuItem', 'F', '', 'ahrs:sysLog:list', '#', 'admin', '2022-11-07 16:59:13', '', null, ''); INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES (2303, '日志新增', 2301, 2, '#', 'menuItem', 'F', '', 'ahrs:sysLog:add', '#', 'admin', '2022-11-07 16:59:53', '', null, ''); INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES (2304, '日志删除', 2301, 3, '#', 'menuItem', 'F', '', 'ahrs:sysLog:remove', '#', 'admin', '2022-11-07 17:00:46', '', null, ''); INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES (2305, '日志修改', 2301, 4, '#', 'menuItem', 'F', '', 'ahrs:userRole:edit', '#', 'admin', '2022-11-07 17:01:08', '', null, ''); /* ----------------------------------------------------------------------- -- @author linian -- @description 创建系统更新日志表，用于保存系统更新日志的内容 -- @time 2023.3.17 16:37 -- @IndexNumber serial_15 ----------------------------------------------------------------------- */ create table sys_log ( log_id varchar(32) not null primary key, log_code varchar(32) not null comment '日志编码', log_title varchar(512) null comment '日志标题', log_content blob not null comment '日志内容', create_time datetime not null comment '创建时间', creater varchar(32) null comment '创建人', update_time datetime not null comment '更新时间', updater varchar(32) null comment '更新人员', delete_flag varchar(2) not null comment '删除标识 00未删除 01已删除' ) comment '系统日志'; /* ----------------------------------------------------------------------- -- @author andy.wu -- @description 消息表新增删除标志 del_flag -- @time 2023.3.17 16:51 -- @IndexNumber serial_16 ----------------------------------------------------------------------- */ alter table sys_message_notice add del_flag char(2) comment '删除标志'; /* ----------------------------------------------------------------------- -- @author linian -- @description 招聘计划变动和招聘计划子表添加年龄条件字段，用于灵活判断招聘人员年龄是否超标 -- @time 2023.3.23 09:28 -- @IndexNumber serial_17 ----------------------------------------------------------------------- */ alter table ahrs_recruit_plan_change add age_condition varchar(16) null comment '年龄条件' after age; alter table ahrs_recruit_plan_child add age_condition varchar(16) null comment '年龄条件' after age; /* ----------------------------------------------------------------------- -- @author ding -- @description 新增人员备注表 -- @time 2023.3.23 09:35 -- @IndexNumber serial_18 ----------------------------------------------------------------------- */ CREATE TABLE `ahrs_personnel_remark` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '备注id', `remark_dept_id` int NULL COMMENT '被备注单位id', `remark_dept_name` varchar(255) NULL COMMENT '被备注单位名称', `remark_idcard` varchar(255) NULL COMMENT '被备注的人', `remark_name` varchar(255) NULL COMMENT '被备注人名', `user_name` varchar(255) NULL COMMENT '备注人', `dept_name` varchar(255) NULL COMMENT '备注单位名称', `dept_id` int NULL COMMENT '备注单位id', `title` varchar(500) NULL COMMENT '备注内容', `attachment` varchar(255) NULL COMMENT '附件', `del_flag` varchar(255) NULL DEFAULT 0 COMMENT '是否删除', `create_by` varchar(255) NULL COMMENT '创建人', `create_time` datetime NULL COMMENT '创建时间', `update_by` varchar(255) NULL COMMENT '修改人', `update_time` datetime NULL COMMENT '修改时间', PRIMARY KEY (`id`) ) COMMENT = '人员备注表'; /* ----------------------------------------------------------------------- -- @author ding -- @description 人员备注菜单添加和权限配置 -- @time 2023.3.23 09:35 -- @IndexNumber serial_20 ----------------------------------------------------------------------- */ INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `url`, `target`, `menu_type`, `visible`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2310, '人员备注控制', 2308, 2, '#', 'menuItem', 'F', '', 'ahrs:personnleRemark:viewTwo', '#', 'admin', '2023-03-24 10:46:31', 'admin', '2023-03-24 10:48:47', ''); INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `url`, `target`, `menu_type`, `visible`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2309, '人员备注新增', 2308, 0, '#', 'menuItem', 'F', '', 'ahrs:personnleRemark:add', '#', 'admin', '2023-03-23 13:51:43', '', NULL, ''); INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `url`, `target`, `menu_type`, `visible`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2308, '人员备注', 2077, 1, '#', 'menuItem', 'C', '', 'ahrs:personnleRemark:view', '#', 'admin', '2023-03-23 09:45:00', 'admin', '2023-03-23 10:18:45', ''); /*操作员权限配置*/ INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (104, 2308); INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (104, 2309); INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (104, 2310); /*初审权限配置*/ INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (105, 2308); INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (105, 2309); INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (105, 2310); /*终审权限配置*/ INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (106, 2308); INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (106, 2309); INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (106, 2310); /* ----------------------------------------------------------------------- -- @author wengchengjian -- @description 岗位变更表增加'确认函中修改后的备注'字段 -- @time 2023.3.24 17:00 -- @IndexNumber serial_19 ----------------------------------------------------------------------- */ ALTER TABLE `ahrs_post_change` ADD COLUMN `confirmation_note` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '确认函中修改后的备注' AFTER `org_staff_grades`; /* ----------------------------------------------------------------------- -- @author wengchengjian -- @description 人员备注 显示已删除记录按钮 和 删除按钮 菜单添加 和 权限控制（仅操作员有这俩个权限） -- @time 2023.3.24 17:00 -- @IndexNumber serial_20 ----------------------------------------------------------------------- */ INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `url`, `target`, `menu_type`, `visible`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2312, '显示全部记录（包括删除）', 2308, 4, '#', 'menuItem', 'F', '', 'ahrs:personnleRemark:all', '#', 'admin', '2023-03-29 10:29:04', '', NULL, ''); INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `url`, `target`, `menu_type`, `visible`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2311, '人员备注删除', 2308, 3, '#', 'menuItem', 'F', '', 'ahrs:personnleRemark:remove', '#', 'admin', '2023-03-29 10:09:01', '', NULL, ''); INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (104, 2311); INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (104, 2312); /* ----------------------------------------------------------------------- -- @author andi.wu -- @description 用户表新增人员附件字段attachment -- @time 2023.03.30 -- @IndexNumber serial_21 ----------------------------------------------------------------------- */ alter table sys_user add attachment varchar(255) default '' comment '用户附件' after remark; /* ----------------------------------------------------------------------- -- @author andi.wu -- @description 用户信息修改记录表新增附件字段 -- @time 2023.03.30 -- @IndexNumber serial_22 ----------------------------------------------------------------------- */ alter table sys_user_change_log add attachment varchar(255) default '' comment '用户附件' after remark;