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 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 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
