Ysurac /
FlightAirMap
| @@ 3941-3979 (lines=39) @@ | ||
| 3938 | * @return Array the airline country list |
|
| 3939 | * |
|
| 3940 | */ |
|
| 3941 | public function countAllFlightOverCountries($limit = true,$olderthanmonths = 0,$sincedate = '') |
|
| 3942 | { |
|
| 3943 | global $globalDBdriver; |
|
| 3944 | /* |
|
| 3945 | $query = "SELECT c.name, c.iso3, c.iso2, count(c.name) as nb |
|
| 3946 | FROM countries c, spotter_output s |
|
| 3947 | WHERE Within(GeomFromText(CONCAT('POINT(',s.longitude,' ',s.latitude,')')), ogc_geom) "; |
|
| 3948 | */ |
|
| 3949 | $query = "SELECT c.name, c.iso3, c.iso2, count(c.name) as nb |
|
| 3950 | FROM countries c, spotter_live s |
|
| 3951 | WHERE c.iso2 = s.over_country "; |
|
| 3952 | if ($olderthanmonths > 0) { |
|
| 3953 | if ($globalDBdriver == 'mysql') { |
|
| 3954 | $query .= 'AND date < DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$olderthanmonths.' MONTH) '; |
|
| 3955 | } else { |
|
| 3956 | $query .= "AND date < CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$olderthanmonths." MONTHS'"; |
|
| 3957 | } |
|
| 3958 | } |
|
| 3959 | if ($sincedate != '') $query .= "AND date > '".$sincedate."' "; |
|
| 3960 | $query .= "GROUP BY c.name ORDER BY nb DESC"; |
|
| 3961 | if ($limit) $query .= " LIMIT 10 OFFSET 0"; |
|
| 3962 | ||
| 3963 | ||
| 3964 | $sth = $this->db->prepare($query); |
|
| 3965 | $sth->execute(); |
|
| 3966 | ||
| 3967 | $flight_array = array(); |
|
| 3968 | $temp_array = array(); |
|
| 3969 | ||
| 3970 | while($row = $sth->fetch(PDO::FETCH_ASSOC)) |
|
| 3971 | { |
|
| 3972 | $temp_array['flight_count'] = $row['nb']; |
|
| 3973 | $temp_array['flight_country'] = $row['name']; |
|
| 3974 | $temp_array['flight_country_iso3'] = $row['iso3']; |
|
| 3975 | $temp_array['flight_country_iso2'] = $row['iso2']; |
|
| 3976 | $flight_array[] = $temp_array; |
|
| 3977 | } |
|
| 3978 | return $flight_array; |
|
| 3979 | } |
|
| 3980 | ||
| 3981 | ||
| 3982 | /** |
|
| @@ 861-899 (lines=39) @@ | ||
| 858 | * @return Array the airline country list |
|
| 859 | * |
|
| 860 | */ |
|
| 861 | public function countAllFlightOverCountries($limit = true,$olderthanmonths = 0,$sincedate = '') |
|
| 862 | { |
|
| 863 | global $globalDBdriver; |
|
| 864 | /* |
|
| 865 | $query = "SELECT c.name, c.iso3, c.iso2, count(c.name) as nb |
|
| 866 | FROM countries c, spotter_archive s |
|
| 867 | WHERE Within(GeomFromText(CONCAT('POINT(',s.longitude,' ',s.latitude,')')), ogc_geom) "; |
|
| 868 | */ |
|
| 869 | $query = "SELECT c.name, c.iso3, c.iso2, count(c.name) as nb |
|
| 870 | FROM countries c, spotter_archive s |
|
| 871 | WHERE c.iso2 = s.over_country "; |
|
| 872 | if ($olderthanmonths > 0) { |
|
| 873 | if ($globalDBdriver == 'mysql') { |
|
| 874 | $query .= 'AND date < DATE_SUB(UTC_TIMESTAMP(),INTERVAL '.$olderthanmonths.' MONTH) '; |
|
| 875 | } else { |
|
| 876 | $query .= "AND date < CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '".$olderthanmonths." MONTHS'"; |
|
| 877 | } |
|
| 878 | } |
|
| 879 | if ($sincedate != '') $query .= "AND date > '".$sincedate."' "; |
|
| 880 | $query .= "GROUP BY c.name, c.iso3, c.iso2 ORDER BY nb DESC"; |
|
| 881 | if ($limit) $query .= " LIMIT 0,10"; |
|
| 882 | ||
| 883 | ||
| 884 | $sth = $this->db->prepare($query); |
|
| 885 | $sth->execute(); |
|
| 886 | ||
| 887 | $flight_array = array(); |
|
| 888 | $temp_array = array(); |
|
| 889 | ||
| 890 | while($row = $sth->fetch(PDO::FETCH_ASSOC)) |
|
| 891 | { |
|
| 892 | $temp_array['flight_count'] = $row['nb']; |
|
| 893 | $temp_array['flight_country'] = $row['name']; |
|
| 894 | $temp_array['flight_country_iso3'] = $row['iso3']; |
|
| 895 | $temp_array['flight_country_iso2'] = $row['iso2']; |
|
| 896 | $flight_array[] = $temp_array; |
|
| 897 | } |
|
| 898 | return $flight_array; |
|
| 899 | } |
|
| 900 | ||
| 901 | } |
|
| 902 | ?> |
|
