/**
* 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);
}
