How to: Export recurring events using a modified ical.php


#1

I thought I would share this code since the folks here at DHTMLX have really provided a great calendaring tool and outstanding customer support for us WordPress developers.

The default ical.php file that is used for exporting scheduled events does not handle recurring or re-scheduled events. Using DHTMLX ical.php as a source, I developed ical_mod.php (see code below) by re-using existing queries in schedulerhelper.php. The modified queries (courtesy of Ilya) in my version of schedulerhelper.php include re-scheduled events since you want all changes to recurring events. Use these modified queries at your own risk since your needs may be entirely different.

Regarding the ics output file, I use a custom field titled “agenda_url” for my events and that field is included in my export file. I also added “LOCATION” to the ics file since it was missing in the original ical.php and I changed the data source for some of the output fields.

You can view the export feature on the right hand sidebar here: sloworkforce.com

[code]<?php

require(’…/…/…/wp-config.php’);

function makeIcal() {
global $wpdb;
$query = ‘SET NAMES utf8’;
$wpdb->query($query);
if (isset($wpdb->base_prefix)) {
$prefix = $wpdb->base_prefix;
} else {
$prefix = $wpdb->prefix;
}
// Set up query start/end dates and get all events scheduled out 1 year
date_default_timezone_set(get_option(‘timezone_string’));
$today = mktime(0, 0, 0, date(“m”), date(“d”), date(“y”));
$start = date(‘Y-m-d H:i:s’, $today);
$endd_12_mos = mktime(0, 0, 0, date(“m”), date(“d”), date(“y”)+1);
$endd = date(‘Y-m-d H:i:s’, $endd_12_mos);
date_default_timezone_set(get_option(‘timezone_string’));
// Use existing modified query for scheduled events. Modified query accounts for re-scheduled events that
// are dropped in the original scheduler query.
$dates = new SchedulerHelper($wpdb->dbh, $prefix.‘events_rec’);
$events = $dates->get_dates($start, $endd);

$blogName = get_option('blogname');
$blogName = str_replace("\r\n", " ", $blogName);
$blogName = str_replace("\n", " ", $blogName);
$timezone = get_option('timezone_string');
// Build iCal file
$ics = "BEGIN:VCALENDAR\r\nPRODID: dhtmlxScheduler\r\nVERSION: 2.0\r\nCALSCALE:GREGORIAN\r\nMETHOD:PUBLISH\r\nX-WR-CALNAME:".$blogName."\r\nX-WR-TIMEZONE:".$timezone."\r\n";
$event = array();
for ($i = 0; $i < count($events); $i ++) {
	$event = $events[$i];
	$start_date = makeTime($event[start_date]);
	$end_date = makeTime($event[end_date]);
    $timest_end = date_parse($event[end_date]);
    $timest_end = mktime($timest_end['hour'], $timest_end['minute'], $timest_end['second'], $timest_end['month'], $timest_end['day'], $timest_end['year']);

    if ($timest_end < time()) {
        $status = 'CANCELLED';
    } else {
        $status = 'CONFIRMED';
    }
    
    if (!empty($event[agenda_url])){
        $uploads_url_path = get_bloginfo('wpurl') . "/downloadfile.php?file=";  
        $event[agenda_url] = $uploads_url_path.$event[agenda_url];
    }
	$dsc = $event[text];
	$uid = md5($event[event_id].time());
	$ics .= "BEGIN:VEVENT\r\nDTSTART:".$start_date."\r\nDTEND:".$end_date."\r\nUID:".$uid."\r\nLOCATION:".$event[location_address]."\r\nDESCRIPTION:".$event[agenda_url]."\r\nSTATUS:".$status."\r\nSUMMARY:".$dsc."\r\nTRANSP:OPAQUE\r\nEND:VEVENT\r\n";
}

$ics .= "END:VCALENDAR";
header('Content-type: text/calendar; charset=utf-8');
header("Content-Disposition: attachment; filename=wib_events_calendar.ics");
echo $ics;

}

function makeTime($date) {
$date = str_replace("-", “”, $date);
$date = str_replace(":", “”, $date);
$date = str_replace(" ", “T”, $date);
return $date;
}

makeIcal();

?>
[/code]

[code]function get_dates($date_start, $date_end) {
$this->date_start = $date_start;
$this->date_end = $date_end;
$date_start = date_parse($date_start);
$this->date_start_ts = mktime($date_start[‘hour’], $date_start[‘minute’], $date_start[‘second’], $date_start[‘month’], $date_start[‘day’], $date_start[‘year’]);
$date_end = date_parse($date_end);
$this->date_end_ts = mktime($date_end[‘hour’], $date_end[‘minute’], $date_end[‘second’], $date_end[‘month’], $date_end[‘day’], $date_end[‘year’]);

	$final = array();
	$updates = Array();
	$query = "SELECT * FROM ".$this->table_name." WHERE `rec_type`='none' OR (`rec_type`='' AND `event_length`!='0')";
	$res = mysql_query($query, $this->connect);
	for ($i = 0; $i < mysql_num_rows($res); $i++) {
		$event = mysql_fetch_assoc($res);
		$updates[mysql_result($res, $i, 'event_length')] = $event;
	}

	$query = "SELECT * FROM ".$this->table_name." WHERE (`start_date`<='".($this->date_end)."' AND `end_date`>='".($this->date_start)."' AND ((`event_pid`='0') OR (`event_pid`!='0' AND `event_length`<'".$this->date_start_ts."')))";
	$res = mysql_query($query, $this->connect);

	while ($data = mysql_fetch_assoc($res)) {
		$event_cur = new SchedulerDate($data, $updates, $this->date_start);
		$event_cur->transpositor($this->date_start_ts);
		$final_temp = $event_cur->date_generator($this->date_start_ts, $this->date_end_ts);
		foreach ($final_temp as $v) {
			$final[] = $v;
		}
	}
	return $final;
}

[/code]
You will need to modify this function as well in SchedulerHelper.php:

[code]function get_correct_date($cur_date, $date_start, $date_end) {
$final = array();
$day = 606024;

	if (count($this->days)) {
		$week_day = $this->get_day_of_week($cur_date);
		$cur_date -= ((--$week_day)*$day);
		$cur_date = $this->get_days($cur_date, $date_start, $date_end);
	} elseif (($this->coun2 != '')&&($this->day != '')) {
			$cur_date = $this->get_day($cur_date, $date_start, $date_end);
		} else {
			if (($cur_date > $date_start)&&($cur_date < $date_end)) {
				$changes = $this->get_updates($cur_date);
				if ($changes == 0) {
					if (date("Y-m-d H:i:s", $cur_date) > $this->date_start_general) {
						$ev = $this->orig_event;
						$ev['start_date'] = date("Y-m-d H:i:s", $cur_date);
						$ev['end_date'] = date("Y-m-d H:i:s", $cur_date + $this->event_length);
						$this->result[] = $ev;

// $this->result[] = array(‘event_id’ => $this->ev_id, ‘start_date’ => date(“Y-m-d H:i:s”, $cur_date), ‘end_date’ => date(“Y-m-d H:i:s”, $cur_date + $this->event_length), ‘text’ => $this->text, ‘rec_type’ => $this->rec_type, ‘event_pid’ => $this->ev_pid, ‘event_length’ => $this->event_length );
}
} elseif ($changes != 1) {
$this->result[] = $changes;
}
}
}
}
[/code]


#2

Hello,

Thank you. Topic was set to “Sticky”.

Best regards,
Ilya


#3

Thank you for posting this. I have a problem, how to export the “label” of a custom list and usernames using iCal. I have tried several codes with getlabel or str_replace but no luck. For instance I query the values of custom fields and user. What I get in $dsc is only user id (numbers) and text values but not labels or usernames. Any help would be greatly appreciated.

[code]<?php

require(’…/…/…/wp-config.php’);

function makeIcal() {
global $wpdb;
$query = ‘SET NAMES utf8’;
$wpdb->query($query);
if (isset($wpdb->base_prefix)) {
$prefix = $wpdb->base_prefix;
} else {
$prefix = $wpdb->prefix;
}

if (isset($_GET['oncoming'])) {
	$query = "SELECT `event_id`, `start_date`, `end_date`, `text`, `user`, `cond`, `rep` FROM `".$prefix."events_rec` WHERE `rec_type`='' AND `event_pid`='0' AND `end_date` > NOW()";
} else {
	$query = "SELECT `event_id`, `start_date`, `end_date`, `text`, `user`, `cond`, `rep` FROM `".$prefix."events_rec` WHERE `rec_type`='' AND `event_pid`='0'";
}
$events = $wpdb->get_results($query);
$blogName = get_option('blogdescription');
$blogName = str_replace("\r\n", " ", $blogName);
$blogName = str_replace("\n", " ", $blogName);
$timezone = get_option('timezone_string');

$ics = "BEGIN:VCALENDAR\r\nPRODID: dhtmlxScheduler\r\nVERSION: 2.0\r\nCALSCALE:GREGORIAN\r\nMETHOD:PUBLISH\r\nX-WR-CALNAME:".$blogName."\r\nX-WR-TIMEZONE:".$timezone."\r\n";

for ($i = 0; $i < count($events); $i ++) {
	$event = $events[$i];
	$start_date = makeTime($event->start_date);
	$end_date = makeTime($event->end_date);
	$timest_end = date_parse($event->end_date);
	$timest_end = mktime($timest_end['hour'], $timest_end['minute'], $timest_end['second'], $timest_end['month'], $timest_end['day'], $timest_end['year']);

	if ($timest_end < time()) {
		$status = 'CANCELLED';
	} else {
		$status = 'CONFIRMED';
	}

	$dsc = "($event->text), ($event->user), ($event->cond), ($event->rep)";
	$uid = md5($event->event_id.time());
	$ics .= "BEGIN:VEVENT\r\nDTSTART:".$start_date."\r\nDTEND:".$end_date."\r\nUID:".$uid."\r\nDESCRIPTION:".$dsc."\r\nSTATUS:".$status."\r\nSUMMARY:".$dsc."\r\nTRANSP:OPAQUE\r\nEND:VEVENT\r\n";
}


$ics .= "END:VCALENDAR";
header('Content-type: text/calendar; charset=utf-8');
header("Content-Disposition: attachment; filename=dhtmlxScheduler.ics");
echo $ics;

}

function makeTime($date) {
$date = str_replace("-", “”, $date);
$date = str_replace(":", “”, $date);
$date = str_replace(" ", “T”, $date);
return $date;
}

makeIcal();

?>[/code]