public List<Routine> selectRoutines() {
List<Routine> result = new ArrayList<Routine>();
SQLiteDatabase database = dbHelper.getReadableDatabase();
String query =
"SELECT ROUTINE.RTNE_ID,RTNE_NAME, SUM(STEP_SECONDS) FROM ROUTINE JOIN STEP ON STEP.RTNE_ID = ROUTINE.RTNE_ID GROUP BY ROUTINE.RTNE_ID, RTNE_NAME;";
Cursor cur = database.rawQuery(query, null);
while (cur.moveToNext()) {
Routine nextRoutine = new Routine(cur.getString(1), "");
nextRoutine.setId(cur.getInt(0));
nextRoutine.setDurationInSeconds(cur.getLong(2));
result.add(nextRoutine);
}
return result;
}
public Routine selectRoutine(int routineId) throws Exception {
final String sql =
"SELECT "
+ DbHelper.ROUTINES
+ "."
+ DbHelper.ROUTINE_ID
+ ", "
+ DbHelper.ROUTINE_NAME
+ ", "
+ DbHelper.STEP_ID
+ ", "
+ DbHelper.STEP_NAME
+ ", "
+ DbHelper.STEP_SECONDS
+ ", "
+ DbHelper.STEP_ORDINAL
+ " "
+ " FROM "
+ DbHelper.ROUTINES
+ " JOIN "
+ DbHelper.STEPS
+ " ON "
+ DbHelper.STEPS
+ "."
+ DbHelper.STEP_ROUTINE_ID
+ " = "
+ DbHelper.ROUTINES
+ "."
+ DbHelper.ROUTINE_ID
+ " "
+ " WHERE "
+ DbHelper.ROUTINES
+ "."
+ DbHelper.ROUTINE_ID
+ " = ? "
+ " ORDER BY "
+ DbHelper.STEP_ORDINAL;
Routine result = null;
SQLiteDatabase database = dbHelper.getReadableDatabase();
try {
Cursor cur = database.rawQuery(sql, new String[] {String.format("%d", routineId)});
if (!cur.moveToFirst()) {
database.close();
return result;
}
result = new Routine(cur.getString(1), "");
result.setId(cur.getInt(0));
long totalDuration = 0;
ArrayList<Step> steps = new ArrayList<Step>();
do {
Step step = new Step();
step.setId(cur.getInt(2));
step.setName(cur.getString(3));
step.setDurationSeconds(cur.getLong(4));
step.setOrdinal(cur.getInt(5));
totalDuration += step.getDurationSeconds();
steps.add(step);
} while (cur.moveToNext());
result.setDurationInSeconds(totalDuration);
result.setSteps(steps);
database.close();
} catch (Exception e) {
database.close();
throw e;
}
return result;
}