def importRoutesList(request, filename, fileType, delete, header):
print(type(request.files['users']))
xlFile = pd.ExcelFile(request.files['users'])
sheets = xlFile.sheet_names
dfDict = pd.read_excel(request.files['users'], sheet_name=sheets, header=header)
conn.execute(users.update().where(and_(users.c.foodBankId==g.user.id, users.c.role=="RECIEVER")).values(inSpreadsheet=0))
for key in dfDict:
df = dfDict[key]
for index, row in df.iterrows():
print(df.columns)
if type(row['First Name']) != float:
if type(row['Last Name']) != float:
fullName = row['First Name'] + " " + row['Last Name']
else:
fullName = row['First Name']
if conn.execute(users.select().where(users.c.name == fullName)).fetchone() is not None:
conn.execute(users.update().where(users.c.name == fullName).values(inSpreadsheet=1))
else:
conn.execute(users.insert(),
name=str(row['First Name']) + " " + str(row['Last Name']),
email="",
address=row['Address 1'],
address2=row['Apt'],
role="RECIEVER",
instructions=row['Notes'],
cellPhone=row['Phone Number'],
zipCode=row['Zip'],
city=row['City'],
state=row['State'],
householdSize=-1,
inSpreadsheet=1,
foodBankId=getFoodBank(row['Address 1']))
if delete:
conn.execute(users.delete().where(and_(users.c.foodBankId==g.user.id, users.c.role=="RECIEVER", users.c.inSpreadsheet==0)))
def register():
if request.method == "POST":
form = request.form.to_dict()
name = fetch_delete('name', form)
birthday = datetime.strptime(fetch_delete('birthday', form), "%Y-%m-%d")
email = fetch_delete('email', form)
password = fetch_delete('password', form)
confirm = fetch_delete('confirm', form)
address = fetch_delete('address', form)
zipCode = fetch_delete('zipCode', form)
cellPhone = fetch_delete('cell', form)
homePhone = fetch_delete('homePhone', form)
instructions = fetch_delete('instructions', form)
restrictions = []
for restriction in dietaryRestrictions:
if restriction in request.form:
restrictions.append(restriction)
error = ""
if conn.execute(users.select().where(users.c.email == email)).fetchone() is not None:
error += '\nUser {} is already registered.'.format(email)
if error == "":
password_hash = generate_password_hash(password)
print("Length of password hash:" + str(len(password_hash)))
conn.execute(users.insert(), name=name, birthday=birthday, email=email, password=password_hash, address=address,
role="RECIEVER", instructions=instructions, cellPhone=cellPhone, homePhone=homePhone,
zipCode=zipCode, completed=0, foodBankId=getFoodBank(address), lastDelivered=datetime.today(), restrictions=dumps(restrictions))
user_id = conn.execute(users.select().where(
users.c.email == email)).fetchone().id
for key in list(form):
if ('name' not in key and 'race' not in key):
form.pop(key, None)
# Get list of keys
keys = [*form]
for i in range(0, len(keys), 2):
conn.execute(family_members.insert(), user=user_id,
name=form[keys[i]], race=form[keys[i + 1]])
return redirect(url_for('auth.login'))
else:
return render_template('auth/register.html', title='Register', dietaryRestrictions=dietaryRestrictions, error=error)
return render_template('auth/register.html', title='Register', dietaryRestrictions=dietaryRestrictions, error="")
def volunteerregister():
foodBanks = conn.execute(
select([users.c.name], whereclause=users.c.role == "ADMIN")).fetchall()
if request.method == "POST":
print("Data: " + str(request.form))
email = request.form['email']
name = request.form['name']
password = request.form['password']
confirm = request.form['confirm']
address = request.form['address']
zipCode = request.form['zipCode']
cellPhone = request.form['cell']
homePhone = request.form['homePhone']
foodBank = request.form['organization']
volunteerRole = request.form['volunteerRole']
# kinda proud of how clean this line is ngl
foodBankId, foodBankEmail = tuple(conn.execute(
select([users.c.id, users.c.email]).where(users.c.name == foodBank)).fetchone())
dayValues = {}
error = ""
# TODO: find error stuff
if error == "":
password_hash = generate_password_hash(password)
conn.execute(users.insert(), email=email, name=name, password=password_hash, address=address,
role="VOLUNTEER", cellPhone=cellPhone, homePhone=homePhone,
zipCode=zipCode, completed=0, approved=False, foodBankId=foodBankId, volunteerRole=volunteerRole)
send_new_volunteer_request_notification(foodBankEmail, name)
return redirect(url_for('auth.login'))
else:
flash(error)
data = {
'email': email,
'address': address,
'name': name,
'cellPhone': cellPhone,
'homePhone': homePhone,
'zipCode': zipCode,
}
return render_template('auth/volunteer-register.html', title='Register', data=data)
data = {'email': '', 'address': '', 'firstName': '',
'lastName': '', 'homePhone': '', 'zipCode': ''}
return render_template('auth/volunteer-register.html', title='Register', data=data, foodBanks=foodBanks)
def addUsersFromDf(df, disabled):
disabledDate = date.today()
for index, row in df.iterrows():
if row['First Name'].lower() == "removal":
disabledDate = row['Last Name'].date()
continue
# This checks to make sure email is not nan
if 'Email' in row.keys() and (type(row['Email']) == str) and not row['Email']=="" and not row['Email']=="*****@*****.**":
emailUser = conn.execute(users.select().where(users.c.email == row['Email'])).fetchone()
if emailUser is not None:
print("Skipping " + str(row) + " because of a duplicate email")
conn.execute(users.update().where(users.c.id == emailUser.id).values(inSpreadsheet=1, disabledDate=disabledDate, disabled=disabled))
continue
else:
nameUser = conn.execute(users.select().where(users.c.name == betterStr(row['First Name']) + " " + betterStr(row['Last Name']))).fetchone()
if nameUser is not None:
print("Found duplicate name in " + str(row))
conn.execute(users.update().where(users.c.id == nameUser.id).values(inSpreadsheet=1, disabledDate=disabledDate, disabled=disabled))
continue
if 'state' not in row.keys():
state = 'WA'
else:
state = row['state']
if 'Household Size' not in row.keys():
hh_size = -1
else:
hh_size = row['Household Size']
conn.execute(users.insert(),
name=betterStr(row['First Name']) + " " + betterStr(row['Last Name']),
email=betterStr(row['Email']),
address=betterStr(row['Address']),
address2=betterStr(row['Apt']),
role="RECIEVER",
instructions=betterStr(row['Notes']),
cellPhone=betterStr(row['Phone']),
zipCode=betterStr(row['Zip']),
city=betterStr(row['City']),
state=state,
householdSize=hh_size,
inSpreadsheet=1,
foodBankId=g.user.id,
disabled=disabled,
disabledDate=disabledDate)
def upload_data():
message = ''
if request.method == "GET":
return render_template('upload_data.html', title='All Users', message=message)
if 'name' in request.form:
form = request.form.to_dict()
name = fetch_delete('name', form)
birthday = datetime.strptime(fetch_delete('birthday', form), "%Y-%m-%d")
email = fetch_delete('email', form)
password = fetch_delete('password', form)
confirm = fetch_delete('confirm', form)
address = fetch_delete('address', form)
zipCode = fetch_delete('zipCode', form)
cellPhone = fetch_delete('cell', form)
homePhone = fetch_delete('homePhone', form)
instructions = fetch_delete('instructions', form)
restrictions = []
error = ""
if (not email == "") and (not email=="*****@*****.**") and conn.execute(users.select().where(users.c.email == email)).fetchone() is not None:
error += '\nUser {} is already registered.'.format(email)
if error == "":
conn.execute(users.insert(), name=name, birthday=birthday, email=email, address=address,
role="RECIEVER", instructions=instructions, cellPhone=cellPhone, homePhone=homePhone,
zipCode=zipCode, completed=0, foodBankId=getFoodBank(address), lastDelivered=datetime.now(), restrictions=dumps(restrictions))
user_id = conn.execute(users.select().where(
users.c.email == email)).fetchone().id
for key in list(form):
if ('name' not in key and 'race' not in key):
form.pop(key, None)
# Get list of keys
keys = [*form]
for i in range(0, len(keys), 2):
conn.execute(family_members.insert(), user=user_id,
name=form[keys[i]], race=form[keys[i + 1]])
setCoords(environ['GOOGLE_API'])
else:
message = "Successfully uploaded the spreadsheet!"
try:
file = request.files.get('users')
filename = file.filename
splitname = filename.split(".")
fileType = splitname[len(splitname) - 1]
print(request.form)
delete = 'delete-checkbox' in request.form.keys()
header = int(request.form['header'])
disabledUsers = 'disabled-checkbox' in request.form.keys()
print("Form: " + str(request.form))
if request.form['spreadsheet-type'] == 'master-spreadsheet':
importMasterList(request, filename, fileType, delete, header, disabledUsers)
elif request.form['spreadsheet-type'] == 'routes-spreadsheet':
importRoutesList(request, filename, fileType, delete, header)
elif request.form['spreadsheet-type'] == 'family-member-data':
importFamilyMemberData(request, filename, fileType, delete, header)
setCoords(environ['GOOGLE_API'])
cities = ['Tacoma', 'University Place', 'Parkland', 'Spanaway', 'Lakewood', 'Puyallup', 'Fife', 'Federal Way', 'Algona', 'Pacific', 'Joint Base Lewis-McChord', 'Steilacoom']
disableOutOfRange(cities)
except Exception as e:
message = "Something went wrong while uploading the spreadsheet. You can view the documentation for correct formatting <a href='https://jaredgoodman03.github.io/Minerva-docs/admin-instructions#file-upload'> here. </a> Here's the error message, so you can figure out what's wrong: <br> <code> " + str(e) + "</code>"
return render_template('upload_data.html', title='All Users', message=message)