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

   1 /* VorlesungsplanDatabaseHelper.java
   2  *
   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.
   7  *
   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.
  12  *
  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/>.
  15  */
  16 package de.dhbwloe.campusapp.database;
  17
  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;
  23
  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;
  32
  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;
  40
  41 import de.dhbwloe.campusapp.CampusAppContext;
  42 import de.dhbwloe.campusapp.vorlesungen.CourseEvent;
  43 import de.dhbwloe.campusapp.vorlesungen.CourseGroup;
  44
  45 /**
  46  * Created by pk910 on 24.01.2016.
  47  */
  48 public class VorlesungsplanDatabaseHelper {
  49     private CampusAppContext AppContext;
  50     private SQLiteDatabase database;
  51
  52     public VorlesungsplanDatabaseHelper(CampusAppContext context, SQLiteDatabase database) {
  53         this.database = database;
  54         AppContext = context;
  55     }
  56
  57
  58     public void updateCourseCalendar(CourseEvent event, Component cevent) {
  59         boolean isExisting = false;
  60         String[] whereArgs = new String[] {
  61                 event.getCourseName(),
  62                 event.getUniqueId()
  63         };
  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)
  68                 return;
  69             isExisting = true;
  70         }
  71         resultSet.close();
  72
  73         if(isExisting) {
  74             try {
  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());
  86
  87                 database.update("CourseCalendar", updateValues, "CourseName = ? AND UniqueId = ?", whereArgs);
  88                 updateCourseCalendarEventTable(event, true, cevent);
  89             } catch(Exception e) {
  90                 e.printStackTrace();
  91             }
  92         } else {
  93             try {
  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());
 107
 108                 long id = database.insertOrThrow("CourseCalendar", null, indexValues);
 109                 event.setEventId((int) id);
 110                 updateCourseCalendarEventTable(event, false, cevent);
 111             } catch(Exception e) {
 112                 e.printStackTrace();
 113             }
 114         }
 115
 116     }
 117
 118     private void updateCourseCalendarEventTable(CourseEvent dbevent, boolean clear, Component event) {
 119         if(clear) {
 120             String[] whereArgs = {
 121                     Integer.toString(dbevent.getEventId())
 122             };
 123             database.rawQuery("DELETE FROM CourseCalendarEvent WHERE EventId = ?", whereArgs);
 124         }
 125
 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);
 131
 132         Log.i("DBM", "Update events table for " + dbevent.getUniqueId() + " ("+dbevent.getEventTitle()+")");
 133         Log.i("DBM", "events: " + recurrances.size());
 134
 135         for (Iterator<Period> iter = recurrances.iterator(); iter.hasNext();) {
 136             Period eventper = iter.next();
 137
 138             try {
 139                 Log.i("DBM Add", dateFormat.format(eventper.getStart()) + " - " + dateFormat.format(eventper.getEnd()));
 140
 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();
 149             }
 150         }
 151     }
 152
 153     public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
 154         String[] whereArgs = new String[] {
 155                 coursename,
 156                 Long.toString(timeFrom),
 157                 Long.toString(timeTo)
 158         };
 159         Cursor resultSet = database.rawQuery(
 160                 "SELECT " +
 161                         "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " +
 162                 "FROM " +
 163                         "CourseCalendarEvent " +
 164                 "LEFT JOIN " +
 165                         "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+
 166                 "WHERE " +
 167                         "CourseName = ? AND CourseCalendarEvent.EventTo >= ? AND CourseCalendarEvent.EventFrom <= ? ",
 168                 whereArgs);
 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")
 185             };
 186             do {
 187                 int groupId = resultSet.getInt(columnIndexes[11]);
 188                 CourseGroup group;
 189                 if(groupId > 0)
 190                     group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
 191                 else
 192                     group = null;
 193
 194                 int eventType = resultSet.getInt(columnIndexes[12]);
 195
 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);
 199
 200                 events.add(event);
 201             } while (resultSet.moveToNext());
 202         }
 203         resultSet.close();
 204
 205         CourseEvent[] eventsArr = new CourseEvent[events.size()];
 206         eventsArr = events.toArray(eventsArr);
 207         return eventsArr;
 208     }
 209
 210     public CourseEvent[] getCourseCalendarTimetable(String coursename, long timeFrom, int days) {
 211         String[] whereArgs;
 212         whereArgs = new String[] {
 213                 coursename,
 214                 Long.toString(timeFrom),
 215                 Integer.toString(days)
 216         };
 217         ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
 218
 219         Cursor resultSet = database.rawQuery(
 220                 "SELECT " +
 221                     "COUNT(*) as EventCount, " +
 222                     "strftime('%Y%m%d', datetime(CourseCalendarEvent.EventTo, 'unixepoch')) as DayId " +
 223                 "FROM "+
 224                     "CourseCalendarEvent " +
 225                 "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
 226                 "WHERE " +
 227                     "CourseCalendar.CourseName = ? AND " +
 228                     "CourseCalendarEvent.EventTo >= ? " +
 229                 "GROUP BY DayId " +
 230                 "ORDER BY DayId ASC " +
 231                 "LIMIT ? ",
 232             whereArgs);
 233         if(resultSet.moveToFirst()) {
 234             do {
 235                 int eventCount = resultSet.getInt(0);
 236                 int dayId = resultSet.getInt(1);
 237
 238                 if(eventCount == 0)
 239                     continue;
 240
 241                 whereArgs = new String[] {
 242                     Integer.toString(dayId),
 243                         coursename
 244                 };
 245
 246                 Cursor resultSet2 = database.rawQuery(
 247                         "SELECT " +
 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 " +
 252                         "FROM " +
 253                             "CourseCalendarEvent " +
 254                         "LEFT JOIN CourseCalendar ON CourseCalendarEvent.EventId = CourseCalendar.Id " +
 255                         "WHERE DayId = ? AND " +
 256                             "CourseCalendar.CourseName = ? " +
 257                         "ORDER BY CourseCalendarEvent.EventFrom ASC",
 258                     whereArgs);
 259
 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")
 274                 };
 275                 resultSet2.moveToFirst();
 276                 do {
 277                     int groupId = resultSet2.getInt(columnIndexes[11]);
 278                     CourseGroup group;
 279                     if(groupId > 0)
 280                         group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
 281                     else
 282                         group = null;
 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);
 287
 288                     events.add(event);
 289                 } while (resultSet2.moveToNext());
 290                 resultSet2.close();
 291             } while (resultSet.moveToNext());
 292             resultSet.close();
 293         }
 294
 295         CourseEvent[] eventsArr = new CourseEvent[events.size()];
 296         eventsArr = events.toArray(eventsArr);
 297         return eventsArr;
 298     }
 299
 300     public CourseEvent[] getCourseEventsByGroup(CourseGroup group) {
 301         String[] whereArgs;
 302         whereArgs = new String[] {
 303                 Integer.toString(group.getGroupId())
 304         };
 305         Cursor resultSet = database.rawQuery(
 306                 "SELECT " +
 307                         "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " +
 308                         "FROM " +
 309                         "CourseCalendarEvent " +
 310                         "LEFT JOIN " +
 311                         "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+
 312                         "WHERE " +
 313                         "CourseGroupId = ? ORDER BY CourseCalendarEvent.EventFrom ASC ",
 314                 whereArgs);
 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")
 331             };
 332             do {
 333                 int eventType = resultSet.getInt(columnIndexes[12]);
 334
 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);
 338
 339                 events.add(event);
 340             } while (resultSet.moveToNext());
 341         }
 342         resultSet.close();
 343
 344         CourseEvent[] eventsArr = new CourseEvent[events.size()];
 345         eventsArr = events.toArray(eventsArr);
 346         return eventsArr;
 347     }
 348
 349     public CourseGroup getCourseGroup(int courseGroupId) {
 350         CourseGroup coursegroup = null;
 351         String[] whereArgs = new String[] {
 352                 Integer.toString(courseGroupId)
 353         };
 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));
 357         }
 358         resultSet.close();
 359         return coursegroup;
 360     }
 361
 362     public CourseGroup getCourseGroup(String coursename, String groupname) {
 363         CourseGroup coursegroup = null;
 364         String[] whereArgs = new String[] {
 365                 coursename,
 366                 groupname
 367         };
 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));
 371         }
 372         resultSet.close();
 373         return coursegroup;
 374     }
 375
 376     public CourseGroup addCourseGroup(String coursename, String groupname) {
 377         long now = (new Date()).getTime() / 1000;
 378         int id = 0;
 379         try {
 380             ContentValues indexValues = new ContentValues();
 381             indexValues.put("CourseName",coursename);
 382             indexValues.put("GroupName", groupname);
 383             indexValues.put("LastUpdate", now);
 384
 385             id = (int) database.insertOrThrow("CourseCalendarGroup", null, indexValues);
 386         } catch(Exception e) {
 387             e.printStackTrace();
 388         }
 389
 390         if(id > 0) {
 391             CourseGroup newGroup = new CourseGroup(id, coursename, groupname);
 392             return newGroup;
 393         } else
 394             return null;
 395     }
 396
 397     public CourseGroup[] getCourseGroups(String coursename, Date notBefore) {
 398         String[] whereArgs;
 399         whereArgs = new String[] {
 400                 coursename
 401         };
 402         ArrayList<CourseGroup> groups = new ArrayList<CourseGroup>();
 403
 404         /*
 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 " +
 412                         ") as t1 " +
 413                         "WHERE " +
 414                         "CourseCalendarGroup.GroupId = t1.CourseGroupId AND " +
 415                         "CourseName = ? AND t1.LastEvent > ? " +
 416                         "ORDER BY FirstEvent ASC",
 417                 whereArgs);
 418         */
 419         Cursor resultSet = database.rawQuery(
 420                 "SELECT " +
 421                     "CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName, " +
 422                     "MIN(CourseCalendarEvent.EventFrom) as FirstEvent, " +
 423                     "MAX(" +
 424                         "CASE WHEN CourseCalendarEvent.EventType IN (0, 1) " +
 425                         "THEN CourseCalendarEvent.EventFrom " +
 426                         "ELSE 0 " +
 427                     "END) as LastEvent, " +
 428                     "MAX(CourseCalendarEvent.EventFrom) as LastEventFilter, " +
 429                     "COUNT(*) as EventCount, " +
 430                     "MIN(" +
 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, " +
 435                     "MIN(" +
 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 " +
 441                 "FROM " +
 442                     "CourseCalendarEvent " +
 443                     "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
 444                     "LEFT JOIN CourseCalendarGroup ON CourseCalendarGroup.GroupId = CourseCalendar.CourseGroupId " +
 445                 "WHERE " +
 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 ",
 450             whereArgs);
 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"),
 462             };
 463             do {
 464                 CourseGroup coursegroup = new CourseGroup(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]));
 465                 Bundle extraData = coursegroup.getExtraData();
 466
 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));
 473
 474                 groups.add(coursegroup);
 475
 476             } while (resultSet.moveToNext());
 477             resultSet.close();
 478         }
 479
 480         CourseGroup[] groupsArr = new CourseGroup[groups.size()];
 481         groupsArr = groups.toArray(groupsArr);
 482         return groupsArr;
 483     }
 484
 485     public CourseEvent[] getCourseExamEvents(String coursename, long timeFrom, long timeTo) {
 486         String[] whereArgs = new String[] {
 487                 coursename,
 488                 Long.toString(timeFrom),
 489                 Long.toString(timeTo)
 490         };
 491         Cursor resultSet = database.rawQuery(
 492                 "SELECT " +
 493                         "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " +
 494                         "FROM " +
 495                         "CourseCalendarEvent " +
 496                         "LEFT JOIN " +
 497                         "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+
 498                         "WHERE " +
 499                         "CourseName = ? AND CourseCalendarEvent.EventTo >= ? AND CourseCalendarEvent.EventFrom <= ? AND EventType = 2",
 500                 whereArgs);
 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")
 517             };
 518             do {
 519                 int groupId = resultSet.getInt(columnIndexes[11]);
 520                 CourseGroup group;
 521                 if(groupId > 0)
 522                     group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
 523                 else
 524                     group = null;
 525
 526                 int eventType = resultSet.getInt(columnIndexes[12]);
 527
 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);
 531
 532                 events.add(event);
 533             } while (resultSet.moveToNext());
 534         }
 535         resultSet.close();
 536
 537         CourseEvent[] eventsArr = new CourseEvent[events.size()];
 538         eventsArr = events.toArray(eventsArr);
 539         return eventsArr;
 540     }
 541
 542 }