| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162 |
- -- ============================================
- -- 教职工信息表（从东软 RYJCSJXXZL_4 视图同步）
- -- 单独存储7000+教职工数据，不放入 sys_user
- -- 匹配成功后再创建系统用户并分配学校管理员角色
- -- ============================================
- drop table if exists complaint_staff;
- create table complaint_staff (
- id bigint not null IDENTITY(1,1),
- u_number varchar(64) default '',
- id_number varchar(64) default '',
- user_uid varchar(64) default '',
- user_name varchar(50) default '',
- user_alias varchar(50) default '',
- user_sex varchar(4) default '',
- mobile varchar(20) default '',
- email varchar(100) default '',
- unit_uid varchar(64) default '',
- unit_name varchar(100) default '',
- id_type varchar(10) default '',
- id_type_name varchar(20) default '',
- is_active varchar(4) default '',
- is_active_name varchar(20) default '',
- belong_school varchar(100) default '',
- belong_class varchar(100) default '',
- begin_time varchar(20) default '',
- end_time varchar(20) default '',
- data_update_time timestamp,
- data_update_flog varchar(4) default '',
- sync_time timestamp,
- create_by varchar(64) default '',
- create_time timestamp,
- update_by varchar(64) default '',
- update_time timestamp,
- remark varchar(500) default '',
- primary key (id)
- );
- COMMENT ON TABLE complaint_staff IS '教职工信息表（东软同步，匹配后创建系统用户）';
- COMMENT ON COLUMN complaint_staff.u_number IS 'U_NUMBER（东软人员编号）';
- COMMENT ON COLUMN complaint_staff.id_number IS '学工号/ID_NUMBER（公众号推送匹配关键字段）';
- COMMENT ON COLUMN complaint_staff.user_uid IS '用户UID';
- COMMENT ON COLUMN complaint_staff.user_name IS '姓名';
- COMMENT ON COLUMN complaint_staff.user_alias IS '用户别名（身份证号）';
- COMMENT ON COLUMN complaint_staff.user_sex IS '性别（1男2女）';
- COMMENT ON COLUMN complaint_staff.mobile IS '手机号（匹配条件之一）';
- COMMENT ON COLUMN complaint_staff.email IS '邮箱';
- COMMENT ON COLUMN complaint_staff.unit_uid IS '学校机构UID';
- COMMENT ON COLUMN complaint_staff.unit_name IS '学校名称（匹配条件之一）';
- COMMENT ON COLUMN complaint_staff.id_type IS '身份类型编码';
- COMMENT ON COLUMN complaint_staff.id_type_name IS '身份类型名称（教职工/学生等）';
- COMMENT ON COLUMN complaint_staff.is_active IS '是否激活（1激活0未激活）';
- COMMENT ON COLUMN complaint_staff.is_active_name IS '激活状态名称';
- COMMENT ON COLUMN complaint_staff.belong_school IS '所属学校';
- COMMENT ON COLUMN complaint_staff.belong_class IS '所属班级';
- COMMENT ON COLUMN complaint_staff.begin_time IS '生效开始时间';
- COMMENT ON COLUMN complaint_staff.end_time IS '生效结束时间';
- COMMENT ON COLUMN complaint_staff.data_update_time IS '东软数据更新时间';
- COMMENT ON COLUMN complaint_staff.data_update_flog IS '增量标识（I新增U更新D删除）';
- COMMENT ON COLUMN complaint_staff.sync_time IS '本地同步时间';
- create index idx_staff_mobile on complaint_staff (mobile);
- create index idx_staff_unit on complaint_staff (unit_uid);
- create index idx_staff_name on complaint_staff (user_name);
- create index idx_staff_idno on complaint_staff (id_number);
|
