+ public CourseGroup[] getCourseGroups(String coursename, Date notBefore) {
+ String[] whereArgs;
+ whereArgs = new String[] {
+ coursename
+ };
+ ArrayList<CourseGroup> groups = new ArrayList<CourseGroup>();
+
+ /*
+ Cursor resultSet = database.rawQuery(
+ "SELECT CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName, t1.FirstEvent, t1.LastEvent, t1.EventCount FROM " +
+ "CourseCalendarGroup, ( " +
+ "SELECT CourseCalendar.CourseGroupId, MIN(CourseCalendarEvent.EventFrom) as FirstEvent, MAX(CourseCalendarEvent.EventTo) as LastEvent, COUNT(*) as EventCount " +
+ "FROM CourseCalendarEvent " +
+ "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
+ "GROUP BY CourseCalendar.CourseGroupId " +
+ ") as t1 " +
+ "WHERE " +
+ "CourseCalendarGroup.GroupId = t1.CourseGroupId AND " +
+ "CourseName = ? AND t1.LastEvent > ? " +
+ "ORDER BY FirstEvent ASC",
+ whereArgs);
+ */
+ Cursor resultSet = database.rawQuery(
+ "SELECT " +
+ "CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName, " +
+ "MIN(CourseCalendarEvent.EventFrom) as FirstEvent, " +
+ "MAX(" +
+ "CASE WHEN CourseCalendarEvent.EventType IN (0, 1) " +
+ "THEN CourseCalendarEvent.EventFrom " +
+ "ELSE 0 " +
+ "END) as LastEvent, " +
+ "MAX(CourseCalendarEvent.EventFrom) as LastEventFilter, " +
+ "COUNT(*) as EventCount, " +
+ "MIN(" +
+ "CASE WHEN CourseCalendarEvent.EventFrom < strftime('%s', 'now') " +
+ "THEN (strftime('%s', 'now')+(86400*365*5)+CourseCalendarEvent.EventFrom) " +
+ "ELSE CourseCalendarEvent.EventFrom " +
+ "END) as NextEvent, " +
+ "MIN(" +
+ "CASE WHEN CourseCalendarEvent.EventFrom < strftime('%s', 'now') OR CourseCalendarEvent.EventType NOT IN (2) " +
+ "THEN (strftime('%s', 'now')+(86400*365*5)+CourseCalendarEvent.EventFrom) " +
+ "ELSE CourseCalendarEvent.EventFrom " +
+ "END) as NextKlausurEvent, " +
+ "MIN(CASE WHEN CourseCalendarEvent.EventFrom <= strftime('%s', 'now') THEN 1 ELSE 0 END) as EventCompleted " +
+ "FROM " +
+ "CourseCalendarEvent " +
+ "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
+ "LEFT JOIN CourseCalendarGroup ON CourseCalendarGroup.GroupId = CourseCalendar.CourseGroupId " +
+ "WHERE " +
+ "CourseCalendar.CourseName = ? " +
+ "GROUP BY CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName " +
+ "HAVING LastEventFilter > " + Long.toString((notBefore == null ? 0L : notBefore.getTime()/1000)) + " " +
+ "ORDER BY EventCompleted DESC, NextEvent ASC ",
+ whereArgs);
+ if(resultSet.moveToFirst()) {
+ int[] columnIndexes = {
+ resultSet.getColumnIndex("GroupId"),
+ resultSet.getColumnIndex("CourseName"),
+ resultSet.getColumnIndex("GroupName"),
+ resultSet.getColumnIndex("FirstEvent"),
+ resultSet.getColumnIndex("LastEvent"),
+ resultSet.getColumnIndex("EventCount"),
+ resultSet.getColumnIndex("NextEvent"),
+ resultSet.getColumnIndex("EventCompleted"),
+ resultSet.getColumnIndex("NextKlausurEvent"),
+ };
+ do {
+ CourseGroup coursegroup = new CourseGroup(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]));
+ Bundle extraData = coursegroup.getExtraData();
+
+ extraData.putLong("FirstEvent", resultSet.getLong(columnIndexes[3]));
+ extraData.putLong("LastEvent", resultSet.getLong(columnIndexes[4]));
+ extraData.putInt("EventCount", resultSet.getInt(columnIndexes[5]));
+ extraData.putLong("NextEvent", resultSet.getLong(columnIndexes[6]));
+ extraData.putLong("NextKlausurEvent", resultSet.getLong(columnIndexes[8]));
+ extraData.putBoolean("EventCompleted", (resultSet.getInt(columnIndexes[7]) == 1));
+
+ groups.add(coursegroup);
+
+ } while (resultSet.moveToNext());
+ resultSet.close();
+ }
+
+ CourseGroup[] groupsArr = new CourseGroup[groups.size()];
+ groupsArr = groups.toArray(groupsArr);
+ return groupsArr;
+ }
+