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

   1 /* MensaplanDatabaseHelper.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
  22 import java.util.ArrayList;
  23
  24 import de.dhbwloe.campusapp.CampusAppContext;
  25 import de.dhbwloe.campusapp.mensaplan.MensaTagesplan;
  26
  27 /**
  28  * Created by pk910 on 24.01.2016.
  29  */
  30 public class MensaplanDatabaseHelper {
  31     private CampusAppContext AppContext;
  32     private SQLiteDatabase database;
  33
  34     public MensaplanDatabaseHelper(CampusAppContext context, SQLiteDatabase database) {
  35         this.database = database;
  36         AppContext = context;
  37     }
  38
  39     public void updateMensaTagesplan(MensaTagesplan plan) {
  40         boolean isExisting = false;
  41         String[] whereArgs = new String[] {
  42                 Long.toString(plan.getPlanDate()),
  43                 plan.getMenuName()
  44         };
  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
  50             isExisting = true;
  51         }
  52         resultSet.close();
  53
  54         if(isExisting) {
  55             try {
  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]);
  67
  68                 database.update("MensaPlan", updateValues, "PlanDate = ? AND MenuName = ?", whereArgs);
  69             } catch(Exception e) {
  70                 e.printStackTrace();
  71             }
  72         } else {
  73             try {
  74                 ContentValues indexValues = new ContentValues();
  75                 indexValues.put("PlanDate", plan.getPlanDate());
  76                 indexValues.put("MenuName", plan.getMenuName());
  77
  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]);
  88
  89                 database.insertOrThrow("MensaPlan", null, indexValues);
  90                 plan.setIsNew();
  91             } catch(Exception e) {
  92                 e.printStackTrace();
  93             }
  94         }
  95
  96     }
  97
  98     public MensaTagesplan[] getMensaTagesplan(long timeFrom, long timeTo) {
  99         String[] whereArgs = new String[] {
 100                 Long.toString(timeFrom),
 101                 Long.toString(timeTo)
 102         };
 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")
 118             };
 119             do {
 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])
 127                 );
 128
 129                 results.add(plan);
 130             } while (resultSet.moveToNext());
 131         }
 132         resultSet.close();
 133
 134         MensaTagesplan[] resultsArr = new MensaTagesplan[results.size()];
 135         resultsArr = results.toArray(resultsArr);
 136         return resultsArr;
 137     }
 138
 139     public long[] getDaysWithPlanData(long timeFrom, long timeTo) {
 140         String[] whereArgs = new String[] {
 141                 Long.toString(timeFrom),
 142                 Long.toString(timeTo)
 143         };
 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()) {
 147             do {
 148                 long date = resultSet.getLong(0);
 149
 150                 results.add(date);
 151             } while (resultSet.moveToNext());
 152         }
 153         resultSet.close();
 154
 155         long[] resultsArr = new long[results.size()];
 156         for(int i = 0; i < resultsArr.length; i++) {
 157             resultsArr[i] = results.get(i);
 158         }
 159         return resultsArr;
 160     }
 161
 162     public long[] getWeeksWithPlanData(long timeFrom, long timeTo) {
 163         String[] whereArgs = new String[] {
 164                 Long.toString(timeFrom),
 165                 Long.toString(timeTo)
 166         };
 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()) {
 170             do {
 171                 long date = resultSet.getLong(0);
 172
 173                 results.add(date);
 174             } while (resultSet.moveToNext());
 175         }
 176         resultSet.close();
 177
 178         long[] resultsArr = new long[results.size()];
 179         for(int i = 0; i < resultsArr.length; i++) {
 180             resultsArr[i] = results.get(i);
 181         }
 182         return resultsArr;
 183     }
 184
 185 }