function fire($job, $vatsim_id)
{
$pilot = Pilot::whereVatsimId($vatsim_id)->first();
if ($pilot->processing == 1) {
$job->delete();
return;
}
try {
$it = new XmlIterator\XmlIterator('https://cert.vatsim.net/vatsimnet/idstatusint.php?cid=' . $vatsim_id, 'user');
$official = iterator_to_array($it)[0];
$pilot->name = $official['name_first'] . ' ' . $official['name_last'];
$pilot->rating_id = $official['rating'];
} catch (ErrorException $e) {
}
$newFlights = array();
$flights = Flight::whereVatsimId($pilot->vatsim_id)->whereState(2)->get();
$totalDistance = 0;
$totalDuration = 0;
$totalFlights = $flights->count();
foreach ($flights as $flight) {
if ($flight->processed == 1) {
$totalDistance += $flight->distance;
$totalDuration += $flight->duration;
} else {
$callsign = str_replace('-', '', strtoupper($flight->callsign));
if (!is_null($airline = $this->getAirlines($callsign))) {
// Airline
$flight->isAirline($airline->icao);
unset($airline);
} elseif (!is_null($registration = $this->getRegistrations($callsign))) {
$flight->isPrivate($registration->country_id);
unset($registration);
}
if (!is_null($flight->departure_time) && !is_null($flight->arrival_time)) {
$duration = $this->duration($flight->departure_time, $flight->arrival_time);
$flight->duration = $duration;
$totalDuration += $duration;
unset($duration);
}
$distance = 0;
foreach ($flight->positions as $key => $position) {
if ($key > 0) {
$distance += $this->distance($position->lat, $position->lon, $previous->lat, $previous->lon);
}
$previous = $position;
}
$flight->distance = $distance;
// $flight->processed = true;
// $flight->save();
if (!is_nan($distance)) {
$totalDistance += $distance;
}
unset($distance, $previous);
}
$newFlights[] = array('id' => $flight->id, 'duration' => $flight->duration, 'distance' => $flight->distance, 'airline_id' => $flight->airline_id, 'callsign_type' => $flight->callsign_type);
unset($flight);
}
unset($flights);
Log::info('queue:legacy[' . $job->getJobId() . '] - processed flights');
DB::statement("create temporary table if not exists flights_temp (\n\t\t\t`id` int(10) unsigned NOT NULL,\n\t\t\t`callsign_type` tinyint(1) NOT NULL DEFAULT '0',\n\t\t\t`airline_id` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,\n\t\t\t`duration` smallint(6) NOT NULL DEFAULT '0',\n\t\t\t`distance` smallint(6) NOT NULL DEFAULT '0',\n\t\t\tPRIMARY KEY (`id`)\n\t\t)");
Log::info('queue:legacy[' . $job->getJobId() . '] - created temp table flights');
$remaining = count($newFlights);
$step = 0;
do {
Log::info('queue:legacy[' . $job->getJobId() . '] - inserted flights - ' . $remaining);
DB::table('flights_temp')->insert(array_slice($newFlights, 100 * $step, 100));
$remaining -= 100;
$step++;
} while ($remaining > 0);
Log::info('queue:legacy[' . $job->getJobId() . '] - inserted flights - done');
DB::statement("update flights dest, flights_temp src set\n\t\t\tdest.callsign_type = src.callsign_type,\n\t\t\tdest.airline_id = src.airline_id,\n\t\t\tdest.duration = src.duration,\n\t\t\tdest.distance = src.distance,\n\t\t\tdest.processed = 1\n\t\twhere dest.id = src.id\n\t\t");
Log::info('queue:legacy[' . $job->getJobId() . '] - updated flights');
$atcs = ATC::whereVatsimId($vatsim_id)->whereNotNull('end')->get();
$totalDurationAtc = 0;
$totalAtc = $atcs->count();
$newAtc = array();
foreach ($atcs as $atc) {
if ($atc->processed) {
if ($atc->facility_id != 99) {
$totalDurationAtc += $atc->duration;
} else {
$totalAtc--;
}
} else {
$atc->facility_id = ends_with($atc->callsign, '_ATIS') ? 99 : $atc->facility_id;
$duration = $this->duration($atc->start, $atc->end);
$atc->duration = $duration;
if ($atc->facility_id != 99) {
$totalDurationAtc += $duration;
}
if ($atc->facility_id < 6) {
$airport = Airport::select('icao')->whereIcao(explode('_', $atc->callsign)[0])->orWhere('iata', '=', explode('_', $atc->callsign)[0])->pluck('icao');
$atc->airport_id = is_null($airport) ? null : $airport;
unset($airport);
} elseif ($atc->facility_id == 6) {
$sector = SectorAlias::select('sectors.code')->where('sector_aliases.code', '=', explode('_', $atc->callsign)[0])->join('sectors', 'sector_aliases.sector_id', '=', 'sectors.id')->pluck('code');
$atc->sector_id = is_null($sector) ? null : $sector;
unset($sector);
} else {
$totalAtc--;
}
// $atc->processed = true;
// $atc->save();
}
$newAtc[] = array('id' => $atc->id, 'airport_id' => $atc->airport_id, 'sector_id' => $atc->sector_id, 'duration' => $atc->duration, 'facility_id' => $atc->facility_id);
unset($atc);
}
unset($atcs);
Log::info('queue:legacy[' . $job->getJobId() . '] - processed atc');
DB::statement("create temporary table if not exists atc_temp (\n\t\t\t`id` int(10) unsigned NOT NULL,\n\t\t\t`facility_id` smallint(6) unsigned NOT NULL,\n\t\t\t`airport_id` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL,\n\t\t\t`sector_id` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,\n\t\t\t`duration` smallint(6) NOT NULL DEFAULT '0',\n\t\t\tPRIMARY KEY (`id`)\n\t\t)");
Log::info('queue:legacy[' . $job->getJobId() . '] - created temp table atc');
$remaining = count($newAtc);
$step = 0;
do {
Log::info('queue:legacy[' . $job->getJobId() . '] - inserted atc - ' . $remaining);
DB::table('atc_temp')->insert(array_slice($newAtc, 100 * $step, 100));
$remaining -= 100;
$step++;
} while ($remaining > 0);
Log::info('queue:legacy[' . $job->getJobId() . '] - inserted atc - done');
DB::statement("update atc dest, atc_temp src set\n\t\t\tdest.duration = src.duration,\n\t\t\tdest.facility_id = src.facility_id,\n\t\t\tdest.airport_id = src.airport_id,\n\t\t\tdest.sector_id = src.sector_id,\n\t\t\tdest.processed = 1\n\t\twhere dest.id = src.id\n\t\t");
Log::info('queue:legacy[' . $job->getJobId() . '] - updated atc');
$pilot->processing = 1;
$pilot->distance = $totalDistance;
$pilot->duration = $totalDuration;
$pilot->counter = $totalFlights;
$pilot->counter_atc = $totalAtc;
$pilot->duration_atc = $totalDurationAtc;
$pilot->save();
$job->delete();
}
/**
* Processes the ATC in the datafeed, both new and existing
* in the database. As well as any ATC in the database that
* have not finished yet but are missing from the datafeed.
*
* @return void
*/
function controllers()
{
$database = ATC::whereNull('end')->get();
$update = array();
$insert = array();
$default = array('vatsim_id' => '', 'callsign' => '', 'atis' => '', 'frequency' => '', 'visual_range' => '0', 'lat' => '0', 'lon' => '0', 'time' => $this->updateDate, 'missing' => 0, 'start' => $this->updateDate, 'facility_id' => 0, 'rating_id' => 1, 'airport_id' => null, 'sector_id' => null);
foreach ($this->controllers as $entry) {
// Find the ATC duty in the data we fetched using the callsign
// and vatsim id of the controllers. If the duty does not exist
// in the database we will create a new one.
$atc = $database->first(function ($key, $atc) use($entry) {
return $atc->callsign == $entry['callsign'] && $atc->vatsim_id == $entry['cid'];
}, new ATC());
$atc->atis = $entry['atis_message'];
$atc->frequency = $entry['frequency'];
$atc->visual_range = $entry['visualrange'];
$atc->lat = $entry['latitude'];
$atc->lon = $entry['longitude'];
$atc->time = $this->updateDate;
$atc->missing = false;
if (!$atc->exists) {
$atc->vatsim_id = $entry['cid'];
$atc->callsign = $entry['callsign'];
$atc->start = Carbon::createFromFormat('YmdHis', $entry['time_logon'], 'UTC');
$atc->facility_id = ends_with($entry['callsign'], '_ATIS') ? 99 : $entry['facilitytype'];
$atc->rating_id = $entry['rating'];
$this->vatsimUser($entry['cid'], true);
if ($atc->facility_id == 6) {
$sector = SectorAlias::select('sectors.code')->where('sector_aliases.code', '=', explode('_', $entry['callsign'])[0])->join('sectors', 'sector_aliases.sector_id', '=', 'sectors.id')->pluck('code');
$atc->sector_id = is_null($sector) ? null : $sector;
unset($sector);
}
}
if ($this->hasRelocated($atc, $entry) && $atc->facility_id < 6) {
$nearby = $this->proximity($entry['latitude'], $entry['longitude']);
$atc->airport_id = is_null($nearby) ? null : $nearby->icao;
unset($nearby);
}
// Skip this record if the callsign is empty
if (empty($atc->callsign) || !$atc->vatsim_id) {
continue;
} elseif ($atc->exists) {
$update[$atc->id] = array_except($atc->toArray(), array('start', 'callsign', 'vatsim_id', 'facility', 'facility_id', 'rating_id', 'sector_id', 'processed', 'pilot', 'created_at', 'updated_at', 'deleted_at'));
} else {
$atc->created_at = Carbon::now();
$atc->updated_at = Carbon::now();
$insert[] = array_merge($default, array_except($atc->toArray(), array('facility', 'deleted_at')));
}
}
// Insert new atc into the atc table right away
$this->progressiveInsert(new ATC(), $insert);
unset($insert, $default);
// Create temporary atc table for records that are to be updated
DB::statement("create temporary table if not exists atc_temp (\n\t\t\t`id` int(10) unsigned NOT NULL,\n\t\t\t`frequency` double(6,3) NOT NULL,\n\t\t\t`visual_range` smallint(6) NOT NULL,\n\t\t\t`lat` double(10,6) NOT NULL,\n\t\t\t`lon` double(10,6) NOT NULL,\n\t\t\t`missing` tinyint(1) NOT NULL DEFAULT '0',\n\t\t\t`airport_id` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL,\n\t\t\t`end` datetime DEFAULT NULL,\n\t\t\t`time` datetime NOT NULL,\n\t\t\t`atis` text COLLATE utf8_unicode_ci,\n\t\t\t`duration` smallint(6) NOT NULL DEFAULT '0',\n\t\t\tPRIMARY KEY (`id`)\n\t\t)");
$missings = $database->filter(function ($atc) use($update) {
return !array_key_exists($atc->id, $update);
});
foreach ($missings as $missing) {
if (empty($missing->callsign) || !$missing->vatsim_id) {
$missing->delete();
} elseif (Carbon::now()->diffInMinutes($missing->time) >= 10) {
$missing->end = $missing->time;
try {
$missing->duration = $this->duration($missing->start, $missing->end);
} catch (InvalidArgumentException $e) {
Log::warning($e);
}
$missing->missing = false;
$missing->pilot->counter_atc = $missing->pilot->counter_atc + 1;
$missing->pilot->duration_atc = $missing->pilot->duration_atc + $missing->duration;
$missing->pilot->save();
} elseif (!$missing->missing) {
$missing->missing = true;
}
$update[$missing->id] = array_except($missing->toArray(), array('start', 'callsign', 'vatsim_id', 'facility', 'facility_id', 'rating_id', 'sector_id', 'processed', 'pilot', 'created_at', 'updated_at', 'deleted_at'));
unset($missing);
}
// Insert atc to be updated into temporary table
$this->progressiveInsert('atc_temp', $update);
// Update atc table with data in temporary table
DB::statement("update atc dest, atc_temp src set\n\t\t\tdest.frequency = src.frequency,\n\t\t\tdest.visual_range = src.visual_range,\n\t\t\tdest.lat = src.lat,\n\t\t\tdest.lon = src.lon,\n\t\t\tdest.airport_id = src.airport_id,\n\t\t\tdest.time = src.time,\n\t\t\tdest.missing = src.missing,\n\t\t\tdest.atis = src.atis,\n\t\t\tdest.duration = src.duration,\n\t\t\tdest.end = src.end,\n\t\t\tdest.updated_at = CURRENT_TIMESTAMP()\n\t\twhere dest.id = src.id");
unset($database, $update, $missings);
}
