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 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 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 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 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 send_overview():
routesList = conn.execute(
routes.select(routes.c.foodBankId == g.user.id).order_by(
routes.c.length)).fetchall()
dictList = []
count = 0
for route in routesList:
dictList.append({
'Route Number': count,
'Length': len(loads(route.content)) - 2,
'Distance': route.length / 1000,
'Date': ' '
})
count += 1
fileName = environ['INSTANCE_PATH'] + 'routes-overview-' + getDateString(
) + '.xlsx'
df = pd.DataFrame(dictList)
df.to_excel(fileName, index=False, header=True)
return send_file(fileName, as_attachment=True)
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 driver_printout():
routeId = conn.execute(
routes.select().where(routes.c.volunteerId == g.user.id)).fetchone()
if routeId == None:
return redirect('/dashboard')
else:
routeId = routeId[0]
usersList = getUsers(routeId)
html = render_template("driver_printout.html",
users=usersList,
volunteer=g.user)
pdf = pdfkit.from_string(html, False)
response = make_response(pdf)
response.headers['Content-type'] = 'application/pdf'
response.headers['Content-Disposition'] = 'inline;'
#return html
return response
def getNextRoute(volunteerId, foodBankId):
oldId = conn.execute(
select([routes.c.id
]).where(routes.c.volunteerId == volunteerId)).fetchone()
routeList = conn.execute(routes.select().where(
and_(routes.c.foodBankId == foodBankId,
routes.c.volunteerId == -1))).fetchall()
now = datetime.now()
# wow tuples how pythonic
maxRoute = (-1, -1) # ID, cost
for route in routeList:
cost = getRouteCost(json.loads(route.content), now)
#print("Cost: " + str(cost))
#print("Route: " + str(route))
if cost > maxRoute[1]:
maxRoute = (route.id, cost)
print("updating to route " + str(maxRoute[0]))
conn.execute(routes.update().where(routes.c.id == maxRoute[0]).values(
volunteerId=volunteerId))
if oldId != None:
conn.execute(routes.update().where(routes.c.id == oldId[0]).values(
volunteerId=-1))
return maxRoute[0]
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)
def create_doordash_spreadsheet():
def get_proximity(lat, lon):
fb = conn.execute(
users.select(users.c.id == g.user.foodBankId)).fetchone()
fbLat = fb.latitude
fbLon = fb.longitude
return measure(lat, lon, fbLat, fbLon)
#columns = ['name', 'email', 'formattedAddress', 'address2', 'cellPhone', 'instructions']
prettyNames = {
'formattedAddress': 'Full Address',
'address2': 'Apt',
'name': 'Name',
'email': 'Email',
'cellPhone': 'Phone'
}
routesRpList = conn.execute(
routes.select(routes.c.foodBankId == g.user.id).order_by(
routes.c.length)).fetchall()[0:6]
removeIds = []
for route in routesRpList:
removeIds.extend(loads(route.content))
enabledRpList = conn.execute(
users.select(
and_(users.c.role == "RECIEVER", users.c.foodBankId == g.user.id,
users.c.disabled == False))).fetchall()
dictList = []
staticValues = {
'Pickup Location Name':
'Eloise\'s Cooking Pot',
'StoreID Provided by Doordash':
'ELOISE-01',
'Pickup Window Start':
'10:00AM',
'Pickup Window End':
'1:00PM',
'Dropoff Instructions':
'Please leave items, take a pictue and leave. Do not return anything'
}
outputColumns = [
'Pickup Location Name', 'StoreID Provided by Doordash',
'Date of Delivery', 'Pickup Window Start', 'Pickup Window End',
'Client First Name', "Client Last Name", "Client Street Address",
"Client Unit", "Client City", "Client State", "Client Zip",
"Client Phone", "Dropoff Instructions"
]
for row in enabledRpList:
if row.id in removeIds:
continue
columns = [
'name', 'formattedAddress', 'address2', 'cellPhone', 'latitude',
'longitude'
]
row2dict = lambda r: {c: betterStr(getattr(r, c)) for c in columns}
d = row2dict(row)
# doordash drivers can't go to the joint base
if 'Joint Base Lewis-McChord' not in d['formattedAddress']:
d['proxmity'] = get_proximity(d['latitude'], d['longitude'])
try:
d['Client First Name'], d['Client Last Name'] = d[
'name'].split(' ')
except:
d['Client First Name'] = d['name']
d['Client Last Name'] = ''
parsed = usaddress.tag(d['formattedAddress'])[0]
d['Client City'] = parsed['PlaceName']
if 'ZipCode' in parsed:
d['Client Zip'] = parsed['ZipCode']
else:
d['Client Zip'] = ''
d['Client State'] = parsed['StateName']
addressFormat = [
'AddressNumber', 'StreetNamePreDirectional',
'StreetNamePreModifier', 'StreetNamePreType', 'StreetName',
'StreetNamePostDirectional', 'StreetNamePostModifier',
'StreetNamePostType'
]
address = ""
for attribute in addressFormat:
if attribute not in parsed.keys():
continue
if address == "":
address = parsed[attribute]
else:
address = address + " " + parsed[attribute]
d['Client Street Address'] = address
d['Client Unit'] = d.pop('address2')
phone = d.pop('cellPhone')
# Strip all non-numeric characters
phone = re.sub('[^0-9]', '', phone)
# Get last 10 digits
phone = phone[-10:]
if phone == "":
phone = environ['DEFAULT_PHONE_NUM']
d['Client Phone'] = phone
d.update(staticValues)
dictList.append(d)
# Sort the dictList by proximity
sortedList = sorted(dictList, key=lambda k: k['proxmity'], reverse=True)
wednesdayList = []
thursdayList = []
# Sets wednesday to be the date of the next wednesday
currentDate = datetime.datetime.today()
wednesdayDate = currentDate + datetime.timedelta(
(9 - currentDate.weekday()) % 7)
thursdayDate = currentDate + datetime.timedelta(
(10 - currentDate.weekday()) % 7)
dateFormat = '%m/%d/%Y'
for x in range(0, len(sortedList)):
if x % 2 == 0:
sortedList[x]['Date of Delivery'] = wednesdayDate.strftime(
dateFormat)
wednesdayList.append(sortedList[x])
else:
sortedList[x]['Date of Delivery'] = thursdayDate.strftime(
dateFormat)
thursdayList.append(sortedList[x])
# Add empty row to wednesday list for spacing
wednesdayList.append({
'Client First Name': '',
'Client Last Name': '',
'Client Street Address': '',
'Client Unit': '',
'Client City': '',
'Client State': '',
'Client Zip': '',
'Client Phone': '',
'date': ''
})
outputDf = pd.DataFrame(wednesdayList + thursdayList)
print(outputDf.columns)
filename = 'doordash-' + getDateString() + '.xlsx'
writer = pd.ExcelWriter(environ['INSTANCE_PATH'] + filename)
outputDf.to_excel(writer,
sheet_name="Doordash clients",
columns=outputColumns,
startrow=0,
index=False,
na_rep="")
writer.save()
return send_file(environ['INSTANCE_PATH'] + filename, as_attachment=True)
def send_spreadsheet():
print("Sending spreadsheet...")
google_maps = request.args.get('map') == 'true'
routesList = conn.execute(
routes.select(routes.c.foodBankId == g.user.id).order_by(
routes.c.length)).fetchall()
outputColumns = [
'Number', 'First Name', "Last Name", "Address", "Apt", "City", "State",
"Zip", "Phone", "Notes"
]
if google_maps:
outputColumns.append("Google Maps")
pdList = []
routeCount = 0
for route in routesList:
usersList = getUsers(route.id,
addOriginal=True,
includeDepot=True,
columns=[
users.c.id, users.c.name, users.c.email,
users.c.formattedAddress, users.c.address2,
users.c.cellPhone, users.c.instructions
])
for user in usersList:
userCount = 1
try:
parsed = usaddress.tag(user['Full Address'])[0]
user['City'] = parsed['PlaceName']
user['State'] = 'WA'
user['Zip'] = parsed['ZipCode']
addressFormat = [
'AddressNumber', 'StreetNamePreDirectional',
'StreetNamePreModifier', 'StreetNamePreType', 'StreetName',
'StreetNamePostDirectional', 'StreetNamePostModifier',
'StreetNamePostType'
]
address = ""
for attribute in addressFormat:
if attribute not in parsed.keys():
continue
if address == "":
address = parsed[attribute]
else:
address = address + " " + parsed[attribute]
except:
address = user['Full Address']
user['Address'] = address
names = user['Name'].split(" ", maxsplit=1)
user['First Name'] = names[0]
user['Last Name'] = names[1]
if user['Last Name'] == "nan":
user['Last Name'] = ''
if 'Google Maps' in outputColumns:
user['Google Maps'] = google_maps_qr.make_single_url(
user['Full Address'])
user['Number'] = userCount
google_maps_link = google_maps_qr.make_url(usersList)
# remove food bank
usersList.remove(usersList[0])
usersList.remove(usersList[len(usersList) - 1])
row_num = 15
create_blank_rows(row_num - len(usersList), usersList, outputColumns)
if google_maps:
footerContent = [['', 'Google maps link:', google_maps_link],
['', 'Assigned to: ', ''], ['', 'Date:', ''],
['', 'Route ' + str(routeCount)]]
else:
footerContent = [['', 'Assigned to: ', ''], ['', 'Date:', ''],
['', 'Route ' + str(routeCount)]]
routeCount += 1
create_footer_rows(footerContent, usersList, outputColumns)
df = pd.DataFrame(usersList)
pdList.append(df)
fileName = environ['INSTANCE_PATH'] + 'routes-' + getDateString() + '.xlsx'
writer = pd.ExcelWriter(fileName, engine="openpyxl")
for index in range(0, len(pdList)):
pdList[index].to_excel(writer,
sheet_name="Route " + str(index),
index=False,
columns=outputColumns)
writer.save()
# set formatting
workbook = load_workbook(fileName)
writer = pd.ExcelWriter(fileName, engine="openpyxl")
writer.book = workbook
for ws in workbook.worksheets:
print(ws.title)
for col in ws.iter_cols():
maxWidth = ''
for cell in col:
cell.font = styles.Font(name='Times New Roman', size=10)
if len(str(cell.value)) > len(
maxWidth
) and cell.value is not None and 'oogle.com' not in cell.value:
maxWidth = str(cell.value)
ws.column_dimensions[col[0].column_letter].width = len(maxWidth)
writer.save()
return send_file(fileName, as_attachment=True)