lundi 28 décembre 2015

Android Sqlite strftime comparison not working

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