/** 补充tenant_employee到t_user */ INSERT INTO t_user(id,LEVELS,DEFAULTDEPARTMENT,LOGINNO,LOGINPWD,NAME,TELEPHONE,STATUS,DOMAINID,ISDOMAINUSER,ORDERBYNO,FIELD11,FIELD12, DIMISSION,LIAISON_OFFICER,TELEPHONE2,TELEPHONEPUBLIC2,EMAILPUBLIC,USERINFOPUBLIC,TELEPHONEPUBLIC) SELECT a.id,'0',a.department_id,b.TELEPHONE,'invalid',b.`NAME`,b.TELEPHONE,1, a.organization_id,a.is_domain_admin,1000,(case when c.credential_type=0 then '居民身份证/户口簿' else c.credential_type end),c.number, 1,1,1,1,1,1,1 FROM tenant_employees AS a join tenant_users b on a.tenant_user_id=b.id left join tenant_user_credentials AS c on c.credential_type=0 and c.tenant_user_id=b.id WHERE a.id not in(SELECT id FROM t_user); /** 查询: 手动处理名字与现有迁移公司重复的企业名称 */ select o.`name`,CONCAT("update tenant_organizations set name=' ",o.`name`,"' where id='",o.id,"';"),d.`NAME` as name2 from tenant_organizations o left join t_domain d on o.id<>d.ID and o.`name`=d.`NAME` where o.id not like '%--_%' and d.id is not null; /** 执行更改: 手动处理名字与现有迁移公司重复的企业名称 */ /** 补充企业信息 */ INSERT INTO `tenant_organizations`(`id`, `approved_information_last_updated_time`, `approved_information_note`, `approved_information_status`, `code`, `created_time`, `industry_code`, `institutional_code`, `last_updated_time`, `logo_path`, `name`, `place_of_business_address`, `place_of_business_province_code`, `place_of_business_province_name`, `place_of_business_city_code`, `place_of_business_city_name`, `place_of_business_district_code`, `place_of_business_district_name`, `place_of_register_address`, `place_of_register_province_code`, `place_of_register_province_name`,`place_of_register_city_code`, `place_of_register_city_name`, `place_of_register_district_code`, `place_of_register_district_name`, `tenant_user_id`) select id,NULL,'',2,id,'2021-01-01',field14,field15,'2021-01-01', k.LOGO_URL,k.`NAME`, k.FIELD13 as place_of_business_address, SUBSTRING_INDEX(FIELD13,',',1) as place_of_business_province_code, SUBSTRING_INDEX(FIELD13,',',1) as place_of_business_province_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1) as place_of_business_city_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1) as place_of_business_city_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1) as place_of_business_district_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1) as place_of_business_district_name, k.FIELD13 as place_of_register_address, SUBSTRING_INDEX(FIELD12,',',1) as place_of_register_province_code, SUBSTRING_INDEX(FIELD12,',',1) as place_of_register_province_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) as place_of_register_city_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) as place_of_register_city_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1) as place_of_register_district_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1) as place_of_register_district_name, (select t2.id from t_user t2 where t2.DOMAINID=k.id and IFNULL(t2.ISDOMAINUSER,false)='true' limit 1) from t_domain k where k.`NAME`<>'all_users_zone' and k.id not in (select id from tenant_organizations); INSERT INTO `obpm2`.`tenant_companies`(`id`, `unify_social_credit_code`, `unify_social_credit_code_file`) select a.id,b.FIELD11,b.FIELD16 from tenant_organizations a join t_domain b on a.id=b.ID where a.id not in (select id from tenant_companies); /** 补充部门信息 查看部分顶级部门不是企业名称的企业: select * from t_domain d where not exists(select 1 from t_department k where k.domain_id=d.id and k.name=d.name) limit 100; 一级部门 */ 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,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.SUPERIOR is null and t.ID not in (select ID from 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, 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.SUPERIOR in (select id from tenant_departments) and t.ID not in (select ID from tenant_departments); /** 【保安人员】 根据baibaodunflow.tlk_securityman保安员补充t_user 身份证信息是有可能为空的 方式一 */ INSERT INTO t_user(id,LEVELS,DEFAULTDEPARTMENT,LOGINNO,LOGINPWD,NAME,TELEPHONE,STATUS,DOMAINID,ISDOMAINUSER,ORDERBYNO,FIELD11,FIELD12, DIMISSION,LIAISON_OFFICER,TELEPHONE2,TELEPHONEPUBLIC2,EMAILPUBLIC,USERINFOPUBLIC,TELEPHONEPUBLIC,FIELD1) select x.id,0,null,x.ITEM_phone,null,x.ITEM_securityName,x.ITEM_phone,1,x.DOMAINID,0,1000,ITEM_documentType,ITEM_documentID, 1,1,1,1,1,1,1,'tlk_securityman' from baibaodunflow.tlk_securityman x where not exists(select 1 from obpm2.t_user k where ifnull(k.loginno,'')=ifnull(x.ITEM_phone,'') and k.domainid=x.DOMAINID) and ITEM_phone is not null and x.id not in (select id from t_user); /** 方式二 */ select concat("INSERT INTO t_user(id,LEVELS,DEFAULTDEPARTMENT,LOGINNO,LOGINPWD,NAME,TELEPHONE,STATUS,DOMAINID,ISDOMAINUSER,ORDERBYNO,FIELD11,FIELD12, DIMISSION,LIAISON_OFFICER,TELEPHONE2,TELEPHONEPUBLIC2,EMAILPUBLIC,USERINFOPUBLIC,TELEPHONEPUBLIC,FIELD1)values('", x.id,"',0,NULL,'",x.ITEM_phone,"',null,'",x.ITEM_securityName,"','",x.ITEM_phone,"',1,'",x.DOMAINID,"',0,1000,'",IFNULL(ITEM_documentType,'居民身份证（户口簿）'),"', '",ifnull(ITEM_documentID,""),"',1,1,1,1,1,1,1,'tlk_securityman');" ) from baibaodunflow.tlk_securityman x where not exists(select 1 from obpm2.t_user k where k.loginno=x.ITEM_phone and k.domainid=x.DOMAINID) and ITEM_phone is not null and x.id not in (select id from t_user); /** 【入离职人员】 根据baibaodunflow.tlk_employmentrecord入离职记录补充t_user 方式一 */ INSERT INTO t_user(id,LEVELS,DEFAULTDEPARTMENT,LOGINNO,LOGINPWD,NAME,TELEPHONE,STATUS,DOMAINID,ISDOMAINUSER,ORDERBYNO,FIELD11,FIELD12, DIMISSION,LIAISON_OFFICER,TELEPHONE2,TELEPHONEPUBLIC2,EMAILPUBLIC,USERINFOPUBLIC,TELEPHONEPUBLIC,FIELD1,FIELD2) select x.id,0,null,x.ITEM_phone,null,x.ITEM_securityName,x.ITEM_phone,1,x.DOMAINID,0,1000,ITEM_documentType,ITEM_documentID, 1,1,1,1,1,1,1,'tlk_employmentrecord-ningxia-0628',ITEM_status from baibaodunflow.tlk_employmentrecord x left join obpm2.t_user k on ifnull(k.loginno,'')=ifnull(x.ITEM_phone,'') and k.domainid=x.DOMAINID left join obpm2.t_user t on t.id=x.id where ITEM_phone is not null and instr(LASTFLOWOPERATION, '99999640000')>1 and k.id is null and t.id is null limit 10; /** 方式二 */ select concat("INSERT INTO t_user(id,LEVELS,DEFAULTDEPARTMENT,LOGINNO,LOGINPWD,NAME,TELEPHONE,STATUS,DOMAINID,ISDOMAINUSER,ORDERBYNO,FIELD11,FIELD12, DIMISSION,LIAISON_OFFICER,TELEPHONE2,TELEPHONEPUBLIC2,EMAILPUBLIC,USERINFOPUBLIC,TELEPHONEPUBLIC,FIELD1,FIELD2)values('", x.id,"',0,NULL,'",x.ITEM_phone,"',null,'",x.ITEM_securityName,"','",x.ITEM_phone,"',1,'",x.DOMAINID,"',0,1000,'",IFNULL(ITEM_documentType,'居民身份证（户口簿）'),"', '",ifnull(ITEM_documentID,""),"',1,1,1,1,1,1,1,'tlk_employmentrecord-ningxia-0628','",ifnull(ITEM_status,''),"');" ) from baibaodunflow.tlk_employmentrecord x left join obpm2.t_user k on ifnull(k.loginno,'')=ifnull(x.ITEM_phone,'') and k.domainid=x.DOMAINID where ITEM_phone is not null and instr(LASTFLOWOPERATION, '99999640000')>1 and k.id is null limit 10; /** identity_user */ /** 备份LOGINNO数据 */ update t_user set FIELD10=LOGINNO; /** 修改LOGINNO为身份证 */ update t_user k set k.LOGINNO=FIELD12 where DOMAINID in (SELECT id FROM obpm2.t_domain a WHERE instr(SORTID, '99999640000')>1 ) and k.LOGINNO<>FIELD12 and length(IFNULL(FIELD12,'')>10; /** 调整代码 */ INSERT INTO `obpm2`.`identity_user`(`id`, `created_time`, `name`, `telephone`, `tenant_user_id`, `idNum`) select l.ID,ifnull((l.LASTMODIFYTIME),'2021-01-01') as max_lastmodifytime,l.`NAME`,l.LOGINNO,l.FIELD12 from t_user l where l.id in ( select mid from ( SELECT DISTINCT t.LOGINNO, (select u.ID from t_user u where u.LOGINNO=t.LOGINNO order by instr(u.ID, '__qFCcCnozYNwzoG3O6Ms') desc limit 1) as mid FROM t_user t where t.DOMAINID in (SELECT id FROM obpm2.t_domain a WHERE instr(SORTID, '99999640000')>1) ) as m ) and id not in (select u.id from identity_user u ); /** identity_principals */ insert into `obpm2`.identity_principals(id,created_time,`status`,identity_user_id,last_updated_time) select a.id,'2021-01-01', (case when IFNULL(b.LOCKFLAG,1)=0 or IFNULL(b.`STATUS`,1)=0 or IFNULL(b.DIMISSION,1)=0 then 2 else 0 end), a.id, a.created_time from identity_user a join t_user b on a.id = b.ID and a.id not in (select id from identity_principals); /** identity_userpassword */ insert into `obpm2`.identity_userpassword(id,user_name,password,algorithm_type) select a.id,b.LOGINNO,b.LOGINPWD,(case when b.PWDTYPE='SM2' then 0 else 1 end) as PWDTYPE from identity_principals a join t_user b on a.id = b.ID and a.id not in (select id from identity_userpassword) and b.LOGINNO not in (select user_name from `obpm2`.identity_userpassword); /** tenant_users */ INSERT INTO `obpm2`.`tenant_users`(`id`, `authenticated_status`, `authenticated_result`, `authenticated_time`, `birthdate`, `checked_status`, `created_time`, `email`, `habit_setting`, `head_photo`, `im_identity`, `lon_lat_json`, `name`, `sex`, `stature`, `telephone`, `user_type`, `selected_credential_id`, `household_type`, `one_inch_color_white_photo`, `two_inch_color_blue_photo`, `education`, `marital_status`, `military_status`, `nation`, `native_place`, `politics_status`) select iu.tenant_user_id, (case when tu.FIELD21='已认证' then 3 when tu.FIELD23='未认证' then 0 else 2 end), tu.FIELD23, tu.LASTMODIFYTIME, null,(case when tu.FIELD13 ='已核验' then 1 when tu.FIELD13='未提交' then 0 else 2 end), IFNULL(tu.LASTMODIFYTIME,CURRENT_TIMESTAMP),tu.email,null as habit_setting, tu.FIELD20 as head_photo, null as im_identity, tu.FIELD25 as lon_lat_json,tu.`NAME`,0, null as stature, tu.LOGINNO as telephone, 0 as userType, null as selected_credential_id,null as household_type, null as one_inch_color_white_photo,null as two_inch_color_blue_photo, null as education,null as marital_status,null as military_status, null as nation, null as native_place,null as politics_status from t_user tu join identity_user iu on tu.ID=iu.id where iu.tenant_user_id not in (select id from tenant_users); /** tenant_user_credentials */ 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 g.id,g.created_time,g.address, g.credential_type,g.front_photo,g.number,g.reverse_photo,g.valid_date_from,g.valid_date_to,g.tenant_user_id,g.head_photo, g.name,1 from ( select tu.id, IFNULL(tu.LASTMODIFYTIME,'2021-01-01') as created_time, IFNULL(tu.FIELD14,'') as address, (case tu.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 0 end ) as credential_type, IFNULL(tu.FIELD18,'') as front_photo, (case when exists(select 1 from tenant_user_credentials x where x.number=tu.FIELD12) then CONCAT(field12,'#',tu.LOGINNO) else field12 end) as number, IFNULL(tu.FIELD19,'') as reverse_photo, tu.FIELD16 as valid_date_from, tu.FIELD17 as valid_date_to, iu.tenant_user_id, IFNULL(tu.FIELD15,'') as head_photo, iu.name,1 from t_user tu join identity_user iu on tu.ID=iu.id where tu.id not in (select m.id from tenant_user_credentials m where m.credential_type in (0,1,2,3,4,5,6)) and field12 is not null ) g where g.number not in (select number from tenant_user_credentials); UPDATE tenant_users x set selected_credential_id= (select k.id from tenant_user_credentials k where k.tenant_user_id=x.id and k.credential_type=0 limit 1) where selected_credential_id is null and x.id like '%--__%'; UPDATE tenant_users x set selected_credential_id= (select k.id from tenant_user_credentials k where k.tenant_user_id=x.id and k.credential_type in (0,1,2,3,4,5,6) limit 1) where selected_credential_id is null and x.id like '%--__%'; /** 合同信息 部分一 */ 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 1 else 0 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 left 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 like '%--_%'; 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 1 else 0 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 left join obpm2.tenant_user_credentials d on d.id=x.selected_credential_id where j.organization_id=c.DOMAINID and d.telephone 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 like '%--_%'; /** 合同信息 对于没有ITEM_SecurityId的用户, 根据DomainId和手机号码找到这个人进行导入 */ 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 1 else 0 end) as date_limit_less, ITEM_CONTRACTENDDATE, ITEM_CONTRACTNAME, '' as note, (case ITEM_CONTRACTSTATE when '有效' then 1 else 0 end) as `status`, '迁移无ITEM_SECURITYID的数据' as status_note, (select e.id from obpm2.tenant_employees e join obpm2.tenant_users u on e.tenant_user_id=u.id where u.telephone=c.ITEM_partyBPhone and e.organization_id=c.DOMAINID limit 1) as employee_id, c.DOMAINID from baibaodunflow.tlk_contracttoman c where c.ID not in (select id from obpm2.tenant_contracts) and c.ITEM_SECURITYID is null ) as k where employee_id like '%--_%'; /** 修改路径 一 */ update `obpm2`.`tenant_contracts` a join ( select p.id, p.ITEM_CONTRACTFILE, (case p.ITEM_ISNOTENDDATE when 1 then 1 else 0 end) as date_limit_less, LOCATE('"path":"',p.ITEM_CONTRACTFILE) as pathIndex, LOCATE(',"url"',p.ITEM_CONTRACTFILE)-LOCATE('"path":"',p.ITEM_CONTRACTFILE)-8 as urlIndex, REPLACE(SUBSTR(p.ITEM_CONTRACTFILE,LOCATE('"path":"',p.ITEM_CONTRACTFILE)+8,LOCATE(',"url"',p.ITEM_CONTRACTFILE)-LOCATE('"path":"',p.ITEM_CONTRACTFILE)-9),"\\/","/") as rpl from baibaodunflow.tlk_contracttoman as p ) as k on a.id=k.id set a.date_limit_less=k.date_limit_less, a.attachment = (case when LOCATE('"path":"',a.attachment)>0 then rpl else a.attachment end ) where a.id like '%--_%' and a.attachment like '%,"url"%'; /** 修改路径 二 */ update `obpm2`.`tenant_contracts` a join ( select p.id, p.ITEM_CONTRACTFILE, (case p.ITEM_ISNOTENDDATE when 1 then 1 else 0 end) as date_limit_less, LOCATE('"path":"',p.ITEM_CONTRACTFILE) as pathIndex, LOCATE(',"url"',p.ITEM_CONTRACTFILE)-LOCATE('"path":"',p.ITEM_CONTRACTFILE)-8 as urlIndex, REPLACE(SUBSTR(p.ITEM_CONTRACTFILE,LOCATE('"path":"',p.ITEM_CONTRACTFILE)+8,LOCATE('","size"',p.ITEM_CONTRACTFILE)-LOCATE('"path":"',p.ITEM_CONTRACTFILE)-8),"\\/","/") as rpl_without_URL from baibaodunflow.tlk_contracttoman as p ) as k on a.id=k.id set a.date_limit_less=k.date_limit_less, a.attachment = (case when LOCATE('"path":"',a.attachment)>0 then rpl_without_URL else a.attachment end ) where a.id like '%--_%' and a.attachment like '%","size"%'; /** 修改路径 三 */ update `obpm2`.`tenant_contracts` a join ( select p.id, p.ITEM_CONTRACTFILE, (case p.ITEM_ISNOTENDDATE when 1 then 1 else 0 end) as date_limit_less, LOCATE('"path":"',p.ITEM_CONTRACTFILE) as pathIndex, LOCATE(',"url"',p.ITEM_CONTRACTFILE)-LOCATE('"path":"',p.ITEM_CONTRACTFILE)-8 as urlIndex, REPLACE(SUBSTR(p.ITEM_CONTRACTFILE,LOCATE('"path":"',p.ITEM_CONTRACTFILE)+8,LOCATE(',"url"',p.ITEM_CONTRACTFILE)-LOCATE('"path":"',p.ITEM_CONTRACTFILE)-9),"\\/","/") as rpl, REPLACE(SUBSTR(p.ITEM_CONTRACTFILE,LOCATE('"path":"',p.ITEM_CONTRACTFILE)+8,LOCATE('","size"',p.ITEM_CONTRACTFILE)-LOCATE('"path":"',p.ITEM_CONTRACTFILE)-8),"\\/","/") as rpl_without_URL from baibaodunflow.tlk_contracttoman as p ) as k on a.id=k.id set a.attachment = (case when length(rpl)>0 then rpl else rpl_without_URL end ) where a.id=k.id and a.id like '%--_%'; /** 修改部门信息 */ select CONCAT("update tenant_departments set parent_id='",(select t1.SUPERIOR from t_department t1 where t1.id=ch.id limit 1),"' where id='",ch.id,"';") from tenant_departments ch join tenant_departments pr on ch.parent_id=pr.ID where ch.organization_id<>pr.organization_id; select CONCAT("update tenant_departments set parent_id='",(select id from tenant_departments t1 where t1.organization_id=d1.organization_id and display_order=0 limit 1), "' where display_order=1 and id='",d1.id,"';") from tenant_departments d1 where display_order=1 and d1.organization_id not in (select t2.organization_id from tenant_departments t2 where t2.id=d1.parent_id) and d1.id like '%--__%'; update tenant_users t join tenant_employees e on t.id =e.tenant_user_id join tenant_user_credentials c on c.id =t.selected_credential_id join baibaodunflow.tlk_securityman s on s.ITEM_documentID =c.number set t.`authenticated_status`=(case s.ITEM_shiming when '认证失败' then 2 when '已认证' then 3 when '认证中' then 1 else 0 end),authenticated_result = ITEM_beizhu where e1.id like '%--__%' and e.organization_id in (SELECT ID FROM obpm2.t_domain WHERE instr(SORTID, '99999640000')>1) and t.authenticated_status in (0,2) ; select * from tenant_users t join tenant_employees e on t.id =e.tenant_user_id join where e.organization_id in (SELECT ID FROM obpm2.t_domain WHERE instr(SORTID, '99999640000')>1) and t.authenticated_status in (0,2) limit 100; update tenant_organizations o,t_domain k set o.industry_code=k.field14 where o.industry_code is null and o.id=k.id and o.id like '%--__%' and instr(k.SORTID, '99999640000')>1 update tenant_organizations o,t_domain k set o.institutional_code=k.field15 where ifnull(o.institutional_code,'')='' and o.id=k.id and o.id like '%--__%' and instr(k.SORTID, '99999640000')>1; update tenant_organizations o,t_domain k set o.logo_path=k.LOGO_URL where ifnull(o.logo_path,'')='' and o.id=k.id and o.id like '%--__%' and instr(k.SORTID, '99999640000')>1; update tenant_organizations o,t_domain k set o.place_of_business_address=k.FIELD13, o.place_of_business_province_code=SUBSTRING_INDEX(FIELD13,',',1), o.place_of_business_province_name= SUBSTRING_INDEX(FIELD13,',',1), o.place_of_business_city_code= SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1), o.place_of_business_city_name= SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1), o.place_of_business_district_code= SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1), o.place_of_business_district_name= SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1) where ifnull(o.place_of_business_address,'')='' and o.id=k.id and o.id like '%--__%' and instr(k.SORTID, '99999640000')>1; update tenant_organizations o,t_domain k set o.place_of_register_address=k.FIELD12, o.place_of_register_province_code=SUBSTRING_INDEX(FIELD12,',',1), o.place_of_register_province_name= SUBSTRING_INDEX(FIELD12,',',1), o.place_of_register_city_code= SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1), o.place_of_register_city_name= SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1), o.place_of_register_district_code= SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1), o.place_of_register_district_name= SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1) where ifnull(o.place_of_register_address,'')='' and o.id=k.id and o.id like '%--__%' and instr(k.SORTID, '99999640000')>1; update tenant_organizations k,sys_region x set place_of_business_province_code=x.id where x.region_full_name=k.place_of_business_province_name and SUBSTR(place_of_business_province_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0') and k.id like '%--__%' and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = d.ID AND instr(d.SORTID, '99999640000')>1); update tenant_organizations k,sys_region x set place_of_business_city_code=x.id where x.region_full_name=x.region_full_name=concat(k.place_of_business_province_name,k.place_of_register_city_name) and SUBSTR(place_of_business_city_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0') and k.id like '%--__%'and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = d.ID AND instr(d.SORTID, '99999640000')>1); update tenant_organizations k,sys_region x set place_of_business_city_code=x.id where x.region_full_name=k.place_of_register_city_name and SUBSTR(place_of_business_city_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0') and k.id like '%--__%'and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = d.ID AND instr(d.SORTID, '99999640000')>1); update tenant_organizations k,sys_region x set place_of_business_district_code=x.id where x.region_full_name=x.region_full_name=concat(k.place_of_business_province_name,k.place_of_register_city_name,k.place_of_business_district_name) and SUBSTR(place_of_business_district_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0') and k.id like '%--__%'and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = d.ID AND instr(d.SORTID, '99999640000')>1); update tenant_organizations k,sys_region x set place_of_business_district_code=x.id where x.region_full_name=k.place_of_business_district_name and SUBSTR(place_of_business_district_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0') and k.id like '%--__%'and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = d.ID AND instr(d.SORTID, '99999640000')>1); update tenant_organizations k,sys_region x set place_of_register_province_code=x.id where x.region_full_name=k.place_of_register_province_name and SUBSTR(place_of_register_province_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0') and k.id like '%--__%'and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = d.ID AND instr(d.SORTID, '99999640000')>1); update tenant_organizations k,sys_region x set place_of_register_city_code=x.id where x.region_full_name=concat(k.place_of_register_province_name,k.place_of_register_city_name) and SUBSTR(place_of_register_city_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0') and k.id like '%--__%'and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = d.ID AND instr(d.SORTID, '99999640000')>1); update tenant_organizations k,sys_region x set place_of_register_city_code=x.id where x.region_full_name=k.place_of_register_city_name and SUBSTR(place_of_register_city_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0') and k.id like '%--__%'and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = d.ID AND instr(d.SORTID, '99999640000')>1); update tenant_organizations k,sys_region x set place_of_register_district_code=x.id where x.region_full_name=concat(k.place_of_register_province_name,k.place_of_register_city_name,k.place_of_register_district_name) and SUBSTR(place_of_register_district_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0') and k.id like '%--__%'and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = d.ID AND instr(d.SORTID, '99999640000')>1); update tenant_organizations k,sys_region x set place_of_register_district_code=x.id where x.region_full_name=k.place_of_register_district_name and SUBSTR(place_of_register_district_code,1,1) not in ('1','2','3','4','5','6','7','8','9','0') and k.id like '%--__%'and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = d.ID AND instr(d.SORTID, '99999640000')>1); /** 重新迁移身份证照片 */ update tenant_user_credentials s, t_user t set front_photo=t.FIELD18, reverse_photo = FIELD19, valid_date_from = IFNULL(t.FIELD16,''), valid_date_to = IFNULL(t.FIELD17,''), head_photo = IFNULL(t.FIELD15,''), s.`name`=t.`NAME` #,s.address = IFNULL(t.FIELD14,'') where s.number=IFNULL(t.FIELD12,'') and (ifnull(front_photo,'')='' and ifnull(reverse_photo,'')='') and s.id like '%--__%' and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE k.ID = t.DOMAINID AND instr(d.SORTID, '99999640000')>1); update tenant_users x set cid=(select k.FIELD24 from t_user k where k.id=x.id limit 1) where cid is null and x.id like '%--__%'; update tenant_users x set wechat=(select k.field22 from t_user k where k.id=x.id limit 1) where wechat is null and x.id like '%--__%'; 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 and k.id like '%--__%'; update tenant_organizations x,t_domain k set x.industry_code=k.FIELD14,x.institutional_code=k.FIELD15 where x.id=k.ID and x.industry_code is null and x.id like '%--__%'; /** 设置一寸免冠照 */ update tenant_users x, t_user z set x.one_inch_color_white_photo=z.field20 where ifnull(x.one_inch_color_white_photo,'')='' and x.id=z.ID and x.id like '%--__%'; 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,c.DOMAINID FROM obpm2.t_user c 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 = ifnull(f.ITEM_sex,t.sex), t.stature = f.ITEM_height WHERE t.id like '%--_%' and t.military_status is null and EXISTS (SELECT 1 FROM obpm2.t_domain d WHERE d.ID = a.DOMAINID AND instr(SORTID, '99999640000')>1); UPDATE obpm2.tenant_users t SET t.education = (case t.education when '1' then '小学' when '2' then '初中' when '3' then '高中' when '4' then '中专' when '5' then '大专' when '6' then '本科' when '7' then '硕士研究生' when '8' then '博士' when '9' then '博士后' when '10' then '其他' end), t.household_type = (case t.household_type when '1' then '农村' else '城镇' end), t.marital_status = (case t.marital_status when '1' then '未婚' when '2' then '已婚' when '3' then '丧偶' when '4' then '离异' else '其他' end), t.nation = (case t.nation when '01' then '汉族' when '02' then '蒙古族' when '03' then '回族' when '04' then '藏族' when '05' then '维吾尔族' when '06' then '苗族' when '07' then '彝族' when '08' then '壮族' when '09' then '布依族' when '10' then '朝鲜族' when '11' then '满族' when '12' then '侗族' when '13' then '瑶族' when '14' then '白族' when '15' then '土家族' when '16' then '哈尼族' when '17' then '哈萨克族' when '18' then '傣族' when '19' then '黎族' when '20' then '傈僳族' when '21' then '佤族' when '22' then '畲族' when '23' then '高山族' when '24' then '拉祜族' when '25' then '水族' when '26' then '东乡族' when '27' then '纳西族' when '28' then '景颇族' when '29' then '柯尔克孜族' when '30' then '土族' when '31' then '达斡尔族' when '32' then '仫佬族' when '33' then '羌族' when '34' then '布朗族' when '35' then '撒拉族' when '36' then '毛难族' when '37' then '仡佬族' when '38' then '锡伯族' when '39' then '阿昌族' when '40' then '普米族' when '41' then '塔吉克族' when '42' then '怒族' when '43' then '乌孜别克族' when '44' then '俄罗斯族' when '45' then '鄂温克族' when '46' then '崩龙族' when '47' then '保安族' when '48' then '裕固族' when '49' then '京族' when '50' then '塔塔尔族' when '51' then '独龙族' when '52' then '鄂伦春族' when '53' then '赫哲族' when '54' then '门巴族' when '55' then '珞巴族' when '56' then '基诺族' when '97' then '其他' when '98' then '外国血统中国籍人士' end), t.military_status = (case t.military_status when '1' then '服过' else '未服' end), t.politics_status = (case t.politics_status when '01' then '中共党员' when '02' then '中共预备党员' when '03' then '共青团员' when '04' then '民革会员' when '05' then '民盟盟员' when '06' then '民建会员' when '07' then '民进会员' when '08' then '农工党党员' when '09' then '致公党党员' when '10' then '九三学社社员' when '11' then '台盟盟员' when '12' then '无党派民主人士' when '13' then ' 群众' end) WHERE id like '%--__%' and exists(select 1 from tenant_employees e join t_domain d on e.organization_id=d.id where e.tenant_user_id=t.id and instr(d.SORTID, '99999640000')>1 ); /** 导入微信信息 */ insert into identity_principals (id,created_time,`status`,identity_user_id,last_updated_time) SELECT e.id,'2021-01-01',1,e.tenant_user_id, createTime from t_wechat_user as l join tenant_employees e on l.userId=e.id where l.userId is not null and e.id not in (select p.id from identity_principals p) and e.id like '%--__%'; /** 导入微信 */ insert into identity_wechat (id,nicky,`open_id`) SELECT e.id,null,openid from t_wechat_user as l join identity_principals e on e.id=l.id where l.userId is not null and e.id not in (select p.id from identity_wechat p) and e.id like '%--__%'; /** 修改微信 */ 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 and wechat is null and x.id like '%--__%' and exists(select 1 from tenant_employees e join t_domain d on e.organization_id=d.id where e.tenant_user_id=t.id and instr(d.SORTID, '99999640000')>1); /** 查询出来之后执行 */ select CONCAT("update t_department set SUPERIOR='", (select p.id from t_department p where p.id=x.DOMAIN_ID and p.DOMAIN_ID=x.DOMAIN_ID limit 1), "' where id='",x.ID,"' and DOMAIN_ID like '%--__%' and LEVELS=1;") from t_department x where x.LEVELS=1 and x.ID not in (select p.id from t_department p where p.levels=0 and p.DOMAIN_ID=x.DOMAIN_ID) and x.Domain_ID like '%--_%' and x.SUPERIOR<>x.DOMAIN_ID; 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 and (IFNULL(unify_social_credit_code,'')='' and IFNULL(unify_social_credit_code,'')='' ) and x.id like '%--__%'; /** 营业执照 */ 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 and (IFNULL(unify_social_credit_code,'')='' and IFNULL(unify_social_credit_code,'')='' ) and x.id like '%--__%'; /** 插入无上级的职员信息 */ 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, m.ITEM_entryDate as hired_date, null as job_number,IFNULL(a.LASTMODIFYTIME,'2021-01-01'), m.ITEM_outRemark as leave_note,null as leave_operator_created_time, null as leave_operator_id,null as leave_operator_name, m.ITEM_outDate as leave_time,0, IFNULL((select (case sm.ITEM_jobType when 3 then 0 when 2 then 1 else 1 end) from baibaodunflow.tlk_securityman sm where sm.DOMAINID=a.DOMAINID and sm.ITEM_documentID=a.field12 limit 1),1) as occupation_type, '' as position, null as positive_date, null as salary, null as salary_bank_number, 0 as `status`, '' as work_place, a.DOMAINID,a.SUPERIOR as superior_id, IFNULL((select c.tenant_user_id from tenant_user_credentials c where c.number=a.FIELD12 limit 1), (select c.id from tenant_users c where c.telephone=a.LOGINNO limit 1) ) as tenant_user_id, null,null from t_user a join tenant_organizations c on c.ID=a.DOMAINID left join baibaodunflow.tlk_employmentrecord m on (m.ITEM_documentID=a.FIELD12) and a.DOMAINID=m.DOMAINID where a.ID not in (select k.id from tenant_employees k) and a.id like '%--__%' and a.SUPERIOR is null and EXISTS (SELECT 1 FROM obpm2.t_domain b WHERE b.ID = a.DOMAINID AND instr(b.SORTID, '99999640000')>1); /** 插入有上级的职员信息, 插入10次以上 */ 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, m.ITEM_entryDate as hired_date, 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, m.ITEM_leaveDate as leave_time,0, (case m.ITEM_jobType when 3 then 0 when 2 then 1 else 1 end) as occupation_type,'', null as positive_date, null as salary, null as salary_bank_number, 0 as `status`, ITEM_LOCATION as work_place, a.DOMAINID,a.SUPERIOR as superior_id, (select c.id from tenant_users c where c.telephone=a.LOGINNO limit 1) as tenant_user_id, null,null from t_user a join tenant_organizations c on c.ID=a.DOMAINID left join baibaodunflow.tlk_securityman m on m.ITEM_documentID=a.FIELD12 and a.DOMAINID=m.DOMAINID where a.ID not in (select k.id from tenant_employees k) and a.SUPERIOR in (select id from tenant_employees) and a.id like '%--__%'; /** 补充修改入职时间 */ update tenant_employee_events ev,tenant_employees e set ev.event_time=e.hired_date where ev.employee_id=e.id and ev.event_type=0; /** 补充修改离职时间 */ update tenant_employee_events ev,tenant_employees e set ev.event_time=e.leave_time where ev.employee_id=e.id and ev.event_type=1 and e.status=1; /** 补充入职时间 */ insert into tenant_employee_events (id,event_type,created_time,employee_id,note,`status`,event_time) select a.id, 0, a.created_time, a.id as employee_id, '补充入职信息',1,ifnull(hired_date,created_time) from tenant_employees a where not exists(select 1 from tenant_employee_events ev where ev.event_type=0 and ev.employee_id=a.id); insert into tenant_employee_events (id,event_type,created_time,employee_id,note,`status`,event_time) select concat(a.id,"-1"), 1, a.created_time, a.id as employee_id, '补充离职信息',1,leave_time from tenant_employees a where a.status=1 and not exists(select 1 from tenant_employee_events ev where ev.event_type=1 and ev.employee_id=a.id); 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 `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 `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) ; /** 重要的 */