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 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 cust_add():
'''Customer add page. If GET request, returns the page to the user, if POST request, collect data from form and INSERT to database '''
db_connection = connect_to_database()
if request.method == 'POST':
print('Customer-add.html POST request')
restroom_id = request.form["restroomID"]
overall_rating = request.form["overallRating"]
cleanliness = request.form["cleanliness"]
comments = request.form["comments"]
# print("restroomID =", restroom_id)
# print("overallRating =", overall_rating)
# print("cleanliness =", cleanliness)
# print("comments =", comments)
query = 'INSERT INTO Reviews (overallRating, cleanliness, comment, createdAt, restroomID, userID) VALUES (%s, %s, %s, CURDATE(), %s, 1)'
data = (overall_rating, cleanliness, comments, restroom_id)
execute_query(db_connection, query, data)
# return render_template('customer_add.html')
return render_template('customer_add_confirm.html')
else:
print('Customer_add.html GET request')
query = "SELECT rr.restroomID, concat(l.street, ', ', l.city, ', ', l.state, ', ', l.country ) as Address FROM Restrooms rr JOIN Locations l ON l.locationID = rr.locationID "
results = execute_query(db_connection, query).fetchall()
# print(results)
return render_template('customer_add.html', results=results)
def add_new_doctor():
db_connection = db.connect_to_database()
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 doctor.id, doctor.fname, doctor.lname, doctor.phoneNumber, doctor.salary, nurse.fname, nurse.lname FROM doctor LEFT JOIN nurse ON doctor.nurseID = nurse.id;"
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template("browse_doctor.j2",
doctors=result,
resultText="Doctor added.")
def delete_manager(id):
db_connection = db.connect_to_database()
query = "DELETE FROM manager WHERE id = %s"
data = (id, )
result = db.execute_query(db_connection, query, data)
print(str(result.rowcount) + " row(s) updated")
return redirect('/browse_manager')
def add_new_office():
db_connection = db.connect_to_database()
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']
if managerID == '':
query = 'INSERT INTO office (name, phoneNumber, street, city, state, zip) VALUES (%s,%s,%s,%s,%s,%s);'
data = (name, phoneNumber, street, city, state, zipcode)
else:
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 office.id, office.name, office.phoneNumber, office.street, office.city, office.state, office.zip, manager.fname, manager.lname FROM office LEFT JOIN manager ON office.managerID = manager.id;"
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template("browse_office.j2",
offices=result,
resultText="Office added.")
def update_office(id):
db_connection = db.connect_to_database()
if request.method == 'GET':
office_query = 'SELECT id, name, phoneNumber, street, city, state, zip, managerID FROM office WHERE id = %s' % (
id)
office_result = db.execute_query(db_connection,
office_query).fetchone()
if office_result == None:
return "No such office found!"
manager_query = 'SELECT id, fname, lname FROM manager'
manager_results = db.execute_query(db_connection,
manager_query).fetchall()
return render_template('office_update.j2',
managers=manager_results,
office=office_result)
elif request.method == 'POST':
office_id = request.form['office_id']
name = request.form['name']
phoneNumber = request.form['phoneNumber']
street = request.form['street']
city = request.form['city']
state = request.form['state']
zipco = request.form['zip']
managerID = request.form['managerID']
if managerID == '':
query = "UPDATE office SET name = %s, phoneNumber = %s, street = %s, city = %s, state = %s, zip = %s, managerID = NULL WHERE id = %s"
data = (name, phoneNumber, street, city, state, zipco, office_id)
else:
query = "UPDATE office SET name = %s, phoneNumber = %s, street = %s, city = %s, state = %s, zip = %s, managerID = %s WHERE id = %s"
data = (name, phoneNumber, street, city, state, zipco, managerID,
office_id)
result = db.execute_query(db_connection, query, data)
print(str(result.rowcount) + " row(s) updated")
return redirect('/browse_office')
def add_new_patient():
db_connection = db.connect_to_database()
if request.method == 'GET':
query = 'SELECT id, fname, lname FROM doctor;'
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template('patient_add_new.j2', doctors=result)
elif request.method == 'POST':
print("Add new patient")
fname = request.form['fname']
lname = request.form['lname']
phoneNumber = request.form['phoneNumber']
street = request.form['street']
city = request.form['city']
state = request.form['state']
zipcode = request.form['zip']
dob = request.form['dob']
weight = request.form['weight']
doctorID = request.form['doctorID']
query = 'INSERT INTO patient (fname, lname, phoneNumber, street, city, state, zip, dob, weight, doctorID) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
data = (fname, lname, phoneNumber, street, city, state, zipcode, dob,
weight, doctorID)
db.execute_query(db_connection, query, data)
query = "SELECT patient.id, patient.fname, patient.lname, patient.phoneNumber, patient.street, patient.city, patient.state, patient.zip, patient.dob, patient.weight, doctor.fname, doctor.lname FROM patient LEFT JOIN doctor ON patient.doctorID = doctor.id;"
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template("browse_patient.j2",
patients=result,
resultText="Patient added")
def update_doctor(id):
db_connection = db.connect_to_database()
if request.method == 'GET':
doctor_query = 'SELECT id, fname, lname, phoneNumber, salary, nurseID FROM doctor WHERE id = %s' % (
id)
doctor_result = db.execute_query(db_connection,
doctor_query).fetchone()
if doctor_result == None:
return "No such doctor found!"
nurses_query = 'SELECT id, fname, lname FROM nurse'
nurses_results = db.execute_query(db_connection,
nurses_query).fetchall()
return render_template('doctor_update.j2',
nurses=nurses_results,
doctor=doctor_result)
elif request.method == 'POST':
doctor_id = request.form['doctor_id']
fname = request.form['fname']
lname = request.form['lname']
phoneNumber = request.form['phoneNumber']
salary = request.form['salary']
nurseID = request.form['nurseID']
query = "UPDATE doctor SET fname = %s, lname = %s, phoneNumber = %s, salary = %s, nurseID = %s WHERE id = %s"
data = (fname, lname, phoneNumber, salary, nurseID, doctor_id)
result = db.execute_query(db_connection, query, data)
print(str(result.rowcount) + " row(s) updated")
return redirect('/browse_doctor')
def add_new_assignment():
db_connection = db.connect_to_database()
if request.method == 'GET':
query = 'SELECT doctor.id, doctor.fname, doctor.lname FROM doctor;'
result = db.execute_query(db_connection, query).fetchall()
print(result)
query = 'SELECT office.id, office.name FROM office;'
result2 = db.execute_query(db_connection, query).fetchall()
print(result2)
return render_template('assignment_add_new.j2',
doctors=result,
offices=result2)
elif request.method == 'POST':
print("Add assignment")
doctorID = request.form['doctorID']
officeID = request.form['officeID']
query = 'INSERT INTO officedoctor (doctorID, officeID) VALUES (%s,%s);'
data = (doctorID, officeID)
db.execute_query(db_connection, query, data)
query = "SELECT officedoctor.id, officedoctor.doctorID, officedoctor.officeID, doctor.fname, doctor.lname, office.name FROM officedoctor INNER JOIN doctor ON officedoctor.doctorID = doctor.id INNER JOIN office ON officedoctor.officeID = office.id;"
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template("browse_assignment.j2",
assignments=result,
resultText="Assignment added.")
def update_patient(id):
db_connection = db.connect_to_database()
if request.method == 'GET':
patient_query = 'SELECT id, fname, lname, phoneNumber, street, city, state, zip, dob, weight, doctorID FROM patient WHERE id = %s' % (
id)
patient_result = db.execute_query(db_connection,
patient_query).fetchone()
if patient_result == None:
return "No such patient found!"
doctor_query = 'SELECT id, fname, lname FROM doctor'
doctor_results = db.execute_query(db_connection,
doctor_query).fetchall()
return render_template('patient_update.j2',
doctors=doctor_results,
patient=patient_result)
elif request.method == 'POST':
patient_id = request.form['patient_id']
fname = request.form['fname']
lname = request.form['lname']
phoneNumber = request.form['phoneNumber']
street = request.form['street']
city = request.form['city']
state = request.form['state']
zipco = request.form['zip']
dob = request.form['dob']
weight = request.form['weight']
doctorID = request.form['doctorID']
query = "UPDATE patient SET fname = %s, lname = %s, phoneNumber = %s, street = %s, city = %s, state = %s, zip = %s, dob = %s, weight = %s, doctorID = %s WHERE id = %s"
data = (fname, lname, phoneNumber, street, city, state, zipco, dob,
weight, doctorID, patient_id)
result = db.execute_query(db_connection, query, data)
print(str(result.rowcount) + " row(s) updated")
return redirect('/browse_patient')
def emp_update():
restroom_id = request.form['restroomID']
address = request.form['address']
street = address.split(',')[0]
city = address.split(',')[1]
state = address.split(',')[2]
country = address.split(',')[3]
open = request.form['openHour']
close = request.form['closeHour']
free = request.form['free']
if free == 'T':
free = 1
else:
free = 0
inspected = request.form['lastInspected']
comments = request.form['comment']
employeeID = request.form['employeeID']
db_connection = connect_to_database()
query = 'UPDATE Locations, Restrooms, RestroomsEmployees SET Locations.street = %s, Locations.city = %s, Locations.state = %s, Locations.country = %s, Restrooms.openHour = %s, Restrooms.closeHour = %s, Restrooms.free = %s, RestroomsEmployees.inspectedAt = %s, RestroomsEmployees.comments = %s WHERE Restrooms.restroomID = %s AND Locations.locationID = (SELECT locationID FROM Restrooms WHERE restroomID = %s) AND RestroomsEmployees.restroomID = %s'
data = (street, city, state, country, open, close, free, inspected,
comments, restroom_id, restroom_id, restroom_id)
result = execute_query(db_connection, query, data)
return redirect('/employee_index')
def emp_delete():
restroom_id = request.form['restroomID']
db_connection = connect_to_database()
query = 'DELETE FROM Restrooms WHERE restroomID = %s'
data = (restroom_id, )
result = execute_query(db_connection, query, data)
return redirect('/employee_index')
def cust_delete():
reviewID = request.form["reviewID"]
db_connection = connect_to_database()
query = "DELETE FROM Reviews WHERE reviewID = %s"
data = (reviewID, )
execute_query(db_connection, query, data)
return redirect('/customer_index')
def search_nurse():
db_connection = db.connect_to_database()
if request.method == "POST":
nurse = request.form['nurse']
query = "SELECT * from nurse WHERE nurse.fname LIKE %s OR nurse.lname LIKE %s;"
data = (nurse, nurse)
result = db.execute_query(db_connection, query, data).fetchall()
if len(data) == 0:
query = "SELECT * FROM nurse;"
data = (nurse, nurse)
result = db.execute_query(db_connection, query, data).fetchall()
return render_template('search_nurse.j2', search=result)
return render_template('search_nurse.j2')
def search_doctor():
db_connection = db.connect_to_database()
if request.method == "POST":
doctor = request.form['doctor']
query = "SELECT doctor.id, doctor.fname, doctor.lname, doctor.phoneNumber, doctor.salary, nurse.fname, nurse.lname FROM doctor LEFT JOIN nurse ON doctor.nurseID = nurse.id WHERE doctor.fname LIKE %s OR doctor.lname LIKE %s;"
data = (doctor, doctor)
result = db.execute_query(db_connection, query, data).fetchall()
if len(data) == 0:
query = "SELECT * FROM doctor;"
data = (doctor, doctor)
result = db.execute_query(db_connection, query, data).fetchall()
return render_template('search_doctor.j2', search=result)
return render_template('search_doctor.j2')
def search_manager():
db_connection = db.connect_to_database()
if request.method == "POST":
manager = request.form['manager']
query = "SELECT * from manager WHERE manager.fname LIKE %s OR manager.lname LIKE %s;"
data = (manager, manager)
result = db.execute_query(db_connection, query, data).fetchall()
if len(data) == 0:
query = "SELECT * FROM manager;"
data = (manager, manager)
result = db.execute_query(db_connection, query, data).fetchall()
return render_template('search_manager.j2', search=result)
return render_template('search_manager.j2')
def search_patient():
db_connection = db.connect_to_database()
if request.method == "POST":
patient = request.form['patient']
query = "SELECT patient.id, patient.fname, patient.lname, patient.phoneNumber, patient.street, patient.city, patient.state, patient.zip, patient.dob, patient.weight, doctor.fname, doctor.lname FROM patient LEFT JOIN doctor ON patient.doctorID = doctor.id WHERE patient.fname LIKE %s OR patient.lname LIKE %s;"
data = (patient, patient)
result = db.execute_query(db_connection, query, data).fetchall()
if len(data) == 0:
query = "SELECT * FROM patient;"
data = (patient, patient)
result = db.execute_query(db_connection, query, data).fetchall()
return render_template('search_patient.j2', search=result)
return render_template('search_patient.j2')
def cust_update():
reviewID = request.form["reviewID"]
overall_rating = request.form["overallRating"]
cleanliness = request.form["cleanliness"]
comment = request.form["comment"]
print("reviewID =", reviewID)
print("overallRating =", overall_rating)
print("cleanliness =", cleanliness)
print("comment =", comment)
db_connection = connect_to_database()
query = "UPDATE Reviews SET overallRating = %s, cleanliness = %s, comment = %s WHERE reviewID = %s"
data = (overall_rating, cleanliness, comment, reviewID)
execute_query(db_connection, query, data)
return redirect('/customer_index')
def search_office():
db_connection = db.connect_to_database()
if request.method == "POST":
office = request.form['office']
query = "SELECT office.id, office.name, office.phoneNumber, office.street, office.city, office.state, office.zip, manager.fname, manager.lname FROM office LEFT JOIN manager ON office.managerID = manager.id WHERE office.name LIKE %s;"
data = (office)
result = db.execute_query(db_connection, query, data).fetchall()
print(result)
if len(data) == 0:
query = "SELECT * FROM office;"
data = (office)
result = db.execute_query(db_connection, query, data).fetchall()
return render_template('search_office.j2', search=result)
return render_template('search_office.j2')
def add_new_manager():
db_connection = db.connect_to_database()
if request.method == 'GET':
return render_template('manager_add_new.j2')
elif request.method == 'POST':
print("Add new manager")
fname = request.form['fname']
lname = request.form['lname']
phoneNumber = request.form['phoneNumber']
salary = request.form['salary']
query = 'INSERT INTO manager (fname, lname, phoneNumber, salary) VALUES (%s,%s,%s,%s);'
data = (fname, lname, phoneNumber, salary)
db.execute_query(db_connection, query, data)
query = "SELECT id, fname, lname, phoneNumber, salary FROM manager;"
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template("browse_manager.j2",
managers=result,
resultText="Manager added.")
def cust_login_verify():
first_name = request.form["firstName"]
last_name = request.form["lastName"]
email = request.form["inputEmail"]
db_connection = connect_to_database()
query = "SELECT * FROM Users WHERE firstName = " '%s' " and lastName = " '%s' " and emailAddress = " '%s' ""
data = (first_name, last_name, email)
results = execute_query(db_connection, query, data).fetchone()
print(results)
if results is None:
# Customer not found create new customer and log in
print("inserting new USER")
query = 'INSERT INTO Users (firstName, lastName, emailAddress) VALUES (%s, %s, %s);'
data = (first_name, last_name, email)
execute_query(db_connection, query, data)
return redirect('/customer_index')
else:
# Customer is found and logged in
return redirect('/customer_index')
def emp_auth():
'''Check if Employee is existing or not. If not, create Employee. If existing, update lastLogin'''
first = request.form['firstName']
last = request.form['lastName']
email = request.form['email']
db_connection = connect_to_database()
query = 'SELECT employeeID FROM Employees WHERE firstName = %s AND lastName = %s AND emailAddress = %s'
data = (first, last, email)
result = execute_query(db_connection, query, data).fetchall()
if len(result) == 0:
# Employee not found, INSERT new Employee
query = 'INSERT INTO Employees (firstName, lastName, emailAddress) VALUES (%s, %s, %s);'
data = (first, last, email)
execute_query(db_connection, query, data)
return redirect('/employee_index')
else:
query = 'UPDATE Employees SET lastLogin = current_timestamp() WHERE employeeID = (SELECT employeeID FROM Employees WHERE firstName = %s AND lastName = %s AND emailAddress = %s)'
data = (first, last, email)
execute_query(db_connection, query, data)
return redirect('/employee_index')
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 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 patient():
db_connection = db.connect_to_database()
query = "SELECT patient.id, patient.fname, patient.lname, patient.phoneNumber, patient.street, patient.city, patient.state, patient.zip, patient.dob, patient.weight, doctor.fname, doctor.lname FROM patient LEFT JOIN doctor ON patient.doctorID = doctor.id;"
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template("browse_patient.j2", patients=result)
# https://www.w3resource.com/python-exercises/web-scraping/web-scraping-exercise-8.php
from flask import Flask, render_template, json, request, redirect
import database.db_connector as db
import os
import wikipedia
# Configuration
app = Flask(__name__)
port = int(os.environ.get('PORT', 8545))
db_connection = db.connect_to_database()
@app.route('/')
def root():
# Get the query string in URL
query = request.args.get('q')
# Set default page in case of error
defaultPage = "Puppies"
try:
# Get page from wikipedia based on search query
wikiPage = wikipedia.page(title=query, auto_suggest=False)
print("Exact Page Found. Checking for images from page")
def manager():
db_connection = db.connect_to_database()
query = "SELECT id, fname, lname, phoneNumber, salary FROM manager;"
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template("browse_manager.j2", managers=result)
from flask import Flask, render_template, json, request, redirect, url_for
import os
import database.db_connector as db
import database.db_credentials as crd
# Configuration
app = Flask(__name__)
db_connection = db.connect_to_database(crd.host, crd.user, crd.passwd, crd.db)
# Routes
@app.route('/')
def reroute():
return redirect(url_for('root'))
@app.route('/index')
def root():
return render_template("main.j2")
@app.route('/games')
def games():
query = "SELECT title, player_count, rating, online, publisher, genre, game_id FROM Games;"
results = db.execute_query(db_connection=db_connection, query=query).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;"
def office():
db_connection = db.connect_to_database()
query = "SELECT office.id, office.name, office.phoneNumber, office.street, office.city, office.state, office.zip, manager.fname, manager.lname FROM office LEFT JOIN manager ON office.managerID = manager.id;"
result = db.execute_query(db_connection, query).fetchall()
print(result)
return render_template("browse_office.j2", offices=result)
