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;
}
- public void updateCourseCalendar(CourseEvent event) {
+ public void updateCourseCalendar(CourseEvent event, Component cevent) {
boolean isExisting = false;
String[] whereArgs = new String[] {
event.getCourseName(),
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();
}
long id = database.insertOrThrow("CourseCalendar", null, indexValues);
event.setEventId((int) id);
- updateCourseCalendarEventTable(event, false);
+ updateCourseCalendarEventTable(event, false, cevent);
} catch(Exception e) {
e.printStackTrace();
}
}
- 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<Period> 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) {
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<CourseEvent> events = new ArrayList<CourseEvent>();
if(resultSet.moveToFirst()) {
int[] columnIndexes = {
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]);
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());
return eventsArr;
}
+ public CourseEvent[] getCourseCalendarTimetable(String coursename, long timeFrom, int days) {
+ String[] whereArgs;
+ whereArgs = new String[] {
+ coursename,
+ Long.toString(timeFrom),
+ Integer.toString(days)
+ };
+ ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
+
+ 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[] {
return null;
}
+ 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;
+ }
+
}
projects / DHBWCampusApp.git / blobdiff