def updateUser():
# if user is not already logged in, send them to the login page
if not current_user.is_authenticated:
flash(
f'You are not yet logged in: Please log in to update your information',
'danger')
return redirect(url_for('login'))
# create registration form object
form = UpdateForm()
# if registration form has been validly submitted
if form.validate_on_submit():
# hash the password that the user ended
hashed_pw = bcrypt.generate_password_hash(
form.password.data).decode('utf-8')
db = get_db()
# set up db cursor
mycursor = db.cursor()
# run the query to update the info in the database
query = """UPDATE Users SET userFirstName = %s, userLastName = %s, userStreetAddress = %s, userCity = %s, userState = %s, userZip = %s, userPhoneNumber = %s, userPW = %s WHERE userID = %s;"""
mycursor.execute(
query,
(form.firstName.data, form.lastName.data, form.userStreet.data,
form.userCity.data, form.userState.data, form.userZip.data,
form.userPhone.data, hashed_pw, current_user.id))
db.commit()
mycursor.close()
# display success message if user successfully registered
flash(f'Thank you for updating your information!', 'success')
# render account page
return redirect(url_for('account'))
# get current logged in user info to prepopulate form on update page
db = get_db()
mycursor = db.cursor()
query = f"SELECT userFirstName, userLastName, userStreetAddress, userCity, userState, userZip, userPhoneNumber FROM Users WHERE userID = '{current_user.id}';"
mycursor.execute(query)
userDataTuple = mycursor.fetchall()
#place data from tuple into form
for d in userDataTuple:
form.firstName.data = d[0]
form.lastName.data = d[1]
form.userStreet.data = d[2]
form.userCity.data = d[3]
form.userState.data = d[4]
form.userZip.data = d[5]
form.userPhone.data = d[6]
# if no data has been submitted, display the registration page
return render_template('updateUser.html',
title='Update User Information',
form=form)
def create_Fulfillment(self, userID, requests): self.userID = userID self.requests = requests db = get_db() mycursor = db.cursor() query = """INSERT INTO Fulfillments (uid, transactionDate, transactionTime) VALUES (%s, CURDATE(), CURTIME())""" mycursor.execute(query, (userID,)) db.commit() mycursor.close() # get id for fulfillment that was just added, since this is needed as a foreign key mycursor = db.cursor() query = f"""SELECT LAST_INSERT_ID();""" mycursor.execute(query) fulfillmentID = mycursor.fetchone() mycursor.close() # update the fulfillmentID for all of the requests for request in requests: # add the foreign key to the Requests table mycursor = db.cursor() query = """UPDATE requests SET fID = %s WHERE requestID = %s""" mycursor.execute(query, (fulfillmentID[0], request)) db.commit() mycursor.close() # remove the request from the user's cart mycursor = db.cursor() query = """UPDATE requests SET cartID = NULL WHERE requestID = %s""" mycursor.execute(query, (request,)) db.commit() mycursor.close()
def add_request(self, items, quantities, userID, requestDate, needByDate, specialInstructions): # add request to the Request table db = get_db() mycursor = db.cursor() query = """INSERT INTO Requests(requestDate, needByDate, specialInstructions, uID) VALUES (%s, %s, %s, %s)""" mycursor.execute(query, (requestDate.pop(), needByDate.pop(), specialInstructions.pop(), userID )) db.commit() mycursor.close() # get id for request that was just added, since this is needed as a foreign key mycursor = db.cursor() query = f"""SELECT LAST_INSERT_ID();""" mycursor.execute(query) requestID = mycursor.fetchone() mycursor.close() # iterate over list of item names that was passed as an argument itemListLength = len(items) for i in range(itemListLength): #grab itemID associated with that item name from the Items table mycursor = db.cursor() query = """SELECT itemID FROM Items WHERE itemName = %s""" mycursor.execute(query, (items[i],)) itemID = mycursor.fetchone() mycursor.close() #insert item id, requestid, and quantity into requestedItems table mycursor = db.cursor() query = """INSERT INTO requestedItems (iID, rID, quantity) VALUES (%s, %s, %s)""" mycursor.execute(query, (itemID[0], requestID[0], quantities[i])) db.commit() mycursor.close()
def login():
# if user is already logged in, send them to the homepage
if current_user.is_authenticated:
return redirect(url_for('home'))
# create login form object
form = LoginForm()
# if login form has been validly submitted
if form.validate_on_submit():
db = get_db()
# set up db cursor
mycursor = db.cursor()
# query the Users mySQL table for the userID, email address and password
query = """SELECT userID, userEmail, userPW from Users WHERE userEmail=%s"""
mycursor.execute(query, (form.email.data, ))
user = mycursor.fetchone()
mycursor.close()
# if the user exists, store the info provided by the query in separate variables
if user:
userID = user[0]
email = user[1]
password = user[2]
# then verify that the entered password matches the password stored in the db
if user and bcrypt.check_password_hash(password,
form.password.data):
# if so, create the a user object (this is necessary for Flask-Login)
user = User(userID, email, password)
# call Flask-Login login_user function to create the session for the user
login_user(user, remember=form.remember.data)
# if there is a next parameter in the url, grab it to forward the user to the appropriate name.
next_page = request.args.get('next')
# now that the user has logged in, send her to either the next page or the account page
return redirect(next_page) if next_page else redirect(
url_for('requests'))
# if email address is found but password doesn't match, display error message
else:
flash('Incorrect password.', 'danger')
# if email address is not found, display error message
else:
flash('Email address not found. Have you registered?', 'danger')
return render_template('login.html', title='Login', form=form)
def addItem(self, itemName): # check to see if a given item is in the Items table db = get_db() mycursor = db.cursor() query = """INSERT INTO Items (itemName) VALUES (%s)""" mycursor.execute(query, (itemName,)) # commit the query db.commit() mycursor.close()
def validate_email(self, email):
db = get_db()
mycursor = db.cursor()
query = f"SELECT userEmail from Users WHERE userEmail='{email.data}';"
mycursor.execute(query)
user = mycursor.fetchall()
mycursor.close()
if user:
raise ValidationError(
'Email address already exists in our system. Please enter a unique email address.'
)
def __init__(self): # set up db cursor db = get_db() mycursor = db.cursor() # grab all items in the db query = f"""SELECT itemName FROM items;""" mycursor.execute(query) # convert mysql result (which is in tuple) to be in a standard list # because jquery autocomplete requires data to be in list format self.itemsData = [item[0] for item in mycursor.fetchall()] mycursor.close()
def validate_username(self, username):
db = get_db()
mycursor = db.cursor()
# run query to see if the username is already in the table
query = f"SELECT userName from Users WHERE userName='******';"
mycursor.execute(query)
user = mycursor.fetchall()
mycursor.close()
# display the validation error
if user:
raise ValidationError(
'Username already exists. Please choose a unique username.')
def is_in_items(self, itemName): # check to see if a given item is in the Items table db = get_db() mycursor = db.cursor() query = """SELECT itemName FROM Items WHERE itemName= %s""" mycursor.execute(query, (itemName,)) self.result = mycursor.fetchall() mycursor.close() # if query result is not null, item is in the table if self.result: return True else: return False
def register():
# if user is already logged in, send them to the homepage
if current_user.is_authenticated:
return redirect(url_for('home'))
# create registration form object
form = RegistrationForm()
# if registration form has been validly submitted
if form.validate_on_submit():
# hash the password that the user ended
hashed_pw = bcrypt.generate_password_hash(
form.password.data).decode('utf-8')
db = get_db()
# set up db cursor
mycursor = db.cursor()
# run the query to add the user to the database
query = """INSERT INTO Users (userName, userFirstName, userLastName, userStreetAddress, userCity, userState, userZip, userPhoneNumber, userEmail, userPW) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
mycursor.execute(
query, (form.username.data, form.firstName.data,
form.lastName.data, form.userStreet.data,
form.userCity.data, form.userState.data, form.userZip.data,
form.userPhone.data, form.email.data, hashed_pw))
# commit the query
db.commit()
mycursor.close()
# display success message if user successfully registered
flash(f'Your account has been created. Please login.', 'success')
# render account page
return redirect(url_for('login'))
# if no data has been submitted, display the registration page
return render_template('register.html', title='Register', form=form)
def removeFromCart():
if request.method == 'POST':
# grab the requestID that was posted
requestID = request.form['requestID']
# update the cartID field in the request
db = get_db()
mycursor = db.cursor()
query = """UPDATE requests SET cartID = NULL WHERE requestID = %s"""
mycursor.execute(query, (requestID, ))
db.commit()
mycursor.close()
# display successmessage to user
flash(f'The request was removed from your cart.', 'success')
# send the user back to the requests page
return redirect(url_for('cart'))
def addToCart():
if request.method == 'POST':
# grab the requestID that was posted
requestID = request.form['requestID']
# update the cartID field in the request
db = get_db()
mycursor = db.cursor()
query = """UPDATE requests SET cartID = %s WHERE requestID = %s"""
mycursor.execute(query, (current_user.id, requestID))
db.commit()
mycursor.close()
# display successmessage to user
flash(f'The request was added to your cart.', 'success')
# send the user back to the requests page
return redirect(url_for('requests'))
def load_user(user_id): # set up db cursor db = get_db() mycursor = db.cursor() # see if the user email exists in the db. grab email address and password query = f"SELECT userID, userEmail, userPW from Users WHERE userID=%s;" mycursor.execute(query,(user_id,)) user = mycursor.fetchone() mycursor.close() if user: # grab the email address and password from the query id = user[0] email = user[1] password = user[2] # create new user object userObj = User(id, email, password) return userObj # if user is not found, return none return user
def get_cart_requests(self, userID):
# grab the zip code that was passed as an argument
self.userID = userID
# create a new dictionary to store all of the open requests
self.cartRequestsDict = {};
# set up db cursor
db = get_db()
mycursor = db.cursor()
# run query to grab open requets
query = """SELECT r.requestID, u.userName, u.userCity, u.userState, u.userZip, r.needByDate, r.specialInstructions, i.itemID, i.itemname, ri.quantity, r.cartID
FROM users u
INNER JOIN requests r ON u.userID = r.uID
INNER JOIN requestedItems ri ON r.requestID = ri.rID
INNER JOIN items i ON ri.iID = i.itemID
WHERE r.fID IS NULL AND r.cartID = %s
ORDER BY r.requestID ASC"""
mycursor.execute(query,(self.userID,))
requestsData = mycursor.fetchall()
mycursor.close()
# if any open requests are returned from the query
if requestsData:
# iterate over the open requets
for row in requestsData:
# if the requestID already exists in openrRequestsDict
if row[0] in self.cartRequestsDict.keys():
# create a new dictionary to store information about the additional item in the request
invDict = {'itemID': row[7], 'itemName': row[8], 'quantity': row[9]}
# append the new dictionary to the list of items already associated with that requestID
self.cartRequestsDict[row[0]]['items'].append(invDict)
# if the requestID doesn't already exist in openRequestsDict
else:
# this list will hold all of the items associated with the request
items = []
# create a new dictionary to store each element of this requestID
rowDict = {'userName': row[1], 'userCity': row[2], 'userState': row[3], 'userZip': row[4], 'needByDate': row[5], 'specialInstructions': row[6], 'items': items}
# create a new dictionary to store information about the first item associated with this requestID
invDict = {'itemID': row[7], 'itemName': row[8], 'quantity': row[9]}
# append the dictionary of items to the items list in rowDict
items.append(invDict);
# associate the items list with rowDict
rowDict.update({'items': items})
# add rowDict to the requests dictionary using requestsID as the key
self.cartRequestsDict[row[0]] = rowDict
# return the dictionary of all open requests
return self.cartRequestsDict
def account():
# if user is not already logged in, send them to the registration page
if not current_user.is_authenticated:
flash(f'You are not yet logged in: Please log in to access this page.',
'danger')
return redirect(url_for('login'))
#get acct info from server
db = get_db()
# set up db cursor
mycursor = db.cursor()
# run the query to add the user to the database
query = f"SELECT userName, userFirstName, userLastName, userStreetAddress, userCity, userState, userZip, userPhoneNumber, userEmail FROM Users WHERE userId = '{current_user.id}';"
mycursor.execute(query)
userTuple = mycursor.fetchall()
for u in userTuple:
userInfo = {
'userName': u[0],
'userFirstName': u[1],
'userLastName': u[2],
'userStreetAddress': u[3],
'userCity': u[4],
'userState': u[5],
'userZip': u[6],
'userPhoneNumber': u[7],
'userEmail': u[8]
}
mycursor.close()
# get data from server for requests by userId of logged in user
#get requestID, needByDate, requestDate, fulfilled, count
#with count = number of items associated with request
db = get_db()
# set up db cursor
mycursor = db.cursor()
query = f"SELECT requestID, requestDate, needByDate, COUNT(*), fID FROM Requests JOIN requestedItems ON Requests.requestID = RequestedItems.rID WHERE uID ='{current_user.id}' GROUP BY requestID;"
mycursor.execute(query)
requestTuple = mycursor.fetchall()
requests = []
for r in requestTuple:
if r[4] == None:
fulfilled = False
else:
fulfilled = True
requests.append({
'requestID': r[0],
'requestDate': r[1],
'needByDate': r[2],
'count': r[3],
'fulfilled': fulfilled
})
mycursor.close()
# get data from server for requests fulfilled by userId of logged in user
#select RequestID, user first name, user last name, user phone, needByDate, fulfillmentDate
db = get_db()
# set up db cursor
mycursor = db.cursor()
query = f"SELECT fulfillmentID, transactionDate FROM Fulfillments WHERE uID ='{current_user.id}';"
mycursor.execute(query)
fulfillTuple = mycursor.fetchall()
fulfillments = []
for f in fulfillTuple:
fulfillments.append({'fulfillmentID': f[0], 'transactionDate': f[1]})
mycursor.close()
return render_template('account.html',
title='Account',
userInfo=userInfo,
requests=requests,
fulfillments=fulfillments)
def displayFulfillment():
if request.method == 'POST':
##SQL for deleting request
db = get_db()
# set up db cursor
mycursor = db.cursor()
# run the query to update the info in the database
query = f"UPDATE Requests SET fID = NULL WHERE fID = '{request.form.get('fulfillmentID')}';"
mycursor.execute(query)
db.commit()
mycursor.close()
db = get_db()
# set up db cursor
mycursor = db.cursor()
# run the query to delete the info in the database
query = f"DELETE FROM Fulfillments WHERE fulfillmentID = '{request.form.get('fulfillmentID')}';"
mycursor.execute(query)
db.commit()
mycursor.close()
##put sql query here to delete request by fulfillmentID
flash(f'Fulfillment Deleted', 'success')
return redirect(url_for('account'))
##USE FULFILLMENT ID TO MAKE QUERY
##query DB here to get all fulfillment data
db = get_db()
# set up db cursor
mycursor = db.cursor()
query = f"SELECT fulfillmentID, transactionDate FROM Fulfillments WHERE fulfillmentID ='{request.args.get('fulfillmentID')}';"
mycursor.execute(query)
fulfillTuple = mycursor.fetchall()
fulfillmentData = {}
for f in fulfillTuple:
fulfillmentData = {'fulfillmentID': f[0], 'fulfillDate': f[1]}
mycursor.close()
#second query that gives all request and assoc user info for each request fulfilled on that fulfillment
db = get_db()
# set up db cursor
mycursor = db.cursor()
requestData = []
#query for request info with associated user data for the user who made that request
query = f"SELECT Requests.requestID, Users.userFirstName, Users.userLastName, Users.userStreetAddress, Users.userCity, Users.userState, Users.userZip, Users.userPhoneNumber, Users.userEmail, Requests.needByDate, Requests.specialInstructions FROM Requests JOIN Users ON Requests.uID = Users.userID WHERE Requests.fID ='{request.args.get('fulfillmentID')}';"
mycursor.execute(query)
requestDetailsTuple = mycursor.fetchall()
mycursor.close()
for d in requestDetailsTuple:
details = []
itemDeets = []
combined = []
details.append({
'requestID': d[0],
'firstName': d[1],
'lastName': d[2],
'userStreet': d[3],
'userCity': d[4],
'userState': d[5],
'userZip': d[6],
'userPhone': d[7],
'userEmail': d[8],
'needByDate': d[9],
'specialInstructions': d[10]
})
db = get_db()
mycursor = db.cursor()
#for each request, query for item info on all items within that request
query = f"SELECT Items.itemName, RequestedItems.quantity FROM RequestedItems JOIN Items ON RequestedItems.iID = Items.itemID WHERE RequestedItems.rID = '{d[0]}';"
mycursor.execute(query)
itemDetailsTuple = mycursor.fetchall()
mycursor.close()
for i in itemDetailsTuple:
itemDeets.append({'itemName': i[0], 'quantity': i[1]})
combined.append(details)
combined.append(itemDeets)
requestData.append(combined)
return render_template('displayFulfillment.html',
title='Your Request',
fulfillmentData=fulfillmentData,
requestData=requestData)
def displayRequest():
if request.method == 'POST':
##SQL for deleting request
db = get_db()
# set up db cursor
mycursor = db.cursor()
# run the query to delete the info in the database
query = f"DELETE FROM RequestedItems WHERE rID = '{request.form.get('requestID')}';"
mycursor.execute(query)
db.commit()
mycursor.close()
db = get_db()
# set up db cursor
mycursor = db.cursor()
# run the query to delete the info in the database
query = f"DELETE FROM Requests WHERE requestID = '{request.form.get('requestID')}';"
mycursor.execute(query)
db.commit()
mycursor.close()
flash(f'Request Deleted', 'success')
return redirect(url_for('account'))
##USE REQUEST ID TO MAKE QUERY
##query DB here to get all item names and item quantities assoc with that req
db = get_db()
# set up db cursor
mycursor = db.cursor()
query = f"SELECT Items.itemName, RequestedItems.quantity FROM RequestedItems JOIN Items ON RequestedItems.iID = Items.itemID WHERE RequestedItems.rID = '{request.args.get('requestID')}';"
mycursor.execute(query)
itemTuple = mycursor.fetchall()
items = []
for i in itemTuple:
items.append({'itemName': i[0], 'quantity': i[1]})
mycursor.close()
##USE REQUEST ID TO MAKE QUERY
##query DB here to get all request info associated with that request ID
db = get_db()
# set up db cursor
mycursor = db.cursor()
query = f"SELECT requestID, requestDate, needByDate, specialInstructions FROM Requests WHERE requestID ='{request.args.get('requestID')}';"
mycursor.execute(query)
requestDataTuple = mycursor.fetchall()
requestData = {}
for d in requestDataTuple:
requestData = {
'requestID': d[0],
'requestDate': d[1],
'needByDate': d[2],
'instructions': d[3]
}
mycursor.close()
return render_template('displayRequest.html',
title='Your Request',
items=items,
requestData=requestData)