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