Welcome to Geeklog, Anonymous Thursday, November 21 2024 @ 06:26 pm EST
Geeklog Forums
vCal format calendar generation.
Anon
Anonymous
I've written some php code that will read the gl_events table and produce a vCal calendar. This means that people can subscribe to a geeklog calendar using iCal, Mozilla Calendar, Outlook, Ximian, etc.
The code is very basic but works, someone with more knowledge of php and geeklog might like to turn this into a plugin.
VERSION:2.0
<?php
/* First we connect with the database */
$dbh = mysql_pconnect("dbserver","dbuser","dbpass");
/* Next we select the database */
mysql_select_db("dbname",$dbh);
/* run the query for all events */
$query="select * from gl_events";
$result=mysql_query($query);
/* How many rows were returned ? */
$returned=mysql_num_rows($result);
/* Lets fetch them and output the vCal data */
if ($returned>0) {
// This gets every returned row, and outputs the vCal data
while($row=mysql_fetch_array($result)) {
/* format event data to vCal */
$vCalDescription = str_replace("r", "\\n", $row["description"]);
$vCalLocation = str_replace("r", "\\n", $row["location"]);
$vCalStart = date("Ymd\THi00", strtotime($row["datestart"]." ".$row["timestart"]) );
$vCalEnd = date("Ymd\THi00", strtotime($row["dateend"]." ".$row["timeend"]) );
/* output the event */
echo("BEGIN:VEVENT\n");
echo("SUMMARY:".$row["title"]."\n");
echo("DESCRIPTION:".$vCalDescription."\n");
echo("DTSTART:".$vCalStart."\n");
echo("LOCATION:".$vCalLocation."\n");
echo("URL;VALUE=URI:".$row["url"]."\n");
echo("DTEND:".$vCalEnd."\n");
echo("END:VEVENT\n");
}
}
/* now we clean up */
mysql_free_result($result);
?>
END:VCALENDAR
It shouldn't be to hard to change this to produce an actual .ics file (if geeklog called this every time an event was edited it would be handy).
The code is very basic but works, someone with more knowledge of php and geeklog might like to turn this into a plugin.
Text Formatted Code
BEGIN:VCALENDARVERSION:2.0
<?php
/* First we connect with the database */
$dbh = mysql_pconnect("dbserver","dbuser","dbpass");
/* Next we select the database */
mysql_select_db("dbname",$dbh);
/* run the query for all events */
$query="select * from gl_events";
$result=mysql_query($query);
/* How many rows were returned ? */
$returned=mysql_num_rows($result);
/* Lets fetch them and output the vCal data */
if ($returned>0) {
// This gets every returned row, and outputs the vCal data
while($row=mysql_fetch_array($result)) {
/* format event data to vCal */
$vCalDescription = str_replace("r", "\\n", $row["description"]);
$vCalLocation = str_replace("r", "\\n", $row["location"]);
$vCalStart = date("Ymd\THi00", strtotime($row["datestart"]." ".$row["timestart"]) );
$vCalEnd = date("Ymd\THi00", strtotime($row["dateend"]." ".$row["timeend"]) );
/* output the event */
echo("BEGIN:VEVENT\n");
echo("SUMMARY:".$row["title"]."\n");
echo("DESCRIPTION:".$vCalDescription."\n");
echo("DTSTART:".$vCalStart."\n");
echo("LOCATION:".$vCalLocation."\n");
echo("URL;VALUE=URI:".$row["url"]."\n");
echo("DTEND:".$vCalEnd."\n");
echo("END:VEVENT\n");
}
}
/* now we clean up */
mysql_free_result($result);
?>
END:VCALENDAR
It shouldn't be to hard to change this to produce an actual .ics file (if geeklog called this every time an event was edited it would be handy).
38
25
Quote
Anon
Anonymous
Ooops, the forum software ate some escape chars and I forgot to put them back in. In the lines containing str_replace there should be before r.
24
28
Quote
Anon
Anonymous
Quote by ronack:It still removed it what should be before r.
Backslash, it should correctly read r (I'm not pressing preview this time since that is when it seemed to vanish).
I've written a better version of this code that produces a .ics file and updates it whenever an event is modified. I'll post it soon.
21
28
Quote
Status: offline
Toholio
Forum User
Newbie
Registered: 07/30/03
Posts: 6
OK, I'm logged in now (since what I'm posting is a bit longer). Here is what I did to get the vCal generation happening automatically. This probably isn't the best way to do it but it does work (for me at least).
Create an empty file called "geeklog.ics" in your /backend folder (remember to make sure php has permission to write to it).
Then, put the following into a file called "icsgen.php" in your /admin folder.
$vCalOutput = "BEGIN:VCALENDAR\nVERSION:1.0\nPRODID:Geeklog\nTZ:+10\n";
/* First we connect with the database */
$dbh = mysql_pconnect("dbserver","dbuser","dbpass");
/* Next we select the database */
mysql_select_db("dbname",$dbh);
/* run the query for all events */
$query="select * from gl_events";
$result=mysql_query($query);
/* How many rows were returned ? */
$returned=mysql_num_rows($result);
/* Lets fetch them and output the vCal data */
if ($returned>0) {
// This gets every returned row, and puts each in a hash
while($row=mysql_fetch_array($result)) {
/* format event data to vCal */
$vCalDescription = str_replace("\r", "\\n", $row["description"]);
$vCalLocation = str_replace("\r", "\\n", $row["location"]);
$vCalStart = date("Ymd\THi00", strtotime($row["datestart"]." ".$row["timestart"]) );
$vCalEnd = date("Ymd\THi00", strtotime($row["dateend"]." ".$row["timeend"]) );
/* output the event */
$vCalOutput = $vCalOutput."BEGIN:VEVENT\n";
$vCalOutput = $vCalOutput."SUMMARY:".$row["title"]."\n";
$vCalOutput = $vCalOutput."DESCRIPTION:".$vCalDescription."\n";
$vCalOutput = $vCalOutput."DTSTART:".$vCalStart."\n";
$vCalOutput = $vCalOutput."LOCATION:".$vCalLocation."\n";
$vCalOutput = $vCalOutput."URL;VALUE=URI:".$row["url"]."\n";
$vCalOutput = $vCalOutput."DTEND:".$vCalEnd."\n";
$vCalOutput = $vCalOutput."END:VEVENT\n";
}
}
/* now we clean up */
mysql_free_result($result);
$vCalOutput = $vCalOutput."END:VCALENDAR";
/* echo($vCalOutput); */
$icsfile=fopen('../backend/geeklog.ics','w');
fputs($icsfile, $vCalOutput);
fclose($icsfile);
?>
Then open up event.php in the /admin folder and add the following code in the function saveevent just after the line beginning "DB_save($_TABLES['events']" and again just after "DB_delete($_TABLES['personal_events']" (its near the end of the file)
include("icsgen.php");
/* END vCal PATCH */
Thats it, edit or delete something in your calendar and the ics file should be updated (but you can always run icsgen.php yourself if you want).
Be sure to let me know if the .ics file isn't compatible with any software that it should be (Outlook is the one most likely to choke)
Create an empty file called "geeklog.ics" in your /backend folder (remember to make sure php has permission to write to it).
Then, put the following into a file called "icsgen.php" in your /admin folder.
Text Formatted Code
<?php$vCalOutput = "BEGIN:VCALENDAR\nVERSION:1.0\nPRODID:Geeklog\nTZ:+10\n";
/* First we connect with the database */
$dbh = mysql_pconnect("dbserver","dbuser","dbpass");
/* Next we select the database */
mysql_select_db("dbname",$dbh);
/* run the query for all events */
$query="select * from gl_events";
$result=mysql_query($query);
/* How many rows were returned ? */
$returned=mysql_num_rows($result);
/* Lets fetch them and output the vCal data */
if ($returned>0) {
// This gets every returned row, and puts each in a hash
while($row=mysql_fetch_array($result)) {
/* format event data to vCal */
$vCalDescription = str_replace("\r", "\\n", $row["description"]);
$vCalLocation = str_replace("\r", "\\n", $row["location"]);
$vCalStart = date("Ymd\THi00", strtotime($row["datestart"]." ".$row["timestart"]) );
$vCalEnd = date("Ymd\THi00", strtotime($row["dateend"]." ".$row["timeend"]) );
/* output the event */
$vCalOutput = $vCalOutput."BEGIN:VEVENT\n";
$vCalOutput = $vCalOutput."SUMMARY:".$row["title"]."\n";
$vCalOutput = $vCalOutput."DESCRIPTION:".$vCalDescription."\n";
$vCalOutput = $vCalOutput."DTSTART:".$vCalStart."\n";
$vCalOutput = $vCalOutput."LOCATION:".$vCalLocation."\n";
$vCalOutput = $vCalOutput."URL;VALUE=URI:".$row["url"]."\n";
$vCalOutput = $vCalOutput."DTEND:".$vCalEnd."\n";
$vCalOutput = $vCalOutput."END:VEVENT\n";
}
}
/* now we clean up */
mysql_free_result($result);
$vCalOutput = $vCalOutput."END:VCALENDAR";
/* echo($vCalOutput); */
$icsfile=fopen('../backend/geeklog.ics','w');
fputs($icsfile, $vCalOutput);
fclose($icsfile);
?>
Then open up event.php in the /admin folder and add the following code in the function saveevent just after the line beginning "DB_save($_TABLES['events']" and again just after "DB_delete($_TABLES['personal_events']" (its near the end of the file)
Text Formatted Code
/* PATCH FOR vCal GENERATION */include("icsgen.php");
/* END vCal PATCH */
Thats it, edit or delete something in your calendar and the ics file should be updated (but you can always run icsgen.php yourself if you want).
Be sure to let me know if the .ics file isn't compatible with any software that it should be (Outlook is the one most likely to choke)
29
19
Quote
Very neat hack Tohollo! I've been contemplating trying something like this but you've gone and done it. Actually, what I'd really like to see is a way to import ical/vcal files so that I don't have to use the web interface to keep things up to date, and also so I can create calendar events "offline" or even on my cell phone (which sync's with my iBook, which could then upload changes to Geeklog, which site user's could be subscribed to....)
This project has code which parses an ical file and displays a page, but I've looked at their parser and it doesn't look very reusable. 
25
27
Quote
wrdickson
Anonymous
I've been doing some gradual munging of this nifty thing to make it work a little better for me. Figured I'd post the differences I've added. I'm new to PHP, so forgive me if I've done something stupid:
I replaced his first $vCalOutput line with the following:
"VERSION:2.0\n" .
"X-WR-CALNAME:VCOS\n" .
"PRODID:-//Geeklog//Version 1.3.8//EN\n" .
"X-WR-TIMEZONE:US/Pacific\n" .
"CALSCALE:GREGORIAN\n" .
"METHOD:PUBLISH\n" .
"BEGIN:VTIMEZONE\n" .
"TZID:US/Pacific\n" .
"LAST-MODIFIED:" . date("Ymd\THi00\Z") . "\n" .
"BEGIN:STANDARD\n" .
"DTSTART:20021027T090000\n" .
"TZOFFSETTO:-0800\n" .
"TZOFFSETFROM:+0000\n" .
"TZNAME:PST\n" .
"END:STANDARD\n" .
"BEGIN:DAYLIGHT\n" .
"DTSTART:20030406T010000\n" .
"TZOFFSETTO:-0700\n" .
"TZOFFSETFROM:-0800\n" .
"TZNAME:PDT\n" .
"END:DAYLIGHT\n" .
"BEGIN:STANDARD\n" .
"DTSTART:20031026T020000\n" .
"TZOFFSETTO:-0800\n" .
"TZOFFSETFROM:-0700\n" .
"TZNAME:PST\n" .
"END:STANDARD\n" .
"BEGIN:DAYLIGHT\n" .
"DTSTART:20040404T010000\n" .
"TZOFFSETTO:-0700\n" .
"TZOFFSETFROM:-0800\n" .
"TZNAME:PDT\n" .
"END:DAYLIGHT\n" .
"END:VTIMEZONE\n";
This helps make the file more useful to an iPod's calendar sync function, which seems to really want the timezone info. Obviously, it'd be smarter to generate the timezone info from Geeklog's timezone setting, but I haven't got around to that.
This replaces the output code (it's not very different):
if ($returned>0) {
// This gets every returned row, and puts each in a hash
while($row=mysql_fetch_array($result)) {
/* format event data to vCal */
$vCalDescription = str_replace("\r", "\\n", $row["description"]);
$vCalLocation = str_replace("\r", "\\n", $row["location"]);
if ($row["allday"]) {
$vCalStart = date("Ymd", strtotime($row["datestart"]));
$vCalEnd = date("Ymd", strtotime($row["dateend"]));
} else {
$vCalStart = date("Ymd\THi00", strtotime($row["datestart"]." ".$row["timestart"]) );
$vCalEnd = date("Ymd\THi00", strtotime($row["dateend"]." ".$row["timeend"]) );
}
/* output the event */
if (!$row["allday"]) {
$vCalOutput = $vCalOutput."BEGIN:VEVENT\n";
$vCalOutput = $vCalOutput."SUMMARY:".$row["title"]."\n";
$vCalOutput = $vCalOutput."DESCRIPTION:".$vCalDescription."\n";
$vCalOutput = $vCalOutput."DTSTART;TZID=US/Pacific:".$vCalStart."\n";
$vCalOutput = $vCalOutput."LOCATION:".$vCalLocation."\n";
$vCalOutput = $vCalOutput."URL;VALUE=URI:".$row["url"]."\n";
$vCalOutput = $vCalOutput."DTEND:".$vCalEnd."\n";
$vCalOutput = $vCalOutput."END:VEVENT\n";
} else {
$dayinc = 1;
while ($vCalStart <= $vCalEnd) {
$vCalOutput = $vCalOutput."BEGIN:VEVENT\n";
$vCalOutput = $vCalOutput."SUMMARY:".$row["title"]."\n";
$vCalOutput = $vCalOutput."DESCRIPTION:".$vCalDescription."\n";
$vCalOutput = $vCalOutput."DTSTART;TZID=US/Pacific:".$vCalStart."\n";
$vCalOutput = $vCalOutput."LOCATION:".$vCalLocation."\n";
$vCalOutput = $vCalOutput."URL;VALUE=URI:".$row["url"]."\n";
$vCalOutput = $vCalOutput."END:VEVENT\n";
$vCalYear = $vCalStart[0] . $vCalStart[1] . $vCalStart[2] . $vCalStart[3];
$vCalMonth = $vCalStart[4] . $vCalStart[5];
$vCalDay = $vCalStart[6] . $vCalStart[7];
$nextday = mktime(0, 0, 0, $vCalMonth, $vCalDay + 1, $vCalYear);
$vCalStart = date("Ymd", $nextday);
}
}
}
}
Two significant changes: first, timezone info is included for events (again, to help with iPod syncing). Second, in the original, a multiday event would appear as one very long event spanning all the hours on your calendar for the days in question. Now, if the event is marked "all day event," it will instead appear as one (or more if necessary) all-day events on the syncing calendar.
I replaced his first $vCalOutput line with the following:
Text Formatted Code
$vCalOutput = "BEGIN:VCALENDAR\n" ."VERSION:2.0\n" .
"X-WR-CALNAME:VCOS\n" .
"PRODID:-//Geeklog//Version 1.3.8//EN\n" .
"X-WR-TIMEZONE:US/Pacific\n" .
"CALSCALE:GREGORIAN\n" .
"METHOD:PUBLISH\n" .
"BEGIN:VTIMEZONE\n" .
"TZID:US/Pacific\n" .
"LAST-MODIFIED:" . date("Ymd\THi00\Z") . "\n" .
"BEGIN:STANDARD\n" .
"DTSTART:20021027T090000\n" .
"TZOFFSETTO:-0800\n" .
"TZOFFSETFROM:+0000\n" .
"TZNAME:PST\n" .
"END:STANDARD\n" .
"BEGIN:DAYLIGHT\n" .
"DTSTART:20030406T010000\n" .
"TZOFFSETTO:-0700\n" .
"TZOFFSETFROM:-0800\n" .
"TZNAME:PDT\n" .
"END:DAYLIGHT\n" .
"BEGIN:STANDARD\n" .
"DTSTART:20031026T020000\n" .
"TZOFFSETTO:-0800\n" .
"TZOFFSETFROM:-0700\n" .
"TZNAME:PST\n" .
"END:STANDARD\n" .
"BEGIN:DAYLIGHT\n" .
"DTSTART:20040404T010000\n" .
"TZOFFSETTO:-0700\n" .
"TZOFFSETFROM:-0800\n" .
"TZNAME:PDT\n" .
"END:DAYLIGHT\n" .
"END:VTIMEZONE\n";
This helps make the file more useful to an iPod's calendar sync function, which seems to really want the timezone info. Obviously, it'd be smarter to generate the timezone info from Geeklog's timezone setting, but I haven't got around to that.
This replaces the output code (it's not very different):
Text Formatted Code
/* Lets fetch them and output the vCal data */if ($returned>0) {
// This gets every returned row, and puts each in a hash
while($row=mysql_fetch_array($result)) {
/* format event data to vCal */
$vCalDescription = str_replace("\r", "\\n", $row["description"]);
$vCalLocation = str_replace("\r", "\\n", $row["location"]);
if ($row["allday"]) {
$vCalStart = date("Ymd", strtotime($row["datestart"]));
$vCalEnd = date("Ymd", strtotime($row["dateend"]));
} else {
$vCalStart = date("Ymd\THi00", strtotime($row["datestart"]." ".$row["timestart"]) );
$vCalEnd = date("Ymd\THi00", strtotime($row["dateend"]." ".$row["timeend"]) );
}
/* output the event */
if (!$row["allday"]) {
$vCalOutput = $vCalOutput."BEGIN:VEVENT\n";
$vCalOutput = $vCalOutput."SUMMARY:".$row["title"]."\n";
$vCalOutput = $vCalOutput."DESCRIPTION:".$vCalDescription."\n";
$vCalOutput = $vCalOutput."DTSTART;TZID=US/Pacific:".$vCalStart."\n";
$vCalOutput = $vCalOutput."LOCATION:".$vCalLocation."\n";
$vCalOutput = $vCalOutput."URL;VALUE=URI:".$row["url"]."\n";
$vCalOutput = $vCalOutput."DTEND:".$vCalEnd."\n";
$vCalOutput = $vCalOutput."END:VEVENT\n";
} else {
$dayinc = 1;
while ($vCalStart <= $vCalEnd) {
$vCalOutput = $vCalOutput."BEGIN:VEVENT\n";
$vCalOutput = $vCalOutput."SUMMARY:".$row["title"]."\n";
$vCalOutput = $vCalOutput."DESCRIPTION:".$vCalDescription."\n";
$vCalOutput = $vCalOutput."DTSTART;TZID=US/Pacific:".$vCalStart."\n";
$vCalOutput = $vCalOutput."LOCATION:".$vCalLocation."\n";
$vCalOutput = $vCalOutput."URL;VALUE=URI:".$row["url"]."\n";
$vCalOutput = $vCalOutput."END:VEVENT\n";
$vCalYear = $vCalStart[0] . $vCalStart[1] . $vCalStart[2] . $vCalStart[3];
$vCalMonth = $vCalStart[4] . $vCalStart[5];
$vCalDay = $vCalStart[6] . $vCalStart[7];
$nextday = mktime(0, 0, 0, $vCalMonth, $vCalDay + 1, $vCalYear);
$vCalStart = date("Ymd", $nextday);
}
}
}
}
Two significant changes: first, timezone info is included for events (again, to help with iPod syncing). Second, in the original, a multiday event would appear as one very long event spanning all the hours on your calendar for the days in question. Now, if the event is marked "all day event," it will instead appear as one (or more if necessary) all-day events on the syncing calendar.
21
18
Quote
wrdickson
Anonymous
Anybody managed to produce vCal output that Outlook will read properly, with multiple events? Both mine and Toholio's original code seem to generate files that iCal can read just fine. Mine fixes timezone issues and the iPod can read it fine. But Outlook can only read the first event in a multi-event file.
It appears that Outlook can only export single events; does it perhaps simply not understand files with multiple events?
It appears that Outlook can only export single events; does it perhaps simply not understand files with multiple events?
19
23
Quote
Status: offline
Toholio
Forum User
Newbie
Registered: 07/30/03
Posts: 6
Quote by wrdickson: Anybody managed to produce vCal output that Outlook will read properly, with multiple events?
I think this is a problem with Outlook. A person using my calendar feed and the latest version of Outlook (2003?) was able to import all the events but older versions seem to read the first one and then stop.
The person who was able to import the calendar was still without any decent way to update it. Loading the vCal file again just gives duplicates.
28
25
Quote
Status: offline
Toholio
Forum User
Newbie
Registered: 07/30/03
Posts: 6
Quote by michaelaskew:Actually, what I'd really like to see is a way to import ical/vcal files...This project has code which parses an ical file and displays a page, but I've looked at their parser and it doesn't look very reusable.
Importing vCal files would be a pain because you'd have to check to see if events were already in the database (unless you don't mind overwriting them, in which case the process would be much simpler).
You could always replace the Geeklog calendar with phpiCal but then you wouldn't have a web interface (not such a problem if you are the only person contributing to the calendar).
I might write a script to import vCal info into Geeklog but I don't have the time to produce a version that wouldn't obliterate whatever used to be in the gl_events table.
25
25
Quote
djbeta
Anonymous
This is a little off the topic of this thread... but I'm hoping someone here might have an idea of how to approach this...
I would like to take a multi-event mac-version calendar export (ical generates an .ics file)
and convert it to a format that Outlook can import.
Can anyone tell me how I can either do this or point me in the right direction so that I can learn how to do this ?
Thanks,
Steven
33
23
Quote
Status: offline
trampoline
Forum User
Full Member
Registered: 02/24/06
Posts: 392
Is anyone having success with this ?
Also is there anyway to IMPORT a whole calendar or calendar events into the geeklog calendar ?
Also is there anyway to IMPORT a whole calendar or calendar events into the geeklog calendar ?
30
20
Quote
;Status: offline
jmucchiello
Forum User
Full Member
Registered: 08/29/05
Posts: 985
I would say you should search /public_html/admin/plugins/calendar/index.php and /plugins/calendar/functions.inc for all occurrences of the COM_rdfUpToDateCheck function and call it there.
That example code is very ugly. Someone should update it to use Geeklog functions to access the database.
That example code is very ugly. Someone should update it to use Geeklog functions to access the database.
20
29
Quote
swninetails
Anonymous
Along the lines of outlook and the ical/vcal format does anyone know how to indicate a specific calendar to import an ical/vcal object into. For instance I have created a format whch outlook can read in but it always places the event into my calendar and I need the event placed into the sales team calendar which is a shared calendar.
26
25
Quote
All times are EST. The time is now 06:26 pm.
- Normal Topic
- Sticky Topic
- Locked Topic
- New Post
- Sticky Topic W/ New Post
- Locked Topic W/ New Post
- View Anonymous Posts
- Able to post
- Filtered HTML Allowed
- Censored Content