INSERT INTO `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 `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','',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)
                  and not exists(select * from (select `NAME`,count(1) as ct from t_domain group by `NAME`) as x  where ct>1 and x.`NAME`=k.`NAME`);
/**
鍏徃淇℃伅
 */
INSERT INTO `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;

update t_department set indexcode=concat(id,'-') where SUPERIOR is null;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;
update t_department t1,t_department t2  set t1.indexcode =concat(t2.indexcode,t1.id,'-') where t1.SUPERIOR = t2.id;


/**
  淇敼閮ㄩ棬
 */
INSERT INTO `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);
/**
  闇€瑕佸惊鐜墽琛屽嚑娆�, 绫讳技濡備笅鎿嶄綔杩涜淇敼
  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 `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 tenant_departments)
     ) as k where icount=1 and SUPERIOR in (select id from tenant_departments);

INSERT INTO `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 tenant_departments)
     ) as k where icount=2 and SUPERIOR in (select id from tenant_departments);

INSERT INTO `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 tenant_departments)
     ) as k where icount=3 and SUPERIOR in (select id from tenant_departments);

INSERT INTO `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 tenant_departments)
     ) as k where icount=4 and SUPERIOR in (select id from tenant_departments);

INSERT INTO `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 tenant_departments)
     ) as k where icount=5 and SUPERIOR in (select id from tenant_departments);

INSERT INTO `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 tenant_departments)
     ) as k where icount=6 and SUPERIOR in (select id from tenant_departments);

INSERT INTO `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 tenant_departments)
     ) as k where icount=7 and SUPERIOR in (select id from tenant_departments);

INSERT INTO `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 tenant_departments)
     ) as k where icount=8 and SUPERIOR in (select id from tenant_departments);

INSERT INTO `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 tenant_departments)
     ) as k where icount=9 and SUPERIOR in (select id from tenant_departments);

INSERT INTO `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 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 `tenant_department_admins`(`id`, `created_time`, `department_id`, `employee_id`, `organization_id`)
select id,'2021-01-01',DEPARTMENTID,USERID,(select u.DOMAINID from t_user u where ID=t.USERID limit 1)
from t_user_department_admin_set t where t.ID not in (select l.id from tenant_department_admins l) and t.USERID is not null
                                     and USERID in (select id from tenant_employees);

/**
  榛樿鎺堟潈鏅鸿兘浜轰簨鐨勫簲鐢�
 */
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 `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 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 NOT EXISTS(SELECT 1 FROM `t_user_department_role_set` t where t.ID=id)
  and ID not in (select IFNULL(USERID,'xxx') from t_user_department_role_set where ROLEID='__2uz0l9F3qiXuMQZmma0');

/**
鎺堟潈鏈夐棶棰樼殑鏁版嵁-淇濊瘉缁勭粐绠＄悊鍛樻嫢鏈夋櫤鑳戒汉浜嬬殑搴旂敤
 */
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 k.lon_lat_json =j.field17 where k.id=j.ID and k.lon_lat_json is null;



update tenant_organizations x,t_domain k
set x.place_of_business_address=k.FIELD13,
    x.place_of_business_province_code=SUBSTRING_INDEX(FIELD13,',',1),
    x.place_of_business_province_name= SUBSTRING_INDEX(FIELD13,',',1),

    x.place_of_business_city_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1),
    x.place_of_business_city_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1),
    x.place_of_business_district_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1),
    x.place_of_business_district_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1),

    x.place_of_register_address = k.FIELD13,
    x.place_of_register_province_code = SUBSTRING_INDEX(FIELD12,',',1),
    x.place_of_register_province_name = SUBSTRING_INDEX(FIELD12,',',1),

    x.place_of_register_city_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) ,
    x.place_of_register_city_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1),
    x.place_of_register_district_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1),
    x.place_of_register_district_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1),
    x.industry_code=k.FIELD14,
    x.institutional_code=k.FIELD15
where x.id=k.ID and x.place_of_business_address is null;

/**
  鍖哄煙淇℃伅
 */
update tenant_organizations 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);