|
1
|
|
|
<?php |
|
2
|
|
|
//$global_query = "SELECT spotter_live.* FROM spotter_live"; |
|
3
|
|
|
|
|
4
|
|
|
class SpotterLive { |
|
5
|
|
|
public $db; |
|
6
|
|
|
static $global_query = "SELECT spotter_live.* FROM spotter_live"; |
|
7
|
|
|
|
|
8
|
|
|
public function __construct($dbc = null) { |
|
9
|
|
|
$Connection = new Connection($dbc); |
|
10
|
|
|
$this->db = $Connection->db(); |
|
11
|
|
|
} |
|
12
|
|
|
|
|
13
|
|
|
/** |
|
14
|
|
|
* Gets all the spotter information based on the latest data entry |
|
15
|
|
|
* |
|
16
|
|
|
* @return Array the spotter information |
|
17
|
|
|
* |
|
18
|
|
|
*/ |
|
19
|
|
|
public function getLiveSpotterData($limit = '', $sort = '', $filter = array()) |
|
20
|
|
|
{ |
|
21
|
|
|
global $globalDBdriver, $globalLiveInterval; |
|
22
|
|
|
$Spotter = new Spotter($this->db); |
|
23
|
|
|
date_default_timezone_set('UTC'); |
|
24
|
|
|
|
|
25
|
|
|
$filter_query = ''; |
|
26
|
|
|
if (isset($filter['source']) && !empty($filter['source'])) { |
|
27
|
|
|
$filter_query = " AND format_source IN ('".implode("','",$filter['source'])."')"; |
|
28
|
|
|
} |
|
29
|
|
|
if (isset($filter['airlines']) && !empty($filter['airlines'])) { |
|
30
|
|
|
$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_live.flightaware_id"; |
|
31
|
|
|
} |
|
32
|
|
|
if (isset($filter['airlinestype']) && !empty($filter['airlinestype'])) { |
|
33
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_output WHERE spotter_output.airline_type = '".$filter['airlinestype']."') sa ON sa.flightaware_id = spotter_live.flightaware_id "; |
|
34
|
|
|
} |
|
35
|
|
|
if (isset($filter['source_aprs']) && !empty($filter['source_aprs'])) { |
|
36
|
|
|
$filter_query = " AND format_source = 'aprs' AND source_name IN ('".implode("','",$filter['source_aprs'])."')"; |
|
37
|
|
|
} |
|
38
|
|
|
|
|
39
|
|
|
$limit_query = ''; |
|
40
|
|
|
if ($limit != '') |
|
41
|
|
|
{ |
|
42
|
|
|
$limit_array = explode(',', $limit); |
|
43
|
|
|
$limit_array[0] = filter_var($limit_array[0],FILTER_SANITIZE_NUMBER_INT); |
|
44
|
|
|
$limit_array[1] = filter_var($limit_array[1],FILTER_SANITIZE_NUMBER_INT); |
|
45
|
|
|
if ($limit_array[0] >= 0 && $limit_array[1] >= 0) |
|
46
|
|
|
{ |
|
47
|
|
|
$limit_query = ' LIMIT '.$limit_array[1].' OFFSET '.$limit_array[0]; |
|
48
|
|
|
} |
|
49
|
|
|
} |
|
50
|
|
|
$orderby_query = ''; |
|
51
|
|
|
if ($sort != '') |
|
52
|
|
|
{ |
|
53
|
|
|
$search_orderby_array = $this->getOrderBy(); |
|
54
|
|
|
$orderby_query = ' '.$search_orderby_array[$sort]['sql']; |
|
55
|
|
|
} |
|
56
|
|
|
|
|
57
|
|
|
if (!isset($globalLiveInterval)) $globalLiveInterval = '200'; |
|
58
|
|
|
if ($globalDBdriver == 'mysql') { |
|
59
|
|
|
//$query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL 30 SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate"; |
|
60
|
|
|
$query = 'SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate'.$filter_query.$orderby_query; |
|
61
|
|
|
} else { |
|
62
|
|
|
$query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate".$filter_query.$orderby_query; |
|
63
|
|
|
} |
|
64
|
|
|
$spotter_array = $Spotter->getDataFromDB($query.$limit_query); |
|
65
|
|
|
|
|
66
|
|
|
return $spotter_array; |
|
67
|
|
|
} |
|
68
|
|
|
|
|
69
|
|
|
/** |
|
70
|
|
|
* Gets Minimal Live Spotter data |
|
71
|
|
|
* |
|
72
|
|
|
* @return Array the spotter information |
|
73
|
|
|
* |
|
74
|
|
|
*/ |
|
75
|
|
|
public function getMinLiveSpotterData($filter = array()) |
|
76
|
|
|
{ |
|
77
|
|
|
global $globalDBdriver, $globalLiveInterval; |
|
78
|
|
|
date_default_timezone_set('UTC'); |
|
79
|
|
|
|
|
80
|
|
|
$filter_query = ''; |
|
81
|
|
|
if (isset($filter['source']) && !empty($filter['source'])) { |
|
82
|
|
|
$filter_query .= " AND format_source IN ('".implode("','",$filter['source'])."') "; |
|
83
|
|
|
} |
|
84
|
|
|
if (isset($filter['airlines']) && !empty($filter['airlines'])) { |
|
85
|
|
|
$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_live.flightaware_id "; |
|
86
|
|
|
} |
|
87
|
|
|
if (isset($filter['airlinestype']) && !empty($filter['airlinestype'])) { |
|
88
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_output WHERE spotter_output.airline_type = '".$filter['airlinestype']."') sa ON sa.flightaware_id = spotter_live.flightaware_id "; |
|
89
|
|
|
} |
|
90
|
|
|
if (isset($filter['source_aprs']) && !empty($filter['source_aprs'])) { |
|
91
|
|
|
$filter_query = " AND format_source = 'aprs' AND source_name IN ('".implode("','",$filter['source_aprs'])."')"; |
|
92
|
|
|
} |
|
93
|
|
|
|
|
94
|
|
|
if (!isset($globalLiveInterval)) $globalLiveInterval = '200'; |
|
95
|
|
|
if ($globalDBdriver == 'mysql') { |
|
96
|
|
|
// $query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL ".$globalLiveInterval." SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate$orderby_query"; |
|
97
|
|
|
// $query = 'SELECT spotter_live.ident, spotter_live.flightaware_id, spotter_live.aircraft_icao, spotter_live.departure_airport_icao as departure_airport, spotter_live.arrival_airport_icao as arrival_airport, spotter_live.latitude, spotter_live.longitude, spotter_live.altitude, spotter_live.heading, spotter_live.ground_speed, spotter_live.squawk, a.aircraft_shadow FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate INNER JOIN (SELECT * FROM aircraft) a on spotter_live.aircraft_icao = a.icao'; |
|
98
|
|
|
// $query = 'SELECT spotter_live.ident, spotter_live.flightaware_id, spotter_live.aircraft_icao, spotter_live.departure_airport_icao as departure_airport, spotter_live.arrival_airport_icao as arrival_airport, spotter_live.latitude, spotter_live.longitude, spotter_live.altitude, spotter_live.heading, spotter_live.ground_speed, spotter_live.squawk FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate'.$filter_query; |
|
99
|
|
|
|
|
100
|
|
|
$query = 'SELECT a.aircraft_shadow, spotter_live.ident, spotter_live.flightaware_id, spotter_live.aircraft_icao, spotter_live.departure_airport_icao as departure_airport, spotter_live.arrival_airport_icao as arrival_airport, spotter_live.latitude, spotter_live.longitude, spotter_live.altitude, spotter_live.heading, spotter_live.ground_speed, spotter_live.squawk, spotter_live.date, spotter_live.format_source |
|
101
|
|
|
FROM spotter_live |
|
102
|
|
|
INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate '.$filter_query.'LEFT JOIN (SELECT aircraft_shadow,icao FROM aircraft) a ON spotter_live.aircraft_icao = a.icao'; |
|
103
|
|
|
|
|
104
|
|
|
// $query = 'SELECT spotter_live.ident, spotter_live.flightaware_id, spotter_live.aircraft_icao, spotter_live.departure_airport_icao as departure_airport, spotter_live.arrival_airport_icao as arrival_airport, spotter_live.latitude, spotter_live.longitude, spotter_live.altitude, spotter_live.heading, spotter_live.ground_speed, spotter_live.squawk FROM spotter_live WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= spotter_live.date ORDER BY spotter_live.date GROUP BY spotter_live.flightaware_id'.$filter_query; |
|
105
|
|
|
|
|
106
|
|
|
//$query = 'SELECT a.aircraft_shadow, spotter_live.ident, spotter_live.flightaware_id, spotter_live.aircraft_icao, spotter_live.departure_airport_icao as departure_airport, spotter_live.arrival_airport_icao as arrival_airport, spotter_live.latitude, spotter_live.longitude, spotter_live.altitude, spotter_live.heading, spotter_live.ground_speed, spotter_live.squawk FROM spotter_live WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= date '.$filter_query.'LEFT JOIN (SELECT aircraft_shadow,icao FROM aircraft) a ON spotter_live.aircraft_icao = a.icao'; |
|
107
|
|
|
} else { |
|
108
|
|
|
//$query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE NOW() AT TIME ZONE 'UTC' - '30 SECONDS'->INTERVAL <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate"; |
|
109
|
|
|
//$query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE NOW() AT TIME ZONE 'UTC' - '".$globalLiveInterval." SECONDS'->INTERVAL <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate$orderby_query"; |
|
110
|
|
|
//$query = 'SELECT spotter_live.ident, spotter_live.flightaware_id, spotter_live.aircraft_icao, spotter_live.departure_airport_icao as departure_airport, spotter_live.arrival_airport_icao as arrival_airport, spotter_live.latitude, spotter_live.longitude, spotter_live.altitude, spotter_live.heading, spotter_live.ground_speed, spotter_live.squawk, a.aircraft_shadow FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate '.$filter_query.'INNER JOIN (SELECT * FROM aircraft) a on spotter_live.aircraft_icao = a.icao'; |
|
111
|
|
|
$query = "SELECT a.aircraft_shadow, spotter_live.ident, spotter_live.flightaware_id, spotter_live.aircraft_icao, spotter_live.departure_airport_icao as departure_airport, spotter_live.arrival_airport_icao as arrival_airport, spotter_live.latitude, spotter_live.longitude, spotter_live.altitude, spotter_live.heading, spotter_live.ground_speed, spotter_live.squawk, spotter_live.date, spotter_live.format_source |
|
112
|
|
|
FROM spotter_live |
|
113
|
|
|
INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate ".$filter_query.'LEFT JOIN (SELECT aircraft_shadow,icao FROM aircraft) a ON spotter_live.aircraft_icao = a.icao'; |
|
114
|
|
|
} |
|
115
|
|
|
// $spotter_array = Spotter->getDataFromDB($query.$limit_query); |
|
116
|
|
|
//echo $query; |
|
117
|
|
|
|
|
118
|
|
|
try { |
|
119
|
|
|
$sth = $this->db->prepare($query); |
|
120
|
|
|
$sth->execute(); |
|
121
|
|
|
} catch(PDOException $e) { |
|
122
|
|
|
echo $e->getMessage(); |
|
123
|
|
|
die; |
|
124
|
|
|
} |
|
125
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
126
|
|
|
|
|
127
|
|
|
return $spotter_array; |
|
128
|
|
|
} |
|
129
|
|
|
|
|
130
|
|
|
/** |
|
131
|
|
|
* Gets Minimal Live Spotter data since xx seconds |
|
132
|
|
|
* |
|
133
|
|
|
* @return Array the spotter information |
|
134
|
|
|
* |
|
135
|
|
|
*/ |
|
136
|
|
|
public function getMinLastLiveSpotterData($filter = array()) |
|
137
|
|
|
{ |
|
138
|
|
|
global $globalDBdriver, $globalLiveInterval; |
|
139
|
|
|
date_default_timezone_set('UTC'); |
|
140
|
|
|
|
|
141
|
|
|
$filter_query = ''; |
|
142
|
|
|
if (isset($filter['source']) && !empty($filter['source'])) { |
|
143
|
|
|
$filter_query .= " AND format_source IN ('".implode("','",$filter['source'])."') "; |
|
144
|
|
|
} |
|
145
|
|
|
if (isset($filter['airlines']) && !empty($filter['airlines'])) { |
|
146
|
|
|
$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_live.flightaware_id "; |
|
147
|
|
|
} |
|
148
|
|
|
if (isset($filter['airlinestype']) && !empty($filter['airlinestype'])) { |
|
149
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_output WHERE spotter_output.airline_type = '".$filter['airlinestype']."') sa ON sa.flightaware_id = spotter_live.flightaware_id "; |
|
150
|
|
|
} |
|
151
|
|
|
if (isset($filter['source_aprs']) && !empty($filter['source_aprs'])) { |
|
152
|
|
|
$filter_query = " AND format_source = 'aprs' AND source_name IN ('".implode("','",$filter['source_aprs'])."')"; |
|
153
|
|
|
} |
|
154
|
|
|
|
|
155
|
|
|
if (!isset($globalLiveInterval)) $globalLiveInterval = '200'; |
|
156
|
|
|
if ($globalDBdriver == 'mysql') { |
|
157
|
|
|
|
|
158
|
|
|
$query = 'SELECT a.aircraft_shadow, a.engine_type, a.engine_count, a.wake_category, spotter_live.ident, spotter_live.flightaware_id, spotter_live.aircraft_icao, spotter_live.departure_airport_icao as departure_airport, spotter_live.arrival_airport_icao as arrival_airport, spotter_live.latitude, spotter_live.longitude, spotter_live.altitude, spotter_live.heading, spotter_live.ground_speed, spotter_live.squawk, spotter_live.date, spotter_live.format_source |
|
159
|
|
|
FROM spotter_live LEFT JOIN (SELECT aircraft_shadow,engine_type, engine_count, wake_category,icao FROM aircraft) a ON spotter_live.aircraft_icao = a.icao WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= spotter_live.date |
|
160
|
|
|
'.$filter_query.'ORDER BY spotter_live.flightaware_id, spotter_live.date'; |
|
161
|
|
|
} else { |
|
162
|
|
|
/* |
|
163
|
|
|
$query = "SELECT a.aircraft_shadow, spotter_live.ident, spotter_live.flightaware_id, spotter_live.aircraft_icao, spotter_live.departure_airport_icao as departure_airport, spotter_live.arrival_airport_icao as arrival_airport, spotter_live.latitude, spotter_live.longitude, spotter_live.altitude, spotter_live.heading, spotter_live.ground_speed, spotter_live.squawk, spotter_live.date, spotter_live.format_source |
|
164
|
|
|
FROM spotter_live WHERE CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= spotter_live.date |
|
165
|
|
|
".$filter_query.'LEFT JOIN (SELECT aircraft_shadow,icao FROM aircraft) a ON spotter_live.aircraft_icao = a.icao ORDER BY spotter_live.flightaware_id, spotter_live.date'; |
|
166
|
|
|
*/ |
|
167
|
|
|
$query = "SELECT a.aircraft_shadow, a.engine_type, a.engine_count, a.wake_category, spotter_live.ident, spotter_live.flightaware_id, spotter_live.aircraft_icao, spotter_live.departure_airport_icao as departure_airport, spotter_live.arrival_airport_icao as arrival_airport, spotter_live.latitude, spotter_live.longitude, spotter_live.altitude, spotter_live.heading, spotter_live.ground_speed, spotter_live.squawk, spotter_live.date, spotter_live.format_source |
|
168
|
|
|
FROM spotter_live LEFT JOIN (SELECT aircraft_shadow,engine_type, engine_count, wake_category, icao FROM aircraft) a ON spotter_live.aircraft_icao = a.icao WHERE CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= spotter_live.date |
|
169
|
|
|
".$filter_query.'ORDER BY spotter_live.flightaware_id, spotter_live.date'; |
|
170
|
|
|
// echo $query; |
|
171
|
|
|
} |
|
172
|
|
|
|
|
173
|
|
|
try { |
|
174
|
|
|
$sth = $this->db->prepare($query); |
|
175
|
|
|
$sth->execute(); |
|
176
|
|
|
} catch(PDOException $e) { |
|
177
|
|
|
echo $e->getMessage(); |
|
178
|
|
|
die; |
|
179
|
|
|
|
|
180
|
|
|
} |
|
181
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
182
|
|
|
|
|
183
|
|
|
return $spotter_array; |
|
184
|
|
|
} |
|
185
|
|
|
|
|
186
|
|
|
/** |
|
187
|
|
|
* Gets number of latest data entry |
|
188
|
|
|
* |
|
189
|
|
|
* @return String number of entry |
|
190
|
|
|
* |
|
191
|
|
|
*/ |
|
192
|
|
|
public function getLiveSpotterCount($filter = array()) |
|
193
|
|
|
{ |
|
194
|
|
|
global $globalDBdriver, $globalLiveInterval; |
|
195
|
|
|
$filter_query = ''; |
|
196
|
|
|
if (isset($filter['source']) && !empty($filter['source'])) { |
|
197
|
|
|
$filter_query = " AND format_source IN ('".implode("','",$filter['source'])."')"; |
|
198
|
|
|
} |
|
199
|
|
|
if (isset($filter['airlines']) && !empty($filter['airlines'])) { |
|
200
|
|
|
$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_live.flightaware_id"; |
|
201
|
|
|
} |
|
202
|
|
|
if (isset($filter['airlinestype']) && !empty($filter['airlinestype'])) { |
|
203
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_output WHERE spotter_output.airline_type = '".$filter['airlinestype']."') sa ON sa.flightaware_id = spotter_live.flightaware_id "; |
|
204
|
|
|
} |
|
205
|
|
|
if (isset($filter['source_aprs']) && !empty($filter['source_aprs'])) { |
|
206
|
|
|
$filter_query = " AND format_source = 'aprs' AND source_name IN ('".implode("','",$filter['source_aprs'])."')"; |
|
207
|
|
|
} |
|
208
|
|
|
|
|
209
|
|
|
if (!isset($globalLiveInterval)) $globalLiveInterval = '200'; |
|
210
|
|
|
if ($globalDBdriver == 'mysql') { |
|
211
|
|
|
//$query = 'SELECT COUNT(*) as nb FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate'.$filter_query; |
|
212
|
|
|
$query = 'SELECT COUNT(DISTINCT flightaware_id) as nb FROM spotter_live WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= date'.$filter_query; |
|
213
|
|
|
} else { |
|
214
|
|
|
//$query = "SELECT COUNT(*) as nb FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE NOW() AT TIME ZONE 'UTC' - '".$globalLiveInterval." SECONDS'->INTERVAL <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate".$filter_query; |
|
215
|
|
|
$query = "SELECT COUNT(DISTINCT flightaware_id) as nb FROM spotter_live WHERE CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= date".$filter_query; |
|
216
|
|
|
} |
|
217
|
|
|
try { |
|
218
|
|
|
$sth = $this->db->prepare($query); |
|
219
|
|
|
$sth->execute(); |
|
220
|
|
|
} catch(PDOException $e) { |
|
221
|
|
|
echo $e->getMessage(); |
|
222
|
|
|
die; |
|
223
|
|
|
} |
|
224
|
|
|
$result = $sth->fetch(PDO::FETCH_ASSOC); |
|
225
|
|
|
return $result['nb']; |
|
226
|
|
|
} |
|
227
|
|
|
|
|
228
|
|
|
/** |
|
229
|
|
|
* Gets all the spotter information based on the latest data entry and coord |
|
230
|
|
|
* |
|
231
|
|
|
* @return Array the spotter information |
|
232
|
|
|
* |
|
233
|
|
|
*/ |
|
234
|
|
|
public function getLiveSpotterDatabyCoord($coord, $filter = array()) |
|
235
|
|
|
{ |
|
236
|
|
|
global $globalDBdriver, $globalLiveInterval; |
|
237
|
|
|
$Spotter = new Spotter($this->db); |
|
238
|
|
|
if (!isset($globalLiveInterval)) $globalLiveInterval = '200'; |
|
239
|
|
|
$filter_query = ''; |
|
240
|
|
|
if (isset($filter['source'])) { |
|
241
|
|
|
$filter_query = " AND format_source IN ('".implode(',',$filter['source'])."')"; |
|
242
|
|
|
} |
|
243
|
|
|
if (isset($filter['airlines'])) { |
|
244
|
|
|
$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_live.flightaware_id"; |
|
245
|
|
|
} |
|
246
|
|
|
if (isset($filter['airlinestype']) && !empty($filter['airlinestype'])) { |
|
247
|
|
|
$filter_query .= " INNER JOIN (SELECT flightaware_id FROM spotter_output WHERE spotter_output.airline_type = '".$filter['airlinestype']."') sa ON sa.flightaware_id = spotter_live.flightaware_id "; |
|
248
|
|
|
} |
|
249
|
|
|
if (isset($filter['source_aprs']) && !empty($filter['source_aprs'])) { |
|
250
|
|
|
$filter_query = " AND format_source = 'aprs' AND source_name IN ('".implode("','",$filter['source_aprs'])."')"; |
|
251
|
|
|
} |
|
252
|
|
|
|
|
253
|
|
|
if (is_array($coord)) { |
|
254
|
|
|
$minlong = filter_var($coord[0],FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION); |
|
255
|
|
|
$minlat = filter_var($coord[1],FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION); |
|
256
|
|
|
$maxlong = filter_var($coord[2],FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION); |
|
257
|
|
|
$maxlat = filter_var($coord[3],FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION); |
|
258
|
|
|
} else return array(); |
|
259
|
|
|
if ($globalDBdriver == 'mysql') { |
|
260
|
|
|
//$query = "SELECT spotter_output.* FROM spotter_output WHERE spotter_output.flightaware_id IN (SELECT spotter_live.flightaware_id FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL ".$globalLiveInterval." SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate AND spotter_live.latitude BETWEEN ".$minlat." AND ".$maxlat." AND spotter_live.longitude BETWEEN ".$minlong." AND ".$maxlong.")"; |
|
261
|
|
|
$query = 'SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate AND spotter_live.latitude BETWEEN '.$minlat.' AND '.$maxlat.' AND spotter_live.longitude BETWEEN '.$minlong.' AND '.$maxlong.' GROUP BY spotter_live.flightaware_id'.$filter_query; |
|
262
|
|
|
} else { |
|
263
|
|
|
$query = "SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE NOW() at time zone 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= l.date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate AND spotter_live.latitude BETWEEN ".$minlat." AND ".$maxlat." AND spotter_live.longitude BETWEEN ".$minlong." AND ".$maxlong." GROUP BY spotter_live.flightaware_id".$filter_query; |
|
264
|
|
|
} |
|
265
|
|
|
$spotter_array = $Spotter->getDataFromDB($query); |
|
266
|
|
|
return $spotter_array; |
|
267
|
|
|
} |
|
268
|
|
|
|
|
269
|
|
|
/** |
|
270
|
|
|
* Gets all the spotter information based on a user's latitude and longitude |
|
271
|
|
|
* |
|
272
|
|
|
* @return Array the spotter information |
|
273
|
|
|
* |
|
274
|
|
|
*/ |
|
275
|
|
|
public function getLatestSpotterForLayar($lat, $lng, $radius, $interval) |
|
276
|
|
|
{ |
|
277
|
|
|
$Spotter = new Spotter($this->db); |
|
278
|
|
|
date_default_timezone_set('UTC'); |
|
279
|
|
|
if ($lat != '') { |
|
280
|
|
|
if (!is_numeric($lat)) { |
|
281
|
|
|
return false; |
|
282
|
|
|
} |
|
283
|
|
|
} |
|
284
|
|
|
|
|
285
|
|
|
if ($lng != '') |
|
286
|
|
|
{ |
|
287
|
|
|
if (!is_numeric($lng)) |
|
288
|
|
|
{ |
|
289
|
|
|
return false; |
|
290
|
|
|
} |
|
291
|
|
|
} |
|
292
|
|
|
|
|
293
|
|
|
if ($radius != '') |
|
294
|
|
|
{ |
|
295
|
|
|
if (!is_numeric($radius)) |
|
296
|
|
|
{ |
|
297
|
|
|
return false; |
|
298
|
|
|
} |
|
299
|
|
|
} |
|
300
|
|
|
$additional_query = ''; |
|
301
|
|
|
if ($interval != '') |
|
302
|
|
|
{ |
|
303
|
|
|
if (!is_string($interval)) |
|
304
|
|
|
{ |
|
305
|
|
|
//$additional_query = ' AND DATE_SUB(UTC_TIMESTAMP(),INTERVAL 1 MINUTE) <= spotter_live.date '; |
|
306
|
|
|
return false; |
|
307
|
|
|
} else { |
|
308
|
|
|
if ($interval == '1m') |
|
309
|
|
|
{ |
|
310
|
|
|
$additional_query = ' AND DATE_SUB(UTC_TIMESTAMP(),INTERVAL 1 MINUTE) <= spotter_live.date '; |
|
311
|
|
|
} else if ($interval == '15m'){ |
|
312
|
|
|
$additional_query = ' AND DATE_SUB(UTC_TIMESTAMP(),INTERVAL 15 MINUTE) <= spotter_live.date '; |
|
313
|
|
|
} |
|
314
|
|
|
} |
|
315
|
|
|
} else { |
|
316
|
|
|
$additional_query = ' AND DATE_SUB(UTC_TIMESTAMP(),INTERVAL 1 MINUTE) <= spotter_live.date '; |
|
317
|
|
|
} |
|
318
|
|
|
|
|
319
|
|
|
$query = "SELECT spotter_live.*, ( 6371 * acos( cos( radians(:lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:lng) ) + sin( radians(:lat) ) * sin( radians( latitude ) ) ) ) AS distance FROM spotter_live |
|
320
|
|
|
WHERE spotter_live.latitude <> '' |
|
321
|
|
|
AND spotter_live.longitude <> '' |
|
322
|
|
|
".$additional_query." |
|
323
|
|
|
HAVING distance < :radius |
|
324
|
|
|
ORDER BY distance"; |
|
325
|
|
|
|
|
326
|
|
|
$spotter_array = $Spotter->getDataFromDB($query, array(':lat' => $lat, ':lng' => $lng,':radius' => $radius)); |
|
327
|
|
|
|
|
328
|
|
|
return $spotter_array; |
|
329
|
|
|
} |
|
330
|
|
|
|
|
331
|
|
|
|
|
332
|
|
|
/** |
|
333
|
|
|
* Gets all the spotter information based on a particular callsign |
|
334
|
|
|
* |
|
335
|
|
|
* @return Array the spotter information |
|
336
|
|
|
* |
|
337
|
|
|
*/ |
|
338
|
|
|
public function getLastLiveSpotterDataByIdent($ident) |
|
339
|
|
|
{ |
|
340
|
|
|
$Spotter = new Spotter($this->db); |
|
341
|
|
|
date_default_timezone_set('UTC'); |
|
342
|
|
|
|
|
343
|
|
|
$ident = filter_var($ident, FILTER_SANITIZE_STRING); |
|
344
|
|
|
$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 GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate ORDER BY spotter_live.date DESC'; |
|
345
|
|
|
|
|
346
|
|
|
$spotter_array = $Spotter->getDataFromDB($query,array(':ident' => $ident)); |
|
347
|
|
|
|
|
348
|
|
|
return $spotter_array; |
|
349
|
|
|
} |
|
350
|
|
|
|
|
351
|
|
|
/** |
|
352
|
|
|
* Gets all the spotter information based on a particular callsign |
|
353
|
|
|
* |
|
354
|
|
|
* @return Array the spotter information |
|
355
|
|
|
* |
|
356
|
|
|
*/ |
|
357
|
|
|
public function getDateLiveSpotterDataByIdent($ident,$date) |
|
358
|
|
|
{ |
|
359
|
|
|
$Spotter = new Spotter($this->db); |
|
360
|
|
|
date_default_timezone_set('UTC'); |
|
361
|
|
|
|
|
362
|
|
|
$ident = filter_var($ident, FILTER_SANITIZE_STRING); |
|
363
|
|
|
$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.date <= :date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate ORDER BY spotter_live.date DESC'; |
|
364
|
|
|
|
|
365
|
|
|
$date = date('c',$date); |
|
366
|
|
|
$spotter_array = $Spotter->getDataFromDB($query,array(':ident' => $ident,':date' => $date)); |
|
367
|
|
|
|
|
368
|
|
|
return $spotter_array; |
|
369
|
|
|
} |
|
370
|
|
|
|
|
371
|
|
|
/** |
|
372
|
|
|
* Gets last spotter information based on a particular callsign |
|
373
|
|
|
* |
|
374
|
|
|
* @return Array the spotter information |
|
375
|
|
|
* |
|
376
|
|
|
*/ |
|
377
|
|
|
public function getLastLiveSpotterDataById($id) |
|
378
|
|
|
{ |
|
379
|
|
|
$Spotter = new Spotter($this->db); |
|
380
|
|
|
date_default_timezone_set('UTC'); |
|
381
|
|
|
|
|
382
|
|
|
$id = filter_var($id, 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.flightaware_id = :id GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate ORDER BY spotter_live.date DESC'; |
|
384
|
|
|
|
|
385
|
|
|
$spotter_array = $Spotter->getDataFromDB($query,array(':id' => $id)); |
|
386
|
|
|
|
|
387
|
|
|
return $spotter_array; |
|
388
|
|
|
} |
|
389
|
|
|
|
|
390
|
|
|
/** |
|
391
|
|
|
* Gets last spotter information based on a particular callsign |
|
392
|
|
|
* |
|
393
|
|
|
* @return Array the spotter information |
|
394
|
|
|
* |
|
395
|
|
|
*/ |
|
396
|
|
|
public function getDateLiveSpotterDataById($id,$date) |
|
397
|
|
|
{ |
|
398
|
|
|
$Spotter = new Spotter($this->db); |
|
399
|
|
|
date_default_timezone_set('UTC'); |
|
400
|
|
|
|
|
401
|
|
|
$id = filter_var($id, FILTER_SANITIZE_STRING); |
|
402
|
|
|
$query = 'SELECT spotter_live.* FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l WHERE l.flightaware_id = :id AND l.date <= :date GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate ORDER BY spotter_live.date DESC'; |
|
403
|
|
|
$date = date('c',$date); |
|
404
|
|
|
$spotter_array = $Spotter->getDataFromDB($query,array(':id' => $id,':date' => $date)); |
|
405
|
|
|
|
|
406
|
|
|
return $spotter_array; |
|
407
|
|
|
} |
|
408
|
|
|
|
|
409
|
|
|
/** |
|
410
|
|
|
* Gets altitude information based on a particular callsign |
|
411
|
|
|
* |
|
412
|
|
|
* @return Array the spotter information |
|
413
|
|
|
* |
|
414
|
|
|
*/ |
|
415
|
|
|
public function getAltitudeLiveSpotterDataByIdent($ident) |
|
416
|
|
|
{ |
|
417
|
|
|
|
|
418
|
|
|
date_default_timezone_set('UTC'); |
|
419
|
|
|
|
|
420
|
|
|
$ident = filter_var($ident, FILTER_SANITIZE_STRING); |
|
421
|
|
|
$query = 'SELECT spotter_live.altitude, spotter_live.date FROM spotter_live WHERE spotter_live.ident = :ident'; |
|
422
|
|
|
|
|
423
|
|
|
try { |
|
424
|
|
|
|
|
425
|
|
|
$sth = $this->db->prepare($query); |
|
426
|
|
|
$sth->execute(array(':ident' => $ident)); |
|
427
|
|
|
} catch(PDOException $e) { |
|
428
|
|
|
echo $e->getMessage(); |
|
429
|
|
|
die; |
|
430
|
|
|
} |
|
431
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
432
|
|
|
|
|
433
|
|
|
return $spotter_array; |
|
434
|
|
|
} |
|
435
|
|
|
|
|
436
|
|
|
/** |
|
437
|
|
|
* Gets all the spotter information based on a particular id |
|
438
|
|
|
* |
|
439
|
|
|
* @return Array the spotter information |
|
440
|
|
|
* |
|
441
|
|
|
*/ |
|
442
|
|
|
public function getAllLiveSpotterDataById($id) |
|
443
|
|
|
{ |
|
444
|
|
|
date_default_timezone_set('UTC'); |
|
445
|
|
|
$id = filter_var($id, FILTER_SANITIZE_STRING); |
|
446
|
|
|
$query = self::$global_query.' WHERE spotter_live.flightaware_id = :id ORDER BY date'; |
|
447
|
|
|
// $spotter_array = Spotter->getDataFromDB($query,array(':id' => $id)); |
|
448
|
|
|
|
|
449
|
|
|
try { |
|
450
|
|
|
|
|
451
|
|
|
$sth = $this->db->prepare($query); |
|
452
|
|
|
$sth->execute(array(':id' => $id)); |
|
453
|
|
|
} catch(PDOException $e) { |
|
454
|
|
|
echo $e->getMessage(); |
|
455
|
|
|
die; |
|
456
|
|
|
} |
|
457
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
458
|
|
|
|
|
459
|
|
|
return $spotter_array; |
|
460
|
|
|
} |
|
461
|
|
|
|
|
462
|
|
|
/** |
|
463
|
|
|
* Gets all the spotter information based on a particular ident |
|
464
|
|
|
* |
|
465
|
|
|
* @return Array the spotter information |
|
466
|
|
|
* |
|
467
|
|
|
*/ |
|
468
|
|
|
public function getAllLiveSpotterDataByIdent($ident) |
|
469
|
|
|
{ |
|
470
|
|
|
date_default_timezone_set('UTC'); |
|
471
|
|
|
$ident = filter_var($ident, FILTER_SANITIZE_STRING); |
|
472
|
|
|
$query = self::$global_query.' WHERE spotter_live.ident = :ident'; |
|
473
|
|
|
try { |
|
474
|
|
|
|
|
475
|
|
|
$sth = $this->db->prepare($query); |
|
476
|
|
|
$sth->execute(array(':ident' => $ident)); |
|
477
|
|
|
} catch(PDOException $e) { |
|
478
|
|
|
echo $e->getMessage(); |
|
479
|
|
|
die; |
|
480
|
|
|
} |
|
481
|
|
|
$spotter_array = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
482
|
|
|
return $spotter_array; |
|
483
|
|
|
} |
|
484
|
|
|
|
|
485
|
|
|
|
|
486
|
|
|
/** |
|
487
|
|
|
* Deletes all info in the table |
|
488
|
|
|
* |
|
489
|
|
|
* @return String success or false |
|
490
|
|
|
* |
|
491
|
|
|
*/ |
|
492
|
|
|
public function deleteLiveSpotterData() |
|
493
|
|
|
{ |
|
494
|
|
|
global $globalDBdriver; |
|
495
|
|
|
if ($globalDBdriver == 'mysql') { |
|
496
|
|
|
//$query = "DELETE FROM spotter_live WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL 30 MINUTE) >= spotter_live.date"; |
|
497
|
|
|
$query = 'DELETE FROM spotter_live WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL 9 HOUR) >= spotter_live.date'; |
|
498
|
|
|
//$query = "DELETE FROM spotter_live WHERE spotter_live.id IN (SELECT spotter_live.id FROM spotter_live INNER JOIN (SELECT l.flightaware_id, max(l.date) as maxdate FROM spotter_live l GROUP BY l.flightaware_id) s on spotter_live.flightaware_id = s.flightaware_id AND spotter_live.date = s.maxdate AND DATE_SUB(UTC_TIMESTAMP(),INTERVAL 1 HOUR) >= spotter_live.date)"; |
|
499
|
|
|
} else { |
|
500
|
|
|
$query = "DELETE FROM spotter_live WHERE NOW() AT TIME ZONE 'UTC' - INTERVAL '9 HOURS' >= spotter_live.date"; |
|
501
|
|
|
} |
|
502
|
|
|
|
|
503
|
|
|
try { |
|
504
|
|
|
|
|
505
|
|
|
$sth = $this->db->prepare($query); |
|
506
|
|
|
$sth->execute(); |
|
507
|
|
|
} catch(PDOException $e) { |
|
508
|
|
|
return "error"; |
|
509
|
|
|
} |
|
510
|
|
|
|
|
511
|
|
|
return "success"; |
|
512
|
|
|
} |
|
513
|
|
|
|
|
514
|
|
|
/** |
|
515
|
|
|
* Deletes all info in the table for aircraft not seen since 2 HOUR |
|
516
|
|
|
* |
|
517
|
|
|
* @return String success or false |
|
518
|
|
|
* |
|
519
|
|
|
*/ |
|
520
|
|
|
public function deleteLiveSpotterDataNotUpdated() |
|
521
|
|
|
{ |
|
522
|
|
|
global $globalDBdriver, $globalDebug; |
|
523
|
|
|
if ($globalDBdriver == 'mysql') { |
|
524
|
|
|
//$query = 'SELECT flightaware_id FROM spotter_live WHERE DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 HOUR) >= spotter_live.date AND spotter_live.flightaware_id NOT IN (SELECT flightaware_id FROM spotter_live WHERE DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 HOUR) < spotter_live.date) LIMIT 800 OFFSET 0'; |
|
525
|
|
|
$query = "SELECT spotter_live.flightaware_id FROM spotter_live INNER JOIN (SELECT flightaware_id,MAX(date) as max_date FROM spotter_live GROUP BY flightaware_id) s ON s.flightaware_id = spotter_live.flightaware_id AND DATE_SUB(UTC_TIMESTAMP(), INTERVAL 2 HOUR) >= s.max_date LIMIT 1200 OFFSET 0"; |
|
526
|
|
|
try { |
|
527
|
|
|
|
|
528
|
|
|
$sth = $this->db->prepare($query); |
|
529
|
|
|
$sth->execute(); |
|
530
|
|
|
} catch(PDOException $e) { |
|
531
|
|
|
return "error"; |
|
532
|
|
|
} |
|
533
|
|
|
$query_delete = 'DELETE FROM spotter_live WHERE flightaware_id IN ('; |
|
534
|
|
|
$i = 0; |
|
535
|
|
|
$j =0; |
|
536
|
|
|
$all = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
537
|
|
|
foreach($all as $row) |
|
538
|
|
|
{ |
|
539
|
|
|
$i++; |
|
540
|
|
|
$j++; |
|
541
|
|
|
if ($j == 30) { |
|
542
|
|
|
if ($globalDebug) echo "."; |
|
543
|
|
|
try { |
|
544
|
|
|
|
|
545
|
|
|
$sth = $this->db->prepare(substr($query_delete,0,-1).")"); |
|
546
|
|
|
$sth->execute(); |
|
547
|
|
|
} catch(PDOException $e) { |
|
548
|
|
|
return "error"; |
|
549
|
|
|
} |
|
550
|
|
|
$query_delete = 'DELETE FROM spotter_live WHERE flightaware_id IN ('; |
|
551
|
|
|
$j = 0; |
|
552
|
|
|
} |
|
553
|
|
|
$query_delete .= "'".$row['flightaware_id']."',"; |
|
554
|
|
|
} |
|
555
|
|
|
if ($i > 0) { |
|
556
|
|
|
try { |
|
557
|
|
|
|
|
558
|
|
|
$sth = $this->db->prepare(substr($query_delete,0,-1).")"); |
|
559
|
|
|
$sth->execute(); |
|
560
|
|
|
} catch(PDOException $e) { |
|
561
|
|
|
return "error"; |
|
562
|
|
|
} |
|
563
|
|
|
} |
|
564
|
|
|
return "success"; |
|
565
|
|
|
} elseif ($globalDBdriver == 'pgsql') { |
|
566
|
|
|
//$query = "SELECT flightaware_id FROM spotter_live WHERE NOW() AT TIME ZONE 'UTC' - INTERVAL '9 HOURS' >= spotter_live.date AND spotter_live.flightaware_id NOT IN (SELECT flightaware_id FROM spotter_live WHERE NOW() AT TIME ZONE 'UTC' - INTERVAL '9 HOURS' < spotter_live.date) LIMIT 800 OFFSET 0"; |
|
567
|
|
|
//$query = "SELECT spotter_live.flightaware_id FROM spotter_live INNER JOIN (SELECT flightaware_id,MAX(date) as max_date FROM spotter_live GROUP BY flightaware_id) s ON s.flightaware_id = spotter_live.flightaware_id AND NOW() AT TIME ZONE 'UTC' - INTERVAL '2 HOURS' >= s.max_date LIMIT 800 OFFSET 0"; |
|
568
|
|
|
$query = "DELETE FROM spotter_live WHERE flightaware_id IN (SELECT spotter_live.flightaware_id FROM spotter_live INNER JOIN (SELECT flightaware_id,MAX(date) as max_date FROM spotter_live GROUP BY flightaware_id) s ON s.flightaware_id = spotter_live.flightaware_id AND NOW() AT TIME ZONE 'UTC' - INTERVAL '2 HOURS' >= s.max_date LIMIT 800 OFFSET 0)"; |
|
569
|
|
|
try { |
|
570
|
|
|
|
|
571
|
|
|
$sth = $this->db->prepare($query); |
|
572
|
|
|
$sth->execute(); |
|
573
|
|
|
} catch(PDOException $e) { |
|
574
|
|
|
return "error"; |
|
575
|
|
|
} |
|
576
|
|
|
/* $query_delete = "DELETE FROM spotter_live WHERE flightaware_id IN ("; |
|
577
|
|
|
$i = 0; |
|
578
|
|
|
$j =0; |
|
579
|
|
|
$all = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
580
|
|
|
foreach($all as $row) |
|
581
|
|
|
{ |
|
582
|
|
|
$i++; |
|
583
|
|
|
$j++; |
|
584
|
|
|
if ($j == 100) { |
|
585
|
|
|
if ($globalDebug) echo "."; |
|
586
|
|
|
try { |
|
587
|
|
|
|
|
588
|
|
|
$sth = $this->db->query(substr($query_delete,0,-1).")"); |
|
589
|
|
|
//$sth->execute(); |
|
590
|
|
|
} catch(PDOException $e) { |
|
591
|
|
|
return "error"; |
|
592
|
|
|
} |
|
593
|
|
|
$query_delete = "DELETE FROM spotter_live WHERE flightaware_id IN ("; |
|
594
|
|
|
$j = 0; |
|
595
|
|
|
} |
|
596
|
|
|
$query_delete .= "'".$row['flightaware_id']."',"; |
|
597
|
|
|
} |
|
598
|
|
|
if ($i > 0) { |
|
599
|
|
|
try { |
|
600
|
|
|
|
|
601
|
|
|
$sth = $this->db->query(substr($query_delete,0,-1).")"); |
|
602
|
|
|
//$sth->execute(); |
|
603
|
|
|
} catch(PDOException $e) { |
|
604
|
|
|
return "error"; |
|
605
|
|
|
} |
|
606
|
|
|
} |
|
607
|
|
|
*/ |
|
608
|
|
|
return "success"; |
|
609
|
|
|
} |
|
610
|
|
|
} |
|
611
|
|
|
|
|
612
|
|
|
/** |
|
613
|
|
|
* Deletes all info in the table for an ident |
|
614
|
|
|
* |
|
615
|
|
|
* @return String success or false |
|
616
|
|
|
* |
|
617
|
|
|
*/ |
|
618
|
|
|
public function deleteLiveSpotterDataByIdent($ident) |
|
619
|
|
|
{ |
|
620
|
|
|
$ident = filter_var($ident, FILTER_SANITIZE_STRING); |
|
621
|
|
|
$query = 'DELETE FROM spotter_live WHERE ident = :ident'; |
|
622
|
|
|
|
|
623
|
|
|
try { |
|
624
|
|
|
|
|
625
|
|
|
$sth = $this->db->prepare($query); |
|
626
|
|
|
$sth->execute(array(':ident' => $ident)); |
|
627
|
|
|
} catch(PDOException $e) { |
|
628
|
|
|
return "error"; |
|
629
|
|
|
} |
|
630
|
|
|
|
|
631
|
|
|
return "success"; |
|
632
|
|
|
} |
|
633
|
|
|
|
|
634
|
|
|
/** |
|
635
|
|
|
* Deletes all info in the table for an id |
|
636
|
|
|
* |
|
637
|
|
|
* @return String success or false |
|
638
|
|
|
* |
|
639
|
|
|
*/ |
|
640
|
|
|
public function deleteLiveSpotterDataById($id) |
|
641
|
|
|
{ |
|
642
|
|
|
$id = filter_var($id, FILTER_SANITIZE_STRING); |
|
643
|
|
|
$query = 'DELETE FROM spotter_live WHERE flightaware_id = :id'; |
|
644
|
|
|
|
|
645
|
|
|
try { |
|
646
|
|
|
|
|
647
|
|
|
$sth = $this->db->prepare($query); |
|
648
|
|
|
$sth->execute(array(':id' => $id)); |
|
649
|
|
|
} catch(PDOException $e) { |
|
650
|
|
|
return "error"; |
|
651
|
|
|
} |
|
652
|
|
|
|
|
653
|
|
|
return "success"; |
|
654
|
|
|
} |
|
655
|
|
|
|
|
656
|
|
|
|
|
657
|
|
|
/** |
|
658
|
|
|
* Gets the aircraft ident within the last hour |
|
659
|
|
|
* |
|
660
|
|
|
* @return String the ident |
|
661
|
|
|
* |
|
662
|
|
|
*/ |
|
663
|
|
|
public function getIdentFromLastHour($ident) |
|
664
|
|
|
{ |
|
665
|
|
|
global $globalDBdriver, $globalTimezone; |
|
666
|
|
|
if ($globalDBdriver == 'mysql') { |
|
667
|
|
|
$query = 'SELECT spotter_live.ident FROM spotter_live |
|
668
|
|
|
WHERE spotter_live.ident = :ident |
|
669
|
|
|
AND spotter_live.date >= DATE_SUB(UTC_TIMESTAMP(),INTERVAL 1 HOUR) |
|
670
|
|
|
AND spotter_live.date < UTC_TIMESTAMP()'; |
|
671
|
|
|
$query_data = array(':ident' => $ident); |
|
672
|
|
|
} else { |
|
673
|
|
|
$query = "SELECT spotter_live.ident FROM spotter_live |
|
674
|
|
|
WHERE spotter_live.ident = :ident |
|
675
|
|
|
AND spotter_live.date >= now() AT TIME ZONE 'UTC' - INTERVAL '1 HOURS' |
|
676
|
|
|
AND spotter_live.date < now() AT TIME ZONE 'UTC'"; |
|
677
|
|
|
$query_data = array(':ident' => $ident); |
|
678
|
|
|
} |
|
679
|
|
|
|
|
680
|
|
|
$sth = $this->db->prepare($query); |
|
681
|
|
|
$sth->execute($query_data); |
|
682
|
|
|
$ident_result=''; |
|
683
|
|
|
while($row = $sth->fetch(PDO::FETCH_ASSOC)) |
|
684
|
|
|
{ |
|
685
|
|
|
$ident_result = $row['ident']; |
|
686
|
|
|
} |
|
687
|
|
|
return $ident_result; |
|
688
|
|
|
} |
|
689
|
|
|
|
|
690
|
|
|
/** |
|
691
|
|
|
* Check recent aircraft |
|
692
|
|
|
* |
|
693
|
|
|
* @return String the ident |
|
694
|
|
|
* |
|
695
|
|
|
*/ |
|
696
|
|
|
public function checkIdentRecent($ident) |
|
697
|
|
|
{ |
|
698
|
|
|
global $globalDBdriver, $globalTimezone; |
|
699
|
|
|
if ($globalDBdriver == 'mysql') { |
|
700
|
|
|
$query = 'SELECT spotter_live.ident, spotter_live.flightaware_id FROM spotter_live |
|
701
|
|
|
WHERE spotter_live.ident = :ident |
|
702
|
|
|
AND spotter_live.date >= DATE_SUB(UTC_TIMESTAMP(),INTERVAL 30 MINUTE)'; |
|
703
|
|
|
// AND spotter_live.date < UTC_TIMESTAMP()"; |
|
704
|
|
|
$query_data = array(':ident' => $ident); |
|
705
|
|
|
} else { |
|
706
|
|
|
$query = "SELECT spotter_live.ident, spotter_live.flightaware_id FROM spotter_live |
|
707
|
|
|
WHERE spotter_live.ident = :ident |
|
708
|
|
|
AND spotter_live.date >= now() AT TIME ZONE 'UTC' - INTERVAL '30 MINUTES'"; |
|
709
|
|
|
// AND spotter_live.date < now() AT TIME ZONE 'UTC'"; |
|
710
|
|
|
$query_data = array(':ident' => $ident); |
|
711
|
|
|
} |
|
712
|
|
|
|
|
713
|
|
|
$sth = $this->db->prepare($query); |
|
714
|
|
|
$sth->execute($query_data); |
|
715
|
|
|
$ident_result=''; |
|
716
|
|
|
while($row = $sth->fetch(PDO::FETCH_ASSOC)) |
|
717
|
|
|
{ |
|
718
|
|
|
$ident_result = $row['flightaware_id']; |
|
719
|
|
|
} |
|
720
|
|
|
return $ident_result; |
|
721
|
|
|
} |
|
722
|
|
|
|
|
723
|
|
|
/** |
|
724
|
|
|
* Check recent aircraft by ModeS |
|
725
|
|
|
* |
|
726
|
|
|
* @return String the ModeS |
|
727
|
|
|
* |
|
728
|
|
|
*/ |
|
729
|
|
|
public function checkModeSRecent($modes) |
|
730
|
|
|
{ |
|
731
|
|
|
global $globalDBdriver, $globalTimezone; |
|
732
|
|
|
if ($globalDBdriver == 'mysql') { |
|
733
|
|
|
$query = 'SELECT spotter_live.ModeS, spotter_live.flightaware_id FROM spotter_live |
|
734
|
|
|
WHERE spotter_live.ModeS = :modes |
|
735
|
|
|
AND spotter_live.date >= DATE_SUB(UTC_TIMESTAMP(),INTERVAL 30 MINUTE)'; |
|
736
|
|
|
// AND spotter_live.date < UTC_TIMESTAMP()"; |
|
737
|
|
|
$query_data = array(':modes' => $modes); |
|
738
|
|
|
} else { |
|
739
|
|
|
$query = "SELECT spotter_live.ModeS, spotter_live.flightaware_id FROM spotter_live |
|
740
|
|
|
WHERE spotter_live.ModeS = :modes |
|
741
|
|
|
AND spotter_live.date >= CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '30 MINUTE'"; |
|
742
|
|
|
// // AND spotter_live.date < CURRENT_TIMESTAMP AT TIME ZONE 'UTC'"; |
|
743
|
|
|
$query_data = array(':modes' => $modes); |
|
744
|
|
|
} |
|
745
|
|
|
|
|
746
|
|
|
$sth = $this->db->prepare($query); |
|
747
|
|
|
$sth->execute($query_data); |
|
748
|
|
|
$ident_result=''; |
|
749
|
|
|
while($row = $sth->fetch(PDO::FETCH_ASSOC)) |
|
750
|
|
|
{ |
|
751
|
|
|
//$ident_result = $row['spotter_live_id']; |
|
752
|
|
|
$ident_result = $row['flightaware_id']; |
|
753
|
|
|
} |
|
754
|
|
|
return $ident_result; |
|
755
|
|
|
} |
|
756
|
|
|
|
|
757
|
|
|
/** |
|
758
|
|
|
* Adds a new spotter data |
|
759
|
|
|
* |
|
760
|
|
|
* @param String $flightaware_id the ID from flightaware |
|
761
|
|
|
* @param String $ident the flight ident |
|
762
|
|
|
* @param String $aircraft_icao the aircraft type |
|
763
|
|
|
* @param String $departure_airport_icao the departure airport |
|
764
|
|
|
* @param String $arrival_airport_icao the arrival airport |
|
765
|
|
|
* @return String success or false |
|
766
|
|
|
* |
|
767
|
|
|
*/ |
|
768
|
|
|
public function addLiveSpotterData($flightaware_id = '', $ident = '', $aircraft_icao = '', $departure_airport_icao = '', $arrival_airport_icao = '', $latitude = '', $longitude = '', $waypoints = '', $altitude = '', $heading = '', $groundspeed = '', $date = '',$departure_airport_time = '', $arrival_airport_time = '', $squawk = '', $route_stop = '', $ModeS = '', $putinarchive = false,$registration = '',$pilot_id = '', $pilot_name = '', $verticalrate = '', $noarchive = false, $ground = false,$format_source = '', $source_name = '', $over_country = '') |
|
769
|
|
|
{ |
|
770
|
|
|
global $globalURL, $globalArchive, $globalDebug; |
|
771
|
|
|
$Common = new Common(); |
|
772
|
|
|
$SpotterArchive = new SpotterArchive($this->db); |
|
773
|
|
|
date_default_timezone_set('UTC'); |
|
774
|
|
|
|
|
775
|
|
|
//getting the airline information |
|
776
|
|
|
if ($ident != '') |
|
777
|
|
|
{ |
|
778
|
|
|
if (!is_string($ident)) |
|
779
|
|
|
{ |
|
780
|
|
|
return false; |
|
781
|
|
|
} |
|
782
|
|
|
} |
|
783
|
|
|
|
|
784
|
|
|
//getting the aircraft information |
|
785
|
|
|
if ($aircraft_icao != '') |
|
786
|
|
|
{ |
|
787
|
|
|
if (!is_string($aircraft_icao)) |
|
788
|
|
|
{ |
|
789
|
|
|
return false; |
|
790
|
|
|
} |
|
791
|
|
|
} |
|
792
|
|
|
//getting the departure airport information |
|
793
|
|
|
if ($departure_airport_icao != '') |
|
794
|
|
|
{ |
|
795
|
|
|
if (!is_string($departure_airport_icao)) |
|
796
|
|
|
{ |
|
797
|
|
|
return false; |
|
798
|
|
|
} |
|
799
|
|
|
} |
|
800
|
|
|
|
|
801
|
|
|
//getting the arrival airport information |
|
802
|
|
|
if ($arrival_airport_icao != '') |
|
803
|
|
|
{ |
|
804
|
|
|
if (!is_string($arrival_airport_icao)) |
|
805
|
|
|
{ |
|
806
|
|
|
return false; |
|
807
|
|
|
} |
|
808
|
|
|
} |
|
809
|
|
|
|
|
810
|
|
|
|
|
811
|
|
|
if ($latitude != '') |
|
812
|
|
|
{ |
|
813
|
|
|
if (!is_numeric($latitude)) |
|
814
|
|
|
{ |
|
815
|
|
|
return false; |
|
816
|
|
|
} |
|
817
|
|
|
} |
|
818
|
|
|
|
|
819
|
|
|
if ($longitude != '') |
|
820
|
|
|
{ |
|
821
|
|
|
if (!is_numeric($longitude)) |
|
822
|
|
|
{ |
|
823
|
|
|
return false; |
|
824
|
|
|
} |
|
825
|
|
|
} |
|
826
|
|
|
|
|
827
|
|
|
if ($waypoints != '') |
|
828
|
|
|
{ |
|
829
|
|
|
if (!is_string($waypoints)) |
|
830
|
|
|
{ |
|
831
|
|
|
return false; |
|
832
|
|
|
} |
|
833
|
|
|
} |
|
834
|
|
|
|
|
835
|
|
|
if ($altitude != '') |
|
836
|
|
|
{ |
|
837
|
|
|
if (!is_numeric($altitude)) |
|
838
|
|
|
{ |
|
839
|
|
|
return false; |
|
840
|
|
|
} |
|
841
|
|
|
} else $altitude = 0; |
|
842
|
|
|
|
|
843
|
|
|
if ($heading != '') |
|
844
|
|
|
{ |
|
845
|
|
|
if (!is_numeric($heading)) |
|
846
|
|
|
{ |
|
847
|
|
|
return false; |
|
848
|
|
|
} |
|
849
|
|
|
} else $heading = 0; |
|
850
|
|
|
|
|
851
|
|
|
if ($groundspeed != '') |
|
852
|
|
|
{ |
|
853
|
|
|
if (!is_numeric($groundspeed)) |
|
854
|
|
|
{ |
|
855
|
|
|
return false; |
|
856
|
|
|
} |
|
857
|
|
|
} else $groundspeed = 0; |
|
858
|
|
|
date_default_timezone_set('UTC'); |
|
859
|
|
|
if ($date == '') $date = date("Y-m-d H:i:s", time()); |
|
860
|
|
|
|
|
861
|
|
|
|
|
862
|
|
|
$flightaware_id = filter_var($flightaware_id,FILTER_SANITIZE_STRING); |
|
863
|
|
|
$ident = filter_var($ident,FILTER_SANITIZE_STRING); |
|
864
|
|
|
$aircraft_icao = filter_var($aircraft_icao,FILTER_SANITIZE_STRING); |
|
865
|
|
|
$departure_airport_icao = filter_var($departure_airport_icao,FILTER_SANITIZE_STRING); |
|
866
|
|
|
$arrival_airport_icao = filter_var($arrival_airport_icao,FILTER_SANITIZE_STRING); |
|
867
|
|
|
$latitude = filter_var($latitude,FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION); |
|
868
|
|
|
$longitude = filter_var($longitude,FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION); |
|
869
|
|
|
$waypoints = filter_var($waypoints,FILTER_SANITIZE_STRING); |
|
870
|
|
|
$altitude = filter_var($altitude,FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION); |
|
871
|
|
|
$heading = filter_var($heading,FILTER_SANITIZE_NUMBER_INT); |
|
872
|
|
|
$groundspeed = filter_var($groundspeed,FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION); |
|
873
|
|
|
$squawk = filter_var($squawk,FILTER_SANITIZE_NUMBER_INT); |
|
874
|
|
|
$route_stop = filter_var($route_stop,FILTER_SANITIZE_STRING); |
|
875
|
|
|
$ModeS = filter_var($ModeS,FILTER_SANITIZE_STRING); |
|
876
|
|
|
$pilot_id = filter_var($pilot_id,FILTER_SANITIZE_STRING); |
|
877
|
|
|
$pilot_name = filter_var($pilot_name,FILTER_SANITIZE_STRING); |
|
878
|
|
|
$format_source = filter_var($format_source,FILTER_SANITIZE_STRING); |
|
879
|
|
|
$source_name = filter_var($source_name,FILTER_SANITIZE_STRING); |
|
880
|
|
|
$over_country = filter_var($over_country,FILTER_SANITIZE_STRING); |
|
881
|
|
|
$verticalrate = filter_var($verticalrate,FILTER_SANITIZE_NUMBER_INT); |
|
882
|
|
|
|
|
883
|
|
|
$airline_name = ''; |
|
884
|
|
|
$airline_icao = ''; |
|
885
|
|
|
$airline_country = ''; |
|
886
|
|
|
$airline_type = ''; |
|
887
|
|
|
$aircraft_shadow = ''; |
|
888
|
|
|
$aircraft_type = ''; |
|
889
|
|
|
$aircraft_manufacturer = ''; |
|
890
|
|
|
|
|
891
|
|
|
|
|
892
|
|
|
|
|
893
|
|
|
$aircraft_name = ''; |
|
894
|
|
|
$departure_airport_name = ''; |
|
895
|
|
|
$departure_airport_city = ''; |
|
896
|
|
|
$departure_airport_country = ''; |
|
897
|
|
|
|
|
898
|
|
|
$arrival_airport_name = ''; |
|
899
|
|
|
$arrival_airport_city = ''; |
|
900
|
|
|
$arrival_airport_country = ''; |
|
901
|
|
|
|
|
902
|
|
|
|
|
903
|
|
|
if ($squawk == '' || $Common->isInteger($squawk) === false ) $squawk = NULL; |
|
904
|
|
|
if ($verticalrate == '' || $Common->isInteger($verticalrate) === false ) $verticalrate = NULL; |
|
905
|
|
|
if ($groundspeed == '' || $Common->isInteger($groundspeed) === false ) $groundspeed = 0; |
|
906
|
|
|
if ($heading == '' || $Common->isInteger($heading) === false ) $heading = 0; |
|
907
|
|
|
|
|
908
|
|
|
$query = 'INSERT INTO spotter_live (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, arrival_airport_icao, arrival_airport_name, arrival_airport_city, arrival_airport_country, latitude, longitude, waypoints, altitude, heading, ground_speed, date, departure_airport_time, arrival_airport_time, squawk, route_stop, ModeS, pilot_id, pilot_name, verticalrate, ground, format_source, source_name, over_country) |
|
909
|
|
|
VALUES (:flightaware_id,:ident,:registration,:airline_name,:airline_icao,:airline_country,:airline_type,:aircraft_icao,:aircraft_shadow,:aircraft_type,:aircraft_manufacturer,:departure_airport_icao,:departure_airport_name, :departure_airport_city, :departure_airport_country, :arrival_airport_icao, :arrival_airport_name, :arrival_airport_city, :arrival_airport_country, :latitude,:longitude,:waypoints,:altitude,:heading,:groundspeed,:date,:departure_airport_time,:arrival_airport_time,:squawk,:route_stop,:ModeS, :pilot_id, :pilot_name, :verticalrate, :ground, :format_source, :source_name, :over_country)'; |
|
910
|
|
|
|
|
911
|
|
|
$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_type' => $aircraft_type,':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,':arrival_airport_icao' => $arrival_airport_icao,':arrival_airport_name' => $arrival_airport_name,':arrival_airport_city' => $arrival_airport_city,':arrival_airport_country' => $arrival_airport_country,':latitude' => $latitude,':longitude' => $longitude, ':waypoints' => $waypoints,':altitude' => $altitude,':heading' => $heading,':groundspeed' => $groundspeed,':date' => $date, ':departure_airport_time' => $departure_airport_time,':arrival_airport_time' => $arrival_airport_time, ':squawk' => $squawk,':route_stop' => $route_stop,':ModeS' => $ModeS, ':pilot_id' => $pilot_id, ':pilot_name' => $pilot_name, ':verticalrate' => $verticalrate, ':format_source' => $format_source,':ground' => $ground, ':source_name' => $source_name, ':over_country' => $over_country); |
|
912
|
|
|
//$query_values = array(':flightaware_id' => $flightaware_id,':ident' => $ident, ':registration' => $registration,':airline_name' => $airline_array[0]['name'],':airline_icao' => $airline_array[0]['icao'],':airline_country' => $airline_array[0]['country'],':airline_type' => $airline_array[0]['type'],':aircraft_icao' => $aircraft_icao,':aircraft_type' => $aircraft_array[0]['type'],':aircraft_manufacturer' => $aircraft_array[0]['manufacturer'],':departure_airport_icao' => $departure_airport_icao,':arrival_airport_icao' => $arrival_airport_icao,':latitude' => $latitude,':longitude' => $longitude, ':waypoints' => $waypoints,':altitude' => $altitude,':heading' => $heading,':groundspeed' => $groundspeed,':date' => $date); |
|
913
|
|
|
try { |
|
914
|
|
|
|
|
915
|
|
|
$sth = $this->db->prepare($query); |
|
916
|
|
|
$sth->execute($query_values); |
|
917
|
|
|
} catch(PDOException $e) { |
|
918
|
|
|
return "error : ".$e->getMessage(); |
|
919
|
|
|
} |
|
920
|
|
|
if (isset($globalArchive) && $globalArchive && $putinarchive && !$noarchive) { |
|
921
|
|
|
if ($globalDebug) echo '(Add to SBS archive : '; |
|
922
|
|
|
$result = $SpotterArchive->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, $heading, $groundspeed, $squawk, $ModeS, $pilot_id, $pilot_name,$verticalrate,$format_source,$source_name, $over_country); |
|
923
|
|
|
if ($globalDebug) echo $result.')'; |
|
924
|
|
|
} |
|
925
|
|
|
return "success"; |
|
926
|
|
|
|
|
927
|
|
|
} |
|
928
|
|
|
|
|
929
|
|
|
public function getOrderBy() |
|
930
|
|
|
{ |
|
931
|
|
|
$orderby = array("aircraft_asc" => array("key" => "aircraft_asc", "value" => "Aircraft Type - ASC", "sql" => "ORDER BY spotter_live.aircraft_icao ASC"), "aircraft_desc" => array("key" => "aircraft_desc", "value" => "Aircraft Type - DESC", "sql" => "ORDER BY spotter_live.aircraft_icao DESC"),"manufacturer_asc" => array("key" => "manufacturer_asc", "value" => "Aircraft Manufacturer - ASC", "sql" => "ORDER BY spotter_live.aircraft_manufacturer ASC"), "manufacturer_desc" => array("key" => "manufacturer_desc", "value" => "Aircraft Manufacturer - DESC", "sql" => "ORDER BY spotter_live.aircraft_manufacturer DESC"),"airline_name_asc" => array("key" => "airline_name_asc", "value" => "Airline Name - ASC", "sql" => "ORDER BY spotter_live.airline_name ASC"), "airline_name_desc" => array("key" => "airline_name_desc", "value" => "Airline Name - DESC", "sql" => "ORDER BY spotter_live.airline_name DESC"), "ident_asc" => array("key" => "ident_asc", "value" => "Ident - ASC", "sql" => "ORDER BY spotter_live.ident ASC"), "ident_desc" => array("key" => "ident_desc", "value" => "Ident - DESC", "sql" => "ORDER BY spotter_live.ident DESC"), "airport_departure_asc" => array("key" => "airport_departure_asc", "value" => "Departure Airport - ASC", "sql" => "ORDER BY spotter_live.departure_airport_city ASC"), "airport_departure_desc" => array("key" => "airport_departure_desc", "value" => "Departure Airport - DESC", "sql" => "ORDER BY spotter_live.departure_airport_city DESC"), "airport_arrival_asc" => array("key" => "airport_arrival_asc", "value" => "Arrival Airport - ASC", "sql" => "ORDER BY spotter_live.arrival_airport_city ASC"), "airport_arrival_desc" => array("key" => "airport_arrival_desc", "value" => "Arrival Airport - DESC", "sql" => "ORDER BY spotter_live.arrival_airport_city DESC"), "date_asc" => array("key" => "date_asc", "value" => "Date - ASC", "sql" => "ORDER BY spotter_live.date ASC"), "date_desc" => array("key" => "date_desc", "value" => "Date - DESC", "sql" => "ORDER BY spotter_live.date DESC")); |
|
932
|
|
|
return $orderby; |
|
933
|
|
|
} |
|
934
|
|
|
|
|
935
|
|
|
} |
|
936
|
|
|
|
|
937
|
|
|
|
|
938
|
|
|
?> |
|
|
|
|
|
|
939
|
|
|
|
Ysurac /
FlightAirMap
Using a closing tag in PHP files that only contain PHP code is not recommended as you might accidentally add whitespace after the closing tag which would then be output by PHP. This can cause severe problems, for example headers cannot be sent anymore.
A simple precaution is to leave off the closing tag as it is not required, and it also has no negative effects whatsoever.