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

USE teachcloud_db;

ALTER TABLE `exam_student` ADD COLUMN `student_id` BIGINT(20) NULL COMMENT '考生ID（basic_exam_student表ID）' AFTER `exam_detail_course_id`;
ALTER TABLE `mark_student`
    ADD COLUMN `student_id` BIGINT(20) NULL COMMENT '考生ID（basic_exam_student表ID）' AFTER `exam_id`,
ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `student_id`;
ALTER TABLE `basic_exam_student` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `exam_card` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `org_id`;
ALTER TABLE `exam_detail_course` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_detail_id`;
ALTER TABLE `exam_task` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `org_id`;
ALTER TABLE `mark_document` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `mark_group` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `mark_paper` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `mark_task` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `teach_course` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `t_p_course` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `professional_id`;
ALTER TABLE `t_b_sync_task` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `client_status` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `print_plan_id`;


ALTER TABLE `basic_course` CHANGE COLUMN `org_id` `org_id` BIGINT NULL COMMENT '机构id' ;
ALTER TABLE `basic_course` DROP INDEX `course_schoolId_code_idx` , ADD UNIQUE INDEX `uq_1` USING BTREE (`school_id`, `teaching_room_id`, `code`);
ALTER TABLE `teach_course` CHANGE COLUMN `course_code` `course_code` VARCHAR(20) NULL COMMENT '课程代码' ;

ALTER TABLE `mark_paper_package`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '课程代码' ,
CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '科目名称' ;


ALTER TABLE `course_dimension`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ;

ALTER TABLE `course_evaluation`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ;

ALTER TABLE `course_target`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ;

ALTER TABLE `course_weight`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ;

ALTER TABLE `exam_task_temp`
    ADD COLUMN `teaching_room_name` VARCHAR(100) NULL COMMENT '开课学院' AFTER `course_name`;

ALTER TABLE `exam_task`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) NULL COMMENT '课程代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(50) NULL COMMENT '课程名称' ;

ALTER TABLE `basic_teach_clazz`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;

ALTER TABLE `basic_teach_clazz`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ;

ALTER TABLE `basic_exam_student`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ,
    CHANGE COLUMN `clazz_id` `clazz_id` BIGINT NULL COMMENT '教学班id（basic_teach_clazz）' ;

ALTER TABLE `exam_detail_course`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(50) CHARACTER SET 'utf8mb4' NULL COMMENT '课程名称' ;

ALTER TABLE `sys_org` DROP COLUMN `third_relate_id`;

ALTER TABLE `exam_student`
    CHANGE COLUMN `student_name` `student_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '考生姓名' ,
    CHANGE COLUMN `student_code` `student_code` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '考生代码' ;


update exam_task a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update exam_card a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update exam_detail_course a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update teach_course a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update t_p_course a left join t_p_professional tpp on a.professional_id = tpp.id left join basic_course bc on tpp.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update t_b_sync_task a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update basic_exam_student a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update basic_teach_clazz a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update course_dimension a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update course_evaluation a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update course_target a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update course_weight a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update mark_student a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_document a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_group a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_paper a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_task a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_paper_package a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update client_status a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;

ALTER TABLE `exam_print_plan` DROP COLUMN `third_relate_name`, DROP COLUMN `third_relate_id`;
ALTER TABLE `exam_student` DROP COLUMN `can_delete`;
ALTER TABLE `basic_exam_student` ADD COLUMN `teach_class_name` VARCHAR(100) NULL AFTER `major`, ADD COLUMN `class_name` VARCHAR(100) NULL AFTER `teach_class_name`;
ALTER TABLE `basic_exam_student` ADD COLUMN `site_number` VARCHAR(10) NULL COMMENT '座位号' AFTER `student_code`, ADD COLUMN `extend_fields` MEDIUMTEXT NULL COMMENT '扩展字段' AFTER `exam_room`;
ALTER TABLE `basic_exam_student`
    CHANGE COLUMN `college` `college_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '学院' ,
    CHANGE COLUMN `major` `major_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '专业' ;

update basic_exam_student bes set bes.teach_class_name = (select btc.clazz_name from basic_teach_clazz btc where bes.clazz_id = btc.id) where bes.teach_class_name is null;

ALTER TABLE `mark_paper` ADD COLUMN `question_status` TINYINT(1) NULL DEFAULT '0' COMMENT '试卷结构是否提交' AFTER `show_object_score`;
update mark_paper set question_status = group_status;

ALTER TABLE `basic_exam_student`
    ADD COLUMN `status` VARCHAR(1) NULL DEFAULT 'N' COMMENT '状态，默认N-正常，D-缓考，F-免考，M-缺考，B-违纪' AFTER `class_name`,
ADD COLUMN `required_fields` MEDIUMTEXT NULL COMMENT '基础字段' AFTER `exam_room`;

ALTER TABLE `exam_card` ADD COLUMN `copy_card_id` BIGINT(20) NULL COMMENT '复制源题卡ID' AFTER `paper_id`;


-- drop table if exists exam_detail_course_paper_type;
-- drop table if exists basic_template_org;
-- drop table if exists cloud_user_push_status;
-- drop table if exists exam_card_detail;
-- drop table if exists exam_paper_class_marker;
-- drop table if exists exam_paper_group;
-- drop table if exists exam_paper_group_marker;
-- drop table if exists exam_paper_structure;
-- drop table if exists exam_task_sync;
-- drop table if exists t_sync_exam_log;
-- drop table if exists t_sync_stmms_exam;
-- drop table if exists basic_college;
-- drop table if exists basic_campus;

-- delete脚本需要加上新增的表

-- 升级说明
-- 1. 将通用规则中，教学班改为teachClassName,班级改为className