/**
* Change IATA to ICAO value for ident
*
* @param String $ident ident
* @return String the icao
*/
public static function ident2icao($ident)
{
if (!is_numeric(substr($ident, 0, 3))) {
if (is_numeric(substr(substr($ident, 0, 3), -1, 1))) {
$airline_icao = substr($ident, 0, 2);
} elseif (is_numeric(substr(substr($ident, 0, 4), -1, 1))) {
//$airline_icao = substr($ident, 0, 3);
return $ident;
} else {
return $ident;
}
} else {
return $ident;
}
if ($airline_icao == 'AF') {
if (filter_var(substr($ident, 2), FILTER_VALIDATE_INT, array("flags" => FILTER_FLAG_ALLOW_OCTAL))) {
$icao = $ident;
} else {
$icao = 'AFR' . ltrim(substr($ident, 2), '0');
}
} else {
$identicao = Spotter::getAllAirlineInfo($airline_icao);
if (isset($identicao[0])) {
$icao = $identicao[0]['icao'] . ltrim(substr($ident, 2), '0');
} else {
$icao = $ident;
}
}
return $icao;
}
public static function translation()
{
require_once '../require/class.Spotter.php';
global $tmp_dir, $globalTransaction;
//$out_file = $tmp_dir.'translation.zip';
//update_db::download('http://www.acarsd.org/download/translation.php',$out_file);
//if (!file_exists($out_file) || !is_readable($out_file)) return FALSE;
//$query = 'TRUNCATE TABLE translation';
$query = "DELETE FROM translation WHERE Source = '' OR Source = :source";
try {
$Connection = new Connection();
$sth = Connection::$db->prepare($query);
$sth->execute(array(':source' => 'translation.csv'));
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
//update_db::unzip($out_file);
$header = NULL;
$delimiter = ';';
$Connection = new Connection();
if (($handle = fopen($tmp_dir . 'translation.csv', 'r')) !== FALSE) {
$i = 0;
//Connection::$db->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);
//Connection::$db->beginTransaction();
while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
$i++;
if ($i > 12) {
$data = $row;
$operator = $data[2];
if ($operator != '' && is_numeric(substr(substr($operator, 0, 3), -1, 1))) {
$airline_array = Spotter::getAllAirlineInfo(substr($operator, 0, 2));
//echo substr($operator, 0, 2)."\n";;
if (count($airline_array) > 0) {
//print_r($airline_array);
$operator = $airline_array[0]['icao'] . substr($operator, 2);
}
}
$operator_correct = $data[3];
if ($operator_correct != '' && is_numeric(substr(substr($operator_correct, 0, 3), -1, 1))) {
$airline_array = Spotter::getAllAirlineInfo(substr($operator_correct, 0, 2));
if (count($airline_array) > 0) {
$operator_correct = $airline_array[0]['icao'] . substr($operator_correct, 2);
}
}
$query = 'INSERT INTO translation (Reg,Reg_correct,Operator,Operator_correct,Source) VALUES (:Reg, :Reg_correct, :Operator, :Operator_correct, :source)';
try {
$sth = Connection::$db->prepare($query);
$sth->execute(array(':Reg' => $data[0], ':Reg_correct' => $data[1], ':Operator' => $operator, ':Operator_correct' => $operator_correct, ':source' => 'translation.csv'));
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
}
}
fclose($handle);
//Connection::$db->commit();
}
// return true;
}
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']));
}
}
}
}
/**
* Get Archive ACARS data from DB
*
* @return Array Return ACARS data in array
*/
public function getArchiveAcarsData($limit = '', $label = '')
{
global $globalURL, $globalDBdriver;
$Image = new Image();
$Spotter = new Spotter();
$Translation = new Translation();
date_default_timezone_set('UTC');
$limit_query = '';
if ($limit != "") {
$limit_array = explode(",", $limit);
$limit_array[0] = filter_var($limit_array[0], FILTER_SANITIZE_NUMBER_INT);
$limit_array[1] = filter_var($limit_array[1], FILTER_SANITIZE_NUMBER_INT);
if ($limit_array[0] >= 0 && $limit_array[1] >= 0) {
//$limit_query = " LIMIT ".$limit_array[0].",".$limit_array[1];
$limit_query = " LIMIT " . $limit_array[1] . " OFFSET " . $limit_array[0];
}
}
//$query = "SELECT *, name as airline_name FROM acars_live a, spotter_image i, airlines l WHERE i.registration = a.registration AND l.icao = a.airline_icao AND l.icao != '' ORDER BY acars_live_id DESC LIMIT 25";
if ($label != '') {
if ($label == 'undefined') {
$query = "SELECT * FROM acars_archive WHERE label NOT IN (SELECT label FROM acars_label) ORDER BY acars_archive_id DESC" . $limit_query;
$query_values = array();
} else {
$query = "SELECT * FROM acars_archive WHERE label = :label ORDER BY acars_archive_id DESC" . $limit_query;
$query_values = array(':label' => $label);
}
} else {
$query = "SELECT * FROM acars_archive ORDER BY acars_archive_id DESC" . $limit_query;
$query_values = array();
}
try {
$sth = $this->db->prepare($query);
$sth->execute($query_values);
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
$i = 0;
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$data = array();
if ($row['registration'] != '') {
$image_array = $Image->getSpotterImage($row['registration']);
if (count($image_array) > 0) {
$data = array_merge($data, array('image_thumbnail' => $image_array[0]['image_thumbnail'], 'image_copyright' => $image_array[0]['image_copyright'], 'image_source' => $image_array[0]['image_source'], 'image_source_website' => $image_array[0]['image_source_website']));
} else {
$data = array_merge($data, array('image_thumbnail' => '', 'image_copyright' => '', 'image_source' => '', 'image_source_website' => ''));
}
} else {
$data = array_merge($data, array('image_thumbnail' => '', 'image_copyright' => '', 'image_source' => '', 'image_source_website' => ''));
}
$icao = '';
if ($row['registration'] == '') {
$row['registration'] = 'NA';
}
if ($row['ident'] == '') {
$row['ident'] = 'NA';
}
$identicao = $Spotter->getAllAirlineInfo(substr($row['ident'], 0, 2));
if (isset($identicao[0])) {
if (substr($row['ident'], 0, 2) == 'AF') {
if (filter_var(substr($row['ident'], 2), FILTER_VALIDATE_INT, array("flags" => FILTER_FLAG_ALLOW_OCTAL))) {
$icao = $row['ident'];
} else {
$icao = 'AFR' . ltrim(substr($row['ident'], 2), '0');
}
} else {
$icao = $identicao[0]['icao'] . ltrim(substr($row['ident'], 2), '0');
}
$data = array_merge($data, array('airline_icao' => $identicao[0]['icao'], 'airline_name' => $identicao[0]['name']));
} else {
$icao = $row['ident'];
}
$icao = $Translation->checkTranslation($icao);
$decode = json_decode($row['decode'], true);
$found = false;
if ($decode != '' && array_key_exists('Departure airport', $decode)) {
$airport_info = $Spotter->getAllAirportInfo($decode['Departure airport']);
if (isset($airport_info[0]['icao'])) {
$decode['Departure airport'] = '<a href="' . $globalURL . '/airport/' . $airport_info[0]['icao'] . '">' . $airport_info[0]['city'] . ',' . $airport_info[0]['country'] . ' (' . $airport_info[0]['icao'] . ')</a>';
}
$found = true;
}
if ($decode != '' && array_key_exists('Arrival airport', $decode)) {
$airport_info = $Spotter->getAllAirportInfo($decode['Arrival airport']);
if (isset($airport_info[0]['icao'])) {
$decode['Arrival airport'] = '<a href="' . $globalURL . '/airport/' . $airport_info[0]['icao'] . '">' . $airport_info[0]['city'] . ',' . $airport_info[0]['country'] . ' (' . $airport_info[0]['icao'] . ')</a>';
}
$found = true;
}
if ($decode != '' && array_key_exists('Airport/Waypoint name', $decode)) {
$airport_info = $Spotter->getAllAirportInfo($decode['Airport/Waypoint name']);
if (isset($airport_info[0]['icao'])) {
$decode['Airport/Waypoint name'] = '<a href="' . $globalURL . '/airport/' . $airport_info[0]['icao'] . '">' . $airport_info[0]['city'] . ',' . $airport_info[0]['country'] . ' (' . $airport_info[0]['icao'] . ')</a>';
$found = true;
}
}
if ($found) {
$row['decode'] = json_encode($decode);
}
$data = array_merge($data, array('registration' => $row['registration'], 'message' => $row['message'], 'date' => $row['date'], 'ident' => $icao, 'decode' => $row['decode']));
$result[] = $data;
$i++;
}
if (isset($result)) {
$result[0]['query_number_rows'] = $i;
return $result;
} else {
return array();
}
}
