/**
  删除之前错误的迁移数据
 */
delete from per_org_relation where perId in (select perId from per_base_info x where x.tlkPerId like '%--_%' and SUBSTR(x.tlkPerId,1,LOCATE('-',x.tlkPerId)-1) REGEXP '^[0-9]+$');
delete from sys_user where user_id in (select perId from per_base_info x where x.tlkPerId like '%--_%' and SUBSTR(x.tlkPerId,1,LOCATE('-',x.tlkPerId)-1) REGEXP '^[0-9]+$');
delete from per_base_info x where x.tlkPerId like '%--_%' and SUBSTR(x.tlkPerId,1,LOCATE('-',x.tlkPerId)-1) REGEXP '^[0-9]+$';
delete from com_base_info where tlkDomainId like '%--_%' and SUBSTR(tlkDomainId,1,LOCATE('-',tlkDomainId)-1) REGEXP '^[0-9]+$';

/**
  迁移数据
 */
SELECT
    a.id,
    SUBSTR(a.id,1,LOCATE('-',a.id)-1) as nid,
    CONCAT( "insert into com_base_info(comId,comName,comCerNo,comCerType,tlkDomainId,tlkComId,createTime,updateTime,comTreeCode,regProvince,regCity,regArea,regAddress,comType,institutionType,industryType)
select 80000", SUBSTR(a.id,1,LOCATE('-',a.id)-1), ",'", a.NAME, "','",IFNULL(b.unify_social_credit_code,""), "',2,'", a.id, a.id, "','", current_timestamp(), "','",current_timestamp(), "','",IFNULL(a.place_of_register_province_code,""),"','",IFNULL(a.place_of_register_city_code,""),"','','",IFNULL(a.place_of_register_district_code,""),"','",IFNULL(a.place_of_register_address,""),
            "',","'9','0102','01'"," from dual where not exists (SELECT 1 from com_base_info where tlkDomainId = '",a.id,"');") AS sqldata
FROM tenant_organizations a INNER JOIN tenant_companies b on a.id = b.id
where SUBSTR(a.id,1,LOCATE('-',a.id)-1) REGEXP '^[0-9]+$' limit 10;

SELECT g.sqldata
from (
         SELECT CONCAT("insert into sys_user(user_id,login_name) select ",SUBSTR(b.id,1,LOCATE('-',b.id)-1),",'",IFNULL(a.telephone,""),"'from dual where not exists (SELECT 1 from sys_user where login_name = '",IFNULL(a.telephone,""),"');",
                       "insert into per_base_info(sex,birthday,perType,perId,comId,userId,mobilePhone,name,idCardType,idCardNo,createTime,tlkPerId) select '",IFNULL(a.sex,""),"','",
                       IFNULL(a.birthdate,""),"',","'010001','",SUBSTR(b.id,1,LOCATE('-',b.id)-1),"',comId",",'",SUBSTR(b.id,1,LOCATE('-',b.id)-1),"','",IFNULL(a.telephone,""),"','",IFNULL(a.name,""),"','",
                       IFNULL(c.credential_type,""),"','",IFNULL(c.number,""),"','",current_timestamp(),"','",b.id,"' from com_base_info where tlkComId='",

                       IFNULL(b.organization_id,""),"'  and not exists (SELECT 1 from per_base_info where tlkPerId = '",b.id,"');",
                       "insert into per_org_relation(entryDate,orgType,perOrgRelationId,perId,orgId,comId,createTime,tlkRelationId)
                       select '",IFNULL(b.hired_date,""),"',2,'",SUBSTR(b.id,1,LOCATE('-',b.id)-1),"','",SUBSTR(b.id,1,LOCATE('-',b.id)-1),"',ifnull((select departId from com_depart where tlkDepartId='",
                       IFNULL(b.department_id,''),"' limit 1),comId),comId,'2022-10-10','",b.id,"' from com_base_info
where tlkComId='",b.organization_id,"' and not exists (SELECT 1 from per_org_relation where tlkRelationId = '",b.id,"');"
                    ) as sqldata
         from tenant_users as a ,
              tenant_employees as b,tenant_user_credentials as c  where a.id = b.tenant_user_id and a.id = c.tenant_user_id and SUBSTR(b.id,1,LOCATE('-',b.id)-1) REGEXP '^[0-9]+$'
     ) as g ;

/**
  非迁移数据
 */
SELECT
    CONCAT( "insert into com_base_info(comId,comName,comCerNo,comCerType,tlkDomainId,createTime,updateTime,comTreeCode,regProvince,regCity,regArea,regAddress,comType,institutionType,industryType)
select 80000", ( @row_number:=@row_number + 1), ",'", a.NAME, "','",IFNULL(b.unify_social_credit_code,""), "',2,'", a.id, "','", current_timestamp(), "','",current_timestamp(), "','",IFNULL(a.place_of_register_province_code,""),"','",IFNULL(a.place_of_register_city_code,""),"','','",IFNULL(a.place_of_register_district_code,""),"','",IFNULL(a.place_of_register_address,""),
            "',","'9','0102','01'"," from dual where not exists (SELECT 1 from com_base_info where tlkDomainId = '",a.id,"');") AS sqldata
FROM tenant_organizations a INNER JOIN tenant_companies b on a.id = b.id,(SELECT @customer_no:=0,@row_number:=0) as t
where a.id not like '%--_%';

SELECT g.sqldata
from (
         SELECT CONCAT("insert into sys_user(user_id,login_name) select ",@rownumber,",'",IFNULL(a.telephone,""),"'from dual where not exists (SELECT 1 from sys_user where login_name = '",IFNULL(a.telephone,""),"');",
                       "insert into per_base_info(sex,birthday,perType,perId,comId,userId,mobilePhone,name,idCardType,idCardNo,createTime,tlkPerId) select '",IFNULL(a.sex,""),"','",
                       IFNULL(a.birthdate,""),"',","'010001','",@rownumber,"',comId",",'",@rownumber,"','",IFNULL(a.telephone,""),"','",IFNULL(a.name,""),"','",
                       IFNULL(c.credential_type,""),"','",IFNULL(c.number,""),"','",current_timestamp(),"','",b.id,"' from com_base_info where tlkComId='",

                       IFNULL(b.organization_id,""),"'  and not exists (SELECT 1 from per_base_info where tlkPerId = '",b.id,"');",
                       "insert into per_org_relation(entryDate,orgType,perOrgRelationId,perId,orgId,comId,createTime,tlkRelationId)
                       select '",IFNULL(b.hired_date,""),"',2,'",@rownumber,"','",@rownumber,"',ifnull((select departId from com_depart where tlkDepartId='",
                       IFNULL(b.department_id,''),"' limit 1),comId),comId,'2022-10-10','",b.id,"' from com_base_info
where tlkComId='",b.organization_id,"' and not exists (SELECT 1 from per_org_relation where tlkRelationId = '",b.id,"');"


                    ) as sqldata,
                (@rownumber :=@rownumber + 1) AS line

         from (select @rownumber:=100000) i,tenant_users as a ,tenant_employees as b,tenant_user_credentials as c  where a.id = b.tenant_user_id and a.id = c.tenant_user_id

     ) as g where g.id not like '%--_%';