+package de.dhbwloe.campusapp.database;
+
+import android.app.Activity;
+import android.content.ContentValues;
+import android.database.Cursor;
+import android.database.sqlite.SQLiteDatabase;
+import android.util.Log;
+
+import net.fortuna.ical4j.model.DateList;
+import net.fortuna.ical4j.model.DateTime;
+import net.fortuna.ical4j.model.Period;
+import net.fortuna.ical4j.model.Recur;
+import net.fortuna.ical4j.model.parameter.Value;
+import net.fortuna.ical4j.model.property.RRule;
+
+import java.lang.reflect.Array;
+import java.text.ParseException;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.ListIterator;
+
+import de.dhbwloe.campusapp.CampusAppContext;
+import de.dhbwloe.campusapp.mensaplan.MensaTagesplan;
+import de.dhbwloe.campusapp.news.NewsItem;
+import de.dhbwloe.campusapp.search.SearchIndices;
+import de.dhbwloe.campusapp.vorlesungen.CourseEvent;
+import de.dhbwloe.campusapp.vorlesungen.CourseGroup;
+
+/**
+ * Created by pk910 on 19.01.2016.
+ */
+public class DatabaseManager {
+ private static final String DATABASE_NAME = "DHBWLoe.CampusApp.DEV";
+ private static final int DATABASE_VERSION = 1;
+ private CampusAppContext AppContext;
+ private SQLiteDatabase database;
+ private NewsDatabaseHelper newsDBHelper;
+ private MensaplanDatabaseHelper mensaplanDBHelper;
+ private VorlesungsplanDatabaseHelper vorlesungsplanDBHelper;
+
+
+ public DatabaseManager(CampusAppContext context) {
+ AppContext = context;
+ }
+
+ public void initializeDatabase() {
+ database = AppContext.getMainActivity().openOrCreateDatabase(DATABASE_NAME, Activity.MODE_PRIVATE, null);
+ database.execSQL("CREATE TABLE IF NOT EXISTS Version(Version INT);");
+
+ Cursor resultSet = database.rawQuery("Select * from Version", null);
+ int version;
+ if(resultSet.moveToFirst()) {
+ version = resultSet.getInt(0);
+ } else {
+ version = 0;
+ database.execSQL("INSERT INTO Version (Version) VALUES (0);");
+ }
+
+ resultSet.close();
+ if(version < DATABASE_VERSION)
+ upgradeTables(version, DATABASE_VERSION);
+
+
+ }
+
+ private void upgradeTables(int oldVersion, int newVersion) {
+ if(oldVersion == 0 && newVersion > 0) {
+ database.execSQL("CREATE TABLE IF NOT EXISTS RuntimeCache " +
+ "(" +
+ "Reference TEXT, " +
+ "Value TEXT, " +
+ "LastUpdate INT, " +
+ "PRIMARY KEY (Reference)" +
+ ");");
+ database.execSQL("CREATE TABLE IF NOT EXISTS SearchIndex " +
+ "(" +
+ "KeyName TEXT, " +
+ "SearchText TEXT, " +
+ "SearchTitle TEXT," +
+ "Description TEXT, " +
+ "StaticEntry INT, " +
+ "UpdateTime INT, " +
+ "TargetPage TEXT);");
+ database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendar " +
+ "(" +
+ "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
+ "CourseName TEXT, " +
+ "UniqueId TEXT, " +
+ "SequenceId INT, " +
+ "EventFrom INT, " +
+ "EventTo INT, " +
+ "EventTitle TEXT, " +
+ "EventLocation TEXT, " +
+ "EventStatus TEXT," +
+ "RecurRule TEXT," +
+ "ExcludeDates TEXT," +
+ "CourseGroupId INT," +
+ "UNIQUE (CourseName, UniqueId)" +
+ ");");
+ database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarEvent " +
+ "(" +
+ "EventId INT, " +
+ "EventFrom INT, " +
+ "EventTo INT, " +
+ "PRIMARY KEY (EventId, EventFrom, EventTo)" +
+ ");");
+ database.execSQL("CREATE INDEX CourseCalendarEventIdx ON CourseCalendarEvent (EventFrom, EventTo);");
+ database.execSQL("CREATE TABLE IF NOT EXISTS NfcCardStore " +
+ "(" +
+ "CardId INT, " +
+ "UpdateTime INT," +
+ "CardData TEXT, " +
+ "CardBalance INT, " +
+ "CardLastTransaction INT, " +
+ "PRIMARY KEY (CardId, UpdateTime)" +
+ ");");
+ database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarGroup " +
+ "(" +
+ "GroupId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
+ "CourseName INT," +
+ "GroupName TEXT, " +
+ "LastUpdate INT, " +
+ "UNIQUE (GroupName)" +
+ ");");
+ database.execSQL("CREATE INDEX CourseCalendarGroupIdx ON CourseCalendarGroup (CourseName, GroupName);");
+ database.execSQL("CREATE TABLE IF NOT EXISTS MensaPlan " +
+ "(" +
+ "PlanDate INT, " +
+ "MenuName TEXT, " +
+ "ChkSum INT, " +
+ "Name TEXT, " +
+ "NameHtml TEXT, " +
+ "Additional TEXT, " +
+ "Notes TEXT, " +
+ "PriceStudents INT, " +
+ "PriceEmployees INT, " +
+ "PriceGuests INT, " +
+ "PriceSchool INT, " +
+ "PRIMARY KEY (PlanDate, MenuName)" +
+ ");");
+ database.execSQL("CREATE TABLE IF NOT EXISTS News " +
+ "(" +
+ "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
+ "Source TEXT, " +
+ "Time INT, " +
+ "UniqueId TEXT, " +
+ "ChkSum INT, " +
+ "Title TEXT, " +
+ "Summary TEXT, " +
+ "Content TEXT, " +
+ "Link TEXT, " +
+ "Categories TEXT, " +
+ "UNIQUE(Source, UniqueId) " +
+ ");");
+ database.execSQL("CREATE INDEX NewsIdx ON News (Source, Time);");
+ }
+ if(oldVersion < 2 && newVersion >= 2) {
+ // Version 2
+
+ }
+
+ database.execSQL("UPDATE Version SET Version = "+Integer.toString(newVersion));
+ }
+
+ public void addSearchIndices(SearchIndices[] indices) {
+ for(int i = 0; i < indices.length; i++) {
+ String[] whereArgs = new String[] {
+ indices[i].getKeyName()
+ };
+ Cursor resultSet = database.rawQuery("SELECT UpdateTime FROM SearchIndex WHERE KeyName = ?", whereArgs);
+ if(resultSet.moveToFirst()) {
+ long updateTime = resultSet.getLong(0);
+ if (updateTime < indices[i].getUpdateTime()) {
+ // remove
+ database.rawQuery("DELETE FROM SearchIndex WHERE KeyName = ?", whereArgs);
+ } else
+ continue;
+ }
+ resultSet.close();
+ // add new
+ try {
+ ContentValues indexValues = new ContentValues();
+ indexValues.put("KeyName", indices[i].getKeyName());
+ indexValues.put("SearchText", indices[i].getKeyWords());
+ indexValues.put("SearchTitle", indices[i].getTitle());
+ indexValues.put("Description", indices[i].getDescription());
+ indexValues.put("StaticEntry", indices[i].getIsStatic() ? 1 : 0);
+ indexValues.put("UpdateTime", indices[i].getUpdateTime());
+ indexValues.put("TargetPage", indices[i].getTarget());
+
+ database.insertOrThrow("SearchIndex", null, indexValues);
+ } catch(Exception e) {
+ e.printStackTrace();
+ }
+ }
+ }
+
+ public SearchIndices[] performSearchRequest(String query, int maxResults) {
+ String[] whereArgs = new String[] {
+ "%" + query + "%"
+ };
+ Cursor resultSet = database.rawQuery("SELECT KeyName, SearchTitle, Description, StaticEntry, UpdateTime, TargetPage, SearchText FROM SearchIndex WHERE SearchText LIKE ? ORDER BY StaticEntry DESC, UpdateTime DESC", whereArgs);
+ ArrayList<SearchIndices> indices = new ArrayList<SearchIndices>();
+ if(resultSet.moveToFirst()) {
+ int[] columnIndexes = {
+ resultSet.getColumnIndex("KeyName"),
+ resultSet.getColumnIndex("SearchTitle"),
+ resultSet.getColumnIndex("Description"),
+ resultSet.getColumnIndex("StaticEntry"),
+ resultSet.getColumnIndex("UpdateTime"),
+ resultSet.getColumnIndex("TargetPage"),
+ resultSet.getColumnIndex("SearchText")
+ };
+ do {
+ SearchIndices cIndices = new SearchIndices(resultSet.getString(columnIndexes[0]), (resultSet.getInt(columnIndexes[3]) == 1));
+ cIndices.setUpdateTime(resultSet.getLong(columnIndexes[4]));
+ cIndices.setTarget(resultSet.getString(columnIndexes[5]));
+ cIndices.addKeyWord(resultSet.getString(columnIndexes[6]));
+ cIndices.setTitle(resultSet.getString(columnIndexes[1]));
+ cIndices.setDescription(resultSet.getString(columnIndexes[2]));
+ indices.add(cIndices);
+ } while (resultSet.moveToNext() && indices.size() < maxResults);
+ }
+ resultSet.close();
+
+ SearchIndices[] indicesArr = new SearchIndices[indices.size()];
+ indicesArr = indices.toArray(indicesArr);
+ return indicesArr;
+ }
+
+ public void setRuntimeCache(String name, String value) {
+ long now = (new Date()).getTime() / 1000;
+ String[] whereArgs = new String[] {
+ name
+ };
+ Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
+ if(resultSet.moveToFirst()) {
+ if(resultSet.getString(0).equalsIgnoreCase(value))
+ return;
+ try {
+ ContentValues updateValues = new ContentValues();
+ updateValues.put("Value", value);
+ updateValues.put("LastUpdate", now);
+
+ database.update("RuntimeCache", updateValues, "Reference = ?", whereArgs);
+ } catch(Exception e) {
+ e.printStackTrace();
+ }
+ } else {
+ try {
+ ContentValues indexValues = new ContentValues();
+ indexValues.put("Reference", name);
+ indexValues.put("Value", value);
+ indexValues.put("LastUpdate", now);
+
+ database.insertOrThrow("RuntimeCache", null, indexValues);
+ } catch(Exception e) {
+ e.printStackTrace();
+ }
+ }
+ resultSet.close();
+ }
+
+ public String getRuntimeCache(String name) {
+ String value = null;
+ String[] whereArgs = new String[] {
+ name
+ };
+ Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
+ if(resultSet.moveToFirst()) {
+ value = resultSet.getString(0);
+ }
+ resultSet.close();
+ return value;
+ }
+
+ public void addNfcCardData(NfcCardData nfcCardData) {
+ String[] whereArgs = new String[] {
+ Integer.toString(nfcCardData.getUniqueId()),
+ Long.toString(nfcCardData.getLastUpdate())
+ };
+ Cursor resultSet = database.rawQuery("SELECT CardData FROM NfcCardStore WHERE CardId = ? AND UpdateTime = ?", whereArgs);
+ if(resultSet.moveToFirst()) {
+ if(resultSet.getString(0).equalsIgnoreCase(nfcCardData.getCardData()))
+ return;
+ try {
+ ContentValues updateValues = new ContentValues();
+ updateValues.put("CardData", nfcCardData.getCardData());
+ updateValues.put("CardBalance", nfcCardData.getBalance());
+ updateValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
+
+ database.update("NfcCardStore", updateValues, "CardId = ? AND UpdateTime = ?", whereArgs);
+ } catch(Exception e) {
+ e.printStackTrace();
+ }
+ } else {
+ try {
+ ContentValues indexValues = new ContentValues();
+ indexValues.put("CardId", nfcCardData.getUniqueId());
+ indexValues.put("UpdateTime", nfcCardData.getLastUpdate());
+ indexValues.put("CardData", nfcCardData.getCardData());
+ indexValues.put("CardBalance", nfcCardData.getBalance());
+ indexValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
+
+ database.insertOrThrow("NfcCardStore", null, indexValues);
+ } catch(Exception e) {
+ e.printStackTrace();
+ }
+ }
+ resultSet.close();
+ }
+
+ public NfcCardData[] getNfcCardData(int lastLimit) {
+ String value = null;
+ Cursor resultSet;
+ if(lastLimit > 0) {
+ String[] whereArgs = {
+ Integer.toString(lastLimit)
+ };
+ resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardData,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC LIMIT ?", whereArgs);
+ } else
+ resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardData,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC", null);
+ ArrayList<NfcCardData> nfcCardDatas = new ArrayList<NfcCardData>();
+ if(resultSet.moveToFirst()) {
+ do {
+ NfcCardData nfcCardData = new NfcCardData(resultSet.getInt(0), resultSet.getLong(1), resultSet.getString(2), resultSet.getInt(3), resultSet.getInt(4));
+ nfcCardDatas.add(nfcCardData);
+ } while (resultSet.moveToNext());
+ }
+ resultSet.close();
+ NfcCardData[] resultsArr = new NfcCardData[nfcCardDatas.size()];
+ resultsArr = nfcCardDatas.toArray(resultsArr);
+ return resultsArr;
+ }
+
+ public void updateCourseCalendar(CourseEvent event) {
+ if(vorlesungsplanDBHelper == null)
+ vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
+ vorlesungsplanDBHelper.updateCourseCalendar(event);
+ }
+
+ public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
+ if(vorlesungsplanDBHelper == null)
+ vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
+ return vorlesungsplanDBHelper.getCourseCalendarEvents(coursename, timeFrom, timeTo);
+ }
+
+ public CourseGroup getCourseGroup(int courseGroupId) {
+ if(vorlesungsplanDBHelper == null)
+ vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
+ return vorlesungsplanDBHelper.getCourseGroup(courseGroupId);
+ }
+
+ public CourseGroup getCourseGroup(String coursename, String groupname) {
+ if(vorlesungsplanDBHelper == null)
+ vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
+ return vorlesungsplanDBHelper.getCourseGroup(coursename, groupname);
+ }
+
+ public CourseGroup addCourseGroup(String coursename, String groupname) {
+ if(vorlesungsplanDBHelper == null)
+ vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
+ return vorlesungsplanDBHelper.addCourseGroup(coursename, groupname);
+ }
+
+ public void updateMensaTagesplan(MensaTagesplan plan) {
+ if(mensaplanDBHelper == null)
+ mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
+ mensaplanDBHelper.updateMensaTagesplan(plan);
+ }
+
+ public MensaTagesplan[] getMensaTagesplan(long timeFrom, long timeTo) {
+ if(mensaplanDBHelper == null)
+ mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
+ return mensaplanDBHelper.getMensaTagesplan(timeFrom, timeTo);
+ }
+
+ public long[] getDaysWithPlanData(long timeFrom, long timeTo) {
+ if(mensaplanDBHelper == null)
+ mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
+ return mensaplanDBHelper.getDaysWithPlanData(timeFrom, timeTo);
+ }
+
+ public long[] getWeeksWithPlanData(long timeFrom, long timeTo) {
+ if(mensaplanDBHelper == null)
+ mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
+ return mensaplanDBHelper.getWeeksWithPlanData(timeFrom, timeTo);
+ }
+
+ public void updateNewsItem(NewsItem news) {
+ if(newsDBHelper == null)
+ newsDBHelper = new NewsDatabaseHelper(AppContext, database);
+ newsDBHelper.updateNewsItem(news);
+ }
+
+ public NewsItem[] getNewsItems(String source, long timeFrom, long timeTo) {
+ if(newsDBHelper == null)
+ newsDBHelper = new NewsDatabaseHelper(AppContext, database);
+ return newsDBHelper.getNewsItems(source, timeFrom, timeTo);
+ }
+
+}