1: <?php
  2: /**
  3:  * Operator storage implementation for PHP's PEAR database abstraction layer.
  4:  *
  5:  * The table structure can be created by the scripts/sql/operator_foo.sql
  6:  * script.
  7:  *
  8:  * Copyright 2008-2012 Horde LLC (http://www.horde.org/)
  9:  *
 10:  * See the enclosed file COPYING for license information (GPL). If you
 11:  * did not receive this file, see http://www.horde.org/licenses/gpl.
 12:  *
 13:  * @author  Your Name <you@example.com>
 14:  * @package Operator
 15:  */
 16: class Operator_Driver_asterisksql extends Operator_Driver {
 17: 
 18:     /**
 19:      * Hash containing connection parameters.
 20:      *
 21:      * @var array
 22:      */
 23:     protected $_params = array();
 24: 
 25:     /**
 26:      * Handle for the current database connection.
 27:      *
 28:      * @var DB
 29:      */
 30:     protected $_db;
 31: 
 32:     /**
 33:      * Handle for the current database connection, used for writing. Defaults
 34:      * to the same handle as $_db if a separate write database is not required.
 35:      *
 36:      * @var DB
 37:      */
 38:     protected $_write_db;
 39: 
 40:     /**
 41:      * Boolean indicating whether or not we're connected to the SQL server.
 42:      *
 43:      * @var boolean
 44:      */
 45:     protected $_connected = false;
 46: 
 47:     /**
 48:      * Constructs a new SQL storage object.
 49:      *
 50:      * @param array $params  A hash containing connection parameters.
 51:      */
 52:     public function __construct($params = array())
 53:     {
 54:         $this->_params = $params;
 55:     }
 56: 
 57:     /**
 58:      * Get call detail records from the database
 59:      *
 60:      * @return array  [0] contains summary statistics; [1] is an array of the
 61:      *                actual call records.
 62:      * @throws Operator_Exception|Horde_Date_Exception
 63:      */
 64:     protected function _getRecords($start, $end, $accountcode = null, $dcontext = null,
 65:                          $rowstart = 0, $rowlimit = null)
 66:     {
 67: 
 68:         // Use the query to make the MySQL driver look like the CDR-CSV driver
 69:         $sql  = 'SELECT accountcode, src, dst, dcontext, clid, channel, ' .
 70:                 'dstchannel, lastapp, lastdata, calldate AS start, ' .
 71:                 'calldate AS answer, calldate AS end, duration, ' .
 72:                 'billsec, disposition, amaflags, userfield, uniqueid ' .
 73:                 ' FROM ' . $this->_params['table'] . ' WHERE %s';
 74:         $filter = array();
 75:         $values = array();
 76: 
 77:         if (!is_numeric($rowstart)) {
 78:             Horde::logMessage('Invalid start row requested.', 'ERR');
 79:             throw new Operator_Exception(_("Internal error.  Details have been logged for the administrator."));
 80:         }
 81:         if (!is_null($rowlimit) && !is_numeric($rowlimit)) {
 82:             Horde::logMessage('Invalid row limit requested.', 'ERR');
 83:             throw new Operator_Exception(_("Internal error.  Details have been logged for the administrator."));
 84:         }
 85: 
 86:         $filter[] = 'calldate >= ?';
 87:         $values[] = $start->strftime('%Y-%m-%d %T');
 88:         $filter[] = 'calldate < ?';
 89:         $values[] =  $end->strftime('%Y-%m-%d %T');
 90: 
 91:         // Filter by account code
 92:         if ($accountcode !== null) {
 93:             $filter[] = 'accountcode LIKE ?';
 94:             $values[] = $accountcode;
 95:         } else {
 96:             $filter[] = 'accountcode = ""';
 97:         }
 98: 
 99:         // Filter by destination context
100:         if ($dcontext !== null) {
101:             $filter[] = 'dcontext LIKE ?';
102:             $values[] = $dcontext;
103:         } else {
104:             $filter[] = 'dcontext = ""';
105:         }
106: 
107:         /* Make sure we have a valid database connection. */
108:         $this->_connect();
109: 
110:         $filterstring = implode(' AND ', $filter);
111:         $sql = sprintf($sql, $filterstring);
112:         /* Log the query at a DEBUG log level. */
113:         Horde::logMessage(sprintf('Operator_Driver_asterisksql::getData(): %s', $sql), 'DEBUG');
114: 
115:         /* Execute the query. */
116:         if (is_null($rowlimit)) {
117:             $res = $this->_db->query($sql, $values);
118:         } else {
119:             $res = $this->_db->limitQuery($sql, $rowstart, $rowlimit, $values);
120:         }
121:         if (is_a($res, 'PEAR_Error')) {
122:             Horde::logMessage($res, 'ERR');
123:             throw new Operator_Exception(_("Internal error.  Details have been logged for the administrator."));
124:         }
125: 
126:         $data = array();
127:         while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
128:             $data[] = $row;
129:         }
130: 
131:         // Get summary statistics on the requested criteria
132:         $sql = 'SELECT COUNT(*) AS numcalls, SUM(duration)/60 AS minutes, ' .
133:                'SUM(CASE disposition WHEN "FAILED" THEN 1 ELSE 0 END) AS ' .
134:                'failed FROM ' . $this->_params['table'] . ' WHERE %s';
135:         $sql = sprintf($sql, $filterstring);
136:         Horde::logMessage(sprintf('Operator_Driver_asterisksql::getData(): %s', $sql), 'DEBUG');
137: 
138:         /* Execute the query. */
139:         $res = $this->_db->getRow($sql, $values, DB_FETCHMODE_ASSOC);
140:         if (is_a($res, 'PEAR_Error')) {
141:             Horde::logMessage($res, 'ERR');
142:             throw new Operator_Exception(_("Internal error.  Details have been logged for the administrator."));
143:         }
144: 
145:         return array($res, $data);
146:     }
147: 
148:     /**
149:      * Get summary call statistics per-month for a given time range, account and
150:      * destination.
151:      *
152:      * @param Horde_Date startdate  Start of the statistics window
153:      * @param Horde_Date enddate    End of the statistics window
154:      * @param string accountcode    Name of the accont for statistics.  Defaults
155:      *                              to null meaning all accounts.
156:      * @param string dcontext       Destination of calls.  Defaults to null.
157:      *
158:      *
159:      * @return array                Array of call statistics.  The key of each
160:      *                              element is the month name in date('Y-m')
161:      *                              format and the value being an array of
162:      *                              statistics for calls placed that month.
163:      * @throws Operator_Exception|Horde_Date_Exception
164:      */
165:     protected function _getMonthlyCallStats($start, $end, $accountcode = null,
166:                                  $dcontext = null)
167:     {
168:         if (!is_a($start, 'Horde_Date') || !is_a($end, 'Horde_Date')) {
169:             Horde::logMessage('Start ane end date must be Horde_Date objects.', 'ERR');
170:             throw new Operator_Exception(_("Internal error.  Details have been logged for the administrator."));
171:         }
172: 
173:         /* Make sure we have a valid database connection. */
174:         $this->_connect();
175: 
176:         // Construct the queries we will be running below
177:         // Use 1=1 to make constructing the filter string easier
178:         $numcalls_query = 'SELECT MONTH(calldate) AS month, ' .
179:                           'YEAR(calldate) AS year, ' .
180:                           'COUNT(*) AS numcalls FROM ' .
181:                           $this->_params['table'] . ' WHERE %s ' .
182:                           'GROUP BY year, month';
183: 
184:         $minutes_query = 'SELECT MONTH(calldate) AS month, ' .
185:                          'YEAR(calldate) AS year, ' .
186:                          'SUM(duration)/60 AS minutes FROM ' .
187:                          $this->_params['table'] . ' WHERE %s ' .
188:                          'GROUP BY year, month';
189: 
190:         $failed_query = 'SELECT MONTH(calldate) AS month, ' .
191:                         'YEAR(calldate) AS year, ' .
192:                         'COUNT(disposition) AS failed FROM ' .
193:                         $this->_params['table'] . ' ' .
194:                         'WHERE disposition="failed" AND %s ' .
195:                         'GROUP BY year, month';
196: 
197:         // Shared SQL filter
198:         $filter = array();
199:         $values = array();
200: 
201:         // Filter by account code
202:         if ($accountcode !== null) {
203:             $filter[] = 'accountcode LIKE ?';
204:             $values[] = $accountcode;
205:         } else {
206:             $filter[] = 'accountcode = ""';
207:         }
208: 
209:         // Filter by destination context
210:         if ($dcontext !== null) {
211:             $filter[] = 'dcontext LIKE ?';
212:             $values[] = $dcontext;
213:         } else {
214:             $filter[] =  'dcontext = ""';
215:         }
216: 
217:         // Filter by the date range (filled in below)
218:         $filter[] = 'calldate >= ?';
219:         $values[] = $start->strftime('%Y-%m-%d %T');
220:         $filter[] = 'calldate < ?';
221:         $values[] = $end->strftime('%Y-%m-%d %T');
222: 
223:         $filterstring = implode(' AND ', $filter);
224: 
225:         $stats = array();
226: 
227:         /* Log the query at a DEBUG log level. */
228:         $sql = sprintf($numcalls_query, $filterstring);
229:         Horde::logMessage(sprintf('Operator_Driver_asterisksql::getCallStats(): Values: %s', print_r($values, true)), 'DEBUG');
230:         Horde::logMessage(sprintf('Operator_Driver_asterisksql::getCallStats(): %s', $sql), 'DEBUG');
231:         $numcalls_res = $this->_db->getAll($sql, $values, DB_FETCHMODE_ASSOC);
232:         if (is_a($numcalls_res, 'PEAR_Error')) {
233:             Horde::logMessage($numcalls_res, 'ERR');
234:             throw new Operator_Exception(_("Internal error.  Details have been logged for the administrator."));
235:         }
236: 
237:         $sql = sprintf($minutes_query, $filterstring);
238:         Horde::logMessage(sprintf('Operator_Driver_asterisksql::getCallStats(): %s', $sql), 'DEBUG');
239:         $minutes_res = $this->_db->getAll($sql, $values, DB_FETCHMODE_ASSOC);
240:         if (is_a($minutes_res, 'PEAR_Error')) {
241:             Horde::logMessage($minutes_res, 'ERR');
242:             throw new Operator_Exception(_("Internal error.  Details have been logged for the administrator."));
243:         }
244: 
245:         $sql = sprintf($failed_query, $filterstring);
246:         Horde::logMessage(sprintf('Operator_Driver_asterisksql::getCallStats(): %s', $sql), 'DEBUG');
247:         $failed_res = $this->_db->getAll($sql, $values, DB_FETCHMODE_ASSOC);
248:         if (is_a($failed_res, 'PEAR_Error')) {
249:             Horde::logMessage($failed_res, 'ERR');
250:             throw new Operator_Exception(_("Internal error.  Details have been logged for the administrator."));
251:         }
252: 
253:         // Normalize the results from the database.  This is done because
254:         // the database will not return values if there are no data that match
255:         // the query.  For example if there were no calls in the month of June
256:         // the results will not have any rows with data for June.  Instead of
257:         // searching through the results for each month we stuff the values we
258:         // have into a temporary array and then create the return value below
259:         // using 0 values where necessary.
260:         $numcalls = array();
261:         foreach ($numcalls_res as $row) {
262:             $numcalls[$row['year']][$row['month']] = $row['numcalls'];
263:         }
264:         $minutes = array();
265:         foreach ($minutes_res as $row) {
266:             $minutes[$row['year']][$row['month']] = $row['minutes'];
267:         }
268:         $failed = array();
269:         foreach ($failed_res as $row) {
270:             $failed[$row['year']][$row['month']] = $row['failed'];
271:         }
272: 
273:         $s_numcalls = array();
274:         $s_minutes = array();
275:         $s_failed = array();
276:         while($start->compareDate($end) <= 0) {
277:             $index = $start->strftime('%Y-%m');
278:             $year = $start->year;
279:             $month = $start->month;
280: 
281:             if (empty($numcalls[$year]) || empty($numcalls[$year][$month])) {
282:                 $s_numcalls[$index] = 0;
283:             } else {
284:                 $s_numcalls[$index] = $numcalls[$year][$month];
285:             }
286: 
287:             if (empty($minutes[$year]) || empty($minutes[$year][$month])) {
288:                 $s_minutes[$index] = 0;
289:             } else {
290:                 $s_minutes[$index] = $minutes[$year][$month];
291:             }
292: 
293:             if (empty($failed[$year]) || empty($failed[$year][$month])) {
294:                 $s_failed[$index] = 0;
295:             } else {
296:                 $s_failed[$index] = $failed[$year][$month];
297:             }
298: 
299:             // Find the first day of the next month
300:             $start->month++;
301:         }
302: 
303:         $info = Operator::getGraphInfo('numcalls');
304:         $stats['numcalls'] = array($info['title'] => $s_numcalls);
305:         $info = Operator::getGraphInfo('minutes');
306:         $stats['minutes'] = array($info['title'] => $s_minutes);
307: //        $info = Operator::getGraphInfo('failed');
308: //        $stats['failed'] = array($info['title'] => $s_failed);
309: 
310:         return $stats;
311:     }
312: 
313:     public function getAccountCodes()
314:     {
315:         /* Make sure we have a valid database connection. */
316:         $this->_connect();
317: 
318:         $sql = 'SELECT DISTINCT(accountcode) FROM ' . $this->_params['table'] .
319:                ' ORDER BY accountcode';
320:         Horde::logMessage(sprintf('Operator_Driver_asterisksql::getAccountCodes(): %s', $sql), 'DEBUG');
321:         $res = $this->_db->getCol($sql, 'accountcode');
322:         if (is_a($res, 'PEAR_Error')) {
323:             Horde::logMessage($res, 'ERR');
324:             throw new Operator_Exception(_("Internal error.  Details have been logged for the administrator."));
325:         }
326: 
327:         return $res;
328:     }
329: 
330:     /**
331:      * Attempts to open a connection to the SQL server.
332:      *
333:      * @return boolean  True on success.
334:      * @throws Horde_Exception
335:      */
336:     protected function _connect()
337:     {
338:         if ($this->_connected) {
339:             return true;
340:         }
341: 
342:         $this->_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('read', 'operator', 'storage');
343:         $this->_write_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('rw', 'operator', 'storage');
344: 
345:         return true;
346:     }
347: 
348:     /**
349:      * Disconnects from the SQL server and cleans up the connection.
350:      *
351:      * @return boolean  True on success, false on failure.
352:      */
353:     protected function _disconnect()
354:     {
355:         if ($this->_connected) {
356:             $this->_connected = false;
357:             $this->_db->disconnect();
358:             $this->_write_db->disconnect();
359:         }
360: 
361:         return true;
362:     }
363: 
364: }
365: