def __eq__(self, other): if isinstance(other, db.m.Event): return db.and_(self.cls.link_type == VCRoomLinkType.event, self.cls.linked_event_id == other.id) elif isinstance(other, db.m.SessionBlock): return db.and_(self.cls.link_type == VCRoomLinkType.block, self.cls.session_block_id == other.id) elif isinstance(other, db.m.Contribution): return db.and_(self.cls.link_type == VCRoomLinkType.contribution, self.cls.contribution_id == other.id) else: raise TypeError('Unexpected object type {}: {}'.format(type(other), other))
def get_events_with_abstract_persons(user, dt=None): """ Return a dict of event ids and the abstract submission related roles the user has in that event. :param user: A `User` :param dt: Only include events taking place on/after that date """ data = defaultdict(set) bad_states = {AbstractState.withdrawn, AbstractState.rejected} # submitter query = (Abstract.query.filter(~Event.is_deleted, ~Abstract.is_deleted, ~Abstract.state.in_(bad_states), Event.ends_after(dt), Abstract.submitter == user).join( Abstract.event).options( load_only('event_id'))) for abstract in query: data[abstract.event_id].add('abstract_submitter') # person abstract_criterion = db.and_(~Abstract.state.in_(bad_states), ~Abstract.is_deleted) query = (user.event_persons.filter( ~Event.is_deleted, Event.ends_after(dt), EventPerson.abstract_links.any( AbstractPersonLink.abstract.has(abstract_criterion))).join( EventPerson.event).options(load_only('event_id'))) for person in query: data[person.event_id].add('abstract_person') return data
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 get_related_categories(user, detailed=True): """Gets the related categories of a user for the dashboard""" favorites = set() if user.favorite_categories: favorites = set(Category.query .filter(Category.id.in_(c.id for c in user.favorite_categories)) .options(undefer('chain_titles')) .all()) managed = set(Category.query .filter(Category.acl_entries.any(db.and_(CategoryPrincipal.type == PrincipalType.user, CategoryPrincipal.user == user, CategoryPrincipal.has_management_role())), ~Category.is_deleted) .options(undefer('chain_titles'))) if not detailed: return favorites | managed res = {} for categ in favorites | managed: res[(categ.title, categ.id)] = { 'categ': categ, 'favorite': categ in favorites, 'managed': categ in managed, 'path': truncate_path(categ.chain_titles[:-1], chars=50) } return OrderedDict(sorted(res.items(), key=itemgetter(0)))
def _filter_by_sessions(self, session_ids, added_since): sid_query = Contribution.session_id.in_(set(session_ids)) session_query = db.and_( AttachmentFolder.link_type == LinkType.session, AttachmentFolder.session.has( Session.id.in_(session_ids) & ~Session.is_deleted)) contrib_query = db.and_( AttachmentFolder.link_type == LinkType.contribution, AttachmentFolder.contribution.has(sid_query & ~Contribution.is_deleted)) subcontrib_query = db.and_( AttachmentFolder.link_type == LinkType.subcontribution, AttachmentFolder.subcontribution.has(sid_query & ~SubContribution.is_deleted & ~Contribution.is_deleted)) return self._build_base_query(added_since).filter( db.or_(session_query, contrib_query, subcontrib_query)).all()
def __eq__(self, other): if isinstance(other, db.m.Category): return db.and_(self.cls.link_type == LinkType.category, self.cls.category_id == other.id) elif isinstance(other, db.m.Event): return db.and_(self.cls.link_type == LinkType.event, self.cls.linked_event_id == other.id) elif isinstance(other, db.m.Session): return db.and_(self.cls.link_type == LinkType.session, self.cls.session_id == other.id) elif isinstance(other, db.m.Contribution): return db.and_(self.cls.link_type == LinkType.contribution, self.cls.contribution_id == other.id) elif isinstance(other, db.m.SubContribution): return db.and_(self.cls.link_type == LinkType.subcontribution, self.cls.subcontribution_id == other.id) else: raise ValueError('Unexpected object type {}: {}'.format( type(other), other))
def is_submission_in_progress(survey): """Check whether the current user has a survey submission in progress""" from fossir.modules.events.surveys.models.surveys import Survey if session.user: query = (Survey.query.with_parent(survey.event) .filter(Survey.submissions.any(db.and_(~SurveySubmission.is_submitted, SurveySubmission.user == session.user)))) user_incomplete_surveys = set(query) return survey in user_incomplete_surveys else: return False
def _get_persons(event, condition): """Queries event persons linked to contributions in the event, filtered using the condition provided.""" return (event.persons.filter( EventPerson.contribution_links.any( db.and_( condition, ContributionPersonLink.contribution.has( ~Contribution.is_deleted)))).options( joinedload('contribution_links').joinedload( 'contribution')).order_by( db.func.lower(EventPerson.last_name)))
def _paper_last_revision(cls): # Incompatible with joinedload subquery = (db.select([ db.func.max(PaperRevision.submitted_dt) ]).where(PaperRevision._contribution_id == cls.id).correlate_except( PaperRevision).as_scalar()) return db.relationship('PaperRevision', uselist=False, lazy=True, viewonly=True, primaryjoin=db.and_( PaperRevision._contribution_id == cls.id, PaperRevision.submitted_dt == subquery))
def was_survey_submitted(survey): """Check whether the current user has submitted a survey""" from fossir.modules.events.surveys.models.surveys import Survey query = (Survey.query.with_parent(survey.event) .filter(Survey.submissions.any(db.and_(SurveySubmission.is_submitted, SurveySubmission.user == session.user)))) user_submitted_surveys = set(query) if session.user and survey in user_submitted_surveys: return True submission_id = session.get('submitted_surveys', {}).get(survey.id) if submission_id is None: return False return SurveySubmission.find(id=submission_id, is_submitted=True).has_rows()
def _make_attachment_count_column_property(cls): from fossir.modules.attachments.models.attachments import Attachment from fossir.modules.attachments.models.folders import AttachmentFolder assert cls.attachment_count is None query = (db.select([db.func.count(Attachment.id)]).select_from( db.join( Attachment, AttachmentFolder, Attachment.folder_id == AttachmentFolder.id)).where( db.and_( ~AttachmentFolder.is_deleted, ~Attachment.is_deleted, (getattr(AttachmentFolder, cls.ATTACHMENT_FOLDER_ID_COLUMN) == cls.id))).correlate_except(AttachmentFolder, Attachment)) cls.attachment_count = db.column_property(query, deferred=True)
def add_contrib_data(): has_contrib = (EventPerson.contribution_links.any( ContributionPersonLink.contribution.has(~Contribution.is_deleted))) has_subcontrib = EventPerson.subcontribution_links.any( SubContributionPersonLink.subcontribution.has(db.and_( ~SubContribution.is_deleted, SubContribution.contribution.has(~Contribution.is_deleted)))) query = (Event.query .options(load_only('id')) .options(noload('*')) .filter(~Event.is_deleted, Event.ends_after(dt), Event.persons.any((EventPerson.user_id == user.id) & (has_contrib | has_subcontrib)))) for event in query: data[event.id].add('contributor')
def visible_categories_cte(self): """ Get a sqlalchemy select for the visible categories within this category, including the category itself. """ cte_query = (select([ Category.id, literal(0).label('level') ]).where((Category.id == self.id) & (Category.visibility.is_(None) | (Category.visibility > 0))).cte( 'visibility_chain', recursive=True)) parent_query = (select([Category.id, cte_query.c.level + 1]).where( db.and_( Category.parent_id == cte_query.c.id, db.or_(Category.visibility.is_(None), Category.visibility > cte_query.c.level + 1)))) return cte_query.union_all(parent_query)
def _filter_list_entries(self, query, filters): if not (filters.get('fields') or filters.get('items')): return query field_types = { str(f.id): f.field_impl for f in self.regform.form_items if f.is_field and not f.is_deleted and ( f.parent_id is None or not f.parent.is_deleted) } field_filters = { field_id: data_list for field_id, data_list in filters['fields'].iteritems() if field_id in field_types } if not field_filters and not filters['items']: return query criteria = [ db.and_(RegistrationFormFieldData.field_id == field_id, field_types[field_id].create_sql_filter(data_list)) for field_id, data_list in field_filters.iteritems() ] items_criteria = [] if 'checked_in' in filters['items']: checked_in_values = filters['items']['checked_in'] # If both values 'true' and 'false' are selected, there's no point in filtering if len(checked_in_values) == 1: items_criteria.append( Registration.checked_in == bool(int(checked_in_values[0]))) if 'state' in filters['items']: states = [ RegistrationState(int(state)) for state in filters['items']['state'] ] items_criteria.append(Registration.state.in_(states)) if field_filters: subquery = (RegistrationData.query.with_entities( db.func.count(RegistrationData.registration_id) ).join(RegistrationData.field_data).filter( RegistrationData.registration_id == Registration.id).filter( db.or_(*criteria)).correlate(Registration).as_scalar()) query = query.filter(subquery == len(field_filters)) return query.filter(db.or_(*items_criteria))
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_members(self): from fossir.modules.users.models.users import User if self.group is None: warn('Tried to get members for invalid group {}'.format(self)) return set() # We actually care about Users, not identities here! emails = set() identifiers = set() for identity_info in self.group: identifiers.add(identity_info.identifier) emails |= {x.lower() for x in identity_info.data.getlist('email') if x} if not identifiers and not emails: return set() return set(User.query.outerjoin(Identity).filter( ~User.is_deleted, db.or_( User.all_emails.contains(db.func.any(list(emails))), db.and_( Identity.provider == self.provider, Identity.identifier.in_(identifiers) ) )))
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_event_regforms(event, user, with_registrations=False): """Get registration forms with information about user registrations. :param event: the `Event` to get registration forms for :param user: A `User` :param with_registrations: Whether to return the user's registration instead of just whether they have one """ if not user: registered_user = db.literal(None if with_registrations else False) elif with_registrations: registered_user = Registration else: registered_user = RegistrationForm.registrations.any((Registration.user == user) & Registration.is_active) query = (RegistrationForm.query.with_parent(event) .with_entities(RegistrationForm, registered_user) .options(undefer('active_registration_count')) .order_by(db.func.lower(RegistrationForm.title))) if with_registrations: query = query.outerjoin(Registration, db.and_(Registration.registration_form_id == RegistrationForm.id, Registration.user == user, Registration.is_active)) return query.all()
def _filter_list_entries(self, query, filters): criteria = [] field_filters = filters.get('fields') item_filters = filters.get('items') extra_filters = filters.get('extra') if not (field_filters or item_filters or extra_filters): return query if field_filters: for contribution_type_id, field_values in field_filters.iteritems( ): criteria.append( Abstract.field_values.any( db.and_( AbstractFieldValue.contribution_field_id == contribution_type_id, AbstractFieldValue.data.op('#>>')('{}').in_( field_values)))) if item_filters: static_filters = { 'accepted_track': Abstract.accepted_track_id, 'accepted_contrib_type': Abstract.accepted_contrib_type_id, 'submitted_contrib_type': Abstract.submitted_contrib_type_id, 'submitted_for_tracks': Abstract.submitted_for_tracks, 'reviewed_for_tracks': Abstract.reviewed_for_tracks } for key, column in static_filters.iteritems(): ids = set(item_filters.get(key, ())) if not ids: continue column_criteria = [] if '_for_tracks' in key: if None in ids: column_criteria.append(~column.any()) ids.discard(None) if ids: column_criteria.append(column.any(Track.id.in_(ids))) else: if None in ids: column_criteria.append(column.is_(None)) ids.discard(None) if ids: column_criteria.append(column.in_(ids)) criteria.append(db.or_(*column_criteria)) if 'state' in item_filters: states = [ AbstractState(int(state)) for state in item_filters['state'] ] criteria.append(Abstract.state.in_(states)) if extra_filters: if extra_filters.get('multiple_tracks'): submitted_for_count = (db.select( [db.func.count()]).as_scalar().where( Abstract.submitted_for_tracks.prop.primaryjoin)) criteria.append(submitted_for_count > 1) if extra_filters.get('comments'): criteria.append(Abstract.submission_comment != '') return query.filter(db.and_(*criteria))
def _mappers_configured(): # We create some column properties here since even with `declared_attr` # the code runs at import time, making it impossible/risky to import other # modules or reference the object itself in there. # The advantage of those column properties is that they behave like regular # (read-only) columns even though they are generated by subqueries. This # allows them to be loaded together with the rest of the data, avoiding # extra queries. To load them automatically you need to undefer them using # the `undefer` query option, e.g. `.options(undefer('chain_titles'))`. from fossir.modules.events import Event # Category.effective_protection_mode -- the effective protection mode # (public/protected) of the category, even if it's inheriting it from its # parent category cte = Category.get_protection_cte() query = select([cte.c.protection_mode ]).where(cte.c.id == Category.id).correlate_except(cte) Category.effective_protection_mode = column_property(query, deferred=True) # Category.effective_icon_data -- the effective icon data of the category, # either set on the category itself or inherited from it cte = Category.get_icon_data_cte() query = (select([ db.func.json_build_object('source_id', cte.c.source_id, 'metadata', cte.c.icon_metadata) ]).where(cte.c.id == Category.id).correlate_except(cte)) Category.effective_icon_data = column_property(query, deferred=True) # Category.event_count -- the number of events in the category itself, # excluding deleted events query = (select([db.func.count(Event.id) ]).where((Event.category_id == Category.id) & ~Event.is_deleted).correlate_except(Event)) Category.event_count = column_property(query, deferred=True) # Category.has_events -- whether the category itself contains any # events, excluding deleted events query = (exists([1]).where((Event.category_id == Category.id) & ~Event.is_deleted).correlate_except(Event)) Category.has_events = column_property(query, deferred=True) # Category.chain_titles -- a list of the titles in the parent chain, # starting with the root category down to the current category. cte = Category.get_tree_cte('title') query = select([cte.c.path ]).where(cte.c.id == Category.id).correlate_except(cte) Category.chain_titles = column_property(query, deferred=True) # Category.chain -- a list of the ids and titles in the parent # chain, starting with the root category down to the current # category. Each chain entry is a dict containing 'id' and `title`. cte = Category.get_tree_cte(lambda cat: db.func.json_build_object( 'id', cat.id, 'title', cat.title)) query = select([cte.c.path ]).where(cte.c.id == Category.id).correlate_except(cte) Category.chain = column_property(query, deferred=True) # Category.deep_events_count -- the number of events in the category # or any child category (excluding deleted events) cte = Category.get_tree_cte() crit = db.and_(cte.c.id == Event.category_id, cte.c.path.contains(array([Category.id])), ~cte.c.is_deleted, ~Event.is_deleted) query = select([db.func.count()]).where(crit).correlate_except(Event) Category.deep_events_count = column_property(query, deferred=True) # Category.deep_children_count -- the number of subcategories in the # category or any child category (excluding deleted ones) cte = Category.get_tree_cte() crit = db.and_(cte.c.path.contains(array([Category.id])), cte.c.id != Category.id, ~cte.c.is_deleted) query = select([db.func.count()]).where(crit).correlate_except(cte) Category.deep_children_count = column_property(query, deferred=True)
def get_non_inheriting_objects(root): """Get a set of child objects that do not inherit protection :param root: An event object (`Event`, `Session`, `Contribution` or `AttachmentFolder`) which may contain objects with a different protection. """ def _query_folders(obj, crit): return (db.m.AttachmentFolder.query.filter_by( event=obj.event, is_deleted=False).filter(crit).options(joinedload('attachments'))) def _process_attachments(folders): for folder in folders: if not folder.is_inheriting: yield folder for attachment in folder.attachments: if not attachment.is_inheriting: yield attachment if isinstance(root, db.m.Event): # For an event we check sessions, contributions and ALL attachments no matter where for sess in db.m.Session.query.with_parent(root).filter( ~db.m.Session.is_inheriting): yield _ProtectedObjectWrapper(sess) for contrib in db.m.Contribution.query.with_parent(root).filter( ~db.m.Contribution.is_inheriting): yield _ProtectedObjectWrapper(contrib) query = (root.all_attachment_folders.filter_by( is_deleted=False).options(joinedload('attachments'))) for obj in _process_attachments(query): yield _ProtectedObjectWrapper(obj) elif isinstance(root, db.m.Session): # For a session we check contributions and attachments within the session crit = db.or_( # attached to the session db.m.AttachmentFolder.object == root, # attached to a contribution in the session db.and_( db.m.AttachmentFolder.link_type == LinkType.contribution, db.m.AttachmentFolder.contribution.has( db.and_(db.m.Contribution.session == root, ~db.m.Contribution.is_deleted))), # attached to a subcontribution in a contribution in the session db.and_( db.m.AttachmentFolder.link_type == LinkType.subcontribution, db.m.AttachmentFolder.subcontribution.has( db.and_( ~db.m.SubContribution.is_deleted, db.m.SubContribution.contribution.has( db.and_(db.m.Contribution.session == root, ~db.m.Contribution.is_deleted)))))) for obj in _process_attachments(_query_folders(root, crit)): yield _ProtectedObjectWrapper(obj) for contrib in root.contributions: if not contrib.is_inheriting: yield _ProtectedObjectWrapper(contrib) elif isinstance(root, db.m.Contribution): # For a contribution we check attachments and subcontrib attachments crit = db.or_( # attached to the contribution db.m.AttachmentFolder.object == root, # attached to a subcontribution of the contribution db.and_( db.m.AttachmentFolder.link_type == LinkType.subcontribution, db.m.AttachmentFolder.subcontribution.has( db.and_(db.m.SubContribution.contribution == root, ~db.m.SubContribution.is_deleted)))) for obj in _process_attachments(_query_folders(root, crit)): yield _ProtectedObjectWrapper(obj) elif isinstance(root, db.m.AttachmentFolder): # For an attachment folder we only check attachments in there for attachment in root.attachments: if not attachment.is_inheriting: yield _ProtectedObjectWrapper(attachment) else: raise TypeError('Unexpected object of type {}: {}'.format( type(root).__name__, root))
def _query_sessions_for_user(event, user): return (Session.query.with_parent(event).filter( Session.acl_entries.any( db.and_(SessionPrincipal.has_management_role('coordinate'), SessionPrincipal.user == user))))
def _query_contributions_with_user_as_submitter(event, user): return (Contribution.query.with_parent(event) .filter(Contribution.acl_entries.any(db.and_(ContributionPrincipal.has_management_role('submit'), ContributionPrincipal.user == user))))