def get_icon_data_cte(cls):
cat_alias = db.aliased(cls)
cte_query = (select([
cat_alias.id,
cat_alias.id.label('source_id'), cat_alias.icon_metadata
]).where(cat_alias.parent_id.is_(None)).cte(recursive=True))
rec_query = (select([
cat_alias.id,
db.case({'null': cte_query.c.source_id},
else_=cat_alias.id,
value=db.func.json_typeof(cat_alias.icon_metadata)),
db.case({'null': cte_query.c.icon_metadata},
else_=cat_alias.icon_metadata,
value=db.func.json_typeof(cat_alias.icon_metadata))
]).where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
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 get_protection_cte(cls):
cat_alias = db.aliased(cls)
cte_query = (select([cat_alias.id, cat_alias.protection_mode]).where(
cat_alias.parent_id.is_(None)).cte(recursive=True))
rec_query = (select([
cat_alias.id,
db.case(
{ProtectionMode.inheriting.value: cte_query.c.protection_mode},
else_=cat_alias.protection_mode,
value=cat_alias.protection_mode)
]).where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
def visibility_horizon_query(self):
"""Get a query object that returns the highest category this one is visible from."""
cte_query = (select([
Category.id, Category.parent_id,
db.case([(Category.visibility.is_(None), None)],
else_=(Category.visibility - 1)).label('n'),
literal(0).label('level')
]).where(Category.id == self.id).cte('visibility_horizon',
recursive=True))
parent_query = (select([
Category.id, Category.parent_id,
db.case([
(Category.visibility.is_(None) & cte_query.c.n.is_(None), None)
],
else_=db.func.least(Category.visibility, cte_query.c.n) -
1), cte_query.c.level + 1
]).where(
db.and_(Category.id == cte_query.c.parent_id,
(cte_query.c.n > 0) | cte_query.c.n.is_(None))))
cte_query = cte_query.union_all(parent_query)
return db.session.query(cte_query.c.id, cte_query.c.n).order_by(
cte_query.c.level.desc()).limit(1)
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 get_track_reviewer_abstract_counts(event, user):
"""Get the numbers of abstracts per track for a specific user.
Note that this does not take into account if the user is a
reviewer for a track; it just checks whether the user has
reviewed an abstract in a track or not.
:return: A dict mapping tracks to dicts containing the counts.
"""
# COUNT() does not count NULL values so we pass NULL in case an
# abstract is not in the submitted state. That way we still get
# the track - filtering using WHERE would only include tracks
# that have some abstract in the submitted state.
count_total = db.func.count(Abstract.id)
count_reviewable = db.func.count(
db.case({AbstractState.submitted.value: Abstract.id},
value=Abstract.state))
count_reviewable_reviewed = db.func.count(
db.case({AbstractState.submitted.value: AbstractReview.id},
value=Abstract.state))
count_total_reviewed = db.func.count(AbstractReview.id)
query = (Track.query.with_parent(event).with_entities(
Track, count_total, count_total_reviewed,
count_reviewable - count_reviewable_reviewed).outerjoin(
Track.abstracts_reviewed).outerjoin(
AbstractReview,
db.and_(AbstractReview.abstract_id == Abstract.id,
AbstractReview.user_id == user.id)).group_by(Track.id))
return {
track: {
'total': total,
'reviewed': reviewed,
'unreviewed': unreviewed
}
for track, total, reviewed, unreviewed in query
}
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 duration(cls):
from fossir.modules.events.contributions import Contribution
from fossir.modules.events.sessions.models.blocks import SessionBlock
from fossir.modules.events.timetable.models.breaks import Break
return db.case(
{
TimetableEntryType.SESSION_BLOCK.value:
db.select([SessionBlock.duration]).where(
SessionBlock.id == cls.session_block_id).correlate_except(
SessionBlock).as_scalar(),
TimetableEntryType.CONTRIBUTION.value:
db.select([Contribution.duration]).where(
Contribution.id == cls.contribution_id).correlate_except(
Contribution).as_scalar(),
TimetableEntryType.BREAK.value:
db.select([Break.duration]).where(Break.id == cls.break_id).
correlate_except(Break).as_scalar(),
},
value=cls.type)
