Add line graph to statistics screen

2 files changed, 38 insertions, 0 deletions

diff --git a/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java b/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java
index 5ea3f1e14..11ff813a9 100644
--- a/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java
+++ b/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java
@@ -771,6 +771,33 @@ public final class DBReader {
         }
     }
 
+    public static class MonthlyStatisticsItem {
+        public int year = 0;
+        public int month = 0;
+        public long timePlayed = 0;
+    }
+
+    @NonNull
+    public static List<MonthlyStatisticsItem> getMonthlyTimeStatistics() {
+        List<MonthlyStatisticsItem> months = new ArrayList<>();
+        PodDBAdapter adapter = PodDBAdapter.getInstance();
+        adapter.open();
+        try (Cursor cursor = adapter.getMonthlyStatisticsCursor()) {
+            int indexMonth = cursor.getColumnIndexOrThrow("month");
+            int indexYear = cursor.getColumnIndexOrThrow("year");
+            int indexTotalDuration = cursor.getColumnIndexOrThrow("total_duration");
+            while (cursor.moveToNext()) {
+                MonthlyStatisticsItem item = new MonthlyStatisticsItem();
+                item.month = Integer.parseInt(cursor.getString(indexMonth));
+                item.year = Integer.parseInt(cursor.getString(indexYear));
+                item.timePlayed = cursor.getLong(indexTotalDuration);
+                months.add(item);
+            }
+        }
+        adapter.close();
+        return months;
+    }
+
     public static class StatisticsResult {
         public List<StatisticsItem> feedTime = new ArrayList<>();
         public long oldestDate = System.currentTimeMillis();
diff --git a/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java b/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java
index 43d9c7f11..ea4617f16 100644
--- a/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java
+++ b/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java
@@ -1133,6 +1133,17 @@ public class PodDBAdapter {
         return db.rawQuery(query, null);
     }
 
+    public final Cursor getMonthlyStatisticsCursor() {
+        final String query = "SELECT SUM(" + KEY_PLAYED_DURATION + ") AS total_duration"
+                + ", strftime('%m', datetime(" + KEY_LAST_PLAYED_TIME + "/1000, 'unixepoch')) AS month"
+                + ", strftime('%Y', datetime(" + KEY_LAST_PLAYED_TIME + "/1000, 'unixepoch')) AS year"
+                + " FROM " + TABLE_NAME_FEED_MEDIA
+                + " WHERE " + KEY_LAST_PLAYED_TIME + " > 0 AND " + KEY_PLAYED_DURATION + " > 0"
+                + " GROUP BY year, month"
+                + " ORDER BY year, month";
+        return db.rawQuery(query, null);
+    }
+
     public int getQueueSize() {
         final String query = String.format("SELECT COUNT(%s) FROM %s", KEY_ID, TABLE_NAME_QUEUE);
         Cursor c = db.rawQuery(query, null);