mainActivity/src/com/example/furt/myapplication/personalDBHelper.java

   1 package com.example.furt.myapplication;
   2
   3 import android.content.ContentValues;
   4 import android.content.Context;
   5 import android.database.Cursor;
   6 import android.database.sqlite.SQLiteDatabase;
   7 import android.database.sqlite.SQLiteOpenHelper;
   8 import android.util.Log;
   9
  10 import java.sql.Timestamp;
  11 import java.util.ArrayList;
  12 import java.util.List;
  13
  14 import static com.example.furt.myapplication.personalTrackerContract.DATABASE_NAME;
  15 import static com.example.furt.myapplication.personalTrackerContract.DATABASE_VERSION;
  16 import static com.example.furt.myapplication.personalTrackerContract.esercizi;
  17 import static com.example.furt.myapplication.personalTrackerContract.esercizi.COLUMN_CHECK;
  18 import static com.example.furt.myapplication.personalTrackerContract.esercizi.COLUMN_CLICK;
  19 import static com.example.furt.myapplication.personalTrackerContract.esercizi.COLUMN_ESERCIZIO;
  20 import static com.example.furt.myapplication.personalTrackerContract.esercizi.COLUMN_MD5;
  21 import static com.example.furt.myapplication.personalTrackerContract.esercizi.COLUMN_TIME;
  22 import static com.example.furt.myapplication.personalTrackerContract.esercizi.COLUMN_UTENTE;
  23 import static com.example.furt.myapplication.personalTrackerContract.esercizi.SQL_CREATE_ENTRIES;
  24 import static com.example.furt.myapplication.personalTrackerContract.esercizi.TABLE_NAME;
  25
  26
  27
  28 public class personalDBHelper extends SQLiteOpenHelper{
  29     public personalDBHelper(Context context) {
  30         super(context, DATABASE_NAME, null, DATABASE_VERSION);
  31     }
  32
  33     public void onCreate(SQLiteDatabase db) {
  34
  35         db.execSQL(SQL_CREATE_ENTRIES);
  36         Log.e("dphelper", SQL_CREATE_ENTRIES);
  37     }
  38
  39     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  40         // This database is only a cache for online data, so its upgrade policy is
  41         // to simply to discard the data and start over
  42         db.execSQL(SQL_CREATE_ENTRIES);
  43         onCreate(db);
  44     }
  45
  46     public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  47         onUpgrade(db, oldVersion, newVersion);
  48     }
  49
  50     public void add(String utente, String esercizio, String md5, Double time, int ckick, int check)
  51     {
  52         SQLiteDatabase db=this.getWritableDatabase();
  53         ContentValues values=new ContentValues();
  54         values.put(COLUMN_MD5,md5);
  55         if(time==0)
  56             values.put(COLUMN_TIME,(double)time());
  57         else
  58             values.put(COLUMN_TIME,(time));
  59         values.put(COLUMN_CHECK,check);
  60         values.put(COLUMN_UTENTE,utente);
  61         values.put(COLUMN_ESERCIZIO,esercizio);
  62         values.put(COLUMN_CLICK, ckick);
  63         db.insert(TABLE_NAME, null, values);
  64         db.close();
  65     }
  66
  67     public void remove(String user, double time)
  68     {
  69         SQLiteDatabase db = this.getWritableDatabase();
  70         String request=COLUMN_MD5 + "=? AND " + COLUMN_TIME + "=? AND " + COLUMN_UTENTE + "=?";
  71         String[] condition=new String[]{
  72                 String.valueOf(time),
  73                 String.valueOf(user)
  74         };
  75         db.delete(TABLE_NAME,request,condition);
  76         db.close();
  77     }
  78
  79     public static Long time()
  80     {
  81         java.util.Date date= new java.util.Date();
  82         Timestamp now=new Timestamp(date.getTime());
  83         return now.getTime();
  84     }
  85
  86     public void removeAll(){
  87         SQLiteDatabase db=this.getWritableDatabase();
  88         String request="TRUNCATE "+TABLE_NAME;
  89     }
  90
  91     public List<listElem> getElem(String user)
  92     {
  93         List<listElem> elem=null;
  94         SQLiteDatabase db=this.getReadableDatabase();
  95         String[] tableColums={
  96             esercizi.COLUMN_MD5,
  97             esercizi.COLUMN_TIME,
  98             esercizi.COLUMN_CLICK,
  99             esercizi.COLUMN_ESERCIZIO
 100         };
 101         String whereClause= COLUMN_UTENTE +"=? AND " + COLUMN_CHECK +"=?";
 102         String[] whereArgs=new String[]{
 103                 String.valueOf(user),
 104                 String.valueOf("0")
 105         };
 106         Cursor cursor=db.query(TABLE_NAME,tableColums,whereClause,whereArgs,null,null,null);
 107         if(cursor.moveToFirst()) {
 108             elem = new ArrayList<listElem>();
 109             listElem el=new listElem();
 110             do {
 111                 el.setMd5 (cursor.getString(0));
 112                 el.setTime(cursor.getDouble(1));
 113                 el.setClick(cursor.getInt(2));
 114                 el.setEsercizio(cursor.getString(3));
 115                 elem.add(el);
 116             }while(cursor.moveToNext());
 117         }
 118         return elem;
 119     }
 120
 121     public List<listElem> getElemForName(String user, String esName)
 122     {
 123         List<listElem> elem=null;
 124         SQLiteDatabase db=this.getReadableDatabase();
 125         String[] tableColums={
 126                 esercizi.COLUMN_MD5,
 127                 esercizi.COLUMN_TIME,
 128                 esercizi.COLUMN_CLICK,
 129                 esercizi.COLUMN_CHECK
 130         };
 131         String whereClause= COLUMN_UTENTE +"=? AND " + COLUMN_ESERCIZIO +"=? ORDER BY "+COLUMN_CLICK;
 132         String[] whereArgs=new String[]{
 133                 String.valueOf(user),
 134                 String.valueOf(esName)
 135         };
 136         Cursor cursor=db.query(TABLE_NAME,tableColums,whereClause,whereArgs,null,null,null);
 137         if(cursor.moveToFirst()) {
 138             elem = new ArrayList<listElem>();
 139             listElem el=new listElem();
 140             do {
 141                 el.setMd5 (cursor.getString(0));
 142                 el.setTime(cursor.getDouble(1));
 143                 el.setClick(cursor.getInt(2));
 144                 el.setCheck(cursor.getInt(3));
 145                 elem.add(el);
 146             }while(cursor.moveToNext());
 147         }
 148         return elem;
 149     }
 150
 151     public List<listElem> getAllElem(String user)
 152     {
 153         List<listElem> elem=null;
 154         SQLiteDatabase db=this.getReadableDatabase();
 155         String[] tableColums={
 156                 esercizi.COLUMN_MD5,
 157                 esercizi.COLUMN_TIME,
 158                 esercizi.COLUMN_CLICK,
 159                 esercizi.COLUMN_ESERCIZIO,
 160                 esercizi.COLUMN_CHECK
 161         };
 162         String whereClause= COLUMN_UTENTE +"=?";
 163         String[] whereArgs=new String[]{
 164                 String.valueOf(user)
 165         };
 166         Cursor cursor=db.query(TABLE_NAME,tableColums,whereClause,whereArgs,null,null,null);
 167         if(cursor.moveToFirst()) {
 168             elem = new ArrayList<listElem>();
 169             listElem el=new listElem();
 170             do {
 171                 el.setMd5 (cursor.getString(0));
 172                 el.setTime(cursor.getDouble(1));
 173                 el.setClick(cursor.getInt(2));
 174                 el.setEsercizio(cursor.getString(3));
 175                 el.setCheck(cursor.getInt(4));
 176                 elem.add(el);
 177             }while(cursor.moveToNext());
 178         }
 179         return elem;
 180     }
 181
 182     public void updateElem(String md5, String user, double time)
 183     {
 184         SQLiteDatabase db=this.getWritableDatabase();
 185         String request="UPDATE "+TABLE_NAME+" set "+COLUMN_CHECK+"=1 WHERE "+COLUMN_UTENTE+"='"+user+"' AND "+COLUMN_MD5+"='"+md5+"' AND "+COLUMN_TIME+"="+time;
 186         db.execSQL(request);
 187     }
 188
 189 }