drop PROCEDURE securityflow.`proc_rd_company_summary`;
CREATE PROCEDURE securityflow.`proc_rd_company_summary`(
	IN p_id VARCHAR ( 100 ),#domainId
	IN p_old_company_name VARCHAR ( 200 ),
	IN p_company_name VARCHAR ( 200 ),
	IN p_legalPerson VARCHAR ( 200 ),
	IN p_registerAddress VARCHAR ( 200 ),
	IN p_registerAddressDetail VARCHAR ( 200 ),
	IN p_officeAddress VARCHAR ( 200 ),
	IN p_officeAddressDetail VARCHAR ( 200 ),
	IN p_businessScope VARCHAR ( 200 ),
	IN p_companyStatus VARCHAR ( 200 ),
	IN p_last_updated_time TIMESTAMP
)
BEGIN

 	declare d_region_code varchar(50);
	declare d_supervise_address_id varchar(100);

    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_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;
  set p_registerAddress = IFNULL(p_registerAddress,'');
	set p_registerAddressDetail = IFNULL(p_registerAddressDetail,'');
	set p_officeAddress = IFNULL(p_officeAddress,'');
	set p_officeAddressDetail = IFNULL(p_officeAddressDetail,'');


  INSERT INTO `securityflow`.`rd_company_summary`(
  `id`, `name`, `legal`, `registerAddress_name`,
  `registerAddress_detail`, `businessAddress_name`, `businessAddress_detail`, `service_scope`,
  `lonLat`, `status`, `serviceStationCount`, `activeSecurityManCount`, `last_sync_time`,
  `last_updated_time`,region_code,supervise_address_id)
  VALUES (p_id, ifnull(p_company_name,''), p_legalPerson, ifnull(p_registerAddress,''),
	ifnull(p_registerAddressDetail,''), ifnull(p_officeAddress,''), ifnull(p_officeAddressDetail,''), p_businessScope, '', (case when p_companyStatus='正常' then 1 else 0 end), 0, 0, CURRENT_TIMESTAMP, p_last_updated_time,d_region_code,d_supervise_address_id) ON DUPLICATE KEY UPDATE `name`=ifnull(p_company_name,''),legal=p_legalPerson,registerAddress_name=ifnull(p_registerAddress,''),registerAddress_detail=ifnull(p_registerAddressDetail,''),businessAddress_name=ifnull(p_officeAddress,''),businessAddress_detail=ifnull(p_officeAddressDetail,''),service_scope=p_businessScope,`status`=(case when p_companyStatus='正常' then 1 else 0 end),
	last_sync_time=CURRENT_TIMESTAMP,
	last_updated_time=p_last_updated_time,region_code=d_region_code,supervise_address_id=d_supervise_address_id;

  if p_old_company_name <> p_company_name then
    update rd_employee_summary set company_name=p_company_name where company_id=p_id;
    update rd_employee_summary_history set company_name=p_company_name where company_id=p_id;
    update rd_security_service_station_summary set company_name=p_company_name where company_id=p_id;
  end if;
END;

demo:
call proc_rd_company_summary('OD6i7ceRbaHXKEOJtJI','lin123-有限公司phhahccc','傅冠南','北京市市辖区通州区','软件园三期','湖南省长沙市天心区','xxxx','scope','正常','2022-01-01');