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