In my app, after i get all strftime('%Y-%m', "+DbHelper.EXPENSE_DATE+" / 1000, 'unixepoch')
in (2015-11) format for example, after that; i will compare them in the next query if their strftime is equal or not to fill my expandablelistview. But no matter how i compare, query fires error. Below are my tries;
CAST(strftime('%Y-%m', "+DbHelper.EXPENSE_DATE+" / 1000, 'unixepoch') AS TEXT) > ?
strftime('%Y-%m', "+DbHelper.EXPENSE_DATE+" / 1000, 'unixepoch') = ?
CAST(strftime('%Y-%m', "+DbHelper.EXPENSE_DATE+" / 1000, 'unixepoch') AS TEXT) lIKE %?%
CAST(strftime('%Y', "+DbHelper.EXPENSE_DATE+" / 1000, 'unixepoch') AS INTEGER) = ? and CAST(strftime('%m', "+DbHelper.EXPENSE_DATE+" / 1000, 'unixepoch') AS INTEGER) = ?
etc...
Below my all query check
lastExpenses = new LinkedHashMap<>();
long s;
ArrayList<Expense> el = new ArrayList<>();
String query = "SELECT DISTINCT strftime('%Y-%m', "+DbHelper.EXPENSE_DATE+" / 1000, 'unixepoch') as 'Date' "+
"FROM "+DbHelper.EXPENSE_TABLE + " ";
Cursor c = db.rawQuery(query,null);
while(c.moveToNext() )
{
double count = 0;
String date = c.getString(c.getColumnIndex("Date"));
String query2 = "SELECT * FROM "+DbHelper.EXPENSE_TABLE+" " +
"WHERE strftime('%Y-%m', "+DbHelper.EXPENSE_DATE+" / 1000, 'unixepoch') = ? ";
Cursor c2 = db.rawQuery(query2,new String[]{date});
while (c2.moveToNext())
{
Expense e = new Expense();
e.setId(c.getLong(c.getColumnIndex(DbHelper.EXPENSE_ID)));
e.setAmount(c.getDouble(c.getColumnIndex(DbHelper.EXPENSE_AMOUNT)));
count += e.getAmount();
e.setDate(c.getLong(c.getColumnIndex(DbHelper.EXPENSE_DATE)));
e.setCategory(c.getString(c.getColumnIndex(DbHelper.EXPENSE_CATEGORY)));
e.setNote(c.getString(c.getColumnIndex(DbHelper.EXPENSE_SUBCATEGORY)));
e.setReasonCategory(c.getString(c.getColumnIndex(DbHelper.EXPENSE_REASONCATEGORY)));
e.setEvent(c.getString(c.getColumnIndex(DbHelper.EXPENSE_EVENT)));
el.add(e);
}
c2.close();
lastExpenses.put(date+DefaultValues.headerSeperator+count,el);
}
c.close();
return lastExpenses; ";
Cursor c2 = db.rawQuery(query2,null);
while (c2.moveToNext())
{
Expense e = new Expense();
e.setId(c.getLong(c.getColumnIndex(DbHelper.EXPENSE_ID)));
e.setAmount(c.getDouble(c.getColumnIndex(DbHelper.EXPENSE_AMOUNT)));
count += e.getAmount();
e.setDate(c.getLong(c.getColumnIndex(DbHelper.EXPENSE_DATE)));
e.setCategory(c.getString(c.getColumnIndex(DbHelper.EXPENSE_CATEGORY)));
e.setNote(c.getString(c.getColumnIndex(DbHelper.EXPENSE_SUBCATEGORY)));
e.setReasonCategory(c.getString(c.getColumnIndex(DbHelper.EXPENSE_REASONCATEGORY)));
e.setEvent(c.getString(c.getColumnIndex(DbHelper.EXPENSE_EVENT)));
el.add(e);
}
c2.close();
lastExpenses.put(date+DefaultValues.headerSeperator+count,el);
}
c.close();
return lastExpenses;
Aucun commentaire:
Enregistrer un commentaire