SELECT
    CONCAT( "insert into com_base_info(comId,comName,comCerNo,comCerType,tlkDomainId,createTime,updateTime,comTreeCode,regProvince,regCity,regArea,regAddress,comType,institutionType,industryType)
select ", ( @row_number:=@row_number + 10000), ",'", 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;


SELECT
    CONCAT( "insert into com_depart (departId,comId,departName,parentDepartId,seq,isDelete,createTime,tlkDepartId,departTreeCode)
            select '",( @row_number:=@row_number + 30000),"',comId,'",NAME,
            "',(case when ",ifnull(PARENT_ID,''),"='' then NULL else ((select departId from com_depart where tlkDepartId='",ifnull(PARENT_ID,''),"' limit 1)) end),",
            display_order,",0,current_timestamp(),'",id,"',
            concat((case when ",ifnull(PARENT_ID,''),"='' then '' else (select departId from com_depart where tlkDepartId='",ifnull(PARENT_ID,''),"'limit 1) end),'",@row_number,"','-') from com_base_info
            where tlkComId='",
            organization_id,
            "' where not exists (SELECT 1 from com_depart where tlkDepartId = '",
            id,
            ")"
        ) AS sqldata
FROM
    tenant_departments,(SELECT @customer_no:=0,@row_number:=0) as t;


SELECT
    CONCAT("insert into sys_user(user_id,login_name) select '",( @row_number:=@row_number + 5000),"','",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','",@row_number,"',comId",",'",@row_number,"','",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)
            select '",IFNULL( b.hired_date, "" ),"',2,'",@row_number,"','",@row_number,"',ifnull((select departId from com_depart where tlkDepartId='",IFNULL( b.department_id, '' ),"' limit 1),comId),comId,current_timestamp()
            from com_base_info where tlkDomainId='",b.organization_id,"' and not exists (SELECT 1 from per_base_info where tlkPerId = '",b.id,"');"
        ) AS sqldata
FROM
    tenant_users AS a join tenant_employees AS b on a.id=b.tenant_user_id
                      join tenant_user_credentials AS c on a.selected_credential_id=c.ID
   ,(SELECT @row_number:=0) as t
where a.id not in (select id from sync_pasp_history where type=0);







