My needs outpace my abilities. This code (mostly) works, but it is really ugly, doesn't always add correctly, and is in desperate need of some help refactoring. I can't wrap my head around what needs done with it.
I have a SQLite table WorkLog
which contains rows of int
, long
, long
, long
, and long
. They are _id
, unused
, dayStartTimestamp
, activityStartTime
, and activityStopTime
. Timestamps are stored as epoch time long
s. It is possible to have multiple activities on the same day. I am trying to calculate the total time working in a time period defined by lookBackHours
.
Time should be the difference between dayStartTime
and activityStopTime
+ 15 minutes for each day. Since there can be many rows with the same dayStartTime
, I want to discard entries with duplicate dayStartTimes
, only keeping the row with the longest time between activityStartTime
and activityStopTime
. I am assuming rows in the db/cursor are sorted by dayStartTime
and activityStopTime
.
In the code below, activityStartTime
is represented by TrackerContract.WorkLog.BLOCK_OUT
and activityStopTime
is rep by TrackerContract.WorkLog.BLOCK_IN
. currentTimeMilliMinutes()
can be assumed to be the same as System.currentTimeMillis()
, and unrelated to the problem of kludgey, buggy code. It simply takes a System.currentTimeMillis()
call, constructs a Calendar
, then returns a millisecond time constructed from the calendar's Year, Month, Date, Hour, and Minute (stripping seconds and milliseconds from the time).
Here is the basic logic:
This method searches a db for entries matching the criteria "anything that ended in
lookbackHours
+15 minutes, or more recently". It puts them into a List of long[2] arrays, storing thedayStartTime
andactivityStopTime
in position 0 and 1 in the array.The next loop goes through and attempts to throw away a list entry if the next one has the same
dayStartTime
and a higher (more recent)activityStopTime
.The final loop takes the surviving entries and sums the difference in [0] and [1] in millis:
public long timeInHours(long lookbackHours) {
//returns total time in past hours up to minute
long sumTotal = 0;
//search DB for activityStopTime lookBackHours + 15 minutes
long timeStamp = currentTimeMilliMinutes() - ((ONE_MINUTE*60L*lookbackHours) + (ONE_MINUTE*15L));
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor result = db.rawQuery("SELECT * FROM WorkLog WHERE " +
TrackerContract.WorkLog.COLUMN_BLOCK_IN + ">=" + timeStamp, null);
result.moveToFirst();
//define paramaters for loops below
int fdpStartIndex = result.getColumnIndex(TrackerContract.WorkLog.COLUMN_FDP_BEGIN);
int blockInIndex = result.getColumnIndex(TrackerContract.WorkLog.COLUMN_BLOCK_IN);
//insert contents of cursor into list of arrays
List<long[]> list = new ArrayList<long[]>();
while (!result.isAfterLast()) {
long[] temp = new long[2];
temp[0] = result.getLong(fdpStartIndex);
temp[1] = result.getLong(blockInIndex);
list.add(temp);
result.moveToNext();
}
//process list of arrays, if current and next start times are the same, and next end time is bigger, drop current row
//multiple entries may have same start time, but multiple end times
//trying to remove all but the longest period per start time
int length = list.size();
Calendar c1 = new GregorianCalendar();
Calendar c2 = new GregorianCalendar();
for (int i = 0; i < length; i++) {
if ((i+1) >= length) {
} else {
c1.setTimeInMillis(list.get(i)[0]);
c2.setTimeInMillis(list.get(i+1)[0]);
if (c1.get(Calendar.MINUTE) == c2.get(Calendar.MINUTE) &&
c1.get(Calendar.HOUR_OF_DAY) == c2.get(Calendar.HOUR_OF_DAY) &&
c1.get(Calendar.DAY_OF_YEAR) == c2.get(Calendar.DAY_OF_YEAR) &&
c1.get(Calendar.YEAR) == c2.get(Calendar.YEAR)) {
if (list.get(i)[1] < list.get(i+1)[1]) {
list.remove(i);
length--;
}
}
}
}
//for each remaining entry, find total duration in millis from start to end, add 15 minutes, add to total millis.
int size = list.size();
for (int i = 0; i < size; i++) {
sumTotal += list.get(i)[1] - list.get(i)[0] + (ONE_MINUTE*15L);
}
return sumTotal;
}
I'm not explicitly asking for someone to rewrite this, but just pointing out the egregious errors and suggesting ways to make it more manageable/maintainable.