]>
Commit | Line | Data |
---|---|---|
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 |