Exemplo n.º 1
0
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)
Exemplo n.º 2
0
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
Exemplo n.º 3
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)
Exemplo n.º 4
0
    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)
Exemplo n.º 5
0
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
Exemplo n.º 6
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)
Exemplo n.º 7
0
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))
Exemplo n.º 8
0
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()
Exemplo n.º 9
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)
Exemplo n.º 10
0
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))
Exemplo n.º 11
0
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]
Exemplo n.º 12
0
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
Exemplo n.º 14
0
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)
Exemplo n.º 15
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()
Exemplo n.º 16
0
 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)
Exemplo n.º 17
0
 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)
Exemplo n.º 18
0
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
Exemplo n.º 19
0
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')
Exemplo n.º 20
0
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
Exemplo n.º 21
0
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))
Exemplo n.º 22
0
 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)
Exemplo n.º 23
0
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)
Exemplo n.º 24
0
 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)
Exemplo n.º 25
0
 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)
Exemplo n.º 26
0
 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)
Exemplo n.º 27
0
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
Exemplo n.º 28
0
 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)
Exemplo n.º 29
0
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())
Exemplo n.º 30
0
Arquivo: util.py Projeto: fph/indico
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())
Exemplo n.º 31
0
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
Exemplo n.º 32
0
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)
Exemplo n.º 33
0
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
Exemplo n.º 34
0
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
Exemplo n.º 35
0
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)
Exemplo n.º 36
0
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)
Exemplo n.º 37
0
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)
Exemplo n.º 38
0
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)
Exemplo n.º 39
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()
Exemplo n.º 40
0
 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
Exemplo n.º 41
0
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
Exemplo n.º 42
0
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))
Exemplo n.º 43
0
 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
Exemplo n.º 44
0
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()
Exemplo n.º 45
0
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
Exemplo n.º 46
0
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())
Exemplo n.º 47
0
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())