vSkZfUzr

1Way to group sqlite cursor results by formated date column
2private static final String CREATE_TABLE_BJJ = "CREATE TABLE IF NOT EXISTS "
3    // TODO finish this
4    + TABLE_BJJ + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
5    + BJJ_HISTORY_MOVE + " TEXT NOT NULL, " + BJJ_HISTORY_MOVECOUNT + " INTEGER NOT NULL, "
6    + BJJ_HISTORY_PERFORMEDBY + " TEXT NOT NULL, " + BJJ_HISTORY_PERFORMEDTO
7    + " TEXT NOT NULL, " + DATE + " datetime NOT NULL, " + UPLOADED
8    + " INTEGER NOT NULL DEFAULT 0);";
9	
10String query ="SELECT DISTINCT Date FROM BJJHistory";
11ArrayList<String> dateList = new ArrayList<String>();
12SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
13Cursor  cursor = database.rawQuery(query,null);
14if (cursor != null) {
15    cursor.moveToFirst();
16    if (!cursor.isAfterLast())
17    {
18         do
19         {
20              String datestr = sdf.format(new Date(cursor.getString(0))); 
21              dateList.add(datestr);
22         }
23         while (cursor.moveToNext());
24    }
25
26}
27return cursor;
28	
29Public class History {
30public String numMoves;
31public String date;
32
33public History() {
34    super();
35}
36
37public History(final String numMoves, final String date) {
38    super();
39    this.numMoves = numMoves;
40    this.date = date;
41    }
42}
43	
44final ArrayList<History> BJJHistory = new ArrayList<History>();
45for (int i = 0; dateList.size(); i++) {
46    final Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM BJJHistory WHERE Date LIKE '" + dateList(i).toString() + "%'") > 0, null); 
47    if (cursor != null) {
48        cursor.moveToFirst();
49
50        BJJHistory.add(new History(cursor.getString(0).toString(),dateList[i].toString())) 
51    }
52	
53SELECT COUNT(*), date("Date") FROM BJHistory GROUP BY date("Date")
54	
55public String[] getUniqueDates(final String tablename) {
56    final String query = "SELECT DISTINCT Date FROM " + tablename + " ORDER BY Date DESC";
57
58    final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
59    final SimpleDateFormat sourceFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
60    final Cursor cursor = database.rawQuery(query, null);
61    String datestr = null;
62
63    final Set<String> set = new LinkedHashSet<String>();
64    if (cursor != null) {
65        cursor.moveToFirst();
66        if (!cursor.isAfterLast()) {
67            do {
68                // Convert the date to YYYY-MM-DD
69                try {
70                    final Date dt = sourceFormat.parse(cursor.getString(0));
71                    datestr = sdf.format(dt);
72                } catch (final ParseException e) {
73
74                    e.printStackTrace();
75                }
76                set.add(datestr);
77
78            } while (cursor.moveToNext());
79        }
80        cursor.close();
81    }
82
83    return set.toArray(new String[0]);
84
85}
86	
87public String selectCount(final String date) {
88    String count = "";
89    final Cursor cursor = database.rawQuery("SELECT COUNT(*) FROM " + SQLiteHelper.TABLE_BJJ
90            + " WHERE Date LIKE '" + date + "%'", null);
91    if (cursor != null) {
92        cursor.moveToFirst();
93
94        count = cursor.getString(0);
95        cursor.close();
96    }
97
98    return count;
99}
100	
101String[] DateArray = datasource.getUniqueDates(SQLiteHelper.TABLE_BJJ);
102final History BJJHistoryArray[] = new History[DateArray.length];
103for (int i = 0; i < DateArray.length; i++) {
104String suffix;
105if (datasource.selectCount(DateArray[i]) == "1") {
106    suffix = " Move";
107    } else {
108    suffix = " Moves";
109}
110    BJJHistoryArray[i] = new History(datasource.selectCount(DateArray[i]) + suffix,
111                    DateArray[i]);
112
113        }
114
115        final HistoryAdapter adapter = new HistoryAdapter(this, R.layout.history_row,
116                BJJHistoryArray);
117        BJJHistory.setAdapter(adapter);