Creating ICS, vCAl, iCal

[Roderick]

Has anyone tackled this on Alpha? MySQL?

I would love to create an .ics file that users could import into their phones, work Outlook, etc. This would keep them updated with the Alpha Calendar without having to check the website.

[Garry Flanigan]

Re: Creating ICS, vCAl, iCal

This thread http://msgboard.alphasoftware.com/al...calendar-event may help.
This creates the file that can be attached to an email. I did it a long time ago and SQL or dbf makes no difference.

[Roderick]

Re: Creating ICS, vCAl, iCal

Gary your code is perfect to attach a single event to an email, etc. That is a great feature that should be built into the A5 calendar. I was thinking more along the lines of a subscription to a calendar this way as events are added on the A5 web app the user's/subscriber's Outlook calendar would be updated regularly.

[Sparticuz]

Re: Creating ICS, vCAl, iCal

You'd want that code imbedded in an a5w page. www.example.com/a5cal.a5w

Then have it create the code, and echo it out properly so Outlook can understand it. (I'm not sure how it would work with Outlook as I'm not sure if it allows web based ical's. Try Google calendar, but Gcal only updates ical's once every couple hours)

[christappan]

Re: Creating ICS, vCAl, iCal

I just came across this thread and thought I'd share my success with something similar. The code below creates an .ics file from database records that a user can subscribe to for read-only viewing in her/his calendar application (Outlook, Thunderbird, Google calendar, mobile devices, etc). I did include a link in the description that can theoretically open the record in your application from the user's calendar application but without having dealt with security issues here, it's not always useful. And on a similar note, the calendar files have to be sitting on the server and accessible to the outside world, so I'm using security by obfuscation--i.e. putting the files in directories named with GUID strings rather than something a bot could easily break into.

It's created with the idea that the entire file is deleted and recreated when events change--not ideal, but it's not resource-intensive either. I pieced it together from a number of sources online and I can assure you there would be ways to do it better, but it's working for us. I'm using MySQL and Jim Coltz's A5 jqCalendar, so my database tables/columns and syntax reflect that, but I think most can still figure out what's going on.

FUNCTION ICSFile   AS C (cEmp_ID AS C)

dim cn as sql::Connection
cn.Open("::Name::MyServerConnection")
dim args as sql::arguments

args.add("Emp_ID",cEmp_ID)

cn.Execute("SELECT Emp_CalURL FROM employee WHERE emp_ID = :Emp_ID",args)

empCalURL = cn.ResultSet.data("Emp_CalURL")

dim calendarFile as C = filename_decode("[PathAlias.File_Path]") + "\Calendars\\" + empCalURL + "\mySite.ics"

if file.exists(calendarfile)
	a5_file_remove(calendarfile)
end if

dir_create_recurse(filename_decode("[PathAlias.File_Path]") + "\Calendars\\" + empCalURL + "\\")

file.create(calendarfile,FILE_RW_SHARED)

dim calendarHeader as C = <<%txt%
BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//MySite//MyCompany//EN
CALSCALE:GREGORIAN
METHOD:PUBLISH
X-WR-RELCALID:ABC
BEGIN:VTIMEZONE
TZID:America/New_York
X-LIC-LOCATION:America/New_York
BEGIN:DAYLIGHT
TZOFFSETFROM:-0500
TZOFFSETTO:-0400
TZNAME:EDT
DTSTART:19700308T020000
RRULE:FREQ=YEARLY;BYDAY=2SU;BYMONTH=3
END:DAYLIGHT
BEGIN:STANDARD
TZOFFSETFROM:-0400
TZOFFSETTO:-0500
TZNAME:EST
DTSTART:19701101T020000
RRULE:FREQ=YEARLY;BYDAY=1SU;BYMONTH=11
END:STANDARD
END:VTIMEZONE%txt%

file.append_line(calendarfile,calendarHeader)

dim sqlSelect as C = <<%txt%
SELECT ID, Subject, Location, Description, StartTime, EndTime, IsAllDayEvent, Color, StartDate, EndDate
FROM jqcalendar 
WHERE Employee = :Emp_ID
AND StartDate BETWEEN ADDDATE(CURDATE(),INTERVAL -60 DAY) AND ADDDATE(CURDATE(),INTERVAL 120 DAY)
%txt%

cn.Execute(sqlSelect, args)
dim rs as sql::ResultSet
rs = cn.ResultSet

rsFlag = rs.nextRow()
while rsFlag
'debug(2)
	UID = convert_type(rs.data("ID"),"C")
	Summary = stritran(stritran(rs.data("Subject"),"\\","\\\"),",","\,")
	Summary = stritran(Summary,";","\;") 'this didn't work when added to line above for some reason
	Description = stritran(stritran(rs.data("Description"),"\\","\\\"),",","\,")
	Description = stritran(Description,";","\;") + "\n\nClick or paste in a browser to edit: https://www.mysite.com/MyCalendarEventPage.a5w?id=" + UID
	IsAllDayEvent = convert_type(rs.data("IsAllDayEvent"),"C")
	if IsAllDayEvent = "0"
		DtStart = "DTSTART;VALUE=DATE-TIME:" + time("yyyyMMdd",rs.data("StartDate")) + "T" + time("0h0m0s",rs.data("StartTime"))
		DtEnd = "DTEND;VALUE=DATE-TIME:" + time("yyyyMMdd",rs.data("EndDate")) + "T" + time("0h0m0s",rs.data("EndTime"))
	else
		DtStart = "DTSTART;VALUE=DATE:" + time("yyyyMMdd",rs.data("StartDate"))
		DtEnd = "DTEND;VALUE=DATE:" + time("yyyyMMdd",rs.data("EndDate"))
	end if		

	dim eventString as C = "BEGIN:VEVENT" + crlf() + "UID:" + UID + crlf() + "SUMMARY:" + Summary + crlf() + "DESCRIPTION:" + Description + crlf() + DtStart + crlf() + DtEnd + crlf() + "TRANSP:OPAQUE" + crlf() + "STATUS:CONFIRMED" + crlf() + "END:VEVENT"
	file.append_line(calendarfile,eventString)
	
	rsFlag = rs.nextRow()
end while

cn.close()

file.append_line(calendarfile,"END:VCALENDAR")

END FUNCTION