delete from dispatch.rd_employee_summary where id in (
    SELECT
        ee.id
    FROM
        obpm2.tenant_users u
            LEFT JOIN obpm2.tenant_user_credentials c ON u.selected_credential_id = c.id
            JOIN obpm2.tenant_employees ee ON ee.tenant_user_id = u.id
            JOIN obpm2.tenant_organizations o ON o.id = ee.organization_id
);


INSERT INTO dispatch.rd_employee_summary (
    id,
    duty_status,
    company_id,
    NAME,
    head_photo,
    contact,
    company_name,
    STATUS,
    checked_status,
    authenticated_status,
    military_status,
    security_certificate_no,
    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,
    idCard_no,
    cid,
    supervise_region_code
) SELECT
      ee.id,
      0,
      ee.organization_id,
      u.`name`,
      c.head_photo,
      SUBSTR(ifnull(u.telephone,''),1,10) as telephone,
      o.`name` AS orgName,
      ee.`status`,
      u.checked_status,
      u.authenticated_status,
      u.military_status,(
          SELECT
              c2.number
          FROM
              obpm2.tenant_user_credentials c2
          WHERE
                  c2.tenant_user_id = u.id
            AND c2.credential_type = 7
                  LIMIT 1
    ) AS securityCredentialNo,
	ee.occupation_type,
	IFNULL( ee.hired_date, CURRENT_TIMESTAMP ) AS hired_date,
	ee.leave_time AS leave_time,
	ifnull( ee.insure, 0 ),
	ee.organization_id,
	ee.tenant_user_id,
	ee.id,
	'#supervise_domain_id-20230407',
	ifnull((
		SELECT
			t2.ITEM_OFFICEPOLICEADDRESSID
		FROM
			baibaodunflow.tlk_attendance_site_base_info t2
			JOIN baibaodunflow.tlk_attendance_site_person_info t3 ON t2.id = t3.ITEM_ATTENDANCESITEID
		WHERE
			t3.ITEM_SECURITYID = ee.id
		ORDER BY
			t2.ITEM_ATTENDANCESTARTDATE DESC
			LIMIT 1
			),
		'TLK_DMT4501002'
	) AS supervise_depart_id,
	ee.last_sync_time AS last_sync_time,
	ee.last_updated_time AS last_updated_time,
	'device_number',
	ifnull( u.im_identity, '#' ),
	ifnull((
		SELECT
			t2.id
		FROM
			baibaodunflow.tlk_attendance_site_base_info t2
			JOIN baibaodunflow.tlk_attendance_site_person_info t3 ON t2.id = t3.ITEM_ATTENDANCESITEID
		WHERE
			t3.ITEM_SECURITYID = ee.id
		ORDER BY
			t2.ITEM_ATTENDANCESTARTDATE DESC
			LIMIT 1
			),
		'#'
	) AS security_station_id,
	ifnull((
		SELECT
			t2.ITEM_ATTENDANCESITENAME
		FROM
			baibaodunflow.tlk_attendance_site_base_info t2
			JOIN baibaodunflow.tlk_attendance_site_person_info t3 ON t2.id = t3.ITEM_ATTENDANCESITEID
		WHERE
			t3.ITEM_SECURITYID = ee.id
		ORDER BY
			t2.ITEM_ATTENDANCESTARTDATE DESC
			LIMIT 1
			),
		'#'
	) AS security_station_name,
	c.number,
	u.cid,
	'110000'
FROM
    obpm2.tenant_users u
    LEFT JOIN obpm2.tenant_user_credentials c ON u.selected_credential_id = c.id
    JOIN obpm2.tenant_employees ee ON ee.tenant_user_id = u.id
    JOIN obpm2.tenant_organizations o ON o.id = ee.organization_id