def add_new_doctor():
if request.method == 'GET':
query = 'SELECT id, fname, lname FROM nurse;'
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template('doctor_add_new.j2', nurses=result)
elif request.method == 'POST':
print("Add new doctor")
fname = request.form['fname']
lname = request.form['lname']
phoneNumber = request.form['phoneNumber']
salary = request.form['salary']
nurseID = request.form['nurseID']
query = 'INSERT INTO doctor (fname, lname, phoneNumber, salary, nurseID) VALUES (%s,%s,%s,%s,%s);'
data = (fname, lname, phoneNumber, salary, nurseID)
db.execute_query(db_connection, query, data)
query = "SELECT id, fname, lname, phoneNumber, salary, nurseID FROM doctor;"
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template("browse_doctor.j2", doctors=result, resultText = "Doctor added.")
def update_nurse(id):
db_connection = db.connect_to_database()
if request.method == 'GET':
nurse_query = 'SELECT id, fname, lname, phoneNumber, salary FROM nurse WHERE id = %s' % (
id)
nurse_result = db.execute_query(db_connection, nurse_query).fetchone()
if nurse_result == None:
return "No such nurse found!"
return render_template('nurse_update.j2', nurse=nurse_result)
elif request.method == 'POST':
nurse_id = request.form['nurse_id']
fname = request.form['fname']
lname = request.form['lname']
phoneNumber = request.form['phoneNumber']
salary = request.form['salary']
query = "UPDATE nurse SET fname = %s, lname = %s, phoneNumber = %s, salary = %s WHERE id = %s"
data = (fname, lname, phoneNumber, salary, nurse_id)
result = db.execute_query(db_connection, query, data)
print(str(result.rowcount) + " row(s) updated")
return redirect('/browse_nurse')
def rentals():
query = "SELECT rental_ID, first_name, last_name, DATE_FORMAT(rent_date, '%%b %%e %%Y') as date, DATE_FORMAT(paid, '%%b %%e %%Y') as paid, " \
"COUNT(game_version) as rentals, COUNT(game_version) * '4.99' as cost, DATE_FORMAT(DATE_ADD(rent_date, INTERVAL 3 DAY), '%%b %%e %%Y') as due, " \
"CASE WHEN DATEDIFF(CURRENT_DATE, rent_date) <= 3 THEN '0.00' WHEN DATE_FORMAT(paid, '%%b %%e %%Y') IS NULL THEN DATEDIFF(CURRENT_DATE, DATE_ADD(rent_date, INTERVAL 3 DAY)) WHEN DATE_ADD(rent_date, INTERVAL 3 DAY) > paid " \
"THEN '0.00' ELSE DATEDIFF(paid, DATE_ADD(rent_date, INTERVAL 3 DAY)) END AS days_late, CASE WHEN DATEDIFF(CURRENT_DATE, rent_date) <= 3 THEN '0.00' WHEN COUNT(game_version) = '0' THEN '0.00' WHEN DATE_FORMAT(paid, '%%b %%e %%Y') IS NULL " \
"THEN DATEDIFF(CURRENT_DATE, DATE_ADD(rent_date, INTERVAL 3 DAY)) * 1.99 WHEN DATE_ADD(rent_date, INTERVAL 3 DAY) > paid THEN '0.00' " \
"ELSE DATEDIFF(paid, DATE_ADD(rent_date, INTERVAL 3 DAY)) * 1.99 END AS late_fee, ((COUNT(game_version) * '4.99') + (CASE WHEN DATEDIFF(CURRENT_DATE, rent_date) <= 3 THEN '0.00' WHEN COUNT(game_version) = '0' THEN '0.00' " \
"WHEN DATE_FORMAT(paid, '%%b %%e %%Y') IS NULL THEN DATEDIFF(CURRENT_DATE, DATE_ADD(rent_date, INTERVAL 3 DAY)) * 1.99 WHEN DATE_ADD(rent_date, INTERVAL 3 DAY) > paid " \
"THEN '0.00' ELSE DATEDIFF(paid, DATE_ADD(rent_date, INTERVAL 3 DAY)) * 1.99 END)) as total FROM Rentals INNER JOIN Customers ON customer = customer_ID " \
"LEFT JOIN Game_Rentals ON rental_ID = rental GROUP BY rental_ID;"
cursor = db.execute_query(db_connection=db_connection, query=query)
results = cursor.fetchall()
q2 = "SELECT game_version, rental, title, name FROM Game_Rentals INNER JOIN Console_Versions on game_version = item_ID " \
"INNER JOIN Games ON game = game_ID INNER JOIN Consoles ON console = console_ID;"
r2 = db.execute_query(db_connection=db_connection, query=q2).fetchall()
q3 = "SELECT title, name FROM Games INNER JOIN Console_Versions ON game_ID = game INNER JOIN Consoles ON console = console_ID;"
r3 = db.execute_query(db_connection=db_connection, query=q3).fetchall()
q4 = "SELECT first_name, last_name, rent_date FROM Customers INNER JOIN Rentals ON customer_ID = customer;"
r4 = db.execute_query(db_connection=db_connection, query=q4).fetchall()
q5 = "SELECT first_name, last_name FROM Customers;"
r5 = db.execute_query(db_connection=db_connection, query=q5).fetchall()
return render_template("rentals.j2", rentals=results, items=r2, g_versions=r3, people=r4, customers=r5)
def deleteEmployeeCustomer(id):
""" Delete a row in the Employees_Customers_Map table """
# Get the employee_customer based on employee_customer_id
employee_customer = get_employee_customer(id)
# Set and execute the query
query = "DELETE FROM Employees_Customers_Map WHERE employee_customer_id = " + str(
id)
cursor = db.execute_query(db_connection=db_connection, query=query)
results = cursor.fetchall()
return redirect('/assign-salesperson')
def root():
# Search Handler
if request.method == 'POST':
name = "%" + request.form['name'] + "%"
query = "SELECT * FROM Organizations WHERE name LIKE %s ORDER BY name"
cursor = db.execute_query(db_connection=db_connection, query=query, query_params=(name,))
results = cursor.fetchall()
cursor.close()
return searchResults(results)
return render_template("main.j2")
def emp_index():
if request.method == 'POST':
print('POST received.')
print()
print('SEARCH: ' + request.form['search'])
print('CATEGORY: ' + request.form['restroomSearch'])
print()
db_connection = connect_to_database()
# if request.form['search'] is empty, return all
if request.form['search'] == '':
query = "SELECT rr.restroomID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address,rr.openHour, rr.closeHour, rr.free, re.inspectedAt, re.comments, re.employeeID FROM Restrooms rr JOIN Locations l on l.locationID = rr.locationID JOIN RestroomsEmployees re on re.restroomID = rr.restroomID ORDER BY 1 asc;"
results = execute_query(db_connection, query).fetchall()
return render_template('/employee_index.html', results=results)
# otherwise, use category for WHERE clause filtering on search text
search = request.form['search']
category = request.form['restroomSearch']
if category == 'restroomID':
query = "SELECT rr.restroomID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rr.openHour, rr.closeHour, rr.free, re.inspectedAt, re.comments, re.employeeID FROM Restrooms rr JOIN Locations l on l.locationID = rr.locationID JOIN RestroomsEmployees re on re.restroomID = rr.restroomID WHERE rr.restroomID = " + search + " ORDER BY 1 asc;"
#data = (search)
results = execute_query(db_connection, query).fetchall()
return render_template('employee_index.html', results=results)
if category == 'street':
query = "SELECT rr.restroomID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rr.openHour, rr.closeHour, rr.free, re.inspectedAt, re.comments, re.employeeID FROM Restrooms rr JOIN Locations l on l.locationID = rr.locationID JOIN RestroomsEmployees re on re.restroomID = rr.restroomID WHERE l.street like '" + search + "' ORDER BY 1 asc;"
print(query)
#data = (search)
results = execute_query(db_connection, query).fetchall()
return render_template('employee_index.html', results=results)
if category == 'city':
query = "SELECT rr.restroomID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rr.openHour, rr.closeHour, rr.free, re.inspectedAt, re.comments, re.employeeID FROM Restrooms rr JOIN Locations l on l.locationID = rr.locationID JOIN RestroomsEmployees re on re.restroomID = rr.restroomID WHERE l.city like '" + search + "' ORDER BY 1 asc;"
#data = (search)
results = execute_query(db_connection, query).fetchall()
return render_template('employee_index.html', results=results)
if category == 'state':
query = "SELECT rr.restroomID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rr.openHour, rr.closeHour, rr.free, re.inspectedAt, re.comments, re.employeeID FROM Restrooms rr JOIN Locations l on l.locationID = rr.locationID JOIN RestroomsEmployees re on re.restroomID = rr.restroomID WHERE l.state like '" + search + "' ORDER BY 1 asc;"
#data = (search)
results = execute_query(db_connection, query).fetchall()
return render_template('employee_index.html', results=results)
if category == 'country':
query = "SELECT rr.restroomID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rr.openHour, rr.closeHour, rr.free, re.inspectedAt, re.comments, re.employeeID FROM Restrooms rr JOIN Locations l on l.locationID = rr.locationID JOIN RestroomsEmployees re on re.restroomID = rr.restroomID WHERE l.country like '" + search + "' ORDER BY 1 asc;"
#data = (search)
results = execute_query(db_connection, query).fetchall()
return render_template('employee_index.html', results=results)
# GET request returns all rows of Restrooms
db_connection = connect_to_database()
query = "SELECT rr.restroomID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address,rr.openHour, rr.closeHour, rr.free, re.inspectedAt, re.comments, re.employeeID FROM Restrooms rr JOIN Locations l on l.locationID = rr.locationID JOIN RestroomsEmployees re on re.restroomID = rr.restroomID ORDER BY 1 asc;"
results = execute_query(db_connection, query).fetchall()
return render_template('employee_index.html', results=results)
def add_product():
if request.method == "GET":
# Get Supplier names for display
query = "SELECT Supplier_id, Supplier_name FROM Suppliers;"
cursor = db.execute_query(db_connection, query)
supplier_data = cursor.fetchall()
supplier_names = [(supplier['Supplier_id'], supplier['Supplier_name'])
for supplier in supplier_data]
cursor.close()
return render_template("add_product.j2", supplier_names=supplier_names)
if request.method == "POST":
pname = request.form['pname']
p_sid = request.form['p_sid']
p_cost = request.form['p_cost']
p_price = request.form['p_price']
p_quan = request.form['p_quan']
query = 'INSERT INTO Products (Product_name, Supplier_id, Unit_cost, Unit_price, Quantity) VALUES (%s,%s,%s,%s,%s)'
data = (pname, p_sid, p_cost, p_price, p_quan)
cursor = db.execute_query(db_connection, query, data)
print('Added product')
cursor.close()
return redirect(url_for('products'))
def customer_like_pet_list():
db_connection = db.db_connection
# user_id = session['userID']
query = 'SELECT * FROM CustomerLikePet WHERE customerID = %d;' % (
session['userID'])
# query = "UPDATE AdminMsg SET status='approved' WHERE adminMsgID=%s;" % (msgID)
cursor = db.execute_query(db_connection, query)
likedResult = cursor.fetchall()
if likedResult:
# print(likedResult)
likedPetsIdList = [pet['petsID'] for pet in likedResult]
# print(likedPetsIdList)
query = 'SELECT * FROM Pets WHERE petsID IN (%s);' % (",".join(
str(elem) for elem in likedPetsIdList))
cursor = db.execute_query(db_connection, query)
results = cursor.fetchall()
return render_template('customer_like_pet_list.j2',
likePets=results,
base64=base64)
else:
return render_template('customer_like_pet_list_empty.j2')
def adpoter_home():
# Check is user is logged in
if 'adopter_loggedin' in session:
# List the newly added pets (4)
db_connection = db.db_connection
query = 'SELECT * FROM Pets ORDER BY date DESC LIMIT 4;'
cursor = db.execute_query(db_connection, query)
results = cursor.fetchall()
return render_template('adopter_home.j2',
userID=session['userID'],
pets=results,
base64=base64)
# User is not logged in
return render_template('adopter_login.j2')
def add_new_office():
if request.method == 'GET':
query = 'SELECT id, fname, lname FROM manager;'
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template('office_add_new.j2', managers=result)
elif request.method == 'POST':
print("Add new office")
name = request.form['name']
phoneNumber = request.form['phoneNumber']
street = request.form['street']
city = request.form['city']
state = request.form['state']
zipcode = request.form['zip']
managerID = request.form['managerID']
query = 'INSERT INTO office (name, phoneNumber, street, city, state, zip, managerID) VALUES (%s,%s,%s,%s,%s,%s,%s);'
data = (name, phoneNumber, street, city, state, zipcode, managerID)
db.execute_query(db_connection, query, data)
query = "SELECT id, name, phoneNumber, street, city, state, zip, managerID FROM office;"
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template("browse_office.j2", offices=result, resultText="Office added.")
def update_game(g_id):
if request.method == 'GET':
g_query = 'SELECT * FROM Games WHERE game_id = %s' % (g_id)
g_r = db.execute_query(db_connection=db_connection,query=g_query).fetchone()
genres = ['2D Platformer', '3D Platformer', 'Action-Adventure', 'Action RPG', 'Arcade',
'Battle Royale', 'Fighting', 'First-Person Shooter', 'Fitness', 'Japanese RPG', 'MOBA/MMORPG',
'Open World', 'Party', 'Puzzle', 'Racing', 'Sports', 'Strategy', 'Survival Horror',
'Third-Person Shooter', 'Visual Novel']
print(g_r)
if g_r == None:
return "No such game exists!"
return render_template('update_game.j2', update=g_r, genres=genres)
elif request.method == 'POST':
print("Updated game!")
game = g_id
print(game)
title = request.form['title']
print(title)
players = request.form['players']
print(players)
publisher = request.form['publisher']
print(publisher)
rating = request.form['rating']
print(rating)
if rating == "Choose...":
rating = "TBD"
online = 'online' in request.form
print(online)
genre = request.form['genre']
print(genre)
if genre == "Choose...":
genre = "Unknown"
print(request.form);
query = 'UPDATE Games SET title = %s, player_count = %s, rating = %s, online = %s, publisher = %s, genre = %s WHERE game_id = %s'
data = (title, players, rating, online, publisher, genre, game)
db.execute_query(db_connection, query, data)
return redirect(url_for('games'))
def updateCustomer(id):
""" Update a given row in the Customers table """
# Get the customer based on customer_id
customer = get_customer(id)
# Get inputs from the POST request and store as variables
if request.method == 'POST':
customer_first_name = request.form['customer_first_name']
customer_last_name = request.form['customer_last_name']
customer_street = request.form['customer_street']
customer_city = request.form['customer_city']
customer_state = request.form['customer_state']
customer_zip = request.form['customer_zip']
favorite_employee = request.form['favorite_employee']
# Set query to update a row based on the form inputs
query = "UPDATE Customers SET customer_first_name=%s, customer_last_name=%s, customer_street=%s, customer_city=%s, customer_state=%s, customer_zip=%s, favorite_employee=%s WHERE customer_id=" + str(
id)
cursor = db.execute_query(
db_connection=db_connection,
query=query,
query_params=(customer_first_name, customer_last_name,
customer_street, customer_city, customer_state,
customer_zip, favorite_employee))
results = cursor.fetchall()
return redirect('/customers')
# Get all rows for dropdown
query = "SELECT * FROM Employees ORDER BY last_name;"
cursor = db.execute_query(db_connection=db_connection, query=query)
Employees = cursor.fetchall()
cursor.close()
return render_template("updateCustomer.j2",
customer=customer,
Employees=Employees)
def update_customer(cu_id):
if request.method == 'GET':
cu_query = "SELECT * FROM Customers WHERE customer_ID = %s" % (cu_id)
cu_r = db.execute_query(db_connection=db_connection,query=cu_query).fetchone()
states = ['AL', 'AK', 'AS', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'GU', 'HI', 'ID', 'IL', 'IN',
'IA',
'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY',
'NC',
'ND', 'MP', 'OH', 'OK', 'OR', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'VI', 'WA',
'WV', 'WI', 'WY']
if cu_r == None:
return "No such customer exists!"
return render_template('update_customer.j2', update=cu_r, states=states)
elif request.method == 'POST':
print("Updated customer!")
first = request.form['f_name']
print(first)
last = request.form['l_name']
print(last)
street = request.form['street']
print(street)
city = request.form['city']
print(city)
state = request.form['state']
print(state)
zip = request.form['zip']
print(zip)
phone = request.form['phone']
print(phone)
email = request.form['email']
print(email)
bday = request.form['birthday']
print(bday)
query = 'UPDATE Customers SET first_name = %s, last_name = %s, street = %s, city = %s, state = %s, zip = %s, phone = %s, email = %s, birthday = %s WHERE customer_ID = %s'
data = (first, last, street, city, state, zip, phone, email, bday, cu_id)
db.execute_query(db_connection, query, data)
return redirect(url_for('customers'))
def add_rental():
if request.method == "GET":
print("The method is wrong. Change this in the HTML.")
query = "SELECT rental_ID, first_name, last_name, DATE_FORMAT(rent_date, '%%b %%e %%Y') as date, DATE_FORMAT(paid, '%%b %%e %%Y') as paid, " \
"COUNT(game_version) as rentals, COUNT(game_version) * '4.99' as cost, DATE_FORMAT(DATE_ADD(rent_date, INTERVAL 3 DAY), '%%b %%e %%Y') as due, " \
"CASE WHEN DATEDIFF(CURRENT_DATE, rent_date) <= 3 THEN '0.00' WHEN DATE_FORMAT(paid, '%%b %%e %%Y') IS NULL THEN DATEDIFF(CURRENT_DATE, DATE_ADD(rent_date, INTERVAL 3 DAY)) WHEN DATE_ADD(rent_date, INTERVAL 3 DAY) > paid " \
"THEN '0.00' ELSE DATEDIFF(paid, DATE_ADD(rent_date, INTERVAL 3 DAY)) END AS days_late, CASE WHEN DATEDIFF(CURRENT_DATE, rent_date) <= 3 THEN '0.00' WHEN COUNT(game_version) = '0' THEN '0.00' WHEN DATE_FORMAT(paid, '%%b %%e %%Y') IS NULL " \
"THEN DATEDIFF(CURRENT_DATE, DATE_ADD(rent_date, INTERVAL 3 DAY)) * 1.99 WHEN DATE_ADD(rent_date, INTERVAL 3 DAY) > paid THEN '0.00' " \
"ELSE DATEDIFF(paid, DATE_ADD(rent_date, INTERVAL 3 DAY)) * 1.99 END AS late_fee, ((COUNT(game_version) * '4.99') + (CASE WHEN DATEDIFF(CURRENT_DATE, rent_date) <= 3 THEN '0.00' WHEN COUNT(game_version) = '0' THEN '0.00' " \
"WHEN DATE_FORMAT(paid, '%%b %%e %%Y') IS NULL THEN DATEDIFF(CURRENT_DATE, DATE_ADD(rent_date, INTERVAL 3 DAY)) * 1.99 WHEN DATE_ADD(rent_date, INTERVAL 3 DAY) > paid " \
"THEN '0.00' ELSE DATEDIFF(paid, DATE_ADD(rent_date, INTERVAL 3 DAY)) * 1.99 END)) as total FROM Rentals INNER JOIN Customers ON customer = customer_ID " \
"LEFT JOIN Game_Rentals ON rental_ID = rental GROUP BY rental_ID;"
cursor = db.execute_query(db_connection=db_connection, query=query)
results = cursor.fetchall()
q2 = "SELECT game_version, rental, title, name FROM Game_Rentals INNER JOIN Console_Versions on game_version = item_ID " \
"INNER JOIN Games ON game = game_ID INNER JOIN Consoles ON console = console_ID;"
r2 = db.execute_query(db_connection=db_connection, query=q2).fetchall()
q3 = "SELECT title, name FROM Games INNER JOIN Console_Versions ON game_ID = game INNER JOIN Consoles ON console = console_ID;"
r3 = db.execute_query(db_connection=db_connection, query=q3).fetchall()
q4 = "SELECT first_name, last_name, rent_date FROM Customers INNER JOIN Rentals ON customer_ID = customer;"
r4 = db.execute_query(db_connection=db_connection, query=q4).fetchall()
q5 = "SELECT first_name, last_name FROM Customers;"
r5 = db.execute_query(db_connection=db_connection, query=q5).fetchall()
return render_template("rentals.j2", rentals=results, items=r2, g_versions=r3, people=r4, customers=r5)
elif request.method == "POST":
print("Adding new rental")
# pull out the title from the form to check if the game has already been added
customer = request.form['customer']
first, last = customer.split(' ', 1)
# pull out rent_date and paid
rent_date = request.form['rent_date']
paid = request.form['paid']
# make sure the customer isn't in the list first
query = "SELECT customer_id FROM Customers WHERE first_name = '" + first + "' AND last_name = '" + last + "';"
check = db.execute_query(db_connection, query).fetchall();
# in SQL/Flask, if a SELECT is returned with no values, fetchall returns () (not '' or None)
if check != ():
query = "INSERT INTO Rentals (customer, rent_date, paid) VALUES (%s,%s,%s);"
data = (check[0]['customer_id'], rent_date, paid)
db.execute_query(db_connection, query, data)
else: print("Customer does not exist.")
return redirect(url_for('rentals'))
def products():
if request.method == "GET":
query = "SELECT * FROM Products;"
cursor = db.execute_query(db_connection=db_connection, query=query)
results = cursor.fetchall()
# Get Supplier names for display
query = "SELECT Supplier_id, Supplier_name FROM Suppliers;"
cursor = db.execute_query(db_connection, query)
supplier_data = cursor.fetchall()
supplier_names = {
supplier['Supplier_id']: supplier['Supplier_name']
for supplier in supplier_data
}
cursor.close()
return render_template("products.j2",
products=results,
supplier_names=supplier_names)
if request.method == "POST":
search_term = "%" + request.form['psearch'] + "%"
query = 'SELECT * FROM Products WHERE Product_name LIKE %s'
data = (search_term, )
cursor = db.execute_query(db_connection, query, data)
results = cursor.fetchall()
print('Searching Products table for {}'.format(search_term))
# Get Supplier names for display
query = "SELECT Supplier_id, Supplier_name FROM Suppliers;"
cursor = db.execute_query(db_connection, query)
supplier_data = cursor.fetchall()
supplier_names = {
supplier['Supplier_id']: supplier['Supplier_name']
for supplier in supplier_data
}
cursor.close()
return render_template("products.j2",
products=results,
supplier_names=supplier_names)
def add_customer():
if request.method == "GET":
query = "SELECT customer_ID, first_name, last_name, street, city, state, zip, phone, email, DATE_FORMAT(birthday, '%%b %%e %%Y') as birthday FROM Customers;"
cursor = db.execute_query(db_connection=db_connection, query=query)
results = cursor.fetchall()
q2 = "SELECT customer, rental, game_version, title, name, DATE_FORMAT(rent_date, '%%b %%e %%Y') AS rent_date, DATE_FORMAT(DATE_ADD(rent_date, INTERVAL 3 DAY), '%%b %%e %%Y') AS due, " \
"DATE_FORMAT(paid, '%%b %%e %%Y') AS paid, first_name, last_name FROM Games INNER JOIN Console_Versions ON game_ID = game " \
"INNER JOIN Consoles ON console = console_ID INNER JOIN Game_Rentals ON item_ID = game_version " \
"INNER JOIN Rentals ON rental = rental_ID INNER JOIN Customers ON customer = customer_ID;"
r2 = db.execute_query(db_connection=db_connection, query=q2).fetchall()
states = ['AL', 'AK', 'AS', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'GU', 'HI', 'ID', 'IL', 'IN',
'IA',
'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY',
'NC',
'ND', 'MP', 'OH', 'OK', 'OR', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'VI', 'WA',
'WV', 'WI', 'WY']
gq = 'SELECT title FROM Games;'
g_r = db.execute_query(db_connection=db_connection, query=gq).fetchall()
cq = 'SELECT name FROM Consoles;'
c_r = db.execute_query(db_connection=db_connection, query=cq).fetchall()
return render_template("customers.j2", customers=results, states=states, items=r2, games=g_r, consoles=c_r)
elif request.method == "POST":
print("Adding new customer")
# pull out attributes
first_name = request.form['first_name']
if " " in first_name:
first_name = first_name.replace(" ", "-")
last_name = request.form['last_name']
if " " in last_name:
last_name = last_name.replace(" ", "-")
street = request.form['street']
city = request.form['city']
state = request.form['state']
zip_code = request.form['zip_code']
phone = request.form['phone']
email = request.form['email']
birthday = request.form['birthday']
# make sure the customer isn't in the list first
query = "SELECT first_name FROM Customers WHERE first_name = '" + first_name + "' AND last_name = '" + last_name + "';"
check = db.execute_query(db_connection, query).fetchall();
# in SQL/Flask, if a SELECT is returned with no values, fetchall returns () (not '' or None)
if check == ():
query = "INSERT INTO Customers (first_name, last_name, street, city, state, zip, phone, email, birthday) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s);"
data = (first_name, last_name, street, city, state, zip_code, phone, email, birthday)
db.execute_query(db_connection, query, data)
return redirect(url_for('customers'))
def browse_detail(id):
db_connection = db.db_connection
if request.method == 'GET':
query = 'SELECT * FROM Pets WHERE petsID = %d;' % (id)
cursor = db.execute_query(db_connection, query)
results = cursor.fetchall()
for pet in results:
pet['isLiked'] = False
# check if this pet is liked by this customer
query = 'SELECT * FROM CustomerLikePet WHERE customerID = "%s" And petsID = "%s";' % (
session['userID'], id)
cursor = db.execute_query(db_connection, query)
likedPetResult = cursor.fetchall()
if likedPetResult: #if this pet is liked by this customer
pet['isLiked'] = True
return render_template('customer_browse_details.j2',
pets=results,
base64=base64)
elif request.method == 'POST':
# Customer click "adopt" button, pet becomes "pending"
query = "UPDATE Pets SET availability='pending' WHERE petsID=%d;" % (
id)
db.execute_query(db.db_connection, query)
petsID = id
customerEmail = session['username']
# First check if row existed IN AdminMsg
query = 'SELECT * FROM AdminMsg WHERE customerEmail = %s AND petsID=%s'
data = (customerEmail, petsID)
cursor = db.execute_query(db_connection, query, data)
results = cursor.fetchall()
if results:
# Update corresponding message
query = 'UPDATE AdminMsg SET status="pending" WHERE customerEmail = %s AND petsID=%s'
data = (customerEmail, petsID)
cursor = db.execute_query(db_connection, query, data)
else:
# Send message to AdminMsg table
query = 'INSERT IGNORE INTO AdminMsg(petsID, customerEmail, status) VALUES (%s, %s, %s)'
status = "pending"
data = (petsID, customerEmail, status)
cursor = db.execute_query(db_connection, query, data)
return render_template('customer_request_ok.j2',
userID=session['userID'])
def Vehicles():
""" Display Vehicles page and handle vehicle search """
query = "SELECT * FROM Vehicles ORDER BY make;"
# Set search query string
searchTerm = request.args.get('searchTerm')
# Set query to return rows specified by search term, else set the query to return all rows
if searchTerm:
query = f'SELECT * FROM Vehicles WHERE vehicle_type LIKE "%%{searchTerm}%%" OR make LIKE "%%{searchTerm}%%" OR model LIKE "%%{searchTerm}%%" or year LIKE "%%{searchTerm}%%" or color LIKE "%%{searchTerm}%%" ORDER BY make;'
# Execute query to display rows
cursor = db.execute_query(db_connection=db_connection, query=query)
results = cursor.fetchall()
cursor.close()
return render_template("Vehicles.j2", Vehicles=results)
def Employees():
""" Display Employees page and handle employee search """
query = "SELECT * FROM Employees ORDER BY last_name;"
# Set search query string
searchTerm = request.args.get('searchTerm')
# Set query to return rows specified by search term, else set the query to return all rows
if searchTerm:
query = f'SELECT * FROM Employees WHERE first_name LIKE "%%{searchTerm}%%" OR last_name LIKE "%%{searchTerm}%%" OR title LIKE "%%{searchTerm}%%" ORDER BY last_name;'
# Execute query to display rows
cursor = db.execute_query(db_connection=db_connection, query=query)
results = cursor.fetchall()
cursor.close()
return render_template("Employees.j2", Employees=results)
def cust_index():
'''Customer landing page, displays data from reviews database table '''
print('Accessing the customer_index landing page')
if request.method == 'POST':
print('POST received.')
print()
print('SEARCH: ' + request.form['search'])
print('CATEGORY: ' + request.form['restroomSearch'])
print()
db_connection = connect_to_database()
# if request.form['search'] is empty, return all
if request.form['search'] == '':
query = "SELECT rv.reviewID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rv.overallRating, rv.cleanliness, rv.comment, rv.createdAt, rv.restroomID, rv.userID FROM Restrooms rr JOIN Locations l ON l.locationID = rr.locationID JOIN Reviews rv ON rv.restroomID = rr.restroomID "
results = execute_query(db_connection, query).fetchall()
# print("blank search field")
return render_template('/customer_index.html', results=results)
# otherwise, use category for WHERE clause filtering on search text
search = request.form['search']
category = request.form['restroomSearch']
if category == 'reviewID':
query = "SELECT rv.reviewID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rv.overallRating, rv.cleanliness, rv.comment, rv.createdAt, rv.restroomID, rv.userID FROM Restrooms rr JOIN Locations l ON l.locationID = rr.locationID JOIN Reviews rv ON rv.restroomID = rr.restroomID WHERE rv.reviewID = " + search
results = execute_query(db_connection, query).fetchall()
return render_template('customer_index.html', results=results)
if category == 'restroomID':
query = "SELECT rv.reviewID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rv.overallRating, rv.cleanliness, rv.comment, rv.createdAt, rv.restroomID, rv.userID FROM Restrooms rr JOIN Locations l ON l.locationID = rr.locationID JOIN Reviews rv ON rv.restroomID = rr.restroomID WHERE rr.restroomID = " + search
# print(query)
#data = (search)
results = execute_query(db_connection, query).fetchall()
return render_template('customer_index.html', results=results)
if category == 'userID':
query = "SELECT rv.reviewID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rv.overallRating, rv.cleanliness, rv.comment, rv.createdAt, rv.restroomID, rv.userID FROM Restrooms rr JOIN Locations l ON l.locationID = rr.locationID JOIN Reviews rv ON rv.restroomID = rr.restroomID WHERE rv.userID = " + search
#data = (search)
results = execute_query(db_connection, query).fetchall()
return render_template('customer_index.html', results=results)
if category == 'cleanliness':
query = "SELECT rv.reviewID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rv.overallRating, rv.cleanliness, rv.comment, rv.createdAt, rv.restroomID, rv.userID FROM Restrooms rr JOIN Locations l ON l.locationID = rr.locationID JOIN Reviews rv ON rv.restroomID = rr.restroomID WHERE rv.cleanliness = '" + search + "';"
#data = (search)
results = execute_query(db_connection, query).fetchall()
# print(results)
return render_template('customer_index.html', results=results)
db_connection = connect_to_database()
query = "SELECT rv.reviewID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address, rv.overallRating, rv.cleanliness, rv.comment, rv.createdAt, rv.restroomID, rv.userID FROM Restrooms rr JOIN Locations l ON l.locationID = rr.locationID JOIN Reviews rv ON rv.restroomID = rr.restroomID "
results = execute_query(db_connection, query).fetchall()
# print(results)
return render_template('customer_index.html', results=results)
def admin_new_pets():
if request.method == 'GET':
return render_template('admin_new_pets.j2')
elif request.method == 'POST':
# print(request.json);
# print(request.form);
db_connection = db.db_connection
query = 'INSERT INTO Pets(type, name, img, breed, age, size, gender, goodWithKids, goodWithDogs, goodWithCats, mustBeLeashed, availability) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
type = request.form['type']
name = request.form['name']
img = request.files['img'].read()
breed = request.form['breed']
age = request.form['age']
size = request.form['size']
gender = request.form['gender']
goodWithKids = request.form['goodWithKids']
goodWithDogs = request.form['goodWithDogs']
goodWithCats = request.form['goodWithCats']
mustBeLeashed = request.form['mustBeLeashed']
availability = request.form['availability']
data = (type, name, img, breed, age, size, gender, goodWithKids,
goodWithDogs, goodWithCats, mustBeLeashed, availability)
cursor = db.execute_query(db.db_connection, query, data)
# def show_warning(msg):
# top = tkinter.Tk()
# top.withdraw()
# top.update()
# tk.messagebox.showwarning("title", msg)
# top.destroy()
# if __name__ == '__main__':
# show_warning('example')
# print("Printed immediately.")
# time.sleep(2.4)
# print("Printed after 2.4 seconds.")
return redirect("%s%s" % ("admin_view_details/", cursor.lastrowid))
def updateVehicle(id):
""" Update a given row in the Vehicles table """
# Get the vehicle based on vin
vehicle = get_vehicle(id)
# Get inputs from the POST request and store as variables
if request.method == 'POST':
vehicle_type = request.form['vehicle_type']
make = request.form['make']
model = request.form['model']
year = request.form['year']
color = request.form['color']
msrp = request.form['msrp']
# Handle checkboxes
if request.form.get("is_preowned") == None:
is_preowned = 0
else:
is_preowned = 1
if request.form.get("is_for_sale") == None:
is_for_sale = 0
else:
is_for_sale = 1
# Set query to update a row based on the form inputs
query = "UPDATE Vehicles SET vehicle_type=%s, make=%s, model=%s, year=%s, color=%s, is_preowned=%s, is_for_sale=%s, msrp=%s WHERE vin=" + (
'"{}"'.format(id))
cursor = db.execute_query(db_connection=db_connection,
query=query,
query_params=(vehicle_type, make, model,
year, color, is_preowned,
is_for_sale, msrp))
results = cursor.fetchall()
return redirect('/vehicles')
return render_template("updateVehicle.j2", vehicle=vehicle)
def emp_add():
'''Employee add page. If a GET request, then simply return the page to the user. If the page is reached via an HTTP POST, then collect the data provided and INSERT the new Restroom and appropriate data.'''
db_connection = connect_to_database()
if request.method == 'POST':
open_hour = request.form["openTime"]
close_hour = request.form["closeTime"]
free = request.form["free"]
comments = request.form["comments"]
if request.form["address2"] != '':
street = request.form["address"] + ' ' + request.form["address2"]
else:
street = request.form["address"]
city = request.form["city"]
state = request.form["state"]
country = request.form["country"]
#first_name = request.form["firstName"]
#last_name = request.form["lastName"]
#email = request.form["email"]
# Need to insert into Locations first
query = 'INSERT INTO Locations (street, city, state, country) VALUES (%s, %s, %s, %s)'
data = (street, city, state, country)
execute_query(db_connection, query, data)
# Next insert into Restrooms
query = 'INSERT INTO Restrooms (locationID, openHour, closeHour, free) VALUES ((SELECT locationID FROM Locations WHERE street = %s AND city = %s AND state = %s AND country = %s), %s, %s, %s)'
data = (street, city, state, country, open_hour, close_hour, free)
execute_query(db_connection, query, data)
# Finally insert into RestroomsEmployees
query = 'INSERT INTO RestroomsEmployees (restroomID, employeeID, comments, inspectedAt) VALUES ((SELECT max(restroomID) FROM Restrooms), (SELECT employeeID FROM Employees ORDER BY lastLogin DESC LIMIT 1), %s, CURRENT_TIMESTAMP())'
data = (comments, )
execute_query(db_connection, query, data)
return redirect('/employee_index', code=302)
else:
return render_template('/employee_add.html')
def filter_customers():
first_name = request.form['ffname']
last_name = request.form['flname']
street = request.form['fstreet']
city = request.form['fcity']
state = request.form['fstate']
zip_code = request.form['fzip']
phone = request.form['fphone']
email = request.form['femail']
age = request.form['fage']
game = request.form['game']
console = request.form['console']
if first_name == '' and last_name == '' and street == '' and city == '' and state == 'Choose...' and zip_code == '' \
and phone == '' and email == '' and age == 'Choose...' and game == 'Choose...' and console == 'Choose...':
return redirect(url_for('customers'))
query = 'SELECT first_name, last_name, street, city, state, zip, phone, email, birthday, customer_id FROM Customers ' \
'LEFT JOIN Rentals ON customer_ID = customer LEFT JOIN Game_Rentals ON rental_ID = rental LEFT JOIN Console_Versions ' \
'ON game_version = item_ID LEFT JOIN Games ON game = game_ID LEFT JOIN Consoles ON console = console_ID WHERE'
where = []
if first_name != '':
where.append(' first_name LIKE "%%' + str(first_name) + '%%"')
if last_name != '':
where.append(' last_name LIKE "%%' + str(last_name) + '%%"')
if street != '':
where.append(' street = "' + str(street) + '"')
if city != '':
where.append(' city LIKE "%%' + str(city) + '%%"')
if state != 'Choose...':
where.append(' state = "' + str(state) + '"')
if zip_code != '':
where.append(' zip = "' + str(zip_code) + '"')
if phone != '':
where.append(' phone = "' + str(phone) + '"')
if email != '':
where.append(' email LIKE "%%' + str(email) + '%%"')
if age != 'Choose...':
if age == '16 & Under':
where.append(' birthday > DATE_SUB(CURRENT_DATE, INTERVAL 17 YEAR)')
elif age == '17-34':
where.append(' birthday > DATE_SUB(CURRENT_DATE, INTERVAL 35 YEAR) AND birthday <= DATE_SUB(CURRENT_DATE, INTERVAL 17 YEAR)')
elif age == '35-49':
where.append(' birthday > DATE_SUB(CURRENT_DATE, INTERVAL 50 YEAR) AND birthday <= DATE_SUB(CURRENT_DATE, INTERVAL 35 YEAR)')
elif age == '50-64':
where.append(' birthday > DATE_SUB(CURRENT_DATE, INTERVAL 64 YEAR) AND birthday <= DATE_SUB(CURRENT_DATE, INTERVAL 50 YEAR)')
else:
where.append(' birthday <= DATE_SUB(CURRENT_DATE, INTERVAL 65 YEAR)')
if game != 'Choose...':
where.append(' title = "' + str(game) + '"')
if console != 'Choose...':
where.append(' name = "' + str(console) + '"')
print(where)
constraints = ''
for i in range(len(where)):
if i == len(where) - 1:
constraints += where[i] + ' GROUP BY customer_ID;'
else:
constraints += where[i] + ' AND'
print(constraints)
query += constraints
print(query)
cursor = db.execute_query(db_connection=db_connection, query=query)
results = cursor.fetchall()
q2 = "SELECT customer, rental, game_version, title, name, DATE_FORMAT(rent_date, '%%b %%e %%Y') AS rent_date, DATE_FORMAT(DATE_ADD(rent_date, INTERVAL 3 DAY), '%%b %%e %%Y') AS due, " \
"DATE_FORMAT(paid, '%%b %%e %%Y') AS paid, first_name, last_name FROM Games INNER JOIN Console_Versions ON game_ID = game " \
"INNER JOIN Consoles ON console = console_ID INNER JOIN Game_Rentals ON item_ID = game_version " \
"INNER JOIN Rentals ON rental = rental_ID INNER JOIN Customers ON customer = customer_ID;"
r2 = db.execute_query(db_connection=db_connection, query=q2).fetchall()
states = ['AL', 'AK', 'AS', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'GU', 'HI', 'ID', 'IL', 'IN',
'IA',
'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY',
'NC',
'ND', 'MP', 'OH', 'OK', 'OR', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'VI', 'WA',
'WV', 'WI', 'WY']
gq = 'SELECT title FROM Games;'
g_r = db.execute_query(db_connection=db_connection, query=gq).fetchall()
cq = 'SELECT name FROM Consoles;'
c_r = db.execute_query(db_connection=db_connection, query=cq).fetchall()
return render_template("customers.j2", customers=results, states=states, items=r2, games=g_r, consoles=c_r)
def delete_item(gv_id, re_id):
query = "DELETE FROM Game_Rentals WHERE game_version = %s AND rental = %s" % (gv_id, re_id)
result = db.execute_query(db_connection, query=query)
print("Row " + str(result.rowcount) + " deleted.")
return redirect(url_for('rentals'))
def legue_histories():
query = "SELECT * FROM league_histories;"
cursor = db.execute_query(db_connection=db_connection, query=query)
results = json.dumps(cursor.fetchall())
return results
def player_team_relations():
query = "SELECT * FROM player_team_relations;"
cursor = db.execute_query(db_connection=db_connection, query=query)
results = json.dumps(cursor.fetchall())
return results
def seasons():
query = "SELECT * FROM seasons;"
cursor = db.execute_query(db_connection=db_connection, query=query)
results = json.dumps(cursor.fetchall())
return results
def filter_games():
query = 'SELECT game_id, title, player_count, rating, publisher, genre FROM Games LEFT JOIN Console_Versions ON ' \
'game_ID = game LEFT JOIN Consoles ON console = console_ID '
renter = request.form['frent']
if renter != "Choose...":
query += 'INNER JOIN Game_Rentals ON item_ID = game_version INNER JOIN Rentals ON rental = rental_ID INNER JOIN Customers ON customer = customer_ID WHERE'
else:
query += 'WHERE'
game = request.form['ftitle']
players = request.form['fplayers']
rating = request.form['frating']
online = request.form['fonline']
pub = request.form['fpub']
genre = request.form['fgenre']
console = request.form['fcon']
where = []
if game != '':
where.append(' title LIKE "%%' + game + '%%"')
if players != 'Choose...':
if players == 'Single-Player':
where.append(' player_count = "1"')
else:
where.append(' player_count > "1"')
if rating != 'Choose...':
where.append(' rating = "' + rating +'"')
if online != 'Choose...':
if online == "Yes":
where.append(' online = "1"')
else:
where.append(' online = "0"')
if pub != 'Choose...':
where.append(' publisher = "' + pub + '"')
if genre != 'Choose...':
where.append(' genre = "' + genre + '"')
if console != 'Choose...':
where.append(' name = "' + console + '"')
if renter != 'Choose...':
first, last = renter.split(' ', 1)
where.append(' first_name = "' + first + '" AND last_name = "' + last + '"')
if len(where) == 0:
return redirect(url_for('games'))
constraints = ''
for i in range(len(where)):
if i == 0:
constraints += where[i]
else:
constraints += ' AND' + where[i]
constraints += ' GROUP BY game_id;'
print(constraints)
query += constraints
print(query)
cursor = db.execute_query(db_connection=db_connection, query=query)
results = cursor.fetchall()
q2 = "SELECT item_ID, game_ID, title, name, quantity, (quantity - SUM(CASE WHEN paid = '0000-00-00' THEN '1' ELSE '0' END)) as available " \
"FROM Console_Versions INNER JOIN Consoles ON console = console_ID INNER JOIN Games ON game = game_ID LEFT JOIN Game_Rentals " \
"ON item_ID = game_version LEFT JOIN Rentals ON rental = rental_ID GROUP BY item_ID"
r2 = db.execute_query(db_connection=db_connection, query=q2).fetchall()
dropdown = "SELECT name, console_id FROM Consoles;"
dd_results = db.execute_query(db_connection=db_connection, query=dropdown).fetchall()
people = "SELECT first_name, last_name FROM Customers;"
ppl_r = db.execute_query(db_connection=db_connection, query=people).fetchall()
genres = ['2D Platformer', '3D Platformer', 'Action-Adventure', 'Action RPG', 'Arcade',
'Battle Royale', 'Fighting', 'First-Person Shooter', 'Fitness', 'Japanese RPG', 'MOBA/MMORPG',
'Open World', 'Party', 'Puzzle', 'Racing', 'Sports', 'Strategy', 'Survival Horror',
'Third-Person Shooter', 'Visual Novel']
return render_template("games.j2", games=results, versions=r2, dropdown=dd_results, genres=genres, people=ppl_r)
def cats_archive():
db_connection = db.db_connection
query = 'SELECT * FROM Pets WHERE type = "%s";' % ("cat")
cursor = db.execute_query(db_connection, query)
results = cursor.fetchall()
return render_template('cats_archive.j2', cats=results, base64=base64)
