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.util.Log;
   7
   8 import net.fortuna.ical4j.model.DateList;
   9 import net.fortuna.ical4j.model.DateTime;
  10 import net.fortuna.ical4j.model.Period;
  11 import net.fortuna.ical4j.model.Recur;
  12 import net.fortuna.ical4j.model.parameter.Value;
  13 import net.fortuna.ical4j.model.property.RRule;
  14
  15 import java.text.ParseException;
  16 import java.util.ArrayList;
  17 import java.util.Date;
  18
  19 import de.dhbwloe.campusapp.CampusAppContext;
  20 import de.dhbwloe.campusapp.vorlesungen.CourseEvent;
  21 import de.dhbwloe.campusapp.vorlesungen.CourseGroup;
  22
  23 /**
  24  * Created by pk910 on 24.01.2016.
  25  */
  26 public class VorlesungsplanDatabaseHelper {
  27     private CampusAppContext AppContext;
  28     private SQLiteDatabase database;
  29
  30     public VorlesungsplanDatabaseHelper(CampusAppContext context, SQLiteDatabase database) {
  31         this.database = database;
  32         AppContext = context;
  33     }
  34
  35
  36     public void updateCourseCalendar(CourseEvent event) {
  37         boolean isExisting = false;
  38         String[] whereArgs = new String[] {
  39                 event.getCourseName(),
  40                 event.getUniqueId()
  41         };
  42         Cursor resultSet = database.rawQuery("SELECT SequenceId FROM CourseCalendar WHERE CourseName = ? AND UniqueId = ?", whereArgs);
  43         if(resultSet.moveToFirst()) {
  44             int sequence = resultSet.getInt(0);
  45             if(event.getSequenceId() <= sequence)
  46                 return;
  47             isExisting = true;
  48         }
  49         resultSet.close();
  50
  51         if(isExisting) {
  52             try {
  53                 ContentValues updateValues = new ContentValues();
  54                 updateValues.put("SequenceId", event.getSequenceId());
  55                 updateValues.put("EventFrom", event.getEventFrom());
  56                 updateValues.put("EventTo", event.getEventTo());
  57                 updateValues.put("EventTitle", event.getEventTitle());
  58                 updateValues.put("EventLocation", event.getEventLocation());
  59                 updateValues.put("EventStatus", event.getEventStatus());
  60                 updateValues.put("RecurRule", event.getRecurRule());
  61                 updateValues.put("ExcludeDates", event.getExcludeDates());
  62                 if(event.getCourseGroup() != null)
  63                     updateValues.put("CourseGroupId", event.getCourseGroup().getGroupId());
  64
  65                 database.update("CourseCalendar", updateValues, "CourseName = ? AND UniqueId = ?", whereArgs);
  66                 updateCourseCalendarEventTable(event, true);
  67             } catch(Exception e) {
  68                 e.printStackTrace();
  69             }
  70         } else {
  71             try {
  72                 ContentValues indexValues = new ContentValues();
  73                 indexValues.put("CourseName", event.getCourseName());
  74                 indexValues.put("UniqueId", event.getUniqueId());
  75                 indexValues.put("SequenceId", event.getSequenceId());
  76                 indexValues.put("EventFrom", event.getEventFrom());
  77                 indexValues.put("EventTo", event.getEventTo());
  78                 indexValues.put("EventTitle", event.getEventTitle());
  79                 indexValues.put("EventLocation", event.getEventLocation());
  80                 indexValues.put("EventStatus", event.getEventStatus());
  81                 indexValues.put("RecurRule", event.getRecurRule());
  82                 indexValues.put("ExcludeDates", event.getExcludeDates());
  83                 if(event.getCourseGroup() != null)
  84                     indexValues.put("CourseGroupId", event.getCourseGroup().getGroupId());
  85
  86                 long id = database.insertOrThrow("CourseCalendar", null, indexValues);
  87                 event.setEventId((int) id);
  88                 updateCourseCalendarEventTable(event, false);
  89             } catch(Exception e) {
  90                 e.printStackTrace();
  91             }
  92         }
  93
  94     }
  95
  96     private void updateCourseCalendarEventTable(CourseEvent event, boolean clear) {
  97         if(clear) {
  98             String[] whereArgs = {
  99                     Integer.toString(event.getEventId())
 100             };
 101             database.rawQuery("DELETE FROM CourseCalendarEvent WHERE EventId = ?", whereArgs);
 102         }
 103
 104         String rrule = event.getRecurRule();
 105         String exdates = event.getExcludeDates();
 106
 107         if(rrule != null && rrule.length() > 0) {
 108             try {
 109                 Log.i("DBM", "RRule: " + rrule);
 110                 RRule rule = new RRule(rrule);
 111                 Recur recur = rule.getRecur();
 112                 Date startDate = new Date((event.getEventFrom())*1000);
 113                 Date endDate = new Date(startDate.getTime() + (86400 * 365)*1000);
 114
 115                 DateTime startDateTime = new DateTime(startDate);
 116                 DateList dates = recur.getDates(startDateTime, new Period(startDateTime, new DateTime(endDate)), Value.DATE);
 117
 118                 Log.i("DBM", "Update events table for " + event.getUniqueId());
 119                 Log.i("DBM", "events: " +dates.size());
 120
 121                 for(int i = 0; i < dates.size(); i++) {
 122                     net.fortuna.ical4j.model.Date date = (net.fortuna.ical4j.model.Date) dates.get(i);
 123                     long eventStartTime = date.getTime() / 1000;
 124                     long eventEndTime = (eventStartTime + (event.getEventTo() - event.getEventFrom())) / 1000;
 125
 126                     try {
 127                         ContentValues indexValues = new ContentValues();
 128                         indexValues.put("EventId", event.getEventId());
 129                         indexValues.put("EventFrom", eventStartTime);
 130                         indexValues.put("EventTo", eventEndTime);
 131                         database.insertOrThrow("CourseCalendarEvent", null, indexValues);
 132                     } catch(Exception e) {
 133                         e.printStackTrace();
 134                     }
 135                 }
 136
 137             } catch (ParseException e) {
 138             }
 139         } else {
 140             try {
 141                 ContentValues indexValues = new ContentValues();
 142                 indexValues.put("EventId", event.getEventId());
 143                 indexValues.put("EventFrom", event.getEventFrom());
 144                 indexValues.put("EventTo", event.getEventTo());
 145                 database.insertOrThrow("CourseCalendarEvent", null, indexValues);
 146             } catch(Exception e) {
 147                 e.printStackTrace();
 148             }
 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("SELECT Id, CourseName, UniqueId, SequenceId, EventFrom, EventTo, EventTitle, EventLocation, EventStatus, RecurRule, ExcludeDates, CourseGroupId FROM CourseCalendar WHERE CourseName = ? AND EventTo >= ? AND EventFrom <= ?", whereArgs);
 160         ArrayList<CourseEvent> events = new ArrayList<CourseEvent>();
 161         if(resultSet.moveToFirst()) {
 162             int[] columnIndexes = {
 163                     resultSet.getColumnIndex("Id"),
 164                     resultSet.getColumnIndex("CourseName"),
 165                     resultSet.getColumnIndex("UniqueId"),
 166                     resultSet.getColumnIndex("SequenceId"),
 167                     resultSet.getColumnIndex("EventFrom"),
 168                     resultSet.getColumnIndex("EventTo"),
 169                     resultSet.getColumnIndex("EventTitle"),
 170                     resultSet.getColumnIndex("EventLocation"),
 171                     resultSet.getColumnIndex("EventStatus"),
 172                     resultSet.getColumnIndex("RecurRule"),
 173                     resultSet.getColumnIndex("ExcludeDates"),
 174                     resultSet.getColumnIndex("CourseGroupId")
 175             };
 176             do {
 177                 int groupId = resultSet.getInt(columnIndexes[11]);
 178                 CourseGroup group;
 179                 if(groupId > 0)
 180                     group = CourseGroup.GetCourseGroupById(AppContext.getDatabaseManager(), groupId);
 181                 else
 182                     group = null;
 183                 CourseEvent event = new CourseEvent(resultSet.getInt(columnIndexes[0]), resultSet.getString(columnIndexes[1]), resultSet.getString(columnIndexes[2]), resultSet.getInt(columnIndexes[3]),
 184                         resultSet.getLong(columnIndexes[4]), resultSet.getLong(columnIndexes[5]), resultSet.getString(columnIndexes[6]), resultSet.getString(columnIndexes[7]),
 185                         resultSet.getString(columnIndexes[8]), resultSet.getString(columnIndexes[9]), resultSet.getString(columnIndexes[10]), group);
 186
 187                 events.add(event);
 188             } while (resultSet.moveToNext());
 189         }
 190         resultSet.close();
 191
 192         CourseEvent[] eventsArr = new CourseEvent[events.size()];
 193         eventsArr = events.toArray(eventsArr);
 194         return eventsArr;
 195     }
 196
 197     public CourseGroup getCourseGroup(int courseGroupId) {
 198         CourseGroup coursegroup = null;
 199         String[] whereArgs = new String[] {
 200                 Integer.toString(courseGroupId)
 201         };
 202         Cursor resultSet = database.rawQuery("SELECT CourseName, GroupName, LastUpdate FROM CourseCalendarGroup WHERE GroupId = ?", whereArgs);
 203         if(resultSet.moveToFirst()) {
 204             coursegroup = new CourseGroup(courseGroupId, resultSet.getString(0), resultSet.getString(1));
 205         }
 206         resultSet.close();
 207         return coursegroup;
 208     }
 209
 210     public CourseGroup getCourseGroup(String coursename, String groupname) {
 211         CourseGroup coursegroup = null;
 212         String[] whereArgs = new String[] {
 213                 coursename,
 214                 groupname
 215         };
 216         Cursor resultSet = database.rawQuery("SELECT GroupId, CourseName, GroupName, LastUpdate FROM CourseCalendarGroup WHERE CourseName = ? AND GroupName = ?", whereArgs);
 217         if(resultSet.moveToFirst()) {
 218             coursegroup = new CourseGroup(resultSet.getInt(0), resultSet.getString(1), resultSet.getString(2));
 219         }
 220         resultSet.close();
 221         return coursegroup;
 222     }
 223
 224     public CourseGroup addCourseGroup(String coursename, String groupname) {
 225         long now = (new Date()).getTime() / 1000;
 226         int id = 0;
 227         try {
 228             ContentValues indexValues = new ContentValues();
 229             indexValues.put("CourseName",coursename);
 230             indexValues.put("GroupName", groupname);
 231             indexValues.put("LastUpdate", now);
 232
 233             id = (int) database.insertOrThrow("CourseCalendarGroup", null, indexValues);
 234         } catch(Exception e) {
 235             e.printStackTrace();
 236         }
 237
 238         if(id > 0) {
 239             CourseGroup newGroup = new CourseGroup(id, coursename, groupname);
 240             return newGroup;
 241         } else
 242             return null;
 243     }
 244
 245 }