app/src/main/java/de/dhbwloe/campusapp/database/VorlesungsplanDatabaseHelper.java

   1 package de.dhbwloe.campusapp.database;
   2
   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;
   8
   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;
  17
  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;
  25
  26 import de.dhbwloe.campusapp.CampusAppContext;
  27 import de.dhbwloe.campusapp.vorlesungen.CourseEvent;
  28 import de.dhbwloe.campusapp.vorlesungen.CourseGroup;
  29
  30 /**
  31  * Created by pk910 on 24.01.2016.
  32  */
  33 public class VorlesungsplanDatabaseHelper {
  34     private CampusAppContext AppContext;
  35     private SQLiteDatabase database;
  36
  37     public VorlesungsplanDatabaseHelper(CampusAppContext context, SQLiteDatabase database) {
  38         this.database = database;
  39         AppContext = context;
  40     }
  41
  42
  43     public void updateCourseCalendar(CourseEvent event, Component cevent) {
  44         boolean isExisting = false;
  45         String[] whereArgs = new String[] {
  46                 event.getCourseName(),
  47                 event.getUniqueId()
  48         };
  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)
  53                 return;
  54             isExisting = true;
  55         }
  56         resultSet.close();
  57
  58         if(isExisting) {
  59             try {
  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());
  71
  72                 database.update("CourseCalendar", updateValues, "CourseName = ? AND UniqueId = ?", whereArgs);
  73                 updateCourseCalendarEventTable(event, true, cevent);
  74             } catch(Exception e) {
  75                 e.printStackTrace();
  76             }
  77         } else {
  78             try {
  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());
  92
  93                 long id = database.insertOrThrow("CourseCalendar", null, indexValues);
  94                 event.setEventId((int) id);
  95                 updateCourseCalendarEventTable(event, false, cevent);
  96             } catch(Exception e) {
  97                 e.printStackTrace();
  98             }
  99         }
 100
 101     }
 102
 103     private void updateCourseCalendarEventTable(CourseEvent dbevent, boolean clear, Component event) {
 104         if(clear) {
 105             String[] whereArgs = {
 106                     Integer.toString(dbevent.getEventId())
 107             };
 108             database.rawQuery("DELETE FROM CourseCalendarEvent WHERE EventId = ?", whereArgs);
 109         }
 110
 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);
 116
 117         Log.i("DBM", "Update events table for " + dbevent.getUniqueId() + " ("+dbevent.getEventTitle()+")");
 118         Log.i("DBM", "events: " + recurrances.size());
 119
 120         for (Iterator<Period> iter = recurrances.iterator(); iter.hasNext();) {
 121             Period eventper = iter.next();
 122
 123             try {
 124                 Log.i("DBM Add", dateFormat.format(eventper.getStart()) + " - " + dateFormat.format(eventper.getEnd()));
 125
 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();
 134             }
 135         }
 136     }
 137
 138     public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
 139         String[] whereArgs = new String[] {
 140                 coursename,
 141                 Long.toString(timeFrom),
 142                 Long.toString(timeTo)
 143         };
 144         Cursor resultSet = database.rawQuery(
 145                 "SELECT " +
 146                         "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " +
 147                 "FROM " +
 148                         "CourseCalendarEvent " +
 149                 "LEFT JOIN " +
 150                         "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+
 151                 "WHERE " +
 152                         "CourseName = ? AND CourseCalendarEvent.EventTo >= ? AND CourseCalendarEvent.EventFrom <= ? ",
 153                 whereArgs);
 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")
 170             };
 171             do {
 172                 int groupId = resultSet.getInt(columnIndexes[11]);
 173                 CourseGroup group;
 174                 if(groupId > 0)
 175                     group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
 176                 else
 177                     group = null;
 178
 179                 int eventType = resultSet.getInt(columnIndexes[12]);
 180
 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);
 184
 185                 events.add(event);
 186             } while (resultSet.moveToNext());
 187         }
 188         resultSet.close();
 189
 190         CourseEvent[] eventsArr = new CourseEvent[events.size()];
 191         eventsArr = events.toArray(eventsArr);
 192         return eventsArr;
 193     }
 194
 195     public CourseEvent[] getCourseCalendarTimetable(String coursename, long timeFrom, int days) {
 196         String[] whereArgs;
 197         whereArgs = new String[] {
 198                 coursename,
 199                 Long.toString(timeFrom),
 200                 Integer.toString(days)
 201         };
 202         ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
 203
 204         Cursor resultSet = database.rawQuery(
 205                 "SELECT " +
 206                     "COUNT(*) as EventCount, " +
 207                     "strftime('%Y%m%d', datetime(CourseCalendarEvent.EventTo, 'unixepoch')) as DayId " +
 208                 "FROM "+
 209                     "CourseCalendarEvent " +
 210                 "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
 211                 "WHERE " +
 212                     "CourseCalendar.CourseName = ? AND " +
 213                     "CourseCalendarEvent.EventTo >= ? " +
 214                 "GROUP BY DayId " +
 215                 "ORDER BY DayId ASC " +
 216                 "LIMIT ? ",
 217             whereArgs);
 218         if(resultSet.moveToFirst()) {
 219             do {
 220                 int eventCount = resultSet.getInt(0);
 221                 int dayId = resultSet.getInt(1);
 222
 223                 if(eventCount == 0)
 224                     continue;
 225
 226                 whereArgs = new String[] {
 227                     Integer.toString(dayId),
 228                         coursename
 229                 };
 230
 231                 Cursor resultSet2 = database.rawQuery(
 232                         "SELECT " +
 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 " +
 237                         "FROM " +
 238                             "CourseCalendarEvent " +
 239                         "LEFT JOIN CourseCalendar ON CourseCalendarEvent.EventId = CourseCalendar.Id " +
 240                         "WHERE DayId = ? AND " +
 241                             "CourseCalendar.CourseName = ? " +
 242                         "ORDER BY CourseCalendarEvent.EventFrom ASC",
 243                     whereArgs);
 244
 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")
 259                 };
 260                 resultSet2.moveToFirst();
 261                 do {
 262                     int groupId = resultSet2.getInt(columnIndexes[11]);
 263                     CourseGroup group;
 264                     if(groupId > 0)
 265                         group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
 266                     else
 267                         group = null;
 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);
 272
 273                     events.add(event);
 274                 } while (resultSet2.moveToNext());
 275                 resultSet2.close();
 276             } while (resultSet.moveToNext());
 277             resultSet.close();
 278         }
 279
 280         CourseEvent[] eventsArr = new CourseEvent[events.size()];
 281         eventsArr = events.toArray(eventsArr);
 282         return eventsArr;
 283     }
 284
 285
 286
 287     public CourseGroup getCourseGroup(int courseGroupId) {
 288         CourseGroup coursegroup = null;
 289         String[] whereArgs = new String[] {
 290                 Integer.toString(courseGroupId)
 291         };
 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));
 295         }
 296         resultSet.close();
 297         return coursegroup;
 298     }
 299
 300     public CourseGroup getCourseGroup(String coursename, String groupname) {
 301         CourseGroup coursegroup = null;
 302         String[] whereArgs = new String[] {
 303                 coursename,
 304                 groupname
 305         };
 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));
 309         }
 310         resultSet.close();
 311         return coursegroup;
 312     }
 313
 314     public CourseGroup addCourseGroup(String coursename, String groupname) {
 315         long now = (new Date()).getTime() / 1000;
 316         int id = 0;
 317         try {
 318             ContentValues indexValues = new ContentValues();
 319             indexValues.put("CourseName",coursename);
 320             indexValues.put("GroupName", groupname);
 321             indexValues.put("LastUpdate", now);
 322
 323             id = (int) database.insertOrThrow("CourseCalendarGroup", null, indexValues);
 324         } catch(Exception e) {
 325             e.printStackTrace();
 326         }
 327
 328         if(id > 0) {
 329             CourseGroup newGroup = new CourseGroup(id, coursename, groupname);
 330             return newGroup;
 331         } else
 332             return null;
 333     }
 334
 335     public CourseGroup[] getCourseGroups(String coursename, Date notBefore) {
 336         String[] whereArgs;
 337         whereArgs = new String[] {
 338                 coursename
 339         };
 340         ArrayList<CourseGroup> groups = new ArrayList<CourseGroup>();
 341
 342         /*
 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 " +
 350                         ") as t1 " +
 351                         "WHERE " +
 352                         "CourseCalendarGroup.GroupId = t1.CourseGroupId AND " +
 353                         "CourseName = ? AND t1.LastEvent > ? " +
 354                         "ORDER BY FirstEvent ASC",
 355                 whereArgs);
 356         */
 357         Cursor resultSet = database.rawQuery(
 358                 "SELECT " +
 359                     "CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName, " +
 360                     "MIN(CourseCalendarEvent.EventFrom) as FirstEvent, " +
 361                     "MAX(" +
 362                         "CASE WHEN CourseCalendarEvent.EventType IN (0, 1) " +
 363                         "THEN CourseCalendarEvent.EventFrom " +
 364                         "ELSE 0 " +
 365                     "END) as LastEvent, " +
 366                     "MAX(CourseCalendarEvent.EventFrom) as LastEventFilter, " +
 367                     "COUNT(*) as EventCount, " +
 368                     "MIN(" +
 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, " +
 373                     "MIN(" +
 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 " +
 379                 "FROM " +
 380                     "CourseCalendarEvent " +
 381                     "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
 382                     "LEFT JOIN CourseCalendarGroup ON CourseCalendarGroup.GroupId = CourseCalendar.CourseGroupId " +
 383                 "WHERE " +
 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 ",
 388             whereArgs);
 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"),
 400             };
 401             do {
 402                 CourseGroup coursegroup = new CourseGroup(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]));
 403                 Bundle extraData = coursegroup.getExtraData();
 404
 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));
 411
 412                 groups.add(coursegroup);
 413
 414             } while (resultSet.moveToNext());
 415             resultSet.close();
 416         }
 417
 418         CourseGroup[] groupsArr = new CourseGroup[groups.size()];
 419         groupsArr = groups.toArray(groupsArr);
 420         return groupsArr;
 421     }
 422
 423 }