def isUniqueBudgetName(budgetName, budgetID, userID):
if budgetID == None:
# Verify the net-new created budget name is not already existing in the users existing budgets
results = db.execute(
"SELECT name FROM budgets WHERE user_id = :usersID", {
"usersID": userID
}).fetchall()
existingBudgets = convertSQLToDict(results)
else:
# Verify the updated budget name is not already existing in the users existing budgets
results = db.execute(
"SELECT name FROM budgets WHERE user_id = :usersID AND NOT id = :oldBudgetID",
{
"usersID": userID,
"oldBudgetID": budgetID
}).fetchall()
existingBudgets = convertSQLToDict(results)
# Loop through all budgets and compare names
isUniqueName = True
for budget in existingBudgets:
if budgetName.lower() == budget["name"].lower():
isUniqueName = False
break
if isUniqueName:
return True
else:
return False
def generateBudgetsReport(userID, year=None):
# Create data structure to hold users category spending data
budgetsReport = []
# Default to getting current years budgets
if not year:
year = datetime.now().year
# Get every budgets spent/remaining for the user
budgetsReport = tendie_dashboard.getBudgets(userID, year)
# Loop through the budgets and add a new key/value pair to hold expense details per budget
if budgetsReport:
for record in budgetsReport:
budgetID = tendie_budgets.getBudgetID(record["name"], userID)
results = db.execute(
"SELECT expenses.description, expenses.category, expenses.expenseDate, expenses.payer, expenses.amount FROM expenses WHERE user_id = :usersID AND date_part('year', date(expensedate)) = :year AND category IN (SELECT categories.name FROM budgetcategories INNER JOIN categories on budgetcategories.category_id = categories.id WHERE budgetcategories.budgets_id = :budgetID)",
{
"usersID": userID,
"year": year,
"budgetID": budgetID
}).fetchall()
expenseDetails = convertSQLToDict(results)
record["expenses"] = expenseDetails
return budgetsReport
def getBudgetsFromSpendCategory(categoryID, userID):
results = db.execute("SELECT budgets.id AS budgetid, budgets.name AS budgetname, categories.id AS categoryid, categories.name AS categoryname FROM budgetcategories INNER JOIN budgets on budgetcategories.budgets_id = budgets.id INNER JOIN categories on budgetcategories.category_id = categories.id WHERE budgets.user_id = :usersID AND budgetcategories.category_id = :categoryID ORDER BY budgets.name, categories.name", {
"usersID": userID, "categoryID": categoryID}).fetchall()
budgets = convertSQLToDict(results)
return budgets
def generateMonthlyReport(userID, year=None):
# Default to getting current years reports
if not year:
year = datetime.now().year
# Create data structure to hold users monthly spending data for the chart (monthly summed data)
spending_month_chart = tendie_dashboard.getMonthlySpending(userID, year)
# Get the spending data from DB for the table (individual expenses per month)
results = db.execute(
"SELECT description, category, expensedate, amount, payer FROM expenses WHERE user_id = :usersID AND date_part('year', date(expensedate)) = :year ORDER BY id ASC",
{
"usersID": userID,
"year": year
}).fetchall()
spending_month_table = convertSQLToDict(results)
# Combine both data points (chart and table) into a single data structure
monthlyReport = {
"chart": spending_month_chart,
"table": spending_month_table
}
return monthlyReport
def getWeeklySpending(weekNames, userID):
weeklySpending = []
week = {"startOfWeek": None, "endOfWeek": None, "amount": None}
# Loop through each week and store the name/amount in a dict
for name in weekNames:
week["endOfWeek"] = name['endofweek'].strftime('%b %d')
week["startOfWeek"] = name['startofweek'].strftime('%b %d')
results = db.execute(
"SELECT SUM(amount) AS amount FROM expenses WHERE user_id = :usersID AND date_part('year', date(expensedate)) = date_part('year', date(:weekName)) AND date_part('week', date(expensedate)) = date_part('week',date(:weekName))",
{"usersID": userID, "weekName": str(name["endofweek"])}).fetchall()
weekSpending = convertSQLToDict(results)
# Set the amount to 0 if there are no expenses for a given week
if weekSpending[0]["amount"] == None:
week["amount"] = 0
else:
week["amount"] = weekSpending[0]["amount"]
# Add the week to the list
weeklySpending.append(week.copy())
# Check to make sure at least 1 of the 4 weeks has expenses, otherwise set it to None so that the UI can be rendered with an appropriate message
hasExpenses = False
for record in weeklySpending:
if record["amount"] != 0:
hasExpenses = True
break
if hasExpenses is False:
weeklySpending.clear()
return weeklySpending
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 getPayers(userID):
results = db.execute(
"SELECT name FROM payers WHERE user_id = :usersID ORDER BY name ASC", {"usersID": userID}).fetchall()
payers = convertSQLToDict(results)
return payers
def getSpendingTrends(userID, year=None):
spending_trends = []
categoryTrend = {"name": None, "proportionalAmount": None,
"totalSpent": None, "totalCount": None}
# Default to getting current years spending
if not year:
year = datetime.now().year
results = db.execute("SELECT category, COUNT(category) as count, SUM(amount) as amount FROM expenses WHERE user_id = :usersID AND date_part('year', date(expensedate)) = :year GROUP BY category ORDER BY COUNT(category) DESC",
{"usersID": userID, "year": year}).fetchall()
categories = convertSQLToDict(results)
# Calculate the total amount spent
totalSpent = 0
for categoryExpense in categories:
totalSpent += categoryExpense["amount"]
for category in categories:
# Do not include category in chart if it's spending accounts for less than 1% of the overall spending
proportionalAmount = round((category["amount"] / totalSpent) * 100)
if (proportionalAmount < 1):
continue
else:
categoryTrend["name"] = category["category"]
categoryTrend["proportionalAmount"] = proportionalAmount
categoryTrend["totalSpent"] = category["amount"]
categoryTrend["totalCount"] = category["count"]
spending_trends.append(categoryTrend.copy())
return spending_trends
def getHistory(userID):
results = db.execute("SELECT description, category, expenseDate AS date, payer, amount, submitTime FROM expenses WHERE user_id = :usersID ORDER BY submitTime ASC",
{"usersID": userID}).fetchall()
history = convertSQLToDict(results)
return history
def getBudgetByID(budgetID, userID):
results = db.execute(
"SELECT name, amount, year, id FROM budgets WHERE user_id = :usersID AND id = :budgetID", {"usersID": userID, "budgetID": budgetID}).fetchall()
budget = convertSQLToDict(results)
return budget[0]
def getLastFourWeekNames():
# Query note: looks back 3 weeks from the current week and *thru* the current week to give a total of 4 weeks of start/end dates
results = db.execute("SELECT date_trunc('week', CURRENT_DATE)::date AS startofweek, (date_trunc('week', CURRENT_DATE) + interval '6 day')::date AS endofweek UNION SELECT date_trunc('week', CURRENT_DATE - interval '1 week')::date AS startofweek, (date_trunc('week', CURRENT_DATE - interval '1 week') + interval '6 day')::date AS endofweek UNION SELECT date_trunc('week', CURRENT_DATE - interval '2 week')::date AS startofweek, (date_trunc('week', CURRENT_DATE - interval '2 week') + interval '6 day')::date AS endofweek UNION SELECT date_trunc('week', CURRENT_DATE - interval '3 week')::date AS startofweek, (date_trunc('week', CURRENT_DATE - interval '3 week') + interval '6 day')::date AS endofweek ORDER BY startofweek ASC").fetchall()
weekNames = convertSQLToDict(results)
return weekNames
def getTotalSpend_Month(userID):
results = db.execute(
"SELECT SUM(amount) AS expenses_month FROM expenses WHERE user_id = :usersID AND date_part('year', date(expensedate)) = date_part('year', CURRENT_DATE) AND date_part('month', date(expensedate)) = date_part('month', CURRENT_DATE)",
{"usersID": userID}).fetchall()
totalSpendMonth = convertSQLToDict(results)
return totalSpendMonth[0]['expenses_month']
def getSpendCategories(userID):
results = db.execute(
"SELECT categories.name FROM usercategories INNER JOIN categories ON usercategories.category_id = categories.id WHERE usercategories.user_id = :usersID",
{"usersID": userID}).fetchall()
categories = convertSQLToDict(results)
return categories
def getSpendCategories_Inactive(userID):
results = db.execute(
"SELECT category FROM expenses WHERE user_id = :usersID AND category NOT IN(SELECT categories.name FROM usercategories INNER JOIN categories ON categories.id = usercategories.category_id WHERE user_id = :usersID) GROUP BY category",
{"usersID": userID}).fetchall()
categories = convertSQLToDict(results)
return categories
def getTotalSpend_Week(userID):
# Query note: Day 0 of a week == Sunday. This query grabs expenses between the *current* weeks Monday and Sunday.
results = db.execute(
"SELECT SUM(amount) AS expenses_week FROM expenses WHERE user_id = :usersID AND date_part('year', date(expensedate)) = date_part('year', CURRENT_DATE) AND date_part('week', date(expensedate)) = date_part('week', CURRENT_DATE)",
{"usersID": userID}).fetchall()
totalSpendWeek = convertSQLToDict(results)
return totalSpendWeek[0]['expenses_week']
def getLastFiveExpenses(userID):
results = db.execute(
"SELECT description, category, expenseDate, payer, amount FROM expenses WHERE user_id = :usersID ORDER BY id DESC LIMIT 5", {"usersID": userID}).fetchall()
lastFiveExpenses = convertSQLToDict(results)
if lastFiveExpenses:
return lastFiveExpenses
else:
return None
def generatePayersReport(userID, year=None):
# Default to getting current years reports
if not year:
year = datetime.now().year
# First get all of the payers from expenses table (this may include payers that don't exist anymore for the user (i.e. deleted the payer and didn't update expense records))
results_payers = db.execute(
"SELECT payer AS name, SUM(amount) AS amount FROM expenses WHERE user_id = :usersID AND date_part('year', date(expensedate)) = :year GROUP BY payer ORDER BY amount DESC",
{
"usersID": userID,
"year": year
}).fetchall()
payers = convertSQLToDict(results_payers)
# Now get any payers the user has in their account but haven't expensed anything
results_nonExpensePayers = db.execute(
"SELECT name FROM payers WHERE user_id = :usersID AND name NOT IN (SELECT payer FROM expenses WHERE expenses.user_id = :usersID AND date_part('year', date(expensedate)) = :year)",
{
"usersID": userID,
"year": year
}).fetchall()
nonExpensePayers = convertSQLToDict(results_nonExpensePayers)
# Add the non-expense payers to the payers data structure and set their amounts to 0
for payer in nonExpensePayers:
newPayer = {"name": payer["name"], "amount": 0}
payers.append(newPayer)
# Calculate the total paid for all payers combined
totalPaid = 0
for payer in payers:
totalPaid = totalPaid + payer["amount"]
# Calculate the % paid per payer and add to the data structure
if totalPaid != 0:
for payer in payers:
payer["percentAmount"] = round((payer["amount"] / totalPaid) * 100)
return payers
else:
return None
def getBudgets(userID):
results = db.execute(
"SELECT id, name, amount FROM budgets WHERE user_id = :usersID ORDER BY name ASC",
{
"usersID": userID
}).fetchall()
budgets = convertSQLToDict(results)
if budgets:
return budgets
else:
return None
def getBudgets(userID, year=None):
budgets = []
budget = {"name": None, "amount": 0, "spent": 0, "remaining": 0}
# Default to getting current years budgets
if not year:
year = datetime.now().year
budgets_query = tendie_budgets.getBudgets(userID)
# Build a budget dict to return
if budgets_query and year in budgets_query:
for record in budgets_query[year]:
budgetID = record["id"]
budget["name"] = record["name"]
budget["amount"] = record["amount"]
# Query the DB for the budgets total spent amount (calculated as the sum of expenses with categories that match the categories selected for the individual budget)
results = db.execute(
"SELECT SUM(amount) AS spent FROM expenses WHERE user_id = :usersID AND date_part('year', date(expensedate)) = :year AND category IN (SELECT categories.name FROM budgetcategories INNER JOIN categories on budgetcategories.category_id = categories.id WHERE budgetcategories.budgets_id = :budgetID)",
{
"usersID": userID,
"year": year,
"budgetID": budgetID
}).fetchall()
budget_TotalSpent = convertSQLToDict(results)
if (budget_TotalSpent[0]["spent"] == None):
budget["spent"] = 0
else:
budget["spent"] = budget_TotalSpent[0]["spent"]
# Set the remaining amount to 0 if the user has spent more than they budgeted for so that the charts don't look crazy
if (budget["spent"] > budget["amount"]):
budget["remaining"] = 0
else:
budget["remaining"] = budget["amount"] - budget["spent"]
# Add the budget to the list
budgets.append(budget.copy())
return budgets
# Return None if no budget was found
else:
return None
def getBudgets(userID):
results = db.execute(
"SELECT id, name, year, amount FROM budgets WHERE user_id = :usersID ORDER BY name ASC", {"usersID": userID}).fetchall()
budgets_query = convertSQLToDict(results)
if budgets_query:
# Create a dict with budget year as key and empty list as value which will store all budgets for that year
budgets = {budget['year']: [] for budget in budgets_query}
# Update the dict by inserting budget info as values
for budget in budgets_query:
budgets[budget['year']].append(
{'amount': budget['amount'], 'id': budget['id'], 'name': budget['name']})
return budgets
else:
return None
def data_change():
# For making sure that request was made by AJAX
request_xhr_key = request.headers.get("X-Requested-With")
if not request_xhr_key or request_xhr_key != "XMLHttpRequest":
return
# Responses for different AJAXs
data = request.get_json()
# Getting a user id
user_id = session["user_id"]
db = engine.connect()
if data.get("data") == "add":
adding_cash = data.get("value")
db.execute(text(
"UPDATE users SET cash = cash + :cash, all_cash = all_cash + :cash WHERE id = :user_id"
),
cash=adding_cash,
user_id=user_id)
return jsonify({'action': 'add', 'cash': adding_cash})
elif data.get("data") == "withdraw":
withdraw_cash = data.get("value")
db.execute(text(
"UPDATE users SET cash = cash - :cash, all_cash = all_cash - :cash WHERE id = :user_id"
),
cash=withdraw_cash,
user_id=user_id)
return jsonify({'action': 'withdraw', 'cash': withdraw_cash})
else:
old_password = data.get("old_password")
new_password = data.get("new_password")
user_data_result = db.execute(
text("SELECT * FROM users WHERE id = :user_id"), user_id=user_id)
row = convertSQLToDict(user_data_result)[0]
# Ensure a password is correct
if not check_password_hash(row["hashed"], old_password):
return jsonify({"correct": False})
else:
db.execute(
text("UPDATE users SET hashed = :hashed WHERE id = :user_id"),
hashed=generate_password_hash(new_password),
user_id=user_id)
return jsonify({"correct": True})
def getMonthlySpending(userID):
spending_month = []
month = {"name": None, "amount": None}
# Query note: pulls data for months of the *current* calendar year
results = db.execute(
"SELECT date_part('month', date(expensedate)) AS month, SUM(amount) AS amount FROM expenses WHERE user_id = :usersID AND date(expensedate) > date_trunc('month',(CURRENT_DATE - interval '11 months')) - interval '1 day' GROUP BY date_part('month', date(expensedate)) ORDER BY month",
{
"usersID": userID
}).fetchall()
spending_month_query = convertSQLToDict(results)
for record in spending_month_query:
month["name"] = calendar.month_abbr[int(record["month"])]
month["amount"] = record["amount"]
spending_month.append(month.copy())
return spending_month
def generateMonthlyReport(userID):
# Create data structure to hold users monthly spending data for the chart (monthly summed data)
spending_month_chart = tendie_dashboard.getMonthlySpending(userID)
# Get the spending data from DB for the table (individual expenses per month)
results = db.execute(
"SELECT description, category, expensedate, amount, payer FROM expenses WHERE user_id = :usersID AND date(expensedate) > date_trunc('month',(CURRENT_DATE - interval '11 months')) - interval '1 day' ORDER BY submitTime ASC",
{
"usersID": userID
}).fetchall()
spending_month_table = convertSQLToDict(results)
# Combine both data points (chart and table) into a single data structure
monthlyReport = {
"chart": spending_month_chart,
"table": spending_month_table
}
return monthlyReport
def getMonthlySpending(userID, year=None):
spending_month = []
month = {"name": None, "amount": None}
# Default to getting current years spending
if not year:
year = datetime.now().year
results = db.execute(
"SELECT date_part('month', date(expensedate)) AS month, SUM(amount) AS amount FROM expenses WHERE user_id = :usersID AND date_part('year', date(expensedate)) = :year GROUP BY date_part('month', date(expensedate)) ORDER BY month",
{"usersID": userID, "year": year}).fetchall()
spending_month_query = convertSQLToDict(results)
for record in spending_month_query:
month["name"] = calendar.month_abbr[int(record["month"])]
month["amount"] = record["amount"]
spending_month.append(month.copy())
return spending_month
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 login():
"""Log user in"""
# Forget any user_id
session.clear()
if request.method == "GET":
return render_template("login.html")
else:
# This route with POST method is also used by AJAX to validate user data without loading
# a new page after it
# For making sure that request was made by AJAX
request_xhr_key = request.headers.get("X-Requested-With")
username = request.form.get("username")
password = request.form.get("password")
# In case if a bad guy disabled JS and submitted the form
if not request_xhr_key or request_xhr_key != "XMLHttpRequest":
# Ensure username is submitted and valid
if not username or username.strip(
) == "" or len(username) > 127 or re.search(
EMOJI_PATTERN, username) or re.search(
space_regex, username):
return apology("Please provide a valid username")
# Ensure password was submitted and valid as well
elif not password or password.strip() == "" or not re.search(
password_regex, password) or re.search(
EMOJI_PATTERN, password):
return apology("Please provide a valid password")
# Query database for username
db = engine.connect()
result = db.execute(
text("SELECT * FROM users WHERE username = :username"),
username=username)
rows = convertSQLToDict(result)
# Ensure username exists
if len(rows) != 1:
if request_xhr_key and request_xhr_key == "XMLHttpRequest":
return jsonify({
"validated": False,
"username": "******"
})
else:
return apology("Wrong username")
# Ensure a password is correct
elif not check_password_hash(rows[0]["hashed"], password):
if request_xhr_key and request_xhr_key == "XMLHttpRequest":
return jsonify({
"validated": False,
"password": "******"
})
else:
return apology("Wrong password")
# Remember which user has logged in
session["user_id"] = rows[0]["id"]
# Return a respective answer for AJAX or make redirection for disabled JS
if request_xhr_key and request_xhr_key == "XMLHttpRequest":
return jsonify({"validated": True})
else:
# Redirect user to home page
return redirect("/")
def shop():
# Query database for username
db = engine.connect()
result = db.execute(text("SELECT * FROM items ORDER BY id ASC"))
rows = convertSQLToDict(result)
return render_template("shop.html", rows=rows)
def profile():
# Getting a user id
user_id = session["user_id"]
db = engine.connect()
if request.method == "GET":
# Query database for user data and items
item_results = db.execute(text("SELECT * FROM items ORDER BY id ASC"))
rows = convertSQLToDict(item_results)
user_result = db.execute(text(
"SELECT username, email, country, age, transport, gender, cash, all_cash FROM users WHERE id = :user_id"
),
user_id=user_id)
user_data = convertSQLToDict(user_result)[0]
return render_template("profile.html", rows=rows, user_data=user_data)
else:
# For making sure that request was made by AJAX
request_xhr_key = request.headers.get("X-Requested-With")
if not request_xhr_key or request_xhr_key != "XMLHttpRequest":
return
# Responses for different AJAXs
data = request.get_json()
if data.get("account"):
purchases_result = db.execute(text(
"SELECT name, number FROM purchases JOIN items ON purchases.item_id = items.id WHERE purchases.user_id = :user_id"
),
user_id=user_id)
user_purchases = convertSQLToDict(purchases_result)
return jsonify(user_purchases)
elif data.get("history"):
history_result = db.execute(text(
"SELECT name, price, number, date FROM history LEFT JOIN items ON history.item_id = items.id WHERE history.user_id = :user_id"
),
user_id=user_id)
history = convertSQLToDict(history_result)
return jsonify(history)
else:
total = data.get("total")
picked_items = data.get("items")
cancelled_items = []
# Query database for items
item_results = db.execute(
text("SELECT * FROM items ORDER BY id ASC"))
rows = convertSQLToDict(item_results)
for item_id in picked_items:
db_item_id = int(item_id) + 1
left_items = rows[int(
item_id)]["current_number"] - picked_items[item_id]
if left_items >= 0:
# Update a number of item left in the shop
db.execute(text(
"UPDATE items SET current_number = :number WHERE id = :item_id"
),
number=left_items,
item_id=db_item_id)
# Check if a user has alredy bought such item before
bought_item = db.execute(text(
"SELECT * FROM purchases WHERE user_id = :user_id AND item_id = :item_id"
),
user_id=user_id,
item_id=db_item_id).fetchall()
if not bought_item:
# If a user has not bought such item yet, then add it to his purchases
db.execute(text(
"INSERT INTO purchases (user_id, item_id, number) VALUES (:user_id, :item_id, :number)"
),
user_id=user_id,
item_id=db_item_id,
number=picked_items[item_id])
else:
# Else update the number of this item in purchases
db.execute(text(
"UPDATE purchases SET number = number + :number WHERE user_id = :user_id AND item_id = :item_id"
),
number=picked_items[item_id],
user_id=user_id,
item_id=db_item_id)
# Insert into history this purchase
db.execute(text(
"INSERT INTO history (user_id, item_id, number) VALUES (:user_id, :item_id, :number)"
),
user_id=user_id,
item_id=db_item_id,
number=picked_items[item_id])
else:
cancelled_items.append(int(item_id))
total -= rows[int(
item_id)]["price"] * picked_items[item_id]
if total != 0:
db.execute(text(
"UPDATE users SET cash = cash - :cash WHERE id = :user_id"
),
cash=total,
user_id=user_id)
if cancelled_items:
return jsonify({
"bought": False,
"total": total,
"cancelledItems": cancelled_items,
"rows": rows
})
else:
return jsonify({"bought": True})
def getSpendCategoryLibrary():
results = db.execute("SELECT id, name FROM categories").fetchall()
convertSQLToDict(results)
return categories
