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