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) {