api/target/classes/sqlScript/20240219_sql.sql

DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='textbook_subscription_record' AND COLUMN_NAME = 'wf_textbook_subscription_item_id') THEN
ALTER TABLE `textbook_subscription_record`   
	ADD COLUMN `wf_textbook_subscription_item_id` BIGINT NULL   COMMENT '教材教辅征订项编号' AFTER `wf_textbook_subscription_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='wf_subscription' AND COLUMN_NAME = 'number') THEN
ALTER TABLE `wf_subscription`   
  ADD COLUMN `number` VARCHAR(20) NULL   COMMENT '编号' AFTER `folder_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_student_assessment_inspection' AND COLUMN_NAME = 'personal_student_user_ids') THEN
ALTER TABLE `base_student_assessment_inspection`   
  CHANGE `student_user_ids` `class_student_user_ids` VARCHAR(4000) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL   COMMENT '班级考核学生对象',
  ADD COLUMN `personal_student_user_ids` VARCHAR(4000) NULL   COMMENT '个人考核学生对象' AFTER `teacher_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_student_school_roll' AND COLUMN_NAME = 'roll_modality') THEN
ALTER TABLE `base_student_school_roll`   
  ADD COLUMN `roll_modality` VARCHAR(50) NULL   COMMENT '学籍形式' AFTER `remark`;
    END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;

-- 机构名称字段长度加长
ALTER TABLE `xjr_department`   
  CHANGE `name` `name` VARCHAR(100) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL   COMMENT '机构名称';

-- 物品申购台账审核状态
DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='wf_subscription' AND COLUMN_NAME = 'status') THEN
ALTER TABLE `wf_subscription`   
  ADD COLUMN `status` INT DEFAULT 0  NULL   COMMENT '状态（0:未审核 1:审核）' AFTER `number`,
  ADD COLUMN `create_user_id` BIGINT NULL AFTER `status`,
  ADD COLUMN `create_date` DATETIME NULL AFTER `create_user_id`,
  ADD COLUMN `modify_user_id` BIGINT NULL AFTER `create_date`,
  ADD COLUMN `modify_date` DATETIME NULL AFTER `modify_user_id`,
  ADD COLUMN `delete_mark` INT NULL AFTER `modify_date`,
  ADD COLUMN `enabled_mark` INT NULL AFTER `delete_mark`;

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

-- ------------------------------------------------
-- 工作流操作记录表
-- ------------------------------------------------
DROP TABLE IF EXISTS xjr_workflow_operate_record;
CREATE TABLE `xjr_workflow_operate_record` (
  `id` BIGINT NOT NULL,
  `node_id` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '节点id',
  `node_type` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '节点类型',
  `node_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '节点名称',
  `node_multi_type` INT DEFAULT NULL COMMENT '节点多实例类型（节点审批类型）',
  `schema_id` BIGINT DEFAULT NULL COMMENT '模板id',
  `process_id` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '流程id',
  `operate_info` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '审批操作信息',
  `record_time` DATETIME(3) DEFAULT NULL COMMENT '记录时间',
  `circulate_message` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '传阅信息',
  `create_user_id` BIGINT DEFAULT NULL COMMENT '操作人id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='工作流操作记录表';


ALTER TABLE `tl`.`xjr_user`   
  CHANGE `gender` `gender` VARCHAR(20) NULL   COMMENT '性别';

-- ------------------------------------------------
-- 教师自评
-- ------------------------------------------------
DROP TABLE IF EXISTS wf_teacher_self_evaluation;
CREATE TABLE `wf_teacher_self_evaluation` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL,
  `create_date` DATETIME(3) DEFAULT NULL,
  `modify_user_id` BIGINT DEFAULT NULL,
  `modify_date` DATETIME(3) DEFAULT NULL,
  `delete_mark` INT DEFAULT NULL,
  `enabled_mark` INT DEFAULT NULL,
  `base_semester_id` BIGINT DEFAULT NULL COMMENT '学期',
  `folder_id` BIGINT DEFAULT NULL COMMENT 'f附件',
  `status` INT DEFAULT '0' COMMENT '流程状态（1:结束 0:未结束）',
  `all_total_score` INT DEFAULT NULL COMMENT '总分',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='教师自评';

-- ------------------------------------------------
-- 教师自评子表
-- ------------------------------------------------
DROP TABLE IF EXISTS wf_teacher_self_evaluation_item;
CREATE TABLE `wf_teacher_self_evaluation_item` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL,
  `create_date` DATETIME(3) DEFAULT NULL,
  `modify_user_id` BIGINT DEFAULT NULL,
  `modify_date` DATETIME(3) DEFAULT NULL,
  `delete_mark` INT DEFAULT NULL,
  `enabled_mark` INT DEFAULT NULL,
  `wf_teacher_self_evaluation_id` BIGINT DEFAULT NULL,
  `category` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '获奖或表彰',
  `wf_teacher_self_evaluation_category_id` BIGINT DEFAULT NULL COMMENT '奖项类别id（wf_teacher_self_evaluation_category）',
  `wf_teacher_self_evaluation_project_id` BIGINT DEFAULT NULL COMMENT '自评具体项目id（wf_teacher_self_evaluation_project）',
  `score` INT DEFAULT NULL COMMENT '项目分数',
  `number` INT DEFAULT NULL COMMENT '获奖次数',
  `total_score` INT DEFAULT NULL COMMENT '合计分数',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='教师自评子表';

-- ------------------------------------------------
-- 教师自评类别
-- ------------------------------------------------
DROP TABLE IF EXISTS wf_teacher_self_evaluation_category;
CREATE TABLE `wf_teacher_self_evaluation_category` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL,
  `create_date` DATETIME(3) DEFAULT NULL,
  `modify_user_id` BIGINT DEFAULT NULL,
  `modify_date` DATETIME(3) DEFAULT NULL,
  `delete_mark` INT DEFAULT NULL,
  `enabled_mark` INT DEFAULT NULL,
  `name` VARCHAR(200) DEFAULT NULL,
  `category` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '获奖或表彰',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='教师自评类别';


-- ------------------------------------------------
-- 教师自评项目
-- ------------------------------------------------
DROP TABLE IF EXISTS wf_teacher_self_evaluation_project;
CREATE TABLE `wf_teacher_self_evaluation_project` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL,
  `create_date` DATETIME(3) DEFAULT NULL,
  `modify_user_id` BIGINT DEFAULT NULL,
  `modify_date` DATETIME(3) DEFAULT NULL,
  `delete_mark` INT DEFAULT NULL,
  `enabled_mark` INT DEFAULT NULL,
  `wf_teacher_self_evaluation_category_id` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `name` VARCHAR(200) DEFAULT NULL,
  `score` INT DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='教师自评项目';


-- 学生异动信息增加状态
DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='school_roll_student' AND COLUMN_NAME = 'before_archives_status') THEN
ALTER TABLE `school_roll_student`   
  ADD COLUMN `before_archives_status` VARCHAR(20) NULL   COMMENT '异动前学籍状态(xjr_dictionary_item[archives_status])' AFTER `status`,
  ADD COLUMN `after_archives_status` VARCHAR(20) NULL   COMMENT '异动后学籍状态(xjr_dictionary_item[archives_status])' AFTER `before_archives_status`;

    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='xjr_workflow_operate_record' AND COLUMN_NAME = 'usage_scenario') THEN
ALTER TABLE `xjr_workflow_operate_record`
    ADD COLUMN `usage_scenario` INT DEFAULT 1 COMMENT '该记录的应用场景' AFTER create_user_id;
    END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;

-- wf_overtime表需添加一个加班人用户id（多选）字段
DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='overtime_workeruser_ids' AND COLUMN_NAME = 'wf_overtime') THEN
ALTER TABLE `wf_overtime`   
  ADD COLUMN `overtime_workeruser_ids` VARCHAR(3000) NULL   COMMENT '加班人id' AFTER `supporting_materials`;

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