def add_new_books():
print("Add new books!\n")
db_connection = connect_to_database()
booklist_len = 0
if request.method == 'GET':
print("get GET \n")
query = 'SELECT author_id, first_name, last_name from authors'
result = execute_query(db_connection, query).fetchall()
print(result)
query2 = 'SELECT publisher_id, company_name from publishers'
result2 = execute_query(db_connection, query2).fetchall()
query5 = 'SELECT isbn from books'
result4 = execute_query(db_connection, query5).fetchall()
booklist_len = len(result4)
return render_template(
'add_new_books.html',
authors=result,
publishers=result2,
font_url1=
"https://fonts.googleapis.com/css?family=Montserrat:200,300,400,500,600,700,800,900",
font_url2=
"https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"
)
elif request.method == 'POST':
print("take user inputs for adding a new book! \n")
isbn = request.form['isbn']
title = request.form['title']
price = request.form['price']
year = request.form['year']
publisher_id = request.form['publisher_id']
book_img = request.form['book_img']
author_id = request.form.getlist('author_id')
print(str(author_id), type(str(author_id)), len(author_id))
print(author_id[0], str(author_id[0]))
if float(price) >= 0 and int(year) >= 0 and int(isbn) >= 0:
query3 = 'INSERT INTO books (isbn, title, price, publisher_id, year, book_img) VALUES (%s,%s,%s,%s,%s,%s)'
data = (isbn, title, price, publisher_id, year, book_img)
execute_query(db_connection, query3, data)
query4 = "SELECT isbn from books"
result3 = execute_query(db_connection, query4).fetchall()
for i in range(len(author_id)):
query5 = 'INSERT INTO books_authors (isbn, author_id) VALUES (%s, %s)'
data2 = (isbn, author_id[i])
execute_query(db_connection, query5, data2)
if booklist_len != len(result3):
flash("You have successfully added new book on the booklist!")
return redirect(url_for('admin'))
else:
flash("please check your input !!")
return redirect(url_for('add_new_books'))
else:
flash(
"Please check your input (positive integer only for price, isbn, and year) "
)
return redirect(url_for('add_new_books'))
def update_order(id):
print('in the update_order function')
db_connection = connect_to_database()
#display existing data about an order
if request.method == 'GET':
order_query = "SELECT Orders.orderID, Orders.orderType, CONCAT(Patients.firstName , ' ' , Patients.lastName) AS Patient, CONCAT(Doctors.firstName , ' ' , Doctors.lastName) AS Doctor, CONCAT(Staff.firstName , ' ' , Staff.lastName) AS Staff, Orders.staffID, Orders.patientID FROM Patients JOIN Orders ON (Patients.patientID = Orders.patientID AND Orders.orderID = %s) LEFT JOIN Doctors ON Orders.doctorID = Doctors.doctorID LEFT JOIN Staff ON Staff.staffID = Orders.staffID"
data = (id, )
order_result = execute_query(db_connection, order_query,
data).fetchone()
staff_query = "SELECT staffID, CONCAT(Staff.firstName , ' ' , Staff.lastName) AS Staff FROM Staff;"
staff_results = execute_query(db_connection, staff_query).fetchall()
if order_result == None:
return "No such Order found!"
return render_template('update_order.html',
staff=staff_results,
order=order_result)
elif request.method == 'POST':
staffID = request.form['staffID']
oldstaffID = request.form['oldstaffID']
orderID = request.form['orderID']
patientID = request.form['patientID']
print("LOOK HERE")
print(staffID == 'None')
print(staffID)
if staffID == oldstaffID:
flash("NOTHING Update! ")
return redirect('/browse_orders')
elif staffID == 'None':
# update orders table
query = "UPDATE Orders SET staffID = NULL WHERE orderID = %s"
data = (orderID, )
result = execute_query(db_connection, query, data)
# check if relation is old and update if it is
qold = 'SELECT staffID, patientID FROM Staff_Patients WHERE staffID = %s and patientID = %s'
dold = (oldstaffID, patientID)
rold = execute_query(db_connection, qold, dold).fetchone()
# delete previous relation from staff_patients table
if rold is not None:
qupd = 'DELETE FROM Staff_Patients WHERE staffID = %s AND patientID = %s'
dupd = (oldstaffID, patientID)
execute_query(db_connection, qupd, dupd)
flash("Updated! " + " Order ID #" + str(orderID))
return redirect('/browse_orders')
else:
# update orders table
query = "UPDATE Orders SET staffID = %s WHERE orderID = %s"
data = (staffID, orderID)
result = execute_query(db_connection, query, data)
# check if relation is old and update if it is
qold = 'SELECT staffID, patientID FROM Staff_Patients WHERE staffID = %s and patientID = %s'
dold = (oldstaffID, patientID)
rold = execute_query(db_connection, qold, dold).fetchone()
# update staff_patients table if relation already exits
if rold is not None:
qupd = 'UPDATE Staff_Patients SET staffID = %s WHERE staffID = %s AND patientID = %s'
dupd = (staffID, oldstaffID, patientID)
execute_query(db_connection, qupd, dupd)
# add new relation to staff_patients table if relation is new
else:
# check if new relation already exits, add if it doesn't
qnew = 'SELECT staffID, patientID FROM Staff_Patients WHERE staffID = %s and patientID = %s'
dnew = (staffID, patientID)
rnew = execute_query(db_connection, qnew, dnew).fetchone()
# add new relation
if rnew is None:
query2 = 'INSERT INTO Staff_Patients (staffID, patientID) VALUES (%s,%s)'
data2 = (staffID, patientID)
execute_query(db_connection, query2, data2)
flash("Updated! " + " Order ID #" + str(orderID))
return redirect('/browse_orders')
def browse_doctors():
print("Browsing all Doctors")
db_connection = connect_to_database()
query = "SELECT * FROM Doctors"
result = execute_query(db_connection, query).fetchall()
return render_template('browse_doctors.html', rows=result)
def deleteCustomer(id):
db_connection = connect_to_database()
query = "DELETE FROM Customers WHERE customerID = %s"
data = (id, )
result = execute_query(db_connection, query, data)
return redirect(url_for('customers'))
def deleteOrders(id):
db_connection = connect_to_database()
query = "DELETE FROM Orders WHERE orderID = %s"
data = (id, )
result = execute_query(db_connection, query, data)
return redirect(url_for('orderProducts'))
def delete_dealership(dealership_id):
db_connection = connect_to_database()
query = "DELETE dealership, dealership_address FROM dealership INNER JOIN dealership_address USING (address_id) WHERE dealership_id = %s"
data = (dealership_id, )
result = execute_query(db_connection, query, data)
return redirect('/')
def delete_vehicle(dealership_id, vehicle_id):
db_connection = connect_to_database()
query = "DELETE FROM vehicle WHERE vehicle_id = %s"
data = (vehicle_id, )
result = execute_query(db_connection, query, data)
return redirect('selectDealership/' + str(dealership_id))
def labelDelete(id):
db_connection = connect_to_database()
query = "DELETE FROM `record_label` WHERE `id` = %s"
data = (id, )
result = execute_query(db_connection, query, data)
return redirect('/labelSearch.html')
def viewClasses():
db_connection = connect_to_database()
query = "SELECT class_name, stat_bonus_name, stat_bonus, class_id FROM classes"
result = execute_query(db_connection, query).fetchall()
return render_template('viewClasses.html', rows=result)
def albumAdd():
if request.method == 'GET':
db_connection = connect_to_database()
query = 'SELECT `record_label`.`name` FROM `record_label`'
label_result = execute_query(db_connection, query).fetchall()
query = 'SELECT `artist`.`name` FROM `artist`'
artist_result = execute_query(db_connection, query).fetchall()
result = {'label_result': label_result, 'artist_result': artist_result}
return render_template('albumAdd.html', rows=result)
elif request.method == 'POST':
db_connection = connect_to_database()
errors = []
# extract data from form
album_name = request.form['albumName']
label_name = request.form['labelName']
release_date = request.form['releaseDate']
artist_name = request.form['artistName']
# get the label id given the label name
data = (label_name, )
query = 'SELECT `record_label`.`id` FROM `record_label` WHERE `record_label`.`name` = %s;'
result = execute_query(db_connection, query, data).fetchall()
try:
label_id = result[0][0]
except IndexError:
errors.append('Invalid label')
artist_keys = [s for s in request.form if 'artistName' in s]
artist_keys.pop()
# get the artist ids given the artist names
data = tuple(
[request.form[s] for s in request.form if 'artistName' in s])
query = 'SELECT `artist`.`id` FROM `artist` WHERE `artist`.`name` = %s'
for artist_key in artist_keys:
query += ' OR `artist`.`name` = %s'
result = execute_query(db_connection, query, data).fetchall()
artist_ids = []
try:
for data_pair in result:
artist_ids.append(data_pair[0])
except IndexError:
errors.append('Invalid artist')
if errors:
return render_template('albumAdd.html', errors=errors)
# insert data
query = ('INSERT INTO `album` '
'(`id`, `name`, `label_id`, `release_date`) '
'VALUES (NULL, %s, %s, %s);')
data = (album_name, label_id, release_date)
result = execute_query(db_connection, query, data)
for artist_id in artist_ids:
query = ('INSERT INTO `album_artist` (`album_id`, `artist_id`) '
'VALUES (LAST_INSERT_ID(), %s);')
data = (artist_id, )
execute_query(db_connection, query, data)
# display search page
query = 'SELECT `album`.`id`, `album`.`name`, `record_label`.`name`, `album`.`release_date` FROM `album`JOIN `record_label` ON `album`.`label_id` = `record_label`.`id` ORDER BY `album`.`name` ASC;'
result = execute_query(db_connection, query).fetchall()
return render_template('albumSearch.html', rows=result)
def artistDelete(id):
db_connection = connect_to_database()
query = "DELETE FROM `artist` WHERE `id` = %s"
data = (id, )
result = execute_query(db_connection, query, data)
return redirect('/artistSearch.html')
def songAdd():
if request.method == 'GET':
db_connection = connect_to_database()
query = 'SELECT `album`.`name` FROM `album`'
album_result = execute_query(db_connection, query).fetchall()
query = 'SELECT `artist`.`name` FROM `artist`'
artist_result = execute_query(db_connection, query).fetchall()
result = {'album_result': album_result, 'artist_result': artist_result}
return render_template('songAdd.html', rows=result)
elif request.method == 'POST':
db_connection = connect_to_database()
errors = []
# extract data from form
song_name = request.form['songName']
album_name = request.form['albumName']
artist_name = request.form['artistName']
# get the album id given the album name
data = (album_name, )
query = 'SELECT `album`.`id` FROM `album` WHERE `album`.`name` = %s;'
result = execute_query(db_connection, query, data).fetchall()
try:
album_id = result[0][0]
except IndexError:
errors.append('Invalid album')
artist_keys = [s for s in request.form if 'artistName' in s]
artist_keys.pop()
# get the artist ids given the artist names
data = tuple(
[request.form[s] for s in request.form if 'artistName' in s])
query = 'SELECT `artist`.`id` FROM `artist` WHERE `artist`.`name` = %s'
for artist_key in artist_keys:
query += ' OR `artist`.`name` = %s'
result = execute_query(db_connection, query, data).fetchall()
artist_ids = []
try:
for data_pair in result:
artist_ids.append(data_pair[0])
except IndexError:
errors.append('Invalid artist')
if errors:
return render_template('songAdd.html', errors=errors)
# insert data
query = ('INSERT INTO `song` (`id`, `name`, `album_id`) '
'VALUES (NULL, %s, %s);')
data = (song_name, album_id)
execute_query(db_connection, query, data)
for artist_id in artist_ids:
query = ('INSERT INTO `song_artist` (`song_id`, `artist_id`) '
'VALUES (LAST_INSERT_ID(), %s);')
data = (artist_id, )
execute_query(db_connection, query, data)
# display search page
query = 'SELECT `song`.`id`, `song`.`name` AS `song_name`, `album`.`name` AS `album_name`, `artist`.`name` AS `artist_name` FROM `song`JOIN `album` on `song`.`album_id` = `album`.`id`JOIN `song_artist` on `song`.`id` = `song_artist`.`song_id`JOIN `artist` ON `song_artist`.`artist_id` = `artist`.`id` ORDER BY song_name ASC;'
result = execute_query(db_connection, query).fetchall()
return render_template('songSearch.html', rows=result)
def songSearch():
if request.method == 'GET':
print("Fetching and rendering song web page")
db_connection = connect_to_database()
query = 'SELECT `song`.`id`, `song`.`name` AS `song_name`, \
`album`.`name` AS `album_name`, \
`artist`.`name` AS `artist_name` \
FROM `song`\
JOIN `album` on `song`.`album_id` = `album`.`id`\
JOIN `song_artist` on `song`.`id` = `song_artist`.`song_id`\
JOIN `artist` ON `song_artist`.`artist_id` = `artist`.`id`\
ORDER BY song_id ASC;'
result = execute_query(db_connection, query).fetchall()
print(result)
result = list(result)
lead = 1
tail = 0
if len(result) > 1:
while lead < len(result) and result[lead][0] == result[tail][0]:
lead += 1
# tail is at the start of the range to condense
# lead is at the end of the range to condense (exclusive)
condensed = list(result[tail])
condensed[3] = [condensed[3]]
for i in range(tail + 1, lead):
condensed[3].append(result[i][3])
result[tail:lead] = [condensed]
result.sort(key=lambda x: x[1])
return render_template('songSearch.html', rows=result)
elif request.method == 'POST':
print("Fetching and rendering song web page post")
db_connection = connect_to_database()
songToSearchFor = request.form['songToSearchFor']
query = "SELECT `song`.`id`, `song`.`name` AS `song_name`,\
`album`.`name` AS `album_name`,\
`artist`.`name` AS `artist_name` \
FROM `song`\
JOIN `album` on `song`.`album_id` = `album`.`id`\
JOIN `song_artist` on `song`.`id` = `song_artist`.`song_id`\
JOIN `artist` ON `song_artist`.`artist_id` = `artist`.`id` \
WHERE `song`.`name` LIKE %s \
ORDER BY song_name ASC"
data = ("%" + songToSearchFor + "%", )
result = execute_query(db_connection, query, data).fetchall()
print(result)
result = list(result)
lead = 1
tail = 0
if len(result) > 1:
while lead < len(result) and result[lead][0] == result[tail][0]:
lead += 1
# tail is at the start of the range to condense
# lead is at the end of the range to condense (exclusive)
condensed = list(result[tail])
condensed[3] = [condensed[3]]
for i in range(tail + 1, lead):
condensed[3].append(result[i][3])
result[tail:lead] = [condensed]
result.sort(key=lambda x: x[1])
return render_template('songSearch.html', rows=result)
def shop_normal():
db_connection = connect_to_database()
print("get GET ! \n")
query = 'SELECT books.title, books.isbn, authors.first_name, authors.last_name, publishers.company_name, books.year, books.price, books.book_img from books INNER JOIN books_authors ON books.isbn = books_authors.isbn INNER JOIN authors ON books_authors.author_id = authors.author_id INNER JOIN publishers ON books.publisher_id = publishers.publisher_id;'
result = execute_query(db_connection, query).fetchall()
if request.method == 'POST':
search_input = request.form['search']
print(search_input)
query2 = 'SELECT books.title, books.isbn, authors.first_name, authors.last_name, publishers.company_name, books.year, books.price, books.book_img from books INNER JOIN books_authors ON books.isbn = books_authors.isbn INNER JOIN authors ON books_authors.author_id = authors.author_id INNER JOIN publishers ON books.publisher_id = publishers.publisher_id WHERE title LIKE "%%{0}%%"; '.format(
search_input)
result2 = execute_query(db_connection, query2).fetchall()
result2 = list(result2)
for i in range(len(result2)):
result2[i] = list(result2[i])
print(result2)
length = len(result2)
index = 0
while index < length:
key = result2[index][1]
adding = ""
index2 = 0
while index2 < length:
if key == result2[index2][1] and index != index2:
adding = ", " + result2[index2][2] + " " + result2[index2][
3]
print(adding)
print("deleting!")
result2[index][3] = str(result2[index][3]) + str(adding)
result2 = result2[:index2] + result2[index2 + 1:]
length -= 1
index2 += 1
index += 1
return render_template(
'shop_normal.html',
book_info=result2,
font_url1=
"https://fonts.googleapis.com/css?family=Montserrat:200,300,400,500,600,700,800,900",
font_url2=
"https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"
)
result = list(result)
for i in range(len(result)):
result[i] = list(result[i])
print(result)
length = len(result)
index = 0
while index < length:
key = result[index][1]
adding = ""
index2 = 0
while index2 < length:
if key == result[index2][1] and index != index2:
adding = ", " + result[index2][2] + " " + result[index2][3]
print(adding)
print("deleting!")
result[index][3] = str(result[index][3]) + str(adding)
result = result[:index2] + result[index2 + 1:]
length -= 1
index2 += 1
index += 1
return render_template(
'shop_normal.html',
book_info=result,
font_url1=
"https://fonts.googleapis.com/css?family=Montserrat:200,300,400,500,600,700,800,900",
font_url2=
"https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"
)
def update_application(id):
db_connection = connect_to_database()
if request.method == 'GET':
#query to populate form for appliation with given id
app_query = 'SELECT * FROM Applications where app_num = %s' % (id)
app_result = execute_query(db_connection, app_query).fetchone()
if app_result == None:
return "No such application found!"
#query to populate shelter dropdown on form
query = 'SELECT shelterID, name from Shelters'
shelters = execute_query(db_connection,query).fetchall()
print(shelters)
#query to populate adopter dropdown on form
query = 'SELECT adopterID, adopterID, first_name, last_name from Adopters'
adopters = execute_query(db_connection,query).fetchall()
print(adopters)
#query to populate counselor dropdown on form
query = 'SELECT counselorID, counselorID, first_name, last_name from Counselors'
counselors = execute_query(db_connection,query).fetchall()
print(counselors)
#query = 'SELECT petID, petID, name from Dogs where adoption_status!="Adopted"'
#pets = execute_query(db_connection,query).fetchall()
#print(pets)
return render_template('update_application.html', shelters=shelters, app=app_result, adopters = adopters, counselors = counselors) #pets = pets)
elif request.method == 'POST':
print('The POST request')
#grabbing info from the form
app_id = request.form['app_id']
adopter_id = request.form['adopter_id']
fname = request.form['fname']
lname = request.form['lname']
shelterID = request.form['shelterID']
counselorID = request.form['counselorID']
if counselorID == "":
counselorID = None
#petID1 = request.form['petID1']
#if petID1 == "":
# petID1 = None
#petID2 = request.form['petID2']
#if petID2 == "":
# petID2 = None
#petID3 = request.form['petID3']
#if petID3 == "":
# petID3 = None
meetGreet = request.form['meetGreet']
numAdults = request.form['numAdults']
numChildren = request.form['numChildren']
numPets = request.form['numPets']
homeType = request.form['homeType']
homeStatus = request.form['homeStatus']
#query to update application
query = "UPDATE Applications SET shelterID= %s, counselorID= %s, meet_greet = %s, num_adults = %s, num_children = %s, num_pets = %s, home_type = %s, home_status = %s WHERE app_num = %s"
data = (shelterID, counselorID, meetGreet, numAdults, numChildren, numPets, homeType, homeStatus, app_id)
result = execute_query(db_connection, query, data)
print(str(result.rowcount) + " row(s) updated")
#also update adopters name if needed
query2 = "UPDATE Adopters SET first_name= %s, last_name= %s WHERE adopterID = %s"
data2 = (fname, lname, adopter_id)
result2 = execute_query(db_connection, query2, data2)
print(str(result2.rowcount) + " row(s) updated")
return redirect('/applications')
def updateCharacter(id):
db_connection = connect_to_database()
if request.method == 'GET':
character_query = 'SELECT char_id, first_name, last_name, strength, dexterity, endurance, intelligence FROM characters WHERE char_id = %s' % (
id)
character_result = execute_query(db_connection,
character_query).fetchone()
guild_query = 'SELECT guild_id, guild_name FROM guilds'
guild_result = execute_query(db_connection, guild_query).fetchall()
class_query = 'SELECT class_id, class_name FROM classes'
class_result = execute_query(db_connection, class_query).fetchall()
spell_query = 'SELECT spell_id, spell_name FROM spells'
spell_result = execute_query(db_connection, spell_query).fetchall()
remove_spell_query = 'SELECT spells.spell_id, spells.spell_name FROM spells INNER JOIN characters_spells ON spells.spell_id = characters_spells.spell_id INNER JOIN characters ON characters_spells.char_id = characters.char_id WHERE characters.char_id = %s ORDER BY spells.spell_id' % (
id)
remove_spell_result = execute_query(db_connection,
remove_spell_query).fetchall()
if character_result == None:
return render_template('notFound.html')
return render_template('updateCharacter.html',
character=character_result,
guilds=guild_result,
classes=class_result,
spells=spell_result,
removeSpell=remove_spell_result)
elif request.method == 'POST':
if request.form['buttonID'] == 'Update Character':
charID = request.form['char_id']
firstName = request.form['first_name']
lastName = request.form['last_name']
strength = request.form['strength']
dexterity = request.form['dexterity']
endurance = request.form['endurance']
intelligence = request.form['intelligence']
guildID = request.form['guilds']
classID = request.form['classes']
#If character has no guild
if (guildID == 'noGuild'):
characterData = (firstName, lastName, strength, dexterity,
endurance, intelligence, None, classID,
charID)
#If character does want to be inserted into a guild
else:
characterData = (firstName, lastName, strength, dexterity,
endurance, intelligence, guildID, classID,
charID)
characterQuery = 'UPDATE characters SET first_name = %s, last_name = %s, strength = %s, dexterity = %s, endurance = %s, intelligence = %s, guild_id = %s, class_id = %s WHERE char_id = %s'
execute_query(db_connection, characterQuery, characterData)
return redirect('/viewCharacters')
elif request.form['buttonID'] == 'Add Spell':
charID = request.form['char_id']
spellID = request.form['spells']
spellQuery = 'INSERT INTO characters_spells (char_id, spell_id) VALUES (%s, %s) ON DUPLICATE KEY UPDATE char_id = %s, spell_id = %s'
spellData = (charID, spellID, charID, spellID)
execute_query(db_connection, spellQuery, spellData)
return redirect('/viewCharacters')
elif request.form['buttonID'] == 'Remove Spell':
charID = request.form['char_id']
removeSpellID = request.form['removeSpell']
removeSpellQuery = 'DELETE FROM characters_spells WHERE char_id = %s AND spell_id = %s'
removeSpellData = (charID, removeSpellID)
execute_query(db_connection, removeSpellQuery, removeSpellData)
return redirect('/viewCharacters')
def test_database_connection():
print("Executing a sample query on the database using the credentials from db_credentials.py")
db_connection = connect_to_database()
query = "SELECT * from bsg_people;"
result = execute_query(db_connection, query);
return render_template('db_test.html', rows=result)
def viewGuilds():
db_connection = connect_to_database()
query = "SELECT guild_name, guild_description, guild_id FROM guilds"
result = execute_query(db_connection, query).fetchall()
return render_template('viewGuilds.html', rows=result)
def delete_employee(dealership_id, employee_id):
db_connection = connect_to_database()
query = "DELETE FROM employees WHERE employee_id = %s"
data = (employee_id, )
result = execute_query(db_connection, query, data)
return redirect('selectDealership/' + str(dealership_id))
def viewSchools():
db_connection = connect_to_database()
query = "SELECT school_name, school_description, school_id FROM schools"
result = execute_query(db_connection, query).fetchall()
return render_template('viewSchools.html', rows=result)
def deleteProduct(id):
db_connection = connect_to_database()
query = "DELETE FROM Products WHERE productID = %s"
data = (id, )
result = execute_query(db_connection, query, data)
return redirect(url_for('products'))
def login():
if current_user.is_authenticated:
return redirect(url_for('home'))
if request.method == 'GET':
return render_template('login.html')
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db_connection = connect_to_database() # connect to db
# get info for specified username
cursor = db_connection.cursor()
cursor.callproc('returnUserInfo', [
username,
])
result = cursor.fetchall()
cursor.close()
# if the user provided a valid username
if result:
# get information about login attempts
last_login_attempt = result[0][
7] # get last login attempt datetime
current_time = datetime.now() # get current datetime
if last_login_attempt is None: # check if it's users first login
last_login_attempt = datetime.min # set first login time to min
difference = current_time - last_login_attempt # calculate the difference
seconds_in_day = 24 * 60 * 60
difference = divmod(
difference.days * seconds_in_day + difference.seconds, 60
) # convert difference to a tuple of difference in minutes and seconds
#ensure they have confirmed their email
if result[0][4] == 0:
flash('Please confirm your email to log in', 'warning')
db_connection.close()
return render_template('login.html')
# if they've failed more than 3 attempts in the last 5 minutes, don't allow login
elif result[0][6] >= 3 and difference[0] < 5:
webapp.logger.warning(
"Third attempt - Login unsuccessful, username: %s",
username)
flash('Too many failed login attempts. Try again later',
'danger')
db_connection.close() # close connection before returning
return render_template('login.html')
# else check validation that user input matched query results - successful login
elif username == result[0][1] and check_password_hash(
result[0][2],
password): # check password against stored hash and salt
# reset login_attempts to 0
query = "UPDATE users SET login_attempts = 0 WHERE user_id = '{}'".format(
result[0][0])
cursor = execute_query(db_connection, query) # run query
cursor.close()
# update last_login_attempt
formatted_date = current_time.strftime('%Y-%m-%d %H:%M:%S')
query = "UPDATE users SET last_login_attempt = '{}' WHERE user_id = '{}'".format(
formatted_date, result[0][0])
cursor = execute_query(db_connection, query) # run query
cursor.close()
#log user in
user = User(user_id=result[0][0],
username=result[0][1],
password=result[0][2],
email=result[0][3])
login_user(user)
session.permanent = True
webapp.logger.info("Login successful, username: %s", username)
flash('You have been logged in!', 'success')
next_page = request.args.get('next')
db_connection.close() # close connection before returning
return redirect(url_for('home'))
# else failed login attempt
else:
# add one to login_attempts
query = "UPDATE users SET login_attempts = '{}' WHERE user_id = '{}'".format(
result[0][6] + 1, result[0][0])
cursor = execute_query(db_connection, query) # run query
cursor.close()
# update last_login_attempt
formatted_date = current_time.strftime('%Y-%m-%d %H:%M:%S')
query = "UPDATE users SET last_login_attempt = '{}' WHERE user_id = '{}'".format(
formatted_date, result[0][0])
cursor = execute_query(db_connection, query) # run query
cursor.close()
webapp.logger.info(
"Login unsuccessful, attempt #%s, username: %s",
result[0][6] + 1, username)
flash('Login Unsuccessful. Please check username and password',
'danger')
db_connection.close() # close connection before returning
return render_template('login.html')
else:
webapp.logger.warning("Login unsuccessful, username not in DB: %s",
username)
flash('Login Unsuccessful. Please check username and password',
'danger')
return render_template('login.html')
def deleteStore(id):
db_connection = connect_to_database()
query = "DELETE FROM Stores WHERE storeID = %s"
data = (id, )
result = execute_query(db_connection, query, data)
return redirect(url_for('stores'))
def register():
if current_user.is_authenticated:
return redirect(url_for('home'))
if request.method == 'GET':
return render_template('accountCreation.html')
if request.method == 'POST':
email = request.form['email']
username = request.form['username']
password = request.form['password']
confirm_password = request.form['confirm_password']
if not complex_password(password):
flash('Password requirements not met', 'danger')
return render_template('accountCreation.html')
if password != confirm_password:
flash('Password confirmation does not match password', 'danger')
return render_template('accountCreation.html')
db_connection = connect_to_database()
# make sure username is unique
query = 'SELECT `username` FROM users'
cursor = execute_query(db_connection, query) # run query
rtn = cursor.fetchall()
cursor.close()
if (any(username in i for i in rtn)):
flash('Username already taken, please try again', 'danger')
db_connection.close() # close connection before returning
return render_template('accountCreation.html')
# make sure email is unique
query = 'SELECT `email` FROM users'
cursor = execute_query(db_connection, query)
rtn = cursor.fetchall()
cursor.close()
if (any(email in i for i in rtn)):
flash('Email already registered, please try again', 'danger')
db_connection.close() # close connection before returning
return render_template('accountCreation.html')
# hash password with random 8 char salt - hash and salt are stored in hashed_password
# in the same string
hashed_password = generate_password_hash(password, salt_length=8)
cursor = db_connection.cursor()
cursor.callproc('addUser', [
username,
hashed_password,
email,
])
db_connection.commit()
cursor.close()
db_connection.close() # close connection before returning
send_confirmation_email(email)
flash(
'Thanks for registering. Please check your email to confirm your email address.',
'success')
return redirect(url_for('login'))
def browse_patients():
print("Browsing all Patients")
db_connection = connect_to_database()
query = "SELECT p.patientID, p.firstName, p.lastName, CONCAT(d.firstName , ' ' , d.lastName) FROM Patients p INNER JOIN Doctors d ON p.primaryDoctorID = d.doctorID"
result = execute_query(db_connection, query).fetchall()
return render_template('browse_patients.html', rows=result)
def author():
print("Fetching author information table")
db_connection = connect_to_database()
query = "SELECT * FROM authors"
result = execute_query(db_connection, query)
print(result)
query = "SELECT authorID FROM authors"
authors = execute_query(db_connection, query)
print(authors)
ids = authors.fetchall()
for id in ids:
print(id)
author_id = request.args.get('id')
print("author id: ", author_id)
search_id = request.args.get('search_id')
print("search id: ", search_id)
if (author_id and search_id is not None):
print("Run both!")
query = "SELECT title FROM books WHERE bookID IN (SELECT bookID FROM authorBooks WHERE authorID = %s)" % author_id
books = execute_query(db_connection, query)
titles = books.fetchall()
for t in titles:
print(t)
print("Selecting author with id ", search_id)
query = "SELECT firstName, lastName FROM authors where authorID=%s" % search_id
names = execute_query(db_connection, query)
for n in names:
print(n)
state = {'id': author_id, 'search_id': search_id}
return render_template('author.html',
state=state,
rows=result,
authors_id=authors,
titles=books,
search=names)
elif (author_id is not None):
query = "SELECT title FROM books WHERE bookID IN (SELECT bookID FROM authorBooks WHERE authorID = %s)" % author_id
books = execute_query(db_connection, query)
titles = books.fetchall()
for t in titles:
print(t)
state = {'id': author_id, 'search_id': search_id}
return render_template('author.html',
state=state,
rows=result,
authors_id=authors,
titles=books)
elif (search_id is not None):
print("Selecting author with id ", search_id)
query = "SELECT firstName, lastName FROM authors where authorID=%s" % search_id
names = execute_query(db_connection, query)
for n in names:
print(n)
state = {'id': author_id, 'search_id': search_id}
return render_template('author.html',
state=state,
rows=result,
authors_id=authors,
search=names)
# if (request.method == 'GET'):
# author_id = request.form.get("id",None)
# print(author_id)
state = {'id': author_id, 'search_id': search_id}
return render_template('author.html',
state=state,
rows=result,
authors_id=authors)
def browse_results():
db_connection = connect_to_database()
query = "SELECT resultID, status, Results.orderID, Results.date, CASE WHEN accessedByDoctor = 1 THEN 'YES' ELSE 'NO' END AS accessedByDoctor, orderType, Patients.lastName FROM Results JOIN Orders ON Results.orderID = Orders.orderID JOIN Patients ON Orders.patientID = Patients.patientID;"
result = execute_query(db_connection, query).fetchall()
return render_template('browse_results.html', rows=result)
def add_application():
db_connection = connect_to_database()
if request.method == 'GET':
#query to populate shelter dropdown
query = 'SELECT shelterID, name from Shelters'
shelters = execute_query(db_connection,query).fetchall()
print(shelters)
#query to populate adopter dropdown
query = 'SELECT adopterID, adopterID, first_name, last_name from Adopters'
adopters = execute_query(db_connection,query).fetchall()
print(adopters)
#query to populate counselor drowpdown
query = 'SELECT counselorID, counselorID, first_name, last_name from Counselors'
counselors = execute_query(db_connection,query).fetchall()
print(counselors)
#query to populate available/pending dogs dropdown
query = 'SELECT petID, petID, name from Dogs where adoption_status!="Adopted"'
pets = execute_query(db_connection,query).fetchall()
print(pets)
return render_template('add_application.html', shelters = shelters, adopters = adopters, counselors = counselors, pets = pets)
elif request.method == 'POST':
print("Adding an application")
date = request.form['appDate']
adopterID = request.form['adopterID']
shelterID = request.form['shelterID']
counselorID = request.form['counselorID']
petID1 = request.form['petID1']
if petID1 == "":
petID1 = None
petID2 = request.form['petID2']
if petID2 == "":
petID2 = None
petID3 = request.form['petID3']
if petID3 == "":
petID3 = None
meet_greet = request.form['meetGreet']
num_adults = request.form['numAdults']
num_kids = request.form['numChildren']
num_pets = request.form['numAnimals']
home_status = request.form['homeStatus']
home_type = request.form['homeType']
db_connection = connect_to_database()
#add adpplication query
query = 'INSERT INTO Applications (app_date, shelterID, counselorID, adopterID, petID1, petID2, petID3, meet_greet, num_adults, num_children, num_pets, home_type, home_status) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
data = (date, shelterID, counselorID, adopterID, petID1, petID2, petID3, meet_greet, num_adults, num_kids, num_pets, home_type, home_status)
result=execute_query(db_connection, query, data)
print(result.lastrowid)
#adding application with pets also results in adding to adoption details
if petID1 != None:
query2 = 'INSERT INTO AdoptionDetails (app_num ,petID, app_status) VALUES (%s,%s,%s)'
data2 = (result.lastrowid, petID1, 'Pending')
execute_query(db_connection,query2,data2)
if petID2 != None:
query3 = 'INSERT INTO AdoptionDetails (app_num ,petID, app_status) VALUES (%s,%s,%s)'
data3 = (result.lastrowid, petID2, 'Pending')
execute_query(db_connection,query3,data3)
if petID3 != None:
query4 = 'INSERT INTO AdoptionDetails (app_num ,petID, app_status) VALUES (%s,%s,%s)'
data4 = (result.lastrowid, petID3, 'Pending')
execute_query(db_connection,query4,data4)
return redirect('/applications')
def browse_staff_patients():
print("Browsing all staff-patient relationships")
db_connection = connect_to_database()
query = "SELECT d.staffID, CONCAT(d.firstName, ' ', d.lastName), p.patientID, CONCAT(p.firstName, ' ', p.lastName) FROM Staff_Patients dp INNER JOIN Staff d ON d.staffID = dp.staffID INNER JOIN Patients p ON p.patientID = dp.patientID"
result = execute_query(db_connection, query).fetchall()
return render_template('browse_staff_patients.html', rows=result)
def update_order(id):
db_connection = connect_to_database()
alerts = ()
# Build Select query to populate form
query = """SELECT DISTINCT order_id, order_date, num_products, total_cost, fname, lname, c.customer_id, o.coupon_id, promotion FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN coupons ON o.coupon_id = coupons.coupon_id
WHERE o.order_id = %s"""
data = (id, )
old_order_data = execute_query(db_connection, query, data).fetchone()
if request.method == "POST":
# Fetch data from the form
orderDate = request.form['orderDate']
numProducts = request.form['numberOfProducts']
cost = request.form['totalCost']
customerID = request.form['customerID']
couponID = request.form['couponID']
# If no coupon is selected
if couponID == 'NULL':
couponID = None
# Run update query
query = "UPDATE orders SET order_date = %s, num_products = %s, total_cost = %s, customer_id = %s, coupon_id = %s WHERE order_id = %s"
data = (orderDate, numProducts, cost, customerID, couponID, id)
execute_query(db_connection, query, data)
alerts = (
"Update successful! Please refresh or return to the order page to see the updated order.",
False)
else:
# The following query is to check if the selected coupon is valid for the selected customer
query = 'SELECT customer_id, coupon_id FROM coupons_customers WHERE customer_id = %s AND coupon_id = %s'
query_params = (customerID, couponID)
result = execute_query(db_connection, query,
query_params).fetchone()
# Check result. If it is None, then an invalid coupon was selected
if result is None:
# If customer can't use the indicated coupon, check to see which coupons they can use
query = """SELECT fname, lname, promotion FROM customers c
LEFT JOIN coupons_customers cc ON c.customer_id = cc.customer_id
LEFT JOIN coupons ON cc.coupon_id = coupons.coupon_id
WHERE c.customer_id = %s"""
query_params = (customerID, )
checkAvailableCoupons = execute_query(db_connection, query,
query_params).fetchall()
# Retrieve the cusomer's name
customerName = checkAvailableCoupons[0][0] + \
' ' + checkAvailableCoupons[0][1]
# If customer does not have any promotions available, then the result will be of length 1
# and None will be present where the promotion should be
if len(checkAvailableCoupons
) == 1 and checkAvailableCoupons[0][2] is None:
alerts = (
f"Error! Order not updated! {customerName} can not apply any coupon to their orders. Please try again.",
True)
else:
# Retrieve all promotions that a customer can use, store it in correctCoupons
correctCoupons = ''
for result in checkAvailableCoupons:
correctCoupons += str(result[2])
correctCoupons += ', '
alerts = (
f"Error! Order not updated! {customerName} can only apply the following coupon(s) to their order: {correctCoupons}. Please try again.",
True)
else:
query = "UPDATE orders SET order_date = %s, num_products = %s, total_cost = %s, customer_id = %s, coupon_id = %s WHERE order_id = %s"
data = [orderDate, numProducts, cost, customerID, couponID, id]
execute_query(db_connection, query, data)
alerts = (
"Update successful! Please refresh or return to the order page to see the updated order.",
False)
# Get all customers except for one from initial query
query = f'SELECT customer_id, fname, lname FROM customers WHERE NOT customer_id = {old_order_data[6]}'
customerResult = execute_query(db_connection, query).fetchall()
# Get all coupons except for one from initial query
if old_order_data[7] is not None:
query = f'SELECT coupon_id, promotion FROM coupons WHERE NOT coupon_id = {old_order_data[7]}'
couponResult = execute_query(db_connection, query).fetchall()
else:
query = f'SELECT coupon_id, promotion FROM coupons WHERE NOT coupon_id is NULL'
couponResult = execute_query(db_connection, query).fetchall()
return render_template("update_order.html",
data=old_order_data,
customers=customerResult,
coupons=couponResult,
alerts=alerts)