1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14:
15: class Whups_Driver_Sql extends Whups_Driver
16: {
17: 18: 19: 20: 21:
22: protected $_db;
23:
24: 25: 26: 27: 28:
29: protected $_map = array(
30: 'id' => 'ticket_id',
31: 'summary' => 'ticket_summary',
32: 'requester' => 'user_id_requester',
33: 'queue' => 'queue_id',
34: 'version' => 'version_id',
35: 'type' => 'type_id',
36: 'state' => 'state_id',
37: 'priority' => 'priority_id',
38: 'timestamp' => 'ticket_timestamp',
39: 'due' => 'ticket_due',
40: 'date_updated' => 'date_updated',
41: 'date_assigned' => 'date_assigned',
42: 'date_resolved' => 'date_resolved'
43: );
44:
45: 46: 47: 48: 49:
50: protected $_guestEmailCache = array();
51:
52: 53: 54: 55: 56:
57: protected $_internalQueueCache = array();
58:
59: 60: 61: 62: 63:
64: protected $_queues = null;
65:
66: 67: 68: 69: 70:
71: protected $_slugs = null;
72:
73: public function setStorage($storage)
74: {
75: if (!($storage instanceof Horde_Db_Adapter_Base)) {
76: throw new InvalidArgumentException("Missing Horde_Db_Adapter_Base");
77: }
78: $this->_db = $storage;
79: }
80:
81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91:
92: public function addQueue($name, $description, $slug = '', $email = '')
93: {
94:
95: if (strlen($slug) &&
96: $this->_db->selectValue('SELECT 1 FROM whups_queues WHERE queue_slug = ?', array($slug))) {
97: throw new Whups_Exception(
98: _("That queue slug is already taken. Please select another."));
99: }
100:
101: try {
102: return $this->_db->insert(
103: 'INSERT INTO whups_queues (queue_name, queue_description, '
104: . 'queue_slug, queue_email) VALUES (?, ?, ?, ?)',
105: array($this->_toBackend($name),
106: $this->_toBackend($description),
107: $slug,
108: $email));
109: } catch (Horde_Db_Exception $e) {
110: throw new Whups_Exception($e);
111: }
112: }
113:
114: 115: 116: 117: 118: 119: 120: 121: 122:
123: public function addType($name, $description)
124: {
125: try {
126: return $this->_db->insert(
127: 'INSERT INTO whups_types (type_name, type_description) '
128: . 'VALUES (?, ?)',
129: array($this->_toBackend($name),
130: $this->_toBackend($description)));
131: } catch (Horde_Db_Exception $e) {
132: throw new Whups_Exception($e);
133: }
134: }
135:
136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146:
147: public function addState($typeId, $name, $description, $category)
148: {
149: try {
150: return $this->_db->insert(
151: 'INSERT INTO whups_states (type_id, state_name, '
152: . 'state_description, state_category) VALUES (?, ?, ?, ?)',
153: array((int)$typeId,
154: $this->_toBackend($name),
155: $this->_toBackend($description),
156: $this->_toBackend($category)));
157: } catch (Horde_Db_Exception $e) {
158: throw new Whups_Exception($e);
159: }
160: }
161:
162: 163: 164: 165: 166: 167: 168: 169: 170: 171:
172: public function addPriority($typeId, $name, $description)
173: {
174: try {
175: return $this->_db->insert(
176: 'INSERT INTO whups_priorities (type_id, priority_name, '
177: . 'priority_description) VALUES (?, ?, ?)',
178: array((int)$typeId,
179: $this->_toBackend($name),
180: $this->_toBackend($description)));
181: } catch (Horde_Db_Exception $e) {
182: throw new Whups_Exception($e);
183: }
184: }
185:
186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196:
197: public function addVersion($queueId, $name, $description, $active)
198: {
199: try {
200: return $this->_db->insert(
201: 'INSERT INTO whups_versions (queue_id, version_name, '
202: . 'version_description, version_active) '
203: . 'VALUES (?, ?, ?, ?)',
204: array((int)$queueId,
205: $this->_toBackend($name),
206: $this->_toBackend($description),
207: (boolean)$active));
208: } catch (Horde_Db_Exception $e) {
209: throw Whups_Exception($e);
210: }
211: }
212:
213: 214: 215: 216: 217: 218: 219: 220: 221: 222:
223: public function addReply($type, $name, $text)
224: {
225: try {
226: return $this->_db->insert(
227: 'INSERT INTO whups_replies (type_id, reply_name, reply_text) '
228: . 'VALUES (?, ?, ?)',
229: array((int)$type,
230: $this->_toBackend($name),
231: $this->_toBackend($text)));
232: } catch (Horde_Db_Exception $e) {
233: throw new Whups_Exception($e);
234: }
235: }
236:
237:
238: 239: 240: 241: 242: 243: 244: 245: 246: 247:
248: public function addTicket(array &$info, $requester)
249: {
250: $type = (int)$info['type'];
251: $state = (int)$info['state'];
252: $priority = (int)$info['priority'];
253: $queue = (int)$info['queue'];
254: $summary = $info['summary'];
255: $version = (int)isset($info['version']) ? $info['version'] : null;
256: $due = isset($info['due']) ? $info['due'] : null;
257: $comment = $info['comment'];
258: $attributes = isset($info['attributes']) ? $info['attributes'] : array();
259:
260:
261: try {
262: $ticket_id = $this->_db->insert(
263: 'INSERT INTO whups_tickets (ticket_summary, '
264: . 'user_id_requester, type_id, state_id, priority_id, '
265: . 'queue_id, ticket_timestamp, ticket_due, version_id)'
266: . ' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
267: array($this->_toBackend($summary),
268: $requester,
269: $type,
270: $state,
271: $priority,
272: $queue,
273: time(),
274: $due,
275: $version));
276: } catch (Horde_Db_Exception $e) {
277: throw new Whups_Exception($e);
278: }
279:
280:
281:
282: if (!empty($info['user_email'])) {
283: $requester = $ticket_id * -1;
284: try {
285: $this->_db->update(
286: 'UPDATE whups_tickets SET user_id_requester = ? WHERE '
287: . 'ticket_id = ?',
288: array($requester, $ticket_id));
289: } catch (Horde_Db_Exception $e) {
290: throw new Whups_Exception($e);
291: }
292: }
293:
294: if ($requester < 0) {
295: try {
296: $this->_db->insert(
297: 'INSERT INTO whups_guests (guest_id, guest_email) '
298: . 'VALUES (?, ?)',
299: array((string)$requester, $info['user_email']));
300: } catch (Horde_Db_Exception $e) {
301: throw new Whups_Exception($e);
302: }
303: }
304:
305: $commentId = $this->addComment(
306: $ticket_id, $comment, $requester,
307: isset($info['user_email']) ? $info['user_email'] : null);
308:
309:
310: if (!empty($info['group'])) {
311: Whups_Ticket::addCommentPerms($commentId, $info['group']);
312: }
313:
314: $transaction = $this->updateLog($ticket_id,
315: $requester,
316: array('state' => $state,
317: 'priority' => $priority,
318: 'type' => $type,
319: 'summary' => $summary,
320: 'due' => $due,
321: 'comment' => $commentId,
322: 'queue' => $queue));
323:
324:
325:
326: $info['last-transaction'] = $transaction;
327:
328:
329: $owners = array_merge(
330: isset($info['owners']) ? $info['owners'] : array(),
331: isset($info['group_owners']) ? $info['group_owners'] : array());
332: foreach ($owners as $owner) {
333: $this->addTicketOwner($ticket_id, $owner);
334: $this->updateLog($ticket_id, $requester,
335: array('assign' => $owner),
336: $transaction);
337: }
338:
339:
340: foreach ($attributes as $attribute_id => $attribute_value) {
341: $attribute_value = $this->_serializeAttribute($attribute_value);
342: $this->_setAttributeValue(
343: $ticket_id, $attribute_id, $attribute_value);
344:
345: $this->updateLog(
346: $ticket_id, $requester,
347: array('attribute' => $attribute_id . ':' . $attribute_value,
348: 'attribute_' . $attribute_id => $attribute_value),
349: $transaction);
350: }
351:
352: return $ticket_id;
353: }
354:
355: 356: 357: 358: 359: 360: 361: 362: 363: 364: 365:
366: public function ($ticket_id, $comment, $creator,
367: $creator_email = null)
368: {
369:
370: try {
371: $id = $this->_db->insert(
372: 'INSERT INTO whups_comments (ticket_id, user_id_creator, '
373: . ' comment_text, comment_timestamp) VALUES (?, ?, ?, ?)',
374: array((int)$ticket_id,
375: $creator,
376: $this->_toBackend($comment),
377: time()));
378:
379: if (empty($creator) || $creator < 0) {
380: $creator = '-' . $id . '_comment';
381: }
382: $this->_db->update(
383: 'UPDATE whups_comments SET user_id_creator = ?'
384: . ' WHERE comment_id = ?',
385: array($creator, $id));
386: } catch (Horde_Db_Exception $e) {
387: throw new Whups_Exception($e);
388: }
389:
390:
391:
392: if ($creator < 0 && !empty($creator_email)) {
393: try {
394: $this->_db->insert(
395: 'INSERT INTO whups_guests (guest_id, guest_email)'
396: . ' VALUES (?, ?)',
397: array((string)$creator, $creator_email));
398: } catch (Horde_Db_Exception $e) {
399: throw new Whups_Exception($e);
400: }
401: }
402:
403: return $id;
404: }
405:
406: 407: 408: 409: 410: 411: 412: 413: 414: 415: 416: 417: 418:
419: public function updateTicket($ticketId, $attributes)
420: {
421: if (!count($attributes)) {
422: return;
423: }
424:
425: $query = '';
426: $values = array();
427: foreach ($attributes as $field => $value) {
428: if (empty($this->_map[$field])) {
429: continue;
430: }
431:
432: $query .= $this->_map[$field] . ' = ?, ';
433: $values[] = $this->_toBackend($value);
434: }
435:
436:
437:
438: if (empty($query)) {
439: return;
440: }
441:
442: $values[] = (int)$ticketId;
443:
444: try {
445: $this->_db->update(
446: 'UPDATE whups_tickets SET ' . substr($query, 0, -2)
447: . ' WHERE ticket_id = ?',
448: $values);
449: } catch (Horde_Db_Exception $e) {
450: throw new Whups_Exception($e);
451: }
452: }
453:
454: 455: 456: 457: 458: 459: 460: 461:
462: public function addTicketOwner($ticketId, $owner)
463: {
464: try {
465: $this->_db->insert(
466: 'INSERT INTO whups_ticket_owners (ticket_id, ticket_owner) '
467: . 'VALUES (?, ?)',
468: array($ticketId, $owner));
469: } catch (Horde_Db_Exception $e) {
470: throw new Whups_Exception($e);
471: }
472: }
473:
474: 475: 476: 477: 478: 479: 480: 481:
482: public function deleteTicketOwner($ticketId, $owner)
483: {
484: try {
485: $this->_db->delete(
486: 'DELETE FROM whups_ticket_owners WHERE ticket_owner = ? '
487: . 'AND ticket_id = ?',
488: array($owner, (int)$ticketId));
489: } catch (Horde_Db_Exception $e) {
490: throw new Whups_Exception($e);
491: }
492: }
493:
494: 495: 496: 497: 498: 499: 500:
501: public function deleteTicket($id)
502: {
503: $id = (int)$id;
504:
505: $tables = array(
506: 'whups_ticket_listeners',
507: 'whups_logs',
508: 'whups_comments',
509: 'whups_tickets',
510: 'whups_attributes');
511:
512: if (!empty($GLOBALS['conf']['vfs']['type'])) {
513: try {
514: $vfs = $GLOBALS['injector']
515: ->getInstance('Horde_Core_Factory_Vfs')
516: ->create();
517: } catch (Horde_Vfs_Exception $e) {
518: throw new Whups_Exception($e);
519: }
520:
521: if ($vfs->isFolder(Whups::VFS_ATTACH_PATH, $id)) {
522: try {
523: $vfs->deleteFolder(Whups::VFS_ATTACH_PATH, $id, true);
524: } catch (Horde_Vfs_Exception $e) {
525: throw new Whups_Exception($e);
526: }
527: }
528: }
529:
530:
531: try {
532: $txs = $this->_db->selectValues(
533: 'SELECT DISTINCT transaction_id FROM whups_logs '
534: . 'WHERE ticket_id = ?',
535: array($id));
536: } catch (Horde_Db_Exception $e) {
537: throw new Whups_Exception($e);
538: }
539:
540: $this->_db->beginDbTransaction();
541: foreach ($tables as $table) {
542: try {
543: $this->_db->delete(
544: 'DELETE FROM ' . $table . ' WHERE ticket_id = ?',
545: array($id));
546: } catch (Horde_Db_Exception $e) {
547: $this->_db->rollbackDbTransaction();
548: throw new Whups_Exception($e);
549: }
550: }
551:
552: if (!empty($txs)) {
553: try {
554: $this->_db->delete(
555: 'DELETE FROM whups_transactions WHERE transaction_id IN '
556: . '(' . str_repeat('?,', count($txs) - 1) . '?)',
557: $txs);
558: } catch (Horde_Db_Exception $e) {
559: $this->_db->rollbackDbTransaction();
560: throw new Whups_Exception($e);
561: }
562: }
563:
564: $this->_db->commitDbTransaction();
565: }
566:
567: 568: 569: 570: 571: 572: 573: 574: 575: 576: 577: 578:
579: public function executeQuery(Whups_Query $query, Horde_Variables $vars,
580: $get_details = true, $munge = true)
581: {
582: $this->jtables = array();
583: $this->joins = array();
584:
585: $where = $query->reduce(array($this, 'clauseFromQuery'), $vars);
586: if (!$where) {
587: $GLOBALS['notification']->push(_("No query to run"), 'horde.message');
588: return array();
589: }
590:
591: if ($this->joins) {
592: $joins = implode(' ', $this->joins);
593: } else {
594: $joins = '';
595: }
596:
597: try {
598: $ids = $this->_db->selectValues(
599: "SELECT whups_tickets.ticket_id FROM whups_tickets $joins "
600: . "WHERE $where");
601: } catch (Horde_Db_Exception $e) {
602: $GLOBALS['notification']->push($e->getMessage(), 'horde.error');
603: return array();
604: }
605:
606: if (!count($ids)) {
607: return array();
608: }
609:
610: if ($get_details) {
611: $ids = $this->getTicketsByProperties(array('id' => $ids), $munge);
612: }
613:
614: return $ids;
615: }
616:
617: public function clauseFromQuery($args, $type, $criterion, $cvalue,
618: $operator, $value)
619: {
620: switch ($type) {
621: case Whups_Query::TYPE_AND:
622: return $this->_concatClauses($args, 'AND');
623:
624: case Whups_Query::TYPE_OR:
625: return $this->_concatClauses($args, 'OR');
626:
627: case Whups_Query::TYPE_NOT:
628: return $this->_notClause($args);
629:
630: case Whups_Query::TYPE_CRITERION:
631: return $this->_criterionClause($criterion, $cvalue, $operator, $value);
632: }
633: }
634:
635: protected function _concatClauses($args, $conjunction)
636: {
637: $count = count($args);
638:
639: if ($count == 0) {
640: $result = '';
641: } elseif ($count == 1) {
642: $result = $args[0];
643: } else {
644: $result = '(' . $args[0] . ')';
645: for ($i = 1; $i < $count; $i++) {
646: if ($args[$i] != '') {
647: $result .= ' ' . $conjunction . ' (' . $args[$i] . ')';
648: }
649: }
650: }
651:
652: return $result;
653: }
654:
655: protected function _notClause($args)
656: {
657: if (count($args) == 0) {
658: return '';
659: }
660:
661: if (count($args) !== 1) {
662: throw new InvalidArgumentException();
663: }
664:
665: return 'NOT (' . $args[0] . ')';
666: }
667:
668: 669: 670: 671: 672: 673:
674: protected function _criterionClause($criterion, $cvalue, $operator, $value)
675: {
676: $func = '';
677: $funcend = '';
678: $value = $this->_toBackend($value);
679:
680: switch ($operator) {
681: case Whups_Query::OPERATOR_GREATER: $op = '>'; break;
682: case Whups_Query::OPERATOR_LESS: $op = '<'; break;
683: case Whups_Query::OPERATOR_EQUAL: $op = '='; break;
684: case Whups_Query::OPERATOR_PATTERN: $op = 'LIKE'; break;
685:
686: case Whups_Query::OPERATOR_CI_SUBSTRING:
687: $value = '%' . str_replace(array('%', '_'), array('\%', '\_'), $value) . '%';
688: if ($this->_db->phptype == 'pgsql') {
689: $op = 'ILIKE';
690: } else {
691: $op = 'LIKE';
692: $func = 'LOWER(';
693: $funcend = ')';
694: }
695: break;
696:
697: case Whups_Query::OPERATOR_CS_SUBSTRING:
698:
699: $func = 'LOCATE(' . $this->_db->quoteString($value) . ', ';
700: $funcend = ')';
701: $op = '>';
702: $value = 0;
703: break;
704:
705: case Whups_Query::OPERATOR_WORD:
706:
707:
708: if ($this->_db->phptype == 'pgsql') {
709: $func = "' ' || ";
710: $funcend = " || ' '";
711: } else {
712: $func = "CONCAT(' ', CONCAT(";
713: $funcend = ", ' '))";
714: }
715: $op = 'LIKE';
716: $value = '%' . str_replace(array('%', '_'), array('\%', '\_'), $value) . '%';
717: break;
718: }
719:
720: $qvalue = $this->_db->quoteString($value);
721: $done = false;
722: $text = '';
723:
724: switch ($criterion) {
725: case Whups_Query::CRITERION_ID:
726: $text = "{$func}whups_tickets.ticket_id{$funcend}";
727: break;
728:
729: case Whups_Query::CRITERION_QUEUE:
730: $text = "{$func}whups_tickets.queue_id{$funcend}";
731: break;
732:
733: case Whups_Query::CRITERION_VERSION:
734: $text = "{$func}whups_tickets.version_id{$funcend}";
735: break;
736:
737: case Whups_Query::CRITERION_TYPE:
738: $text = "{$func}whups_tickets.type_id{$funcend}";
739: break;
740:
741: case Whups_Query::CRITERION_STATE:
742: $text = "{$func}whups_tickets.state_id{$funcend}";
743: break;
744:
745: case Whups_Query::CRITERION_PRIORITY:
746: $text = "{$func}whups_tickets.priority_id{$funcend}";
747: break;
748:
749: case Whups_Query::CRITERION_SUMMARY:
750: $text = "{$func}whups_tickets.ticket_summary{$funcend}";
751: break;
752:
753: case Whups_Query::CRITERION_TIMESTAMP:
754: $text = "{$func}whups_tickets.ticket_timestamp{$funcend}";
755: break;
756:
757: case Whups_Query::CRITERION_UPDATED:
758: $text = "{$func}whups_tickets.date_updated{$funcend}";
759: break;
760:
761: case Whups_Query::CRITERION_RESOLVED:
762: $text = "{$func}whups_tickets.date_resolved{$funcend}";
763: break;
764:
765: case Whups_Query::CRITERION_ASSIGNED:
766: $text = "{$func}whups_tickets.date_assigned{$funcend}";
767: break;
768:
769: case Whups_Query::CRITERION_DUE:
770: $text = "{$func}whups_tickets.ticket_due{$funcend}";
771: break;
772:
773: case Whups_Query::CRITERION_ATTRIBUTE:
774: $cvalue = (int)$cvalue;
775:
776: if (!isset($this->jtables['whups_attributes'])) {
777: $this->jtables['whups_attributes'] = 1;
778: }
779: $v = $this->jtables['whups_attributes']++;
780:
781: $this->joins[] = "LEFT JOIN whups_attributes wa$v ON (whups_tickets.ticket_id = wa$v.ticket_id AND wa$v.attribute_id = $cvalue)";
782: $text = "{$func}wa$v.attribute_value{$funcend} $op $qvalue";
783: $done = true;
784: break;
785:
786: case Whups_Query::CRITERION_OWNERS:
787: if (!isset($this->jtables['whups_ticket_owners'])) {
788: $this->jtables['whups_ticket_owners'] = 1;
789: }
790: $v = $this->jtables['whups_ticket_owners']++;
791:
792: $this->joins[] = "LEFT JOIN whups_ticket_owners wto$v ON whups_tickets.ticket_id = wto$v.ticket_id";
793: $qvalue = $this->_db->quotestring('user:' . $value);
794: $text = "{$func}wto$v.ticket_owner{$funcend} $op $qvalue";
795: $done = true;
796: break;
797:
798: case Whups_Query::CRITERION_REQUESTER:
799: if (!isset($this->jtables['whups_guests'])) {
800: $this->jtables['whups_guests'] = 1;
801: }
802: $v = $this->jtables['whups_guests']++;
803:
804: $this->joins[] = "LEFT JOIN whups_guests wg$v ON whups_tickets.user_id_requester = wg$v.guest_id";
805: $text = "{$func}whups_tickets.user_id_requester{$funcend} $op $qvalue OR {$func}wg$v.guest_email{$funcend} $op $qvalue";
806: $done = true;
807: break;
808:
809: case Whups_Query::CRITERION_GROUPS:
810: if (!isset($this->jtables['whups_ticket_owners'])) {
811: $this->jtables['whups_ticket_owners'] = 1;
812: }
813: $v = $this->jtables['whups_ticket_owners']++;
814:
815: $this->joins[] = "LEFT JOIN whups_ticket_owners wto$v ON whups_tickets.ticket_id = wto$v.ticket_id";
816: $qvalue = $this->_db->quoteString('group:' . $value);
817: $text = "{$func}wto$v.ticket_owner{$funcend} $op $qvalue";
818: $done = true;
819: break;
820:
821: case Whups_Query::CRITERION_ADDED_COMMENT:
822: if (!isset($this->jtables['whups_comments'])) {
823: $this->jtables['whups_comments'] = 1;
824: }
825: $v = $this->jtables['whups_comments']++;
826:
827: $this->joins[] = "LEFT JOIN whups_comments wc$v ON (whups_tickets.ticket_id = wc$v.ticket_id)";
828: $text = "{$func}wc$v.user_id_creator{$funcend} $op $qvalue";
829: $done = true;
830: break;
831:
832: case Whups_Query::CRITERION_COMMENT:
833: if (!isset($this->jtables['whups_comments'])) {
834: $this->jtables['whups_comments'] = 1;
835: }
836: $v = $this->jtables['whups_comments']++;
837:
838: $this->joins[] = "LEFT JOIN whups_comments wc$v ON (whups_tickets.ticket_id = wc$v.ticket_id)";
839: $text = "{$func}wc$v.comment_text{$funcend} $op $qvalue";
840: $done = true;
841: break;
842: }
843:
844: if ($done == false) {
845: $text .= " $op $qvalue";
846: }
847:
848: return $text;
849: }
850:
851: 852: 853: 854: 855: 856: 857: 858: 859: 860:
861: public function getTicketsByProperties(array $info, $munge = true,
862: $perowner = false)
863: {
864: if (isset($info['queue']) && !count($info['queue'])) {
865: return array();
866: }
867:
868:
869: $where = $this->_generateWhere(
870: 'whups_tickets',
871: array('ticket_id', 'type_id', 'state_id', 'priority_id', 'queue_id'),
872: $info, 'integer');
873:
874: $where2 = $this->_generateWhere(
875: 'whups_tickets', array('user_id_requester'), $info, 'string');
876:
877: if (empty($where)) {
878: $where = $where2;
879: } elseif (!empty($where2)) {
880: $where .= ' AND ' . $where2;
881: }
882:
883:
884: if (!empty($info['summary'])) {
885: $where = $this->_addWhere(
886: $where, 1,
887: 'LOWER(whups_tickets.ticket_summary) LIKE '
888: . $this->_db->quotestring('%' . Horde_String::lower($info['summary']) . '%'));
889: }
890:
891:
892: if (!empty($info['ticket_timestamp'])) {
893: $where = $this->_addDateWhere($where, $info['ticket_timestamp'], 'ticket_timestamp');
894: }
895: if (!empty($info['date_updated'])) {
896: $where = $this->_addDateWhere($where, $info['date_updated'], 'date_updated');
897: }
898: if (!empty($info['date_assigned'])) {
899: $where = $this->_addDateWhere($where, $info['date_assigned'], 'date_assigned');
900: }
901: if (!empty($info['date_resolved'])) {
902: $where = $this->_addDateWhere($where, $info['date_resolved'], 'date_resolved');
903: }
904: if (!empty($info['ticket_due'])) {
905: $where = $this->_addDateWhere($where, $info['ticket_due'], 'ticket_due');
906: }
907:
908: $fields = array(
909: 'ticket_id AS id',
910: 'ticket_summary AS summary',
911: 'user_id_requester',
912: 'state_id AS state',
913: 'type_id AS type',
914: 'priority_id AS priority',
915: 'queue_id AS queue',
916: 'date_updated',
917: 'date_assigned',
918: 'date_resolved',
919: 'version_id AS version');
920:
921: $fields = $this->_prefixTableToColumns('whups_tickets', $fields)
922: . ', whups_tickets.ticket_timestamp AS timestamp, whups_tickets.ticket_due AS due';
923: $tables = 'whups_tickets';
924: $join = '';
925: $groupby = 'whups_tickets.ticket_id, whups_tickets.ticket_summary, whups_tickets.user_id_requester, whups_tickets.state_id, whups_tickets.type_id, whups_tickets.priority_id, whups_tickets.queue_id, whups_tickets.ticket_timestamp, whups_tickets.ticket_due, whups_tickets.date_updated, whups_tickets.date_assigned, whups_tickets.date_resolved';
926:
927:
928: if (isset($info['category'])) {
929: if (is_array($info['category'])) {
930: $cat = '';
931: foreach ($info['category'] as $category) {
932: if (!empty($cat)) {
933: $cat .= ' OR ';
934: }
935: $cat .= 'whups_states.state_category = '
936: . $this->_db->quotestring($category);
937: }
938: $cat = ' AND (' . $cat . ')';
939: } else {
940: $cat = isset($info['category'])
941: ? ' AND whups_states.state_category = '
942: . $this->_db->quotestring($info['category'])
943: : '';
944: }
945: } else {
946: $cat = '';
947: }
948:
949:
950: if (isset($info['type_id'])) {
951: if (is_array($info['type_id'])) {
952: $t = array();
953: foreach ($info['type_id'] as $type) {
954: $t[] = 'whups_tickets.type_id = '
955: . $this->_db->quotestring($type);
956: }
957: $t = ' AND (' . implode(' OR ', $t) . ')';
958: } else {
959: $t = isset($info['type_id'])
960: ? ' AND whups_tickets.type_id = '
961: . $this->_db->quotestring($info['type_id'])
962: : '';
963: }
964:
965: $this->_addWhere($where, $t, $t);
966: }
967:
968: $nouc = isset($info['nouc'])
969: ? " AND whups_states.state_category <> 'unconfirmed'" : '';
970: $nores = isset($info['nores'])
971: ? " AND whups_states.state_category <> 'resolved'" : '';
972: $nonew = isset($info['nonew'])
973: ? " AND whups_states.state_category <> 'new'" : '';
974: $noass = isset($info['noass'])
975: ? " AND whups_states.state_category <> 'assigned'" : '';
976:
977: $uc = isset($info['uc'])
978: ? " AND whups_states.state_category = 'unconfirmed'" : '';
979: $res = isset($info['res'])
980: ? " AND whups_states.state_category = 'resolved'" : '';
981: $new = isset($info['new'])
982: ? " AND whups_states.state_category = 'new'" : '';
983: $ass = isset($info['ass'])
984: ? " AND whups_states.state_category = 'assigned'" : '';
985:
986:
987: if ($nouc || $nores || $nonew || $noass ||
988: $uc || $res || $new || $ass || $cat) {
989: $where = $this->_addWhere($where, 1, "(whups_tickets.type_id = whups_states.type_id AND whups_tickets.state_id = whups_states.state_id$nouc$nores$nonew$noass$uc$res$new$ass$cat)");
990: }
991:
992:
993: $join = '';
994:
995:
996: if (isset($info['owner'])) {
997: $join .= ' INNER JOIN whups_ticket_owners ON whups_tickets.ticket_id = whups_ticket_owners.ticket_id AND ';
998: if (is_array($info['owner'])) {
999: $clauses = array();
1000: foreach ($info['owner'] as $owner) {
1001: $clauses[] = 'whups_ticket_owners.ticket_owner = '
1002: . $this->_db->quotestring($owner);
1003: }
1004: $join .= '(' . implode(' OR ', $clauses) . ')';
1005: } else {
1006: $join .= 'whups_ticket_owners.ticket_owner = '
1007: . $this->_db->quotestring($info['owner']);
1008: }
1009: }
1010: if (isset($info['notowner'])) {
1011: if ($info['notowner'] === true) {
1012:
1013: $join .= ' LEFT JOIN whups_ticket_owners ON whups_tickets.ticket_id = whups_ticket_owners.ticket_id AND whups_ticket_owners.ticket_owner IS NOT NULL';
1014: } else {
1015: $join .= ' LEFT JOIN whups_ticket_owners ON whups_tickets.ticket_id = whups_ticket_owners.ticket_id AND whups_ticket_owners.ticket_owner = ' . $this->_db->quotestring($info['notowner']);
1016: }
1017: $where = $this->_addWhere($where, 1,
1018: 'whups_ticket_owners.ticket_id IS NULL');
1019: }
1020:
1021: if ($munge) {
1022: $myqueues = $GLOBALS['registry']->hasMethod('tickets/listQueues') == $GLOBALS['registry']->getApp();
1023: $myversions = $GLOBALS['registry']->hasMethod('tickets/listVersions') == $GLOBALS['registry']->getApp();
1024: $fields = "$fields, " .
1025: 'whups_types.type_name AS type_name, ' .
1026: 'whups_states.state_name AS state_name, ' .
1027: 'whups_states.state_category AS state_category, ' .
1028: 'whups_priorities.priority_name AS priority_name';
1029:
1030: $join .=
1031: ' INNER JOIN whups_types ON whups_tickets.type_id = whups_types.type_id' .
1032: ' INNER JOIN whups_states ON whups_tickets.state_id = whups_states.state_id' .
1033: ' INNER JOIN whups_priorities ON whups_tickets.priority_id = whups_priorities.priority_id' .
1034: ' INNER JOIN whups_states state2 ON whups_tickets.type_id = state2.type_id';
1035:
1036: $groupby .= ', whups_types.type_name, whups_states.state_name, whups_states.state_category';
1037: if ($myversions) {
1038: $versions = array();
1039: $fields .= ', whups_versions.version_name AS version_name'
1040: . ', whups_versions.version_description AS version_description'
1041: . ', whups_versions.version_active AS version_active';
1042: $join .= ' LEFT JOIN whups_versions ON whups_tickets.version_id = whups_versions.version_id';
1043: $groupby .= ', whups_versions.version_name, whups_versions.version_description, whups_versions.version_active, whups_tickets.version_id';
1044: }
1045: if ($myqueues) {
1046: $queues = array();
1047: $fields .= ', whups_queues.queue_name AS queue_name';
1048: $join .= ' INNER JOIN whups_queues ON whups_tickets.queue_id = whups_queues.queue_id';
1049: $groupby .= ', whups_queues.queue_name';
1050: }
1051: $groupby .= ', whups_priorities.priority_name';
1052: }
1053:
1054: if ($perowner) {
1055: $join .= ' LEFT JOIN whups_ticket_owners ON whups_tickets.ticket_id = whups_ticket_owners.ticket_id';
1056: $fields .= ', whups_ticket_owners.ticket_owner AS owner';
1057: $groupby .= ', whups_ticket_owners.ticket_owner';
1058: }
1059:
1060: $query = "SELECT $fields FROM $tables$join "
1061: . (!empty($where) ? "WHERE $where " : '')
1062: . 'GROUP BY ' . $groupby;
1063:
1064: try {
1065: $info = $this->_db->selectAll($query);
1066: } catch (Horde_Db_Exception $e) {
1067: throw new Whups_Exception($e);
1068: }
1069:
1070: if (!count($info)) {
1071: return array();
1072: }
1073:
1074: $info = $this->_fromBackend($info);
1075:
1076: $tickets = array();
1077: foreach ($info as $ticket) {
1078: if ($munge) {
1079: if (!$myqueues) {
1080: if (!isset($queues[$ticket['queue']])) {
1081: $queues[$ticket['queue']] = $GLOBALS['registry']->call(
1082: 'tickets/getQueueDetails',
1083: array($ticket['queue']));
1084: }
1085: $ticket['queue_name'] = $queues[$ticket['queue']]['name'];
1086: if (isset($queues[$ticket['queue']]['link'])) {
1087: $ticket['queue_link'] = $queues[$ticket['queue']]['link'];
1088: }
1089: }
1090: if (!$myversions) {
1091: if (!isset($versions[$ticket['version']])) {
1092: $versions[$ticket['version']] = $GLOBALS['registry']->call(
1093: 'tickets/getVersionDetails',
1094: array($ticket['version']));
1095: }
1096: $ticket['version_name'] = $versions[$ticket['version']]['name'];
1097: if (isset($versions[$ticket['version']]['link'])) {
1098: $ticket['version_link'] = $versions[$ticket['version']]['link'];
1099: }
1100: }
1101: $ticket['requester_formatted'] = Whups::formatUser($ticket['user_id_requester'], false, true, true);
1102: }
1103: $tickets[$ticket['id']] = $ticket;
1104: }
1105:
1106: $owners = $this->getOwners(array_keys($tickets));
1107: foreach ($owners as $id => $owners) {
1108: $tickets[$id]['owners'] = $owners;
1109: foreach($owners as $owner) {
1110: $tickets[$id]['owners_formatted'][] = Whups::formatUser($owner, false, true, true);
1111: }
1112: }
1113: $attributes = $this->getTicketAttributesWithNames(array_keys($tickets));
1114: foreach ($attributes as $row) {
1115: $attribute_id = 'attribute_' . $row['attribute_id'];
1116: try {
1117: $tickets[$row['id']][$attribute_id] =
1118: Horde_Serialize::unserialize($row['attribute_value'],
1119: Horde_Serialize::JSON);
1120: } catch (Horde_Serialize_Exception $e) {
1121: $tickets[$row['id']][$attribute_id] = $row['attribute_value'];
1122: }
1123: $tickets[$row['id']][$attribute_id . '_name'] = $row['attribute_name'];
1124: }
1125:
1126: return array_values($tickets);
1127: }
1128:
1129: 1130: 1131: 1132: 1133: 1134: 1135: 1136: 1137: 1138:
1139: public function getTicketDetails($ticket, $checkPerms = true)
1140: {
1141: $result = $this->getTicketsByProperties(array('id' => $ticket));
1142:
1143: if (!isset($result[0])) {
1144: throw new Horde_Exception_NotFound(
1145: sprintf(_("Ticket %s was not found."), $ticket));
1146: }
1147:
1148: $queues = Whups::permissionsFilter(
1149: $this->getQueues(), 'queue', Horde_Perms::READ,
1150: $GLOBALS['registry']->getAuth(), $result[0]['user_id_requester']);
1151:
1152: if ($checkPerms &&
1153: !in_array($result[0]['queue'], array_flip($queues))) {
1154: throw new Horde_Exception_PermissionDenied(
1155: sprintf(_("You do not have permission to access this ticket (%s)."), $ticket));
1156: }
1157:
1158: return $result[0];
1159: }
1160:
1161: 1162: 1163: 1164: 1165: 1166: 1167: 1168:
1169: public function getTicketState($ticket_id)
1170: {
1171: try {
1172: return $this->_db->SelectOne(
1173: 'SELECT whups_tickets.state_id, whups_states.state_category '
1174: . 'FROM whups_tickets INNER JOIN whups_states '
1175: . 'ON whups_tickets.state_id = whups_states.state_id '
1176: . 'WHERE ticket_id = ?',
1177: array($ticket_id));
1178: } catch (Horde_Db_Exception $e) {
1179: throw new Whups_Exception($e);
1180: }
1181: }
1182:
1183: 1184: 1185: 1186: 1187: 1188: 1189: 1190:
1191: public function getGuestEmail($guest_id)
1192: {
1193: if (!isset($this->_guestEmailCache[$guest_id])) {
1194: try {
1195: $result = $this->_db->selectValue(
1196: 'SELECT guest_email FROM whups_guests WHERE guest_id = ?',
1197: array($guest_id));
1198: } catch (Horde_Db_Exception $e) {
1199: throw new Whups_Exception($e);
1200: }
1201: $this->_guestEmailCache[$guest_id] = $this->_fromBackend($result);
1202: }
1203:
1204: return $this->_guestEmailCache[$guest_id];
1205: }
1206:
1207:
1208: 1209: 1210: 1211: 1212: 1213: 1214: 1215:
1216: protected function _getHistory($ticket_id)
1217: {
1218: $where = 'whups_logs.ticket_id = ' . (int)$ticket_id;
1219: $join = 'LEFT JOIN whups_comments
1220: ON whups_logs.log_type = \'comment\'
1221: AND whups_logs.log_value_num = whups_comments.comment_id
1222: LEFT JOIN whups_versions
1223: ON whups_logs.log_type = \'version\'
1224: AND whups_logs.log_value_num = whups_versions.version_id
1225: LEFT JOIN whups_states
1226: ON whups_logs.log_type = \'state\'
1227: AND whups_logs.log_value_num = whups_states.state_id
1228: LEFT JOIN whups_priorities
1229: ON whups_logs.log_type = \'priority\'
1230: AND whups_logs.log_value_num = whups_priorities.priority_id
1231: LEFT JOIN whups_types
1232: ON whups_logs.log_type = \'type\'
1233: AND whups_logs.log_value_num = whups_types.type_id
1234: LEFT JOIN whups_attributes_desc
1235: ON whups_logs.log_type = \'attribute\'
1236: AND whups_logs.log_value_num = whups_attributes_desc.attribute_id
1237: LEFT JOIN whups_transactions
1238: ON whups_logs.transaction_id = whups_transactions.transaction_id';
1239:
1240: $fields = $this->_prefixTableToColumns('whups_comments',
1241: array('comment_text'))
1242: . ', whups_transactions.transaction_timestamp AS timestamp, whups_logs.ticket_id'
1243: . ', whups_logs.log_type, whups_logs.log_value'
1244: . ', whups_logs.log_value_num, whups_logs.log_id'
1245: . ', whups_logs.transaction_id, whups_transactions.transaction_user_id user_id'
1246: . ', whups_priorities.priority_name, whups_states.state_name, whups_versions.version_name'
1247: . ', whups_types.type_name, whups_attributes_desc.attribute_name';
1248:
1249: $query = "SELECT $fields FROM whups_logs $join WHERE $where "
1250: . "ORDER BY whups_logs.transaction_id";
1251:
1252: try {
1253: $history = $this->_db->selectAll($query);
1254: } catch (Horde_Db_Exception $e) {
1255: throw new Whups_Exception($e);
1256: }
1257:
1258: $history = $this->_fromBackend($history);
1259: for ($i = 0, $iMax = count($history); $i < $iMax; ++$i) {
1260: if ($history[$i]['log_type'] == 'queue') {
1261: $queue = $this->getQueue($history[$i]['log_value_num']);
1262: $history[$i]['queue_name'] = $queue ? $queue['name'] : null;
1263: }
1264: }
1265:
1266: return $history;
1267: }
1268:
1269: 1270: 1271: 1272: 1273: 1274:
1275: public function deleteHistory($transaction)
1276: {
1277: $transaction = (int)$transaction;
1278: $this->_db->beginDbTransaction();
1279:
1280:
1281: try {
1282: $comments = $this->_db->selectValues(
1283: 'SELECT log_value FROM whups_logs WHERE log_type = ? '
1284: . 'AND transaction_id = ?',
1285: array('comment', $transaction));
1286: } catch (Horde_Db_Exception $e) {
1287: $this->_db->rollbackDbTransaction();
1288: throw new Whups_Exception($e);
1289: }
1290:
1291: if ($comments) {
1292: $query = sprintf(
1293: 'DELETE FROM whups_comments WHERE comment_id IN (%s)',
1294: implode(',', $comments));
1295: try {
1296: $this->_db->delete($query);
1297: } catch (Horde_Db_Exception $e) {
1298: $this->_db->rollbackDbTransaction();
1299: throw new Whups_Exception($e);
1300: }
1301: }
1302:
1303:
1304: if (isset($GLOBALS['conf']['vfs']['type'])) {
1305: try {
1306: $attachments = $this->_db->selectAll(
1307: 'SELECT ticket_id, log_value FROM whups_logs '
1308: . 'WHERE log_type = ? AND transaction_id = ?',
1309: array('attachment', $transaction));
1310: } catch (Horde_Db_Exception $e) {
1311: $this->_db->rollbackDbTransaction();
1312: throw new Whups_Exception($e);
1313: }
1314:
1315: $vfs = $GLOBALS['injector']
1316: ->getInstance('Horde_Core_Factory_Vfs')
1317: ->create();
1318: foreach ($attachments as $attachment) {
1319: $dir = Whups::VFS_ATTACH_PATH . '/' . $attachment['ticket_id'];
1320: if ($vfs->exists($dir, $attachment['log_value'])) {
1321: try {
1322: $vfs->deleteFile($dir, $attachment['log_value']);
1323: } catch (Horde_Vfs_Exception $e) {
1324: $this->_db->rollbackDbTransaction();
1325: throw new Whups_Exception($e);
1326: }
1327: } else {
1328: Horde::logMessage(sprintf(_("Attachment %s not found."),
1329: $attachment['log_value']),
1330: 'WARN');
1331: }
1332: }
1333: }
1334:
1335: try {
1336: $this->_db->delete(
1337: 'DELETE FROM whups_logs WHERE transaction_id = ?',
1338: array($transaction));
1339: $this->_db->delete(
1340: 'DELETE FROM whups_transactions WHERE transaction_id = ?',
1341: array($transaction));
1342: } catch (Horde_Db_Exception $e) {
1343: $this->_db->rollbackDbTransaction();
1344: throw new Whups_Exception($e);
1345: }
1346:
1347: $this->_db->commitDbTransaction();
1348: }
1349:
1350: 1351: 1352: 1353: 1354: 1355: 1356: 1357:
1358: public function getQueueSummary($queue_ids)
1359: {
1360: $qstring = implode(', ', array_map('intval', $queue_ids));
1361:
1362: $sql = 'SELECT q.queue_id AS id, q.queue_slug AS slug, '
1363: . 'q.queue_name AS name, q.queue_description AS description, '
1364: . 'ty.type_name as type, COUNT(t.ticket_id) AS open_tickets '
1365: . 'FROM whups_queues q LEFT JOIN whups_tickets t '
1366: . 'ON q.queue_id = t.queue_id '
1367: . 'INNER JOIN whups_states s '
1368: . 'ON (t.state_id = s.state_id AND s.state_category != \'resolved\') '
1369: . 'INNER JOIN whups_types ty ON ty.type_id = t.type_id '
1370: . 'WHERE q.queue_id IN (' . $qstring . ') '
1371: . 'GROUP BY q.queue_id, q.queue_slug, q.queue_name, '
1372: . 'q.queue_description, ty.type_name ORDER BY q.queue_name';
1373:
1374: try {
1375: $queues = $this->_db->selectAll($sql);
1376: } catch (Horde_Db_Exception $e) {
1377: throw new Whups_Exception($e);
1378: }
1379:
1380: return $this->_fromBackend($queues);
1381: }
1382:
1383: 1384: 1385: 1386: 1387: 1388: 1389: 1390:
1391: public function getQueueInternal($queueId)
1392: {
1393: if (isset($this->_internalQueueCache[$queueId])) {
1394: return $this->_internalQueueCache[$queueId];
1395: }
1396:
1397: try {
1398: $queue = $this->_db->selectOne(
1399: 'SELECT queue_id, queue_name, queue_description, '
1400: . 'queue_versioned, queue_slug, queue_email '
1401: . 'FROM whups_queues WHERE queue_id = ?',
1402: array((int)$queueId));
1403: } catch (Horde_Db_Exception $e) {
1404: throw new Whups_Exception($e);
1405: }
1406:
1407: if (!$queue) {
1408: return array();
1409: }
1410:
1411: $queue = $this->_fromBackend($queue);
1412: $this->_internalQueueCache[$queueId] = array(
1413: 'id' => (int)$queue['queue_id'],
1414: 'name' => $queue['queue_name'],
1415: 'description' => $queue['queue_description'],
1416: 'versioned' => (bool)$queue['queue_versioned'],
1417: 'slug' => $queue['queue_slug'],
1418: 'email' => $queue['queue_email'],
1419: 'readonly' => false);
1420:
1421: return $this->_internalQueueCache[$queueId];
1422: }
1423:
1424:
1425: 1426: 1427: 1428: 1429: 1430: 1431: 1432:
1433: public function getQueueBySlugInternal($slug)
1434: {
1435: try {
1436: $queue = $this->_db->selectOne(
1437: 'SELECT queue_id, queue_name, queue_description, '
1438: . 'queue_versioned, queue_slug FROM whups_queues WHERE '
1439: . 'queue_slug = ?',
1440: array((string)$slug));
1441: } catch (Horde_Db_Exception $e) {
1442: throw new Whups_Exception($e);
1443: }
1444:
1445: if (!$queue) {
1446: return array();
1447: }
1448:
1449: $queue = $this->_fromBackend($queue);
1450: return array(
1451: 'id' => $queue['queue_id'],
1452: 'name' => $queue['queue_name'],
1453: 'description' => $queue['queue_description'],
1454: 'versioned' => $queue['queue_versioned'],
1455: 'slug' => $queue['queue_slug'],
1456: 'readonly' => false);
1457: }
1458:
1459: 1460: 1461: 1462: 1463: 1464:
1465: public function getQueuesInternal()
1466: {
1467: if (is_null($this->_queues)) {
1468: try {
1469: $queues = $this->_db->selectAssoc(
1470: 'SELECT queue_id, queue_name FROM whups_queues '
1471: . 'ORDER BY queue_name');
1472: } catch (Horde_Db_Exception $e) {
1473: throw new Whups_Exception($e);
1474: }
1475: $this->_queues = $this->_fromBackend($queues);
1476: }
1477:
1478: return $this->_queues;
1479: }
1480:
1481: 1482: 1483: 1484: 1485: 1486:
1487: public function getSlugs()
1488: {
1489: if (is_null($this->_slugs)) {
1490: try {
1491: $queues = $this->_db->selectAssoc(
1492: 'SELECT queue_id, queue_slug FROM whups_queues '
1493: . 'WHERE queue_slug IS NOT NULL AND queue_slug <> \'\' '
1494: . 'ORDER BY queue_slug');
1495: } catch (Horde_Db_Exception $e) {
1496: throw new Whups_Exception($e);
1497: }
1498: $this->_slugs = $this->_fromBackend($queues);
1499: }
1500:
1501: return $this->_slugs;
1502: }
1503:
1504: 1505: 1506: 1507: 1508: 1509: 1510: 1511: 1512: 1513: 1514: 1515: 1516: 1517: 1518:
1519: public function updateQueue($queueId, $name, $description,
1520: array $types = array(), $versioned = 0,
1521: $slug = '', $email = '', $default = null)
1522: {
1523: global $registry;
1524:
1525: if ($registry->hasMethod('tickets/listQueues') == $registry->getApp()) {
1526:
1527: if (!empty($slug)) {
1528: if ($this->_db->selectValue('SELECT 1 FROM whups_queues WHERE queue_slug = ? AND queue_id <> ?', array($slug, $queueId))) {
1529: throw new Whups_Exception(
1530: _("That queue slug is already taken. Please select another."));
1531: }
1532: }
1533:
1534:
1535: try {
1536: $this->_db->update(
1537: 'UPDATE whups_queues SET queue_name = ?, '
1538: . 'queue_description = ?, queue_versioned = ?, '
1539: . 'queue_slug = ?, queue_email = ? WHERE queue_id = ?',
1540: array($this->_toBackend($name),
1541: $this->_toBackend($description),
1542: empty($versioned) ? 0 : 1,
1543: $slug,
1544: $email,
1545: (int)$queueId));
1546: } catch (Horde_Db_Exception $e) {
1547: throw new Whups_Exception($e);
1548: }
1549: }
1550:
1551:
1552: try {
1553: $this->_db->delete(
1554: 'DELETE FROM whups_types_queues WHERE queue_id = ?',
1555: array((int)$queueId));
1556: } catch (Horde_Db_Exception $e) {
1557: throw new Whups_Exception($e);
1558: }
1559:
1560:
1561: if (is_array($types)) {
1562: foreach ($types as $typeId) {
1563: try {
1564: $this->_db->insert(
1565: 'INSERT INTO whups_types_queues '
1566: . '(queue_id, type_id, type_default) VALUES (?, ?, ?)',
1567: array((int)$queueId,
1568: (int)$typeId,
1569: $default == $typeId ? 1 : 0));
1570: } catch (Horde_Db_Exception $e) {
1571: throw new Whups_Exception($e);
1572: }
1573: }
1574: }
1575: }
1576:
1577: 1578: 1579: 1580: 1581: 1582: 1583:
1584: public function getDefaultType($queue)
1585: {
1586: try {
1587: return $this->_db->selectValue(
1588: 'SELECT type_id FROM whups_types_queues '
1589: . 'WHERE type_default = 1 AND queue_id = ?',
1590: array($queue));
1591: } catch (Horde_Db_Exception $e) {
1592: throw new Whups_Exception($e);
1593: }
1594: }
1595:
1596: 1597: 1598: 1599: 1600: 1601: 1602:
1603: public function deleteQueue($queueId)
1604: {
1605:
1606: try {
1607: $result = $this->_db->selectAll(
1608: 'SELECT ticket_id FROM whups_tickets WHERE queue_id = ?',
1609: array((int)$queueId));
1610: } catch (Horde_Db_Exception $e) {
1611: throw new Whups_Exception($e);
1612: }
1613: foreach ($result as $ticket) {
1614: $this->deleteTicket($ticket['ticket_id']);
1615: }
1616:
1617:
1618: $tables = array(
1619: 'whups_queues_users',
1620: 'whups_types_queues',
1621: 'whups_versions',
1622: 'whups_queues');
1623: $this->_db->beginDbTransaction();
1624: foreach ($tables as $table) {
1625: try {
1626: $this->_db->delete(
1627: 'DELETE FROM ' . $table . ' WHERE queue_id = ?',
1628: array((int)$queueId));
1629: } catch (Horde_Db_Exception $e) {
1630: $this->_db->rollbackDbTransaction();
1631: throw new Whups_Exception($e);
1632: }
1633: }
1634: $this->_db->commitDbTransaction();
1635:
1636: return parent::deleteQueue($queueId);
1637: }
1638:
1639: 1640: 1641: 1642: 1643: 1644: 1645:
1646: public function updateTypesQueues(array $tmPairs)
1647: {
1648:
1649: $this->_db->beginDbTransaction();
1650:
1651:
1652: try {
1653: $this->_db->delete('DELETE FROM whups_types_queues');
1654: } catch (Horde_Db_Exception $e) {
1655: $this->_db->rollbackDbTransaction();
1656: throw new Whups_Exception($e);
1657: }
1658:
1659:
1660: foreach ($tmPairs as $pair) {
1661: try {
1662: $this->_db->insert(
1663: 'INSERT INTO whups_types_queues (queue_id, type_id) '
1664: . 'VALUES (?, ?)',
1665: array((int)$pair[0], (int)$pair[1]));
1666: } catch (Horde_Db_Exception $e) {
1667: $this->_db->rollbackDbTransaction();
1668: throw new Whups_Exception($e);
1669: }
1670: }
1671:
1672: try {
1673: $this->_db->commitDbTransaction();
1674: } catch (Horde_Db_Exception $e) {
1675: $this->_db->rollbackDbTransaction();
1676: throw new Whups_Exception($e);
1677: }
1678: }
1679:
1680: 1681: 1682: 1683: 1684: 1685: 1686: 1687:
1688: public function getQueueUsers($queueId)
1689: {
1690: try {
1691: return $this->_db->selectValues(
1692: 'SELECT user_uid FROM whups_queues_users'
1693: . ' WHERE queue_id = ? ORDER BY user_uid',
1694: array((int)$queueId));
1695: } catch (Horde_Db_Exception $e) {
1696: throw new Whups_Exception($e);
1697: }
1698: }
1699:
1700: 1701: 1702: 1703: 1704: 1705: 1706: 1707:
1708: public function addQueueUser($queueId, $userId)
1709: {
1710: if (!is_array($userId)) {
1711: $userId = array($userId);
1712: }
1713: foreach ($userId as $user) {
1714: try {
1715: $this->_db->insert(
1716: 'INSERT INTO whups_queues_users (queue_id, user_uid) '
1717: . 'VALUES (?, ?)',
1718: array((int)$queueId, $user));
1719: } catch (Horde_Db_Exception $e) {
1720: throw new Whups_Exception($e);
1721: }
1722: }
1723: }
1724:
1725: 1726: 1727: 1728: 1729: 1730: 1731: 1732:
1733: public function removeQueueUser($queueId, $userId)
1734: {
1735: try {
1736: $this->_db->delete(
1737: 'DELETE FROM whups_queues_users'
1738: . ' WHERE queue_id = ? AND user_uid = ?',
1739: array((int)$queueId, $userId));
1740: } catch (Horde_Db_Exception $e) {
1741: throw new Whups_Exception($e);
1742: }
1743: }
1744:
1745: 1746: 1747: 1748: 1749: 1750: 1751: 1752:
1753: public function getType($typeId)
1754: {
1755: try {
1756: $type = $this->_db->selectOne(
1757: 'SELECT type_id, type_name, type_description '
1758: . 'FROM whups_types WHERE type_id = ?',
1759: array((int)$typeId));
1760: } catch (Horde_Db_Exception $e) {
1761: throw new Whups_Exception($e);
1762: }
1763:
1764: $type = $this->_fromBackend($type);
1765:
1766: return array('id' => $typeId,
1767: 'name' => $type['type_name'],
1768: 'description' => $type['type_description']);
1769: }
1770:
1771: 1772: 1773: 1774: 1775: 1776: 1777: 1778:
1779: public function getTypes($queueId)
1780: {
1781: try {
1782: $types = $this->_db->selectAssoc(
1783: 'SELECT t.type_id, t.type_name '
1784: . 'FROM whups_types t, whups_types_queues tm '
1785: . 'WHERE tm.queue_id = ? AND tm.type_id = t.type_id '
1786: . 'ORDER BY t.type_name',
1787: array((int)$queueId));
1788: } catch (Horde_Db_Exception $e) {
1789: throw new Whups_Exception($e);
1790: }
1791:
1792: return $this->_fromBackend($types);
1793: }
1794:
1795: 1796: 1797: 1798: 1799: 1800: 1801: 1802:
1803: public function getTypeIds($queueId)
1804: {
1805: try {
1806: return $this->_db->selectValues(
1807: 'SELECT type_id FROM whups_types_queues '
1808: . 'WHERE queue_id = ? ORDER BY type_id',
1809: array((int)$queueId));
1810: } catch (Horde_Db_Exception $e) {
1811: throw new Whups_Exception($e);
1812: }
1813: }
1814:
1815: 1816: 1817: 1818: 1819: 1820:
1821: public function getAllTypes()
1822: {
1823: try {
1824: $types = $this->_db->selectAssoc(
1825: 'SELECT type_id, type_name FROM whups_types ORDER BY type_name');
1826: } catch (Horde_Db_Exception $e) {
1827: throw new Whups_Exception($e);
1828: }
1829:
1830: return $this->_fromBackend($types);
1831: }
1832:
1833: 1834: 1835: 1836: 1837: 1838:
1839: public function getAllTypeInfo()
1840: {
1841: try {
1842: $info = $this->_db->selectAll(
1843: 'SELECT type_id, type_name, type_description '
1844: . 'FROM whups_types ORDER BY type_id');
1845: } catch (Horde_Db_Exception $e) {
1846: throw new Whups_Exception($e);
1847: }
1848:
1849: return $this->_fromBackend($info);
1850: }
1851:
1852: 1853: 1854: 1855: 1856: 1857: 1858: 1859:
1860: public function getTypeName($type)
1861: {
1862: try {
1863: $name = $this->_db->selectValue(
1864: 'SELECT type_name FROM whups_types WHERE type_id = ?',
1865: array((int)$type));
1866: } catch (Horde_Db_Exception $e) {
1867: throw new Whups_Exception($e);
1868: }
1869:
1870: return $this->_fromBackend($name);
1871: }
1872:
1873: 1874: 1875: 1876: 1877: 1878: 1879: 1880: 1881:
1882: public function updateType($typeId, $name, $description)
1883: {
1884: try {
1885: $this->_db->update(
1886: 'UPDATE whups_types SET type_name = ?, type_description = ? '
1887: . 'WHERE type_id = ?',
1888: array($this->_toBackend($name),
1889: $this->_toBackend($description),
1890: (int)$typeId));
1891: } catch (Horde_Db_Exception $e) {
1892: throw new Whups_Exception($e);
1893: }
1894: }
1895:
1896: 1897: 1898: 1899: 1900: 1901: 1902:
1903: public function deleteType($typeId)
1904: {
1905: $this->_db->beginDbTransaction();
1906: $values = array((int)$typeId);
1907: try {
1908: $this->_db->delete(
1909: 'DELETE FROM whups_states WHERE type_id = ?',
1910: $values);
1911:
1912: $this->_db->delete(
1913: 'DELETE FROM whups_priorities WHERE type_id = ?',
1914: $values);
1915:
1916: $this->_db->delete(
1917: 'DELETE FROM whups_attributes_desc WHERE type_id = ?',
1918: $values);
1919:
1920: $this->_db->delete(
1921: 'DELETE FROM whups_types WHERE type_id = ?',
1922: $values);
1923: $this->_db->commitDbTransaction();
1924: } catch (Horde_Db_Exception $e) {
1925: $this->_db->rollbackDbTransaction();
1926: throw new Whups_Exception($e);
1927: }
1928: }
1929:
1930: 1931: 1932: 1933: 1934: 1935: 1936: 1937: 1938: 1939:
1940: public function getStates($type = null, $category = '', $notcategory = '')
1941: {
1942: $fields = 'state_id, state_name';
1943: $from = 'whups_states';
1944: $order = 'state_category, state_name';
1945: if (empty($type)) {
1946: $fields .= ', whups_types.type_id, type_name';
1947: $from .= ' LEFT JOIN whups_types ON whups_states.type_id = whups_types.type_id';
1948: $where = '';
1949: $order = 'type_name, ' . $order;
1950: } else {
1951: $where = 'type_id = ' . $type;
1952: }
1953:
1954: if (!is_array($category)) {
1955: $where = $this->_addWhere($where, $category, 'state_category = ' . $this->_db->quoteString($category));
1956: } else {
1957: $clauses = array();
1958: foreach ($category as $cat) {
1959: $clauses[] = 'state_category = ' . $this->_db->quoteString($cat);
1960: }
1961: if (count($clauses))
1962: $where = $this->_addWhere($where, $cat, implode(' OR ', $clauses));
1963: }
1964:
1965: if (!is_array($notcategory)) {
1966: $where = $this->_addWhere($where, $notcategory, 'state_category <> ' . $this->_db->quoteString($notcategory));
1967: } else {
1968: $clauses = array();
1969: foreach ($notcategory as $notcat) {
1970: $clauses[] = 'state_category <> ' . $this->_db->quoteString($notcat);
1971: }
1972: if (count($clauses)) {
1973: $where = $this->_addWhere($where, $notcat, implode(' OR ', $clauses));
1974: }
1975: }
1976: if (!empty($where)) {
1977: $where = ' WHERE ' . $where;
1978: }
1979:
1980: $query = "SELECT $fields FROM $from$where ORDER BY $order";
1981: try {
1982: $states = $this->_db->selectAll($query);
1983: } catch (Horde_Db_Exception $e) {
1984: throw new Whups_Exception($e);
1985: }
1986:
1987: $return = array();
1988: if (empty($type)) {
1989: foreach ($states as $state) {
1990: $return[$state['state_id']] = $state['state_name'] . ' (' . $state['type_name'] . ')';
1991: }
1992: } else {
1993: foreach ($states as $state) {
1994: $return[$state['state_id']] = $state['state_name'];
1995: }
1996: }
1997:
1998: return $this->_fromBackend($return);
1999: }
2000:
2001: 2002: 2003: 2004: 2005: 2006: 2007:
2008: public function getState($stateId)
2009: {
2010: try {
2011: $state = $this->_db->selectOne(
2012: 'SELECT state_name, state_description, state_category, '
2013: . 'type_id FROM whups_states WHERE state_id = ?',
2014: array((int)$stateId));
2015: } catch (Horde_Db_Exception $e) {
2016: throw new Whups_Exception($e);
2017: }
2018:
2019: $state = $this->_fromBackend($state);
2020:
2021: return array('id' => $stateId,
2022: 'name' => $state['state_name'],
2023: 'description' => $state['state_description'],
2024: 'category' => $state['state_category'],
2025: 'type' => $state['type_id']);
2026: }
2027:
2028: 2029: 2030: 2031: 2032: 2033: 2034: 2035:
2036: public function getAllStateInfo($type)
2037: {
2038: try {
2039: $info = $this->_db->selectAll(
2040: 'SELECT state_id, state_name, state_description, '
2041: . 'state_category FROM whups_states WHERE type_id = ? '
2042: . 'ORDER BY state_id',
2043: array((int)$type));
2044: } catch (Horde_Db_Exception $e) {
2045: throw new Whups_Exception($e);
2046: }
2047:
2048: return $this->_fromBackend($info);
2049: }
2050:
2051: 2052: 2053: 2054: 2055: 2056: 2057: 2058: 2059: 2060:
2061: public function updateState($stateId, $name, $description, $category)
2062: {
2063: try {
2064: $this->_db->update(
2065: 'UPDATE whups_states SET state_name = ?, state_description = ?, '
2066: . 'state_category = ? WHERE state_id = ?',
2067: array($this->_toBackend($name),
2068: $this->_toBackend($description),
2069: $this->_toBackend($category),
2070: (int)$stateId));
2071: } catch (Horde_Db_Exception $e) {
2072: throw new Whups_Exception($e);
2073: }
2074: }
2075:
2076: 2077: 2078: 2079: 2080: 2081: 2082: 2083:
2084: public function getDefaultState($type)
2085: {
2086: try {
2087: return $this->_db->selectValue(
2088: 'SELECT state_id FROM whups_states WHERE state_default = 1 '
2089: . 'AND type_id = ?',
2090: array($type));
2091: } catch (Horde_Db_Exception $e) {
2092: throw new Whups_Exception($e);
2093: }
2094: }
2095:
2096: 2097: 2098: 2099: 2100: 2101: 2102: 2103:
2104: public function setDefaultState($type, $state)
2105: {
2106: $this->_db->beginDbTransaction();
2107: try {
2108: $this->_db->update(
2109: 'UPDATE whups_states SET state_default = 0 WHERE type_id = ?',
2110: array((int)$type));
2111: $this->_db->update(
2112: 'UPDATE whups_states SET state_default = 1 WHERE state_id = ?',
2113: array((int)$state));
2114: $this->_db->commitDbTransaction();
2115: } catch (Horde_Db_Exception $e) {
2116: $this->_db->rollbackDbTransaction();
2117: throw new Whups_Exception($e);
2118: }
2119: }
2120:
2121: 2122: 2123: 2124: 2125: 2126: 2127:
2128: public function deleteState($state_id)
2129: {
2130: try {
2131: $this->_db->delete('DELETE FROM whups_states WHERE state_id = ?',
2132: array((int)$state_id));
2133: } catch (Horde_Db_Exception $e) {
2134: throw new Whups_Exception($e);
2135: }
2136: }
2137:
2138: 2139: 2140: 2141: 2142: 2143: 2144: 2145:
2146: public function getQuery($queryId)
2147: {
2148: try {
2149: $query = $this->_db->selectOne(
2150: 'SELECT query_parameters, query_object FROM whups_queries '
2151: . 'WHERE query_id = ?',
2152: array((int)$queryId));
2153: } catch (Horde_Db_Exception $e) {
2154: throw new Whups_Exception($e);
2155: }
2156:
2157: return $this->_fromBackend($query);
2158: }
2159:
2160: 2161: 2162: 2163: 2164: 2165: 2166: 2167: 2168:
2169: public function saveQuery($query)
2170: {
2171: try {
2172: $exists = $this->_db->selectValue(
2173: 'SELECT 1 FROM whups_queries WHERE query_id = ?',
2174: array((int)$query->id));
2175: } catch (Horde_Db_Exception $e) {
2176: throw new Whups_Exception($e);
2177: }
2178:
2179: $values = $this->_toBackend(array(serialize($query->parameters),
2180: serialize($query->query),
2181: $query->id));
2182:
2183: try {
2184: if ($exists) {
2185: $this->_db->update(
2186: 'UPDATE whups_queries SET query_parameters = ?, '
2187: . 'query_object = ? WHERE query_id = ?',
2188: $values);
2189: } else {
2190: $this->_db->insert(
2191: 'INSERT INTO whups_queries (query_parameters, '
2192: . 'query_object, query_id) VALUES (?, ?, ?)',
2193: $values);
2194: }
2195: } catch (Horde_Db_Exception $e) {
2196: throw new Whups_Exception($e);
2197: }
2198: }
2199:
2200: 2201: 2202: 2203: 2204: 2205: 2206:
2207: public function deleteQuery($queryId)
2208: {
2209: try {
2210: $this->_db->delete('DELETE FROM whups_queries WHERE query_id = ?',
2211: array((int)$queryId));
2212: } catch (Horde_Db_Exception $e) {
2213: throw new Whups_Exception($e);
2214: }
2215: }
2216:
2217: 2218: 2219: 2220: 2221: 2222: 2223: 2224: 2225:
2226: public function isCategory($category, $state_id)
2227: {
2228: try {
2229: return (bool)$this->_db->selectValue(
2230: 'SELECT 1 FROM whups_states '
2231: . 'WHERE state_id = ? AND state_category = ?',
2232: array((int)$state_id, $category));
2233: } catch (Horde_Db_Exception $e) {
2234: throw new Whups_Exception($e);
2235: }
2236: }
2237:
2238: 2239: 2240: 2241: 2242: 2243: 2244: 2245:
2246: public function getAllPriorityInfo($type)
2247: {
2248: try {
2249: $info = $this->_db->selectAll(
2250: 'SELECT priority_id, priority_name, priority_description '
2251: . 'FROM whups_priorities WHERE type_id = ? '
2252: . 'ORDER BY priority_id',
2253: array((int)$type));
2254: } catch (Horde_Db_Exception $e) {
2255: throw new Whups_Exception($e);
2256: }
2257:
2258: return $this->_fromBackend($info);
2259: }
2260:
2261: 2262: 2263: 2264: 2265: 2266: 2267: 2268: 2269: 2270: 2271:
2272: public function getPriorities($type = null)
2273: {
2274: $fields = 'priority_id, priority_name';
2275: $from = 'whups_priorities';
2276: $order = 'priority_name';
2277: if (empty($type)) {
2278: $fields .= ', whups_types.type_id, type_name';
2279: $from .= ' LEFT JOIN whups_types ON whups_priorities.type_id = whups_types.type_id';
2280: $where = '';
2281: $order = 'type_name, ' . $order;
2282: } else {
2283: $where = ' WHERE type_id = ' . $type;
2284: }
2285:
2286: $query = "SELECT $fields FROM $from$where ORDER BY $order";
2287: try {
2288: $priorities = $this->_db->selectAll($query);
2289: } catch (Horde_Db_Exception $e) {
2290: throw new Whups_Exception($e);
2291: }
2292:
2293: $return = array();
2294: if (empty($type)) {
2295: foreach ($priorities as $priority) {
2296: $return[$priority['priority_id']] = $priority['priority_name'] . ' (' . $priority['type_name'] . ')';
2297: }
2298: } else {
2299: foreach ($priorities as $priority) {
2300: $return[$priority['priority_id']] = $priority['priority_name'];
2301: }
2302: }
2303:
2304: return $this->_fromBackend($return);
2305: }
2306:
2307: 2308: 2309: 2310: 2311: 2312: 2313: 2314:
2315: public function getPriority($priorityId)
2316: {
2317: try {
2318: $priority = $this->_db->selectOne(
2319: 'SELECT priority_name, priority_description, type_id '
2320: . 'FROM whups_priorities WHERE priority_id = ?',
2321: array((int)$priorityId));
2322: } catch (Horde_Db_Exception $e) {
2323: throw new Whups_Exception($e);
2324: }
2325:
2326: $priority = $this->_fromBackend($priority);
2327:
2328: return array('id' => $priorityId,
2329: 'name' => $priority['priority_name'],
2330: 'description' => $priority['priority_description'],
2331: 'type' => $priority['type_id']);
2332: }
2333:
2334: 2335: 2336: 2337: 2338: 2339: 2340: 2341: 2342:
2343: public function updatePriority($priorityId, $name, $description)
2344: {
2345: try {
2346: $this->_db->update(
2347: 'UPDATE whups_priorities SET priority_name = ?, '
2348: . 'priority_description = ? WHERE priority_id = ?',
2349: array($this->_toBackend($name),
2350: $this->_toBackend($description),
2351: (int)$priorityId));
2352: } catch (Horde_Db_Exception $e) {
2353: throw new Whups_Exception($e);
2354: }
2355: }
2356:
2357: 2358: 2359: 2360: 2361: 2362: 2363: 2364:
2365: public function getDefaultPriority($type)
2366: {
2367: try {
2368: return $this->_db->selectValue(
2369: 'SELECT priority_id FROM whups_priorities '
2370: . 'WHERE priority_default = 1 AND type_id = ?',
2371: array((int)$type));
2372: } catch (Horde_Db_Exception $e) {
2373: throw new Whups_Exception($e);
2374: }
2375: }
2376:
2377: 2378: 2379: 2380: 2381: 2382: 2383: 2384:
2385: public function setDefaultPriority($type, $priority)
2386: {
2387: $this->_db->beginDbTransaction();
2388: try {
2389: $this->_db->update(
2390: 'UPDATE whups_priorities SET priority_default = 0 '
2391: . 'WHERE type_id = ?',
2392: array((int)$type));
2393: $this->_db->update(
2394: 'UPDATE whups_priorities SET priority_default = 1 '
2395: . 'WHERE priority_id = ?',
2396: array((int)$priority));
2397: $this->_db->commitDbTransaction();
2398: } catch (Horde_Db_Exception $e) {
2399: $this->_db->rollbackDbTransaction();
2400: throw new Whups_Exception($e);
2401: }
2402: }
2403:
2404: 2405: 2406: 2407: 2408: 2409: 2410:
2411: public function deletePriority($priorityId)
2412: {
2413: try {
2414: $this->_db->delete(
2415: 'DELETE FROM whups_priorities WHERE priority_id = ?',
2416: array((int)$priorityId));
2417: } catch (Horde_Db_Exception $e) {
2418: throw new Whups_Exception($e);
2419: }
2420: }
2421:
2422: 2423: 2424: 2425: 2426: 2427: 2428: 2429:
2430: public function getVersionInfoInternal($queue)
2431: {
2432: try {
2433: $info = $this->_db->selectAll(
2434: 'SELECT version_id, version_name, version_description, '
2435: . 'version_active FROM whups_versions WHERE queue_id = ?'
2436: . ' ORDER BY version_id',
2437: array((int)$queue));
2438: } catch (Horde_Db_Exception $e) {
2439: throw new Whups_Exception($e);
2440: }
2441:
2442: return $this->_fromBackend($info);
2443: }
2444:
2445: 2446: 2447: 2448: 2449: 2450: 2451: 2452:
2453: public function getVersionInternal($versionId)
2454: {
2455: try {
2456: $version = $this->_db->selectOne(
2457: 'SELECT version_name, version_description, version_active '
2458: . 'FROM whups_versions WHERE version_id = ?',
2459: array((int)$versionId));
2460: } catch (Horde_Db_Exception $e) {
2461: throw new Whups_Exception($e);
2462: }
2463:
2464: $version = $this->_fromBackend($version);
2465:
2466: return array('id' => $versionId,
2467: 'name' => $version['version_name'],
2468: 'description' => $version['version_description'],
2469: 'active' => !empty($version['version_active']));
2470: }
2471:
2472: 2473: 2474: 2475: 2476: 2477: 2478: 2479: 2480: 2481:
2482: public function updateVersion($versionId, $name, $description, $active)
2483: {
2484: try {
2485: $this->_db->update(
2486: 'UPDATE whups_versions SET version_name = ?, '
2487: . 'version_description = ?, version_active = ? '
2488: . 'WHERE version_id = ?',
2489: array($this->_toBackend($name),
2490: $this->_toBackend($description),
2491: (int)$active,
2492: (int)$versionId));
2493: } catch (Horde_Db_Exception $e) {
2494: throw new Whups_Exception($e);
2495: }
2496: }
2497:
2498: 2499: 2500: 2501: 2502: 2503: 2504:
2505: public function deleteVersion($versionId)
2506: {
2507: try {
2508: $this->_db->delete(
2509: 'DELETE FROM whups_versions WHERE version_id = ?',
2510: array((int)$versionId));
2511: } catch (Horde_Db_Exception $e) {
2512: throw new Whups_Exception($e);
2513: }
2514: }
2515:
2516: 2517: 2518: 2519: 2520: 2521: 2522: 2523:
2524: public function getReplies($type)
2525: {
2526: try {
2527: $rows = $this->_db->selectAll(
2528: 'SELECT reply_id, reply_name, reply_text '
2529: . 'FROM whups_replies WHERE type_id = ? ORDER BY reply_name',
2530: array((int)$type));
2531: } catch (Horde_Db_Exception $e) {
2532: throw new Whups_Exception($e);
2533: }
2534:
2535: $info = array();
2536: foreach ($rows as $row) {
2537: $info[$row['reply_id']] = $this->_fromBackend($row);
2538: }
2539:
2540: return $info;
2541: }
2542:
2543: 2544: 2545: 2546: 2547: 2548: 2549: 2550:
2551: public function getReply($reply_id)
2552: {
2553: try {
2554: $reply = $this->_db->selectOne(
2555: 'SELECT reply_name, reply_text, type_id '
2556: . 'FROM whups_replies WHERE reply_id = ?',
2557: array((int)$reply_id));
2558: } catch (Horde_Db_Exception $e) {
2559: throw new Whups_Exception($e);
2560: }
2561:
2562: return $this->_fromBackend($reply);
2563: }
2564:
2565: 2566: 2567: 2568: 2569: 2570: 2571: 2572: 2573:
2574: public function updateReply($reply, $name, $text)
2575: {
2576: try {
2577: $this->_db->update(
2578: 'UPDATE whups_replies SET reply_name = ?, reply_text = ? '
2579: . 'WHERE reply_id = ?',
2580: array($this->_toBackend($name),
2581: $this->_toBackend($text),
2582: (int)$reply));
2583: } catch (Horde_Db_Exception $e) {
2584: throw new Whups_Exception($e);
2585: }
2586: }
2587:
2588: 2589: 2590: 2591: 2592: 2593: 2594:
2595: public function deleteReply($reply)
2596: {
2597: try {
2598: $this->_db->delete('DELETE FROM whups_replies WHERE reply_id = ?',
2599: array((int)$reply));
2600: } catch (Horde_Db_Exception $e) {
2601: throw new Whups_Exception($e);
2602: }
2603:
2604: parent::deleteReply($reply);
2605: }
2606:
2607: 2608: 2609: 2610: 2611: 2612: 2613: 2614:
2615: public function addListener($ticket, $user)
2616: {
2617: try {
2618: $this->_db->insert(
2619: 'INSERT INTO whups_ticket_listeners (ticket_id, user_uid)'
2620: . ' VALUES (?, ?)',
2621: array((int)$ticket, $user));
2622: } catch (Horde_Db_Exception $e) {
2623: throw new Whups_Exception($e);
2624: }
2625: }
2626:
2627: 2628: 2629: 2630: 2631: 2632: 2633: 2634:
2635: public function deleteListener($ticket, $user)
2636: {
2637: try {
2638: $this->_db->delete(
2639: 'DELETE FROM whups_ticket_listeners WHERE ticket_id = ?'
2640: . ' AND user_uid = ?',
2641: array((int)$ticket, $user));
2642: } catch (Horde_Db_Exception $e) {
2643: throw new Whups_Exception($e);
2644: }
2645: }
2646:
2647: 2648: 2649: 2650: 2651: 2652: 2653: 2654: 2655: 2656: 2657: 2658:
2659: public function getListeners($ticket, $withowners = true,
2660: $withrequester = true,
2661: $withresponsible = false)
2662: {
2663: try {
2664: $listeners = $this->_db->selectValues(
2665: 'SELECT DISTINCT l.user_uid FROM whups_ticket_listeners l, '
2666: . 'whups_tickets t WHERE (l.ticket_id = ?)',
2667: array((int)$ticket));
2668: } catch (Horde_Db_Exception $e) {
2669: throw new Whups_Exception($e);
2670: }
2671: $users = array();
2672: foreach ($listeners as $user) {
2673: $users[$user] = 'listener';
2674: }
2675:
2676: $tinfo = $this->getTicketDetails($ticket);
2677: if ($withresponsible) {
2678: foreach ($this->getQueueUsers($tinfo['queue']) as $user) {
2679: $users[$user] = 'queue';
2680: }
2681: }
2682:
2683:
2684: $requester = $tinfo['user_id_requester'];
2685: $owner_is_requester = false;
2686: if (isset($tinfo['owners'])) {
2687: foreach ($tinfo['owners'] as $owner) {
2688: $owner = str_replace('user:', '', $owner);
2689: if ($owner == $requester) {
2690: $owner_is_requester = true;
2691: }
2692: if ($withowners) {
2693: $users[$owner] = 'owner';
2694: } else {
2695: if (isset($users[$owner])) {
2696: unset($users[$owner]);
2697: }
2698: }
2699: }
2700: }
2701:
2702: if (!$withrequester) {
2703: if (isset($users[$requester]) &&
2704: (!$withowners || $owner_is_requester)) {
2705: unset($users[$requester]);
2706: }
2707: } elseif (!empty($requester) && !isset($users[$requester])) {
2708: $users[$requester] = 'requester';
2709: }
2710:
2711: return $users;
2712: }
2713:
2714: 2715: 2716: 2717: 2718: 2719: 2720: 2721: 2722: 2723: 2724: 2725: 2726: 2727: 2728: 2729: 2730:
2731: public function addAttributeDesc($type_id, $name, $desc, $type, $params,
2732: $required)
2733: {
2734: try {
2735: return $this->_db->insert(
2736: 'INSERT INTO whups_attributes_desc '
2737: . '(type_id, attribute_name, attribute_description, '
2738: . 'attribute_type, attribute_params, attribute_required)'
2739: . ' VALUES (?, ?, ?, ?, ?, ?)',
2740: array((int)$type_id,
2741: $this->_toBackend($name),
2742: $this->_toBackend($desc),
2743: $type,
2744: serialize($this->_toBackend($params)),
2745: (int)($required == 'on')));
2746: } catch (Horde_Db_Exception $e) {
2747: throw new Whups_Exception($e);
2748: }
2749: }
2750:
2751: 2752: 2753: 2754: 2755: 2756: 2757: 2758: 2759: 2760: 2761: 2762:
2763: public function updateAttributeDesc($attribute_id, $newname, $newdesc,
2764: $newtype, $newparams, $newrequired)
2765: {
2766: try {
2767: $this->_db->update(
2768: 'UPDATE whups_attributes_desc '
2769: . 'SET attribute_name = ?, attribute_description = ?, '
2770: . 'attribute_type = ?, attribute_params = ?, '
2771: . 'attribute_required = ? WHERE attribute_id = ?',
2772: array($this->_toBackend($newname),
2773: $this->_toBackend($newdesc),
2774: $newtype,
2775: serialize($this->_toBackend($newparams)),
2776: (int)($newrequired == 'on'),
2777: (int)$attribute_id));
2778: } catch (Horde_Db_Exception $e) {
2779: throw new Whups_Exception($e);
2780: }
2781: }
2782:
2783: 2784: 2785: 2786: 2787: 2788: 2789:
2790: public function deleteAttributeDesc($attribute_id)
2791: {
2792: $this->_db->beginDbTransaction();
2793: try {
2794: $this->_db->delete(
2795: 'DELETE FROM whups_attributes_desc WHERE attribute_id = ?',
2796: array((int)$attribute_id));
2797: $this->_db->delete(
2798: 'DELETE FROM whups_attributes WHERE attribute_id = ?',
2799: array((int)$attribute_id));
2800: $this->_db->commitDbTransaction();
2801: } catch (Horde_Db_Exception $e) {
2802: $this->_db->rollbackDbTransaction();
2803: throw new Whups_Exception($e);
2804: }
2805: }
2806:
2807: 2808: 2809: 2810: 2811: 2812:
2813: public function getAllAttributes()
2814: {
2815: try {
2816: $attributes = $this->_db->selectAll(
2817: 'SELECT attribute_id, attribute_name, attribute_description, '
2818: . 'type_id FROM whups_attributes_desc');
2819: } catch (Horde_Db_Exception $e) {
2820: throw new Whups_Exception($e);
2821: }
2822: return $this->_fromBackend($attributes);
2823: }
2824:
2825: 2826: 2827: 2828: 2829: 2830: 2831: 2832:
2833: public function getAttributeDesc($attribute_id)
2834: {
2835: try {
2836: $attribute = $this->_db->selectOne(
2837: 'SELECT attribute_name, attribute_description, '
2838: . 'attribute_type, attribute_params, attribute_required '
2839: . 'FROM whups_attributes_desc WHERE attribute_id = ?',
2840: array((int)$attribute_id));
2841: } catch (Horde_Db_Exception $e) {
2842: throw new Whups_Exception($e);
2843: }
2844:
2845: return array(
2846: 'id' => $attribute_id,
2847: 'name' => $this->_fromBackend($attribute['attribute_name']),
2848: 'description' => $this->_fromBackend($attribute['attribute_description']),
2849: 'type' => empty($attribute['attribute_type'])
2850: ? 'text'
2851: : $attribute['attribute_type'],
2852: 'params' => $this->_fromBackend(@unserialize($attribute['attribute_params'])),
2853: 'required' => (bool)$attribute['attribute_required']);
2854: }
2855:
2856: 2857: 2858: 2859: 2860: 2861: 2862: 2863:
2864: public function getAttributeName($attribute_id)
2865: {
2866: try {
2867: $name = $this->_db->selectValue(
2868: 'SELECT attribute_name FROM whups_attributes_desc '
2869: . 'WHERE attribute_id = ?',
2870: array((int)$attribute_id));
2871: } catch (Horde_Db_Exception $e) {
2872: throw new Whups_Exception($e);
2873: }
2874: return $this->_fromBackend($name);
2875: }
2876:
2877: 2878: 2879: 2880: 2881: 2882: 2883:
2884: protected function _getAttributesForType($type = null)
2885: {
2886: $fields = 'attribute_id, attribute_name, attribute_description, '
2887: . 'attribute_type, attribute_params, attribute_required';
2888: $from = 'whups_attributes_desc';
2889: $order = 'attribute_name';
2890: if (empty($type)) {
2891: $fields .= ', whups_types.type_id, type_name';
2892: $from .= ' LEFT JOIN whups_types ON '
2893: . 'whups_attributes_desc.type_id = whups_types.type_id';
2894: $where = '';
2895: $order = 'type_name, ' . $order;
2896: } else {
2897: $where = ' WHERE type_id = ' . (int)$type;
2898: }
2899:
2900: $query = "SELECT $fields FROM $from$where ORDER BY $order";
2901: try {
2902: $attributes = $this->_db->selectAll($query);
2903: } catch (Horde_Db_Exception $e) {
2904: throw new Whups_Exception($e);
2905: }
2906: $results = array();
2907: foreach ($attributes as $attribute) {
2908: $id = $attribute['attribute_id'];
2909: $results[$id] = $attribute;
2910: $results[$id]['attribute_name'] =
2911: $this->_fromBackend($attribute['attribute_name']);
2912: if (empty($type)) {
2913: $results[$id]['attribute_name'] .=
2914: ' (' . $attribute['type_name'] . ')';
2915: }
2916: $results[$id]['attribute_description'] =
2917: $this->_fromBackend($attribute['attribute_description']);
2918: $results[$id]['attribute_type'] =
2919: empty($attribute['attribute_type'])
2920: ? 'text'
2921: : $attribute['attribute_type'];
2922: $results[$id]['attribute_params'] =
2923: $this->_fromBackend(@unserialize($attribute['attribute_params']));
2924: $results[$id]['attribute_required'] =
2925: (bool)$attribute['attribute_required'];
2926: }
2927:
2928: return $results;
2929: }
2930:
2931: 2932: 2933: 2934: 2935: 2936: 2937: 2938:
2939: public function getAttributeNamesForType($type_id)
2940: {
2941: try {
2942: $names = $this->_db->selectAll(
2943: 'SELECT attribute_name FROM whups_attributes_desc '
2944: . 'WHERE type_id = ? ORDER BY attribute_name',
2945: array((int)$type_id));
2946: } catch (Horde_Db_Exception $e) {
2947: throw new Whups_Exception($e);
2948: }
2949:
2950: return $this->_fromBackend($names);
2951: }
2952:
2953: 2954: 2955: 2956: 2957: 2958: 2959: 2960:
2961: public function getAttributeInfoForType($type_id)
2962: {
2963: try {
2964: $info = $this->_db->selectAll(
2965: 'SELECT attribute_id, attribute_name, attribute_description '
2966: . 'FROM whups_attributes_desc WHERE type_id = ? '
2967: . 'ORDER BY attribute_id',
2968: array((int)$type_id));
2969: } catch (Horde_Db_Exception $e) {
2970: throw new Whups_Exception($e);
2971: }
2972:
2973: return $this->_fromBackend($info);
2974: }
2975:
2976: 2977: 2978: 2979: 2980: 2981: 2982: 2983: 2984:
2985: protected function _setAttributeValue($ticket_id, $attribute_id,
2986: $attribute_value)
2987: {
2988: $db_attribute_value = $this->_toBackend($attribute_value);
2989:
2990: $this->_db->beginDbTransaction();
2991: try {
2992: $this->_db->delete(
2993: 'DELETE FROM whups_attributes WHERE ticket_id = ? '
2994: . 'AND attribute_id = ?',
2995: array($ticket_id, $attribute_id));
2996:
2997: if (strlen($attribute_value)) {
2998: $this->_db->insert(
2999: 'INSERT INTO whups_attributes (ticket_id, attribute_id, attribute_value) VALUES (?, ?, ?)',
3000: array($ticket_id, $attribute_id, $db_attribute_value));
3001: }
3002: $this->_db->commitDbTransaction();
3003: } catch (Horde_Db_Exception $e) {
3004: $this->_db->rollbackDbTransaction();
3005: throw new Whups_Exception($e);
3006: }
3007: }
3008:
3009: 3010: 3011: 3012: 3013: 3014: 3015: 3016: 3017: 3018: 3019:
3020: public function getTicketAttributes($ticket_id)
3021: {
3022: if (is_array($ticket_id)) {
3023:
3024:
3025: if (!count($ticket_id)) {
3026: return array();
3027: }
3028:
3029: try {
3030: $attributes = $this->_db->selectAll(
3031: 'SELECT ticket_id AS id, attribute_id, attribute_value '
3032: . 'FROM whups_attributes WHERE ticket_id IN ('
3033: . str_repeat('?, ', count($ticket_id) - 1) . '?)',
3034: $ticket_id);
3035: } catch (Horde_Db_Exception $e) {
3036: throw new Whups_Exception($e);
3037: }
3038: } else {
3039: try {
3040: $attributes = $this->_db->selectAssoc(
3041: 'SELECT attribute_id, attribute_value'
3042: . ' FROM whups_attributes WHERE ticket_id = ?',
3043: array((int)$ticket_id));
3044: } catch (Horde_Db_Exception $e) {
3045: throw new Whups_Exception($e);
3046: }
3047: }
3048:
3049: $attributes = $this->_fromBackend($attributes);
3050: foreach ($attributes as &$attribute) {
3051: try {
3052: $attribute = Horde_Serialize::unserialize(
3053: $attribute,
3054: Horde_Serialize::JSON);
3055: } catch (Horde_Serialize_Exception $e) {
3056: }
3057: }
3058:
3059: return $attributes;
3060: }
3061:
3062: 3063: 3064: 3065: 3066: 3067: 3068: 3069: 3070: 3071: 3072:
3073: public function getTicketAttributesWithNames($ticket_id)
3074: {
3075: if (is_array($ticket_id)) {
3076:
3077:
3078: if (!count($ticket_id)) {
3079: return array();
3080: }
3081:
3082: try {
3083: $attributes = $this->_db->selectAll(
3084: 'SELECT ticket_id AS id, d.attribute_name, '
3085: . 'a.attribute_id, a.attribute_value '
3086: . 'FROM whups_attributes a INNER JOIN '
3087: . 'whups_attributes_desc d '
3088: . 'ON (d.attribute_id = a.attribute_id)'
3089: . 'WHERE a.ticket_id IN ('
3090: . str_repeat('?, ', count($ticket_id) - 1) . '?)',
3091: $ticket_id);
3092: } catch (Horde_Db_Exception $e) {
3093: throw new Whups_Exception($e);
3094: }
3095: } else {
3096: try {
3097: $attributes = $this->_db->selectAssoc(
3098: 'SELECT d.attribute_name, a.attribute_value '
3099: . 'FROM whups_attributes a INNER JOIN '
3100: . 'whups_attributes_desc d '
3101: . 'ON (d.attribute_id = a.attribute_id)'
3102: . 'WHERE a.ticket_id = ? ORDER BY d.attribute_name',
3103: array((int)$ticket_id));
3104: } catch (Horde_Db_Exception $e) {
3105: throw new Whups_Exception($e);
3106: }
3107: }
3108:
3109: $attributes = $this->_fromBackend($attributes);
3110: foreach ($attributes as &$attribute) {
3111: try {
3112: $attribute = Horde_Serialize::unserialize(
3113: $attribute,
3114: Horde_Serialize::JSON);
3115: } catch (Horde_Serialize_Exception $e) {
3116: }
3117: }
3118:
3119: return $attributes;
3120: }
3121:
3122: 3123: 3124: 3125: 3126: 3127: 3128: 3129: 3130:
3131: protected function _getAllTicketAttributesWithNames($ticket_id)
3132: {
3133: try {
3134: $attributes = $this->_db->selectAll(
3135: 'SELECT d.attribute_id, d.attribute_name, '
3136: . 'd.attribute_description, d.attribute_type, '
3137: . 'd.attribute_params, d.attribute_required, '
3138: . 'a.attribute_value FROM whups_attributes_desc d '
3139: . 'LEFT JOIN whups_tickets t ON (t.ticket_id = ?) '
3140: . 'LEFT OUTER JOIN whups_attributes a '
3141: . 'ON (d.attribute_id = a.attribute_id AND a.ticket_id = ?) '
3142: . 'WHERE d.type_id = t.type_id ORDER BY d.attribute_name',
3143: array($ticket_id, $ticket_id));
3144: } catch (Horde_Db_Exception $e) {
3145: throw new Whups_Exception($e);
3146: }
3147:
3148: foreach ($attributes as &$attribute) {
3149: $attribute['attribute_name'] =
3150: $this->_fromBackend($attribute['attribute_name']);
3151: $attribute['attribute_description'] =
3152: $this->_fromBackend($attribute['attribute_description']);
3153: $attribute['attribute_type'] =
3154: empty($attribute['attribute_type'])
3155: ? 'text'
3156: : $attribute['attribute_type'];
3157: $attribute['attribute_params'] =
3158: $this->_fromBackend(@unserialize($attribute['attribute_params']));
3159: $attribute['attribute_required'] =
3160: (bool)$attribute['attribute_required'];
3161: try {
3162: $attribute['attribute_value'] = Horde_Serialize::unserialize(
3163: $attribute['attribute_value'],
3164: Horde_Serialize::JSON);
3165: } catch (Horde_Serialize_Exception $e) {
3166: }
3167: }
3168:
3169: return $attributes;
3170: }
3171:
3172: 3173: 3174: 3175: 3176: 3177: 3178: 3179:
3180: public function getOwners($ticketId)
3181: {
3182: if (is_array($ticketId)) {
3183: if (!count($ticketId)) {
3184: return array();
3185: }
3186:
3187: try {
3188: $owners = $this->_db->selectAll(
3189: 'SELECT ticket_id AS id, ticket_owner AS owner '
3190: . 'FROM whups_ticket_owners WHERE ticket_id IN '
3191: . '(' . str_repeat('?, ', count($ticketId) - 1) . '?)',
3192: $ticketId);
3193: } catch (Horde_Db_Exception $e) {
3194: throw new Whups_Exception($e);
3195: }
3196: } else {
3197: try {
3198: $owners = $this->_db->selectAll(
3199: 'SELECT ticket_id as id, ticket_owner as owner '
3200: . 'FROM whups_ticket_owners WHERE ticket_id = ?',
3201: array((int)$ticketId));
3202: } catch (Horde_Db_Exception $e) {
3203: throw new Whups_Exception($e);
3204: }
3205: }
3206:
3207: $results = array();
3208: foreach ($owners as $owner) {
3209: $results[$owner['id']][] = $owner['owner'];
3210: }
3211:
3212: return $results;
3213: }
3214:
3215: 3216: 3217: 3218: 3219: 3220: 3221: 3222: 3223: 3224: 3225:
3226: public function updateLog($ticket_id, $user, array $changes = array(),
3227: $transactionId = null)
3228: {
3229: if (is_null($transactionId)) {
3230: $transactionId = $this->newTransaction($user);
3231: }
3232:
3233: foreach ($changes as $type => $values) {
3234: if (!is_array($values)) {
3235: $values = array($values);
3236: }
3237: foreach ($values as $value) {
3238: try {
3239: $this->_db->insert(
3240: 'INSERT INTO whups_logs (transaction_id, '
3241: . 'ticket_id, log_type, log_value, '
3242: . 'log_value_num) VALUES (?, ?, ?, ?, ?)',
3243: array((int)$transactionId,
3244: (int)$ticket_id,
3245: $type,
3246: $this->_toBackend((string)$value),
3247: (int)$value));
3248: } catch (Horde_Db_Exception $e) {
3249: throw new Whups_Exception($e);
3250: }
3251: }
3252: }
3253:
3254: return $transactionId;
3255: }
3256:
3257: 3258: 3259: 3260: 3261: 3262: 3263: 3264: 3265:
3266: public function newTransaction($creator, $creator_email = null)
3267: {
3268: $insert = 'INSERT INTO whups_transactions '
3269: . '(transaction_timestamp, transaction_user_id) VALUES(?, ?)';
3270:
3271: $this->_db->beginDbTransaction();
3272: try {
3273: if ((empty($creator) || $creator < 0) && !empty($creator_email)) {
3274:
3275:
3276: $transactionId = $this->_db->insert($insert, array(time(), 'x'));
3277: $creator = '-' . $transactionId . '_transaction';
3278: $this->_db->insert(
3279: 'INSERT INTO whups_guests (guest_id, guest_email) '
3280: . 'VALUES (?, ?)',
3281: array((string)$creator, $creator_email));
3282: $this->_db->update(
3283: 'UPDATE whups_transactions SET transaction_user_id = ? '
3284: . 'WHERE transaction_id = ?',
3285: array($creator, $transactionId));
3286: } else {
3287: $transactionId = $this->_db->insert($insert, array(time(), $creator));
3288: }
3289: } catch (Horde_Db_Exception $e) {
3290: $this->_db->rollbackDbTransaction();
3291: throw new Whups_Exception($e);
3292: }
3293: $this->_db->commitDbTransaction();
3294:
3295: return $transactionId;
3296: }
3297:
3298: protected function _generateWhere($table, $fields, &$info, $type)
3299: {
3300: $where = '';
3301: $this->_mapFields($info);
3302:
3303: foreach ($fields as $field) {
3304: if (isset($info[$field])) {
3305: $prop = $info[$field];
3306: if (is_array($info[$field])) {
3307: $clauses = array();
3308: foreach ($prop as $pprop) {
3309: if (@settype($pprop, $type)) {
3310: $clauses[] = "$table.$field = " . $this->_db->quoteString($pprop);
3311: }
3312: }
3313: if (count($clauses)) {
3314: $where = $this->_addWhere($where, true, implode(' OR ', $clauses));
3315: }
3316: } else {
3317: $success = @settype($prop, $type);
3318: $where = $this->_addWhere($where, !is_null($prop) && $success, "$table.$field = " . $this->_db->quoteString($prop));
3319: }
3320: }
3321: }
3322:
3323: foreach ($fields as $field) {
3324: if (isset($info["not$field"])) {
3325: $prop = $info["not$field"];
3326:
3327: if (strpos($prop, ',') === false) {
3328: $success = @settype($prop, $type);
3329: $where = $this->_addWhere($where, $prop && $success, "$table.$field <> " . $this->_db->quoteString($prop));
3330: } else {
3331: $set = explode(',', $prop);
3332:
3333: foreach ($set as $prop) {
3334: $success = @settype($prop, $type);
3335: $where = $this->_addWhere($where, $prop && $success, "$table.$field <> " . $this->_db->quoteString($prop));
3336: }
3337: }
3338: }
3339: }
3340:
3341: return $where;
3342: }
3343:
3344: protected function _mapFields(&$info)
3345: {
3346: foreach ($info as $key => $val) {
3347: if ($key === 'id') {
3348: $info['ticket_id'] = $info['id'];
3349: unset($info['id']);
3350: } elseif ($key === 'state' ||
3351: $key === 'type' ||
3352: $key === 'queue' ||
3353: $key === 'priority') {
3354: $info[$key . '_id'] = $info[$key];
3355: unset($info[$key]);
3356: } elseif ($key === 'requester') {
3357: $info['user_id_' . $key] = $info[$key];
3358: unset($info[$key]);
3359: }
3360: }
3361: }
3362:
3363: protected function _addWhere($where, $condition, $clause, $conjunction = 'AND')
3364: {
3365: if (!empty($condition)) {
3366: if (!empty($where)) {
3367: $where .= " $conjunction ";
3368: }
3369:
3370: $where .= "($clause)";
3371: }
3372:
3373: return $where;
3374: }
3375:
3376: protected function _addDateWhere($where, $data, $type)
3377: {
3378: if (is_array($data)) {
3379: if (!empty($data['from'])) {
3380: $where = $this->_addWhere($where, true,
3381: $type . ' >= ' . (int)$data['from']);
3382: }
3383: if (!empty($data['to'])) {
3384: $where = $this->_addWhere($where, true,
3385: $type . ' <= ' . (int)$data['to']);
3386: }
3387: return $where;
3388: }
3389:
3390: return $this->_addWhere($where, true, $type . ' = ' . (int)$data);
3391: }
3392:
3393: protected function _prefixTableToColumns($table, $columns)
3394: {
3395: $join = "";
3396:
3397: $clause = '';
3398: foreach ($columns as $column) {
3399: $clause .= "$join$table.$column";
3400: $join = ', ';
3401: }
3402:
3403: return $clause;
3404: }
3405:
3406: protected function _toBackend($value)
3407: {
3408: return Horde_String::convertCharset($value, 'UTF-8', $this->_db->getOption('charset'));
3409: }
3410:
3411: protected function _fromBackend($value)
3412: {
3413: return Horde_String::convertCharset($value, $this->_db->getOption('charset'), 'UTF-8');
3414: }
3415: }
3416: