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 getUsers(routeId):
print("Route ID:" + str(routeId))
row2dict = lambda r: {
c.name: str(getattr(r, c.name))
for c in users.columns
}
# Get the ID's that our volunteer is assigned to
route_rp = conn.execute(
routes.select().where(routes.c.id == routeId)).fetchone()
content = loads(route_rp.content)
toReturn = []
allDone = True
for userId in content:
if userId != g.user.foodBankId: # Stupid to put the food bank on the user's list of orders
user_rp = conn.execute(
users.select().where(users.c.id == userId)).fetchone()
if user_rp == None:
continue
userObj = row2dict(user_rp)
userObj['doneToday'] = user_rp['lastDelivered'].date(
) == datetime.today().date()
if not userObj['doneToday']:
allDone = False
toReturn.append(userObj)
userObj['googleMapsUrl'] = google_maps_qr.make_single_url(
userObj['formattedAddress'])
print("Users: " + str(toReturn))
if allDone:
conn.execute(routes.update().where(routes.c.id == routeId).values(
volunteerId=-1))
return toReturn
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 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 availableDates():
numDays = 10 # number of available days to display
toReturn = []
currentDay = datetime.date.today() + datetime.timedelta(days=1)
whereClauses = {
"sunday": users.c.sunday,
"monday": users.c.monday,
"tuesday": users.c.tuesday,
"wednesday": users.c.wednesday,
"thursday": users.c.thursday,
"friday": users.c.friday,
"saturday": users.c.saturday
}
while len(toReturn) < numDays:
dayOfWeek = currentDay.strftime("%A").lower()
volunteers = conn.execute(
users.select(whereclause=and_(
whereClauses[dayOfWeek] == True, users.c.foodBankId ==
g.user.foodBankId, users.c.approved == True))).fetchall()
maxOrders = conn.execute(
select([users.c.maxOrders
]).where(users.c.id == g.user.foodBankId)).fetchone()[0]
eligibleVolunteers = []
for volunteer in volunteers:
ordersList = conn.execute(
orders.select(
whereclause=(and_(orders.c.volunteerId == volunteer.id,
orders.c.completed == 0)))).fetchall()
if len(ordersList) < maxOrders:
eligibleVolunteers.append(volunteer)
if len(eligibleVolunteers) > 0:
toReturn.append(currentDay)
currentDay = currentDay + datetime.timedelta(days=1)
return toReturn
def createAllRoutes(foodBankId,
num_vehicles=100,
stopConversion=1000,
globalSpanCostCoefficient=4000,
solutionLimit=10000):
print("Number of vehicles: " + str(num_vehicles))
print(environ['INSTANCE_PATH'])
usersList = conn.execute(users.select().where(
and_(users.c.role == "RECIEVER", users.c.foodBankId == g.user.id,
users.c.disabled == False))).fetchall()
data = create_data(num_vehicles, globalSpanCostCoefficient, stopConversion,
solutionLimit, usersList)
print("Calculating routes...")
assignments = get_order_assignments(num_vehicles, data, stopConversion,
globalSpanCostCoefficient,
solutionLimit)
routeList = []
for i in range(len(assignments)):
userIdList = [] # sorted list to store as column with volunteer
for user in assignments[i][0]:
userIdList.append(user['id'])
routeList.append(userIdList)
conn.execute(routes.delete().where(routes.c.foodBankId == foodBankId))
for routeNum in range(len(routeList)):
route = routeList[routeNum]
length = assignments[routeNum][1]
print("Route: " + str(route))
if len(route) == 2:
continue
conn.execute(routes.insert().values(foodBankId=foodBankId,
length=length,
content=json.dumps(route),
volunteerId=-1))
def getUsers(routeId):
print("Route ID:" + str(routeId))
row2dict = lambda r: {
c.name: str(getattr(r, c.name))
for c in users.columns
}
# Get the ID's that our volunteer is assigned to
route_rp = conn.execute(
routes.select().where(routes.c.id == routeId)).fetchone()
content = loads(route_rp.content)
toReturn = []
for userId in content:
#if userId != g.user.foodBankId: # Stupid to put the food bank on the user's list of orders
user_rp = conn.execute(
users.select().where(users.c.id == userId)).fetchone()
if user_rp == None:
continue
userObj = row2dict(user_rp)
if user_rp['lastDelivered']:
userObj['doneToday'] = user_rp['lastDelivered'].date(
) == datetime.today().date()
else:
userObj['doneToday'] = False
toReturn.append(userObj)
return toReturn
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 bag(orderId):
order = conn.execute(orders.select().where(orders.c.id==orderId)).fetchone()
if (not order.bagged == 1):
conn.execute(orders.update().where(orders.c.id==orderId).values(bagged=1))
client = conn.execute(users.select().where(users.c.id==order.userId)).fetchone()
volunteer = conn.execute(users.select().where(users.c.id==order.volunteerId)).fetchone()
refreshOrdering(volunteer)
send_bagged_notification(reciever_email=volunteer.email, orderId=orderId, address=client.address)
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 unassign(orderId):
order = conn.execute(orders.select().where(orders.c.id==orderId)).fetchone()
print("Order: " + str(order))
volunteer = conn.execute(users.select().where(users.c.id==order.volunteerId)).fetchone()
print("volunteer: " + str(volunteer))
if not volunteer == None:
foodBank = conn.execute(users.select().where(users.c.id==volunteer.foodBankId)).fetchone()
conn.execute(orders.update().where(orders.c.id==orderId).values(volunteerId=None))
refreshOrdering(volunteer)
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 assign(orderId, volunteerId):
print("volunteer id: " + str(volunteerId))
volunteer = conn.execute(users.select().where(users.c.id==volunteerId)).fetchone()
conn.execute(orders.update().where(orders.c.id==orderId).values(volunteerId=volunteerId))
refreshOrdering(volunteer)
# If it's already been bagged we send the email notification
order = conn.execute(orders.select().where(orders.c.id==orderId)).fetchone()
client = conn.execute(users.select().where(users.c.id==order.userId)).fetchone()
if order.bagged == 1:
send_bagged_notification(reciever_email=volunteer.email, orderId=orderId, address=client.address)
def google_maps_redirect(query):
splitQuery = query.split('-')
userIdList = splitQuery[0].split('+')
api_key = splitQuery[1]
foodBank = conn.execute(
users.select().where(users.c.apiKey == api_key)).fetchall()
if foodBank == None:
return "Sorry, that URL contains an invalid API key."
userList = []
for id in userIdList:
userList.append(
conn.execute(users.select().where(users.c.id == id)).fetchone())
return redirect(google_maps_qr.make_url(userList))
def qr_mark_as_complete():
if "orderId" in request.args.keys():
orderId = int(request.args['orderId'])
order = conn.execute(
orders.select().where(orders.c.id == orderId)).fetchone()
if order.completed == 0:
email = conn.execute(
select([users.c.email
]).where(users.c.id == order.userId)).fetchone()[0]
send_recieved_notification(email)
conn.execute(orders.update().where(orders.c.id == orderId).values(
completed=1))
return "Order " + str(orderId) + " has been marked as complete."
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 master_driver_printout():
print("ID" + str(g.user.id))
routesList = conn.execute(
routes.select().where(routes.c.foodBankId == g.user.id)).fetchall()
routeDictList = []
count = 0
for route in routesList:
routeDict = {}
routeDict['usersList'] = getUsers(route.id)
for user in routeDict['usersList']:
qr_data = google_maps_qr.make_user_qr(user['formattedAddress'])
user['qr_data'] = qr_data
routeDict['qr'] = google_maps_qr.make_qr_code(routeDict['usersList'],
g.user.apiKey)
routeDict['headerText'] = "Route " + str(
count) #+ "(ID: " + str(route['id']) + ")"
routeDict['length'] = route['length'] / 1000
count += 1
routeDictList.append(routeDict)
options = {
'header-right': '[page]/[toPage]',
'orientation': 'Landscape',
'margin-top': '0',
'margin-bottom': '0'
}
html = render_template("driver_printout.html", routes=routeDictList)
pdf = pdfkit.from_string(html, False, options=options)
response = make_response(pdf)
response.headers['Content-type'] = 'application/pdf'
response.headers['Content-Disposition'] = 'inline;'
return response
def driver_printout(routeId):
print("getting driver printout")
route = {}
route['usersList'] = getUsers(routeId)
# Generate QR codes for each user
for user in route['usersList']:
qr_data = google_maps_qr.make_user_qr(user['formattedAddress'])
user['qr_data'] = qr_data
route['qr'] = google_maps_qr.make_qr_code(route['usersList'],
g.user.apiKey)
route['headerText'] = "Route " + str(routeId)
route['length'] = conn.execute(
select([routes.c.length
]).where(routes.c.id == routeId)).fetchone()[0] / 1000.0
html = render_template("driver_printout.html", routes=[route])
options = {
'orientation': 'Landscape',
'margin-top': '0',
'margin-bottom': '0'
}
pdf = pdfkit.from_string(html, False, options=options)
response = make_response(pdf)
response.headers['Content-type'] = 'application/pdf'
response.headers['Content-Disposition'] = 'inline;'
#return html
return response
def getRoutes():
row2dict = lambda r: {
c.name: str(getattr(r, c.name))
for c in routes.columns
}
rp = conn.execute(
routes.select().where(routes.c.foodBankId == g.user.id)).fetchall()
if len(rp) == 0:
return []
scoreAverage = 0
toReturn = []
for route_rp in rp:
routeDict = row2dict(route_rp)
routeDict['userList'] = getUsers(route_rp.id)
routeDict['google_maps'] = google_maps_qr.make_url(
routeDict['userList'])
routeDict['parsedContent'] = loads(route_rp.content)
score = assign.getRouteCost(routeDict['parsedContent'], datetime.now())
scoreAverage += score
routeDict['score'] = score
routeDict['osm'] = google_maps_qr.osm_url(routeDict['userList'])
toReturn.append(routeDict)
scoreAverage = scoreAverage / len(rp)
for routeDict in toReturn:
if scoreAverage == 0:
scoreAverage = 1
routeDict['weightedScore'] = round(
10 * routeDict['score'] / scoreAverage, 2)
return toReturn
def manageRoutes():
#itemsList = conn.execute(items.select(items.c.foodBankId==g.user.foodBankId)).fetchall()
print("Type: " + str(type(routes)))
print("Attributes: " + str(dir(routes)))
if request.method == "POST":
print("Values: " + str(request.values.to_dict()))
if request.method == "POST" and 'num_vehicles' in request.values.to_dict(
).keys():
print(request.values.to_dict())
separateCities = 'separate_cities' in request.values.keys()
return loadingScreen(
num_vehicles=int(request.values.get('num_vehicles')),
global_span_cost=int(request.values.get('global_span_cost')),
stop_conversion=int(request.values.get('stop_conversion')),
solution_limit=int(request.values.get('solution_limit')),
separate_cities=separateCities)
'''
if request.values['separateCities'] == 'None':
print("Not separating cities")
assign.createAllRoutes(foodBankId=g.user.id, num_vehicles=int(request.values.get('num-vehicles')),
globalSpanCostCoefficient=int(request.values.get('global_span_cost')),
stopConversion=int(request.values.get('stop_conversion')), solutionLimit=int(request.values.get('solution-limit')))
else:
print("Seperating cities")
assign.createAllRoutesSeparatedCities(foodBankId=g.user.id, num_vehicles=int(request.values.get('num-vehicles')),
globalSpanCostCoefficient=int(request.values.get('global_span_cost')),
stopConversion=int(request.values.get('stop_conversion')), solutionLimit=int(request.values.get('solution-limit')))
'''
elif request.method == "POST" and 'move-user' in request.values.to_dict(
).keys():
userId = int(request.values.to_dict()['move-user'])
toRoute = int(request.values.to_dict()['to-route'])
fromRoute = int(request.values.to_dict()['from-route'])
print("Moving user " + str(userId) + " to route " + str(toRoute))
assign.assignUserToRoute(toRoute, userId, fromRoute)
elif request.method == "POST" and 'move-route' in request.values.to_dict(
).keys():
toRoute = int(request.values.to_dict()['to-route'])
fromRoute = int(request.values.to_dict()['move-route'])
print("Moving route " + str(fromRoute) + " to " + str(toRoute))
clients = loads(
conn.execute(
select([routes.c.content
]).where(routes.c.id == fromRoute)).fetchone()[0])
# remove food bank
clients.remove(clients[0])
clients.remove(clients[len(clients) - 1])
for client in clients:
print("Removing client " + str(client))
assign.assignUserToRoute(toRoute, client, fromRoute)
elif request.method == "POST" and 'split-route' in request.values.to_dict(
).keys():
routeId = int(request.values.to_dict()['split-route'])
splitRoute(routeId)
routeList = getRoutes()
stats = getStats(routeList)
return render_template("routes_view.html", routes=routeList, stats=stats)
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = conn.execute(users.select().where(
users.c.id == user_id)).fetchone()
def create_master_spreadsheet():
columns = [
'name', 'email', 'formattedAddress', 'address2', 'cellPhone',
'instructions'
]
prettyNames = {
'formattedAddress': 'Full Address',
'address2': 'Apt',
'name': 'Name',
'email': 'Email',
'cellPhone': 'Phone',
'instructions': 'Notes'
}
enabledRpList = conn.execute(
users.select(
and_(users.c.role == "RECIEVER", users.c.foodBankId == g.user.id,
users.c.disabled == False))).fetchall()
enabled = generateUserDataFrame(enabledRpList, prettyNames)
disabledRpList = conn.execute(
users.select(
and_(users.c.role == "RECIEVER", users.c.foodBankId == g.user.id,
users.c.disabled == True)).order_by(
users.c.disabledDate)).fetchall()
disabled = generateUserDataFrame(disabledRpList, prettyNames)
outputColumns = [
'First Name', "Last Name", "Email", "Address", "Apt", "City", "Zip",
"Phone", "Notes"
]
writer = pd.ExcelWriter(environ['INSTANCE_PATH'] +
'client-master-list.xlsx')
enabled.to_excel(writer,
sheet_name="Master list",
columns=outputColumns,
startrow=0,
index=False,
na_rep="")
disabled.to_excel(writer,
sheet_name="Disabled clients",
columns=outputColumns,
startrow=0,
index=False,
na_rep="")
writer.save()
return send_file(environ['INSTANCE_PATH'] + 'client-master-list.xlsx',
as_attachment=True)
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 getVolunteers():
proxy = conn.execute(
users.select(
and_(users.c.role == "VOLUNTEER", users.c.approved == True,
users.c.volunteerRole == "DRIVER"))).fetchall()
dictList = []
for volunteer in proxy:
volunteerDict = {}
columns = conn.execute(users.select()).keys()
for column in columns:
volunteerDict[column] = getattr(volunteer, column)
volunteerDict['numOrders'] = len(
conn.execute(
orders.select(
and_(orders.c.volunteerId == volunteer.id,
orders.c.completed == 0))).fetchall())
dictList.append(volunteerDict)
return dictList
def getVolunteerInfoList(foodBankId):
row2dict = lambda r: {
c.name: str(getattr(r, c.name))
for c in users.columns
}
volunteerList = conn.execute(users.select().where(
and_(users.c.role == "VOLUNTEER", users.c.foodBankId == foodBankId,
users.c.approved == True)))
toReturn = []
for volunteer_rp in volunteerList:
volunteerDict = row2dict(volunteer_rp)
route = conn.execute(routes.select().where(
routes.c.volunteerId == volunteerDict['id'])).fetchone()
if route != None:
volunteerDict['userList'] = getUsers(route.id)
else:
volunteerDict['userList'] = []
toReturn.append(volunteerDict)
return toReturn
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 getUsers(routeId,
addOriginal=False,
columns=[
users.c.name, users.c.email, users.c.cellPhone,
users.c.instructions, users.c.address,
users.c.formattedAddress, users.c.address2,
users.c.householdSize
],
includeDepot=False):
print("Route ID:" + str(routeId))
prettyNames = {
'formattedAddress': 'Full Address',
'address2': 'Apt',
'address': 'Original Address',
'name': 'Name',
'email': 'Email',
'cellPhone': 'Phone',
'instructions': 'Notes',
'householdSize': 'Household Size',
'id': 'id',
'latitude': 'latitude',
'longitude': 'longitude'
}
row2dict = lambda r: {
prettyNames[c.name]: betterStr(getattr(r, c.name))
for c in columns
}
# Get the ID's that our volunteer is assigned to
route_rp = conn.execute(
routes.select().where(routes.c.id == routeId)).fetchone()
content = loads(route_rp.content)
toReturn = []
for userId in content:
if userId != g.user.id or includeDepot: # Stupid to put the food bank on the user's list of orders
user_rp = conn.execute(
users.select().where(users.c.id == userId)).fetchone()
userObj = row2dict(user_rp)
toReturn.append(userObj)
#print("Users: " + str(toReturn))
return toReturn
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 assignUserToRoute(toRoute, userId, fromRoute):
# Remove from original route first
idArr = json.loads(
conn.execute(
select([routes.c.content
]).where(routes.c.id == fromRoute)).fetchone()[0])
idArr.remove(userId)
if len(idArr) == 2:
conn.execute(routes.delete().where(routes.c.id == fromRoute))
else:
conn.execute(routes.update().where(routes.c.id == fromRoute).values(
content=json.dumps(idArr)))
routeContent = getUsers(
toRoute, columns=[users.c.id, users.c.latitude, users.c.longitude])
userToInsert = conn.execute(
users.select().where(users.c.id == userId)).fetchone()
minIndex = 0
minDistance = 999999
for i in range(0, len(routeContent) - 1):
userFrom = routeContent[i]
print("At user " + str(userFrom))
userTo = routeContent[i + 1]
distanceLeft = measure(userFrom['latitude'], userFrom['longitude'],
userToInsert['latitude'],
userToInsert['longitude'])
distanceRight = measure(userToInsert['latitude'],
userToInsert['longitude'], userTo['latitude'],
userTo['longitude'])
distance = distanceLeft + distanceRight
print("Distance at index " + str(i) + ": " + str(distance))
if distance < minDistance:
minIndex = i
minDistance = distance
print("Inserting to index " + str(minIndex) + " with distance " +
str(minDistance))
idArr = json.loads(
conn.execute(select([routes.c.content
]).where(routes.c.id == toRoute)).fetchone()[0])
idArr.insert(minIndex + 1, userId)
conn.execute(routes.update().where(routes.c.id == toRoute).values(
content=json.dumps(idArr)))
