|
1
|
|
|
<?php |
|
2
|
|
|
class SpotterArchive { |
|
3
|
|
|
public $global_query = "SELECT spotter_archive.* FROM spotter_archive"; |
|
4
|
|
|
public $db; |
|
5
|
|
|
|
|
6
|
|
|
public function __construct($dbc = null) { |
|
7
|
|
|
$Connection = new Connection($dbc); |
|
8
|
|
|
$this->db = $Connection->db; |
|
9
|
|
|
if ($this->db === null) die('Error: No DB connection. (SpotterArchive)'); |
|
10
|
|
|
} |
|
11
|
|
|
|
|
12
|
|
|
/** |
|
13
|
|
|
* Get SQL query part for filter used |
|
14
|
|
|
* @param Array $filter the filter |
|
15
|
|
|
* @return Array the SQL part |
|
16
|
|
|
*/ |
|
17
|
|
|
public function getFilter($filter = array(),$where = false,$and = false) { |
|
18
|
|
|
global $globalFilter, $globalStatsFilters, $globalFilterName, $globalDBdriver; |
|
19
|
|
|
$filters = array(); |
|
20
|
|
|
if (is_array($globalStatsFilters) && isset($globalStatsFilters[$globalFilterName])) { |
|
21
|
|
|
if (isset($globalStatsFilters[$globalFilterName][0]['source'])) { |
|
22
|
|
|
$filters = $globalStatsFilters[$globalFilterName]; |
|
23
|
|
|
} else { |
|
24
|
|
|
$filter = array_merge($filter,$globalStatsFilters[$globalFilterName]); |
|
25
|
|
|
} |
|
26
|
|
|
} |
|
27
|
|
|
if (isset($filter[0]['source'])) { |
|
28
|
|
|
$filters = array_merge($filters,$filter); |
|
29
|
|
|
} |
|
30
|
|
|
if (is_array($globalFilter)) $filter = array_merge($filter,$globalFilter); |
|
31
|
|
|
$filter_query_join = ''; |
|
32
|
|
|
$filter_query_where = ''; |
|
33
|
|
|
foreach($filters as $flt) { |
|
34
|
|
|
if (isset($flt['airlines']) && !empty($flt['airlines'])) { |
|
35
|
|
|
if ($flt['airlines'][0] != '' && $flt['airlines'][0] != 'all') { |
|
36
|
|
|
if (isset($flt['source'])) { |
|
37
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.airline_icao IN ('".implode("','",$flt['airlines'])."') AND spotter_archive_output.format_source IN ('".implode("','",$flt['source'])."')) saff ON saff.flightaware_id = spotter_archive_output.flightaware_id"; |
|
38
|
|
|
} else { |
|
39
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.airline_icao IN ('".implode("','",$flt['airlines'])."')) saff ON saff.flightaware_id = spotter_archive_output.flightaware_id"; |
|
40
|
|
|
} |
|
41
|
|
|
} |
|
42
|
|
|
} |
|
43
|
|
|
if (isset($flt['pilots_id']) && !empty($flt['pilots_id'])) { |
|
44
|
|
|
if (isset($flt['source'])) { |
|
45
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.pilot_id IN ('".implode("','",$flt['pilots_id'])."') AND spotter_archive_output.format_source IN ('".implode("','",$flt['source'])."')) sp ON sp.flightaware_id = spotter_archive_output.flightaware_id"; |
|
46
|
|
|
} else { |
|
47
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.pilot_id IN ('".implode("','",$flt['pilots_id'])."')) sp ON sp.flightaware_id = spotter_archive_output.flightaware_id"; |
|
48
|
|
|
} |
|
49
|
|
|
} |
|
50
|
|
|
if (isset($flt['idents']) && !empty($flt['idents'])) { |
|
51
|
|
|
if (isset($flt['source'])) { |
|
52
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.ident IN ('".implode("','",$flt['idents'])."') AND spotter_archive_output.format_source IN ('".implode("','",$flt['source'])."')) spi ON spi.flightaware_id = spotter_archive_output.flightaware_id"; |
|
53
|
|
|
} else { |
|
54
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.ident IN ('".implode("','",$flt['idents'])."')) spi ON spi.flightaware_id = spotter_archive_output.flightaware_id"; |
|
55
|
|
|
} |
|
56
|
|
|
} |
|
57
|
|
|
if (isset($flt['registrations']) && !empty($flt['registrations'])) { |
|
58
|
|
|
if (isset($flt['source'])) { |
|
59
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.registration IN ('".implode("','",$flt['registrations'])."') AND spotter_archive_output.format_source IN ('".implode("','",$flt['source'])."')) sre ON sre.flightaware_id = spotter_archive_output.flightaware_id"; |
|
60
|
|
|
} else { |
|
61
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.registration IN ('".implode("','",$flt['registrations'])."')) sre ON sre.flightaware_id = spotter_archive_output.flightaware_id"; |
|
62
|
|
|
} |
|
63
|
|
|
} |
|
64
|
|
|
if ((isset($flt['airlines']) && empty($flt['airlines']) && isset($flt['pilots_id']) && empty($flt['pilots_id']) && isset($flt['idents']) && empty($flt['idents']) && isset($flt['registrations']) && empty($flt['registrations'])) || (!isset($flt['airlines']) && !isset($flt['pilots_id']) && !isset($flt['idents']) && !isset($flt['registrations']))) { |
|
65
|
|
|
if (isset($flt['source'])) { |
|
66
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_output.format_source IN ('".implode("','",$flt['source'])."')) saa ON saa.flightaware_id = spotter_archive_output.flightaware_id"; |
|
67
|
|
|
} |
|
68
|
|
|
} |
|
69
|
|
|
} |
|
70
|
|
|
if (isset($filter['airlines']) && !empty($filter['airlines'])) { |
|
71
|
|
|
if ($filter['airlines'][0] != '' && $filter['airlines'][0] != 'all') { |
|
72
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.airline_icao IN ('".implode("','",$filter['airlines'])."')) saf ON saf.flightaware_id = spotter_archive_output.flightaware_id"; |
|
73
|
|
|
} |
|
74
|
|
|
} |
|
75
|
|
|
if (isset($filter['airlinestype']) && !empty($filter['airlinestype'])) { |
|
76
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.airline_type = '".$filter['airlinestype']."') sa ON sa.flightaware_id = spotter_archive_output.flightaware_id "; |
|
77
|
|
|
} |
|
78
|
|
|
if (isset($filter['pilots_id']) && !empty($filter['pilots_id'])) { |
|
79
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.pilot_id IN ('".implode("','",$filter['pilots_id'])."')) spi ON spi.flightaware_id = spotter_archive_output.flightaware_id"; |
|
80
|
|
|
} |
|
81
|
|
|
if (isset($filter['source']) && !empty($filter['source'])) { |
|
82
|
|
|
if (count($filter['source']) == 1) { |
|
83
|
|
|
$filter_query_where .= " AND format_source = '".$filter['source'][0]."'"; |
|
84
|
|
|
} else { |
|
85
|
|
|
$filter_query_where .= " AND format_source IN ('".implode("','",$filter['source'])."')"; |
|
86
|
|
|
} |
|
87
|
|
|
} |
|
88
|
|
|
if (isset($filter['ident']) && !empty($filter['ident'])) { |
|
89
|
|
|
$filter_query_where .= " AND ident = '".$filter['ident']."'"; |
|
90
|
|
|
} |
|
91
|
|
|
if (isset($filter['id']) && !empty($filter['id'])) { |
|
92
|
|
|
$filter_query_where .= " AND flightaware_id = '".$filter['id']."'"; |
|
93
|
|
|
} |
|
94
|
|
|
if (isset($filter['source_aprs']) && !empty($filter['source_aprs'])) { |
|
95
|
|
|
$filter_query_where .= " AND format_source = 'aprs' AND source_name IN ('".implode("','",$filter['source_aprs'])."')"; |
|
96
|
|
|
} |
|
97
|
|
|
if ((isset($filter['year']) && $filter['year'] != '') || (isset($filter['month']) && $filter['month'] != '') || (isset($filter['day']) && $filter['day'] != '')) { |
|
98
|
|
|
$filter_query_date = ''; |
|
99
|
|
|
if (isset($filter['year']) && $filter['year'] != '') { |
|
100
|
|
|
if ($globalDBdriver == 'mysql') { |
|
101
|
|
|
$filter_query_date .= " AND YEAR(spotter_archive_output.date) = '".$filter['year']."'"; |
|
102
|
|
|
} else { |
|
103
|
|
|
$filter_query_date .= " AND EXTRACT(YEAR FROM spotter_archive_output.date) = '".$filter['year']."'"; |
|
104
|
|
|
} |
|
105
|
|
|
} |
|
106
|
|
|
if (isset($filter['month']) && $filter['month'] != '') { |
|
107
|
|
|
if ($globalDBdriver == 'mysql') { |
|
108
|
|
|
$filter_query_date .= " AND MONTH(spotter_archive_output.date) = '".$filter['month']."'"; |
|
109
|
|
|
} else { |
|
110
|
|
|
$filter_query_date .= " AND EXTRACT(MONTH FROM spotter_archive_output.date) = '".$filter['month']."'"; |
|
111
|
|
|
} |
|
112
|
|
|
} |
|
113
|
|
|
if (isset($filter['day']) && $filter['day'] != '') { |
|
114
|
|
|
if ($globalDBdriver == 'mysql') { |
|
115
|
|
|
$filter_query_date .= " AND DAY(spotter_archive_output.date) = '".$filter['day']."'"; |
|
116
|
|
|
} else { |
|
117
|
|
|
$filter_query_date .= " AND EXTRACT(DAY FROM spotter_archive_output.date) = '".$filter['day']."'"; |
|
118
|
|
|
} |
|
119
|
|
|
} |
|
120
|
|
|
$filter_query_join .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output".preg_replace('/^ AND/',' WHERE',$filter_query_date).") sd ON sd.flightaware_id = spotter_archive_output.flightaware_id"; |
|
121
|
|
|
} |
|
122
|
|
|
if ($filter_query_where == '' && $where) $filter_query_where = ' WHERE'; |
|
123
|
|
|
elseif ($filter_query_where != '' && $and) $filter_query_where .= ' AND'; |
|
124
|
|
|
if ($filter_query_where != '') { |
|
125
|
|
|
$filter_query_where = preg_replace('/^ AND/',' WHERE',$filter_query_where); |
|
126
|
|
|
} |
|
127
|
|
|
$filter_query = $filter_query_join.$filter_query_where; |
|
128
|
|
|
return $filter_query; |
|
129
|
|
|
} |
|
130
|
|
|
|
|
131
|
|
|
// Spotter_archive |
|
132
|
|
|
public function addSpotterArchiveData($flightaware_id = '', $ident = '', $registration = '', $airline_name = '', $airline_icao = '', $airline_country = '', $airline_type = '', $aircraft_icao = '', $aircraft_shadow = '', $aircraft_name = '', $aircraft_manufacturer = '', $departure_airport_icao = '', $departure_airport_name = '', $departure_airport_city = '', $departure_airport_country = '', $departure_airport_time = '',$arrival_airport_icao = '', $arrival_airport_name = '', $arrival_airport_city ='', $arrival_airport_country = '', $arrival_airport_time = '', $route_stop = '', $date = '',$latitude = '', $longitude = '', $waypoints = '', $altitude = '', $real_altitude = '',$heading = '', $ground_speed = '', $squawk = '', $ModeS = '', $pilot_id = '', $pilot_name = '',$verticalrate = '',$format_source = '', $source_name = '', $over_country = '') { |
|
133
|
|
|
require_once(dirname(__FILE__).'/class.Spotter.php'); |
|
134
|
|
|
if ($over_country == '') { |
|
135
|
|
|
$Spotter = new Spotter($this->db); |
|
136
|
|
|
$data_country = $Spotter->getCountryFromLatitudeLongitude($latitude,$longitude); |
|
137
|
|
|
if (!empty($data_country)) $country = $data_country['iso2']; |
|
138
|
|
|
else $country = ''; |
|
139
|
|
|
} else $country = $over_country; |
|
140
|
|
|
if ($airline_type === NULL) $airline_type =''; |
|
141
|
|
|
|
|
142
|
|
|
//if ($country == '') echo "\n".'************ UNKNOW COUNTRY ****************'."\n"; |
|
143
|
|
|
//else echo "\n".'*/*/*/*/*/*/*/ Country : '.$country.' */*/*/*/*/*/*/*/*/'."\n"; |
|
144
|
|
|
|
|
145
|
|
|
// Route is not added in spotter_archive |
|
146
|
|
|
$query = "INSERT INTO spotter_archive (flightaware_id, ident, registration, airline_name, airline_icao, airline_country, airline_type, aircraft_icao, aircraft_shadow, aircraft_name, aircraft_manufacturer, departure_airport_icao, departure_airport_name, departure_airport_city, departure_airport_country, departure_airport_time,arrival_airport_icao, arrival_airport_name, arrival_airport_city, arrival_airport_country, arrival_airport_time, route_stop, date,latitude, longitude, waypoints, altitude, heading, ground_speed, squawk, ModeS, pilot_id, pilot_name, verticalrate,format_source,over_country,source_name,real_altitude) |
|
147
|
|
|
VALUES (:flightaware_id, :ident, :registration, :airline_name, :airline_icao, :airline_country, :airline_type, :aircraft_icao, :aircraft_shadow, :aircraft_name, :aircraft_manufacturer, :departure_airport_icao, :departure_airport_name, :departure_airport_city, :departure_airport_country, :departure_airport_time,:arrival_airport_icao, :arrival_airport_name, :arrival_airport_city, :arrival_airport_country, :arrival_airport_time, :route_stop, :date,:latitude, :longitude, :waypoints, :altitude, :heading, :ground_speed, :squawk, :ModeS, :pilot_id, :pilot_name, :verticalrate, :format_source, :over_country, :source_name,:real_altitude)"; |
|
148
|
|
|
|
|
149
|
|
|
$query_values = array(':flightaware_id' => $flightaware_id, ':ident' => $ident, ':registration' => $registration, ':airline_name' => $airline_name, ':airline_icao' => $airline_icao, ':airline_country' => $airline_country, ':airline_type' => $airline_type, ':aircraft_icao' => $aircraft_icao, ':aircraft_shadow' => $aircraft_shadow, ':aircraft_name' => $aircraft_name, ':aircraft_manufacturer' => $aircraft_manufacturer, ':departure_airport_icao' => $departure_airport_icao, ':departure_airport_name' => $departure_airport_name, ':departure_airport_city' => $departure_airport_city, ':departure_airport_country' => $departure_airport_country, ':departure_airport_time' => $departure_airport_time,':arrival_airport_icao' => $arrival_airport_icao, ':arrival_airport_name' => $arrival_airport_name, ':arrival_airport_city' => $arrival_airport_city, ':arrival_airport_country' => $arrival_airport_country, ':arrival_airport_time' => $arrival_airport_time, ':route_stop' => $route_stop, ':date' => $date,':latitude' => $latitude, ':longitude' => $longitude, ':waypoints' => $waypoints, ':altitude' => $altitude, ':heading' => $heading, ':ground_speed' => $ground_speed, ':squawk' => $squawk, ':ModeS' => $ModeS, ':pilot_id' => $pilot_id, ':pilot_name' => $pilot_name, ':verticalrate' => $verticalrate, ':format_source' => $format_source, ':over_country' => $country, ':source_name' => $source_name,':real_altitude' => $real_altitude); |
|
150
|
|
|
try { |
|
151
|
|
|
$sth = $this->db->prepare($query); |
|
152
|
|
|
$sth->execute($query_values); |
|
153
|
|
|
$sth->closeCursor(); |
|
154
|
|
|
} catch(PDOException $e) { |
|
155
|
|
|
return "error : ".$e->getMessage(); |
|
156
|
|
|
} |
|
157
|
|
|
return "success"; |
|
158
|
|
|
} |
|
159
|
|
|
|
|
160
|
|
|
|
|
161
|
|
|
/** |
|
162
|
|
|
* Gets all the spotter information based on a particular callsign |
|
163
|
|
|
* |
|
164
|
|
|
* @return Array the spotter information |
|
165
|
|
|
* |
|
166
|
|
|
*/ |
|
167
|
|
|
public function getLastArchiveSpotterDataByIdent($ident) { |
|
168
|
|
|
$Spotter = new Spotter($this->db); |
|
169
|
|
|
date_default_timezone_set('UTC'); |
|
170
|
|
|
|
|
171
|
|
|
$ident = filter_var($ident, FILTER_SANITIZE_STRING); |
|
172
|
|
|
//$query = "SELECT spotter_archive.* FROM spotter_archive INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_archive l WHERE l.ident = :ident GROUP BY l.flightaware_id) s on spotter_archive.flightaware_id = s.flightaware_id AND spotter_archive.date = s.maxdate LIMIT 1"; |
|
173
|
|
|
$query = "SELECT spotter_archive.* FROM spotter_archive WHERE ident = :ident ORDER BY date DESC LIMIT 1"; |
|
174
|
|
|
|
|
175
|
|
|
$spotter_array = $Spotter->getDataFromDB($query,array(':ident' => $ident)); |
|
176
|
|
|
|
|
177
|
|
|
return $spotter_array; |
|
178
|
|
|
} |
|
179
|
|
|
|
|
180
|
|
|
|
|
181
|
|
|
/** |
|
182
|
|
|
* Gets last the spotter information based on a particular id |
|
183
|
|
|
* |
|
184
|
|
|
* @return Array the spotter information |
|
185
|
|
|
* |
|
186
|
|
|
*/ |
|
187
|
|
|
public function getLastArchiveSpotterDataById($id) { |
|
188
|
|
|
$Spotter = new Spotter($this->db); |
|
189
|
|
|
date_default_timezone_set('UTC'); |
|
190
|
|
|
$id = filter_var($id, FILTER_SANITIZE_STRING); |
|
191
|
|
|
//$query = SpotterArchive->$global_query." WHERE spotter_archive.flightaware_id = :id"; |
|
192
|
|
|
//$query = "SELECT spotter_archive.* FROM spotter_archive INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_archive l WHERE l.flightaware_id = :id GROUP BY l.flightaware_id) s on spotter_archive.flightaware_id = s.flightaware_id AND spotter_archive.date = s.maxdate LIMIT 1"; |
|
193
|
|
|
$query = "SELECT * FROM spotter_archive WHERE flightaware_id = :id ORDER BY date DESC LIMIT 1"; |
|
194
|
|
|
|
|
195
|
|
|
// $spotter_array = Spotter->getDataFromDB($query,array(':id' => $id)); |
|
196
|
|
|
/* |
|
197
|
|
|
try { |
|
198
|
|
|
$Connection = new Connection(); |
|
199
|
|
|
$sth = Connection->$db->prepare($query); |
|
200
|
|
|
$sth->execute(array(':id' => $id)); |
|
201
|
|
|
} catch(PDOException $e) { |
|
202
|
|
|
return "error"; |
|
203
|
|
|
} |
|
204
|
|
|
$spotter_array = $sth->fetchAll(PDO->FETCH_ASSOC); |
|
205
|
|
|
*/ |
|
206
|
|
|
$spotter_array = $Spotter->getDataFromDB($query,array(':id' => $id)); |
|
207
|
|
|
|
|
208
|
|
|
return $spotter_array; |
|
209
|
|
|
} |
|
210
|
|
|
|
|
211
|
|
|
/** |
|
212
|
|
|
* Gets all the spotter information based on a particular id |
|
213
|
|
|
* |
|
214
|
|
|
* @return Array the spotter information |
|
215
|
|
|
* |
|
216
|
|
|
*/ |
|
217
|
|
|
public function getAllArchiveSpotterDataById($id) { |
|
218
|
|
|
date_default_timezone_set('UTC'); |
|
219
|
|
|
$id = filter_var($id, FILTER_SANITIZE_STRING); |
|
220
|
|
|
$query = $this->global_query." WHERE spotter_archive.flightaware_id = :id ORDER BY date"; |
|
221
|
|
|
|
|
222
|
|
|
// $spotter_array = Spotter->getDataFromDB($query,array(':id' => $id)); |
|
223
|
|
|
|
|
224
|
|
|
try { |
|
225
|
|
|
$sth = $this->db->prepare($query); |
|
226
|
|
|
$sth->execute(array(':id' => $id)); |
|
227
|
|
|
} catch(PDOException $e) { |
|
228
|
|
|
echo $e->getMessage(); |
|
229
|
|
|
die; |
|
230
|
|
|
} |
|
231
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
232
|
|
|
|
|
233
|
|
|
return $spotter_array; |
|
234
|
|
|
} |
|
235
|
|
|
|
|
236
|
|
|
/** |
|
237
|
|
|
* Gets coordinate & time spotter information based on a particular id |
|
238
|
|
|
* |
|
239
|
|
|
* @return Array the spotter information |
|
240
|
|
|
* |
|
241
|
|
|
*/ |
|
242
|
|
|
public function getCoordArchiveSpotterDataById($id) { |
|
243
|
|
|
date_default_timezone_set('UTC'); |
|
244
|
|
|
$id = filter_var($id, FILTER_SANITIZE_STRING); |
|
245
|
|
|
$query = "SELECT spotter_archive.latitude, spotter_archive.longitude, spotter_archive.date FROM spotter_archive WHERE spotter_archive.flightaware_id = :id ORDER by spotter_archive.date ASC"; |
|
246
|
|
|
try { |
|
247
|
|
|
$sth = $this->db->prepare($query); |
|
248
|
|
|
$sth->execute(array(':id' => $id)); |
|
249
|
|
|
} catch(PDOException $e) { |
|
250
|
|
|
echo $e->getMessage(); |
|
251
|
|
|
die; |
|
252
|
|
|
} |
|
253
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
254
|
|
|
return $spotter_array; |
|
255
|
|
|
} |
|
256
|
|
|
|
|
257
|
|
|
/** |
|
258
|
|
|
* Gets coordinate & time spotter information based on a particular id |
|
259
|
|
|
* |
|
260
|
|
|
* @return Array the spotter information |
|
261
|
|
|
* |
|
262
|
|
|
*/ |
|
263
|
|
|
public function getCoordArchiveSpotterDataByIdDate($id,$begindate,$enddate) { |
|
264
|
|
|
date_default_timezone_set('UTC'); |
|
265
|
|
|
$id = filter_var($id, FILTER_SANITIZE_STRING); |
|
266
|
|
|
$query = "SELECT spotter_archive.latitude, spotter_archive.longitude, spotter_archive.date FROM spotter_archive WHERE spotter_archive.flightaware_id = :id AND spotter_archive.date BETWEEN :begindate AND :enddate ORDER by spotter_archive.date ASC"; |
|
267
|
|
|
try { |
|
268
|
|
|
$sth = $this->db->prepare($query); |
|
269
|
|
|
$sth->execute(array(':id' => $id,':begindate' => $begindate,':enddate' => $enddate)); |
|
270
|
|
|
} catch(PDOException $e) { |
|
271
|
|
|
echo $e->getMessage(); |
|
272
|
|
|
die; |
|
273
|
|
|
} |
|
274
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
275
|
|
|
return $spotter_array; |
|
276
|
|
|
} |
|
277
|
|
|
|
|
278
|
|
|
|
|
279
|
|
|
/** |
|
280
|
|
|
* Gets altitude information based on a particular callsign |
|
281
|
|
|
* |
|
282
|
|
|
* @return Array the spotter information |
|
283
|
|
|
* |
|
284
|
|
|
*/ |
|
285
|
|
|
public function getAltitudeArchiveSpotterDataByIdent($ident) { |
|
286
|
|
|
|
|
287
|
|
|
date_default_timezone_set('UTC'); |
|
288
|
|
|
|
|
289
|
|
|
$ident = filter_var($ident, FILTER_SANITIZE_STRING); |
|
290
|
|
|
$query = "SELECT spotter_archive.altitude, spotter_archive.date FROM spotter_archive WHERE spotter_archive.ident = :ident AND spotter_archive.latitude <> 0 AND spotter_archive.longitude <> 0 ORDER BY date"; |
|
291
|
|
|
|
|
292
|
|
|
try { |
|
293
|
|
|
$sth = $this->db->prepare($query); |
|
294
|
|
|
$sth->execute(array(':ident' => $ident)); |
|
295
|
|
|
} catch(PDOException $e) { |
|
296
|
|
|
echo $e->getMessage(); |
|
297
|
|
|
die; |
|
298
|
|
|
} |
|
299
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
300
|
|
|
|
|
301
|
|
|
return $spotter_array; |
|
302
|
|
|
} |
|
303
|
|
|
|
|
304
|
|
|
/** |
|
305
|
|
|
* Gets altitude information based on a particular id |
|
306
|
|
|
* |
|
307
|
|
|
* @return Array the spotter information |
|
308
|
|
|
* |
|
309
|
|
|
*/ |
|
310
|
|
|
public function getAltitudeArchiveSpotterDataById($id) { |
|
311
|
|
|
|
|
312
|
|
|
date_default_timezone_set('UTC'); |
|
313
|
|
|
|
|
314
|
|
|
$id = filter_var($id, FILTER_SANITIZE_STRING); |
|
315
|
|
|
$query = "SELECT spotter_archive.altitude, spotter_archive.date FROM spotter_archive WHERE spotter_archive.flightaware_id = :id AND spotter_archive.latitude <> 0 AND spotter_archive.longitude <> 0 ORDER BY date"; |
|
316
|
|
|
|
|
317
|
|
|
try { |
|
318
|
|
|
$sth = $this->db->prepare($query); |
|
319
|
|
|
$sth->execute(array(':id' => $id)); |
|
320
|
|
|
} catch(PDOException $e) { |
|
321
|
|
|
echo $e->getMessage(); |
|
322
|
|
|
die; |
|
323
|
|
|
} |
|
324
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
325
|
|
|
|
|
326
|
|
|
return $spotter_array; |
|
327
|
|
|
} |
|
328
|
|
|
|
|
329
|
|
|
/** |
|
330
|
|
|
* Gets altitude & speed information based on a particular id |
|
331
|
|
|
* |
|
332
|
|
|
* @return Array the spotter information |
|
333
|
|
|
* |
|
334
|
|
|
*/ |
|
335
|
|
|
public function getAltitudeSpeedArchiveSpotterDataById($id) { |
|
336
|
|
|
date_default_timezone_set('UTC'); |
|
337
|
|
|
$id = filter_var($id, FILTER_SANITIZE_STRING); |
|
338
|
|
|
$query = "SELECT spotter_archive.altitude, spotter_archive.real_altitude,spotter_archive.ground_speed, spotter_archive.date FROM spotter_archive WHERE spotter_archive.flightaware_id = :id ORDER BY date"; |
|
339
|
|
|
try { |
|
340
|
|
|
$sth = $this->db->prepare($query); |
|
341
|
|
|
$sth->execute(array(':id' => $id)); |
|
342
|
|
|
} catch(PDOException $e) { |
|
343
|
|
|
echo $e->getMessage(); |
|
344
|
|
|
die; |
|
345
|
|
|
} |
|
346
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
347
|
|
|
return $spotter_array; |
|
348
|
|
|
} |
|
349
|
|
|
|
|
350
|
|
|
/** |
|
351
|
|
|
* Gets altitude information based on a particular callsign |
|
352
|
|
|
* |
|
353
|
|
|
* @return Array the spotter information |
|
354
|
|
|
* |
|
355
|
|
|
*/ |
|
356
|
|
|
public function getLastAltitudeArchiveSpotterDataByIdent($ident) { |
|
357
|
|
|
date_default_timezone_set('UTC'); |
|
358
|
|
|
$ident = filter_var($ident, FILTER_SANITIZE_STRING); |
|
359
|
|
|
$query = "SELECT spotter_archive.altitude, spotter_archive.date FROM spotter_archive INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_archive l WHERE l.ident = :ident GROUP BY l.flightaware_id) s on spotter_archive.flightaware_id = s.flightaware_id AND spotter_archive.date = s.maxdate LIMIT 1"; |
|
360
|
|
|
// $query = "SELECT spotter_archive.altitude, spotter_archive.date FROM spotter_archive WHERE spotter_archive.ident = :ident"; |
|
361
|
|
|
try { |
|
362
|
|
|
$sth = $this->db->prepare($query); |
|
363
|
|
|
$sth->execute(array(':ident' => $ident)); |
|
364
|
|
|
} catch(PDOException $e) { |
|
365
|
|
|
echo $e->getMessage(); |
|
366
|
|
|
die; |
|
367
|
|
|
} |
|
368
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
369
|
|
|
return $spotter_array; |
|
370
|
|
|
} |
|
371
|
|
|
|
|
372
|
|
|
|
|
373
|
|
|
|
|
374
|
|
|
/** |
|
375
|
|
|
* Gets all the archive spotter information |
|
376
|
|
|
* |
|
377
|
|
|
* @return Array the spotter information |
|
378
|
|
|
* |
|
379
|
|
|
*/ |
|
380
|
|
|
public function getSpotterArchiveData($ident,$flightaware_id,$date) { |
|
381
|
|
|
$Spotter = new Spotter($this->db); |
|
382
|
|
|
$ident = filter_var($ident, FILTER_SANITIZE_STRING); |
|
383
|
|
|
$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"; |
|
384
|
|
|
$spotter_array = $Spotter->getDataFromDB($query,array(':ident' => $ident,':flightaware_id' => $flightaware_id,':date' => $date.'%')); |
|
385
|
|
|
return $spotter_array; |
|
386
|
|
|
} |
|
387
|
|
|
|
|
388
|
|
|
public function deleteSpotterArchiveTrackData() { |
|
389
|
|
|
global $globalArchiveKeepTrackMonths, $globalDBdriver; |
|
390
|
|
|
if ($globalDBdriver == 'mysql') { |
|
391
|
|
|
$query = 'DELETE FROM spotter_archive WHERE spotter_archive.date < DATE_SUB(UTC_TIMESTAMP(), INTERVAL '.$globalArchiveKeepTrackMonths.' MONTH) LIMIT 10000'; |
|
392
|
|
|
} else { |
|
393
|
|
|
$query = "DELETE FROM spotter_archive WHERE spotter_archive_id IN (SELECT spotter_archive_id FROM spotter_archive WHERE spotter_archive.date < CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalArchiveKeepTrackMonths." MONTH' LIMIT 10000)"; |
|
394
|
|
|
} |
|
395
|
|
|
try { |
|
396
|
|
|
$sth = $this->db->prepare($query); |
|
397
|
|
|
$sth->execute(); |
|
398
|
|
|
} catch(PDOException $e) { |
|
399
|
|
|
echo $e->getMessage(); |
|
400
|
|
|
die; |
|
401
|
|
|
} |
|
402
|
|
|
} |
|
403
|
|
|
public function deleteSpotterArchiveTrackDataByID($id) { |
|
404
|
|
|
global $globalArchiveKeepTrackMonths, $globalDBdriver; |
|
405
|
|
|
$query = 'DELETE FROM spotter_archive WHERE spotter_archive.flightaware_id = :id'; |
|
406
|
|
|
try { |
|
407
|
|
|
$sth = $this->db->prepare($query); |
|
408
|
|
|
$sth->execute(array(':id' => $id)); |
|
409
|
|
|
} catch(PDOException $e) { |
|
410
|
|
|
echo $e->getMessage(); |
|
411
|
|
|
die; |
|
412
|
|
|
} |
|
413
|
|
|
} |
|
414
|
|
|
|
|
415
|
|
|
/** |
|
416
|
|
|
* Gets Minimal Live Spotter data |
|
417
|
|
|
* |
|
418
|
|
|
* @return Array the spotter information |
|
419
|
|
|
* |
|
420
|
|
|
*/ |
|
421
|
|
|
public function getMinLiveSpotterData($begindate,$enddate,$filter = array(),$part = 0) { |
|
422
|
|
|
global $globalDBdriver, $globalLiveInterval; |
|
423
|
|
|
date_default_timezone_set('UTC'); |
|
424
|
|
|
//$filter_query = $this->getFilter($filter,true,true); |
|
425
|
|
|
|
|
426
|
|
|
$filter_query = ''; |
|
427
|
|
|
if (isset($filter['source']) && !empty($filter['source'])) { |
|
428
|
|
|
$filter_query .= " AND format_source IN ('".implode("','",$filter['source'])."') "; |
|
429
|
|
|
} |
|
430
|
|
|
// Use spotter_output also ? |
|
431
|
|
|
if (isset($filter['airlines']) && !empty($filter['airlines'])) { |
|
432
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.airline_icao IN ('".implode("','",$filter['airlines'])."')) so ON so.flightaware_id = spotter_archive.flightaware_id "; |
|
433
|
|
|
} |
|
434
|
|
|
if (isset($filter['airlinestype']) && !empty($filter['airlinestype'])) { |
|
435
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.airline_type = '".$filter['airlinestype']."') sa ON sa.flightaware_id = spotter_archive.flightaware_id "; |
|
436
|
|
|
} |
|
437
|
|
|
if (isset($filter['source_aprs']) && !empty($filter['source_aprs'])) { |
|
438
|
|
|
$filter_query = " AND format_source = 'aprs' AND source_name IN ('".implode("','",$filter['source_aprs'])."')"; |
|
439
|
|
|
} |
|
440
|
|
|
|
|
441
|
|
|
$limit = ''; |
|
442
|
|
|
if ($part != 0) { |
|
443
|
|
|
$limit = ' LIMIT 100 OFFSET '.($part-1)*100; |
|
444
|
|
|
} |
|
445
|
|
|
if ($globalDBdriver == 'mysql') { |
|
446
|
|
|
$query = 'SELECT spotter_archive.date,spotter_archive.flightaware_id, spotter_archive.ident, spotter_archive.aircraft_icao, spotter_archive.departure_airport_icao as departure_airport, spotter_archive.arrival_airport_icao as arrival_airport, spotter_archive.latitude, spotter_archive.longitude, spotter_archive.altitude, spotter_archive.heading, spotter_archive.ground_speed, spotter_archive.squawk, a.aircraft_shadow,a.engine_type, a.engine_count, a.wake_category |
|
447
|
|
|
FROM spotter_archive |
|
448
|
|
|
INNER JOIN aircraft a on spotter_archive.aircraft_icao = a.icao |
|
449
|
|
|
WHERE spotter_archive.date BETWEEN '."'".$begindate."'".' AND '."'".$enddate."'".' |
|
450
|
|
|
'.$filter_query.' ORDER BY flightaware_id'.$limit; |
|
451
|
|
|
} else { |
|
452
|
|
|
|
|
453
|
|
|
$query = 'SELECT spotter_archive.flightaware_id, spotter_archive.date, spotter_archive.ident, spotter_archive.aircraft_icao, spotter_archive.departure_airport_icao as departure_airport, spotter_archive.arrival_airport_icao as arrival_airport, spotter_archive.latitude, spotter_archive.longitude, spotter_archive.altitude, spotter_archive.heading, spotter_archive.ground_speed, spotter_archive.squawk, a.aircraft_shadow,a.engine_type, a.engine_count, a.wake_category |
|
454
|
|
|
FROM spotter_archive |
|
455
|
|
|
INNER JOIN aircraft a on spotter_archive.aircraft_icao = a.icao |
|
456
|
|
|
WHERE spotter_archive.date BETWEEN '."'".$begindate."'".' AND '."'".$enddate."'".' |
|
457
|
|
|
'.$filter_query.' ORDER BY flightaware_id'.$limit; |
|
458
|
|
|
|
|
459
|
|
|
/* |
|
460
|
|
|
$query = 'SELECT spotter_archive.flightaware_id, spotter_archive.date, spotter_archive.ident, spotter_archive.aircraft_icao, spotter_archive.departure_airport_icao as departure_airport, spotter_archive.arrival_airport_icao as arrival_airport, spotter_archive.latitude, spotter_archive.longitude, spotter_archive.altitude, spotter_archive.heading, spotter_archive.ground_speed, spotter_archive.squawk |
|
461
|
|
|
FROM spotter_archive |
|
462
|
|
|
WHERE spotter_archive.date BETWEEN '."'".$begindate."'".' AND '."'".$enddate."'".' |
|
463
|
|
|
'.$filter_query.' ORDER BY flightaware_id'.$limit; |
|
464
|
|
|
*/ |
|
465
|
|
|
|
|
466
|
|
|
} |
|
467
|
|
|
//echo $query; |
|
468
|
|
|
try { |
|
469
|
|
|
$sth = $this->db->prepare($query); |
|
470
|
|
|
$sth->execute(); |
|
471
|
|
|
} catch(PDOException $e) { |
|
472
|
|
|
echo $e->getMessage(); |
|
473
|
|
|
die; |
|
474
|
|
|
} |
|
475
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
476
|
|
|
return $spotter_array; |
|
477
|
|
|
} |
|
478
|
|
|
|
|
479
|
|
|
/** |
|
480
|
|
|
* Gets Minimal Live Spotter data |
|
481
|
|
|
* |
|
482
|
|
|
* @return Array the spotter information |
|
483
|
|
|
* |
|
484
|
|
|
*/ |
|
485
|
|
|
public function getMinLiveSpotterDataPlayback($begindate,$enddate,$filter = array()) { |
|
486
|
|
|
global $globalDBdriver, $globalLiveInterval; |
|
487
|
|
|
date_default_timezone_set('UTC'); |
|
488
|
|
|
|
|
489
|
|
|
//$filter_query = $this->getFilter($filter,true,true); |
|
490
|
|
|
|
|
491
|
|
|
$filter_query = ''; |
|
492
|
|
|
if (isset($filter['source']) && !empty($filter['source'])) { |
|
493
|
|
|
$filter_query .= " AND format_source IN ('".implode("','",$filter['source'])."') "; |
|
494
|
|
|
} |
|
495
|
|
|
// Use spotter_output also ? |
|
496
|
|
|
if (isset($filter['airlines']) && !empty($filter['airlines'])) { |
|
497
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.airline_icao IN ('".implode("','",$filter['airlines'])."')) so ON so.flightaware_id = spotter_archive.flightaware_id "; |
|
498
|
|
|
} |
|
499
|
|
|
if (isset($filter['airlinestype']) && !empty($filter['airlinestype'])) { |
|
500
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_archive_output WHERE spotter_archive_output.airline_type = '".$filter['airlinestype']."') sa ON sa.flightaware_id = spotter_archive.flightaware_id "; |
|
501
|
|
|
} |
|
502
|
|
|
if (isset($filter['source_aprs']) && !empty($filter['source_aprs'])) { |
|
503
|
|
|
$filter_query = " AND format_source = 'aprs' AND source_name IN ('".implode("','",$filter['source_aprs'])."')"; |
|
504
|
|
|
} |
|
505
|
|
|
|
|
506
|
|
|
|
|
507
|
|
|
//if (!isset($globalLiveInterval)) $globalLiveInterval = '200'; |
|
508
|
|
|
if ($globalDBdriver == 'mysql') { |
|
509
|
|
|
/* |
|
510
|
|
|
$query = 'SELECT a.aircraft_shadow, spotter_archive.ident, spotter_archive.flightaware_id, spotter_archive.aircraft_icao, spotter_archive.departure_airport_icao as departure_airport, spotter_archive.arrival_airport_icao as arrival_airport, spotter_archive.latitude, spotter_archive.longitude, spotter_archive.altitude, spotter_archive.heading, spotter_archive.ground_speed, spotter_archive.squawk |
|
511
|
|
|
FROM spotter_archive |
|
512
|
|
|
INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_archive l WHERE (l.date BETWEEN '."'".$begindate."'".' AND '."'".$enddate."'".') GROUP BY l.flightaware_id) s on spotter_archive.flightaware_id = s.flightaware_id AND spotter_archive.date = s.maxdate '.$filter_query.'LEFT JOIN (SELECT aircraft_shadow,icao FROM aircraft) a ON spotter_archive.aircraft_icao = a.icao'; |
|
513
|
|
|
*/ |
|
514
|
|
|
$query = 'SELECT a.aircraft_shadow, spotter_archive_output.ident, spotter_archive_output.flightaware_id, spotter_archive_output.aircraft_icao, spotter_archive_output.departure_airport_icao as departure_airport, spotter_archive_output.arrival_airport_icao as arrival_airport, spotter_archive_output.latitude, spotter_archive_output.longitude, spotter_archive_output.altitude, spotter_archive_output.heading, spotter_archive_output.ground_speed, spotter_archive_output.squawk |
|
515
|
|
|
FROM spotter_archive_output |
|
516
|
|
|
LEFT JOIN (SELECT aircraft_shadow,icao FROM aircraft) a ON spotter_archive_output.aircraft_icao = a.icao |
|
517
|
|
|
WHERE (spotter_archive_output.date BETWEEN '."'".$begindate."'".' AND '."'".$enddate."'".') |
|
518
|
|
|
'.$filter_query.' GROUP BY spotter_archive_output.flightaware_id, spotter_archive_output.ident, spotter_archive_output.aircraft_icao, spotter_archive_output.departure_airport_icao, spotter_archive_output.arrival_airport_icao, spotter_archive_output.latitude, spotter_archive_output.longitude, spotter_archive_output.altitude, spotter_archive_output.heading, spotter_archive_output.ground_speed, spotter_archive_output.squawk, a.aircraft_shadow'; |
|
519
|
|
|
|
|
520
|
|
|
} else { |
|
521
|
|
|
//$query = 'SELECT spotter_archive_output.ident, spotter_archive_output.flightaware_id, spotter_archive_output.aircraft_icao, spotter_archive_output.departure_airport_icao as departure_airport, spotter_archive_output.arrival_airport_icao as arrival_airport, spotter_archive_output.latitude, spotter_archive_output.longitude, spotter_archive_output.altitude, spotter_archive_output.heading, spotter_archive_output.ground_speed, spotter_archive_output.squawk, a.aircraft_shadow FROM spotter_archive_output INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_archive_output l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_archive_output.flightaware_id = s.flightaware_id AND spotter_archive_output.date = s.maxdate '.$filter_query.'INNER JOIN (SELECT * FROM aircraft) a on spotter_archive_output.aircraft_icao = a.icao'; |
|
522
|
|
|
/* |
|
523
|
|
|
$query = 'SELECT spotter_archive_output.ident, spotter_archive_output.flightaware_id, spotter_archive_output.aircraft_icao, spotter_archive_output.departure_airport_icao as departure_airport, spotter_archive_output.arrival_airport_icao as arrival_airport, spotter_archive_output.latitude, spotter_archive_output.longitude, spotter_archive_output.altitude, spotter_archive_output.heading, spotter_archive_output.ground_speed, spotter_archive_output.squawk, a.aircraft_shadow |
|
524
|
|
|
FROM spotter_archive_output |
|
525
|
|
|
INNER JOIN (SELECT * FROM aircraft) a on spotter_archive_output.aircraft_icao = a.icao |
|
526
|
|
|
WHERE spotter_archive_output.date >= '."'".$begindate."'".' AND spotter_archive_output.date <= '."'".$enddate."'".' |
|
527
|
|
|
'.$filter_query.' GROUP BY spotter_archive_output.flightaware_id, spotter_archive_output.ident, spotter_archive_output.aircraft_icao, spotter_archive_output.departure_airport_icao, spotter_archive_output.arrival_airport_icao, spotter_archive_output.latitude, spotter_archive_output.longitude, spotter_archive_output.altitude, spotter_archive_output.heading, spotter_archive_output.ground_speed, spotter_archive_output.squawk, a.aircraft_shadow'; |
|
528
|
|
|
*/ |
|
529
|
|
|
$query = 'SELECT DISTINCT spotter_archive_output.flightaware_id, spotter_archive_output.ident, spotter_archive_output.aircraft_icao, spotter_archive_output.departure_airport_icao as departure_airport, spotter_archive_output.arrival_airport_icao as arrival_airport, spotter_archive_output.latitude, spotter_archive_output.longitude, spotter_archive_output.altitude, spotter_archive_output.heading, spotter_archive_output.ground_speed, spotter_archive_output.squawk, a.aircraft_shadow |
|
530
|
|
|
FROM spotter_archive_output |
|
531
|
|
|
INNER JOIN (SELECT * FROM aircraft) a on spotter_archive_output.aircraft_icao = a.icao |
|
532
|
|
|
WHERE spotter_archive_output.date >= '."'".$begindate."'".' AND spotter_archive_output.date <= '."'".$enddate."'".' |
|
533
|
|
|
'.$filter_query.' LIMIT 200 OFFSET 0'; |
|
534
|
|
|
// .' GROUP BY spotter_output.flightaware_id, spotter_output.ident, spotter_output.aircraft_icao, spotter_output.departure_airport_icao, spotter_output.arrival_airport_icao, spotter_output.latitude, spotter_output.longitude, spotter_output.altitude, spotter_output.heading, spotter_output.ground_speed, spotter_output.squawk, a.aircraft_shadow'; |
|
535
|
|
|
|
|
536
|
|
|
} |
|
537
|
|
|
//echo $query; |
|
538
|
|
|
try { |
|
539
|
|
|
$sth = $this->db->prepare($query); |
|
540
|
|
|
$sth->execute(); |
|
541
|
|
|
} catch(PDOException $e) { |
|
542
|
|
|
echo $e->getMessage(); |
|
543
|
|
|
die; |
|
544
|
|
|
} |
|
545
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
546
|
|
|
|
|
547
|
|
|
return $spotter_array; |
|
548
|
|
|
} |
|
549
|
|
|
|
|
550
|
|
|
/** |
|
551
|
|
|
* Gets count Live Spotter data |
|
552
|
|
|
* |
|
553
|
|
|
* @return Array the spotter information |
|
554
|
|
|
* |
|
555
|
|
|
*/ |
|
556
|
|
|
public function getLiveSpotterCount($begindate,$enddate,$filter = array()) { |
|
557
|
|
|
global $globalDBdriver, $globalLiveInterval; |
|
558
|
|
|
date_default_timezone_set('UTC'); |
|
559
|
|
|
|
|
560
|
|
|
$filter_query = ''; |
|
561
|
|
|
if (isset($filter['source']) && !empty($filter['source'])) { |
|
562
|
|
|
$filter_query .= " AND format_source IN ('".implode("','",$filter['source'])."') "; |
|
563
|
|
|
} |
|
564
|
|
|
if (isset($filter['airlines']) && !empty($filter['airlines'])) { |
|
565
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_output WHERE spotter_output.airline_icao IN ('".implode("','",$filter['airlines'])."')) so ON so.flightaware_id = spotter_archive.flightaware_id "; |
|
566
|
|
|
} |
|
567
|
|
|
if (isset($filter['airlinestype']) && !empty($filter['airlinestype'])) { |
|
568
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_output WHERE spotter_output.airline_type = '".$filter['airlinestype']."') sa ON sa.flightaware_id = spotter_archive.flightaware_id "; |
|
569
|
|
|
} |
|
570
|
|
|
if (isset($filter['source_aprs']) && !empty($filter['source_aprs'])) { |
|
571
|
|
|
$filter_query = " AND format_source = 'aprs' AND source_name IN ('".implode("','",$filter['source_aprs'])."')"; |
|
572
|
|
|
} |
|
573
|
|
|
|
|
574
|
|
|
//if (!isset($globalLiveInterval)) $globalLiveInterval = '200'; |
|
575
|
|
|
if ($globalDBdriver == 'mysql') { |
|
576
|
|
|
$query = 'SELECT COUNT(DISTINCT flightaware_id) as nb |
|
577
|
|
|
FROM spotter_archive l |
|
578
|
|
|
WHERE (l.date BETWEEN DATE_SUB('."'".$begindate."'".',INTERVAL '.$globalLiveInterval.' SECOND) AND '."'".$begindate."'".')'.$filter_query; |
|
579
|
|
|
} else { |
|
580
|
|
|
$query = 'SELECT COUNT(DISTINCT flightaware_id) as nb FROM spotter_archive l WHERE (l.date BETWEEN '."'".$begindate."' - INTERVAL '".$globalLiveInterval." SECONDS' AND "."'".$enddate."'".')'.$filter_query; |
|
581
|
|
|
} |
|
582
|
|
|
//echo $query; |
|
583
|
|
|
try { |
|
584
|
|
|
$sth = $this->db->prepare($query); |
|
585
|
|
|
$sth->execute(); |
|
586
|
|
|
} catch(PDOException $e) { |
|
587
|
|
|
echo $e->getMessage(); |
|
588
|
|
|
die; |
|
589
|
|
|
} |
|
590
|
|
|
$result = $sth->fetch(PDO::FETCH_ASSOC); |
|
591
|
|
|
$sth->closeCursor(); |
|
592
|
|
|
return $result['nb']; |
|
593
|
|
|
|
|
594
|
|
|
} |
|
595
|
|
|
|
|
596
|
|
|
|
|
597
|
|
|
|
|
598
|
|
|
// Spotter_Archive_output |
|
599
|
|
|
|
|
600
|
|
|
/** |
|
601
|
|
|
* Gets all the spotter information |
|
602
|
|
|
* |
|
603
|
|
|
* @return Array the spotter information |
|
604
|
|
|
* |
|
605
|
|
|
*/ |
|
606
|
|
|
public function searchSpotterData($q = '', $registration = '', $aircraft_icao = '', $aircraft_manufacturer = '', $highlights = '', $airline_icao = '', $airline_country = '', $airline_type = '', $airport = '', $airport_country = '', $callsign = '', $departure_airport_route = '', $arrival_airport_route = '', $owner = '',$pilot_id = '',$pilot_name = '',$altitude = '', $date_posted = '', $limit = '', $sort = '', $includegeodata = '',$origLat = '',$origLon = '',$dist = '', $filters=array()) { |
|
607
|
|
|
global $globalTimezone, $globalDBdriver; |
|
608
|
|
|
require_once(dirname(__FILE__).'/class.Translation.php'); |
|
609
|
|
|
$Translation = new Translation($this->db); |
|
610
|
|
|
$Spotter = new Spotter($this->db); |
|
611
|
|
|
|
|
612
|
|
|
date_default_timezone_set('UTC'); |
|
613
|
|
|
|
|
614
|
|
|
$query_values = array(); |
|
615
|
|
|
$additional_query = ''; |
|
616
|
|
|
$limit_query = ''; |
|
617
|
|
|
$filter_query = $this->getFilter($filters); |
|
618
|
|
|
if ($q != "") { |
|
619
|
|
|
if (!is_string($q)) { |
|
620
|
|
|
return false; |
|
|
|
|
|
|
621
|
|
|
} else { |
|
622
|
|
|
|
|
623
|
|
|
$q_array = explode(" ", $q); |
|
624
|
|
|
|
|
625
|
|
|
foreach ($q_array as $q_item) { |
|
626
|
|
|
$additional_query .= " AND ("; |
|
627
|
|
|
$additional_query .= "(spotter_archive_output.spotter_id like '%".$q_item."%') OR "; |
|
628
|
|
|
$additional_query .= "(spotter_archive_output.aircraft_icao like '%".$q_item."%') OR "; |
|
629
|
|
|
$additional_query .= "(spotter_archive_output.aircraft_name like '%".$q_item."%') OR "; |
|
630
|
|
|
$additional_query .= "(spotter_archive_output.aircraft_manufacturer like '%".$q_item."%') OR "; |
|
631
|
|
|
$additional_query .= "(spotter_archive_output.airline_icao like '%".$q_item."%') OR "; |
|
632
|
|
|
$additional_query .= "(spotter_archive_output.airline_name like '%".$q_item."%') OR "; |
|
633
|
|
|
$additional_query .= "(spotter_archive_output.airline_country like '%".$q_item."%') OR "; |
|
634
|
|
|
$additional_query .= "(spotter_archive_output.departure_airport_icao like '%".$q_item."%') OR "; |
|
635
|
|
|
$additional_query .= "(spotter_archive_output.departure_airport_name like '%".$q_item."%') OR "; |
|
636
|
|
|
$additional_query .= "(spotter_archive_output.departure_airport_city like '%".$q_item."%') OR "; |
|
637
|
|
|
$additional_query .= "(spotter_archive_output.departure_airport_country like '%".$q_item."%') OR "; |
|
638
|
|
|
$additional_query .= "(spotter_archive_output.arrival_airport_icao like '%".$q_item."%') OR "; |
|
639
|
|
|
$additional_query .= "(spotter_archive_output.arrival_airport_name like '%".$q_item."%') OR "; |
|
640
|
|
|
$additional_query .= "(spotter_archive_output.arrival_airport_city like '%".$q_item."%') OR "; |
|
641
|
|
|
$additional_query .= "(spotter_archive_output.arrival_airport_country like '%".$q_item."%') OR "; |
|
642
|
|
|
$additional_query .= "(spotter_archive_output.registration like '%".$q_item."%') OR "; |
|
643
|
|
|
$additional_query .= "(spotter_archive_output.owner_name like '%".$q_item."%') OR "; |
|
644
|
|
|
$additional_query .= "(spotter_archive_output.pilot_id like '%".$q_item."%') OR "; |
|
645
|
|
|
$additional_query .= "(spotter_archive_output.pilot_name like '%".$q_item."%') OR "; |
|
646
|
|
|
$additional_query .= "(spotter_archive_output.ident like '%".$q_item."%') OR "; |
|
647
|
|
|
$translate = $Translation->ident2icao($q_item); |
|
648
|
|
|
if ($translate != $q_item) $additional_query .= "(spotter_archive_output.ident like '%".$translate."%') OR "; |
|
649
|
|
|
$additional_query .= "(spotter_archive_output.highlight like '%".$q_item."%')"; |
|
650
|
|
|
$additional_query .= ")"; |
|
651
|
|
|
} |
|
652
|
|
|
} |
|
653
|
|
|
} |
|
654
|
|
|
|
|
655
|
|
|
if ($registration != "") { |
|
656
|
|
|
$registration = filter_var($registration,FILTER_SANITIZE_STRING); |
|
657
|
|
|
if (!is_string($registration)) { |
|
658
|
|
|
return array(); |
|
659
|
|
|
} else { |
|
660
|
|
|
$additional_query .= " AND (spotter_archive_output.registration = '".$registration."')"; |
|
661
|
|
|
} |
|
662
|
|
|
} |
|
663
|
|
|
|
|
664
|
|
|
if ($aircraft_icao != "") { |
|
665
|
|
|
$aircraft_icao = filter_var($aircraft_icao,FILTER_SANITIZE_STRING); |
|
666
|
|
|
if (!is_string($aircraft_icao)) { |
|
667
|
|
|
return array(); |
|
668
|
|
|
} else { |
|
669
|
|
|
$additional_query .= " AND (spotter_archive_output.aircraft_icao = '".$aircraft_icao."')"; |
|
670
|
|
|
} |
|
671
|
|
|
} |
|
672
|
|
|
|
|
673
|
|
|
if ($aircraft_manufacturer != "") { |
|
674
|
|
|
$aircraft_manufacturer = filter_var($aircraft_manufacturer,FILTER_SANITIZE_STRING); |
|
675
|
|
|
if (!is_string($aircraft_manufacturer)) { |
|
676
|
|
|
return array(); |
|
677
|
|
|
} else { |
|
678
|
|
|
$additional_query .= " AND (spotter_archive_output.aircraft_manufacturer = '".$aircraft_manufacturer."')"; |
|
679
|
|
|
} |
|
680
|
|
|
} |
|
681
|
|
|
|
|
682
|
|
|
if ($highlights == "true") { |
|
683
|
|
|
if (!is_string($highlights)) { |
|
684
|
|
|
return array(); |
|
685
|
|
|
} else { |
|
686
|
|
|
$additional_query .= " AND (spotter_archive_output.highlight <> '')"; |
|
687
|
|
|
} |
|
688
|
|
|
} |
|
689
|
|
|
|
|
690
|
|
|
if ($airline_icao != "") { |
|
691
|
|
|
$airline_icao = filter_var($airline_icao,FILTER_SANITIZE_STRING); |
|
692
|
|
|
if (!is_string($airline_icao)) { |
|
693
|
|
|
return array(); |
|
694
|
|
|
} else { |
|
695
|
|
|
$additional_query .= " AND (spotter_archive_output.airline_icao = '".$airline_icao."')"; |
|
696
|
|
|
} |
|
697
|
|
|
} |
|
698
|
|
|
|
|
699
|
|
|
if ($airline_country != "") { |
|
700
|
|
|
$airline_country = filter_var($airline_country,FILTER_SANITIZE_STRING); |
|
701
|
|
|
if (!is_string($airline_country)) { |
|
702
|
|
|
return array(); |
|
703
|
|
|
} else { |
|
704
|
|
|
$additional_query .= " AND (spotter_archive_output.airline_country = '".$airline_country."')"; |
|
705
|
|
|
} |
|
706
|
|
|
} |
|
707
|
|
|
|
|
708
|
|
|
if ($airline_type != "") { |
|
709
|
|
|
$airline_type = filter_var($airline_type,FILTER_SANITIZE_STRING); |
|
710
|
|
|
if (!is_string($airline_type)) { |
|
711
|
|
|
return array(); |
|
712
|
|
|
} else { |
|
713
|
|
|
if ($airline_type == "passenger") { |
|
714
|
|
|
$additional_query .= " AND (spotter_archive_output.airline_type = 'passenger')"; |
|
715
|
|
|
} |
|
716
|
|
|
if ($airline_type == "cargo") { |
|
717
|
|
|
$additional_query .= " AND (spotter_archive_output.airline_type = 'cargo')"; |
|
718
|
|
|
} |
|
719
|
|
|
if ($airline_type == "military") { |
|
720
|
|
|
$additional_query .= " AND (spotter_archive_output.airline_type = 'military')"; |
|
721
|
|
|
} |
|
722
|
|
|
} |
|
723
|
|
|
} |
|
724
|
|
|
|
|
725
|
|
|
if ($airport != "") { |
|
726
|
|
|
$airport = filter_var($airport,FILTER_SANITIZE_STRING); |
|
727
|
|
|
if (!is_string($airport)) { |
|
728
|
|
|
return array(); |
|
729
|
|
|
} else { |
|
730
|
|
|
$additional_query .= " AND ((spotter_archive_output.departure_airport_icao = '".$airport."') OR (spotter_archive_output.arrival_airport_icao = '".$airport."'))"; |
|
731
|
|
|
} |
|
732
|
|
|
} |
|
733
|
|
|
|
|
734
|
|
|
if ($airport_country != "") { |
|
735
|
|
|
$airport_country = filter_var($airport_country,FILTER_SANITIZE_STRING); |
|
736
|
|
|
if (!is_string($airport_country)) { |
|
737
|
|
|
return array(); |
|
738
|
|
|
} else { |
|
739
|
|
|
$additional_query .= " AND ((spotter_archive_output.departure_airport_country = '".$airport_country."') OR (spotter_archive_output.arrival_airport_country = '".$airport_country."'))"; |
|
740
|
|
|
} |
|
741
|
|
|
} |
|
742
|
|
|
|
|
743
|
|
|
if ($callsign != "") { |
|
744
|
|
|
$callsign = filter_var($callsign,FILTER_SANITIZE_STRING); |
|
745
|
|
|
if (!is_string($callsign)) { |
|
746
|
|
|
return array(); |
|
747
|
|
|
} else { |
|
748
|
|
|
$translate = $Translation->ident2icao($callsign); |
|
749
|
|
|
if ($translate != $callsign) { |
|
750
|
|
|
$additional_query .= " AND (spotter_archive_output.ident = :callsign OR spotter_archive_output.ident = :translate)"; |
|
751
|
|
|
$query_values = array_merge($query_values,array(':callsign' => $callsign,':translate' => $translate)); |
|
752
|
|
|
} else { |
|
753
|
|
|
$additional_query .= " AND (spotter_archive_output.ident = '".$callsign."')"; |
|
754
|
|
|
} |
|
755
|
|
|
} |
|
756
|
|
|
} |
|
757
|
|
|
|
|
758
|
|
|
if ($owner != "") { |
|
759
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
760
|
|
|
if (!is_string($owner)) { |
|
761
|
|
|
return array(); |
|
762
|
|
|
} else { |
|
763
|
|
|
$additional_query .= " AND (spotter_archive_output.owner_name = '".$owner."')"; |
|
764
|
|
|
} |
|
765
|
|
|
} |
|
766
|
|
|
|
|
767
|
|
|
if ($pilot_name != "") { |
|
768
|
|
|
$pilot_name = filter_var($pilot_name,FILTER_SANITIZE_STRING); |
|
769
|
|
|
if (!is_string($pilot_name)) { |
|
770
|
|
|
return array(); |
|
771
|
|
|
} else { |
|
772
|
|
|
$additional_query .= " AND (spotter_archive_output.pilot_name = '".$pilot_name."')"; |
|
773
|
|
|
} |
|
774
|
|
|
} |
|
775
|
|
|
|
|
776
|
|
|
if ($pilot_id != "") { |
|
777
|
|
|
$pilot_id = filter_var($pilot_id,FILTER_SANITIZE_NUMBER_INT); |
|
778
|
|
|
if (!is_string($pilot_id)) { |
|
779
|
|
|
return array(); |
|
780
|
|
|
} else { |
|
781
|
|
|
$additional_query .= " AND (spotter_archive_output.pilot_id = '".$pilot_id."')"; |
|
782
|
|
|
} |
|
783
|
|
|
} |
|
784
|
|
|
|
|
785
|
|
|
if ($departure_airport_route != "") { |
|
786
|
|
|
$departure_airport_route = filter_var($departure_airport_route,FILTER_SANITIZE_STRING); |
|
787
|
|
|
if (!is_string($departure_airport_route)) { |
|
788
|
|
|
return array(); |
|
789
|
|
|
} else { |
|
790
|
|
|
$additional_query .= " AND (spotter_archive_output.departure_airport_icao = '".$departure_airport_route."')"; |
|
791
|
|
|
} |
|
792
|
|
|
} |
|
793
|
|
|
|
|
794
|
|
|
if ($arrival_airport_route != "") { |
|
795
|
|
|
$arrival_airport_route = filter_var($arrival_airport_route,FILTER_SANITIZE_STRING); |
|
796
|
|
|
if (!is_string($arrival_airport_route)) { |
|
797
|
|
|
return array(); |
|
798
|
|
|
} else { |
|
799
|
|
|
$additional_query .= " AND (spotter_archive_output.arrival_airport_icao = '".$arrival_airport_route."')"; |
|
800
|
|
|
} |
|
801
|
|
|
} |
|
802
|
|
|
|
|
803
|
|
|
if ($altitude != "") { |
|
804
|
|
|
$altitude_array = explode(",", $altitude); |
|
805
|
|
|
|
|
806
|
|
|
$altitude_array[0] = filter_var($altitude_array[0],FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION); |
|
807
|
|
|
$altitude_array[1] = filter_var($altitude_array[1],FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION); |
|
808
|
|
|
|
|
809
|
|
|
|
|
810
|
|
|
if ($altitude_array[1] != "") { |
|
811
|
|
|
$altitude_array[0] = substr($altitude_array[0], 0, -2); |
|
812
|
|
|
$altitude_array[1] = substr($altitude_array[1], 0, -2); |
|
813
|
|
|
$additional_query .= " AND altitude BETWEEN '".$altitude_array[0]."' AND '".$altitude_array[1]."' "; |
|
814
|
|
|
} else { |
|
815
|
|
|
$altitude_array[0] = substr($altitude_array[0], 0, -2); |
|
816
|
|
|
$additional_query .= " AND altitude <= '".$altitude_array[0]."' "; |
|
817
|
|
|
} |
|
818
|
|
|
} |
|
819
|
|
|
|
|
820
|
|
|
if ($date_posted != "") { |
|
821
|
|
|
$date_array = explode(",", $date_posted); |
|
822
|
|
|
$date_array[0] = filter_var($date_array[0],FILTER_SANITIZE_STRING); |
|
823
|
|
|
$date_array[1] = filter_var($date_array[1],FILTER_SANITIZE_STRING); |
|
824
|
|
|
if ($globalTimezone != '') { |
|
825
|
|
|
date_default_timezone_set($globalTimezone); |
|
826
|
|
|
$datetime = new DateTime(); |
|
827
|
|
|
$offset = $datetime->format('P'); |
|
828
|
|
|
} else $offset = '+00:00'; |
|
829
|
|
|
if ($date_array[1] != "") { |
|
830
|
|
|
$date_array[0] = date("Y-m-d H:i:s", strtotime($date_array[0])); |
|
831
|
|
|
$date_array[1] = date("Y-m-d H:i:s", strtotime($date_array[1])); |
|
832
|
|
|
if ($globalDBdriver == 'mysql') { |
|
833
|
|
|
$additional_query .= " AND TIMESTAMP(CONVERT_TZ(spotter_archive_output.date,'+00:00', '".$offset."')) >= '".$date_array[0]."' AND TIMESTAMP(CONVERT_TZ(spotter_archive_output.date,'+00:00', '".$offset."')) <= '".$date_array[1]."' "; |
|
834
|
|
|
} else { |
|
835
|
|
|
$additional_query .= " AND spotter_archive_output.date::timestamp AT TIME ZONE INTERVAL ".$offset." >= CAST('".$date_array[0]."' AS TIMESTAMP) AND spotter_archive_output.date::timestamp AT TIME ZONE INTERVAL ".$offset." <= CAST('".$date_array[1]."' AS TIMESTAMP) "; |
|
836
|
|
|
} |
|
837
|
|
|
} else { |
|
838
|
|
|
$date_array[0] = date("Y-m-d H:i:s", strtotime($date_array[0])); |
|
839
|
|
|
if ($globalDBdriver == 'mysql') { |
|
840
|
|
|
$additional_query .= " AND TIMESTAMP(CONVERT_TZ(spotter_archive_output.date,'+00:00', '".$offset."')) >= '".$date_array[0]."' "; |
|
841
|
|
|
} else { |
|
842
|
|
|
$additional_query .= " AND spotter_archive_output.date::timestamp AT TIME ZONE INTERVAL ".$offset." >= CAST('".$date_array[0]."' AS TIMESTAMP) "; |
|
843
|
|
|
} |
|
844
|
|
|
} |
|
845
|
|
|
} |
|
846
|
|
|
if ($limit != "") { |
|
847
|
|
|
$limit_array = explode(",", $limit); |
|
848
|
|
|
$limit_array[0] = filter_var($limit_array[0],FILTER_SANITIZE_NUMBER_INT); |
|
849
|
|
|
$limit_array[1] = filter_var($limit_array[1],FILTER_SANITIZE_NUMBER_INT); |
|
850
|
|
|
if ($limit_array[0] >= 0 && $limit_array[1] >= 0) { |
|
851
|
|
|
//$limit_query = " LIMIT ".$limit_array[0].",".$limit_array[1]; |
|
852
|
|
|
$limit_query = " LIMIT ".$limit_array[1]." OFFSET ".$limit_array[0]; |
|
853
|
|
|
} |
|
854
|
|
|
} |
|
855
|
|
|
if ($origLat != "" && $origLon != "" && $dist != "") { |
|
856
|
|
|
$dist = number_format($dist*0.621371,2,'.',''); |
|
857
|
|
|
$query="SELECT spotter_archive_output.*, 3956 * 2 * ASIN(SQRT( POWER(SIN(($origLat - ABS(CAST(spotter_archive.latitude as double precision)))*pi()/180/2),2)+COS( $origLat *pi()/180)*COS(ABS(CAST(spotter_archive.latitude as double precision))*pi()/180)*POWER(SIN(($origLon-CAST(spotter_archive.longitude as double precision))*pi()/180/2),2))) as distance |
|
858
|
|
|
FROM spotter_archive_output, spotter_archive WHERE spotter_output_archive.flightaware_id = spotter_archive.flightaware_id AND spotter_output.ident <> '' ".$additional_query."AND CAST(spotter_archive.longitude as double precision) between ($origLon-$dist/ABS(cos(radians($origLat))*69)) and ($origLon+$dist/ABS(cos(radians($origLat))*69)) and CAST(spotter_archive.latitude as double precision) between ($origLat-($dist/69)) and ($origLat+($dist/69)) |
|
859
|
|
|
AND (3956 * 2 * ASIN(SQRT( POWER(SIN(($origLat - ABS(CAST(spotter_archive.latitude as double precision)))*pi()/180/2),2)+COS( $origLat *pi()/180)*COS(ABS(CAST(spotter_archive.latitude as double precision))*pi()/180)*POWER(SIN(($origLon-CAST(spotter_archive.longitude as double precision))*pi()/180/2),2)))) < $dist".$filter_query." ORDER BY distance"; |
|
860
|
|
|
} else { |
|
861
|
|
|
if ($sort != "") { |
|
862
|
|
|
$search_orderby_array = $Spotter->getOrderBy(); |
|
863
|
|
|
$orderby_query = $search_orderby_array[$sort]['sql']; |
|
864
|
|
|
} else { |
|
865
|
|
|
$orderby_query = " ORDER BY spotter_archive_output.date DESC"; |
|
866
|
|
|
} |
|
867
|
|
|
if ($includegeodata == "true") { |
|
868
|
|
|
$additional_query .= " AND (spotter_archive_output.waypoints <> '')"; |
|
869
|
|
|
} |
|
870
|
|
|
|
|
871
|
|
|
$query = "SELECT spotter_archive_output.* FROM spotter_archive_output |
|
872
|
|
|
WHERE spotter_archive_output.ident <> '' |
|
873
|
|
|
".$additional_query." |
|
874
|
|
|
".$filter_query.$orderby_query; |
|
875
|
|
|
} |
|
876
|
|
|
$spotter_array = $Spotter->getDataFromDB($query, $query_values,$limit_query); |
|
877
|
|
|
return $spotter_array; |
|
878
|
|
|
} |
|
879
|
|
|
|
|
880
|
|
|
public function deleteSpotterArchiveData() { |
|
881
|
|
|
global $globalArchiveKeepMonths, $globalDBdriver; |
|
882
|
|
|
date_default_timezone_set('UTC'); |
|
883
|
|
|
if ($globalDBdriver == 'mysql') { |
|
884
|
|
|
$query = 'DELETE FROM spotter_archive_output WHERE spotter_archive_output.date < DATE_SUB(UTC_TIMESTAMP(), INTERVAL '.$globalArchiveKeepMonths.' MONTH) LIMIT 10000'; |
|
885
|
|
|
} else { |
|
886
|
|
|
$query = "DELETE FROM spotter_archive_output WHERE spotter_archive_id IN (SELECT spotter_archive_id FROM spotter_archive_output WHERE spotter_archive_output.date < CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalArchiveKeepMonths." MONTH' LIMIT 10000)"; |
|
887
|
|
|
} |
|
888
|
|
|
try { |
|
889
|
|
|
$sth = $this->db->prepare($query); |
|
890
|
|
|
$sth->execute(); |
|
891
|
|
|
} catch(PDOException $e) { |
|
892
|
|
|
return "error"; |
|
893
|
|
|
} |
|
894
|
|
|
} |
|
895
|
|
|
|
|
896
|
|
|
/** |
|
897
|
|
|
* Gets all the spotter information based on the callsign |
|
898
|
|
|
* |
|
899
|
|
|
* @return Array the spotter information |
|
900
|
|
|
* |
|
901
|
|
|
*/ |
|
902
|
|
|
public function getSpotterDataByIdent($ident = '', $limit = '', $sort = '') { |
|
903
|
|
|
$global_query = "SELECT spotter_archive_output.* FROM spotter_archive_output"; |
|
904
|
|
|
|
|
905
|
|
|
date_default_timezone_set('UTC'); |
|
906
|
|
|
$Spotter = new Spotter($this->db); |
|
907
|
|
|
|
|
908
|
|
|
$query_values = array(); |
|
909
|
|
|
$limit_query = ''; |
|
910
|
|
|
$additional_query = ''; |
|
911
|
|
|
|
|
912
|
|
|
if ($ident != "") { |
|
913
|
|
|
if (!is_string($ident)) { |
|
914
|
|
|
return false; |
|
|
|
|
|
|
915
|
|
|
} else { |
|
916
|
|
|
$additional_query = " AND spotter_archive_output.ident = :ident"; |
|
917
|
|
|
$query_values = array(':ident' => $ident); |
|
918
|
|
|
} |
|
919
|
|
|
} |
|
920
|
|
|
|
|
921
|
|
|
if ($limit != "") { |
|
922
|
|
|
$limit_array = explode(",", $limit); |
|
923
|
|
|
|
|
924
|
|
|
$limit_array[0] = filter_var($limit_array[0],FILTER_SANITIZE_NUMBER_INT); |
|
925
|
|
|
$limit_array[1] = filter_var($limit_array[1],FILTER_SANITIZE_NUMBER_INT); |
|
926
|
|
|
|
|
927
|
|
|
if ($limit_array[0] >= 0 && $limit_array[1] >= 0) { |
|
928
|
|
|
//$limit_query = " LIMIT ".$limit_array[0].",".$limit_array[1]; |
|
929
|
|
|
$limit_query = " LIMIT ".$limit_array[1]." OFFSET ".$limit_array[0]; |
|
930
|
|
|
} |
|
931
|
|
|
} |
|
932
|
|
|
|
|
933
|
|
|
if ($sort != "") { |
|
934
|
|
|
$search_orderby_array = $Spotter->getOrderBy(); |
|
935
|
|
|
$orderby_query = $search_orderby_array[$sort]['sql']; |
|
936
|
|
|
} else { |
|
937
|
|
|
$orderby_query = " ORDER BY spotter_archive_output.date DESC"; |
|
938
|
|
|
} |
|
939
|
|
|
|
|
940
|
|
|
$query = $global_query." WHERE spotter_archive_output.ident <> '' ".$additional_query." ".$orderby_query; |
|
941
|
|
|
|
|
942
|
|
|
$spotter_array = $Spotter->getDataFromDB($query, $query_values, $limit_query); |
|
943
|
|
|
|
|
944
|
|
|
return $spotter_array; |
|
945
|
|
|
} |
|
946
|
|
|
|
|
947
|
|
|
|
|
948
|
|
|
/** |
|
949
|
|
|
* Gets all the spotter information based on the owner |
|
950
|
|
|
* |
|
951
|
|
|
* @return Array the spotter information |
|
952
|
|
|
* |
|
953
|
|
|
*/ |
|
954
|
|
|
public function getSpotterDataByOwner($owner = '', $limit = '', $sort = '', $filter = array()) { |
|
955
|
|
|
$global_query = "SELECT spotter_archive_output.* FROM spotter_archive_output"; |
|
956
|
|
|
|
|
957
|
|
|
date_default_timezone_set('UTC'); |
|
958
|
|
|
$Spotter = new Spotter($this->db); |
|
959
|
|
|
|
|
960
|
|
|
$query_values = array(); |
|
961
|
|
|
$limit_query = ''; |
|
962
|
|
|
$additional_query = ''; |
|
963
|
|
|
$filter_query = $this->getFilter($filter,true,true); |
|
964
|
|
|
|
|
965
|
|
|
if ($owner != "") { |
|
966
|
|
|
if (!is_string($owner)) { |
|
967
|
|
|
return false; |
|
|
|
|
|
|
968
|
|
|
} else { |
|
969
|
|
|
$additional_query = " AND (spotter_archive_output.owner_name = :owner)"; |
|
970
|
|
|
$query_values = array(':owner' => $owner); |
|
971
|
|
|
} |
|
972
|
|
|
} |
|
973
|
|
|
|
|
974
|
|
|
if ($limit != "") { |
|
975
|
|
|
$limit_array = explode(",", $limit); |
|
976
|
|
|
|
|
977
|
|
|
$limit_array[0] = filter_var($limit_array[0],FILTER_SANITIZE_NUMBER_INT); |
|
978
|
|
|
$limit_array[1] = filter_var($limit_array[1],FILTER_SANITIZE_NUMBER_INT); |
|
979
|
|
|
|
|
980
|
|
|
if ($limit_array[0] >= 0 && $limit_array[1] >= 0 && $limit_array[0] != '' && $limit_array[1] != '') { |
|
981
|
|
|
//$limit_query = " LIMIT ".$limit_array[0].",".$limit_array[1]; |
|
982
|
|
|
$limit_query = " LIMIT ".$limit_array[1]." OFFSET ".$limit_array[0]; |
|
983
|
|
|
} |
|
984
|
|
|
} |
|
985
|
|
|
|
|
986
|
|
|
if ($sort != "") { |
|
987
|
|
|
$search_orderby_array = $Spotter->getOrderBy(); |
|
988
|
|
|
$orderby_query = $search_orderby_array[$sort]['sql']; |
|
989
|
|
|
} else { |
|
990
|
|
|
$orderby_query = " ORDER BY spotter_archive_output.date DESC"; |
|
991
|
|
|
} |
|
992
|
|
|
|
|
993
|
|
|
$query = $global_query.$filter_query." spotter_archive_output.owner_name <> '' ".$additional_query." ".$orderby_query; |
|
994
|
|
|
|
|
995
|
|
|
$spotter_array = $Spotter->getDataFromDB($query, $query_values, $limit_query); |
|
996
|
|
|
|
|
997
|
|
|
return $spotter_array; |
|
998
|
|
|
} |
|
999
|
|
|
|
|
1000
|
|
|
/** |
|
1001
|
|
|
* Gets all the spotter information based on the pilot |
|
1002
|
|
|
* |
|
1003
|
|
|
* @return Array the spotter information |
|
1004
|
|
|
* |
|
1005
|
|
|
*/ |
|
1006
|
|
|
public function getSpotterDataByPilot($pilot = '', $limit = '', $sort = '', $filter = array()) { |
|
1007
|
|
|
$global_query = "SELECT spotter_archive_output.* FROM spotter_archive_output"; |
|
1008
|
|
|
|
|
1009
|
|
|
date_default_timezone_set('UTC'); |
|
1010
|
|
|
$Spotter = new Spotter($this->db); |
|
1011
|
|
|
|
|
1012
|
|
|
$query_values = array(); |
|
1013
|
|
|
$limit_query = ''; |
|
1014
|
|
|
$additional_query = ''; |
|
1015
|
|
|
$filter_query = $this->getFilter($filter,true,true); |
|
1016
|
|
|
|
|
1017
|
|
|
if ($pilot != "") { |
|
1018
|
|
|
$additional_query = " AND (spotter_archive_output.pilot_id = :pilot OR spotter_archive_output.pilot_name = :pilot)"; |
|
1019
|
|
|
$query_values = array(':pilot' => $pilot); |
|
1020
|
|
|
} |
|
1021
|
|
|
|
|
1022
|
|
|
if ($limit != "") { |
|
1023
|
|
|
$limit_array = explode(",", $limit); |
|
1024
|
|
|
|
|
1025
|
|
|
$limit_array[0] = filter_var($limit_array[0],FILTER_SANITIZE_NUMBER_INT); |
|
1026
|
|
|
$limit_array[1] = filter_var($limit_array[1],FILTER_SANITIZE_NUMBER_INT); |
|
1027
|
|
|
|
|
1028
|
|
|
if ($limit_array[0] >= 0 && $limit_array[1] >= 0) { |
|
1029
|
|
|
//$limit_query = " LIMIT ".$limit_array[0].",".$limit_array[1]; |
|
1030
|
|
|
$limit_query = " LIMIT ".$limit_array[1]." OFFSET ".$limit_array[0]; |
|
1031
|
|
|
} |
|
1032
|
|
|
} |
|
1033
|
|
|
|
|
1034
|
|
|
if ($sort != "") { |
|
1035
|
|
|
$search_orderby_array = $Spotter->getOrderBy(); |
|
1036
|
|
|
$orderby_query = $search_orderby_array[$sort]['sql']; |
|
1037
|
|
|
} else { |
|
1038
|
|
|
$orderby_query = " ORDER BY spotter_archive_output.date DESC"; |
|
1039
|
|
|
} |
|
1040
|
|
|
|
|
1041
|
|
|
$query = $global_query.$filter_query." spotter_archive_output.pilot_name <> '' ".$additional_query." ".$orderby_query; |
|
1042
|
|
|
|
|
1043
|
|
|
$spotter_array = $Spotter->getDataFromDB($query, $query_values, $limit_query); |
|
1044
|
|
|
|
|
1045
|
|
|
return $spotter_array; |
|
1046
|
|
|
} |
|
1047
|
|
|
|
|
1048
|
|
|
/** |
|
1049
|
|
|
* Gets all number of flight over countries |
|
1050
|
|
|
* |
|
1051
|
|
|
* @return Array the airline country list |
|
1052
|
|
|
* |
|
1053
|
|
|
*/ |
|
1054
|
|
|
public function countAllFlightOverCountries($limit = true,$olderthanmonths = 0,$sincedate = '') { |
|
1055
|
|
|
global $globalDBdriver; |
|
1056
|
|
|
/* |
|
1057
|
|
|
$query = "SELECT c.name, c.iso3, c.iso2, count(c.name) as nb |
|
1058
|
|
|
FROM countries c, spotter_archive s |
|
1059
|
|
|
WHERE Within(GeomFromText(CONCAT('POINT(',s.longitude,' ',s.latitude,')')), ogc_geom) "; |
|
1060
|
|
|
*/ |
|
1061
|
|
|
$query = "SELECT c.name, c.iso3, c.iso2, count(c.name) as nb |
|
1062
|
|
|
FROM countries c, spotter_archive s |
|
1063
|
|
|
WHERE c.iso2 = s.over_country "; |
|
1064
|
|
|
if ($olderthanmonths > 0) { |
|
1065
|
|
|
if ($globalDBdriver == 'mysql') { |
|
1066
|
|
|
$query .= 'AND date < DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$olderthanmonths.' MONTH) '; |
|
1067
|
|
|
} else { |
|
1068
|
|
|
$query .= "AND date < CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$olderthanmonths." MONTHS'"; |
|
1069
|
|
|
} |
|
1070
|
|
|
} |
|
1071
|
|
|
if ($sincedate != '') $query .= "AND date > '".$sincedate."' "; |
|
1072
|
|
|
$query .= "GROUP BY c.name, c.iso3, c.iso2 ORDER BY nb DESC"; |
|
1073
|
|
|
if ($limit) $query .= " LIMIT 0,10"; |
|
1074
|
|
|
|
|
1075
|
|
|
|
|
1076
|
|
|
$sth = $this->db->prepare($query); |
|
1077
|
|
|
$sth->execute(); |
|
1078
|
|
|
|
|
1079
|
|
|
$flight_array = array(); |
|
1080
|
|
|
$temp_array = array(); |
|
1081
|
|
|
|
|
1082
|
|
|
while($row = $sth->fetch(PDO::FETCH_ASSOC)) { |
|
1083
|
|
|
$temp_array['flight_count'] = $row['nb']; |
|
1084
|
|
|
$temp_array['flight_country'] = $row['name']; |
|
1085
|
|
|
$temp_array['flight_country_iso3'] = $row['iso3']; |
|
1086
|
|
|
$temp_array['flight_country_iso2'] = $row['iso2']; |
|
1087
|
|
|
$flight_array[] = $temp_array; |
|
1088
|
|
|
} |
|
1089
|
|
|
return $flight_array; |
|
1090
|
|
|
} |
|
1091
|
|
|
|
|
1092
|
|
|
/** |
|
1093
|
|
|
* Gets all number of flight over countries |
|
1094
|
|
|
* |
|
1095
|
|
|
* @return Array the airline country list |
|
1096
|
|
|
* |
|
1097
|
|
|
*/ |
|
1098
|
|
|
public function countAllFlightOverCountriesByAirlines($limit = true,$olderthanmonths = 0,$sincedate = '') { |
|
1099
|
|
|
global $globalDBdriver; |
|
1100
|
|
|
/* |
|
1101
|
|
|
$query = "SELECT c.name, c.iso3, c.iso2, count(c.name) as nb |
|
1102
|
|
|
FROM countries c, spotter_archive s |
|
1103
|
|
|
WHERE Within(GeomFromText(CONCAT('POINT(',s.longitude,' ',s.latitude,')')), ogc_geom) "; |
|
1104
|
|
|
*/ |
|
1105
|
|
|
$query = "SELECT o.airline_icao,c.name, c.iso3, c.iso2, count(c.name) as nb |
|
1106
|
|
|
FROM countries c, spotter_archive s, spotter_output o |
|
1107
|
|
|
WHERE c.iso2 = s.over_country AND o.airline_icao <> '' AND o.flightaware_id = s.flightaware_id "; |
|
1108
|
|
|
if ($olderthanmonths > 0) { |
|
1109
|
|
|
if ($globalDBdriver == 'mysql') { |
|
1110
|
|
|
$query .= 'AND s.date < DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$olderthanmonths.' MONTH) '; |
|
1111
|
|
|
} else { |
|
1112
|
|
|
$query .= "AND s.date < CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$olderthanmonths." MONTHS'"; |
|
1113
|
|
|
} |
|
1114
|
|
|
} |
|
1115
|
|
|
if ($sincedate != '') $query .= "AND s.date > '".$sincedate."' "; |
|
1116
|
|
|
$query .= "GROUP BY o.airline_icao,c.name, c.iso3, c.iso2 ORDER BY nb DESC"; |
|
1117
|
|
|
if ($limit) $query .= " LIMIT 0,10"; |
|
1118
|
|
|
|
|
1119
|
|
|
|
|
1120
|
|
|
$sth = $this->db->prepare($query); |
|
1121
|
|
|
$sth->execute(); |
|
1122
|
|
|
|
|
1123
|
|
|
$flight_array = array(); |
|
1124
|
|
|
$temp_array = array(); |
|
1125
|
|
|
|
|
1126
|
|
|
while($row = $sth->fetch(PDO::FETCH_ASSOC)) { |
|
1127
|
|
|
$temp_array['airline_icao'] = $row['airline_icao']; |
|
1128
|
|
|
$temp_array['flight_count'] = $row['nb']; |
|
1129
|
|
|
$temp_array['flight_country'] = $row['name']; |
|
1130
|
|
|
$temp_array['flight_country_iso3'] = $row['iso3']; |
|
1131
|
|
|
$temp_array['flight_country_iso2'] = $row['iso2']; |
|
1132
|
|
|
$flight_array[] = $temp_array; |
|
1133
|
|
|
} |
|
1134
|
|
|
return $flight_array; |
|
1135
|
|
|
} |
|
1136
|
|
|
|
|
1137
|
|
|
/** |
|
1138
|
|
|
* Gets all aircraft types that have flown over by owner |
|
1139
|
|
|
* |
|
1140
|
|
|
* @return Array the aircraft list |
|
1141
|
|
|
* |
|
1142
|
|
|
*/ |
|
1143
|
|
|
public function countAllAircraftTypesByOwner($owner,$filters = array()) |
|
1144
|
|
|
{ |
|
1145
|
|
|
global $globalDBdriver; |
|
1146
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1147
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
1148
|
|
|
$query = "SELECT DISTINCT spotter_archive_output.aircraft_icao, COUNT(spotter_archive_output.aircraft_icao) AS aircraft_icao_count, spotter_archive_output.aircraft_name, spotter_archive_output.aircraft_manufacturer |
|
1149
|
|
|
FROM spotter_archive_output".$filter_query." spotter_archive_output.owner_name = :owner"; |
|
1150
|
|
|
$query_values = array(); |
|
1151
|
|
|
$query .= " GROUP BY spotter_archive_output.aircraft_name, spotter_archive_output.aircraft_manufacturer, spotter_archive_output.aircraft_icao |
|
1152
|
|
|
ORDER BY aircraft_icao_count DESC"; |
|
1153
|
|
|
$query_values = array_merge($query_values,array(':owner' => $owner)); |
|
1154
|
|
|
$sth = $this->db->prepare($query); |
|
1155
|
|
|
$sth->execute($query_values); |
|
1156
|
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC); |
|
1157
|
|
|
} |
|
1158
|
|
|
|
|
1159
|
|
|
/** |
|
1160
|
|
|
* Gets all airlines by owner |
|
1161
|
|
|
* |
|
1162
|
|
|
* @return Array the airline list |
|
1163
|
|
|
* |
|
1164
|
|
|
*/ |
|
1165
|
|
|
public function countAllAirlinesByOwner($owner,$filters = array()) |
|
1166
|
|
|
{ |
|
1167
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
1168
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1169
|
|
|
$query = "SELECT DISTINCT spotter_archive_output.airline_name, spotter_archive_output.airline_icao, spotter_archive_output.airline_country, COUNT(spotter_archive_output.airline_name) AS airline_count |
|
1170
|
|
|
FROM spotter_archive_output".$filter_query." spotter_archive_output.owner_name = :owner |
|
1171
|
|
|
GROUP BY spotter_archive_output.airline_icao, spotter_archive_output.airline_name, spotter_archive_output.airline_country |
|
1172
|
|
|
ORDER BY airline_count DESC"; |
|
1173
|
|
|
|
|
1174
|
|
|
$sth = $this->db->prepare($query); |
|
1175
|
|
|
$sth->execute(array(':owner' => $owner)); |
|
1176
|
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC); |
|
1177
|
|
|
} |
|
1178
|
|
|
|
|
1179
|
|
|
/** |
|
1180
|
|
|
* Gets all arrival airports by country of the airplanes that have flown over based on a owner |
|
1181
|
|
|
* |
|
1182
|
|
|
* @return Array the airport list |
|
1183
|
|
|
* |
|
1184
|
|
|
*/ |
|
1185
|
|
|
public function countAllArrivalAirportCountriesByOwner($owner, $filters = array()) |
|
1186
|
|
|
{ |
|
1187
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1188
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
1189
|
|
|
$query = "SELECT DISTINCT spotter_archive_output.arrival_airport_country, COUNT(spotter_archive_output.arrival_airport_country) AS airport_arrival_country_count, countries.iso3 AS airport_arrival_country_iso3 |
|
1190
|
|
|
FROM countries, spotter_archive_output".$filter_query." spotter_archive_output.arrival_airport_country <> '' AND spotter_archive_output.owner_name = :owner AND countries.name = spotter_archive_output.arrival_airport_country |
|
1191
|
|
|
GROUP BY spotter_archive_output.arrival_airport_country, countries.iso3 |
|
1192
|
|
|
ORDER BY airport_arrival_country_count DESC"; |
|
1193
|
|
|
$sth = $this->db->prepare($query); |
|
1194
|
|
|
$sth->execute(array(':owner' => $owner)); |
|
1195
|
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC); |
|
1196
|
|
|
} |
|
1197
|
|
|
|
|
1198
|
|
|
/** |
|
1199
|
|
|
* Gets all arrival airports of the airplanes that have flown over based on a owner |
|
1200
|
|
|
* |
|
1201
|
|
|
* @return Array the airport list |
|
1202
|
|
|
* |
|
1203
|
|
|
*/ |
|
1204
|
|
|
public function countAllArrivalAirportsByOwner($owner,$filters = array()) |
|
1205
|
|
|
{ |
|
1206
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1207
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
1208
|
|
|
$query = "SELECT DISTINCT spotter_archive_output.arrival_airport_icao, COUNT(spotter_archive_output.arrival_airport_icao) AS airport_arrival_icao_count, spotter_archive_output.arrival_airport_name, spotter_archive_output.arrival_airport_city, spotter_archive_output.arrival_airport_country |
|
1209
|
|
|
FROM spotter_archive_output".$filter_query." spotter_archive_output.arrival_airport_name <> '' AND spotter_archive_output.arrival_airport_icao <> 'NA' AND spotter_archive_output.arrival_airport_icao <> '' AND spotter_archive_output.owner_name = :owner |
|
1210
|
|
|
GROUP BY spotter_archive_output.arrival_airport_icao, spotter_archive_output.arrival_airport_name, spotter_archive_output.arrival_airport_city, spotter_archive_output.arrival_airport_country |
|
1211
|
|
|
ORDER BY airport_arrival_icao_count DESC"; |
|
1212
|
|
|
$sth = $this->db->prepare($query); |
|
1213
|
|
|
$sth->execute(array(':owner' => $owner)); |
|
1214
|
|
|
$airport_array = array(); |
|
1215
|
|
|
$temp_array = array(); |
|
1216
|
|
|
while($row = $sth->fetch(PDO::FETCH_ASSOC)) |
|
1217
|
|
|
{ |
|
1218
|
|
|
$temp_array['airport_arrival_icao'] = $row['arrival_airport_icao']; |
|
1219
|
|
|
$temp_array['airport_arrival_icao_count'] = $row['airport_arrival_icao_count']; |
|
1220
|
|
|
$temp_array['airport_arrival_name'] = $row['arrival_airport_name']; |
|
1221
|
|
|
$temp_array['airport_arrival_city'] = $row['arrival_airport_city']; |
|
1222
|
|
|
$temp_array['airport_arrival_country'] = $row['arrival_airport_country']; |
|
1223
|
|
|
$airport_array[] = $temp_array; |
|
1224
|
|
|
} |
|
1225
|
|
|
return $airport_array; |
|
1226
|
|
|
} |
|
1227
|
|
|
|
|
1228
|
|
|
/** |
|
1229
|
|
|
* Gets all departure airports by country of the airplanes that have flown over based on owner |
|
1230
|
|
|
* |
|
1231
|
|
|
* @return Array the airport list |
|
1232
|
|
|
* |
|
1233
|
|
|
*/ |
|
1234
|
|
|
public function countAllDepartureAirportCountriesByOwner($owner,$filters = array()) |
|
1235
|
|
|
{ |
|
1236
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1237
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
1238
|
|
|
$query = "SELECT DISTINCT spotter_archive_output.departure_airport_country, COUNT(spotter_archive_output.departure_airport_country) AS airport_departure_country_count, countries.iso3 AS airport_departure_country_iso3 |
|
1239
|
|
|
FROM spotter_archive_output,countries".$filter_query." spotter_archive_output.departure_airport_country <> '' AND spotter_archive_output.owner_name = :owner AND countries.name = spotter_archive_output.departure_airport_country |
|
1240
|
|
|
GROUP BY spotter_archive_output.departure_airport_country, countries.iso3 |
|
1241
|
|
|
ORDER BY airport_departure_country_count DESC"; |
|
1242
|
|
|
$sth = $this->db->prepare($query); |
|
1243
|
|
|
$sth->execute(array(':owner' => $owner)); |
|
1244
|
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC); |
|
1245
|
|
|
} |
|
1246
|
|
|
|
|
1247
|
|
|
/** |
|
1248
|
|
|
* Gets all departure airports of the airplanes that have flown over based on a owner |
|
1249
|
|
|
* |
|
1250
|
|
|
* @return Array the airport list |
|
1251
|
|
|
* |
|
1252
|
|
|
*/ |
|
1253
|
|
|
public function countAllDepartureAirportsByOwner($owner,$filters = array()) |
|
1254
|
|
|
{ |
|
1255
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1256
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
1257
|
|
|
$query = "SELECT DISTINCT spotter_archive_output.departure_airport_icao, COUNT(spotter_archive_output.departure_airport_icao) AS airport_departure_icao_count, spotter_archive_output.departure_airport_name, spotter_archive_output.departure_airport_city, spotter_archive_output.departure_airport_country, airport.latitude, airport.longitude |
|
1258
|
|
|
FROM spotter_archive_output,airport".$filter_query." spotter_archive_output.departure_airport_name <> '' AND spotter_archive_output.departure_airport_icao <> 'NA' AND spotter_archive_output.departure_airport_icao <> '' AND spotter_archive_output.owner_name = :owner AND airport.icao = spotter_archive_output.departure_airport_icao |
|
1259
|
|
|
GROUP BY spotter_archive_output.departure_airport_icao, spotter_archive_output.departure_airport_name, spotter_archive_output.departure_airport_city, spotter_archive_output.departure_airport_country, airport.latitude, airport.longitude |
|
1260
|
|
|
ORDER BY airport_departure_icao_count DESC"; |
|
1261
|
|
|
$sth = $this->db->prepare($query); |
|
1262
|
|
|
$sth->execute(array(':owner' => $owner)); |
|
1263
|
|
|
$airport_array = array(); |
|
1264
|
|
|
$temp_array = array(); |
|
1265
|
|
|
while($row = $sth->fetch(PDO::FETCH_ASSOC)) |
|
1266
|
|
|
{ |
|
1267
|
|
|
$temp_array['airport_departure_icao'] = $row['departure_airport_icao']; |
|
1268
|
|
|
$temp_array['airport_departure_icao_count'] = $row['airport_departure_icao_count']; |
|
1269
|
|
|
$temp_array['airport_departure_name'] = $row['departure_airport_name']; |
|
1270
|
|
|
$temp_array['airport_departure_city'] = $row['departure_airport_city']; |
|
1271
|
|
|
$temp_array['airport_departure_country'] = $row['departure_airport_country']; |
|
1272
|
|
|
$temp_array['airport_departure_latitude'] = $row['latitude']; |
|
1273
|
|
|
$temp_array['airport_departure_longitude'] = $row['longitude']; |
|
1274
|
|
|
$airport_array[] = $temp_array; |
|
1275
|
|
|
} |
|
1276
|
|
|
return $airport_array; |
|
1277
|
|
|
} |
|
1278
|
|
|
|
|
1279
|
|
|
/** |
|
1280
|
|
|
* Gets all aircraft manufacturer that have flown over by owner |
|
1281
|
|
|
* |
|
1282
|
|
|
* @return Array the aircraft manufacturer list |
|
1283
|
|
|
* |
|
1284
|
|
|
*/ |
|
1285
|
|
|
public function countAllAircraftManufacturerByOwner($owner,$filters = array()) |
|
1286
|
|
|
{ |
|
1287
|
|
|
global $globalDBdriver; |
|
1288
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1289
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
1290
|
|
|
$query = "SELECT DISTINCT spotter_archive_output.aircraft_manufacturer, COUNT(spotter_archive_output.aircraft_manufacturer) AS aircraft_manufacturer_count |
|
1291
|
|
|
FROM spotter_archive_output".$filter_query." spotter_archive_output.aircraft_manufacturer <> '' AND spotter_archive_output.owner_name = :owner"; |
|
1292
|
|
|
$query_values = array(); |
|
1293
|
|
|
$query_values = array_merge($query_values,array(':owner' => $owner)); |
|
1294
|
|
|
$query .= " GROUP BY spotter_archive_output.aircraft_manufacturer |
|
1295
|
|
|
ORDER BY aircraft_manufacturer_count DESC"; |
|
1296
|
|
|
$sth = $this->db->prepare($query); |
|
1297
|
|
|
$sth->execute($query_values); |
|
1298
|
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC); |
|
1299
|
|
|
} |
|
1300
|
|
|
|
|
1301
|
|
|
/** |
|
1302
|
|
|
* Gets all aircraft registration that have flown over by owner |
|
1303
|
|
|
* |
|
1304
|
|
|
* @return Array the aircraft list |
|
1305
|
|
|
* |
|
1306
|
|
|
*/ |
|
1307
|
|
|
public function countAllAircraftRegistrationByOwner($owner,$filters = array()) |
|
1308
|
|
|
{ |
|
1309
|
|
|
global $globalDBdriver; |
|
1310
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1311
|
|
|
$Image = new Image($this->db); |
|
1312
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
1313
|
|
|
$query = "SELECT DISTINCT spotter_archive_output.aircraft_icao, COUNT(spotter_archive_output.registration) AS registration_count, spotter_archive_output.aircraft_name, spotter_archive_output.aircraft_manufacturer, spotter_archive_output.registration, spotter_archive_output.airline_name |
|
1314
|
|
|
FROM spotter_archive_output".$filter_query." spotter_archive_output.registration <> '' AND spotter_archive_output.owner_name = :owner"; |
|
1315
|
|
|
$query_values = array(); |
|
1316
|
|
|
$query_values = array_merge($query_values,array(':owner' => $owner)); |
|
1317
|
|
|
$query .= " GROUP BY spotter_archive_output.registration,spotter_archive_output.aircraft_icao, spotter_archive_output.aircraft_name, spotter_archive_output.aircraft_manufacturer, spotter_archive_output.airline_name |
|
1318
|
|
|
ORDER BY registration_count DESC"; |
|
1319
|
|
|
$sth = $this->db->prepare($query); |
|
1320
|
|
|
$sth->execute($query_values); |
|
1321
|
|
|
$aircraft_array = array(); |
|
1322
|
|
|
$temp_array = array(); |
|
1323
|
|
|
while($row = $sth->fetch(PDO::FETCH_ASSOC)) |
|
1324
|
|
|
{ |
|
1325
|
|
|
$temp_array['aircraft_icao'] = $row['aircraft_icao']; |
|
1326
|
|
|
$temp_array['aircraft_name'] = $row['aircraft_name']; |
|
1327
|
|
|
$temp_array['aircraft_manufacturer'] = $row['aircraft_manufacturer']; |
|
1328
|
|
|
$temp_array['registration'] = $row['registration']; |
|
1329
|
|
|
$temp_array['airline_name'] = $row['airline_name']; |
|
1330
|
|
|
$temp_array['image_thumbnail'] = ""; |
|
1331
|
|
|
if($row['registration'] != "") |
|
1332
|
|
|
{ |
|
1333
|
|
|
$image_array = $Image->getSpotterImage($row['registration']); |
|
1334
|
|
|
if (isset($image_array[0]['image_thumbnail'])) $temp_array['image_thumbnail'] = $image_array[0]['image_thumbnail']; |
|
1335
|
|
|
else $temp_array['image_thumbnail'] = ''; |
|
1336
|
|
|
} |
|
1337
|
|
|
$temp_array['registration_count'] = $row['registration_count']; |
|
1338
|
|
|
$aircraft_array[] = $temp_array; |
|
1339
|
|
|
} |
|
1340
|
|
|
return $aircraft_array; |
|
1341
|
|
|
} |
|
1342
|
|
|
|
|
1343
|
|
|
/** |
|
1344
|
|
|
* Gets all route combinations based on an owner |
|
1345
|
|
|
* |
|
1346
|
|
|
* @return Array the route list |
|
1347
|
|
|
* |
|
1348
|
|
|
*/ |
|
1349
|
|
|
public function countAllRoutesByOwner($owner,$filters = array()) |
|
1350
|
|
|
{ |
|
1351
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1352
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
1353
|
|
|
$query = "SELECT DISTINCT concat(spotter_archive_output.departure_airport_icao, ' - ', spotter_archive_output.arrival_airport_icao) AS route, count(concat(spotter_archive_output.departure_airport_icao, ' - ', spotter_archive_output.arrival_airport_icao)) AS route_count, spotter_archive_output.departure_airport_icao, spotter_archive_output.departure_airport_name AS airport_departure_name, spotter_archive_output.departure_airport_city AS airport_departure_city, spotter_archive_output.departure_airport_country AS airport_departure_country, spotter_archive_output.arrival_airport_icao, spotter_archive_output.arrival_airport_name AS airport_arrival_name, spotter_archive_output.arrival_airport_city AS airport_arrival_city, spotter_archive_output.arrival_airport_country AS airport_arrival_country |
|
1354
|
|
|
FROM spotter_archive_output".$filter_query." spotter_archive_output.ident <> '' AND spotter_archive_output.owner_name = :owner |
|
1355
|
|
|
GROUP BY route, spotter_archive_output.departure_airport_icao, spotter_archive_output.departure_airport_name, spotter_archive_output.departure_airport_city, spotter_archive_output.departure_airport_country, spotter_archive_output.arrival_airport_icao, spotter_archive_output.arrival_airport_name, spotter_archive_output.arrival_airport_city, spotter_archive_output.arrival_airport_country |
|
1356
|
|
|
ORDER BY route_count DESC"; |
|
1357
|
|
|
$sth = $this->db->prepare($query); |
|
1358
|
|
|
$sth->execute(array(':owner' => $owner)); |
|
1359
|
|
|
$routes_array = array(); |
|
1360
|
|
|
$temp_array = array(); |
|
1361
|
|
|
while($row = $sth->fetch(PDO::FETCH_ASSOC)) |
|
1362
|
|
|
{ |
|
1363
|
|
|
$temp_array['route_count'] = $row['route_count']; |
|
1364
|
|
|
$temp_array['airport_departure_icao'] = $row['departure_airport_icao']; |
|
1365
|
|
|
$temp_array['airport_departure_name'] = $row['airport_departure_name']; |
|
1366
|
|
|
$temp_array['airport_departure_city'] = $row['airport_departure_city']; |
|
1367
|
|
|
$temp_array['airport_departure_country'] = $row['airport_departure_country']; |
|
1368
|
|
|
$temp_array['airport_arrival_icao'] = $row['arrival_airport_icao']; |
|
1369
|
|
|
$temp_array['airport_arrival_name'] = $row['airport_arrival_name']; |
|
1370
|
|
|
$temp_array['airport_arrival_city'] = $row['airport_arrival_city']; |
|
1371
|
|
|
$temp_array['airport_arrival_country'] = $row['airport_arrival_country']; |
|
1372
|
|
|
$routes_array[] = $temp_array; |
|
1373
|
|
|
} |
|
1374
|
|
|
return $routes_array; |
|
1375
|
|
|
} |
|
1376
|
|
|
|
|
1377
|
|
|
/** |
|
1378
|
|
|
* Counts all hours by a owner |
|
1379
|
|
|
* |
|
1380
|
|
|
* @return Array the hour list |
|
1381
|
|
|
* |
|
1382
|
|
|
*/ |
|
1383
|
|
|
public function countAllHoursByOwner($owner, $filters = array()) |
|
1384
|
|
|
{ |
|
1385
|
|
|
global $globalTimezone, $globalDBdriver; |
|
1386
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1387
|
|
|
$owner = filter_var($owner,FILTER_SANITIZE_STRING); |
|
1388
|
|
|
if ($globalTimezone != '') { |
|
1389
|
|
|
date_default_timezone_set($globalTimezone); |
|
1390
|
|
|
$datetime = new DateTime(); |
|
1391
|
|
|
$offset = $datetime->format('P'); |
|
1392
|
|
|
} else $offset = '+00:00'; |
|
1393
|
|
|
if ($globalDBdriver == 'mysql') { |
|
1394
|
|
|
$query = "SELECT HOUR(CONVERT_TZ(spotter_archive_output.date,'+00:00', :offset)) AS hour_name, count(*) as hour_count |
|
1395
|
|
|
FROM spotter_archive_output".$filter_query." spotter_archive_output.owner_name = :owner |
|
1396
|
|
|
GROUP BY hour_name |
|
1397
|
|
|
ORDER BY hour_name ASC"; |
|
1398
|
|
|
} else { |
|
1399
|
|
|
$query = "SELECT EXTRACT(HOUR FROM spotter_archive_output.date AT TIME ZONE INTERVAL :offset) AS hour_name, count(*) as hour_count |
|
1400
|
|
|
FROM spotter_archive_output".$filter_query." spotter_archive_output.owner_name = :owner |
|
1401
|
|
|
GROUP BY hour_name |
|
1402
|
|
|
ORDER BY hour_name ASC"; |
|
1403
|
|
|
} |
|
1404
|
|
|
$sth = $this->db->prepare($query); |
|
1405
|
|
|
$sth->execute(array(':owner' => $owner,':offset' => $offset)); |
|
1406
|
|
|
$hour_array = array(); |
|
1407
|
|
|
$temp_array = array(); |
|
1408
|
|
|
while($row = $sth->fetch(PDO::FETCH_ASSOC)) |
|
1409
|
|
|
{ |
|
1410
|
|
|
$temp_array['hour_name'] = $row['hour_name']; |
|
1411
|
|
|
$temp_array['hour_count'] = $row['hour_count']; |
|
1412
|
|
|
$hour_array[] = $temp_array; |
|
1413
|
|
|
} |
|
1414
|
|
|
return $hour_array; |
|
1415
|
|
|
} |
|
1416
|
|
|
|
|
1417
|
|
|
/** |
|
1418
|
|
|
* Gets last spotter information based on a particular callsign |
|
1419
|
|
|
* |
|
1420
|
|
|
* @return Array the spotter information |
|
1421
|
|
|
* |
|
1422
|
|
|
*/ |
|
1423
|
|
|
public function getDateArchiveSpotterDataById($id,$date) { |
|
1424
|
|
|
$Spotter = new Spotter($this->db); |
|
1425
|
|
|
date_default_timezone_set('UTC'); |
|
1426
|
|
|
$id = filter_var($id, FILTER_SANITIZE_STRING); |
|
1427
|
|
|
$query = 'SELECT spotter_archive.* FROM spotter_archive INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_archive l WHERE l.flightaware_id = :id AND l.date <= :date GROUP BY l.flightaware_id) s on spotter_archive.flightaware_id = s.flightaware_id AND spotter_archive.date = s.maxdate ORDER BY spotter_archive.date DESC'; |
|
1428
|
|
|
$date = date('c',$date); |
|
1429
|
|
|
$spotter_array = $Spotter->getDataFromDB($query,array(':id' => $id,':date' => $date)); |
|
1430
|
|
|
return $spotter_array; |
|
1431
|
|
|
} |
|
1432
|
|
|
|
|
1433
|
|
|
/** |
|
1434
|
|
|
* Gets all the spotter information based on a particular callsign |
|
1435
|
|
|
* |
|
1436
|
|
|
* @return Array the spotter information |
|
1437
|
|
|
* |
|
1438
|
|
|
*/ |
|
1439
|
|
|
public function getDateArchiveSpotterDataByIdent($ident,$date) { |
|
1440
|
|
|
$Spotter = new Spotter($this->db); |
|
1441
|
|
|
date_default_timezone_set('UTC'); |
|
1442
|
|
|
$ident = filter_var($ident, FILTER_SANITIZE_STRING); |
|
1443
|
|
|
$query = 'SELECT spotter_archive.* FROM spotter_archive INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_archive l WHERE l.ident = :ident AND l.date <= :date GROUP BY l.flightaware_id) s on spotter_archive.flightaware_id = s.flightaware_id AND spotter_archive.date = s.maxdate ORDER BY spotter_archive.date DESC'; |
|
1444
|
|
|
$date = date('c',$date); |
|
1445
|
|
|
$spotter_array = $Spotter->getDataFromDB($query,array(':ident' => $ident,':date' => $date)); |
|
1446
|
|
|
return $spotter_array; |
|
1447
|
|
|
} |
|
1448
|
|
|
|
|
1449
|
|
|
/** |
|
1450
|
|
|
* Gets all the spotter information based on the airport |
|
1451
|
|
|
* |
|
1452
|
|
|
* @return Array the spotter information |
|
1453
|
|
|
* |
|
1454
|
|
|
*/ |
|
1455
|
|
|
public function getSpotterDataByAirport($airport = '', $limit = '', $sort = '',$filters = array()) { |
|
1456
|
|
|
global $global_query; |
|
1457
|
|
|
$Spotter = new Spotter($this->db); |
|
1458
|
|
|
date_default_timezone_set('UTC'); |
|
1459
|
|
|
$query_values = array(); |
|
1460
|
|
|
$limit_query = ''; |
|
1461
|
|
|
$additional_query = ''; |
|
1462
|
|
|
$filter_query = $this->getFilter($filters,true,true); |
|
1463
|
|
|
|
|
1464
|
|
|
if ($airport != "") { |
|
1465
|
|
|
if (!is_string($airport)) { |
|
1466
|
|
|
return false; |
|
|
|
|
|
|
1467
|
|
|
} else { |
|
1468
|
|
|
$additional_query .= " AND ((spotter_archive_output.departure_airport_icao = :airport) OR (spotter_archive_output.arrival_airport_icao = :airport))"; |
|
1469
|
|
|
$query_values = array(':airport' => $airport); |
|
1470
|
|
|
} |
|
1471
|
|
|
} |
|
1472
|
|
|
|
|
1473
|
|
|
if ($limit != "") { |
|
1474
|
|
|
$limit_array = explode(",", $limit); |
|
1475
|
|
|
|
|
1476
|
|
|
$limit_array[0] = filter_var($limit_array[0],FILTER_SANITIZE_NUMBER_INT); |
|
1477
|
|
|
$limit_array[1] = filter_var($limit_array[1],FILTER_SANITIZE_NUMBER_INT); |
|
1478
|
|
|
|
|
1479
|
|
|
if ($limit_array[0] >= 0 && $limit_array[1] >= 0) { |
|
1480
|
|
|
//$limit_query = " LIMIT ".$limit_array[0].",".$limit_array[1]; |
|
1481
|
|
|
$limit_query = " LIMIT ".$limit_array[1]." OFFSET ".$limit_array[0]; |
|
1482
|
|
|
} |
|
1483
|
|
|
} |
|
1484
|
|
|
|
|
1485
|
|
|
if ($sort != "") { |
|
1486
|
|
|
$search_orderby_array = $Spotter->getOrderBy(); |
|
1487
|
|
|
$orderby_query = $search_orderby_array[$sort]['sql']; |
|
1488
|
|
|
} else { |
|
1489
|
|
|
$orderby_query = " ORDER BY spotter_archive_output.date DESC"; |
|
1490
|
|
|
} |
|
1491
|
|
|
|
|
1492
|
|
|
$query = $global_query.$filter_query." spotter_archive_output.ident <> '' ".$additional_query." AND ((spotter_archive_output.departure_airport_icao <> 'NA') AND (spotter_archive_output.arrival_airport_icao <> 'NA')) ".$orderby_query; |
|
1493
|
|
|
|
|
1494
|
|
|
$spotter_array = $Spotter->getDataFromDB($query, $query_values, $limit_query); |
|
1495
|
|
|
|
|
1496
|
|
|
return $spotter_array; |
|
1497
|
|
|
} |
|
1498
|
|
|
} |
|
1499
|
|
|
?> |
Ysurac /
FlightAirMap
Loading history...
If you return a value from a function or method, it should be a sub-type of the type that is given by the parent type f.e. an interface, or abstract method. This is more formally defined by the Lizkov substitution principle, and guarantees that classes that depend on the parent type can use any instance of a child type interchangably. This principle also belongs to the SOLID principles for object oriented design.
Let’s take a look at an example:
Our function
my_functionexpects aPostobject, and outputs the author of the post. The base classPostreturns a simple string and outputting a simple string will work just fine. However, the child classBlogPostwhich is a sub-type ofPostinstead decided to return anobject, and is therefore violating the SOLID principles. If aBlogPostwere passed tomy_function, PHP would not complain, but ultimately fail when executing thestrtouppercall in its body.