/**
* 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";
}
/**
* Download Reports in CSV format
*/
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' => '');
// 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;
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;
}
// 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)) . "' ";
}
// 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;
}
} 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";
}
/**
* Test CSV Download
* @test
*/
public function testDownloadCSV()
{
// Test to ensure validation passed
$this->assertEquals(TRUE, download::validate($this->post), 'Report download validation failed');
// If we have no reports
if (count($this->incident) == 0) {
$this->markTestSkipped('There are no reports, CSV Download test skipped');
}
$expected_csv_content = "\"#\",\"FORM #\",\"INCIDENT TITLE\",\"INCIDENT DATE\"";
// Include location information?
if (in_array(1, $this->post['data_include'])) {
$expected_csv_content .= ",\"LOCATION\"";
}
// Include description information?
if (in_array(2, $this->post['data_include'])) {
$expected_csv_content .= ",\"DESCRIPTION\"";
}
// Include category information?
if (in_array(3, $this->post['data_include'])) {
$expected_csv_content .= ",\"CATEGORY\"";
}
// Include latitude information?
if (in_array(4, $this->post['data_include'])) {
$expected_csv_content .= ",\"LATITUDE\"";
}
// Include longitude information?
if (in_array(5, $this->post['data_include'])) {
$expected_csv_content .= ",\"LONGITUDE\"";
}
// Include custom forms information?
if (in_array(6, $this->post['data_include'])) {
foreach ($this->custom_forms as $field_name) {
$expected_csv_content .= ",\"" . $field_name['field_name'] . "-" . $field_name['form_id'] . "\"";
}
}
// Include personal information?
if (in_array(7, $this->post['data_include'])) {
$expected_csv_content .= ",\"FIRST NAME\",\"LAST NAME\",\"EMAIL\"";
}
$expected_csv_content .= ",\"APPROVED\",\"VERIFIED\"";
$expected_csv_content .= "\r\n";
// Add Report information
$report = $this->incident[0];
// Report id, form_id, title, and date
$expected_csv_content .= '"' . $report->id . '",' . '"' . $report->form_id . '",' . '"' . $report->incident_title . '",' . '"' . $report->incident_date . '"';
// Include location information?
if (in_array(1, $this->post['data_include'])) {
$expected_csv_content .= ',"' . $report->location->location_name . '"';
}
// Include description information?
if (in_array(2, $this->post['data_include'])) {
$expected_csv_content .= ',"' . $report->incident_description . '"';
}
// Include category information?
if (in_array(3, $this->post['data_include'])) {
$cat = array();
foreach ($report->incident_category as $category) {
if ($category->category->category_title) {
$cat[] = $category->category->category_title;
}
}
$expected_csv_content .= ',"' . implode($cat, ',') . '"';
}
// Include latitude information?
if (in_array(4, $this->post['data_include'])) {
$expected_csv_content .= ',"' . $report->location->latitude . '"';
}
// Include longitude information?
if (in_array(5, $this->post['data_include'])) {
$expected_csv_content .= ',"' . $report->location->longitude . '"';
}
// Include custom forms information?
if (in_array(6, $this->post['data_include'])) {
$custom_fields = customforms::get_custom_form_fields($report->id, '', false);
if (!empty($custom_fields)) {
foreach ($custom_fields as $custom_field) {
$expected_csv_content .= ',"' . $custom_field['field_response'] . '"';
}
} else {
foreach ($this->custom_forms as $custom) {
$expected_csv_content .= ',""';
}
}
}
// Include personal information?
if (in_array(7, $this->post['data_include'])) {
$person = $report->incident_person;
if ($person->loaded) {
$expected_csv_content .= ',"' . $person->person_first . '"' . ',"' . $person->person_last . '"' . ',"' . $person->person_email . '"';
} else {
$expected_csv_content .= ',""' . ',""' . ',""';
}
}
// Approved status
if ($report->incident_active) {
$expected_csv_content .= ",\"YES\"";
} else {
$expected_csv_content .= ",\"NO\"";
}
// Verified Status
if ($report->incident_verified) {
$expected_csv_content .= ",\"YES\"";
} else {
$expected_csv_content .= ",\"NO\"";
}
// End Expected output
$expected_csv_content .= "\r\n";
// Grab actual output
$actual_csv_content = download::download_csv($this->post, $this->incident, $this->custom_forms);
// Test CSV Output
$this->assertEquals($expected_csv_content, $actual_csv_content, 'CSV Download failed. Content mismatch');
}