def book():
print("Fetching book information table")
db_connection = connect_to_database()
query = "SELECT * FROM books"
result = execute_query(db_connection, query)
print(result)
query = "SELECT bookID FROM books"
books = execute_query(db_connection, query)
print(books)
ids = books.fetchall()
for id in ids:
print(id)
book_id = request.args.get('book_id')
print("book id: ", book_id)
if (book_id is not None):
print("Selecting author with id ", book_id)
query = "SELECT title FROM books where bookID=%s" % book_id
titles = execute_query(db_connection, query)
for n in titles:
print(n)
return render_template('book.html',
rows=result,
book_id=books,
search=titles)
# state = {'id': author_id, 'search_id': search_id}
return render_template('book.html', rows=result, book_id=books)
def create_palace():
"""
process the create a palace query sent from new_palace() and displays a confirmation message.
"""
db_connection = connect_to_database()
# create palace
inputs = request.form.to_dict(flat=True)
print("Creating a new Palace...")
query = "INSERT INTO `palaces` (`name`, `description`) " \
"VALUES('{}', '{}');".format(inputs['name'], inputs['desc'])
# successfully created message and instructions
message = 'Palace successfully created.'
instruction = 'Select the palace in the dropdown above and click "VIEW" to add locus to it.'
# error handler for mysql. Set error message to be displayed on webapp
try:
execute_query(db_connection, query).fetchall()
except db_connection.IntegrityError as err:
message = 'Could not create palace.'
instruction = 'Palace name must be unique. Error: ' + str(err)
context = {
'list_palaces': list_palaces(),
'message': message,
'instruction': instruction
}
return render_template('createPalace.html', context=context)
def admin_update_product(product_id):
'''Update a product that exists in the database.'''
db_connection = connect_to_database()
# Get the existing product info and pass it into a template
if request.method == 'GET':
# Create and execute query to get all product info
get_prod_info_query = 'SELECT * FROM `products` WHERE product_id = %s;' % (
product_id)
get_prod_info_result = execute_query(db_connection,
get_prod_info_query).fetchone()
return render_template('admin-update-product.html',
product=get_prod_info_result)
# Update the product using the submitted form
if request.method == 'POST':
# Gather form data
product_name = request.form['product-name']
category = request.form['category']
vendor = request.form['vendor']
price = request.form['price']
qty_available = request.form['qty-available']
image = request.form['image']
# Create and execute query to update the product
update_prod_query = 'UPDATE `products` SET product_name = %s, category = %s, vendor = %s, price = %s, image = %s, quantity_available = %s \
WHERE product_id = %s;'
data = (product_name, category, vendor, price, image, qty_available,
product_id)
update_prod_result = execute_query(db_connection, update_prod_query,
data)
return redirect(
url_for('admin')) # Redirect user back to the admin page
def update_staff(id):
print('in the update_staff function')
db_connection = connect_to_database()
#display existing data about a staff
if request.method == 'GET':
staff_query = "SELECT firstName, lastName, staffType, staffID from Staff WHERE staffID = %s" % (
id)
staff_result = execute_query(db_connection, staff_query).fetchone()
# when staff is not part of Staff table
if staff_result == None:
return "No such Staff found!"
return render_template('update_staff.html', staff=staff_result)
# update staff and show the change in the browse staff page
elif request.method == 'POST':
staffID = request.form['staffID']
firstName = request.form['firstName']
lastName = request.form['lastName']
staffType = request.form['staffType']
query = "UPDATE Staff SET firstName = %s, lastName = %s, staffType = %s WHERE staffID = %s"
data = (firstName, lastName, staffType, staffID)
result = execute_query(db_connection, query, data)
flash("Updated! " + " Staff ID #" + str(staffID))
return redirect('/browse_staff')
def add_staff_patients():
db_connection = connect_to_database()
if request.method == 'GET':
patient_query = 'SELECT patientID, firstName, lastName FROM Patients'
patient_result = execute_query(db_connection, patient_query)
staff_query = 'SELECT staffID, firstName, lastName FROM Staff'
staff_result = execute_query(db_connection, staff_query)
return render_template('add_staff_patients.html',
staff=staff_result,
patients=patient_result)
elif request.method == 'POST':
print("Adding new staff-patient relationship")
staf_id = request.form['staf_id']
pat_id = request.form['pat_id']
# check if relation already exists
qo = 'SELECT staffID, patientID FROM Staff_Patients WHERE staffID = %s and patientID = %s'
do = (staf_id, pat_id)
ro = execute_query(db_connection, qo, do).fetchone()
if ro is None:
query = 'INSERT INTO Staff_Patients (staffID, patientID) VALUES (%s,%s)'
data = (staf_id, pat_id)
execute_query(db_connection, query, data)
flash('Relationship Added!')
else:
flash('Error Relation Already Exits!')
return redirect('add_staff_patients')
def updateProduct(id):
print('In the function')
db_connection = connect_to_database()
#display existing data
if request.method == 'GET':
print('The GET request')
product_query = 'SELECT productID, productName, brandName, price, category, sale, color from Products WHERE productID = %s' % (
id)
product_result = execute_query(db_connection, product_query).fetchone()
if product_result == None:
return "No product found!"
return render_template('updateProduct.html', product=product_result)
elif request.method == 'POST':
print('The POST request')
productID = request.form['productID']
productName = request.form['productName']
brandName = request.form['brandName']
price = request.form['price']
category = request.form['category']
sale = request.form['sale']
color = request.form['color']
query = "UPDATE Products SET productName = %s, brandName = %s, price = %s, category = %s, sale = %s, color = %s WHERE productID = %s"
data = (productName, brandName, price, category, sale, color,
productID)
result = execute_query(db_connection, query, data)
return redirect(url_for('products'))
def updateCustomer(id):
print('In the function')
db_connection = connect_to_database()
#display existing data
if request.method == 'GET':
print('The GET request')
customer_query = 'SELECT customerID, email, firstName, lastName, address, dob, phone, city, state, zipcode from Customers WHERE customerID = %s' % (
id)
customer_result = execute_query(db_connection,
customer_query).fetchone()
if customer_result == None:
return "No customer found!"
return render_template('updateCustomer.html', customer=customer_result)
elif request.method == 'POST':
print('The POST request')
customerID = request.form['customerID']
email = request.form['email']
firstName = request.form['firstName']
lastName = request.form['lastName']
address = request.form['address']
dob = request.form['dob']
phone = request.form['phone']
city = request.form['city']
state = request.form['state']
zipcode = request.form['zipcode']
query = "UPDATE Customers SET email = %s, firstName = %s, lastName = %s, address = %s, dob = %s, phone = %s, city = %s, state = %s, zipcode = %s WHERE customerID = %s"
data = (email, firstName, lastName, address, dob, phone, city, state,
zipcode, customerID)
result = execute_query(db_connection, query, data)
return redirect(url_for('customers'))
def browse_counselors():
print("Fetching and rendering counselors web page")
db_connection = connect_to_database()
#display all counselors
if request.method == 'GET':
query = "SELECT `counselorID`, `first_name`, `last_name`, `name` FROM `Counselors` INNER JOIN Shelters ON Counselors.shelterID = Shelters.shelterID ORDER BY `first_name`"
result = execute_query(db_connection, query).fetchall()
print(result)
#query to show dropdown shelter options in counselor form
query = 'SELECT shelterID, name from Shelters'
shelters = execute_query(db_connection,query).fetchall()
print(shelters)
return render_template('counselors.html', rows=result, shelters=shelters)
elif request.method == 'POST':
#add counselor to table
print("Adding an counselor")
fname = request.form['firstName']
lname = request.form['lastName']
shelterID = request.form['shelterID']
#query to add counselor
query = 'INSERT INTO Counselors (first_name, last_name, shelterID) VALUES (%s,%s,%s)'
data = (fname, lname, shelterID)
db_connection = connect_to_database()
execute_query(db_connection, query, data)
return redirect('/counselors')
def update_counselor(id):
db_connection = connect_to_database()
#display existing data
if request.method == 'GET':
#query to populate form for counselor with given id
people_query = 'SELECT counselorID, first_name, last_name, shelterID FROM Counselors where counselorID = %s' % (id)
people_result = execute_query(db_connection, people_query).fetchone()
if people_result == None:
return "No such person found!"
#query to poulate shelter dropdown on form
shelters_query = 'SELECT shelterID, name from Shelters'
shelters_results = execute_query(db_connection, shelters_query).fetchall()
print('Returning')
return render_template('update_counselor.html', shelters = shelters_results, person = people_result)
elif request.method == 'POST':
print('The POST request')
#grabbing info from the form
character_id = request.form['character_id']
fname = request.form['fname']
lname = request.form['lname']
shelter = request.form['shelter']
#query to update counselors
query = "UPDATE Counselors SET first_name= %s, last_name= %s, shelterID = %s WHERE counselorID = %s"
data = (fname, lname, shelter, character_id)
result = execute_query(db_connection, query, data)
print(str(result.rowcount) + " row(s) updated")
return redirect('/counselors')
def classesedit(Class_ID):
#If a GET request was the method by which the page was requested, then we will display the page containing an empty textform for inputting a new value for the Class_Name attribute for this particular row in the Class table
if request.method == 'GET':
return render_template('classesedit.html')
#If a POST request was the method by which the page was requested, then the user arrives to this view after having used the forms involved in updating a class's name.
elif request.method == 'POST':
#Connect to your database and create a new database object
db_connection = connect_to_database()
#Obtain the user supplied strings from the forms in the Edit Class Page and assign them to the correspondingly named variable below.
New_ClassName = request.form['EditClassNameInput']
#Create a string representation of an UPDATE query into the Class Table
query = 'UPDATE Class SET Class_Name = %s WHERE Class_ID = %s;'
#Use the variable containing user input from the form and store it as a tuple assigned to var data.
data = (
New_ClassName,
Class_ID,
)
#Execute the query using the query's string representation, tuple of user "selected" data, and the database object.
execute_query(db_connection, query, data)
#Redirect the user back to the '/students' view to show the newly updated Class's name.
return redirect('/classes')
def schoolsadd():
#If a GET request was the method by which the page was requested, then we will display the page containing empty forms for adding a new School.
if request.method == 'GET':
return render_template('schoolsadd.html')
#If a POST request was the method by which the page was requested, then the user arrives to this view after having used the forms involved in adding a new School.
elif request.method == 'POST':
#Connect to your database and create a new database object
db_connection = connect_to_database()
#Obtain the user supplied strings from the forms in the Add Schools Page and assign them to the correspondingly named variables below.
School_Name = request.form['SchoolNameInput']
School_Address = request.form['SchoolAddressInput']
School_City = request.form['SchoolCityInput']
School_Zipcode = request.form['SchoolZipcodeInput']
#Create a string representation of an INSERT query into the School Table
query = 'INSERT INTO School(School_Name, Street_Address, City, Zip_Code) VALUES (%s, %s, %s, %s);'
#Use the variables containing user input from the forms and store them together as a tuple assigned to var data.
data = (School_Name, School_Address, School_City, School_Zipcode)
#Execute the query using the query's string representation, tuple of user supplied data, and the database object.
execute_query(db_connection, query, data)
return redirect('/schools')
def schools():
#Connect to your database and create a new database object
db_connection = connect_to_database()
#If a GET request was the method by which the page was requested, then we will display all the user-relevant information from the School table
if request.method == 'GET':
query = 'SELECT School_Name, Street_Address, City, Zip_Code FROM School;'
result = execute_query(db_connection, query).fetchall()
return render_template('schools.html', SELECT_School_Rows=result)
#If a POST request was the method by which the page was requested, then the user arrives to this page after having used the Search form/submit button
elif request.method == 'POST':
#Take the user supplied string they are interested in using for their search criteria
Search_String = request.form['FindSchoolName']
#Add MariaDB compliant wildcard SQL operators on either side of the user's search string and save the result as a string object assigned to var pattern.
pattern = '%' + Search_String + '%'
#Execute a SELECT query using the user's search term concatenated with the SQL wildcards to the database object. Store the result of the query in var result. The values correspond to the variable SELECT_School_Rows in the Jinja template.
result = execute_query(
db_connection,
'SELECT School_Name, Street_Address, City, Zip_Code FROM School WHERE School_Name LIKE %s;',
(pattern, )).fetchall()
#Populate the table in schools.html template with the values stored in var result.
return render_template('schools.html', SELECT_School_Rows=result)
def order():
print("Fetching order information table")
db_connection = connect_to_database()
query = "SELECT * FROM orders"
result = execute_query(db_connection, query)
print(result)
query = "SELECT orderID FROM orders"
order = execute_query(db_connection, query)
print(order)
ids = order.fetchall()
for id in ids:
print(id)
order_id = request.args.get('order_id')
print("order id: ", order_id)
if (order_id is not None):
print("Selecting customer with id ", order_id)
query = "SELECT customerID, orderDate FROM orders where orderID=%s" % order_id
orderInfo = execute_query(db_connection, query)
for n in orderInfo:
print(n)
return render_template('order.html',
rows=result,
order_id=order,
order=orderInfo)
return render_template('order.html', rows=result, order_id=order)
def customer():
print("Fetching customer information table")
db_connection = connect_to_database()
query = "SELECT * FROM customers"
result = execute_query(db_connection, query)
print(result)
query = "SELECT customerID FROM customers"
customer = execute_query(db_connection, query)
print(customer)
ids = customer.fetchall()
for id in ids:
print(id)
customer_id = request.args.get('customer_id')
print("customer id: ", customer_id)
if (customer_id is not None):
print("Selecting customer with id ", customer_id)
query = "SELECT phoneNumber FROM customers where customerID=%s" % customer_id
customerInfo = execute_query(db_connection, query)
for n in customerInfo:
print(n)
return render_template('customer.html',
rows=result,
customer_id=customer,
customer=customerInfo)
return render_template('customer.html', rows=result, customer_id=customer)
def browse_drinks():
db_connection = connect_to_database()
# Adding a new drink from form
if request.method == 'POST':
print("Add new drink!")
price = request.form['price']
name = request.form['name']
inventory = request.form['inventory']
secret_ingredient = request.form['sec_ing']
if(price != '' and inventory != '' and name != ''):
query = 'INSERT INTO drinks (price, inventory, secret_ingredient, name) VALUES (%s,%s,%s, %s)'
if secret_ingredient == '0':
data = (price, inventory, None, name)
else:
data = (price, inventory, secret_ingredient, name)
execute_query(db_connection, query, data)
print("drink added!")
# Getting current drinks
print("Fetching and rendering drinks web page")
query = "SELECT drinks.id, price, inventory, ingredients.ingredient_name as 'Secret Ingredient', drinks.name from drinks LEFT JOIN ingredients ON drinks.secret_ingredient = ingredients.id;"
drink_result = execute_query(db_connection, query).fetchall()
# Getting ingredients for add new drink dropdown
query = 'SELECT id, ingredient_name FROM ingredients'
ing_result = execute_query(db_connection, query).fetchall()
ing_result = list(ing_result)
ing_result.append((0,'None'))
ing_result = tuple(ing_result)
return render_template('browse_drinks.html', rows=drink_result, ingredients=ing_result, links=links)
def browse_dogs():
db_connection = connect_to_database()
if request.method == 'GET':
#query to display dogs table
query = 'SELECT petID, Shelters.name, Dogs.name, birthday, gender, breed, size, adoption_status, energy_level, coat_type, color, dogs_ok, cats_ok, kids_ok FROM Dogs INNER JOIN Shelters ON Dogs.shelterID = Shelters.shelterID'
result = execute_query(db_connection, query).fetchall()
return render_template('dogs.html', rows=result)
elif request.method == 'POST':
status = request.form['dogstatus']
search = '%' + request.form['dogSearch'] + '%'
#if filter chosen-
if status != "any":
#filter dogs by given status
query = "SELECT petID, Shelters.name, Dogs.name, birthday, gender, breed, size, adoption_status, energy_level, coat_type, color, dogs_ok, cats_ok, kids_ok FROM Dogs INNER JOIN Shelters ON Dogs.shelterID = Shelters.shelterID WHERE adoption_status=%s"
data = (status,)
result = execute_query(db_connection, query, data).fetchall()
#if search criteria entered
elif search != None:
#search for dog by name
query = "SELECT petID, Shelters.name, Dogs.name, birthday, gender, breed, size, adoption_status, energy_level, coat_type, color, dogs_ok, cats_ok, kids_ok FROM Dogs INNER JOIN Shelters ON Dogs.shelterID = Shelters.shelterID WHERE Dogs.name LIKE %s"
data = (search,)
result = execute_query(db_connection, query, data).fetchall()
#default- display dog table per usual
else:
query = 'SELECT petID, Shelters.name, Dogs.name, birthday, gender, breed, size, adoption_status, energy_level, coat_type, color, dogs_ok, cats_ok, kids_ok FROM Dogs INNER JOIN Shelters ON Dogs.shelterID = Shelters.shelterID'
result = execute_query(db_connection, query).fetchall()
return render_template('dogs.html', rows=result)
def modifyCustomers():
db_connection = connect_to_database()
if request.method == 'GET':
query = 'SELECT customerID from Customers'
result = execute_query(db_connection, query).fetchall()
print(result)
return render_template('modifyCustomers.html', rows=result)
elif request.method == 'POST':
print("Add new customer")
email = request.form['email']
firstName = request.form['firstName']
lastName = request.form['lastName']
address = request.form['address']
dob = request.form['dob']
phone = request.form['phone']
city = request.form['city']
state = request.form['state']
zipcode = request.form['zipcode']
query = 'INSERT INTO Customers (email, firstName, lastName, address, dob, phone, city, state, zipcode) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)'
data = (email, firstName, lastName, address, dob, phone, city, state,
zipcode)
execute_query(db_connection, query, data)
return redirect(url_for('customers'))
def add_new_dog():
db_connection = connect_to_database()
if request.method == 'GET':
return render_template('add_dog.html')
elif request.method == 'POST':
print("Add new dog!")
shelterID = '1'
name = request.form['dogname']
birthday = request.form['birthday']
breed = request.form['breed']
gender = request.form['doggender']
size = request.form['dogsize']
status = request.form['dogstatus']
energy = request.form['energy']
coat = request.form['coat']
color = request.form['color']
dogsOK = request.form['dogsOK']
catsOK = request.form['catsOK']
kidsOK = request.form['kidsOK']
#query to add dog
query = 'INSERT INTO Dogs (shelterID, name, birthday, gender, breed, size, adoption_status, energy_level, coat_type, color, dogs_ok, cats_ok, kids_ok) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
data = (shelterID, name, birthday, gender, breed, size, status, energy, coat, color, dogsOK, catsOK, kidsOK)
execute_query(db_connection, query, data)
return redirect('/dogs')
def updateOrder(id):
print('In the function')
db_connection = connect_to_database()
#display existing data
if request.method == 'GET':
print('The GET request')
order_query = 'SELECT orderID, customerID, productID, dateOrdered, dateDelivered, totalPrice from Orders WHERE orderID = %s' % (
id)
order_result = execute_query(db_connection, order_query).fetchone()
if order_result == None:
return "No order found!"
return render_template('updateOrder.html', order=order_result)
elif request.method == 'POST':
print('The POST request')
orderID = request.form['orderID']
customerID = request.form['customerID']
productID = request.form['productID']
dateOrdered = request.form['dateOrdered']
dateDelivered = request.form['dateDelivered']
totalPrice = request.form['totalPrice']
query = "UPDATE Orders SET customerID = %s, productID = %s, dateOrdered = %s, dateDelivered = %s, totalPrice = %s WHERE orderID = %s"
data = (customerID, productID, dateOrdered, dateDelivered, totalPrice,
orderID)
result = execute_query(db_connection, query, data)
return redirect(url_for('orders'))
def update_people(id):
db_connection = connect_to_database()
#display existing data
if request.method == 'GET':
people_query = 'SELECT character_id, fname, lname, homeworld, age from bsg_people WHERE character_id = %s' % (id)
people_result = execute_query(db_connection, people_query).fetchone()
if people_result == None:
return "No such person found!"
planets_query = 'SELECT planet_id, name from bsg_planets'
planets_results = execute_query(db_connection, planets_query).fetchall();
return render_template('people_update.html', planets = planets_results, person = people_result)
elif request.method == 'POST':
print("Update people!");
character_id = request.form['character_id']
fname = request.form['fname']
lname = request.form['lname']
age = request.form['age']
homeworld = request.form['homeworld']
print(request.form);
query = "UPDATE bsg_people SET fname = %s, lname = %s, age = %s, homeworld = %s WHERE character_id = %s"
data = (fname, lname, age, homeworld, character_id)
result = execute_query(db_connection, query, data)
print(str(result.rowcount) + " row(s) updated");
return redirect('/browse_bsg_people')
def updateStore(id):
print('In the function')
db_connection = connect_to_database()
#display existing data
if request.method == 'GET':
print('The GET request')
store_query = 'SELECT storeID, address, city, state, daysOpen, hours FROM Stores WHERE storeID = %s' % (
id)
store_result = execute_query(db_connection, store_query).fetchone()
if store_result == None:
return "No such person found!"
return render_template('updateStore.html', store=store_result)
elif request.method == 'POST':
print('The POST request')
storeID = request.form['storeID']
address = request.form['address']
city = request.form['city']
state = request.form['state']
daysOpen = request.form['daysOpen']
hours = request.form['hours']
query = "UPDATE Stores SET address = %s, city = %s, state = %s, daysOpen = %s, hours = %s WHERE storeID = %s"
data = (address, city, state, daysOpen, hours, storeID)
result = execute_query(db_connection, query, data)
return redirect(url_for('stores'))
def add_reservations():
db_connection = connect_to_database()
if request.method == 'GET':
query = "SELECT memberID, memberFirst, memberLast FROM Members;"
result = execute_query(db_connection, query).fetchall()
return render_template('reservations_add.html', members = result)
elif request.method == 'POST':
memberID = request.form['memberID']
isbn = request.form['isbn']
dateIssued = request.form['dateIssued']
dateDue = request.form['dateDue']
isReturned = request.form['isReturned']
# Error handle
try:
query = ''' INSERT INTO Reservations (memberID, bookID, dateIssued, dateDue, isReturned) VALUES
(%s,
(SELECT bi.bookID FROM Book_Items AS bi LEFT JOIN Books AS b ON bi.isbn=b.isbn WHERE b.isbn = %s),
%s,%s,%s) '''
data = (memberID, isbn, dateIssued, dateDue, isReturned)
execute_query(db_connection, query, data)
except:
print("Invalid reservation input")
query = "SELECT memberID, memberFirst, memberLast FROM Members;"
result = execute_query(db_connection, query).fetchall()
return render_template('reservations_add.html', members = result)
def add_results():
db_connection = connect_to_database()
q1 = 'SELECT Orders.orderID, Patients.lastName, Orders.orderType from Orders JOIN Patients ON Orders.patientID = Patients.patientID'
r1 = execute_query(db_connection, q1).fetchall()
# pre-fill form with order details
if request.method == 'GET':
return render_template('add_results.html', orders=r1)
elif request.method == 'POST':
print("Add new Result!")
orderID = request.form['orderID']
access = request.form['access']
status = request.form['status']
date = request.form['date']
# check if order already has a result
qo = 'SELECT orderID FROM Results WHERE orderID = %s'
do = (orderID, )
ro = execute_query(db_connection, qo, do).fetchall()
co = len(ro)
# cannot add result to order that already has a result (one result per order)
if co > 0:
flash(
"Failed: Result for Order Already Exits! Please Update Result!"
)
return render_template('add_results.html', orders=r1)
else:
query = 'INSERT INTO Results (status, orderID, date, accessedByDoctor) VALUES (%s,%s,%s,%s)'
data = (status, orderID, date, int(access))
execute_query(db_connection, query, data)
flash('Result Added!')
return render_template('add_results.html', orders=r1)
def update_reservations(id):
db_connection = connect_to_database()
if request.method == 'GET':
reservation_query = ''' SELECT DISTINCT r.reservationID, m.memberFirst, m.memberLast, b.isbn, r.dateIssued, r.dateDue, (SELECT IF(isReturned, \'Yes\', \'No\')) FROM Reservations AS r
LEFT JOIN Members AS m ON r.memberID=m.memberID
LEFT JOIN Book_Items AS bi ON r.bookID=bi.bookID
LEFT JOIN Books AS b ON bi.isbn=b.isbn
WHERE reservationID = %s ''' % (id)
reservation_result = execute_query(db_connection, reservation_query).fetchone()
return render_template('reservations_update.html', reservation = reservation_result)
elif request.method == 'POST':
reservationID = request.form['reservationID']
memberFirst = request.form['memberFirst']
memberLast = request.form['memberLast']
isbn = request.form['isbn']
dateIssued = request.form['dateIssued']
dateDue = request.form['dateDue']
isReturned = request.form['isReturned']
query = ''' UPDATE Reservations SET
memberID = (SELECT memberID FROM Members WHERE memberFirst = %s AND memberLast = %s),
bookID = (SELECT bi.bookID FROM Book_Items AS bi LEFT JOIN Books AS b ON bi.isbn=b.isbn WHERE bi.isbn = %s LIMIT 1),
dateIssued = %s, dateDue = %s, isReturned = %s WHERE reservationID = %s '''
data = (memberFirst, memberLast, isbn, dateIssued, dateDue, isReturned, reservationID)
execute_query(db_connection, query, data)
return redirect('/reservations_browse')
def add_loci():
"""
Route to execute the query to add a locus to the database
after user submits the form in the bottom of the view_palace route
"""
db_connection = connect_to_database()
inputs = request.form.to_dict(flat=True)
print("Creating a new Locus...")
# calls a stored procedure in the database
query = "CALL add_loci('{}', '{}', '{}', '{}');".format(
inputs['palace_id'], inputs['locus_name'], inputs['digitsA'],
inputs['digitsB'])
# error handler for mysql
try:
execute_query(db_connection, query).fetchall()
except db_connection.IntegrityError as err:
message = 'Could not create Loci.'
instruction = 'Loci name must be unique. Error: ' + str(err)
context = {
'list_palaces': list_palaces(),
'message': message,
'instruction': instruction
}
# using createPalace template just to display the error msg.
return render_template('createPalace.html', context=context)
return redirect("/view/" + inputs['palace_id'])
def select_dealership(dealership_id):
db_connection = connect_to_database()
search_emp = ''
search_veh = ''
if request.method == 'POST':
if 'search_emp' in request.form:
search_emp = request.form['search_emp']
if 'search_veh' in request.form:
search_veh = request.form['search_veh']
dealership_query = "SELECT dealership_name, hours, address_line_1, address_line_2, city, zip, country FROM dealership INNER JOIN dealership_address USING (address_id) WHERE dealership_id = %s;" % (
dealership_id)
dealership = execute_query(db_connection, dealership_query).fetchall()
employees_query = "SELECT num_employees, f_name, l_name, position, employee_id FROM (SELECT COUNT(employee_id) AS num_employees, dealership_id FROM employees WHERE dealership_id = %s) AS tabl1 INNER JOIN (SELECT f_name, l_name, position, employee_id, dealership_id FROM employees WHERE dealership_id = %s) AS tabl2 USING (dealership_id) WHERE INSTR(f_name, '%s') OR INSTR(l_name, '%s');" % (
dealership_id, dealership_id, search_emp, search_emp)
employees = execute_query(db_connection, employees_query).fetchall()
vehicles_query = "SELECT num_vehicles, vehicle_id, type_name, vin FROM (SELECT vehicle_id, type_name, vin, dealership_id FROM vehicle INNER JOIN vehicle_type ON type=type_id WHERE dealership_id = %s) AS tabl1 INNER JOIN (SELECT COUNT(vehicle_id) AS num_vehicles, dealership_id FROM vehicle WHERE dealership_id = %s) AS tabl2 USING (dealership_id) WHERE INSTR(vin, '%s');" % (
dealership_id, dealership_id, search_veh)
vehicles = execute_query(db_connection, vehicles_query).fetchall()
types_query = 'SELECT type_name FROM vehicle_type'
types = execute_query(db_connection, types_query).fetchall()
return render_template('selectDealership.html',
employees=employees,
vehicles=vehicles,
dealership=dealership,
dealership_id=dealership_id,
types=types)
def view_palace(palace_id=1):
"""
Route to view a selected palace. Default set to 1 only used if user clicks link in homepage
"""
db_connection = connect_to_database()
# get palace name and description
query = "SELECT `name`, `description` FROM `palaces` WHERE `palace_id` ='{}'".format(
palace_id)
rtn = execute_query(db_connection, query).fetchall()
# create context dictionary
context = {
'list_palaces': list_palaces(),
'palace_name': rtn[0][0],
'palace_desc': rtn[0][1],
'palace_id': palace_id
}
# get loci data
query = "SELECT loci.loci_id, `name`, `first_chunk_value`, `second_chunk_value`, m_person.person, m_action.action "\
"FROM `loci` JOIN `chunks` ON chunks.loci_id = loci.loci_id "\
"JOIN `chunks_mnemo` ON chunks.chunk_id = chunks_mnemo.chunk_id "\
"JOIN `mnemonics` AS m_person ON chunks_mnemo.first_mnemo = m_person.mnemo_id "\
"JOIN `mnemonics` AS m_action ON chunks_mnemo.second_mnemo = m_action.mnemo_id "\
"WHERE loci.palace_id = '{}' "\
"ORDER BY loci.loci_id;".format(palace_id)
rtn = execute_query(db_connection, query).fetchall()
context['rows'] = rtn # rows = loci data
return render_template('view.html', context=context)
def select_vehicle(dealership_id, vehicle_id):
db_connection = connect_to_database()
search = ''
if request.method == 'POST':
search = request.form['search']
dealership_query = "SELECT dealership_name, hours, address_line_1, address_line_2, city, zip, country FROM dealership INNER JOIN dealership_address USING (address_id) WHERE dealership_id = %s;" % (
dealership_id)
dealership = execute_query(db_connection, dealership_query).fetchall()
num_emp_query = "SELECT COUNT(employee_id) FROM employees WHERE dealership_id = %s" % (
dealership_id)
num_emp = execute_query(db_connection, num_emp_query).fetchall()
vehicles_query = "SELECT num_vehicles, vehicle_id, type_name, vin FROM (SELECT vehicle_id, type_name, vin, dealership_id FROM vehicle INNER JOIN vehicle_type ON type=type_id WHERE vehicle_id = %s) AS tabl1 INNER JOIN (SELECT COUNT(vehicle_id) AS num_vehicles, dealership_id FROM vehicle WHERE dealership_id = %s) AS tabl2 USING (dealership_id);" % (
vehicle_id, dealership_id)
vehicles = execute_query(db_connection, vehicles_query).fetchall()
features_query = "SELECT feature_name, feature_value, feature_description, feature_id FROM vehicle INNER JOIN vehicle_feature USING (vehicle_id) INNER JOIN feature USING (feature_id) WHERE vehicle_id = %s AND INSTR(feature_name, '%s')" % (
vehicle_id, search)
features = execute_query(db_connection, features_query).fetchall()
allfeatures_query = "SELECT feature_id, feature_name, feature_description FROM feature"
allfeatures = execute_query(db_connection, allfeatures_query).fetchall()
return render_template('selectVehicle.html',
dealership=dealership,
dealership_id=dealership_id,
employees=num_emp,
vehicles=vehicles,
features=features,
allfeatures=allfeatures)
def signin():
'''Allow the user to sign in or register as a new customer.'''
# Handle customer registration
if request.method == 'POST':
db_connection = connect_to_database()
# Gather all user data
fname = request.form['sign-up-fname-field']
lname = request.form['sign-up-lname-field']
email = request.form['sign-up-email-field']
password = request.form['sign-up-password-field']
phone = request.form['sign-up-phone-number-field']
# Create and execute query to add a new customer to the database
query = 'INSERT INTO `customers` (fname, lname, email, password, phone_number) \
VALUES (%s, %s, %s, %s, %s);'
customer_data = (fname, lname, email, password, phone)
execute_query(db_connection, query, customer_data)
# Redirect the user to the home page after registering
return redirect(url_for('home'))
elif request.method == 'GET':
return render_template("signin.html")
def update_list(list_id):
"""
Display list update form and process any updates using the same function
"""
db_connection = connect_to_database()
# display current data
if request.method == 'GET':
query = "SELECT * FROM `lists` WHERE `list_id` ='{}'".format(
list_id) # get info of list
cursor = execute_query(db_connection, query)
rtn = cursor.fetchall()
cursor.close()
context = {
'list_id': rtn[0][0],
'list_name': rtn[0][2],
'list_desc': rtn[0][3]
}
db_connection.close() # close connection before returning
return render_template('update_list.html', context=context)
elif request.method == 'POST':
query = "UPDATE `lists` SET `name` = %s, `description` = %s WHERE `list_id` = %s"
data = (request.form['list_name'], request.form['list_desc'], list_id)
cursor = execute_query(db_connection, query, data)
cursor.close()
db_connection.close() # close connection before returning
webapp.logger.info(
"Update list. userid: %s, list_id: %s, list_name: %s, list_desc: %s",
current_user.id, list_id, request.form['list_name'],
request.form['list_desc'])
return redirect('/home')
