1: <?php
  2: /**
  3:  * Hermes SQL storage driver.
  4:  *
  5:  *
  6:  * See the enclosed file LICENSE for license information (BSD). If you
  7:  * did not receive this file, see http://www.horde.org/licenses/bsdl.php.
  8:  *
  9:  * @author  Chuck Hagenbuch <chuck@horde.org>
 10:  * @author  Michael J. Rubinsky <mrubinsk@horde.org>
 11:  * @package Hermes
 12:  */
 13: class Hermes_Driver_Sql extends Hermes_Driver
 14: {
 15:     /**
 16:      * Handle for the current database connection.
 17:      *
 18:      * @var Horde_Db_Adapter
 19:      */
 20:     protected $_db;
 21: 
 22:     /**
 23:      * Constructor
 24:      *
 25:      * @param array $params  A hash containing connection parameters.
 26:      * <pre>
 27:      *   db_adapter => The Horde_Db_Adapter object
 28:      * </pre>
 29:      *
 30:      * @return Hermes_Driver_Sql  The driver object.
 31:      */
 32:     public function __construct($params = array())
 33:     {
 34:         parent::__construct($params);
 35:         if (empty($params['db_adapter'])) {
 36:             throw new InvalidArgumentException('Missing Horde_Db_Adapter parameter.');
 37:         }
 38:         $this->_db = $params['db_adapter'];
 39:     }
 40: 
 41:     /**
 42:      * Save a row of billing information.
 43:      *
 44:      * @param string $employee  The Horde ID of the person who worked the
 45:      *                          hours.
 46:      * @param array $info       The billing information to enter. Must contain
 47:      *                          the following entries:
 48:      *<pre>
 49:      *  'date'         The day the hours were worked (ISO format)
 50:      *  'client'       The id of the client the work was done for.
 51:      *  'type'         The type of work done.
 52:      *  'hours'        The number of hours worked
 53:      *  'billable'     (optional) Whether or not the work is billable hours.
 54:      *  'description'  A short description of the work.
 55:      *  'note'         Any notes.
 56:      *  'costobject'   The costobject id
 57:      *</pre>
 58:      *
 59:      * @return integer  The new timeslice_id of the newly entered slice
 60:      * @throws Hermes_Exception
 61:      */
 62:     public function enterTime($employee, $info)
 63:     {
 64:         /* Get job rate */
 65:         $sql = 'SELECT jobtype_rate FROM hermes_jobtypes WHERE jobtype_id = ?';
 66:         try {
 67:             $job_rate = $this->_db->selectValue($sql, array($info['type']));
 68:         } catch (Horde_Db_Exception $e) {
 69:             throw new Hermes_Exception($e);
 70:         }
 71:         $dt = new Horde_Date($info['date']);
 72:         $sql = 'INSERT INTO hermes_timeslices (' .
 73:                'clientjob_id, employee_id, jobtype_id, ' .
 74:                'timeslice_hours, timeslice_isbillable, ' .
 75:                'timeslice_date, timeslice_description, ' .
 76:                'timeslice_note, timeslice_rate, costobject_id) ' .
 77:                'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
 78: 
 79:         $values = array($info['client'],
 80:                         $employee,
 81:                         $info['type'],
 82:                         $info['hours'],
 83:                         isset($info['billable']) ? (int)$info['billable'] : 0,
 84:                         $dt->timestamp() + 1,
 85:                         $this->_convertToDriver($info['description']),
 86:                         $this->_convertToDriver($info['note']),
 87:                         (float)$job_rate,
 88:                         (empty($info['costobject']) ? null :
 89:                          $info['costobject']));
 90: 
 91:         try {
 92:             return $this->_db->insert($sql, $values);
 93:         } catch (Horde_Db_Exception $e) {
 94:             throw new Hermes_Exception($e);
 95:         }
 96:     }
 97: 
 98:     /**
 99:      * Update a set of billing information.
100:      *
101:      * @param array $entries  The billing information to enter. Each array row
102:      *                        must contain the following entries:
103:      *              'id'           The id of this time entry.
104:      *              'date'         The day the hours were worked (ISO format)
105:      *              'client'       The id of the client the work was done for.
106:      *              'type'         The type of work done.
107:      *              'hours'        The number of hours worked
108:      *              'rate'         The hourly rate the work was done at.
109:      *              'billable'     Whether or not the work is billable hours.
110:      *              'description'  A short description of the work.
111:      *
112:      *                        If any rows contain a 'delete' entry, those rows
113:      *                        will be deleted instead of updated.
114:      *
115:      * @return mixed  boolean
116:      * @throws Horde_Exception_PermissionDenied
117:      * @throws Hermes_Exception
118:      */
119:     public function updateTime($entries)
120:     {
121:         foreach ($entries as $info) {
122:             if (!Hermes::canEditTimeslice($info['id'])) {
123:                 throw new Horde_Exception_PermissionDenied(_("Access denied; user cannot modify this timeslice."));
124:             }
125:             if (!empty($info['delete'])) {
126:                 try {
127:                     return $this->_db->delete('DELETE FROM hermes_timeslices WHERE timeslice_id = ?', array((int)$info['id']));
128:                 } catch (Horde_Db_Exception $e) {
129:                     throw new Hermes_Exception($e);
130:                 }
131:             } else {
132:                 if (isset($info['employee'])) {
133:                     $employee_cl = ' employee_id = ?,';
134: 
135:                     $values = array($info['employee']);
136:                 } else {
137:                     $employee_cl = '';
138:                 }
139:                 $dt = new Horde_Date($info['date']);
140:                 $sql = 'UPDATE hermes_timeslices SET' . $employee_cl .
141:                        ' clientjob_id = ?, jobtype_id = ?,' .
142:                        ' timeslice_hours = ?, timeslice_isbillable = ?,' .
143:                        ' timeslice_date = ?, timeslice_description = ?,' .
144:                        ' timeslice_note = ?, costobject_id = ?' .
145:                        ' WHERE timeslice_id = ?';
146:                 $values = array($info['client'],
147:                                 $info['type'],
148:                                 $info['hours'],
149:                                 (isset($info['billable']) ? (int)$info['billable'] : 0),
150:                                 $dt->timestamp(),
151:                                 $this->_convertToDriver($info['description']),
152:                                 $this->_convertToDriver($info['note']),
153:                                 (empty($info['costobject']) ? null : $info['costobject']),
154:                                 (int)$info['id']);
155:                 try {
156:                     return $this->_db->update($sql, $values);
157:                 } catch (Horde_Db_Exception $e) {
158:                     throw new Hermes_Exception($e);
159:                 }
160:             }
161:         }
162:     }
163: 
164:     /**
165:      * Fetch time slices
166:      *
167:      * @param array $filters
168:      * @param array $fields
169:      *
170:      * @return array  Array of timeslice objects
171:      */
172:     public function getHours(array $filters = array(), array $fields = array())
173:     {
174:         global $conf;
175: 
176:         $fieldlist = array(
177:             'id'          => 'b.timeslice_id as id',
178:             'client'      => ' b.clientjob_id as client',
179:             'employee'    => ' b.employee_id as employee',
180:             'type'        => ' b.jobtype_id as type',
181:             '_type_name'  => ' j.jobtype_name as "_type_name"',
182:             'hours'       => ' b.timeslice_hours as hours',
183:             'rate'        => ' b.timeslice_rate as rate',
184:             'billable'    => empty($conf['time']['choose_ifbillable'])
185:                 ? ' j.jobtype_billable as billable'
186:                 : ' b.timeslice_isbillable as billable',
187:             'date'        => ' b.timeslice_date as "date"',
188:             'description' => ' b.timeslice_description as description',
189:             'note' => ' b.timeslice_note as note',
190:             'submitted'   => ' b.timeslice_submitted as submitted',
191:             'costobject'  => ' b.costobject_id as costobject');
192: 
193:         if (!empty($fields)) {
194:             $fieldlist = array_keys(array_intersect(array_flip($fieldlist), $fields));
195:         }
196:         $fieldlist = implode(', ', $fieldlist);
197:         $sql = 'SELECT ' . $fieldlist . ' FROM hermes_timeslices b INNER JOIN hermes_jobtypes j ON b.jobtype_id = j.jobtype_id';
198:         if (count($filters) > 0) {
199:             $where = '';
200:             $glue = '';
201:             foreach ($filters as $field => $filter) {
202:                 switch ($field) {
203:                 case 'client':
204:                     $where .= $glue . $this->_equalClause('b.clientjob_id', $filter);
205:                     $glue = ' AND';
206:                     break;
207: 
208:                 case 'jobtype':
209:                     $where .= $glue . $this->_equalClause('b.jobtype_id', $filter);
210:                     $glue = ' AND';
211:                     break;
212: 
213:                 case 'submitted':
214:                     $where .= $glue . ' timeslice_submitted = ' . (int)$filter;
215:                     $glue = ' AND';
216:                     break;
217: 
218:                 case 'exported':
219:                     $where .= $glue . ' timeslice_exported = ' . (int)$filter;
220:                     $glue = ' AND';
221:                     break;
222: 
223:                 case 'billable':
224:                     $where .= $glue
225:                         . (empty($conf['time']['choose_ifbillable'])
226:                            ? ' jobtype_billable = '
227:                            : ' timeslice_isbillable = ')
228:                         . (int)$filter;
229:                     $glue = ' AND';
230:                     break;
231: 
232:                 case 'start':
233:                     $where .= $glue . ' timeslice_date >= ' . (int)$filter;
234:                     $glue = ' AND';
235:                     break;
236: 
237:                 case 'end':
238:                     $where .= $glue . ' timeslice_date <= ' . (int)$filter;
239:                     $glue = ' AND';
240:                     break;
241: 
242:                 case 'employee':
243:                     $where .= $glue . $this->_equalClause('employee_id', $filter);
244:                     $glue = ' AND';
245:                     break;
246: 
247:                 case 'id':
248:                     $where .= $glue . $this->_equalClause('timeslice_id', (int)$filter, false);
249:                     $glue = ' AND';
250:                     break;
251: 
252:                 case 'costobject':
253:                     $where .= $glue . $this->_equalClause('costobject_id', $filter);
254:                     $glue = ' AND';
255:                     break;
256:                 }
257:             }
258:         }
259: 
260:         if (!empty($where)) {
261:             $sql .= ' WHERE ' . $where;
262:         }
263:         $sql .= ' ORDER BY timeslice_date DESC, clientjob_id';
264: 
265:         try {
266:             $hours = $this->_db->selectAll($sql);
267:         } catch (Horde_Db_Exception $e) {
268:             throw new Hermes_Exception($e);
269:         }
270:         $slices = array();
271: 
272:         // Do per-record processing
273:         $addcostobject = empty($fields) || in_array('costobject', $fields);
274:         foreach ($hours as $key => $hour) {
275:             if (isset($hour['date'])) {
276:                 // Convert timestamps to Horde_Date objects
277:                 $hour['date'] = new Horde_Date($hour['date']);
278:             }
279:             if (isset($hour['description'])) {
280:                 $hour['description'] = $this->_convertFromDriver($hour['description']);
281:             }
282:             if (isset($hour['note'])) {
283:                 $hour['note'] = $this->_convertFromDriver($hour['note']);
284:             }
285:             if ($addcostobject) {
286:                 if (empty($hour['costobject'])) {
287:                     $hour['_costobject_name'] = '';
288:                 } else {
289:                     try {
290:                         $costobject = Hermes::getCostObjectByID($hour['costobject']);
291:                     } catch (Horde_Exception $e) {
292:                         $hour['_costobject_name'] = sprintf(_("Error: %s"), $e->getMessage());
293:                     }
294:                     $hour['_costobject_name'] = $costobject['name'];
295:                 }
296:             }
297: 
298:             $slices[$key] = new Hermes_Slice($hour);
299:         }
300: 
301:         return $slices;
302:     }
303: 
304:     /**
305:      * @TODO
306:      */
307:     private function _equalClause($lhs, $rhs, $quote = true)
308:     {
309:         if (!is_array($rhs)) {
310:             if ($quote) {
311:                 return sprintf(' %s = %s', $lhs, $this->_db->quote($rhs));
312:             }
313:             return sprintf(' %s = %s', $lhs, $rhs);
314:         }
315: 
316:         if (count($rhs) == 0) {
317:             return ' FALSE';
318:         }
319: 
320:         $glue = '';
321:         $ret = sprintf(' %s IN ( ', $lhs);
322:         foreach ($rhs as $value) {
323:             $ret .= $glue . $this->_db->quote($value);
324:             $glue = ', ';
325:         }
326:         return $ret . ' )';
327:     }
328: 
329:     /**
330:      * @TODO
331:      *
332:      * @param <type> $field
333:      * @param <type> $hours
334:      * @return <type>
335:      */
336:     public function markAs($field, $hours)
337:     {
338:         if (!count($hours)) {
339:             return false;
340:         }
341: 
342:         switch ($field) {
343:         case 'submitted':
344:             $h_field = 'timeslice_submitted';
345:             break;
346: 
347:         case 'exported':
348:             $h_field = 'timeslice_exported';
349:             break;
350: 
351:         default:
352:             return false;
353:         }
354: 
355:         $ids = array();
356:         foreach ($hours as $entry) {
357:             $ids[] = (int)$entry['id'];
358:         }
359: 
360:         $sql = 'UPDATE hermes_timeslices SET ' . $h_field . ' = 1' .
361:                ' WHERE timeslice_id IN (' . implode(',', $ids) . ')';
362: 
363:         return $this->_db->update($sql);
364:     }
365: 
366:     /**
367:      * @TODO
368:      *
369:      * @param <type> $criteria
370:      * @return <type>
371:      */
372:     public function listJobTypes(array $criteria = array())
373:     {
374:         $where = array();
375:         $values = array();
376:         if (isset($criteria['id'])) {
377:             $where[] = 'jobtype_id = ?';
378:             $values[] = $criteria['id'];
379:         }
380:         if (isset($criteria['enabled'])) {
381:             $where[] = 'jobtype_enabled = ?';
382:             $values[] = ($criteria['enabled'] ? 1 : 0);
383:         }
384: 
385:         $sql = 'SELECT jobtype_id, jobtype_name, jobtype_enabled'
386:             . ', jobtype_rate, jobtype_billable FROM hermes_jobtypes'
387:             . (empty($where) ? '' : (' WHERE ' . join(' AND ', $where)))
388:             . ' ORDER BY jobtype_name';
389: 
390:         try {
391:             $rows = $this->_db->selectAll($sql, $values);
392:         } catch (Horde_Db_Exception $e) {
393:             throw new Hermes_Exception($e);
394:         }
395: 
396:         $results = array();
397:         foreach ($rows as $row) {
398:             $id = $row['jobtype_id'];
399:             $results[$id] = array(
400:                 'id'       => $id,
401:                 'name'     => $this->_convertFromDriver($row['jobtype_name']),
402:                 'rate'     => (float)$row['jobtype_rate'],
403:                 'billable' => (int)$row['jobtype_billable'],
404:                 'enabled'  => !empty($row['jobtype_enabled']));
405:         }
406: 
407:         return $results;
408:     }
409: 
410:     public function updateJobType($jobtype)
411:     {
412:         if (!isset($jobtype['enabled'])) {
413:             $jobtype['enabled'] = 1;
414:         }
415:         if (!isset($jobtype['billable'])) {
416:             $jobtype['billable'] = 1;
417:         }
418:         if (empty($jobtype['id'])) {
419:             $sql = 'INSERT INTO hermes_jobtypes (jobtype_name, jobtype_enabled, '
420:                 . 'jobtype_rate, jobtype_billable) VALUES (?, ?, ?, ?)';
421:             $values = array(
422:                 $this->_convertToDriver($jobtype['name']),
423:                 (int)$jobtype['enabled'],
424:                 (float)$jobtype['rate'],
425:                 (int)$jobtype['billable']);
426: 
427:             try {
428:                 return $this->_db->insert($sql, $values);
429:             } catch (Horde_Db_Exception $e) {
430:                 throw new Hermes_Exception($e);
431:             }
432:         } else {
433:             $sql = 'UPDATE hermes_jobtypes' .
434:                    ' SET jobtype_name = ?, jobtype_enabled = ?, jobtype_rate = ?,' .
435:                    ' jobtype_billable = ?  WHERE jobtype_id = ?';
436:             $values = array($jobtype['name'],
437:                             (int)$jobtype['enabled'],
438:                             (float)$jobtype['rate'],
439:                             (int)$jobtype['billable'],
440:                             $jobtype['id']);
441: 
442:             try {
443:                 $this->_db->update($sql, $values);
444:             } catch (Horde_Db_Exception $e) {
445:                 throw new Hermes_Exception($e);
446:             }
447: 
448:             return $jobtype['id'];
449:         }
450:     }
451: 
452:     public function deleteJobType($jobTypeID)
453:     {
454:         try {
455:             return $this->_db->delete('DELETE FROM hermes_jobtypes WHERE jobtype_id = ?', array($jobTypeID));
456:         } catch (Horde_Db_Exception $e) {
457:             throw Hermes_Exception($e);
458:         }
459:     }
460: 
461:     /**
462:      * @see Hermes_Driver::updateDeliverable
463:      */
464:     public function updateDeliverable($deliverable)
465:     {
466:         if (empty($deliverable['id'])) {
467:             $sql = 'INSERT INTO hermes_deliverables ('
468:                 . ' client_id, deliverable_name, deliverable_parent,'
469:                 . ' deliverable_estimate, deliverable_active,'
470:                 . ' deliverable_description) VALUES (?, ?, ?, ?, ?, ?)';
471: 
472:             $values = array(
473:                 $deliverable['client_id'],
474:                 $this->_convertToDriver($deliverable['name']),
475:                 (empty($deliverable['parent']) ? null :
476:                  (int)$deliverable['parent']),
477:                 (empty($deliverable['estimate']) ? null :
478:                  $deliverable['estimate']),
479:                 ($deliverable['active'] ? 1 : 0),
480:                 (empty($deliverable['description']) ?
481:                  null :
482:                  $this->_convertToDriver($deliverable['description'])));
483: 
484:             try {
485:                 return $this->_db->insert($sql, $values);
486:             } catch (Horde_Db_Exception $e) {
487:                 throw new Hermes_Exception($e);
488:             }
489:         } else {
490:             $sql = 'UPDATE hermes_deliverables SET client_id = ?,'
491:                 . ' deliverable_name = ?, deliverable_parent = ?,'
492:                 . ' deliverable_estimate = ?, deliverable_active = ?,'
493:                 . ' deliverable_description = ? WHERE deliverable_id = ?';
494: 
495:             $values = array(
496:                 $deliverable['client_id'],
497:                 $this->_convertToDriver($deliverable['name']),
498:                 (empty($deliverable['parent']) ? null :
499:                  (int)$deliverable['parent']),
500:                 (empty($deliverable['estimate']) ? null :
501:                  $deliverable['estimate']),
502:                 ($deliverable['active'] ? 1 : 0),
503:                 (empty($deliverable['description']) ?
504:                  null :
505:                  $this->_convertToDriver($deliverable['description'])),
506:                 $deliverable['id']);
507:             try {
508:                 $this->_db->update($sql, $values);
509:                 return $deliverable['id'];
510:             } catch (Horde_Db_Exception $e) {
511:                 throw new Hermes_Exception($e);
512:             }
513:         }
514:     }
515: 
516:     /**
517:      * @see Hermes_Driver::listDeliverables()
518:      */
519:     public function listDeliverables($criteria = array())
520:     {
521:         $where = array();
522:         $values = array();
523:         if (isset($criteria['id'])) {
524:             $where[] = 'deliverable_id = ?';
525:             $values[] = $criteria['id'];
526:         }
527:         if (isset($criteria['client_id'])) {
528:             if (is_array($criteria['client_id'])) {
529:                 $where[] = 'client_id IN ('
530:                     . implode(', ',
531:                               array_fill(0, count($criteria['client_id']), '?'))
532:                     . ')';
533:                 $values = array_merge($values, $criteria['client_id']);
534:             } else {
535:                 $where[] = 'client_id = ?';
536:                 $values[] = $criteria['client_id'];
537:             }
538:         }
539:         if (isset($criteria['active'])) {
540:             if ($criteria['active']) {
541:                 $where[] = 'deliverable_active <> ?';
542:             } else {
543:                 $where[] = 'deliverable_active = ?';
544:             }
545:             $values[] = 0;
546:         }
547: 
548:         $sql = 'SELECT * FROM hermes_deliverables'
549:             . (count($where) ? ' WHERE ' . join(' AND ', $where) : '');
550: 
551:         try {
552:             $rows = $this->_db->selectAll($sql, $values);
553:         } catch (Horde_Db_Exception $e) {
554:             throw new Hermes_Exception($e);
555:         }
556: 
557:         $deliverables = array();
558:         foreach ($rows as $row) {
559:             $deliverable = array(
560:                 'id'          => $row['deliverable_id'],
561:                 'client_id'   => $row['client_id'],
562:                 'name'        => $this->_convertFromDriver($row['deliverable_name']),
563:                 'parent'      => $row['deliverable_parent'],
564:                 'estimate'    => $row['deliverable_estimate'],
565:                 'active'      => !empty($row['deliverable_active']),
566:                 'description' => $this->_convertFromDriver($row['deliverable_description']));
567:             $deliverables[$row['deliverable_id']] = $deliverable;
568:         }
569: 
570:         return $deliverables;
571:     }
572: 
573:     /**
574:      * @see Hermes_Driver::updateDeliverable
575:      * @throws Hermes_Exception
576:      */
577:     public function deleteDeliverable($deliverableID)
578:     {
579:         $sql = 'SELECT COUNT(*) AS c FROM hermes_deliverables WHERE deliverable_parent = ?';
580:         $values = array($deliverableID);
581: 
582:         try {
583:             $result = $this->_db->selectValue($sql, $values);
584:         } catch (Horde_Db_Exception $e) {
585:             throw new Hermes_Exception($e);
586:         }
587:         if (!empty($result)) {
588:             throw new Hermes_Exception(_("Cannot delete deliverable; it has children."));
589:         }
590: 
591:         $sql = 'SELECT COUNT(*) AS c FROM hermes_timeslices WHERE costobject_id = ?';
592:         $values = array($deliverableID);
593:         try {
594:             $result = $this->_db->selectValue($sql, $values);
595:         } catch (Horde_Db_Exception $e) {
596:             throw new Hermes_Exception($e);
597:         }
598:         if (!empty($result)) {
599:             throw Hermes_Exception(_("Cannot delete deliverable; there is time entered on it."));
600:         }
601: 
602:         $sql = 'DELETE FROM hermes_deliverables WHERE deliverable_id = ?';
603:         $values = array($deliverableID);
604: 
605:         try {
606:             return $this->_db->delete($sql, $values);
607:         } catch (Horde_Db_Exception $e) {
608:             throw new Hermes_Exception($e);
609:         }
610:     }
611: 
612:     /**
613:      * Fetch client settings from storage.
614:      *
615:      * @param integer the client id
616:      *
617:      * @return array  A hash of client settings.
618:      * @throws Hermes_Exception
619:      */
620:     public function getClientSettings($clientID)
621:     {
622:         $clients = Hermes::listClients();
623:         if (empty($clientID) || !isset($clients[$clientID])) {
624:             throw new Horde_Exception_NotFound('Does not exist');
625:         }
626: 
627:         $sql = 'SELECT clientjob_id, clientjob_enterdescription,'
628:             . ' clientjob_exportid FROM hermes_clientjobs'
629:             . ' WHERE clientjob_id = ?';
630:         $values = array($clientID);
631: 
632:         try {
633:             $rows = $this->_db->selectAll($sql, $values);
634:         } catch (Horde_Db_Exception $e) {
635:             throw new Hermes_Exception($e);
636:         }
637: 
638:         $clientJob = array();
639:         foreach ($rows as $row) {
640:             $clientJob[$row['clientjob_id']] = array(
641:                 $row['clientjob_enterdescription'],
642:                 $row['clientjob_exportid']);
643:         }
644: 
645:         if (isset($clientJob[$clientID])) {
646:             $settings = array(
647:                 'id' => $clientID,
648:                 'enterdescription' => $clientJob[$clientID][0],
649:                 'exportid' => $this->_convertFromDriver($clientJob[$clientID][1]));
650:         } else {
651:             $settings = array(
652:                 'id' => $clientID,
653:                 'enterdescription' => 1,
654:                 'exportid' => null);
655:         }
656:         $settings['name'] = $clients[$clientID];
657: 
658:         return $settings;
659:     }
660: 
661:     /**
662:      * @TODO
663:      *
664:      * @param <type> $clientID
665:      * @param <type> $enterDescription
666:      * @param string $exportID
667:      * @return <type>
668:      */
669:     public function updateClientSettings($clientID, $enterDescription = 1, $exportID = null)
670:     {
671:         if (empty($exportID)) {
672:             $exportID = null;
673:         }
674: 
675:         $sql = 'SELECT clientjob_id FROM hermes_clientjobs WHERE clientjob_id = ?';
676:         $values = array($clientID);
677: 
678:         if ($this->_db->selectValue($sql, $values) !== $clientID) {
679:             $sql = 'INSERT INTO hermes_clientjobs (clientjob_id,'
680:                 . ' clientjob_enterdescription, clientjob_exportid)'
681:                 . ' VALUES (?, ?, ?)';
682:             $values = array(
683:                 $clientID,
684:                 (int)$enterDescription,
685:                 $this->_convertToDriver($exportID));
686: 
687:             try {
688:                 return $this->_db->insert($sql, $values);
689:             } catch (Horde_Db_Exception $e) {
690:                 throw new Hermes_Exception($e);
691:             }
692:         } else {
693:             $sql = 'UPDATE hermes_clientjobs SET'
694:                 . ' clientjob_exportid = ?, clientjob_enterdescription = ?'
695:                 . ' WHERE clientjob_id = ?';
696:             $values = array(
697:                 $this->_convertToDriver($exportID),
698:                 (int)$enterDescription,
699:                 $clientID);
700: 
701:             try {
702:                 return $this->_db->update($sql, $values);
703:             } catch (Horde_Db_Exception $e) {
704:                 throw new Hermes_Exception($e);
705:             }
706:         }
707:     }
708: 
709:     /**
710:      * @TODO
711:      * @global  $conf
712:      * @return <type>
713:      */
714:     public function purge()
715:     {
716:         global $conf;
717: 
718:         $query = 'DELETE FROM hermes_timeslices'
719:             . ' WHERE timeslice_exported = ? AND timeslice_date < ?';
720:         $values = array(
721:             1,
722:             mktime(0, 0, 0, date('n'), date('j') - $conf['time']['days_to_keep']));
723: 
724:         return $this->_db->delete($query, $values);
725:     }
726: 
727:     /**
728:      * Converts a value from the driver's charset to the default
729:      * charset.
730:      *
731:      * @param mixed $value  A value to convert.
732:      *
733:      * @return mixed  The converted value.
734:      */
735:     protected function _convertFromDriver($value)
736:     {
737:         return Horde_String::convertCharset($value, $this->_db->getOption('charset'), 'UTF-8');
738:     }
739: 
740:     /**
741:      * Converts a value from the default charset to the driver's
742:      * charset.
743:      *
744:      * @param mixed $value  A value to convert.
745:      *
746:      * @return mixed  The converted value.
747:      */
748:     protected function _convertToDriver($value)
749:     {
750:         return Horde_String::convertCharset($value, 'UTF-8', $this->_db->getOption('charset'));
751: 
752:     }
753: 
754: }
755: