def convert_reservation_repeatability(old): return RepeatMapping.getNewMapping(old)
def find_with_filters(filters, avatar): from indico.modules.rb.models.locations import Location equipment_count = len(filters['available_equipment']) equipment_subquery = None if equipment_count: equipment_subquery = ( db.session.query(RoomEquipmentAssociation) .with_entities(func.count(RoomEquipmentAssociation.c.room_id)) .filter( RoomEquipmentAssociation.c.room_id == Room.id, RoomEquipmentAssociation.c.equipment_id.in_(eq.id for eq in filters['available_equipment']) ) .correlate(Room) .as_scalar() ) q = ( Room.query .join(Location.rooms) .filter( Location.id == filters['location'].id if filters['location'] else True, (Room.capacity >= (filters['capacity'] * 0.8)) if filters['capacity'] else True, Room.is_reservable if filters['is_only_public'] else True, Room.is_auto_confirm if filters['is_auto_confirm'] else True, Room.is_active if filters.get('is_only_active', False) else True, (equipment_subquery == equipment_count) if equipment_subquery is not None else True) ) if filters['available'] != -1: repetition = RepeatMapping.getNewMapping(ast.literal_eval(filters['repeatability'])) is_available = Room.filter_available(filters['start_dt'], filters['end_dt'], repetition, include_pre_bookings=filters['include_pre_bookings'], include_pending_blockings=filters['include_pending_blockings']) # Filter the search results if filters['available'] == 0: # booked/unavailable q = q.filter(~is_available) elif filters['available'] == 1: # available q = q.filter(is_available) free_search_columns = ( 'name', 'site', 'division', 'building', 'floor', 'number', 'telephone', 'key_location', 'comments' ) if filters['details']: # Attributes are stored JSON-encoded, so we need to JSON-encode the provided string and remove the quotes # afterwards since PostgreSQL currently does not expose a function to decode a JSON string: # http://www.postgresql.org/message-id/[email protected] details = filters['details'].lower() details_str = u'%{}%'.format(details) details_json = u'%{}%'.format(json.dumps(details)[1:-1]) free_search_criteria = [getattr(Room, c).ilike(details_str) for c in free_search_columns] free_search_criteria.append(Room.attributes.any(cast(RoomAttributeAssociation.value, db.String) .ilike(details_json))) q = q.filter(or_(*free_search_criteria)) q = q.order_by(Room.capacity) rooms = q.all() # Apply a bunch of filters which are *much* easier to do here than in SQL! if filters['is_only_public']: # This may trigger additional SQL queries but is_public is cached and doing this check here is *much* easier rooms = [r for r in rooms if r.is_public] if filters.get('is_only_my_rooms'): rooms = [r for r in rooms if r.is_owned_by(avatar)] if filters['capacity']: # Unless it would result in an empty resultset we don't want to show room which >20% more capacity # than requested. This cannot be done easily in SQL so we do that logic here after the SQL query already # weeded out rooms that are too small matching_capacity_rooms = [r for r in rooms if r.capacity <= filters['capacity'] * 1.2] if matching_capacity_rooms: rooms = matching_capacity_rooms return rooms