Tutorial - SuperOffice iCal Feed

Having calendar data in SuperOffice is nice, but there are lots of other places that your calendar shows up. Your phone has a calendar, and while SuperOffice Pocket is nice, it may not be available on your type of phone, or you may want to display the calendar on your desktop using a calendar widget.

The iCal standard is a way to publish calendar data on the internet. This standard lets us publish your calendar in a way that lets Apple, Google, and others read your appointments.

Security considerations

To avoid sending the username + password around the web, we will instead use good old security by obscurity. A better name for this is a nonce or a cookie. If you know the secret word, you are allowed in. If you don't know the right word, you don't get anything.

We can't rely on user input because other systems on the internet will be accessing the calendar on our behalf. We are not going to be around to type in the username + password every time Google wants to check our calendar.

Instead, we will use a system user to read the appointments - this avoids relying on a username + password coded into the URL.

To avoid giving everyone access to your calendar, we use a unique random identifier (a GUID) to identify you. This ID can be used to check that the request came from someone who knows the GUID (is allowed to read the calendar). So the GUID is a proxy for the username + password, with the added benefit of not expiring (like a session ticket tends to do) and not changing, even if the user's password changes.

So protect the iCal GUID like it was your password, and you'll be OK.

As an added precaution, we set up the GUID system so that only one GUID is valid at a time. If you log in again, we generate a new GUID and overwrite the old one, so that old logins no longer work.

So if someone steals your iCal link, just make a new one and the old one will stop working.

Our website will start with a simple web form: Username + Password.

We will take this and log in to NetServer. If the login is successful, we generate a random GUID and store it as a foreign-key on the user's associate record.

protected void LoginSubmit_ServerClick(object sender, EventArgs e)
{
  SoSession sesh = SoSession.Authenticate(Username.Value, Password.Value);
  if (sesh.IsOpen)
  {
    string guid = Guid.NewGuid().ToString();
    int id = sesh.Principal.AssociateId;
    AssociateRow assoc = AssociateRow.GetFromIdxAssociateId(id);
    int fkDevId = Util.GetFkDevice("SO-iCal");
    assoc.ForeignKeyHelper.Set( fkDevId, "iCal",  guid );
    assoc.ForeignKeyHelper.Save();
    sesh.Close();
    string url = string.Format("Result.aspx?associd={0}&guid={1}", id, guid );
    Response.Redirect( url );
  }
  else
    Result.InnerText = "Unable to log in to SuperOffice as " + Username.Value;
  }
  catch(Exception)
  {
    Result.InnerText = "Unable to log in to SuperOffice as " + Username.Value;
  }
}

The Util.GetFkDevice is a helper that creates a foreign-key app and device record for us if necessary.

If the login is successful, we store the unique ID needed to log in and send the browser to the Result.aspx page.

The result page shows us the link that we can give to other systems for reading the calendar.

This page just checks that the ID and GUID match. If they match, then we construct the link to the iCal page.

We don't have the user's login info here, so we can't log in as the user. Instead, we log in as a system user (login info is read from the config file AppSettings).

int associd = 0;
int.TryParse(Request.QueryString["associd"], out associd);
string guid = Request.QueryString["guid"];
string souser = ConfigurationSettings.AppSettings["SOUser"];
string sopass = ConfigurationSettings.AppSettings["SOPass"];

using (SoSession sesh = SoSession.Authenticate(souser, sopass))
{
  AssociateRow assocRow = AssociateRow.GetFromIdxAssociateId(associd);
  if (assocRow.AssociateId == associd)
  {
    int fkDevId = Util.GetFkDevice("SO-iCal");
    if (guid == assocRow.ForeignKeyHelper.Get(fkDevId, "iCal"))
    {
      string tmpUri = Request.Url.AbsoluteUri;
      tmpUri = tmpUri.Replace("Result.aspx", "iCal.aspx");
      ResultInfo.InnerHtml = "**Success!** You can add the following link to Google Calendar: <br>" + "<a href=\"" + tmpUri + "\">" + tmpUri + "</a><br>\n" + "<p>Note that old links you may have created will stop working. Only one link is valid at a time.";
    }
  }
}

The system user bypasses the sentry security restrictions and is therefore allowed to read and publish the user's private appointments. This is not so important here but is more important on the next page, the iCal Results.

The iCal page does the same login check as the Result page, but it reads the user's follow-ups and outputs the iCal. The search limits the number of follow-ups to about 2 months worth, weighted towards future/planned follow-up.

DateTime fromDate = DateTime.Now.AddDays(-10);
DateTime toDate = DateTime.Now.AddDays(39);
AppointmentCollection.CustomSearch q = new AppointmentCollection.CustomSearch();
q.Restriction = q.TableInfo.AssociateId.Equal( S.Parameter(associd)).And( q.TableInfo.Type.In(S.Parameter(1), S.Parameter(2), S.Parameter(6))).And( q.TableInfo.DoBy.Between(S.Parameter(fromDate), S.Parameter(toDate) ));
AppointmentCollection appoints = AppointmentCollection.GetFromCustomSearch(q);

To output the follow-ups, we need to set a few things in the header:

Response.ContentType = "text/calendar";
Response.Charset = "";
Response.Cache.SetNoServerCaching();
Response.Cache.SetExpires(DateTime.Now);
Response.Cache.SetNoStore();
Response.ContentEncoding = Encoding.UTF8;

The response itself has a header and then a list of follow-up details.

Content header

Response.Write("BEGIN:VCALENDAR\r\n");
Response.Write("PRODID:-//SuperOffice AS//SuperOffice Calendar 7//EN\r\n");
Response.Write("VERSION:2.0\r\n");
Response.Write("CALSCALE:GREGORIAN\r\n");
Response.Write("METHOD:PUBLISH\r\n");
Response.Write("X-WR-CALNAME:SO " + name + "\r\n");
Response.Write("X-WR-CALDESC:" + name + " SuperOffice calendar\r\n");
Response.Write("X-WR-TIMEZONE;VALUE=TEXT:Europe/Oslo\r\n");

Per-appointment output

private string MakeLocalDate(DateTime dt)
{
  return dt.ToString("yyyyMMdd\\THHmmss");
}

private string MakeDate(DateTime dt)
{
  return dt.ToUniversalTime().ToString("yyyyMMdd\\THHmmss\\Z");
}

foreach (Appointment a in appoints)
{
  string start = MakeLocalDate(a.DoBy);
  string end = MakeLocalDate(a.EndDate);
  string id = "appointmentid-" + a.AppointmentId.ToString() + "@" + Request.ServerVariables["SERVER_NAME"];
  string type = a.Task == null ? "" : a.Task.Name;
  string registered = MakeDate(a.Registered);
  string updated = a.Updated.Year < 1900 ? registered : MakeDate(a.Updated);
  string desc = a.AppointmentText == null ? "" :
                a.AppointmentText.Text.Replace("\r\n", "\\n").Replace("\n", "\\n").Replace("\r", "\\r");
  string cont = a.Contact == null ? "" : a.Contact.Name;
  string summary = type + ": " + desc;
  if (summary.Length > 60)
    summary = summary.Substring(0, 60) + "...";
  Response.Write("BEGIN:VEVENT\r\n");
  Response.Write("DTSTART;TZID=Europe/Oslo:" + start + "\r\n");
  Response.Write("DTEND;TZID=Europe/Oslo:" + end + "\r\n");
  Response.Write("DTSTAMP:" + updated + "\r\n");
  Response.Write("UID:" + id + "\r\n");
  Response.Write("CLASS:PUBLIC\r\n");
  Response.Write("CREATED:" + registered + "\r\n");
  Response.Write("LAST-MODIFIED:" + updated + "\r\n");
  Response.Write("SUMMARY:" + summary + "\r\n");
  Response.Write("CATEGORIES:" + type + "\r\n");
  Response.Write("DESCRIPTION:" + type + " / " + cont + " / " + desc + "\r\n");
  Response.Write("SEQUENCE:0\r\n");
  if( a.Type == AppointmentType.BookingForChecklist || a.Type == AppointmentType.BookingForDiary )
    Response.Write("STATUS:TENTATIVE\r\n");
  else
    Response.Write("STATUS:CONFIRMED\r\n");
  if( a.FreeBusy == 0 )
    Response.Write("TRANSP:OPAQUE\r\n");
  else
    Response.Write("TRANSP:TRANSPARENT\r\n");
  Response.Write("END:VEVENT\r\n");
}

Google is picky about the time-zone ID on the DTSTART/DTEND – it must be present.

This page results in output like this:

BEGIN:VCALENDAR
PRODID:-//SuperOffice AS//SuperOffice Calendar 6//EN
VERSION:2.0
CALSCALE:GREGORIAN
METHOD:PUBLISH
X-WR-CALNAME:SO Christian
X-WR-CALDESC:Christian SuperOffice calendar
X-WR-TIMEZONE;VALUE=TEXT:Europe/Oslo
BEGIN:VEVENT
DTSTART;TZID=Europe/Oslo:20110209T123000
DTEND;TZID=Europe/Oslo:20110209T133000
DTSTAMP:20110209T083551Z
UID:appointmentid-3146920@localhost
CLASS:PUBLIC
CREATED:20110125T141334Z
LAST-MODIFIED:20110209T083551Z
SUMMARY:Meeting - IN: Discuss Expander Online Extensibility\n\n-Web...
CATEGORIES:Meeting - IN
DESCRIPTION:Meeting - IN / SuperOffice AS / Discuss Expander Online Extensibility\n\n- Web Services (currently limited availability)\n- NetServer Services Scripting\n- .merge/.config file modifications
SEQUENCE:0
STATUS:CONFIRMED
TRANSP:OPAQUE
END:VEVENT
BEGIN:VEVENT
DTSTART;TZID=Europe/Oslo:20110211T130000
DTEND;TZID=Europe/Oslo:20110211T140000
DTSTAMP:20110207T101132Z
UID:appointmentid-3154868@localhost
CLASS:PUBLIC
CREATED:20110203T110804Z
LAST-MODIFIED:20110207T101132Z
SUMMARY:Meeting - IN: (Almost) MAF-meeting – Configuration
...
END:VEVENT
END:VCALENDAR

The non-XML format shown in the previous step is what Outlook, Google, and Apple's calendar can use to integrate external calendar events.

Outlook

Outlook can load the iCal feed using the internet calendar account setting.

1. Under the TOOLS menu, open ACCOUNT SETTINGS. A dialog appears.

2. Go to the INTERNET CALENDARS tab and add a NEW calendar.

   

   

3. Outlook will download the follow-up once you have pasted in the link to ical.aspx?associd=123&guid=1234-4567…

4. The calendar shows up in the calendar view in Outlook – you just need to mark the checkbox for the calendar you added.

   

Google Calendar

So if you have a Google Phone, you can now get your SuperOffice calendar to show up on the phone. To do this we go via the Google Calendar web page.

1. Log in to the http://calendar.google.com/ website.

2. Click on the ADD menu under OTHER CALENDARS: ADD BY URL

   

   A simple dialog box appears (similar to the one in Outlook)

   

3. Paste the ical.aspx?associd=123&guid=1234-4567… link in here.

4. Google will read the follow-ups and layer them on top of your calendar. You can change the name and whether the calendar is shared by editing the settings. The resulting calendar looks like this:

   

These follow-ups will now also appear on your Android phone calendar if you have enabled calendar sync on your device

Desktop Gadget

But what if you don't want to turn on your phone to check your SuperOffice calendar? What if you just want to glance at the desktop and see where you are supposed to be?

The Windows Live Calendar Gadget is just what you need.

1. Download the latest gadget from http://wlcalendargadget.codeplex.com/

2. Install the gadget and then right-click the desktop and choose GADGETS.

   

3. Add the Windows Live Calendar Gadget. It will appear somewhere on the desktop.

4. Adjust the settings on the calendar. You have to paste the iCal link into the textbox at the top of the calendar gadget settings dialog.

   

5. Click OK and the gadget will load the follow-ups from the URL and display them under the week or month.

   

   

6. Click on a follow-up for the full description of it.

In Summary, you have now learned how to build a simple website that lets you log in to SuperOffice and get a unique URL that you can use to read your calendar without logging in each time.

• Log in
• Result page
• Step Three
• iCal Results
• Integrate external calendar events