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)