-- ============================================================ -- 湛江市智慧人社运营运维（2025-2027年）项目 -- 就业一湛通服务平台 - 见习人员管理 全部表重建脚本 -- -- 说明：删除并重建所有见习人员相关表和视图，包含最新字段 -- 数据库：达梦数据库 (DM8) 兼容 -- 创建日期：2026-06-04 -- 注意：此脚本需手动执行，不在Flyway中自动运行 -- ============================================================ -- ============================================================ -- 第1步：删除视图和表（按依赖顺序） -- 达梦数据库 DROP ... IF EXISTS 语法 -- ============================================================ DROP VIEW IF EXISTS v_internship_personnel_list; DROP TABLE IF EXISTS internship_personnel_service_follow; DROP TABLE IF EXISTS internship_personnel; -- ============================================================ -- 第2步：创建见习人员信息表 -- 达梦兼容：COMMENT 不内联，使用 COMMENT ON COLUMN 单独语句 -- ============================================================ CREATE TABLE INTERNSHIP_PERSONNEL ( ID VARCHAR(36) NOT NULL, PERSONAL_ID VARCHAR(36) NOT NULL, INTERNSHIP_POST_ID VARCHAR(36), INTERNSHIP_STATUS VARCHAR(50), START_DATE DATE, END_DATE DATE, AUDIT_STATUS VARCHAR(50) DEFAULT '待审核', AUDIT_OPINION VARCHAR(500), MAJOR_TAG VARCHAR(50), MINOR_TAG VARCHAR(200), CUSTOM_TAG VARCHAR(500), PRIMARY KEY (ID) ); COMMENT ON TABLE INTERNSHIP_PERSONNEL IS '见习人员信息表'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.ID IS '主键ID'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.PERSONAL_ID IS '关联个人信息ID'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.INTERNSHIP_POST_ID IS '关联见习岗位ID'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.INTERNSHIP_STATUS IS '见习状态（见习中/已期满/已退出）'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.START_DATE IS '见习开始日期'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.END_DATE IS '见习结束日期'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.AUDIT_STATUS IS '审核状态（待审核/已通过/未通过）'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.AUDIT_OPINION IS '审核意见'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.MAJOR_TAG IS '人员大类标签（见习人员）'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.MINOR_TAG IS '人员小类标签'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL.CUSTOM_TAG IS '自定义标签（逗号分隔，字典编码：internship_custom_tag）'; -- ============================================================ -- 第3步：创建见习人员服务跟进记录表 -- ============================================================ CREATE TABLE INTERNSHIP_PERSONNEL_SERVICE_FOLLOW ( ID VARCHAR(36) NOT NULL, INTERNSHIP_PERSONNEL_ID VARCHAR(36) NOT NULL, SERVICE_CONTENT VARCHAR(2000), SERVICE_TIME DATETIME, SERVICE_PROVIDER VARCHAR(100), CREATE_BY VARCHAR(50), CREATE_TIME DATETIME, UPDATE_BY VARCHAR(50), UPDATE_TIME DATETIME, DEL_FLAG INT DEFAULT 0, PRIMARY KEY (ID) ); COMMENT ON TABLE INTERNSHIP_PERSONNEL_SERVICE_FOLLOW IS '见习人员服务跟进记录表'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.ID IS '主键ID'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.INTERNSHIP_PERSONNEL_ID IS '关联见习人员ID'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.SERVICE_CONTENT IS '服务内容'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.SERVICE_TIME IS '服务时间'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.SERVICE_PROVIDER IS '服务人员'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.CREATE_BY IS '创建人'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.CREATE_TIME IS '创建时间'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.UPDATE_BY IS '更新人'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.UPDATE_TIME IS '更新时间'; COMMENT ON COLUMN INTERNSHIP_PERSONNEL_SERVICE_FOLLOW.DEL_FLAG IS '删除标识（0未删除，1已删除）'; -- ============================================================ -- 第4步：创建见习人员列表视图 -- 达梦兼容：使用 MONTHS_BETWEEN + FLOOR 替代 TIMESTAMPDIFF -- 使用 CURRENT_DATE 替代 CURDATE() -- ============================================================ CREATE VIEW v_internship_personnel_list AS SELECT internship_personnel.id, internship_personnel.personal_id, internship_personnel.internship_post_id, internship_personnel.internship_status, internship_personnel.start_date, internship_personnel.end_date, internship_personnel.audit_status, internship_personnel.audit_opinion, internship_personnel.major_tag, internship_personnel.minor_tag, internship_personnel.custom_tag, personal_info.full_name, personal_info.gender, FLOOR(MONTHS_BETWEEN(CURRENT_DATE, personal_info.birth_date) / 12) AS age, personal_info.contact_phone, personal_info.education, personal_info.id_number, personal_info.household_location, personal_info.current_residence, personal_info.job_seeker_category, personal_info.job_search_status, internship_post.post_name, internship_post.company_name FROM internship_personnel LEFT JOIN personal_info ON internship_personnel.personal_id = personal_info.id LEFT JOIN internship_post ON internship_personnel.internship_post_id = internship_post.id; -- ============================================================ -- 第5步：插入测试数据 -- 关联规则： -- 周九(206, 应届高校毕业生) → 软件测试见习生(恒兴科技) -- 吴十(207, 新成长失业青年) → 行政文员见习生(博文教育) -- 郑十一(208, 农村转移就业劳动者) → 机械维修见习生(港湾实业) -- 周九(206) → 农业技术见习生(绿源农业) -- 吴十(207) → 物业管理见习生(阳光物业, 待审核) -- ============================================================ INSERT INTO internship_personnel (ID, PERSONAL_ID, INTERNSHIP_POST_ID, INTERNSHIP_STATUS, START_DATE, END_DATE, AUDIT_STATUS, AUDIT_OPINION, MAJOR_TAG, MINOR_TAG, CUSTOM_TAG) VALUES (178060100000330, 178060100000206, 178060100000320, '见习中', '2026-04-01', '2026-09-30', '已通过', '符合见习条件，审核通过', '1', '1', NULL); INSERT INTO internship_personnel (ID, PERSONAL_ID, INTERNSHIP_POST_ID, INTERNSHIP_STATUS, START_DATE, END_DATE, AUDIT_STATUS, AUDIT_OPINION, MAJOR_TAG, MINOR_TAG, CUSTOM_TAG) VALUES (178060100000331, 178060100000207, 178060100000322, '见习中', '2026-05-01', '2026-07-31', '已通过', '符合见习条件，审核通过', '1', '2', NULL); INSERT INTO internship_personnel (ID, PERSONAL_ID, INTERNSHIP_POST_ID, INTERNSHIP_STATUS, START_DATE, END_DATE, AUDIT_STATUS, AUDIT_OPINION, MAJOR_TAG, MINOR_TAG, CUSTOM_TAG) VALUES (178060100000332, 178060100000208, 178060100000321, '已期满', '2025-12-01', '2026-05-31', '已通过', '符合见习条件，审核通过', '1', '1', NULL); INSERT INTO internship_personnel (ID, PERSONAL_ID, INTERNSHIP_POST_ID, INTERNSHIP_STATUS, START_DATE, END_DATE, AUDIT_STATUS, AUDIT_OPINION, MAJOR_TAG, MINOR_TAG, CUSTOM_TAG) VALUES (178060100000333, 178060100000206, 178060100000323, '见习中', '2026-03-15', '2026-09-14', '已通过', '计算机专业背景，推荐到软件测试岗位见习', '1', '1', NULL); INSERT INTO internship_personnel (ID, PERSONAL_ID, INTERNSHIP_POST_ID, INTERNSHIP_STATUS, START_DATE, END_DATE, AUDIT_STATUS, AUDIT_OPINION, MAJOR_TAG, MINOR_TAG, CUSTOM_TAG) VALUES (178060100000334, 178060100000207, 178060100000324, '待审核', NULL, NULL, '待审核', NULL, '1', '2', NULL);