INSERT INTO `dispatch`.`rd_employee_summary`(`id`, `NAME`, `head_photo`, `contact`, `company_id`, `company_name`, `STATUS`, `checked_status`, `authenticated_status`, `duty_status`, `military_status`, `security_certificate_no`, `lon_lat`, `occupation_type`, `hired_date`, `leave_time`, `insure`, `organization_id`, `tenant_user_id`, `tenant_employee_id`, `supervise_domain_id`, `supervise_depart_id`, `last_sync_time`, `last_updated_time`, `device_number`, `tenant_im_user_id`, `security_station_id`, `security_station_name`)
select
    ee.id,u.`name`,cc.front_photo,u.telephone,ee.organization_id,
    oo.`name`,ee.`status`,u.checked_status,u.authenticated_status,0,
    u.marital_status,
    (select p2.number from obpm2.tenant_user_credentials p2 where p2.tenant_user_id=u.id and p2.credential_type=7 limit 1),
	 GeomFromText('POINT(1 1)'),
	 ee.occupation_type,CURRENT_DATE,ee.leave_time,IFNULL(ee.insure,false),ee.organization_id,u.id,ee.id,
	 IFNULL(
	 (select x2.organization_id
	 from
baibaodunflow.tlk_attendance_site_person_info info
join baibaodunflow.tlk_attendance_site_base_info xx on xx.id=info.ITEM_ATTENDANCESITEID
join obpm2.tenant_departments x2 on x2.id=ITEM_OFFICEPOLICEADDRESSID
where info.ITEM_SECURITYID=ee.id and xx.ITEM_ATTENDANCESTARTDATE<=CURRENT_DATE and xx.ITEM_ATTENDANCEENDDATE>=CURRENT_DATE limit 1),'#'),

	 ifnull((select xx.ITEM_OFFICEPOLICEADDRESSID
	 from baibaodunflow.tlk_attendance_site_person_info info
join baibaodunflow.tlk_attendance_site_base_info xx on xx.id=info.ITEM_ATTENDANCESITEID
   where info.ITEM_SECURITYID=ee.id and xx.ITEM_ATTENDANCESTARTDATE<=CURRENT_DATE and xx.ITEM_ATTENDANCEENDDATE>=CURRENT_DATE limit 1),'#'),

	 CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'device_number',u.im_identity,
	 (select xx.ID
	 from baibaodunflow.tlk_attendance_site_person_info info
join baibaodunflow.tlk_attendance_site_base_info xx on xx.id=info.ITEM_ATTENDANCESITEID
   where info.ITEM_SECURITYID=ee.id and xx.ITEM_ATTENDANCESTARTDATE<=CURRENT_DATE and xx.ITEM_ATTENDANCEENDDATE>=CURRENT_DATE limit 1),
	 (select xx.ITEM_ATTENDANCESITENAME
	 from baibaodunflow.tlk_attendance_site_person_info info
join baibaodunflow.tlk_attendance_site_base_info xx on xx.id=info.ITEM_ATTENDANCESITEID
   where info.ITEM_SECURITYID=ee.id and xx.ITEM_ATTENDANCESTARTDATE<=CURRENT_DATE and xx.ITEM_ATTENDANCEENDDATE>=CURRENT_DATE limit 1)

from obpm2.tenant_users u join obpm2.tenant_employees ee on u.id=ee.tenant_user_id
    join obpm2.tenant_user_credentials cc on cc.id=u.selected_credential_id
    join obpm2.tenant_organizations oo on oo.id=ee.organization_id;

INSERT INTO `dispatch`.`rd_company_summary`(`id`, `NAME`, `legal`, `legal_telephone`, `institutional`, `register_address`, `business_address`, `count_of_activity_station`, `count_of_security_man`, `service_scope`, `lon_lat`, `organization_id`, `supervise_region_code`, `status`, `last_sync_time`, `last_updated_time`)
select
    o.id,o.`name`,CONCAT('张',rand()),CONCAT('132443',rand()),'保安服务公司','北京市市辖区西城区','北京市市辖区西城区',100,
    2000,'门卫,巡逻,守护',null,o.id,'110000',0,'2023-03-30','2023-03-30'
from obpm2.tenant_organizations o;

INSERT INTO `dispatch`.`rd_security_station_summary`(`id`, `NAME`, `station_type`, `company_name`, `principal_id`, `principal_name`, `principal_contact`, `service_scope`, `address`, `count_of_security_man`, `security_certificate_no`, `lon_lat`, `organization_id`, `supervise_domain_id`, `supervise_depart_id`, `last_sync_time`, `last_updated_time`, `site_state`, `begin_date`, `end_date`)
select
    id,`ITEM_ATTENDANCESITENAME`,ITEM_ATTENDANCESITETYPE,
    x.ITEM_COMPANYNAME,x.ITEM_PRINCIPAL,x.ITEM_PRINCIPALNAME,x.ITEM_PRINCIPALPHONENO,x.ITEM_SERVEOBJECTTYPE,
    x.ITEM_ATTENDANCESITEFULLADDRESS,100,20,null,DOMAINID,ITEM_OFFICEPOLICEADDRESSID,ITEM_OFFICEPOLICEADDRESSID,
    x.LASTMODIFIED,x.LASTMODIFIED,ITEM_ATTENDANCESITESTATE,
    '2021-02-01','2023-05-04'
from baibaodunflow.tlk_attendance_site_base_info x;

update dispatch.rd_security_station_summary set  supervise_depart_id='BJ_TLK_DMT10',supervise_domain_id='BJ_TLK_DMT10' where pk_id <5374;
update dispatch.rd_security_station_summary set  supervise_depart_id='BJ_TLK_DMT11',supervise_domain_id='BJ_TLK_DMT11' where pk_id >5374 and pk_id >50374 ;
update dispatch.rd_security_station_summary set  supervise_depart_id='BJ_TLK_DMT1001',supervise_domain_id='BJ_TLK_DMT1001' where pk_id >50374 and pk_id >70374 ;

