/** * Gets last spotter information based on a particular callsign * * @return Array the spotter information * */ public static function getLastLiveSpotterDataById($id) { date_default_timezone_set('UTC'); $id = filter_var($id, FILTER_SANITIZE_STRING); $query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE l.flightaware_id = :id GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate"; $spotter_array = Spotter::getDataFromDB($query, array(':id' => $id)); return $spotter_array; }
/** * Gets all the archive spotter information * * @return Array the spotter information * */ public function getSpotteArchiveData($ident, $flightaware_id, $date) { $Spotter = new Spotter(); $ident = filter_var($ident, FILTER_SANITIZE_STRING); $query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE l.ident = :ident AND l.flightaware_id = :flightaware_id AND l.date LIKE :date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate"; $spotter_array = $Spotter->getDataFromDB($query, array(':ident' => $ident, ':flightaware_id' => $flightaware_id, ':date' => $date . '%')); return $spotter_array; }
/** * Gets all the spotter information based on calculated upcoming flights * * @return Array the spotter information * */ public static function getUpcomingFlights($limit = '', $sort = '') { global $global_query, $globalDBdriver, $globalTimezone; date_default_timezone_set('UTC'); 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]; } } $currentHour = date("G"); $next3Hours = date("G", strtotime("+3 hour")); //if the next 3 hours is already equal to/past midnight, we limit it to stay there, otherwise the query will fail if ($currentHour >= 21 && $next3Hours >= 00) { $next3Hours = 24; } $currentDayofWeek = date("l"); if ($globalDBdriver == 'mysql') { if ($sort != "") { $search_orderby_array = Spotter::getOrderBy(); $orderby_query = $search_orderby_array[$sort]['sql']; } else { $orderby_query = " ORDER BY HOUR(spotter_output.date) ASC"; } $query = "SELECT spotter_output.*, count(spotter_output.ident) as ident_count\n\t\t\t FROM spotter_output\n\t\t\t WHERE DAYNAME(spotter_output.date) = '{$currentDayofWeek}' AND HOUR(spotter_output.date) >= '{$currentHour}' AND HOUR(spotter_output.date) <= '{$next3Hours}'\n\t\t\t GROUP BY spotter_output.ident HAVING ident_count > 10 {$orderby_query}"; $spotter_array = Spotter::getDataFromDB($query . $limit_query); } else { if ($globalDBdriver == 'pgsql') { if ($sort != "") { $search_orderby_array = Spotter::getOrderBy(); $orderby_query = $search_orderby_array[$sort]['sql']; } else { $orderby_query = " ORDER BY EXTRACT (HOUR FROM spotter_output.date) ASC"; } $query = "SELECT spotter_output.*, count(spotter_output.ident) as ident_count\n\t\t\t FROM spotter_output\n\t\t\t WHERE DATE_PART('dow', spotter_output.date) = DATE_PART('dow', date 'now' AT TIME ZONE :timezone) AND EXTRACT (HOUR FROM spotter_output.date AT TIME ZONE :timezone) >= '{$currentHour}' AND EXTRACT (HOUR FROM spotter_output.date AT TIME ZONE :timezone) <= '{$next3Hours}'\n\t\t\t GROUP BY spotter_output.ident, spotter_output.spotter_id HAVING count(spotter_output.ident) > 10 {$orderby_query}"; $spotter_array = Spotter::getDataFromDB($query . $limit_query, array(':timezone' => $globalTimezone)); } } return $spotter_array; }