def get_rooms_conflicts(rooms, start_dt, end_dt, repeat_frequency, repeat_interval, blocked_rooms, nonbookable_periods, unbookable_hours, skip_conflicts_with=None, allow_admin=False): rooms_conflicts = defaultdict(set) rooms_pre_conflicts = defaultdict(set) rooms_conflicting_candidates = defaultdict(set) skip_conflicts_with = skip_conflicts_with or [] candidates = ReservationOccurrence.create_series( start_dt, end_dt, (repeat_frequency, repeat_interval)) room_ids = [room.id for room in rooms] query = (ReservationOccurrence.query.filter( Reservation.room_id.in_(room_ids), ReservationOccurrence.is_valid, ReservationOccurrence.filter_overlap(candidates)).join( ReservationOccurrence.reservation).options( ReservationOccurrence.NO_RESERVATION_USER_STRATEGY, contains_eager(ReservationOccurrence.reservation))) if skip_conflicts_with: query = query.filter(~Reservation.id.in_(skip_conflicts_with)) overlapping_occurrences = group_list( query, key=lambda obj: obj.reservation.room.id) for room_id, occurrences in overlapping_occurrences.iteritems(): conflicts = get_room_bookings_conflicts(candidates, occurrences, room_id, skip_conflicts_with) rooms_conflicts[room_id], rooms_pre_conflicts[ room_id], rooms_conflicting_candidates[room_id] = conflicts for room_id, occurrences in blocked_rooms.iteritems(): conflicts, conflicting_candidates = get_room_blockings_conflicts( room_id, candidates, occurrences) rooms_conflicts[room_id] |= conflicts rooms_conflicting_candidates[room_id] |= conflicting_candidates if not (allow_admin and rb_is_admin(session.user)): for room_id, occurrences in nonbookable_periods.iteritems(): room = Room.get_one(room_id) if not room.can_override(session.user, allow_admin=allow_admin): conflicts, conflicting_candidates = get_room_nonbookable_periods_conflicts( candidates, occurrences) rooms_conflicts[room_id] |= conflicts rooms_conflicting_candidates[room_id] |= conflicting_candidates for room_id, occurrences in unbookable_hours.iteritems(): room = Room.get_one(room_id) if not room.can_override(session.user, allow_admin=allow_admin): conflicts, conflicting_candidates = get_room_unbookable_hours_conflicts( candidates, occurrences) rooms_conflicts[room_id] |= conflicts rooms_conflicting_candidates[room_id] |= conflicting_candidates rooms_conflicting_candidates = defaultdict( list, ((k, list(v)) for k, v in rooms_conflicting_candidates.items())) return rooms_conflicts, rooms_pre_conflicts, rooms_conflicting_candidates
def filter_available(start_dt, end_dt, repetition, include_blockings=True, include_pre_bookings=True, include_pending_blockings=False): """Returns a SQLAlchemy filter criterion ensuring that the room is available during the given time.""" # Check availability against reservation occurrences dummy_occurrences = ReservationOccurrence.create_series(start_dt, end_dt, repetition) overlap_criteria = ReservationOccurrence.filter_overlap(dummy_occurrences) reservation_criteria = [Reservation.room_id == Room.id, ReservationOccurrence.is_valid, overlap_criteria] if not include_pre_bookings: reservation_criteria.append(Reservation.is_accepted) occurrences_filter = (Reservation.query .join(ReservationOccurrence.reservation) .filter(and_(*reservation_criteria))) # Check availability against blockings filters = ~occurrences_filter.exists() if include_blockings: if include_pending_blockings: valid_states = (BlockedRoom.State.accepted, BlockedRoom.State.pending) else: valid_states = (BlockedRoom.State.accepted,) # TODO: only take blockings into account which the user cannot override blocking_criteria = [Room.id == BlockedRoom.room_id, BlockedRoom.state.in_(valid_states), db_dates_overlap(Blocking, 'start_date', end_dt.date(), 'end_date', start_dt.date(), inclusive=True)] blockings_filter = (BlockedRoom.query .join(Blocking.blocked_rooms) .filter(and_(*blocking_criteria))) return filters & ~blockings_filter.exists() return filters
def filter_available(start_dt, end_dt, repetition, include_pre_bookings=True, include_pending_blockings=True): """Returns a SQLAlchemy filter criterion ensuring that the room is available during the given time.""" # Check availability against reservation occurrences dummy_occurrences = ReservationOccurrence.create_series( start_dt, end_dt, repetition) overlap_criteria = ReservationOccurrence.filter_overlap( dummy_occurrences) reservation_criteria = [ Reservation.room_id == Room.id, ReservationOccurrence.is_valid, overlap_criteria ] if not include_pre_bookings: reservation_criteria.append(Reservation.is_accepted) occurrences_filter = Reservation.occurrences.any( and_(*reservation_criteria)) # Check availability against blockings if include_pending_blockings: valid_states = (BlockedRoom.State.accepted, BlockedRoom.State.pending) else: valid_states = (BlockedRoom.State.accepted, ) blocking_criteria = [ BlockedRoom.blocking_id == Blocking.id, BlockedRoom.state.in_(valid_states), Blocking.start_date <= start_dt.date(), Blocking.end_date >= end_dt.date() ] blockings_filter = Room.blocked_rooms.any(and_(*blocking_criteria)) return ~occurrences_filter & ~blockings_filter
def find_overlapping_with(room, occurrences, skip_reservation_id=None): return Reservation.find( Reservation.room == room, Reservation.id != skip_reservation_id, ReservationOccurrence.is_valid, ReservationOccurrence.filter_overlap(occurrences), _join=ReservationOccurrence)
def get_rooms_conflicts(rooms, start_dt, end_dt, repeat_frequency, repeat_interval, blocked_rooms, nonbookable_periods, unbookable_hours): rooms_conflicts = defaultdict(list) rooms_pre_conflicts = defaultdict(list) candidates = ReservationOccurrence.create_series( start_dt, end_dt, (repeat_frequency, repeat_interval)) room_ids = [room.id for room in rooms] query = (ReservationOccurrence.query.filter( Reservation.room_id.in_(room_ids), ReservationOccurrence.is_valid, ReservationOccurrence.filter_overlap(candidates)).join( ReservationOccurrence.reservation).options( ReservationOccurrence.NO_RESERVATION_USER_STRATEGY, contains_eager(ReservationOccurrence.reservation))) overlapping_occurrences = group_list( query, key=lambda obj: obj.reservation.room.id) for room_id, occurrences in overlapping_occurrences.iteritems(): rooms_conflicts[room_id], rooms_pre_conflicts[ room_id] = get_room_bookings_conflicts(candidates, occurrences) for room_id, occurrences in blocked_rooms.iteritems(): rooms_conflicts[room_id] += get_room_blockings_conflicts( room_id, candidates, occurrences) for room_id, occurrences in nonbookable_periods.iteritems(): rooms_conflicts[room_id] += get_room_nonbookable_periods_conflicts( candidates, occurrences) for room_id, occurrences in unbookable_hours.iteritems(): rooms_conflicts[room_id] += get_room_unbookable_hours_conflicts( candidates, occurrences) return rooms_conflicts, rooms_pre_conflicts
def get_rooms_conflicts(rooms, start_dt, end_dt, repeat_frequency, repeat_interval, blocked_rooms, nonbookable_periods, unbookable_hours): rooms_conflicts = defaultdict(list) rooms_pre_conflicts = defaultdict(list) candidates = ReservationOccurrence.create_series(start_dt, end_dt, (repeat_frequency, repeat_interval)) room_ids = [room.id for room in rooms] query = (ReservationOccurrence.query .filter(Reservation.room_id.in_(room_ids), ReservationOccurrence.is_valid, ReservationOccurrence.filter_overlap(candidates)) .join(ReservationOccurrence.reservation) .options(ReservationOccurrence.NO_RESERVATION_USER_STRATEGY, contains_eager(ReservationOccurrence.reservation))) overlapping_occurrences = group_list(query, key=lambda obj: obj.reservation.room.id) for room_id, occurrences in overlapping_occurrences.iteritems(): rooms_conflicts[room_id], rooms_pre_conflicts[room_id] = get_room_bookings_conflicts(candidates, occurrences) for room_id, occurrences in blocked_rooms.iteritems(): rooms_conflicts[room_id] += get_room_blockings_conflicts(room_id, candidates, occurrences) for room_id, occurrences in nonbookable_periods.iteritems(): rooms_conflicts[room_id] += get_room_nonbookable_periods_conflicts(candidates, occurrences) for room_id, occurrences in unbookable_hours.iteritems(): rooms_conflicts[room_id] += get_room_unbookable_hours_conflicts(candidates, occurrences) return rooms_conflicts, rooms_pre_conflicts
def get_rooms_conflicts(rooms, start_dt, end_dt, repeat_frequency, repeat_interval, blocked_rooms, nonbookable_periods, unbookable_hours, skip_conflicts_with=None): rooms_conflicts = defaultdict(list) rooms_pre_conflicts = defaultdict(list) skip_conflicts_with = skip_conflicts_with or [] candidates = ReservationOccurrence.create_series(start_dt, end_dt, (repeat_frequency, repeat_interval)) room_ids = [room.id for room in rooms] query = (ReservationOccurrence.query .filter(Reservation.room_id.in_(room_ids), ReservationOccurrence.is_valid, ReservationOccurrence.filter_overlap(candidates)) .join(ReservationOccurrence.reservation) .options(ReservationOccurrence.NO_RESERVATION_USER_STRATEGY, contains_eager(ReservationOccurrence.reservation))) if skip_conflicts_with: query = query.filter(~Reservation.id.in_(skip_conflicts_with)) overlapping_occurrences = group_list(query, key=lambda obj: obj.reservation.room.id) for room_id, occurrences in overlapping_occurrences.iteritems(): rooms_conflicts[room_id], rooms_pre_conflicts[room_id] = get_room_bookings_conflicts(candidates, occurrences, skip_conflicts_with) for room_id, occurrences in blocked_rooms.iteritems(): rooms_conflicts[room_id] += get_room_blockings_conflicts(room_id, candidates, occurrences) # TODO: do proper per-room override checks if not rb_is_admin(session.user): for room_id, occurrences in nonbookable_periods.iteritems(): rooms_conflicts[room_id] += get_room_nonbookable_periods_conflicts(candidates, occurrences) for room_id, occurrences in unbookable_hours.iteritems(): rooms_conflicts[room_id] += get_room_unbookable_hours_conflicts(candidates, occurrences) return rooms_conflicts, rooms_pre_conflicts
def filter_available(start_dt, end_dt, repetition, include_pre_bookings=True, include_pending_blockings=True): """Returns a SQLAlchemy filter criterion ensuring that the room is available during the given time.""" # Check availability against reservation occurrences dummy_occurrences = ReservationOccurrence.create_series(start_dt, end_dt, repetition) overlap_criteria = ReservationOccurrence.filter_overlap(dummy_occurrences) reservation_criteria = [Reservation.room_id == Room.id, ReservationOccurrence.is_valid, overlap_criteria] if not include_pre_bookings: reservation_criteria.append(Reservation.is_accepted) occurrences_filter = (Reservation.query .join(ReservationOccurrence.reservation) .filter(and_(*reservation_criteria))) # Check availability against blockings if include_pending_blockings: valid_states = (BlockedRoom.State.accepted, BlockedRoom.State.pending) else: valid_states = (BlockedRoom.State.accepted,) blocking_criteria = [Room.id == BlockedRoom.room_id, BlockedRoom.state.in_(valid_states), db_dates_overlap(Blocking, 'start_date', end_dt.date(), 'end_date', start_dt.date(), inclusive=True)] blockings_filter = (BlockedRoom.query .join(Blocking.blocked_rooms) .filter(and_(*blocking_criteria))) return ~occurrences_filter.exists() & ~blockings_filter.exists()
def find_overlapping_with(room, occurrences, skip_reservation_id=None): return Reservation.find( Reservation.room == room, Reservation.id != skip_reservation_id, ReservationOccurrence.is_valid, ReservationOccurrence.filter_overlap(occurrences), _join=ReservationOccurrence, )
def test_filter_overlap(create_occurrence, overlapping_combination_from_2am_to_4am): start_hour, end_hour, expected = overlapping_combination_from_2am_to_4am() occ1 = create_occurrence(start_dt=date.today() + relativedelta(hour=2), end_dt=date.today() + relativedelta(hour=4)) occ2 = ReservationOccurrence(start_dt=date.today() + relativedelta(hour=start_hour), end_dt=date.today() + relativedelta(hour=end_hour)) overlap_filter = ReservationOccurrence.filter_overlap([occ2]) assert (occ1 in ReservationOccurrence.find_all(overlap_filter)) == expected
def get_rooms_conflicts(rooms, start_dt, end_dt, repeat_frequency, repeat_interval, blocked_rooms, nonbookable_periods, unbookable_hours, skip_conflicts_with=None, allow_admin=False): rooms_conflicts = defaultdict(set) rooms_pre_conflicts = defaultdict(set) rooms_conflicting_candidates = defaultdict(set) skip_conflicts_with = skip_conflicts_with or [] candidates = ReservationOccurrence.create_series(start_dt, end_dt, (repeat_frequency, repeat_interval)) room_ids = [room.id for room in rooms] query = (ReservationOccurrence.query .filter(Reservation.room_id.in_(room_ids), ReservationOccurrence.is_valid, ReservationOccurrence.filter_overlap(candidates)) .join(ReservationOccurrence.reservation) .options(ReservationOccurrence.NO_RESERVATION_USER_STRATEGY, contains_eager(ReservationOccurrence.reservation))) if skip_conflicts_with: query = query.filter(~Reservation.id.in_(skip_conflicts_with)) overlapping_occurrences = group_list(query, key=lambda obj: obj.reservation.room.id) for room_id, occurrences in overlapping_occurrences.iteritems(): conflicts = get_room_bookings_conflicts(candidates, occurrences, room_id, skip_conflicts_with) rooms_conflicts[room_id], rooms_pre_conflicts[room_id], rooms_conflicting_candidates[room_id] = conflicts for room_id, occurrences in blocked_rooms.iteritems(): conflicts, conflicting_candidates = get_room_blockings_conflicts(room_id, candidates, occurrences) rooms_conflicts[room_id] |= conflicts rooms_conflicting_candidates[room_id] |= conflicting_candidates if not (allow_admin and rb_is_admin(session.user)): for room_id, occurrences in nonbookable_periods.iteritems(): room = Room.get_one(room_id) if not room.can_override(session.user, allow_admin=allow_admin): conflicts, conflicting_candidates = get_room_nonbookable_periods_conflicts(candidates, occurrences) rooms_conflicts[room_id] |= conflicts rooms_conflicting_candidates[room_id] |= conflicting_candidates for room_id, occurrences in unbookable_hours.iteritems(): room = Room.get_one(room_id) if not room.can_override(session.user, allow_admin=allow_admin): conflicts, conflicting_candidates = get_room_unbookable_hours_conflicts(candidates, occurrences) rooms_conflicts[room_id] |= conflicts rooms_conflicting_candidates[room_id] |= conflicting_candidates rooms_conflicting_candidates = defaultdict(list, ((k, list(v)) for k, v in rooms_conflicting_candidates.items())) return rooms_conflicts, rooms_pre_conflicts, rooms_conflicting_candidates