Template for caldav_driver.php
[caldav_driver.git] / database_driver.php
1 <?php
2
3 /**
4 * Database driver for the Calendar plugin
5 *
6 * @version @package_version@
7 * @author Lazlo Westerhof <hello@lazlo.me>
8 * @author Thomas Bruederli <bruederli@kolabsys.com>
9 *
10 * Copyright (C) 2010, Lazlo Westerhof <hello@lazlo.me>
11 * Copyright (C) 2012, Kolab Systems AG <contact@kolabsys.com>
12 *
13 * This program is free software: you can redistribute it and/or modify
14 * it under the terms of the GNU Affero General Public License as
15 * published by the Free Software Foundation, either version 3 of the
16 * License, or (at your option) any later version.
17 *
18 * This program is distributed in the hope that it will be useful,
19 * but WITHOUT ANY WARRANTY; without even the implied warranty of
20 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21 * GNU Affero General Public License for more details.
22 *
23 * You should have received a copy of the GNU Affero General Public License
24 * along with this program. If not, see <http://www.gnu.org/licenses/>.
25 */
26
27
28 class database_driver extends calendar_driver
29 {
30 const DB_DATE_FORMAT = 'Y-m-d H:i:s';
31
32 // features this backend supports
33 public $alarms = true;
34 public $attendees = true;
35 public $freebusy = false;
36 public $attachments = true;
37 public $alarm_types = array('DISPLAY');
38
39 private $rc;
40 private $cal;
41 private $cache = array();
42 private $calendars = array();
43 private $calendar_ids = '';
44 private $free_busy_map = array('free' => 0, 'busy' => 1, 'out-of-office' => 2, 'outofoffice' => 2, 'tentative' => 3);
45 private $sensitivity_map = array('public' => 0, 'private' => 1, 'confidential' => 2);
46 private $server_timezone;
47
48 private $db_events = 'events';
49 private $db_calendars = 'calendars';
50 private $db_attachments = 'attachments';
51
52
53 /**
54 * Default constructor
55 */
56 public function __construct($cal)
57 {
58 $this->cal = $cal;
59 $this->rc = $cal->rc;
60 $this->server_timezone = new DateTimeZone(date_default_timezone_get());
61
62 // load library classes
63 require_once($this->cal->home . '/lib/Horde_Date_Recurrence.php');
64
65 // read database config
66 $db = $this->rc->get_dbh();
67 $this->db_events = $this->rc->config->get('db_table_events', $db->table_name($this->db_events));
68 $this->db_calendars = $this->rc->config->get('db_table_calendars', $db->table_name($this->db_calendars));
69 $this->db_attachments = $this->rc->config->get('db_table_attachments', $db->table_name($this->db_attachments));
70
71 $this->_read_calendars();
72 }
73
74 /**
75 * Read available calendars for the current user and store them internally
76 */
77 private function _read_calendars()
78 {
79 $hidden = array_filter(explode(',', $this->rc->config->get('hidden_calendars', '')));
80
81 if (!empty($this->rc->user->ID)) {
82 $calendar_ids = array();
83 $result = $this->rc->db->query(
84 "SELECT *, calendar_id AS id FROM " . $this->db_calendars . "
85 WHERE user_id=?
86 ORDER BY name",
87 $this->rc->user->ID
88 );
89 while ($result && ($arr = $this->rc->db->fetch_assoc($result))) {
90 $arr['showalarms'] = intval($arr['showalarms']);
91 $arr['active'] = !in_array($arr['id'], $hidden);
92 $arr['name'] = html::quote($arr['name']);
93 $arr['listname'] = html::quote($arr['name']);
94 $this->calendars[$arr['calendar_id']] = $arr;
95 $calendar_ids[] = $this->rc->db->quote($arr['calendar_id']);
96 }
97 $this->calendar_ids = join(',', $calendar_ids);
98 }
99 }
100
101 /**
102 * Get a list of available calendars from this source
103 *
104 * @param bool $active Return only active calendars
105 * @param bool $personal Return only personal calendars
106 *
107 * @return array List of calendars
108 */
109 public function list_calendars($active = false, $personal = false)
110 {
111 // attempt to create a default calendar for this user
112 if (empty($this->calendars)) {
113 if ($this->create_calendar(array('name' => 'Default', 'color' => 'cc0000')))
114 $this->_read_calendars();
115 }
116
117 $calendars = $this->calendars;
118
119 // filter active calendars
120 if ($active) {
121 foreach ($calendars as $idx => $cal) {
122 if (!$cal['active']) {
123 unset($calendars[$idx]);
124 }
125 }
126 }
127
128 // 'personal' is unsupported in this driver
129
130 return $calendars;
131 }
132
133 /**
134 * Create a new calendar assigned to the current user
135 *
136 * @param array Hash array with calendar properties
137 * name: Calendar name
138 * color: The color of the calendar
139 * @return mixed ID of the calendar on success, False on error
140 */
141 public function create_calendar($prop)
142 {
143 $result = $this->rc->db->query(
144 "INSERT INTO " . $this->db_calendars . "
145 (user_id, name, color, showalarms)
146 VALUES (?, ?, ?, ?)",
147 $this->rc->user->ID,
148 $prop['name'],
149 $prop['color'],
150 $prop['showalarms']?1:0
151 );
152
153 if ($result)
154 return $this->rc->db->insert_id($this->db_calendars);
155
156 return false;
157 }
158
159 /**
160 * Update properties of an existing calendar
161 *
162 * @see calendar_driver::edit_calendar()
163 */
164 public function edit_calendar($prop)
165 {
166 $query = $this->rc->db->query(
167 "UPDATE " . $this->db_calendars . "
168 SET name=?, color=?, showalarms=?
169 WHERE calendar_id=?
170 AND user_id=?",
171 $prop['name'],
172 $prop['color'],
173 $prop['showalarms']?1:0,
174 $prop['id'],
175 $this->rc->user->ID
176 );
177
178 return $this->rc->db->affected_rows($query);
179 }
180
181 /**
182 * Set active/subscribed state of a calendar
183 * Save a list of hidden calendars in user prefs
184 *
185 * @see calendar_driver::subscribe_calendar()
186 */
187 public function subscribe_calendar($prop)
188 {
189 $hidden = array_flip(explode(',', $this->rc->config->get('hidden_calendars', '')));
190
191 if ($prop['active'])
192 unset($hidden[$prop['id']]);
193 else
194 $hidden[$prop['id']] = 1;
195
196 return $this->rc->user->save_prefs(array('hidden_calendars' => join(',', array_keys($hidden))));
197 }
198
199 /**
200 * Delete the given calendar with all its contents
201 *
202 * @see calendar_driver::remove_calendar()
203 */
204 public function remove_calendar($prop)
205 {
206 if (!$this->calendars[$prop['id']])
207 return false;
208
209 // events and attachments will be deleted by foreign key cascade
210
211 $query = $this->rc->db->query(
212 "DELETE FROM " . $this->db_calendars . "
213 WHERE calendar_id=?",
214 $prop['id']
215 );
216
217 return $this->rc->db->affected_rows($query);
218 }
219
220 /**
221 * Add a single event to the database
222 *
223 * @param array Hash array with event properties
224 * @see calendar_driver::new_event()
225 */
226 public function new_event($event)
227 {
228 if (!$this->validate($event))
229 return false;
230
231 if (!empty($this->calendars)) {
232 if ($event['calendar'] && !$this->calendars[$event['calendar']])
233 return false;
234 if (!$event['calendar'])
235 $event['calendar'] = reset(array_keys($this->calendars));
236
237 $event = $this->_save_preprocess($event);
238
239 $this->rc->db->query(sprintf(
240 "INSERT INTO " . $this->db_events . "
241 (calendar_id, created, changed, uid, %s, %s, all_day, recurrence, title, description, location, categories, url, free_busy, priority, sensitivity, attendees, alarms, notifyat)
242 VALUES (?, %s, %s, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
243 $this->rc->db->quote_identifier('start'),
244 $this->rc->db->quote_identifier('end'),
245 $this->rc->db->now(),
246 $this->rc->db->now()
247 ),
248 $event['calendar'],
249 strval($event['uid']),
250 $event['start']->format(self::DB_DATE_FORMAT),
251 $event['end']->format(self::DB_DATE_FORMAT),
252 intval($event['all_day']),
253 $event['_recurrence'],
254 strval($event['title']),
255 strval($event['description']),
256 strval($event['location']),
257 strval($event['categories']),
258 strval($event['url']),
259 intval($event['free_busy']),
260 intval($event['priority']),
261 intval($event['sensitivity']),
262 $event['attendees'],
263 $event['alarms'],
264 $event['notifyat']
265 );
266
267 $event_id = $this->rc->db->insert_id($this->db_events);
268
269 if ($event_id) {
270 $event['id'] = $event_id;
271
272 // add attachments
273 if (!empty($event['attachments'])) {
274 foreach ($event['attachments'] as $attachment) {
275 $this->add_attachment($attachment, $event_id);
276 unset($attachment);
277 }
278 }
279
280 $this->_update_recurring($event);
281 }
282
283 return $event_id;
284 }
285
286 return false;
287 }
288
289 /**
290 * Update an event entry with the given data
291 *
292 * @param array Hash array with event properties
293 * @see calendar_driver::edit_event()
294 */
295 public function edit_event($event)
296 {
297 if (!empty($this->calendars)) {
298 $update_master = false;
299 $update_recurring = true;
300 $old = $this->get_event($event);
301
302 // increment sequence number
303 if ($old['sequence'] && empty($event['sequence']))
304 $event['sequence'] = max($event['sequence'], $old['sequence']+1);
305
306 // modify a recurring event, check submitted savemode to do the right things
307 if ($old['recurrence'] || $old['recurrence_id']) {
308 $master = $old['recurrence_id'] ? $this->get_event(array('id' => $old['recurrence_id'])) : $old;
309
310 // keep saved exceptions (not submitted by the client)
311 if ($old['recurrence']['EXDATE'])
312 $event['recurrence']['EXDATE'] = $old['recurrence']['EXDATE'];
313
314 switch ($event['_savemode']) {
315 case 'new':
316 $event['uid'] = $this->cal->generate_uid();
317 return $this->new_event($event);
318
319 case 'current':
320 // add exception to master event
321 $master['recurrence']['EXDATE'][] = $old['start'];
322 $update_master = true;
323
324 // just update this occurence (decouple from master)
325 $update_recurring = false;
326 $event['recurrence_id'] = 0;
327 $event['recurrence'] = array();
328 break;
329
330 case 'future':
331 if ($master['id'] != $event['id']) {
332 // set until-date on master event, then save this instance as new recurring event
333 $master['recurrence']['UNTIL'] = clone $event['start'];
334 $master['recurrence']['UNTIL']->sub(new DateInterval('P1D'));
335 unset($master['recurrence']['COUNT']);
336 $update_master = true;
337
338 // if recurrence COUNT, update value to the correct number of future occurences
339 if ($event['recurrence']['COUNT']) {
340 $fromdate = clone $event['start'];
341 $fromdate->setTimezone($this->server_timezone);
342 $sqlresult = $this->rc->db->query(sprintf(
343 "SELECT event_id FROM " . $this->db_events . "
344 WHERE calendar_id IN (%s)
345 AND %s >= ?
346 AND recurrence_id=?",
347 $this->calendar_ids,
348 $this->rc->db->quote_identifier('start')
349 ),
350 $fromdate->format(self::DB_DATE_FORMAT),
351 $master['id']);
352 if ($count = $this->rc->db->num_rows($sqlresult))
353 $event['recurrence']['COUNT'] = $count;
354 }
355
356 $update_recurring = true;
357 $event['recurrence_id'] = 0;
358 break;
359 }
360 // else: 'future' == 'all' if modifying the master event
361
362 default: // 'all' is default
363 $event['id'] = $master['id'];
364 $event['recurrence_id'] = 0;
365
366 // use start date from master but try to be smart on time or duration changes
367 $old_start_date = $old['start']->format('Y-m-d');
368 $old_start_time = $old['allday'] ? '' : $old['start']->format('H:i');
369 $old_duration = $old['end']->format('U') - $old['start']->format('U');
370
371 $new_start_date = $event['start']->format('Y-m-d');
372 $new_start_time = $event['allday'] ? '' : $event['start']->format('H:i');
373 $new_duration = $event['end']->format('U') - $event['start']->format('U');
374
375 $diff = $old_start_date != $new_start_date || $old_start_time != $new_start_time || $old_duration != $new_duration;
376
377 // shifted or resized
378 if ($diff && ($old_start_date == $new_start_date || $old_duration == $new_duration)) {
379 $event['start'] = $master['start']->add($old['start']->diff($event['start']));
380 $event['end'] = clone $event['start'];
381 $event['end']->add(new DateInterval('PT'.$new_duration.'S'));
382 }
383 break;
384 }
385 }
386
387 $success = $this->_update_event($event, $update_recurring);
388 if ($success && $update_master)
389 $this->_update_event($master, true);
390
391 return $success;
392 }
393
394 return false;
395 }
396
397 /**
398 * Convert save data to be used in SQL statements
399 */
400 private function _save_preprocess($event)
401 {
402 // shift dates to server's timezone
403 $event['start'] = clone $event['start'];
404 $event['start']->setTimezone($this->server_timezone);
405 $event['end'] = clone $event['end'];
406 $event['end']->setTimezone($this->server_timezone);
407
408 // compose vcalendar-style recurrencue rule from structured data
409 $rrule = $event['recurrence'] ? libcalendaring::to_rrule($event['recurrence']) : '';
410 $event['_recurrence'] = rtrim($rrule, ';');
411 $event['free_busy'] = intval($this->free_busy_map[strtolower($event['free_busy'])]);
412 $event['sensitivity'] = intval($this->sensitivity_map[strtolower($event['sensitivity'])]);
413
414 if (isset($event['allday'])) {
415 $event['all_day'] = $event['allday'] ? 1 : 0;
416 }
417
418 // compute absolute time to notify the user
419 $event['notifyat'] = $this->_get_notification($event);
420
421 // process event attendees
422 $_attendees = '';
423 foreach ((array)$event['attendees'] as $attendee) {
424 if (!$attendee['name'] && !$attendee['email'])
425 continue;
426 $_attendees .= 'NAME="'.addcslashes($attendee['name'], '"') . '"' .
427 ';STATUS=' . $attendee['status'].
428 ';ROLE=' . $attendee['role'] .
429 ';EMAIL=' . $attendee['email'] .
430 "\n";
431 }
432 $event['attendees'] = rtrim($_attendees);
433
434 return $event;
435 }
436
437 /**
438 * Compute absolute time to notify the user
439 */
440 private function _get_notification($event)
441 {
442 if ($event['alarms'] && $event['start'] > new DateTime()) {
443 $alarm = libcalendaring::get_next_alarm($event);
444
445 if ($alarm['time'] && $alarm['action'] == 'DISPLAY')
446 return date('Y-m-d H:i:s', $alarm['time']);
447 }
448
449 return null;
450 }
451
452 /**
453 * Save the given event record to database
454 *
455 * @param array Event data, already passed through self::_save_preprocess()
456 * @param boolean True if recurring events instances should be updated, too
457 */
458 private function _update_event($event, $update_recurring = true)
459 {
460 $event = $this->_save_preprocess($event);
461 $sql_set = array();
462 $set_cols = array('start', 'end', 'all_day', 'recurrence_id', 'sequence', 'title', 'description', 'location', 'categories', 'url', 'free_busy', 'priority', 'sensitivity', 'attendees', 'alarms', 'notifyat');
463 foreach ($set_cols as $col) {
464 if (is_object($event[$col]) && is_a($event[$col], 'DateTime'))
465 $sql_set[] = $this->rc->db->quote_identifier($col) . '=' . $this->rc->db->quote($event[$col]->format(self::DB_DATE_FORMAT));
466 else if (isset($event[$col]))
467 $sql_set[] = $this->rc->db->quote_identifier($col) . '=' . $this->rc->db->quote($event[$col]);
468 }
469
470 if ($event['_recurrence'])
471 $sql_set[] = $this->rc->db->quote_identifier('recurrence') . '=' . $this->rc->db->quote($event['_recurrence']);
472
473 if ($event['_fromcalendar'] && $event['_fromcalendar'] != $event['calendar'])
474 $sql_set[] = 'calendar_id=' . $this->rc->db->quote($event['calendar']);
475
476 $query = $this->rc->db->query(sprintf(
477 "UPDATE " . $this->db_events . "
478 SET changed=%s %s
479 WHERE event_id=?
480 AND calendar_id IN (" . $this->calendar_ids . ")",
481 $this->rc->db->now(),
482 ($sql_set ? ', ' . join(', ', $sql_set) : '')
483 ),
484 $event['id']
485 );
486
487 $success = $this->rc->db->affected_rows($query);
488
489 // add attachments
490 if ($success && !empty($event['attachments'])) {
491 foreach ($event['attachments'] as $attachment) {
492 $this->add_attachment($attachment, $event['id']);
493 unset($attachment);
494 }
495 }
496
497 // remove attachments
498 if ($success && !empty($event['deleted_attachments'])) {
499 foreach ($event['deleted_attachments'] as $attachment) {
500 $this->remove_attachment($attachment, $event['id']);
501 }
502 }
503
504 if ($success) {
505 unset($this->cache[$event['id']]);
506 if ($update_recurring)
507 $this->_update_recurring($event);
508 }
509
510 return $success;
511 }
512
513 /**
514 * Insert "fake" entries for recurring occurences of this event
515 */
516 private function _update_recurring($event)
517 {
518 if (empty($this->calendars))
519 return;
520
521 // clear existing recurrence copies
522 $this->rc->db->query(
523 "DELETE FROM " . $this->db_events . "
524 WHERE recurrence_id=?
525 AND calendar_id IN (" . $this->calendar_ids . ")",
526 $event['id']
527 );
528
529 // create new fake entries
530 if ($event['recurrence']) {
531 // include library class
532 require_once($this->cal->home . '/lib/calendar_recurrence.php');
533
534 $recurrence = new calendar_recurrence($this->cal, $event);
535
536 $count = 0;
537 $duration = $event['start']->diff($event['end']);
538 while ($next_start = $recurrence->next_start()) {
539 $next_start->setTimezone($this->server_timezone);
540 $next_end = clone $next_start;
541 $next_end->add($duration);
542 $notify_at = $this->_get_notification(array('alarms' => $event['alarms'], 'start' => $next_start, 'end' => $next_end));
543 $query = $this->rc->db->query(sprintf(
544 "INSERT INTO " . $this->db_events . "
545 (calendar_id, recurrence_id, created, changed, uid, %s, %s, all_day, recurrence, title, description, location, categories, url, free_busy, priority, sensitivity, alarms, notifyat)
546 SELECT calendar_id, ?, %s, %s, uid, ?, ?, all_day, recurrence, title, description, location, categories, url, free_busy, priority, sensitivity, alarms, ?
547 FROM " . $this->db_events . " WHERE event_id=? AND calendar_id IN (" . $this->calendar_ids . ")",
548 $this->rc->db->quote_identifier('start'),
549 $this->rc->db->quote_identifier('end'),
550 $this->rc->db->now(),
551 $this->rc->db->now()
552 ),
553 $event['id'],
554 $next_start->format(self::DB_DATE_FORMAT),
555 $next_end->format(self::DB_DATE_FORMAT),
556 $notify_at,
557 $event['id']
558 );
559
560 if (!$this->rc->db->affected_rows($query))
561 break;
562
563 // stop adding events for inifinite recurrence after 20 years
564 if (++$count > 999 || (!$recurrence->recurEnd && !$recurrence->recurCount && $next_start->format('Y') > date('Y') + 20))
565 break;
566 }
567 }
568 }
569
570 /**
571 * Move a single event
572 *
573 * @param array Hash array with event properties
574 * @see calendar_driver::move_event()
575 */
576 public function move_event($event)
577 {
578 // let edit_event() do all the magic
579 return $this->edit_event($event + (array)$this->get_event($event));
580 }
581
582 /**
583 * Resize a single event
584 *
585 * @param array Hash array with event properties
586 * @see calendar_driver::resize_event()
587 */
588 public function resize_event($event)
589 {
590 // let edit_event() do all the magic
591 return $this->edit_event($event + (array)$this->get_event($event));
592 }
593
594 /**
595 * Remove a single event from the database
596 *
597 * @param array Hash array with event properties
598 * @param boolean Remove record irreversible (@TODO)
599 *
600 * @see calendar_driver::remove_event()
601 */
602 public function remove_event($event, $force = true)
603 {
604 if (!empty($this->calendars)) {
605 $event += (array)$this->get_event($event);
606 $master = $event;
607 $update_master = false;
608 $savemode = 'all';
609
610 // read master if deleting a recurring event
611 if ($event['recurrence'] || $event['recurrence_id']) {
612 $master = $event['recurrence_id'] ? $this->get_event(array('id' => $event['recurrence_id'])) : $event;
613 $savemode = $event['_savemode'];
614 }
615
616 switch ($savemode) {
617 case 'current':
618 // add exception to master event
619 $master['recurrence']['EXDATE'][] = $event['start'];
620 $update_master = true;
621
622 // just delete this single occurence
623 $query = $this->rc->db->query(
624 "DELETE FROM " . $this->db_events . "
625 WHERE calendar_id IN (" . $this->calendar_ids . ")
626 AND event_id=?",
627 $event['id']
628 );
629 break;
630
631 case 'future':
632 if ($master['id'] != $event['id']) {
633 // set until-date on master event
634 $master['recurrence']['UNTIL'] = clone $event['start'];
635 $master['recurrence']['UNTIL']->sub(new DateInterval('P1D'));
636 unset($master['recurrence']['COUNT']);
637 $update_master = true;
638
639 // delete this and all future instances
640 $fromdate = clone $event['start'];
641 $fromdate->setTimezone($this->server_timezone);
642 $query = $this->rc->db->query(
643 "DELETE FROM " . $this->db_events . "
644 WHERE calendar_id IN (" . $this->calendar_ids . ")
645 AND " . $this->rc->db->quote_identifier('start') . " >= ?
646 AND recurrence_id=?",
647 $fromdate->format(self::DB_DATE_FORMAT),
648 $master['id']
649 );
650 break;
651 }
652 // else: future == all if modifying the master event
653
654 default: // 'all' is default
655 $query = $this->rc->db->query(
656 "DELETE FROM " . $this->db_events . "
657 WHERE (event_id=? OR recurrence_id=?)
658 AND calendar_id IN (" . $this->calendar_ids . ")",
659 $master['id'],
660 $master['id']
661 );
662 break;
663 }
664
665 $success = $this->rc->db->affected_rows($query);
666 if ($success && $update_master)
667 $this->_update_event($master, true);
668
669 return $success;
670 }
671
672 return false;
673 }
674
675 /**
676 * Return data of a specific event
677 * @param mixed Hash array with event properties or event UID
678 * @param boolean Only search in writeable calendars (ignored)
679 * @param boolean Only search in active calendars
680 * @param boolean Only search in personal calendars (ignored)
681 * @return array Hash array with event properties
682 */
683 public function get_event($event, $writeable = false, $active = false, $personal = false)
684 {
685 $id = is_array($event) ? ($event['id'] ? $event['id'] : $event['uid']) : $event;
686 $col = is_array($event) && is_numeric($id) ? 'event_id' : 'uid';
687
688 if ($this->cache[$id])
689 return $this->cache[$id];
690
691 if ($active) {
692 $calendars = $this->calendars;
693 foreach ($calendars as $idx => $cal) {
694 if (!$cal['active']) {
695 unset($calendars[$idx]);
696 }
697 }
698 $cals = join(',', $calendars);
699 }
700 else {
701 $cals = $this->calendar_ids;
702 }
703
704 $result = $this->rc->db->query(sprintf(
705 "SELECT e.*, (SELECT COUNT(attachment_id) FROM " . $this->db_attachments . "
706 WHERE event_id = e.event_id OR event_id = e.recurrence_id) AS _attachments
707 FROM " . $this->db_events . " AS e
708 WHERE e.calendar_id IN (%s)
709 AND e.$col=?",
710 $cals
711 ),
712 $id);
713
714 if ($result && ($event = $this->rc->db->fetch_assoc($result)) && $event['event_id']) {
715 $this->cache[$id] = $this->_read_postprocess($event);
716 return $this->cache[$id];
717 }
718
719 return false;
720 }
721
722 /**
723 * Get event data
724 *
725 * @see calendar_driver::load_events()
726 */
727 public function load_events($start, $end, $query = null, $calendars = null, $virtual = 1, $modifiedsince = null)
728 {
729 if (empty($calendars))
730 $calendars = array_keys($this->calendars);
731 else if (is_string($calendars))
732 $calendars = explode(',', $calendars);
733
734 // only allow to select from calendars of this use
735 $calendar_ids = array_map(array($this->rc->db, 'quote'), array_intersect($calendars, array_keys($this->calendars)));
736
737 // compose (slow) SQL query for searching
738 // FIXME: improve searching using a dedicated col and normalized values
739 if ($query) {
740 foreach (array('title','location','description','categories','attendees') as $col)
741 $sql_query[] = $this->rc->db->ilike($col, '%'.$query.'%');
742 $sql_add = 'AND (' . join(' OR ', $sql_query) . ')';
743 }
744
745 if (!$virtual)
746 $sql_arr .= ' AND e.recurrence_id = 0';
747
748 if ($modifiedsince)
749 $sql_add .= ' AND e.changed >= ' . $this->rc->db->quote(date('Y-m-d H:i:s', $modifiedsince));
750
751 $events = array();
752 if (!empty($calendar_ids)) {
753 $result = $this->rc->db->query(sprintf(
754 "SELECT e.*, (SELECT COUNT(attachment_id) FROM " . $this->db_attachments . "
755 WHERE event_id = e.event_id OR event_id = e.recurrence_id) AS _attachments
756 FROM " . $this->db_events . " AS e
757 WHERE e.calendar_id IN (%s)
758 AND e.start <= %s AND e.end >= %s
759 %s
760 GROUP BY e.event_id",
761 join(',', $calendar_ids),
762 $this->rc->db->fromunixtime($end),
763 $this->rc->db->fromunixtime($start),
764 $sql_add
765 ));
766
767 while ($result && ($event = $this->rc->db->fetch_assoc($result))) {
768 $events[] = $this->_read_postprocess($event);
769 }
770 }
771
772 return $events;
773 }
774
775 /**
776 * Convert sql record into a rcube style event object
777 */
778 private function _read_postprocess($event)
779 {
780 $free_busy_map = array_flip($this->free_busy_map);
781 $sensitivity_map = array_flip($this->sensitivity_map);
782
783 $event['id'] = $event['event_id'];
784 $event['start'] = new DateTime($event['start']);
785 $event['end'] = new DateTime($event['end']);
786 $event['allday'] = intval($event['all_day']);
787 $event['created'] = new DateTime($event['created']);
788 $event['changed'] = new DateTime($event['changed']);
789 $event['free_busy'] = $free_busy_map[$event['free_busy']];
790 $event['sensitivity'] = $sensitivity_map[$event['sensitivity']];
791 $event['calendar'] = $event['calendar_id'];
792 $event['recurrence_id'] = intval($event['recurrence_id']);
793
794 // parse recurrence rule
795 if ($event['recurrence'] && preg_match_all('/([A-Z]+)=([^;]+);?/', $event['recurrence'], $m, PREG_SET_ORDER)) {
796 $event['recurrence'] = array();
797 foreach ($m as $rr) {
798 if (is_numeric($rr[2]))
799 $rr[2] = intval($rr[2]);
800 else if ($rr[1] == 'UNTIL')
801 $rr[2] = date_create($rr[2]);
802 else if ($rr[1] == 'EXDATE')
803 $rr[2] = array_map('date_create', explode(',', $rr[2]));
804 $event['recurrence'][$rr[1]] = $rr[2];
805 }
806 }
807
808 if ($event['_attachments'] > 0)
809 $event['attachments'] = (array)$this->list_attachments($event);
810
811 // decode serialized event attendees
812 if ($event['attendees']) {
813 $attendees = array();
814 foreach (explode("\n", $event['attendees']) as $line) {
815 $att = array();
816 foreach (rcube_utils::explode_quoted_string(';', $line) as $prop) {
817 list($key, $value) = explode("=", $prop);
818 $att[strtolower($key)] = stripslashes(trim($value, '""'));
819 }
820 $attendees[] = $att;
821 }
822 $event['attendees'] = $attendees;
823 }
824
825 unset($event['event_id'], $event['calendar_id'], $event['notifyat'], $event['all_day'], $event['_attachments']);
826 return $event;
827 }
828
829 /**
830 * Get a list of pending alarms to be displayed to the user
831 *
832 * @see calendar_driver::pending_alarms()
833 */
834 public function pending_alarms($time, $calendars = null)
835 {
836 if (empty($calendars))
837 $calendars = array_keys($this->calendars);
838 else if (is_string($calendars))
839 $calendars = explode(',', $calendars);
840
841 // only allow to select from calendars with activated alarms
842 $calendar_ids = array();
843 foreach ($calendars as $cid) {
844 if ($this->calendars[$cid] && $this->calendars[$cid]['showalarms'])
845 $calendar_ids[] = $cid;
846 }
847 $calendar_ids = array_map(array($this->rc->db, 'quote'), $calendar_ids);
848
849 $alarms = array();
850 if (!empty($calendar_ids)) {
851 $result = $this->rc->db->query(sprintf(
852 "SELECT * FROM " . $this->db_events . "
853 WHERE calendar_id IN (%s)
854 AND notifyat <= %s AND %s > %s",
855 join(',', $calendar_ids),
856 $this->rc->db->fromunixtime($time),
857 $this->rc->db->quote_identifier('end'),
858 $this->rc->db->fromunixtime($time)
859 ));
860
861 while ($result && ($event = $this->rc->db->fetch_assoc($result)))
862 $alarms[] = $this->_read_postprocess($event);
863 }
864
865 return $alarms;
866 }
867
868 /**
869 * Feedback after showing/sending an alarm notification
870 *
871 * @see calendar_driver::dismiss_alarm()
872 */
873 public function dismiss_alarm($event_id, $snooze = 0)
874 {
875 // set new notifyat time or unset if not snoozed
876 $notify_at = $snooze > 0 ? date(self::DB_DATE_FORMAT, time() + $snooze) : null;
877
878 $query = $this->rc->db->query(sprintf(
879 "UPDATE " . $this->db_events . "
880 SET changed=%s, notifyat=?
881 WHERE event_id=?
882 AND calendar_id IN (" . $this->calendar_ids . ")",
883 $this->rc->db->now()),
884 $notify_at,
885 $event_id
886 );
887
888 return $this->rc->db->affected_rows($query);
889 }
890
891 /**
892 * Save an attachment related to the given event
893 */
894 private function add_attachment($attachment, $event_id)
895 {
896 $data = $attachment['data'] ? $attachment['data'] : file_get_contents($attachment['path']);
897
898 $query = $this->rc->db->query(
899 "INSERT INTO " . $this->db_attachments .
900 " (event_id, filename, mimetype, size, data)" .
901 " VALUES (?, ?, ?, ?, ?)",
902 $event_id,
903 $attachment['name'],
904 $attachment['mimetype'],
905 strlen($data),
906 base64_encode($data)
907 );
908
909 return $this->rc->db->affected_rows($query);
910 }
911
912 /**
913 * Remove a specific attachment from the given event
914 */
915 private function remove_attachment($attachment_id, $event_id)
916 {
917 $query = $this->rc->db->query(
918 "DELETE FROM " . $this->db_attachments .
919 " WHERE attachment_id = ?" .
920 " AND event_id IN (SELECT event_id FROM " . $this->db_events .
921 " WHERE event_id = ?" .
922 " AND calendar_id IN (" . $this->calendar_ids . "))",
923 $attachment_id,
924 $event_id
925 );
926
927 return $this->rc->db->affected_rows($query);
928 }
929
930 /**
931 * List attachments of specified event
932 */
933 public function list_attachments($event)
934 {
935 $attachments = array();
936
937 if (!empty($this->calendar_ids)) {
938 $result = $this->rc->db->query(
939 "SELECT attachment_id AS id, filename AS name, mimetype, size " .
940 " FROM " . $this->db_attachments .
941 " WHERE event_id IN (SELECT event_id FROM " . $this->db_events .
942 " WHERE event_id=?" .
943 " AND calendar_id IN (" . $this->calendar_ids . "))".
944 " ORDER BY filename",
945 $event['recurrence_id'] ? $event['recurrence_id'] : $event['event_id']
946 );
947
948 while ($result && ($arr = $this->rc->db->fetch_assoc($result))) {
949 $attachments[] = $arr;
950 }
951 }
952
953 return $attachments;
954 }
955
956 /**
957 * Get attachment properties
958 */
959 public function get_attachment($id, $event)
960 {
961 if (!empty($this->calendar_ids)) {
962 $result = $this->rc->db->query(
963 "SELECT attachment_id AS id, filename AS name, mimetype, size " .
964 " FROM " . $this->db_attachments .
965 " WHERE attachment_id=?".
966 " AND event_id=?",
967 $id,
968 $event['recurrence_id'] ? $event['recurrence_id'] : $event['id']
969 );
970
971 if ($result && ($arr = $this->rc->db->fetch_assoc($result))) {
972 return $arr;
973 }
974 }
975
976 return null;
977 }
978
979 /**
980 * Get attachment body
981 */
982 public function get_attachment_body($id, $event)
983 {
984 if (!empty($this->calendar_ids)) {
985 $result = $this->rc->db->query(
986 "SELECT data " .
987 " FROM " . $this->db_attachments .
988 " WHERE attachment_id=?".
989 " AND event_id=?",
990 $id,
991 $event['id']
992 );
993
994 if ($result && ($arr = $this->rc->db->fetch_assoc($result))) {
995 return base64_decode($arr['data']);
996 }
997 }
998
999 return null;
1000 }
1001
1002 /**
1003 * Remove the given category
1004 */
1005 public function remove_category($name)
1006 {
1007 $query = $this->rc->db->query(
1008 "UPDATE " . $this->db_events . "
1009 SET categories=''
1010 WHERE categories=?
1011 AND calendar_id IN (" . $this->calendar_ids . ")",
1012 $name
1013 );
1014
1015 return $this->rc->db->affected_rows($query);
1016 }
1017
1018 /**
1019 * Update/replace a category
1020 */
1021 public function replace_category($oldname, $name, $color)
1022 {
1023 $query = $this->rc->db->query(
1024 "UPDATE " . $this->db_events . "
1025 SET categories=?
1026 WHERE categories=?
1027 AND calendar_id IN (" . $this->calendar_ids . ")",
1028 $name,
1029 $oldname
1030 );
1031
1032 return $this->rc->db->affected_rows($query);
1033 }
1034
1035 }
1036
This page took 0.192595 seconds and 6 git commands to generate.