/**
* Tests reports::fetch_incidents()
* @test
*
* This tests compares the output SQL of fetch_incidents against a pre-defined SQL
* statement based on dummy values. The objective of this test is to check whether
* reports::fetch_incidents processes the parameters property
*/
public function testFetchIncidents()
{
// Get random location and fetch the latitude and longitude
$location = ORM::factory('location', testutils::get_random_id('location'));
$longitude = $location->longitude;
$latitude = $location->latitude;
// Build the list of HTTP_GET parameters
$filter_params = array('c' => array(3, 4, 5), 'start_loc' => $latitude . "," . $longitude, 'radius' => '20', 'mode' => array(1, 2), 'm' => array(1), 'from' => '07/07/2011', 'to' => '07/21/2011', 'v' => 1);
// Add the report filter params to the list of HTTP_GET parameters
$_GET = array_merge($_GET, $filter_params);
// Get the incidents
$incidents = reports::fetch_incidents();
// Get the table prefix
$table_prefix = Kohana::config('database.default.table_prefix');
// Expected SQL statement; based on the $filter_params above
$expected_sql = "SELECT DISTINCT i.id incident_id, i.incident_title, i.incident_description, i.incident_date, " . "i.incident_mode, i.incident_active, i.incident_verified, i.location_id, l.country_id, l.location_name, l.latitude, l.longitude " . ", ((ACOS(SIN(" . $latitude . " * PI() / 180) * SIN(l.`latitude` * PI() / 180) + COS(" . $latitude . " * PI() / 180) * " . "\tCOS(l.`latitude` * PI() / 180) * COS((" . $longitude . " - l.`longitude`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance " . "FROM " . $table_prefix . "incident i " . "INNER JOIN " . $table_prefix . "location l ON (i.location_id = l.id) " . "INNER JOIN " . $table_prefix . "incident_category ic ON (ic.incident_id = i.id) " . "INNER JOIN " . $table_prefix . "category c ON (ic.category_id = c.id) " . "WHERE i.incident_active = 1 " . "AND (c.id IN (" . implode(",", $filter_params['c']) . ") OR c.parent_id IN (" . implode(",", $filter_params['c']) . ")) " . "AND c.category_visible = 1 " . "AND i.incident_mode IN (" . implode(",", $filter_params['mode']) . ") " . "AND i.incident_date >= \"2011-07-07\" " . "AND i.incident_date <= \"2011-07-21\" " . "AND i.id IN (SELECT DISTINCT incident_id FROM " . $table_prefix . "media WHERE media_type IN (" . implode(",", $filter_params['m']) . ")) " . "AND i.incident_verified IN (" . $filter_params['v'] . ") " . "HAVING distance <= " . $filter_params['radius'] . " " . "ORDER BY i.incident_date DESC ";
// Test the expected SQL against the returned
$this->assertEquals($expected_sql, $incidents->sql());
// Garbage collection
unset($location, $latitude, $longitude, $incidents, $filter_params);
}
/**
* Tests reports::fetch_incidents()
* @test
*
* This tests compares the output SQL of fetch_incidents against a pre-defined SQL
* statement based on dummy values. The objective of this test is to check whether
* reports::fetch_incidents processes the parameters property
*/
public function testFetchIncidents()
{
// Get random location and fetch the latitude and longitude
$location = ORM::factory('location', testutils::get_random_id('location'));
$longitude = $location->longitude;
$latitude = $location->latitude;
// Build the list of HTTP_GET parameters
$filter_params = array('c' => array(3, 4, 5), 'start_loc' => $latitude . "," . $longitude, 'radius' => '20', 'mode' => array(1, 2), 'm' => array(1), 'from' => '07/07/2011', 'to' => '07/21/2011', 'v' => 1);
// Add the report filter params to the list of HTTP_GET parameters
$_GET = array_merge($_GET, $filter_params);
// Get the incidents
$incidents = reports::fetch_incidents();
// Get the table prefix
$table_prefix = Kohana::config('database.default.table_prefix');
// Expected SQL statement; based on the $filter_params above
// Distance calculation deets:
// 60 = nautical miles per degree of latitude, 1.1515 miles in every nautical mile, 1.609344 km = 1 km
// more details about the math here: http://sgowtham.net/ramblings/2009/08/04/php-calculating-distance-between-two-locations-given-their-gps-coordinates/
$expected_sql = "SELECT DISTINCT i.id incident_id, i.incident_title, i.incident_description, i.incident_date, " . "i.incident_mode, i.incident_active, i.incident_verified, i.location_id, l.country_id, l.location_name, l.latitude, l.longitude " . ", ((ACOS(SIN(" . $latitude . " * PI() / 180) * SIN(l.`latitude` * PI() / 180) + COS(" . $latitude . " * PI() / 180) * " . "\tCOS(l.`latitude` * PI() / 180) * COS((" . $longitude . " - l.`longitude`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) AS distance " . "FROM " . $table_prefix . "incident i " . "LEFT JOIN " . $table_prefix . "location l ON (i.location_id = l.id) " . "LEFT JOIN " . $table_prefix . "incident_category ic ON (ic.incident_id = i.id) " . "LEFT JOIN " . $table_prefix . "category c ON (ic.category_id = c.id) " . "WHERE i.incident_active = 1 " . "AND (c.id IN (" . implode(",", $filter_params['c']) . ") OR c.parent_id IN (" . implode(",", $filter_params['c']) . ")) " . "AND c.category_visible = 1 " . "AND i.incident_mode IN (" . implode(",", $filter_params['mode']) . ") " . "AND i.incident_date >= \"2011-07-07 00:00:00\" " . "AND i.incident_date <= \"2011-07-21 23:59:59\" " . "AND i.id IN (SELECT DISTINCT incident_id FROM " . $table_prefix . "media WHERE media_type IN (" . implode(",", $filter_params['m']) . ")) " . "AND i.incident_verified IN (" . $filter_params['v'] . ") " . "HAVING distance <= " . $filter_params['radius'] . " " . "ORDER BY i.incident_date DESC ";
// Test the expected SQL against the returned
$this->assertEquals($expected_sql, $incidents->sql());
// Garbage collection
unset($location, $latitude, $longitude, $incidents, $filter_params);
}
/**
* Helper method to load the report listing view
*/
private function _get_report_listing_view($locale = '')
{
// Check if the local is empty
if (empty($locale)) {
$locale = Kohana::config('locale.language.0');
}
// Load the report listing view
$report_listing = new View('reports_listing');
// Fetch all incidents
$all_incidents = reports::fetch_incidents();
// Pagination
$pagination = new Pagination(array('style' => 'front-end-reports', 'query_string' => 'page', 'items_per_page' => (int) Kohana::config('settings.items_per_page'), 'total_items' => $all_incidents->count()));
// Reports
$incidents = Incident_Model::get_incidents(reports::$params, $pagination);
// Swap out category titles with their proper localizations using an array (cleaner way to do this?)
$localized_categories = array();
foreach ($incidents as $incident) {
$incident = ORM::factory('incident', $incident->incident_id);
foreach ($incident->category as $category) {
$ct = (string) $category->category_title;
if (!isset($localized_categories[$ct])) {
$translated_title = Category_Lang_Model::category_title($category->id, $locale);
$localized_categories[$ct] = $category->category_title;
if ($translated_title) {
$localized_categories[$ct] = $translated_title;
}
}
}
}
// Set the view content
$report_listing->incidents = $incidents;
$report_listing->localized_categories = $localized_categories;
//Set default as not showing pagination. Will change below if necessary.
$report_listing->pagination = "";
// Pagination and Total Num of Report Stats
$plural = $pagination->total_items == 1 ? "" : "s";
// Set the next and previous page numbers
$report_listing->next_page = $pagination->next_page;
$report_listing->previous_page = $pagination->previous_page;
if ($pagination->total_items > 0) {
$current_page = $pagination->sql_offset / $pagination->items_per_page + 1;
$total_pages = ceil($pagination->total_items / $pagination->items_per_page);
if ($total_pages >= 1) {
$report_listing->pagination = $pagination;
// Show the total of report
// @todo This is only specific to the frontend reports theme
$report_listing->stats_breadcrumb = $pagination->current_first_item . '-' . $pagination->current_last_item . ' of ' . $pagination->total_items . ' ' . Kohana::lang('ui_main.reports');
} else {
// If we don't want to show pagination
$report_listing->stats_breadcrumb = $pagination->total_items . ' ' . Kohana::lang('ui_admin.reports');
}
} else {
$report_listing->stats_breadcrumb = '(' . $pagination->total_items . ' report' . $plural . ')';
}
// Return
return $report_listing;
}
/**
* Lists the reports.
*
* @param int $page
*/
public function index($page = 1)
{
// If user doesn't have access, redirect to dashboard
if (!$this->auth->has_permission("reports_view")) {
url::redirect(url::site() . 'admin/dashboard');
}
$this->template->content = new View('admin/reports/main');
$this->template->content->title = Kohana::lang('ui_admin.reports');
// Database table prefix
$table_prefix = Kohana::config('database.default.table_prefix');
// Hook into the event for the reports::fetch_incidents() method
Event::add('ushahidi_filter.fetch_incidents_set_params', array($this, '_add_incident_filters'));
$status = "0";
if (!empty($_GET['status'])) {
$status = $_GET['status'];
if (strtolower($status) == 'a') {
array_push($this->params, 'i.incident_active = 0');
} elseif (strtolower($status) == 'v') {
array_push($this->params, 'i.incident_verified = 0');
} elseif (strtolower($status) == 'o') {
array_push($this->params, '(ic.category_id IS NULL)');
} elseif (strtolower($status) != 'search') {
$status = "0";
}
}
// Get Search Keywords (If Any)
if (isset($_GET['k'])) {
// Brute force input sanitization
// Phase 1 - Strip the search string of all non-word characters
$keyword_raw = isset($_GET['k']) ? preg_replace('#/\\w+/#', '', $_GET['k']) : "";
// Strip any HTML tags that may have been missed in Phase 1
$keyword_raw = strip_tags($keyword_raw);
// Phase 3 - Invoke Kohana's XSS cleaning mechanism just incase an outlier wasn't caught
// in the first 2 steps
$keyword_raw = $this->input->xss_clean($keyword_raw);
$filter = " (" . $this->_get_searchstring($keyword_raw) . ")";
array_push($this->params, $filter);
} else {
$keyword_raw = "";
}
$this->template->content->search_form = $this->_search_form();
$this->template->content->search_form->keywords = $keyword_raw;
// Handler sort/order fields
$order_field = 'date';
$sort = 'DESC';
if (isset($_GET['order'])) {
$order_field = html::escape($_GET['order']);
}
if (isset($_GET['sort'])) {
$sort = strtoupper($_GET['sort']) == 'ASC' ? 'ASC' : 'DESC';
}
// Check, has the form been submitted?
$form_error = FALSE;
$errors = array();
$form_saved = FALSE;
$form_action = "";
if ($_POST) {
$post = Validation::factory($_POST);
// Add some filters
$post->pre_filter('trim', TRUE);
// Add some rules, the input field, followed by a list of checks,
// carried out in order
$post->add_rules('action', 'required', 'alpha', 'length[1,1]');
$post->add_rules('incident_id.*', 'required', 'numeric');
if (in_array($post->action, array('a', 'u')) and !Auth::instance()->has_permission('reports_approve')) {
$post->add_error('action', 'permission');
}
if ($post->action == 'v' and !Auth::instance()->has_permission('reports_verify')) {
$post->add_error('action', 'permission');
}
if ($post->action == 'd' and !Auth::instance()->has_permission('reports_edit')) {
$post->add_error('action', 'permission');
}
if ($post->action == 'a') {
// sanitize the incident_ids
$post->incident_id = array_map('intval', $post->incident_id);
// Query to check if this report is uncategorized i.e categoryless
$query = "SELECT i.* FROM " . $table_prefix . "incident i " . "LEFT JOIN " . $table_prefix . "incident_category ic ON i.id=ic.incident_id " . "LEFT JOIN " . $table_prefix . "category c ON c.id = ic.category_id " . "WHERE c.id IS NULL " . "AND i.id IN :incidentids";
$result = Database::instance()->query($query, array(':incidentids' => $post->incident_id));
// We enly approve the report IF it's categorized
// throw an error if any incidents aren't categorized
foreach ($result as $incident) {
$post->add_error('incident_id', 'categories_required', $incident->incident_title);
}
}
if ($post->validate()) {
// Approve Action
if ($post->action == 'a') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
if ($update->loaded == TRUE) {
$update->incident_active = '1';
// Tag this as a report that needs to be sent out as an alert
if ($update->incident_alert_status != '2') {
// 2 = report that has had an alert sent
$update->incident_alert_status = '1';
}
$update->save();
// Record 'Verified By' Action
reports::verify_approve($update);
// Action::report_approve - Approve a Report
Event::run('ushahidi_action.report_approve', $update);
}
$form_action = utf8::strtoupper(Kohana::lang('ui_admin.approved'));
}
} elseif ($post->action == 'u') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
if ($update->loaded == TRUE) {
$update->incident_active = '0';
// If Alert hasn't been sent yet, disable it
if ($update->incident_alert_status == '1') {
$update->incident_alert_status = '0';
}
$update->save();
// Record 'Verified By' Action
reports::verify_approve($update);
// Action::report_unapprove - Unapprove a Report
Event::run('ushahidi_action.report_unapprove', $update);
}
}
$form_action = utf8::strtoupper(Kohana::lang('ui_admin.unapproved'));
} elseif ($post->action == 'v') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
$verify = new Verify_Model();
if ($update->loaded == TRUE) {
if ($update->incident_verified == '1') {
$update->incident_verified = '0';
$verify->verified_status = '0';
} else {
$update->incident_verified = '1';
$verify->verified_status = '2';
}
$update->save();
// Record 'Verified By' Action
reports::verify_approve($update);
}
}
// Set the form action
$form_action = utf8::strtoupper(Kohana::lang('ui_admin.verified_unverified'));
} elseif ($post->action == 'd') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
if ($update->loaded) {
$update->delete();
}
}
$form_action = utf8::strtoupper(Kohana::lang('ui_admin.deleted'));
}
$form_saved = TRUE;
} else {
// Repopulate the form fields
//$form = arr::overwrite($form, $post->as_array());
// Populate the error fields, if any
$errors = $post->errors('reports');
$form_error = TRUE;
}
}
// Fetch all incidents
$incidents = reports::fetch_incidents(TRUE, Kohana::config('settings.items_per_page_admin'));
Event::run('ushahidi_filter.filter_incidents', $incidents);
$this->template->content->countries = Country_Model::get_countries_list();
$this->template->content->incidents = $incidents;
$this->template->content->pagination = reports::$pagination;
$this->template->content->form_error = $form_error;
$this->template->content->errors = $errors;
$this->template->content->form_saved = $form_saved;
$this->template->content->form_action = $form_action;
// Total Reports
$this->template->content->total_items = reports::$pagination->total_items;
// Status Tab
$this->template->content->status = $status;
$this->template->content->order_field = $order_field;
$this->template->content->sort = $sort;
$this->themes->map_enabled = TRUE;
$this->themes->json2_enabled = TRUE;
$this->themes->treeview_enabled = TRUE;
// Javascript Header
$this->themes->js = new View('admin/reports/reports_js');
}
/**
* Generate geojson
*
* @param string $type type of geojson to generate. Valid options are: 'clusters' and 'markers'
**/
protected function geojson($type)
{
$color = Kohana::config('settings.default_map_all');
$icon = "";
$markers = FALSE;
if (Kohana::config('settings.default_map_all_icon_id')) {
$icon_object = ORM::factory('media')->find(Kohana::config('settings.default_map_all_icon_id'));
$icon = url::convert_uploaded_to_abs($icon_object->media_medium);
}
// Category ID
$category_id = (isset($_GET['c']) and intval($_GET['c']) > 0) ? intval($_GET['c']) : 0;
// Get the category colour
if (Category_Model::is_valid_category($category_id)) {
// Get the color & icon
$cat = ORM::factory('category', $category_id);
$color = $cat->category_color;
$icon = "";
if ($cat->category_image) {
$icon = url::convert_uploaded_to_abs($cat->category_image);
}
}
$params = array('color' => $color, 'icon' => $icon);
Event::run('ushahidi_filter.json_alter_params', $params);
$color = $params['color'];
$icon = $params['icon'];
// Run event ushahidi_filter.json_replace_markers
// This allows a plugin to completely replace $markers
// If markers are added at this point we don't bother fetching incidents at all
Event::run('ushahidi_filter.json_replace_markers', $markers);
// Fetch the incidents
if (!$markers) {
$markers = (isset($_GET['page']) and intval($_GET['page']) > 0) ? reports::fetch_incidents(TRUE) : reports::fetch_incidents();
}
// Run event ushahidi_filter.json_alter_markers
// This allows a plugin to alter $markers
// Plugins can add or remove markers as needed
Event::run('ushahidi_filter.json_alter_markers', $markers);
// Get geojson features array
$function = "{$type}_geojson";
$json_features = $this->{$function}($markers, $category_id, $color, $icon);
$this->render_geojson($json_features);
}
/**
* Generate JSON in CLUSTER mode
*/
public function cluster()
{
// Database
$db = new Database();
$json = "";
$json_item = "";
$json_array = array();
$geometry_array = array();
$color = Kohana::config('settings.default_map_all');
$icon = "";
// Get Zoom Level
$zoomLevel = (isset($_GET['z']) and !empty($_GET['z'])) ? (int) $_GET['z'] : 8;
//$distance = 60;
$distance = (10000000 >> $zoomLevel) / 100000;
// Fetch the incidents using the specified parameters
$incidents = reports::fetch_incidents();
// Category ID
$category_id = (isset($_GET['c']) and intval($_GET['c']) > 0) ? intval($_GET['c']) : 0;
// Start date
$start_date = (isset($_GET['s']) and intval($_GET['s']) > 0) ? intval($_GET['s']) : NULL;
// End date
$end_date = (isset($_GET['e']) and intval($_GET['e']) > 0) ? intval($_GET['e']) : NULL;
if (Category_Model::is_valid_category($category_id)) {
// Get the color
$color = ORM::factory('category', $category_id)->category_color;
}
// Create markers by marrying the locations and incidents
$markers = array();
foreach ($incidents as $incident) {
$markers[] = array('id' => $incident->incident_id, 'incident_title' => $incident->incident_title, 'latitude' => $incident->latitude, 'longitude' => $incident->longitude, 'thumb' => '');
}
$clusters = array();
// Clustered
$singles = array();
// Non Clustered
// Loop until all markers have been compared
while (count($markers)) {
$marker = array_pop($markers);
$cluster = array();
// Compare marker against all remaining markers.
foreach ($markers as $key => $target) {
// This function returns the distance between two markers, at a defined zoom level.
// $pixels = $this->_pixelDistance($marker['latitude'], $marker['longitude'],
// $target['latitude'], $target['longitude'], $zoomLevel);
$pixels = abs($marker['longitude'] - $target['longitude']) + abs($marker['latitude'] - $target['latitude']);
// If two markers are closer than defined distance, remove compareMarker from array and add to cluster.
if ($pixels < $distance) {
unset($markers[$key]);
$target['distance'] = $pixels;
$cluster[] = $target;
}
}
// If a marker was added to cluster, also add the marker we were comparing to.
if (count($cluster) > 0) {
$cluster[] = $marker;
$clusters[] = $cluster;
} else {
$singles[] = $marker;
}
}
// Create Json
foreach ($clusters as $cluster) {
// Calculate cluster center
$bounds = $this->_calculateCenter($cluster);
$cluster_center = $bounds['center'];
$southwest = $bounds['sw'];
$northeast = $bounds['ne'];
// Number of Items in Cluster
$cluster_count = count($cluster);
// Get the time filter
$time_filter = (!empty($start_date) and !empty($end_date)) ? "&s=" . $start_date . "&e=" . $end_date : "";
// Build out the JSON string
$json_item = "{";
$json_item .= "\"type\":\"Feature\",";
$json_item .= "\"properties\": {";
$json_item .= "\"name\":\"" . str_replace(chr(10), ' ', str_replace(chr(13), ' ', "<a href=" . url::base() . "reports/index/?c=" . $category_id . "&sw=" . $southwest . "&ne=" . $northeast . $time_filter . ">" . $cluster_count . " Reports</a>")) . "\",";
$json_item .= "\"link\": \"" . url::base() . "reports/index/?c=" . $category_id . "&sw=" . $southwest . "&ne=" . $northeast . $time_filter . "\", ";
$json_item .= "\"category\":[0], ";
$json_item .= "\"color\": \"" . $color . "\", ";
$json_item .= "\"icon\": \"" . $icon . "\", ";
$json_item .= "\"thumb\": \"\", ";
$json_item .= "\"timestamp\": \"0\", ";
$json_item .= "\"count\": \"" . $cluster_count . "\"";
$json_item .= "},";
$json_item .= "\"geometry\": {";
$json_item .= "\"type\":\"Point\", ";
$json_item .= "\"coordinates\":[" . $cluster_center . "]";
$json_item .= "}";
$json_item .= "}";
array_push($json_array, $json_item);
}
foreach ($singles as $single) {
$json_item = "{";
$json_item .= "\"type\":\"Feature\",";
$json_item .= "\"properties\": {";
$json_item .= "\"name\":\"" . str_replace(chr(10), ' ', str_replace(chr(13), ' ', "<a href=" . url::base() . "reports/view/" . $single['id'] . "/>" . str_replace('"', '\\"', $single['incident_title']) . "</a>")) . "\",";
$json_item .= "\"link\": \"" . url::base() . "reports/view/" . $single['id'] . "\", ";
$json_item .= "\"category\":[0], ";
$json_item .= "\"color\": \"" . $color . "\", ";
$json_item .= "\"icon\": \"" . $icon . "\", ";
// $json_item .= "\"thumb\": \"".$single['thumb']."\", ";
$json_item .= "\"timestamp\": \"0\", ";
$json_item .= "\"count\": \"" . 1 . "\"";
$json_item .= "},";
$json_item .= "\"geometry\": {";
$json_item .= "\"type\":\"Point\", ";
$json_item .= "\"coordinates\":[" . $single['longitude'] . ", " . $single['latitude'] . "]";
$json_item .= "}";
$json_item .= "}";
array_push($json_array, $json_item);
}
$json = implode(",", $json_array);
//
// E.Kala July 27, 2011
// @todo Parking this geometry business for review
//
// if (count($geometry_array))
// {
// $json = implode(",", $geometry_array).",".$json;
// }
header('Content-type: application/json; charset=utf-8');
$this->template->json = $json;
}
/**
* Helper method to load the report listing view
*/
private function _get_report_listing_view($locale = '')
{
// Check if the local is empty
if (empty($locale)) {
$locale = Kohana::config('locale.language.0');
}
// Load the report listing view
$report_listing = new View('reports/list');
// Fetch all incidents
$incidents = reports::fetch_incidents(TRUE);
// Pagination
$pagination = reports::$pagination;
// For compatibility with older custom themes:
// Generate array of category titles with their proper localizations using an array
// DO NOT use this in new code, call Category_Lang_Model::category_title() directly
foreach (Category_Model::categories() as $category) {
$localized_categories[$category['category_title']] = Category_Lang_Model::category_title($category['category_id']);
}
// Set the view content
$report_listing->incidents = $incidents;
$report_listing->localized_categories = $localized_categories;
//Set default as not showing pagination. Will change below if necessary.
$report_listing->pagination = "";
// Pagination and Total Num of Report Stats
$plural = $pagination->total_items == 1 ? "" : "s";
// Set the next and previous page numbers
$report_listing->next_page = $pagination->next_page;
$report_listing->previous_page = $pagination->previous_page;
if ($pagination->total_items > 0) {
$current_page = $pagination->sql_offset / $pagination->items_per_page + 1;
$total_pages = ceil($pagination->total_items / $pagination->items_per_page);
if ($total_pages >= 1) {
$report_listing->pagination = $pagination;
// Show the total of report
// @todo This is only specific to the frontend reports theme
$report_listing->stats_breadcrumb = $pagination->current_first_item . '-' . $pagination->current_last_item . ' of ' . $pagination->total_items . ' ' . Kohana::lang('ui_main.reports');
} else {
// If we don't want to show pagination
$report_listing->stats_breadcrumb = $pagination->total_items . ' ' . Kohana::lang('ui_admin.reports');
}
} else {
$report_listing->stats_breadcrumb = '(' . $pagination->total_items . ' report' . $plural . ')';
}
// Return
return $report_listing;
}
/**
* Lists the reports.
*
* @param int $page
*/
public function index($page = 1)
{
// If user doesn't have access, redirect to dashboard
if (!admin::permissions($this->user, "reports_view")) {
url::redirect(url::site() . 'admin/dashboard');
}
$this->template->content = new View('admin/reports');
$this->template->content->title = Kohana::lang('ui_admin.reports');
//hook into the event for the reports::fetch_incidents() method
Event::add('ushahidi_filter.fetch_incidents_set_params', array($this, '_add_incident_filters'));
$status = "0";
if (!empty($_GET['status'])) {
$status = $_GET['status'];
if (strtolower($status) == 'a') {
array_push($this->params, 'i.incident_active = 0');
} elseif (strtolower($status) == 'v') {
array_push($this->params, 'i.incident_verified = 0');
} else {
$status = "0";
}
}
// Get Search Keywords (If Any)
if (isset($_GET['k'])) {
// Brute force input sanitization
// Phase 1 - Strip the search string of all non-word characters
$keyword_raw = isset($_GET['k']) ? preg_replace('#/\\w+/#', '', $_GET['k']) : "";
// Strip any HTML tags that may have been missed in Phase 1
$keyword_raw = strip_tags($keyword_raw);
// Phase 3 - Invoke Kohana's XSS cleaning mechanism just incase an outlier wasn't caught
// in the first 2 steps
$keyword_raw = $this->input->xss_clean($keyword_raw);
$filter = " (" . $this->_get_searchstring($keyword_raw) . ")";
array_push($this->params, $filter);
} else {
$keyword_raw = "";
}
// Check, has the form been submitted?
$form_error = FALSE;
$form_saved = FALSE;
$form_action = "";
if ($_POST) {
$post = Validation::factory($_POST);
// Add some filters
$post->pre_filter('trim', TRUE);
// Add some rules, the input field, followed by a list of checks, carried out in order
$post->add_rules('action', 'required', 'alpha', 'length[1,1]');
$post->add_rules('incident_id.*', 'required', 'numeric');
if ($post->validate()) {
// Approve Action
if ($post->action == 'a') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
if ($update->loaded == TRUE) {
$update->incident_active = $update->incident_active == 0 ? '1' : '0';
// Tag this as a report that needs to be sent out as an alert
if ($update->incident_alert_status != '2') {
// 2 = report that has had an alert sent
$update->incident_alert_status = '1';
}
$update->save();
$verify = new Verify_Model();
$verify->incident_id = $item;
$verify->verified_status = '1';
// Record 'Verified By' Action
$verify->user_id = $_SESSION['auth_user']->id;
$verify->verified_date = date("Y-m-d H:i:s", time());
$verify->save();
// Action::report_approve - Approve a Report
Event::run('ushahidi_action.report_approve', $update);
}
}
$form_action = strtoupper(Kohana::lang('ui_admin.approved'));
} elseif ($post->action == 'u') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
if ($update->loaded == TRUE) {
$update->incident_active = '0';
// If Alert hasn't been sent yet, disable it
if ($update->incident_alert_status == '1') {
$update->incident_alert_status = '0';
}
$update->save();
$verify = new Verify_Model();
$verify->incident_id = $item;
$verify->verified_status = '0';
// Record 'Verified By' Action
$verify->user_id = $_SESSION['auth_user']->id;
$verify->verified_date = date("Y-m-d H:i:s", time());
$verify->save();
// Action::report_unapprove - Unapprove a Report
Event::run('ushahidi_action.report_unapprove', $update);
}
}
$form_action = strtoupper(Kohana::lang('ui_admin.unapproved'));
} elseif ($post->action == 'v') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
$verify = new Verify_Model();
if ($update->loaded == TRUE) {
if ($update->incident_verified == '1') {
$update->incident_verified = '0';
$verify->verified_status = '0';
} else {
$update->incident_verified = '1';
$verify->verified_status = '2';
}
$update->save();
$verify->incident_id = $item;
// Record 'Verified By' Action
$verify->user_id = $_SESSION['auth_user']->id;
$verify->verified_date = date("Y-m-d H:i:s", time());
$verify->save();
}
}
// Set the form action
$form_action = strtoupper(Kohana::lang('ui_admin.verified_unverified'));
} elseif ($post->action == 'd') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
if ($update->loaded == TRUE) {
$incident_id = $update->id;
$location_id = $update->location_id;
$update->delete();
// Delete Location
ORM::factory('location')->where('id', $location_id)->delete_all();
// Delete Categories
ORM::factory('incident_category')->where('incident_id', $incident_id)->delete_all();
// Delete Translations
ORM::factory('incident_lang')->where('incident_id', $incident_id)->delete_all();
// Delete Photos From Directory
foreach (ORM::factory('media')->where('incident_id', $incident_id)->where('media_type', 1) as $photo) {
deletePhoto($photo->id);
}
// Delete Media
ORM::factory('media')->where('incident_id', $incident_id)->delete_all();
// Delete Sender
ORM::factory('incident_person')->where('incident_id', $incident_id)->delete_all();
// Delete relationship to SMS message
$updatemessage = ORM::factory('message')->where('incident_id', $incident_id)->find();
if ($updatemessage->loaded == TRUE) {
$updatemessage->incident_id = 0;
$updatemessage->save();
}
// Delete Comments
ORM::factory('comment')->where('incident_id', $incident_id)->delete_all();
// Delete form responses
ORM::factory('form_response')->where('incident_id', $incident_id)->delete_all();
// Action::report_delete - Deleted a Report
Event::run('ushahidi_action.report_delete', $incident_id);
}
}
$form_action = strtoupper(Kohana::lang('ui_admin.deleted'));
}
$form_saved = TRUE;
} else {
$form_error = TRUE;
}
}
// Fetch all incidents
$all_incidents = reports::fetch_incidents();
// Pagination
$pagination = new Pagination(array('style' => 'front-end-reports', 'query_string' => 'page', 'items_per_page' => (int) Kohana::config('settings.items_per_page'), 'total_items' => $all_incidents->count()));
Event::run('ushahidi_filter.pagination', $pagination);
// Reports
$incidents = Incident_Model::get_incidents(reports::$params, $pagination);
Event::run('ushahidi_filter.filter_incidents', $incidents);
$this->template->content->countries = Country_Model::get_countries_list();
$this->template->content->incidents = $incidents;
$this->template->content->pagination = $pagination;
$this->template->content->form_error = $form_error;
$this->template->content->form_saved = $form_saved;
$this->template->content->form_action = $form_action;
// Total Reports
$this->template->content->total_items = $pagination->total_items;
// Status Tab
$this->template->content->status = $status;
// Javascript Header
$this->template->js = new View('admin/reports_js');
}
/**
* Function: json_timeline
*
* Description: Creates the json of incidents that goes on the timeline
*
* @param obj $controller - The controller that's calling this function
* @param bool $on_the_back_end - True if this json is going to a client on the backend
* @param string $extra_where_text - If you want to add some extra where text to the SQL
* @param array $joins - Array of joins you'd like to add. Great if you're looking to select things based on non-standard tables
* @param array $custom_category_to_table_mapping - Maps what the joins are on.
*
* Views:
*
* Results: Json is sent to the client
*/
public static function json_timeline($controller, $on_the_back_end = true, $extra_where_text = "", $joins = array(), $custom_category_to_table_mapping = array())
{
$category_ids = array('0');
//get the coloring mode
$color_mode = ORM::factory('enhancedmap_settings')->where('key', 'color_mode')->find()->value;
if (isset($_GET['c']) and is_array($_GET['c'])) {
$category_ids = array();
//make sure we only hanlde numeric cat ids
foreach ($_GET['c'] as $cat) {
if (is_numeric($cat)) {
$category_ids[] = $cat;
}
}
}
$is_all_categories = false;
if (count($category_ids) == 0 || $category_ids[0] == '0') {
$is_all_categories = true;
}
$controller->auto_render = FALSE;
$db = new Database();
$interval = (isset($_GET["i"]) and !empty($_GET["i"])) ? $_GET["i"] : "month";
// Get the Counts
$select_date_text = "DATE_FORMAT(incident_date, '%Y-%m-01')";
$groupby_date_text = "DATE_FORMAT(incident_date, '%Y%m')";
if ($interval == 'day') {
$select_date_text = "DATE_FORMAT(incident_date, '%Y-%m-%d')";
$groupby_date_text = "DATE_FORMAT(incident_date, '%Y%m%d')";
} elseif ($interval == 'hour') {
$select_date_text = "DATE_FORMAT(incident_date, '%Y-%m-%d %H:%M')";
$groupby_date_text = "DATE_FORMAT(incident_date, '%Y%m%d%H')";
} elseif ($interval == 'week') {
$select_date_text = "STR_TO_DATE(CONCAT(CAST(YEARWEEK(incident_date) AS CHAR), ' Sunday'), '%X%V %W')";
$groupby_date_text = "YEARWEEK(incident_date)";
}
//more than one color
$color = Kohana::config('settings.default_map_all');
if ($is_all_categories) {
} else {
if ($color_mode == 'merge_all') {
//more than one color
$colors = array();
foreach ($category_ids as $cat) {
$colors[] = ORM::factory('category', $cat)->category_color;
}
$color = self::merge_colors($colors);
} else {
if ($color_mode == 'highest_first') {
$highest_color = null;
foreach ($category_ids as $cat) {
$c = ORM::factory('category', $cat);
if ($highest_color == null or $highest_color->category_position > $c->category_position) {
$highest_color = $c;
}
}
$color = $highest_color->category_color;
}
}
}
$graph_data = array();
$graph_data[0] = array();
$graph_data[0]['label'] = "Category Title";
//is this used for anything?
$graph_data[0]['color'] = '#' . $color;
$graph_data[0]['data'] = array();
$incidents = reports::fetch_incidents();
$approved_IDs_str = "('-1')";
if (count($incidents) > 0) {
$i = 0;
$approved_IDs_str = "(";
foreach ($incidents as $incident) {
$i++;
$approved_IDs_str = $i > 1 ? $approved_IDs_str . ', ' : $approved_IDs_str;
$approved_IDs_str = $approved_IDs_str . "'" . $incident->incident_id . "'";
}
$approved_IDs_str = $approved_IDs_str . ") ";
}
$table_prefix = Kohana::config('database.default.table_prefix');
$query = 'SELECT UNIX_TIMESTAMP(' . $select_date_text . ') AS time, COUNT(id) AS number FROM ' . $table_prefix . 'incident WHERE incident.id in' . $approved_IDs_str . ' GROUP BY ' . $groupby_date_text;
$query = $db->query($query);
foreach ($query as $items) {
array_push($graph_data[0]['data'], array($items->time * 1000, $items->number));
}
header('Content-type: application/json; charset=utf-8');
echo json_encode($graph_data);
}
/**
* Generate JSON in CLUSTER mode
*/
public function cluster()
{
// Database
$db = new Database();
$json = '';
$json_item = array();
$json_features = array();
$geometry_array = array();
$color = Kohana::config('settings.default_map_all');
$icon = "";
if (Kohana::config('settings.default_map_all_icon_id')) {
$icon_object = ORM::factory('media')->find(Kohana::config('settings.default_map_all_icon_id'));
$icon = url::convert_uploaded_to_abs($icon_object->media_medium);
}
// Get Zoom Level
$zoomLevel = (isset($_GET['z']) and !empty($_GET['z'])) ? (int) $_GET['z'] : 8;
//$distance = 60;
$distance = (10000000 >> $zoomLevel) / 100000;
// Fetch the incidents using the specified parameters
$incidents = reports::fetch_incidents();
// Category ID
$category_id = (isset($_GET['c']) and intval($_GET['c']) > 0) ? intval($_GET['c']) : 0;
// Start date
$start_date = (isset($_GET['s']) and intval($_GET['s']) > 0) ? intval($_GET['s']) : NULL;
// End date
$end_date = (isset($_GET['e']) and intval($_GET['e']) > 0) ? intval($_GET['e']) : NULL;
if (Category_Model::is_valid_category($category_id)) {
// Get the color & icon
$cat = ORM::factory('category', $category_id);
$color = $cat->category_color;
if ($cat->category_image) {
$icon = url::convert_uploaded_to_abs($cat->category_image);
}
}
// Create markers by marrying the locations and incidents
$markers = array();
foreach ($incidents as $incident) {
$markers[] = array('id' => $incident->incident_id, 'incident_title' => $incident->incident_title, 'latitude' => $incident->latitude, 'longitude' => $incident->longitude, 'thumb' => '');
}
$clusters = array();
// Clustered
$singles = array();
// Non Clustered
// Loop until all markers have been compared
while (count($markers)) {
$marker = array_pop($markers);
$cluster = array();
// Compare marker against all remaining markers.
foreach ($markers as $key => $target) {
// This function returns the distance between two markers, at a defined zoom level.
// $pixels = $this->_pixelDistance($marker['latitude'], $marker['longitude'],
// $target['latitude'], $target['longitude'], $zoomLevel);
$pixels = abs($marker['longitude'] - $target['longitude']) + abs($marker['latitude'] - $target['latitude']);
// If two markers are closer than defined distance, remove compareMarker from array and add to cluster.
if ($pixels < $distance) {
unset($markers[$key]);
$target['distance'] = $pixels;
$cluster[] = $target;
}
}
// If a marker was added to cluster, also add the marker we were comparing to.
if (count($cluster) > 0) {
$cluster[] = $marker;
$clusters[] = $cluster;
} else {
$singles[] = $marker;
}
}
// Create Json
foreach ($clusters as $cluster) {
// Calculate cluster center
$bounds = $this->calculate_center($cluster);
$cluster_center = array_values($bounds['center']);
$southwest = $bounds['sw']['longitude'] . ',' . $bounds['sw']['latitude'];
$northeast = $bounds['ne']['longitude'] . ',' . $bounds['ne']['latitude'];
// Number of Items in Cluster
$cluster_count = count($cluster);
// Get the time filter
$time_filter = (!empty($start_date) and !empty($end_date)) ? "&s=" . $start_date . "&e=" . $end_date : "";
// Build out the JSON string
$link = url::base() . "reports/index/?c=" . $category_id . "&sw=" . $southwest . "&ne=" . $northeast . $time_filter;
$item_name = $this->get_title(Kohana::lang('ui_main.reports_count', $cluster_count), $link);
$json_item = array();
$json_item['type'] = 'Feature';
$json_item['properties'] = array('name' => $item_name, 'link' => $link, 'category' => array($category_id), 'color' => $color, 'icon' => $icon, 'thumb' => '', 'timestamp' => 0, 'count' => $cluster_count);
$json_item['geometry'] = array('type' => 'Point', 'coordinates' => $cluster_center);
array_push($json_features, $json_item);
}
foreach ($singles as $single) {
$link = url::base() . "reports/view/" . $single['id'];
$item_name = $this->get_title($single['incident_title'], $link);
$json_item = array();
$json_item['type'] = 'Feature';
$json_item['properties'] = array('name' => $item_name, 'link' => $link, 'category' => array($category_id), 'color' => $color, 'icon' => $icon, 'thumb' => '', 'timestamp' => 0, 'count' => 1);
$json_item['geometry'] = array('type' => 'Point', 'coordinates' => array($single['longitude'], $single['latitude']));
array_push($json_features, $json_item);
}
//
// E.Kala July 27, 2011
// @todo Parking this geometry business for review
//
// if (count($geometry_array))
// {
// $json = implode(",", $geometry_array).",".$json;
// }
Event::run('ushahidi_filter.json_cluster_features', $json_features);
$json = json_encode(array("type" => "FeatureCollection", "features" => $json_features));
header('Content-type: application/json; charset=utf-8');
echo $json;
}
private function get_counts()
{
//loop through each of the geometries and see how many reports fall under both the geometry category and
//the dependent
$geometries = ORM::factory("densitymap_geometry")->find_all();
$geometries_and_counts = array();
foreach ($geometries as $geometry) {
$_GET['dm'] = $geometry->category_id;
$reports = reports::fetch_incidents();
$geometries_and_counts[$geometry->id] = count($reports);
}
//end foreach loop over all the geometries
return $geometries_and_counts;
}
/**
* Lists the reports.
*
* @param int $page
*/
public function index($page = 1)
{
// If user doesn't have access, redirect to dashboard
if (!admin::permissions($this->user, "reports_view")) {
url::redirect(url::site() . 'admin/dashboard');
}
$this->template->content = new View('admin/reports');
$this->template->content->title = Kohana::lang('ui_admin.reports');
// Database table prefix
$table_prefix = Kohana::config('database.default.table_prefix');
// Hook into the event for the reports::fetch_incidents() method
Event::add('ushahidi_filter.fetch_incidents_set_params', array($this, '_add_incident_filters'));
$status = "0";
if (!empty($_GET['status'])) {
$status = $_GET['status'];
if (strtolower($status) == 'a') {
array_push($this->params, 'i.incident_active = 0');
} elseif (strtolower($status) == 'v') {
array_push($this->params, 'i.incident_verified = 0');
} elseif (strtolower($status) == 'o') {
array_push($this->params, 'ic.category_id = 5');
} else {
$status = "0";
}
}
// Get Search Keywords (If Any)
if (isset($_GET['k'])) {
// Brute force input sanitization
// Phase 1 - Strip the search string of all non-word characters
$keyword_raw = isset($_GET['k']) ? preg_replace('#/\\w+/#', '', $_GET['k']) : "";
// Strip any HTML tags that may have been missed in Phase 1
$keyword_raw = strip_tags($keyword_raw);
// Phase 3 - Invoke Kohana's XSS cleaning mechanism just incase an outlier wasn't caught
// in the first 2 steps
$keyword_raw = $this->input->xss_clean($keyword_raw);
$filter = " (" . $this->_get_searchstring($keyword_raw) . ")";
array_push($this->params, $filter);
} else {
$keyword_raw = "";
}
// Check, has the form been submitted?
$form_error = FALSE;
$form_saved = FALSE;
$form_action = "";
if ($_POST) {
$post = Validation::factory($_POST);
// Add some filters
$post->pre_filter('trim', TRUE);
// Add some rules, the input field, followed by a list of checks,
// carried out in order
$post->add_rules('action', 'required', 'alpha', 'length[1,1]');
$post->add_rules('incident_id.*', 'required', 'numeric');
if ($post->validate()) {
// Approve Action
if ($post->action == 'a') {
foreach ($post->incident_id as $item) {
// Database instance
$db = new Database();
// Query to check if this report is uncategorized i.e categoryless
$query = "SELECT ic.* FROM " . $table_prefix . "incident_category ic " . "INNER JOIN " . $table_prefix . "category c ON c.id = ic.category_id " . "INNER JOIN " . $table_prefix . "incident i ON i.id=ic.incident_id " . "WHERE c.category_title =\"NONE\" AND c.category_trusted = '1' " . "AND ic.incident_id = {$item}";
$result = $db->query($query);
// Only approve the report IF it's not uncategorized
// i.e the query returns a null set
if (count($result) == 0) {
$update = new Incident_Model($item);
if ($update->loaded == TRUE) {
$update->incident_active = $update->incident_active == 0 ? '1' : '0';
// Tag this as a report that needs to be sent out as an alert
if ($update->incident_alert_status != '2') {
// 2 = report that has had an alert sent
$update->incident_alert_status = '1';
}
$update->save();
$verify = new Verify_Model();
$verify->incident_id = $item;
$verify->verified_status = '1';
// Record 'Verified By' Action
$verify->user_id = $_SESSION['auth_user']->id;
$verify->verified_date = date("Y-m-d H:i:s", time());
$verify->save();
// Action::report_approve - Approve a Report
Event::run('ushahidi_action.report_approve', $update);
}
}
$form_action = strtoupper(Kohana::lang('ui_admin.approved'));
}
} elseif ($post->action == 'u') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
if ($update->loaded == TRUE) {
$update->incident_active = '0';
// If Alert hasn't been sent yet, disable it
if ($update->incident_alert_status == '1') {
$update->incident_alert_status = '0';
}
$update->save();
$verify = new Verify_Model();
$verify->incident_id = $item;
$verify->verified_status = '0';
// Record 'Verified By' Action
$verify->user_id = $_SESSION['auth_user']->id;
$verify->verified_date = date("Y-m-d H:i:s", time());
$verify->save();
// Action::report_unapprove - Unapprove a Report
Event::run('ushahidi_action.report_unapprove', $update);
}
}
$form_action = strtoupper(Kohana::lang('ui_admin.unapproved'));
} elseif ($post->action == 'v') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
$verify = new Verify_Model();
if ($update->loaded == TRUE) {
if ($update->incident_verified == '1') {
$update->incident_verified = '0';
$verify->verified_status = '0';
} else {
$update->incident_verified = '1';
$verify->verified_status = '2';
}
$update->save();
$verify->incident_id = $item;
// Record 'Verified By' Action
$verify->user_id = $_SESSION['auth_user']->id;
$verify->verified_date = date("Y-m-d H:i:s", time());
$verify->save();
}
}
// Set the form action
$form_action = strtoupper(Kohana::lang('ui_admin.verified_unverified'));
} elseif ($post->action == 'd') {
foreach ($post->incident_id as $item) {
$update = new Incident_Model($item);
if ($update->loaded) {
$update->delete();
}
}
$form_action = strtoupper(Kohana::lang('ui_admin.deleted'));
}
$form_saved = TRUE;
} else {
$form_error = TRUE;
}
}
// Fetch all incidents
$all_incidents = reports::fetch_incidents();
// Pagination
$pagination = new Pagination(array('style' => 'front-end-reports', 'query_string' => 'page', 'items_per_page' => (int) Kohana::config('settings.items_per_page'), 'total_items' => $all_incidents->count()));
Event::run('ushahidi_filter.pagination', $pagination);
// Reports
$incidents = Incident_Model::get_incidents(reports::$params, $pagination);
Event::run('ushahidi_filter.filter_incidents', $incidents);
$this->template->content->countries = Country_Model::get_countries_list();
$this->template->content->incidents = $incidents;
$this->template->content->pagination = $pagination;
$this->template->content->form_error = $form_error;
$this->template->content->form_saved = $form_saved;
$this->template->content->form_action = $form_action;
// Total Reports
$this->template->content->total_items = $pagination->total_items;
// Status Tab
$this->template->content->status = $status;
// Javascript Header
$this->template->js = new View('admin/reports_js');
}
/**
* Helper method to load the report listing view
*/
private function _get_report_listing_view($locale = '')
{
// Check if the local is empty
if (empty($locale)) {
$locale = Kohana::config('locale.language.0');
}
// Load the report listing view
$report_listing = new View('reports_listing');
// Fetch all incidents
$all_incidents = reports::fetch_incidents();
// Reports
$incidents = Incident_Model::get_incidents(reports::$params);
// Swap out category titles with their proper localizations using an array (cleaner way to do this?)
$localized_categories = array();
foreach ($incidents as $incident) {
$incident = ORM::factory('incident', $incident->incident_id);
foreach ($incident->category as $category) {
$ct = (string) $category->category_title;
if (!isset($localized_categories[$ct])) {
$localized_categories[$ct] = Category_Lang_Model::category_title($category->id, $locale);
}
}
}
// Set the view content
$report_listing->incidents = $incidents;
$report_listing->localized_categories = $localized_categories;
//Set default as not showing pagination. Will change below if necessary.
$report_listing->pagination = "";
// Pagination and Total Num of Report Stats
$plural = count($incidents) > 1 ? "" : "s";
$report_listing->stats_breadcrumb = count($incidents) . ' ' . Kohana::lang('ui_admin.reports') . $plural;
// Return
return $report_listing;
}
