From e557fe2dc7bb95b54d78039e56f5fcb4977cc435 Mon Sep 17 00:00:00 2001
From: Mark Powers <markppowers0@gmail.com>
Date: Thu, 19 Nov 2020 21:38:07 -0600
Subject: Add rolling averages for categories in summary

---
 src/server/summary.js      | 37 +++++++++++++++++++++++++++++++++++--
 src/templates/summary.html | 20 ++++++++++++++++----
 2 files changed, 51 insertions(+), 6 deletions(-)

(limited to 'src')

diff --git a/src/server/summary.js b/src/server/summary.js
index 1235542..6cba4d9 100644
--- a/src/server/summary.js
+++ b/src/server/summary.js
@@ -117,9 +117,42 @@ async function formatSummary(database, username) {
     summary.month_avg = getBudgetAverage(summary.month)
     summary.week_avg = getBudgetAverage(summary.week)
 
+    let categories = await database.query(`select category, sum(amount) as s from transactions where username = '${username}' and category <> '' group by category`, { type: database.QueryTypes.SELECT });
+    let categories_30day = await database.query(`select category, sum(amount) as s from transactions where username = '${username}' and category <> '' and createdAt BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) and NOW() group by category`, { type: database.QueryTypes.SELECT });
+    let categories_90day = await database.query(`select category, sum(amount) as s from transactions where username = '${username}' and category <> '' and createdAt BETWEEN DATE_SUB(NOW(), INTERVAL 90 DAY) and NOW() group by category`, { type: database.QueryTypes.SELECT });
+    let categories_365day = await database.query(`select category, sum(amount) as s from transactions where username = '${username}' and category <> '' and createdAt BETWEEN DATE_SUB(NOW(), INTERVAL 365 DAY) and NOW() group by category`, { type: database.QueryTypes.SELECT });
+    summary.categories = {}
+    categories.forEach(el => {
+        summary.categories[el.category] = {category: el.category, all: el.s, d30: 0, d90: 0}
+    })
+    categories_30day.forEach(el => {
+        summary.categories[el.category].d30 = el.s
+    })
+    categories_90day.forEach(el => {
+        summary.categories[el.category].d90 = el.s
+    })
+    categories_365day.forEach(el => {
+        summary.categories[el.category].d365 = el.s
+    })
+
+    let subcategories = await database.query(`select subcategory, sum(amount) as s from transactions where username = '${username}' and subcategory <> '' group by subcategory`, { type: database.QueryTypes.SELECT });
+    let subcategories_30day = await database.query(`select subcategory, sum(amount) as s from transactions where username = '${username}' and subcategory <> '' and createdAt BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) and NOW() group by subcategory`, { type: database.QueryTypes.SELECT });
+    let subcategories_90day = await database.query(`select subcategory, sum(amount) as s from transactions where username = '${username}' and subcategory <> '' and createdAt BETWEEN DATE_SUB(NOW(), INTERVAL 90 DAY) and NOW() group by subcategory`, { type: database.QueryTypes.SELECT });
+    let subcategories_365day = await database.query(`select subcategory, sum(amount) as s from transactions where username = '${username}' and subcategory <> '' and createdAt BETWEEN DATE_SUB(NOW(), INTERVAL 365 DAY) and NOW() group by subcategory`, { type: database.QueryTypes.SELECT });
+    summary.subcategories = {}
+    subcategories.forEach(el => {
+        summary.subcategories[el.subcategory] = {subcategory: el.subcategory, all: el.s, d30: 0, d90: 0}
+    })
+    subcategories_30day.forEach(el => {
+        summary.subcategories[el.subcategory].d30 = el.s
+    })
+    subcategories_90day.forEach(el => {
+        summary.subcategories[el.subcategory].d90 = el.s
+    })
+    subcategories_365day.forEach(el => {
+        summary.subcategories[el.subcategory].d365 = el.s
+    })
 
-    summary.categories = await database.query(`select category, sum(amount) as s from transactions where username = '${username}' and category <> '' group by category`, { type: database.QueryTypes.SELECT });
-    summary.subcategories = await database.query(`select subcategory, sum(amount) as s from transactions where username = '${username}' and subcategory <> '' group by subcategory`, { type: database.QueryTypes.SELECT });
     summary.name = username
     return summary
 }
diff --git a/src/templates/summary.html b/src/templates/summary.html
index f9ae202..15d944d 100644
--- a/src/templates/summary.html
+++ b/src/templates/summary.html
@@ -99,12 +99,18 @@
                 <table class="table">
                     <tr>
                         <th>Category</th>
-                        <th>Total</th>
+                        <th>Last 30 days</th>
+                        <th>Last 90 days</th>
+                        <th>Last year</th>
+                        <th>All-time</th>
                     </tr>
                     {{#each categories}}
                     <tr>
                         <td>{{this.category}}</td>
-                        <td>{{this.s}}</td>
+                        <td>{{this.d30}}</td>
+                        <td>{{this.d90}}</td>
+                        <td>{{this.d365}}</td>
+                        <td>{{this.all}}</td>
                     </tr>
                     {{/each}}
                 </table>
@@ -114,12 +120,18 @@
                 <table class="table">
                     <tr>
                         <th>Sub-Category</th>
-                        <th>Total</th>
+                        <th>Last 30 days</th>
+                        <th>Last 90 days</th>
+                        <th>Last year</th>
+                        <th>All-time</th>
                     </tr>
                     {{#each subcategories}}
                     <tr>
                         <td>{{this.subcategory}}</td>
-                        <td>{{this.s}}</td>
+                        <td>{{this.d30}}</td>
+                        <td>{{this.d90}}</td>
+                        <td>{{this.d365}}</td>
+                        <td>{{this.all}}</td>
                     </tr>
                     {{/each}}
                 </table>
-- 
cgit v1.2.3