/**
* 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";
}
}
