
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`,background_screening_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,
  1 as background_screening_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,
       ifnull((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;

create table tmp_depart_qy as select * from (
SELECT id from t_department
where SUPERIOR not in (select id from t_department)
) as sql_table;


update t_department t1,t_department t2
set t1.SUPERIOR = t2.id
where t1.id in (select id from tmp_depart_qy)
and t1.DOMAIN_ID = t2.DOMAIN_ID
and t2.SUPERIOR is null
;


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;

create table tmp_depart_qy as select * from (
select `NAME` from t_department group by `NAME` HAVING COUNT(1)>1
) as sql_table;

update t_department set `NAME`=CONCAT(`NAME`,'-',id) where `NAME` in (select `NAME` from tmp_depart_qy);


/**
  修改部门
 */
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,ifnull(t.`CODE`,' '),IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.INDEXCODE,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.`NAME`,
  null as permission_config,0,1,0,t.DOMAIN_ID,t.SUPERIOR
from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.ID not in (select ID from 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,']',',"__d1qEDy3wYq8VF5KvUoE"]') where BIND_APPLICATIONS not like '%__d1qEDy3wYq8VF5KvUoE%' and LENGTH(BIND_APPLICATIONS)>5;
update t_domain set BIND_APPLICATIONS=REPLACE(BIND_APPLICATIONS,']','"__d1qEDy3wYq8VF5KvUoE"]') where BIND_APPLICATIONS not like '%__d1qEDy3wYq8VF5KvUoE%' and LENGTH(BIND_APPLICATIONS)>1;
update t_domain set BIND_APPLICATIONS='["__d1qEDy3wYq8VF5KvUoE"]' where BIND_APPLICATIONS is null;

update t_user set FIELD16='1900-01-01' where FIELD16='';
update t_user set FIELD17='3050-01-01' where FIELD17='';

/**
证件信息
 */
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,'1900-01-01') as valid_date_from,IFNULL(t.FIELD17,'3050-01-01') 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);


/**
  默认授权智能人事
 */
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,'__qPyH54DLGsIuTK2OHup' from  t_user u
where u.DOMAINID in (select id from t_domain d where d.BIND_APPLICATIONS like '%__d1qEDy3wYq8VF5KvUoE%' )
  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='__qPyH54DLGsIuTK2OHup');

/**
授权有问题的数据-保证组织管理员拥有智能人事的应用
 */
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,'__qPyH54DLGsIuTK2OHup'
from  t_user u
where u.DOMAINID in (select id from t_domain d where d.BIND_APPLICATIONS like '%__d1qEDy3wYq8VF5KvUoE%' ) 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 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);









