EmploymentSite/doc/待更新脚本

--2024-02-22
ALTER TABLE pc_post RENAME COLUMN trailtime TO trailMonths

--2024-04-10
alter table pc_company add column Longitude varchar(50)
alter table pc_company add column Latitude varchar(50)

-- 2024.4.16
delete from sys_dictionary_item where DictionaryCode='DataRange';

INSERT INTO sys_dictionary_item (DictionaryItemID, Code, DictionaryCode, Value, Name, OrderNo, RecordStatus, IsEditable) VALUES ('f06c44b6-a77a-11ed-a6c5-7085c2a9999e', '', 'DataRange', 0, '全部', 0, 1, 0);
INSERT INTO sys_dictionary_item (DictionaryItemID, Code, DictionaryCode, Value, Name, OrderNo, RecordStatus, IsEditable) VALUES ('f06cf371-a77a-11ed-a6c5-7085c2a9999e', '', 'DataRange', 1, '市', 10, 1, 0);
INSERT INTO sys_dictionary_item (DictionaryItemID, Code, DictionaryCode, Value, Name, OrderNo, RecordStatus, IsEditable) VALUES ('f06cf371-a77a-11ed-a6c5-7185c2a9999e', '', 'DataRange', 2, '区', 20, 1, 0);
INSERT INTO sys_dictionary_item (DictionaryItemID, Code, DictionaryCode, Value, Name, OrderNo, RecordStatus, IsEditable) VALUES ('f06cf371-a77a-11ed-a6c5-7285c2a9999e', '', 'DataRange', 3, '机构', 30, 1, 0);
INSERT INTO sys_dictionary_item (DictionaryItemID, Code, DictionaryCode, Value, Name, OrderNo, RecordStatus, IsEditable) VALUES ('f06cf371-a77a-11ed-a6c5-7385c2a9999e', '', 'DataRange', 4, '驿站', 40, 1, 0);
INSERT INTO sys_dictionary_item (DictionaryItemID, Code, DictionaryCode, Value, Name, OrderNo, RecordStatus, IsEditable) VALUES ('f06cf371-a77a-11ed-a6c5-7485c2a9999e', '', 'DataRange', 5, '企业', 50, 1, 0);
INSERT INTO sys_dictionary_item (DictionaryItemID, Code, DictionaryCode, Value, Name, OrderNo, RecordStatus, IsEditable) VALUES ('f06cf371-a77a-11ed-a6c5-7585c3a9999e', '', 'DataRange', 6, '本人', 60, 1, 0);


-- 2024.4.17
insert sys_dictionary values('NationType','民族',89,1);
insert sys_dictionary_item values(UUID(),'','NationType',1,'汉族',1,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',2,'蒙古族',2,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',3,'回族',3,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',4,'藏族',4,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',5,'维吾尔族',5,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',6,'苗族',6,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',7,'彝族',7,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',8,'壮族',8,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',9,'布依族',9,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',10,'朝鲜族',10,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',11,'满族',11,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',12,'侗族',12,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',13,'瑶族',13,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',14,'白族',14,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',15,'土家族',15,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',16,'哈尼族',16,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',17,'哈萨克族',17,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',18,'傣族',18,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',19,'黎族',19,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',20,'傈傈族',20,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',21,'佤族',21,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',22,'畲族',22,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',23,'高山族',23,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',24,'拉祜族',24,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',25,'水族',25,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',26,'东乡族',26,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',27,'纳西族',27,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',28,'景颇族',28,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',29,'柯尔克族',29,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',30,'土族',30,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',31,'达斡尔族',31,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',32,'仫佬族',32,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',33,'羌族',33,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',34,'布朗族',34,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',35,'撒拉族',35,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',36,'毛难族',36,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',37,'仡佬族',37,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',38,'锡伯族',38,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',39,'阿昌族',39,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',40,'普米族',40,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',41,'塔吉克族',41,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',42,'怒族',42,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',43,'乌孜别克',43,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',44,'俄罗斯族',44,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',45,'鄂温克族',45,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',46,'崩龙族',46,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',47,'保安族',47,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',48,'裕固族',48,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',49,'京族',49,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',50,'塔塔尔族',50,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',51,'独龙族',51,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',52,'鄂伦春族',52,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',53,'赫哲族',53,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',54,'门巴族',54,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',55,'珞巴族',55,1,1);
insert sys_dictionary_item values(UUID(),'','NationType',56,'基诺族',56,1,1);

-- 2024.4.17
alter table pc_site_user add UserNo varchar(50) comment '工号';
alter table pc_site_user add IDCard varchar(100) comment '身份证号';

--2024.4.18
insert into sys_dictionary values('CompanyType','企业类型',53,1);
INSERT into sys_dictionary_item (`DictionaryItemID`, `Code`, `DictionaryCode`, `Value`, `Name`, `OrderNo`, `RecordStatus`, `IsEditable`)
VALUES
(UUID(), '', 'CompanyType', 1, '国有企业', 1, 1, 0),
(UUID(), '', 'CompanyType', 2, '联营企业', 2, 1, 0),
(UUID(), '', 'CompanyType', 3, '有限责任公司', 3, 1, 0),
(UUID(), '', 'CompanyType', 4, '股份有限公司', 4, 1, 0),
(UUID(), '', 'CompanyType', 5, '私营企业', 5, 1, 0),
(UUID(), '', 'CompanyType', 6, '中外合资企业', 6, 1, 0),
(UUID(), '', 'CompanyType', 7, '外资企业', 7, 1, 0);


-- 2024.4.18
alter table pc_company add openId varchar(300) comment '微信openid';
alter table pc_jobuser add openId varchar(300) comment '微信openid';
alter table pc_recommend_mgt add isSendCompany bit comment '是否推送给公司';
alter table pc_recommend_mgt add isSendUser bit comment '是否推送给求职人员';

create table wx_messageStatus
(
    messageStatusId      varchar(50) not null comment '唯一ID',
    openId               varchar(300) comment '微信openId',
    sourceId             varchar(50) comment '业务ID',
    content              text comment '推送内容',
    url                  varchar(1000),
    sendTime             datetime comment '推送时间',
    recordStatus         int comment '状态，0未推送，1已推送',
    createUserID         varchar(50) comment '创建人',
    createTime           datetime comment '创建时间',
    modifyUserID         varchar(50) comment '修改人',
    modifyTime           datetime comment '修改时间',
    primary key (messageStatusId)
);


create table wx_messageTempSetting
(
    templateNo           varchar(10) not null comment '自编唯一',
    templateName         varchar(50) comment '自编名称',
    templateId           varchar(300) comment '微信公众号生成',
    url                  varchar(300) comment '手机端路由地址，如/login?userId=234',
    primary key (templateNo)
);

create table wx_messageTempSetting_field
(
    templateNo           varchar(10) not null comment '自编唯一',
    wxFieldName          varchar(30) not null comment '微信字段名称',
    sysFieldName         varchar(30) comment '系统字段名称',
    primary key (templateNo, wxFieldName)
);

alter table wx_messageTempSetting_field add constraint FK_Reference_26 foreign key (templateNo)
    references wx_messageTempSetting (templateNo) on delete restrict on update restrict;


insert into wx_messageTempSetting
values('toCompany','应聘人员信息通知','-R2xeAeey7rOKN5VAuUr8cFMsJFlFp7ksN7m6H5_5VU','/wxMessage/jobUserInfo');
insert into wx_messageTempSetting
values('toUser','岗位信息通知','hSE-nWMrspk2uHlJ9BahP4FJo9nCOUpnQiMAOQC2a6k','/wxMessage/companyInfo');

insert into wx_messageTempSetting_field
values('toCompany','thing28','userName'),('toCompany','phone_number34','usermobile'),('toCompany','thing10','userHuntPostName');
insert into wx_messageTempSetting_field
values('toUser','thing5','companyname'),('toUser','thing4','postName'),('toUser','character_string2','companymobile');

--2024-04-23
 alter table pc_profession modify ProfessionName varchar(50)
 alter table pc_company alter column  companyType int

 -- 2023.4.24
 create table sys_user_datarange
 (
     userId               varchar(50) not null comment '用户ID',
     datarangeId          varchar(50) not null comment '结合用户类型区分区ID或驿站ID',
     primary key (userId, datarangeId)
 );

 alter table sys_user_datarange comment '用户数据权限表，结合用户类型区分区ID或驿站ID';