drop PROCEDURE securityflow.`proc_rd_employee_summary`;
CREATE PROCEDURE securityflow.`proc_rd_employee_summary`(
	IN p_id VARCHAR ( 100 ),
	IN p_tenant_user_id VARCHAR ( 50 ),
	IN p_employ_status TINYINT,
	IN p_occupation_type TINYINT,
	IN p_hired_date TIMESTAMP,
	IN p_leave_date TIMESTAMP,
	IN p_organization_id varchar(100),
    IN p_last_sync_time TIMESTAMP
)
BEGIN
  declare d_name VARCHAR(100);
  declare d_number VARCHAR(100);
  declare d_sex TINYINT;
  declare d_telephone VARCHAR(100);
  declare d_organization_name VARCHAR(100);
	declare d_security_certificate_number VARCHAR(100);
	declare d_authenticated_status TINYINT;
	declare d_military_status TINYINT;
	declare d_checked_status TINYINT;
	declare d_lon_lat POINT;
	declare d_latest_employeeId varchar(100);
	declare d_region_code varchar(50);
	declare d_supervise_address_id varchar(100);
	set d_lon_lat = POINT(10.1,11.0);

   if p_organization_id is not null then
        select
            dl.ITEM_REGIONId,tk.item_registerPoliceAddressID into d_region_code,d_supervise_address_id
        from organizationauth.tlk_DepartmentLevel dl JOIN baibaodunflow.tlk_SetSupervise tk ON dl.id = tk.item_registerPoliceAddressID
        where tk.domainid=p_organization_id limit 1;
        if d_region_code is null then
          set d_region_code='#';
        end if;
        if d_supervise_address_id is null then
          set d_supervise_address_id='#';
        end if;

        select
            u.`name`,c.number,u.sex,u.TELEPHONE,
                (select o.`name` from obpm2.tenant_organizations o where o.id=p_organization_id limit 1),
                (select uc.number from obpm2.tenant_user_credentials uc where uc.tenant_user_id=u.id and uc.credential_type=7 limit 1),
                (select ee.id from obpm2.tenant_employees ee where ee.tenant_user_id=u.id order by ee.hired_date desc limit 1),
                u.authenticated_status,(case when u.military_status='服过' then 1 else 0 end),
                u.checked_status
                into d_name,d_number,d_sex,
                d_telephone,d_organization_name,d_security_certificate_number,
                d_latest_employeeId,
                d_authenticated_status,d_military_status,d_checked_status
        from obpm2.tenant_users u left join obpm2.tenant_user_credentials c
        on u.selected_credential_id=c.id where u.id=p_tenant_user_id;

        if p_id = d_latest_employeeId then
      INSERT INTO securityflow.`rd_employee_summary`
        (`id`, `tenant_user_id`, `name`, `idCard`, `sex`, `telephone`, `company_id`, `company_name`, `authenticate_status`, `security_certificate_number`,
        `military_status`, `lon_lat`, `employ_status`, `dispatch_device_json`, `last_sync_time`, `last_updated_time`,
        occupation_type,checked_status,region_code,supervise_address_id)values
     (p_id, p_tenant_user_id,d_name,d_number,d_sex,d_telephone,p_organization_id,d_organization_name,
             d_authenticated_status,d_security_certificate_number,d_military_status,d_lon_lat,p_employ_status,'[]',CURRENT_TIMESTAMP,
             p_last_sync_time,p_occupation_type,d_checked_status,d_region_code,d_supervise_address_id)
     ON DUPLICATE KEY UPDATE `name`=d_name, `idCard`=d_number, `sex`=d_sex,
     `telephone`=d_telephone, `company_id`=p_organization_id, `company_name`=d_organization_name,
     `authenticate_status`=d_authenticated_status, `security_certificate_number`=d_security_certificate_number,
     `employ_status`=p_employ_status,last_updated_time=p_last_sync_time,military_status=d_military_status,
     lon_lat=d_lon_lat,last_sync_time=CURRENT_TIMESTAMP,occupation_type=p_occupation_type,checked_status=d_checked_status,
     region_code=d_region_code,supervise_address_id=d_supervise_address_id;
     end if;

     insert into rd_employee_summary_history
     (
        id,tenant_user_id,employ_status,hired_date,leave_date,company_id,company_name,last_sync_time,last_updated_time,region_code,supervise_address_id
     )values(p_id, p_tenant_user_id,p_employ_status,p_hired_date,p_leave_date,p_organization_id,d_organization_name,CURRENT_TIMESTAMP,p_last_sync_time,
             d_region_code,d_supervise_address_id
     ) ON DUPLICATE KEY UPDATE employ_status=p_employ_status,hired_date=p_hired_date,leave_date=p_leave_date,
     company_id=p_organization_id,company_name=d_organization_name,last_sync_time=CURRENT_TIMESTAMP,
     last_updated_time=p_last_sync_time,region_code=d_region_code,supervise_address_id=d_supervise_address_id;
 end if;
END;

demo:
call securityflow.`proc_rd_employee_summary`('003z7uXv','nAbqua3i',0,0,'2022-01-02 00:00:00',null,'RTarXCMs','2022-02-09 05:44:31');