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