if (is_array($globalFilter)) $filter = array_merge($filter,$globalFilter);
34
$filter_query_join = '';
35
$filter_query_where = '';
36
foreach($filters as $flt) {
37
if (isset($flt['idents']) && !empty($flt['idents'])) {
38
if (isset($flt['source'])) {
39
$filter_query_join .= " INNER JOIN (SELECT fammarine_id FROM marine_output WHERE marine_output.ident IN ('".implode("','",$flt['idents'])."') AND marine_output.format_source IN ('".implode("','",$flt['source'])."')) spid ON spid.fammarine_id = marine_live.fammarine_id";
40
} else {
41
$filter_query_join .= " INNER JOIN (SELECT fammarine_id FROM marine_output WHERE marine_output.ident IN ('".implode("','",$flt['idents'])."')) spid ON spid.fammarine_id = marine_live.fammarine_id";
42
}
43
}
44
}
45
if (isset($filter['source']) && !empty($filter['source'])) {
46
$filter_query_where .= " AND format_source IN ('".implode("','",$filter['source'])."')";
47
}
48
if (isset($filter['ident']) && !empty($filter['ident'])) {
49
$filter_query_where .= " AND ident = '".$filter['ident']."'";
50
}
51
if (isset($filter['id']) && !empty($filter['id'])) {
52
$filter_query_where .= " AND fammarine_id = '".$filter['id']."'";
53
}
54
if (isset($filter['mmsi']) && !empty($filter['mmsi'])) {
55
$filter_query_where .= " AND mmsi = '".$filter['mmsi']."'";
56
}
57
if (isset($filter['race']) && !empty($filter['race'])) {
58
$filter_query_where .= " AND race_id = '".$filter['race']."'";
if ($orderby_query == '') $orderby_query= ' ORDER BY date DESC';
132
133
if (!isset($globalLiveInterval)) $globalLiveInterval = '200';
134
if ($globalDBdriver == 'mysql') {
135
//$query = "SELECT marine_live.* FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL 30 SECOND) <= l.date GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate";
136
$query = 'SELECT marine_live.* FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate'.$filter_query.$orderby_query;
137
} else {
138
$query = "SELECT marine_live.* FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= l.date GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate".$filter_query.$orderby_query;
FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate'.$filter_query." marine_live.latitude <> 0 AND marine_live.longitude <> 0";
FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= l.date GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate".$filter_query." marine_live.latitude <> '0' AND marine_live.longitude <> '0'";
FROM marine_archive INNER JOIN (SELECT fammarine_id FROM marine_live'.$filter_query.' DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval." SECOND) <= marine_live.date) l ON l.fammarine_id = marine_archive.fammarine_id ";
205
if ($usecoord) $query .= "AND marine_archive.latitude BETWEEN ".$minlat." AND ".$maxlat." AND marine_archive.longitude BETWEEN ".$minlong." AND ".$maxlong." ";
FROM marine_live'.$filter_query.' DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval." SECOND) <= marine_live.date ";
217
if ($usecoord) $query .= "AND marine_live.latitude BETWEEN ".$minlat." AND ".$maxlat." AND marine_live.longitude BETWEEN ".$minlong." AND ".$maxlong." ";
218
$query .= "AND marine_live.latitude <> '0' AND marine_live.longitude <> '0'
219
ORDER BY marine_live.fammarine_id, marine_live.date";
220
if ($limit) $query .= " LIMIT ".$globalMap3DMarinesLimit;
221
}
222
} else {
223
if (isset($globalArchive) && $globalArchive === TRUE) {
FROM marine_archive INNER JOIN (SELECT fammarine_id FROM marine_live".$filter_query." CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= marine_live.date) l ON l.fammarine_id = marine_archive.fammarine_id ";
226
if ($usecoord) $query .= "AND marine_archive.latitude BETWEEN ".$minlat." AND ".$maxlat." AND marine_archive.longitude BETWEEN ".$minlong." AND ".$maxlong." ";
FROM marine_live".$filter_query." CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= marine_live.date ";
237
if ($usecoord) $query .= "AND marine_live.latitude BETWEEN ".$minlat." AND ".$maxlat." AND marine_live.longitude BETWEEN ".$minlong." AND ".$maxlong." ";
238
$query .= "AND marine_live.latitude <> '0' AND marine_live.longitude <> '0'
239
ORDER BY marine_live.fammarine_id, marine_live.date";
240
if ($limit) $query .= " LIMIT ".$globalMap3DMarinesLimit;
if (!isset($globalLiveInterval)) $globalLiveInterval = '200';
266
if ($globalDBdriver == 'mysql') {
267
$query = 'SELECT COUNT(DISTINCT marine_live.fammarine_id) as nb FROM marine_live'.$filter_query.' DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= date';
268
} else {
269
$query = "SELECT COUNT(DISTINCT marine_live.fammarine_id) as nb FROM marine_live".$filter_query." CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= date";
270
}
271
try {
272
$sth = $this->db->prepare($query);
273
$sth->execute();
274
} catch(PDOException $e) {
275
echo $e->getMessage();
276
die;
277
}
278
$result = $sth->fetch(PDO::FETCH_ASSOC);
279
$sth->closeCursor();
280
return $result['nb'];
281
}
282
283
/**
284
* Gets all the spotter information based on the latest data entry and coord
285
*
286
* @return Array the spotter information
287
*
288
*/
289
public function getLiveMarineDatabyCoord($coord, $filter = array())
290
{
291
global $globalDBdriver, $globalLiveInterval;
292
$Marine = new Marine($this->db);
293
if (!isset($globalLiveInterval)) $globalLiveInterval = '200';
$query = 'SELECT marine_live.* FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval.' SECOND) <= l.date GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate AND marine_live.latitude BETWEEN '.$minlat.' AND '.$maxlat.' AND marine_live.longitude BETWEEN '.$minlong.' AND '.$maxlong.' GROUP BY marine_live.fammarine_id ORDER BY date DESC'.$filter_query;
304
} else {
305
$query = "SELECT marine_live.* FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE NOW() at time zone 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= l.date GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate AND marine_live.latitude BETWEEN ".$minlat." AND ".$maxlat." AND marine_live.longitude BETWEEN ".$minlong." AND ".$maxlong." GROUP BY marine_live.fammarine_id ORDEr BY date DESC".$filter_query;
306
}
307
$spotter_array = $Marine->getDataFromDB($query);
308
return $spotter_array;
309
}
310
311
/**
312
* Gets all the spotter information based on the latest data entry and coord
313
*
314
* @return Array the spotter information
315
*
316
*/
317
public function getMinLiveMarineDatabyCoord($coord, $filter = array())
318
{
319
global $globalDBdriver, $globalLiveInterval, $globalArchive;
320
$Marine = new Marine($this->db);
321
if (!isset($globalLiveInterval)) $globalLiveInterval = '200';
FROM marine_live'.$filter_query.' DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$globalLiveInterval." SECOND) <= marine_live.date AND marine_live.latitude <> '0' AND marine_live.longitude <> '0' AND marine_live.latitude BETWEEN ".$minlat.' AND '.$maxlat.' AND marine_live.longitude BETWEEN '.$minlong.' AND '.$maxlong."
334
ORDER BY marine_live.fammarine_id, marine_live.date";
FROM marine_live".$filter_query." CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$globalLiveInterval." SECONDS' <= marine_live.date AND marine_live.latitude <> '0' AND marine_live.longitude <> '0' AND marine_live.latitude BETWEEN ".$minlat." AND ".$maxlat." AND marine_live.longitude BETWEEN ".$minlong." AND ".$maxlong."
338
ORDER BY marine_live.fammarine_id, marine_live.date";
339
}
340
*/
341
if ($globalDBdriver == 'mysql') {
342
if (isset($globalArchive) && $globalArchive === TRUE) {
$query = 'SELECT marine_live.* FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE l.ident = :ident GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate ORDER BY marine_live.date DESC';
$query = 'SELECT marine_live.* FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE l.ident = :ident AND l.date <= :date GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate ORDER BY marine_live.date DESC';
$query = 'SELECT marine_live.* FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE l.mmsi = :mmsi AND l.date <= :date GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate ORDER BY marine_live.date DESC';
* Gets last spotter information based on a particular callsign
501
*
502
* @return Array the spotter information
503
*
504
*/
505
public function getLastLiveMarineDataById($id)
506
{
507
$Marine = new Marine($this->db);
508
date_default_timezone_set('UTC');
509
510
$id = filter_var($id, FILTER_SANITIZE_STRING);
511
$query = 'SELECT marine_live.* FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE l.fammarine_id = :id GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate ORDER BY marine_live.date DESC';
* Gets last spotter information based on a particular callsign
520
*
521
* @return Array the spotter information
522
*
523
*/
524
public function getDateLiveMarineDataById($id,$date)
525
{
526
$Marine = new Marine($this->db);
527
date_default_timezone_set('UTC');
528
529
$id = filter_var($id, FILTER_SANITIZE_STRING);
530
$query = 'SELECT marine_live.* FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l WHERE l.fammarine_id = :id AND l.date <= :date GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate ORDER BY marine_live.date DESC';
//$query = "DELETE FROM marine_live WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL 30 MINUTE) >= marine_live.date";
606
$query = 'DELETE FROM marine_live WHERE DATE_SUB(UTC_TIMESTAMP(),INTERVAL 9 HOUR) >= marine_live.date';
607
//$query = "DELETE FROM marine_live WHERE marine_live.id IN (SELECT marine_live.id FROM marine_live INNER JOIN (SELECT l.fammarine_id, max(l.date) as maxdate FROM marine_live l GROUP BY l.fammarine_id) s on marine_live.fammarine_id = s.fammarine_id AND marine_live.date = s.maxdate AND DATE_SUB(UTC_TIMESTAMP(),INTERVAL 1 HOUR) >= marine_live.date)";
608
} else {
609
$query = "DELETE FROM marine_live WHERE NOW() AT TIME ZONE 'UTC' - INTERVAL '9 HOURS' >= marine_live.date";
610
}
611
612
try {
613
614
$sth = $this->db->prepare($query);
615
$sth->execute();
616
} catch(PDOException $e) {
617
return "error";
618
}
619
620
return "success";
621
}
622
623
/**
624
* Deletes all info in the table for aircraft not seen since 2 HOUR
625
*
626
* @return String success or false
627
*
628
*/
629
public function deleteLiveMarineDataNotUpdated()
630
{
631
global $globalDBdriver, $globalDebug;
632
if ($globalDBdriver == 'mysql') {
633
//$query = 'SELECT fammarine_id FROM marine_live WHERE DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 HOUR) >= marine_live.date AND marine_live.fammarine_id NOT IN (SELECT fammarine_id FROM marine_live WHERE DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 HOUR) < marine_live.date) LIMIT 800 OFFSET 0';
634
$query = "SELECT marine_live.fammarine_id FROM marine_live INNER JOIN (SELECT fammarine_id,MAX(date) as max_date FROM marine_live GROUP BY fammarine_id) s ON s.fammarine_id = marine_live.fammarine_id AND DATE_SUB(UTC_TIMESTAMP(), INTERVAL 2 HOUR) >= s.max_date LIMIT 1200 OFFSET 0";
635
try {
636
637
$sth = $this->db->prepare($query);
638
$sth->execute();
639
} catch(PDOException $e) {
640
return "error";
641
}
642
$query_delete = 'DELETE FROM marine_live WHERE fammarine_id IN (';
//$query = "SELECT fammarine_id FROM marine_live WHERE NOW() AT TIME ZONE 'UTC' - INTERVAL '9 HOURS' >= marine_live.date AND marine_live.fammarine_id NOT IN (SELECT fammarine_id FROM marine_live WHERE NOW() AT TIME ZONE 'UTC' - INTERVAL '9 HOURS' < marine_live.date) LIMIT 800 OFFSET 0";
676
//$query = "SELECT marine_live.fammarine_id FROM marine_live INNER JOIN (SELECT fammarine_id,MAX(date) as max_date FROM marine_live GROUP BY fammarine_id) s ON s.fammarine_id = marine_live.fammarine_id AND NOW() AT TIME ZONE 'UTC' - INTERVAL '2 HOURS' >= s.max_date LIMIT 800 OFFSET 0";
677
$query = "DELETE FROM marine_live WHERE fammarine_id IN (SELECT marine_live.fammarine_id FROM marine_live INNER JOIN (SELECT fammarine_id,MAX(date) as max_date FROM marine_live GROUP BY fammarine_id) s ON s.fammarine_id = marine_live.fammarine_id AND NOW() AT TIME ZONE 'UTC' - INTERVAL '2 HOURS' >= s.max_date LIMIT 800 OFFSET 0)";
678
try {
679
680
$sth = $this->db->prepare($query);
681
$sth->execute();
682
} catch(PDOException $e) {
683
return "error";
684
}
685
/* $query_delete = "DELETE FROM marine_live WHERE fammarine_id IN (";
Both the $myVar assignment in line 1 and the $higher assignment in line 2
are dead. The first because $myVar is never used and the second because
$higher is always overwritten for every possible time line.
Loading history...
778
return $sth->fetchAll(PDO::FETCH_ASSOC);
779
}
780
781
/**
782
* Gets the aircraft ident within the last hour
783
*
784
* @return String the ident
785
*
786
*/
787
public function getIdentFromLastHour($ident)
788
{
789
global $globalDBdriver, $globalTimezone;
790
if ($globalDBdriver == 'mysql') {
791
$query = 'SELECT marine_live.ident FROM marine_live
792
WHERE marine_live.ident = :ident
793
AND marine_live.date >= DATE_SUB(UTC_TIMESTAMP(),INTERVAL 1 HOUR)
794
AND marine_live.date < UTC_TIMESTAMP()';
795
$query_data = array(':ident' => $ident);
796
} else {
797
$query = "SELECT marine_live.ident FROM marine_live
798
WHERE marine_live.ident = :ident
799
AND marine_live.date >= now() AT TIME ZONE 'UTC' - INTERVAL '1 HOURS'
800
AND marine_live.date < now() AT TIME ZONE 'UTC'";
801
$query_data = array(':ident' => $ident);
802
}
803
804
$sth = $this->db->prepare($query);
805
$sth->execute($query_data);
806
$ident_result='';
807
while($row = $sth->fetch(PDO::FETCH_ASSOC))
808
{
809
$ident_result = $row['ident'];
810
}
811
return $ident_result;
812
}
813
814
/**
815
* Check recent aircraft
816
*
817
* @return String the ident
818
*
819
*/
820
public function checkIdentRecent($ident)
821
{
822
global $globalDBdriver, $globalTimezone;
823
if ($globalDBdriver == 'mysql') {
824
$query = 'SELECT marine_live.ident, marine_live.fammarine_id FROM marine_live
825
WHERE marine_live.ident = :ident
826
AND marine_live.date >= DATE_SUB(UTC_TIMESTAMP(),INTERVAL 30 MINUTE)';
827
// AND marine_live.date < UTC_TIMESTAMP()";
828
$query_data = array(':ident' => $ident);
829
} else {
830
$query = "SELECT marine_live.ident, marine_live.fammarine_id FROM marine_live
831
WHERE marine_live.ident = :ident
832
AND marine_live.date >= now() AT TIME ZONE 'UTC' - INTERVAL '30 MINUTES'";
833
// AND marine_live.date < now() AT TIME ZONE 'UTC'";
834
$query_data = array(':ident' => $ident);
835
}
836
837
$sth = $this->db->prepare($query);
838
$sth->execute($query_data);
839
$ident_result='';
840
while($row = $sth->fetch(PDO::FETCH_ASSOC))
841
{
842
$ident_result = $row['fammarine_id'];
843
}
844
return $ident_result;
845
}
846
847
/**
848
* Check recent aircraft by id
849
*
850
* @return String the ident
851
*
852
*/
853
public function checkIdRecent($id)
854
{
855
global $globalDBdriver, $globalTimezone;
856
if ($globalDBdriver == 'mysql') {
857
$query = 'SELECT marine_live.ident, marine_live.fammarine_id FROM marine_live
858
WHERE marine_live.fammarine_id = :id
859
AND marine_live.date >= DATE_SUB(UTC_TIMESTAMP(),INTERVAL 10 HOUR)';
860
// AND marine_live.date < UTC_TIMESTAMP()";
861
$query_data = array(':id' => $id);
862
} else {
863
$query = "SELECT marine_live.ident, marine_live.fammarine_id FROM marine_live
864
WHERE marine_live.fammarine_id = :id
865
AND marine_live.date >= now() AT TIME ZONE 'UTC' - INTERVAL '10 HOUR'";
866
// AND marine_live.date < now() AT TIME ZONE 'UTC'";
867
$query_data = array(':id' => $id);
868
}
869
870
$sth = $this->db->prepare($query);
871
$sth->execute($query_data);
872
$ident_result='';
873
while($row = $sth->fetch(PDO::FETCH_ASSOC))
874
{
875
$ident_result = $row['fammarine_id'];
876
}
877
return $ident_result;
878
}
879
880
/**
881
* Check recent aircraft by mmsi
882
*
883
* @return String the ident
884
*
885
*/
886
public function checkMMSIRecent($mmsi)
887
{
888
global $globalDBdriver, $globalTimezone;
889
if ($globalDBdriver == 'mysql') {
890
$query = 'SELECT marine_live.fammarine_id FROM marine_live
891
WHERE marine_live.mmsi = :mmsi
892
AND marine_live.date >= DATE_SUB(UTC_TIMESTAMP(),INTERVAL 10 HOUR)';
893
// AND marine_live.date < UTC_TIMESTAMP()";
894
$query_data = array(':mmsi' => $mmsi);
895
} else {
896
$query = "SELECT marine_live.fammarine_id FROM marine_live
897
WHERE marine_live.mmsi = :mmsi
898
AND marine_live.date >= now() AT TIME ZONE 'UTC' - INTERVAL '10 HOUR'";
899
// AND marine_live.date < now() AT TIME ZONE 'UTC'";
900
$query_data = array(':mmsi' => $mmsi);
901
}
902
903
$sth = $this->db->prepare($query);
904
$sth->execute($query_data);
905
$ident_result='';
906
while($row = $sth->fetch(PDO::FETCH_ASSOC))
907
{
908
$ident_result = $row['fammarine_id'];
909
}
910
return $ident_result;
911
}
912
913
/**
914
* Adds a new spotter data
915
*
916
* @param String $fammarine_id the ID from flightaware
917
* @param String $ident the flight ident
918
* @param String $aircraft_icao the aircraft type
919
* @param String $departure_airport_icao the departure airport
920
* @param String $arrival_airport_icao the arrival airport
Ysurac /
FlightAirMap
Ycarus
This check looks for variable assignements that are either overwritten by other assignments or where the variable is not used subsequently.
Both the
$myVarassignment in line 1 and the$higherassignment in line 2 are dead. The first because$myVaris never used and the second because$higheris always overwritten for every possible time line.