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