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 edit_users(userId):
user = conn.execute(users.select().where(users.c.id == userId)).fetchone()
if request.method == "GET":
return render_template('edit_user.html', user=user)
else:
print(request.form.to_dict())
address = request.form['address']
latitude = user['latitude']
longitude = user['longitude']
if address != user['formattedAddress']:
API_KEY = environ['GOOGLE_API']
googleWrapper = geopy.geocoders.GoogleV3(api_key=API_KEY)
coords = googleWrapper.geocode(query=request.form['address'],
timeout=100)
address = coords[0]
latitude = coords[1][0]
longitude = coords[1][1]
disabled = user['disabled'] or 'disable' == request.form[
'action-on-exit']
conn.execute(users.update().where(users.c.id == userId).values(
name=request.form['name'],
formattedAddress=address,
address2=request.form['address2'],
email=request.form['email'],
cellPhone=request.form['cellPhone'],
instructions=request.form['instructions'],
latitude=latitude,
longitude=longitude,
disabled=disabled))
return redirect('/all_users#card-' + str(user['id']))
def makeAllEmailsMailinator():
userList = conn.execute(users.select()).fetchall()
for user in userList:
if not user['email'] == None and not '@mailinator.com' in user['email']:
newEmail = user['name'].replace(' ', '') + "@mailinator.com"
conn.execute(users.update().where(users.c.id == user.id).values(
email=newEmail))
def dashboard():
# Uncomment this line for testing. This gives everyone mailinator emails,
# so that you don't accidentally send them conformiation emails while testing.
#makeAllEmailsMailinator()
if request.method == "GET" and "claimRoute" in request.args.keys():
print("Getting next route...")
route = getNextRoute(g.user.id, g.user.foodBankId)
print("route: " + str(route))
#conn.execute(users.update().where(users.c.id==g.user.id).values(ordering=dumps(route)))
return redirect('/dashboard')
if request.method == "POST":
firstKey = next(request.form.keys())
userId = int(firstKey)
conn.execute(users.update().where(users.c.id == userId).values(
lastDelivered=datetime.now()))
# If you refresh the page and resend data, it'll send 2 confirmation emails. This if statement prevents that.
email = conn.execute(
select([users.c.email]).where(users.c.id == userId)).fetchone()[0]
send_recieved_notification(email)
routeId = conn.execute(
select([routes.c.id
]).where(routes.c.volunteerId == g.user.id)).fetchone()
if routeId == None:
return render_template("dashboard.html", users=[], google_maps="")
else:
routeId = routeId[0]
userList = getUsers(routeId)
checkedIn = g.user.checkedIn == str(date.today())
return render_template("dashboard.html",
users=userList,
approved=g.user.approved,
google_maps=google_maps_qr.make_url(userList))
def importFamilyMemberData(request, filename, fileType, delete, header):
df = pd.DataFrame()
conn.execute(users.update().where(and_(users.c.foodBankId==g.user.id, users.c.role=="RECIEVER")).values(inSpreadsheet=0))
if (fileType == 'csv'):
df = pd.read_csv(request.files['users'], header=header, keep_default_na=False)
else:
print(dir(request.files['users']))
df = pd.read_excel(request.files['users'].read(), sheet_name="Master list", header=header, keep_default_na=False)
df = df.dropna(thresh=2)
print("asdf")
for index, row in df.iterrows():
row_rp = conn.execute(users.select().where(users.c.name == betterStr(row['First Name']) + " " + betterStr(row['Last Name']))).fetchone()
if row_rp == None: # skip people that have been deleted
continue
for x in range(1, 16):
race = row['Person ' + str(x) + ' Race/Ethnicity']
if race == None or race == '':
break
race = race.split(' & ')
dob = row['Person ' + str(x) + ' date of birth']
print('Data: ' + str((row['Person ' + str(x) + ' date of birth'])))
if type(dob) == str: # This usually happens when someone only enters the last 4 digits of a date, which breaks the date formatting, so we'll just guess if it's post or pre 2000
if int(dob[-2:]) < 30: # Using 30 for future proofing, if it's post-2030 why are you still using this software
dob = dob[:-4] + '20' + dob[-2:]
else:
dob = dob[:-4] + '19' + dob[-2:]
print(dob)
dob = datetime.strptime(dob, '%m/%d/%Y')
dob = dob.date()
conn.execute(family_members.insert().values(user=row_rp.id, dob=dob, race=dumps(race)))
def disableOutOfRange(cities):
usersList = conn.execute(users.select().where(and_(users.c.foodBankId==g.user.id, users.c.disabled==False, users.c.role=="RECIEVER")))
for user in usersList:
disable = True
for city in cities:
if city in user.formattedAddress:
disable = False
if disable:
conn.execute(users.update().where(users.c.id==user.id).values(disabled=True, disabledDate=date.today()))
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 dashboard():
# itemsList = loads(conn.execute(users.select(users.c.id==g.user.foodBankId)).fetchone()['items'])
# Get all the volunteers that are assigned to our food bank
volunteers = conn.execute(users.select().where(
and_(users.c.foodBankId == g.user.id, users.c.role == "VOLUNTEER",
users.c.approved == True)))
unassigned = conn.execute(users.select().where(
and_(users.c.foodBankId == g.user.id, users.c.role == "VOLUNTEER",
users.c.approved == False)))
if request.method == "GET" and "assign" in request.args.keys():
conn.execute(
users.update(users.c.name == request.args['assign']).values(
approved=True))
volunteerEmail = conn.execute(
select([
users.c.email
]).where(users.c.name == request.args['assign'])).fetchone()[0]
send_volunteer_acceptance_notification(volunteerEmail, g.user.name)
return redirect("/modify")
if request.method == "POST":
try:
key = next(request.form.keys())
except:
key = ""
print("Key: " + key)
if "unassign" in key:
orderId = key[len('unassign-'):]
unassign(int(orderId))
elif "remove" in key:
volunteerId = int(key[len('remove-')])
conn.execute(users.delete().where(users.c.id == volunteerId))
'''
userId = next(request.form.keys())
print(userId)
query = select([users.c.completed]).where(users.c.id==userId)
completed = conn.execute(query).fetchone()[0]
# If you refresh the page and resend data, it'll send 2 conformation emails. This prevents that.
if (completed == 0):
email = conn.execute(select([users.c.email]).where(users.c.id==userId)).fetchone()[0]
send_recieved_notification(email)
conn.execute(users.update().where(users.c.id==userId).values(completed=1))
completedUsers = getUsers(1, zipCode)
uncompletedUsers = getUsers(0, zipCode)
for user in completedUsers:
print(user)
'''
return render_template("modify_volunteers.html",
volunteers=getVolunteerInfoList(g.user.id),
unassigned=unassigned)
def change_pass():
if request.method == 'POST':
old = request.form['old']
new = request.form['new']
confirm = request.form['confirm']
if check_password_hash(g.user['password'], old):
if new == confirm:
conn.execute(users.update().where(users.c.id == g.user['id']).values(
password=generate_password_hash(new)))
return redirect('/youraccount')
else:
flash("Passwords do not match.")
else:
flash("Your current password is incorrect.")
return render_template("auth/changepass.html")
def importMasterList(request, filename, fileType, delete, header, disabledSheet=True):
masterDf = pd.DataFrame()
conn.execute(users.update().where(and_(users.c.foodBankId==g.user.id, users.c.role=="RECIEVER")).values(inSpreadsheet=0))
if (fileType == 'csv'):
masterDf = pd.read_csv(request.files['users'], header=header, keep_default_na=False)
else:
masterDf = pd.read_excel(request.files['users'].read(), sheet_name="Master list", header=header, keep_default_na=False)
masterDf = masterDf.dropna(thresh=2)
if disabledSheet:
disabledDf = pd.read_excel(request.files['users'].read(), sheet_name="Disabled clients", header=header, keep_default_na=False)
disabledDf = disabledDf.dropna(thresh=2)
addUsersFromDf(disabledDf, True)
addUsersFromDf(masterDf, False)
if delete:
conn.execute(users.delete().where(and_(users.c.foodBankId==g.user.id, users.c.role=="RECIEVER", users.c.inSpreadsheet==0)))
def change_info():
if request.method == 'POST':
if g.user.role == "ADMIN":
newItemsList = []
for attribute in request.form:
if attribute[:len('name')] == 'name':
newItemsList.append(request.form[attribute])
else:
given = request.form[attribute]
if (given != '') and attribute != 'submit':
print("Given: " + str(given))
query = users.update().where(users.c.id == g.user['id'])
values = {
'name': query.values(name=given),
'email': query.values(email=given),
'address': query.values(address=given),
'cellPhone': query.values(cellPhone=given),
'instructions': query.values(instructions=given),
'homePhone': query.values(homePhone=given),
'requestPageDescription': query.values(requestPageDescription=given)
}[attribute]
conn.execute(values)
# Remove all of our items, so that we can just cleanly replace it
conn.execute(items.delete().where(items.c.foodBankId == g.user.id))
# Insert new elements into table
for itemName in newItemsList:
conn.execute(items.insert().values(
name=itemName, foodBankId=g.user.id))
return redirect('/youraccount')
if g.user.role == "ADMIN":
rawItemsList = conn.execute(select([items.c.name]).where(
items.c.foodBankId == g.user.id)).fetchall()
itemsList = []
for item in rawItemsList:
itemsList.append(item[0])
print("Items list: " + str(itemsList))
return render_template("auth/changeinfo.html", user=g.user, items=itemsList)
family_raw = conn.execute(family_members.select().where(
g.user.id == family_members.c.user)).fetchall()
family = []
for member in family_raw:
family.append([member[1], member[2]])
return render_template("auth/changeinfo.html", user=g.user, family_members=family)
def setCoords(API_key):
print("Setting coordinates...")
googleWrapper = geopy.geocoders.GoogleV3(api_key=API_key)
userList = conn.execute(users.select()).fetchall()
for user in userList:
if not (user.latitude and user.longitude and user.formattedAddress):
fullAddr = str(user['address']) + ", " + str(user['zipCode'])
#print("Fulladdr: " + fullAddr)
coords = googleWrapper.geocode(query=fullAddr, timeout=100)
if coords == None: # One of the zip codes in the spreadsheet is wrong
coords = googleWrapper.geocode(query=user['address'] + " WA",
timeout=100)
#print("Name: " + str(user['name']))
#print("Original address: " + str(user['address']))
#print("Coords: " + str(coords))
conn.execute(users.update().where(users.c.id == user.id).values(
formattedAddress=coords[0],
latitude=coords[1][0],
longitude=coords[1][1]))
def refreshOrdering(volunteer):
foodBank = conn.execute(users.select().where(users.c.id==volunteer.foodBankId)).fetchone()
orderList = conn.execute(orders.select().where(and_(orders.c.volunteerId==volunteer.id, orders.c.completed==0, orders.c.bagged==1))).fetchall()
ordering = getOrdering(origin=foodBank.address, destination=volunteer.address, orderList=orderList)
conn.execute(users.update().where(users.c.id==volunteer.id).values(ordering=dumps(ordering)))
def all_users():
#itemsList = conn.execute(items.select(items.c.foodBankId==g.user.foodBankId)).fetchall()
userList = getUserList()
showingDuplicates = False
print(create_master_spreadsheet())
if request.method == "POST" and 'num-vehicles' in request.values.to_dict(
).keys():
print(request.values.to_dict())
if 'redirect' in request.values.to_dict().keys():
return loadingScreen(
num_vehicles=request.values.get('num-vehicles'))
else:
assign.createAllRoutes(foodBankId=g.user.id,
num_vehicles=int(
request.values.get('num-vehicles')))
return redirect('/all_orders')
if request.method == "GET" and "volunteer" in request.args.keys():
volunteerId = int(request.args.get("volunteer"))
orderId = int(request.args.get("order"))
order_assignment.assign(orderId=orderId, volunteerId=volunteerId)
return redirect("/all_users")
elif 'find-duplicates' in request.args.keys():
# TODO: this is a really really shitty algorithm
# and can definitley be sped up (but I only need to
# run it once a week so it should be OK)
setDuplicates = set() # set of sets of user ID's
for firstUser in userList:
userIdSet = {firstUser.id}
scoreMax = 0
for secondUser in userList:
score = fuzz.ratio(firstUser.name, secondUser.name)
print("Ratio: " + str(score))
# TODO: make this use things other than name
if score > 80:
userIdSet.add(secondUser.id)
scoreMax = max(score, scoreMax)
if len(userIdSet) > 1:
setDuplicates.add(Duplicate(userIdSet, scoreMax))
# print("Set duplicates:" + str(next(iter(setDuplicates))))
userList = []
for duplicate in setDuplicates:
for userId in duplicate.userIds:
row = (conn.execute(
users.select().where(users.c.id == userId)).fetchone())
d = dict(row.items())
d['hue'] = duplicate.hue
userList.append(d)
showingDuplicates = True
if request.method == "POST":
key = next(request.form.keys())
print("Key: " + str(key))
if "delete" in key:
userId = int(key[len('delete-'):])
conn.execute(users.delete().where(users.c.id == userId))
if "enable" in key:
userId = int(key[len('enable-'):])
conn.execute(users.update().where(users.c.id == userId).values(
disabled=False))
if "download-master-spreadsheet" in key:
return create_master_spreadsheet()
if "disable" in key:
userId = int(key[len('disable-'):])
conn.execute(users.update().where(users.c.id == userId).values(
disabled=True))
routesList = conn.execute(routes.select().where(
routes.c.foodBankId == g.user.foodBankId)).fetchall()
for route in routesList:
content = loads(route.content)
if userId in content:
print("UserID in content!")
content.remove(userId)
conn.execute(
routes.update().where(routes.c.id == route.id).values(
content=dumps(content)))
break
userList = getUserList()
volunteers = getVolunteers()
today = datetime.date.today()
activeUsersCount = 0
disabledUsersCount = 0
for user in userList:
if user['disabled']:
disabledUsersCount += 1
else:
activeUsersCount += 1
return render_template("view_all_orders.html",
users=userList,
showingDuplicates=showingDuplicates,
volunteers=volunteers,
activeUsersCount=activeUsersCount,
disabledUsersCount=disabledUsersCount)