aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--src/server/summary.js37
-rw-r--r--src/templates/summary.html20
2 files changed, 51 insertions, 6 deletions
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>