测评票脚本2023年10月27日

# 新表存图片
create table t_json_data_img
(
    id       varchar(64)  not null
        primary key,
    img_name varchar(100) null,
    img_data longblob     null,
    constraint t_json_data_img_pk2
        unique (id)
)

# 暂时不用
insert into t_json_data_img (select id, imgname, imgdata from t_json_data);

# 暂时不用
# update t_json_data set imgdata = '' where 1=1;

# 新增表结果汇总
create table e_jghz
(
    id          varchar(64)                         not null,
    indicator   varchar(100)                        null comment '指标',
    score       int                                 null comment '分数',
    batch_no    varchar(64)                         null comment '批次',
    org_id      varchar(64)                         null comment '单位ID',
    org_name    varchar(64)                         null comment '单位',
    ticket_name varchar(20)                         null comment '票名称',
    ticket_no   varchar(64)                         null comment '票号',
    ticket_type varchar(10)                         null comment '票类型',
    `key`       varchar(100)                        null comment '父节点',
    table_type  int                                 null comment '表类型',
    create_time timestamp default current_timestamp null comment '记录创建事件',
    constraint e_jghz_pk
        primary key (id)
);

# 新增索引
CREATE INDEX idx_batch_no_table_type_ticket_type ON e_jghz (batch_no, table_type, ticket_type);
CREATE INDEX idx_org_id ON e_jghz (org_id);

# 新增菜单 明细统计
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, url, target, menu_type, visible, is_refresh, perms,
                         icon, create_by, create_time, update_by, update_time, remark)
    VALUES (2270, '明细统计', 2113, 0, '/score/cptjfx/analyse', 'menuItem', 'C', '0', '1', '', '#', 'admin',
            '2023-10-29 11:00:16', 'admin', '2023-10-29 11:12:03', '');

# 汇总表
create table e_jghz_analyse
(
    id          varchar(64)  not null,
    org_id      varchar(64)  null comment '单位ID',
    org_name    varchar(100) null comment '单位名称',
    type        varchar(10)  null comment '表类型',
    result      json         null comment '统计结果',
    create_time timestamp    null comment '创建时间',
    constraint e_jghz_analyse_pk
        primary key (id)
)
    comment '汇总分析';

# 添加批次
alter table e_jghz_analyse
    add batch_no varchar(64) null comment '批次';

# 添加一级指标
alter table e_jghz_analyse
    add `key` varchar(64) null;

# 添加排名字段
alter table e_jghz_analyse
    add score varchar(20) null comment '排名';

# 汇总菜单
INSERT INTOsys_menu (menu_id, menu_name, parent_id, order_num, url, target, menu_type, visible, is_refresh, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES (2271, '汇总统计', 2113, 2, '/score/cptjfx/analyseResult', 'menuItem', 'C', '0', '1', '', '#', 'admin', '2023-11-02 21:21:19', 'admin', '2023-11-02 21:27:02', '');

# 配置
UPDATE sys_config SET config_name = '主框架页-默认皮肤样式名称', config_key = 'sys.index.skinName', config_value = 'skin-blue', config_type = 'Y', create_by = 'admin', create_time = '2021-08-07 11:23:43', update_by = '', update_time = null, remark = '蓝色 skin-blue、绿色 skin-green、紫色 skin-purple、红色 skin-red、黄色 skin-yellow' WHERE config_id = 1;
UPDATE sys_config SET config_name = '用户管理-账号初始密码', config_key = 'sys.user.initPassword', config_value = '123456', config_type = 'Y', create_by = 'admin', create_time = '2021-08-07 11:23:43', update_by = '', update_time = null, remark = '初始化密码 123456' WHERE config_id = 2;
UPDATE sys_config SET config_name = '主框架页-侧边栏主题', config_key = 'sys.index.sideTheme', config_value = 'theme-dark', config_type = 'Y', create_by = 'admin', create_time = '2021-08-07 11:23:43', update_by = '', update_time = null, remark = '深黑主题theme-dark，浅色主题theme-light，深蓝主题theme-blue' WHERE config_id = 3;
UPDATE sys_config SET config_name = '账号自助-是否开启用户注册功能', config_key = 'sys.account.registerUser', config_value = 'true', config_type = 'Y', create_by = 'admin', create_time = '2021-08-07 11:23:43', update_by = '', update_time = null, remark = '是否开启注册用户功能（true开启，false关闭）' WHERE config_id = 4;
UPDATE sys_config SET config_name = '用户管理-密码字符范围', config_key = 'sys.account.chrtype', config_value = '0', config_type = 'Y', create_by = 'admin', create_time = '2021-08-07 11:23:43', update_by = '', update_time = null, remark = '默认任意字符范围，0任意（密码可以输入任意字符），1数字（密码只能为0-9数字），2英文字母（密码只能为a-z和A-Z字母），3字母和数字（密码必须包含字母，数字）,4字母数字和特殊字符（目前支持的特殊字符包括：~!@#$%^&*()-=_+）' WHERE config_id = 5;
UPDATE sys_config SET config_name = '用户管理-初始密码修改策略', config_key = 'sys.account.initPasswordModify', config_value = '0', config_type = 'Y', create_by = 'admin', create_time = '2021-08-07 11:23:43', update_by = '', update_time = null, remark = '0：初始密码修改策略关闭，没有任何提示，1：提醒用户，如果未修改初始密码，则在登录时就会提醒修改密码对话框' WHERE config_id = 6;
UPDATE sys_config SET config_name = '用户管理-账号密码更新周期', config_key = 'sys.account.passwordValidateDays', config_value = '0', config_type = 'Y', create_by = 'admin', create_time = '2021-08-07 11:23:43', update_by = '', update_time = null, remark = '密码更新周期（填写数字，数据初始化值为0不限制，若修改必须为大于0小于365的正整数），如果超过这个周期登录系统时，则在登录时就会提醒修改密码对话框' WHERE config_id = 7;
UPDATE sys_config SET config_name = '主框架页-菜单导航显示风格', config_key = 'sys.index.menuStyle', config_value = 'default', config_type = 'Y', create_by = 'admin', create_time = '2021-08-07 11:23:43', update_by = '', update_time = null, remark = '菜单导航显示风格（default为左侧导航菜单，topnav为顶部导航菜单）' WHERE config_id = 8;
UPDATE sys_config SET config_name = '主框架页-是否开启页脚', config_key = 'sys.index.ignoreFooter', config_value = 'true', config_type = 'Y', create_by = 'admin', create_time = '2021-08-07 11:23:43', update_by = '', update_time = null, remark = '是否开启底部页脚显示（true显示，false隐藏）' WHERE config_id = 9;