function get_Schedule($id, $ident)
{
global $globalDebug;
// Get schedule here, so it's done only one time
$Connection = new Connection();
$dbc = $Connection->db;
$Spotter = new Spotter($dbc);
$Schedule = new Schedule($dbc);
$Translation = new Translation($dbc);
$operator = $Spotter->getOperator($ident);
if ($Schedule->checkSchedule($operator) == 0) {
$operator = $Translation->checkTranslation($ident);
if ($Schedule->checkSchedule($operator) == 0) {
$schedule = $Schedule->fetchSchedule($operator);
if (count($schedule) > 0) {
if ($globalDebug) {
echo "-> Schedule info for " . $operator . " (" . $ident . ")\n";
}
$this->all_flights[$id] = array_merge($this->all_flights[$id], array('departure_airport_time' => $schedule['DepartureTime']));
$this->all_flights[$id] = array_merge($this->all_flights[$id], array('arrival_airport_time' => $schedule['ArrivalTime']));
// FIXME : Check if route schedule = route from DB
if ($schedule['DepartureAirportIATA'] != '') {
if ($this->all_flights[$id]['departure_airport'] != $Spotter->getAirportIcao($schedule['DepartureAirportIATA'])) {
$airport_icao = $Spotter->getAirportIcao($schedule['DepartureAirportIATA']);
if ($airport_icao != '') {
$this->all_flights[$id]['departure_airport'] = $airport_icao;
if ($globalDebug) {
echo "-> Change departure airport to " . $airport_icao . " for " . $ident . "\n";
}
}
}
}
if ($schedule['ArrivalAirportIATA'] != '') {
if ($this->all_flights[$id]['arrival_airport'] != $Spotter->getAirportIcao($schedule['ArrivalAirportIATA'])) {
$airport_icao = $Spotter->getAirportIcao($schedule['ArrivalAirportIATA']);
if ($airport_icao != '') {
$this->all_flights[$id]['arrival_airport'] = $airport_icao;
if ($globalDebug) {
echo "-> Change arrival airport to " . $airport_icao . " for " . $ident . "\n";
}
}
}
}
$Schedule->addSchedule($operator, $this->all_flights[$id]['departure_airport'], $this->all_flights[$id]['departure_airport_time'], $this->all_flights[$id]['arrival_airport'], $this->all_flights[$id]['arrival_airport_time'], $schedule['Source']);
}
}
}
//$Connection->db = null;
}
static function get_Schedule($id, $ident)
{
global $globalDebug;
// Get schedule here, so it's done only one time
$operator = Spotter::getOperator($ident);
if (Schedule::checkSchedule($operator) == 0) {
$operator = Translation::checkTranslation($ident);
if (Schedule::checkSchedule($operator) == 0) {
$schedule = Schedule::fetchSchedule($operator);
if (count($schedule) > 0) {
if ($globalDebug) {
echo "-> Schedule info for " . $operator . " (" . $ident . ")\n";
}
self::$all_flights[$id] = array_merge(self::$all_flights[$id], array('departure_airport_time' => $schedule['DepartureTime']));
self::$all_flights[$id] = array_merge(self::$all_flights[$id], array('arrival_airport_time' => $schedule['ArrivalTime']));
// FIXME : Check if route schedule = route from DB
if ($schedule['DepartureAirportIATA'] != '') {
if (self::$all_flights[$id]['departure_airport'] != Spotter::getAirportIcao($schedule['DepartureAirportIATA'])) {
$airport_icao = Spotter::getAirportIcao($schedule['DepartureAirportIATA']);
if ($airport_icao != '') {
self::$all_flights[$id]['departure_airport'] = $airport_icao;
if ($globalDebug) {
echo "-> Change departure airport to " . $airport_icao . " for " . $ident . "\n";
}
}
}
}
if ($schedule['ArrivalAirportIATA'] != '') {
if (self::$all_flights[$id]['arrival_airport'] != Spotter::getAirportIcao($schedule['ArrivalAirportIATA'])) {
$airport_icao = Spotter::getAirportIcao($schedule['ArrivalAirportIATA']);
if ($airport_icao != '') {
self::$all_flights[$id]['arrival_airport'] = $airport_icao;
if ($globalDebug) {
echo "-> Change arrival airport to " . $airport_icao . " for " . $ident . "\n";
}
}
}
}
Schedule::addSchedule($operator, self::$all_flights[$id]['departure_airport'], self::$all_flights[$id]['departure_airport_time'], self::$all_flights[$id]['arrival_airport'], self::$all_flights[$id]['arrival_airport_time'], $schedule['Source']);
}
}
}
}
public function getSchedule($ident)
{
//$query = "SELECT * FROM schedule WHERE ident = :ident LIMIT 1";
$Translation = new Translation();
$operator = $Translation->checkTranslation($ident, false);
if ($ident != $operator) {
$query = "SELECT FromAirport_ICAO as departure_airport_icao, ToAirport_ICAO as arrival_airport_icao, FromAirport_Time as departure_airport_time, ToAirport_Time as arrival_airport_time FROM routes WHERE CallSign = :operator OR CallSign = :ident LIMIT 1";
$query_values = array(':ident' => $ident, 'operator' => $operator);
} else {
$query = "SELECT FromAirport_ICAO as departure_airport_icao, ToAirport_ICAO as arrival_airport_icao, FromAirport_Time as departure_airport_time, ToAirport_Time as arrival_airport_time FROM routes WHERE CallSign = :ident LIMIT 1";
$query_values = array(':ident' => $ident);
}
try {
$sth = $this->db->prepare($query);
$sth->execute($query_values);
} catch (PDOException $e) {
return "error : " . $e->getMessage();
}
$row = $sth->fetch(PDO::FETCH_ASSOC);
if (count($row) > 0) {
return $row;
} else {
return array();
}
}
/**
* Add ModeS data to DB
*
* @param String $ident ident
* @param String $registration Registration of the aircraft
* @param String $icao
* @param String $ICAOTypeCode
*/
public function addModeSData($ident, $registration, $icao = '', $ICAOTypeCode = '')
{
global $globalDebug, $globalDBdriver;
$ident = trim($ident);
$Translation = new Translation();
$Spotter = new Spotter();
if ($globalDebug) {
echo "Test if we add ModeS data...";
}
//if ($icao == '') $icao = ACARS->ident2icao($ident);
if ($icao == '') {
$icao = $Translation->checkTranslation($ident);
}
if ($globalDebug) {
echo '- Ident : ' . $icao . ' - ';
}
if ($ident == '' || $registration == '') {
if ($globalDebug) {
echo "Ident or registration null, exit\n";
}
return '';
}
$registration = str_replace('.', '', $registration);
$ident = $Translation->ident2icao($ident);
// Check if a flight with same registration is flying now, if ok check if callsign = name in ACARS, else add it to translation
if ($globalDebug) {
echo "Check if needed to add translation " . $ident . '... ';
}
$querysi = "SELECT ident FROM spotter_live s,aircraft_modes a WHERE a.ModeS = s.ModeS AND a.Registration = :registration LIMIT 1";
$querysi_values = array(':registration' => $registration);
try {
$sthsi = $this->db->prepare($querysi);
$sthsi->execute($querysi_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
$resultsi = $sthsi->fetch(PDO::FETCH_ASSOC);
//print_r($resultsi);
if (count($resultsi) > 0 && $resultsi['ident'] != $ident && $resultsi['ident'] != '') {
$Translation = new Translation();
$trans_ident = $Translation->getOperator($resultsi['ident']);
if ($globalDebug) {
echo 'Add translation to table : ' . $ident . ' -> ' . $resultsi['ident'] . ' ';
}
if ($ident != $trans_ident) {
$Translation->addOperator($resultsi['ident'], $ident, 'ACARS');
} elseif ($trans_ident == $ident) {
$Translation->updateOperator($resultsi['ident'], $ident, 'ACARS');
}
}
if ($globalDebug) {
echo 'Done' . "\n";
}
$query = "SELECT flightaware_id, ModeS FROM spotter_output WHERE ident = :ident ORDER BY spotter_id DESC LIMIT 1";
$query_values = array(':ident' => $icao);
try {
$sth = $this->db->prepare($query);
$sth->execute($query_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
$result = $sth->fetch(PDO::FETCH_ASSOC);
//print_r($result);
if (isset($result['flightaware_id'])) {
if (isset($result['ModeS'])) {
$ModeS = $result['ModeS'];
} else {
$ModeS = '';
}
if ($ModeS == '') {
$id = explode('-', $result['flightaware_id']);
$ModeS = $id[0];
}
if ($ModeS != '') {
$country = $Spotter->countryFromAircraftRegistration($registration);
$queryc = "SELECT * FROM aircraft_modes WHERE ModeS = :modes LIMIT 1";
$queryc_values = array(':modes' => $ModeS);
try {
$sthc = $this->db->prepare($queryc);
$sthc->execute($queryc_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
$row = $sthc->fetch(PDO::FETCH_ASSOC);
if (count($row) == 0) {
if ($globalDebug) {
echo " Add to ModeS table - ";
}
$queryi = "INSERT INTO aircraft_modes (ModeS,ModeSCountry,Registration,ICAOTypeCode,Source) VALUES (:ModeS,:ModeSCountry,:Registration, :ICAOTypeCode,'ACARS')";
$queryi_values = array(':ModeS' => $ModeS, ':ModeSCountry' => $country, ':Registration' => $registration, ':ICAOTypeCode' => $ICAOTypeCode);
try {
$sthi = $this->db->prepare($queryi);
$sthi->execute($queryi_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
} else {
if ($globalDebug) {
echo " Update ModeS table - ";
}
if ($ICAOTypeCode != '') {
$queryi = "UPDATE aircraft_modes SET ModeSCountry = :ModeSCountry,Registration = :Registration,ICAOTypeCode = :ICAOTypeCode,Source = 'ACARS',LastModified = NOW() WHERE ModeS = :ModeS";
$queryi_values = array(':ModeS' => $ModeS, ':ModeSCountry' => $country, ':Registration' => $registration, ':ICAOTypeCode' => $ICAOTypeCode);
} else {
$queryi = "UPDATE aircraft_modes SET ModeSCountry = :ModeSCountry,Registration = :Registration,Source = 'ACARS',LastModified = NOW() WHERE ModeS = :ModeS";
$queryi_values = array(':ModeS' => $ModeS, ':ModeSCountry' => $country, ':Registration' => $registration);
}
try {
$sthi = $this->db->prepare($queryi);
$sthi->execute($queryi_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
}
/*
if ($globalDebug) echo " Update Spotter_live table - ";
if ($ICAOTypeCode != '') {
$queryi = "UPDATE spotter_live SET registration = :Registration,aircraft_icao = :ICAOTypeCode WHERE ident = :ident";
$queryi_values = array(':Registration' => $registration, ':ICAOTypeCode' => $ICAOTypeCode, ':ident' => $icao);
} else {
$queryi = "UPDATE spotter_live SET registration = :Registration WHERE ident = :ident";
$queryi_values = array(':Registration' => $registration,':ident' => $icao);
}
try {
$sthi = $this->db->prepare($queryi);
$sthi->execute($queryi_values);
} catch(PDOException $e) {
if ($globalDebug) echo $e->getMessage();
return "error : ".$e->getMessage();
}
*/
if ($globalDebug) {
echo " Update Spotter_output table - ";
}
if ($ICAOTypeCode != '') {
if ($globalDBdriver == 'mysql') {
$queryi = "UPDATE spotter_output SET registration = :Registration,aircraft_icao = :ICAOTypeCode WHERE ident = :ident AND date >= date_sub(UTC_TIMESTAMP(), INTERVAL 1 HOUR)";
} else {
if ($globalDBdriver == 'pgsql') {
$queryi = "UPDATE spotter_output SET registration = :Registration,aircraft_icao = :ICAOTypeCode WHERE ident = :ident AND date >= NOW() AT TIME ZONE 'UTC' - '1 HOUR'->INTERVAL";
}
}
$queryi_values = array(':Registration' => $registration, ':ICAOTypeCode' => $ICAOTypeCode, ':ident' => $icao);
} else {
if ($globalDBdriver == 'mysql') {
$queryi = "UPDATE spotter_output SET registration = :Registration WHERE ident = :ident AND date >= date_sub(UTC_TIMESTAMP(), INTERVAL 1 HOUR)";
} elseif ($globalDBdriver == 'pgsql') {
$queryi = "UPDATE spotter_output SET registration = :Registration WHERE ident = :ident AND date >= NOW() AT TIME ZONE 'UTC' - '1 HOUR'->INTERVAL";
}
$queryi_values = array(':Registration' => $registration, ':ident' => $icao);
}
try {
$sthi = $this->db->prepare($queryi);
$sthi->execute($queryi_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
}
} else {
if ($globalDebug) {
echo " Can't find ModeS in spotter_output - ";
}
}
if ($globalDebug) {
echo "Done\n";
}
}
/**
* Add ModeS data to DB
*
* @param String $ident ident
* @param String $registration Registration of the aircraft
* @param String $icao
* @param String $ICAOTypeCode
*/
public function addModeSData($ident, $registration, $icao = '', $ICAOTypeCode = '')
{
global $globalDebug;
$Translation = new Translation();
$Spotter = new Spotter();
if ($globalDebug) {
echo "Test if we add ModeS data...";
}
//if ($icao == '') $icao = ACARS->ident2icao($ident);
if ($icao == '') {
$icao = $Translation->checkTranslation($ident);
}
if ($globalDebug) {
echo '- Ident : ' . $icao . ' - ';
}
if ($ident == '' || $registration == '') {
if ($globalDebug) {
echo "Ident or registration null, exit\n";
}
return '';
}
$query = "SELECT flightaware_id, ModeS FROM spotter_output WHERE ident = :ident ORDER BY spotter_id DESC LIMIT 1";
$query_values = array(':ident' => $icao);
try {
$sth = $this->db->prepare($query);
$sth->execute($query_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
$result = $sth->fetch(PDO::FETCH_ASSOC);
//print_r($result);
if (isset($result['flightaware_id'])) {
$ModeS = $result['ModeS'];
if ($ModeS == '') {
$id = explode('-', $result['flightaware_id']);
$ModeS = $id[0];
}
if ($ModeS != '') {
$country = $Spotter->countryFromAircraftRegistration($registration);
$queryc = "SELECT * FROM aircraft_modes WHERE ModeS = :modes LIMIT 1";
$queryc_values = array(':modes' => $ModeS);
try {
$sthc = $this->db->prepare($queryc);
$sthc->execute($queryc_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
$row = $sthc->fetch(PDO::FETCH_ASSOC);
if (count($row) == 0) {
if ($globalDebug) {
echo " Add to ModeS table - ";
}
$queryi = "INSERT INTO aircraft_modes (ModeS,ModeSCountry,Registration,ICAOTypeCode,Source) VALUES (:ModeS,:ModeSCountry,:Registration, :ICAOTypeCode,'ACARS')";
$queryi_values = array(':ModeS' => $ModeS, ':ModeSCountry' => $country, ':Registration' => $registration, ':ICAOTypeCode' => $ICAOTypeCode);
try {
$sthi = $this->db->prepare($queryi);
$sthi->execute($queryi_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
} else {
if ($globalDebug) {
echo " Update ModeS table - ";
}
if ($ICAOTypeCode != '') {
$queryi = "UPDATE aircraft_modes SET ModeSCountry = :ModeSCountry,Registration = :Registration,ICAOTypeCode = :ICAOTypeCode,Source = 'ACARS',LastModified = NOW() WHERE ModeS = :ModeS";
$queryi_values = array(':ModeS' => $ModeS, ':ModeSCountry' => $country, ':Registration' => $registration, ':ICAOTypeCode' => $ICAOTypeCode);
} else {
$queryi = "UPDATE aircraft_modes SET ModeSCountry = :ModeSCountry,Registration = :Registration,Source = 'ACARS',LastModified = NOW() WHERE ModeS = :ModeS";
$queryi_values = array(':ModeS' => $ModeS, ':ModeSCountry' => $country, ':Registration' => $registration);
}
try {
$sthi = $this->db->prepare($queryi);
$sthi->execute($queryi_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
}
if ($globalDebug) {
echo " Update Spotter_live table - ";
}
if ($ICAOTypeCode != '') {
$queryi = "UPDATE spotter_live SET registration = :Registration,aircraft_icao = :ICAOTypeCode WHERE ident = :ident";
$queryi_values = array(':Registration' => $registration, ':ICAOTypeCode' => $ICAOTypeCode, ':ident' => $icao);
} else {
$queryi = "UPDATE spotter_live SET registration = :Registration WHERE ident = :ident";
$queryi_values = array(':Registration' => $registration, ':ident' => $icao);
}
try {
$sthi = $this->db->prepare($queryi);
$sthi->execute($queryi_values);
} catch (PDOException $e) {
if ($globalDebug) {
echo $e->getMessage();
}
return "error : " . $e->getMessage();
}
}
} else {
if ($globalDebug) {
echo " Can't find ModeS in spotter_output - ";
}
}
if ($globalDebug) {
echo "Done\n";
}
}