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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 request_items(): foodBank = conn.execute( users.select(users.c.id == g.user.foodBankId)).fetchone() itemsList = conn.execute( select([items.c.name ]).where(items.c.foodBankId == g.user.foodBankId)).fetchall() description = foodBank['requestPageDescription'] if request.method == "POST": itemsOrdered = [] for item in request.form: itemsOrdered.append(item) send_request_confirmation(g.user['email'], itemsOrdered, "date strftime would go here") oldOrder = conn.execute(orders.select().where( and_(orders.c.userId == g.user.id, orders.c.completed == 0))).fetchone() if oldOrder != None: volunteer = conn.execute(users.select().where( users.c.id == oldOrder.volunteerId)).fetchone() if volunteer != None: conn.execute( orders.update(orders.c.userId == g.user.id).values( completed=1)) refreshOrdering(volunteer) # insert new order into the orders table orderId = conn.execute( orders.insert(), contents=dumps(itemsOrdered), completed=0, bagged=0, userId=g.user.id, foodBankId=g.user.foodBankId).inserted_primary_key[0] return redirect("/success") categories = [] return render_template("request_items.html", items=itemsList, categories=categories, dates="availableDates() would go here", description=description)
def generate_shipping_labels(): volunteers = getVolunteers() ordersDict = getOrders(g.user.id) itemsList = loads( conn.execute( users.select(users.c.id == g.user.foodBankId)).fetchone()['items']) html = render_template("shipping-labels.html", orders=ordersDict, volunteers=volunteers, barcode_to_base64=barcode_to_base64, qrcode_to_base64=qrcode_to_base64) # Uncomment this line for debugging #return html pdf = pdfkit.from_string(html, False) response = make_response(pdf) response.headers['Content-type'] = 'application/pdf' response.headers['Content-Disposition'] = 'inline;' return response
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 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)))
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 login(): redirect_url = request.args.get('redirect_url') if redirect_url is None: redirect_url = 'index' if request.method == 'POST': email = request.form['email'] password = request.form['password'] error = None user = conn.execute(users.select().where( users.c.email == email)).fetchone() if user is None: error = 'Incorrect email address.' elif not check_password_hash(user['password'], password): error = 'Incorrect password.' if error is None: session.clear() session['user_id'] = user['id'] return redirect(url_for('index')) flash(error) return render_template('auth/login.html', title="Log In")
def createAllRoutesSeparatedCities(foodBankId, num_vehicles=100, stopConversion=1000, globalSpanCostCoefficient=4000, solutionLimit=10000): minLength = 4 # TODO: make this configurable 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) placesDict = {} # Keys are cities and values are arrays of user RowProxy's # Distribute users into dictionary by city for user in usersList: parsed = usaddress.tag(user['formattedAddress'])[0] city = parsed['PlaceName'] #print("City: " + city) if city in placesDict.keys(): placesDict[city].append(user) else: placesDict[city] = [user] print("placesDict keys: " + str(placesDict.keys())) # Now, we take cities that have less than five people (e.g. Fife) and plop them into the biggest city (like Tacoma) biggestCity = next(iter(placesDict.keys())) for city in placesDict.keys(): if len(placesDict[city]) > len(placesDict[biggestCity]): biggestCity = city keyArr = list(placesDict.keys()) for city in keyArr: if len(placesDict[city]) < minLength: placesDict[biggestCity] = placesDict[biggestCity] + placesDict.pop( city, None) #placesDict.pop('Tacoma', None) conn.execute(routes.delete().where(routes.c.foodBankId == foodBankId)) # Now, the real fun begins for city in placesDict.keys(): print("Calculating routes for " + city + "...") data = create_data(num_vehicles, globalSpanCostCoefficient, stopConversion, solutionLimit, placesDict[city], addToMax=True) assignments = get_order_assignments(num_vehicles, data, stopConversion, globalSpanCostCoefficient, solutionLimit) localRouteList = [] for i in range(len(assignments)): userIdList = [] for user in assignments[i][0]: userIdList.append(user['id']) localRouteList.append(userIdList) for routeNum in range(len(localRouteList)): route = localRouteList[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 getUsersFromIdList(contents): toReturn = [] for id in contents: user = conn.execute(users.select().where(users.c.id == id)).fetchone() toReturn.append(user) return toReturn
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)
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)))