def getUpdatableBudget(budget, userID):
# Get the users library of spend categories
categories = tendie_categories.getSpendCategories(userID)
# Get the budget's spend categories and % amount for each category
results = db.execute("SELECT DISTINCT categories.name, budgetCategories.amount FROM budgetCategories INNER JOIN categories ON budgetCategories.category_id = categories.id INNER JOIN budgets ON budgetCategories.budgets_id = budgets.id WHERE budgets.id = :budgetsID",
{"budgetsID": budget["id"]}).fetchall()
budgetCategories = convertSQLToDict(results)
# Add 'categories' as a new key/value pair to the existing budget dict
budget["categories"] = []
# Populate the categories by looping through and adding all their categories
for category in categories:
for budgetCategory in budgetCategories:
# Mark the category as checked/True if it exists in the budget that the user wants to update
if category["name"] == budgetCategory["name"]:
# Convert the percentage (decimal) into a whole integer to be consistent with UX
amount = round(budgetCategory["amount"] * 100)
budget["categories"].append(
{"name": category["name"], "amount": amount, "checked": True})
break
else:
budget["categories"].append(
{"name": category["name"], "amount": None, "checked": False})
return budget
def addexpenses():
"""Add new expense(s)"""
# User reached route via POST
if request.method == "POST":
# 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)
# User reached route via GET
else:
# Get the users spend categories
categories = tendie_categories.getSpendCategories(session["user_id"])
# Get the users payers
payers = tendie_account.getPayers(session["user_id"])
# Render expense page
date = datetime.today().strftime('%Y-%m-%d')
return render_template("addexpenses.html",
categories=categories,
date=date,
payers=payers)
def createbudget():
"""Create a budget"""
# User reached route via POST
if request.method == "POST":
# Make sure user has no more than 20 budgets (note: 20 is an arbitrary value)
budgets = tendie_budgets.getBudgets(session["user_id"])
if budgets:
budgetCount = 0
for year in budgets:
budgetCount += len(budgets[year])
if budgetCount >= 20:
return apology("You've reached the max amount of budgets'")
# Get all of the budget info provided from the HTML form
formData = list(request.form.items())
# Remove CSRF field from form data before processing
formData.pop(0)
# Generate data structure to hold budget info from form
budgetDict = tendie_budgets.generateBudgetFromForm(formData)
# Render error message if budget name or categories contained invalid data
if "apology" in budgetDict:
return apology(budgetDict["apology"])
else:
# Add budget to DB for user
budget = tendie_budgets.createBudget(budgetDict,
session["user_id"])
# Render error message if budget name is a duplicate of another budget the user has
if "apology" in budget:
return apology(budget["apology"])
else:
return render_template("budgetcreated.html", results=budget)
else:
# Get the users income
income = tendie_account.getIncome(session["user_id"])
# Get the users total budgeted amount
budgeted = tendie_budgets.getTotalBudgetedByYear(session["user_id"])
# Get the users spend categories
categories = tendie_categories.getSpendCategories(session["user_id"])
return render_template("createbudget.html",
income=income,
budgeted=budgeted,
categories=categories)
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 categories():
"""Manage spending categories"""
# User reached route via POST
if request.method == "POST":
# Initialize user's actions
userHasSelected_newCategory = False
userHasSelected_renameCategory = False
userHasSelected_deleteCategory = False
# Initialize user alerts
alert_newCategory = None
alert_renameCategory = None
alert_deleteCategory = None
# Determine what action was selected by the user (button/form trick from: https://stackoverflow.com/questions/26217779/how-to-get-the-name-of-a-submitted-form-in-flask)
if "btnCreateCategory" in request.form:
userHasSelected_newCategory = True
elif "btnRenameCategory" in request.form:
userHasSelected_renameCategory = True
elif "btnDeleteCategory" in request.form:
userHasSelected_deleteCategory = True
else:
return apology("Doh! Spend Categories is drunk. Try again!")
# Get new category details and create a new record in the DB
if userHasSelected_newCategory:
# Get the new name provided by user
newCategoryName = request.form.get("createName").strip()
# Make sure user has no more than 30 categories (note: 30 is an arbitrary value)
categoryCount = len(
tendie_categories.getSpendCategories(session["user_id"]))
if categoryCount >= 30:
return apology("You've reached the max amount of categories")
# Check to see if the new name already exists in the database (None == does not exist)
categoryID = tendie_categories.getCategoryID(newCategoryName)
# Category exists in the database already
if categoryID:
# Make sure the user isn't trying to add a category they already have by passing in the users ID now (None == does not exists)
existingID = tendie_categories.getCategoryID(
newCategoryName, session["user_id"])
if (existingID):
return apology("You already have '" + newCategoryName +
"' category")
# Add the category to the users account
else:
tendie_categories.addCategory_User(categoryID,
session["user_id"])
# Category does not exist in the DB already - create a new category and then add it to the users account
else:
# Creates a new category in the DB
newCategoryID = tendie_categories.addCategory_DB(
newCategoryName)
# Adds the category to the users account
tendie_categories.addCategory_User(newCategoryID,
session["user_id"])
# Set the alert message for user
alert_newCategory = newCategoryName
# Get renamed category details and update records in the DB
if userHasSelected_renameCategory:
# Get the new/old names provided by user
oldCategoryName = request.form.get("oldname").strip()
newCategoryName = request.form.get("newname").strip()
# Check to see if the *old* category actually exists in the database (None == does not exist)
oldCategoryID = tendie_categories.getCategoryID(oldCategoryName)
# Old category does not exists in the database, throw error
if oldCategoryID is None:
return apology(
"The category you're trying to rename doesn't exist")
# Check to see if the *new* name already exists in the database (None == does not exist)
newCategoryID = tendie_categories.getCategoryID(newCategoryName)
# Category exists in the database already
if newCategoryID:
# Make sure the user isn't trying to rename to a category they already have by passing in the users ID now (None == does not exists)
existingID = tendie_categories.getCategoryID(
newCategoryName, session["user_id"])
if existingID:
return apology("You already have '" + newCategoryName +
"' category")
# Get the new category name from the DB (prevents string upper/lowercase inconsistencies that can result from using the users input from the form instead of the DB)
newCategoryNameFromDB = tendie_categories.getSpendCategoryName(
newCategoryID)
# Rename the category
tendie_categories.renameCategory(oldCategoryID, newCategoryID,
oldCategoryName,
newCategoryNameFromDB,
session["user_id"])
# Category does not exist in the DB already - create a new category and then add it to the users account
else:
# Creates a new category in the DB
newCategoryID = tendie_categories.addCategory_DB(
newCategoryName)
# Rename the category
tendie_categories.renameCategory(oldCategoryID, newCategoryID,
oldCategoryName,
newCategoryName,
session["user_id"])
# Set the alert message for user
alert_renameCategory = [oldCategoryName, newCategoryName]
# Get deleted category details and update records in the DB
if userHasSelected_deleteCategory:
# Get the name of the category the user wants to delete
deleteName = request.form.get("delete").strip()
# Check to see if the category actually exists in the database (None == does not exist)
categoryID = tendie_categories.getCategoryID(deleteName)
# Category does not exists in the database, throw error
if categoryID is None:
return apology(
"The category you're trying to delete doesn't exist")
# Make sure user has at least 1 category (do not allow 0 categories)
categoryCount = len(
tendie_categories.getSpendCategories(session["user_id"]))
if categoryCount <= 1:
return apology("You need to keep at least 1 spend category")
# Delete the category
tendie_categories.deleteCategory(categoryID, session["user_id"])
# Set the alert message for user
alert_deleteCategory = deleteName
# Get the users spend categories
categories = tendie_categories.getSpendCategories(session["user_id"])
return render_template("categories.html",
categories=categories,
newCategory=alert_newCategory,
renamedCategory=alert_renameCategory,
deleteCategory=alert_deleteCategory)
# User reached route via GET
else:
# Get the users spend categories
categories = tendie_categories.getSpendCategories(session["user_id"])
# Get the budgets associated with each spend category
categoryBudgets = tendie_categories.getBudgetsSpendCategories(
session["user_id"])
# Generate a single data structure for storing all categories and their associated budgets
categoriesWithBudgets = tendie_categories.generateSpendCategoriesWithBudgets(
categories, categoryBudgets)
return render_template("categories.html",
categories=categoriesWithBudgets,
newCategory=None,
renamedCategory=None,
deleteCategory=None)
def expensehistory():
"""Show history of expenses or let the user update existing expense"""
# User reached route via GET
if request.method == "GET":
# Get all of the users expense history ordered by submission time
history = tendie_expenses.getHistory(session["user_id"])
# Get the users spend categories
categories = tendie_categories.getSpendCategories(session["user_id"])
# Get the users payers (for modal)
payers = tendie_account.getPayers(session["user_id"])
return render_template("expensehistory.html",
history=history,
categories=categories,
payers=payers,
isDeleteAlert=False)
# User reached route via POST
else:
# Initialize users action
userHasSelected_deleteExpense = False
# Determine what action was selected by the user (button/form trick from: https://stackoverflow.com/questions/26217779/how-to-get-the-name-of-a-submitted-form-in-flask)
if "btnDeleteConfirm" in request.form:
userHasSelected_deleteExpense = True
elif "btnSave" in request.form:
userHasSelected_deleteExpense = False
else:
return apology("Doh! Spend Categories is drunk. Try again!")
# Get the existing expense record ID from the DB and build a data structure to store old expense details
oldExpense = tendie_expenses.getExpense(request.form,
session["user_id"])
# Make sure an existing record was found otherwise render an error message
if oldExpense["id"] == None:
return apology(
"The expense record you're trying to update doesn't exist")
# Delete the existing expense record
if userHasSelected_deleteExpense == True:
# Delete the old record from the DB
deleted = tendie_expenses.deleteExpense(oldExpense,
session["user_id"])
if not deleted:
return apology("The expense was unable to be deleted")
# Get the users expense history, spend categories, payers, and then render the history page w/ delete alert
history = tendie_expenses.getHistory(session["user_id"])
categories = tendie_categories.getSpendCategories(
session["user_id"])
payers = tendie_account.getPayers(session["user_id"])
return render_template("expensehistory.html",
history=history,
categories=categories,
payers=payers,
isDeleteAlert=True)
# Update the existing expense record
else:
# Update the old record with new details from the form
expensed = tendie_expenses.updateExpense(oldExpense, request.form,
session["user_id"])
if not expensed:
return apology("The expense was unable to be updated")
# Redirect to results page and render a summary of the updated expense
return render_template("expensed.html", results=expensed)
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)
