def delete_order_item():
order_items_id = request.form.get("remove_item")
query = f"DELETE FROM order_items where id={int(order_items_id)};"
execute_query(query)
return redirect(url_for("admin_edit_orders"))
def delete_enrollment():
enroll_id = request.form.get("remove_item")
query = f"DELETE FROM enrollments WHERE id={int(enroll_id)};"
execute_query(query)
return redirect(request.referrer)
def register():
first_name = request.form.get("first_name")
last_name = request.form.get("last_name")
username = request.form.get("username")
password = request.form.get("password")
email = request.form.get("email")
# Verify username isn't already in use
query = f"SELECT username FROM users where username='******';"
results = execute_query(query)
response = results.fetchall()
if len(response):
return render_template('index.html')
else:
admin = False
query = f"INSERT INTO users (first_name, last_name, username, password, email_address, admin) VALUES" \
f"('{first_name}', '{last_name}', '{username}', '{password}', '{email}', {admin});"
execute_query(query)
session.permanent = True
session['logged_in'] = True
session["username"] = username
session["cart"] = {}
return render_template('index.html')
def delete_payment_info():
payment_id = request.form.get("remove_payment")
query = f"DELETE FROM payment_information where id={int(payment_id)};"
execute_query(query)
return redirect(request.referrer)
def delete_address_info():
address_id = request.form.get("remove_address")
query = f"DELETE FROM addresses where id={int(address_id)};"
execute_query(query)
return redirect(request.referrer)
def drop_enrolled_class():
class_id = request.form.get("drop_class")
print(class_id)
query = f"DELETE FROM enrollments WHERE id={int(class_id)}"
execute_query(query)
return redirect(request.referrer)
def enroll_class():
username = session["username"]
class_id = request.form["enroll"]
query = f"INSERT INTO enrollments (user_id, class_id, course_result) " \
f"VALUES ((SELECT id from users WHERE username='******'), {int(class_id)}, 'Not Taken');"
execute_query(query)
return redirect(request.referrer)
def post_edit_enrollment_page():
enroll_id = request.form.get("save_item")
course_result = request.form.get("course_result")
if enroll_id is None:
return redirect(url_for("admin_edit_enrollments"))
query = f"UPDATE enrollments SET course_result='{course_result}' WHERE id={int(enroll_id)};"
execute_query(query)
return redirect(url_for("admin_edit_enrollments"))
def post_edit_order_items_page():
order_id = request.form.get("save_item")
quantity = request.form.get("quantity")
if order_id is None:
return redirect(url_for("admin_edit_orders"))
query = f"UPDATE order_items SET quantity={int(quantity)} WHERE id={int(order_id)};"
execute_query(query)
return redirect(url_for("admin_edit_orders"))
def delete_class():
class_id = request.form.get("remove_item")
# Need to safely delete all associated data - specifically any enrollments that contain this class
query = f"DELETE FROM enrollments where class_id={int(class_id)};"
execute_query(query)
query = f"DELETE FROM classes WHERE id={int(class_id)};"
execute_query(query)
return redirect(request.referrer)
def delete_product():
item_id = request.form.get("remove_item")
# Need to safely delete all associated data - specifically any order_items that contain item
query = f"DELETE FROM order_items where item_id={int(item_id)};"
execute_query(query)
query = f"DELETE FROM items WHERE id={int(item_id)};"
execute_query(query)
return redirect(request.referrer)
def add_class():
class_name = request.form.get("class_name")
date = request.form.get("date")
instructor = request.form.get("instructor")
seats = request.form.get("available_seats")
price = request.form.get("price")
query = f"INSERT INTO classes (class_name, date, instructor, available_seats, price) " \
f"VALUES ('{class_name}', '{date}', '{instructor}', {int(seats)}, {int(price)});"
execute_query(query)
return redirect(request.referrer)
def add_payment_info():
username = session["username"]
name = request.form.get("customer_name")
card_number = request.form.get("card_number")
security_number = request.form.get("security_number")
expiration_date = request.form.get("expiration_date")
query = f"INSERT INTO payment_information (user_id, name, card_number, security_number, expiration_date) " \
f"VALUES ((SELECT id from users WHERE username='******'), '{name}', {int(card_number)}, {int(security_number)}, '{expiration_date}');"
execute_query(query)
return redirect(request.referrer)
def add_address_info():
username = session["username"]
street_address = request.form.get("street_address_1")
secondary_street_address = request.form.get("street_address_2")
city = request.form.get("city")
state = request.form.get("state")
zip_code = request.form.get("zip_code")
query = f"INSERT INTO addresses (user_id, street_address, secondary_street_address, city, state, zip_code) " \
f"VALUES ((SELECT id from users WHERE username='******'), '{street_address}', '{secondary_street_address}', '{city}', '{state}', '{zip_code}');"
execute_query(query)
return redirect(request.referrer)
def admin_edit_accounts():
query = "SELECT * FROM users;"
results = execute_query(query)
response = results.fetchall()
data = format_data(response, [
"first_name", "last_name", "username", "password", "email_address",
"admin"
])
headers = [
"First Name", "Last Name", "Username", "Password", "Email", "Admin",
"Action(s)"
]
button = ["edit", "delete", "add"]
title = "Admin Tools - Accounts"
page = "edit_accounts"
add = {
"first_name": "text",
"last_name": "text",
"username": "******",
"password": "******",
"email": "text",
"admin": "checkbox"
}
return render_template('tables.html',
data=data,
headers=headers,
button=button,
title=title,
page=page,
add=add)
def admin_edit_enrollments():
query = f"SELECT enrollments.id, enrollments.course_result, classes.class_name, classes.date, classes.instructor, " \
f"users.first_name, users.last_name FROM enrollments " \
f"INNER JOIN classes ON enrollments.class_id=classes.id " \
f"INNER JOIN users ON enrollments.user_id=users.id;"
results = execute_query(query)
response = results.fetchall()
data = format_data(response, [
"class_name", "first_name", "last_name", "date", "instructor",
"course_result"
])
headers = [
"Class Name", "First Name", "Last Name", "Class Date", "Instructor",
"Course Result", "Action(s)"
]
button = ["edit", "delete"]
title = "Admin Tools - Enrollments"
page = "edit_enrollments"
return render_template('tables.html',
data=data,
headers=headers,
button=button,
title=title,
page=page)
def admin_edit_products():
query = "SELECT items.id, items.product_name, items.price, items.stock_quantity, vendors.vendor_name " \
"FROM items LEFT OUTER JOIN vendors ON items.vendor_id=vendors.id;"
results = execute_query(query)
response = results.fetchall()
# TODO: Set vendor name to "Homemade" if NULL
data = format_data(
response, ["product_name", "vendor_name", "price", "stock_quantity"])
headers = [
"Item Name", "Vendor", "Cost", "Quantity Available", "Action(s)"
]
button = ["edit", "delete", "add"]
title = "Admin Tools - Products"
page = "edit_products"
add = {
"item_name": "text",
"vendor": "text",
"price": "number",
"quantity": "number"
}
return render_template('tables.html',
data=data,
headers=headers,
button=button,
title=title,
page=page,
add=add)
def admin_edit_orders():
query = "SELECT orders.id, orders.date, orders.fulfilled, orders.total_cost, users.first_name, users.last_name " \
"FROM orders INNER JOIN users ON orders.user_id=users.id;"
results = execute_query(query)
response = results.fetchall()
data = format_data(
response,
["id", "first_name", "last_name", "date", "total_cost", "fulfilled"])
headers = [
"Order Number", "First Name", "Last Name", "Order Date", "Total Cost",
"Fulfilled", "Action(s)"
]
button = ["view", "edit", "delete"]
title = "Admin Tools - Orders"
page = "edit_orders"
return render_template('tables.html',
data=data,
headers=headers,
button=button,
title=title,
page=page)
def admin_edit_classes():
query = "SELECT id, class_name, date, instructor, available_seats, price FROM classes;"
results = execute_query(query)
response = results.fetchall()
data = format_data(
response,
["class_name", "date", "instructor", "available_seats", "price"])
headers = [
"Class Name", "Class Date", "Instructor", "Available Seats", "Price",
"Action(s)"
]
button = ["edit", "delete", "add"]
title = "Admin Tools - Classes"
page = "edit_classes"
add = {
"class_name": "text",
"date": "date",
"instructor": "text",
"available_seats": "number",
"price": "number"
}
return render_template('tables.html',
data=data,
headers=headers,
button=button,
title=title,
page=page,
add=add)
def post_payment_info():
payment_id = request.form.get("save_item")
name = request.form.get("name")
card_number = request.form.get("card_number")
security_number = request.form.get("security_number")
date = request.form.get("expiration_date")
if payment_id is None:
return redirect(url_for("payment_info"))
query = f"UPDATE payment_information SET " \
f"name='{name}', card_number={int(card_number)}, security_number={int(security_number)}, expiration_date='{date}' " \
f"WHERE id={int(payment_id)};"
execute_query(query)
return redirect(url_for("payment_info"))
def post_edit_class_page():
class_id = request.form.get("save_item")
class_name = request.form.get("class_name")
date = request.form.get("date")
instructor = request.form.get("instructor")
seats = request.form.get("available_seats")
price = request.form.get("price")
if class_id is None:
return redirect(url_for("admin_edit_classes"))
query = f"UPDATE classes " \
f"SET class_name='{class_name}', date='{date}', instructor='{instructor}', " \
f"available_seats={int(seats)}, price={int(price)}" \
f" WHERE id={int(class_id)};"
execute_query(query)
return redirect(url_for("admin_edit_classes"))
def post_edit_orders_page():
order_id = request.form.get("save_item")
date = request.form.get("date")
total_cost = request.form.get("total_cost")
fulfilled_status = request.form.get("fulfilled")
if order_id is None:
return redirect(url_for("admin_edit_orders"))
if fulfilled_status == "1":
fulfilled = True
else:
fulfilled = False
query = f"UPDATE orders SET date='{date}', total_cost={int(total_cost)}, fulfilled={fulfilled} WHERE id={int(order_id)};"
execute_query(query)
return redirect(url_for("admin_edit_orders"))
def post_address_info():
address_id = request.form.get("save_item")
street_address = request.form.get("street_address")
secondary_street_address = request.form.get("secondary_street_address")
city = request.form.get("city")
state = request.form.get("state")
zip_code = request.form.get("zip_code")
if address_id is None:
return redirect(url_for("address_info"))
query = f"UPDATE addresses SET " \
f"street_address='{street_address}', secondary_street_address='{secondary_street_address}', " \
f"city='{city}', state='{state}', zip_code='{zip_code}' " \
f"WHERE id={int(address_id)};"
execute_query(query)
return redirect(url_for("address_info"))
def orders():
username = session["username"]
query = f"SELECT * FROM orders WHERE user_id=(SELECT id from users WHERE username='******');"
results = execute_query(query)
response = results.fetchall()
# TODO: Fix the format_data to replace boolean with checkbox somehow?
data = format_data(response, ["id", "date", "total_cost", "fulfilled"])
return render_template('order_history.html', data=data)
def payment_info():
username = session["username"]
query = f"SELECT * FROM payment_information WHERE user_id=(SELECT id from users WHERE username='******');"
results = execute_query(query)
response = results.fetchall()
data = format_data(
response,
["name", "card_number", "security_number", "expiration_date"])
return render_template('payment_info.html', data=data)
def address_info():
username = session["username"]
query = f"SELECT * FROM addresses WHERE user_id=(SELECT id from users WHERE username='******');"
results = execute_query(query)
response = results.fetchall()
data = format_data(response, [
"street_address", "secondary_street_address", "city", "state",
"zip_code"
])
return render_template('address_info.html', data=data)
def add_user_account():
first_name = request.form.get("first_name")
last_name = request.form.get("last_name")
user_name = request.form.get("username")
password = request.form.get("password")
email = request.form.get("email")
checkbox = request.form.get("admin")
if checkbox is not None:
admin = True
else:
admin = False
# TODO: Check that username isn't already in use
query = f"INSERT INTO users (first_name, last_name, username, password, email_address, admin) " \
f"VALUES ('{first_name}', '{last_name}', '{user_name}', '{password}', '{email}', {admin});"
execute_query(query)
return redirect(request.referrer)
def enrolled_classes():
username = session["username"]
query = f"SELECT enrollments.id, enrollments.course_result, classes.class_name, classes.date, classes.instructor " \
f"FROM enrollments INNER JOIN classes ON enrollments.class_id=classes.id " \
f"WHERE enrollments.user_id=(SELECT id from users WHERE username='******');"
results = execute_query(query)
response = results.fetchall()
data = format_data(response,
["class_name", "date", "instructor", "course_result"])
return render_template('enrolled_classes.html', data=data)
def add_new_product():
item_name = request.form.get("item_name")
vendor = request.form.get("vendor") or None
cost = request.form.get("price")
quantity = request.form.get("quantity")
if vendor is None:
query = f"INSERT INTO items (product_name, price, stock_quantity) " \
f"VALUES ('{item_name}', {int(cost)}, {int(quantity)});"
else:
query = f"SELECT id from vendors where vendor_name='{vendor}';"
results = execute_query(query)
vendor_id = results.fetchall()
if not vendor_id:
query = f"INSERT INTO vendors (vendor_name) VALUES ('{vendor}');"
execute_query(query)
query = f"SELECT id from vendors where vendor_name='{vendor}';"
results = execute_query(query)
vendor_id = results.fetchall()
vendor_id = vendor_id[0]["id"]
query = f"INSERT INTO items (vendor_id, product_name, price, stock_quantity) " \
f"VALUES ({int(vendor_id)}, '{item_name}', {int(cost)}, {int(quantity)});"
execute_query(query)
return redirect(request.referrer)
def load_products():
# grabbing the value from whatever the button name is for filter
filter_value = request.form.get("filter")
filter_text = request.form.get("filter_text")
# Perform an if statement similar to the one in admin_edit_products where it checks if value is None. If it is none
# then the page wasn't called by filter button and we return the select query that isn't filtering anything
if filter_value is not None:
# If the button value is not None then we know that a call was made to filter so we need to grab the filter value
# from the form and perform a query using the LIKE mysql verb
query = f"SELECT items.id, items.product_name, items.price, items.stock_quantity, vendors.vendor_name" \
f" FROM items LEFT OUTER JOIN vendors ON items.vendor_id=vendors.id WHERE items.product_name LIKE " \
f"'%{filter_text}%' OR vendors.vendor_name LIKE '%{filter_text}%';"
results = execute_query(query)
response = results.fetchall()
data = format_data(
response,
["product_name", "vendor_name", "price", "stock_quantity"])
return render_template('products.html', data=data)
# Then in either case we can format data and render products.html
query = "SELECT items.id, items.product_name, items.price, items.stock_quantity, vendors.vendor_name " \
"FROM items LEFT OUTER JOIN vendors ON items.vendor_id=vendors.id;"
results = execute_query(query)
response = results.fetchall()
# TODO: Set vendor name to "Homemade" if NULL
data = format_data(
response, ["product_name", "vendor_name", "price", "stock_quantity"])
return render_template('products.html', data=data)
