/** 迁移补充, 智能人事的bug 产生的存在tenant_user但是不存在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,c.credential_type,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); /** 认证中心-认证主体 */ INSERT INTO `obpm2`.`identity_user`(`id`, `created_time`, `name`, `telephone`, `tenant_user_id`) select max_id,max_lastmodifytime,max_name,max_loginno,max_id from ( select max(id) as max_id, ifnull(max(a.LASTMODIFYTIME),'2021-01-01') as max_lastmodifytime, max(a.`NAME`) as max_name, LOGINNO as max_loginno from t_user a where a.id like '%--_%' and REPLACE(IFNULL(LOGINNO,'0'),'','0')<>'0' group by LOGINNO ) as t where max_loginno not in (select telephone from identity_user) and max_id not in (select id from `obpm2`.`identity_user`); /** 认证中心-账号基本信息 */ 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); /** 认证中心-账号密码 */ 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); /** 人-租户 */ 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); /** 迁移身份证证件信息 */ 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 ; /** 重点问题: 身份证类型不对 */ update tenant_user_credentials c, (select (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.FIELD12,'') as number from t_user tu) k set c.credential_type=k.credential_type where c.number=k.number and c.credential_type in (0,1,2,3,4,5,6) and c.id like '%--__%' /** 采用一步步的 */ select concat("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`)values('",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, IFNULL(tu.FIELD12,'') 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 iu.IFNULL(tu.FIELD12,'') not in (select x.id from tenant_users x join tenant_user_credentials u on x.id=u.tenant_user_id) and where tu.id not in (select m.id from tenant_user_credentials m) ) g where g.number not in (select u.number from tenant_user_credentials u); update tenant_user_credentials set valid_date_from='1900-01-01' where valid_date_from='0000-00-00'and id like '%--__%'; update tenant_user_credentials set valid_date_to='3050-01-01' where valid_date_to='0000-00-00' and id like '%--__%'; update tenant_user_credentials c set c.credential_type=0 where c.credential_type =-1 and c.id like '%--__%'; /* 不清楚为什么查不出来 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, IFNULL(tu.FIELD12,'') as number, IFNULL(tu.FIELD19,'') as reverse_photo, IFNULL(tu.FIELD16,'') as valid_date_from, IFNULL(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 ) g where g.tenant_user_id not in (select p.tenant_user_id from tenant_user_credentials p where p.credential_type in (0,1,2,3,4,5,6)); */ /** 到这边 同步证书到智能人事 保安资格证" "1" "保安等级证" "2" "保安持枪证" "3 先排除重复的资格证信息 */ #delete from tenant_user_credentials where id like '%--_%' and credential_type>6; 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 id,created_time,ITEM_certificateFrom,credential_type,front_photo,ITEM_certificateNO,reverse_photo, ITEM_certificateDate,valid_date_to,tenant_user_id,head_photo,ITEM_securityName,selected from ( select b.id,b.CREATED as created_time,b.ITEM_certificateFrom, (case b.ITEM_certificateType when '1' then 7 when '保安员资格证' then 7 when '2' then 8 else 8 end) as credential_type, null as front_photo,b.ITEM_certificateNO,null as reverse_photo, b.ITEM_certificateDate,null as valid_date_to,x.tenant_user_id,null as head_photo,b.ITEM_securityName,0 as selected from baibaodunflow.tlk_certificate b join tenant_user_credentials x on b.ITEM_documentID=x.number where b.ITEM_certificateType in ('1','2','保安员资格证') #and b.id not in (select p.id from `obpm2`.`tenant_user_credentials` p) and not exists(select 1 from `obpm2`.`tenant_user_credentials` k where k.number=b.ITEM_certificateNO and k.credential_type in (7,8,9)) and (select count(1) from baibaodunflow.tlk_certificate h where h.ITEM_certificateNO=b.ITEM_certificateNO)=1 ) as k; 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 id,created_time,ITEM_certificateFrom,credential_type,front_photo,ITEM_certificateNO,reverse_photo, ITEM_certificateDate,valid_date_to,tenant_user_id,head_photo,ITEM_securityName,selected from ( select b.id,b.CREATED as created_time,b.ITEM_certificateFrom, (case b.ITEM_certificateType when '1' then 7 when '保安员资格证' then 7 when '2' then 8 else 8 end) as credential_type, null as front_photo,b.ITEM_certificateNO,null as reverse_photo, b.ITEM_certificateDate,null as valid_date_to,x.tenant_user_id,null as head_photo,b.ITEM_securityName,0 as selected from baibaodunflow.tlk_certificate b join tenant_user_credentials x on b.ITEM_documentID=x.number where b.ITEM_certificateType in ('1','2','保安员资格证') and b.id not in (select p.id from `obpm2`.`tenant_user_credentials` p) and (select count(1) from baibaodunflow.tlk_certificate h where h.ITEM_certificateNO=b.ITEM_certificateNO)=1 ) as k; /** 2022-04-05执行到这边 排查是否存在重复的 */ select id,created_time,ITEM_certificateFrom,credential_type,front_photo,ITEM_certificateNO,reverse_photo, ITEM_certificateDate,valid_date_to,tenant_user_id,head_photo,ITEM_securityName,selected from ( select b.id,b.CREATED as created_time,b.ITEM_certificateFrom,(case b.ITEM_certificateType when 1 then 7 when 2 then 8 else 8 end) as credential_type, null as front_photo,b.ITEM_certificateNO,null as reverse_photo, b.ITEM_certificateDate,null as valid_date_to,x.tenant_user_id,null as head_photo,b.ITEM_securityName,0 as selected from baibaodunflow.tlk_certificate b join tenant_user_credentials x on b.ITEM_documentID=x.number where b.ITEM_certificateType in (1,2) and b.id not in (select p.id from `obpm2`.`tenant_user_credentials` p) and (select count(1) from baibaodunflow.tlk_certificate h where h.ITEM_certificateNO=b.ITEM_certificateNO)=1 ) as k; /** 设置默认证件信息 */ 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 '%--__%'; /** 组织信息 需要补充审核信息 approved_information_last_updated_time/approved_information_note/approved_information_status/tenant_user_id */ 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); /** 批量导入合同信息, 有部分合同跟人-公司对不上, 需要补充离职人员的信息 */ 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 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 '%--_%'; /** 对于没有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 '%--_%'; /** 通过手机号关联导入合同 */ /* 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 x.id from tenant_employees x join tenant_users u on x.tenant_user_id=u.id where u.telephone=c.ITEM_partyBPhone and x.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) ) as k where DOMAINID 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 e set checked_status=2 where checked_status=1 and id not in (select tenant_user_id from tenant_user_credentials k where k.credential_type=0) and e.id like '%--__%'; /** 身份证证件的, 则将已提交改为已核验 */ update tenant_user_credentials t,tenant_users e set e.checked_status=1 where checked_status=2 and t.tenant_user_id=e.id and t.credential_type=0 and e.id like '%--__%'; /** 修改实名认证状态 */ update tenant_users e1, (select t.LOGINNO,(case s.ITEM_shiming when '认证失败' then 2 when '已认证' then 3 when '认证中' then 1 else 0 end) as authestatus,s.ITEM_beizhu from t_user t join baibaodunflow.tlk_securityman s on t.FIELD12=s.ITEM_documentID) s2 set `authenticated_status`=authestatus,authenticated_result = ITEM_beizhu where e1.`telephone`=s2.LOGINNO and authenticated_status in (0,2) and e1.id like '%--__%'; 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 '%--__%'; 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 '%--__%'; 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 '%--__%'; 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 '%--__%'; 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 '%--__%'; 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 '%--__%'; 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 '%--__%'; 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 '%--__%'; 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 '%--__%'; 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 '%--__%'; 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 '%--__%'; update tenant_organizations k,sys_region x set place_of_register_city_code=x.id where x.region_full_name=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 '%--__%'; 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 '%--__%'; 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 '%--__%'; 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 '%--__%'; /** 重新迁移身份证照片 */ 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 s.id like '%--__%'; /** 修改cid */ 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 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; 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 '%--__%'; /** 导入微信信息 */ 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 '%--__%'; /** 查询出来之后执行 */ 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; /** 警告: t_user的数据有问题, 特别是湖南, 设置组织管理员 */ #update tenant_employees set is_domain_admin=1 #where id in (select u.id from t_user u where ifnull(u.ISDOMAINUSER,'xxx')='true' or ifnull(u.ISDOMAINUSER,'xxx')='1') # and k.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_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); /** 导入二级部门 执行10次 */ 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); /** 。。。。。 迁移没有上级的职员 之后在补充部门信息 */ 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,'' as position, 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 is null and a.id like '%--__%'; /** 迁移有上级的职员 执行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 '%--__%'; /** 从离职记录中补充, 已经离职的人员信息 */ 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 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,DOMAINID,superior_id,tenant_user_id,unique_offset,department_id from ( select m.id,'2021-01-01' as created_time,null as EMAIL, m.ITEM_entryDate as hired_date, null as job_number, '2021-01-01' as last_updated_time, m.ITEM_outRemark as leave_note,null as leave_operator_created_time, null as leave_operator_id,'系统导入-来自离职人员记录' as leave_operator_name, m.ITEM_outDate as leave_time,0 as master_slave_type, (select (case a.ITEM_jobType when 3 then 0 when 2 then 1 else 1 end) from baibaodunflow.tlk_securityman a where m.ITEM_documentID=a.ITEM_documentID limit 1) as occupation_type, '' as position, null as positive_date, null as salary, null as salary_bank_number, 0 as `status`, '' as work_place, m.DOMAINID,null as superior_id, (select k.tenant_user_id from tenant_user_credentials k where k.number=m.ITEM_documentID and k.credential_type=0 limit 1) as tenant_user_id, null as unique_offset,null as department_id from baibaodunflow.tlk_employmentrecord m where m.id like '%--__%') k where not exists(select 1 from `obpm2`.`tenant_employees` p where p.organization_id=k.DOMAINID and k.tenant_user_id=p.tenant_user_id); /** 批量补充t_user信息 */ SELECT DISTINCT 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,FIELD26)select '", a.id,"','",'0',"','",IFNULL(a.department_id,'NULL'),"','",b.TELEPHONE,"','invalid','",b.`NAME`,"','",b.TELEPHONE,"','",1,"','", a.organization_id,"','",0,"','",1000,"','",c.credential_type,"','",c.number,"',", 1,",",1,",",1,",",1,",",1,",",1,",",1,",'迁移来自离职记录' from dual where not exists(select 1 from t_user where id='",a.id,"');" ) as sqlx 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 CONCAT(a.organization_id,b.telephone) not in (select CONCAT(t.DOMAINID,t.LOGINNO) from t_user t) and a.id not in (select id from t_user); insert into tenant_employee_events (id,event_type,created_time,employee_id,note,`status`) select a.id, (case item_status when '离职' then 1 else 0 end) as event_type, a.CREATED, e.id as employee_id, '来自入离职记录信息',1 from baibaodunflow.tlk_employmentrecord a join tenant_user_credentials x on a.ITEM_DOCUMENTID=x.number and x.credential_type=0 join tenant_employees e on e.tenant_user_id=x.tenant_user_id and a.DOMAINID=e.organization_id where a.ITEM_STATUS in ('在职','离职') and a.id not in (select id from tenant_employees); /* update tenant_users u join tenant_user_credentials c on u.id = c.tenant_user_id and c.credential_type=0 set u.head_photo=c.head_photo where u.id like '%--_%' and ifnull(u.head_photo,'')=''; */ select * from information_schema.`PROCESSLIST` where INSTR(COMMAND,'Query') order by TIME desc;