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','',2,id,'2021-01-01',field14,field15,'2021-01-01',
       k.LOGO_URL,k.`NAME`,k.FIELD13,SUBSTRING_INDEX(FIELD13,',',1),SUBSTRING_INDEX(FIELD13,',',1),
       SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1),SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1),
       SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1),SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1),

       k.FIELD13,SUBSTRING_INDEX(FIELD12,',',1),SUBSTRING_INDEX(FIELD12,',',1),
       SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1),SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1),
       SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1),SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1),
       (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)


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

/**
  修改部门
 */
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);
/**
  需要循环执行几次, 类似如下操作进行修改
  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);

INSERT INTO `obpm2`.`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`)
select
    a.id,IFNULL(a.LASTMODIFYTIME,'2021-01-01'),a.EMAIL,

    b.ITEM_entryDate,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,
    b.ITEM_leaveDate as leave_time,0,
    (case when IFNULL(ITEM_jobType,3)=3 THEN 0 ELSE 1 END) as occupation_type,'' as position,

		b.ITEM_entryDate as positive_date,
		null as salary, null as salary_bank_number,
	 (case when IFNULL(b.ITEM_entryDate,'2022-01-01')='2022-01-01' then 1 else 0 end)as `status`,

	 null as work_place,
	 a.DOMAINID,a.SUPERIOR as superior_id,(select c.id from tenant_users c where c.telephone=a.telephone limit 1),
	 null,a.DEFAULTDEPARTMENT
from t_user a join baibaodunflow.tlk_securityman b on a.FIELD12=b.ITEM_documentID and b.DOMAINID= a.DOMAINID 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 `obpm2`.`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`)
select
    id,LASTMODIFYTIME,EMAIL,ITEM_entryDate,job_number,LASTMODIFYTIME,leave_note,leave_operator_created_time,leave_operator_id,leave_operator_name,
    leave_time,0,occupation_type,position,positive_date,salary,salary_bank_number,`status`,work_place,DOMAINID,superior_id,tenant_user_id,
    null,DEFAULTDEPARTMENT
from (

         select
             a.id,IFNULL(a.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,a.EMAIL,

             b.ITEM_entryDate,null as job_number,
             null as leave_note,null as leave_operator_created_time,null as leave_operator_id,null as leave_operator_name,
             b.ITEM_leaveDate as leave_time,
             (case when IFNULL(ITEM_jobType,3)=3 THEN 0 ELSE 1 END) as occupation_type,'' as position,

		b.ITEM_entryDate as positive_date,
		null as salary, null as salary_bank_number,
	 (case when IFNULL(b.ITEM_entryDate,'2022-01-01')='2022-01-01' then 1 else 0 end)as `status`,

	 null as work_place,
	 a.DOMAINID,a.SUPERIOR as superior_id,(select c.id from tenant_users c where c.telephone=a.telephone limit 1) as tenant_user_id,

	 (case when IFNULL(a.DEFAULTDEPARTMENT,'')='' then (select y1.ID from t_department y1 where y1.DOMAIN_ID=y1.INDEXCODE or y1.ID = y1.INDEXCODE or CONCAT(y1.ID,'-')=y1.INDEXCODE  limit 1) else a.DEFAULTDEPARTMENT end) as DEFAULTDEPARTMENT
from t_user a join baibaodunflow.tlk_securityman b on a.FIELD12=b.ITEM_documentID and b.DOMAINID= a.DOMAINID
join tenant_organizations c on c.ID=a.DOMAINID where a.ID not in (select k.id from tenant_employees k)
     ) as o where o.DEFAULTDEPARTMENT in (select id from tenant_departments) ;

/**
  部门用户关系
 */
INSERT INTO `obpm2`.`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);

/**
  设置部门管理员
 */
INSERT INTO `obpm2`.`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);