stmms-ms-server/sql/v3.1.0表结构修改日志.sql

ALTER TABLE `msyj-local-v3.1.0`.`param_setting`
DROP COLUMN `take_best`,
DROP COLUMN `clear_data`,
DROP COLUMN `show_standard_paper_manage`,
DROP COLUMN `auto_callback_show_deviation`,
DROP COLUMN `prop_denominator`,
DROP COLUMN `score_show_all_paper`,
DROP COLUMN `change_stage`,
DROP COLUMN `round_up`,
DROP COLUMN `level_show_all_paper`,
DROP COLUMN `cumulative_error`,
DROP COLUMN `majority`,
DROP COLUMN `auto_callback`,
DROP COLUMN `deviation`,
DROP COLUMN `paper_stage`,
DROP COLUMN `name_rule`,
DROP COLUMN `image_encrypt`,
DROP COLUMN `package_scan`,
ADD COLUMN `collect_config` MEDIUMTEXT NULL COMMENT '采集规则参数' AFTER `work_id`,
ADD COLUMN `level_config` MEDIUMTEXT NULL COMMENT '细分档规则参数' AFTER `collect_config`,
ADD COLUMN `rough_level_config` MEDIUMTEXT NULL COMMENT '粗分档规则参数' AFTER `level_config`,
ADD COLUMN `score_config` MEDIUMTEXT NULL COMMENT '打分规则参数' AFTER `first_level_config`;


CREATE TABLE `marker_group_student`  (
                                         `id` bigint(20) NOT NULL AUTO_INCREMENT,
                                         `work_id` bigint(20) NOT NULL,
                                         `subject` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                                         `stage` int(1) NOT NULL COMMENT '所属阶段',
                                         `group_id` bigint(20) NOT NULL COMMENT '分组ID',
                                         `paper_id` bigint(20) NOT NULL COMMENT '试卷ID',
                                         `exam_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                                         `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                                         `area_code` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                                         `area_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                                         `exam_room` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                                         `school` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                                         `source_name` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '生源地',
                                         `group_id` bigint(20) NOT NULL COMMENT '批次号',
                                         `used` tinyint(1) NULL DEFAULT NULL COMMENT '已否已发任务',
                                         PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


ALTER TABLE `marker_group`
    CHANGE COLUMN `work_id` `work_id` BIGINT(20) NOT NULL AFTER `id`,
    ADD COLUMN `stage` INT(1) NULL AFTER `subject`;

ALTER TABLE `level`
    ADD COLUMN `rough_code` varchar(2) NULL COMMENT '粗分档档位' AFTER `kdpt`,
    ADD COLUMN `rough_weight` INT(11) NULL COMMENT '粗分档典型值' AFTER `rough_code`;

CREATE TABLE `mark_task_rough_level`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `work_id` bigint(20) NOT NULL,
  `paper_id` bigint(20) NOT NULL,
  `is_rejected` bit(1) NOT NULL,
  `level_value` int(11) NULL DEFAULT NULL,
  `marker_id` bigint(20) NOT NULL,
  `marker_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `origin_level` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `question_id` bigint(20) NULL DEFAULT NULL,
  `result` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `level` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '最终档位（和paper表中level一致）',
  `stage` int(11) NOT NULL,
  `subject` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `created_on` datetime(0) NULL DEFAULT NULL,
  `updated_on` datetime(0) NULL DEFAULT NULL,
  `random_seq` int(11) NULL DEFAULT NULL,
  `secret_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `random_seq_new` bigint(20) NULL DEFAULT NULL COMMENT '随机号',
  `is_active` bit(1) NULL DEFAULT b'0' COMMENT '是否激活',
  `is_test` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否试评,0:不是，1:数据已导入，2:试评中',
  `batch_no` bigint(45) NULL DEFAULT NULL COMMENT '批次号',
  `is_change_stage` tinyint(1) NULL DEFAULT NULL COMMENT '是否改档',
  `serial_number` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '叫号序号',
  `display_number` int(11) NULL DEFAULT NULL,
  `is_sample` bit(1) NULL DEFAULT NULL COMMENT '是否标准卷',
  `deviation_direction` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '偏差方向',
  `is_mark` bit(1) NULL DEFAULT b'0' COMMENT '是否标记',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_mark_task_paper_id`(`paper_id`) USING BTREE,
  INDEX `question_id`(`question_id`, `marker_id`, `stage`, `result`, `is_rejected`, `random_seq`, `random_seq_new`) USING BTREE,
  INDEX `idx_union_1`(`marker_id`, `stage`, `result`, `random_seq`, `random_seq_new`) USING BTREE,
  CONSTRAINT `mark_task_rough_level_ibfk_1` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_10` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_11` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_12` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_13` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_14` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_15` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_2` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_3` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_4` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_5` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_6` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_7` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_8` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mark_task_rough_level_ibfk_9` FOREIGN KEY (`paper_id`) REFERENCES `paper` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 152 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;


ALTER TABLE `paper`
    ADD COLUMN `is_rough_sample` BIT(1) NULL DEFAULT NULL COMMENT '粗分档标准卷' ,
    ADD COLUMN `rough_level` varchar(2) NULL COMMENT '粗分档档位' AFTER `is_tagged`,
    ADD COLUMN `redo_rough_level` VARCHAR(5) NULL COMMENT '粗分档建议档位' AFTER `rough_level`,
    ADD COLUMN `rough_batch_no` BIGINT(45) NULL COMMENT '粗分档批次号' AFTER `is_active`,
    ADD COLUMN `is_rough_arbitrated` BIT(1) NULL COMMENT '粗分档仲裁' AFTER `is_mark`,
    ADD COLUMN `is_rough_manual` BIT(1) NULL AFTER `is_rough_arbitrated`,
    ADD COLUMN `is_rough_rejected` BIT(1) NULL COMMENT '粗分档打回' AFTER `is_rough_manual`,
    ADD COLUMN `is_rough_mark` BIT(1) NULL COMMENT '粗分档标记' AFTER `is_rough_rejected`;

ALTER TABLE `paper`
    ADD COLUMN `is_rough_shift` BIT(1) NULL AFTER `redo_rough_level`,
ADD COLUMN `is_rough_shift_score` BIT(1) NULL AFTER `is_rough_shift`;


CREATE TABLE `message` (
                       `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
                       `work_id` BIGINT(20) NULL COMMENT '工作ID',
                       `subject` VARCHAR(10) NULL COMMENT '科目',
                       `stage` INT(2) NULL COMMENT '阶段',
                       `send_user_id` BIGINT(20) NULL COMMENT '发件人ID',
                       `send_user_name` VARCHAR(45) NULL COMMENT '发件人名称',
                       `content` VARCHAR(500) NULL COMMENT '消息内容',
                       `receive_user` MEDIUMTEXT NULL COMMENT '收件人集合json',
                       `create_time` DATETIME NULL COMMENT '创建时间',
                       PRIMARY KEY (`id`))
    COMMENT = '消息管理';

CREATE TABLE `message_receive` (
                              `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
                              `message_id` BIGINT(20) NULL,
                              `receive_user_id` BIGINT(20) NULL,
                              `is_read` tinyint(1) NULL,
                              PRIMARY KEY (`id`))
    COMMENT = '消息发送的用户';


ALTER TABLE `change_level`
    ADD COLUMN `stage` INT(2) NULL COMMENT '阶段' AFTER `subject`;