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     public CourseEvent[] getCourseEventsByGroup(CourseGroup group) {
 286         String[] whereArgs;
 287         whereArgs = new String[] {
 288                 Integer.toString(group.getGroupId())
 289         };
 290         Cursor resultSet = database.rawQuery(
 291                 "SELECT " +
 292                         "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " +
 293                         "FROM " +
 294                         "CourseCalendarEvent " +
 295                         "LEFT JOIN " +
 296                         "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+
 297                         "WHERE " +
 298                         "CourseGroupId = ? ORDER BY CourseCalendarEvent.EventFrom ASC ",
 299                 whereArgs);
 300         ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
 301         if(resultSet.moveToFirst()) {
 302             int[] columnIndexes = {
 303                     resultSet.getColumnIndex("Id"),
 304                     resultSet.getColumnIndex("CourseName"),
 305                     resultSet.getColumnIndex("UniqueId"),
 306                     resultSet.getColumnIndex("SequenceId"),
 307                     resultSet.getColumnIndex("EventFrom"),
 308                     resultSet.getColumnIndex("EventTo"),
 309                     resultSet.getColumnIndex("EventTitle"),
 310                     resultSet.getColumnIndex("EventLocation"),
 311                     resultSet.getColumnIndex("EventStatus"),
 312                     resultSet.getColumnIndex("RecurRule"),
 313                     resultSet.getColumnIndex("ExcludeDates"),
 314                     resultSet.getColumnIndex("CourseGroupId"),
 315                     resultSet.getColumnIndex("EventType")
 316             };
 317             do {
 318                 int eventType = resultSet.getInt(columnIndexes[12]);
 319
 320                 CourseEvent event = new CourseEvent(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]), resultSet.getInt(columnIndexes[3]),
 321                         resultSet.getLong(columnIndexes[4]), resultSet.getLong(columnIndexes[5]), resultSet.getString(columnIndexes[6]), resultSet.getString(columnIndexes[7]),
 322                         resultSet.getString(columnIndexes[8]), resultSet.getString(columnIndexes[9]), resultSet.getString(columnIndexes[10]), group, eventType);
 323
 324                 events.add(event);
 325             } while (resultSet.moveToNext());
 326         }
 327         resultSet.close();
 328
 329         CourseEvent[] eventsArr = new CourseEvent[events.size()];
 330         eventsArr = events.toArray(eventsArr);
 331         return eventsArr;
 332     }
 333
 334     public CourseGroup getCourseGroup(int courseGroupId) {
 335         CourseGroup coursegroup = null;
 336         String[] whereArgs = new String[] {
 337                 Integer.toString(courseGroupId)
 338         };
 339         Cursor resultSet = database.rawQuery("SELECT CourseName, GroupName, LastUpdate FROM CourseCalendarGroup WHERE GroupId = ?", whereArgs);
 340         if(resultSet.moveToFirst()) {
 341             coursegroup = new CourseGroup(courseGroupId, resultSet.getString(0), resultSet.getString(1));
 342         }
 343         resultSet.close();
 344         return coursegroup;
 345     }
 346
 347     public CourseGroup getCourseGroup(String coursename, String groupname) {
 348         CourseGroup coursegroup = null;
 349         String[] whereArgs = new String[] {
 350                 coursename,
 351                 groupname
 352         };
 353         Cursor resultSet = database.rawQuery("SELECT GroupId, CourseName, GroupName, LastUpdate FROM CourseCalendarGroup WHERE CourseName = ? AND GroupName = ?", whereArgs);
 354         if(resultSet.moveToFirst()) {
 355             coursegroup = new CourseGroup(resultSet.getInt(0), resultSet.getString(1), resultSet.getString(2));
 356         }
 357         resultSet.close();
 358         return coursegroup;
 359     }
 360
 361     public CourseGroup addCourseGroup(String coursename, String groupname) {
 362         long now = (new Date()).getTime() / 1000;
 363         int id = 0;
 364         try {
 365             ContentValues indexValues = new ContentValues();
 366             indexValues.put("CourseName",coursename);
 367             indexValues.put("GroupName", groupname);
 368             indexValues.put("LastUpdate", now);
 369
 370             id = (int) database.insertOrThrow("CourseCalendarGroup", null, indexValues);
 371         } catch(Exception e) {
 372             e.printStackTrace();
 373         }
 374
 375         if(id > 0) {
 376             CourseGroup newGroup = new CourseGroup(id, coursename, groupname);
 377             return newGroup;
 378         } else
 379             return null;
 380     }
 381
 382     public CourseGroup[] getCourseGroups(String coursename, Date notBefore) {
 383         String[] whereArgs;
 384         whereArgs = new String[] {
 385                 coursename
 386         };
 387         ArrayList<CourseGroup> groups = new ArrayList<CourseGroup>();
 388
 389         /*
 390         Cursor resultSet = database.rawQuery(
 391                 "SELECT CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName, t1.FirstEvent, t1.LastEvent, t1.EventCount FROM " +
 392                         "CourseCalendarGroup, ( " +
 393                             "SELECT CourseCalendar.CourseGroupId, MIN(CourseCalendarEvent.EventFrom) as FirstEvent, MAX(CourseCalendarEvent.EventTo) as LastEvent, COUNT(*) as EventCount " +
 394                             "FROM CourseCalendarEvent " +
 395                             "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
 396                             "GROUP BY CourseCalendar.CourseGroupId " +
 397                         ") as t1 " +
 398                         "WHERE " +
 399                         "CourseCalendarGroup.GroupId = t1.CourseGroupId AND " +
 400                         "CourseName = ? AND t1.LastEvent > ? " +
 401                         "ORDER BY FirstEvent ASC",
 402                 whereArgs);
 403         */
 404         Cursor resultSet = database.rawQuery(
 405                 "SELECT " +
 406                     "CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName, " +
 407                     "MIN(CourseCalendarEvent.EventFrom) as FirstEvent, " +
 408                     "MAX(" +
 409                         "CASE WHEN CourseCalendarEvent.EventType IN (0, 1) " +
 410                         "THEN CourseCalendarEvent.EventFrom " +
 411                         "ELSE 0 " +
 412                     "END) as LastEvent, " +
 413                     "MAX(CourseCalendarEvent.EventFrom) as LastEventFilter, " +
 414                     "COUNT(*) as EventCount, " +
 415                     "MIN(" +
 416                         "CASE WHEN CourseCalendarEvent.EventFrom < strftime('%s', 'now') " +
 417                         "THEN (strftime('%s', 'now')+(86400*365*5)+CourseCalendarEvent.EventFrom) " +
 418                         "ELSE CourseCalendarEvent.EventFrom " +
 419                     "END) as NextEvent, " +
 420                     "MIN(" +
 421                         "CASE WHEN CourseCalendarEvent.EventFrom < strftime('%s', 'now') OR CourseCalendarEvent.EventType NOT IN (2) " +
 422                         "THEN (strftime('%s', 'now')+(86400*365*5)+CourseCalendarEvent.EventFrom) " +
 423                         "ELSE CourseCalendarEvent.EventFrom " +
 424                     "END) as NextKlausurEvent, " +
 425                     "MIN(CASE WHEN CourseCalendarEvent.EventFrom <=  strftime('%s', 'now') THEN 1 ELSE 0 END) as EventCompleted " +
 426                 "FROM " +
 427                     "CourseCalendarEvent " +
 428                     "LEFT JOIN CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId " +
 429                     "LEFT JOIN CourseCalendarGroup ON CourseCalendarGroup.GroupId = CourseCalendar.CourseGroupId " +
 430                 "WHERE " +
 431                     "CourseCalendar.CourseName = ? " +
 432                 "GROUP BY CourseCalendarGroup.GroupId, CourseCalendarGroup.CourseName, CourseCalendarGroup.GroupName " +
 433                     "HAVING LastEventFilter > " + Long.toString((notBefore == null ? 0L : notBefore.getTime()/1000)) + " " +
 434                 "ORDER BY EventCompleted DESC, NextEvent ASC ",
 435             whereArgs);
 436         if(resultSet.moveToFirst()) {
 437             int[] columnIndexes = {
 438                     resultSet.getColumnIndex("GroupId"),
 439                     resultSet.getColumnIndex("CourseName"),
 440                     resultSet.getColumnIndex("GroupName"),
 441                     resultSet.getColumnIndex("FirstEvent"),
 442                     resultSet.getColumnIndex("LastEvent"),
 443                     resultSet.getColumnIndex("EventCount"),
 444                     resultSet.getColumnIndex("NextEvent"),
 445                     resultSet.getColumnIndex("EventCompleted"),
 446                     resultSet.getColumnIndex("NextKlausurEvent"),
 447             };
 448             do {
 449                 CourseGroup coursegroup = new CourseGroup(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]));
 450                 Bundle extraData = coursegroup.getExtraData();
 451
 452                 extraData.putLong("FirstEvent", resultSet.getLong(columnIndexes[3]));
 453                 extraData.putLong("LastEvent", resultSet.getLong(columnIndexes[4]));
 454                 extraData.putInt("EventCount", resultSet.getInt(columnIndexes[5]));
 455                 extraData.putLong("NextEvent", resultSet.getLong(columnIndexes[6]));
 456                 extraData.putLong("NextKlausurEvent", resultSet.getLong(columnIndexes[8]));
 457                 extraData.putBoolean("EventCompleted", (resultSet.getInt(columnIndexes[7]) == 1));
 458
 459                 groups.add(coursegroup);
 460
 461             } while (resultSet.moveToNext());
 462             resultSet.close();
 463         }
 464
 465         CourseGroup[] groupsArr = new CourseGroup[groups.size()];
 466         groupsArr = groups.toArray(groupsArr);
 467         return groupsArr;
 468     }
 469
 470     public CourseEvent[] getCourseExamEvents(String coursename, long timeFrom, long timeTo) {
 471         String[] whereArgs = new String[] {
 472                 coursename,
 473                 Long.toString(timeFrom),
 474                 Long.toString(timeTo)
 475         };
 476         Cursor resultSet = database.rawQuery(
 477                 "SELECT " +
 478                         "Id, CourseName, UniqueId, SequenceId, CourseCalendarEvent.EventFrom, CourseCalendarEvent.EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId, EventType " +
 479                         "FROM " +
 480                         "CourseCalendarEvent " +
 481                         "LEFT JOIN " +
 482                         "CourseCalendar ON CourseCalendar.Id = CourseCalendarEvent.EventId "+
 483                         "WHERE " +
 484                         "CourseName = ? AND CourseCalendarEvent.EventTo >= ? AND CourseCalendarEvent.EventFrom <= ? AND EventType = 2",
 485                 whereArgs);
 486         ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
 487         if(resultSet.moveToFirst()) {
 488             int[] columnIndexes = {
 489                     resultSet.getColumnIndex("Id"),
 490                     resultSet.getColumnIndex("CourseName"),
 491                     resultSet.getColumnIndex("UniqueId"),
 492                     resultSet.getColumnIndex("SequenceId"),
 493                     resultSet.getColumnIndex("EventFrom"),
 494                     resultSet.getColumnIndex("EventTo"),
 495                     resultSet.getColumnIndex("EventTitle"),
 496                     resultSet.getColumnIndex("EventLocation"),
 497                     resultSet.getColumnIndex("EventStatus"),
 498                     resultSet.getColumnIndex("RecurRule"),
 499                     resultSet.getColumnIndex("ExcludeDates"),
 500                     resultSet.getColumnIndex("CourseGroupId"),
 501                     resultSet.getColumnIndex("EventType")
 502             };
 503             do {
 504                 int groupId = resultSet.getInt(columnIndexes[11]);
 505                 CourseGroup group;
 506                 if(groupId > 0)
 507                     group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
 508                 else
 509                     group = null;
 510
 511                 int eventType = resultSet.getInt(columnIndexes[12]);
 512
 513                 CourseEvent event = new CourseEvent(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]), resultSet.getInt(columnIndexes[3]),
 514                         resultSet.getLong(columnIndexes[4]), resultSet.getLong(columnIndexes[5]), resultSet.getString(columnIndexes[6]), resultSet.getString(columnIndexes[7]),
 515                         resultSet.getString(columnIndexes[8]), resultSet.getString(columnIndexes[9]), resultSet.getString(columnIndexes[10]), group, eventType);
 516
 517                 events.add(event);
 518             } while (resultSet.moveToNext());
 519         }
 520         resultSet.close();
 521
 522         CourseEvent[] eventsArr = new CourseEvent[events.size()];
 523         eventsArr = events.toArray(eventsArr);
 524         return eventsArr;
 525     }
 526
 527 }