1 /* VorlesungsplanDatabaseHelper.java
3 * This program is free software: you can redistribute it and/or modify
4 * it under the terms of the GNU General Public License as published by
5 * the Free Software Foundation, either version 3 of the License, or
6 * (at your option) any later version.
8 * This program is distributed in the hope that it will be useful,
9 * but WITHOUT ANY WARRANTY; without even the implied warranty of
10 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 * GNU General Public License for more details.
13 * You should have received a copy of the GNU General Public License
14 * along with this program. If not, see <http://www.gnu.org/licenses/>.
16 package de.dhbwloe.campusapp.database;
18 import android.content.ContentValues;
19 import android.database.Cursor;
20 import android.database.sqlite.SQLiteDatabase;
21 import android.os.Bundle;
22 import android.util.Log;
24 import net.fortuna.ical4j.model.Component;
25 import net.fortuna.ical4j.model.DateList;
26 import net.fortuna.ical4j.model.DateTime;
27 import net.fortuna.ical4j.model.Period;
28 import net.fortuna.ical4j.model.PeriodList;
29 import net.fortuna.ical4j.model.Recur;
30 import net.fortuna.ical4j.model.parameter.Value;
31 import net.fortuna.ical4j.model.property.RRule;
33 import java.text.ParseException;
34 import java.text.SimpleDateFormat;
35 import java.util.ArrayList;
36 import java.util.Date;
37 import java.util.Iterator;
38 import java.util.List;
39 import java.util.TimeZone;
41 import de.dhbwloe.campusapp.CampusAppContext;
42 import de.dhbwloe.campusapp.vorlesungen.CourseEvent;
43 import de.dhbwloe.campusapp.vorlesungen.CourseGroup;
46 * Created by pk910 on 24.01.2016.
48 public class VorlesungsplanDatabaseHelper {
49 private CampusAppContext AppContext;
50 private SQLiteDatabase database;
52 public VorlesungsplanDatabaseHelper(CampusAppContext context, SQLiteDatabase database) {
53 this.database = database;
58 public void updateCourseCalendar(CourseEvent event, Component cevent) {
59 boolean isExisting = false;
60 String[] whereArgs = new String[] {
61 event.getCourseName(),
64 Cursor resultSet = database.rawQuery("SELECT SequenceId FROM CourseCalendar WHERE CourseName = ? AND UniqueId = ?", whereArgs);
65 if(resultSet.moveToFirst()) {
66 int sequence = resultSet.getInt(0);
67 if(event.getSequenceId() <= sequence)
75 ContentValues updateValues = new ContentValues();
76 updateValues.put("SequenceId", event.getSequenceId());
77 updateValues.put("EventFrom", event.getEventFrom());
78 updateValues.put("EventTo", event.getEventTo());
79 updateValues.put("EventTitle", event.getEventTitle());
80 updateValues.put("EventLocation", event.getEventLocation());
81 updateValues.put("EventStatus", event.getEventStatus());
82 updateValues.put("RecurRule", event.getRecurRule());
83 updateValues.put("ExcludeDates", event.getExcludeDates());
84 if(event.getCourseGroup() != null)
85 updateValues.put("CourseGroupId", event.getCourseGroup().getGroupId());
87 database.update("CourseCalendar", updateValues, "CourseName = ? AND UniqueId = ?", whereArgs);
88 updateCourseCalendarEventTable(event, true, cevent);
89 } catch(Exception e) {
94 ContentValues indexValues = new ContentValues();
95 indexValues.put("CourseName", event.getCourseName());
96 indexValues.put("UniqueId", event.getUniqueId());
97 indexValues.put("SequenceId", event.getSequenceId());
98 indexValues.put("EventFrom", event.getEventFrom());
99 indexValues.put("EventTo", event.getEventTo());
100 indexValues.put("EventTitle", event.getEventTitle());
101 indexValues.put("EventLocation", event.getEventLocation());
102 indexValues.put("EventStatus", event.getEventStatus());
103 indexValues.put("RecurRule", event.getRecurRule());
104 indexValues.put("ExcludeDates", event.getExcludeDates());
105 if(event.getCourseGroup() != null)
106 indexValues.put("CourseGroupId", event.getCourseGroup().getGroupId());
108 long id = database.insertOrThrow("CourseCalendar", null, indexValues);
109 event.setEventId((int) id);
110 updateCourseCalendarEventTable(event, false, cevent);
111 } catch(Exception e) {
118 private void updateCourseCalendarEventTable(CourseEvent dbevent, boolean clear, Component event) {
120 String[] whereArgs = {
121 Integer.toString(dbevent.getEventId())
123 database.rawQuery("DELETE FROM CourseCalendarEvent WHERE EventId = ?", whereArgs);
126 SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd'T'hhmmss");
127 Date startDate = new Date((dbevent.getEventFrom())*1000);
128 Date endDate = new Date(startDate.getTime() + ((long)86400 * 365)*1000);
129 Period period = new Period(new DateTime(startDate), new DateTime(endDate));
130 PeriodList recurrances = event.calculateRecurrenceSet(period);
132 Log.i("DBM", "Update events table for " + dbevent.getUniqueId() + " ("+dbevent.getEventTitle()+")");
133 Log.i("DBM", "events: " + recurrances.size());
135 for (Iterator<Period> iter = recurrances.iterator(); iter.hasNext();) {
136 Period eventper = iter.next();
139 Log.i("DBM Add", dateFormat.format(eventper.getStart()) + " - " + dateFormat.format(eventper.getEnd()));
141 ContentValues indexValues = new ContentValues();
142 indexValues.put("EventId", dbevent.getEventId());
143 indexValues.put("EventFrom", eventper.getStart().getTime() / 1000);
144 indexValues.put("EventTo", eventper.getEnd().getTime() / 1000);
145 indexValues.put("EventType", dbevent.getCourseTypeId());
146 database.insertOrThrow("CourseCalendarEvent", null, indexValues);
147 } catch(Exception e) {
148 //e.printStackTrace();
153 public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
154 String[] whereArgs = new String[] {
156 Long.toString(timeFrom),
157 Long.toString(timeTo)
159 Cursor resultSet = database.rawQuery(
161 "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " +
163 "CourseCalendarEvent " +
165 "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+
167 "CourseName = ? AND CourseCalendarEvent.EventTo >= ? AND CourseCalendarEvent.EventFrom <= ? ",
169 ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
170 if(resultSet.moveToFirst()) {
171 int[] columnIndexes = {
172 resultSet.getColumnIndex("Id"),
173 resultSet.getColumnIndex("CourseName"),
174 resultSet.getColumnIndex("UniqueId"),
175 resultSet.getColumnIndex("SequenceId"),
176 resultSet.getColumnIndex("EventFrom"),
177 resultSet.getColumnIndex("EventTo"),
178 resultSet.getColumnIndex("EventTitle"),
179 resultSet.getColumnIndex("EventLocation"),
180 resultSet.getColumnIndex("EventStatus"),
181 resultSet.getColumnIndex("RecurRule"),
182 resultSet.getColumnIndex("ExcludeDates"),
183 resultSet.getColumnIndex("CourseGroupId"),
184 resultSet.getColumnIndex("EventType")
187 int groupId = resultSet.getInt(columnIndexes[11]);
190 group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
194 int eventType = resultSet.getInt(columnIndexes[12]);
196 CourseEvent event = new CourseEvent(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]), resultSet.getInt(columnIndexes[3]),
197 resultSet.getLong(columnIndexes[4]), resultSet.getLong(columnIndexes[5]), resultSet.getString(columnIndexes[6]), resultSet.getString(columnIndexes[7]),
198 resultSet.getString(columnIndexes[8]), resultSet.getString(columnIndexes[9]), resultSet.getString(columnIndexes[10]), group, eventType);
201 } while (resultSet.moveToNext());
205 CourseEvent[] eventsArr = new CourseEvent[events.size()];
206 eventsArr = events.toArray(eventsArr);
210 public CourseEvent[] getCourseCalendarTimetable(String coursename, long timeFrom, int days) {
212 whereArgs = new String[] {
214 Long.toString(timeFrom),
215 Integer.toString(days)
217 ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
219 Cursor resultSet = database.rawQuery(
221 "COUNT(*) as EventCount, " +
222 "strftime('%Y%m%d', datetime(CourseCalendarEvent.EventTo, 'unixepoch')) as DayId " +
224 "CourseCalendarEvent " +
225 "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
227 "CourseCalendar.CourseName = ? AND " +
228 "CourseCalendarEvent.EventTo >= ? " +
230 "ORDER BY DayId ASC " +
233 if(resultSet.moveToFirst()) {
235 int eventCount = resultSet.getInt(0);
236 int dayId = resultSet.getInt(1);
241 whereArgs = new String[] {
242 Integer.toString(dayId),
246 Cursor resultSet2 = database.rawQuery(
248 "CourseCalendar.Id, CourseCalendar.CourseName, CourseCalendar.UniqueId, CourseCalendar.SequenceId, " +
249 "CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, CourseCalendar.EventTitle, CourseCalendar.EventLocation, " +
250 "CourseCalendar.EventStatus, CourseCalendar.RecurRule, CourseCalendar.ExcludeDates, CourseCalendar.CourseGroupId, EventType, " +
251 "strftime('%Y%m%d', datetime(CourseCalendarEvent.EventTo, 'unixepoch')) as DayId " +
253 "CourseCalendarEvent " +
254 "LEFT JOIN CourseCalendar ON CourseCalendarEvent.EventId = CourseCalendar.Id " +
255 "WHERE DayId = ? AND " +
256 "CourseCalendar.CourseName = ? " +
257 "ORDER BY CourseCalendarEvent.EventFrom ASC",
260 int[] columnIndexes = {
261 resultSet2.getColumnIndex("Id"),
262 resultSet2.getColumnIndex("CourseName"),
263 resultSet2.getColumnIndex("UniqueId"),
264 resultSet2.getColumnIndex("SequenceId"),
265 resultSet2.getColumnIndex("EventFrom"),
266 resultSet2.getColumnIndex("EventTo"),
267 resultSet2.getColumnIndex("EventTitle"),
268 resultSet2.getColumnIndex("EventLocation"),
269 resultSet2.getColumnIndex("EventStatus"),
270 resultSet2.getColumnIndex("RecurRule"),
271 resultSet2.getColumnIndex("ExcludeDates"),
272 resultSet2.getColumnIndex("CourseGroupId"),
273 resultSet2.getColumnIndex("EventType")
275 resultSet2.moveToFirst();
277 int groupId = resultSet2.getInt(columnIndexes[11]);
280 group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
283 int eventType = resultSet2.getInt(columnIndexes[12]);
284 CourseEvent event = new CourseEvent(resultSet2.getInt(columnIndexes[0]), resultSet2.getString(columnIndexes[1]), resultSet2.getString(columnIndexes[2]), resultSet2.getInt(columnIndexes[3]),
285 resultSet2.getLong(columnIndexes[4]), resultSet2.getLong(columnIndexes[5]), resultSet2.getString(columnIndexes[6]), resultSet2.getString(columnIndexes[7]),
286 resultSet2.getString(columnIndexes[8]), resultSet2.getString(columnIndexes[9]), resultSet2.getString(columnIndexes[10]), group, eventType);
289 } while (resultSet2.moveToNext());
291 } while (resultSet.moveToNext());
295 CourseEvent[] eventsArr = new CourseEvent[events.size()];
296 eventsArr = events.toArray(eventsArr);
300 public CourseEvent[] getCourseEventsByGroup(CourseGroup group) {
302 whereArgs = new String[] {
303 Integer.toString(group.getGroupId())
305 Cursor resultSet = database.rawQuery(
307 "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " +
309 "CourseCalendarEvent " +
311 "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+
313 "CourseGroupId = ? ORDER BY CourseCalendarEvent.EventFrom ASC ",
315 ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
316 if(resultSet.moveToFirst()) {
317 int[] columnIndexes = {
318 resultSet.getColumnIndex("Id"),
319 resultSet.getColumnIndex("CourseName"),
320 resultSet.getColumnIndex("UniqueId"),
321 resultSet.getColumnIndex("SequenceId"),
322 resultSet.getColumnIndex("EventFrom"),
323 resultSet.getColumnIndex("EventTo"),
324 resultSet.getColumnIndex("EventTitle"),
325 resultSet.getColumnIndex("EventLocation"),
326 resultSet.getColumnIndex("EventStatus"),
327 resultSet.getColumnIndex("RecurRule"),
328 resultSet.getColumnIndex("ExcludeDates"),
329 resultSet.getColumnIndex("CourseGroupId"),
330 resultSet.getColumnIndex("EventType")
333 int eventType = resultSet.getInt(columnIndexes[12]);
335 CourseEvent event = new CourseEvent(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]), resultSet.getInt(columnIndexes[3]),
336 resultSet.getLong(columnIndexes[4]), resultSet.getLong(columnIndexes[5]), resultSet.getString(columnIndexes[6]), resultSet.getString(columnIndexes[7]),
337 resultSet.getString(columnIndexes[8]), resultSet.getString(columnIndexes[9]), resultSet.getString(columnIndexes[10]), group, eventType);
340 } while (resultSet.moveToNext());
344 CourseEvent[] eventsArr = new CourseEvent[events.size()];
345 eventsArr = events.toArray(eventsArr);
349 public CourseGroup getCourseGroup(int courseGroupId) {
350 CourseGroup coursegroup = null;
351 String[] whereArgs = new String[] {
352 Integer.toString(courseGroupId)
354 Cursor resultSet = database.rawQuery("SELECT CourseName, GroupName, LastUpdate FROM CourseCalendarGroup WHERE GroupId = ?", whereArgs);
355 if(resultSet.moveToFirst()) {
356 coursegroup = new CourseGroup(courseGroupId, resultSet.getString(0), resultSet.getString(1));
362 public CourseGroup getCourseGroup(String coursename, String groupname) {
363 CourseGroup coursegroup = null;
364 String[] whereArgs = new String[] {
368 Cursor resultSet = database.rawQuery("SELECT GroupId, CourseName, GroupName, LastUpdate FROM CourseCalendarGroup WHERE CourseName = ? AND GroupName = ?", whereArgs);
369 if(resultSet.moveToFirst()) {
370 coursegroup = new CourseGroup(resultSet.getInt(0), resultSet.getString(1), resultSet.getString(2));
376 public CourseGroup addCourseGroup(String coursename, String groupname) {
377 long now = (new Date()).getTime() / 1000;
380 ContentValues indexValues = new ContentValues();
381 indexValues.put("CourseName",coursename);
382 indexValues.put("GroupName", groupname);
383 indexValues.put("LastUpdate", now);
385 id = (int) database.insertOrThrow("CourseCalendarGroup", null, indexValues);
386 } catch(Exception e) {
391 CourseGroup newGroup = new CourseGroup(id, coursename, groupname);
397 public CourseGroup[] getCourseGroups(String coursename, Date notBefore) {
399 whereArgs = new String[] {
402 ArrayList<CourseGroup> groups = new ArrayList<CourseGroup>();
405 Cursor resultSet = database.rawQuery(
406 "SELECT CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName, t1.FirstEvent, t1.LastEvent, t1.EventCount FROM " +
407 "CourseCalendarGroup, ( " +
408 "SELECT CourseCalendar.CourseGroupId, MIN(CourseCalendarEvent.EventFrom) as FirstEvent, MAX(CourseCalendarEvent.EventTo) as LastEvent, COUNT(*) as EventCount " +
409 "FROM CourseCalendarEvent " +
410 "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
411 "GROUP BY CourseCalendar.CourseGroupId " +
414 "CourseCalendarGroup.GroupId = t1.CourseGroupId AND " +
415 "CourseName = ? AND t1.LastEvent > ? " +
416 "ORDER BY FirstEvent ASC",
419 Cursor resultSet = database.rawQuery(
421 "CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName, " +
422 "MIN(CourseCalendarEvent.EventFrom) as FirstEvent, " +
424 "CASE WHEN CourseCalendarEvent.EventType IN (0, 1) " +
425 "THEN CourseCalendarEvent.EventFrom " +
427 "END) as LastEvent, " +
428 "MAX(CourseCalendarEvent.EventFrom) as LastEventFilter, " +
429 "COUNT(*) as EventCount, " +
431 "CASE WHEN CourseCalendarEvent.EventFrom < strftime('%s', 'now') " +
432 "THEN (strftime('%s', 'now')+(86400*365*5)+CourseCalendarEvent.EventFrom) " +
433 "ELSE CourseCalendarEvent.EventFrom " +
434 "END) as NextEvent, " +
436 "CASE WHEN CourseCalendarEvent.EventFrom < strftime('%s', 'now') OR CourseCalendarEvent.EventType NOT IN (2) " +
437 "THEN (strftime('%s', 'now')+(86400*365*5)+CourseCalendarEvent.EventFrom) " +
438 "ELSE CourseCalendarEvent.EventFrom " +
439 "END) as NextKlausurEvent, " +
440 "MIN(CASE WHEN CourseCalendarEvent.EventFrom <= strftime('%s', 'now') THEN 1 ELSE 0 END) as EventCompleted " +
442 "CourseCalendarEvent " +
443 "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
444 "LEFT JOIN CourseCalendarGroup ON CourseCalendarGroup.GroupId = CourseCalendar.CourseGroupId " +
446 "CourseCalendar.CourseName = ? " +
447 "GROUP BY CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName " +
448 "HAVING LastEventFilter > " + Long.toString((notBefore == null ? 0L : notBefore.getTime()/1000)) + " " +
449 "ORDER BY EventCompleted DESC, NextEvent ASC ",
451 if(resultSet.moveToFirst()) {
452 int[] columnIndexes = {
453 resultSet.getColumnIndex("GroupId"),
454 resultSet.getColumnIndex("CourseName"),
455 resultSet.getColumnIndex("GroupName"),
456 resultSet.getColumnIndex("FirstEvent"),
457 resultSet.getColumnIndex("LastEvent"),
458 resultSet.getColumnIndex("EventCount"),
459 resultSet.getColumnIndex("NextEvent"),
460 resultSet.getColumnIndex("EventCompleted"),
461 resultSet.getColumnIndex("NextKlausurEvent"),
464 CourseGroup coursegroup = new CourseGroup(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]));
465 Bundle extraData = coursegroup.getExtraData();
467 extraData.putLong("FirstEvent", resultSet.getLong(columnIndexes[3]));
468 extraData.putLong("LastEvent", resultSet.getLong(columnIndexes[4]));
469 extraData.putInt("EventCount", resultSet.getInt(columnIndexes[5]));
470 extraData.putLong("NextEvent", resultSet.getLong(columnIndexes[6]));
471 extraData.putLong("NextKlausurEvent", resultSet.getLong(columnIndexes[8]));
472 extraData.putBoolean("EventCompleted", (resultSet.getInt(columnIndexes[7]) == 1));
474 groups.add(coursegroup);
476 } while (resultSet.moveToNext());
480 CourseGroup[] groupsArr = new CourseGroup[groups.size()];
481 groupsArr = groups.toArray(groupsArr);
485 public CourseEvent[] getCourseExamEvents(String coursename, long timeFrom, long timeTo) {
486 String[] whereArgs = new String[] {
488 Long.toString(timeFrom),
489 Long.toString(timeTo)
491 Cursor resultSet = database.rawQuery(
493 "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " +
495 "CourseCalendarEvent " +
497 "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+
499 "CourseName = ? AND CourseCalendarEvent.EventTo >= ? AND CourseCalendarEvent.EventFrom <= ? AND EventType = 2",
501 ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
502 if(resultSet.moveToFirst()) {
503 int[] columnIndexes = {
504 resultSet.getColumnIndex("Id"),
505 resultSet.getColumnIndex("CourseName"),
506 resultSet.getColumnIndex("UniqueId"),
507 resultSet.getColumnIndex("SequenceId"),
508 resultSet.getColumnIndex("EventFrom"),
509 resultSet.getColumnIndex("EventTo"),
510 resultSet.getColumnIndex("EventTitle"),
511 resultSet.getColumnIndex("EventLocation"),
512 resultSet.getColumnIndex("EventStatus"),
513 resultSet.getColumnIndex("RecurRule"),
514 resultSet.getColumnIndex("ExcludeDates"),
515 resultSet.getColumnIndex("CourseGroupId"),
516 resultSet.getColumnIndex("EventType")
519 int groupId = resultSet.getInt(columnIndexes[11]);
522 group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
526 int eventType = resultSet.getInt(columnIndexes[12]);
528 CourseEvent event = new CourseEvent(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]), resultSet.getInt(columnIndexes[3]),
529 resultSet.getLong(columnIndexes[4]), resultSet.getLong(columnIndexes[5]), resultSet.getString(columnIndexes[6]), resultSet.getString(columnIndexes[7]),
530 resultSet.getString(columnIndexes[8]), resultSet.getString(columnIndexes[9]), resultSet.getString(columnIndexes[10]), group, eventType);
533 } while (resultSet.moveToNext());
537 CourseEvent[] eventsArr = new CourseEvent[events.size()];
538 eventsArr = events.toArray(eventsArr);