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

-- ------------------------------------------------------------------毕业--------------------------------------------------------------------

-- ----------------------------
-- 学生毕业管理
-- ----------------------------
DROP TABLE IF EXISTS base_student_graduate;
CREATE TABLE base_student_graduate
(
    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 '序号',
    `user_id`  BIGINT NULL DEFAULT NULL COMMENT '学生(xjr_user)',
    `certificate_number` VARCHAR(50) NULL DEFAULT NULL COMMENT '证书编号',
    `certificate_date` DATE NULL DEFAULT NULL COMMENT '发证日期',
    `leaving_bed_date` DATE NULL DEFAULT NULL COMMENT '离寝日期',
    `appendix_id` BIGINT NULL DEFAULT NULL COMMENT '附件id',
    `status` INT NOT NULL DEFAULT 0 COMMENT '证书发放状态（0:未发放 1:已发放）',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '学生毕业管理';

ALTER TABLE `course_table`
    ADD COLUMN `jianyue_id` VARCHAR(40) NULL   COMMENT '简约课表的id' AFTER `modify_date`;
ALTER TABLE `wf_subscription`
    ADD COLUMN `folder_id` BIGINT NULL   COMMENT '附件夹主键' AFTER `total_amount`;
ALTER TABLE `base_student_assessment_inspection`
    CHANGE `reason` `reason` VARCHAR(1000) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL   COMMENT '备注',
    ADD COLUMN `student_user_ids` VARCHAR(4000) NULL   COMMENT '学生ids(多选)' AFTER `status`;
ALTER TABLE `base_student_assessment_inspection`
    ADD COLUMN `dept_id` BIGINT NULL   COMMENT '专业部id' AFTER `student_user_ids`;

ALTER TABLE `base_student_assessment_inspection`
    ADD COLUMN `assessment_type` VARCHAR(50) NULL   COMMENT '考核对象（class:班级 personal:个人）';


-- -------------------------------
-- 调课顶课申请
-- -------------------------------
DROP TABLE IF EXISTS wf_course_adjust;
CREATE TABLE wf_course_adjust
(
    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 '序号',
    `user_id` BIGINT NULL DEFAULT NULL COMMENT '申请人id',
    `dept_id`  BIGINT NULL DEFAULT NULL COMMENT '专业部id',
    `adjust_type` VARCHAR(50) NULL DEFAULT NULL COMMENT '调课类型',
    `reason`  VARCHAR(100) NULL DEFAULT NULL COMMENT '原因',
    `adjust_date` DATE NULL DEFAULT NULL COMMENT '调整日期',
    `remark` VARCHAR(300) NULL DEFAULT NULL COMMENT '备注',
    `course_id` BIGINT NULL DEFAULT NULL COMMENT '课程id(base_course_subject)',
    `exchange_date` DATE NULL DEFAULT NULL COMMENT '对调日期',
    `exchange_teacher_id` BIGINT NULL DEFAULT NULL COMMENT '替班教师id(xjr_user)',
    `exchange_course_id` BIGINT NULL DEFAULT NULL COMMENT '对调课程id(base_course_subject)',
    `status` INT NOT NULL DEFAULT 0 COMMENT '状态（0:未通过 1:已通过）',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '调课顶课申请';


-- -------------------------------
-- 课表发布消息接收
-- -------------------------------
DROP TABLE IF EXISTS course_receive_msg;
CREATE TABLE course_receive_msg
(
    id BIGINT NOT NULL COMMENT '主键编号',
    `create_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 '序号',
    `school_id` VARCHAR(50) NULL DEFAULT NULL COMMENT '学校id',
    `edu_year_serial_no` VARCHAR(50) NULL DEFAULT NULL COMMENT '年级id',
    `start_date` VARCHAR(50) NULL DEFAULT NULL COMMENT '开始时间',
    `end_date`  VARCHAR(50) NULL DEFAULT NULL COMMENT '结束时间',
    `is_callback` INT NULL DEFAULT NULL COMMENT '是否已回调',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '课表发布消息接收';
-- ------------------
-- 课表，字段长度调整
-- ---------------------
ALTER TABLE course_table
    CHANGE `site_id` `site_id` BIGINT DEFAULT 0  NOT NULL   COMMENT '场地编号(site)',
    ADD COLUMN `source_data` TEXT NULL   COMMENT '源数据' AFTER `jianyue_id`;


DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF not EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='base_course_subject' AND COLUMN_NAME = 'org_id') THEN
ALTER TABLE `base_course_subject`
    ADD COLUMN `org_id` BIGINT NULL   COMMENT '开课机构id' AFTER `remark`;
END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;

DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='evaluate_manage' AND COLUMN_NAME = 'source_id') THEN
ALTER TABLE `evaluate_manage`
    ADD COLUMN `source_id` BIGINT NULL   COMMENT '来源id';
END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;

DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='base_grade' AND COLUMN_NAME = 'status') THEN
        ALTER TABLE `base_grade`
            ADD COLUMN `status` INT NULL DEFAULT 1 COMMENT '在读状态（1:在读 0:毕业）' AFTER `title`;

    END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;
--
DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='base_student_assessment_inspection' AND COLUMN_NAME = 'teacher_id') THEN
ALTER TABLE `base_student_assessment_inspection`
    ADD COLUMN `teacher_id` BIGINT NULL   COMMENT '班主任id';
END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;



-- ------------------------------------------------------------------毕业--------------------------------------------------------------------

-- -------------------------------------------------------------------评价管理-----------------------------------------------------------------
DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF not EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='evaluate_manage' AND COLUMN_NAME = 'is_auto_create') THEN

ALTER TABLE `evaluate_manage`
    ADD COLUMN `is_auto_create` INT NULL   COMMENT '是否自动生成（0:否，1:是）' AFTER `status`,
	ADD COLUMN `create_frequency` VARCHAR(50) NULL   COMMENT '生成频率（xjr_dictionary_detail[create_frequency]）' AFTER `is_auto_create`;

END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;
-- -------------------------------------------------------------------评价管理-----------------------------------------------------------------
-- -------------------------------------------------------------------考核-----------------------------------------------------------------
-- -------------------------------
-- 指标公式规则管理
-- -------------------------------
DROP TABLE IF EXISTS quota_formula_rule;
CREATE TABLE quota_formula_rule
(
    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(255) NULL DEFAULT NULL COMMENT '规则名称',
    `formula`  VARCHAR(1000) NULL DEFAULT NULL COMMENT '规则公式',
    `bind_quota` VARCHAR(2000) NULL DEFAULT NULL COMMENT '指标',
    `remark`  VARCHAR(500) NULL DEFAULT NULL COMMENT '备注',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '指标公式规则管理';

-- -------------------------------
-- 考核基数
-- -------------------------------
DROP TABLE IF EXISTS quota_formula_rule_constant;
CREATE TABLE quota_formula_rule_constant
(
    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(255) NULL DEFAULT NULL COMMENT '基数名称',
    `formula`  VARCHAR(1000) NULL DEFAULT NULL COMMENT '规则公式',
    `bind_quota` VARCHAR(50) NULL DEFAULT NULL COMMENT '指标',
    `remark`  VARCHAR(500) NULL DEFAULT NULL COMMENT '备注',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '考核基数';


-- -------------------------------
-- 考核表单属性
-- -------------------------------
DROP TABLE IF EXISTS quota_formula_rule_attribute;
CREATE TABLE quota_formula_rule_attribute
(
    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(255) NULL DEFAULT NULL COMMENT '属性名称',
    `formula`  VARCHAR(1000) NULL DEFAULT NULL COMMENT '属性值',
    `remark`  VARCHAR(500) NULL DEFAULT NULL COMMENT '备注',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '考核表单属性';

-- -------------------------------------------------------------------考核-----------------------------------------------------------------

-- -------------------------------------------------------------------评价-----------------------------------------------------------------
-- -------------------------------
-- 参评人提交记录表
-- -------------------------------
CREATE TABLE `evaluate_submit_record`(
`id` BIGINT NOT NULL,
`evaluate_manage_id` BIGINT COMMENT '评价管理编号',
`evaluate_executer_id` BIGINT COMMENT '参评人编号',
`create_date` DATETIME COMMENT '提交时间',
`evaluated_object` VARCHAR(30) COMMENT '被评对象（teacher：老师，class：班级，student：学生）',
`evaluated_object_id` BIGINT COMMENT '被评对象编号',
PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8mb4
COMMENT='参评人提交记录表';

DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='evaluate_result' AND COLUMN_NAME = 'submit_record_id') THEN
        ALTER TABLE `evaluate_result`
            ADD COLUMN `submit_record_id` BIGINT NULL   COMMENT '提交记录主键编号（evaluate_submit_record）' AFTER `opinion`;

    END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;

ALTER TABLE evaluate_submit_record MODIFY id varchar(64);
-- -------------------------------------------------------------------评价-----------------------------------------------------------------