/**
* Gets all the spotter information based on the latest data entry
*
* @return Array the spotter information
*
*/
public static function getLiveSpotterData($limit = '', $sort = '')
{
global $globalDBdriver, $globalLiveInterval;
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[1] . " OFFSET " . $limit_array[0];
}
}
$orderby_query = '';
if ($sort != "") {
$search_orderby_array = SpotterLive::getOrderBy();
$orderby_query = ' ' . $search_orderby_array[$sort]['sql'];
}
if (!isset($globalLiveInterval)) {
$globalLiveInterval = '200';
}
if ($globalDBdriver == 'mysql') {
//$query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL 30 SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate";
$query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL " . $globalLiveInterval . " SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate{$orderby_query}";
} else {
if ($globalDBdriver == 'pgsql') {
//$query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE NOW() AT TIME ZONE 'UTC' - '30 SECONDS'::INTERVAL <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate";
$query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE NOW() AT TIME ZONE 'UTC' - '" . $globalLiveInterval . " SECONDS'::INTERVAL <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate{$orderby_query}";
}
}
$spotter_array = Spotter::getDataFromDB($query . $limit_query);
return $spotter_array;
}
