api/src/main/resources/sqlScript/20231129_sql.sql

-- ------------------------------------------------------------------学籍异动管理--------------------------------------------------------------------
-- ----------------------------
-- 学籍异动类别
-- ----------------------------
DROP TABLE IF EXISTS school_roll_category;
CREATE TABLE school_roll_category
(
    id BIGINT NOT NULL COMMENT '主键编号',
    `create_user_id` BIGINT NULL DEFAULT NULL COMMENT '创建人',
    `create_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
    `modify_user_id` BIGINT NULL DEFAULT NULL COMMENT '修改人',
    `modify_date` datetime NULL DEFAULT NULL COMMENT '修改时间',
    `delete_mark` INT NOT NULL COMMENT '删除标记',
    `enabled_mark` INT NOT NULL COMMENT '有效标志',
    `sort_code` INT NULL DEFAULT NULL COMMENT '序号',
    `name` VARCHAR(200) NOT NULL COMMENT '名称',
    `scholarship_source` varchar(20) NOT NULL DEFAULT 0 COMMENT '学籍异动类型(xjr_dictionary_item[school_change_type])',
    `remark` VARCHAR(1000) COMMENT '备注',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '学籍异动类别';

-- ----------------------------
-- 学籍异动原因
-- ----------------------------
DROP TABLE IF EXISTS school_roll_reason;
CREATE TABLE school_roll_reason
(
    id BIGINT NOT NULL COMMENT '主键编号',
    `create_user_id` BIGINT NULL DEFAULT NULL COMMENT '创建人',
    `create_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
    `modify_user_id` BIGINT NULL DEFAULT NULL COMMENT '修改人',
    `modify_date` datetime NULL DEFAULT NULL COMMENT '修改时间',
    `delete_mark` INT NOT NULL COMMENT '删除标记',
    `enabled_mark` INT NOT NULL COMMENT '有效标志',
    `sort_code` INT NULL DEFAULT NULL COMMENT '序号',
    `reason` VARCHAR(2000) NOT NULL COMMENT '原因',
    `school_roll_category_id` bigint NULL DEFAULT NULL COMMENT '学籍异动类别ID(school_roll_category)',
    `remark` VARCHAR(1000) COMMENT '备注',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '学籍异动原因';

-- ----------------------------
-- 学籍异动学生
-- ----------------------------
DROP TABLE IF EXISTS school_roll_student;
CREATE TABLE school_roll_student
(
    id BIGINT NOT NULL COMMENT '主键编号',
    `create_user_id` BIGINT NULL DEFAULT NULL COMMENT '创建人',
    `create_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
    `modify_user_id` BIGINT NULL DEFAULT NULL COMMENT '修改人',
    `modify_date` datetime NULL DEFAULT NULL COMMENT '修改时间',
    `delete_mark` INT NOT NULL COMMENT '删除标记',
    `enabled_mark` INT NOT NULL COMMENT '有效标志',
    `sort_code` INT NULL DEFAULT NULL COMMENT '序号',
    `applicant_user_id` bigint NULL DEFAULT NULL COMMENT '申请人',
    `scholarship_source` varchar(20) NOT NULL DEFAULT 0 COMMENT '学籍异动类型(xjr_dictionary_item[school_change_type])',
    `school_roll_category_id` bigint NULL DEFAULT NULL COMMENT '学籍异动类别ID(school_roll_category)',
    `school_roll_reason_id` bigint NULL DEFAULT NULL COMMENT '学籍异动原因ID(school_roll_reason)',
    `reason` varchar(2000) NULL DEFAULT NULL COMMENT '原因',

    `before_department_id` bigint NULL DEFAULT NULL COMMENT '异动前所属机构(xjr_department)',
    `before_grade_id` bigint NULL DEFAULT NULL COMMENT '异动前入学年级(base_grade)',
    `before_class_id` bigint NULL DEFAULT NULL COMMENT '异动前班级(base_class)',
    `before_major_set_id` bigint NULL DEFAULT NULL COMMENT '异动前专业方向(base_major_set)',

    `back_department_id` bigint NULL DEFAULT NULL COMMENT '异动后所属机构(xjr_department)',
    `back_grade_id` bigint NULL DEFAULT NULL COMMENT '异动后入学年级(base_grade)',
    `back_class_id` bigint NULL DEFAULT NULL COMMENT '异动后班级(base_class)',
    `back_major_set_id` bigint NULL DEFAULT NULL COMMENT '异动后专业方向(base_major_set)',

    `student_id` bigint NULL DEFAULT NULL COMMENT '学生编号(学生user_id)',
    `is_retain` int NULL DEFAULT NULL COMMENT '是否保留学籍(0:否 1:是)',
    `is_record` int NULL DEFAULT NULL COMMENT '是否记录到档案(0:否 1:是)',
    `is_cancel_account` int NULL DEFAULT NULL COMMENT '是否注销账号(0:否 1:是)',
    `is_recover` int NULL DEFAULT NULL COMMENT '是否恢复账号(0:否 1:是)',
    `is_under_graduate` int NULL DEFAULT NULL COMMENT '是否给予肄业(0:否 1:是)',
    `is_maintained` int NULL DEFAULT NULL COMMENT '是否已经维护到国家/省学籍系统(0:未维护 1:已维护 2:无需维护)',
    `in_class_date` date NULL DEFAULT NULL COMMENT '入班时间',
    `out_class_date` date NULL DEFAULT NULL COMMENT '离班时间',
    `leave_year` varchar(200) NULL DEFAULT NULL COMMENT '休学年限 ',
    `leave_start_year` date NULL DEFAULT NULL COMMENT '休学时段(开始时间)',
    `leave_end_year` date NULL DEFAULT NULL COMMENT '休学时段(结束时间)',
    `remark` varchar(1000) COMMENT '备注',
    `status` int not null default 0 COMMENT '状态（1:结束 0:未结束）',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '学籍异动学生';

-- ----------------------------
-- 插班生管理
-- ----------------------------
DROP TABLE IF EXISTS school_roll_transfer_student;
CREATE TABLE school_roll_transfer_student
(
    id BIGINT NOT NULL COMMENT '主键编号',
    `create_user_id` BIGINT NULL DEFAULT NULL COMMENT '创建人',
    `create_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
    `modify_user_id` BIGINT NULL DEFAULT NULL COMMENT '修改人',
    `modify_date` datetime NULL DEFAULT NULL COMMENT '修改时间',
    `delete_mark` INT NOT NULL COMMENT '删除标记',
    `enabled_mark` INT NOT NULL COMMENT '有效标志',
    `sort_code` INT NULL DEFAULT NULL COMMENT '序号',
    `department_id` bigint NULL DEFAULT NULL COMMENT '所属机构(xjr_department)',
    `grade_id` bigint NULL DEFAULT NULL COMMENT '入学年级(base_grade)',
    `class_id` bigint NULL DEFAULT NULL COMMENT '班级(base_class)',
    `major_set_id` bigint NULL DEFAULT NULL COMMENT '专业方向(base_major_set)',
    `in_class_date` date NULL DEFAULT NULL COMMENT '入班时间',

    `before_school_name` varchar(200) NULL DEFAULT NULL COMMENT '转学前学校名称',
    `before_grade_name` varchar(200) NULL DEFAULT NULL COMMENT '转学前年级名称',
    `before_class_name`  varchar(200) NULL DEFAULT NULL COMMENT '转学前班级名称',
    `before_major_set_name` varchar(200) NULL DEFAULT NULL COMMENT '转学前专业方向名称',
    `before_school_id` varchar(200) NULL DEFAULT NULL COMMENT '转学前学号',
    `transfer_date` date NULL DEFAULT NULL COMMENT '转学日期',

    `student_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学号',
    `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
    `as_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '别名',
    `gender` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别(xjr_dictionary_item[gender])',
    `credential_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '证件类型(xjr_dictionary_item[credential_type])',
    `credential_number` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '证件号码',
    `day_of_birth` date NULL DEFAULT NULL COMMENT '出生日期',
    `birth_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '生日类型(xjr_dictionary_item[date_type])',
    `nation` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '民族(xjr_dictionary_item[nation])',
    `blood_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '血型(xjr_dictionary_item[blood_type])',
    `health` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '健康状况(xjr_dictionary_item[health])',
    `marital_state` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '婚姻状况(xjr_dictionary_item[marital_state])',
    `political_state` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '政治面貌(xjr_dictionary_item[political_state])',
    `religion` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '信仰宗教(xjr_dictionary_item[religion])',
    `nationality` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '国籍地区(xjr_dictionary_item[nationality])',
    `chinese_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '港澳台侨外(xjr_dictionary_item[chinese_type])',
    `provinces` bigint NULL DEFAULT NULL COMMENT '籍贯 省(xjr_area)',
    `city` bigint NULL DEFAULT NULL COMMENT '籍贯 市(xjr_area)',
    `district` bigint NULL DEFAULT NULL COMMENT '籍贯 区/县(xjr_area)',
    `house_hold_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '户口类别(xjr_dictionary_item[house_hold_type])',
    `house_provinces` bigint NULL DEFAULT NULL COMMENT '户口所在地 省(xjr_area)',
    `house_city` bigint NULL DEFAULT NULL COMMENT '户口所在地 市(xjr_area)',
    `house_district` bigint NULL DEFAULT NULL COMMENT '户口所在地 区/县(xjr_area)',
    `house_hold_address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '户口所在详细地址',
    `belongs_police` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '所属派出所',
    `birth_provinces` bigint NULL DEFAULT NULL COMMENT '出生地 省(xjr_area)',
    `birth_city` bigint NULL DEFAULT NULL COMMENT '出生地 市(xjr_area)',
    `birth_district` bigint NULL DEFAULT NULL COMMENT '出生地 区/县(xjr_area)',
    `residence_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学生居住地类型(xjr_dictionary_item[residence_type])',
    `is_migrate_children` smallint NULL DEFAULT NULL COMMENT '是否随迁子女',
    `is_floating_population` smallint NULL DEFAULT NULL COMMENT '是否流动人口',
    `telephone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '家庭电话',
    `contact` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '联系人姓名',
    `address` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '家庭住址',
    `wechat` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci  NULL COMMENT '微信号',
    `is_indemnify` smallint NULL DEFAULT NULL COMMENT '是否低保',
    `is_stipend` smallint NULL DEFAULT NULL COMMENT '是否享受国家助学金',
    `is_free` smallint NULL DEFAULT NULL COMMENT '是否免学费',
    `is_filing_card` smallint NULL DEFAULT NULL COMMENT '是否建档立卡',
    `is_stay` smallint NULL DEFAULT NULL COMMENT '是否住宿',
    `status` int not null default 0 COMMENT '状态（1:结束 0:未结束）',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '插班生管理';

-- ----------------------------
-- 升学管理
-- ----------------------------
DROP TABLE IF EXISTS school_roll_further_education;
CREATE TABLE school_roll_further_education
(
    id BIGINT NOT NULL COMMENT '主键编号',
    `create_user_id` BIGINT NULL DEFAULT NULL COMMENT '创建人',
    `create_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
    `modify_user_id` BIGINT NULL DEFAULT NULL COMMENT '修改人',
    `modify_date` datetime NULL DEFAULT NULL COMMENT '修改时间',
    `delete_mark` INT NOT NULL COMMENT '删除标记',
    `enabled_mark` INT NOT NULL COMMENT '有效标志',
    `sort_code` INT NULL DEFAULT NULL COMMENT '序号',
    `applicant_user_id` bigint NULL DEFAULT NULL COMMENT '申请人',
    `reason` varchar(2000) NULL DEFAULT NULL COMMENT '原因',

    `before_department_id` bigint NULL DEFAULT NULL COMMENT '异动前所属机构(xjr_department)',
    `before_grade_id` bigint NULL DEFAULT NULL COMMENT '异动前入学年级(base_grade)',
    `before_class_id` bigint NULL DEFAULT NULL COMMENT '异动前班级(base_class)',
    `before_major_set_id` bigint NULL DEFAULT NULL COMMENT '异动前专业方向(base_major_set)',

    `back_department_id` bigint NULL DEFAULT NULL COMMENT '异动后所属机构(xjr_department)',
    `back_grade_id` bigint NULL DEFAULT NULL COMMENT '异动后入学年级(base_grade)',
    `back_class_id` bigint NULL DEFAULT NULL COMMENT '异动后班级(base_class)',
    `back_major_set_id` bigint NULL DEFAULT NULL COMMENT '异动后专业方向(base_major_set)',

    `remark` varchar(1000) COMMENT '备注',
    `status` int not null default 0 COMMENT '状态（1:结束 0:未结束）',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '升学管理';
-- ------------------------------------------------------------------学籍异动管理--------------------------------------------------------------------