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 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_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 _make_occurrence_date_filter(date_column, default_values, room_columns, value_col=Reservation.repeat_frequency):
notification_before = db.case({RepeatFrequency.WEEK.value: room_columns['weekly'],
RepeatFrequency.MONTH.value: room_columns['monthly']},
else_=room_columns['default'], value=value_col)
notification_before_default = db.case({RepeatFrequency.WEEK.value: default_values['weekly'],
RepeatFrequency.MONTH.value: default_values['monthly']},
else_=default_values['default'], value=value_col)
notification_before_days = db.func.coalesce(notification_before, notification_before_default)
days_until = db.cast(date_column, db.Date) - date.today()
return days_until == notification_before_days
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 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 _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 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 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),
db.cast(ReservationOccurrence.start_dt, db.Date) >= start_date,
db.cast(ReservationOccurrence.end_dt, db.Date) <= 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 _mappers_configured():
from .revisions import EditingRevision, InitialRevisionState, FinalRevisionState
# Editable.state -- the state of the editable itself
cases = db.cast(db.case({
FinalRevisionState.none: db.case({
InitialRevisionState.new: EditableState.new,
InitialRevisionState.ready_for_review: EditableState.ready_for_review,
InitialRevisionState.needs_submitter_confirmation: EditableState.needs_submitter_confirmation
}, value=EditingRevision.initial_state),
# the states resulting in None are always followed by another revision, so we don't ever
# expect the latest revision of an editable to have such a state
FinalRevisionState.replaced: None,
FinalRevisionState.needs_submitter_confirmation: None,
FinalRevisionState.needs_submitter_changes: EditableState.needs_submitter_changes,
FinalRevisionState.accepted: EditableState.accepted,
FinalRevisionState.rejected: EditableState.rejected,
}, value=EditingRevision.final_state), PyIntEnum(EditableState))
query = (select([cases])
.where(EditingRevision.editable_id == Editable.id)
.order_by(EditingRevision.created_dt.desc())
.limit(1)
.correlate_except(EditingRevision))
Editable.state = column_property(query)
# Editable.revision_count -- the number of revisions the editable has
query = (select([db.func.count(EditingRevision.id)])
.where(EditingRevision.editable_id == Editable.id)
.correlate_except(EditingRevision))
Editable.revision_count = column_property(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),
db.cast(ReservationOccurrence.start_dt, db.Date) >= start_date,
db.cast(ReservationOccurrence.end_dt, db.Date) <= 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 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 _clone_timetable(self, new_event):
offset = new_event.start_dt - self.old_event.start_dt
# no need to copy the type; it's set automatically based on the object
attrs = get_simple_column_attrs(TimetableEntry) - {'type', 'start_dt'}
break_strategy = defaultload('break_')
break_strategy.joinedload('own_venue')
break_strategy.joinedload('own_room').lazyload('*')
entry_key_order = db.case({
TimetableEntryType.SESSION_BLOCK: db.func.concat('s', TimetableEntry.id),
TimetableEntryType.CONTRIBUTION: db.func.concat('c', TimetableEntry.id),
TimetableEntryType.BREAK: db.func.concat('b', TimetableEntry.id),
}, value=TimetableEntry.type)
query = (self.old_event.timetable_entries
.options(joinedload('parent').lazyload('*'),
break_strategy)
.order_by(TimetableEntry.parent_id.is_(None).desc(), entry_key_order))
# iterate over all timetable entries; start with top-level
# ones so we can build a mapping that can be used once we
# reach nested entries
entry_map = {}
for old_entry in query:
entry = TimetableEntry()
entry.start_dt = old_entry.start_dt + offset
entry.populate_from_attrs(old_entry, attrs)
if old_entry.parent is not None:
entry.parent = entry_map[old_entry.parent]
if old_entry.session_block is not None:
entry.session_block = self._session_block_map[old_entry.session_block]
if old_entry.contribution is not None:
entry.contribution = self._contrib_map[old_entry.contribution]
if old_entry.break_ is not None:
entry.break_ = self._clone_break(old_entry.break_)
new_event.timetable_entries.append(entry)
entry_map[old_entry] = entry
def is_active(cls):
submissions = (db.session.query(db.func.count(db.m.SurveySubmission.id))
.filter(db.m.SurveySubmission.survey_id == cls.id)
.correlate(Survey)
.as_scalar())
limit_criterion = db.case([(cls.submission_limit.is_(None), True)],
else_=(submissions < cls.submission_limit))
return ~cls.is_deleted & cls.questions.any() & cls.has_started & ~cls.has_ended & limit_criterion
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 get_protection_parent_cte(cls):
cat_alias = db.aliased(cls)
cte_query = (select([cat_alias.id, db.cast(literal(None), db.Integer).label('protection_parent')])
.where(cat_alias.parent_id.is_(None))
.cte(recursive=True))
rec_query = (select([cat_alias.id,
db.case({ProtectionMode.inheriting.value: func.coalesce(cte_query.c.protection_parent, 0)},
else_=cat_alias.id, value=cat_alias.protection_mode)])
.where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
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 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 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 _mapper_configured():
# Session.effective_protection_mode -- the effective protection mode
# (public/protected) of the session, even if it's inheriting it from its
# parent event
query = (select([db.case({ProtectionMode.inheriting.value: Event.effective_protection_mode},
else_=Session.protection_mode, value=Session.protection_mode)])
.where(Event.id == Session.event_id)
.correlate(Session)
.scalar_subquery())
Session.effective_protection_mode = column_property(query, deferred=True)
Session.register_location_events()
Session.register_protection_events()
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 _mapper_configured():
# Contribution.effective_protection_mode -- the effective protection mode
# (public/protected) of the contribution, even if it's inheriting it from
# its event or session
protection_mode_case = db.case([
((Contribution.protection_mode == ProtectionMode.inheriting)
& Contribution.session_id.is_(None), Event.effective_protection_mode),
((Contribution.protection_mode == ProtectionMode.inheriting)
& Contribution.session_id.isnot(None),
select([Session.effective_protection_mode
]).where(Session.id == Contribution.session_id).correlate(
Contribution).scalar_subquery())
],
else_=Contribution.protection_mode)
query = (select([protection_mode_case
]).where(Event.id == Contribution.event_id).correlate(
Contribution).scalar_subquery())
Contribution.effective_protection_mode = column_property(query,
deferred=True)
Contribution.register_location_events()
@listens_for(Contribution.session, 'set')
def _set_session_block(target, value, *unused):
if value is None:
target.session_block = None
@listens_for(Contribution.timetable_entry, 'set')
@no_autoflush
def _set_timetable_entry(target, value, *unused):
if value is None:
target.session_block = None
else:
if target.session is not None:
target.session_block = value.parent.session_block
@listens_for(Contribution.duration, 'set')
def _set_duration(target, value, oldvalue, *unused):
from indico.modules.events.util import register_time_change
if oldvalue in (NEVER_SET, NO_VALUE):
return
if value != oldvalue and target.timetable_entry is not None:
register_time_change(target.timetable_entry)
def duration(cls):
from indico.modules.events.contributions import Contribution
from indico.modules.events.sessions.models.blocks import SessionBlock
from indico.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)
def duration(cls):
from indico.modules.events.contributions import Contribution
from indico.modules.events.sessions.models.blocks import SessionBlock
from indico.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)
