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