def room_assistance_emails():
inner = (Request.query.filter(
Request.type == 'room-assistance',
Request.state == RequestState.accepted).add_columns(
func.jsonb_array_elements_text(
Request.data['occurrences']).label('requested_at')).subquery())
aliased_event = aliased(Event, name='event')
requests = (db.session.query(inner, aliased_event).join(
aliased_event, aliased_event.id == inner.c.event_id).filter(
aliased_event.own_room_id.isnot(None)).filter(
db.cast(inner.c.requested_at, db.Date) == db.cast(
now_utc(), db.Date)).all())
requests = [req._asdict() for req in requests]
template = get_plugin_template_module('emails/room_assistance_emails.html',
requests=requests,
parse_dt=dateutil.parser.parse)
recipients = RoomAssistancePlugin.settings.get(
'room_assistance_recipients')
if recipients:
email = make_email(from_address=config.NO_REPLY_EMAIL,
to_list=recipients,
template=template,
html=True)
send_email(email)
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 conference_room_emails():
start_date, end_date = _get_start_end_date()
date_filter = db.and_(
db.cast(Reservation.start_dt, db.Date) >= start_date,
db.cast(Reservation.start_dt, db.Date) <= end_date)
start_dt = as_utc(datetime.combine(start_date, time()))
end_dt = as_utc(datetime.combine(end_date, time()))
events_by_room = {}
for room in CERNCronjobsPlugin.settings.get('rooms'):
query = (Event.query.filter(~Event.is_deleted,
Event.happens_between(start_dt, end_dt),
Event.own_room_id == room.id).order_by(
Event.start_dt))
events_by_room[room] = _group_by_date(query)
res_events_by_room = {}
for room in CERNCronjobsPlugin.settings.get('reservation_rooms'):
res_events_by_room[room] = _group_by_date(
_get_reservations_query(date_filter, room_id=room.id))
category_ids = [
int(category['id'])
for category in CERNCronjobsPlugin.settings.get('categories')
]
committees = _get_category_events_query(start_dt, end_dt, category_ids)
template = get_plugin_template_module(
'conference_room_email.html',
events_by_room=events_by_room,
res_events_by_room=res_events_by_room,
committees_by_date=_group_by_date(committees))
recipients = CERNCronjobsPlugin.settings.get('conf_room_recipients')
if recipients:
_send_email(recipients, template)
def _get_stats(self, start_date, end_date):
access_start = db.cast(
db.func.coalesce(
db.cast(Request.data['start_dt_override'].astext,
UTCDateTime()),
Event.start_dt).astimezone('Europe/Zurich'), db.Date)
access_end = db.cast(
db.func.coalesce(
db.cast(Request.data['end_dt_override'].astext, UTCDateTime()),
Event.end_dt).astimezone('Europe/Zurich'), db.Date)
query = (db.session.query(
access_start, access_end, db.func.count('*')).filter(
CERNAccessRequest.request_state == CERNAccessRequestState.
active).join(CERNAccessRequest.registration).join(
Registration.event).join(
Request,
db.and_(
Request.event_id == Event.id,
Request.type == 'cern-access',
Request.state == RequestState.accepted)).filter(
_db_dates_overlap(access_start, access_end,
start_date,
end_date)).group_by(
access_start,
access_end))
counts = Counter()
for start, end, count in query:
for offset in range((end - start).days + 1):
day = start + timedelta(days=offset)
counts[day] += count
return dict(counts)
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 _filter_coordinates(query, filters):
try:
sw_lat = filters['sw_lat']
sw_lng = filters['sw_lng']
ne_lat = filters['ne_lat']
ne_lng = filters['ne_lng']
except KeyError:
return query
return query.filter(db.cast(Room.latitude, db.Float) >= sw_lat,
db.cast(Room.latitude, db.Float) <= ne_lat,
db.cast(Room.longitude, db.Float) >= sw_lng,
db.cast(Room.longitude, db.Float) <= ne_lng)
def _query_managed_rooms(user):
# We can get a list of all groups for the user
iterator = chain.from_iterable(multipass.identity_providers[x.provider].get_identity_groups(x.identifier)
for x in user.identities)
groups = {(g.provider.name, g.name) for g in iterator}
# XXX: refactor this once we have a proper ACL for rooms
if multipass.default_group_provider:
groups = {name for provider, name in groups if provider == multipass.default_group_provider.name}
else:
groups = {unicode(group.id) for group in user.local_groups}
attrs = [db.cast(x, JSONB) for x in groups]
is_manager = Room.attributes.any(db.cast(RoomAttributeAssociation.value, JSONB).in_(attrs))
return Room.query.filter(Room.is_active, db.or_(Room.owner == user, is_manager))
def _find_requests(from_dt=None, to_dt=None):
query = (Request.query.options(joinedload(
Request.event)).filter(Request.type == 'room-assistance',
Request.state == RequestState.accepted,
Request.data.has_key('start_dt'))) # noqa
if from_dt:
query = query.filter(
db.cast(Request.data['start_dt'].astext, db.DateTime) >= from_dt)
if to_dt:
query = query.filter(
db.cast(Request.data['start_dt'].astext, db.DateTime) <= to_dt)
return query.all()
def _filter_coordinates(query, filters):
try:
sw_lat = filters['sw_lat']
sw_lng = filters['sw_lng']
ne_lat = filters['ne_lat']
ne_lng = filters['ne_lng']
except KeyError:
return query
return query.filter(db.cast(Room.latitude, db.Float) >= sw_lat,
db.cast(Room.latitude, db.Float) <= ne_lat,
db.cast(Room.longitude, db.Float) >= sw_lng,
db.cast(Room.longitude, db.Float) <= ne_lng)
def _query_managed_rooms(user):
# We can get a list of all groups for the user
iterator = chain.from_iterable(multipass.identity_providers[x.provider].get_identity_groups(x.identifier)
for x in user.identities)
groups = {(g.provider.name, g.name) for g in iterator}
# XXX: refactor this once we have a proper ACL for rooms
if multipass.default_group_provider:
groups = {name for provider, name in groups if provider == multipass.default_group_provider.name}
else:
groups = {unicode(group.id) for group in user.local_groups}
attrs = [db.cast(x, JSONB) for x in groups]
is_manager = Room.attributes.any(db.cast(RoomAttributeAssociation.value, JSONB).in_(attrs))
return Room.query.filter(Room.is_active, db.or_(Room.owner == user, is_manager))
def _find_requests(from_dt=None, to_dt=None):
inner = (Request.query
.filter(Request.type == 'room-assistance',
Request.state == RequestState.accepted)
.add_columns(func.jsonb_array_elements_text(Request.data['occurrences']).label('requested_at'))
.subquery())
aliased_event = aliased(Event, name='event')
query = db.session.query(inner, aliased_event).join(aliased_event, aliased_event.id == inner.c.event_id)
if from_dt:
query = query.filter(db.cast(inner.c.requested_at, db.DateTime) >= from_dt)
if to_dt:
query = query.filter(db.cast(inner.c.requested_at, db.DateTime) <= to_dt)
return [req._asdict() for req in query]
def delete_field_data():
today = now_utc().date()
registration_data = (
RegistrationData.query.join(RegistrationFormFieldData).join(
RegistrationFormField,
RegistrationFormFieldData.field_id == RegistrationFormField.id).
join(RegistrationForm).join(Event).filter(
~RegistrationFormField.is_purged,
RegistrationFormField.retention_period.isnot(None),
db.cast(Event.end_dt, db.Date) +
RegistrationFormField.retention_period <= today).all())
data_by_regform = _group_by_regform(registration_data)
for regform, regform_data in data_by_regform.items():
fields = [data.field_data.field for data in regform_data]
logger.info('Purging fields from regform %r: %r', regform, fields)
for data in regform_data:
logger.debug('Deleting registration field data: %r', data)
# Overwrite with the field's default value.
# This is cleaner than setting the data to 'None' since some fields
# expect structured data e.g. Accommodation & {Single,Multi}Choice.
# This makes the React code relatively simple and we can always distinguish
# purged fields since they have the 'is_purged' flag set to True
data.data = data.field_data.field.field_impl.default_value
if data.field_data.field.field_impl.is_file_field:
_delete_file(data)
data.field_data.field.is_purged = True
db.session.commit()
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
if entry.start_dt.astimezone(
self.event.tzinfo).date() != self.day:
raise NoReportError.wrap_exc(
BadRequest(
_('This action cannot be completed because the event dates'
' have changed. Please reload the page and try again.'
)))
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 _mapper_configured():
from indico.modules.events.registration.models.form_fields import RegistrationFormFieldData
from indico.modules.events.registration.models.items import RegistrationFormItem
@listens_for(Registration.registration_form, 'set')
def _set_event_id(target, value, *unused):
target.event_id = value.event_id
@listens_for(Registration.checked_in, 'set')
def _set_checked_in_dt(target, value, *unused):
if not value:
target.checked_in_dt = None
elif target.checked_in != value:
target.checked_in_dt = now_utc()
@listens_for(Registration.transaction, 'set')
def _set_transaction_id(target, value, *unused):
value.registration = target
query = (select([
db.func.coalesce(
db.func.sum(db.func.jsonb_array_length(RegistrationData.data)), 0)
+ 1
]).where(
db.and_(
(RegistrationData.registration_id == Registration.id),
(RegistrationData.field_data_id == RegistrationFormFieldData.id),
(RegistrationFormFieldData.field_id == RegistrationFormItem.id),
(RegistrationFormItem.input_type == 'accompanying_persons'),
db.cast(
RegistrationFormItem.data['persons_count_against_limit'].
astext, db.Boolean))).correlate_except(
RegistrationData).scalar_subquery())
Registration.occupied_slots = column_property(query, deferred=True)
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 siblings_query(self):
tzinfo = self.event_new.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_new).filter(*criteria)
def get_upcoming_events():
"""Get the global list of upcoming events"""
from indico.modules.events import Event
data = upcoming_events_settings.get_all()
if not data['max_entries'] or not data['entries']:
return
tz = timezone(config.DEFAULT_TIMEZONE)
now = now_utc(False).astimezone(tz)
base_query = (Event.query
.filter(Event.effective_protection_mode == ProtectionMode.public,
~Event.is_deleted,
Event.end_dt.astimezone(tz) > now)
.options(load_only('id', 'title', 'start_dt', 'end_dt')))
queries = []
predicates = {'category': lambda id_: Event.category_id == id_,
'category_tree': lambda id_: Event.category_chain_overlaps(id_) & Event.is_visible_in(id_),
'event': lambda id_: Event.id == id_}
for entry in data['entries']:
delta = timedelta(days=entry['days'])
query = (base_query
.filter(predicates[entry['type']](entry['id']))
.filter(db.cast(Event.start_dt.astimezone(tz), db.Date) > (now - delta).date())
.with_entities(Event, db.literal(entry['weight']).label('weight')))
queries.append(query)
query = (queries[0].union(*queries[1:])
.order_by(db.desc('weight'), Event.start_dt, Event.title)
.limit(data['max_entries']))
for row in query:
event = row[0]
# we cache the result of the function and is_deleted is used in the repr
# and having a broken repr on the cached objects would be ugly
set_committed_value(event, 'is_deleted', False)
yield event
def save_token(token_data, request):
requested_scopes = set(scope_to_list(token_data.get('scope', '')))
application = OAuthApplication.query.filter_by(
client_id=request.client.client_id).one()
link = OAuthApplicationUserLink.query.with_parent(application).with_parent(
request.user).first()
if link is None:
link = OAuthApplicationUserLink(application=application,
user=request.user,
scopes=requested_scopes)
else:
if not requested_scopes:
# for already-authorized apps not specifying a scope uses all scopes the
# user previously granted to the app
requested_scopes = set(link.scopes)
token_data['scope'] = list_to_scope(requested_scopes)
new_scopes = requested_scopes - set(link.scopes)
if new_scopes:
logger.info('New scopes for %r: %s', link, new_scopes)
link.update_scopes(new_scopes)
link.tokens.append(
OAuthToken(access_token=token_data['access_token'],
scopes=requested_scopes))
# get rid of old tokens if there are too many
q = (db.session.query(OAuthToken.id).with_parent(link).filter_by(
_scopes=db.cast(sorted(requested_scopes), ARRAY(db.String))).order_by(
OAuthToken.created_dt.desc()).offset(
MAX_TOKENS_PER_SCOPE).scalar_subquery())
OAuthToken.query.filter(
OAuthToken.id.in_(q)).delete(synchronize_session='fetch')
def get_upcoming_events():
"""Get the global list of upcoming events"""
from indico.modules.events import Event
data = upcoming_events_settings.get_all()
if not data['max_entries'] or not data['entries']:
return
tz = timezone(config.DEFAULT_TIMEZONE)
now = now_utc(False).astimezone(tz)
base_query = (Event.query
.filter(Event.effective_protection_mode == ProtectionMode.public,
~Event.is_deleted,
Event.end_dt.astimezone(tz) > now)
.options(load_only('id', 'title', 'start_dt', 'end_dt')))
queries = []
cols = {'category': Event.category_id,
'event': Event.id}
for entry in data['entries']:
delta = timedelta(days=entry['days'])
query = (base_query
.filter(cols[entry['type']] == entry['id'])
.filter(db.cast(Event.start_dt.astimezone(tz), db.Date) > (now - delta).date())
.with_entities(Event, db.literal(entry['weight']).label('weight')))
queries.append(query)
query = (queries[0].union(*queries[1:])
.order_by(db.desc('weight'), Event.start_dt, Event.title)
.limit(data['max_entries']))
for row in query:
event = row[0]
# we cache the result of the function and is_deleted is used in the repr
# and having a broken repr on the cached objects would be ugly
set_committed_value(event, 'is_deleted', False)
yield event
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 _process(self):
event_start_date = db.cast(TimetableEntry.start_dt.astimezone(self.event_new.tzinfo), db.Date)
entries = self.event_new.timetable_entries.filter(event_start_date == self.date)
latest_end_dt = self._find_latest_end_dt(entries)
if latest_end_dt is None:
event_start = self.event_new.start_dt
latest_end_dt = utc.localize(datetime.combine(self.date, event_start.time()))
return self._format_date(latest_end_dt)
def room_assistance_emails():
requests = (Request.query.filter(
Request.type == 'room-assistance',
Request.state == RequestState.accepted, Event.own_room_id.isnot(None),
db.cast(Request.data['start_dt'].astext,
db.Date) == db.cast(now_utc(), db.Date)).join(Event))
template = get_plugin_template_module('emails/room_assistance_emails.html',
requests=requests,
parse_dt=dateutil.parser.parse)
recipients = RoomAssistancePlugin.settings.get(
'room_assistance_recipients')
if recipients:
email = make_email(from_address=config.NO_REPLY_EMAIL,
to_list=recipients,
template=template,
html=True)
send_email(email)
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_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(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(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 _process(self):
event_start_date = db.cast(
TimetableEntry.start_dt.astimezone(self.event_new.tzinfo), db.Date)
entries = self.event_new.timetable_entries.filter(
event_start_date == self.date)
latest_end_dt = self._find_latest_end_dt(entries)
if latest_end_dt is None:
event_start = self.event_new.start_dt
latest_end_dt = utc.localize(
datetime.combine(self.date, event_start.time()))
return self._format_date(latest_end_dt)
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_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 _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_events_by_year(category_id=None):
"""Get the number of events for each year.
:param category_id: The category ID to get statistics for. Events
from subcategories are also included.
:return: A dictionary mapping years to event counts.
"""
category_filter = Event.category_chain_overlaps(
category_id) if category_id else True
query = (db.session.query(
db.cast(db.extract('year', Event.start_dt),
db.Integer).label('year'), db.func.count()).filter(
~Event.is_deleted,
category_filter).order_by('year').group_by('year'))
return dict(query)
def get_active_bookings(limit, start_dt, last_reservation_id=None, **filters):
criteria = [ReservationOccurrence.start_dt > start_dt]
if last_reservation_id is not None:
criteria.append(db.and_(db.cast(ReservationOccurrence.start_dt, db.Date) >= start_dt,
ReservationOccurrence.reservation_id > last_reservation_id))
query = (_bookings_query(filters, noload_room=True)
.filter(db.or_(*criteria))
.order_by(ReservationOccurrence.start_dt,
ReservationOccurrence.reservation_id,
db.func.indico.natsort(Room.full_name))
.limit(limit))
bookings, total = with_total_rows(query)
rows_left = total - limit if total > limit else total
return group_by_occurrence_date(query, sort_by=lambda obj: (obj.start_dt, obj.reservation_id)), rows_left
def get_active_bookings(limit, start_dt, last_reservation_id=None, **filters):
criteria = [ReservationOccurrence.start_dt > start_dt]
if last_reservation_id is not None:
criteria.append(db.and_(db.cast(ReservationOccurrence.start_dt, db.Date) >= start_dt,
ReservationOccurrence.reservation_id > last_reservation_id))
query = (_bookings_query(filters)
.filter(db.or_(*criteria))
.order_by(ReservationOccurrence.start_dt,
ReservationOccurrence.reservation_id,
db.func.indico.natsort(Room.full_name))
.limit(limit))
bookings, total = with_total_rows(query)
rows_left = total - limit if total > limit else total
return group_by_occurrence_date(query, sort_by=lambda obj: (obj.start_dt, obj.reservation_id)), rows_left
def startup_assistance_emails():
reservations = _get_reservations_query(
db.cast(Reservation.start_dt, db.Date) == date.today())
reservations_by_room = OrderedDict()
for reservation in reservations:
if reservation.room in reservations_by_room:
reservations_by_room[reservation.room].append(reservation)
else:
reservations_by_room[reservation.room] = [reservation]
template = get_plugin_template_module(
'startup_assistance_emails.html',
reservations_by_room=reservations_by_room)
recipients = CERNCronjobsPlugin.settings.get(
'startup_assistance_recipients')
if recipients:
_send_email(recipients, template)
def get_events_by_year(category_id=None):
"""Get the number of events for each year.
:param category_id: The category ID to get statistics for. Events
from subcategories are also included.
:return: An `OrderedDict` mapping years to event counts.
"""
category_filter = Event.category_chain_overlaps(category_id) if category_id else True
query = (db.session
.query(db.cast(db.extract('year', Event.start_dt), db.Integer).label('year'),
db.func.count())
.filter(~Event.is_deleted,
category_filter)
.order_by('year')
.group_by('year'))
return OrderedDict(query)
def get_contribs_by_year(category_id=None):
"""Get the number of contributions for each year.
:param category_id: The category ID to get statistics for.
Contributions from subcategories are also
included.
:return: A dictionary mapping years to contribution counts.
"""
category_filter = Event.category_chain_overlaps(
category_id) if category_id else True
query = (db.session.query(
db.cast(db.extract('year',
TimetableEntry.start_dt), db.Integer).label('year'),
db.func.count()).join(TimetableEntry.event).filter(
TimetableEntry.type == TimetableEntryType.CONTRIBUTION,
~Event.is_deleted,
category_filter).order_by('year').group_by('year'))
return dict(query)
def get_contribs_by_year(category_id=None):
"""Get the number of contributions for each year.
:param category_id: The category ID to get statistics for.
Contributions from subcategories are also
included.
:return: An `OrderedDict` mapping years to contribution counts.
"""
category_filter = Event.category_chain_overlaps(category_id) if category_id else True
query = (db.session
.query(db.cast(db.extract('year', TimetableEntry.start_dt), db.Integer).label('year'),
db.func.count())
.join(TimetableEntry.event)
.filter(TimetableEntry.type == TimetableEntryType.CONTRIBUTION,
~Event.is_deleted,
category_filter)
.order_by('year')
.group_by('year'))
return OrderedDict(query)
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 _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 find_event_vc_rooms(from_dt=None, to_dt=None, distinct=False):
"""Finds VC rooms matching certain criteria
:param from_dt: earliest event/contribution to include
:param to_dt: latest event/contribution to include
:param distinct: if True, never return the same ``(event, vcroom)``
more than once (even if it's linked more than once to
that event)
"""
from indico.modules.vc.models.vc_rooms import VCRoomEventAssociation
query = VCRoomEventAssociation.find()
if distinct:
query = query.distinct(VCRoomEventAssociation.event_id, VCRoomEventAssociation.vc_room_id)
if from_dt is not None or to_dt is not None:
query = query.join(IndexedEvent, IndexedEvent.id == db.cast(VCRoomEventAssociation.event_id, db.String))
if from_dt is not None:
query = query.filter(IndexedEvent.start_date >= from_dt)
if to_dt is not None:
query = query.filter(IndexedEvent.start_date < to_dt)
for vc_room in query:
yield vc_room
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 _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
if entry.start_dt.astimezone(self.event.tzinfo).date() != self.day:
raise NoReportError.wrap_exc(BadRequest(_('This action cannot be completed because the event dates'
' have changed. Please reload the page and try again.')))
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 delete_registrations():
is_expired = db.and_(
RegistrationForm.retention_period.isnot(None),
db.cast(Event.end_dt, db.Date) + RegistrationForm.retention_period <=
now_utc().date())
registrations = (Registration.query.join(
RegistrationForm,
RegistrationForm.id == Registration.registration_form_id).join(
Event).filter(is_expired).all())
regforms = RegistrationForm.query.join(Event).filter(is_expired).all()
for reg in registrations:
logger.info('Purging registration: %r', reg)
for data in reg.data:
if data.field_data.field.field_impl.is_file_field:
_delete_file(data)
db.session.delete(reg)
for regform in regforms:
close_registration(regform)
regform.is_purged = True
db.session.commit()
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_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())
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())