Using GROUP BY to fetch rows from SQLiteDatabase and then render them using SimpleCursorAdapter

Question

I want to display total amount of seconds that was spent each day on a project.

I want to use SQLiteDatabase to fetch rows using the query below and then use SimpleCursorAdapter to fill a ListView.

SELECT projectId, date, sum(endedAt-startedAt) as hours
FROM project_reports
WHERE startedAt >= 1270098000
AND endedAt <= 1272776399
GROUP BY date, projectId

Answer 1

I couldn't figure out how to do it in each file.

ItemsDbAdapter:

    public Cursor findAllGrouped(Date from, Date to) {
    String where = KEY_STARTEDAT + " >= " + from.getTime() + " AND " + KEY_STARTEDAT + " <= "
            + to.getTime();

    String[] columns = new String[] { KEY_ROWID, KEY_PROJECTID, KEY_DATE,
            "sum(" + KEY_ENDEDAT + "-" + KEY_STARTEDAT + ") as hours" };

    return _dbHelper.getDb().query(true, DATABASE_TABLE, columns, where, null,
            KEY_DATE + ", " + KEY_PROJECTID, null, null, null);

}

Activity:

    private void setWeek(int weekNumber) {

    Date from, to;
    Calendar calendar = new GregorianCalendar();
    calendar.set(GregorianCalendar.DAY_OF_WEEK, GregorianCalendar.MONDAY);
    calendar.set(GregorianCalendar.WEEK_OF_YEAR, weekNumber); 
    from = calendar.getTime();
    calendar.add(GregorianCalendar.DAY_OF_WEEK, 6);
    to = calendar.getTime();

    this._itemsCursor = _itemsAdapter.findAllGrouped(from, to);
    startManagingCursor(_itemsCursor);
    SimpleCursorAdapter cursorAdapter = new SimpleCursorAdapter(this,
            R.layout.submit_list_row, _itemsCursor,
            new String[] { ItemsDbAdapter.KEY_PROJECTID, ItemsDbAdapter.KEY_DATE, "hours" },
            new int[] {R.id.date, R.id.title, R.id.hours});
    cursorAdapter.setViewBinder(new MyViewBinder());

    ListView view = (ListView)findViewById(R.id.items);
    view.setAdapter(cursorAdapter);
    view.setOnItemClickListener(this);
    Log.i("List", "Displaying week " + weekNumber);
}

// since we want to use the same field twice (once for date and once for time)
class MyViewBinder implements SimpleCursorAdapter.ViewBinder
{

    @Override
    public boolean setViewValue(View view, Cursor cursor, int columnIndex) {
        TextView textField;
        view = (View)view.getParent();

        switch (columnIndex) {
            case 1: //projectid
                textField = (TextView)view.findViewById(R.id.title);
                textField.setText(_projectsAdapter.getTitle(cursor.getInt(columnIndex)));
                cursor.getColumnName(1);
                return true;
            case 2: //date
                Time time = new Time();
                time.set(cursor.getLong(columnIndex));
                String tertxc = android.text.format.DateUtils.getDayOfWeekString(time.weekDay, DateUtils.LENGTH_MEDIUM);
                textField = (TextView)view.findViewById(R.id.date);
                textField.setText(tertxc);

                return true;
            case 3: //hours
                textField = (TextView)view.findViewById(R.id.hours);
                int hours = cursor.getInt(columnIndex) / 3600000; //3600000 milliseconds
                textField.setText(hours + "");
                return true;
        }

        return true;
    }

}