def add_roommates(event, hotel_block, room_id): if not check_permission(f'hotel_block.{hotel_block}.write', event=event): return "", 403 reqs = db.query(HotelRoomRequest).filter(HotelRoomRequest.event==event, HotelRoomRequest.badge.in_(g.data['roommates'])).all() room_nights = db.query(HotelRoomNight).filter(HotelRoomNight.event==event).all() room_nights = {x.id: x for x in room_nights} for req in reqs: for night in req.room_night_requests: assign = False if night.requested: if room_nights[night.room_night].restricted: for approval in req.room_night_approvals: if approval.room_night == night.room_night and approval.approved: assign = True break else: assign = True for assignment in req.room_night_assignments: if assignment.room_night == night.room_night: assign = False break if assign: db.add(RoomNightAssignment(event=event, badge=req.badge, room_night=night.room_night, hotel_room=room_id)) update_room_request_props(db, reqs, assigned=True) for req in reqs: db.add(req) db.commit() return "null", 200
def shift_signup(event, shift): if 'badge' in request.json: badge = db.query(Badge).filter(Badge.id == request.json['badge']).one() elif g.badge: badge = g.badge else: return "The current user does not have a badge, and thus cannot sign up for shifts.", 412 shift = db.query(Shift).filter(Shift.id == shift).one() if shift.filledslots >= shift.slots: return "The shift is full.", 412 shift.filledslots += 1 assignment = ShiftAssignment(shift=shift.id, badge=badge.id, event=event) signup = ShiftSignup(badge=badge.id, shift=shift.id, job=shift.job, schedule=shift.schedule, schedule_event=shift.schedule_event, starttime=shift.starttime, duration=shift.duration) db.add(shift) db.add(assignment) db.add(signup) db.commit() return jsonify(shift=Shift.serialize(shift), shift_assignment=ShiftAssignment.serialize(assignment), shift_signup=ShiftSignup.serialize(signup))
def block_assignments(event): if request.method == "GET": if not check_permission("hotel_block.*.read", event=event): return "", 403 subquery = db.query(RoomNightRequest.badge).filter(RoomNightRequest.event == event, RoomNightRequest.requested == True).distinct().subquery().select() rows = db.query(Badge, HotelRoomRequest.declined, HotelRoomRequest.hotel_block, HotelRoomRequest.id, HotelRoomRequest.notes).options(joinedload('departments')).filter(HotelRoomRequest.event == event, HotelRoomRequest.badge.in_(subquery)).join(HotelRoomRequest, HotelRoomRequest.badge == Badge.id).all() badge_dicts = [] for badge, declined, block, hrr, notes in rows: if not declined: badge_dicts.append({ "id": badge.id, "notes": notes, "public_name": badge.public_name, "departments": [x.id for x in badge.departments], "department_names": [x.name for x in badge.departments], "badge_type": badge.badge_type, "hotel_block": block, "hotel_room_request": hrr }) return jsonify(badge_dicts) elif request.method == "POST": if not check_permission("hotel_block.*.write", event=event): return "", 403 room_requests = {x['id']: x['hotel_block'] for x in g.data['updates']} hotel_room_requests = db.query(HotelRoomRequest).filter(HotelRoomRequest.id.in_(room_requests.keys()), HotelRoomRequest.event == event).all() for hotel_room_request in hotel_room_requests: hotel_room_request.hotel_block = room_requests[hotel_room_request.id] db.commit() return "null", 200 return "", 406
def logout(): if g.user: db.query(Session).filter(Session.user == g.user.id).delete() if 'session' in request.cookies: db.query(Session).filter( Session.secret == request.cookies.get('session')).delete() db.commit() return "null", 200
def room_details(event): if not check_permission("hotel_block.*.read"): return "", 403 rooms = [int(x) for x in g.data['rooms'].split(",")] rnas = db.query(RoomNightAssignment, Badge.public_name).join(Badge, Badge.id == RoomNightAssignment.badge).filter(RoomNightAssignment.hotel_room.in_(rooms)).all() details = {} for rna, public_name in rnas: if not rna.hotel_room in details: details[rna.hotel_room] = { "room_nights": [], "roommates": {}, "empty_slots": 0 } if not rna.room_night in details[rna.hotel_room]['room_nights']: details[rna.hotel_room]['room_nights'].append(rna.room_night) if not rna.badge in details[rna.hotel_room]['roommates']: details[rna.hotel_room]['roommates'][rna.badge] = { "id": rna.badge, "name": public_name, "errors": set() } gender_prefs = {} room_nights = db.query(HotelRoomNight).filter(HotelRoomNight.event == event).all() room_nights = {x.id: x for x in room_nights} hotel_rooms = db.query(HotelRoom, HotelRoomRequest).join(RoomNightAssignment, HotelRoom.id == RoomNightAssignment.hotel_room).join(HotelRoomRequest, HotelRoomRequest.badge == RoomNightAssignment.badge).filter(HotelRoom.id.in_(rooms)).options(joinedload(HotelRoomRequest.room_night_approvals)).options(joinedload(HotelRoomRequest.room_night_requests)).all() for hotel_room, request in hotel_rooms: if not hotel_room.id in gender_prefs: gender_prefs[hotel_room.id] = set() gender_prefs[hotel_room.id].add(config.gender_map.get(request.preferred_gender, "Unknown")) for hotel_room, request in hotel_rooms: for roommate_request in request.roommate_requests: if not roommate_request in hotel_room.roommates: details[hotel_room.id]['roommates'][request.badge]['errors'].add('Missing Roommate') for antiroommate_request in request.roommate_anti_requests: if antiroommate_request in hotel_room.roommates: details[hotel_room.id]['roommates'][request.badge]['errors'].add('Anti-requested Roommate') nights = set() for night_request in request.room_night_requests: if night_request.requested: if room_nights[night_request.room_night].restricted: for approval in request.room_night_approvals: if approval.room_night == night_request.room_night and approval.approved: nights.add(night_request.room_night) else: nights.add(night_request.room_night) extra_nights = nights.symmetric_difference(set(details[hotel_room.id]['room_nights'])) if extra_nights: details[hotel_room.id]['roommates'][request.badge]['errors'].add(f'Extra Room Night ({len(extra_nights)})') if request.prefer_single_gender and len(gender_prefs[hotel_room.id]) > 1: details[hotel_room.id]['roommates'][request.badge]['errors'].add(f'Gender Mismatch ({request.preferred_gender}) ({", ".join(gender_prefs[hotel_room.id])})') details[hotel_room.id]['roommates'][request.badge]['errors'] = list(details[hotel_room.id]['roommates'][request.badge]['errors']) details[hotel_room.id]['empty_slots'] += len(set(details[hotel_room.id]['room_nights']).difference(nights)) return jsonify(details)
def flush_session_perms(user_id=None): if user_id: sessions = db.query(Session).filter(Session.user == user_id).all() else: sessions = db.query(Session).all() for session in sessions: perms = get_permissions(user_id=session.user) session.permissions=json.dumps(perms) db.add(session) db.commit()
def remove_shift(db, shift): signups = db.query(ShiftSignup).filter( ShiftSignup.shift == shift.id).order_by( ShiftSignup.signuptime.desc()).all() for signup in signups: signup.shift = None db.add(signup) db.query(ShiftAssignment).filter( ShiftAssignment.shift == shift.id).delete() db.delete(shift)
def remove_roommates(event, hotel_block, room_id): if not check_permission(f'hotel_block.{hotel_block}.write', event=event): return "", 403 db.query(RoomNightAssignment).filter(RoomNightAssignment.event==event, RoomNightAssignment.hotel_room==room_id, RoomNightAssignment.badge.in_(g.data['roommates'])).delete() reqs = db.query(HotelRoomRequest).filter(HotelRoomRequest.event==event, HotelRoomRequest.badge.in_(g.data['roommates'])).all() update_room_request_props(db, reqs, assigned=False) for req in reqs: db.add(req) db.commit() return "null", 200
def reschedule_job(job, schedule_event=None): """Regenerates the shifts associated with this job. If a schedule_event is passed then it will only regenerate overlapping shifts. """ if schedule_event: schedule_events = [schedule_event] else: schedule_events = db.query(ScheduleEvent).filter( or_(ScheduleEvent.schedule.in_([x.id for x in job.schedules]), ScheduleEvent.id.in_([x.id for x in job.schedule_events]))).all() if not schedule_event: # Completely regenerate this schedule. Drop and recreate everything. existing = db.query(Shift).filter(Shift.job == job.id).all() new = create_shift_schedule(job, schedule_events) for shift in existing: remove_shift(db, shift) db.flush() for shift in new: add_shift(db, shift) if schedule_event: # Find adjacent schedule events adjacent = [] starttime = schedule_event.starttime - datetime.timedelta( seconds=10) # Treat times within 10 seconds as being adjacent endtime = schedule_event.starttime + datetime.timedelta( seconds=schedule_event.duration + 10) # Calculate the endtime, and add 10 seconds of margin for event in schedule_events: if not event in adjacent: eventstart = event.starttime eventend = event.starttime + datetime.timedelta( seconds=event.duration) if eventstart <= starttime and eventend >= starttime: adjacent.append(event) elif eventstart >= starttime and eventstart <= endtime: adjacent.append(event) # Delete all adjacent schedule events for event in adjacent: shifts = db.query(Shift).filter( Shift.schedule_event == event.id).all() for shift in shifts: remove_shift(db, shift) db.flush() # Create new shifts for adjacent schedule events if request.method == "DELETE": adjacent.remove(schedule_event) new = create_shift_schedule(job, adjacent) for shift in new: add_shift(db, shift) shifts = db.query(Shift).filter(Shift.job == job.id).all() assignments = db.query(ShiftAssignment).all() return shifts
def shiftassignmentchange(db, shiftassignment): if request.method == "POST": shift = db.query(Shift).filter(Shift.id == shiftassignment.shift).one() shift.filledslots += 1 if shift.filledslots > shift.slots: return "Too many badges assigned to shift.", 412 db.add(shift) elif request.method == "DELETE": shift = db.query(Shift).filter( Shift.id == shiftassignmentchange.shift).one() shift.filledslots = max(0, shift.filledslots - 1) db.add(shift)
def clear_broken_signups(db, shift): signups = db.query(ShiftSignup).filter( ShiftSignup.shift == shift.id).order_by( ShiftSignup.signuptime.desc()).all() for signup in signups: if signup.starttime != shift.starttime or signup.duration != shift.duration or signup.job != shift.job or shift.filledslots > shift.slots: signup.shift = None db.query(ShiftAssignment).filter( ShiftAssignment.shift == shift.id).delete() db.add(signup) shift.filledslots = max(0, shift.filledslots - 1) db.add(shift)
def check_login(): res = {} if g.user: user = db.query(User).filter(User.id == g.user.id).one() res['user'] = User.serialize(user) res['session'] = g.session.secret if g.badge: badge = db.query(Badge).filter(Badge.id == g.badge.id).one() res['badge'] = Badge.serialize(badge, serialize_relationships=True) res['session'] = g.session.secret if res: return jsonify(res) return "", 406
def available_jobs(event): if "badge" in request.args: badge = db.query(Badge).filter( Badge.id == request.args['badge']).one_or_none() elif g.badge: badge = g.badge if not badge: return "Your current user does not have a badge and no badge was passed as a parameter.", 412 all_roles = db.query(DepartmentRole, DepartmentGrant).join( DepartmentGrant, DepartmentGrant.role == DepartmentRole.id).filter( DepartmentGrant.user == badge.user).all() roles = {} for dept in badge.departments: roles[dept.id] = [] for role, grant in all_roles: if grant.department == dept.id or grant.department is None: roles[dept.id].append(role.id) res = db.query(Department, Job).join(Job, Job.department == Department.id).filter( Department.id.in_([x.id for x in badge.departments])).all() jobs = [] for department, job in res: for role in job.roles: if not role.id in roles[department.id]: break else: # All required roles are present, this user may sign up for this job. jobject = {"job": Job.serialize(job), "shifts": []} shifts = db.query(Shift).filter(Shift.job == job.id).all() for shift in shifts: jobject['shifts'].append({ 'id': shift.id, 'job': shift.job, 'schedule': shift.schedule, 'schedule_event': shift.schedule_event, 'starttime': shift.starttime, 'duration': shift.duration, 'slots': shift.slots, 'filledslots': shift.filledslots, 'weighting': shift.weighting, 'blocks': [] }) if shift.filledslots >= shift.slots: jobject['shifts'][-1]['blocks'].append("Shift is full.") jobs.append(jobject) return jsonify(jobs)
def crud_single(model, event=None, department=None, id=None): perms = model_permissions(model.__tablename__.lower()) if request.method == "GET": if READ_PERMS.intersection( perms['*']) or (id in perms and READ_PERMS.intersection(perms[id])): full = request.args.get("full", False, type=lambda x: x.lower() == 'true') instance = db.query(model).filter(model.id == id).one_or_none() return jsonify( model.serialize(instance, serialize_relationships=full)) raise PermissionDenied() elif request.method == "PATCH": if WRITE_PERMS.intersection( perms['*']) or (id in perms and WRITE_PERMS.intersection(perms[id])): g.data['id'] = id g.data = {k: v for k, v in g.data.items() if hasattr(model, k)} instance = model.deserialize(g.data) db.add(instance) if hasattr(instance, 'onchange_cb'): db.flush() for cb in instance.onchange_cb: cb(db, instance) db.commit() return jsonify(model.serialize(instance)) raise PermissionDenied() elif request.method == "DELETE": if WRITE_PERMS.intersection( perms['*']) or (id in perms and WRITE_PERMS.intersection(perms[id])): instance = db.query(model).filter(model.id == id).one_or_none() if not instance: return "", 404 instance_data = None if hasattr(instance, 'onchange_cb'): instance_data = model.serialize(instance, serialize_relationships=True) db.delete(instance) if hasattr(instance, 'onchange_cb'): db.flush() for cb in instance.onchange_cb: cb(db, instance, deleted=instance_data) db.commit() return "null" raise PermissionDenied() raise MethodNotAllowed()
def update_room_request_props(db, reqs, assigned=None, requested=None, approved=None): if not reqs: return room_nights = db.query(HotelRoomNight).filter(HotelRoomNight.event == reqs[0].event).all() room_night_lookup = {x.id: x for x in room_nights} for req in reqs: req.requested = False req.approved = False if not req.declined: for rnr in req.room_night_requests: if rnr.requested: req.requested = True if not room_night_lookup[rnr.room_night].restricted: req.approved = True else: for rna in req.room_night_approvals: if rna.room_night == rnr.room_night and rna.approved: req.approved = True req.assigned = bool(req.room_night_assignments) if not assigned is None: req.assigned = assigned if not requested is None: req.requested = requested if not approved is None: req.approved = approved
def create_attendee(uber_model, event, hotel_eligible=True): staff_badge_type = db.query(BadgeType).filter(BadgeType.name == "Staff", BadgeType.event == event).one_or_none() if not staff_badge_type: staff_badge_type = BadgeType(name="Staff", description="Experienced Volunteers") db.flush() badge = Badge( event=event, badge_type=staff_badge_type.id, printed_number=uber_model['badge_num'], printed_name=uber_model['badge_printed_name'], public_name=uber_model['full_name'], search_name=uber_model['full_name'].lower(), first_name=uber_model['first_name'], last_name=uber_model['last_name'], legal_name_matches=(not uber_model['legal_name']), emergency_contact_name=uber_model['ec_name'], emergency_contact_phone=uber_model['ec_phone'], phone=uber_model['cellphone'], email=uber_model['email'], uber_id=uber_model['id'] ) db.add(badge) db.flush() if hotel_eligible: hotel_request = HotelRoomRequest(event=event, badge=badge.id) db.add(hotel_request) db.flush() return badge
def _store_response(self, job_id, iterable): with self.lock: if self.context[job_id]['state'] == "pending": self.context[job_id]['state'] = "immediate" return request_context = json.dumps({ "status": self.context[job_id]['status'], "response_headers": self.context[job_id]['response_headers'] }) del self.context[job_id] progress = json.dumps({ "complete": True, "amount": 0, "messages": "", "status": "" }) if r: r.set(f"{job_id}/context", request_context) for data in iterable: r.append(f"{job_id}/data", data) r.set(f"{job_id}/progress", progress) else: data = bytes() for chunk in iterable: data = data + chunk with self.lock: job = db.query(BackgroundJob).filter( BackgroundJob.uuid == job_id).one() job.progress = progress job.result = data job.context = request_context db.add(job) db.commit()
def update_room_request(db, instance, deleted=None): reqs = [] if request.method == "DELETE" and type(instance) is HotelRoom: print(f"Deleting hotel room {deleted['name']}") print(deleted) reqs = db.query(HotelRoomRequest).filter(HotelRoomRequest.badge.in_(deleted['roommates'])).all() elif type(instance) is RoomNightRequest or type(instance) is RoomNightAssignment or type(instance) is RoomNightApproval: reqs = db.query(HotelRoomRequest).filter(HotelRoomRequest.badge == instance.badge).all() elif type(instance) is HotelRoomRequest: reqs = [instance] elif type(instance) is HotelRoomNight: reqs = db.query(HotelRoomRequest).filter(HotelRoomRequest.event == instance.event).options(joinedload('room_night_requests')).options(joinedload('room_night_approvals')).options(joinedload('room_night_assignments')).all() update_room_request_props(db, reqs) if not type(instance) is HotelRoomRequest: for req in reqs: db.add(req)
def deserialize(cls, data): name = cls.__tablename__ if type(data) is list: single_item = False else: data = [ data, ] single_item = True to_fetch = {} models = [] perms = model_permissions(name) for instance in data: if type(instance) is int: to_fetch[instance] = {} elif "id" in instance: to_fetch[instance['id']] = instance else: if not {"create", "*"}.intersection(perms['*']): raise PermissionDenied( f"User is not permitted to create {name}") models.append(cls(**cls.filter_columns(instance, perms))) existing = db.query(cls).filter(cls.id.in_(to_fetch.keys())).all() for model in existing: fields = cls.filter_columns(to_fetch[model.id], perms, existing=model) for key, val in fields.items(): setattr(model, key, val) models.append(model) if single_item: return models[0] return models
def get_uber_department(): event = config.uber_event if not 'uber_id' in g.data: return "You must provide an uber_id", 406 dept = db.query(Department).filter(Department.uber_id == g.data['uber_id']).one_or_none() if dept: return Department.serialize(dept) return "", 404
def schedulechange(db, schedule): if request.method == "DELETE": shifts = db.query(Shift).filter(Shift.schedule == schedule.id).all() list(map(db.delete, shifts)) jobs = db.query(Job).filter( Job.schedules.any(Schedule.id == schedule.id)).all() for job in jobs: job.schedules.remove(schedule.id) db.add(job) shiftassignments = db.query(ShiftAssignment).filter( ShiftAssignment.shift.in_([x.id for x in shifts])).all() list(map(db.delete, shiftassignments)) shiftsignups = db.query(ShiftSignup).filter( ShiftSignup.schedule == schedule.id).all() for signup in shiftsignups: signup.schedule = None db.add(signup)
def submitted_requests(event): if request.method == "GET": if not check_permission("hotel_request.*.read"): return "", 403 subquery = db.query(RoomNightRequest.badge).filter(RoomNightRequest.event == event, RoomNightRequest.requested == True).distinct().subquery().select() rows = db.query(HotelRoomRequest.id, HotelRoomRequest.declined, HotelRoomRequest.notes, Badge.id, Badge.public_name).filter(HotelRoomRequest.event == event, HotelRoomRequest.badge.in_(subquery)).join(HotelRoomRequest, HotelRoomRequest.badge == Badge.id).all() results = [] for ID, declined, notes, BID, public_name in rows: if not declined: results.append({ "id": ID, "badge": BID, "public_name": public_name, "notes": notes }) return jsonify(results) return "", 406
def slow_call(): if check_permission("circuitbreaker.*.test"): for i in range(10): time.sleep(1) g.progress(i * 0.1, f"Doing a thing (part {i} / 10)") events = db.query(Event).all() return jsonify(Event.serialize(events, g)), 200 return "Permission Denied", 403
def hotel_request_api(event): if not check_permission("rooming.*.request", event=event): return "", 403 if not g.badge: return "Could not locate badge", 404 hotel_request = db.query(HotelRoomRequest).filter(HotelRoomRequest.badge == g.badge.id).one_or_none() if not hotel_request: return "Could not locate hotel room request", 404 return hotel_request_single_api(event, hotel_request.id)
def request_complete(): if not 'id' in request.args: resp = send_file(os.path.join(config.static_path, "checkbox_unchecked.png")) resp.cache_control.max_age = 10 return resp id = request.args['id'] badge = db.query(Badge).filter(Badge.uber_id == id).one_or_none() if not badge: resp = send_file(os.path.join(config.static_path, "checkbox_unchecked.png")) resp.cache_control.max_age = 10 return resp req = db.query(HotelRoomRequest).filter(HotelRoomRequest.badge == badge.id).one_or_none() if req: room_nights = db.query(RoomNightRequest).filter(RoomNightRequest.event == badge.event, RoomNightRequest.badge == badge.id).all() if room_nights: if (req.first_name and req.last_name and any([x.requested for x in room_nights])) or req.declined: return send_file(os.path.join(config.static_path, "checkbox_checked.png")) resp = send_file(os.path.join(config.static_path, "checkbox_unchecked.png")) resp.cache_control.max_age = 10 return resp
def request_search(event, hotel_block): if not check_permission("hotel_block.*.read"): return "", 403 reqs = db.query(HotelRoomRequest).filter( HotelRoomRequest.event == event, HotelRoomRequest.hotel_block == hotel_block, HotelRoomRequest.approved==True, HotelRoomRequest.assigned==False ).join(Badge, Badge.id==HotelRoomRequest.badge).filter( Badge.search_name.contains(g.data['search_term']) ).order_by(g.data['sort']).offset(int(g.data['offset'])).limit(int(g.data['limit'])).all() count = db.query(HotelRoomRequest).filter( HotelRoomRequest.event == event, HotelRoomRequest.hotel_block == hotel_block, HotelRoomRequest.approved==True, HotelRoomRequest.assigned==False ).join(Badge, Badge.id==HotelRoomRequest.badge).filter( Badge.search_name.contains(g.data['search_term']) ).count() return jsonify(requests=HotelRoomRequest.serialize(reqs, serialize_relationships=True, deep=True), count=count), 200
def hotel_approve(event, department): if check_permission("hotel_request.*.approve", event=event, department=department): room_night_request = db.query(RoomNightRequest).filter(RoomNightRequest.room_night == request.json['room_night'], RoomNightRequest.badge == request.json['badge']).one_or_none() if not room_night_request: return "Could not find corresponding request.", 404 approval = db.query(RoomNightApproval).filter(RoomNightApproval.badge == request.json['badge'], RoomNightApproval.room_night == request.json['room_night'], RoomNightApproval.department == department).one_or_none() if request.json['approved'] is None: if approval: db.delete(approval) else: if not approval: approval = RoomNightApproval(event=event, badge=request.json['badge'], department=department) approval.approved = request.json['approved'] approval.room_night = request.json['room_night'] db.add(approval) hotel_room_request = db.query(HotelRoomRequest).filter(HotelRoomRequest.badge == room_night_request.badge).one() update_room_request_props(db, [hotel_room_request,]) db.add(room_night_request) db.commit() return "null", 200 return "", 403
def login(): if request.json['username'] and request.json['password']: user = db.query(User).filter( User.username == request.json['username']).one_or_none() if user: if sha256_crypt.verify(request.json['password'], user.password): badges = db.query(Badge).filter(Badge.user == user.id).all() badge = None if badges: badge = badges[0].id perm_cache = get_permissions(user=user.id) session = Session(user=user.id, badge=badge, last_active=datetime.datetime.now(), secret=str(uuid.uuid4()), permissions=json.dumps(perm_cache)) db.add(session) db.commit() response = jsonify(session.secret) response.set_cookie('session', session.secret) return response return "", 406
def change_password(user_id): if not check_permission(f"user.{user_id}.change_password" ) and not check_permission(f"user.{user_id}.self"): return "", 403 user = db.query(User).filter(User.id == user_id).one() if not 'password' in g.data: return "You must provide a password", 406 if len(g.data['password']) < 8: return "Your password must be at least 8 characters.", 406 user.password = sha256_crypt.hash(g.data['password']) db.add(user) db.commit() return "null", 200
def hotel_requests(event, department): requests = db.query(Badge, HotelRoomRequest).join(BadgeToDepartment, BadgeToDepartment.badge == Badge.id).filter(BadgeToDepartment.department == department).join(HotelRoomRequest, HotelRoomRequest.badge == BadgeToDepartment.badge).all() res = [] for req in requests: badge, roomrequest = req if not check_permission("hotel_request.*.approve", event=event, department=department): continue room_nights = db.query(RoomNightRequest, RoomNightApproval).join(RoomNightApproval, and_(RoomNightApproval.badge == RoomNightRequest.badge, RoomNightApproval.room_night == RoomNightRequest.room_night, RoomNightApproval.department == department), isouter=True).filter(RoomNightRequest.badge == badge.id).all() res.append({ "id": badge.id, "name": badge.public_name, "justification": roomrequest.room_night_justification, "room_nights": { btr.room_night: { "id": btr.id, "requested": btr.requested, "room_night": btr.room_night, "approved": rna.approved if rna else None } for btr, rna in room_nights} }) res = sorted(res, key=lambda x: x['name']) return jsonify(res)