From 7a3a509ad4b647c6060728dcdd7781a1320b4b9d Mon Sep 17 00:00:00 2001 From: Ben Klang Date: Fri, 27 Jun 2008 17:17:10 +0000 Subject: [PATCH] Add more monthly call stats and take a shot at graphing them. --- graphgen.php | 33 ++++++++++++++ lib/Driver/asterisksql.php | 73 +++++++++++++++++++++++-------- lib/Form/SearchCDR.php | 5 ++- lib/base.php | 6 ++- viewgraph.php | 105 ++++++++++++++++++++++++++++++++++----------- 5 files changed, 177 insertions(+), 45 deletions(-) create mode 100644 graphgen.php diff --git a/graphgen.php b/graphgen.php new file mode 100644 index 000000000..149ea4ae2 --- /dev/null +++ b/graphgen.php @@ -0,0 +1,33 @@ + + * + * See the enclosed file COPYING for license information (GPL). If you + * did not receive this file, see http://www.fsf.org/copyleft/gpl.html. + * + * @author Ben Klang + */ + +@define('OPERATOR_BASE', dirname(__FILE__)); +require_once OPERATOR_BASE . '/lib/base.php'; + +// Load PEAR's Image_Graph library +require_once 'Image/Graph.php'; + +$graphtype = Util::getFormData('graph'); +#$graphname = Util::getFormData('name'); +$cachekey = Util::getFormData('key'); + +$stats = unserialize($cache->get($cachekey, 0)); +Horde::logMessage(print_r($stats, true), __FILE__, __LINE__, PEAR_LOG_ERR); +$graph = Image_Graph::factory('graph', array(600, 400)); +$plotarea = $graph->addNew('plotarea'); +$dataset = Image_Graph::factory('dataset'); +foreach ($stats as $month => $stats) { + $dataset->addPoint($month, $stats[$graphtype]); +} +$plot = $plotarea->addNew('bar', $dataset); +$graph->done(); + diff --git a/lib/Driver/asterisksql.php b/lib/Driver/asterisksql.php index 6af916502..28ef3b81c 100644 --- a/lib/Driver/asterisksql.php +++ b/lib/Driver/asterisksql.php @@ -20,7 +20,7 @@ * The table structure can be created by the scripts/sql/operator_foo.sql * script. * - * $Horde: incubator/operator/lib/Driver/asterisksql.php,v 1.3 2008/06/26 19:23:17 bklang Exp $ + * $Horde: incubator/operator/lib/Driver/asterisksql.php,v 1.4 2008/06/27 17:17:11 bklang Exp $ * * Copyright 2007-2008 The Horde Project (http://www.horde.org/) * @@ -109,10 +109,16 @@ class Operator_Driver_asterisksql extends Operator_Driver { // Filter by account code if ($accountcode !== null) { - $sql .= ' WHERE accountcode = ? '; + $sql .= ' WHERE accountcode LIKE ? '; $values[] = $accountcode; } + // Filter by destination context + if ($dcontext !== null) { + $sql .= ' WHERE dcontext LIKE ? '; + $values[] = $dcontext; + } + /* Make sure we have a valid database connection. */ $this->_connect(); @@ -142,7 +148,8 @@ class Operator_Driver_asterisksql extends Operator_Driver { * method will additionall return PEAR_Error * on failure. */ - function getCallStats($start, $end, $accountcode = null, $dcontext = null) + function getCallStatsByMonth($start, $end, $accountcode = null, + $dcontext = null) { if (!is_a($start, 'Horde_Date') || !is_a($end, 'Horde_Date')) { Horde::logMessage('Start ane end date must be Horde_Date objects.', __FILE__, __LINE__, PEAR_LOG_ERR); @@ -156,41 +163,58 @@ class Operator_Driver_asterisksql extends Operator_Driver { $start->mday = 1; $end->mday = Horde_Date::daysInMonth($end->month, $end->year); - $sql = 'SELECT COUNT(*) AS count FROM ' . $this->_params['table'] . - ' WHERE 1=1'; - // Use 1=1 to make constructing the string easier + + // Construct the queries we will be running below + // Use 1=1 to make constructing the filter string easier + $numcalls_query = 'SELECT COUNT(*) AS count FROM ' . + $this->_params['table'] . ' WHERE 1=1'; + + $minutes_query = 'SELECT SUM(duration)/60 AS minutes FROM ' . + $this->_params['table'] . ' WHERE 1=1'; + + $failed_query = 'SELECT COUNT(disposition) AS failed FROM ' . + $this->_params['table'] . ' WHERE ' . + 'disposition="failed"'; $values = array(); + // Shared SQL filter + $filter = ''; + // Filter by account code if ($accountcode !== null) { - $sql .= ' AND accountcode = ?'; + $filter .= ' AND accountcode LIKE ?'; $values[] = $accountcode; } // Filter by destination context if ($dcontext !== null) { - $sql .= ' AND dcontext = ?'; + $filter .= ' AND dcontext LIKE ?'; $values[] = $dcontext; } - $sql .= ' AND calldate >= ? AND calldate < ?'; + // Filter by the date range (filled in below) + $filter .= ' AND calldate >= ? AND calldate < ?'; $stats = array(); + // Copy the object so we can reuse the start date below + $curdate = new Horde_Date($start); + // FIXME: Is there a more efficient way to do this? Perhaps // lean more on the SQL engine? - while($start->compareDate($end) <= 0) { + while($curdate->compareDate($end) <= 0) { $curvalues = $values; - $curvalues[] = $start->strftime('%Y-%m-%d %T'); + $curvalues[] = $curdate->strftime('%Y-%m-%d %T'); // Index for the results array - $index = $start->strftime('%Y-%m'); + $index = $curdate->strftime('%Y-%m'); // Find the first day of the next month - $start->month++; - $start->correct(); - $curvalues[] = $start->strftime('%Y-%m-%d %T'); + $curdate->month++; + $curdate->correct(); + $curvalues[] = $curdate->strftime('%Y-%m-%d %T'); + $sql = $numcalls_query . $filter; /* Log the query at a DEBUG log level. */ Horde::logMessage(sprintf('Operator_Driver_asterisksql::getCallStats(): %s', $sql), __FILE__, __LINE__, PEAR_LOG_DEBUG); @@ -199,10 +223,25 @@ class Operator_Driver_asterisksql extends Operator_Driver { Horde::logMessage($res, __FILE__, __LINE__, PEAR_LOG_ERR); return PEAR::raiseError(_("Internal error. Details have been logged for the administrator.")); } - $stats[$index]['numcalls'] = $res; - // TODO: Add more monthly statistics + $sql = $minutes_query . $filter; + Horde::logMessage(sprintf('Operator_Driver_asterisksql::getCallStats(): %s', $sql), __FILE__, __LINE__, PEAR_LOG_DEBUG); + $res = $this->_db->getOne($minutes_query . $filter, $curvalues); + if (is_a($res, 'PEAR_Error')) { + Horde::logMessage($res, __FILE__, __LINE__, PEAR_LOG_ERR); + return PEAR::raiseError(_("Internal error. Details have been logged for the administrator.")); + } + $stats[$index]['minutes'] = $res; + + $sql = $failed_query . $filter; + Horde::logMessage(sprintf('Operator_Driver_asterisksql::getCallStats(): %s', $sql), __FILE__, __LINE__, PEAR_LOG_DEBUG); + $res = $this->_db->getOne($sql, $curvalues); + if (is_a($res, 'PEAR_Error')) { + Horde::logMessage($res, __FILE__, __LINE__, PEAR_LOG_ERR); + return PEAR::raiseError(_("Internal error. Details have been logged for the administrator.")); + } + $stats[$index]['failed'] = $res; } return $stats; diff --git a/lib/Form/SearchCDR.php b/lib/Form/SearchCDR.php index 2e1a54224..fb768266a 100644 --- a/lib/Form/SearchCDR.php +++ b/lib/Form/SearchCDR.php @@ -2,7 +2,7 @@ /** * SearchCDRForm Class * - * $Horde: incubator/operator/lib/Form/SearchCDR.php,v 1.1 2008/04/19 01:26:06 bklang Exp $ + * $Horde: incubator/operator/lib/Form/SearchCDR.php,v 1.2 2008/06/27 17:17:11 bklang Exp $ * * Copyright 2008 Alkaloid Networks LLC * @@ -61,7 +61,8 @@ class SearchCDRForm extends Horde_Form { $params = array($start_year, $end_year, $picker, $format_in, $format_out, $show_seconds); - $this->addVariable(_("Account Code"), 'accountcode', 'text', false, false, _("An empty account code will only match records with no account.")); + $this->addVariable(_("Account Code"), 'accountcode', 'text', false, false, _("An empty account code will select records with an empty account code. To search for all records account codes use %")); + $this->addVariable(_("Destination Context"), 'dcontext', 'text', false, false, _("An empty destination context will select records with an empty destination context. To search for all destination contexts use %")); $this->addVariable(_("Start Date/Time"), 'startdate', 'datetime', true, false, null, $params); $this->addVariable(_("End Date/Time"), 'enddate', 'datetime', true, false, null, $params); } diff --git a/lib/base.php b/lib/base.php index 53376aea1..0e9fbdc7f 100644 --- a/lib/base.php +++ b/lib/base.php @@ -2,7 +2,7 @@ /** * Operator base application file. * - * $Horde: incubator/operator/lib/base.php,v 1.2 2008/06/27 04:03:51 bklang Exp $ + * $Horde: incubator/operator/lib/base.php,v 1.3 2008/06/27 17:17:11 bklang Exp $ * * This file brings in all of the dependencies that every Operator script will * need, and sets up objects that all scripts use. @@ -42,5 +42,9 @@ require_once OPERATOR_BASE . '/lib/Operator.php'; require_once OPERATOR_BASE . '/lib/Driver.php'; $GLOBALS['operator_driver'] = Operator_Driver::factory(); +// Caching system for storing DB results +$cache = &Horde_Cache::singleton($GLOBALS['conf']['cache']['driver'], + Horde::getDriverConfig('cache', $GLOBALS['conf']['cache']['driver'])); + // Start output compression. Horde::compressOutput(); diff --git a/viewgraph.php b/viewgraph.php index a13ef5f27..007720a43 100644 --- a/viewgraph.php +++ b/viewgraph.php @@ -1,6 +1,6 @@ * @@ -19,32 +19,87 @@ require_once 'Horde/Form/Renderer.php'; require_once 'Horde/Variables.php'; require_once OPERATOR_BASE . '/lib/Form/SearchCDR.php'; -// Load PEAR's Image_Graph library -require_once 'Image/Graph.php'; - $renderer = new Horde_Form_Renderer(); $vars = Variables::getDefaultVariables(); -$startdate = array('year' => 2007, - 'month' => 1, - 'mday' => 1); -$enddate = array('year' => date('Y'), - 'month' => date('n'), - 'mday' => date('j')); - -$startdate = new Horde_Date($startdate); -$enddate = new Horde_Date($enddate); -$accountcode = null; -$dcontext = null; - -$stats = $operator_driver->getCallStats($startdate, $enddate, $accountcode, $dcontext); - -$graph = Image_Graph::factory('graph', array(600, 400)); -$plotarea = $graph->addNew('plotarea'); -$dataset = Image_Graph::factory('dataset'); -foreach ($stats as $month => $stats) { - $dataset->addPoint($month, $stats['numcalls']); +$form = new SearchCDRForm($vars); +if ($form->isSubmitted() && $form->validate($vars, true)) { + if ($vars->exists('accountcode')) { + $accountcode = $vars->get('accountcode'); + } else { + // Search all accounts. + $accountcode = null; + } + if ($vars->exists('dcontext')) { + $dcontext = $vars->get('dcontext'); + } else { + // Search all contexts. + $dcontext = null; + } + $start = new Horde_Date($vars->get('startdate')); + $end = new Horde_Date($vars->get('enddate')); + + // See if we have cached data + $cachekey = md5(serialize(array('getCallStatsByMonth', $start, $end, + $accountcode, $dcontext))); + // Use 0 lifetime to allow cache lifetime to be set when storing the object + $stats = $cache->get($cachekey, 0); + if ($stats === false) { + $stats = $operator_driver->getCallStatsByMonth($start, $end, + $accountcode, $dcontext); + $res = $cache->set($cachekey, serialize($stats), 600); + if ($res === false) { + Horde::logMessage('The cache system has experienced an error. Unable to continue.', __FILE__, __LINE__, PEAR_LOG_ERR); + $notification->push(_("Internal error. Details have been logged for the administrator.")); + unset($stats); + } + } else { + // Cached data is stored serialized + $stats = unserialize($stats); + } + $_SESSION['operator']['lastsearch']['params'] = array( + 'accountcode' => $vars->get('accountcode'), + 'startdate' => $vars->get('startdate'), + 'enddate' => $vars->get('enddate')); +} else { + if (isset($_SESSION['operator']['lastsearch']['params'])) { + foreach($_SESSION['operator']['lastsearch']['params'] as $var => $val) { + $vars->set($var, $val); + } + } + if (isset($_SESSION['operator']['lastsearch']['data'])) { + $data = $_SESSION['operator']['lastsearch']['data']; + } +} + +if (!empty($stats)) { + $numcalls_graph = $minutes_graph = $failed_graph = + Horde::applicationUrl('graphgen.php'); + + $numcalls_graph = Util::addParameter($numcalls_graph, array( + 'graph' => 'numcalls', 'key' => $cachekey)); + $minutes_graph = Util::addParameter($minutes_graph, array( + 'graph' => 'minutes', 'key' => $cachekey)); + $failed_graph = Util::addParameter($failed_graph, array( + 'graph' => 'failed', 'key' => $cachekey)); } -$plot = $plotarea->addNew('bar', $dataset); -$graph->done(); + +$title = _("Call Detail Records Graph"); + +require OPERATOR_TEMPLATES . '/common-header.inc'; +require OPERATOR_TEMPLATES . '/menu.inc'; + +$form->renderActive($renderer, $vars); + +if (!empty($stats)) { + echo '
'; + echo '
'; + echo '
'; + echo '
'; +} + +require $registry->get('templates', 'horde') . '/common-footer.inc'; + +// Don't leave stale stats lying about +unset($_SESSION['operator']['stats']); -- 2.11.0