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)