1: <?php
  2: /**
  3:  * IMP_Sentmail implementation for SQL databases.
  4:  *
  5:  * The table structure is as follows:
  6:  * <pre>
  7:  * CREATE TABLE imp_sentmail (
  8:  *     sentmail_id        BIGINT NOT NULL AUTO_INCREMENT,
  9:  *     sentmail_who       VARCHAR(255) NOT NULL,
 10:  *     sentmail_ts        BIGINT NOT NULL,
 11:  *     sentmail_messageid VARCHAR(255) NOT NULL,
 12:  *     sentmail_action    VARCHAR(32) NOT NULL,
 13:  *     sentmail_recipient VARCHAR(255) NOT NULL,
 14:  *     sentmail_success   INT NOT NULL,
 15:  *
 16:  *     PRIMARY KEY (sentmail_id)
 17:  * );
 18:  *
 19:  * CREATE INDEX sentmail_ts_idx ON imp_sentmail (sentmail_ts);
 20:  * CREATE INDEX sentmail_who_idx ON imp_sentmail (sentmail_who);
 21:  * CREATE INDEX sentmail_success_idx ON imp_sentmail (sentmail_success);
 22:  * </pre>
 23:  *
 24:  * Copyright 2010-2012 Horde LLC (http://www.horde.org/)
 25:  *
 26:  * See the enclosed file COPYING for license information (GPL). If you
 27:  * did not receive this file, see http://www.horde.org/licenses/gpl.
 28:  *
 29:  * @author   Jan Schneider <jan@horde.org>
 30:  * @author   Michael Slusarz <slusarz@horde.org>
 31:  * @category Horde
 32:  * @license  http://www.horde.org/licenses/gpl GPL
 33:  * @package  IMP
 34:  */
 35: class IMP_Sentmail_Sql extends IMP_Sentmail_Base
 36: {
 37:     /**
 38:      * Handle for the current database connection.
 39:      *
 40:      * @var Horde_Db_Adapter
 41:      */
 42:     protected $_db;
 43: 
 44:     /**
 45:      * @param array $params  Parameters:
 46:      * <pre>
 47:      * db - (Horde_Db_Adapter) [REQUIRED] The DB instance.
 48:      * table - (string) The name of the sentmail table.
 49:      *         DEFAULT: 'imp_sentmail'
 50:      * </pre>
 51:      */
 52:     public function __construct(array $params = array())
 53:     {
 54:         if (!isset($params['db'])) {
 55:             throw new IMP_Exception('Missing db parameter.');
 56:         }
 57:         $this->_db = $params['db'];
 58:         unset($params['db']);
 59: 
 60:         $params = array_merge(array(
 61:             'table' => 'imp_sentmail'
 62:         ), $params);
 63: 
 64:         parent::__construct($params);
 65:     }
 66: 
 67:     /**
 68:      */
 69:     protected function _log($action, $message_id, $recipient, $success)
 70:     {
 71:         /* Build the SQL query. */
 72:         $query = sprintf('INSERT INTO %s (sentmail_who, sentmail_ts, sentmail_messageid, sentmail_action, sentmail_recipient, sentmail_success) VALUES (?, ?, ?, ?, ?, ?)', $this->_params['table']);
 73:         $values = array(
 74:             $GLOBALS['registry']->getAuth(),
 75:             time(),
 76:             $message_id,
 77:             $action,
 78:             $recipient,
 79:             intval($success)
 80:         );
 81: 
 82:         /* Execute the query. */
 83:         try {
 84:             $this->_db->insert($query, $values);
 85:         } catch (Horde_Db_Exception $e) {}
 86:     }
 87: 
 88:     /**
 89:      */
 90:     public function favouriteRecipients($limit, $filter = null)
 91:     {
 92:         /* Build the SQL query. */
 93:         $where = '';
 94:         if (!empty($filter)) {
 95:             $filter = array_map(array($this->_db, 'quote'), $filter);
 96:             $where = sprintf(' AND sentmail_action in (%s)',
 97:                              implode(', ', $filter));
 98:         }
 99: 
100:         $query = sprintf('SELECT sentmail_recipient, count(*) AS sentmail_count FROM %s WHERE sentmail_who = %s AND sentmail_success = 1%s GROUP BY sentmail_recipient ORDER BY sentmail_count DESC',
101:                          $this->_params['table'],
102:                          $this->_db->quote($GLOBALS['registry']->getAuth()),
103:                          $where);
104: 
105:         /* Execute the query. */
106:         try {
107:             $query = $this->_db->addLimitOffset($query, array('limit' => $limit));
108:             return $this->_db->selectValues($query);
109:         } catch (Horde_Db_Exception $e) {
110:             throw new IMP_Exception($e);
111:         }
112:     }
113: 
114:     /**
115:      */
116:     public function numberOfRecipients($hours, $user = false)
117:     {
118:         /* Build the SQL query. */
119:         $query = sprintf('SELECT COUNT(*) FROM %s WHERE sentmail_ts > ?',
120:                          $this->_params['table']);
121:         if ($user) {
122:             $query .= sprintf(' AND sentmail_who = %s', $this->_db->quote($GLOBALS['registry']->getAuth()));
123:         }
124: 
125:         /* Execute the query. */
126:         try {
127:             return $this->_db->selectValue($query, array(time() - $hours * 3600));
128:         } catch (Horde_Db_Exception $e) {
129:             throw new IMP_Exception($e);
130:         }
131:     }
132: 
133:     /**
134:      */
135:     protected function _deleteOldEntries($before)
136:     {
137:         /* Build the SQL query. */
138:         $query = sprintf('DELETE FROM %s WHERE sentmail_ts < ?',
139:                          $this->_params['table']);
140: 
141:         /* Execute the query. */
142:         try {
143:             $this->_db->delete($query, array($before));
144:         } catch (Horde_Db_Exception $e) {}
145:     }
146: 
147: }
148: