1: <?php
  2: 
  3: require_once HYLAX_BASE . '/lib/SQL/Attributes.php';
  4: 
  5: /**
  6:  * Hylax_Storage_sql Class
  7:  *
  8:  * Copyright 2003-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 Marko Djukic <marko@oblo.com>
 14:  * @package Hylax
 15:  */
 16: class Hylax_Storage_sql extends Hylax_Storage {
 17: 
 18:     /**
 19:      * Handle for the database connection.
 20:      *
 21:      * @var DB
 22:      */
 23:     var $_db;
 24: 
 25:     /**
 26:      * @var Hylax_SQL_Attributes
 27:      */
 28:     var $_attributes;
 29: 
 30:     function Hylax_Storage_sql($params)
 31:     {
 32:         parent::Hylax_Storage($params);
 33:         $this->initialise();
 34: 
 35:         /* Set up the storage attributes object in the $_attributes var. */
 36:         $attrib_params = array('primary_table'   => 'hylax_faxes',
 37:                                'attribute_table' => 'hylax_fax_attributes',
 38:                                'id_column'       => 'fax_id');
 39:         $this->_attributes = new Hylax_SQL_Attributes($this->_db, $attrib_params);
 40:     }
 41: 
 42:     function newFaxId()
 43:     {
 44:         $id = $this->_db->nextId('hylax_faxes');
 45:         if (is_a($id, 'PEAR_Error')) {
 46:             Horde::logMessage('Could not generate new fax id. %s' . $id->getMessage(), 'ERR');
 47:         } else {
 48:             Horde::logMessage('Generated new fax id: ' . $id, 'DEBUG');
 49:         }
 50:         return $id;
 51:     }
 52: 
 53:     function _createFax(&$info)
 54:     {
 55:         /* Save to SQL. */
 56:         $sql = 'INSERT INTO hylax_faxes (fax_id, fax_type, fax_user, fax_number, fax_pages, fax_created, fax_folder) VALUES (?, ?, ?, ?, ?, ?, ?)';
 57:         $values = array($info['fax_id'],
 58:                        (int)$info['fax_type'],
 59:                        $info['fax_user'],
 60:                        $info['fax_number'],
 61:                        $info['fax_pages'],
 62:                        $info['fax_created'],
 63:                        $info['fax_folder']);
 64:         Horde::logMessage('SQL Query by Hylax_Storage_sql::_createFax(): ' . $sql, 'DEBUG');
 65:         $result = $this->_db->query($sql, $values);
 66:         if (is_a($result, 'PEAR_Error')) {
 67:             Horde::logMessage($result, 'ERR');
 68:         }
 69:         return $result;
 70:     }
 71: 
 72:     function _listFaxes($folder)
 73:     {
 74:         $sql = 'SELECT * FROM hylax_faxes WHERE fax_folder = ?';
 75:         $values = array($folder);
 76:         $faxes = $this->_db->getAll($sql, $values, DB_FETCHMODE_ASSOC);
 77:         return $faxes;
 78:     }
 79: 
 80:     function getFax($fax_id)
 81:     {
 82:         $sql = 'SELECT * FROM hylax_faxes WHERE fax_id = ?';
 83:         $values = array($fax_id);
 84:         $fax = $this->_db->getRow($sql, $values, DB_FETCHMODE_ASSOC);
 85:         if (empty($fax)) {
 86:             return PEAR::raiseError(_("No such fax found."));
 87:         }
 88:         return $fax;
 89:     }
 90: 
 91:     function getFaxFolder($fax_id)
 92:     {
 93:         $sql = 'SELECT fax_folder FROM hylax_faxes WHERE fax_id = ?';
 94:         $values = array($fax_id);
 95:         $fax_folder = $this->_db->getOne($sql, $values);
 96:         if (empty($fax_folder)) {
 97:             return PEAR::raiseError(_("No such fax found."));
 98:         }
 99:         return $fax_folder;
100:     }
101: 
102:     function _setFaxNumber($fax_id, $number)
103:     {
104:         $sql = 'UPDATE hylax_faxes SET fax_number = ? WHERE fax_id = ?';
105:         $values = array($number, (int)$fax_id);
106:         return $this->_db->query($sql, $values);
107:     }
108: 
109:     function _setJobId($fax_id, $job_id)
110:     {
111:         $sql = 'UPDATE hylax_faxes SET job_id = ? WHERE fax_id = ?';
112:         $values = array((int)$job_id, (int)$fax_id);
113:         return $this->_db->query($sql, $values);
114:     }
115: 
116:     function _getFolder($folder, $path = null)
117:     {
118:         switch ($folder) {
119:         case 'inbox':
120:             //return $this->_parseFaxStat($this->_exec('faxstat -r'));
121:             break;
122: 
123:         case 'outbox':
124:             return $this->_parseFaxStat($this->_exec('faxstat -s'));
125:             break;
126: 
127:         case 'archive':
128:             //return $GLOBALS['storage']->getFolder($path);
129:             break;
130:         }
131:     }
132: 
133:     /**
134:      * Fetches a list of available gateways.
135:      *
136:      * @return array  An array of the available gateways.
137:      */
138:     function getGateways()
139:     {
140:         /* Get the gateways. */
141:         $sql = 'SELECT * FROM swoosh_gateways';
142:         Horde::logMessage('SQL Query by Hylax_Storage_sql::_getGateways(): ' . $sql, 'DEBUG');
143:         $result = $this->_db->getAll($sql, DB_FETCHMODE_ASSOC);
144:         if (is_a($result, 'PEAR_Error')) {
145:             Horde::logMessage($result, 'ERR');
146:         }
147: 
148:         return $result;
149:     }
150: 
151:     /**
152:      * Fetches a gateway from the backend.
153:      *
154:      * @param int $gateway_id  The gateway id to fetch.
155:      *
156:      * @return array  An array containing the gateway settings and parameters.
157:      */
158:     function &getGateway($gateway_id)
159:     {
160:         /* Get the gateways. */
161:         $sql = 'SELECT * FROM swoosh_gateways WHERE gateway_id = ?';
162:         $values = array((int)$gateway_id);
163:         Horde::logMessage('SQL Query by Hylax_Storage_sql::getGateway(): ' . $sql, 'DEBUG');
164:         $gateway = $this->_db->getRow($sql, $values, DB_FETCHMODE_ASSOC);
165:         if (is_a($gateway, 'PEAR_Error')) {
166:             Horde::logMessage($gateway, 'ERR');
167:             return $gateway;
168:         }
169: 
170:         /* Unserialize the gateway params. */
171:         $gateway['gateway_params'] = Horde_Serialize::unserialize($gateway['gateway_params'], Horde_Serialize::UTF7_BASIC);
172: 
173:         /* Unserialize the gateway send params. */
174:         $gateway['gateway_sendparams'] = Horde_Serialize::unserialize($gateway['gateway_sendparams'], Horde_Serialize::UTF7_BASIC);
175: 
176:         return $gateway;
177:     }
178: 
179:     /**
180:      * Saves a gateway to the backend.
181:      *
182:      * @param array $info  The gateway settings to be saved passed by reference
183:      *                     as an array.
184:      *
185:      * @return mixed  Gateway id on success or a PEAR error on failure.
186:      */
187:     function saveGateway(&$info)
188:     {
189:         if (empty($info['gateway_id'])) {
190:             /* No existing gateway id, so new gateway and get next id. */
191:             $info['gateway_id'] = $this->_db->nextId('swoosh_gateways');
192:             if (is_a($info['gateway_id'], 'PEAR_Error')) {
193:                 Horde::logMessage($info['gateway_id'], 'ERR');
194:                 return $info['gateway_id'];
195:             }
196:             $sql = 'INSERT INTO swoosh_gateways (gateway_id, gateway_driver, gateway_name, gateway_params, gateway_sendparams) VALUES (?, ?, ?, ?, ?)';
197:             $values = array();
198:         } else {
199:             /* Existing gateway id, so editing an existing gateway. */
200:             $sql_sprintf = 'UPDATE swoosh_gateways SET gateway_id = ?, gateway_driver = ?, gateway_name = ?, gateway_params = ?, gateway_sendparams = ? WHERE gateway_id = ?';
201:             $values = array((int)$info['gateway_id']);
202:         }
203: 
204:         /* Serialize the gateway params. */
205:         if (!empty($info['gateway_params'])) {
206:             $info['gateway_params'] = Horde_Serialize::serialize($info['gateway_params'], Horde_Serialize::UTF7_BASIC);
207:         } else {
208:             $info['gateway_params'] = 'NULL';
209:         }
210: 
211:         /* Serialize the gateway send params. */
212:         if (!empty($info['gateway_sendparams'])) {
213:             $info['gateway_sendparams'] = Horde_Serialize::serialize($info['gateway_sendparams'], Horde_Serialize::UTF7_BASIC);
214:         } else {
215:             $info['gateway_sendparams'] = 'NULL';
216:         }
217: 
218:         /* Put together the sql statement. */
219:         array_unshift($values,
220:                       (int)$info['gateway_id'],
221:                       $info['gateway_driver'],
222:                       $info['gateway_name'],
223:                       $info['gateway_params'],
224:                       $info['gateway_sendparams']);
225:         Horde::logMessage('SQL Query by Hylax_Storage_sql::saveGateway(): ' . $sql, 'DEBUG');
226:         $result = $this->_db->query($sql, $values);
227:         if (is_a($result, 'PEAR_Error')) {
228:             Horde::logMessage($result, 'ERR');
229:             return $result;
230:         }
231: 
232:         return $info['gateway_id'];
233:     }
234: 
235:     /**
236:      * Deletes a gateway from the backend.
237:      *
238:      * @param int $gateway_id  The gateway id of the gateway to delete.
239:      *
240:      * @return mixed  True on success or a PEAR error on failure.
241:      */
242:     function deleteGateway($gateway_id)
243:     {
244:         $sql = 'DELETE FROM swoosh_gateways WHERE gateway_id = ?';
245:         $values = array((int)$gateway_id);
246:         Horde::logMessage('SQL Query by Hylax_Storage_sql::deleteGateway(): ' . $sql, 'DEBUG');
247:         $result = $this->_db->query($sql, $values);
248:         if (is_a($result, 'PEAR_Error')) {
249:             Horde::logMessage($result, 'ERR');
250:         }
251: 
252:         return $result;
253:     }
254: 
255:     /**
256:      * Saves a message to the backend.
257:      *
258:      * @param integer $gateway_id       The id of the gateway used to send this
259:      *                                  message.
260:      * @param string $message_text      The text of the message.
261:      * @param string $message_params    Any send params used for this message.
262:      * @param string $message_batch_id  If batch sending is used, the batch id
263:      *                                  of this message.
264:      *
265:      * @return mixed  True on success or PEAR Error on failure.
266:      */
267:     function saveMessage($gateway_id, $message_text, $message_params, $message_batch_id = null)
268:     {
269:         $message_id = $this->_db->nextId('swoosh_messages');
270:         if (is_a($message_id, 'PEAR_Error')) {
271:             Horde::logMessage($message_id, 'ERR');
272:             return $message_id;
273:         }
274: 
275:         /* Serialize the message params. */
276:         $message_params = Horde_Serialize::serialize($message_params, Horde_Serialize::UTF7_BASIC);
277: 
278:         $sql = 'INSERT INTO swoosh_messages (message_id, user_uid, gateway_id, message_batch_id, message_text, message_params, message_submitted) VALUES (?, ?, ?, ?, ?, ?, ?)';
279:         $values = array((int)$message_id,
280:                         $GLOBALS['registry']->getAuth(),
281:                         (int)$gateway_id,
282:                         is_null($message_batch_id) ? 'NULL' : (int)$message_batch_id,
283:                         $message_text,
284:                         $message_params,
285:                         (int)time());
286:         $result = $this->_db->query($sql, $values);
287:         if (is_a($result, 'PEAR_Error')) {
288:             Horde::logMessage($result, 'ERR');
289:             return $result;
290:         }
291: 
292:         return $message_id;
293:     }
294: 
295:     /**
296:      * Saves an individual send to the backend. This will be one instance of
297:      * a message being sent to a recipient.
298:      *
299:      * @param int    $message_id  The message id.
300:      * @param string $remote_id   The text of the message.
301:      * @param string $recipient   Any send params used for this
302:      * @param string $error       Any send params used for this
303:      *
304:      * @return mixed  The send id on success or PEAR Error on failure.
305:      */
306:     function saveSend($message_id, $remote_id, $recipient, $error)
307:     {
308:         $send_id = $this->_db->nextId('swoosh_sends');
309:         if (is_a($send_id, 'PEAR_Error')) {
310:             Horde::logMessage($send_id, 'ERR');
311:             return $send_id;
312:         }
313:         $sql = 'INSERT INTO swoosh_sends (send_id, message_id, send_remote_id, send_recipient, send_status) VALUES (?, ?, ?, ?, ?)';
314:         $values = array($send_id,
315:                         $message_id,
316:                         (is_null($remote_id) ? 'NULL' : $remote_id),
317:                         $recipient,
318:                         (is_null($error) ? 'NULL' : $error));
319:         $result = $this->_db->query($sql, $values);
320:         if (is_a($result, 'PEAR_Error')) {
321:             Horde::logMessage($result, 'ERR');
322:             return $result;
323:         }
324: 
325:         return $send_id;
326:     }
327: 
328:     /**
329:      * Gets all the messages for the current user.
330:      *
331:      * @access private
332:      *
333:      * @return mixed  The array of messages on success or PEAR Error on failure.
334:      */
335:     function _getMessages()
336:     {
337:         $sql = 'SELECT * FROM swoosh_messages WHERE user_uid = ?';
338:         $values = array($GLOBALS['registry']->getAuth());
339:         Horde::logMessage('SQL Query by Hylax_Storage_sql::_getMessages(): ' . $sql, 'DEBUG');
340:         $result = $this->_db->getAssoc($sql, false, $values, DB_FETCHMODE_ASSOC);
341:         if (is_a($result, 'PEAR_Error')) {
342:             Horde::logMessage($result, 'ERR');
343:         }
344: 
345:         return $result;
346:     }
347: 
348:     /**
349:      * Fetches all sends for one or more message ids.
350:      *
351:      * @access private
352:      * @param int|array  $message_id  The message id(s).
353:      *
354:      * @return mixed  The send id on success or PEAR Error on failure.
355:      */
356:     function _getSends($message_ids)
357:     {
358:         if (!is_array($message_ids)) {
359:             $message_ids = array($message_ids);
360:         }
361: 
362:         $sql = 'SELECT message_id, swoosh_sends.* FROM swoosh_sends' .
363:                ' WHERE message_id IN (' . str_repeat('?, ', count($message_ids) - 1) . '?)';
364:         $values = $message_ids;
365:         Horde::logMessage('SQL Query by Hylax_Storage_sql::_getSends(): ' . $sql, 'DEBUG');
366:         $result = $this->_db->getAssoc($sql, false, $values, DB_FETCHMODE_ASSOC, true);
367:         if (is_a($result, 'PEAR_Error')) {
368:             Horde::logMessage($result, 'ERR');
369:         }
370: 
371:         return $result;
372:     }
373: 
374:     function initialise()
375:     {
376:         $this->_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('rw', 'hylax', 'sql');
377: 
378:         return true;
379:     }
380: 
381: }
382: