AHRS/sql/sqlManage/sqlManage.sql

/*
 数据库更新的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_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES ( '短信日志', 2053, 6, '/system/msgLog', 'menuItem', 'C', '0', 'system:msgLog:view', '#', 'admin', '2022-11-17 15:25:40', '', null, '');
INSERT INTO sys_menu ( menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES ( '日志查询', 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_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_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 ( 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_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 ( 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_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 ( 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_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 ( 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_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 ( 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_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 ( 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_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 ( 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_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 (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_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 ( 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_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 ( 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_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 ( 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_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 ( 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_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 ( 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
  -----------------------------------------------------------------------
 */
/*
 *先执行第一句然后把第一句的ID放在后面四句的上级菜单ID
 */

INSERT INTO sys_menu ( menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES ( '系统更新日志', 2053, 5, '/ahrs/sysLog', 'menuItem', 'C', '0', 'ahrs:sysLog:view', '#', 'admin', '2022-11-07 16:58:16', '', null, '');

select * from sys_menu where menu_name='系统更新日志';

INSERT INTO sys_menu ( menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES ( '日志查询', 2310, 1, '#', 'menuItem', 'F', '', 'ahrs:sysLog:list', '#', 'admin', '2022-11-07 16:59:13', '', null, '');
INSERT INTO sys_menu ( menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES ( '日志新增', 2310, 2, '#', 'menuItem', 'F', '', 'ahrs:sysLog:add', '#', 'admin', '2022-11-07 16:59:53', '', null, '');
INSERT INTO sys_menu ( menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES ( '日志删除', 2310, 3, '#', 'menuItem', 'F', '', 'ahrs:sysLog:remove', '#', 'admin', '2022-11-07 17:00:46', '', null, '');
INSERT INTO sys_menu ( menu_name, parent_id, order_num, url, target, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES ( '日志修改', 2310, 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 DEFAULT '00' 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) DEFAULT '0' 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_19
  -----------------------------------------------------------------------
 */
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_20
  -----------------------------------------------------------------------
 */
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;


/*
   -----------------------------------------------------------------------
  -- @author linian
  -- @description  备注，字数不足200的放大到200字
  -- @time 2023.4.12
  -- @IndexNumber serial_23
  -----------------------------------------------------------------------
 */
alter table ahrs_recruit_plan_child modify remark varchar(1024) null comment '备注';
alter table ahrs_post_transfer modify remark varchar(1024) default '' null comment '备注';
alter table ahrs_recruit_plan_change modify remark varchar(1024) null comment '备注';
alter table ahrs_review_apply modify remark varchar(1024) default '' null comment '备注';
alter table ahrs_review_main modify remark varchar(1024) null comment '备注';
alter table ahrs_review_personnel modify remark varchar(1024) default '' null comment '备注';
alter table ahrs_second_post_change modify remark varchar(1024) null comment '备注';
alter table ahrs_second_post_lsb modify remark varchar(1024) default '' null comment '备注';
alter table sys_dict_data modify remark varchar(1024) null comment '备注';

/*
   -----------------------------------------------------------------------
  -- @author wengchengjian
  -- @description  修改用户表的初始化审批权限
  -- @time 2023.04.12
  -- @IndexNumber serial_24
  -----------------------------------------------------------------------
 */
UPDATE sys_user SET initialize = '' WHERE initialize IS NULL;


/*
   -----------------------------------------------------------------------
  -- @author ding
  -- @description  岗位变动排序字段默认值设置为0 处理原本为null的数据
  -- @time 2023.4.12
  -- @IndexNumber serial_25
  -----------------------------------------------------------------------
 */
ALTER TABLE `ahrs_post_change` MODIFY COLUMN `sort` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 0 COMMENT '排序' AFTER `user_status`;
ALTER TABLE `ahrs_personnel_change` MODIFY COLUMN `sort` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 0 COMMENT '排序' AFTER `name`;
UPDATE ahrs_personnel_change set sort = 0 WHERE sort is NULL or sort = '';
UPDATE ahrs_post_change set sort = 0 WHERE sort is NULL or sort = '';




/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  所有事业单位初始化状态关闭
  -- @time 2023.4.18
  -- @IndexNumber serial_26
  -----------------------------------------------------------------------
 */
 update sys_dept set initialized_permissions='0' where dept_category='3';


/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  消息表增加显示位置字段
  -- @time 2023.5.4
  -- @IndexNumber serial_27
  -----------------------------------------------------------------------
 */
alter table sys_message_notice
    add type_flag char(2) default '0' comment '显示标志（目前支持在首页和登录页显示）';


/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  文件排序字段设置为int类型
  -- @time 2023.5.12
  -- @IndexNumber serial_28
  -----------------------------------------------------------------------
 */
alter table ahrs_file
    modify sort int(11) null comment '排序字段';

/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  用户表附件字段为null的都改为''
  -- @time 2023.6.1
  -- @IndexNumber serial_29
  -----------------------------------------------------------------------
 */

update sys_user set attachment='' where attachment is null;
update sys_user_change_log set attachment='' where attachment is null;



/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  申报表新增备注字段
  -- @time 2023.6.14
  -- @IndexNumber serial_30
  -----------------------------------------------------------------------
 */
alter table ahrs_declaration
    add comment varchar(1000) default '' comment '备注信息';



/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  新增用户申请表
  -- @time 2023.6.19
  -- @IndexNumber serial_31
  -----------------------------------------------------------------------
 */
create table ahrs_user_apply
(
    apply_id               bigint auto_increment comment '申请id'
        primary key,
    apply_dept_id          bigint              null comment '申请单位id',
    apply_dept_name        varchar(255)        null comment '申请单位名',
    apply_user_name        varchar(255)        null comment '申请人姓名',
    user_name              varchar(255)        null comment '用户名',
    simple_name            varchar(255)        null comment '用户简称',
    phonenumber            varchar(255)        null comment '手机号',
    password               varchar(255)        null comment '密码',
    key1                   varchar(255)        null comment '密钥',
    idcard                 varchar(255)        null comment '身份证号',
    email                  varchar(255)        null comment '邮箱',
    landline               varchar(255)        null comment '座机号码',
    apply_target_dept_name varchar(255)        null comment '目标单位名',
    attachment             varchar(255)        null comment '附件',
    remark                 varchar(255)        null comment '备注',
    apply_content          char(2) default '0' null comment '申请内容(0新建用户 1启用用户 2作废用户)',
    del_flag               char(2) default '0' null comment '删除标志(0未删除 1已删除)',
    apply_time             datetime            null comment '申请时间',
    apply_status           char(2) default '0' null comment '审批情况(0同意 1拒绝 2忽略 )',
    salt                   varchar(255)        null comment '随机盐',
    role_ids               bigint              null comment '角色id',
    recipient_id           bigint              null comment '接收人id',
    login_name             varchar(255)        null comment '登录名',
    apply_target_dept_id   bigint              null comment '目标单位id',
    status                 char(2) default '0' null comment '状态',
    user_id                bigint              null comment '用户id'
)
    comment '用户申请表' collate = utf8_bin;



/*
   -----------------------------------------------------------------------
  -- @author zeyuan
  -- @description  act_hi_comment流程意见表添加 节点是否通过记录
  -- @time 2023.6.27
  -- @IndexNumber serial_32
  -----------------------------------------------------------------------
 */
ALTER TABLE `act_hi_comment`
    ADD COLUMN `OPINION` varchar(50) NULL COMMENT '本次节点是否通过' AFTER `FULL_MSG_`;


/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  用户表增加申请字段
  -- @time 2023.6.28
  -- @IndexNumber serial_33
  -----------------------------------------------------------------------
 */

alter table sys_user
    add approve_status char(2) default '1' comment '审批用户标识(0同意 1不同意 默认为1)';

/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  文件表新增显示标识字段
  -- @time 2023.8.31
  -- @IndexNumber serial_34
  -----------------------------------------------------------------------
 */

 alter table ahrs_file
    add show_flag char(2) not null default '0' comment '显示标识(主要用于上级指定文件给下级看)(省级为1,市级为2，默认为0)';
# 创建两个联合索引
create index ahrs_file_union_class
    on ahrs_file (classification, dept_id, del_flag, status);
create index show_del_status_dept_id_
    on ahrs_file (show_flag, del_flag, status, dept_id);




/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  新增港澳台字典数据，修改人员表字段foreigner的字节数为3
  -- @time 2023.9.5
  -- @IndexNumber serial_35
  -----------------------------------------------------------------------
 */
insert into sys_dict_type( dict_name, dict_type, status, create_by, create_time )
values( '港澳台同胞类型', 'hongkong_macao_taiwan_type', '0', 'admin', sysdate());
insert into sys_dict_data ( dict_sort, dict_label, dict_value, dict_type, list_class, is_default, status, create_time )
values ( 1, '否', '000', 'hongkong_macao_taiwan_type', 'default', 'Y', '0', sysdate() );
insert into sys_dict_data ( dict_sort, dict_label, dict_value, dict_type, list_class, is_default, status, create_time )
values ( 2, '港澳居民', '001', 'hongkong_macao_taiwan_type', 'default', 'Y', '0', sysdate() );
insert into sys_dict_data ( dict_sort, dict_label, dict_value, dict_type, list_class, is_default, status, create_time )
values ( 3, '台湾同胞', '002', 'hongkong_macao_taiwan_type', 'default', 'Y', '0', sysdate() );
insert into sys_dict_data ( dict_sort, dict_label, dict_value, dict_type, list_class, is_default, status, create_time )
values ( 4, '外籍人士', '003', 'hongkong_macao_taiwan_type', 'default', 'Y', '0', sysdate() ) ;
insert into sys_dict_data ( dict_sort, dict_label, dict_value, dict_type, list_class, is_default, status, create_time )
values ( 5, '留学归国', '004', 'hongkong_macao_taiwan_type', 'default', 'Y', '0', sysdate() );


alter table ahrs_personnel
    modify foreigner char(3) default '000' null comment '是否港澳台及外籍人士';
alter table ahrs_personnel_now
    modify foreigner char(3) default '' null comment '是否港澳台及外籍人士';
alter table ahrs_personnel_lsb
    modify foreigner char(3) default '' null comment '是否港澳台及外籍人士';



/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  人员历史变更表新增操作类型字段
  -- @time 2023.9.11
  -- @IndexNumber serial_36
  -----------------------------------------------------------------------
 */
alter table sys_user_change_log
    add operate_type char(2) default '' not null comment '操作类型';



/*
   -----------------------------------------------------------------------
  -- @author andi.wu
  -- @description  岗位变动表状态字段备注修改
  -- @time 2023.11.13
  -- @IndexNumber serial_37
  -----------------------------------------------------------------------
 */
alter table ahrs_post_transfer
    modify status varchar(10) default '0' null comment '调动信息状态（0-未提交，1-已提交，2-同意，3-不同意，4-暂置）';


/*
   -----------------------------------------------------------------------
  -- @author wengchengjian
  -- @description  招聘人员的学位字段改成手写的
  -- @time 2023.12.15
  -- @IndexNumber serial_38
  -----------------------------------------------------------------------
 */
ALTER TABLE `ahrs_recruit_personnel_lsb`
    MODIFY COLUMN `degree` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '学位' AFTER `education_certificate`;

ALTER TABLE `ahrs_recruit_personnel_change`
    MODIFY COLUMN `degree` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '学位' AFTER `education_certificate`;

ALTER TABLE `ahrs_recruit_personnel`
    MODIFY COLUMN `degree` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '学位' AFTER `education_certificate`;

UPDATE ahrs_recruit_personnel_lsb set degree = CONCAT('{"tree":"',degree,'"}');
UPDATE ahrs_recruit_personnel_change set degree = CONCAT('{"tree":"',degree,'"}');
UPDATE ahrs_recruit_personnel set degree = CONCAT('{"tree":"',degree,'"}');

/*
   -----------------------------------------------------------------------
  -- @author wengchengjian
  -- @description  人员的是否港澳台的默认值修改
  -- @time 2024.1.30
  -- @IndexNumber serial_39
  -----------------------------------------------------------------------
 */
ALTER TABLE `ahrs_personnel`
    MODIFY COLUMN `foreigner` char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '是否港澳台及外籍人士' AFTER `personnel_status`;


/*
   -----------------------------------------------------------------------
  -- @author wengchengjian
  -- @description  招聘结果修改字典和增加职称字段
  -- @time 2024.2.1
  -- @IndexNumber serial_40
  -----------------------------------------------------------------------
 */
ALTER TABLE `ahrs_recruit_personnel_lsb`
    ADD COLUMN `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '职称' AFTER `letter`;
ALTER TABLE `ahrs_recruit_personnel_change`
    ADD COLUMN `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '职称' AFTER `back_by`;
ALTER TABLE `ahrs_recruit_personnel`
    ADD COLUMN `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '职称' AFTER `status`;
INSERT INTO `sys_dict_data`(`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 (0, '博士研究生', '0', NULL, 'recruit_per_education', NULL, NULL, 'N', '0', '', '2022-10-26 19:31:45', 'admin', '2022-10-26 19:33:48', NULL, '');
UPDATE sys_dict_data set dict_label = '硕士研究生' WHERE dict_code = 17234;