1: <?php
  2: /**
  3:  * Provides the SQL backend driver for the Shout application.
  4:  *
  5:  * Copyright 2009-2010 Alkaloid Networks LLC (http://projects.alkaloid.net)
  6:  *
  7:  * See the enclosed file COPYING for license information (BSD). If you
  8:  * did not receive this file, see
  9:  * http://www.opensource.org/licenses/bsd-license.php.
 10:  *
 11:  * @author  Ben Klang <ben@alkaloid.net>
 12:  * @package Shout
 13:  */
 14: class Shout_Driver_Sql extends Shout_Driver
 15: {
 16:     /**
 17:      * Handle for the current database connection.
 18:      * @var object $_db
 19:      */
 20:     protected $_db = null;
 21: 
 22:     /**
 23:      * Handle for the current writable database connection.
 24:      * @var object $_db
 25:      */
 26:     protected $_write_db = null;
 27: 
 28:     /**
 29:      * Boolean indicating whether or not we're connected to the LDAP
 30:      * server.
 31:      * @var boolean $_connected
 32:      */
 33:     protected $_connected = false;
 34: 
 35: 
 36:     /**
 37:     * Constructs a new Shout LDAP driver object.
 38:     *
 39:     * @param array  $params    A hash containing connection parameters.
 40:     */
 41:     public function __construct($params = array())
 42:     {
 43:         parent::__construct($params);
 44:         $this->_connect();
 45:     }
 46: 
 47:     public function getAccounts()
 48:     {
 49:         $this->_connect();
 50: 
 51:         $sql = 'SELECT name, code, adminpin FROM accounts';
 52:         $vars = array();
 53: 
 54:         $msg = 'SQL query in Shout_Driver_Sql#getAccounts(): ' . $sql;
 55:         Horde::logMessage($msg, 'DEBUG');
 56:         $result = $this->_db->query($sql, $vars);
 57:         if ($result instanceof PEAR_Error) {
 58:             throw new Shout_Exception($result);
 59:         }
 60: 
 61:         $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
 62:         if ($row instanceof PEAR_Error) {
 63:             throw new Shout_Exception($row);
 64:         }
 65: 
 66:         $accounts = array();
 67:         while ($row && !($row instanceof PEAR_Error)) {
 68:             $accounts[$row['code']] = $row;
 69:             $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
 70:         }
 71: 
 72:          $result->free();
 73:          return $accounts;
 74:     }
 75: 
 76:     public function saveAccount($code, $name, $adminpin)
 77:     {
 78:         $this->_connect();
 79: 
 80:         // FIXME: Enable editing of account details
 81: //        if (isset($details['oldname'])) {
 82: //            if (!isset($menus[$details['oldname']])) {
 83: //                throw new Shout_Exception(_("Old account not found.  Edit aborted."));
 84: //            } else {
 85: //                throw new Shout_Exception(_("Unsupported operation."));
 86: //                $sql = 'UPDATE accounts SET code = ?, name = ?, adminpin = ? ' .
 87: //                       'WHERE code = ?';
 88: //            }
 89: //        } else {
 90:         $sql = 'INSERT INTO accounts (code, name, adminpin) VALUES (?,?,?)';
 91:         $vars = array($code, $name, $adminpin);
 92:         $msg = 'SQL query in Shout_Driver_Sql#saveAccount(): ' . $sql;
 93:         Horde::logMessage($msg, 'DEBUG');
 94:         $result = $this->_db->query($sql, $vars);
 95:         if ($result instanceof PEAR_Error) {
 96:             throw new Shout_Exception($result);
 97:         }
 98: 
 99:     }
100: 
101:     public function getMenus($account)
102:     {
103:         $this->_connect();
104: 
105:         $sql = 'SELECT accounts.code AS account, menus.name AS name, ' .
106:                'menus.description AS description, recording_id ' .
107:                'FROM menus INNER JOIN accounts ON menus.account_id = accounts.id ' .
108:                'WHERE accounts.code = ?';
109:         $values = array($account);
110: 
111:         $msg = 'SQL query in Shout_Driver_Sql#getMenus(): ' . $sql;
112:         Horde::logMessage($msg, 'DEBUG');
113:         $result = $this->_db->query($sql, $values);
114:         if ($result instanceof PEAR_Error) {
115:             throw new Shout_Exception($result);
116:         }
117: 
118:         $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
119:         if ($row instanceof PEAR_Error) {
120:             throw new Shout_Exception($row);
121:         }
122: 
123:         $menus = array();
124:         while ($row && !($row instanceof PEAR_Error)) {
125:             $menu = $row['name'];
126:             $menus[$menu] = array(
127:                 'name' => $menu,
128:                 'description' => $row['description'],
129:                 'recording_id' => $row['recording_id']
130:             );
131:             $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
132:         }
133:         $result->free();
134:         return $menus;
135:     }
136: 
137:     public function saveMenuInfo($account, $details)
138:     {
139:         $menus = $this->getMenus($account);
140:         if (isset($details['oldname'])) {
141:             if (!isset($menus[$details['oldname']])) {
142:                 throw new Shout_Exception(_("Old menu not found.  Edit aborted."));
143:             } else {
144:                 $sql = 'UPDATE menus SET name = ?, description = ?, ' .
145:                        'recording_id = ? WHERE account_id = ' .
146:                        '(SELECT id FROM accounts WHERE code = ?) AND name = ?';
147:                 $values = array($details['name'], $details['description'],
148:                                 $details['recording_id'], $account,
149:                                 $details['oldname']);
150:             }
151:         } else {
152:             $sql = "INSERT INTO menus (account_id, name, description, recording_id) " .
153:                    "VALUES ((SELECT id FROM accounts WHERE code = ?), ?, ?, ?)";
154:             $values = array($account, $details['name'],
155:                             $details['description'], $details['recording_id']);
156:         }
157: 
158:         $msg = 'SQL query in Shout_Driver_Sql#saveMenu(): ' . $sql;
159:         Horde::logMessage($msg, 'DEBUG');
160:         $result = $this->_write_db->query($sql, $values);
161:         if ($result instanceof PEAR_Error) {
162:             throw new Shout_Exception($result);
163:         }
164: 
165:         return true;
166:     }
167: 
168:     public function deleteMenu($account, $menu)
169:     {
170:         // Disassociate any numbers that were previously associated
171:         $sql = 'UPDATE numbers SET menu_id = 1 WHERE ' .
172:                '(SELECT id FROM menus WHERE name = ? AND account_id = ' .
173:                '(SELECT id FROM accounts WHERE code = ?))';
174:         $values = array($menu, $account);
175:         $msg = 'SQL query in Shout_Driver_Sql#deleteMenu(): ' . $sql;
176:         Horde::logMessage($msg, 'DEBUG');
177:         $result = $this->_write_db->query($sql, $values);
178:         if ($result instanceof PEAR_Error) {
179:             throw new Shout_Exception($result);
180:         }
181: 
182:         // Remove any associated menu entries
183:         $sql = 'DELETE FROM menu_entries WHERE menu_id = ' .
184:                '(SELECT id FROM menus WHERE name = ? AND account_id = ' .
185:                '(SELECT id FROM accounts WHERE code = ?))';
186:         $values = array($menu, $account);
187:         $msg = 'SQL query in Shout_Driver_Sql#deleteMenu(): ' . $sql;
188:         Horde::logMessage($msg, 'DEBUG');
189:         $result = $this->_write_db->query($sql, $values);
190:         if ($result instanceof PEAR_Error) {
191:             throw new Shout_Exception($result);
192:         }
193: 
194:         // Finally, remove the menu itself.
195:         $sql = 'DELETE FROM menus WHERE name = ? AND account_id = ' .
196:                '(SELECT id FROM accounts WHERE code = ?)';
197:         $values = array($menu, $account);
198:         $msg = 'SQL query in Shout_Driver_Sql#deleteMenu(): ' . $sql;
199:         Horde::logMessage($msg, 'DEBUG');
200:         $result = $this->_write_db->query($sql, $values);
201:         if ($result instanceof PEAR_Error) {
202:             throw new Shout_Exception($result);
203:         }
204:     }
205: 
206:     public function getMenuActions($account, $menu)
207:     {
208:         static $menuActions;
209:         if (!empty($menuActions[$menu])) {
210:             return $menuActions[$menu];
211:         }
212: 
213:         $sql = "SELECT accounts.code AS account, menus.name AS description, " .
214:                "actions.name AS action, menu_entries.digit AS digit, " .
215:                "menu_entries.args AS args FROM menu_entries " .
216:                "INNER JOIN menus ON menu_entries.menu_id = menus.id " .
217:                "INNER JOIN actions ON menu_entries.action_id = actions.id " .
218:                "INNER JOIN accounts ON menus.account_id = accounts.id " .
219:                "WHERE accounts.code = ? AND menus.name = ?";
220:         $values = array($account, $menu);
221: 
222:         $msg = 'SQL query in Shout_Driver_Sql#getMenuActions(): ' . $sql;
223:         Horde::logMessage($msg, 'DEBUG');
224:         $result = $this->_db->query($sql, $values);
225:         if ($result instanceof PEAR_Error) {
226:             throw new Shout_Exception($result);
227:         }
228: 
229:         $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
230:         if ($row instanceof PEAR_Error) {
231:             throw new Shout_Exception($row);
232:         }
233: 
234:         $menuActions[$menu] = array();
235:         while ($row && !($row instanceof PEAR_Error)) {
236:             $menuActions[$menu][$row['digit']] = array(
237:                 'digit' => $row['digit'],
238:                 'action' => $row['action'],
239:                 'args' => Horde_Yaml::load($row['args'])
240:             );
241:             $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
242:         }
243:         $result->free();
244: 
245:         return $menuActions[$menu];
246:     }
247: 
248:     public function deleteMenuAction($account, $menu, $digit)
249:     {
250:         // Remove any existing action
251:         $sql = 'DELETE FROM menu_entries WHERE menu_id = ' .
252:                '(SELECT id FROM menus WHERE account_id = ' .
253:                '(SELECT id FROM accounts WHERE code = ?) AND name = ?) ' .
254:                'AND digit = ?';
255:         $values = array($account, $menu, $digit);
256:         $msg = 'SQL query in Shout_Driver_Sql#saveMenuAction(): ' . $sql;
257:         Horde::logMessage($msg, 'DEBUG');
258:         $result = $this->_write_db->query($sql, $values);
259:         if ($result instanceof PEAR_Error) {
260:             throw new Shout_Exception($result);
261:         }
262:     }
263: 
264:     public function saveMenuAction($account, $menu, $digit, $action, $args)
265:     {
266:         $this->deleteMenuAction($account, $menu, $digit);
267: 
268:         $sql = 'INSERT INTO menu_entries (menu_id, digit, action_id, args) ' .
269:                'VALUES((SELECT id FROM menus WHERE account_id = ' .
270:                '(SELECT id FROM accounts WHERE code = ?) AND name = ?), ?, ' .
271:                '(SELECT id FROM actions WHERE name = ?), ?)';
272:         $yamlargs = Horde_Yaml::dump($args);
273:         $values = array($account, $menu, $digit, $action, $yamlargs);
274:         $msg = 'SQL query in Shout_Driver_Sql#saveMenuAction(): ' . $sql;
275:         Horde::logMessage($msg, 'DEBUG');
276:         $result = $this->_write_db->query($sql, $values);
277:         if ($result instanceof PEAR_Error) {
278:             throw new Shout_Exception($result);
279:         }
280: 
281:         return true;
282:     }
283: 
284:     /**
285:      * Get a list of devices for a given account
286:      *
287:      * @param string $account    Account in which to search for devicess
288:      *
289:      * @return array  Array of devices within this account with their information
290:      *
291:      * @access private
292:      */
293:     public function getDevices($account)
294:     {
295:         $sql = 'SELECT id, name, alias, callerid, context, mailbox, host, ' .
296:                'permit, nat, secret, disallow, allow ' .
297:                'FROM %s WHERE accountcode = ?';
298:         $sql = sprintf($sql, $this->_params['table']);
299:         $args = array($account);
300:         $msg = 'SQL query in Shout_Driver_Sql#getDevices(): ' . $sql;
301:         Horde::logMessage($msg, 'DEBUG');
302:         $sth = $this->_db->prepare($sql);
303:         $result = $this->_db->execute($sth, $args);
304:         if ($result instanceof PEAR_Error) {
305:             throw new Shout_Exception($result);
306:         }
307: 
308:         $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
309:         if ($row instanceof PEAR_Error) {
310:             throw new Shout_Exception($row);
311:         }
312: 
313:         $devices = array();
314:         while ($row && !($row instanceof PEAR_Error)) {
315:             // Asterisk uses the "name" field to indicate the registration
316:             // identifier.  We use the field "alias" to put a friendly name on
317:             // the device.  Thus devid => name and name => alias
318:             $devid = $row['name'];
319:             $row['devid'] = $devid;
320:             $row['name'] = $row['alias'];
321:             unset($row['alias']);
322: 
323:             // Trim off the context from the mailbox number
324:             list($row['mailbox']) = explode('@', $row['mailbox']);
325: 
326:             // The UI calls the 'secret' a 'password'
327:             $row['password'] = $row['secret'];
328:             unset($row['secret']);
329: 
330:             // Hide the DB internal ID from the front-end
331:             unset($row['id']);
332: 
333:             $devices[$devid] = $row;
334: 
335:             /* Advance to the new row in the result set. */
336:             $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
337:         }
338: 
339:         $result->free();
340:         return $devices;
341:     }
342: 
343:     /**
344:      * Save a device (add or edit) to the backend.
345:      *
346:      * @param string $account  The account in which this device is valid
347:      * @param string $devid    Device ID to save
348:      * @param array $details      Array of device details
349:      */
350:     public function saveDevice($account, $devid, &$details)
351:     {
352:         // Check permissions and possibly update the authentication tokens
353:         parent::saveDevice($account, $devid, $details);
354: 
355:         // See getDevices() for an explanation of these conversions
356:         $details['alias'] = $details['name'];
357:         $details['name'] = $details['devid'];
358:         unset($details['devid']);
359:         $details['mailbox'] .= '@' . $account;
360: 
361:         // Prepare the SQL query and arguments
362:         $args = array(
363:             $details['name'],
364:             $account,
365:             $details['callerid'],
366:             $details['mailbox'],
367:             $details['password'],
368:             $account,
369:             $details['alias'],
370:         );
371: 
372:         if (!empty($devid)) {
373:             // This is an edit
374:             $details['name'] = $details['devid'];
375:             $sql = 'UPDATE %s SET name = ?, accountcode = ?, callerid = ?, ' .
376:                    'mailbox = ?, secret = ?, context = ?, alias = ?, ' .
377:                    'canreinvite = "no", nat = "yes", type = "peer", ' .
378:                    'host = "dynamic" WHERE name = ?';
379:             $args[] = $devid;
380:         } else {
381:             // This is an add.  Generate a new unique ID and secret
382:             $sql = 'INSERT INTO %s (name, accountcode, callerid, mailbox, ' .
383:                    'secret, context, alias, canreinvite, nat, type, host) ' .
384:                    'VALUES (?, ?, ?, ?, ?, ?, ?, "no", "yes", "peer", ' .
385:                    '"dynamic")';
386: 
387:         }
388:         $sql = sprintf($sql, $this->_params['table']);
389: 
390:         $msg = 'SQL query in Shout_Driver_Sql#saveDevice(): ' . $sql;
391:         Horde::logMessage($msg, 'DEBUG');
392:         $sth = $this->_write_db->prepare($sql);
393:         $result = $this->_write_db->execute($sth, $args);
394:         if ($result instanceof PEAR_Error) {
395:             $msg = $result->getMessage() . ': ' . $result->getDebugInfo();
396:             Horde::logMessage($msg, 'ERR');
397:             throw new Shout_Exception(_("Internal database error.  Details have been logged for the administrator."));
398:         }
399: 
400:         return true;
401:     }
402: 
403:     public function deleteDevice($account, $devid)
404:     {
405:         parent::deleteDevice($account, $devid);
406: 
407:         $sql = 'DELETE FROM %s WHERE devid = ?';
408:         $sql = sprintf($sql, $this->_params['table']);
409:         $values = array($devid);
410: 
411:         $msg = 'SQL query in Shout_Driver_Sql#deleteDevice(): ' . $sql;
412:         Horde::logMessage($msg, 'DEBUG');
413:         $res = $this->_write_db->query($sql);
414: 
415:         if ($res instanceof PEAR_Error) {
416:             throw new Shout_Exception($res->getMessage(), $res->getCode());
417:         }
418: 
419:         return true;
420:     }
421: 
422:     /**
423:      * Get a list of users valid for the accounts
424:      *
425:      * @param string $account Account on which to search
426:      *
427:      * @return array User information indexed by voice mailbox number
428:      */
429:     public function getExtensions($account)
430:     {
431:         throw new Shout_Exception("Not implemented yet.");
432:     }
433: 
434:     /**
435:      * Save a user to the LDAP tree
436:      *
437:      * @param string $account Account to which the user should be added
438:      *
439:      * @param string $extension Extension to be saved
440:      *
441:      * @param array $userdetails Phone numbers, PIN, options, etc to be saved
442:      *
443:      * @return TRUE on success, PEAR::Error object on error
444:      */
445:     public function saveExtension($account, $extension, $userdetails)
446:     {
447:         parent::saveExtension($account, $extension, $details);
448:         throw new Shout_Exception("Not implemented.");
449:     }
450: 
451:     /**
452:      * Deletes a user from the LDAP tree
453:      *
454:      * @param string $account Account to delete the user from
455:      * @param string $extension Extension of the user to be deleted
456:      *
457:      * @return boolean True on success, PEAR::Error object on error
458:      */
459:     public function deleteExtension($account, $extension)
460:     {
461:         throw new Shout_Exception("Not implemented.");
462:     }
463: 
464:     public function getConferences($account)
465:     {
466:         $sql = 'SELECT id, room_number AS roomno, name, pin, options ' .
467:                'FROM conferences ' .
468:                'WHERE account_id = (SELECT id FROM accounts WHERE code = ?);';
469:         $args = array($account);
470:         $msg = 'SQL query in Shout_Driver_Sql#getConferences(): ' . $sql;
471:         Horde::logMessage($msg, 'DEBUG');
472:         $result = $this->_db->query($sql, $args);
473:         if ($result instanceof PEAR_Error) {
474:             throw new Shout_Exception($result);
475:         }
476: 
477:         $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
478:         if ($row instanceof PEAR_Error) {
479:             throw new Shout_Exception($row);
480:         }
481: 
482:         $conferences = array();
483:         while ($row && !($row instanceof PEAR_Error)) {
484:             $roomno = $row['roomno'];
485:             $conferences[$roomno] = $row;
486: 
487:             /* Advance to the new row in the result set. */
488:             $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
489:         }
490: 
491:         $result->free();
492:         return $conferences;
493:     }
494: 
495:     public function saveConference($account, $roomno, $details)
496:     {
497:         if (isset($details['oldroomno'])) {
498:             // This is an edit
499:             $sql = 'UPDATE conferences ' .
500:                    'SET room_number = ?, name = ?, pin = ? ' .
501:                    'WHERE room_number = ? AND account_id = ' .
502:                    '(SELECT id FROM accounts WHERE code = ?)';
503:             $args = array($roomno, $details['name'], $details['pin'],
504:                           $details['oldroomno'], $account);
505:         } else {
506:             $sql = 'INSERT INTO conferences ' .
507:                    '(room_number, name, pin, account_id) ' .
508:                    'VALUES (?, ?, ?, (SELECT id FROM accounts WHERE code = ?))';
509:             $args = array($roomno, $details['name'], $details['pin'], $account);
510:         }
511: 
512:         $msg = 'SQL query in Shout_Driver_Sql#saveConference(): ' . $sql;
513:         Horde::logMessage($msg, 'DEBUG');
514:         $result = $this->_write_db->query($sql, $args);
515:         if ($result instanceof PEAR_Error) {
516:             throw new Shout_Exception($result);
517:         }
518: 
519:         return true;
520:     }
521: 
522:     public function getRecordings($account)
523:     {
524:         $sql = 'SELECT id, filename FROM recordings ' .
525:                'WHERE account_id = (SELECT id FROM accounts WHERE code = ?);';
526:         $args = array($account);
527:         $msg = 'SQL query in Shout_Driver_Sql#getRecordings(): ' . $sql;
528:         Horde::logMessage($msg, 'DEBUG');
529:         $result = $this->_db->query($sql, $args);
530:         if ($result instanceof PEAR_Error) {
531:             throw new Shout_Exception($result);
532:         }
533: 
534:         $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
535:         if ($row instanceof PEAR_Error) {
536:             throw new Shout_Exception($row);
537:         }
538: 
539:         $recordings = array();
540:         while ($row && !($row instanceof PEAR_Error)) {
541:             $id = $row['id'];
542:             $recordings[$id] = $row;
543: 
544:             /* Advance to the new row in the result set. */
545:             $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
546:         }
547: 
548:         $result->free();
549:         return $recordings;
550:     }
551: 
552:     public function getRecordingByName($account, $filename)
553:     {
554:         $sql = 'SELECT id, filename FROM recordings ' .
555:                'WHERE account_id = (SELECT id FROM accounts WHERE code = ?) ' .
556:                'AND filename = ?;';
557:         $args = array($account, $filename);
558:         $msg = 'SQL query in Shout_Driver_Sql#getRecordingByName(): ' . $sql;
559:         Horde::logMessage($msg, 'DEBUG');
560:         $result = $this->_db->query($sql, $args);
561:         if ($result instanceof PEAR_Error) {
562:             throw new Shout_Exception($result);
563:         }
564: 
565:         $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
566:         if ($row instanceof PEAR_Error) {
567:             throw new Shout_Exception($row);
568:         }
569:         if ($row === null) {
570:             throw new Shout_Exception('No such recording found for this account.');
571:         }
572:         $result->free();
573:         return $row;
574:     }
575: 
576:     public function addRecording($account, $name)
577:     {
578:         $sql = 'INSERT INTO recordings (filename, account_id) ' .
579:                'VALUES (?,(SELECT id FROM accounts WHERE code = ?))';
580:         $args = array($name, $account);
581: 
582:         $msg = 'SQL query in Shout_Driver_Sql#addRecording(): ' . $sql;
583:         Horde::logMessage($msg, 'DEBUG');
584:         $result = $this->_write_db->query($sql, $args);
585:         if ($result instanceof PEAR_Error) {
586:             throw new Shout_Exception($result);
587:         }
588: 
589:         return true;
590:     }
591: 
592:     public function deleteRecording($account, $name)
593:     {
594:         $sql = 'DELETE FROM recordings WHERE filename = ? AND account_id = ' .
595:                '(SELECT id FROM accounts WHERE code = ?)';
596:         $vars = array($name, $account);
597: 
598:         $msg = 'SQL query in Shout_Driver_Sql#deleteRecording(): ' . $sql;
599:         Horde::logMessage($msg, 'DEBUG');
600:         $result = $this->_write_db->query($sql, $args);
601:         if ($result instanceof PEAR_Error) {
602:             throw new Shout_Exception($result);
603:         }
604: 
605:         return true;
606:     }
607: 
608:     public function getNumbers($account = null)
609:     {
610:         if (!empty($account)) {
611:             $sql = 'SELECT numbers.id AS id, numbers.did AS number, ' .
612:                    'accounts.code AS accountcode, menus.name AS menuName ' .
613:                    'FROM numbers ' .
614:                    'INNER JOIN accounts ON numbers.account_id = accounts.id ' .
615:                    'INNER JOIN menus ON numbers.menu_id = menus.id ' .
616:                    'WHERE accounts.code = ?';
617:             $values = array($account);
618: 
619:         } else {
620:             $sql = 'SELECT numbers.id AS id, numbers.did AS number, ' .
621:                    'accounts.code AS accountcode, menus.name AS menuName ' .
622:                    'FROM numbers ' .
623:                    'INNER JOIN accounts ON numbers.account_id = accounts.id ' .
624:                    'INNER JOIN menus ON numbers.menu_id = menus.id';
625:             $values = array();
626:         }
627:         
628:         $msg = 'SQL query in Shout_Driver_Sql#getNumbers(): ' . $sql;
629:         Horde::logMessage($msg, 'DEBUG');
630:         $result = $this->_db->query($sql, $values);
631:         if ($result instanceof PEAR_Error) {
632:             throw new Shout_Exception($result);
633:         }
634: 
635:         $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
636:         if ($row instanceof PEAR_Error) {
637:             throw new Shout_Exception($row);
638:         }
639: 
640:         $numbers = array();
641:         while ($row && !($row instanceof PEAR_Error)) {
642:             $id = $row['number'];
643:             $numbers[$id] = $row;
644: 
645:             /* Advance to the new row in the result set. */
646:             $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
647:         }
648: 
649:         $result->free();
650:         return $numbers;
651:     }
652: 
653:     public function deleteNumber($number)
654:     {
655:         // Remove any existing action
656:         $sql = 'DELETE FROM numbers WHERE did = ?';
657:         $values = array($number);
658:         $msg = 'SQL query in Shout_Driver_Sql#deleteNumber(): ' . $sql;
659:         Horde::logMessage($msg, 'DEBUG');
660:         $result = $this->_write_db->query($sql, $values);
661:         if ($result instanceof PEAR_Error) {
662:             throw new Shout_Exception($result);
663:         }
664:         return true;
665:     }
666: 
667:     public function saveNumber($number, $account, $menu)
668:     {
669:         $numbers = $this->getNumbers();
670:         if (isset($numbers[$number])) {
671:             // This is an edit
672:             $sql = 'UPDATE numbers SET ' .
673:                    'account_id = (SELECT id FROM accounts WHERE code = ?), ';
674:             $values = array($account);
675:             if ($menu == 'INACTIVE') {
676:                 // Special handling for the 'NONE' menu
677:                 $sql .= 'menu_id = 1 ';
678:             } else {
679:                 $sql .= 'menu_id = (SELECT id FROM menus WHERE name = ? AND ' .
680:                         'account_id = (SELECT id FROM accounts WHERE code = ?)) ';
681:                 $values[] = $menu;
682:                 $values[] = $account;
683:             }
684:             $sql .= 'WHERE did = ?';
685:             $values[] = $number;
686:         } else {
687:             // This is an add
688:             $sql = 'INSERT INTO numbers (account_id, menu_id, did) VALUES (' .
689:                    '(SELECT id FROM accounts WHERE code = ?), ';
690:             $values = array($account);
691:             if ($menu == 'INACTIVE') {
692:                 // Special handling for the 'NONE' menu
693:                 $sql .= 'menu_id = 1, ';
694:             } else {
695:                 $sql .= 'menu_id = (SELECT id FROM menus WHERE name = ? AND ' .
696:                         'account_id = (SELECT id FROM accounts WHERE code = ?)), ';
697:                 $values[] = $menu;
698:                 $values[] = $account;
699:             }
700:             $sql .= '?)';
701:             $values[] = $number;
702:         }
703: 
704:         $msg = 'SQL query in Shout_Driver_Sql#saveNumber(): ' . $sql;
705:         Horde::logMessage($msg, 'DEBUG');
706:         $result = $this->_write_db->query($sql, $values);
707:         if ($result instanceof PEAR_Error) {
708:             throw new Shout_Exception($result);
709:         }
710: 
711:         return true;
712:     }
713: 
714:     /**
715:      * Attempts to open a persistent connection to the SQL server.
716:      *
717:      * @throws Shout_Exception
718:      */
719:     protected function _connect()
720:     {
721:         if (!$this->_connected) {
722:             try {
723:                 $this->_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('read', 'shout', $this->_params['class']);
724:                 $this->_write_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('rw', 'shout', $this->_params['class']);
725:             } catch (Horde_Exception $e) {
726:                 throw new Shout_Exception($e);
727:             }
728: 
729:             $this->_connected = true;
730:         }
731:     }
732: 
733:     /**
734:      * Disconnects from the SQL server and cleans up the connection.
735:      */
736:     protected function _disconnect()
737:     {
738:         if ($this->_connected) {
739:             $this->_connected = false;
740:             $this->_db->disconnect();
741:             $this->_write_db->disconnect();
742:         }
743:     }
744: 
745: }
746: