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
