From e557fe2dc7bb95b54d78039e56f5fcb4977cc435 Mon Sep 17 00:00:00 2001 From: Mark Powers Date: Thu, 19 Nov 2020 21:38:07 -0600 Subject: Add rolling averages for categories in summary --- src/server/summary.js | 37 +++++++++++++++++++++++++++++++++++-- 1 file changed, 35 insertions(+), 2 deletions(-) (limited to 'src/server') 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 } -- cgit v1.2.3