def calculate_rooms_booked_time(rooms, start_date=None, end_date=None):
if end_date is None:
end_date = date.today() - relativedelta(days=1)
if start_date is None:
start_date = end_date - relativedelta(days=29)
# Reservations on working days
reservations = Reservation.find(
Reservation.room_id.in_(r.id for r in rooms),
db.extract('dow', ReservationOccurrence.start_dt).between(1, 5),
ReservationOccurrence.start_dt >= start_date,
ReservationOccurrence.end_dt <= end_date,
ReservationOccurrence.is_valid,
_join=ReservationOccurrence)
rsv_start = db.cast(ReservationOccurrence.start_dt, db.TIME)
rsv_end = db.cast(ReservationOccurrence.end_dt, db.TIME)
slots = ((db.cast(start, db.TIME), db.cast(end, db.TIME))
for start, end in Location.working_time_periods)
# this basically handles all possible ways an occurrence overlaps with each one of the working time slots
overlaps = sum(
db.case([((rsv_start < start) & (rsv_end > end),
db.extract('epoch', end - start)),
((rsv_start < start) & (rsv_end > start) & (rsv_end <= end),
db.extract('epoch', rsv_end - start)),
((rsv_start >= start) & (rsv_start < end) & (rsv_end > end),
db.extract('epoch', end - rsv_start)),
((rsv_start >= start) & (rsv_end <= end),
db.extract('epoch', rsv_end - rsv_start))],
else_=0) for start, end in slots)
return reservations.with_entities(db.func.sum(overlaps)).scalar() or 0
def category_suggestions():
users = (User.query.filter(
~User.is_deleted,
User._all_settings.any(
db.and_(UserSetting.module == 'users',
UserSetting.name == 'suggest_categories',
db.cast(UserSetting.value, db.String) == 'true'))))
for user in users:
existing = {x.category: x for x in user.suggested_categories}
related = set(get_related_categories(user, detailed=False))
for category, score in get_category_scores(user).iteritems():
if score < SUGGESTION_MIN_SCORE:
continue
if (category in related or category.is_deleted
or category.suggestions_disabled
or any(p.suggestions_disabled
for p in category.parent_chain_query)):
continue
logger.debug('Suggesting %s with score %.03f for %s', category,
score, user)
suggestion = existing.get(category) or SuggestedCategory(
category=category, user=user)
suggestion.score = score
user.settings.set('suggest_categories', False)
db.session.commit()
def siblings_query(self):
tzinfo = self.event.tzinfo
day = self.start_dt.astimezone(tzinfo).date()
criteria = (TimetableEntry.id != self.id,
TimetableEntry.parent == self.parent,
db.cast(TimetableEntry.start_dt.astimezone(tzinfo),
db.Date) == day)
return TimetableEntry.query.with_parent(self.event).filter(*criteria)
def get_recent_news():
"""Get a list of recent news for the home page"""
settings = news_settings.get_all()
if not settings['show_recent']:
return []
delta = timedelta(
days=settings['max_age']) if settings['max_age'] else None
return (NewsItem.query.filter(
db.cast(NewsItem.created_dt, db.Date) >
(now_utc() - delta).date() if delta else True).order_by(
NewsItem.created_dt.desc()).limit(settings['max_entries']).all())
def get_protection_parent_cte(self):
cte_query = (select([
Category.id,
db.cast(literal(None), db.Integer).label('protection_parent')
]).where(Category.id == self.id).cte(recursive=True))
rec_query = (select([
Category.id,
db.case(
{
ProtectionMode.inheriting.value:
func.coalesce(cte_query.c.protection_parent, self.id)
},
else_=Category.id,
value=Category.protection_mode)
]).where(Category.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
def _make_occurrence_date_filter():
_default = rb_settings.get('notification_before_days')
_default_weekly = rb_settings.get('notification_before_days_weekly')
_default_monthly = rb_settings.get('notification_before_days_monthly')
notification_before_days_room = db.case(
{
RepeatFrequency.WEEK.value: Room.notification_before_days_weekly,
RepeatFrequency.MONTH.value: Room.notification_before_days_monthly
},
else_=Room.notification_before_days,
value=Reservation.repeat_frequency)
notification_before_days_default = db.case(
{
RepeatFrequency.WEEK.value: _default_weekly,
RepeatFrequency.MONTH.value: _default_monthly
},
else_=_default,
value=Reservation.repeat_frequency)
notification_before_days = db.func.coalesce(
notification_before_days_room, notification_before_days_default)
days_until_occurrence = db.cast(ReservationOccurrence.start_dt,
db.Date) - date.today()
return days_until_occurrence == notification_before_days
def _entries(self):
if self.session_block:
# if we have a session block we reschedule the entries inside that block
for entry in self.session_block.timetable_entry.children:
# the block should only have entries on the same day
assert entry.start_dt.astimezone(
self.event.tzinfo).date() == self.day
yield entry
elif self.session:
# if we have a session we reschedule the blocks of that session on the day
for block in self.session.blocks:
if not block.timetable_entry:
continue
if block.timetable_entry.start_dt.astimezone(
self.event.tzinfo).date() == self.day:
yield block.timetable_entry
else:
# if we are on the top level we reschedule all top-level entries on the day
query = (self.event.timetable_entries.filter(
TimetableEntry.parent_id.is_(None),
db.cast(TimetableEntry.start_dt.astimezone(self.event.tzinfo),
db.Date) == self.day))
for entry in query:
yield entry
def get_session_block_entries(event, day):
"""Returns a list of event top-level session blocks for the given `day`"""
return (event.timetable_entries
.filter(db.cast(TimetableEntry.start_dt.astimezone(event.tzinfo), db.Date) == day.date(),
TimetableEntry.type == TimetableEntryType.SESSION_BLOCK)
.all())