package com.eryos.android.cigarettecounter.database;
/**
* Project : CigaretteCounter
* Author : Olivier Combe
* URL : http://eryos.fr/CigaretteCounter/
*
* Copyright (C) 2011 - Olivier Combe
*
* Licensed under the Apache License, Version 2.0 (the "License"); you may not
* use this file except in compliance with the License. You may obtain a copy of
* the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
* License for the specific language governing permissions and limitations under
* the License.
*
*/
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;
import android.content.Context;
import android.database.Cursor;
import android.database.CursorIndexOutOfBoundsException;
import android.util.Log;
import com.eryos.android.cigarettecounter.beans.SmokeAverage;
import com.eryos.android.cigarettecounter.beans.SmokeCounter;
import com.eryos.android.cigarettecounter.beans.SmokeTime;
import com.eryos.android.cigarettecounter.tool.Tools;
public class CigaretteDatabase extends DBAdapter {
public static final int NUMBER_OF_HISTORY = 6;
public static final int ROUNDING = 2;
private static final String DATE_FORMAT = "%d/%m/%Y";
private static final String WEEK_FORMAT = "%W";
private static final String MONTH_FORMAT = "%m";
//private static final String NOW = "datetime("+System.currentTimeMillis()/1000+", 'unixepoch', 'localtime')";
private static final String THIS_DAY = "strftime('"+DATE_FORMAT+"', "+getNow()+")";
private static final String THIS_WEEK = "strftime('"+WEEK_FORMAT+"', "+getNow()+")";
private static final String THIS_MONTH = "strftime('"+MONTH_FORMAT+"', "+getNow()+")";
private static final String SIX_WEEKS_AGO = "strftime('%Y-%W', "+getNow()+", '-"+(NUMBER_OF_HISTORY*7)+" days')";
private static final String SIX_MONTHS_AGO = "strftime('%Y-%m', "+getNow()+", '-"+NUMBER_OF_HISTORY+" months')";
private static final String DB_WEEK = "strftime('"+WEEK_FORMAT+"', "+KEY_SMOKE_TIME+")";
private static final String DB_MONTH = "strftime('"+MONTH_FORMAT+"', "+KEY_SMOKE_TIME+")";
public static String getNow(){
return "datetime("+System.currentTimeMillis()/1000+", 'unixepoch', 'localtime')";
}
private String LOG_TAG = "";
private static final String CSV_SEPARATOR = ";";
private static CigaretteDatabase currentDB;
/**
* Create a new CigaretteDatabase Object link to the context in argument
* @param ctx Application context
*/
private CigaretteDatabase(Context ctx) {
super(ctx);
LOG_TAG = getClass().getSimpleName();
}
public static CigaretteDatabase getInstance(Context ctx){
if ( currentDB == null ){
currentDB = new CigaretteDatabase(ctx);
}
return currentDB;
}
/*****************************************************************************/
/**
* Return the result of the division between SUM and NB
*/
public double getAverage(double _sum, double _nb){
try {
if ( _nb != 0 ){
BigDecimal sum = new BigDecimal(_sum).setScale(2);
BigDecimal nb = new BigDecimal(_nb).setScale(2);
return sum.divide(nb, ROUNDING, RoundingMode.DOWN).doubleValue();
}else
return 0;
} catch (Exception e) {
Log.e(this.getClass().getSimpleName(), "Compute Average", e);
return -1;
}
}
/**
* Parse an SQL cursor into a ArrayList of SmokeAverage(Label/Double)
*/
public ArrayList<SmokeAverage> parseCursorAsSmokeAverage( Cursor mCursor ){
ArrayList<SmokeAverage> res = new ArrayList<SmokeAverage>();
if (mCursor != null){
mCursor.moveToFirst();
for(int i=0; i < mCursor.getCount(); i++) {
res.add(new SmokeAverage(mCursor.getString(0), mCursor.getDouble(3) ));
mCursor.moveToNext();
}
}
return res;
}
/**
* Parse an SQL cursor into a ArrayList of SmokeCount(Label/Int)
*/
public ArrayList<SmokeCounter> parseCursorAsSmokeCounter( Cursor mCursor ){
ArrayList<SmokeCounter> res = new ArrayList<SmokeCounter>();
if (mCursor != null){
mCursor.moveToFirst();
for(int i=0; i < mCursor.getCount(); i++) {
SmokeCounter smoke = new SmokeCounter(mCursor.getString(0),mCursor.getInt(1));
try {
Calendar c = Calendar.getInstance();
c.set(Calendar.DAY_OF_MONTH, mCursor.getInt(2));
c.set(Calendar.MONTH, mCursor.getInt(3));
c.set(Calendar.YEAR, mCursor.getInt(4));
smoke.setDate(c);
} catch (Exception e) {
Log.e(this.getClass().getSimpleName(), "Parse as Smoke Counter", e);
}
res.add(smoke);
mCursor.moveToNext();
}
}
return res;
}
/*****************************************************************************/
/**
* Return the number of cigarettes smoked this day
*/
public int getDailyCount(){
Log.i(getClass().getSimpleName(), "get Daily Count");
String sqlQuery =
"SELECT strftime('"+DATE_FORMAT+"', smoke_time), count(1) "+
"FROM "+ DATABASE_TABLE +" "+
"WHERE strftime('"+DATE_FORMAT+"', smoke_time) = "+THIS_DAY+" "+
"GROUP BY strftime('"+DATE_FORMAT+"', smoke_time) ;";
int res = 0;
Cursor mCursor = db.rawQuery(sqlQuery, null);
if (mCursor != null && mCursor.getCount() > 0) {
mCursor.moveToFirst();
try {
res = mCursor.getInt(1);
} catch (CursorIndexOutOfBoundsException e) {
Log.w(getClass().getSimpleName(), "get Daily Count", e);
}
}
return res;
}
/**
* Return the number of cigarettes smoked this week
*/
public int getWeeklyCount(){
Log.i(this.getClass().getSimpleName(), "get Weekly Count");
String sqlQuery =
"SELECT count(1) Nb "+
"FROM "+ DATABASE_TABLE +" "+
"WHERE "+DB_WEEK+" = "+THIS_WEEK+"; ";
int res = -1;
Cursor mCursor = db.rawQuery(sqlQuery, null);
if (mCursor != null) {
mCursor.moveToFirst();
res = mCursor.getInt(0);
}
return res;
}
/**
* Return the number of cigarettes smoked this week
*/
public double getWeeklyCost(){
Log.i(this.getClass().getSimpleName(), "get Weekly Cost");
String sqlQuery =
"SELECT round(sum(ifnull(smoke_valid,0)), "+ROUNDING+") Nb "+
"FROM "+ DATABASE_TABLE +" "+
"WHERE strftime('%W', smoke_time) = "+THIS_WEEK+"; ";
double res = -1;
Cursor mCursor = db.rawQuery(sqlQuery, null);
if (mCursor != null) {
mCursor.moveToFirst();
res = mCursor.getDouble(0);
}
return res;
}
/**
* Return the average number of cigarettes smoked by day this week
*/
public double getWeeklyAverage(){
Log.i(this.getClass().getSimpleName(), "get Weekly Average");
try {
String sqlQuery =
"SELECT sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
"FROM "+
"( "+
" SELECT strftime('%d/%m/%Y', smoke_time) Day, count(1) Nb "+
" FROM "+ DATABASE_TABLE +" "+
" WHERE strftime('%W', smoke_time) = "+THIS_WEEK+" "+
" GROUP BY strftime('%d/%m/%Y', smoke_time) "+
" ORDER BY strftime('%d/%m/%Y', smoke_time) "+
");";
double res = -1;
Cursor mCursor = db.rawQuery(sqlQuery, null);
if (mCursor != null) {
mCursor.moveToFirst();
res = mCursor.getDouble(2);getAverage( mCursor.getDouble(0), mCursor.getDouble(1) );
}
return res;
} catch (Exception e) {
e.printStackTrace();
return -1;
}
}
/**
* Return the number of cigarettes smoked each day of the week
*/
public ArrayList<SmokeCounter> getWeeklyDetails(){
Log.i(this.getClass().getSimpleName(), "get Weekly Details");
String sqlQuery =
//"SELECT strftime('%w', smoke_time) Day, count(1) Nb " +
"SELECT strftime('%w', smoke_time) Day, count(1) Nb, strftime('%d', smoke_time) Day, strftime('%m', smoke_time) Month, strftime('%Y', smoke_time) Year "+
"FROM "+ DATABASE_TABLE +" " +
"WHERE strftime('%W', smoke_time) = "+THIS_WEEK+" " +
"GROUP BY strftime('%w', smoke_time) " +
"ORDER BY strftime('%Y-%m-%d', smoke_time); ";
Cursor mCursor = db.rawQuery(sqlQuery, null);
return parseCursorAsSmokeCounter(mCursor);
}
/**
* Return the average number of cigarettes smoked by day for the last 6 weeks
*/
public ArrayList<SmokeAverage> getLastWeeksAverage(){
Log.i(this.getClass().getSimpleName(), "get Last 6 Weekly History");
String sqlQuery =
"SELECT Week, sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
"FROM "+
"( "+
" SELECT strftime('%W', smoke_time) Week, strftime('%Y-%m-%d', smoke_time) Day, count(1) Nb "+
" FROM "+ DATABASE_TABLE +" "+
" WHERE strftime('%Y-%W', smoke_time) > "+SIX_WEEKS_AGO+" "+
" GROUP BY strftime('%W', smoke_time), strftime('%d/%m/%Y', smoke_time) "+
") "+
"GROUP BY Week " +
"ORDER BY Day; ";
Cursor mCursor = db.rawQuery(sqlQuery, null);
return parseCursorAsSmokeAverage(mCursor);
}
/**
* Return the number of cigarettes smoked this month
*/
public int getMonthlyCount(){
Log.i(this.getClass().getSimpleName(), "get Monthly Count");
String sqlQuery =
"SELECT count(1) Nb "+
"FROM "+ DATABASE_TABLE +" "+
"WHERE strftime('%m', smoke_time) = "+THIS_MONTH+"; ";
int res = -1;
Cursor mCursor = db.rawQuery(sqlQuery, null);
if (mCursor != null) {
mCursor.moveToFirst();
res = mCursor.getInt(0);
}
return res;
}
/**
* Return the number of cigarettes smoked this month
*/
public double getMonthlyCost(){
Log.i(this.getClass().getSimpleName(), "get Monthly Cost");
String sqlQuery =
"SELECT round(sum(ifnull(smoke_valid,0)), "+ROUNDING+") Nb "+
"FROM "+ DATABASE_TABLE +" "+
"WHERE strftime('%m', smoke_time) = "+THIS_MONTH+"; ";
double res = -1;
Cursor mCursor = db.rawQuery(sqlQuery, null);
if (mCursor != null) {
mCursor.moveToFirst();
res = mCursor.getDouble(0);
}
return res;
}
/**
* Return the average number of cigarettes smoked by day this month
*/
public double getMonthlyAverage(){
Log.i(this.getClass().getSimpleName(), "get Monthly Average");
try {
String sqlQuery =
"SELECT sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
"FROM "+
"( "+
" SELECT strftime('%d/%m/%Y', smoke_time) Day, count(1) Nb "+
" FROM "+ DATABASE_TABLE +" "+
" WHERE strftime('%m', smoke_time) = "+THIS_MONTH+" "+
" GROUP BY strftime('%d/%m/%Y', smoke_time) "+
" ORDER BY strftime('%d/%m/%Y', smoke_time) "+
");";
double res = -1;
Cursor mCursor = db.rawQuery(sqlQuery, null);
if (mCursor != null) {
mCursor.moveToFirst();
res = mCursor.getDouble(2);//getAverage( mCursor.getDouble(0), mCursor.getDouble(1) );
}
return res;
} catch (Exception e) {
e.printStackTrace();
return -1;
}
}
/**
* Return the number of cigarettes smoked by day for this month
*/
public ArrayList<SmokeCounter> getMonthlyDetails(){
Log.i(this.getClass().getSimpleName(), "get Monthly Details");
try {
String sqlQuery =
"SELECT strftime('%d/%m/%Y', smoke_time) Day, count(1) Nb, strftime('%d', smoke_time) Day, strftime('%m', smoke_time) Month, strftime('%Y', smoke_time) Year "+
"FROM "+ DATABASE_TABLE +" "+
"WHERE strftime('%m', smoke_time) = "+THIS_MONTH+" "+
"GROUP BY strftime('%d/%m/%Y', smoke_time) "+
"ORDER BY strftime('%d/%m/%Y', smoke_time); ";
Cursor mCursor = db.rawQuery(sqlQuery, null);
return parseCursorAsSmokeCounter(mCursor);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* Return the average number of cigarettes smoked by day for the last 6 month
*/
public ArrayList<SmokeAverage> getLastMonthsAverage(){
Log.i(this.getClass().getSimpleName(), "get Last 6 Months History");
try {
String sqlQuery =
"SELECT Month, sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
"FROM "+
"( "+
" SELECT strftime('%m', smoke_time) Month, strftime('%Y-%m-%d', smoke_time) Day, count(1) Nb "+
" FROM "+ DATABASE_TABLE +" "+
" WHERE strftime('%Y-%m', smoke_time) > "+SIX_MONTHS_AGO+" "+
" GROUP BY strftime('%m', smoke_time), strftime('%d/%m/%Y', smoke_time) ORDER BY smoke_time"+
") "+
"GROUP BY Month " +
"ORDER BY Day; ";
Cursor mCursor = db.rawQuery(sqlQuery, null);
return parseCursorAsSmokeAverage(mCursor);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* Return the time since the last cigarette
*/
public SmokeTime getDelaySinceLast(){
Log.i(getClass().getSimpleName(), "get Delay Since Last Cigarette");
try {
SmokeTime res = new SmokeTime();
String sqlQuery =
"SELECT strftime('%s', "+getNow()+"), strftime('%s',MAX(smoke_time))"+
"FROM "+ DATABASE_TABLE +";";
Cursor mCursor = db.rawQuery(sqlQuery, null);
if (mCursor != null) {
mCursor.moveToFirst();
String now = mCursor.getString(0)+"000";
String max = mCursor.getString(1)+"000";
Calendar cNow = Calendar.getInstance();
Calendar cMax = Calendar.getInstance();
cNow.setTimeInMillis(new Long(now));
cMax.setTimeInMillis(new Long(max));
Date dMax = cMax.getTime();
Date dNow = cNow.getTime();
long diff = new Long(now) - new Long(max); // milisecond
long seconds = diff / 1000; // seconds
int minute2 = (int)(seconds / 60);
int hour2 = (int)minute2 / 60; // hours
minute2 = minute2 - ( 60 * hour2 );
int hour = hour2;
int minute = minute2;
//int hour = dNow.getHours() - dMax.getHours();
//int minute = dNow.getMinutes() - dMax.getMinutes();
hour = hour < 0 ? 0 : hour;
minute = minute < 0 ? 0 : minute;
Log.d(getClass().getSimpleName(), "DB Last : "+max+" "+dMax.toLocaleString());
Log.d(getClass().getSimpleName(), "DB Now : "+now+" "+dNow.toLocaleString());
if ( new Long(max) <= new Long(now) ){
res.setHour(hour);
res.setMinute(minute);
res.setSeconds(0);
res.genTotalMinutes();
}else{
Log.d(getClass().getSimpleName(), "Max is greater than Now !!");
res.setHour(0);
res.setMinute(0);
res.setSeconds(0);
res.genTotalMinutes();
}
}
return res;
} catch (NumberFormatException e) {
Log.e(getClass().getSimpleName(), "get Delay Since Last", e);
return new SmokeTime();
}
}
/**
* Return the average time between 2 cigarettes
*/
public SmokeTime getAverageDelay(){
Log.i(this.getClass().getSimpleName(), "get Average Delay between 2 Cigarettes");
return new SmokeTime();
/*
try {
SmokeTime res = new SmokeTime();
String sqlQuery =
"SELECT ROUND(AVG(cur-last), 2) as AVG_DELAY " +
"FROM " +
"( " +
" SELECT "+KEY_SMOKE_TIME+" AS cur, " +
" ( " +
" SELECT MAX("+KEY_SMOKE_TIME+") " +
" FROM "+ DATABASE_TABLE +" " +
" WHERE "+KEY_SMOKE_TIME+" < tcur."+KEY_SMOKE_TIME+" " +
" AND strftime('"+DATE_FORMAT+"', "+KEY_SMOKE_TIME+") = strftime('"+DATE_FORMAT+"', tcur."+KEY_SMOKE_TIME+") " +
" ) AS last " +
" FROM "+ DATABASE_TABLE +" tcur " +
" WHERE "+DB_MONTH+" >= "+THIS_MONTH+" " +
") " +
"WHERE last IS NOT NULL;";
System.out.println(sqlQuery);
// "SELECT AVG(diff), strftime(AVG(diff), 'unixepoch', 'localtime') "+
// "FROM ( "+
// " SELECT ( smoke_time - ( SELECT MAX(smoke_time) FROM "+ DATABASE_TABLE +" WHERE smoke_time < t1.smoke_time ) ) as diff "+
// " FROM "+ DATABASE_TABLE +" t1 "+
// " WHERE strftime('%m', smoke_time) = "+THIS_MONTH+" "+
// " ORDER BY smoke_time "+
// ");";
Cursor mCursor = db.rawQuery(sqlQuery, null);
if (mCursor != null) {
mCursor.moveToFirst();
System.out.println("Delay Double: "+mCursor.getDouble(0));
res.setTotalTime(mCursor.getDouble(0));
res.parse();
}
//return res;
return new SmokeTime();
} catch (Exception e) {
Log.e(this.getClass().getSimpleName(), "get Average Delay", e);
return new SmokeTime();
}*/
}
/************************************************************************************/
/**
*
* @param _day
* @param _month
* @param _year
*/
public void invalidDate(int _day, int _month, int _year) {
Log.i(this.getClass().getSimpleName(), "Invalide a date");
String day = String.valueOf(_day);
String month = String.valueOf(_month);
String year = String.valueOf(_year);
if ( day.length() == 1 )
day = "0"+day;
if ( month.length() == 1 )
month = "0"+month;
db.execSQL("DELETE FROM "+DATABASE_TABLE+" WHERE strftime('%Y-%m-%d', smoke_time) = strftime('%Y-%m-%d', '"+year+"-"+month+"-"+day+"');");
}
/**
*
* @param price
*/
public void insertRandomData( float price){
Log.i(this.getClass().getSimpleName(), "Insert Random Data");
Calendar yesterday = Calendar.getInstance();
yesterday.add(Calendar.DAY_OF_MONTH, -1); // Yesterday
Calendar c = Calendar.getInstance(); // Today
// MONTHS
for ( int i = -5 ; i <= 0 ; i ++ ){
c = Calendar.getInstance(); // Today
c.setTimeZone(TimeZone.getDefault());
c.add(Calendar.MONTH, i);
int year = c.get(Calendar.YEAR);
int month = c.get(Calendar.MONTH)+1;
int lastDayOfMonth = Tools.getLastDayOfMonth(c.get(Calendar.MONTH));
if ( month-1 == yesterday.get(Calendar.MONTH) )
lastDayOfMonth = yesterday.get(Calendar.DAY_OF_MONTH);
// DAYS
for ( int day = 1; day <= lastDayOfMonth ; day++ ){ // for each day of the month
int nbInsertForCurrentDay = ((int) (Math.random()*10))+5; // between 5 and 15
c.set(Calendar.DAY_OF_MONTH, day);
// OCCURENCY
for ( int x = 0 ; x < nbInsertForCurrentDay ; x++ ){
int hour = ((int) (Math.random()*17))+6; // between 7 and 23
int minute = (int) (Math.random()*59); // between 1 and 59
int second = (int) (Math.random()*59); // between 1 and 59
int milisec = (int) (Math.random()*99); // between 1 and 99 // INSERT
c.set(Calendar.HOUR_OF_DAY, hour);
c.set(Calendar.MINUTE, minute);
c.set(Calendar.SECOND, second);
c.set(Calendar.MILLISECOND, milisec);
Log.d(getClass().getSimpleName(), "Insert day : "+c.getTime().toLocaleString());
long timestamp = c.getTimeInMillis()/1000;
if ( price < 0 )
price = 0;
// TODO
db.execSQL("INSERT INTO smoke_table ( smoke_time, smoke_reason, smoke_valid ) VALUES ( datetime("+timestamp+", 'unixepoch', 'localtime'), 'Random Data', "+price+");");
//db.execSQL("INSERT INTO smoke_table ( smoke_time, smoke_reason, smoke_valid ) VALUES ('"+year+"-"+sMonth+"-"+sDay+" "+sHour+":"+sMinute+":"+sSecond+"', 'Random', 'Y');");
}
}
}
}
/**
*
* @return
*/
public ArrayList<String> exportData(){
Log.i(getClass().getSimpleName(), "Export data");
ArrayList<String> data = new ArrayList<String>();
data.add("Smoke Time"+CSV_SEPARATOR+"Smoke Reason"+CSV_SEPARATOR+"Smoke Price");
try {
String sqlQuery =
"SELECT strftime('%Y-%m-%d %H:%M:%S', "+KEY_SMOKE_TIME+") , "+KEY_SMOKE_REASON+", "+KEY_SMOKE_PRICE+" "+
"FROM "+ DATABASE_TABLE +"; ";
Cursor mCursor = db.rawQuery(sqlQuery, null);
if ( mCursor != null ){
mCursor.moveToFirst();
while (mCursor != null && ! mCursor.isAfterLast()) {
String line = mCursor.getString(0)+ CSV_SEPARATOR +mCursor.getString(1)+ CSV_SEPARATOR +mCursor.getFloat(2);
data.add(line);
mCursor.moveToNext();
}
}
Log.d(getClass().getSimpleName(), data.size()+"line(s) exported");
} catch (Exception e) {
Log.e(this.getClass().getSimpleName(), "Export Data", e);
}
return data;
}
/**
* Method used to store in DB all data contained in the ArrayList in param
* @param data : List of data in CSV format
* @return number of lines inserted
*/
public int importData(ArrayList<String> data){
Log.i(this.getClass().getSimpleName(), "Import Data");
int nbImport = 0;
for ( String line : data){
if ( ! line.startsWith("Smoke") ){
String[] tmpValues = line.split(CSV_SEPARATOR);
String sqlQuery = "INSERT INTO "+ DATABASE_TABLE +" ( "+KEY_ROWID+", "+KEY_SMOKE_TIME+", "+KEY_SMOKE_REASON+", "+KEY_SMOKE_PRICE+" ) VALUES (null, '"+tmpValues[0]+"', '"+tmpValues[1]+"', '"+tmpValues[2]+"');";
db.execSQL(sqlQuery);
nbImport++;
}
}
Log.d(getClass().getSimpleName(), nbImport+"line(s) imported");
return nbImport;
}
}
|