/**
* Download Reports in CSV format
* JP: Added filter by search option. Also added HTML download.
*/
public function download()
{
// If user doesn't have access, redirect to dashboard
if (!$this->auth->has_permission("reports_download")) {
url::redirect(url::site() . 'admin/dashboard');
}
$this->template->content = new View('admin/reports/download');
$this->template->content->title = Kohana::lang('ui_admin.download_reports');
$errors = $form = array('format' => '', 'data_active' => array(), 'data_verified' => array(), 'data_include' => array(), 'from_date' => '', 'to_date' => '', 'form_auth_token' => '', 'filter_search' => '');
// Default to all selected
$form['data_active'] = array(0, 1);
$form['data_verified'] = array(0, 1);
$form['data_include'] = array(1, 2, 3, 4, 5, 6, 7);
$form_error = FALSE;
// Check, has the form been submitted, if so, setup validation
if ($_POST) {
// Instantiate Validation, use $post, so we don't overwrite $_POST fields with our own things
$post = array_merge($_POST, $_FILES);
// Test to see if things passed the rule checks
if (download::validate($post)) {
// Set filter
$filter = '( ';
// Report Type Filter
$show_active = FALSE;
$show_inactive = FALSE;
$show_verified = FALSE;
$show_not_verified = FALSE;
$filter_search = FALSE;
if (in_array(1, $post->data_active)) {
$show_active = TRUE;
}
if (in_array(0, $post->data_active)) {
$show_inactive = TRUE;
}
if (in_array(1, $post->data_verified)) {
$show_verified = TRUE;
}
if (in_array(0, $post->data_verified)) {
$show_not_verified = TRUE;
}
if (!empty($post->filter_search)) {
$filter_search = TRUE;
}
// Handle active or not active
if ($show_active && !$show_inactive) {
$filter .= ' incident_active = 1 ';
} elseif (!$show_active && $show_inactive) {
$filter .= ' incident_active = 0 ';
} elseif ($show_active && $show_inactive) {
$filter .= ' (incident_active = 1 OR incident_active = 0) ';
} elseif (!$show_active && !$show_inactive) {
// Equivalent to 1 = 0
$filter .= ' (incident_active = 0 AND incident_active = 1) ';
}
$filter .= ' AND ';
// Handle verified
if ($show_verified && !$show_not_verified) {
$filter .= ' incident_verified = 1 ';
} elseif (!$show_verified && $show_not_verified) {
$filter .= ' incident_verified = 0 ';
} elseif ($show_verified && $show_not_verified) {
$filter .= ' (incident_verified = 0 OR incident_verified = 1) ';
} elseif (!$show_verified && !$show_not_verified) {
$filter .= ' (incident_verified = 0 AND incident_verified = 1) ';
}
$filter .= ') ';
// Report Date Filter
if (!empty($post->from_date)) {
$filter .= " AND incident_date >= '" . date("Y-m-d H:i:s", strtotime($post->from_date)) . "' ";
}
if (!empty($post->to_date)) {
$filter .= " AND incident_date <= '" . date("Y-m-d H:i:s", strtotime($post->to_date)) . "' ";
}
// JP: Search Filter
if ($filter_search) {
$where_string = "";
$or = "";
// Stop words that we won't search for
// Add words as needed!!
$stop_words = array('the', 'and', 'a', 'to', 'of', 'in', 'i', 'is', 'that', 'it', 'on', 'you', 'this', 'for', 'but', 'with', 'are', 'have', 'be', 'at', 'or', 'as', 'was', 'so', 'if', 'out', 'not');
// Phase 1 - Fetch the search string and perform initial sanitization
$keyword_raw = preg_replace('#/\\w+/#', '', $post->filter_search);
// Phase 2 - Strip the search string of any HTML and PHP tags that may be present for additional safety
$keyword_raw = strip_tags($keyword_raw);
// Phase 3 - Apply Kohana's XSS cleaning mechanism
$keyword_raw = $this->input->xss_clean($keyword_raw);
// Database instance
$db = new Database();
$keywords = explode(' ', $keyword_raw);
if (is_array($keywords) and !empty($keywords)) {
array_change_key_case($keywords, CASE_LOWER);
$i = 0;
foreach ($keywords as $value) {
if (!in_array($value, $stop_words) and !empty($value)) {
// Escape the string for query safety
$chunk = $db->escape_str($value);
if ($i > 0) {
$or = ' OR ';
}
$where_string = $where_string . $or . "(incident_title LIKE '%{$chunk}%' OR incident_description LIKE '%{$chunk}%')";
$i++;
}
}
}
$filter .= " AND " . $where_string;
}
// Retrieve reports
$incidents = ORM::factory('incident')->where($filter)->orderby('incident_dateadd', 'desc')->find_all();
// Retrieve categories
$categories = Category_Model::get_categories(FALSE, FALSE, FALSE);
// Retrieve Forms
$forms = ORM::Factory('form')->find_all();
// Retrieve Custom forms
$custom_forms = customforms::get_custom_form_fields();
// If CSV format is selected
if ($post->format == 'csv') {
$report_csv = download::download_csv($post, $incidents, $custom_forms);
// Output to browser
header("Content-type: text/x-csv");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Disposition: attachment; filename=" . time() . ".csv");
header("Content-Length: " . strlen($report_csv));
echo $report_csv;
exit;
}
// If XML format is selected
if ($post->format == 'xml') {
header('Content-type: text/xml; charset=UTF-8');
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Disposition: attachment; filename=" . time() . ".xml");
$content = download::download_xml($post, $incidents, $categories, $forms);
echo $content;
exit;
}
// JP: If HTML format is selected...
if ($post->format == 'html') {
$content = download::download_html($post, $incidents, $custom_forms);
header("Content-type: text/html; charset=UTF-8");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Disposition: attachment; filename=" . time() . ".html");
header("Content-Length: " . strlen($content));
echo $content;
exit;
}
} else {
// Repopulate the form fields
$form = arr::overwrite($form, $post->as_array());
// Populate the error fields, if any
$errors = arr::merge($errors, $post->errors('report'));
$form_error = TRUE;
}
}
$this->template->content->form = $form;
$this->template->content->errors = $errors;
$this->template->content->form_error = $form_error;
// Javascript Header
$this->themes->js = new View('admin/reports/download_js');
$this->themes->js->calendar_img = url::base() . "media/img/icon-calendar.gif";
}
/**
* 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();
// Filter by User
$user_id = (int) $user_id;
$u_sql = $user_id ? " AND user_id = " . $user_id . " " : "";
// Query to generate the report count
$sql = '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 ' . $table_prefix . 'incident ';
// Check if the range has been specified and is non-zero then add predicates to the query
if ($range != NULL and intval($range) > 0) {
$sql .= 'WHERE incident_date >= DATE_SUB(CURDATE(), INTERVAL ' . $db->escape_str($range) . ' DAY) ';
} else {
$sql .= 'WHERE 1=1 ';
}
// Group and order the records
$sql .= $u_sql . 'GROUP BY date ORDER BY incident_date ASC';
$query = $db->query($sql);
$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;
}
/**
* Function that saves incident geometries
*
* @param Incident_Model $incident
* @param mixed $incident
*
*/
public static function save_report_geometry($post, $incident)
{
// Delete all current geometry
ORM::factory('geometry')->where('incident_id', $incident->id)->delete_all();
if (isset($post->geometry)) {
// Database object
$db = new Database();
// SQL for creating the incident geometry
$sql = "INSERT INTO " . Kohana::config('database.default.table_prefix') . "geometry " . "(incident_id, geometry, geometry_label, geometry_comment, geometry_color, geometry_strokewidth) " . "VALUES(%d, GeomFromText('%s'), '%s', '%s', '%s', %s)";
foreach ($post->geometry as $item) {
if (!empty($item)) {
//Decode JSON
$item = json_decode($item);
//++ TODO - validate geometry
$geometry = isset($item->geometry) ? $db->escape_str($item->geometry) : "";
$label = isset($item->label) ? $db->escape_str(substr($item->label, 0, 150)) : "";
$comment = isset($item->comment) ? $db->escape_str(substr($item->comment, 0, 255)) : "";
$color = isset($item->color) ? $db->escape_str(substr($item->color, 0, 6)) : "";
$strokewidth = (isset($item->strokewidth) and (double) $item->strokewidth) ? (double) $item->strokewidth : "2.5";
if ($geometry) {
// Format the SQL string
$sql = sprintf($sql, $incident->id, $geometry, $label, $comment, $color, $strokewidth);
// Execute the query
$db->query($sql);
}
}
}
}
}
/**
* Build a search query with relevancy
* Stop word control included
*/
public function index($page = 1)
{
$this->template->content = new View('search');
$search_query = "";
$keyword_string = "";
$where_string = "";
$plus = "";
$or = "";
$search_info = "";
$html = "";
$pagination = "";
// Stop words that we won't search for
// Add words as needed!!
$stop_words = array('the', 'and', 'a', 'to', 'of', 'in', 'i', 'is', 'that', 'it', 'on', 'you', 'this', 'for', 'but', 'with', 'are', 'have', 'be', 'at', 'or', 'as', 'was', 'so', 'if', 'out', 'not');
if ($_GET) {
/**
* NOTES: 15/10/2010 - Emmanuel Kala <*****@*****.**>
*
* The search string undergoes a 3-phase sanitization process. This is not optimal
* but it works for now. The Kohana provided XSS cleaning mechanism does not expel
* content contained in between HTML tags this the "bruteforce" input sanitization.
*
* However, XSS is attempted using Javascript tags, Kohana's routing mechanism strips
* the "<script>" tags from the URL variables and passes inline text as part of the URL
* variable - This has to be fixed
*/
// Phase 1 - Fetch the search string and perform initial sanitization
$keyword_raw = isset($_GET['k']) ? preg_replace('#/\\w+/#', '', $_GET['k']) : "";
// Phase 2 - Strip the search string of any HTML and PHP tags that may be present for additional safety
$keyword_raw = strip_tags($keyword_raw);
// Phase 3 - Apply Kohana's XSS cleaning mechanism
$keyword_raw = $this->input->xss_clean($keyword_raw);
} else {
$keyword_raw = "";
}
// Database instance
$db = new Database();
$keywords = explode(' ', $keyword_raw);
if (is_array($keywords) and !empty($keywords)) {
array_change_key_case($keywords, CASE_LOWER);
$i = 0;
foreach ($keywords as $value) {
if (!in_array($value, $stop_words) and !empty($value)) {
// Escape the string for query safety
$chunk = $db->escape_str($value);
if ($i > 0) {
$plus = ' + ';
$or = ' OR ';
}
// Give relevancy weighting
// Title weight = 2
// Description weight = 1
$keyword_string = $keyword_string . $plus . "(CASE WHEN incident_title LIKE '%{$chunk}%' THEN 2 ELSE 0 END) + " . "(CASE WHEN incident_description LIKE '%{$chunk}%' THEN 1 ELSE 0 END) ";
$where_string = $where_string . $or . "(incident_title LIKE '%{$chunk}%' OR incident_description LIKE '%{$chunk}%')";
$i++;
}
}
if (!empty($keyword_string) and !empty($where_string)) {
// Limit the result set to only those reports that have been approved
$where_string = '(' . $where_string . ') AND incident_active = 1';
$search_query = "SELECT *, (" . $keyword_string . ") AS relevance FROM " . $this->table_prefix . "incident " . "WHERE " . $where_string . " " . "ORDER BY relevance DESC LIMIT ";
}
}
if (!empty($search_query)) {
// Pagination
$pagination = new Pagination(array('query_string' => 'page', 'items_per_page' => (int) Kohana::config('settings.items_per_page'), 'total_items' => ORM::factory('incident')->where($where_string)->count_all()));
$query = $db->query($search_query . $pagination->sql_offset . "," . (int) Kohana::config('settings.items_per_page'));
// Results Bar
if ($pagination->total_items != 0) {
$search_info .= "<div class=\"search_info\">" . Kohana::lang('ui_admin.showing_results') . ' ' . ($pagination->sql_offset + 1) . ' ' . Kohana::lang('ui_admin.to') . ' ' . ((int) Kohana::config('settings.items_per_page') + $pagination->sql_offset) . ' ' . Kohana::lang('ui_admin.of') . ' ' . $pagination->total_items . ' ' . Kohana::lang('ui_admin.searching_for') . ' <strong>' . $keyword_raw . "</strong>" . "</div>";
} else {
$search_info .= "<div class=\"search_info\">0 " . Kohana::lang('ui_admin.results') . "</div>";
$html .= "<div class=\"search_result\">";
$html .= "<h3>" . Kohana::lang('ui_admin.your_search_for') . "<strong> " . $keyword_raw . "</strong> " . Kohana::lang('ui_admin.match_no_documents') . "</h3>";
$html .= "</div>";
$pagination = "";
}
foreach ($query as $search) {
$incident_id = $search->id;
$incident_title = strip_tags($search->incident_title);
$highlight_title = "";
$incident_title_arr = explode(' ', $incident_title);
foreach ($incident_title_arr as $value) {
if (in_array(strtolower($value), $keywords) and !in_array(strtolower($value), $stop_words)) {
$highlight_title .= "<span class=\"search_highlight\">" . $value . "</span> ";
} else {
$highlight_title .= $value . " ";
}
}
// Remove any markup, otherwise trimming below will mess things up
$incident_description = strip_tags($search->incident_description);
// Trim to 180 characters without cutting words
if (strlen($incident_description) > 180 and strlen($incident_description) > 1) {
$whitespaceposition = strpos($incident_description, " ", 175) - 1;
$incident_description = substr($incident_description, 0, $whitespaceposition);
}
$highlight_description = "";
$incident_description_arr = explode(' ', $incident_description);
foreach ($incident_description_arr as $value) {
if (in_array(strtolower($value), $keywords) && !in_array(strtolower($value), $stop_words)) {
$highlight_description .= "<span class=\"search_highlight\">" . $value . "</span> ";
} else {
$highlight_description .= $value . " ";
}
}
$incident_date = date('D M j Y g:i:s a', strtotime($search->incident_date));
$html .= "<div class=\"search_result\">";
$html .= "<h3><a href=\"" . url::base() . "reports/view/" . $incident_id . "\">" . $highlight_title . "</a></h3>";
$html .= $highlight_description . " ...";
$html .= "<div class=\"search_date\">" . $incident_date . " | " . Kohana::lang('ui_admin.relevance') . ": <strong>+" . $search->relevance . "</strong></div>";
$html .= "</div>";
}
} else {
// Results Bar
$search_info .= "<div class=\"search_info\">0 " . Kohana::lang('ui_admin.results') . "</div>";
$html .= "<div class=\"search_result\">";
$html .= "<h3>" . Kohana::lang('ui_admin.your_search_for') . "<strong>" . $keyword_raw . "</strong> " . Kohana::lang('ui_admin.match_no_documents') . "</h3>";
$html .= "</div>";
}
$html .= $pagination;
$this->template->content->search_info = $search_info;
$this->template->content->search_results = $html;
// Rebuild Header Block
$this->template->header->header_block = $this->themes->header_block();
$this->template->footer->footer_block = $this->themes->footer_block();
}
/**
* Save user-specific plugin settings
*
* @param unknown_type $user_id
* @param string $settings JSON-encoded string, to be stored directly in the database
*/
function setUserSettings($user_id, $settings)
{
if (is_null($this->instance)) {
trigger_error("YOU MUST set the container first before trying to set a user's settings!");
}
$db = new Database();
$db->query("DELETE FROM user_plugin_settings WHERE user_id = " . $db->escape($user_id) . " AND plugin_name = '{$this->name}' AND container_id='{$this->container}'");
$db->query("INSERT INTO user_plugin_settings (plugin_name, user_id, container_id, settings) VALUES ('{$this->name}', " . $db->escape($user_id) . ", " . $db->escape($this->container) . ", '" . $db->escape_str($settings) . "')");
}
/**
* Helper function to fetch and optionally paginate the list of
* incidents/reports via the Incident Model using one or all of the
* following URL parameters
* - category
* - location bounds
* - incident mode
* - media
* - location radius
* JP: added one additional URL parameter:
* - search
*
* @param bool $paginate Optionally paginate the incidents - Default is FALSE
* @param int $items_per_page No. of items to show per page
* @return Database_Result
*/
public static function fetch_incidents($paginate = FALSE, $items_per_page = 0)
{
// Reset the paramters
self::$params = array();
// Initialize the category id
$category_id = 0;
$table_prefix = Kohana::config('database.default.table_prefix');
// Fetch the URL data into a local variable
$url_data = $_GET;
// Split selected parameters on ","
// For simplicity, always turn them into arrays even theres just one value
$exclude_params = array('c', 'v', 'm', 'mode', 'sw', 'ne', 'start_loc');
foreach ($url_data as $key => $value) {
if (in_array($key, $exclude_params) and !is_array($value)) {
$url_data[$key] = explode(",", $value);
}
}
//> BEGIN PARAMETER FETCH
//
// Check for the category parameter
//
if (isset($url_data['c']) and is_array($url_data['c'])) {
// Sanitize each of the category ids
$category_ids = array();
foreach ($url_data['c'] as $c_id) {
if (intval($c_id) > 0) {
$category_ids[] = intval($c_id);
}
}
// Check if there are any category ids
if (count($category_ids) > 0) {
$category_ids = implode(",", $category_ids);
array_push(self::$params, '(c.id IN (' . $category_ids . ') OR c.parent_id IN (' . $category_ids . '))', 'c.category_visible = 1');
}
}
//
// Incident modes
//
if (isset($url_data['mode']) and is_array($url_data['mode'])) {
$incident_modes = array();
// Sanitize the modes
foreach ($url_data['mode'] as $mode) {
if (intval($mode) > 0) {
$incident_modes[] = intval($mode);
}
}
// Check if any modes exist and add them to the parameter list
if (count($incident_modes) > 0) {
array_push(self::$params, 'i.incident_mode IN (' . implode(",", $incident_modes) . ')');
}
}
//
// Location bounds parameters
//
if (isset($url_data['sw']) and isset($url_data['ne'])) {
$southwest = $url_data['sw'];
$northeast = $url_data['ne'];
if (count($southwest) == 2 and count($northeast) == 2) {
$lon_min = (double) $southwest[0];
$lon_max = (double) $northeast[0];
$lat_min = (double) $southwest[1];
$lat_max = (double) $northeast[1];
// Add the location conditions to the parameter list
array_push(self::$params, 'l.latitude >= ' . $lat_min, 'l.latitude <= ' . $lat_max, 'l.longitude >= ' . $lon_min, 'l.longitude <= ' . $lon_max);
}
}
//
// Location bounds - based on start location and radius
//
if (isset($url_data['radius']) and isset($url_data['start_loc'])) {
//if $url_data['start_loc'] is just comma delimited strings, then make it into an array
if (intval($url_data['radius']) > 0 and is_array($url_data['start_loc'])) {
$bounds = $url_data['start_loc'];
if (count($bounds) == 2 and is_numeric($bounds[0]) and is_numeric($bounds[1])) {
self::$params['radius'] = array('distance' => intval($url_data['radius']), 'latitude' => $bounds[0], 'longitude' => $bounds[1]);
}
}
}
//
// Check for incident date range parameters
//
if (!empty($url_data['from'])) {
// Add hours/mins/seconds so we still get reports if from and to are the same day
$date_from = date('Y-m-d 00:00:00', strtotime($url_data['from']));
array_push(self::$params, 'i.incident_date >= "' . $date_from . '"');
}
if (!empty($url_data['to'])) {
// Add hours/mins/seconds so we still get reports if from and to are the same day
$date_to = date('Y-m-d 23:59:59', strtotime($url_data['to']));
array_push(self::$params, 'i.incident_date <= "' . $date_to . '"');
}
// Additional checks for date parameters specified in timestamp format
// This only affects those submitted from the main page
// Start Date
if (isset($_GET['s']) and intval($_GET['s']) > 0) {
$start_date = intval($_GET['s']);
array_push(self::$params, 'i.incident_date >= "' . date("Y-m-d H:i:s", $start_date) . '"');
}
// End Date
if (isset($_GET['e']) and intval($_GET['e'])) {
$end_date = intval($_GET['e']);
array_push(self::$params, 'i.incident_date <= "' . date("Y-m-d H:i:s", $end_date) . '"');
}
//
// Check for media type parameter
//
if (isset($url_data['m']) and is_array($url_data['m'])) {
// An array of media filters has been specified
// Validate the media types
$media_types = array();
foreach ($url_data['m'] as $media_type) {
if (intval($media_type) > 0) {
$media_types[] = intval($media_type);
}
}
if (count($media_types) > 0) {
array_push(self::$params, 'i.id IN (SELECT DISTINCT incident_id FROM ' . $table_prefix . 'media WHERE media_type IN (' . implode(",", $media_types) . '))');
}
}
//
// Check if the verification status has been specified
//
if (isset($url_data['v']) and is_array($url_data['v'])) {
$verified_status = array();
foreach ($url_data['v'] as $verified) {
if (intval($verified) >= 0) {
$verified_status[] = intval($verified);
}
}
if (count($verified_status) > 0) {
array_push(self::$params, 'i.incident_verified IN (' . implode(",", $verified_status) . ')');
}
}
//Search for Keyword in all Custom Form Fields
if (isset($url_data['custom_field_0'])) {
$keywords = $url_data['custom_field_0'];
$db = new Database();
$rows = $db->query('SELECT DISTINCT incident_id FROM ' . $table_prefix . 'form_response WHERE form_response LIKE "%' . $keywords . '%"');
$incident_ids = '';
foreach ($rows as $row) {
if ($incident_ids != '') {
$incident_ids .= ',';
}
$incident_ids .= $row->incident_id;
}
//make sure there are IDs found
if ($incident_ids != '') {
array_push(self::$params, 'i.id IN (' . $incident_ids . ')');
} else {
array_push(self::$params, 'i.id IN (0)');
}
}
// End of handling cff
//
// Check if they're filtering over custom form fields
//
if (isset($url_data['cff']) and is_array($url_data['cff'])) {
$where_text = "";
$i = 0;
foreach ($url_data['cff'] as $field) {
$field_id = $field[0];
if (intval($field_id) < 1) {
continue;
}
$field_value = $field[1];
if (is_array($field_value)) {
$field_value = implode(",", $field_value);
}
$i++;
if ($i > 1) {
$where_text .= " OR ";
}
$where_text .= "(form_field_id = " . intval($field_id) . " AND form_response LIKE '%" . Database::instance()->escape_str(trim($field_value)) . "%')";
}
// Make sure there was some valid input in there
if ($i > 0) {
// Get the valid IDs - faster in a separate query as opposed
// to a subquery within the main query
$db = new Database();
$rows = $db->query('SELECT DISTINCT incident_id FROM ' . $table_prefix . 'form_response WHERE ' . $where_text);
$incident_ids = '';
foreach ($rows as $row) {
if ($incident_ids != '') {
$incident_ids .= ',';
}
$incident_ids .= $row->incident_id;
}
//make sure there are IDs found
if ($incident_ids != '') {
array_push(self::$params, 'i.id IN (' . $incident_ids . ')');
} else {
array_push(self::$params, 'i.id IN (0)');
}
}
}
// End of handling cff
// JP: Check if the reports are being filtered via search.
if (isset($url_data['q']) and is_string($url_data['q'])) {
$filter_search_query = $url_data['q'];
if (!empty($filter_search_query)) {
$search_query = "";
$keyword_string = "";
$where_string = "";
$plus = "";
$or = "";
$search_info = "";
$html = "";
$pagination = "";
// Stop words that we won't search for
// Add words as needed!!
$stop_words = array('the', 'and', 'a', 'to', 'of', 'in', 'i', 'is', 'that', 'it', 'on', 'you', 'this', 'for', 'but', 'with', 'are', 'have', 'be', 'at', 'or', 'as', 'was', 'so', 'if', 'out', 'not');
// Phase 1 - Fetch the search string and perform initial sanitization
$keyword_raw = preg_replace('#/\\w+/#', '', $filter_search_query);
// Phase 2 - Strip the search string of any HTML and PHP tags that may be present for additional safety
$keyword_raw = strip_tags($keyword_raw);
// Phase 3 - Apply Kohana's XSS cleaning mechanism
$keyword_raw = security::xss_clean($keyword_raw);
// Database instance
$db = new Database();
$keywords = explode(' ', $keyword_raw);
if (is_array($keywords) and !empty($keywords)) {
array_change_key_case($keywords, CASE_LOWER);
$i = 0;
foreach ($keywords as $value) {
if (!in_array($value, $stop_words) and !empty($value)) {
// Escape the string for query safety
$chunk = $db->escape_str($value);
if ($i > 0) {
$plus = ' + ';
$or = ' OR ';
}
$where_string = $where_string . $or . "(incident_title LIKE '%{$chunk}%' OR incident_description LIKE '%{$chunk}%')";
$i++;
}
}
if (!empty($keyword_string) and !empty($where_string)) {
// Limit the result set to only those reports that have been approved
$where_string = '(' . $where_string . ') AND incident_active = 1';
$search_query = "SELECT *, (" . $keyword_string . ") AS relevance FROM " . $table_prefix . "incident " . "WHERE " . $where_string . " " . "ORDER BY relevance DESC LIMIT ?, ?";
}
}
$rows = $db->query('SELECT DISTINCT id FROM ' . $table_prefix . 'incident WHERE ' . $where_string);
$incident_ids = '';
foreach ($rows as $row) {
if ($incident_ids != '') {
$incident_ids .= ',';
}
$incident_ids .= $row->id;
}
//make sure there are IDs found
if ($incident_ids != '') {
array_push(self::$params, 'i.id IN (' . $incident_ids . ')');
} else {
array_push(self::$params, 'i.id IN (0)');
}
}
}
// In case a plugin or something wants to get in on the parameter fetching fun
Event::run('ushahidi_filter.fetch_incidents_set_params', self::$params);
//> END PARAMETER FETCH
// Check for order and sort params
$order_field = NULL;
$sort = NULL;
$order_options = array('title' => 'i.incident_title', 'date' => 'i.incident_date', 'id' => 'i.id');
if (isset($url_data['order']) and isset($order_options[$url_data['order']])) {
$order_field = $order_options[$url_data['order']];
}
if (isset($url_data['sort'])) {
$sort = strtoupper($url_data['sort']) == 'ASC' ? 'ASC' : 'DESC';
}
if ($paginate) {
// Fetch incident count
$incident_count = Incident_Model::get_incidents(self::$params, false, $order_field, $sort, TRUE);
// Set up pagination
$page_limit = intval($items_per_page) > 0 ? $items_per_page : intval(Kohana::config('settings.items_per_page'));
$total_items = $incident_count->current() ? $incident_count->current()->report_count : 0;
$pagination = new Pagination(array('style' => 'front-end-reports', 'query_string' => 'page', 'items_per_page' => $page_limit, 'total_items' => $total_items));
Event::run('ushahidi_filter.pagination', $pagination);
self::$pagination = $pagination;
// Return paginated results
return Incident_Model::get_incidents(self::$params, self::$pagination, $order_field, $sort);
} else {
// Return
return Incident_Model::get_incidents(self::$params, false, $order_field, $sort);
}
}
function index()
{
$this->template->this_page = 'download';
$this->template->header->this_page = 'download';
$this->template->content = new View('download_reports');
$this->template->content->calendar_img = url::base() . "media/img/icon-calendar.gif";
$this->template->content->title = Kohana::lang('ui_admin.download_reports');
// Javascript Header
$this->themes->js = new View('download_reports_js');
$this->themes->js->calendar_img = url::base() . "media/img/icon-calendar.gif";
$this->themes->treeview_enabled = TRUE;
$this->template->header->header_block = $this->themes->header_block();
$this->template->footer->footer_block = $this->themes->footer_block();
// Select first and last incident
$from = orm::factory('incident')->orderby('incident_date', 'asc')->find();
$to = orm::factory('incident')->orderby('incident_date', 'desc')->find();
$from_date = substr($from->incident_date, 5, 2) . "/" . substr($from->incident_date, 8, 2) . "/" . substr($from->incident_date, 0, 4);
$to_date = substr($to->incident_date, 5, 2) . "/" . substr($to->incident_date, 8, 2) . "/" . substr($to->incident_date, 0, 4);
$form = array('category' => '', 'verified' => '', 'category_all' => '', 'from_date' => '', 'to_date' => '', 'filter_search' => '');
$errors = $form;
$form_error = FALSE;
$form['from_date'] = $from_date;
$form['to_date'] = $to_date;
if ($_POST) {
// Instantiate Validation, use $post, so we don't overwrite $_POST fields with our own things
$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('category.*', 'required', 'numeric');
$post->add_rules('verified.*', 'required', 'numeric', 'between[0,1]');
$post->add_rules('formato', 'required', 'numeric', 'between[0,2]');
$post->add_rules('from_date', 'required', 'date_mmddyyyy');
$post->add_rules('to_date', 'required', 'date_mmddyyyy');
// Validate the report dates, if included in report filter
if (!empty($_POST['from_date']) && !empty($_POST['to_date'])) {
// TO Date not greater than FROM Date?
if (strtotime($_POST['from_date']) > strtotime($_POST['to_date'])) {
$post->add_error('to_date', 'range_greater');
}
}
// $post validate check
if ($post->validate()) {
// Check child categories too
$categories = ORM::factory('category')->select('id')->in('parent_id', $post->category)->find_all();
foreach ($categories as $cat) {
$post->category[] = $cat->id;
}
$incident_query = ORM::factory('incident')->select('DISTINCT incident.id')->select('incident.*')->where('incident_active', 1);
$incident_query->in('category_id', $post->category);
// If only unverified selected
if (in_array('0', $post->verified) && !in_array('1', $post->verified)) {
$incident_query->where('incident_verified', 0);
} elseif (!in_array('0', $post->verified) && in_array('1', $post->verified)) {
$incident_query->where('incident_verified', 1);
}
// else - do nothing
// Report Date Filter
if (!empty($post->from_date) && !empty($post->to_date)) {
// JP: Added the times 00:00:00 for the from_date and 23:59:59 for the to_date; this fixes the bug where reports made on the day of the to_date would not be included since the time was not specified.
$incident_query->where(array('incident_date >=' => date("Y-m-d 00:00:00", strtotime($post->from_date)), 'incident_date <=' => date("Y-m-d 23:59:59", strtotime($post->to_date))));
}
// JP: Search Filter
if (!empty($post->filter_search)) {
$where_string = "";
$or = "";
// Stop words that we won't search for
// Add words as needed!!
$stop_words = array('the', 'and', 'a', 'to', 'of', 'in', 'i', 'is', 'that', 'it', 'on', 'you', 'this', 'for', 'but', 'with', 'are', 'have', 'be', 'at', 'or', 'as', 'was', 'so', 'if', 'out', 'not');
// Phase 1 - Fetch the search string and perform initial sanitization
$keyword_raw = preg_replace('#/\\w+/#', '', $post->filter_search);
// Phase 2 - Strip the search string of any HTML and PHP tags that may be present for additional safety
$keyword_raw = strip_tags($keyword_raw);
// Phase 3 - Apply Kohana's XSS cleaning mechanism
$keyword_raw = $this->input->xss_clean($keyword_raw);
// Database instance
$db = new Database();
$keywords = explode(' ', $keyword_raw);
if (is_array($keywords) and !empty($keywords)) {
array_change_key_case($keywords, CASE_LOWER);
$i = 0;
foreach ($keywords as $value) {
if (!in_array($value, $stop_words) and !empty($value)) {
// Escape the string for query safety
$chunk = $db->escape_str($value);
if ($i > 0) {
$or = ' OR ';
}
$where_string = $where_string . $or . "(incident_title LIKE '%{$chunk}%' OR incident_description LIKE '%{$chunk}%')";
$i++;
}
}
}
$incident_query->where($where_string);
}
$incidents = $incident_query->join('incident_category', 'incident_category.incident_id', 'incident.id', 'INNER')->orderby('incident_date', 'desc')->find_all();
// CSV selected
if ($post->formato == 0) {
$report_csv = "#,INCIDENT TITLE,INCIDENT DATE,LOCATION,DESCRIPTION,CATEGORY,LATITUDE,LONGITUDE,APPROVED,VERIFIED\n";
foreach ($incidents as $incident) {
$new_report = array();
array_push($new_report, '"' . $incident->id . '"');
array_push($new_report, '"' . $this->_csv_text($incident->incident_title) . '"');
array_push($new_report, '"' . $incident->incident_date . '"');
array_push($new_report, '"' . $this->_csv_text($incident->location->location_name) . '"');
array_push($new_report, '"' . $this->_csv_text($incident->incident_description) . '"');
$catstring = '"';
$catcnt = 0;
foreach ($incident->incident_category as $category) {
if ($catcnt > 0) {
$catstring .= ",";
}
if ($category->category->category_title) {
$catstring .= $this->_csv_text($category->category->category_title);
}
$catcnt++;
}
$catstring .= '"';
array_push($new_report, $catstring);
array_push($new_report, '"' . $incident->location->latitude . '"');
array_push($new_report, '"' . $incident->location->longitude . '"');
if ($incident->incident_active) {
array_push($new_report, "YES");
} else {
array_push($new_report, "NO");
}
if ($incident->incident_verified) {
array_push($new_report, "YES");
} else {
array_push($new_report, "NO");
}
array_push($new_report, "\n");
$repcnt = 0;
foreach ($new_report as $column) {
if ($repcnt > 0) {
$report_csv .= ",";
}
$report_csv .= $column;
$repcnt++;
}
}
// Output to browser
header("Content-type: text/x-csv");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Disposition: attachment; filename=" . time() . ".csv");
header("Content-Length: " . strlen($report_csv));
echo $report_csv;
exit;
} else {
if ($post->formato == 1) {
$categories = ORM::factory('category')->where('category_visible', 1)->find_all();
header("Content-Type: application/vnd.google-earth.kml+xml");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Disposition: attachment; filename=" . time() . ".kml");
$view = new View("kml");
$view->kml_name = htmlspecialchars(Kohana::config('settings.site_name'));
$view->items = $incidents;
$view->categories = $categories;
$view->render(TRUE);
exit;
} else {
$h = array();
$h[] = '<!DOCTYPE html>';
$h[] = ' <head>';
$h[] = ' <title>';
$h[] = ' Downloaded Reports';
$h[] = ' </title>';
$h[] = ' <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />';
$h[] = ' <style>';
$h[] = ' table { border-collapse: collapse; }';
$h[] = ' table thead tr { background: #CCCCCC; }';
$h[] = ' table thead tr th, table tbody tr td { padding: 0.75em; border: 1px solid #AAAAAA; }';
$h[] = ' table tbody tr.even, table tbody tr.alt, table tbody tr:nth-of-type(even) { background: #DDDDDD; }';
$h[] = ' </style>';
$h[] = ' </head>';
$h[] = ' <body>';
$h[] = ' <table>';
$h[] = ' <thead>';
$h[] = ' <tr>';
$h[] = ' <th>';
$h[] = ' #';
$h[] = ' </th>';
$h[] = ' <th>';
$h[] = ' TITLE';
$h[] = ' </th>';
$h[] = ' <th>';
$h[] = ' DATE';
$h[] = ' </th>';
$h[] = ' <th>';
$h[] = ' LOCATION';
$h[] = ' </th>';
$h[] = ' <th>';
$h[] = ' DESCRIPTION';
$h[] = ' </th>';
$h[] = ' <th>';
$h[] = ' CATEGORY';
$h[] = ' </th>';
$h[] = ' <th>';
$h[] = ' LATITUDE';
$h[] = ' </th>';
$h[] = ' <th>';
$h[] = ' LONGITUDE';
$h[] = ' </th>';
$h[] = ' <th>';
$h[] = ' APPROVED';
$h[] = ' </th>';
$h[] = ' <th>';
$h[] = ' VERIFIED';
$h[] = ' </th>';
$h[] = ' </tr>';
$h[] = ' </thead>';
$h[] = ' <tbody>';
foreach ($incidents as $r) {
$h[] = ' <tr>';
$h[] = ' <td>';
$h[] = ' ' . $r->id;
$h[] = ' </td>';
$h[] = ' <td>';
$h[] = ' ' . $r->incident_title;
$h[] = ' </td>';
$h[] = ' <td>';
$h[] = ' ' . $r->incident_date;
$h[] = ' </td>';
$h[] = ' <td>';
$h[] = ' ' . $r->location->location_name;
$h[] = ' </td>';
$h[] = ' <td>';
$h[] = ' ' . $r->incident_description;
$h[] = ' </td>';
$h[] = ' <td>';
$cats = '';
$cnt = 0;
foreach ($r->incident_category as $cat) {
if ($cnt > 0) {
$cats .= ", ";
}
if ($cat->category->category_title) {
$cats .= $cat->category->category_title;
}
$cnt++;
}
$h[] = ' ' . $cats;
$h[] = ' </td>';
$h[] = ' <td>';
$h[] = ' ' . $r->location->latitude;
$h[] = ' </td>';
$h[] = ' <td>';
$h[] = ' ' . $r->location->longitude;
$h[] = ' </td>';
$h[] = ' <td>';
if ($r->incident_active) {
$h[] = ' YES';
} else {
$h[] = ' NO';
}
$h[] = ' </td>';
$h[] = ' <td>';
if ($r->incident_verified) {
$h[] = ' YES';
} else {
$h[] = ' NO';
}
$h[] = ' </td>';
$h[] = ' </tr>';
}
$h[] = ' </tbody>';
$h[] = ' </table>';
$h[] = ' </body>';
$h[] = '</html>';
$f = implode("\n", $h);
header("Content-type: text/html; charset=UTF-8");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Disposition: attachment; filename=" . time() . ".html");
header("Content-Length: " . strlen($f));
echo $f;
exit;
}
}
} else {
// repopulate the form fields
$form = arr::overwrite($form, $post->as_array());
// populate the error fields, if any
$errors = arr::overwrite($errors, $post->errors('download_reports'));
$form_error = TRUE;
}
}
$this->template->content->form = $form;
$this->template->content->errors = $errors;
$this->template->content->form_error = $form_error;
}
