public static function updateFieldsFromOtherTables() { global $globalDebug; $Connection = new Connection(); // routes if ($globalDebug) { print "Routes...\n"; } $query = "SELECT spotter_output.spotter_id, routes.FromAirport_ICAO, routes.ToAirport_ICAO FROM spotter_output, routes WHERE spotter_output.ident = routes.CallSign AND ( spotter_output.departure_airport_icao != routes.FromAirport_ICAO OR spotter_output.arrival_airport_icao != routes.ToAirport_ICAO) AND routes.FromAirport_ICAO != ''"; $sth = Connection::$db->prepare($query); $sth->execute(); while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { $departure_airport_array = Spotter::getAllAirportInfo($row['fromairport_icao']); $arrival_airport_array = Spotter::getAllAirportInfo($row['toairport_icao']); if (count($departure_airport_array) > 0 && count($arrival_airport_array) > 0) { $update_query = "UPDATE spotter_output SET departure_airport_icao = :fromicao, arrival_airport_icao = :toicao, departure_airport_name = :departure_airport_name, departure_airport_city = :departure_airport_city, departure_airport_country = :departure_airport_country, arrival_airport_name = :arrival_airport_name, arrival_airport_city = :arrival_airport_city, arrival_airport_country = :arrival_airport_country WHERE spotter_id = :spotter_id"; $sthu = Connection::$db->prepare($update_query); $sthu->execute(array(':fromicao' => $row['fromairport_icao'], ':toicao' => $row['toairport_icao'], ':spotter_id' => $row['spotter_id'], ':departure_airport_name' => $departure_airport_array[0]['name'], ':departure_airport_city' => $departure_airport_array[0]['city'], ':departure_airport_country' => $departure_airport_array[0]['country'], ':arrival_airport_name' => $arrival_airport_array[0]['name'], ':arrival_airport_city' => $arrival_airport_array[0]['city'], ':arrival_airport_country' => $arrival_airport_array[0]['country'])); } } if ($globalDebug) { print "Airlines...\n"; } //airlines $query = "SELECT spotter_output.spotter_id, spotter_output.ident FROM spotter_output WHERE spotter_output.airline_name = '' OR spotter_output.airline_name = 'Not Available'"; $sth = Connection::$db->prepare($query); $sth->execute(); while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { if (is_numeric(substr($row['ident'], -1, 1))) { $airline_array = Spotter::getAllAirlineInfo(substr($row['ident'], 0, 3)); $update_query = "UPDATE spotter_output SET spotter_output.airline_name = :airline_name, spotter_output.airline_icao = :airline_icao, spotter_output.airline_country = :airline_country, spotter_output.airline_type = :airline_type WHERE spotter_output.spotter_id = :spotter_id"; $sthu = Connection::$db->prepare($update_query); $sthu->execute(array(':airline_name' => $airline_array[0]['name'], ':airline_icao' => $airline_array[0]['icao'], ':airline_country' => $airline_array[0]['country'], ':airline_type' => $airline_array[0]['type'], ':spotter_id' => $row['spotter_id'])); } } if ($globalDebug) { print "Remove Duplicate in aircraft_modes...\n"; } //duplicate modes $query = "DELETE aircraft_modes FROM aircraft_modes LEFT OUTER JOIN (SELECT max(`AircraftID`) as `AircraftID`,`ModeS` FROM `aircraft_modes` group by ModeS) as KeepRows ON aircraft_modes.AircraftID = KeepRows.AircraftID WHERE KeepRows.AircraftID IS NULL"; $sth = Connection::$db->prepare($query); $sth->execute(); if ($globalDebug) { print "Aircraft...\n"; } //aircraft $query = "SELECT spotter_output.spotter_id, spotter_output.aircraft_icao, spotter_output.registration FROM spotter_output WHERE spotter_output.aircraft_name = '' OR spotter_output.aircraft_name = 'Not Available'"; $sth = Connection::$db->prepare($query); $sth->execute(); while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { if ($row['aircraft_icao'] != '') { $aircraft_name = Spotter::getAllAircraftInfo($row['aircraft_icao']); if ($row['registration'] != "") { Image::addSpotterImage($row['registration']); } if (count($aircraft_name) > 0) { $update_query = "UPDATE spotter_output SET spotter_output.aircraft_name = :aircraft_name, spotter_output.aircraft_manufacturer = :aircraft_manufacturer WHERE spotter_output.spotter_id = :spotter_id"; $sthu = Connection::$db->prepare($update_query); $sthu->execute(array(':aircraft_name' => $aircraft_name[0]['type'], ':aircraft_manufacturer' => $aircraft_name[0]['manufacturer'], ':spotter_id' => $row['spotter_id'])); } } } }
if (isset($spotter_item['registration'])) { $output .= '"registration": "' . $spotter_item['registration'] . '",'; } if (isset($spotter_item['aircraft_name']) && isset($spotter_item['aircraft_type'])) { $output .= '"aircraft_name": "' . $spotter_item['aircraft_name'] . ' (' . $spotter_item['aircraft_type'] . ')",'; $output .= '"aircraft_wiki": "http://' . strtolower($globalLanguage) . '.wikipedia.org/wiki/' . urlencode(str_replace(' ', '_', $spotter_item['aircraft_name'])) . '",'; } elseif (isset($spotter_item['aircraft_type'])) { $output .= '"aircraft_name": "NA (' . $spotter_item['aircraft_type'] . ')",'; } elseif (!$min) { $output .= '"aircraft_name": "NA",'; } if (!isset($spotter_item['aircraft_shadow'])) { if (!isset($spotter_item['aircraft_icao']) || $spotter_item['aircraft_icao'] == '') { $spotter_item['aircraft_shadow'] = ''; } else { $aircraft_info = $Spotter->getAllAircraftInfo($spotter_item['aircraft_icao']); if (count($aircraft_info) > 0) { $spotter_item['aircraft_shadow'] = $aircraft_info[0]['aircraft_shadow']; } else { $spotter_item['aircraft_shadow'] = ''; } } } if ($spotter_item['aircraft_shadow'] == '') { $output .= '"aircraft_shadow": "default.png",'; } else { $output .= '"aircraft_shadow": "' . $spotter_item['aircraft_shadow'] . '",'; } if (isset($spotter_item['airline_name'])) { $output .= '"airline_name": "' . $spotter_item['airline_name'] . '",'; } elseif (!$min) {