Heavy use is made of Request Tracker in my office. Apparently support for iCalendar is due in a future version - but apprently the future isn't soon enough...

Therefore, I've quickly hacked up a perl script to take a (lower case) queue name as an argument and output ical to stdout. Then another script in /etc/cron.hourly/ generates .ics files in a web accessable directory.

Obviously this bypasses the RT login system, so you may want to protect the directory using some apache based restriction if the ticket subjects may contain sensitive information.

  for i in `grep -v "^#" rt-queue-list`;
  do 
      rtwebcalgen.pl $i > /srv/web/directory/$i.ics
  done;

#!/usr/bin/perl
# quick hack to dump out RT tickets with due dates set in RFC2445 format

use DBI;

$SITE_CONFIG_FILE = "/etc/request-tracker3.4/RT_SiteConfig.pm";
local *Set = sub { $_[0] = $_[1] unless defined $_[0] };
require $SITE_CONFIG_FILE
  || die ("Couldn't load RT config file '$SITE_CONFIG_FILE'\n$@");

my $setqueue = @ARGV[0];

my $dbh = DBI->connect('DBI:'.$DatabaseType.':'.$DatabaseName,
 $DatabaseUser,$DatabasePassword );

my $query =
 " SELECT t.id, " .
 "  DATE_FORMAT(t.Created,'%Y%m%dT%H%i%sZ') AS Created, " .
 "  DATE_FORMAT(t.Due,'%Y%m%d') AS DueStart, " .
 "  DATE_FORMAT(ADDDATE(t.Due,1),'%Y%m%d') AS DueEnd, " .
 "  LOWER(q.Name) AS Queue, " .
 "  t.Subject, " .
 "  t.Priority, " .
 "  u.Name AS Owner " .
 " FROM Tickets t " .
 " LEFT JOIN Queues q ON q.id = t.Queue " .
 " LEFT JOIN Users u ON u.id = t.Owner " .
 " WHERE t.Status REGEXP 'open|new' " .
 "  AND t.Due NOT LIKE '1970-01-01%' ";
if (!$setqueue eq "") {
        $query .= "  AND q.Name = LOWER('".$setqueue."')";
}
$query .= " ORDER BY t.Due";

my $sth = $dbh->prepare( $query );
$sth->execute();
my ($id, $Created, $DueStart, $DueEnd, $Queue, $Subject, $Priority,
  $Owner);
$sth->bind_columns( undef, \$id, \$Created, \$DueStart, \$DueEnd, 
 \$Queue, \$Subject, \$Priority, \$Owner);

print "BEGIN:VCALENDAR\n";
print "VERSION:2.0\n";
print "X-WR-CALNAME;VALUE=TEXT:RT";
if (!$setqueue eq "") {
  print " $setqueue queue";
} else {
  print " tickets";
}
print "\n";
print "PRODID:-//lee//rtwebcalgen.pl//EN\n";
print "CALSCALE:GREGORIAN\n";
print "METHOD:PUBLISH\n";
while( $sth->fetch() ) {
        print "BEGIN:VEVENT\n";
        print "DTSTAMP:${Created}\n";
        print "DTSTART;VALUE=DATE:${DueStart}\n";
        print "DTEND;VALUE=DATE:${DueEnd}\n";
        $Subject =~ s/,/\\,/g; # commas must be escaped
        print "SUMMARY:${id} ${Subject} [${Owner}]\n";
        print "UID:${id}_${CorrespondAddress}\n";
        print "URL:${WebBaseURL}/Ticket/Display.html?id=${id}\n";
        print "PRIORITY:${Priority}\n";
        #print "STATUS:NEEDS-ACTION\n";
        print "CLASS:PRIVATE\n";
        print "TRANSP:TRANSPARENT\n";
        print "X-RT-ID:${id}\n";
        print "X-RT-QUEUE:${Queue}\n";
        print "CATEGORIES:${Queue}\n";
        print "X-RT-OWNER:${Owner}\n";
        print "END:VEVENT\n";
}
print "END:VCALENDAR\n";

$sth->finish();
$dbh->disconnect();

(Note, it's a very simple hack to get it to generate TODO lists - you modify the SQL query, replace VEVENT with VTODO and set the STATUS.)

Update: hah. As anyone who doesn't live in the GMT timezone will know, RT stores dates in UTC. I only just noticed because the hour difference from the 26th, all the dates normally displayed as "00:00" for next week are actually stored as "23:00" the day before! I'll update this entry later to reflect this...