app/src/main/java/de/dhbwloe/campusapp/database/MensaplanDatabaseHelper.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
   7 import java.util.ArrayList;
   8
   9 import de.dhbwloe.campusapp.CampusAppContext;
  10 import de.dhbwloe.campusapp.mensaplan.MensaTagesplan;
  11
  12 /**
  13  * Created by pk910 on 24.01.2016.
  14  */
  15 public class MensaplanDatabaseHelper {
  16     private CampusAppContext AppContext;
  17     private SQLiteDatabase database;
  18
  19     public MensaplanDatabaseHelper(CampusAppContext context, SQLiteDatabase database) {
  20         this.database = database;
  21         AppContext = context;
  22     }
  23
  24     public void updateMensaTagesplan(MensaTagesplan plan) {
  25         boolean isExisting = false;
  26         String[] whereArgs = new String[] {
  27                 Long.toString(plan.getPlanDate()),
  28                 plan.getMenuName()
  29         };
  30         Cursor resultSet = database.rawQuery("SELECT ChkSum FROM MensaPlan WHERE PlanDate = ? AND MenuName = ?", whereArgs);
  31         if(resultSet.moveToFirst()) {
  32             long chksum = resultSet.getLong(0);
  33             if(plan.getChkSum() == chksum)
  34                 return; // nothing to update
  35             isExisting = true;
  36         }
  37         resultSet.close();
  38
  39         if(isExisting) {
  40             try {
  41                 ContentValues updateValues = new ContentValues();
  42                 updateValues.put("ChkSum", plan.getChkSum());
  43                 updateValues.put("Name", plan.getName());
  44                 updateValues.put("NameHtml", plan.getNameHtml());
  45                 updateValues.put("Additional", plan.getAdditional());
  46                 updateValues.put("Notes", plan.getNotes());
  47                 int plainPrice[] = plan.getPlainPrice();
  48                 updateValues.put("PriceStudents", plainPrice[0]);
  49                 updateValues.put("PriceEmployees", plainPrice[1]);
  50                 updateValues.put("PriceGuests", plainPrice[2]);
  51                 updateValues.put("PriceSchool", plainPrice[3]);
  52
  53                 database.update("MensaPlan", updateValues, "PlanDate = ? AND MenuName = ?", whereArgs);
  54             } catch(Exception e) {
  55                 e.printStackTrace();
  56             }
  57         } else {
  58             try {
  59                 ContentValues indexValues = new ContentValues();
  60                 indexValues.put("PlanDate", plan.getPlanDate());
  61                 indexValues.put("MenuName", plan.getMenuName());
  62
  63                 indexValues.put("ChkSum", plan.getChkSum());
  64                 indexValues.put("Name", plan.getName());
  65                 indexValues.put("NameHtml", plan.getNameHtml());
  66                 indexValues.put("Additional", plan.getAdditional());
  67                 indexValues.put("Notes", plan.getNotes());
  68                 int plainPrice[] = plan.getPlainPrice();
  69                 indexValues.put("PriceStudents", plainPrice[0]);
  70                 indexValues.put("PriceEmployees", plainPrice[1]);
  71                 indexValues.put("PriceGuests", plainPrice[2]);
  72                 indexValues.put("PriceSchool", plainPrice[3]);
  73
  74                 database.insertOrThrow("MensaPlan", null, indexValues);
  75                 plan.setIsNew();
  76             } catch(Exception e) {
  77                 e.printStackTrace();
  78             }
  79         }
  80
  81     }
  82
  83     public MensaTagesplan[] getMensaTagesplan(long timeFrom, long timeTo) {
  84         String[] whereArgs = new String[] {
  85                 Long.toString(timeFrom),
  86                 Long.toString(timeTo)
  87         };
  88         Cursor resultSet = database.rawQuery("SELECT PlanDate,MenuName,ChkSum,Name,NameHtml,Additional,Notes,PriceStudents,PriceEmployees,PriceGuests,PriceSchool FROM MensaPlan WHERE PlanDate >= ? AND PlanDate <= ?", whereArgs);
  89         ArrayList<MensaTagesplan> results = new ArrayList<MensaTagesplan>();
  90         if(resultSet.moveToFirst()) {
  91             int[] columnIndexes = {
  92                     resultSet.getColumnIndex("PlanDate"),
  93                     resultSet.getColumnIndex("MenuName"),
  94                     resultSet.getColumnIndex("ChkSum"),
  95                     resultSet.getColumnIndex("Name"),
  96                     resultSet.getColumnIndex("NameHtml"),
  97                     resultSet.getColumnIndex("Additional"),
  98                     resultSet.getColumnIndex("Notes"),
  99                     resultSet.getColumnIndex("PriceStudents"),
 100                     resultSet.getColumnIndex("PriceEmployees"),
 101                     resultSet.getColumnIndex("PriceGuests"),
 102                     resultSet.getColumnIndex("PriceSchool")
 103             };
 104             do {
 105                 MensaTagesplan plan = new MensaTagesplan(
 106                         resultSet.getLong(columnIndexes[0]), resultSet.getString(columnIndexes[1]),
 107                         resultSet.getLong(columnIndexes[2]), resultSet.getString(columnIndexes[3]),
 108                         resultSet.getString(columnIndexes[4]), resultSet.getString(columnIndexes[5]),
 109                         resultSet.getString(columnIndexes[6]),
 110                         resultSet.getInt(columnIndexes[7]), resultSet.getInt(columnIndexes[8]),
 111                         resultSet.getInt(columnIndexes[9]), resultSet.getInt(columnIndexes[10])
 112                 );
 113
 114                 results.add(plan);
 115             } while (resultSet.moveToNext());
 116         }
 117         resultSet.close();
 118
 119         MensaTagesplan[] resultsArr = new MensaTagesplan[results.size()];
 120         resultsArr = results.toArray(resultsArr);
 121         return resultsArr;
 122     }
 123
 124     public long[] getDaysWithPlanData(long timeFrom, long timeTo) {
 125         String[] whereArgs = new String[] {
 126                 Long.toString(timeFrom),
 127                 Long.toString(timeTo)
 128         };
 129         Cursor resultSet = database.rawQuery("SELECT PlanDate FROM MensaPlan WHERE PlanDate >= ? AND PlanDate <= ?  GROUP BY PlanDate", whereArgs);
 130         ArrayList<Long> results = new ArrayList<Long>();
 131         if(resultSet.moveToFirst()) {
 132             do {
 133                 long date = resultSet.getLong(0);
 134
 135                 results.add(date);
 136             } while (resultSet.moveToNext());
 137         }
 138         resultSet.close();
 139
 140         long[] resultsArr = new long[results.size()];
 141         for(int i = 0; i < resultsArr.length; i++) {
 142             resultsArr[i] = results.get(i);
 143         }
 144         return resultsArr;
 145     }
 146
 147     public long[] getWeeksWithPlanData(long timeFrom, long timeTo) {
 148         String[] whereArgs = new String[] {
 149                 Long.toString(timeFrom),
 150                 Long.toString(timeTo)
 151         };
 152         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);
 153         ArrayList<Long> results = new ArrayList<Long>();
 154         if(resultSet.moveToFirst()) {
 155             do {
 156                 long date = resultSet.getLong(0);
 157
 158                 results.add(date);
 159             } while (resultSet.moveToNext());
 160         }
 161         resultSet.close();
 162
 163         long[] resultsArr = new long[results.size()];
 164         for(int i = 0; i < resultsArr.length; i++) {
 165             resultsArr[i] = results.get(i);
 166         }
 167         return resultsArr;
 168     }
 169
 170 }