function export_pilot_csv($id)
{
/* to be imported to a Palm with:
* pilot-datebook -r csv -f webcalendar-export.txt -w hotsync
*/
$res = export_get_event_entry($id);
echo "uid,attributes,category,untimed,beginDate,beginTime,endDate,endTime,description,note,alarm,advance,advanceUnit,repeatType,repeatForever,repeatEnd,repeatFrequency,repeatDay,repeatWeekdays,repeatWeekstart\n";
while ($row = dbi_fetch_row($res)) {
// uid (long)
echo $row[0], ',';
// attributes (int)
// 128 = 0x80 : Deleted
// 64 = 0x40 : Dirty
// 32 = 0x20 : Busy
// 16 = 0x10 : Secret/Private
echo $row[7] == 'R' ? '16,' : '0,';
// category (int: 0=Unfiled)
echo '0,';
// untimed (int: 0=Appointment, 1=Untimed)
// note: Palm "Untimed" is WebCalendar "AllDay"
if ($row[4] < 0) {
echo '1,', substr($row[3], 0, 4), '-', substr($row[3], 4, 2), '-', substr($row[3], 6, 2), ',00:00:00,', substr($row[3], 0, 4), '-', substr($row[3], 4, 2), '-', substr($row[3], 6, 2), ',00:00:00,';
} else {
echo '0,', pilot_date_time($row[3], $row[4], 0, ','), ',', pilot_date_time($row[3], $row[4], $row[8], ','), ',';
//endDate,endTime
}
//end if ( $row[4] < 0 )
// description (str)
echo '"', preg_replace("/\r?\n/", "\\n", $row[1]), '",';
// note (str)
echo '"', preg_replace("/\r?\n/", "\\n", $row[9]), '",';
// alarm, advance, advanceUnit
// alarm (int: 0=no alarm, 1=alarm)
// FIXME: verify if WebCal. DB interpreted correctly
// advance (int), advanceUnit (int: 0=minutes, 1=hours, 2=days)
// FIXME: better adjust unit
$ext = get_cal_ent_extras($row[0], 'webcal_site_extras', "cal_name = 'Reminder' AND cal_remind = 1");
if ($ext) {
echo '1,', $ext[5], ',0,';
} else {
echo '0,0,0,';
}
// repeat:
// repeatType (int: 0=none, 1=daily, 2=weekly, 3=monthly, 4=monthly/weekday,
// repeatForever (int: 0=not forever, 1=forever) 5=yearly)
// repeatEnd (time)
// repeatFrequency (int)
// repeatDay (int: day# or 0..6=Sun..Sat 1st, 7..13 2nd, 14..20 3rd,
// 21..27 4th, 28-34 last week)
// repeatWeekdays (int: add - 1=Sun,2=Mon,4=Tue,8=Wed,16=Thu,32=Fri,64=Sat)
// repeatWeekstart (int)
$ext = get_cal_ent_extras($row[0], 'webcal_entry_repeats');
if ($ext) {
switch ($ext[1]) {
case 'daily':
$repType = 1;
break;
case 'weekly':
$repType = 2;
break;
case 'monthlyByDate':
$repType = 3;
break;
case 'monthlyByDay':
$repType = 4;
break;
case 'yearly':
$repType = 5;
break;
default:
$repType = 0;
}
} else {
$repType = 0;
}
if ($repType) {
echo $repType, ',';
// repeatType
if ($ext[2]) {
echo '0,', substr($ext[2], 0, 4), '-', substr($ext[2], 4, 2), '-', substr($ext[2], 6, 2), ' 00:00:00,';
} else {
echo '1,,';
}
// repeatForever,repeatEnd
echo $ext[3], ',';
// repeatFrequency
switch ($repType) {
case 2:
// weekly
echo '0,', bindec(strtr(strrev($ext[4]), 'yn', '10')), ",1\n";
break;
case 3:
// monthly/weekday
// repeatDay (0..6=Sun..Sat 1st, 7..13 2nd, 14..20 3rd,
// 21..27 4th, 28-34 last week)
echo floor(substr($row[3], 6, 2) / 7) * 7 + date('w', date_to_epoch($row[3])), ",0,0\n";
break;
case 1:
// daily
// daily
case 4:
// monthly
// monthly
case 5:
// yearly
echo "0,0,0\n";
}
//end switch
} else {
echo "0,0,,0,0,0,0\n";
}
}
//end if ( $repType )
}
function export_ical($id = 'all', $attachment = false)
{
global $publish_fullname, $login, $cal_type, $cat_filter, $vtimezone_data, $use_vtimezone;
$exportId = -1;
$ret = $Vret = $vtimezone_data = $use_vtimezone = '';
$res = export_get_event_entry($id, $attachment);
$entry_array = array();
$count = 0;
while ($entry = dbi_fetch_row($res)) {
$entry_array[$count++] = $entry;
}
dbi_free_result($res);
//abort if no records to output
if ($count == 0) {
return;
}
// Always output something, even if no records come back
// This prevents errors on the iCal client
$ret = "BEGIN:VCALENDAR\r\n";
$title = utf8_encode('X-WR-CALNAME;VALUE=TEXT:' . (empty($publish_fullname) ? $login : translate($publish_fullname)));
$title = str_replace(',', "\\,", $title);
$ret .= "{$title}\r\n";
$ret .= generate_prodid('ics');
$ret .= "VERSION:2.0\r\n";
$ret .= "METHOD:PUBLISH\r\n";
while (list($key, $row) = each($entry_array)) {
$id = $row[0];
$event_uid = generate_uid($id);
$name = $row[1];
$priority = $row[2];
$date = $row[3];
$time = sprintf("%06d", $row[4]);
$status = $row[5];
$create_by = $row[6];
$access = $row[7];
$duration = $row[8];
$description = $row[9];
// New columns to support tasks
$percent = empty($row[10]) ? 0 : $row[10];
$completed = empty($row[11]) ? '' : substr($row[11], 0, 8) . 'T' . sprintf("%06d", substr($row[11], 9, 6));
$due_date = $row[12];
$due_time = $row[13];
$location = $row[14];
$url = $row[15];
$cal_type = $row[16];
$moddate = $row[17];
$modtime = $row[18];
// Figure out Categories
$categories = get_categories_by_id($id, $login);
// Add entry in webcal_import_data if it does not exist.
// Even thought we're exporting, this data needs to be valid
// for proper parsing of response from iCal client.
// If this is the first event that has never been published,
// then create a new import instance to associate with what we are doing.
$sql = 'SELECT wid.cal_external_id
FROM webcal_import_data wid, webcal_entry_user weu
WHERE wid.cal_id = weu.cal_id AND wid.cal_id = ? AND
weu.cal_login = ?';
$res = dbi_execute($sql, array($id, $login));
if ($res) {
if ($row = dbi_fetch_row($res)) {
// event has been published (or imported) before
$event_uid = $row[0];
} else {
if ($exportId < 0) {
// This is first event that has not been published before.
// Create an entry in webcal_import.
// It would be nice if we could put a name in here of who
// or where the remote cal subscription is coming from in case
// they update some of our events. But, I cannot see a way to
// do that.
$exportId = create_import_instance();
}
if ($attachment == false) {
save_uid_for_event($exportId, $id, $event_uid);
}
}
dbi_free_result($res);
}
// get recurrance info
$recurrance = export_recurrence_ical($id);
if (!empty($recurrance)) {
$use_vtimezone = true;
}
/* snippet from RFC2445
The "VTIMEZONE" calendar component MUST be present if the iCalendar
object contains an RRULE that generates dates on both sides of a time
zone shift (e.g. both in Standard Time and Daylight Saving Time)
unless the iCalendar object intends to convey a floating time (See
the section "4.1.10.11 Time" for proper interpretation of floating
time). It can be present if the iCalendar object does not contain
such a RRULE. In addition, if a RRULE is present, there MUST be valid
time zone information for all recurrence instances.
However, this is not possible to implement at this time
*/
// if Categories were selected as an export filter, then abort this
// event if it does not contain that category
if (!empty($cat_filter)) {
if (count($categories) == 0 || !array_key_exists($cat_filter, $categories)) {
continue;
}
}
if ($cal_type == 'E' || $cal_type == 'M') {
$exporting_event = true;
/* Start of event */
$Vret .= "BEGIN:VEVENT\r\n";
} else {
if ($cal_type == 'T' || $cal_type == 'N') {
$exporting_event = false;
/* Start of VTODO */
$Vret .= "BEGIN:VTODO\r\n";
} else {
if ($cal_type == 'J' || $cal_type == 'O') {
$exporting_event = false;
/* Start of VJOURNAL */
$Vret .= "BEGIN:VJOURNAL\r\n";
}
}
}
/* UID of the event (folded to 76 char) */
$array = export_fold_lines("UID:{$event_uid}");
while (list($key, $value) = each($array)) {
$Vret .= "{$value}\r\n";
}
$Vret .= 'LAST-MODIFIED:' . export_get_utc_date($moddate, $modtime) . "\r\n";
$name = preg_replace("/\r/", ' ', $name);
// escape,; \ in octal ascii
$name = addcslashes($name, ",;\\");
$description = preg_replace("/\r/", ' ', $description);
$description = addcslashes($description, ",;\\");
$description = str_replace(chr(10), chr(92) . chr(110), $description);
/* SUMMARY of the event (folded to 76 char) */
$name = 'SUMMARY:' . $name;
$array = export_fold_lines($name, 'utf8');
while (list($key, $value) = each($array)) {
$Vret .= "{$value}\r\n";
}
/* DESCRIPTION if any (folded to 76 char) */
if ($description != '') {
$description = 'DESCRIPTION:' . $description;
$array = export_fold_lines($description, 'utf8');
while (list($key, $value) = each($array)) {
$Vret .= "{$value}\r\n";
}
}
/* LOCATION if any (folded to 76 char) */
if ($location != '') {
$location = 'LOCATION:' . $location;
$array = export_fold_lines($location, 'utf8');
while (list($key, $value) = each($array)) {
$Vret .= "{$value}\r\n";
}
}
/* URL if any (folded to 76 char) */
if ($url != '') {
$url = 'URL:' . $url;
$array = export_fold_lines($url, 'utf8');
while (list($key, $value) = each($array)) {
$Vret .= "{$value}\r\n";
}
}
/* CATEGORIES if any (folded to 76 char) */
if (isset($categories) && count($categories)) {
$categories = 'CATEGORIES:' . implode(',', $categories);
$array = export_fold_lines($categories, 'utf8');
while (list($key, $value) = each($array)) {
$Vret .= "{$value}\r\n";
}
}
/* CLASS either "PRIVATE", "CONFIDENTIAL", or "PUBLIC" (the default) */
if ($access == 'R') {
$Vret .= "CLASS:PRIVATE\r\n";
} else {
if ($access == 'C') {
$Vret .= "CLASS:CONFIDENTIAL\r\n";
} else {
$Vret .= "CLASS:PUBLIC\r\n";
}
}
/* STATUS */
if ($cal_type == 'E' || $cal_type == 'M') {
if ($status == 'A') {
$Vret .= "STATUS:CONFIRMED\r\n";
} else {
if ($status == 'W') {
$Vret .= "STATUS:TENTATIVE\r\n";
} else {
if ($status == 'D') {
$Vret .= "STATUS:CANCELLED\r\n";
}
}
}
} else {
if ($cal_type == 'T' || $cal_type == 'N') {
if ($status == 'A' && empty($completed)) {
$Vret .= "STATUS:IN-PROCESS\r\n";
} else {
if ($status == 'A') {
$Vret .= "STATUS:COMPLETED\r\n";
} else {
if ($status == 'W') {
$Vret .= "STATUS:NEEDS-ACTION\r\n";
} else {
if ($status == 'D') {
$Vret .= "STATUS:CANCELLED\r\n";
}
}
}
}
}
}
// ATTENDEE of the event
$attendee = export_get_attendee($id, 'ical');
$attendcnt = count($attendee);
for ($i = 0; $i < $attendcnt; $i++) {
$attendee[$i] = export_fold_lines($attendee[$i], 'utf8');
while (list($key, $value) = each($attendee[$i])) {
$Vret .= "{$value}\r\n";
}
}
/* Time - all times are utc */
$Vret .= export_time($date, $duration, $time, 'ical', $cal_type);
// VTODO specific items
$task_complete = false;
if ($cal_type == 'T' || $cal_type == 'N') {
$Vret .= 'DUE:' . $due_date . 'T' . sprintf("%06d", $due_time) . "Z\r\n";
if (!empty($completed)) {
$Vret .= 'COMPLETED:' . $completed . "\r\n";
$task_complete = true;
}
$Vret .= 'PERCENT-COMPLETE:' . $percent . "\r\n";
}
/* Recurrence */
$Vret .= $recurrance;
/* handle alarms */
$Vret .= export_alarm_ical($id, $date, $description, $task_complete);
if ($cal_type == 'E' || $cal_type == 'M') {
/* End of event */
$Vret .= "END:VEVENT\r\n";
} else {
if ($cal_type == 'T' || $cal_type == 'N') {
/* Start of VTODO */
$Vret .= "END:VTODO\r\n";
} else {
if ($cal_type == 'J' || $cal_type == 'O') {
/* Start of VJOURNAL */
$Vret .= "END:VJOURNAL\r\n";
}
}
}
}
/* VTIMEZONE Set in export_time () if needed */
$ret .= $vtimezone_data . $Vret;
$ret .= "END:VCALENDAR\r\n";
// attachment will be true if called during email creation
if (!$attachment) {
echo $ret;
} else {
return $ret;
}
}
