Skip to content

Instantly share code, notes, and snippets.

@aaferrari
Last active November 1, 2018 05:56
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save aaferrari/695507232ee902de58faab7698f84694 to your computer and use it in GitHub Desktop.
Save aaferrari/695507232ee902de58faab7698f84694 to your computer and use it in GitHub Desktop.
Convert TimeTable subjects schedules into an iCal file.
/*
To test it you need to extract the database of the application and execute the
SQL statements in an external program or from Android with the following command
(it is assumed that the device is rooted):
sqlite3 /data/data/com.gabrielittner.timetable/databases/timetable.db < timetable2ical.sql
*/
CREATE temporary TABLE weekdays ('day' INTEGER, 'short_name' CHAR, 'long_name' VARCHAR);
insert into weekdays values('1', 'MO', 'Monday');
insert into weekdays values('2', 'TU', 'Tuesday');
insert into weekdays values('3', 'WE', 'Wednesday');
insert into weekdays values('4', 'TH', 'Thursday');
insert into weekdays values('5', 'FR', 'Friday');
insert into weekdays values('6', 'SA', 'Saturday');
insert into weekdays values('7', 'SU', 'Sunday');
/* Create a temporary table in order to work more easily with the data */
CREATE temporary TABLE intermediate AS
SELECT lday, lsubject, lroom, ltype, lteacher,
(select short_name from weekdays where day=lday) AS shortday,
(select long_name from weekdays where day=lday) AS longday,
llesson, labbreviation,
Strftime('%H%M%S', lstart * 60, 'unixepoch') AS start,
Strftime('%H%M%S', lend * 60, 'unixepoch') AS end,
Strftime('%Y%m%dT', Substr(updated, '1', 10), 'unixepoch') AS date_updated,
Strftime('%Y%m%dT%H%M%S', Substr(updated, '1', 10), 'unixepoch') AS dtstamp
FROM lessons order by lday;
/* Unique information on each subject */
CREATE temporary table subjects as select distinct lsubject, labbreviation, start, end from intermediate;
/* Definitive select */
/* Header */
select 'BEGIN:VCALENDAR
PRODID:Pseudo iCal exporter
VERSION:2.0' union
select 'BEGIN:VEVENT
UID:Timetable2iCal' || random() || '
DTSTAMP:' || (select max(dtstamp) from intermediate where lsubject= subjects.lsubject and start= subjects.start and end=subjects.end) ||'
DTSTART:'|| (select max(date_updated) from intermediate where lsubject= subjects.lsubject and start= subjects.start and end=subjects.end) || start ||'
DTEND:'|| (select max(date_updated) from intermediate where lsubject= subjects.lsubject and start= subjects.start and end=subjects.end) || end ||'
LOCATION:'|| ifnull((select group_concat(ordered, ', ') from (select lroom || ' (' || longday || ')' as ordered from intermediate where lsubject= subjects.lsubject and start= subjects.start and end=subjects.end and lroom not like '')), '') ||'
SUMMARY:'|| lsubject ||'
DESCRIPTION:* Abbreviated name: ' || labbreviation ||'\n
* Lessons: ' || ifnull((select group_concat(ordered, ', ') from (select llesson || ' (' || longday || ')' as ordered from intermediate where lsubject= subjects.lsubject and start= subjects.start and end=subjects.end and llesson not like '')), '') ||'\n
* Teachers: ' || ifnull((select group_concat(ordered, ', ') from (select lteacher || ' (' || longday || ')' as ordered from intermediate where lsubject= subjects.lsubject and start= subjects.start and end=subjects.end and lteacher not like '')), '') ||'\n
* Type: ' || ifnull((select group_concat(ordered, ', ') from (select ltype || ' (' || longday || ')' as ordered from intermediate where lsubject= subjects.lsubject and start= subjects.start and end=subjects.end and ltype not like '')), '') ||'
RRULE:FREQ=WEEKLY;WKST=MO;BYDAY='|| (select distinct GROUP_CONCAT(shortday) from intermediate where lsubject= subjects.lsubject and start= subjects.start and end=subjects.end) ||'
SEQUENCE:0
STATUS:CONFIRMED
TRANSP:OPAQUE
END:VEVENT' from subjects
/* Footer */ union select 'END:VCALENDAR';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment