update t_user set FIELD29=SUPERIOR;
update t_user set SUPERIOR=null;
update
  t_user t1,t_department t2
set t1.DEFAULTDEPARTMENT = t2.id
where t1.DEFAULTDEPARTMENT not in (select id from t_department)
  and t1.DOMAINID = t2.DOMAIN_ID and t2.SUPERIOR is null;

INSERT INTO `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`,`is_domain_admin`)
select
  a.id,IFNULL(a.LASTMODIFYTIME,'2021-01-01'),a.EMAIL,
  IFNULL(a.LASTMODIFYTIME,'2021-01-01'),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,
  null as leave_time,0,
  (case when IFNULL(null,3)=3 THEN 0 ELSE 1 END) as occupation_type,'' as position,
  IFNULL(a.LASTMODIFYTIME,'2021-01-01') as positive_date,
  null as salary, null as salary_bank_number,
  0 as `status`,
  null as work_place,
  a.DOMAINID,a.SUPERIOR as superior_id,
  (select c.id from tenant_users c where c.telephone=a.LOGINNO limit 1),
  null,a.DEFAULTDEPARTMENT,0
from t_user a join tenant_organizations c on c.ID=a.DOMAINID
where a.ID not in (select k.id from tenant_employees k) and a.DEFAULTDEPARTMENT in (select id from tenant_departments) ;


INSERT INTO `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`,`is_domain_admin`)
select
  a.id,IFNULL(a.LASTMODIFYTIME,'2021-01-01'),a.EMAIL,
  IFNULL(a.LASTMODIFYTIME,'2021-01-01'),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,
  null as leave_time,0,
  (case when IFNULL(null,3)=3 THEN 0 ELSE 1 END) as occupation_type,'' as position,
  IFNULL(a.LASTMODIFYTIME,'2021-01-01') as positive_date,
  null as salary, null as salary_bank_number,
  0 as `status`,
  null as work_place,
  a.DOMAINID,a.SUPERIOR as superior_id,(select c.id from tenant_users c where c.telephone=a.LOGINNO limit 1),
  null,a.DEFAULTDEPARTMENT,0
from t_user a join tenant_organizations c on c.ID=a.DOMAINID
where a.ID not in (select k.id from tenant_employees k) and a.DEFAULTDEPARTMENT is null;

/**
  无效数据
 */
select * from t_user where id not in (select k.id from tenant_employees k) and DOMAINID in (select id from t_domain);
/**
  改回去
 */
update tenant_employees x, t_user z
set x.superior_id=z.FIELD29
where x.id=z.ID;

update tenant_employees e, t_user t
set e.tenant_user_id =(select h.id from tenant_users h where h.telephone=t.LOGINNO limit 1)
where e.id=t.id and e.tenant_user_id not in (select id from tenant_users);

INSERT INTO `tenant_department_employee_relations`(`id`, `created_time`, `master_slave_type`, `department_id`, `employee_id`)
select
  maxID,'2021-01-01',2,DEPARTMENTID,USERID
from (
       select max(z.ID) as maxID,
              z.DEPARTMENTID,
              z.USERID,
              count(1)  as ct
       from t_user_department_role_set z
       where z.DEPARTMENTID in (select id from t_department)
         and z.USERID in (select o.ID from t_user o)
       group by DEPARTMENTID, USERID
     ) p
where maxID not in (select id from tenant_department_employee_relations)
  AND NOT EXISTS(SELECT 1 FROM `tenant_department_employee_relations` t where t.department_id=DEPARTMENTID AND t.employee_id=USERID) -- 排查唯一约束
  AND EXISTS(SELECT 1 FROM `tenant_departments` t where t.id=DEPARTMENTID); -- 排除外键约束

update tenant_employees k
set k.department_id=(select c.department_id from tenant_department_employee_relations c where c.employee_id=k.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) and DEPARTMENTID in (select ID from tenant_departments);

/**
确认离职状态
 */
update tenant_employees set `status`=0;

/**
  授权应用
 */
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 tenant_departments x set total_member=(select count(1) from tenant_department_employee_relations c where c.department_id=x.id);

/**
  证件信息
 */
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_employees set is_domain_admin=1  where id in (select id from t_user u where u.ISDOMAINUSER='true');
update tenant_employees set is_domain_admin=1  where `position` in ('系统管理员','总经理','副总经理');
/**
  设置无效数据
 */
# 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';

/**
  替换合同格式
 */
update tenant_contracts set attachment=REPLACE(SUBSTR(attachment,LOCATE( '"path":"', attachment)+8,(LOCATE('"url":', attachment)-LOCATE( '"path":"', attachment)-8-2)),'\\','')
where attachment like '[{%';

/**
  默认授权智能人事
 */

insert into t_user_department_role_set(id,DEPARTMENTID,USERID,ROLEID)
select CONCAT(id,'_role'), (select k.department_id from tenant_department_employee_relations k where k.employee_id=u.id limit 1) as DepartId,
       id as userId,'__qPyH54DLGsIuTK2OHup'
from  tenant_employees u
where u.organization_id in (select id from t_domain d where d.BIND_APPLICATIONS like '%__d1qEDy3wYq8VF5KvUoE%' )
  and u.id not in (select p.USERID from t_user_department_role_set p where p.ROLEID='__qPyH54DLGsIuTK2OHup');

/**
  设备ID
 */
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 set approved_information_status=1 where `name` not like '%all%';

update tenant_users x ,tenant_user_credentials z
set x.selected_credential_id=z.id
where z.tenant_user_id=x.id;


/**
  多个身份证绑定不同手机号码的情况
 */
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
  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,CONCAT(IFNULL(t.FIELD12,''),'&',t.LOGINNO) 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 tenant_users x join t_user t on x.id=t.ID
where t.ID not in (select p.tenant_user_id from tenant_user_credentials p );

update tenant_users x,tenant_user_credentials k
SET x.selected_credential_id=k.id
where x.id=k.id and x.selected_credential_id is null;

update tenant_users x set x.selected_credential_id=null
where x.selected_credential_id in (select id from tenant_user_credentials where number like '&%');

delete from tenant_user_credentials where number like '&%';

select  CONCAT('update tenant_user_credentials set number=SUBSTRING_INDEX(number,''&'',1) where id=''',id,''';') as sqlx,
        number,id,SUBSTRING_INDEX(number,'&',1)
from tenant_user_credentials where number like '%&%' and tenant_user_id  in (select id from tenant_users x) order by number asc;

select
  sqlx,ct
from (
       SELECT
         sqlx,number,id,kn,count(1) as ct
       from (
              select CONCAT('update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,''&'',1),''.'') where id=''',id,''';') as sqlx,
                     number,id,SUBSTRING_INDEX(number,'&',1) as kn from tenant_user_credentials where number like '%&%'
                                                                                                  and tenant_user_id  in (select id from tenant_users x)
            ) as k
       group by kn
     ) as lk where lk.ct=1;

select
  sqlx,ct
from (
       SELECT
         sqlx,number,id,kn,count(1) as ct
       from (
              select CONCAT('update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,''&'',1),''..'') where id=''',id,''';') as sqlx,
                     number,id,SUBSTRING_INDEX(number,'&',1) as kn from tenant_user_credentials where number like '%&%'
                                                                                                  and tenant_user_id  in (select id from tenant_users x)
            ) as k
       group by kn
     ) as lk where lk.ct=2;

select
  sqlx,ct
from (
       SELECT
         sqlx,number,id,kn,count(1) as ct
       from (
              select CONCAT('update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,''&'',1),''...'') where id=''',id,''';') as sqlx,
                     number,id,SUBSTRING_INDEX(number,'&',1) as kn from tenant_user_credentials where number like '%&%'
                                                                                                  and tenant_user_id  in (select id from tenant_users x)
            ) as k
       group by kn
     ) as lk where lk.ct=3;

select
  sqlx,ct
from (
       SELECT
         sqlx,number,id,kn,count(1) as ct
       from (
              select CONCAT('update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,''&'',1),''...'') where id=''',id,''';') as sqlx,
                     number,id,SUBSTRING_INDEX(number,'&',1) as kn from tenant_user_credentials where number like '%&%'
                                                                                                  and tenant_user_id  in (select id from tenant_users x)
            ) as k
       group by kn
     ) as lk where lk.ct=3;

update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'._') where id='TrfWFROzCBoaaNvDtaQ';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'__') where id='vRo7ABJdL7sNl9VPM15';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'..._') where id='Yk7sCYkL9i1KvsLhoV2';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'...__') where id='ZYhiU1OBxyBeadIqrWR';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'__.') where id='__REfy8WnFNVXLHJ5OkJ7';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'__..') where id='__rSWnOXwBxgCzaYyIC8n';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'___') where id='__S4usvWwTnKRp1WtQmIE';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'____') where id='__SCFXXj3ynQpO2t6ryL0';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'_ _') where id='__t9crbmBCwsDZSRWCeYn';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'. _') where id='__vsA6GfXEwpuDJDAeHE0';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'. .') where id='__W6WadyCQEXdIOXVkPoF';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'.._') where id='__WA4VOqYpxQvgW5C91CS';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'_..') where id='__X8W6wWaym0gNXcYKbBO';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'._.') where id='__XnNSkDYrxkBMhkziarr';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'.._.') where id='__YAgOKEgSvLAYruP9SP3';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'..._.') where id='__yHvvrKz9iqGWYDvC51c';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'._...') where id='__z28e2xFSavhlVYRbR0Z';
update tenant_user_credentials set number=concat(SUBSTRING_INDEX(number,'&',1),'_....') where id='__ZUlmVzUBMmtv9KH2Xph';

-- update tenant_organizations k, t_domain j set lon_lat_json =j.field17 where k.id=j.ID and k.lon_lat_json is null;

SELECT *
from (select x.`name`,x.organization_id,COUNT(1) as ct,
             CONCAT('update tenant_departments set `name`=''',x.`name`,'.'' where id=''',id,'''') as sqxl from tenant_departments x group by x.`name`,x.organization_id ) as k
where k.ct=2;

update tenant_user_credentials set credential_type=2 where credential_type=-1;

/**
  组织管理员
 */
update tenant_employees set is_domain_admin=1  where id in (select id from t_user u where u.ISDOMAINUSER='true');
update tenant_employees set is_domain_admin=1  where `position` in ('系统管理员','总经理','副总经理');

insert into tenant_department_employee_relations(id,created_time,master_slave_type,department_id,employee_id)
select
  CONCAT(x.id,d.id),CURRENT_TIMESTAMP,2,x.id,d.id
from tenant_departments x join tenant_employees d on x.id=d.department_id
where CONCAT(x.id,d.id) not in (select CONCAT(t.department_id,t.employee_id) from tenant_department_employee_relations t);
/**
  部门用户关系
 */
INSERT INTO `tenant_department_employee_relations`(`id`, `created_time`, `master_slave_type`, `department_id`, `employee_id`)
select
  maxID,'2021-01-01',2,DEPARTMENTID,USERID
from (
       select
         max(z.ID) as maxID,z.DEPARTMENTID,z.USERID,count(1) as ct
       from t_user_department_role_set z join tenant_employees e on z.USERID=e.id
       group by z.DEPARTMENTID,z.USERID
     ) as o where ct=1 and
    CONCAT(DEPARTMENTID,USERID) not in (select CONCAT(department_id,employee_id) from tenant_department_employee_relations)
              and DEPARTMENTID in (select id from tenant_departments) and maxID not in (select id from tenant_department_employee_relations) ;

INSERT INTO `tenant_department_employee_relations`(`id`, `created_time`, `master_slave_type`, `department_id`, `employee_id`)
select
  maxID,'2021-01-01',2,DEPARTMENTID,USERID
from (
       select
         max(z.ID) as maxID,z.DEPARTMENTID,z.USERID,count(1) as ct
       from t_user_department_role_set z join tenant_employees e on z.USERID=e.id
       group by z.DEPARTMENTID,z.USERID
     ) as o where ct>1 and
    CONCAT(DEPARTMENTID,USERID) not in (select CONCAT(department_id,employee_id) from tenant_department_employee_relations)
              and DEPARTMENTID in (select id from tenant_departments) and maxID not in (select id from tenant_department_employee_relations) ;

/**
  设置用户的默认部门
 */
update tenant_employees k set k.department_id=(select c.department_id from tenant_department_employee_relations c where c.employee_id=k.id limit 1);


/**
  统计部门人员信息
 */
update tenant_departments x set total_member=(select count(1) from tenant_department_employee_relations c where c.department_id=x.id);


update tenant_contracts set attachment=REPLACE(SUBSTR(attachment,LOCATE( '"path":"', attachment)+8,(LOCATE('"url":', attachment)-LOCATE( '"path":"', attachment)-8-2)),'\\','')
where attachment like '[{%';