1 package de.dhbwloe.campusapp.database;
3 import android.content.ContentValues;
4 import android.database.Cursor;
5 import android.database.sqlite.SQLiteDatabase;
6 import android.os.Bundle;
7 import android.util.Log;
9 import net.fortuna.ical4j.model.Component;
10 import net.fortuna.ical4j.model.DateList;
11 import net.fortuna.ical4j.model.DateTime;
12 import net.fortuna.ical4j.model.Period;
13 import net.fortuna.ical4j.model.PeriodList;
14 import net.fortuna.ical4j.model.Recur;
15 import net.fortuna.ical4j.model.parameter.Value;
16 import net.fortuna.ical4j.model.property.RRule;
18 import java.text.ParseException;
19 import java.text.SimpleDateFormat;
20 import java.util.ArrayList;
21 import java.util.Date;
22 import java.util.Iterator;
23 import java.util.List;
24 import java.util.TimeZone;
26 import de.dhbwloe.campusapp.CampusAppContext;
27 import de.dhbwloe.campusapp.vorlesungen.CourseEvent;
28 import de.dhbwloe.campusapp.vorlesungen.CourseGroup;
31 * Created by pk910 on 24.01.2016.
33 public class VorlesungsplanDatabaseHelper {
34 private CampusAppContext AppContext;
35 private SQLiteDatabase database;
37 public VorlesungsplanDatabaseHelper(CampusAppContext context, SQLiteDatabase database) {
38 this.database = database;
43 public void updateCourseCalendar(CourseEvent event, Component cevent) {
44 boolean isExisting = false;
45 String[] whereArgs = new String[] {
46 event.getCourseName(),
49 Cursor resultSet = database.rawQuery("SELECT SequenceId FROM CourseCalendar WHERE CourseName = ? AND UniqueId = ?", whereArgs);
50 if(resultSet.moveToFirst()) {
51 int sequence = resultSet.getInt(0);
52 if(event.getSequenceId() <= sequence)
60 ContentValues updateValues = new ContentValues();
61 updateValues.put("SequenceId", event.getSequenceId());
62 updateValues.put("EventFrom", event.getEventFrom());
63 updateValues.put("EventTo", event.getEventTo());
64 updateValues.put("EventTitle", event.getEventTitle());
65 updateValues.put("EventLocation", event.getEventLocation());
66 updateValues.put("EventStatus", event.getEventStatus());
67 updateValues.put("RecurRule", event.getRecurRule());
68 updateValues.put("ExcludeDates", event.getExcludeDates());
69 if(event.getCourseGroup() != null)
70 updateValues.put("CourseGroupId", event.getCourseGroup().getGroupId());
72 database.update("CourseCalendar", updateValues, "CourseName = ? AND UniqueId = ?", whereArgs);
73 updateCourseCalendarEventTable(event, true, cevent);
74 } catch(Exception e) {
79 ContentValues indexValues = new ContentValues();
80 indexValues.put("CourseName", event.getCourseName());
81 indexValues.put("UniqueId", event.getUniqueId());
82 indexValues.put("SequenceId", event.getSequenceId());
83 indexValues.put("EventFrom", event.getEventFrom());
84 indexValues.put("EventTo", event.getEventTo());
85 indexValues.put("EventTitle", event.getEventTitle());
86 indexValues.put("EventLocation", event.getEventLocation());
87 indexValues.put("EventStatus", event.getEventStatus());
88 indexValues.put("RecurRule", event.getRecurRule());
89 indexValues.put("ExcludeDates", event.getExcludeDates());
90 if(event.getCourseGroup() != null)
91 indexValues.put("CourseGroupId", event.getCourseGroup().getGroupId());
93 long id = database.insertOrThrow("CourseCalendar", null, indexValues);
94 event.setEventId((int) id);
95 updateCourseCalendarEventTable(event, false, cevent);
96 } catch(Exception e) {
103 private void updateCourseCalendarEventTable(CourseEvent dbevent, boolean clear, Component event) {
105 String[] whereArgs = {
106 Integer.toString(dbevent.getEventId())
108 database.rawQuery("DELETE FROM CourseCalendarEvent WHERE EventId = ?", whereArgs);
111 SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd'T'hhmmss");
112 Date startDate = new Date((dbevent.getEventFrom())*1000);
113 Date endDate = new Date(startDate.getTime() + ((long)86400 * 365)*1000);
114 Period period = new Period(new DateTime(startDate), new DateTime(endDate));
115 PeriodList recurrances = event.calculateRecurrenceSet(period);
117 Log.i("DBM", "Update events table for " + dbevent.getUniqueId() + " ("+dbevent.getEventTitle()+")");
118 Log.i("DBM", "events: " + recurrances.size());
120 for (Iterator<Period> iter = recurrances.iterator(); iter.hasNext();) {
121 Period eventper = iter.next();
124 Log.i("DBM Add", dateFormat.format(eventper.getStart()) + " - " + dateFormat.format(eventper.getEnd()));
126 ContentValues indexValues = new ContentValues();
127 indexValues.put("EventId", dbevent.getEventId());
128 indexValues.put("EventFrom", eventper.getStart().getTime() / 1000);
129 indexValues.put("EventTo", eventper.getEnd().getTime() / 1000);
130 indexValues.put("EventType", dbevent.getCourseTypeId());
131 database.insertOrThrow("CourseCalendarEvent", null, indexValues);
132 } catch(Exception e) {
133 //e.printStackTrace();
138 public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
139 String[] whereArgs = new String[] {
141 Long.toString(timeFrom),
142 Long.toString(timeTo)
144 Cursor resultSet = database.rawQuery(
146 "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " +
148 "CourseCalendarEvent " +
150 "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+
152 "CourseName = ? AND CourseCalendarEvent.EventTo >= ? AND CourseCalendarEvent.EventFrom <= ? ",
154 ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
155 if(resultSet.moveToFirst()) {
156 int[] columnIndexes = {
157 resultSet.getColumnIndex("Id"),
158 resultSet.getColumnIndex("CourseName"),
159 resultSet.getColumnIndex("UniqueId"),
160 resultSet.getColumnIndex("SequenceId"),
161 resultSet.getColumnIndex("EventFrom"),
162 resultSet.getColumnIndex("EventTo"),
163 resultSet.getColumnIndex("EventTitle"),
164 resultSet.getColumnIndex("EventLocation"),
165 resultSet.getColumnIndex("EventStatus"),
166 resultSet.getColumnIndex("RecurRule"),
167 resultSet.getColumnIndex("ExcludeDates"),
168 resultSet.getColumnIndex("CourseGroupId"),
169 resultSet.getColumnIndex("EventType")
172 int groupId = resultSet.getInt(columnIndexes[11]);
175 group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
179 int eventType = resultSet.getInt(columnIndexes[12]);
181 CourseEvent event = new CourseEvent(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]), resultSet.getInt(columnIndexes[3]),
182 resultSet.getLong(columnIndexes[4]), resultSet.getLong(columnIndexes[5]), resultSet.getString(columnIndexes[6]), resultSet.getString(columnIndexes[7]),
183 resultSet.getString(columnIndexes[8]), resultSet.getString(columnIndexes[9]), resultSet.getString(columnIndexes[10]), group, eventType);
186 } while (resultSet.moveToNext());
190 CourseEvent[] eventsArr = new CourseEvent[events.size()];
191 eventsArr = events.toArray(eventsArr);
195 public CourseEvent[] getCourseCalendarTimetable(String coursename, long timeFrom, int days) {
197 whereArgs = new String[] {
199 Long.toString(timeFrom),
200 Integer.toString(days)
202 ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
204 Cursor resultSet = database.rawQuery(
206 "COUNT(*) as EventCount, " +
207 "strftime('%Y%m%d', datetime(CourseCalendarEvent.EventTo, 'unixepoch')) as DayId " +
209 "CourseCalendarEvent " +
210 "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
212 "CourseCalendar.CourseName = ? AND " +
213 "CourseCalendarEvent.EventTo >= ? " +
215 "ORDER BY DayId ASC " +
218 if(resultSet.moveToFirst()) {
220 int eventCount = resultSet.getInt(0);
221 int dayId = resultSet.getInt(1);
226 whereArgs = new String[] {
227 Integer.toString(dayId),
231 Cursor resultSet2 = database.rawQuery(
233 "CourseCalendar.Id, CourseCalendar.CourseName, CourseCalendar.UniqueId, CourseCalendar.SequenceId, " +
234 "CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, CourseCalendar.EventTitle, CourseCalendar.EventLocation, " +
235 "CourseCalendar.EventStatus, CourseCalendar.RecurRule, CourseCalendar.ExcludeDates, CourseCalendar.CourseGroupId, EventType, " +
236 "strftime('%Y%m%d', datetime(CourseCalendarEvent.EventTo, 'unixepoch')) as DayId " +
238 "CourseCalendarEvent " +
239 "LEFT JOIN CourseCalendar ON CourseCalendarEvent.EventId = CourseCalendar.Id " +
240 "WHERE DayId = ? AND " +
241 "CourseCalendar.CourseName = ? " +
242 "ORDER BY CourseCalendarEvent.EventFrom ASC",
245 int[] columnIndexes = {
246 resultSet2.getColumnIndex("Id"),
247 resultSet2.getColumnIndex("CourseName"),
248 resultSet2.getColumnIndex("UniqueId"),
249 resultSet2.getColumnIndex("SequenceId"),
250 resultSet2.getColumnIndex("EventFrom"),
251 resultSet2.getColumnIndex("EventTo"),
252 resultSet2.getColumnIndex("EventTitle"),
253 resultSet2.getColumnIndex("EventLocation"),
254 resultSet2.getColumnIndex("EventStatus"),
255 resultSet2.getColumnIndex("RecurRule"),
256 resultSet2.getColumnIndex("ExcludeDates"),
257 resultSet2.getColumnIndex("CourseGroupId"),
258 resultSet2.getColumnIndex("EventType")
260 resultSet2.moveToFirst();
262 int groupId = resultSet2.getInt(columnIndexes[11]);
265 group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
268 int eventType = resultSet2.getInt(columnIndexes[12]);
269 CourseEvent event = new CourseEvent(resultSet2.getInt(columnIndexes[0]), resultSet2.getString(columnIndexes[1]), resultSet2.getString(columnIndexes[2]), resultSet2.getInt(columnIndexes[3]),
270 resultSet2.getLong(columnIndexes[4]), resultSet2.getLong(columnIndexes[5]), resultSet2.getString(columnIndexes[6]), resultSet2.getString(columnIndexes[7]),
271 resultSet2.getString(columnIndexes[8]), resultSet2.getString(columnIndexes[9]), resultSet2.getString(columnIndexes[10]), group, eventType);
274 } while (resultSet2.moveToNext());
276 } while (resultSet.moveToNext());
280 CourseEvent[] eventsArr = new CourseEvent[events.size()];
281 eventsArr = events.toArray(eventsArr);
287 public CourseGroup getCourseGroup(int courseGroupId) {
288 CourseGroup coursegroup = null;
289 String[] whereArgs = new String[] {
290 Integer.toString(courseGroupId)
292 Cursor resultSet = database.rawQuery("SELECT CourseName, GroupName, LastUpdate FROM CourseCalendarGroup WHERE GroupId = ?", whereArgs);
293 if(resultSet.moveToFirst()) {
294 coursegroup = new CourseGroup(courseGroupId, resultSet.getString(0), resultSet.getString(1));
300 public CourseGroup getCourseGroup(String coursename, String groupname) {
301 CourseGroup coursegroup = null;
302 String[] whereArgs = new String[] {
306 Cursor resultSet = database.rawQuery("SELECT GroupId, CourseName, GroupName, LastUpdate FROM CourseCalendarGroup WHERE CourseName = ? AND GroupName = ?", whereArgs);
307 if(resultSet.moveToFirst()) {
308 coursegroup = new CourseGroup(resultSet.getInt(0), resultSet.getString(1), resultSet.getString(2));
314 public CourseGroup addCourseGroup(String coursename, String groupname) {
315 long now = (new Date()).getTime() / 1000;
318 ContentValues indexValues = new ContentValues();
319 indexValues.put("CourseName",coursename);
320 indexValues.put("GroupName", groupname);
321 indexValues.put("LastUpdate", now);
323 id = (int) database.insertOrThrow("CourseCalendarGroup", null, indexValues);
324 } catch(Exception e) {
329 CourseGroup newGroup = new CourseGroup(id, coursename, groupname);
335 public CourseGroup[] getCourseGroups(String coursename, Date notBefore) {
337 whereArgs = new String[] {
340 ArrayList<CourseGroup> groups = new ArrayList<CourseGroup>();
343 Cursor resultSet = database.rawQuery(
344 "SELECT CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName, t1.FirstEvent, t1.LastEvent, t1.EventCount FROM " +
345 "CourseCalendarGroup, ( " +
346 "SELECT CourseCalendar.CourseGroupId, MIN(CourseCalendarEvent.EventFrom) as FirstEvent, MAX(CourseCalendarEvent.EventTo) as LastEvent, COUNT(*) as EventCount " +
347 "FROM CourseCalendarEvent " +
348 "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
349 "GROUP BY CourseCalendar.CourseGroupId " +
352 "CourseCalendarGroup.GroupId = t1.CourseGroupId AND " +
353 "CourseName = ? AND t1.LastEvent > ? " +
354 "ORDER BY FirstEvent ASC",
357 Cursor resultSet = database.rawQuery(
359 "CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName, " +
360 "MIN(CourseCalendarEvent.EventFrom) as FirstEvent, " +
362 "CASE WHEN CourseCalendarEvent.EventType IN (0, 1) " +
363 "THEN CourseCalendarEvent.EventFrom " +
365 "END) as LastEvent, " +
366 "MAX(CourseCalendarEvent.EventFrom) as LastEventFilter, " +
367 "COUNT(*) as EventCount, " +
369 "CASE WHEN CourseCalendarEvent.EventFrom < strftime('%s', 'now') " +
370 "THEN (strftime('%s', 'now')+(86400*365*5)+CourseCalendarEvent.EventFrom) " +
371 "ELSE CourseCalendarEvent.EventFrom " +
372 "END) as NextEvent, " +
374 "CASE WHEN CourseCalendarEvent.EventFrom < strftime('%s', 'now') OR CourseCalendarEvent.EventType NOT IN (2) " +
375 "THEN (strftime('%s', 'now')+(86400*365*5)+CourseCalendarEvent.EventFrom) " +
376 "ELSE CourseCalendarEvent.EventFrom " +
377 "END) as NextKlausurEvent, " +
378 "MIN(CASE WHEN CourseCalendarEvent.EventFrom <= strftime('%s', 'now') THEN 1 ELSE 0 END) as EventCompleted " +
380 "CourseCalendarEvent " +
381 "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
382 "LEFT JOIN CourseCalendarGroup ON CourseCalendarGroup.GroupId = CourseCalendar.CourseGroupId " +
384 "CourseCalendar.CourseName = ? " +
385 "GROUP BY CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName " +
386 "HAVING LastEventFilter > " + Long.toString((notBefore == null ? 0L : notBefore.getTime()/1000)) + " " +
387 "ORDER BY EventCompleted DESC, NextEvent ASC ",
389 if(resultSet.moveToFirst()) {
390 int[] columnIndexes = {
391 resultSet.getColumnIndex("GroupId"),
392 resultSet.getColumnIndex("CourseName"),
393 resultSet.getColumnIndex("GroupName"),
394 resultSet.getColumnIndex("FirstEvent"),
395 resultSet.getColumnIndex("LastEvent"),
396 resultSet.getColumnIndex("EventCount"),
397 resultSet.getColumnIndex("NextEvent"),
398 resultSet.getColumnIndex("EventCompleted"),
399 resultSet.getColumnIndex("NextKlausurEvent"),
402 CourseGroup coursegroup = new CourseGroup(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]));
403 Bundle extraData = coursegroup.getExtraData();
405 extraData.putLong("FirstEvent", resultSet.getLong(columnIndexes[3]));
406 extraData.putLong("LastEvent", resultSet.getLong(columnIndexes[4]));
407 extraData.putInt("EventCount", resultSet.getInt(columnIndexes[5]));
408 extraData.putLong("NextEvent", resultSet.getLong(columnIndexes[6]));
409 extraData.putLong("NextKlausurEvent", resultSet.getLong(columnIndexes[8]));
410 extraData.putBoolean("EventCompleted", (resultSet.getInt(columnIndexes[7]) == 1));
412 groups.add(coursegroup);
414 } while (resultSet.moveToNext());
418 CourseGroup[] groupsArr = new CourseGroup[groups.size()];
419 groupsArr = groups.toArray(groupsArr);