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.util.Log;
8 import net.fortuna.ical4j.model.DateList;
9 import net.fortuna.ical4j.model.DateTime;
10 import net.fortuna.ical4j.model.Period;
11 import net.fortuna.ical4j.model.Recur;
12 import net.fortuna.ical4j.model.parameter.Value;
13 import net.fortuna.ical4j.model.property.RRule;
15 import java.text.ParseException;
16 import java.util.ArrayList;
17 import java.util.Date;
19 import de.dhbwloe.campusapp.CampusAppContext;
20 import de.dhbwloe.campusapp.vorlesungen.CourseEvent;
21 import de.dhbwloe.campusapp.vorlesungen.CourseGroup;
24 * Created by pk910 on 24.01.2016.
26 public class VorlesungsplanDatabaseHelper {
27 private CampusAppContext AppContext;
28 private SQLiteDatabase database;
30 public VorlesungsplanDatabaseHelper(CampusAppContext context, SQLiteDatabase database) {
31 this.database = database;
36 public void updateCourseCalendar(CourseEvent event) {
37 boolean isExisting = false;
38 String[] whereArgs = new String[] {
39 event.getCourseName(),
42 Cursor resultSet = database.rawQuery("SELECT SequenceId FROM CourseCalendar WHERE CourseName = ? AND UniqueId = ?", whereArgs);
43 if(resultSet.moveToFirst()) {
44 int sequence = resultSet.getInt(0);
45 if(event.getSequenceId() <= sequence)
53 ContentValues updateValues = new ContentValues();
54 updateValues.put("SequenceId", event.getSequenceId());
55 updateValues.put("EventFrom", event.getEventFrom());
56 updateValues.put("EventTo", event.getEventTo());
57 updateValues.put("EventTitle", event.getEventTitle());
58 updateValues.put("EventLocation", event.getEventLocation());
59 updateValues.put("EventStatus", event.getEventStatus());
60 updateValues.put("RecurRule", event.getRecurRule());
61 updateValues.put("ExcludeDates", event.getExcludeDates());
62 if(event.getCourseGroup() != null)
63 updateValues.put("CourseGroupId", event.getCourseGroup().getGroupId());
65 database.update("CourseCalendar", updateValues, "CourseName = ? AND UniqueId = ?", whereArgs);
66 updateCourseCalendarEventTable(event, true);
67 } catch(Exception e) {
72 ContentValues indexValues = new ContentValues();
73 indexValues.put("CourseName", event.getCourseName());
74 indexValues.put("UniqueId", event.getUniqueId());
75 indexValues.put("SequenceId", event.getSequenceId());
76 indexValues.put("EventFrom", event.getEventFrom());
77 indexValues.put("EventTo", event.getEventTo());
78 indexValues.put("EventTitle", event.getEventTitle());
79 indexValues.put("EventLocation", event.getEventLocation());
80 indexValues.put("EventStatus", event.getEventStatus());
81 indexValues.put("RecurRule", event.getRecurRule());
82 indexValues.put("ExcludeDates", event.getExcludeDates());
83 if(event.getCourseGroup() != null)
84 indexValues.put("CourseGroupId", event.getCourseGroup().getGroupId());
86 long id = database.insertOrThrow("CourseCalendar", null, indexValues);
87 event.setEventId((int) id);
88 updateCourseCalendarEventTable(event, false);
89 } catch(Exception e) {
96 private void updateCourseCalendarEventTable(CourseEvent event, boolean clear) {
98 String[] whereArgs = {
99 Integer.toString(event.getEventId())
101 database.rawQuery("DELETE FROM CourseCalendarEvent WHERE EventId = ?", whereArgs);
104 String rrule = event.getRecurRule();
105 String exdates = event.getExcludeDates();
107 if(rrule != null && rrule.length() > 0) {
109 Log.i("DBM", "RRule: " + rrule);
110 RRule rule = new RRule(rrule);
111 Recur recur = rule.getRecur();
112 Date startDate = new Date((event.getEventFrom())*1000);
113 Date endDate = new Date(startDate.getTime() + (86400 * 365)*1000);
115 DateTime startDateTime = new DateTime(startDate);
116 DateList dates = recur.getDates(startDateTime, new Period(startDateTime, new DateTime(endDate)), Value.DATE);
118 Log.i("DBM", "Update events table for " + event.getUniqueId());
119 Log.i("DBM", "events: " +dates.size());
121 for(int i = 0; i < dates.size(); i++) {
122 net.fortuna.ical4j.model.Date date = (net.fortuna.ical4j.model.Date) dates.get(i);
123 long eventStartTime = date.getTime() / 1000;
124 long eventEndTime = (eventStartTime + (event.getEventTo() - event.getEventFrom())) / 1000;
127 ContentValues indexValues = new ContentValues();
128 indexValues.put("EventId", event.getEventId());
129 indexValues.put("EventFrom", eventStartTime);
130 indexValues.put("EventTo", eventEndTime);
131 database.insertOrThrow("CourseCalendarEvent", null, indexValues);
132 } catch(Exception e) {
137 } catch (ParseException e) {
141 ContentValues indexValues = new ContentValues();
142 indexValues.put("EventId", event.getEventId());
143 indexValues.put("EventFrom", event.getEventFrom());
144 indexValues.put("EventTo", event.getEventTo());
145 database.insertOrThrow("CourseCalendarEvent", null, indexValues);
146 } catch(Exception e) {
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("SELECT Id, CourseName, UniqueId, SequenceId, EventFrom, EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId FROM CourseCalendar WHERE CourseName = ? AND EventTo >= ? AND EventFrom <= ?", whereArgs);
160 ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
161 if(resultSet.moveToFirst()) {
162 int[] columnIndexes = {
163 resultSet.getColumnIndex("Id"),
164 resultSet.getColumnIndex("CourseName"),
165 resultSet.getColumnIndex("UniqueId"),
166 resultSet.getColumnIndex("SequenceId"),
167 resultSet.getColumnIndex("EventFrom"),
168 resultSet.getColumnIndex("EventTo"),
169 resultSet.getColumnIndex("EventTitle"),
170 resultSet.getColumnIndex("EventLocation"),
171 resultSet.getColumnIndex("EventStatus"),
172 resultSet.getColumnIndex("RecurRule"),
173 resultSet.getColumnIndex("ExcludeDates"),
174 resultSet.getColumnIndex("CourseGroupId")
177 int groupId = resultSet.getInt(columnIndexes[11]);
180 group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
183 CourseEvent event = new CourseEvent(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]), resultSet.getInt(columnIndexes[3]),
184 resultSet.getLong(columnIndexes[4]), resultSet.getLong(columnIndexes[5]), resultSet.getString(columnIndexes[6]), resultSet.getString(columnIndexes[7]),
185 resultSet.getString(columnIndexes[8]), resultSet.getString(columnIndexes[9]), resultSet.getString(columnIndexes[10]), group);
188 } while (resultSet.moveToNext());
192 CourseEvent[] eventsArr = new CourseEvent[events.size()];
193 eventsArr = events.toArray(eventsArr);
197 public CourseGroup getCourseGroup(int courseGroupId) {
198 CourseGroup coursegroup = null;
199 String[] whereArgs = new String[] {
200 Integer.toString(courseGroupId)
202 Cursor resultSet = database.rawQuery("SELECT CourseName, GroupName, LastUpdate FROM CourseCalendarGroup WHERE GroupId = ?", whereArgs);
203 if(resultSet.moveToFirst()) {
204 coursegroup = new CourseGroup(courseGroupId, resultSet.getString(0), resultSet.getString(1));
210 public CourseGroup getCourseGroup(String coursename, String groupname) {
211 CourseGroup coursegroup = null;
212 String[] whereArgs = new String[] {
216 Cursor resultSet = database.rawQuery("SELECT GroupId, CourseName, GroupName, LastUpdate FROM CourseCalendarGroup WHERE CourseName = ? AND GroupName = ?", whereArgs);
217 if(resultSet.moveToFirst()) {
218 coursegroup = new CourseGroup(resultSet.getInt(0), resultSet.getString(1), resultSet.getString(2));
224 public CourseGroup addCourseGroup(String coursename, String groupname) {
225 long now = (new Date()).getTime() / 1000;
228 ContentValues indexValues = new ContentValues();
229 indexValues.put("CourseName",coursename);
230 indexValues.put("GroupName", groupname);
231 indexValues.put("LastUpdate", now);
233 id = (int) database.insertOrThrow("CourseCalendarGroup", null, indexValues);
234 } catch(Exception e) {
239 CourseGroup newGroup = new CourseGroup(id, coursename, groupname);