/** * Generate table of CDRs. * * @return void * @access public */ public function table() { // CSV mode? $csvmode = isset($this->get["csv"]) && $this->get["csv"] == "on" ? true : false; if (isset($this->get["number"])) { // restrict by one specific number $number = $this->get["number"]; // retrieve records $cdrs = $this->db->GetAssoc("\n\t\t\t\tSELECT\t" . DB_TABLE . ".uniqueid, " . DB_TABLE . ".*,\n\t\t\t\t\tSEC_TO_TIME(" . DB_TABLE . ".duration) AS formatted_duration,\n\t\t\t\t\tSEC_TO_TIME(" . DB_TABLE . ".billsec) AS formatted_billsec\n\t\t\t\tFROM " . DB_TABLE . "\n\t\t\t\tWHERE clid = '{$number}' OR src = '{$number}' OR dst = '{$number}'\n\t\t\t\tORDER BY calldate ASC;\n\t\t\t"); } else { // restrict by date range // determine dates if (isset($this->get["year"])) { // calculate overview for year $from = "'{$this->get['year']}-01-01 00:00:00'"; $to = "DATE_ADD('{$this->get['year']}-01-01 00:00:00', INTERVAL 1 YEAR)"; if ($csvmode) { $csvlabel = $this->get['year']; } } else { if (isset($this->get["month"])) { // calculate overview for month $from = "'{$this->get['month']}-01 00:00:00'"; $to = "DATE_ADD('{$this->get['month']}-01 00:00:00', INTERVAL 1 MONTH)"; if ($csvmode) { $csvlabel = $this->get['month']; } } else { // no date data passed, just do today $today = date("Y-m-d"); $from = "'{$today} 00:00:00'"; $to = "DATE_ADD('{$today} 00:00:00', INTERVAL 1 DAY)"; if ($csvmode) { $csvlabel = $today; } } } // retrieve records $cdrs = $this->db->GetAssoc("\n\t\t\t\tSELECT\t" . DB_TABLE . ".uniqueid, " . DB_TABLE . ".*,\n\t\t\t\t\tSEC_TO_TIME(" . DB_TABLE . ".duration) AS formatted_duration,\n\t\t\t\t\tSEC_TO_TIME(" . DB_TABLE . ".billsec) AS formatted_billsec\n\t\t\t\tFROM " . DB_TABLE . "\n\t\t\t\tWHERE calldate >= {$from} AND calldate < {$to}\n\t\t\t\tORDER BY calldate ASC;\n\t\t\t"); } if ($csvmode) { // export data as CSV file $this->utils->csv_export($cdrs, "agcdr-cdrs-{$csvlabel}.csv"); exit; } else { // build CSV request variables if (isset($this->get["year"])) { $csvrequest = "year={$this->get["year"]}"; } if (isset($this->get["month"])) { $csvrequest = "month={$this->get["month"]}"; } $csvrequest .= "&csv=on"; $this->template->csvrequest = $csvrequest; // calculate totals $totals = LocalLib::calculate_duration_totals($cdrs); // assign to template and render page $this->template->cdrs = $cdrs; $this->template->totals = $totals; $this->template->menuoptions = $this->template->datatablesRecordCountMenu(count($cdrs)); $this->template->show("table"); } }
/** * Perform search and return results. * * @return void * @access public */ public function results() { // CSV mode? $csvmode = isset($_POST["csv"]) ? true : false; // is this a quick search? if (isset($_POST["quicksearch"])) { // clean input $search = $this->clean_query($_POST["quicksearch"]); // check that keyword is at least 3 characters in length // this is checked by JavaScript, but need to check just in case if (strlen($search) < 3) { return false; } // build query foreach (array_keys(get_object_vars(new cdr())) as $field) { if ($field != "id") { $where[] = "{$field} LIKE '%{$search}%'"; } } $sql = "SELECT\t" . DB_TABLE . ".uniqueid, " . DB_TABLE . ".*,\n\t\t\t\t\tSEC_TO_TIME(" . DB_TABLE . ".duration) AS formatted_duration,\n\t\t\t\t\tSEC_TO_TIME(" . DB_TABLE . ".billsec) AS formatted_billsec\n\t\t\t\tFROM " . DB_TABLE . "\n\t\t\t\tWHERE " . implode(" OR ", $where) . "\n\t\t\t\tORDER BY calldate DESC;\n\t\t\t"; // run query $results = $this->db->GetAssoc($sql); // set quick search string back in template if (!$csvmode) { $this->template->quicksearch = $search; } } else { // it's a proper search // is it a single-day date-only search? if ($_POST["field_1"] == "calldate") { $single_day_search = true; } // retrieve search criteria $criteria = array(); foreach ($_POST as $key => $value) { if (substr($key, 0, 9) == "criteria_" && strlen($value) >= 3) { $row = substr($key, -1, 1); array_push($criteria, array("field" => $_POST["field_{$row}"], "operator" => $_POST["operator_{$row}"], "keywords" => $this->clean_query($value))); } } // process as long as there's one set of criteria if (count($criteria) > 0) { // build query $where = array(); if (!isset($single_day_search)) { $where[] = strftime("calldate >= '%Y/%m/%d 00:00:00'", strtotime($_POST["date_from"])); $where[] = strftime("calldate <= '%Y/%m/%d 23:59:59'", strtotime($_POST["date_to"])); } foreach ($criteria as $crit) { switch ($crit['operator']) { case "contains": $keywords = "LIKE '%{$crit['keywords']}%'"; break; case "equals": $keywords = "= '{$crit['keywords']}'"; break; case "starts": $keywords = "LIKE '{$crit['keywords']}%'"; break; case "ends": $keywords = "LIKE '%{$crit['keywords']}%"; break; case "ltet": $keywords = "<= '{$crit['keywords']}'"; break; case "gtet": $keywords = ">= '{$crit['keywords']}'"; break; } $where[] = "{$crit['field']} {$keywords}"; } $sql = "SELECT\t" . DB_TABLE . ".uniqueid, " . DB_TABLE . ".*,\n\t\t\t\t\t\tSEC_TO_TIME(" . DB_TABLE . ".duration) AS formatted_duration,\n\t\t\t\t\t\tSEC_TO_TIME(" . DB_TABLE . ".billsec) AS formatted_billsec\n\t\t\t\t\tFROM " . DB_TABLE . "\n\t\t\t\t\tWHERE " . implode(" AND ", $where) . "\n\t\t\t\t\tORDER BY calldate DESC;\n\t\t\t\t"; // run query $results = $this->db->GetAssoc($sql); // set criteria back in template if (!$csvmode) { $this->template->criteria = $criteria; $this->template->date_from = $_POST["date_from"]; $this->template->date_to = $_POST["date_to"]; if (isset($single_day_search)) { $this->template->single_day_search = true; } } } } if ($csvmode) { // export data as CSV file $this->utils->csv_export($results, "agcdr-search-results.csv"); exit; } else { // render results normally // calculate totals $totals = LocalLib::calculate_duration_totals($results); // set results and other data in template $this->template->results = $results; $this->template->totals = $totals; $this->template->menuoptions = $this->template->datatablesRecordCountMenu(count($results)); // prepare JSON for CSV download button $ovars = $_POST; $ovars["csv"] = "on"; $this->template->csvjson = json_encode($ovars); // render page $this->template->show("results"); } }