#对比在职人员数量
select
    count(1)
from tenant_employees ee where ee.id like '%--_%' and ee.`status`=0 or
    (select ev.event_type from tenant_employee_events ev where ev.employee_id=ee.id
                                                           and ev.created_time>'2022-05-15 16:00:00'
                                                           and ev.event_type in (0,1) order by ev.created_time desc limit 1)=1;

SELECT
    count(1)
from (
         select
             ITEM_documentID,DOMAINID,
             (select k1.ITEM_outDate
              from baibaodunflow.tlk_employmentrecord k1
              where k1.DOMAINID=kg.DOMAINID and k1.ITEM_documentID=kg.ITEM_documentID
              order by IFNULL(ITEM_outDate,'2022-09-09') desc limit 1) as ITEM_outDate
    from (
		select
		 DISTINCT rd.ITEM_documentID,rd.DOMAINID
	from baibaodunflow.tlk_employmentrecord rd
	) as kg
) pk where pk.ITEM_outDate is null;

#$$$$$$查出所有在职但是跟入离职不一致的人员数据

select
    ee.id,ee.`status`,ee.is_domain_admin,ee.tenant_user_id,CONCAT("'",ee.tenant_user_id,"'") as tid,
    cc.number,cc.credential_type
from tenant_employees ee
         join tenant_user_credentials cc on ee.tenant_user_id=cc.tenant_user_id and cc.credential_type=0
where ee.id like '%--_%' and (
                                         ee.`status`=0 or
                                         (select ev.event_type from tenant_employee_events ev where ev.employee_id=ee.id
                                                                                                and ev.created_time>'2022-05-15 16:00:00' and ev.event_type in (0,1) order by ev.created_time desc limit 1)=1
    ) and CONCAT(cc.number,ee.organization_id) in (
  SELECT
     CONCAT(ITEM_documentID,DOMAINID)
	from (
		select
		 ITEM_documentID,DOMAINID,
		 (select k1.ITEM_outDate
			from baibaodunflow.tlk_employmentrecord k1
			where k1.DOMAINID=kg.DOMAINID and k1.ITEM_documentID=kg.ITEM_documentID
			order by IFNULL(ITEM_outDate,'2022-09-09') desc limit 1) as ITEM_outDate
		from (
			select
			 DISTINCT rd.ITEM_documentID,rd.DOMAINID
		from baibaodunflow.tlk_employmentrecord rd
		) as kg
	) pk where pk.ITEM_outDate is null
)

#查出所有在职人员跟入离职记录不一致的问题
select
    ee.id,ee.`status`,ee.is_domain_admin,ee.tenant_user_id,CONCAT("'",ee.tenant_user_id,"',") as tid,
    cc.number,cc.credential_type
from tenant_employees ee
         join tenant_user_credentials cc on ee.tenant_user_id=cc.tenant_user_id and cc.credential_type=0
where ee.id like '%--_%' and (
                                         ee.`status`=0 or
                                         (select ev.event_type from tenant_employee_events ev where ev.employee_id=ee.id
                                                                                                and ev.created_time>'2022-05-15 16:00:00' and ev.event_type in (0,1) order by ev.created_time desc limit 1)=1
    )
and CONCAT(cc.number,ee.organization_id) not in (
  SELECT
     CONCAT(ITEM_documentID,DOMAINID)
	from (
		select
		 ITEM_documentID,DOMAINID,
		 (select k1.ITEM_outDate
			from baibaodunflow.tlk_employmentrecord k1
			where k1.DOMAINID=kg.DOMAINID and k1.ITEM_documentID=kg.ITEM_documentID
			order by IFNULL(ITEM_outDate,'2022-09-09') desc limit 1) as ITEM_outDate
		from (
			select
			 DISTINCT rd.ITEM_documentID,rd.DOMAINID
		from baibaodunflow.tlk_employmentrecord rd
		) as kg
	) pk where pk.ITEM_outDate is null
)

#查出所在离职人员与入离职记录不一致的人员列表

select
    ee.id,ee.`status`,ee.is_domain_admin,ee.tenant_user_id,CONCAT("'",ee.tenant_user_id,"',") as tid,
    cc.number,cc.credential_type
from tenant_employees ee
         join tenant_user_credentials cc on ee.tenant_user_id=cc.tenant_user_id and cc.credential_type=0
where ee.id like '%--_%' and (
                                         ee.`status`=1 or
                                         (select ev.event_type from tenant_employee_events ev where ev.employee_id=ee.id
                                                                                                and ev.created_time>'2022-05-15 16:00:00' and ev.event_type in (0,1) order by ev.created_time desc limit 1)=0
    )
and CONCAT(cc.number,ee.organization_id) not in (
  SELECT
     CONCAT(ITEM_documentID,DOMAINID)
	from (
		select
		 ITEM_documentID,DOMAINID,
		 (select k1.ITEM_outDate
			from baibaodunflow.tlk_employmentrecord k1
			where k1.DOMAINID=kg.DOMAINID and k1.ITEM_documentID=kg.ITEM_documentID
			order by IFNULL(ITEM_outDate,'2022-09-09') desc limit 1) as ITEM_outDate
		from (
			select
			 DISTINCT rd.ITEM_documentID,rd.DOMAINID
		from baibaodunflow.tlk_employmentrecord rd
		) as kg
	) pk where pk.ITEM_outDate is null
)

#确认所有不在入离职记录的人员

select
    ee.id,ee.`status`,ee.is_domain_admin,ee.tenant_user_id,CONCAT("'",ee.tenant_user_id,"',") as tid,
    cc.number,cc.credential_type
from tenant_employees ee
         join tenant_user_credentials cc on ee.tenant_user_id=cc.tenant_user_id and cc.credential_type=0
where ee.id like '%--_%' and (
                                         ee.`status`=1 or
                                         (select ev.event_type from tenant_employee_events ev where ev.employee_id=ee.id
                                                                                                and ev.created_time>'2022-05-15 16:00:00' and ev.event_type in (0,1) order by ev.created_time desc limit 1)=0
    )
and CONCAT(cc.number,ee.organization_id) not in (
  SELECT
     CONCAT(ITEM_documentID,DOMAINID)
	from (
		select
		 ITEM_documentID,DOMAINID,
		 (select k1.ITEM_outDate
			from baibaodunflow.tlk_employmentrecord k1
			where k1.DOMAINID=kg.DOMAINID and k1.ITEM_documentID=kg.ITEM_documentID
			order by IFNULL(ITEM_outDate,'2022-09-09') desc limit 1) as ITEM_outDate
		from (
			select
			 DISTINCT rd.ITEM_documentID,rd.DOMAINID
		from baibaodunflow.tlk_employmentrecord rd
		) as kg
	) pk where pk.ITEM_outDate is null
)

#确认入离职记录的人没有在智能人事的人员列表
select count(1)
from obpm2.t_user t join baibaodunflow.tlk_employmentrecord rd on t.FIELD12 = rd.ITEM_documentID and t.DOMAINID=rd.DOMAINID
where CONCAT(t.FIELD12,rd.DOMAINID) not in
      (select
           CONCAT(cc.number,ee.organization_id)
       from tenant_employees ee join tenant_user_credentials cc on ee.tenant_user_id=cc.tenant_user_id and cc.credential_type=0
      )

#----------------------------------------------------------------------------------------------------------

/**
  批量修改合同信息
 */
select
    ac.employee_id,
    ac.a_name,
    ac.b_name,
    ac.organization_id,
    (select dd.id from tenant_employees dd where dd.tenant_user_id=ee.tenant_user_id and dd.organization_id=ac.organization_id limit 1) as employee_id2
from tenant_contracts ac join tenant_employees ee on ac.employee_id=ee.id
    join tenant_user_credentials cc on cc.tenant_user_id=ee.tenant_user_id and cc.credential_type=0
where ac.organization_id<>ee.organization_id and cc.number='230225196803293539';

update tenant_contracts ac join tenant_employees ee on ac.employee_id=ee.id
    join tenant_user_credentials cc on cc.tenant_user_id=ee.tenant_user_id and cc.credential_type=0
    set ac.employee_id=(select dd.id from tenant_employees dd where dd.tenant_user_id=ee.tenant_user_id and dd.organization_id=ac.organization_id limit 1),
        last_modifier_name=CONCAT(IFNULL(last_modifier_name,''),'20220528')
where ac.organization_id<>ee.organization_id
  and exists(select 1 from tenant_employees ex where ex.tenant_user_id=ee.tenant_user_id and ex.organization_id=ac.organization_id);

#调整错误的tenant_user_id
select
    telephone,formattedTel,old_tenant_user_id,new_tenant_user_id,
    concat("update tenant_employees set tenant_user_id='",new_tenant_user_id ,"' where tenant_user_id='",old_tenant_user_id,"';") as updateSql,
    concat("delete from tenant_employees where tenant_user_id='",old_tenant_user_id,"' and id like '%--__XQfjpd9pI9QLFaMYoYm' and STATUS;") as deleteSql
from (
         select
             id,telephone,formattedTel,id as old_tenant_user_id,
             (select u.id from tenant_users u where u.telephone=formattedTel limit 1) as new_tenant_user_id,
     (select u.`name` from tenant_users u where u.telephone=formattedTel limit 1) as tenant_user_name,
     `NAME`
    from (
			 select
						id,`NAME`,
						telephone,LOCATE('-',telephone),
						SUBSTRING(telephone,LOCATE('_',telephone)+1) as afterFormattedTel,
						SUBSTRING(telephone,1,LOCATE('--',telephone)-2) as beforeFormattedTel,
				 from tenant_users
				 where id like '%--__XQfjpd9pI9QLFaMYoYm' and telephone like '%--_%'
	) as ak
) as wa where wa.old_tenant_user_id in (select tenant_user_id from tenant_employees)


/**
  一条条执行的数据
 */
select u.`name`,u.telephone,`status`,tenant_user_id,ee.id,organization_id from tenant_employees ee join tenant_users u on ee.tenant_user_id=u.id
where tenant_user_id in ('954337263949848576--__XQfjpd9pI9QLFaMYoYm','599244378787020800--__qFCcCnozYNwzoG3O6Ms')
order by organization_id asc, tenant_user_id asc;

update tenant_employee_events set employee_id='D0BQP6S3' where employee_id='yqqn7C0p';
update tenant_department_admins set employee_id='D0BQP6S3' where employee_id='yqqn7C0p';
update tenant_contracts set employee_id='D0BQP6S3' where employee_id='yqqn7C0p';

delete from tenant_employees where id='yqqn7C0p';

/**
  查找没有导入到智能人事的入离职信息
 */
select id,ITEM_documentID,ITEM_phone,DOMAINID,ITEM_status,ITEM_entryDate,ITEM_outDate,ITEM_outRemark,
       (select u.ID from t_user u where u.FIELD12=rd.ITEM_documentID limit 1) as t_user_document_id,
    (select u.ID from t_user u where u.LOGINNO=rd.ITEM_phone limit 1) as t_user_loginno_id
from baibaodunflow.tlk_employmentrecord rd where not exists(
    select 1
    from tenant_employees ee join tenant_user_credentials cc on ee.tenant_user_id=cc.tenant_user_id
    where ee.organization_id=rd.DOMAINID and cc.number=rd.ITEM_documentID
    );

select *
from (
         select
             id,ITEM_documentID,ITEM_phone,DOMAINID,ITEM_status,ITEM_entryDate,ITEM_outDate,ITEM_outRemark,
             IFNULL(t_user_document_id,t_user_loginno_id) as tenant_user_id,
             IFNULL(from_tenant_user_credential_id,from_tenant_user_id) as from_tenant_user_id,

             t_user_loginno_id,t_user_document_id
         from (
                  select id,ITEM_documentID,ITEM_phone,DOMAINID,ITEM_status,ITEM_entryDate,ITEM_outDate,ITEM_outRemark,
                         (select u.ID from t_user u where u.FIELD12=rd.ITEM_documentID limit 1) as t_user_document_id,
              (select u.ID from t_user u where u.LOGINNO=rd.ITEM_phone limit 1) as t_user_loginno_id,
              (select tu.id from tenant_users tu where tu.telephone=rd.ITEM_phone limit 1) as from_tenant_user_id,
              (select cc.tenant_user_id from tenant_user_credentials cc where cc.number=rd.ITEM_documentID limit 1) as from_tenant_user_credential_id
             from baibaodunflow.tlk_employmentrecord rd where not exists(
             select 1
             from tenant_employees ee join tenant_user_credentials cc on ee.tenant_user_id=cc.tenant_user_id
             where ee.organization_id=rd.DOMAINID and cc.number=rd.ITEM_documentID
             )
     ) as lm
    ) mk ;

update tenant_users
set third_party_login_no= telephone
where third_party_login_no is null and telephone like '%--_%';

select
    CONCAT("update tenant_users set telephone='",rightTel,"' where id='",id,"' and telephone='",telephone,"';")
from (
         select
             (case when not exists(select 1 from tenant_users ck where ck.telephone=beforeFormattedTel) then beforeFormattedTel
                   when not exists(select 1 from tenant_users ck where ck.telephone=afterFormattedTel) then afterFormattedTel else NULL end) as rightTel,
             id,`NAME`,
             telephone,afterFormattedTel,beforeFormattedTel
         from (
                  select
                      id,`NAME`,
                      telephone,LOCATE('-',telephone),
                      SUBSTRING(telephone,LOCATE('_',telephone)+1) as afterFormattedTel,
                      SUBSTRING(telephone,1,LOCATE('--',telephone)-1) as beforeFormattedTel
                  from tenant_users
                  where id like '%--__XQfjpd9pI9QLFaMYoYm' and telephone like '%--_%'
              ) as kg
     ) as km;

select
    i.telephone as identity_telephone,
    u.telephone,
    CONCAT(
            "update identity_userpassword set user_name='",u.telephone,
            "' where id in (select p.id from identity_principals p where p.identity_user_id='",i.id,"') and user_name like '%--_%' and user_name like '%",u.telephone,"%';",
            "update identity_user set telephone='",u.telephone,"' where tenant_user_id='",i.tenant_user_id,"' and telephone like '%--_%' and telephone like '%",u.telephone,"%';")
from identity_user i join tenant_users u on i.tenant_user_id=u.id
where i.telephone like '%--_%' and u.telephone not like '%--_%';