update t_user set FIELD29=SUPERIOR;
update t_user set SUPERIOR=null;

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

INSERT INTO public.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,COALESCE(a.LASTMODIFYTIME,'2021-01-01'),a.EMAIL,
  COALESCE(a.LASTMODIFYTIME,'2021-01-01'),null as job_number,COALESCE(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 COALESCE(null,3)=3 THEN 0 ELSE 1 END) as occupation_type,'' as position,
  COALESCE(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 public.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,COALESCE(a.LASTMODIFYTIME,'2021-01-01'),a.EMAIL,

  COALESCE(a.LASTMODIFYTIME,'2021-01-01'),null as job_number,COALESCE(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 COALESCE(null,3)=3 THEN 0 ELSE 1 END) as occupation_type,'' as position,

  COALESCE(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;

/**
  改回去
 */
update tenant_employees x
set superior_id=z.FIELD29
  from t_user z
where x.id=z.ID;

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

INSERT INTO public.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 department_id=(select c.department_id from tenant_department_employee_relations c where c.employee_id=k.id limit 1);

/**
导入部门管理员
 */
INSERT INTO public.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,']',',"__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;

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

/**
  证书信息
 */
INSERT INTO public.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,to_date(valid_date_from, 'DD Mon YYYY'),to_date(valid_date_to, 'DD Mon YYYY'),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,COALESCE(t.LASTMODIFYTIME,'2021-01-01') as created_time,
             COALESCE(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,
             COALESCE(t.FIELD18,'') as front_photo,COALESCE(t.FIELD12,'')as number, COALESCE(t.FIELD19,'')  as reverse_photo,
             COALESCE(t.FIELD16,'') as valid_date_from,COALESCE(t.FIELD17,'') as valid_date_to,
             (select k.id from tenant_users k where k.telephone=t.loginno limit 1) as tenant_user_id,
             COALESCE(t.FIELD15,'') as head_photo, t.NAME ,1 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 COALESCE(x.number,'')<>'' and number not in (select number from tenant_user_credentials)
    group by credential_type,number,x.selected
  ) 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 public.tenant_contracts set attachment=REPLACE(SUBSTR(attachment,POSITION( '"path":"'in attachment)+8,(POSITION('"url":'in attachment)-POSITION( '"path":"'in 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,'__2uz0l9F3qiXuMQZmma0'
from  tenant_employees u
where u.organization_id in (select id from t_domain d where d.BIND_APPLICATIONS like '%__xkeybkGaZa1SboC9GL1%' )
  and u.id not in (select p.USERID from t_user_department_role_set p where p.ROLEID='__2uz0l9F3qiXuMQZmma0');

/**
  设备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
set unify_social_credit_code=j.FIELD11, unify_social_credit_code_file=j.FIELD16
  from t_domain j
where x.id=j.ID and j.FIELD11 is not null and j.FIELD16 is not null;


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

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

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

    place_of_register_city_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) ,
    place_of_register_city_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1),
    place_of_register_district_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1),
    place_of_register_district_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1),
    industry_code=k.FIELD14,
    institutional_code=k.FIELD15
  from t_domain k
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
set selected_credential_id=z.id
  from tenant_user_credentials z
where z.tenant_user_id=x.id;

/**
  一寸免冠照片
 */
update tenant_users x
set one_inch_color_white_photo=z.field20
  from t_user z
where x.one_inch_color_white_photo is null and x.id=z.ID;

update t_user set FIELD16=null where FIELD16='';
update t_user set FIELD17=null where FIELD17='';
/**
  多个身份证绑定不同手机号码的情况
 */
INSERT INTO public.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,to_timestamp(COALESCE(t.LASTMODIFYTIME,'2021-01-01')::text, 'yyyy-MM-dd hh24:mi:ss') as created_time,
  COALESCE(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,
  COALESCE(t.FIELD18,'') as front_photo,CONCAT(COALESCE(t.FIELD12,''),'&',t.LOGINNO) as number,
  COALESCE(t.FIELD19,'')  as reverse_photo,
  to_date(COALESCE(t.FIELD16,'1900-01-01'), 'yyyy-MM-dd') as valid_date_from,to_date(COALESCE(t.FIELD17,'3050-01-01'), 'yyyy-MM-dd') as valid_date_to,
  (select k.id from tenant_users k where k.telephone=t.loginno limit 1) as tenant_user_id,
  COALESCE(t.FIELD15,'') as head_photo, t.NAME ,1 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
SET selected_credential_id=k.id
  from tenant_user_credentials k
where x.id=k.id and x.selected_credential_id is null;

update tenant_users x set  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 '&%';

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 set lon_lat_json =j.field17
  from t_domain j
where k.id=j.ID and k.lon_lat_json is null;


update tenant_users t
set
  birthdate = f.birthdate,
  education = f.education,
  household_type = f.household_type,
  marital_status = f.marital_status,
  nation = f.nation,
  military_status = f.military_status,
  politics_status = f.politics_status,
  sex = f.sex,
  stature = f.stature
  from tenant_users f
where t.nation is null and t.stature is null
  and t.id in
    (select ct.id from tenant_user_credentials ct JOIN tenant_user_credentials ct2 on ct.number like CONCAT(ct2.number,'%')
  where ct.tenant_user_id=t.id and ct2.tenant_user_id=f.id );


update tenant_users x
set wechat=field22
  from (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
where x.id=y.tenant_user_id;
-- 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 public.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 public.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 department_id=(select c.department_id from tenant_department_employee_relations c where c.employee_id=k.id limit 1);

/**
注意：再次确认入驻状态
 */
update tenant_employees z
set status=0
where id in (select t.id from t_user t where COALESCE(t.LASTMODIFYTIME,'2022-01-01')='2022-01-01')
   or not exists(select t.id from t_user t where t.ID=z.id);

update tenant_employees
set status=1
where id not in (select t.id from t_user t WHERE COALESCE(t.LASTMODIFYTIME,'2022-01-01')='2022-01-01');

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


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