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)