stmms-ft/install/mysql/upgrade/1.5.1.sql

-- 1.5.1
USE `stmms_ft`;

ALTER TABLE eb_selective_student ADD INDEX `index1` (`exam_id`, `subject_code`);
ALTER TABLE m_trial_library DROP INDEX `index2`;
ALTER TABLE m_trial_library ADD INDEX `index2` (`student_id`, `group_number`);
ALTER TABLE eb_marker ADD COLUMN `finish_count_na` int(11)     DEFAULT NULL COMMENT '完成数量不含仲裁';
ALTER TABLE eb_marker ADD COLUMN `valid_count_na`  int(11)     DEFAULT NULL COMMENT '有效数量不含仲裁';
ALTER TABLE eb_marker ADD COLUMN `avg_score_na`    double      DEFAULT NULL COMMENT '平均分不含仲裁';
ALTER TABLE eb_marker ADD COLUMN `avg_speed_na`    double      DEFAULT NULL COMMENT '平均时长不含仲裁';
ALTER TABLE eb_marker ADD COLUMN `stdev_score_na`  double      DEFAULT NULL COMMENT '标准差不含仲裁';

ALTER TABLE eb_exam_student ADD COLUMN `objective_page_count`   int(11)     NOT NULL DEFAULT 0 COMMENT '客观题图数量';
ALTER TABLE eb_exam ADD COLUMN `inspect_round_limit`   	tinyint(1)  NOT NULL DEFAULT 1 COMMENT '全卷复核进度100%时才能再次复核';

update b_privilege set seq=50 where code='exam_paper';
update b_privilege set seq=40 where code='exam_scan';


delete from b_privilege  where code in ('exam_report_subject','exam_report_subject_range');
delete from b_role_privilege  where privilege_code in ('exam_report_subject','exam_report_subject_range');
delete from b_role_privilege  where privilege_code in ('exam_mark-group-add','exam_mark-group-edit','exam_mark-group-reset_edit','exam_mark-group-delete');

INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('overview', '主页导览', 'root_code', 'MENU', '/admin/basic/overview', 5,1,'icon-bookmark','index.overview');

INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`,`icon`,`i18n`)
VALUES ('exam_report_subject', '科目分析', 'root_code', 'MENU', '/admin/basic/role/info/sub/page', 110,1,'icon-asterisk','index.report.subject');
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`)
VALUES ('exam_report_subject-total', '总量分析', 'exam_report_subject', 'PAGE', '/admin/exam/reportSubject', 10,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`)
VALUES ('exam_report_subject-range', '分段统计', 'exam_report_subject', 'PAGE', '/admin/exam/reportSubjectRange', 20,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`)
VALUES ('exam_report_subject-college', '学院分析', 'exam_report_subject', 'PAGE', '/admin/exam/reportSubjectCollege', 30,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`)
VALUES ('exam_report_subject-teacher', '任课老师分析', 'exam_report_subject', 'PAGE', '/admin/exam/reportSubjectTeacher', 40,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`)
VALUES ('exam_report_subject-class', '班级分析', 'exam_report_subject', 'PAGE', '/admin/exam/reportSubjectClass', 50,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`)
VALUES ('exam_report_subject-objective_question', '客观题分析', 'exam_report_subject', 'PAGE', '/admin/exam/reportSubjectQuestion', 60,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`)
VALUES ('exam_report_subject-subjective_question', '主观题分析', 'exam_report_subject', 'PAGE', '/admin/exam/reportSubjectQuestion', 70,2);
INSERT INTO `b_privilege` ( `code`, `name`, `parent_code`, `privilege_type`, `privilege_uri`, `seq`,`level`)
VALUES ('exam_report_subject-group', '大题分析', 'exam_report_subject', 'PAGE', '/admin/exam/reportSubjectGroup', 80,2);

INSERT INTO `b_privilege` (`code`, `name`, `parent_code`, `privilege_type`, `level`, `seq`)
VALUES ('user_list-help_video', '帮助视频', 'user_list', 'BUTTON', 2, 1);

INSERT INTO `b_privilege` (`code`, `name`, `parent_code`, `privilege_type`, `level`, `seq`)
VALUES ('exam_mark-group-help_video', '帮助视频', 'exam_mark-group', 'BUTTON', 3, 5);

INSERT INTO `b_privilege` (`code`, `name`, `parent_code`, `privilege_type`, `level`, `seq`)
VALUES ('exam_inspected_info-list-help_video', '帮助视频', 'exam_inspected_info-list', 'BUTTON', 3, 2); 

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'SCHOOL_ADMIN',p.`code`,1 from b_privilege p join b_school s
where p.`code` in('overview','exam_report_subject','exam_report_subject-total','exam_report_subject-range','exam_report_subject-college',
'exam_report_subject-teacher','exam_report_subject-class'
,'exam_report_subject-objective_question','exam_report_subject-subjective_question','exam_report_subject-group');

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'SUBJECT_HEADER',p.`code`,1 from b_privilege p join b_school s
where p.`code` in('exam_report_subject','exam_report_subject-total','exam_report_subject-range','exam_report_subject-college',
'exam_report_subject-teacher','exam_report_subject-class'
,'exam_report_subject-objective_question','exam_report_subject-subjective_question','exam_report_subject-group'
);

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'COLLEGE_ADMIN',p.`code`,1 from b_privilege p join b_school s
where p.`code` in('exam_report_subject','exam_report_subject-total','exam_report_subject-range','exam_report_subject-college',
'exam_report_subject-teacher','exam_report_subject-class'
,'exam_report_subject-objective_question','exam_report_subject-subjective_question','exam_report_subject-group'
);

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'SCHOOL_VIEWER',p.`code`,1 from b_privilege p join b_school s
where p.`code` in('exam_report_subject','exam_report_subject-total');

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'SCHOOL_ADMIN',p.`code`,1 from b_privilege p join b_school s
where p.`code` in('exam_mark-group-add','exam_mark-group-edit','exam_mark-group-reset_edit'
,'exam_mark-group-delete','user_list-help_video','exam_mark-group-help_video','exam_inspected_info-list-help_video');

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'SUBJECT_HEADER',p.`code`,0 from b_privilege p join b_school s
where p.`code` in('exam_mark-group-add','exam_mark-group-edit','exam_mark-group-reset_edit','exam_mark-group-delete');

INSERT INTO b_role_privilege (`school_id`, `role_code`, `privilege_code`,`enable`)
SELECT s.id,'COLLEGE_ADMIN',p.`code`,0 from b_privilege p join b_school s
where p.`code` in('exam_mark-group-add','exam_mark-group-edit','exam_mark-group-reset_edit','exam_mark-group-delete');


USE college_scan_multi;

TRUNCATE TABLE college_scan_multi.absent;
TRUNCATE TABLE college_scan_multi.append_scan;
TRUNCATE TABLE college_scan_multi.baseinfo;
TRUNCATE TABLE college_scan_multi.baseinfolog;
TRUNCATE TABLE college_scan_multi.campus;
TRUNCATE TABLE college_scan_multi.card_defcount;
TRUNCATE TABLE college_scan_multi.check_omr;
TRUNCATE TABLE college_scan_multi.check_student;
TRUNCATE TABLE college_scan_multi.cliprect;
TRUNCATE TABLE college_scan_multi.config;
TRUNCATE TABLE college_scan_multi.eb_exam_student;
TRUNCATE TABLE college_scan_multi.import_sign_page;
TRUNCATE TABLE college_scan_multi.kslb;
TRUNCATE TABLE college_scan_multi.objective;
TRUNCATE TABLE college_scan_multi.ocrdata;
TRUNCATE TABLE college_scan_multi.ocrdata_history;
TRUNCATE TABLE college_scan_multi.omrresult;
TRUNCATE TABLE college_scan_multi.registno;
TRUNCATE TABLE college_scan_multi.registqty;
TRUNCATE TABLE college_scan_multi.sign_page;
TRUNCATE TABLE college_scan_multi.subject_code_kgt;
TRUNCATE TABLE college_scan_multi.task_child;
TRUNCATE TABLE college_scan_multi.task_master;
TRUNCATE TABLE college_scan_multi.upload_file_list;
TRUNCATE TABLE college_scan_multi.verify_check_info;

ALTER TABLE college_scan_multi.`baseinfo` ADD COLUMN `isOnLine`  smallint(1) NULL DEFAULT NULL AFTER `conver`;
ALTER TABLE college_scan_multi.`check_omr` DROP INDEX `mark_dth`;
ALTER TABLE college_scan_multi.`check_omr` ADD COLUMN `mark_xth`  longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL AFTER `check_ip`;
ALTER TABLE college_scan_multi.`check_omr` ADD COLUMN `admin_mark_xth`  longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL AFTER `admin_check_status`;
ALTER TABLE college_scan_multi.`check_omr` ADD COLUMN `check_history1`  longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL AFTER `admin_mark_xth`;
ALTER TABLE college_scan_multi.`check_omr` ADD COLUMN `admin_mark_xth_history`  longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL AFTER `check_history1`;
ALTER TABLE college_scan_multi.`check_omr` DROP COLUMN `mark_dth`;
CREATE INDEX `mark_xth` ON college_scan_multi.`check_omr`(`mark_xth`(768)) USING BTREE ;

ALTER TABLE college_scan_multi.`ocrdata` ADD COLUMN `is_kgt_km`  int(1) NULL DEFAULT NULL AFTER `SubjectName`;
ALTER TABLE college_scan_multi.`ocrdata` ADD COLUMN `omr_repeat`  bit(1) NULL DEFAULT NULL AFTER `OCRUser`;
ALTER TABLE college_scan_multi.`ocrdata` MODIFY COLUMN `isUpload_kgt`  int(1) NULL DEFAULT NULL AFTER `isUpload`;
ALTER TABLE college_scan_multi.`ocrdata` DROP COLUMN `slicePic`;
ALTER TABLE college_scan_multi.`task_child` MODIFY COLUMN `check_times1`  varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL AFTER `task_id`;
ALTER TABLE college_scan_multi.`task_child` ADD COLUMN `check_user`  varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL AFTER `check_status1`;
CREATE INDEX `exam_id` ON college_scan_multi.`task_child`(`exam_id`) USING BTREE ;
CREATE INDEX `exam_number` ON college_scan_multi.`task_child`(`exam_number`) USING BTREE ;
CREATE INDEX `task_id` ON college_scan_multi.`task_child`(`task_id`) USING BTREE ;
ALTER TABLE college_scan_multi.`task_master` ADD COLUMN `task_class`  varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL AFTER `task_id`;
ALTER TABLE college_scan_multi.`task_master` ADD COLUMN `task_xh`  int(3) NULL DEFAULT NULL AFTER `task_type`;
ALTER TABLE college_scan_multi.`task_master` ADD COLUMN `task_ui_where`  longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL AFTER `task_where`;
CREATE INDEX `task_id` ON college_scan_multi.`task_master`(`task_id`) USING BTREE ;
CREATE INDEX `task_class` ON college_scan_multi.`task_master`(`task_class`) USING BTREE ;
CREATE INDEX `task_xh` ON college_scan_multi.`task_master`(`task_xh`) USING BTREE ;
ALTER TABLE college_scan_multi.`upload_file_list` ADD COLUMN `json_card`  longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL AFTER `DiskSerNo`;

DROP TABLE IF EXISTS college_scan_multi.`ocrdata_objective`;
CREATE TABLE college_scan_multi.`ocrdata_objective` (
                                                        `exam_id` int(5) NOT NULL,
                                                        `exam_number` varchar(30) NOT NULL,
                                                        `is_merge` bit(1) DEFAULT NULL,
                                                        `subjective_DiskSerNo` varchar(50) DEFAULT NULL,
                                                        `subjective_sheet_count` int(3) DEFAULT NULL,
                                                        `subjective_sheet_src` longtext,
                                                        `subjective_slice_count` int(3) DEFAULT NULL,
                                                        `subjective_slice_src` longtext,
                                                        `subjective_scan_page_list` varchar(100) DEFAULT NULL,
                                                        `subjective_slice_point` longtext,
                                                        `subjective_is_upload` bit(1) DEFAULT NULL,
                                                        `objective_DiskSerNo` varchar(50) DEFAULT NULL,
                                                        `objective_sheet_count` int(3) DEFAULT NULL,
                                                        `objective_sheet_src` longtext,
                                                        `objective_slice_count` int(3) DEFAULT NULL,
                                                        `objective_slice_src` longtext,
                                                        `objective_scan_page_list` varchar(100) DEFAULT NULL,
                                                        `objective_slice_point` longtext,
                                                        `objective_kgt_point` longtext,
                                                        `objective_is_upload` bit(1) DEFAULT NULL,
                                                        `objective_absent` bit(1) DEFAULT NULL,
                                                        `objective_wj` bit(1) DEFAULT NULL,
                                                        `objective_paper_type` varchar(5) DEFAULT NULL,
                                                        `objective_firstResult` longtext,
                                                        `objective_secondResult` longtext,
                                                        PRIMARY KEY (`exam_id`,`exam_number`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS college_scan_multi.`omr_picture_data`;
CREATE TABLE college_scan_multi.`omr_picture_data` (
                                                       `exam_id` int(5) NOT NULL,
                                                       `exam_number` varchar(30) NOT NULL,
                                                       `subject_code` varchar(30) DEFAULT NULL,
                                                       `yccl` int(3) DEFAULT NULL,
                                                       `first_result` longtext,
                                                       `second_result` longtext,
                                                       `kgt_point` longtext,
                                                       `is_upload` bit(1) DEFAULT NULL,
                                                       PRIMARY KEY (`exam_id`,`exam_number`) USING BTREE,
                                                       KEY `exam_id` (`exam_id`) USING BTREE,
                                                       KEY `exam_number` (`exam_number`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS college_scan_multi.`task_ans_check_child`;
CREATE TABLE college_scan_multi.`task_ans_check_child` (
                                                           `exam_id` int(11) NOT NULL,
                                                           `exam_number` varchar(30) NOT NULL,
                                                           `task_type` int(3) DEFAULT NULL,
                                                           `task_id` varchar(30) NOT NULL,
                                                           `task_xh` int(3) NOT NULL,
                                                           `omrResult` longtext,
                                                           `check_times1` varchar(30) DEFAULT NULL,
                                                           `check_ip1` varchar(30) DEFAULT NULL,
                                                           `check_status1` varchar(30) DEFAULT NULL,
                                                           `check_user` varchar(30) DEFAULT NULL,
                                                           PRIMARY KEY (`exam_id`,`exam_number`,`task_id`) USING BTREE,
                                                           KEY `exam_number` (`exam_number`) USING BTREE,
                                                           KEY `task_type` (`task_type`) USING BTREE,
                                                           KEY `task_id` (`task_id`) USING BTREE,
                                                           KEY `task_xh` (`task_xh`) USING BTREE,
                                                           KEY `check_ip1` (`check_ip1`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;