def test_filter_available( dummy_room, create_reservation, create_blocking, has_booking, has_blocking, has_pre_booking, include_pre_bookings, has_pending_blocking, include_pending_blockings, filtered, ): if has_booking: create_reservation( start_dt=datetime.combine(date.today(), time(8)), end_dt=datetime.combine(date.today(), time(10)) ) if has_pre_booking: create_reservation( start_dt=datetime.combine(date.today(), time(10)), end_dt=datetime.combine(date.today(), time(12)), is_accepted=False, ) if has_blocking: create_blocking(state=BlockedRoom.State.accepted) if has_pending_blocking: create_blocking(state=BlockedRoom.State.pending) availabilty_filter = Room.filter_available( get_day_start(date.today()), get_day_end(date.today()), (RepeatFrequency.NEVER, 0), include_pre_bookings=include_pre_bookings, include_pending_blockings=include_pending_blockings, ) assert set(Room.find_all(availabilty_filter)) == (set() if filtered else {dummy_room})
def get_suggestions(filters, limit=None): room_filters = { key: value for key, value in filters.iteritems() if key in ('capacity', 'equipment', 'building', 'text', 'floor') } query = search_for_rooms(room_filters, only_available=False) query = query.filter( ~Room.filter_available(filters['start_dt'], filters['end_dt'], ( filters['repeat_frequency'], filters['repeat_interval']))) rooms = query.all() if filters['repeat_frequency'] == RepeatFrequency.NEVER: return sort_suggestions( get_single_booking_suggestions(rooms, filters['start_dt'], filters['end_dt'], limit=limit)) else: return get_number_of_skipped_days_for_rooms( rooms, filters['start_dt'], filters['end_dt'], filters['repeat_frequency'], filters['repeat_interval'], limit=limit)
def search_for_rooms(filters, only_available=False): query = (Room.query .outerjoin(favorite_room_table, db.and_(favorite_room_table.c.user_id == session.user.id, favorite_room_table.c.room_id == Room.id)) .reset_joinpoint() # otherwise filter_by() would apply to the favorite table .options(raiseload('owner')) .filter(Room.is_active) .order_by(favorite_room_table.c.user_id.is_(None), db.func.indico.natsort(Room.full_name))) criteria = {} if 'capacity' in filters: query = query.filter(db.or_(Room.capacity >= (filters['capacity'] * 0.8), Room.capacity.is_(None))) if 'building' in filters: criteria['building'] = filters['building'] if 'floor' in filters: criteria['floor'] = filters['floor'] query = query.filter_by(**criteria) if 'text' in filters: query = query.filter(_make_room_text_filter(filters['text'])) if filters.get('equipment'): subquery = (db.session.query(RoomEquipmentAssociation) .with_entities(db.func.count(RoomEquipmentAssociation.c.room_id)) .filter( RoomEquipmentAssociation.c.room_id == Room.id, EquipmentType.name.in_(filters['equipment']) ) .join(EquipmentType, RoomEquipmentAssociation.c.equipment_id == EquipmentType.id) .correlate(Room) .as_scalar()) query = query.filter(subquery == len(filters['equipment'])) if filters.get('favorite'): query = query.filter(favorite_room_table.c.user_id.isnot(None)) if filters.get('mine'): ids = get_managed_room_ids(session.user) if ids: query = query.filter(Room.id.in_(ids)) query = _filter_coordinates(query, filters) if not only_available: return query start_dt, end_dt = filters['start_dt'], filters['end_dt'] repeatability = (filters['repeat_frequency'], filters['repeat_interval']) query = query.filter(Room.filter_available(start_dt, end_dt, repeatability, include_pre_bookings=True, include_pending_blockings=True)) if not rb_is_admin(session.user): selected_period_days = (filters['end_dt'] - filters['start_dt']).days booking_limit_days = db.func.coalesce(Room.booking_limit_days, rb_settings.get('booking_limit')) own_rooms = [r.id for r in Room.get_owned_by(session.user)] query = query.filter(db.or_(Room.id.in_(own_rooms) if own_rooms else False, db.and_(Room.filter_bookable_hours(start_dt.time(), end_dt.time()), db.or_(booking_limit_days.is_(None), selected_period_days <= booking_limit_days)))) return query
def test_filter_available(dummy_room, create_reservation, create_blocking, has_booking, has_blocking, has_pre_booking, include_pre_bookings, has_pending_blocking, include_pending_blockings, filtered): if has_booking: create_reservation(start_dt=datetime.combine(date.today(), time(8)), end_dt=datetime.combine(date.today(), time(10))) if has_pre_booking: create_reservation(start_dt=datetime.combine(date.today(), time(10)), end_dt=datetime.combine(date.today(), time(12)), state=ReservationState.pending) if has_blocking: create_blocking(state=BlockedRoom.State.accepted) if has_pending_blocking: create_blocking(state=BlockedRoom.State.pending) availabilty_filter = Room.filter_available(get_day_start(date.today()), get_day_end(date.today()), (RepeatFrequency.NEVER, 0), include_pre_bookings=include_pre_bookings, include_pending_blockings=include_pending_blockings) assert set(Room.find_all(availabilty_filter)) == (set() if filtered else {dummy_room})
def search_for_rooms(filters, allow_admin=False, availability=None): """Search for a room, using the provided filters. :param filters: The filters, provided as a dictionary :param allow_admin: A boolean specifying whether admins have override privileges :param availability: A boolean specifying whether (un)available rooms should be provided, or `None` in case all rooms should be returned. """ query = ( Room.query.outerjoin( favorite_room_table, db.and_( favorite_room_table.c.user_id == session.user.id, favorite_room_table.c.room_id == Room.id)).reset_joinpoint( ) # otherwise filter_by() would apply to the favorite table .options(joinedload('owner').load_only('id')).filter( ~Room.is_deleted).order_by(favorite_room_table.c.user_id.is_(None), db.func.indico.natsort(Room.full_name))) criteria = {} if 'capacity' in filters: query = query.filter(Room.capacity >= filters['capacity']) if 'building' in filters: criteria['building'] = filters['building'] if 'division' in filters: criteria['division'] = filters['division'] query = query.filter_by(**criteria) if 'text' in filters: text = ' '.join(filters['text'].strip().split()) if text.startswith('#') and text[1:].isdigit(): query = query.filter(Room.id == int(text[1:])) else: query = query.filter(_make_room_text_filter(text)) if filters.get('equipment'): subquery = (db.session.query(RoomEquipmentAssociation).with_entities( db.func.count(RoomEquipmentAssociation.c.room_id)).filter( RoomEquipmentAssociation.c.room_id == Room.id, EquipmentType.name.in_(filters['equipment'])).join( EquipmentType, RoomEquipmentAssociation.c.equipment_id == EquipmentType.id).correlate(Room).as_scalar()) query = query.filter(subquery == len(filters['equipment'])) if filters.get('features'): for feature in filters['features']: query = query.filter( Room.available_equipment.any( EquipmentType.features.any(RoomFeature.name == feature))) if filters.get('favorite'): query = query.filter(favorite_room_table.c.user_id.isnot(None)) if filters.get('mine'): ids = get_managed_room_ids(session.user) query = query.filter(Room.id.in_(ids)) query = _filter_coordinates(query, filters) if availability is None: return query start_dt, end_dt = filters['start_dt'], filters['end_dt'] repeatability = (filters['repeat_frequency'], filters['repeat_interval']) availability_filters = [ Room.filter_available(start_dt, end_dt, repeatability, include_blockings=False, include_pre_bookings=False) ] if not (allow_admin and rb_is_admin(session.user)): selected_period_days = (filters['end_dt'] - filters['start_dt']).days booking_limit_days = db.func.coalesce(Room.booking_limit_days, rb_settings.get('booking_limit')) criterion = db.and_( Room.filter_bookable_hours(start_dt.time(), end_dt.time()), Room.filter_nonbookable_periods(start_dt, end_dt), db.or_(booking_limit_days.is_(None), selected_period_days <= booking_limit_days)) unbookable_ids = [ room.id for room in query.filter(db.and_( *availability_filters), ~criterion) if not room.can_override(session.user, allow_admin=False) ] availability_filters.append(~Room.id.in_(unbookable_ids)) availability_criterion = db.and_(*availability_filters) if availability is False: availability_criterion = ~availability_criterion return query.filter(availability_criterion)
def _getAnswer(self): rooms = Room.find_all(Room.filter_available(self._start_dt, self._end_dt, self._repetition)) return [room.id for room in rooms]
def _getAnswer(self): rooms = Room.find_all( Room.filter_available(self._start_dt, self._end_dt, self._repetition)) return [room.id for room in rooms]
def search_for_rooms(filters, availability=None): """Search for a room, using the provided filters. :param filters: The filters, provided as a dictionary :param availability: A boolean specifying whether (un)available rooms should be provided, or `None` in case all rooms should be returned. """ query = ( Room.query.outerjoin( favorite_room_table, db.and_( favorite_room_table.c.user_id == session.user.id, favorite_room_table.c.room_id == Room.id)).reset_joinpoint( ) # otherwise filter_by() would apply to the favorite table .options(raiseload('owner')).filter(Room.is_active).order_by( favorite_room_table.c.user_id.is_(None), db.func.indico.natsort(Room.full_name))) criteria = {} if 'capacity' in filters: query = query.filter(Room.capacity >= filters['capacity']) if 'building' in filters: criteria['building'] = filters['building'] if 'division' in filters: criteria['division'] = filters['division'] query = query.filter_by(**criteria) if 'text' in filters: query = query.filter(_make_room_text_filter(filters['text'])) if filters.get('equipment'): subquery = (db.session.query(RoomEquipmentAssociation).with_entities( db.func.count(RoomEquipmentAssociation.c.room_id)).filter( RoomEquipmentAssociation.c.room_id == Room.id, EquipmentType.name.in_(filters['equipment'])).join( EquipmentType, RoomEquipmentAssociation.c.equipment_id == EquipmentType.id).correlate(Room).as_scalar()) query = query.filter(subquery == len(filters['equipment'])) if filters.get('favorite'): query = query.filter(favorite_room_table.c.user_id.isnot(None)) if filters.get('mine'): ids = get_managed_room_ids(session.user) if ids: query = query.filter(Room.id.in_(ids)) query = _filter_coordinates(query, filters) if availability is None: return query start_dt, end_dt = filters['start_dt'], filters['end_dt'] repeatability = (filters['repeat_frequency'], filters['repeat_interval']) availability_query = Room.filter_available(start_dt, end_dt, repeatability, include_pre_bookings=True, include_pending_blockings=True) if availability is False: availability_query = ~availability_query query = query.filter(availability_query) if not rb_is_admin(session.user): selected_period_days = (filters['end_dt'] - filters['start_dt']).days booking_limit_days = db.func.coalesce(Room.booking_limit_days, rb_settings.get('booking_limit')) own_rooms = [r.id for r in Room.get_owned_by(session.user)] query = query.filter( db.or_( Room.id.in_(own_rooms) if own_rooms else False, db.and_( Room.filter_bookable_hours(start_dt.time(), end_dt.time()), Room.filter_nonbookable_periods(start_dt, end_dt), db.or_(booking_limit_days.is_(None), selected_period_days <= booking_limit_days)))) return query
def search_for_rooms(filters, allow_admin=False, availability=None): """Search for a room, using the provided filters. :param filters: The filters, provided as a dictionary :param allow_admin: A boolean specifying whether admins have override privileges :param availability: A boolean specifying whether (un)available rooms should be provided, or `None` in case all rooms should be returned. """ query = (Room.query .outerjoin(favorite_room_table, db.and_(favorite_room_table.c.user_id == session.user.id, favorite_room_table.c.room_id == Room.id)) .reset_joinpoint() # otherwise filter_by() would apply to the favorite table .options(joinedload('owner').load_only('id')) .filter(~Room.is_deleted) .order_by(favorite_room_table.c.user_id.is_(None), db.func.indico.natsort(Room.full_name))) criteria = {} if 'capacity' in filters: query = query.filter(Room.capacity >= filters['capacity']) if 'building' in filters: criteria['building'] = filters['building'] if 'division' in filters: criteria['division'] = filters['division'] query = query.filter_by(**criteria) if 'text' in filters: text = ' '.join(filters['text'].strip().split()) query = query.filter(_make_room_text_filter(text)) if filters.get('equipment'): subquery = (db.session.query(RoomEquipmentAssociation) .with_entities(db.func.count(RoomEquipmentAssociation.c.room_id)) .filter( RoomEquipmentAssociation.c.room_id == Room.id, EquipmentType.name.in_(filters['equipment']) ) .join(EquipmentType, RoomEquipmentAssociation.c.equipment_id == EquipmentType.id) .correlate(Room) .as_scalar()) query = query.filter(subquery == len(filters['equipment'])) if filters.get('features'): for feature in filters['features']: query = query.filter(Room.available_equipment.any(EquipmentType.features.any(RoomFeature.name == feature))) if filters.get('favorite'): query = query.filter(favorite_room_table.c.user_id.isnot(None)) if filters.get('mine'): ids = get_managed_room_ids(session.user) query = query.filter(Room.id.in_(ids)) query = _filter_coordinates(query, filters) if availability is None: return query start_dt, end_dt = filters['start_dt'], filters['end_dt'] repeatability = (filters['repeat_frequency'], filters['repeat_interval']) availability_filters = [Room.filter_available(start_dt, end_dt, repeatability, include_blockings=False, include_pre_bookings=True)] if not (allow_admin and rb_is_admin(session.user)): selected_period_days = (filters['end_dt'] - filters['start_dt']).days booking_limit_days = db.func.coalesce(Room.booking_limit_days, rb_settings.get('booking_limit')) criterion = db.and_(Room.filter_bookable_hours(start_dt.time(), end_dt.time()), Room.filter_nonbookable_periods(start_dt, end_dt), db.or_(booking_limit_days.is_(None), selected_period_days <= booking_limit_days)) unbookable_ids = [room.id for room in query.filter(db.and_(*availability_filters), ~criterion) if not room.can_override(session.user, allow_admin=False)] availability_filters.append(~Room.id.in_(unbookable_ids)) availability_criterion = db.and_(*availability_filters) if availability is False: availability_criterion = ~availability_criterion return query.filter(availability_criterion)
def search_for_rooms(filters, availability=None): """Search for a room, using the provided filters. :param filters: The filters, provided as a dictionary :param availability: A boolean specifying whether (un)available rooms should be provided, or `None` in case all rooms should be returned. """ query = (Room.query .outerjoin(favorite_room_table, db.and_(favorite_room_table.c.user_id == session.user.id, favorite_room_table.c.room_id == Room.id)) .reset_joinpoint() # otherwise filter_by() would apply to the favorite table .options(raiseload('owner')) .filter(Room.is_active) .order_by(favorite_room_table.c.user_id.is_(None), db.func.indico.natsort(Room.full_name))) criteria = {} if 'capacity' in filters: query = query.filter(Room.capacity >= filters['capacity']) if 'building' in filters: criteria['building'] = filters['building'] if 'floor' in filters: criteria['floor'] = filters['floor'] if 'division' in filters: criteria['division'] = filters['division'] query = query.filter_by(**criteria) if 'text' in filters: query = query.filter(_make_room_text_filter(filters['text'])) if filters.get('equipment'): subquery = (db.session.query(RoomEquipmentAssociation) .with_entities(db.func.count(RoomEquipmentAssociation.c.room_id)) .filter( RoomEquipmentAssociation.c.room_id == Room.id, EquipmentType.name.in_(filters['equipment']) ) .join(EquipmentType, RoomEquipmentAssociation.c.equipment_id == EquipmentType.id) .correlate(Room) .as_scalar()) query = query.filter(subquery == len(filters['equipment'])) if filters.get('favorite'): query = query.filter(favorite_room_table.c.user_id.isnot(None)) if filters.get('mine'): ids = get_managed_room_ids(session.user) if ids: query = query.filter(Room.id.in_(ids)) query = _filter_coordinates(query, filters) if availability is None: return query start_dt, end_dt = filters['start_dt'], filters['end_dt'] repeatability = (filters['repeat_frequency'], filters['repeat_interval']) availability_query = Room.filter_available(start_dt, end_dt, repeatability, include_pre_bookings=True, include_pending_blockings=True) if availability is False: availability_query = ~availability_query query = query.filter(availability_query) if not rb_is_admin(session.user): selected_period_days = (filters['end_dt'] - filters['start_dt']).days booking_limit_days = db.func.coalesce(Room.booking_limit_days, rb_settings.get('booking_limit')) own_rooms = [r.id for r in Room.get_owned_by(session.user)] query = query.filter(db.or_(Room.id.in_(own_rooms) if own_rooms else False, db.and_(Room.filter_bookable_hours(start_dt.time(), end_dt.time()), Room.filter_nonbookable_periods(start_dt, end_dt), db.or_(booking_limit_days.is_(None), selected_period_days <= booking_limit_days)))) return query