/** * 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); }
/** * 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)); }