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)