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
  tu.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.id not in (select id from tenant_users) and iu.telephone not in (select telephone from tenant_users);

/**
  手动删除一些测试的账号信息，比如: 测试环境的如下信息是我们再验证的时候产生的tenant_user
  select * from tenant_users where telephone in ('15959298383','15959298385','15959394811');
  处理之后，再执行一遍
 */
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
  tu.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.id not in (select id from tenant_users) and iu.telephone not in (select telephone from tenant_users);


/**
  租户组织信息
  其中, 假设正式站点不会出现重复公司名称的情况, 因此, 先忽略这些重复的问题
 */
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,'2021-01-01','',1,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)
                  and not exists(select * from (select `NAME`,count(1) as ct from t_domain group by `NAME`) as x  where ct>1)
                  and k.id not in ('0djR2lTTHmarWpxXfsX','706C2tpAynO1n42tZf3','8X3R6hysu0KNO5sbP2Q','eKEyC0pL7a6WclEmA6u','h1I9v2vmup3gJJP7Mij','KVPzwK9eIOo85Z9HLic','o7PpVJadZlgzst5ctKU','R3qw0IxG797gRfWiBzl','Y7cdSfuKEzTunV46eRb','yD8Mq3bhdAlgbC6dcgf','__07mGzGR4dORmP7Hv5Rw','__3HiQsrDk9I2pM9jeasF','__ApVLNSyKHtPSgUIthIQ','__FZYEULJL63VGTcfNCEB','__lyYge19uxV1BBQoRaAy','__npmJ7nQFn5W03T6gU2A','__xqMgqg41hIArkE3YzP8','__xrEdvb6gcdonjB092t7','__Yho6OySgxUM1GDvDFzV');


/**
公司信息
 */
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);

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;

/**
  修改部门
 */
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 obpm2.tenant_departments)
                                                                      and t.`NAME` not in (select name 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.SUPERIOR is null and t.ID not in (select ID from obpm2.tenant_departments)
                                                                      and t.`NAME` in (select name from obpm2.tenant_departments);
/**
  需要循环执行几次, 类似如下操作进行修改
  update t_department set `NAME`=CONCAT(`NAME`,'..') where id='yJWAZLGc';
  select *
from (
select
  count(1) as ct,
	`NAME`,
	DOMAIN_ID
from t_department group by `NAME`,DOMAIN_ID
) t where t.ct>1;
 */
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
  id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR
from (
       select
         (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount,

         t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`,
         null as permission_config,t.DOMAIN_ID,t.SUPERIOR
       from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id
       where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments)
     ) as k where icount=1 and SUPERIOR 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
  id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR
from (
       select
         (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount,

         t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`,
         null as permission_config,t.DOMAIN_ID,t.SUPERIOR
       from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id
       where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments)
     ) as k where icount=2 and SUPERIOR 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
  id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR
from (
       select
         (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount,

         t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`,
         null as permission_config,t.DOMAIN_ID,t.SUPERIOR
       from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id
       where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments)
     ) as k where icount=3 and SUPERIOR 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
  id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR
from (
       select
         (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount,

         t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`,
         null as permission_config,t.DOMAIN_ID,t.SUPERIOR
       from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id
       where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments)
     ) as k where icount=4 and SUPERIOR 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
  id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR
from (
       select
         (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount,

         t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`,
         null as permission_config,t.DOMAIN_ID,t.SUPERIOR
       from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id
       where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments)
     ) as k where icount=5 and SUPERIOR 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
  id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR
from (
       select
         (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount,

         t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`,
         null as permission_config,t.DOMAIN_ID,t.SUPERIOR
       from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id
       where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments)
     ) as k where icount=6 and SUPERIOR 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
  id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR
from (
       select
         (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount,

         t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`,
         null as permission_config,t.DOMAIN_ID,t.SUPERIOR
       from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id
       where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments)
     ) as k where icount=7 and SUPERIOR 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
  id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR
from (
       select
         (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount,

         t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`,
         null as permission_config,t.DOMAIN_ID,t.SUPERIOR
       from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id
       where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments)
     ) as k where icount=8 and SUPERIOR 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
  id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR
from (
       select
         (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount,

         t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`,
         null as permission_config,t.DOMAIN_ID,t.SUPERIOR
       from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id
       where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments)
     ) as k where icount=9 and SUPERIOR 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
  id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR
from (
       select
         (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount,

         t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`,
         null as permission_config,t.DOMAIN_ID,t.SUPERIOR
       from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id
       where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments)
     ) as k where icount=10 and SUPERIOR in (select id from tenant_departments);
/**
职员
  先手动修改错误的部门信息
 */
update t_user h set h.DEFAULTDEPARTMENT=(select r.DEPARTMENTID from t_user_department_role_set r where r.USERID=h.ID limit 1);
update tenant_employees c set tenant_user_id=(select b.id from tenant_users b join t_user x on b.telephone=x.LOGINNO where x.id=c.id limit 1);


/**
  设置部门管理员
 */
INSERT INTO `obpm2`.`tenant_department_admins`(`id`, `created_time`, `department_id`, `employee_id`, `organization_id`)
select DISTINCT 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 t.DEPARTMENTID in (select id from tenant_departments)
;

/**
  默认授权智能人事的应用
 */
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;

/**
证件信息
 */
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_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 t_user_department_role_set(id,DEPARTMENTID,USERID,ROLEID)
select DISTINCT id, (select k.DEPARTMENTID from t_user_department_role_set k where k.USERID=u.ID limit 1) as DepartId,id as userId,'__2uz0l9F3qiXuMQZmma0'
from  t_user u
where u.DOMAINID in (select id from t_domain d where d.BIND_APPLICATIONS like '%__xkeybkGaZa1SboC9GL1%' )
  and ID not in (select IFNULL(USERID,'xxx') from t_user_department_role_set where ROLEID='__2uz0l9F3qiXuMQZmma0')
  and ID not in(select id from t_user_department_role_set)
;

/**
授权有问题的数据-保证组织管理员拥有智能人事的应用
 */
insert into t_user_department_role_set(id,DEPARTMENTID,USERID,ROLEID)
select id, (select k.DEPARTMENTID from t_user_department_role_set k where k.USERID=u.ID limit 1) as DepartId,id as userId,'__2uz0l9F3qiXuMQZmma0'
from  t_user u
where u.DOMAINID in (select id from t_domain d where d.BIND_APPLICATIONS like '%__xkeybkGaZa1SboC9GL1%' ) and u.ID not in (
  select USERID from t_user_department_role_set) and (u.ISDOMAINUSER='true' or u.ISDOMAINUSER='1');





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 lon_lat_json =j.field17 where k.id=j.ID and x.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 k
set place_of_business_province_code=(select x.id from sys_region x where x.region_full_name=k.place_of_business_province_name limit 1);
update tenant_organizations k
set place_of_business_city_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_business_province_name,k.place_of_register_city_name) limit 1);
update tenant_organizations k
set place_of_business_district_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_business_province_name,k.place_of_register_city_name,k.place_of_business_district_name) limit 1);

update tenant_organizations k
set place_of_register_province_code=(select x.id from sys_region x where x.region_full_name=k.place_of_register_province_name limit 1);
update tenant_organizations k
set place_of_register_city_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_register_province_name,k.place_of_register_city_name) limit 1);
update tenant_organizations k
set place_of_register_district_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_register_province_name,k.place_of_register_city_name,k.place_of_register_district_name) limit 1);









