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