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