zjrs-jeecgBoot/.docs/sql/建表语句/招聘会管理-关联表与字段补充.sql

-- ============================================================
-- 招聘会管理模块 - 达梦(DM)数据库变更脚本
-- 日期：2026-06-13
-- 说明：新增招聘会关联表、补充字段、字典数据
-- ============================================================

-- 1. 招聘会管理表新增字段
ALTER TABLE RECRUITMENT_FAIR ADD COLUMN FAIR_DESCRIPTION TEXT;
COMMENT ON COLUMN RECRUITMENT_FAIR.FAIR_DESCRIPTION IS '招聘会描述';
ALTER TABLE RECRUITMENT_FAIR ADD COLUMN FAIR_IMAGE VARCHAR(500);
COMMENT ON COLUMN RECRUITMENT_FAIR.FAIR_IMAGE IS '封面图URL';
ALTER TABLE RECRUITMENT_FAIR ADD COLUMN REGISTRATION_DEADLINE VARCHAR(50);
COMMENT ON COLUMN RECRUITMENT_FAIR.REGISTRATION_DEADLINE IS '报名截止时间';
ALTER TABLE RECRUITMENT_FAIR ADD COLUMN MAX_ENTERPRISES INT;
COMMENT ON COLUMN RECRUITMENT_FAIR.MAX_ENTERPRISES IS '最大参展企业数';
ALTER TABLE RECRUITMENT_FAIR ADD COLUMN MAX_PARTICIPANTS INT;
COMMENT ON COLUMN RECRUITMENT_FAIR.MAX_PARTICIPANTS IS '最大参与人数';
ALTER TABLE RECRUITMENT_FAIR ADD COLUMN CONTACT_PERSON VARCHAR(100);
COMMENT ON COLUMN RECRUITMENT_FAIR.CONTACT_PERSON IS '联系人';
ALTER TABLE RECRUITMENT_FAIR ADD COLUMN CONTACT_PHONE VARCHAR(20);
COMMENT ON COLUMN RECRUITMENT_FAIR.CONTACT_PHONE IS '联系电话';
ALTER TABLE RECRUITMENT_FAIR ADD COLUMN FAIR_SCHEDULE TEXT;
COMMENT ON COLUMN RECRUITMENT_FAIR.FAIR_SCHEDULE IS '日程安排';

-- 2. 招聘会-企业关联表
CREATE TABLE IF NOT EXISTS RECRUITMENT_FAIR_ENTERPRISE (
    ID VARCHAR(36) NOT NULL,
    CREATE_BY VARCHAR(50),
    CREATE_TIME TIMESTAMP,
    UPDATE_BY VARCHAR(50),
    UPDATE_TIME TIMESTAMP,
    SYS_ORG_CODE VARCHAR(64),
    FAIR_ID VARCHAR(36) NOT NULL,
    ENTERPRISE_ID VARCHAR(36) NOT NULL,
    ENTERPRISE_NAME VARCHAR(200),
    STATUS VARCHAR(2) DEFAULT '0',
    PRIMARY KEY (ID)
);
COMMENT ON TABLE RECRUITMENT_FAIR_ENTERPRISE IS '招聘会-企业关联表';
COMMENT ON COLUMN RECRUITMENT_FAIR_ENTERPRISE.FAIR_ID IS '招聘会ID';
COMMENT ON COLUMN RECRUITMENT_FAIR_ENTERPRISE.ENTERPRISE_ID IS '企业ID';
COMMENT ON COLUMN RECRUITMENT_FAIR_ENTERPRISE.ENTERPRISE_NAME IS '企业名称';
COMMENT ON COLUMN RECRUITMENT_FAIR_ENTERPRISE.STATUS IS '参展状态：0-待审核，1-已通过，2-已拒绝';
CREATE INDEX IDX_RFE_FAIR_ID ON RECRUITMENT_FAIR_ENTERPRISE(FAIR_ID);
CREATE INDEX IDX_RFE_ENT_ID ON RECRUITMENT_FAIR_ENTERPRISE(ENTERPRISE_ID);

-- 3. 招聘会-个人报名表
CREATE TABLE IF NOT EXISTS RECRUITMENT_FAIR_PERSONAL (
    ID VARCHAR(36) NOT NULL,
    CREATE_BY VARCHAR(50),
    CREATE_TIME TIMESTAMP,
    UPDATE_BY VARCHAR(50),
    UPDATE_TIME TIMESTAMP,
    SYS_ORG_CODE VARCHAR(64),
    FAIR_ID VARCHAR(36) NOT NULL,
    PERSONAL_ID VARCHAR(36) NOT NULL,
    PERSONAL_NAME VARCHAR(100),
    CONTACT_PHONE VARCHAR(20),
    STATUS VARCHAR(2) DEFAULT '0',
    PRIMARY KEY (ID)
);
COMMENT ON TABLE RECRUITMENT_FAIR_PERSONAL IS '招聘会-个人报名表';
COMMENT ON COLUMN RECRUITMENT_FAIR_PERSONAL.FAIR_ID IS '招聘会ID';
COMMENT ON COLUMN RECRUITMENT_FAIR_PERSONAL.PERSONAL_ID IS '个人ID';
COMMENT ON COLUMN RECRUITMENT_FAIR_PERSONAL.PERSONAL_NAME IS '个人姓名';
COMMENT ON COLUMN RECRUITMENT_FAIR_PERSONAL.CONTACT_PHONE IS '联系电话';
COMMENT ON COLUMN RECRUITMENT_FAIR_PERSONAL.STATUS IS '报名状态：0-已报名，1-已签到，2-已取消';
CREATE INDEX IDX_RFP_FAIR_ID ON RECRUITMENT_FAIR_PERSONAL(FAIR_ID);
CREATE INDEX IDX_RFP_PER_ID ON RECRUITMENT_FAIR_PERSONAL(PERSONAL_ID);

-- 4. 字典数据 - 招聘会状态
INSERT INTO SYS_DICT (ID, DICT_NAME, DICT_CODE, DESCRIPTION, DEL_FLAG, CREATE_BY, CREATE_TIME, TYPE, TENANT_ID) VALUES ('fair_status_dict', '招聘会状态', 'fair_status', '招聘会状态', 0, 'admin', CURRENT_TIMESTAMP, 0, NULL);
INSERT INTO SYS_DICT_ITEM (ID, DICT_ID, ITEM_TEXT, ITEM_VALUE, DESCRIPTION, SORT_ORDER, STATUS, CREATE_BY, CREATE_TIME) VALUES ('fair_status_0', 'fair_status_dict', '未开始', '0', '', 1, 1, 'admin', CURRENT_TIMESTAMP);
INSERT INTO SYS_DICT_ITEM (ID, DICT_ID, ITEM_TEXT, ITEM_VALUE, DESCRIPTION, SORT_ORDER, STATUS, CREATE_BY, CREATE_TIME) VALUES ('fair_status_1', 'fair_status_dict', '报名中', '1', '', 2, 1, 'admin', CURRENT_TIMESTAMP);
INSERT INTO SYS_DICT_ITEM (ID, DICT_ID, ITEM_TEXT, ITEM_VALUE, DESCRIPTION, SORT_ORDER, STATUS, CREATE_BY, CREATE_TIME) VALUES ('fair_status_2', 'fair_status_dict', '进行中', '2', '', 3, 1, 'admin', CURRENT_TIMESTAMP);
INSERT INTO SYS_DICT_ITEM (ID, DICT_ID, ITEM_TEXT, ITEM_VALUE, DESCRIPTION, SORT_ORDER, STATUS, CREATE_BY, CREATE_TIME) VALUES ('fair_status_3', 'fair_status_dict', '已结束', '3', '', 4, 1, 'admin', CURRENT_TIMESTAMP);

-- 5. 字典数据 - 举办方式
INSERT INTO SYS_DICT (ID, DICT_NAME, DICT_CODE, DESCRIPTION, DEL_FLAG, CREATE_BY, CREATE_TIME, TYPE, TENANT_ID) VALUES ('fair_hold_method_dict', '举办方式', 'fair_hold_method', '招聘会举办方式', 0, 'admin', CURRENT_TIMESTAMP, 0, NULL);
INSERT INTO SYS_DICT_ITEM (ID, DICT_ID, ITEM_TEXT, ITEM_VALUE, DESCRIPTION, SORT_ORDER, STATUS, CREATE_BY, CREATE_TIME) VALUES ('fair_hold_0', 'fair_hold_method_dict', '线上', '0', '', 1, 1, 'admin', CURRENT_TIMESTAMP);
INSERT INTO SYS_DICT_ITEM (ID, DICT_ID, ITEM_TEXT, ITEM_VALUE, DESCRIPTION, SORT_ORDER, STATUS, CREATE_BY, CREATE_TIME) VALUES ('fair_hold_1', 'fair_hold_method_dict', '线下', '1', '', 2, 1, 'admin', CURRENT_TIMESTAMP);
INSERT INTO SYS_DICT_ITEM (ID, DICT_ID, ITEM_TEXT, ITEM_VALUE, DESCRIPTION, SORT_ORDER, STATUS, CREATE_BY, CREATE_TIME) VALUES ('fair_hold_2', 'fair_hold_method_dict', '线上+线下', '2', '', 3, 1, 'admin', CURRENT_TIMESTAMP);

-- 6. 字典数据 - 数据来源
INSERT INTO SYS_DICT (ID, DICT_NAME, DICT_CODE, DESCRIPTION, DEL_FLAG, CREATE_BY, CREATE_TIME, TYPE, TENANT_ID) VALUES ('fair_data_source_dict', '招聘会数据来源', 'fair_data_source', '招聘会数据来源', 0, 'admin', CURRENT_TIMESTAMP, 0, NULL);
INSERT INTO SYS_DICT_ITEM (ID, DICT_ID, ITEM_TEXT, ITEM_VALUE, DESCRIPTION, SORT_ORDER, STATUS, CREATE_BY, CREATE_TIME) VALUES ('fair_source_0', 'fair_data_source_dict', '省系统', '0', '', 1, 1, 'admin', CURRENT_TIMESTAMP);
INSERT INTO SYS_DICT_ITEM (ID, DICT_ID, ITEM_TEXT, ITEM_VALUE, DESCRIPTION, SORT_ORDER, STATUS, CREATE_BY, CREATE_TIME) VALUES ('fair_source_1', 'fair_data_source_dict', '市系统', '1', '', 2, 1, 'admin', CURRENT_TIMESTAMP);