paper-library-service/paper-library/install/mysql/upgrade/1.0.5.sql

USE paper_library_db;

CREATE TABLE `basic_file_type`  (
        `id` bigint(0) NOT NULL,
        `school_id` bigint(0) NOT NULL COMMENT '学校ID',
        `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '文件类型名称',
        `type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '类别：系统，自定义',
        `create_id` bigint(0) NULL DEFAULT NULL COMMENT '创建人',
        `create_time` bigint(0) NULL DEFAULT NULL COMMENT '创建时间',
        `update_id` bigint(0) NULL DEFAULT NULL COMMENT '更新人',
        `update_time` bigint(0) NULL DEFAULT NULL COMMENT '更新时间',
        PRIMARY KEY (`id`) USING BTREE,
        UNIQUE INDEX `UNIQUE_SCHOOL_ID_NAME`(`school_id`, `name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '文件类型' ROW_FORMAT = Dynamic;

CREATE TABLE `basic_field` (
       `id` BIGINT(20) NOT NULL COMMENT '主键',
       `school_id` BIGINT(20) NOT NULL COMMENT '学校ID',
       `code` VARCHAR(45) NOT NULL COMMENT '字段代码',
       `name` VARCHAR(45) NOT NULL COMMENT '字段名称',
       `enable` TINYINT(1) NOT NULL COMMENT '是否选中',
       `disabled` TINYINT(1) NOT NULL COMMENT '是否禁用',
       `basic_field` TINYINT(1) NOT NULL COMMENT '是否基础字段',
       `create_id` BIGINT(20) NULL,
       `create_time` BIGINT(20) NULL,
       `update_id` BIGINT(20) NULL,
       `update_time` BIGINT(20) NULL,
       PRIMARY KEY (`id`))
    COMMENT = '字段管理表';

CREATE TABLE `basic_batch_number` (
      `exam_id` BIGINT(20) NOT NULL COMMENT '考试ID',
      `user_id` BIGINT(20) NOT NULL COMMENT '用户ID',
      `alphabet` VARCHAR(4) NULL COMMENT '字母（前缀）',
      `sequence` INT NULL COMMENT '序号',
      PRIMARY KEY (`exam_id`, `user_id`));

ALTER TABLE `exam_student` ADD COLUMN file_path varchar(255) NULL COMMENT '生成pdf的文件路径' AFTER `update_time`;
ALTER TABLE `exam_student` ADD COLUMN `extend_fields` MEDIUMTEXT NULL COMMENT '扩展字段' AFTER `remark`;
ALTER TABLE `paper_library` ADD COLUMN `batch_no` varchar(20) NULL COMMENT '批次号' AFTER `recognize_result`;
ALTER TABLE `paper_library_other` ADD COLUMN `file_type_id` bigint NULL COMMENT '文件类型ID' AFTER `update_time`;
ALTER TABLE `t_b_task` CHANGE COLUMN `org_id` `exam_id` BIGINT NULL DEFAULT NULL COMMENT '考试ID' ;
ALTER TABLE `t_b_task` CHANGE COLUMN `txt_file_path` `error_file_path` VARCHAR(500) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '错误文件路径' ;

ALTER TABLE `exam_course` DROP INDEX `idx_1`;
ALTER TABLE `paper_library` ADD INDEX `idx_3` (`create_id` ASC);
ALTER TABLE `paper_library_other` DROP INDEX `idx_1` ,ADD INDEX `idx_1` (`paper_scan_task_id` ASC, `file_type_id` ASC);

INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (560, '文件类型管理', 'FileTypeManage', 'MENU', 8, 6, NULL, NULL, 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (561, '新建', 'Add', 'BUTTON', 560, 1, 'AUTH', '565', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (562, '删除', 'Delete', 'LINK', 560, 1, 'AUTH', '566', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (563, '列表', 'List', 'LIST', 560, 1, 'AUTH', '564', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (564, '查询', '/api/admin/basic/filetype/page', 'URL', 560, 1, 'AUTH', NULL, 1, 1, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (565, '新增', '/api/admin/basic/filetype/save', 'URL', 560, 2, 'AUTH', NULL, 1, 1, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (566, '删除', '/api/admin/basic/filetype/delete', 'URL', 560, 3, 'AUTH', NULL, 1, 1, 1);

INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (570, '扫描统计管理', 'ScanStatManage', 'MENU', 8, 7, NULL, NULL, 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (571, '列表', 'List', 'LIST', 570, 1, 'AUTH', '573', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (572, '导出', 'Add', 'BUTTON', 570, 1, 'AUTH', '574', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (573, '查询', '/api/admin/basic/scan/stat/page', 'URL', 570, 1, 'AUTH', NULL, 1, 1, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (574, '导出', '/api/admin/basic/scan/stat/export', 'URL', 570, 2, 'AUTH', NULL, 1, 1, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (575, '查询条件', 'Condition', 'CONDITION', 570, 2, 'AUTH', '573', '1', '0', '1');
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (576, '查询', 'Select', 'BUTTON', 570, 3, 'AUTH', '573', '1', '0', '1');


INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (280, '图片管理', 'PictureManage', 'MENU', 18, 1, NULL, NULL, 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (281, '查询条件', 'Condition', 'CONDITION', 280, 1, 'AUTH', '286', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (282, '列表', 'List', 'LIST', 280, 2, 'AUTH', '286', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (283, '查询', 'Select', 'BUTTON', 280, 3, 'AUTH', '286', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (284, '下载图片', 'DownloadPicture', 'BUTTON', 280, 4, 'AUTH', '287', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (285, '查看详情', 'PictureDetail', 'LINK', 280, 5, 'AUTH', '288,289', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (286, '查询接口', '/api/admin/paper/picture/page', 'URL', 280, 1, 'AUTH', NULL, 1, 1, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (287, '下载图片', '/api/admin/paper/picture/download', 'URL', 280, 1, 'AUTH', NULL, 1, 1, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (288, '查看图片', '/api/admin/paper/picture/list_student_picture', 'URL', 280, 1, 'AUTH', NULL, 1, 1, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (289, '保存图片', '/api/admin/paper/picture/save', 'URL', 280, 1, 'AUTH', NULL, 1, 1, 1);

INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (279, 'pdf生成', 'Pdf', 'BUTTON', 260, 6, 'AUTH', '290', 1, 0, 1);
INSERT INTO `sys_privilege`(`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES (290, 'pdf图片生成接口', '/api/admin/paper/document/pdf/generate', 'URL', 260, 11, 'AUTH', NULL, 1, 1, 1);

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `enable`, `default_auth`, `front_display`) VALUES ('273', '字段管理', 'FieldManage', 'MENU', '8', '2', '1', '0', '1');
UPDATE `sys_privilege` SET `sequence` = '4' WHERE (`id` = '510');
UPDATE `sys_privilege` SET `sequence` = '5' WHERE (`id` = '9');
UPDATE `sys_privilege` SET `sequence` = '6' WHERE (`id` = '20');
UPDATE `sys_privilege` SET `sequence` = '10' WHERE (`id` = '549');

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('274', '查询', '/api/admin/basic/field/list', 'URL', '273', '1', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('275', '保存', '/api/admin/basic/field/save', 'URL', '273', '2', 'AUTH', '1', '1', '1');
UPDATE `sys_privilege` SET `related` = '274,275' WHERE (`id` = '273');

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('33', '模板下载', '/api/admin/exam/student/download_template', 'URL', '20', '5', 'AUTH', '1', '1', '1');
UPDATE `sys_privilege` SET `related` = '32,33' WHERE (`id` = '26');

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('567', '查询列表', '/api/admin/basic/filetype/list', 'URL', '560', '4', 'AUTH', '1', '1', '1');

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('205', '根据学号获取考生信息', '/api/admin/client/student/get', 'URL', '200', '5','AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('206', '获取考场/班级', '/api/admin/client/classroom/get', 'URL', '200', '6', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('207', '获取批次号', '/api/admin/client/batch_no/get', 'URL', '200', '7', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('208', '新增学生', '/api/admin/client/student/save', 'URL', '200', '8', 'AUTH', '1', '1', '1');
UPDATE `sys_privilege` SET `related` = '30,202,203,204,205,206,207,208,274,565,567' WHERE (`id` = '200');

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('276', '删除图片', '/api/admin/paper/document/picture/delete', 'URL', '260', '8', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('277', '旋转保存图片', '/api/admin/paper/document/picture/save', 'URL', '260', '9', 'AUTH', '1', '1', '1');
UPDATE `sys_privilege` SET `related` = '267,268,269,270,271,276,277' WHERE (`id` = '264');

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('278', '汇总统计', '/api/admin/paper/document/stat', 'URL', '260', '10', 'AUTH', '1', '1', '1');
UPDATE `sys_privilege` SET `related` = '266,278' WHERE (`id` = '261');
UPDATE `sys_privilege` SET `related` = '266,278' WHERE (`id` = '262');
UPDATE `sys_privilege` SET `related` = '266,278' WHERE (`id` = '263');

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('253', '清除学生数据', 'ClearStudentScanData', 'LINK', '244', '6', 'AUTH', '254', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('254', '清除学生数据接口', '/api/admin/paper/scan_task/clear_single_data', 'URL', '244', '4', 'AUTH', '1', '1', '1');

UPDATE `sys_privilege` SET `enable` = '0' WHERE (`id` = '12');
UPDATE `sys_privilege` SET `enable` = '0' WHERE (`id` = '512');

ALTER TABLE `paper_library` ADD COLUMN `school_id` bigint NULL COMMENT '学校ID' AFTER `batch_no`;
ALTER TABLE `paper_library` ADD COLUMN `create_date` varchar(10) NULL COMMENT '创建日期' AFTER `create_time`;
ALTER TABLE `paper_library` DROP INDEX `idx_3`, ADD INDEX `idx_3`(`school_id`,`create_id` , `create_date`) USING BTREE;

UPDATE paper_library p,paper_scan_task t SET p.school_id=t.school_id WHERE p.paper_scan_task_id=t.id;
UPDATE paper_library SET create_date=date_format( FROM_UNIXTIME( create_time / 1000 ), '%Y-%m-%d');

ALTER TABLE `paper_library` ADD INDEX `idx_4` (`create_id` ASC) ,ADD INDEX `idx_5` (`create_date` ASC) ;