def retrieveSavedSchools(email): # open the userInformation workbook wb = xlrd.open_workbook('userInformation.xls') # open the userInformation worksheet ws = wb.sheet_by_name('userInformation') # create schoolList schoolList = [] # gives row index the email is stored in in the userInformation database row = findEmailInDB(email) # if email does not exist in database if row == -1: # no record, no sorting return False # retrieve number of schools stored in that record numOfSchools = int(ws.cell(row + 1, 0).value) # for each school in that record for i in range(numOfSchools): # append that school into schoolList schoolList.append(ws.cell(row + 1, i + 1).value) return schoolList
def saveSchool(email, schoolName, savedDate): wb = xlrd.open_workbook('userInformation.xls') ws = wb.sheet_by_name('userInformation') newWB = copy(wb) newWS = newWB.get_sheet(0) # gives the row index where that email is stored row = findEmailInDB(email) # if row == -1, the email is not in the database if row == -1: # cannot save that school return False # retrieve number of schools stored in that record numOfSchools = int(ws.cell(row + 1, 0).value) # increment the number of schools saved numOfSchools += 1 # write the new number of schools saved to the record newWS.write(row + 1, 0, numOfSchools) # at the matching column index, write the new school and its saved date newWS.write(row + 1, numOfSchools, schoolName) newWS.write(row + 2, numOfSchools, savedDate) # move one cell to the right and write 'end' to it newWS.write(row + 1, numOfSchools + 1, 'end') newWS.write(row + 2, numOfSchools + 1, 'end') newWB.save('userInformation.xls') # school is successfully saved return True
def login(): # create the form object form = LoginForm() # validate user input if form.validate_on_submit(): # all form data is stored in the request object flask auto creates # read in the email email = request.form['email'] # returns the row index the email is located in in the userInformation database, if found emailRow = findEmailInDB(email) # an actual row index got returned if emailRow > 0: # read in the password password = request.form['password'] # make sure that the password matches that paired with that email in database matches = passwordMatchesThatPairedWithEmailInDB( password, emailRow) if matches == True: global log_in log_in = True global global_email global_email = email global global_userSavedSchoolList global_userSavedSchoolList = retrieveSavedSchools(global_email) # both email and password are correct, redirect to initial UI page return redirect(url_for('savedlist')) # render template on html and form return render_template('login.html', form=form)
def writeNewUserToDB(email, password, postalCode): # if email already exists in the database, not a new user # cannot create a new record for that user # return false if (findEmailInDB(email) > 0): return False # open the userInformation workbook wb = xlrd.open_workbook('userInformation.xls') # make a copy of the userInformation workbook newWB = copy(wb) # use the copy to open the worksheet newWS = newWB.get_sheet(0) # open the userInformation worksheet ws = wb.sheet_by_name('userInformation') # read the number of records stored from this cell numOfRecords = int(ws.cell(0, 1).value) # calculate the index of the row to write to row = 2 + (numOfRecords*4) # write the email newWS.write(row, 0, email) # write the password newWS.write(row, 1, password) # if the postal code is an empty string if postalCode == '': # write postal code as 'null' newWS.write(row, 2, 'null') # else, write the actual postal code else: newWS.write(row, 2, postalCode) # set no of schools saved to 0 newWS.write(row+1, 0,0) # increment the number of records and write it to the database newWS.write(0, 1, numOfRecords + 1) # save the workbook newWB.save('userInformation.xls') return True
def retrievePostalCode(email): # open the userInformation workbook wb = xlrd.open_workbook('userInformation.xls') # open the userInformation worksheet ws = wb.sheet_by_name('userInformation') # gives row index the email is stored in in the userInformation database row = findEmailInDB(email) # if email does not exist in database if row == -1: # no record, no sorting return False # retrieve postalcode postalCode = (ws.cell(row, 2).value) return postalCode
def deleteSavedSchool(email, schoolName): # make a copy of the workbook and make changes to it wb = xlrd.open_workbook('userInformation.xls') ws = wb.sheet_by_name('userInformation') newWB = copy(wb) newWS = newWB.get_sheet(0) # gives the row index where the email is stored row = findEmailInDB(email) # if email does not exist in the database if row == -1: # cannot delete return False # retrieve number of schools stored in that record numOfSchools = int(ws.cell(row + 1, 0).value) # decrement the number of schools saved numOfSchools -= 1 # write the new number of schools saved to the record newWS.write(row + 1, 0, numOfSchools) # initialize col col = 1 # initialize sn sn = ws.cell(row + 1, col).value # while we have not reached 'end' while (sn != 'end'): # compare sn with schoolName if sn == schoolName: break else: col += 1 sn = ws.cell(row + 1, col).value # if sn == 'end' if (sn == 'end'): print('The school is not saved!') return False # increment col col += 1 # get the next sn sn = ws.cell(row + 1, col).value sd = ws.cell(row + 2, col).value # loop through the remaining cells till 'end' and shift them left by one column each while (sn != 'end'): # shift the school name left by one cell newWS.write(row + 1, col - 1, sn) # shift the saved date left by one cell newWS.write(row + 2, col - 1, sd) # move on to the next school col += 1 sn = ws.cell(row + 1, col).value sd = ws.cell(row + 2, col).value # shift 'end' left by one cell newWS.write(row + 1, col - 1, 'end') newWS.write(row + 2, col - 1, 'end') newWS.write(row + 1, col, '') newWS.write(row + 2, col, '') newWB.save('userInformation.xls') # removal successful return True