Example #1
0
 /**
  * Retrieves cities listing using GeoNames Service
  *
  * @param int $country_id The id of the country to retrieve cities for
  * Returns a JSON response
  */
 public function update_cities($country_id = 0)
 {
     $this->template = "";
     $this->auto_render = FALSE;
     // Get country ISO code from DB
     $country = ORM::factory('country', (int) $country_id);
     // No. of cities fetched
     $entries = 0;
     // Default payload to be returned to client as a JSON object
     $status_response = array("status" => "error", "response" => sprintf("%d %s %s", $entries, Kohana::lang('ui_admin.cities_loaded'), Kohana::lang('ui_admin.country_not_found')));
     if ($country->loaded) {
         $base_url = "http://overpass-api.de/api/";
         // Get the cities within the country info
         // Limited to 1000 items to avoid query time out
         /* This URL runs an Overpass API request using OverpassQL similar to:
         				[out:json][timeout:300];
         				area["admin_level"="2"]["name:en"~"^Germany"];
         				(
         				  node["place"="city"](area);
         				);
         				out body 1000;
         			*/
         $cities_url = $base_url . "interpreter?data=" . urlencode('[out:json][timeout:300];area["admin_level"="2"]["name:en"~"^' . $country->country . '"];(node["place"="city"](area););out body 1000;');
         // Fetch the cities
         $cities_client = new HttpClient($cities_url, 300);
         if (($response = $cities_client->execute()) !== FALSE) {
             // Decode the JSON responce
             $response = json_decode($response);
             $cities = isset($response->elements) ? $response->elements : array();
             // Only proceed if cities are returned
             if (count($cities) > 0) {
                 // Set the city count for the country
                 $country->cities = count($cities);
                 $country->save();
                 // Delete all the cities for the current country
                 ORM::factory('city')->where('country_id', $country->id)->delete_all();
                 // Manually construct the query (DB library can't do bulk inserts)
                 $query = sprintf("INSERT INTO %scity (`country_id`, `city`, `city_lat`, `city_lon`) VALUES ", $this->table_prefix);
                 $values = array();
                 // Create a database expression and use that to sanitize values
                 $values_expr = new Database_Expression("(:countryid, :city, :lat, :lon)");
                 // Add the freshly fetched cities
                 foreach ($cities as $city) {
                     // Skip nameless nodes or nodes with lat/lon
                     if (!isset($city->tags->name) or !$city->lat or !$city->lon) {
                         continue;
                     }
                     $values_expr->param(':countryid', $country->id);
                     $values_expr->param(':city', $city->tags->name);
                     $values_expr->param(':lat', $city->lat);
                     $values_expr->param(':lon', $city->lon);
                     $values[] = $values_expr->compile();
                 }
                 $query .= implode(",", $values);
                 // Batch insert
                 Database::instance()->query($query);
                 $entries = count($cities);
             }
             // Set the response payload
             $status_response['status'] = "success";
             $status_response['response'] = sprintf("%d %s", $entries, Kohana::lang('ui_admin.cities_loaded'));
         } else {
             // Geonames timeout
             $status_response['response'] = Kohana::lang('ui_admin.timeout');
         }
     }
     echo json_encode($status_response);
 }
Example #2
0
 /**
  * Get the number of reports by date for dashboard chart
  *
  * @param int $range No. of days in the past
  * @param int $user_id
  * @return array
  */
 public static function get_number_reports_by_date($range = NULL, $user_id = NULL)
 {
     // Table Prefix
     $table_prefix = Kohana::config('database.default.table_prefix');
     // Database instance
     $db = new Database();
     $params = array();
     $db->select('COUNT(id) as count', 'DATE(incident_date) as date', 'MONTH(incident_date) as month', 'DAY(incident_date) as day', 'YEAR(incident_date) as year')->from('incident')->groupby('date')->orderby('incident_date', 'ASC');
     if (!empty($user_id)) {
         $db->where('user_id', $user_id);
     }
     if (!empty($range)) {
         // Use Database_Expression to sanitize range param
         $range_expr = new Database_Expression('incident_date  >= DATE_SUB(CURDATE(), INTERVAL :range DAY)', array(':range' => (int) $range));
         $db->where($range_expr->compile());
     }
     $query = $db->get();
     $result = $query->result_array(FALSE);
     $array = array();
     foreach ($result as $row) {
         $timestamp = mktime(0, 0, 0, $row['month'], $row['day'], $row['year']) * 1000;
         $array["{$timestamp}"] = $row['count'];
     }
     return $array;
 }
Example #3
0
 /**
  * Given a validation object, updates the settings table
  * with the values assigned to its properties
  *
  * @param Validation $settings Validation object
  */
 public static function save_all(Validation $settings)
 {
     // For old schema throw error
     if (!self::new_schema()) {
         throw new Kohana_User_Exception('Settings database schema out of date');
     }
     // Get all the settings
     $all_settings = self::get_array();
     // Settings update query
     $query = sprintf("UPDATE `%ssettings` SET `value` = CASE `key` ", Kohana::config('database.default.table_prefix'));
     // Used for building the query clauses for the final query
     $values = array();
     $keys = array();
     // Modification date
     $settings['date_modify'] = date("Y-m-d H:i:s", time());
     // List of value to skip
     $skip = array('api_live');
     $value_expr = new Database_Expression("WHEN :key THEN :value ");
     foreach ($settings as $key => $value) {
         // If an item has been marked for skipping or is a
         // non-existent setting, skip current iteration
         if (in_array($key, $skip) or empty($key) or !array_key_exists($key, $all_settings)) {
             continue;
         }
         // Check for the timezone
         if ($key === 'timezone' and $value == 0) {
             $value = NULL;
         }
         $value_expr->param(':key', $key);
         $value_expr->param(':value', $value);
         $keys[] = $key;
         $values[] = $value_expr->compile();
     }
     // Construct the final query
     $query .= implode(" ", $values) . "END WHERE `key` IN :keys";
     // Performa batch update
     Database::instance()->query($query, array(':keys' => $keys));
 }