def generateSpendingTrendsReport(userID, year=None):
# Default to getting current years reports
if not year:
year = datetime.now().year
# Get chart data for spending trends
spending_trends_chart = tendie_dashboard.getSpendingTrends(userID, year)
# Data structure for spending trends table
categories = []
category = {
"name": None,
"expenseMonth": 0,
"expenseCount": 0,
"amount": 0
}
spending_trends_table = {
"January": [],
"February": [],
"March": [],
"April": [],
"May": [],
"June": [],
"July": [],
"August": [],
"September": [],
"October": [],
"November": [],
"December": []
}
# Get all of the users categories first (doesn't include old categories the user deleted but are still tracked in Expenses)
categories_active = tendie_categories.getSpendCategories(userID)
# Get any categories that are in expenses but no longer exist as a selectable category for the user (because they deleted the category)
categories_inactive = tendie_categories.getSpendCategories_Inactive(userID)
# First fill using the users current categories, and then inactive categories from Expenses
for activeCategory in categories_active:
category["name"] = activeCategory["name"]
categories.append(category.copy())
for inactiveCategory in categories_inactive:
category["name"] = inactiveCategory["category"]
categories.append(category.copy())
# Place a deep copy of the categories into each month (need deep copy here because every category may have unique spend data month to month. TODO: optimize this for memory/performance later)
for month in spending_trends_table.keys():
spending_trends_table[month] = copy.deepcopy(categories)
# Get expense data for each category by month (retrieves the total amount of expenses per category by month, and the total count of expenses per category by month. Assumes there is at least 1 expense for the category)
results = db.execute(
"SELECT date_part('month', date(expensedate)) AS monthofcategoryexpense, category AS name, COUNT(category) AS count, SUM(amount) AS amount FROM expenses WHERE user_id = :usersID AND date_part('year', date(expensedate)) = :year GROUP BY date_part('month', date(expensedate)), category ORDER BY COUNT(category) DESC",
{
"usersID": userID,
"year": year
}).fetchall()
spending_trends_table_query = convertSQLToDict(results)
# Loop thru each monthly category expense from above DB query and update the data structure that holds all monthly category expenses
for categoryExpense in spending_trends_table_query:
# Get the key (month) for the data structure
monthOfExpense = calendar.month_name[int(
categoryExpense["monthofcategoryexpense"])]
# Traverse the data structure: 1) go to the dict month based on the category expense date, 2) loop thru each dict category until a match in name occurs with the expense, 3) update the dict month/amount/count properties to match the DB record
for category in spending_trends_table[monthOfExpense]:
if category["name"] == categoryExpense["name"]:
category["expenseMonth"] = categoryExpense[
"monthofcategoryexpense"]
category["expenseCount"] = categoryExpense["count"]
category["amount"] = categoryExpense["amount"]
break
else:
continue
# Calculates and stores the amount spent per category for the table (note: can't get this to work in jinja with the spending_trends_table dict because of how jinja scopes variables. TODO: rethink data-structure to combine these)
numberOfCategories = len(categories)
categoryTotal = 0
# Loops through every month per category and sums up the monthly amounts
for i in range(numberOfCategories):
for month in spending_trends_table.keys():
categoryTotal += spending_trends_table[month][i]["amount"]
categories[i]["amount"] = categoryTotal
categoryTotal = 0
# Combine both data points (chart, table, categories) into a single data structure
spendingTrendsReport = {
"chart": spending_trends_chart,
"table": spending_trends_table,
"categories": categories
}
return spendingTrendsReport
def index():
"""Show dashboard of budget/expenses"""
# User reached route via GET
if request.method == "GET":
# TODO reduce or completely remove the redundant use of javascript code in dashboard.js and reports.js
# Initialize metrics to None to render the appropriate UX if data does not exist yet for the user
expenses_year = None
expenses_month = None
expenses_week = None
expenses_last5 = None
spending_week = []
spending_month = []
# Get the users spend categories (for quick expense modal)
categories = tendie_categories.getSpendCategories(session["user_id"])
# Get the users payers (for quick expense modal)
payers = tendie_account.getPayers(session["user_id"])
# Get todays date (for quick expense modal)
date = datetime.today().strftime('%Y-%m-%d')
# Get the users income
income = tendie_account.getIncome(session["user_id"])
# Get current years total expenses for the user
expenses_year = tendie_dashboard.getTotalSpend_Year(session["user_id"])
# Get current months total expenses for the user
expenses_month = tendie_dashboard.getTotalSpend_Month(
session["user_id"])
# Get current week total expenses for the user
expenses_week = tendie_dashboard.getTotalSpend_Week(session["user_id"])
# Get last 5 expenses for the user
expenses_last5 = tendie_dashboard.getLastFiveExpenses(
session["user_id"])
# Get every budgets spent/remaining for the user
budgets = tendie_dashboard.getBudgets(session["user_id"])
# Get weekly spending for the user
weeks = tendie_dashboard.getLastFourWeekNames()
spending_week = tendie_dashboard.getWeeklySpending(
weeks, session["user_id"])
# Get monthly spending for the user (for the current year)
spending_month = tendie_dashboard.getMonthlySpending(
session["user_id"])
# Get spending trends for the user
spending_trends = tendie_dashboard.getSpendingTrends(
session["user_id"])
# Get payer spending for the user
payersChart = tendie_reports.generatePayersReport(session["user_id"])
return render_template("index.html",
categories=categories,
payers=payers,
date=date,
income=income,
expenses_year=expenses_year,
expenses_month=expenses_month,
expenses_week=expenses_week,
expenses_last5=expenses_last5,
budgets=budgets,
spending_week=spending_week,
spending_month=spending_month,
spending_trends=spending_trends,
payersChart=payersChart)
# User reached route via POST
else:
# Get all of the expenses provided from the HTML form
formData = list(request.form.items())
# Add expenses to the DB for user
expenses = tendie_expenses.addExpenses(formData, session["user_id"])
# Redirect to results page and render a summary of the submitted expenses
return render_template("expensed.html", results=expenses)