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