1 /* MensaplanDatabaseHelper.java
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.
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.
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/>.
16 package de.dhbwloe.campusapp.database;
18 import android.content.ContentValues;
19 import android.database.Cursor;
20 import android.database.sqlite.SQLiteDatabase;
22 import java.util.ArrayList;
24 import de.dhbwloe.campusapp.CampusAppContext;
25 import de.dhbwloe.campusapp.mensaplan.MensaTagesplan;
28 * Created by pk910 on 24.01.2016.
30 public class MensaplanDatabaseHelper {
31 private CampusAppContext AppContext;
32 private SQLiteDatabase database;
34 public MensaplanDatabaseHelper(CampusAppContext context, SQLiteDatabase database) {
35 this.database = database;
39 public void updateMensaTagesplan(MensaTagesplan plan) {
40 boolean isExisting = false;
41 String[] whereArgs = new String[] {
42 Long.toString(plan.getPlanDate()),
45 Cursor resultSet = database.rawQuery("SELECT ChkSum FROM MensaPlan WHERE PlanDate = ? AND MenuName = ?", whereArgs);
46 if(resultSet.moveToFirst()) {
47 long chksum = resultSet.getLong(0);
48 if(plan.getChkSum() == chksum)
49 return; // nothing to update
56 ContentValues updateValues = new ContentValues();
57 updateValues.put("ChkSum", plan.getChkSum());
58 updateValues.put("Name", plan.getName());
59 updateValues.put("NameHtml", plan.getNameHtml());
60 updateValues.put("Additional", plan.getAdditional());
61 updateValues.put("Notes", plan.getNotes());
62 int plainPrice[] = plan.getPlainPrice();
63 updateValues.put("PriceStudents", plainPrice[0]);
64 updateValues.put("PriceEmployees", plainPrice[1]);
65 updateValues.put("PriceGuests", plainPrice[2]);
66 updateValues.put("PriceSchool", plainPrice[3]);
68 database.update("MensaPlan", updateValues, "PlanDate = ? AND MenuName = ?", whereArgs);
69 } catch(Exception e) {
74 ContentValues indexValues = new ContentValues();
75 indexValues.put("PlanDate", plan.getPlanDate());
76 indexValues.put("MenuName", plan.getMenuName());
78 indexValues.put("ChkSum", plan.getChkSum());
79 indexValues.put("Name", plan.getName());
80 indexValues.put("NameHtml", plan.getNameHtml());
81 indexValues.put("Additional", plan.getAdditional());
82 indexValues.put("Notes", plan.getNotes());
83 int plainPrice[] = plan.getPlainPrice();
84 indexValues.put("PriceStudents", plainPrice[0]);
85 indexValues.put("PriceEmployees", plainPrice[1]);
86 indexValues.put("PriceGuests", plainPrice[2]);
87 indexValues.put("PriceSchool", plainPrice[3]);
89 database.insertOrThrow("MensaPlan", null, indexValues);
91 } catch(Exception e) {
98 public MensaTagesplan[] getMensaTagesplan(long timeFrom, long timeTo) {
99 String[] whereArgs = new String[] {
100 Long.toString(timeFrom),
101 Long.toString(timeTo)
103 Cursor resultSet = database.rawQuery("SELECT PlanDate,MenuName,ChkSum,Name,NameHtml,Additional,Notes,PriceStudents,PriceEmployees,PriceGuests,PriceSchool FROM MensaPlan WHERE PlanDate >= ? AND PlanDate <= ?", whereArgs);
104 ArrayList<MensaTagesplan> results = new ArrayList<MensaTagesplan>();
105 if(resultSet.moveToFirst()) {
106 int[] columnIndexes = {
107 resultSet.getColumnIndex("PlanDate"),
108 resultSet.getColumnIndex("MenuName"),
109 resultSet.getColumnIndex("ChkSum"),
110 resultSet.getColumnIndex("Name"),
111 resultSet.getColumnIndex("NameHtml"),
112 resultSet.getColumnIndex("Additional"),
113 resultSet.getColumnIndex("Notes"),
114 resultSet.getColumnIndex("PriceStudents"),
115 resultSet.getColumnIndex("PriceEmployees"),
116 resultSet.getColumnIndex("PriceGuests"),
117 resultSet.getColumnIndex("PriceSchool")
120 MensaTagesplan plan = new MensaTagesplan(
121 resultSet.getLong(columnIndexes[0]), resultSet.getString(columnIndexes[1]),
122 resultSet.getLong(columnIndexes[2]), resultSet.getString(columnIndexes[3]),
123 resultSet.getString(columnIndexes[4]), resultSet.getString(columnIndexes[5]),
124 resultSet.getString(columnIndexes[6]),
125 resultSet.getInt(columnIndexes[7]), resultSet.getInt(columnIndexes[8]),
126 resultSet.getInt(columnIndexes[9]), resultSet.getInt(columnIndexes[10])
130 } while (resultSet.moveToNext());
134 MensaTagesplan[] resultsArr = new MensaTagesplan[results.size()];
135 resultsArr = results.toArray(resultsArr);
139 public long[] getDaysWithPlanData(long timeFrom, long timeTo) {
140 String[] whereArgs = new String[] {
141 Long.toString(timeFrom),
142 Long.toString(timeTo)
144 Cursor resultSet = database.rawQuery("SELECT PlanDate FROM MensaPlan WHERE PlanDate >= ? AND PlanDate <= ? GROUP BY PlanDate", whereArgs);
145 ArrayList<Long> results = new ArrayList<Long>();
146 if(resultSet.moveToFirst()) {
148 long date = resultSet.getLong(0);
151 } while (resultSet.moveToNext());
155 long[] resultsArr = new long[results.size()];
156 for(int i = 0; i < resultsArr.length; i++) {
157 resultsArr[i] = results.get(i);
162 public long[] getWeeksWithPlanData(long timeFrom, long timeTo) {
163 String[] whereArgs = new String[] {
164 Long.toString(timeFrom),
165 Long.toString(timeTo)
167 Cursor resultSet = database.rawQuery("SELECT MIN(PlanDate) AS PlanDate, strftime(\"%W\", PlanDate, \"unixepoch\") AS PlanWeek FROM MensaPlan WHERE PlanDate >= ? AND PlanDate <= ? GROUP BY PlanWeek", whereArgs);
168 ArrayList<Long> results = new ArrayList<Long>();
169 if(resultSet.moveToFirst()) {
171 long date = resultSet.getLong(0);
174 } while (resultSet.moveToNext());
178 long[] resultsArr = new long[results.size()];
179 for(int i = 0; i < resultsArr.length; i++) {
180 resultsArr[i] = results.get(i);