server/distributed-print/install/mysql/upgrade/3.2.5.sql

USE teachcloud_db;

DROP TABLE IF EXISTS `basic_teach_course`;
CREATE TABLE `basic_teach_course` (
                                      `id` bigint NOT NULL COMMENT '主键',
                                      `school_id` bigint DEFAULT NULL COMMENT '学校id',
                                      `org_id` bigint DEFAULT NULL COMMENT '机构id',
                                      `user_id` bigint DEFAULT NULL COMMENT '用户ID',
                                      `course_id` bigint DEFAULT NULL COMMENT '课程ID',
                                      `enable` tinyint DEFAULT '1' COMMENT '是否启用？1-是 ；0-否',
                                      `create_id` bigint DEFAULT NULL COMMENT '创建人',
                                      `create_time` bigint DEFAULT NULL COMMENT '创建时间',
                                      `update_id` bigint DEFAULT NULL COMMENT '更新人',
                                      `update_time` bigint DEFAULT NULL COMMENT '更新时间',
                                      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='老师和课程关联表';

-- 按学校更新重复的准考证号。准考证号学校下唯一
UPDATE exam_student t
SET
    t.ticket_number = CONCAT(t.ticket_number,
                             ROUND(ROUND(RAND(), 4) * 10000))
WHERE
    EXISTS( SELECT
                temp.ticket_number
            FROM
                (SELECT
                     x.school_id, x.ticket_number
                 FROM
                     exam_student x
                 GROUP BY x.school_id , x.ticket_number
                 HAVING COUNT(x.ticket_number) > 1) temp
            WHERE
                    t.school_id = temp.school_id
              AND t.ticket_number = temp.ticket_number);

alter table exam_student add UNIQUE index `unique_ticket_number_idx`(`school_id`, `ticket_number`);

ALTER TABLE `exam_detail`
    ADD COLUMN `backup_count` DOUBLE NULL COMMENT '试卷、题卡备份数量（0-1为按比例，大于等于1为按数量）' AFTER `print_count`;

ALTER TABLE `exam_print_plan`
    CHANGE COLUMN `backup_count` `backup_count` DOUBLE NOT NULL COMMENT '试卷、题卡备份数量（0-1为按比例，大于等于1为按数量）' ;

ALTER TABLE `basic_print_config`
    CHANGE COLUMN `backup_count` `backup_count` DOUBLE NULL DEFAULT NULL COMMENT '试卷、题卡备份数量（0-1为按比例，大于等于1为按数量）' ;

ALTER TABLE `basic_card_rule`
    CHANGE COLUMN `objective_attention` `objective_attention` TEXT NULL COMMENT '客观题注意事项' ,
    CHANGE COLUMN `subjective_attention` `subjective_attention` TEXT NULL COMMENT '主观题注意事项' ;

ALTER TABLE `exam_detail` DROP COLUMN `print_count`;

CREATE TABLE `exam_detail_course_paper_type` (
                                                 `id` BIGINT(20) NOT NULL,
                                                 `exam_detail_course_id` BIGINT(20) NOT NULL,
                                                 `paper_type` VARCHAR(5) NOT NULL COMMENT '卷型',
                                                 PRIMARY KEY (`id`));
ALTER TABLE `exam_detail_course_paper_type`
    ADD INDEX `idx_exam_detail_course_id` (`exam_detail_course_id` ASC);

ALTER TABLE `exam_paper_structure`
    CHANGE COLUMN `objective_structure` `objective_structure` MEDIUMTEXT NULL DEFAULT NULL COMMENT '客观题试卷结构JSON' ,
    CHANGE COLUMN `subjective_structure` `subjective_structure` MEDIUMTEXT NULL DEFAULT NULL COMMENT '主观题试卷结构JSON' ,
    CHANGE COLUMN `paper_info_json` `paper_info_json` MEDIUMTEXT NULL DEFAULT NULL COMMENT '整个试卷信息json（前端提供的全部信息）' ;

ALTER TABLE basic_student MODIFY COLUMN student_code varchar(100) NOT NULL COMMENT '学生编号';
ALTER TABLE basic_student MODIFY COLUMN student_name varchar(100) NOT NULL COMMENT '学生姓名';
ALTER TABLE exam_student MODIFY COLUMN student_code varchar(100) NOT NULL COMMENT '考生代码';
ALTER TABLE exam_student MODIFY COLUMN student_name varchar(100) NOT NULL COMMENT '考生姓名';
ALTER TABLE sys_user MODIFY COLUMN code varchar(100) NULL COMMENT '工号';
ALTER TABLE basic_course MODIFY COLUMN code varchar(100) NOT NULL COMMENT '课程编码';
ALTER TABLE sys_user MODIFY COLUMN mobile_number varchar(30) NULL COMMENT '手机号';

ALTER TABLE `basic_template`
    ADD COLUMN `diallel` TINYINT(1) NULL DEFAULT '1' COMMENT '考生信息是否双列显示' AFTER `enable`,
    ADD COLUMN `text_desc` VARCHAR(500) NULL COMMENT '文字说明' AFTER `diallel`;

ALTER TABLE `exam_paper_structure`
    CHANGE COLUMN `paper_type` `paper_type` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '试卷类型' ;

ALTER TABLE `exam_paper_structure`
    CHANGE COLUMN `paper_number` `paper_number` VARCHAR(50) CHARACTER SET 'utf8mb4' NOT NULL COMMENT '试卷编号' ;

ALTER TABLE `exam_task_sync`
    ADD COLUMN `total_paper_type` VARCHAR(50) NULL COMMENT '所有卷型' AFTER `paper_type`;

ALTER TABLE `exam_task_sync` CHANGE COLUMN `paper_type` `paper_type` VARCHAR(50) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '试卷类型' ;
ALTER TABLE `basic_school`
    ADD COLUMN `init_password` VARCHAR(100) NULL COMMENT '该学校用户的初始密码，默认为12345678' AFTER `logo`;

INSERT INTO `sys_role_privilege` VALUES (506, 10, 576, 1);
INSERT INTO `sys_role_privilege` VALUES (507, 10, 833, 1);
INSERT INTO `sys_role_privilege` VALUES (508, 10, 834, 1);
INSERT INTO `sys_role_privilege` VALUES (509, 10, 835, 1);
INSERT INTO `sys_role_privilege` VALUES (510, 10, 836, 1);
INSERT INTO `sys_role_privilege` VALUES (511, 10, 837, 1);
INSERT INTO `sys_role_privilege` VALUES (512, 10, 838, 1);
INSERT INTO `sys_role_privilege` VALUES (513, 10, 839, 1);
INSERT INTO `sys_role_privilege` VALUES (514, 10, 840, 1);
INSERT INTO `sys_role_privilege` VALUES (515, 10, 841, 1);
INSERT INTO `sys_role_privilege` VALUES (516, 10, 842, 1);
INSERT INTO `sys_role_privilege` VALUES (517, 10, 650, 1);
INSERT INTO `sys_role_privilege` VALUES (518, 10, 843, 1);
INSERT INTO `sys_role_privilege` VALUES (519, 10, 844, 1);
INSERT INTO `sys_role_privilege` VALUES (520, 10, 77, 1);
INSERT INTO `sys_role_privilege` VALUES (521, 10, 845, 1);
INSERT INTO `sys_role_privilege` VALUES (522, 10, 846, 1);
INSERT INTO `sys_role_privilege` VALUES (523, 10, 847, 1);
INSERT INTO `sys_role_privilege` VALUES (524, 10, 848, 1);
INSERT INTO `sys_role_privilege` VALUES (525, 10, 849, 1);
INSERT INTO `sys_role_privilege` VALUES (526, 10, 850, 1);
INSERT INTO `sys_role_privilege` VALUES (527, 10, 851, 1);
INSERT INTO `sys_role_privilege` VALUES (528, 10, 852, 1);
INSERT INTO `sys_role_privilege` VALUES (529, 10, 853, 1);
INSERT INTO `sys_role_privilege` VALUES (530, 10, 866, 1);
INSERT INTO `sys_role_privilege` VALUES (531, 10, 830, 1);
INSERT INTO `sys_role_privilege` VALUES (532, 10, 831, 1);
INSERT INTO `sys_role_privilege` VALUES (533, 10, 575, 1);
INSERT INTO `sys_role_privilege` VALUES (534, 9, 867, 1);
INSERT INTO `sys_role_privilege` VALUES (535, 10, 832, 1);
INSERT INTO `sys_role_privilege` VALUES (536, 9, 868, 1);

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('867', '下载', 'Download', 'LINK', '624', '9', 'AUTH', '868', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('868', '下载', '/api/admin/exam/card/download_card', 'URL', '624', '6', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('869', '试卷题卡预览日志记录接口', '/api/admin/basic/operation_log/save_preview_log', 'URL', '149', '14', 'SYS', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('870', '结束', 'Finish', 'LINK', '43', '4', 'AUTH', '871', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('871', '结束', '/api/admin/exam/print/plan_finish', 'URL', '43', '7', 'AUTH', '1', '1', '1');

UPDATE `sys_privilege` SET `enable` = '0', `front_display` = '0' WHERE (`id` = '306');

update exam_detail ed set ed.backup_count = (select backup_count from exam_print_plan epp where ed.print_plan_id = epp.id) where ed.id > 0;

UPDATE `sys_privilege` SET `name` = '撤回提交' WHERE (`id` = '335');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('872', '批量撤回', 'BatchEnd', 'BUTTON', '47', '3', 'AUTH', '873', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('873', '批量撤回', '/api/admin/exam/print/task_batch_cancel', 'URL', '47', '4', 'AUTH', '1', '1', '1');

UPDATE `sys_privilege` SET `related` = '395,435,557,665,792,793' WHERE (`id` = '394');
UPDATE `sys_privilege` SET `related` = '395,435,557,665,792,793' WHERE (`id` = '397');

update exam_card t set t.card_rule_id = (select distinct bpc.card_rule_id from basic_print_config bpc join exam_task et on bpc.school_id = et.school_id and bpc.exam_id = et.exam_id
                                                                                             join exam_task_detail etd on et.id = etd.exam_task_id
                                         where FIND_IN_SET(t.id, REPLACE(REPLACE(substring_index(substring_index(CONVERT((etd.paper_attachment_ids ->>'$[*].cardId') USING utf8), ']', 1), '[',-1),' "','"'),'"','')) )
where t.type = 'CUSTOM' and t.card_rule_id is null and t.id > 0;

insert into exam_detail_course_paper_type select id + cast(FLOOR(RAND()*10000000000000) as signed), id, paper_type from exam_detail_course where paper_type is not null and id>0;

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('874', '共用接口-查询学院', '/api/admin/basic/condition/list_college', 'URL', '149', '14', 'SYS', '1', '1', '1');

UPDATE sys_config
SET config_value='UTF-8'
WHERE config_key='sys.txt.charset';

update `exam_task_sync` set total_paper_type = paper_type where total_paper_type is null;

update `basic_school` set init_password = 'MTIzNDU2Nzg=';