X-Git-Url: http://git.pk910.de/?p=DHBWCampusApp.git;a=blobdiff_plain;f=app%2Fsrc%2Fmain%2Fjava%2Fde%2Fdhbwloe%2Fcampusapp%2Fdatabase%2FVorlesungsplanDatabaseHelper.java;h=f4d5c8a99ba5fb717d5099c34f8536af0a80ff2d;hp=e38b87d25f00d7f7cf11ea82d18dcbe7ac2d2c0b;hb=9a28e7b4c1520f629721693a04b4978fec9692e7;hpb=89252e3c22caf6dcccd0c50ad3a9282a53b5a890 diff --git a/app/src/main/java/de/dhbwloe/campusapp/database/VorlesungsplanDatabaseHelper.java b/app/src/main/java/de/dhbwloe/campusapp/database/VorlesungsplanDatabaseHelper.java index e38b87d..f4d5c8a 100644 --- a/app/src/main/java/de/dhbwloe/campusapp/database/VorlesungsplanDatabaseHelper.java +++ b/app/src/main/java/de/dhbwloe/campusapp/database/VorlesungsplanDatabaseHelper.java @@ -3,18 +3,25 @@ package de.dhbwloe.campusapp.database; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; +import android.os.Bundle; import android.util.Log; +import net.fortuna.ical4j.model.Component; import net.fortuna.ical4j.model.DateList; import net.fortuna.ical4j.model.DateTime; import net.fortuna.ical4j.model.Period; +import net.fortuna.ical4j.model.PeriodList; import net.fortuna.ical4j.model.Recur; import net.fortuna.ical4j.model.parameter.Value; import net.fortuna.ical4j.model.property.RRule; import java.text.ParseException; +import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; +import java.util.Iterator; +import java.util.List; +import java.util.TimeZone; import de.dhbwloe.campusapp.CampusAppContext; import de.dhbwloe.campusapp.vorlesungen.CourseEvent; @@ -33,7 +40,7 @@ public class VorlesungsplanDatabaseHelper { } - public void updateCourseCalendar(CourseEvent event) { + public void updateCourseCalendar(CourseEvent event, Component cevent) { boolean isExisting = false; String[] whereArgs = new String[] { event.getCourseName(), @@ -63,7 +70,7 @@ public class VorlesungsplanDatabaseHelper { updateValues.put("CourseGroupId", event.getCourseGroup().getGroupId()); database.update("CourseCalendar", updateValues, "CourseName = ? AND UniqueId = ?", whereArgs); - updateCourseCalendarEventTable(event, true); + updateCourseCalendarEventTable(event, true, cevent); } catch(Exception e) { e.printStackTrace(); } @@ -85,7 +92,7 @@ public class VorlesungsplanDatabaseHelper { long id = database.insertOrThrow("CourseCalendar", null, indexValues); event.setEventId((int) id); - updateCourseCalendarEventTable(event, false); + updateCourseCalendarEventTable(event, false, cevent); } catch(Exception e) { e.printStackTrace(); } @@ -93,61 +100,39 @@ public class VorlesungsplanDatabaseHelper { } - private void updateCourseCalendarEventTable(CourseEvent event, boolean clear) { + private void updateCourseCalendarEventTable(CourseEvent dbevent, boolean clear, Component event) { if(clear) { String[] whereArgs = { - Integer.toString(event.getEventId()) + Integer.toString(dbevent.getEventId()) }; database.rawQuery("DELETE FROM CourseCalendarEvent WHERE EventId = ?", whereArgs); } - String rrule = event.getRecurRule(); - String exdates = event.getExcludeDates(); + SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd'T'hhmmss"); + Date startDate = new Date((dbevent.getEventFrom())*1000); + Date endDate = new Date(startDate.getTime() + ((long)86400 * 365)*1000); + Period period = new Period(new DateTime(startDate), new DateTime(endDate)); + PeriodList recurrances = event.calculateRecurrenceSet(period); + + Log.i("DBM", "Update events table for " + dbevent.getUniqueId() + " ("+dbevent.getEventTitle()+")"); + Log.i("DBM", "events: " + recurrances.size()); + + for (Iterator iter = recurrances.iterator(); iter.hasNext();) { + Period eventper = iter.next(); - if(rrule != null && rrule.length() > 0) { - try { - Log.i("DBM", "RRule: " + rrule); - RRule rule = new RRule(rrule); - Recur recur = rule.getRecur(); - Date startDate = new Date((event.getEventFrom())*1000); - Date endDate = new Date(startDate.getTime() + (86400 * 365)*1000); - - DateTime startDateTime = new DateTime(startDate); - DateList dates = recur.getDates(startDateTime, new Period(startDateTime, new DateTime(endDate)), Value.DATE); - - Log.i("DBM", "Update events table for " + event.getUniqueId()); - Log.i("DBM", "events: " +dates.size()); - - for(int i = 0; i < dates.size(); i++) { - net.fortuna.ical4j.model.Date date = (net.fortuna.ical4j.model.Date) dates.get(i); - long eventStartTime = date.getTime() / 1000; - long eventEndTime = (eventStartTime + (event.getEventTo() - event.getEventFrom())) / 1000; - - try { - ContentValues indexValues = new ContentValues(); - indexValues.put("EventId", event.getEventId()); - indexValues.put("EventFrom", eventStartTime); - indexValues.put("EventTo", eventEndTime); - database.insertOrThrow("CourseCalendarEvent", null, indexValues); - } catch(Exception e) { - e.printStackTrace(); - } - } - - } catch (ParseException e) { - } - } else { try { + Log.i("DBM Add", dateFormat.format(eventper.getStart()) + " - " + dateFormat.format(eventper.getEnd())); + ContentValues indexValues = new ContentValues(); - indexValues.put("EventId", event.getEventId()); - indexValues.put("EventFrom", event.getEventFrom()); - indexValues.put("EventTo", event.getEventTo()); + indexValues.put("EventId", dbevent.getEventId()); + indexValues.put("EventFrom", eventper.getStart().getTime() / 1000); + indexValues.put("EventTo", eventper.getEnd().getTime() / 1000); + indexValues.put("EventType", dbevent.getCourseTypeId()); database.insertOrThrow("CourseCalendarEvent", null, indexValues); } catch(Exception e) { - e.printStackTrace(); + //e.printStackTrace(); } } - } public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) { @@ -156,7 +141,16 @@ public class VorlesungsplanDatabaseHelper { Long.toString(timeFrom), Long.toString(timeTo) }; - Cursor resultSet = database.rawQuery("SELECT Id, CourseName, UniqueId, SequenceId, EventFrom, EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId FROM CourseCalendar WHERE CourseName = ? AND EventTo >= ? AND EventFrom <= ?", whereArgs); + Cursor resultSet = database.rawQuery( + "SELECT " + + "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " + + "FROM " + + "CourseCalendarEvent " + + "LEFT JOIN " + + "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+ + "WHERE " + + "CourseName = ? AND CourseCalendarEvent.EventTo >= ? AND CourseCalendarEvent.EventFrom <= ? ", + whereArgs); ArrayList events = new ArrayList(); if(resultSet.moveToFirst()) { int[] columnIndexes = { @@ -171,7 +165,8 @@ public class VorlesungsplanDatabaseHelper { resultSet.getColumnIndex("EventStatus"), resultSet.getColumnIndex("RecurRule"), resultSet.getColumnIndex("ExcludeDates"), - resultSet.getColumnIndex("CourseGroupId") + resultSet.getColumnIndex("CourseGroupId"), + resultSet.getColumnIndex("EventType") }; do { int groupId = resultSet.getInt(columnIndexes[11]); @@ -180,9 +175,12 @@ public class VorlesungsplanDatabaseHelper { group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId); else group = null; + + int eventType = resultSet.getInt(columnIndexes[12]); + CourseEvent event = new CourseEvent(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]), resultSet.getInt(columnIndexes[3]), resultSet.getLong(columnIndexes[4]), resultSet.getLong(columnIndexes[5]), resultSet.getString(columnIndexes[6]), resultSet.getString(columnIndexes[7]), - resultSet.getString(columnIndexes[8]), resultSet.getString(columnIndexes[9]), resultSet.getString(columnIndexes[10]), group); + resultSet.getString(columnIndexes[8]), resultSet.getString(columnIndexes[9]), resultSet.getString(columnIndexes[10]), group, eventType); events.add(event); } while (resultSet.moveToNext()); @@ -194,6 +192,98 @@ public class VorlesungsplanDatabaseHelper { return eventsArr; } + public CourseEvent[] getCourseCalendarTimetable(String coursename, long timeFrom, int days) { + String[] whereArgs; + whereArgs = new String[] { + coursename, + Long.toString(timeFrom), + Integer.toString(days) + }; + ArrayList events = new ArrayList(); + + Cursor resultSet = database.rawQuery( + "SELECT " + + "COUNT(*) as EventCount, " + + "strftime('%Y%m%d', datetime(CourseCalendarEvent.EventTo, 'unixepoch')) as DayId " + + "FROM "+ + "CourseCalendarEvent " + + "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " + + "WHERE " + + "CourseCalendar.CourseName = ? AND " + + "CourseCalendarEvent.EventTo >= ? " + + "GROUP BY DayId " + + "ORDER BY DayId ASC " + + "LIMIT ? ", + whereArgs); + if(resultSet.moveToFirst()) { + do { + int eventCount = resultSet.getInt(0); + int dayId = resultSet.getInt(1); + + if(eventCount == 0) + continue; + + whereArgs = new String[] { + Integer.toString(dayId), + coursename + }; + + Cursor resultSet2 = database.rawQuery( + "SELECT " + + "CourseCalendar.Id, CourseCalendar.CourseName, CourseCalendar.UniqueId, CourseCalendar.SequenceId, " + + "CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, CourseCalendar.EventTitle, CourseCalendar.EventLocation, " + + "CourseCalendar.EventStatus, CourseCalendar.RecurRule, CourseCalendar.ExcludeDates, CourseCalendar.CourseGroupId, EventType, " + + "strftime('%Y%m%d', datetime(CourseCalendarEvent.EventTo, 'unixepoch')) as DayId " + + "FROM " + + "CourseCalendarEvent " + + "LEFT JOIN CourseCalendar ON CourseCalendarEvent.EventId = CourseCalendar.Id " + + "WHERE DayId = ? AND " + + "CourseCalendar.CourseName = ? " + + "ORDER BY CourseCalendarEvent.EventFrom ASC", + whereArgs); + + int[] columnIndexes = { + resultSet2.getColumnIndex("Id"), + resultSet2.getColumnIndex("CourseName"), + resultSet2.getColumnIndex("UniqueId"), + resultSet2.getColumnIndex("SequenceId"), + resultSet2.getColumnIndex("EventFrom"), + resultSet2.getColumnIndex("EventTo"), + resultSet2.getColumnIndex("EventTitle"), + resultSet2.getColumnIndex("EventLocation"), + resultSet2.getColumnIndex("EventStatus"), + resultSet2.getColumnIndex("RecurRule"), + resultSet2.getColumnIndex("ExcludeDates"), + resultSet2.getColumnIndex("CourseGroupId"), + resultSet2.getColumnIndex("EventType") + }; + resultSet2.moveToFirst(); + do { + int groupId = resultSet2.getInt(columnIndexes[11]); + CourseGroup group; + if(groupId > 0) + group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId); + else + group = null; + int eventType = resultSet2.getInt(columnIndexes[12]); + CourseEvent event = new CourseEvent(resultSet2.getInt(columnIndexes[0]), resultSet2.getString(columnIndexes[1]), resultSet2.getString(columnIndexes[2]), resultSet2.getInt(columnIndexes[3]), + resultSet2.getLong(columnIndexes[4]), resultSet2.getLong(columnIndexes[5]), resultSet2.getString(columnIndexes[6]), resultSet2.getString(columnIndexes[7]), + resultSet2.getString(columnIndexes[8]), resultSet2.getString(columnIndexes[9]), resultSet2.getString(columnIndexes[10]), group, eventType); + + events.add(event); + } while (resultSet2.moveToNext()); + resultSet2.close(); + } while (resultSet.moveToNext()); + resultSet.close(); + } + + CourseEvent[] eventsArr = new CourseEvent[events.size()]; + eventsArr = events.toArray(eventsArr); + return eventsArr; + } + + + public CourseGroup getCourseGroup(int courseGroupId) { CourseGroup coursegroup = null; String[] whereArgs = new String[] { @@ -242,4 +332,92 @@ public class VorlesungsplanDatabaseHelper { return null; } + public CourseGroup[] getCourseGroups(String coursename, Date notBefore) { + String[] whereArgs; + whereArgs = new String[] { + coursename + }; + ArrayList groups = new ArrayList(); + + /* + 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; + } + }