We are using phpScheduleIt for booking of a number of rooms and other resources.
The attached code exports calenders for different resources in separate ical files.
ICalExports.php is called from crontab, to get frequently updated ICal files. These ical-files are published in the web-server,
and can be accessed by Outlook, Evolution and alike through webcal://severname/cal/filename.ics
The diff-patch below is against v1.2.12 and adds additional queries; the changes in ./lib/icalendar/ICalReservationFormatter.php
add "Resources" to the exported Description field and converts multi-line entries in such a way that Outlook import works without errors.
Cheers,
Alois
Code for ICalExports.php:
<?php
/**
* Provides ability to generate an iCalendar export file for different resources,
* This script can be called from the server administrator with php icalexports.php
* and might be called from crontab in order to update the ical files in regular intervals.
* @author Nick Korbel <lqqkout13@users.sourceforge.net>
* @version 05-07-06
* @package phpScheduleIt.iCalendar
*
* Copyright (C) 2003 - 2007 phpScheduleIt
* Copyright (C) 2011 Alois Schloegl, IST Austria
* License: GPL, see LICENSE
*/
require_once('../lib/pagebase/download/StreamDownload.php');
require_once('../lib/icalendar/ICalExport.php');
require_once('../lib/icalendar/ICalReservationFormatter.php');
require_once('../lib/vcalendar/VCalExport.php');
require_once('../lib/vcalendar/VCalReservationFormatter.php');
require_once('../lib/ReservationSearch.php');
require_once('../lib/Auth.class.php');
define('ICAL', 'ical');
define('VCAL','vcal');
/*
if (!Auth::is_logged_in()) {
CmnFns::redirect('../ctrlpnl.php', 1, false);
}
*/
$path = '/var/www/cal/';
$ext = '.ics';
###################################### Schedule - grouped ##############################
# getReservationsScheduleIdResults($id, $filename);
getReservationsScheduleIdResults('sc14cdd0fc3ceaee', 'Rooms.LAB.Building');
getReservationsScheduleIdResults('sc14cb587394f78e', 'Imaging+Microscopy');
getReservationsScheduleIdResults('sc14cb6ba709a8fe', 'Rooms.Central.Building');
getReservationsScheduleIdResults('sc14cefa3e0c0990', 'Rooms.Admin+FM.Building');
getReservationsScheduleIdResults('sc14d74f390bda86', 'PreClinical.Facility');
###################################### Additional Resources ##############################
# Remark: If "resourceprovider" matches the "Assessory Name" of several entries, all schedules are combined
getReservationsAdditionalResourcesResults('IT', 'IT');
getReservationsAdditionalResourcesResults('Catering', 'Catering');
###################################### Resources ##############################
getReservationsResource('sc14cdd05075162e', 'Room.Neuroscience');
getReservationsResource('sc14cb5881dabf04', 'Ekaterina.Papusheva');
getReservationsResource('sc14d5e31d907b0a', 'Ballroom');
getReservationsResource('sc14d5e31653fcc6', 'Mondi_3');
getReservationsResource('sc14d5e313639f2e', 'Mondi_1');
getReservationsResource('sc14d5e31500b142', 'Mondi_2');
getReservationsResource('sc14cd81f4b4da2b', 'Meeting_Room_1');
getReservationsResource('sc14d5e31f67cde8', 'Lecture_Hall');
getReservationsResource('sc14cd81f9ea8501', 'Evolutionary_Biology_Room');
getReservationsResource('sc14d3552439b5d1', 'I04.017_MP_TRIM_LaVision');
getReservationsResource('sc14ce29e7c81a0b', 'I04.U19_Leica_SP5_upright_confocal');
getReservationsResource('sc14ce4d7e0ce9b5', 'I04.U19_Leica_SP5_inverted_confocal');
getReservationsResource('sc14ce4dbe518185', 'I04.017_Nicon_Eclipse');
getReservationsResource('sc14cefa48a4780c', 'Big_Meeting_Room_1st_Floor');
getReservationsResource('sc14cefa4d21f764', 'Meeting_Room_EG');
getReservationsResource('sc14cefa51e7cb0a', 'Meeting_Room_FM1');
getReservationsResource('sc14d1063a47c21f', 'Experimental_Bio_Room_2');
getReservationsResource('sc14d2167c078041', 'I04.U17_TIRF-FRAP');
getReservationsResource('sc14d21a9461b460', 'AFM_JPKr');
getReservationsResource('sc14d5e32a34449b', 'Foyer');
echo("ICal export finished\r\n");
return;
function getReservationsResource($schedueleid, $filename) {
$search = new ReservationSearch(new ReservationSearchDB());
$results = array();
$results = $search->getReservationsResource($schedueleid);
$e = new ICalExport($results);
global $path, $ext;
$fid = fopen($path.$filename.$ext, 'w');
fwrite($fid, $e->toString($filename));
fclose($fid);
}
function getReservationsAdditionalResourcesResults($resourceprovider, $filename) {
$search = new ReservationSearch(new ReservationSearchDB());
$results = array();
$results = $search->getReservationsAdditionalResources($resourceprovider);
$e = new ICalExport($results);
global $path, $ext;
$fid = fopen($path.$filename.$ext, 'w');
fwrite($fid, $e->toString($filename));
fclose($fid);
}
function getReservationsScheduleIdResults($resourceid, $filename) {
$search = new ReservationSearch(new ReservationSearchDB());
$results = array();
$results = $search->getReservationsScheduleId($resourceid);
$e = new ICalExport($results);
global $path, $ext;
$fid = fopen($path.$filename.$ext, 'w');
fwrite($fid, $e->toString($filename));
fclose($fid);
}
function getExport() {
$results = getResults();
if (isset($_GET['type']) && $_GET['type'] == VCAL) {
return new VCalExport($results);
}
else {
return new ICalExport($results);
}
}
function getExtension() {
if (isset($_GET['type']) && $_GET['type'] == VCAL) {
return 'vcs';
}
else {
return 'ics';
}
}
function getResults() {
$search = new ReservationSearch(new ReservationSearchDB());
$results = array();
if (isset($_GET['resid'])) {
$results = $search->getReservation(htmlspecialchars($_GET['resid']));
}
elseif (isset($_GET['resourceid'])) {
$results = $search->getReservationsResource(htmlspecialchars($_GET['resourceid']));
}
elseif (isset($_GET['resourceprovider'])) {
$results = $search->getReservationsAdditionalResources(htmlspecialchars($_GET['resourceprovider']));
}
elseif (isset($_GET['scheduleid'])) {
$results = $search->getReservationsScheduleId(htmlspecialchars($_GET['scheduleid']));
}
else {
$start = null;
$end = null;
$userid = Auth::getCurrentID();
if ( isset($_GET['start_date']) && !empty($_GET['start_date']) ) {
$start_date = htmlspecialchars($_GET['start_date']);
$dates = explode(INTERNAL_DATE_SEPERATOR, $start_date);
$start = mktime(0, 0, 0, $dates[0], $dates[1], $dates[2]);
}
if ( isset($_GET['end_date']) && !empty($_GET['end_date']) ) {
$end_date = htmlspecialchars($_GET['end_date']);
$dates = explode(INTERNAL_DATE_SEPERATOR, $end_date);
$end = mktime(0, 0, 0, $dates[0], $dates[1], $dates[2]);
}
CmnFns::write_log("get liefert " . $_GET['machid']);
$results = $search->getReservations($userid, $start, $end);
}
return $results;
}
?>
The following patch
diff -rc /usr/local/src/phpscheduleit/1.2.12/lib/db/ReservationSearchDB.class.php ./lib/db/ReservationSearchDB.class.php
*** /usr/local/src/phpscheduleit/1.2.12/lib/db/ReservationSearchDB.class.php 2011-03-10 22:39:33.000000000 +0100
--- ./lib/db/ReservationSearchDB.class.php 2011-03-14 10:23:22.000000000 +0100
***************
*** 6,11 ****
--- 6,12 ----
* @package phpScheduleIt.ReservationSearch
*
* Copyright (C) 2003 - 2007 phpScheduleIt
+ * Copyright (C) 2011 Alois Schloegl, IST Austria
* License: GPL, see LICENSE
*/
***************
*** 85,89 ****
--- 86,313 ----
return $return;
}
+
+ function getReservationsResource($machid, $start, $end) {
+ $return = array();
+ $values = array($machid);
+
+ $query = 'SELECT r.*, rem.reminder_time, rem.reminderid FROM ' . $this->get_table(TBL_RESERVATIONS) . ' r'
+ . ' LEFT JOIN ' . $this->get_table(TBL_REMINDERS) . ' rem ON r.resid = rem.resid'
+ . ' WHERE r.machid = ? ';
+
+ if ($start != null) {
+ $values[] = $start->date;
+ $values[] = $start->date;
+ $values[] = $start->time;
+ $query .= ' AND (r.start_date >= ? OR (r.start_date = ? AND r.starttime >= ?))';
+ }
+
+ if ($end != null) {
+ $values[] = $end->date;
+ $values[] = $end->date;
+ $values[] = $end->time;
+ $query .= ' AND (r.end_date <= ? OR (r.end_date = ? AND r.endtime <= ?))';
+ }
+
+ $result = $this->db->query($query, $values);
+ $this->check_for_error($result);
+
+ while ($rs = $result->fetchRow()) {
+ $res = new ReservationResult();
+
+ $res->id = $rs['resid'];
+ $res->start_date = $rs['start_date'];
+ $res->end_date = $rs['end_date'];
+ $res->start = $rs['starttime'];
+ $res->end = $rs['endtime'];
+ $res->resource = new Resource($rs['machid']);
+ $res->resource->db = null;
+ $res->created = $rs['created'];
+ $res->modified = $rs['modified'];
+ $res->parentid = $rs['parentid'];
+ $res->summary = $rs['summary'];
+ $res->scheduleid = $rs['scheduleid'];
+ $res->is_pending = $rs['is_pending'];
+ $res->is_participant = $rs['owner'] == 0;
+
+ $reminder = new Reminder($rs['reminderid']);
+ $reminder->set_reminder_time($rs['reminder_time']);
+ $res->reminderid = $rs['reminderid'];
+ $res->reminder_minutes_prior = $reminder->getMinutuesPrior($res);
+
+ $users = $this->get_res_users($res->id);
+
+ for ($i = 0; $i < count($users); $i++) {
+ if ($users[$i]['owner'] == 1) {
+ $res->user = new User($users[$i]['memberid']);
+ $res->user->db = null;
+ break;
+ }
+ else {
+ $res->users[] = $users[$i];
+ }
+
+ $res->users[] = $users[$i];
+ }
+
+ $res->resources = $this->get_sup_resources($res->id);
+
+ $return[] = $res;
+ }
+
+ $result->free();
+
+ return $return;
+ }
+
+ function getReservationsAdditionalResources($resourceprovider, $start, $end) {
+ $return = array();
+ $values = array();
+
+ $query = 'SELECT DISTINCT r.*, rem.reminder_time, rem.reminderid
+ FROM reservations r
+ LEFT JOIN reminders rem ON r.resid = rem.resid
+ INNER JOIN reservation_resources ON reservation_resources.resid = r.resid
+ INNER JOIN additional_resources ON additional_resources.resourceid = reservation_resources.resourceid
+ WHERE additional_resources.name LIKE \'' . $resourceprovider . '%\'
+ ';
+
+ if ($start != null) {
+ $values[] = $start->date;
+ $values[] = $start->date;
+ $values[] = $start->time;
+ $query .= ' AND (r.start_date >= ? OR (r.start_date = ? AND r.starttime >= ?))';
+ }
+
+ if ($end != null) {
+ $values[] = $end->date;
+ $values[] = $end->date;
+ $values[] = $end->time;
+ $query .= ' AND (r.end_date <= ? OR (r.end_date = ? AND r.endtime <= ?))';
+ }
+
+ $result = $this->db->query($query, $values);
+ $this->check_for_error($result);
+
+ while ($rs = $result->fetchRow()) {
+ $res = new ReservationResult();
+
+ $res->id = $rs['resid'];
+ $res->start_date = $rs['start_date'];
+ $res->end_date = $rs['end_date'];
+ $res->start = $rs['starttime'];
+ $res->end = $rs['endtime'];
+ $res->resource = new Resource($rs['machid']);
+ $res->resource->db = null;
+ $res->created = $rs['created'];
+ $res->modified = $rs['modified'];
+ $res->parentid = $rs['parentid'];
+ $res->summary = $rs['summary'];
+ $res->scheduleid = $rs['scheduleid'];
+ $res->is_pending = $rs['is_pending'];
+ $res->is_participant = $rs['owner'] == 0;
+
+ $reminder = new Reminder($rs['reminderid']);
+ $reminder->set_reminder_time($rs['reminder_time']);
+ $res->reminderid = $rs['reminderid'];
+ $res->reminder_minutes_prior = $reminder->getMinutuesPrior($res);
+
+
+ $users = $this->get_res_users($res->id);
+
+ for ($i = 0; $i < count($users); $i++) {
+ if ($users[$i]['owner'] == 1) {
+ $res->user = new User($users[$i]['memberid']);
+ $res->user->db = null;
+ break;
+ }
+ else {
+ $res->users[] = $users[$i];
+ }
+ $res->users[] = $users[$i];
+ }
+
+ $res->resources = $this->get_sup_resources($res->id);
+
+ $return[] = $res;
+ }
+
+ $result->free();
+
+ return $return;
+ }
+
+ function getReservationsScheduleId($scheduleid, $start, $end) {
+ $return = array();
+ $values = array($scheduleid);
+
+ $query = 'SELECT r.*, rem.reminder_time, rem.reminderid FROM ' . $this->get_table(TBL_RESERVATIONS) . ' r
+ LEFT JOIN ' . $this->get_table(TBL_REMINDERS) .' rem ON r.resid = rem.resid
+ INNER JOIN resources ON resources.machid = r.machid
+ WHERE resources.scheduleid = ?
+ ';
+
+ if ($start != null) {
+ $values[] = $start->date;
+ $values[] = $start->date;
+ $values[] = $start->time;
+ $query .= ' AND (r.start_date >= ? OR (r.start_date = ? AND r.starttime >= ?))';
+ }
+
+ if ($end != null) {
+ $values[] = $end->date;
+ $values[] = $end->date;
+ $values[] = $end->time;
+ $query .= ' AND (r.end_date <= ? OR (r.end_date = ? AND r.endtime <= ?))';
+ }
+
+ $result = $this->db->query($query, $values);
+ $this->check_for_error($result);
+
+ while ($rs = $result->fetchRow()) {
+ $res = new ReservationResult();
+
+ $res->id = $rs['resid'];
+ $res->start_date = $rs['start_date'];
+ $res->end_date = $rs['end_date'];
+ $res->start = $rs['starttime'];
+ $res->end = $rs['endtime'];
+ $res->resource = new Resource($rs['machid']);
+ $res->resource->db = null;
+ $res->created = $rs['created'];
+ $res->modified = $rs['modified'];
+ $res->parentid = $rs['parentid'];
+ $res->summary = $rs['summary'];
+ $res->scheduleid = $rs['scheduleid'];
+ $res->is_pending = $rs['is_pending'];
+ $res->is_participant = $rs['owner'] == 0;
+
+ $reminder = new Reminder($rs['reminderid']);
+ $reminder->set_reminder_time($rs['reminder_time']);
+ $res->reminderid = $rs['reminderid'];
+ $res->reminder_minutes_prior = $reminder->getMinutuesPrior($res);
+
+ $users = $this->get_res_users($res->id);
+ for ($i = 0; $i < count($users); $i++) {
+ if ($users[$i]['owner'] == 1) {
+ $res->user = new User($users[$i]['memberid']);
+ $res->user->db = null;
+ break;
+ }
+ else {
+ $res->users[] = $users[$i];
+ }
+ $res->users[] = $users[$i];
+ }
+
+ $res->resources = $this->get_sup_resources($res->id);
+
+ $return[] = $res;
+ }
+
+ $result->free();
+
+ return $return;
+ }
}
?>
diff -rc /usr/local/src/phpscheduleit/1.2.12/lib/icalendar/ICalExport.php ./lib/icalendar/ICalExport.php
*** /usr/local/src/phpscheduleit/1.2.12/lib/icalendar/ICalExport.php 2011-03-10 22:39:33.000000000 +0100
--- ./lib/icalendar/ICalExport.php 2011-03-07 12:02:53.000000000 +0100
***************
*** 31,48 ****
return $builder->toString();
}
! function toString() {
$builder = new StringBuilder();
! $builder->append($this->getHeader());
$builder->append($this->_parse());
$builder->append($this->getFooter());
! return $builder->toString();
}
! function getHeader() {
global $conf;
! return "BEGIN:VCALENDAR\r\nCALSCALE:GREGORIAN\r\nMETHOD:PUBLISH\r\nPRODID:-//phpScheduleIt//{$conf['app']['version']}//EN\r\nX-WR-CALNAME;VALUE=TEXT:phpScheduleIt\r\nVERSION:2.0\r\n";
}
function getFooter() {
--- 31,48 ----
return $builder->toString();
}
! function toString($title = "phpScheduleIt") {
$builder = new StringBuilder();
! $builder->append($this->getHeader($title));
$builder->append($this->_parse());
$builder->append($this->getFooter());
! return utf8_encode($builder->toString());
}
! function getHeader($title) {
global $conf;
! return "BEGIN:VCALENDAR\r\nCALSCALE:GREGORIAN\r\nMETHOD:PUBLISH\r\nPRODID:-//phpScheduleIt//{$conf['app']['version']}//EN\r\nX-WR-CALNAME;VALUE=TEXT:".$title."\r\nVERSION:2.0\r\n";
}
function getFooter() {
diff -rc /usr/local/src/phpscheduleit/1.2.12/lib/icalendar/ICalReservationFormatter.php ./lib/icalendar/ICalReservationFormatter.php
*** /usr/local/src/phpscheduleit/1.2.12/lib/icalendar/ICalReservationFormatter.php 2011-03-10 22:39:33.000000000 +0100
--- ./lib/icalendar/ICalReservationFormatter.php 2011-03-14 10:30:14.000000000 +0100
***************
*** 6,11 ****
--- 6,12 ----
* @package phpScheduleIt.iCalendar
*
* Copyright (C) 2003 - 2007 phpScheduleIt
+ * Copyright (C) 2011 Alois Schloegl, IST Austria
* License: GPL, see LICENSE
*/
***************
*** 33,38 ****
--- 34,40 ----
$builder->append($this->formatOwner());
$builder->append($this->formatParticipants());
$builder->append($this->formatSummary());
+ $builder->append($this->formatDescription());
$builder->append($this->formatReminder());
$builder->append($this->formatResources());
$builder->append("END:VEVENT\r\n");
***************
*** 109,115 ****
$builder = new StringBuilder();
$summary = (!empty($this->_reservation->summary)) ? $this->_reservation->summary : $this->_reservation->resource->properties['name'];
! $builder->append("SUMMARY:$summary\r\n");
return $builder->toString();
}
--- 111,141 ----
$builder = new StringBuilder();
$summary = (!empty($this->_reservation->summary)) ? $this->_reservation->summary : $this->_reservation->resource->properties['name'];
! $summary = strtok($summary, "\r\n");
!
! return $builder->toString();
! }
!
! function formatDescription() {
! $builder = new StringBuilder();
!
! $desc = (!empty($this->_reservation->summary)) ? $this->_reservation->summary : $this->_reservation->resource->properties['name'];
!
! // parse each line and replace each <CR><LINE> with <space><LINE><CR><LF> in order to avoid errors in Outlook import
! $desc = strtok($this->_reservation->summary,"\r\n");
! $builder->append("DESCRIPTION:$desc\r\n");
! $desc = strtok("\r\n");
! while (!empty($desc)) {
! $builder->append(" $desc\r\n");
! $desc = strtok("\r\n");
! }
!
! // add Resources in Description
! $builder->append(" Resources:{$this->_reservation->resource->properties['name']}\r\n");
!
! for ($i = 0; $i < count($this->_reservation->resources); $i++) {
! $builder->append(" {$this->_reservation->resources[$i]['name']}\r\n");
! }
return $builder->toString();
}
diff -rc /usr/local/src/phpscheduleit/1.2.12/lib/ReservationSearch.php ./lib/ReservationSearch.php
*** /usr/local/src/phpscheduleit/1.2.12/lib/ReservationSearch.php 2011-03-10 22:39:33.000000000 +0100
--- ./lib/ReservationSearch.php 2011-03-14 10:23:10.000000000 +0100
***************
*** 6,11 ****
--- 6,12 ----
* @package phpScheduleIt.ReservationSearch
*
* Copyright (C) 2003 - 2007 phpScheduleIt
+ * Copyright (C) 2011 Alois Schloegl, IST Austria
* License: GPL, see LICENSE
*/
***************
*** 43,47 ****
--- 44,91 ----
return $this->data->getReservations($userid, $start, $end);
}
+
+ function getReservationsResource($resourceid, $start_date = null, $end_date = null) {
+ $start = null;
+ $end = null;
+
+ if ($start_date != null) {
+ $start = Time::getServerTime($start_date, 0);
+ }
+ if ($end_date != null) {
+ $end = Time::getServerTime($end_date, 0);
+ }
+
+ return $this->data->getReservationsResource($resourceid, $start, $end);
+ }
+
+ function getReservationsAdditionalResources($resourceprovider, $start_date = null, $end_date = null) {
+ $start = null;
+ $end = null;
+
+ if ($start_date != null) {
+ $start = Time::getServerTime($start_date, 0);
+ }
+ if ($end_date != null) {
+ $end = Time::getServerTime($end_date, 0);
+ }
+
+ return $this->data->getReservationsAdditionalResources($resourceprovider, $start, $end);
+ }
+
+ function getReservationsScheduleId($scheduleid, $start_date = null, $end_date = null) {
+ $start = null;
+ $end = null;
+
+ if ($start_date != null) {
+ $start = Time::getServerTime($start_date, 0);
+ }
+ if ($end_date != null) {
+ $end = Time::getServerTime($end_date, 0);
+ }
+
+ return $this->data->getReservationsScheduleId($scheduleid, $start, $end);
+ }
+
}
?>
