/** 重复数据的异常处理 INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.INDEXCODE,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,CONCAT(t.`NAME`,'|',t.ID) , null as permission_config,0,1,0,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is null and t.ID not in (select ID from obpm2.tenant_departments); select t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.INDEXCODE,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.`NAME`, null as permission_config,0,1,0,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.ID not in (select ID from obpm2.tenant_departments); INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.INDEXCODE,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,CONCAT(t.`NAME`,'|',t.ID) , null as permission_config,0,1,0,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.ID not in (select ID from obpm2.tenant_departments); select t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.INDEXCODE,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.`NAME`, null as permission_config,0,1,0,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.ID not in (select ID from obpm2.tenant_departments) */ update t_user set FIELD29=SUPERIOR; update t_user set SUPERIOR=null; INSERT INTO `obpm2`.`tenant_employees`( `id`, `created_time`, `email`, `hired_date`, `job_number`, `last_updated_time`, `leave_note`, `leave_operator_created_time`, `leave_operator_id`, `leave_operator_name`, `leave_time`, `master_slave_type`, `occupation_type`, `position`, `positive_date`, `salary`, `salary_bank_number`, `status`, `work_place`, `organization_id`, `superior_id`, `tenant_user_id`, `unique_offset`, `department_id`) select a.id,IFNULL(a.LASTMODIFYTIME,'2021-01-01'),a.EMAIL, b.ITEM_entryDate,null as job_number,IFNULL(a.LASTMODIFYTIME,'2021-01-01'), null as leave_note,null as leave_operator_created_time,null as leave_operator_id,null as leave_operator_name, b.ITEM_leaveDate as leave_time,0, (case when IFNULL(ITEM_jobType,3)=3 THEN 0 ELSE 1 END) as occupation_type,'' as position, b.ITEM_entryDate as positive_date, null as salary, null as salary_bank_number, (case when (select b1.ITEM_isInMyCompany from baibaodunflow.tlk_securityman b1 join obpm2.t_user a1 on a1.FIELD12=b1.ITEM_documentID where a1.ID=a.id limit 1) then 1 else 0 end)as `status`, null as work_place, a.DOMAINID,a.SUPERIOR as superior_id, (select c.id from tenant_users c where c.telephone=a.LOGINNO limit 1), null,a.DEFAULTDEPARTMENT from t_user a left join baibaodunflow.tlk_securityman b on a.FIELD12=b.ITEM_documentID and b.DOMAINID= a.DOMAINID join tenant_organizations c on c.ID=a.DOMAINID where a.ID not in (select k.id from tenant_employees k) and a.DEFAULTDEPARTMENT in (select id from tenant_departments) ; INSERT INTO `obpm2`.`tenant_employees`( `id`, `created_time`, `email`, `hired_date`, `job_number`, `last_updated_time`, `leave_note`, `leave_operator_created_time`, `leave_operator_id`, `leave_operator_name`, `leave_time`, `master_slave_type`, `occupation_type`, `position`, `positive_date`, `salary`, `salary_bank_number`, `status`, `work_place`, `organization_id`, `superior_id`, `tenant_user_id`, `unique_offset`, `department_id`) select a.id,IFNULL(a.LASTMODIFYTIME,'2021-01-01'),a.EMAIL, b.ITEM_entryDate,null as job_number,IFNULL(a.LASTMODIFYTIME,'2021-01-01'), null as leave_note,null as leave_operator_created_time,null as leave_operator_id,null as leave_operator_name, b.ITEM_leaveDate as leave_time,0, (case when IFNULL(ITEM_jobType,3)=3 THEN 0 ELSE 1 END) as occupation_type,'' as position, b.ITEM_entryDate as positive_date, null as salary, null as salary_bank_number, (case when (select b1.ITEM_isInMyCompany from baibaodunflow.tlk_securityman b1 join obpm2.t_user a1 on a1.FIELD12=b1.ITEM_documentID where a1.ID=a.id limit 1) then 1 else 0 end)as `status`, null as work_place, a.DOMAINID,a.SUPERIOR as superior_id,(select c.id from tenant_users c where c.telephone=a.LOGINNO limit 1), null,a.DEFAULTDEPARTMENT from t_user a left join baibaodunflow.tlk_securityman b on a.FIELD12=b.ITEM_documentID and b.DOMAINID= a.DOMAINID join tenant_organizations c on c.ID=a.DOMAINID where a.ID not in (select k.id from tenant_employees k) and a.DEFAULTDEPARTMENT is null; /** 无效数据 */ select * from t_user where id not in (select k.id from tenant_employees k) and DOMAINID in (select id from t_domain); /** 改回去 */ update tenant_employees x, t_user z set x.superior_id=z.FIELD29 where x.id=z.ID; update tenant_employees e, t_user t set e.tenant_user_id =(select h.id from tenant_users h where h.telephone=t.LOGINNO limit 1) where e.id=t.id and e.tenant_user_id not in (select id from tenant_users); INSERT INTO `obpm2`.`tenant_department_employee_relations`(`id`, `created_time`, `master_slave_type`, `department_id`, `employee_id`) select maxID,'2021-01-01',2,DEPARTMENTID,USERID from ( select max(z.ID) as maxID, z.DEPARTMENTID, z.USERID, count(1) as ct from t_user_department_role_set z where z.DEPARTMENTID in (select id from t_department) and z.USERID in (select o.ID from t_user o) group by DEPARTMENTID, USERID ) p where maxID not in (select id from tenant_department_employee_relations) and USERID not in (select employee_id from tenant_department_employee_relations) update tenant_employees k set k.department_id=(select c.department_id from tenant_department_employee_relations c where c.employee_id=k.id limit 1); update tenant_employees k set k.department_id=(select c.department_id from tenant_department_employee_relations c where c.employee_id=k.id limit 1) where k.department_id is null; /** 导入部门管理员 */ INSERT INTO `obpm2`.`tenant_department_admins`(`id`, `created_time`, `department_id`, `employee_id`, `organization_id`) select id,'2021-01-01',DEPARTMENTID,USERID,(select u.DOMAINID from t_user u where ID=t.USERID limit 1) from t_user_department_admin_set t where t.ID not in (select l.id from tenant_department_admins l) and t.USERID is not null and USERID in (select id from tenant_employees) and DEPARTMENTID in (select ID from tenant_departments); /** 确认离职状态 */ update tenant_employees set `status`=0; update tenant_employees set `status`=1 where id in (select t.id from t_user t join baibaodunflow.tlk_securityman s on t.FIELD12=s.ITEM_documentID where IFNULL(s.ITEM_leaveDate,'2052-01-01')<>'2052-01-01'); /** 授权应用 */ update t_domain set BIND_APPLICATIONS=REPLACE(BIND_APPLICATIONS,']',',"__xkeybkGaZa1SboC9GL1"]') where BIND_APPLICATIONS not like '%__xkeybkGaZa1SboC9GL1%' and LENGTH(BIND_APPLICATIONS)>5; update t_domain set BIND_APPLICATIONS=REPLACE(BIND_APPLICATIONS,']','"__xkeybkGaZa1SboC9GL1"]') where BIND_APPLICATIONS not like '%__xkeybkGaZa1SboC9GL1%' and LENGTH(BIND_APPLICATIONS)>1; update t_domain set BIND_APPLICATIONS='["__xkeybkGaZa1SboC9GL1"]' where BIND_APPLICATIONS is null; /** 统计部门 */ update tenant_departments x set total_member=(select count(1) from tenant_department_employee_relations c where c.department_id=x.id); /** 证书信息 */ INSERT INTO `obpm2`.`tenant_user_credentials`(`id`, `created_time`, `address`, `credential_type`, `front_photo`, `number`, `reverse_photo`, `valid_date_from`, `valid_date_to`, `tenant_user_id`, `head_photo`, `name`, `selected`) select mId,mct,address,credential_type,front_photo,number, reverse_photo,valid_date_from,valid_date_to,mtenant_user_id,head_photo,`NAME`, selected from ( select min(x.id) as mId,min(x.created_time) as mct,MAX(x.address) AS address,x.credential_type,MAX(x.front_photo) AS front_photo,x.number, MAX(x.reverse_photo) AS reverse_photo, MAX(x.valid_date_from) AS valid_date_from, MAX(x.valid_date_to) AS valid_date_to,min(x.tenant_user_id) as mtenant_user_id, MAX(x.head_photo) AS head_photo,max(x.`NAME`) as `NAME`,x.selected from ( select t.id,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as created_time, IFNULL(t.FIELD14,'') as address, (case t.FIELD11 when '居民身份证（户口簿）' then 0 when '居民身份证' then 0 when '警号' then 6 when '香港特区护照/身份证' then 1 when '香港特区护照/身份证明' then 1 when '澳门特区护照/身份证' then 2 when '澳门特区护照/身份证明' then 2 when '台湾居民来往大陆通行证' then 3 when '外国护照' then 4 when '退伍军人证' then 5 else -1 end)as credential_type, IFNULL(t.FIELD18,'') as front_photo,IFNULL(t.FIELD12,'')as number, IFNULL(t.FIELD19,'') as reverse_photo, IFNULL(t.FIELD16,'') as valid_date_from,IFNULL(t.FIELD17,'') as valid_date_to, (select k.id from tenant_users k where k.telephone=t.loginno limit 1) as tenant_user_id, IFNULL(t.FIELD15,'') as head_photo, t.`NAME` ,true as selected from t_user t where t.FIELD11 is not null ) as x where x.id not in (select id from tenant_user_credentials) and x.credential_type>-1 and IFNULL(x.number,'')<>'' and number not in (select number from tenant_user_credentials) group by credential_type,number ) as hg ; UPDATE tenant_users x set selected_credential_id=(select k.id from tenant_user_credentials k where k.tenant_user_id=x.id limit 1); /** 配置组织管理员 */ update tenant_employees set is_domain_admin=1 where id in (select id from t_user u where u.ISDOMAINUSER='true'); update tenant_employees set is_domain_admin=1 where `position` in ('系统管理员','总经理','副总经理'); /** 设置无效数据 */ update tenant_user_credentials set valid_date_from='1900-01-01' where valid_date_from='0000-00-00'; update tenant_user_credentials set valid_date_to='3050-01-01' where valid_date_to='0000-00-00'; /** 合同 */ INSERT INTO `obpm2`.`tenant_contracts`(`id`, `creator_created_time`, `creator_id`, `creator_name`, `last_modifier_created_time`, `last_modifier_id`, `last_modifier_name`, `a_name`, `attachment`, `b_name`, `begin_date`, `date_limit_less`, `end_date`, `name`, `note`, `status`, `status_note`, `employee_id`, `organization_id`) select id,creator_created_time,creator_id,creator_name,last_modifier_created_time,last_modifier_id,last_modifier_name, ITEM_PARTYANAME,ITEM_CONTRACTFILE,ITEM_PARTYBNAME,ITEM_CONTRACTSTARTDATE,date_limit_less,ITEM_CONTRACTENDDATE, ITEM_CONTRACTNAME,note, `status`,status_note,employee_id,DOMAINID from ( select id, IFNULL(LASTMODIFIED,'2021-01-01') as creator_created_time, null as creator_id,null as creator_name,null as last_modifier_created_time,null as last_modifier_id,null as last_modifier_name, ITEM_PARTYANAME, ITEM_CONTRACTFILE, ITEM_PARTYBNAME, ITEM_CONTRACTSTARTDATE, (case ITEM_ISNOTENDDATE when 1 then 0 else 1 end) as date_limit_less, ITEM_CONTRACTENDDATE, ITEM_CONTRACTNAME, '' as note, (case ITEM_CONTRACTSTATE when '有效' then 1 else 0 end) as `status`, '' as status_note, (select j.ID from obpm2.tenant_users x join obpm2.tenant_employees j on x.id=j.tenant_user_id join obpm2.tenant_user_credentials d on d.id=x.selected_credential_id where j.organization_id=c.DOMAINID and d.number in (select x.ITEM_documentID from baibaodunflow.tlk_securityman x where x.id=c.ITEM_SECURITYID) limit 1) as employee_id, c.DOMAINID from baibaodunflow.tlk_contracttoman c where c.ID not in (select id from obpm2.tenant_contracts) ) as k where employee_id in (select id from tenant_employees); /** 替换合同格式 */ update obpm2.tenant_contracts set attachment=REPLACE(SUBSTR(attachment,LOCATE( '"path":"', attachment)+8,(LOCATE('"url":', attachment)-LOCATE( '"path":"', attachment)-8-2)),'\\','') where attachment like '[{%'; /** 默认授权智能人事 */ /* insert into t_user_department_role_set(id,DEPARTMENTID,USERID,ROLEID) select CONCAT(id,'_role'), (select k.department_id from tenant_department_employee_relations k where k.employee_id=u.id limit 1) as DepartId, id as userId,'__2uz0l9F3qiXuMQZmma0' from tenant_employees u where u.organization_id in (select id from t_domain d where d.BIND_APPLICATIONS like '%__xkeybkGaZa1SboC9GL1%' ) and u.id not in (select p.USERID from t_user_department_role_set p where p.ROLEID='__2uz0l9F3qiXuMQZmma0') */ /** 设备ID */ update tenant_users x set cid=(select k.FIELD24 from t_user k where k.id=x.id limit 1), wechat=(select k.field22 from t_user k where k.id=x.id limit 1); update tenant_companies x, t_domain j set x.unify_social_credit_code=j.FIELD11,x.unify_social_credit_code_file=j.FIELD16 where x.id=j.ID; update tenant_organizations k, t_domain j set k.lon_lat_json =j.field17 where k.id=j.ID and k.lon_lat_json is null; update tenant_organizations x,t_domain k set x.place_of_business_address=k.FIELD13, x.place_of_business_province_code=SUBSTRING_INDEX(FIELD13,',',1), x.place_of_business_province_name= SUBSTRING_INDEX(FIELD13,',',1), x.place_of_business_city_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1), x.place_of_business_city_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1), x.place_of_business_district_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1), x.place_of_business_district_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1), x.place_of_register_address = k.FIELD13, x.place_of_register_province_code = SUBSTRING_INDEX(FIELD12,',',1), x.place_of_register_province_name = SUBSTRING_INDEX(FIELD12,',',1), x.place_of_register_city_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) , x.place_of_register_city_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1), x.place_of_register_district_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1), x.place_of_register_district_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1), x.industry_code=k.FIELD14, x.institutional_code=k.FIELD15 where x.id=k.ID and x.place_of_business_address is null; update tenant_organizations set approved_information_status=1 where `name` not like '%all%' update tenant_users x ,tenant_user_credentials z set x.selected_credential_id=z.id where z.tenant_user_id=x.id; /** 一寸免冠照片 */ update tenant_users x, t_user z set x.one_inch_color_white_photo=z.field20 where x.one_inch_color_white_photo is null and x.id=z.ID; /** 多个身份证绑定不同手机号码的情况 */ INSERT INTO `obpm2`.`tenant_user_credentials`(`id`, `created_time`, `address`, `credential_type`, `front_photo`, `number`, `reverse_photo`, `valid_date_from`, `valid_date_to`, `tenant_user_id`, `head_photo`, `name`, `selected`) select t.id,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as created_time, IFNULL(t.FIELD14,'') as address, (case t.FIELD11 when '居民身份证（户口簿）' then 0 when '居民身份证' then 0 when '警号' then 6 when '香港特区护照/身份证' then 1 when '香港特区护照/身份证明' then 1 when '澳门特区护照/身份证' then 2 when '澳门特区护照/身份证明' then 2 when '台湾居民来往大陆通行证' then 3 when '外国护照' then 4 when '退伍军人证' then 5 else -1 end)as credential_type, IFNULL(t.FIELD18,'') as front_photo,CONCAT(IFNULL(t.FIELD12,''),'&',t.LOGINNO) as number, IFNULL(t.FIELD19,'') as reverse_photo, IFNULL(t.FIELD16,'') as valid_date_from,IFNULL(t.FIELD17,'') as valid_date_to, (select k.id from tenant_users k where k.telephone=t.loginno limit 1) as tenant_user_id, IFNULL(t.FIELD15,'') as head_photo, t.`NAME` ,true as selected from tenant_users x join t_user t on x.id=t.ID where t.ID not in (select p.tenant_user_id from tenant_user_credentials p ); update tenant_users x,tenant_user_credentials k SET x.selected_credential_id=k.id where x.id=k.id and x.selected_credential_id is null; update tenant_users x set x.selected_credential_id=null where x.selected_credential_id in (select id from tenant_user_credentials where number like '&%'); delete from tenant_user_credentials where number like '&%'; select CONCAT('update tenant_user_credentials set number=SUBSTRING_INDEX(number,''&'',1) where id=''',id,''';') as sqlx, number,id,SUBSTRING_INDEX(number,'&',1) from tenant_user_credentials where number like '%&%' and tenant_user_id in (select id from tenant_users x) order by number asc; select sqlx,ct from ( SELECT sqlx,number,id,kn,count(1) as ct from ( select CONCAT('update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,''&'',1),''.'') where id=''',id,''';') as sqlx, number,id,SUBSTRING_INDEX(number,'&',1) as kn from tenant_user_credentials where number like '%&%' and tenant_user_id in (select id from tenant_users x) ) as k group by kn ) as lk where lk.ct=1; select sqlx,ct from ( SELECT sqlx,number,id,kn,count(1) as ct from ( select CONCAT('update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,''&'',1),''..'') where id=''',id,''';') as sqlx, number,id,SUBSTRING_INDEX(number,'&',1) as kn from tenant_user_credentials where number like '%&%' and tenant_user_id in (select id from tenant_users x) ) as k group by kn ) as lk where lk.ct=2; select sqlx,ct from ( SELECT sqlx,number,id,kn,count(1) as ct from ( select CONCAT('update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,''&'',1),''...'') where id=''',id,''';') as sqlx, number,id,SUBSTRING_INDEX(number,'&',1) as kn from tenant_user_credentials where number like '%&%' and tenant_user_id in (select id from tenant_users x) ) as k group by kn ) as lk where lk.ct=3; select sqlx,ct from ( SELECT sqlx,number,id,kn,count(1) as ct from ( select CONCAT('update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,''&'',1),''...'') where id=''',id,''';') as sqlx, number,id,SUBSTRING_INDEX(number,'&',1) as kn from tenant_user_credentials where number like '%&%' and tenant_user_id in (select id from tenant_users x) ) as k group by kn ) as lk where lk.ct=3; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'._') where id='TrfWFROzCBoaaNvDtaQ'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'__') where id='vRo7ABJdL7sNl9VPM15'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'..._') where id='Yk7sCYkL9i1KvsLhoV2'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'...__') where id='ZYhiU1OBxyBeadIqrWR'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'__.') where id='__REfy8WnFNVXLHJ5OkJ7'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'__..') where id='__rSWnOXwBxgCzaYyIC8n'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'___') where id='__S4usvWwTnKRp1WtQmIE'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'____') where id='__SCFXXj3ynQpO2t6ryL0'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'_ _') where id='__t9crbmBCwsDZSRWCeYn'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'. _') where id='__vsA6GfXEwpuDJDAeHE0'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'. .') where id='__W6WadyCQEXdIOXVkPoF'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'.._') where id='__WA4VOqYpxQvgW5C91CS'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'_..') where id='__X8W6wWaym0gNXcYKbBO'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'._.') where id='__XnNSkDYrxkBMhkziarr'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'.._.') where id='__YAgOKEgSvLAYruP9SP3'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'..._.') where id='__yHvvrKz9iqGWYDvC51c'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'._...') where id='__z28e2xFSavhlVYRbR0Z'; update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'_....') where id='__ZUlmVzUBMmtv9KH2Xph'; update tenant_organizations k, t_domain j set lon_lat_json =j.field17 where k.id=j.ID and k.lon_lat_json is null; SELECT * from (select x.`name`,x.organization_id,COUNT(1) as ct, CONCAT('update tenant_departments set `name`=''',x.`name`,'.'' where id=''',id,'''') as sqxl from tenant_departments x group by x.`name`,x.organization_id ) as k where k.ct=2; UPDATE obpm2.tenant_users t INNER JOIN ( SELECT b.ITEM_birthdate, b.ITEM_education, b.ITEM_householdType, b.ITEM_maritalStatus, b.ITEM_nation, b.ITEM_isVeteran, b.ITEM_politicalOutlook, b.ITEM_sex, b.ITEM_height, c.id FROM obpm2.t_user c LEFT JOIN baibaodunflow.tlk_securityman b ON c.FIELD12 = b.ITEM_documentID AND b.DOMAINID = c.DOMAINID ) f ON f.id = t.id SET t.birthdate = f.ITEM_birthdate, t.education = f.ITEM_education, t.household_type = f.ITEM_householdType, t.marital_status = f.ITEM_maritalStatus, t.nation = f.ITEM_nation, t.military_status = f.ITEM_isVeteran, t.politics_status = f.ITEM_politicalOutlook, t.sex = f.ITEM_sex, t.stature = f.ITEM_height WHERE t.birthdate is null and t.education is null and t.household_type is null and t.marital_status is null and t.nation is null and t.military_status is null and t.politics_status is null and t.stature is null; update tenant_users t,tenant_users f set t.birthdate = f.birthdate, t.education = f.education, t.household_type = f.household_type, t.marital_status = f.marital_status, t.nation = f.nation, t.military_status = f.military_status, t.politics_status = f.politics_status, t.sex = f.sex, t.stature = f.stature where t.nation is null and t.stature is null and t.id in (select ct.id from tenant_user_credentials ct JOIN tenant_user_credentials ct2 on ct.number like CONCAT(ct2.number,'%') where ct.tenant_user_id=t.id and ct2.tenant_user_id=f.id ); insert into identity_wechat (id,nicky,`open_id`,identity_user_id) SELECT id,createTime,active,identity_user_id from ( select id,createTime,active,(select k.id from identity_user k where k.telephone=m.mobilePhone limit 1) as identity_user_id from t_wechat_user m) as l where l.identity_user_id is not null; insert into identity_wechat (id,nicky,`open_id`) SELECT id,NULL,openid from ( select id,createTime,active,openid,(select k.id from identity_user k where k.telephone=m.mobilePhone limit 1) as identity_user_id from t_wechat_user m) as l where l.identity_user_id is not null; update identity_wechat x, (select field22,m.telephone,k.id from t_user f join identity_user m on f.LOGINNO=m.telephone join identity_principals k on k.identity_user_id=m.ID) y set nicky=field22 where x.id=y.id; update tenant_users x, (select field22,m.telephone,k.id,m.tenant_user_id from t_user f join identity_user m on f.LOGINNO=m.telephone join identity_principals k on k.identity_user_id=m.ID) y set wechat=field22 where x.id=y.tenant_user_id; update tenant_user_credentials set credential_type=2 where credential_type=-1; /** 组织管理员 */ update tenant_employees set is_domain_admin=1 where id in (select id from t_user u where u.ISDOMAINUSER='true'); update tenant_employees set is_domain_admin=1 where `position` in ('系统管理员','总经理','副总经理'); insert into tenant_department_employee_relations(id,created_time,master_slave_type,department_id,employee_id) select CONCAT(x.id,d.id),CURRENT_TIMESTAMP,2,x.id,d.id from tenant_departments x join tenant_employees d on x.id=d.department_id where CONCAT(x.id,d.id) not in (select CONCAT(t.department_id,t.employee_id) from tenant_department_employee_relations t); /** 部门用户关系 */ INSERT INTO `obpm2`.`tenant_department_employee_relations`(`id`, `created_time`, `master_slave_type`, `department_id`, `employee_id`) select maxID,'2021-01-01',2,DEPARTMENTID,USERID from ( select max(z.ID) as maxID,z.DEPARTMENTID,z.USERID,count(1) as ct from t_user_department_role_set z join tenant_employees e on z.USERID=e.id group by z.DEPARTMENTID,z.USERID ) as o where ct=1 and CONCAT(DEPARTMENTID,USERID) not in (select CONCAT(department_id,employee_id) from tenant_department_employee_relations) and DEPARTMENTID in (select id from tenant_departments) and maxID not in (select id from tenant_department_employee_relations) ; INSERT INTO `obpm2`.`tenant_department_employee_relations`(`id`, `created_time`, `master_slave_type`, `department_id`, `employee_id`) select maxID,'2021-01-01',2,DEPARTMENTID,USERID from ( select max(z.ID) as maxID,z.DEPARTMENTID,z.USERID,count(1) as ct from t_user_department_role_set z join tenant_employees e on z.USERID=e.id group by z.DEPARTMENTID,z.USERID ) as o where ct>1 and CONCAT(DEPARTMENTID,USERID) not in (select CONCAT(department_id,employee_id) from tenant_department_employee_relations) and DEPARTMENTID in (select id from tenant_departments) and maxID not in (select id from tenant_department_employee_relations) ; /** 设置用户的默认部门 */ update tenant_employees k set k.department_id=(select c.department_id from tenant_department_employee_relations c where c.employee_id=k.id limit 1); /** 注意：再次确认入驻状态 */ update tenant_employees z set `status`=0 where id in (select t.id from t_user t join baibaodunflow.tlk_securityman s on t.FIELD12=s.ITEM_documentID where IFNULL(s.ITEM_leaveDate,'2022-01-01')='2022-01-01') or not exists(select t.id from t_user t join baibaodunflow.tlk_securityman s on t.FIELD12=s.ITEM_documentID where t.ID=z.id); update tenant_employees set `status`=1 where id not in (select t.id from t_user t join baibaodunflow.tlk_securityman s on t.FIELD12=s.ITEM_documentID and IFNULL(s.ITEM_leaveDate,'2022-01-01')='2022-01-01'); /** 统计部门人员信息 */ update tenant_departments x set total_member=(select count(1) from tenant_department_employee_relations c where c.department_id=x.id); /* 合同信息 */ INSERT INTO `obpm2`.`tenant_contracts`(`id`, `creator_created_time`, `creator_id`, `creator_name`, `last_modifier_created_time`, `last_modifier_id`, `last_modifier_name`, `a_name`, `attachment`, `b_name`, `begin_date`, `date_limit_less`, `end_date`, `name`, `note`, `status`, `status_note`, `employee_id`, `organization_id`) select id,creator_created_time,creator_id,creator_name,last_modifier_created_time,last_modifier_id,last_modifier_name, ITEM_PARTYANAME,ITEM_CONTRACTFILE,ITEM_PARTYBNAME,ITEM_CONTRACTSTARTDATE,date_limit_less,ITEM_CONTRACTENDDATE, ITEM_CONTRACTNAME,note, `status`,status_note,employee_id,DOMAINID from ( select id, IFNULL(LASTMODIFIED,'2021-01-01') as creator_created_time, null as creator_id,null as creator_name,null as last_modifier_created_time,null as last_modifier_id,null as last_modifier_name, ITEM_PARTYANAME, ITEM_CONTRACTFILE, ITEM_PARTYBNAME, ITEM_CONTRACTSTARTDATE, (case ITEM_ISNOTENDDATE when 1 then 0 else 1 end) as date_limit_less, ITEM_CONTRACTENDDATE, ITEM_CONTRACTNAME, '' as note, (case ITEM_CONTRACTSTATE when '有效' then 1 else 0 end) as `status`, '' as status_note, (select x.id from tenant_employees x join t_user u on x.id=u.ID join baibaodunflow.tlk_securityman m on m.ITEM_documentID=u.FIELD12 where m.ID=c.ITEM_SECURITYID limit 1) as employee_id, c.DOMAINID from baibaodunflow.tlk_contracttoman c where c.ID not in (select id from obpm2.tenant_contracts) ) as k where employee_id in (select id from tenant_employees); update obpm2.tenant_contracts set attachment=REPLACE(SUBSTR(attachment,LOCATE( '"path":"', attachment)+8,(LOCATE('"url":', attachment)-LOCATE( '"path":"', attachment)-8-2)),'\\','') where attachment like '[{%'; /** 配置应用包 */ update tlk_app_package set item_own_apps = CONCAT(item_own_apps,';','__xkeybkGaZa1SboC9GL1') where item_own_apps not like '%__xkeybkGaZa1SboC9GL1%'; /** update t_user set SUPERIOR=FIELD29 where SUPERIOR is null and FIELD29 is not null; update tenant_employees x, t_user y set x.superior_id=y.SUPERIOR where x.superior_id is null */