def get_existing_rooms_occurrences(rooms,
start_dt,
end_dt,
repeat_frequency,
repeat_interval,
allow_overlapping=False,
only_accepted=False):
room_ids = [room.id for room in rooms]
query = (ReservationOccurrence.query.filter(
ReservationOccurrence.is_valid,
Reservation.room_id.in_(room_ids)).join(
ReservationOccurrence.reservation).options(
ReservationOccurrence.NO_RESERVATION_USER_STRATEGY,
contains_eager(ReservationOccurrence.reservation)))
if allow_overlapping:
query = query.filter(
db_dates_overlap(ReservationOccurrence, 'start_dt', start_dt,
'end_dt', end_dt))
else:
query = query.filter(ReservationOccurrence.start_dt >= start_dt,
ReservationOccurrence.end_dt <= end_dt)
if only_accepted:
query = query.filter(Reservation.is_accepted)
if repeat_frequency != RepeatFrequency.NEVER:
candidates = ReservationOccurrence.create_series(
start_dt, end_dt, (repeat_frequency, repeat_interval))
dates = [candidate.start_dt for candidate in candidates]
query = query.filter(
db.cast(ReservationOccurrence.start_dt, db.Date).in_(dates))
return group_list(query,
key=lambda obj: obj.reservation.room.id,
sort_by=lambda obj:
(obj.reservation.room_id, obj.start_dt))
def get_room_statistics(room):
data = {
'count': {
'id': 'times_booked',
'values': [],
'note': False
},
'percentage': {
'id': 'occupancy',
'values': [],
'note': True
}
}
ranges = [7, 30, 365]
end_date = date.today()
for days in ranges:
start_date = date.today() - relativedelta(days=days)
count = (ReservationOccurrence.query
.join(ReservationOccurrence.reservation)
.join(Reservation.room)
.filter(Room.id == room.id,
ReservationOccurrence.is_valid,
db_dates_overlap(ReservationOccurrence,
'start_dt', datetime.combine(start_date, time()),
'end_dt', datetime.combine(end_date, time.max)))
.count())
percentage = calculate_rooms_occupancy([room], start_date, end_date) * 100
if count > 0 or percentage > 0:
data['count']['values'].append({'days': days, 'value': count})
data['percentage']['values'].append({'days': days, 'value': percentage})
return data
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.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 get_room_statistics(room):
data = {
'count': {
'id': 'times_booked',
'values': [],
'note': False
},
'percentage': {
'id': 'occupancy',
'values': [],
'note': True
}
}
ranges = [7, 30, 365]
end_date = date.today()
for days in ranges:
start_date = date.today() - relativedelta(days=days)
count = (ReservationOccurrence.query.join(
ReservationOccurrence.reservation).join(Reservation.room).filter(
Room.id == room.id, ReservationOccurrence.is_valid,
db_dates_overlap(ReservationOccurrence, 'start_dt',
datetime.combine(start_date,
time()), 'end_dt',
datetime.combine(end_date,
time.max))).count())
percentage = calculate_rooms_occupancy([room], start_date,
end_date) * 100
if count > 0 or percentage > 0:
data['count']['values'].append({'days': days, 'value': count})
data['percentage']['values'].append({
'days': days,
'value': percentage
})
return data
def _process(self):
from indico.modules.events.contributions import contribution_settings
show_booking_warning = False
if (config.ENABLE_ROOMBOOKING and not self.event.has_ended and self.event.room
and not self.event.room_reservation_links):
# Check if any of the managers of the event already have a booking that overlaps with the event datetime
manager_ids = [p.user.id for p in self.event.acl_entries if p.user]
has_overlap = (ReservationOccurrence.query
.filter(ReservationOccurrence.is_valid,
db.or_(Reservation.booked_for_id.in_(manager_ids),
Reservation.created_by_id.in_(manager_ids)),
db_dates_overlap(ReservationOccurrence,
'start_dt', self.event.start_dt_local,
'end_dt', self.event.end_dt_local),
Reservation.room_id == self.event.room.id,
~Room.is_deleted)
.join(Reservation)
.join(Room)
.has_rows())
show_booking_warning = not has_overlap
has_reference_types = ReferenceType.query.has_rows()
has_event_labels = EventLabel.query.has_rows()
show_draft_warning = (self.event.type_ == EventType.conference and
not contribution_settings.get(self.event, 'published') and
(TimetableEntry.query.with_parent(self.event).has_rows() or
Contribution.query.with_parent(self.event).has_rows()))
return WPEventSettings.render_template('settings.html', self.event, 'settings',
show_booking_warning=show_booking_warning,
show_draft_warning=show_draft_warning,
has_reference_types=has_reference_types,
has_event_labels=has_event_labels)
def filter_overlap(occurrences):
if not occurrences:
raise RuntimeError(
'Cannot check for overlap with empty occurrence list')
return or_(
db_dates_overlap(ReservationOccurrence, 'start_dt', occ.start_dt,
'end_dt', occ.end_dt) for occ in occurrences)
def find_with_filters(filters, avatar=None):
from indico.modules.rb.models.rooms import Room
from indico.modules.rb.models.reservations import Reservation
q = ReservationOccurrence.find(Room.is_active, _join=[Reservation, Room],
_eager=ReservationOccurrence.reservation)
if 'start_dt' in filters and 'end_dt' in filters:
start_dt = filters['start_dt']
end_dt = filters['end_dt']
criteria = []
# We have to check the time range for EACH DAY
for day_start_dt in iterdays(start_dt, end_dt):
# Same date, but the end time
day_end_dt = datetime.combine(day_start_dt.date(), end_dt.time())
criteria.append(db_dates_overlap(ReservationOccurrence, 'start_dt', day_start_dt, 'end_dt', day_end_dt))
q = q.filter(or_(*criteria))
if filters.get('is_only_mine') and avatar:
q = q.filter((Reservation.booked_for_id == avatar.id) | (Reservation.created_by_id == avatar.id))
if filters.get('room_ids'):
q = q.filter(Room.id.in_(filters['room_ids']))
if filters.get('is_only_confirmed_bookings') and not filters.get('is_only_pending_bookings'):
q = q.filter(Reservation.is_accepted)
elif not filters.get('is_only_confirmed_bookings') and filters.get('is_only_pending_bookings'):
q = q.filter(~Reservation.is_accepted)
if filters.get('is_rejected') and filters.get('is_cancelled'):
q = q.filter(Reservation.is_rejected | ReservationOccurrence.is_rejected
| Reservation.is_cancelled | ReservationOccurrence.is_cancelled)
else:
if filters.get('is_rejected'):
q = q.filter(Reservation.is_rejected | ReservationOccurrence.is_rejected)
else:
q = q.filter(~Reservation.is_rejected & ~ReservationOccurrence.is_rejected)
if filters.get('is_cancelled'):
q = q.filter(Reservation.is_cancelled | ReservationOccurrence.is_cancelled)
else:
q = q.filter(~Reservation.is_cancelled & ~ReservationOccurrence.is_cancelled)
if filters.get('is_archived'):
q = q.filter(Reservation.is_archived)
if filters.get('uses_vc'):
q = q.filter(Reservation.uses_vc)
if filters.get('needs_vc_assistance'):
q = q.filter(Reservation.needs_vc_assistance)
if filters.get('needs_assistance'):
q = q.filter(Reservation.needs_assistance)
if filters.get('booked_for_name'):
qs = u'%{}%'.format(filters['booked_for_name'])
q = q.filter(Reservation.booked_for_name.ilike(qs))
if filters.get('reason'):
qs = u'%{}%'.format(filters['reason'])
q = q.filter(Reservation.booking_reason.ilike(qs))
return q.order_by(Room.id)
def get_blocked_rooms(start_dt, end_dt, states=None):
query = (Room.query
.join(Room.blocked_rooms)
.join(BlockedRoom.blocking)
.filter(db_dates_overlap(Blocking, 'start_date', start_dt, 'end_date', end_dt)))
if states:
query = query.filter(BlockedRoom.state.in_(states))
return query.all()
def get_blocked_rooms(start_dt, end_dt, states=None):
query = (Room.query
.join(Room.blocked_rooms)
.join(BlockedRoom.blocking)
.filter(db_dates_overlap(Blocking, 'start_date', start_dt, 'end_date', end_dt)))
if states:
query = query.filter(BlockedRoom.state.in_(states))
return query.all()
def happens_between(cls, from_dt=None, to_dt=None):
if from_dt is not None and to_dt is not None:
# any event that takes place during the specified range
return db_dates_overlap(cls, 'start_dt', from_dt, 'end_dt', to_dt, inclusive=True)
elif from_dt is not None:
# any event that starts on/after the specified date
return cls.start_dt >= from_dt
elif to_dt is not None:
# any event that ends on/before the specifed date
return cls.end_dt <= to_dt
else:
return True
def happens_between(cls, from_dt=None, to_dt=None):
if from_dt is not None and to_dt is not None:
# any event that takes place during the specified range
return db_dates_overlap(cls, 'start_dt', from_dt, 'end_dt', to_dt, inclusive=True)
elif from_dt is not None:
# any event that starts on/after the specified date
return cls.start_dt >= from_dt
elif to_dt is not None:
# any event that ends on/before the specifed date
return cls.end_dt <= to_dt
else:
return True
def _get_stats(date):
today_dt = datetime.combine(date, time())
bookings_today = (ReservationOccurrence.query.filter(
ReservationOccurrence.is_valid,
db_dates_overlap(ReservationOccurrence, 'start_dt', today_dt,
'end_dt', today_dt + timedelta(days=1))).count())
return jsonify(
active_rooms=Room.query.filter_by(is_deleted=False).count(),
buildings=Room.query.distinct(
Room.building).filter_by(is_deleted=False).count(),
pending_bookings=Reservation.query.filter(
Reservation.is_pending, ~Reservation.is_archived).count(),
bookings_today=bookings_today)
def _get_stats(date):
today_dt = datetime.combine(date, time())
bookings_today = (ReservationOccurrence.query
.filter(ReservationOccurrence.is_valid,
db_dates_overlap(ReservationOccurrence,
'start_dt', today_dt,
'end_dt', today_dt + timedelta(days=1)))
.count())
return jsonify(
active_rooms=Room.query.filter_by(is_active=True).count(),
buildings=Room.query.distinct(Room.building).filter_by(is_active=True).count(),
pending_bookings=Reservation.query.filter(Reservation.is_pending, ~Reservation.is_archived).count(),
bookings_today=bookings_today
)
def _process(self, room_ids, start_date, end_date, format):
occurrences = (ReservationOccurrence.query
.join(ReservationOccurrence.reservation)
.filter(Reservation.room_id.in_(room_ids),
ReservationOccurrence.is_valid,
db_dates_overlap(ReservationOccurrence,
'start_dt', datetime.combine(start_date, time()),
'end_dt', datetime.combine(end_date, time.max)))).all()
token = unicode(uuid.uuid4())
headers, rows = generate_spreadsheet_from_occurrences(occurrences)
_export_cache.set(token, {'headers': headers, 'rows': rows}, time=1800)
download_url = url_for('rb.export_bookings_file', format=format, token=token)
return jsonify(url=download_url)
def get_existing_room_occurrences(room, start_dt, end_dt, allow_overlapping=False, only_accepted=False):
query = (ReservationOccurrence.query
.filter(Reservation.room_id == room.id, ReservationOccurrence.is_valid)
.join(ReservationOccurrence.reservation)
.order_by(ReservationOccurrence.start_dt.asc())
.options(ReservationOccurrence.NO_RESERVATION_USER_STRATEGY,
contains_eager(ReservationOccurrence.reservation)))
if allow_overlapping:
query = query.filter(db_dates_overlap(ReservationOccurrence, 'start_dt', start_dt, 'end_dt', end_dt))
else:
query = query.filter(ReservationOccurrence.start_dt >= start_dt, ReservationOccurrence.end_dt <= end_dt)
if only_accepted:
query = query.filter(Reservation.is_accepted)
return query.all()
def get_room_blockings(start_dt=None,
end_dt=None,
created_by=None,
in_rooms_owned_by=None):
query = Blocking.query
if start_dt and not end_dt:
query = query.filter(Blocking.is_active_at(start_dt))
elif start_dt and end_dt:
query = query.filter(
db_dates_overlap(Blocking, 'start_date', start_dt, 'end_date',
end_dt))
criteria = []
if created_by:
criteria.append(Blocking.created_by_user == created_by)
if in_rooms_owned_by:
criteria.append(Room.owner == in_rooms_owned_by)
query = (query.join(Blocking.blocked_rooms).join(BlockedRoom.room))
query = query.filter(db.or_(*criteria))
return query.all()
def _process(self):
show_booking_warning = False
if (config.ENABLE_ROOMBOOKING and not self.event.has_ended and self.event.room
and not self.event.room_reservation_links):
# Check if any of the managers of the event already have a booking that overlaps with the event datetime
manager_ids = [p.user.id for p in self.event.acl_entries if p.user]
has_overlap = (ReservationOccurrence.query
.filter(ReservationOccurrence.is_valid,
db.or_(Reservation.booked_for_id.in_(manager_ids),
Reservation.created_by_id.in_(manager_ids)),
db_dates_overlap(ReservationOccurrence,
'start_dt', self.event.start_dt_local,
'end_dt', self.event.end_dt_local),
Reservation.room_id == self.event.room.id,
~Room.is_deleted)
.join(Reservation)
.join(Room)
.has_rows())
show_booking_warning = not has_overlap
return WPEventSettings.render_template('settings.html', self.event, 'settings',
show_booking_warning=show_booking_warning)
def get_existing_rooms_occurrences(rooms, start_dt, end_dt, repeat_frequency, repeat_interval, allow_overlapping=False,
only_accepted=False):
room_ids = [room.id for room in rooms]
query = (ReservationOccurrence.query
.filter(ReservationOccurrence.is_valid, Reservation.room_id.in_(room_ids))
.join(ReservationOccurrence.reservation)
.options(ReservationOccurrence.NO_RESERVATION_USER_STRATEGY,
contains_eager(ReservationOccurrence.reservation)))
if allow_overlapping:
query = query.filter(db_dates_overlap(ReservationOccurrence, 'start_dt', start_dt, 'end_dt', end_dt))
else:
query = query.filter(ReservationOccurrence.start_dt >= start_dt, ReservationOccurrence.end_dt <= end_dt)
if only_accepted:
query = query.filter(Reservation.is_accepted)
if repeat_frequency != RepeatFrequency.NEVER:
candidates = ReservationOccurrence.create_series(start_dt, end_dt, (repeat_frequency, repeat_interval))
dates = [candidate.start_dt for candidate in candidates]
query = query.filter(db.cast(ReservationOccurrence.start_dt, db.Date).in_(dates))
return group_list(query, key=lambda obj: obj.reservation.room.id,
sort_by=lambda obj: (obj.reservation.room_id, obj.start_dt))
def filter_overlap(occurrences):
return or_(db_dates_overlap(ReservationOccurrence, 'start_dt', occ.start_dt, 'end_dt', occ.end_dt)
for occ in occurrences)
def filter_overlap(occurrences):
return or_(
db_dates_overlap(ReservationOccurrence, 'start_dt', occ.start_dt,
'end_dt', occ.end_dt) for occ in occurrences)
def find_with_filters(cls, filters, user=None):
from indico.modules.rb.models.rooms import Room
from indico.modules.rb.models.reservations import Reservation
q = (ReservationOccurrence.find(
Room.is_active,
_join=[ReservationOccurrence.reservation, Room],
_eager=ReservationOccurrence.reservation).options(
cls.NO_RESERVATION_USER_STRATEGY))
if 'start_dt' in filters and 'end_dt' in filters:
start_dt = filters['start_dt']
end_dt = filters['end_dt']
criteria = []
# We have to check the time range for EACH DAY
for day_start_dt in iterdays(start_dt, end_dt):
# Same date, but the end time
day_end_dt = datetime.combine(day_start_dt.date(),
end_dt.time())
criteria.append(
db_dates_overlap(ReservationOccurrence, 'start_dt',
day_start_dt, 'end_dt', day_end_dt))
q = q.filter(or_(*criteria))
if filters.get('is_only_mine') and user:
q = q.filter((Reservation.booked_for_id == user.id)
| (Reservation.created_by_id == user.id))
if filters.get('room_ids'):
q = q.filter(Room.id.in_(filters['room_ids']))
if filters.get('is_only_confirmed_bookings'
) and not filters.get('is_only_pending_bookings'):
q = q.filter(Reservation.is_accepted)
elif not filters.get('is_only_confirmed_bookings') and filters.get(
'is_only_pending_bookings'):
q = q.filter(~Reservation.is_accepted)
if filters.get('is_rejected') and filters.get('is_cancelled'):
q = q.filter(Reservation.is_rejected
| ReservationOccurrence.is_rejected
| Reservation.is_cancelled
| ReservationOccurrence.is_cancelled)
else:
if filters.get('is_rejected'):
q = q.filter(Reservation.is_rejected
| ReservationOccurrence.is_rejected)
else:
q = q.filter(~Reservation.is_rejected
& ~ReservationOccurrence.is_rejected)
if filters.get('is_cancelled'):
q = q.filter(Reservation.is_cancelled
| ReservationOccurrence.is_cancelled)
else:
q = q.filter(~Reservation.is_cancelled
& ~ReservationOccurrence.is_cancelled)
if filters.get('is_archived'):
q = q.filter(Reservation.is_archived)
if filters.get('uses_vc'):
q = q.filter(Reservation.uses_vc)
if filters.get('needs_vc_assistance'):
q = q.filter(Reservation.needs_vc_assistance)
if filters.get('needs_assistance'):
q = q.filter(Reservation.needs_assistance)
if filters.get('booked_for_name'):
qs = u'%{}%'.format(filters['booked_for_name'])
q = q.filter(Reservation.booked_for_name.ilike(qs))
if filters.get('reason'):
qs = u'%{}%'.format(filters['reason'])
q = q.filter(Reservation.booking_reason.ilike(qs))
return q.order_by(Room.id)
