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

-- -----------------------------------------------------------
-- 会议表增加字段
-- -----------------------------------------------------------
DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='official_document_received' AND COLUMN_NAME = 'meeting_time') THEN
ALTER TABLE `official_document_received`   
  ADD COLUMN `meeting_time` DATETIME NULL   COMMENT '会议时间' AFTER `file_id`,
  ADD COLUMN `meeting_address` VARCHAR(50) NULL   COMMENT '会议地点' AFTER `meeting_time`,
  ADD COLUMN `attendees` VARCHAR(3000) NULL   COMMENT '与会人员' AFTER `meeting_address`;
    END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;
ALTER TABLE official_document_received
    ADD user_ids VARCHAR(512) NULL COMMENT '传阅人（可多选）';

-- -----------------------------------------------------------
-- 学籍表中增加考号
-- -----------------------------------------------------------
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 = 'candidate_number') THEN
ALTER TABLE `base_student_school_roll`   
  ADD COLUMN `candidate_number` VARCHAR(20) NULL   COMMENT '考号' AFTER `roll_modality`;
    END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;

-- ------------------------------------------------
-- 资产管理
-- ------------------------------------------------
DROP TABLE IF EXISTS wf_asset_manage;
CREATE TABLE `wf_asset_manage` (
  `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,
  `asset_type` VARCHAR(30) DEFAULT NULL COMMENT '资产类型',
  `asset_category` VARCHAR(30) DEFAULT NULL COMMENT '资产类别',
  `asset_species` VARCHAR(30) DEFAULT NULL COMMENT '资产种类',
  `name` VARCHAR(100) DEFAULT NULL COMMENT '名称',
  `dept_id` BIGINT DEFAULT NULL COMMENT '所属部门[xjr_department]',
  `user_id` BIGINT DEFAULT NULL COMMENT '使用人id[xjr_user]',
  `amount` DECIMAL(8,2) DEFAULT NULL COMMENT '金额',
  `status` SMALLINT DEFAULT 0 COMMENT '状态(0:未结束 1:结束)',
  `area_type` VARCHAR(50) DEFAULT NULL COMMENT '区域类型',
  `folder_id` BIGINT DEFAULT NULL COMMENT '附件id',
  `base_office_build_id` BIGINT DEFAULT NULL COMMENT '楼栋id',
  `floor_number` INT DEFAULT NULL COMMENT '楼层',
  `room_number` VARCHAR(50) DEFAULT NULL COMMENT '门牌号',
  PRIMARY KEY (`id`) USING BTREE
) 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='base_classroom' AND COLUMN_NAME = 'number') THEN
ALTER TABLE `base_classroom`   
  ADD COLUMN `number` VARCHAR(50) NULL   COMMENT '门牌号' AFTER `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_schema' AND COLUMN_NAME = 'style_type') THEN
ALTER TABLE `xjr_workflow_schema` 
  ADD COLUMN `style_type` INT DEFAULT 0  NULL   COMMENT '样式(0:表单 1:表格)' AFTER `modify_user_id`;

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


-- ------------------------------------------------
-- 考核模板
-- ------------------------------------------------
DROP TABLE IF EXISTS assessment_template;
CREATE TABLE `assessment_template` (
  `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(100) DEFAULT NULL COMMENT '名称',
  `score` BIGINT DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='考核模板';

-- ------------------------------------------------
-- 考核模板题目
-- ------------------------------------------------
DROP TABLE IF EXISTS assessment_question;
CREATE TABLE `assessment_question` (
  `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,
  `sort_code` INT DEFAULT NULL,
  `name` VARCHAR(100) DEFAULT NULL COMMENT '题目题干',
  `score` BIGINT DEFAULT NULL COMMENT '分数',
  `type` VARCHAR(100) DEFAULT NULL COMMENT '题目类型',
  `category` INT DEFAULT NULL COMMENT '类别(1:大题 2:小题 3:选项)',
  `parent_id` BIGINT DEFAULT NULL COMMENT '父级id', 
  `required` SMALLINT DEFAULT 1 COMMENT '小题是否必填(1:是 0:否)',
  `assessment_template_id` BIGINT DEFAULT NULL COMMENT '考核模板id(assessment_template)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='考核模板题目';


-- ------------------------------------------------
-- 考核计划
-- ------------------------------------------------
DROP TABLE IF EXISTS assessment_template_plan;
CREATE TABLE `assessment_template_plan` (
  `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(100) DEFAULT NULL COMMENT '名称',
  `score` BIGINT DEFAULT NULL COMMENT '分数',
  `start_time` DATETIME DEFAULT NULL COMMENT '计划开始时间',
  `end_time` DATETIME DEFAULT NULL COMMENT '计划结束时间',
  `status` INT DEFAULT 0 COMMENT '状态(0:未发布 1:已发布 2:作废)',
  `type` VARCHAR(30) DEFAULT 0 COMMENT '选人方式(全部：all 随机：random)',
  `random_number` INT DEFAULT NULL COMMENT '随机人数',
  `assessment_template_id` BIGINT DEFAULT NULL COMMENT '考核模板id(assessment_template)',
  base_semester_id BIGINT DEFAULT NULL COMMENT '学期id(base_semester)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='考核计划';

-- ------------------------------------------------
-- 考核模板题目
-- ------------------------------------------------
DROP TABLE IF EXISTS assessment_plan_question;
CREATE TABLE `assessment_plan_question` (
  `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,
  `sort_code` INT DEFAULT NULL,
  `name` VARCHAR(100) DEFAULT NULL COMMENT '题目题干',
  `score` BIGINT DEFAULT NULL COMMENT '分数',
  `type` VARCHAR(100) DEFAULT NULL COMMENT '题目类型',
  `category` INT DEFAULT NULL COMMENT '类别(1:大题 2:小题 3:选项)',
  `parent_id` BIGINT DEFAULT NULL COMMENT '父级id', 
  `required` SMALLINT DEFAULT 1 COMMENT '小题是否必填(1:是 0:否)',
  `assessment_template_plan_id` BIGINT DEFAULT NULL COMMENT '考核计划id(assessment_template_plan)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='考核模板题目';


-- ------------------------------------------------
-- 考核计划答题参与班级
-- ------------------------------------------------
DROP TABLE IF EXISTS assessment_plan_answer_class;
CREATE TABLE `assessment_plan_answer_class` (
  `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,
  `class_id` BIGINT DEFAULT NULL COMMENT '班级id',
  `is_confirm` INT DEFAULT 0 COMMENT '班主任是否确认(0:否 1:是)',
  `assessment_template_plan_id` BIGINT DEFAULT NULL COMMENT '考核计划id(assessment_template_plan)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='考核计划答题参与班级';

-- ------------------------------------------------
-- 考核计划答题参与学生
-- ------------------------------------------------
DROP TABLE IF EXISTS assessment_plan_answer_student;
CREATE TABLE `assessment_plan_answer_student` (
  `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,
  `student_user_id` BIGINT DEFAULT NULL COMMENT '学生id',
  `is_confirm` INT DEFAULT 0 COMMENT '班主任是否确认(0:否 1:是)',
	`submit_status` INT DEFAULT 0 COMMENT '状态(0:未答题 1:已答题)',
  `assessment_template_plan_id` BIGINT DEFAULT NULL COMMENT '考核计划id(assessment_template_plan)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='考核计划答题参与学生';


-- ------------------------------------------------
-- 考核答题结果
-- ------------------------------------------------
DROP TABLE IF EXISTS assessment_plan_answer_result;
CREATE TABLE `assessment_plan_answer_result` (
  `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,
  `student_user_id` BIGINT DEFAULT NULL COMMENT '学生id',
  `question_id` BIGINT DEFAULT NULL COMMENT '选项id(assessment_plan_question[category=2的id])',
  `answer_id` VARCHAR(40) DEFAULT NULL COMMENT '选项id(assessment_plan_question[category=3的id])',
  `assessment_template_plan_id` BIGINT DEFAULT NULL COMMENT '考核计划id(assessment_template_plan)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='考核答题结果';

DROP TABLE IF EXISTS day_time_select;
CREATE TABLE `day_time_select` (
  `time` DOUBLE DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;