class DegreeProgressCategoryUnitRequirement(db.Model):
__tablename__ = 'degree_progress_category_unit_requirements'
category_id = db.Column(db.Integer, db.ForeignKey('degree_progress_categories.id'), primary_key=True)
unit_requirement_id = db.Column(db.Integer, db.ForeignKey('degree_progress_unit_requirements.id'), primary_key=True)
category = db.relationship('DegreeProgressCategory', back_populates='unit_requirements')
unit_requirement = db.relationship('DegreeProgressUnitRequirement', back_populates='categories')
def __init__(
self,
category_id,
unit_requirement_id,
):
self.category_id = category_id
self.unit_requirement_id = unit_requirement_id
@classmethod
def create(cls, category_id, unit_requirement_id):
db.session.add(cls(category_id=category_id, unit_requirement_id=unit_requirement_id))
std_commit()
@classmethod
def delete_mappings(cls, unit_requirement_id):
for mapping in cls.query.filter_by(unit_requirement_id=unit_requirement_id).all():
db.session.delete(mapping)
std_commit()
@classmethod
def find_by_category_id(cls, category_id):
return cls.query.filter_by(category_id=category_id).all()
class UniversityDeptMember(Base):
__tablename__ = 'university_dept_members'
university_dept_id = db.Column(db.Integer,
db.ForeignKey('university_depts.id'),
primary_key=True)
authorized_user_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
primary_key=True)
is_advisor = db.Column(db.Boolean, nullable=False)
is_director = db.Column(db.Boolean, nullable=False)
authorized_user = db.relationship('AuthorizedUser',
back_populates='department_memberships')
# Pre-load UniversityDept below to avoid 'failed to locate', as seen during routes.py init phase
university_dept = db.relationship(UniversityDept.__name__,
back_populates='authorized_users')
@classmethod
def create_membership(cls, university_dept, authorized_user, is_advisor,
is_director):
if not len(authorized_user.department_memberships):
mapping = cls(is_advisor=is_advisor, is_director=is_director)
mapping.authorized_user = authorized_user
mapping.university_dept = university_dept
authorized_user.department_memberships.append(mapping)
university_dept.authorized_users.append(mapping)
db.session.add(mapping)
std_commit()
class AlertView(db.Model):
__tablename__ = 'alert_views'
alert_id = db.Column(db.Integer,
db.ForeignKey('alerts.id'),
primary_key=True)
viewer_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
primary_key=True)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
dismissed_at = db.Column(db.DateTime)
viewer = db.relationship('AuthorizedUser', back_populates='alert_views')
alert = db.relationship('Alert', back_populates='views')
class AppointmentTopic(db.Model):
__tablename__ = 'appointment_topics'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
appointment_id = db.Column(db.Integer,
db.ForeignKey('appointments.id'),
nullable=False)
topic = db.Column(db.String(50), nullable=False)
deleted_at = db.Column(db.DateTime)
appointment = db.relationship('Appointment', back_populates='topics')
def __init__(self, appointment_id, topic):
self.appointment_id = appointment_id
self.topic = topic
@classmethod
def create(cls, appointment, topic):
return AppointmentTopic(
appointment_id=appointment.id,
topic=topic,
)
@classmethod
def find_by_appointment_id(cls, appointment_id):
return cls.query.filter(
and_(cls.appointment_id == appointment_id,
cls.deleted_at == None)).all() # noqa: E711
def to_api_json(self):
return self.topic
class NoteTopic(db.Model):
__tablename__ = 'note_topics'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
note_id = db.Column(db.Integer, db.ForeignKey('notes.id'), nullable=False)
topic = db.Column(db.String(50), nullable=False)
author_uid = db.Column(db.String(255),
db.ForeignKey('authorized_users.uid'),
nullable=False)
note = db.relationship('Note', back_populates='topics')
deleted_at = db.Column(db.DateTime)
def __init__(self, note_id, topic, author_uid):
self.note_id = note_id
self.topic = topic
self.author_uid = author_uid
@classmethod
def create_note_topic(cls, note, topic, author_uid):
return NoteTopic(
note_id=note.id,
topic=topic,
author_uid=author_uid,
)
@classmethod
def find_by_note_id(cls, note_id):
return cls.query.filter(
and_(cls.note_id == note_id,
cls.deleted_at == None)).all() # noqa: E711
def to_api_json(self):
return self.topic
class NoteTemplateTopic(db.Model):
__tablename__ = 'note_template_topics'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
note_template_id = db.Column(db.Integer, db.ForeignKey('note_templates.id'), nullable=False)
topic = db.Column(db.String(50), nullable=False)
note_template = db.relationship('NoteTemplate', back_populates='topics')
def __init__(self, note_template_id, topic):
self.note_template_id = note_template_id
self.topic = topic
@classmethod
def create(cls, note_template_id, topic):
return cls(note_template_id=note_template_id, topic=topic)
@classmethod
def find_by_note_template_id(cls, note_template_id):
return cls.query.filter(and_(cls.note_template_id == note_template_id)).all()
@classmethod
def delete(cls, topic_id):
topic = cls.query.filter_by(id=topic_id).first()
db.session.delete(topic)
std_commit()
def to_api_json(self):
return self.topic
class NoteTemplateAttachment(db.Model):
__tablename__ = 'note_template_attachments'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
note_template_id = db.Column(db.Integer,
db.ForeignKey('note_templates.id'),
nullable=False)
path_to_attachment = db.Column('path_to_attachment',
db.String(255),
nullable=False)
uploaded_by_uid = db.Column('uploaded_by_uid',
db.String(255),
nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
deleted_at = db.Column(db.DateTime)
note_template = db.relationship('NoteTemplate',
back_populates='attachments')
__table_args__ = (
db.UniqueConstraint(
'note_template_id',
'path_to_attachment',
# Constraint name length is limited to 63 bytes in Postgres so we abbreviate the prefix.
name='nta_note_template_id_path_to_attachment_unique_constraint',
), )
def __init__(self, note_template_id, path_to_attachment, uploaded_by_uid):
self.note_template_id = note_template_id
self.path_to_attachment = path_to_attachment
self.uploaded_by_uid = uploaded_by_uid
def get_user_filename(self):
return get_attachment_filename(self.id, self.path_to_attachment)
@classmethod
def find_by_id(cls, attachment_id):
return cls.query.filter(
and_(cls.id == attachment_id,
cls.deleted_at == None)).first() # noqa: E711
@classmethod
def get_attachments(cls, attachment_ids):
return cls.query.filter(
and_(cls.id.in_(attachment_ids),
cls.deleted_at == None)).all() # noqa: E711
@classmethod
def create(cls, note_template_id, name, byte_stream, uploaded_by):
return NoteTemplateAttachment(
note_template_id=note_template_id,
path_to_attachment=put_attachment_to_s3(name=name,
byte_stream=byte_stream),
uploaded_by_uid=uploaded_by,
)
def to_api_json(self):
return note_attachment_to_api_json(self)
class AuthorizedUser(Base, UserMixin):
__tablename__ = 'authorized_users'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
uid = db.Column(db.String(255), nullable=False, unique=True)
is_admin = db.Column(db.Boolean)
department_memberships = db.relationship(
'UniversityDeptMember',
back_populates='authorized_user',
lazy='joined',
)
cohort_filters = db.relationship(
'CohortFilter',
secondary=cohort_filter_owners,
back_populates='owners',
lazy='joined',
)
alert_views = db.relationship(
'AlertView',
back_populates='viewer',
lazy='joined',
)
def __init__(self, uid, is_admin=False):
self.uid = uid
self.is_admin = is_admin
def __repr__(self):
return f"""<AuthorizedUser {self.uid},
is_admin={self.is_admin},
updated={self.updated_at},
created={self.created_at}>
"""
def get_id(self):
"""Override UserMixin, since our DB conventionally reserves 'id' for generated keys."""
return self.uid
@classmethod
def find_by_uid(cls, uid):
"""Supports Flask-Login via user_loader in routes.py."""
user = AuthorizedUser.query.filter_by(uid=uid).first()
std_commit()
return user
class CuratedCohortStudent(db.Model):
__tablename__ = 'student_group_members'
curated_cohort_id = db.Column('student_group_id',
db.Integer,
db.ForeignKey('student_groups.id'),
primary_key=True)
sid = db.Column('sid', db.String(80), primary_key=True)
curated_cohort = db.relationship('CuratedCohort',
back_populates='students')
class DegreeProgressNote(Base):
__tablename__ = 'degree_progress_notes'
body = db.Column(db.Text, nullable=False)
template_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_templates.id'),
nullable=False,
primary_key=True)
updated_by = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
template = db.relationship('DegreeProgressTemplate', back_populates='note')
def __init__(self, body, template_id, updated_by):
self.body = body
self.template_id = template_id
self.updated_by = updated_by
def __repr__(self):
return f"""<DegreeProgressNote template_id={self.template_id},
body={self.body},
created_at={self.created_at},
updated_at={self.updated_at},
updated_by={self.updated_by}>"""
@classmethod
def upsert(
cls,
body,
template_id,
updated_by,
):
note = cls.query.filter_by(template_id=template_id).first()
if note:
note.body = body
note.updated_by = updated_by
else:
note = cls(
body=body,
template_id=template_id,
updated_by=updated_by,
)
db.session.add(note)
std_commit()
return note
def to_api_json(self):
return {
'body': self.body,
'createdAt': _isoformat(self.created_at),
'templateId': self.template_id,
'updatedAt': _isoformat(self.updated_at),
'updatedBy': self.updated_by,
}
class UniversityDept(Base):
__tablename__ = 'university_depts'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
dept_code = db.Column(db.String(80), nullable=False)
dept_name = db.Column(db.String(255), nullable=False)
authorized_users = db.relationship(
'UniversityDeptMember',
back_populates='university_dept',
)
automate_memberships = db.Column(db.Boolean, nullable=False, default=False)
__table_args__ = (db.UniqueConstraint(
'dept_code',
'dept_name',
name='university_depts_code_unique_constraint'), )
def __init__(self, dept_code, dept_name, automate_memberships):
self.dept_code = dept_code
self.dept_name = dept_name
self.automate_memberships = automate_memberships
@classmethod
def find_by_dept_code(cls, dept_code):
return cls.query.filter_by(dept_code=dept_code).first()
@classmethod
def create(cls, dept_code, dept_name, automate_memberships):
dept = cls(dept_code=dept_code,
dept_name=dept_name,
automate_memberships=automate_memberships)
db.session.add(dept)
std_commit()
return dept
def delete_all_members(self):
sql = """
DELETE FROM university_dept_members WHERE university_dept_id = :id;
UPDATE authorized_users SET deleted_at = now()
WHERE is_admin IS FALSE
AND deleted_at IS NULL
AND id NOT IN (SELECT authorized_user_id FROM university_dept_members);"""
db.session.execute(text(sql), {'id': self.id})
std_commit()
def memberships_from_loch(self):
program_affiliations = BERKELEY_DEPT_CODE_TO_PROGRAM_AFFILIATIONS.get(
self.dept_code)
if not program_affiliations:
return []
return data_loch.get_advisor_uids_for_affiliations(
program_affiliations.get('program'),
program_affiliations.get('affiliations'),
)
class NoteAttachment(db.Model):
__tablename__ = 'note_attachments'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
note_id = db.Column(db.Integer, db.ForeignKey('notes.id'), nullable=False)
path_to_attachment = db.Column('path_to_attachment',
db.String(255),
nullable=False)
uploaded_by_uid = db.Column('uploaded_by_uid',
db.String(255),
nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
deleted_at = db.Column(db.DateTime)
note = db.relationship('Note', back_populates='attachments')
def __init__(self, note_id, path_to_attachment, uploaded_by_uid):
self.note_id = note_id
self.path_to_attachment = path_to_attachment
self.uploaded_by_uid = uploaded_by_uid
@classmethod
def create(cls, note_id, name, byte_stream, uploaded_by):
return NoteAttachment(
note_id=note_id,
path_to_attachment=put_attachment_to_s3(name=name,
byte_stream=byte_stream),
uploaded_by_uid=uploaded_by,
)
@classmethod
def create_using_template_attachment(cls, note_id, template_attachment,
uploaded_by):
return NoteAttachment(
note_id=note_id,
path_to_attachment=template_attachment.path_to_attachment,
uploaded_by_uid=uploaded_by,
)
@classmethod
def find_by_id(cls, attachment_id):
return cls.query.filter(
and_(cls.id == attachment_id,
cls.deleted_at == None)).first() # noqa: E711
def get_user_filename(self):
return get_attachment_filename(self.id, self.path_to_attachment)
def to_api_json(self):
return note_attachment_to_api_json(self)
class AuthorizedUser(Base, UserMixin):
__tablename__ = 'authorized_users'
id = db.Column(db.Integer, nullable=False, primary_key=True)
uid = db.Column(db.String(255), nullable=False, unique=True)
is_advisor = db.Column(db.Boolean)
is_admin = db.Column(db.Boolean)
is_director = db.Column(db.Boolean)
cohort_filters = db.relationship('CohortFilter',
secondary=cohort_filter_owners,
back_populates='owners')
def __init__(self,
uid,
is_advisor=True,
is_admin=False,
is_director=False):
self.uid = uid
self.is_advisor = is_advisor
self.is_admin = is_admin
self.is_director = is_director
def __repr__(self):
return '<AuthorizedUser {}, is_advisor={}, is_admin={}, is_director={}, updated={}, created={}>'.format(
self.uid,
self.is_advisor,
self.is_admin,
self.is_director,
self.updated_at,
self.created_at,
)
def get_id(self):
"""Override UserMixin, since our DB conventionally reserves 'id' for generated keys."""
return self.uid
@classmethod
def find_by_uid(cls, uid):
return AuthorizedUser.query.filter_by(uid=uid).first()
class NoteTemplate(Base):
__tablename__ = 'note_templates'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
body = db.Column(db.Text, nullable=False)
creator_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
deleted_at = db.Column(db.DateTime, nullable=True)
is_private = db.Column(db.Boolean, nullable=False, default=False)
subject = db.Column(db.String(255), nullable=False)
title = db.Column(db.String(255), nullable=False)
topics = db.relationship(
'NoteTemplateTopic',
primaryjoin='and_(NoteTemplate.id==NoteTemplateTopic.note_template_id)',
back_populates='note_template',
lazy=True,
)
attachments = db.relationship(
'NoteTemplateAttachment',
primaryjoin=
'and_(NoteTemplate.id==NoteTemplateAttachment.note_template_id, NoteTemplateAttachment.deleted_at==None)',
back_populates='note_template',
lazy=True,
)
__table_args__ = (db.UniqueConstraint(
'creator_id',
'title',
'deleted_at',
name='student_groups_owner_id_name_unique_constraint',
), )
def __init__(self, body, creator_id, subject, title, is_private=False):
self.body = body
self.creator_id = creator_id
self.is_private = is_private
self.subject = subject
self.title = title
@classmethod
def create(cls,
creator_id,
subject,
title,
attachments=(),
body='',
is_private=False,
topics=()):
creator = AuthorizedUser.find_by_id(creator_id)
if creator:
note_template = cls(body=body,
creator_id=creator_id,
is_private=is_private,
subject=subject,
title=title)
for topic in topics:
note_template.topics.append(
NoteTemplateTopic.create(
note_template.id,
titleize(vacuum_whitespace(topic))), )
for byte_stream_bundle in attachments:
note_template.attachments.append(
NoteTemplateAttachment.create(
note_template_id=note_template.id,
name=byte_stream_bundle['name'],
byte_stream=byte_stream_bundle['byte_stream'],
uploaded_by=creator.uid,
), )
db.session.add(note_template)
std_commit()
return note_template
@classmethod
def find_by_id(cls, note_template_id):
return cls.query.filter(
and_(cls.id == note_template_id,
cls.deleted_at == None)).first() # noqa: E711
@classmethod
def get_templates_created_by(cls, creator_id):
return cls.query.filter_by(creator_id=creator_id,
deleted_at=None).order_by(cls.title).all()
@classmethod
def rename(cls, note_template_id, title):
note_template = cls.find_by_id(note_template_id)
if note_template:
note_template.title = title
std_commit()
return note_template
else:
return None
@classmethod
def update(
cls,
body,
note_template_id,
subject,
attachments=(),
delete_attachment_ids=(),
is_private=False,
topics=(),
):
note_template = cls.find_by_id(note_template_id)
if note_template:
creator = AuthorizedUser.find_by_id(note_template.creator_id)
note_template.body = body
note_template.is_private = is_private
note_template.subject = subject
cls._update_note_template_topics(note_template, topics)
if delete_attachment_ids:
cls._delete_attachments(note_template, delete_attachment_ids)
for byte_stream_bundle in attachments:
cls._add_attachment(note_template, byte_stream_bundle,
creator.uid)
std_commit()
db.session.refresh(note_template)
return note_template
else:
return None
@classmethod
def delete(cls, note_template_id):
note_template = cls.find_by_id(note_template_id)
if note_template:
now = utc_now()
note_template.deleted_at = now
for attachment in note_template.attachments:
attachment.deleted_at = now
for topic in note_template.topics:
db.session.delete(topic)
std_commit()
def to_api_json(self):
attachments = [
a.to_api_json() for a in self.attachments if not a.deleted_at
]
topics = [t.to_api_json() for t in self.topics]
return {
'id': self.id,
'attachments': attachments,
'body': self.body,
'isPrivate': self.is_private,
'subject': self.subject,
'title': self.title,
'topics': topics,
'createdAt': self.created_at.astimezone(tzutc()).isoformat(),
'updatedAt': self.updated_at.astimezone(tzutc()).isoformat(),
}
@classmethod
def _update_note_template_topics(cls, note_template, topics):
modified = False
now = utc_now()
topics = set([titleize(vacuum_whitespace(topic)) for topic in topics])
existing_topics = set(
note_topic.topic
for note_topic in NoteTemplateTopic.find_by_note_template_id(
note_template.id))
topics_to_delete = existing_topics - topics
topics_to_add = topics - existing_topics
for topic in topics_to_delete:
topic_to_delete = next(
(t for t in note_template.topics if t.topic == topic), None)
if topic_to_delete:
NoteTemplateTopic.delete(topic_to_delete.id)
modified = True
for topic in topics_to_add:
note_template.topics.append(
NoteTemplateTopic.create(note_template, topic), )
modified = True
if modified:
note_template.updated_at = now
@classmethod
def _add_attachment(cls, note_template, attachment, uploaded_by_uid):
note_template.attachments.append(
NoteTemplateAttachment.create(
note_template_id=note_template.id,
name=attachment['name'],
byte_stream=attachment['byte_stream'],
uploaded_by=uploaded_by_uid,
), )
note_template.updated_at = utc_now()
@classmethod
def _delete_attachments(cls, note_template, delete_attachment_ids):
modified = False
now = utc_now()
for attachment in note_template.attachments:
if attachment.id in delete_attachment_ids:
attachment.deleted_at = now
modified = True
if modified:
note_template.updated_at = now
class Alert(Base):
__tablename__ = 'alerts'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
sid = db.Column(db.String(80), nullable=False)
alert_type = db.Column(db.String(80), nullable=False)
key = db.Column(db.String(255), nullable=False)
message = db.Column(db.Text, nullable=False)
deleted_at = db.Column(db.DateTime)
views = db.relationship(
'AlertView',
back_populates='alert',
lazy=True,
)
__table_args__ = (db.UniqueConstraint(
'sid',
'alert_type',
'key',
'created_at',
name='alerts_sid_alert_type_key_created_at_unique_constraint',
), )
@classmethod
def create(cls,
sid,
alert_type,
key=None,
message=None,
deleted_at=None,
created_at=None):
# Alerts must contain a key, unique per SID and alert type, which will allow them to be located
# and modified on updates to the data that originally generated the alert. The key defaults
# to a string representation of today's date, but will more often (depending on the alert type)
# contain a reference to a related resource, such as a course or assignment id.
if key is None:
key = datetime.now().strftime('%Y-%m-%d')
else:
# If we get a blank string as key, deliver a stern warning to the code that submitted it.
key = key.strip()
if not key:
raise ValueError('Blank string submitted for alert key')
alert = cls(sid, alert_type, key, message, deleted_at)
if created_at:
alert.created_at = created_at
alert.updated_at = created_at
db.session.add(alert)
std_commit()
def __init__(self, sid, alert_type, key, message=None, deleted_at=None):
self.sid = sid
self.alert_type = alert_type
self.key = key
self.message = message
self.deleted_at = deleted_at
def __repr__(self):
return f"""<Alert {self.id},
sid={self.sid},
alert_type={self.alert_type},
key={self.key},
message={self.message},
deleted_at={self.deleted_at},
updated={self.updated_at},
created={self.created_at}>
"""
@classmethod
def dismiss(cls, alert_id, viewer_id):
alert = cls.query.filter_by(id=alert_id).first()
if alert:
alert_view = AlertView.query.filter_by(viewer_id=viewer_id,
alert_id=alert_id).first()
if alert_view:
alert_view.dismissed_at = datetime.now()
else:
db.session.add(
AlertView(viewer_id=viewer_id,
alert_id=alert_id,
dismissed_at=datetime.now()))
std_commit()
else:
raise BadRequestError(f'No alert found for id {alert_id}')
@classmethod
def current_alert_counts_for_viewer(cls, viewer_id):
query = """
SELECT alerts.sid, count(*) as alert_count
FROM alerts LEFT JOIN alert_views
ON alert_views.alert_id = alerts.id
AND alert_views.viewer_id = :viewer_id
WHERE alerts.deleted_at IS NULL
AND alerts.key LIKE :key
AND alert_views.dismissed_at IS NULL
GROUP BY alerts.sid
"""
params = {'viewer_id': viewer_id, 'key': current_term_id() + '_%'}
return cls.alert_counts_by_query(query, params)
@classmethod
def current_alert_counts_for_sids(cls,
viewer_id,
sids,
count_only=False,
offset=None,
limit=None):
query = """
SELECT alerts.sid, count(*) as alert_count
FROM alerts LEFT JOIN alert_views
ON alert_views.alert_id = alerts.id
AND alert_views.viewer_id = :viewer_id
WHERE alerts.deleted_at IS NULL
AND alerts.key LIKE :key
AND alerts.sid = ANY(:sids)
AND alert_views.dismissed_at IS NULL
GROUP BY alerts.sid
ORDER BY alert_count DESC, alerts.sid
"""
if offset:
query += ' OFFSET :offset'
if limit:
query += ' LIMIT :limit'
params = {
'viewer_id': viewer_id,
'key': current_term_id() + '_%',
'sids': sids,
'offset': offset,
'limit': limit,
}
return cls.alert_counts_by_query(query, params, count_only=count_only)
@classmethod
def alert_counts_by_query(cls, query, params, count_only=False):
results = db.session.execute(text(query), params)
# If we're only interested in the alert count, skip the student data fetch below.
if count_only:
return [{
'sid': row['sid'],
'alertCount': row['alert_count']
} for row in results]
alert_counts_by_sid = {
row['sid']: row['alert_count']
for row in results
}
sids = list(alert_counts_by_sid.keys())
def result_to_dict(result):
result_dict = {
'sid': result.get('sid'),
'uid': result.get('uid'),
'firstName': result.get('first_name'),
'lastName': result.get('last_name'),
'alertCount': alert_counts_by_sid.get(result.get('sid')),
}
return result_dict
return [
result_to_dict(result)
for result in data_loch.get_basic_student_data(sids)
]
@classmethod
def current_alerts_for_sid(cls, viewer_id, sid):
query = text("""
SELECT alerts.*, alert_views.dismissed_at
FROM alerts LEFT JOIN alert_views
ON alert_views.alert_id = alerts.id
AND alert_views.viewer_id = :viewer_id
WHERE alerts.deleted_at IS NULL
AND alerts.key LIKE :key
AND alerts.sid = :sid
ORDER BY alerts.created_at
""")
results = db.session.execute(query, {
'viewer_id': viewer_id,
'key': current_term_id() + '_%',
'sid': sid
})
feed = []
def result_to_dict(result):
return {
camelize(key): result[key]
for key in ['id', 'alert_type', 'key', 'message']
}
for result in results:
dismissed_at = result['dismissed_at']
alert = {
**result_to_dict(result),
**{
'dismissed':
dismissed_at and dismissed_at.strftime('%Y-%m-%d %H:%M:%S'),
'createdAt':
result['created_at'].strftime('%Y-%m-%d %H:%M:%S'),
'updatedAt':
result['updated_at'].strftime('%Y-%m-%d %H:%M:%S'),
},
}
feed.append(alert)
return feed
def activate(self, preserve_creation_date=False):
self.deleted_at = None
# Some alert types, such as withdrawals and midpoint deficient grades, don't include a time-shifted message
# and shouldn't be treated as updated after creation.
if preserve_creation_date:
self.updated_at = self.created_at
std_commit()
def deactivate(self):
self.deleted_at = datetime.now()
std_commit()
@classmethod
def create_or_activate(
cls,
alert_type,
key,
message,
sid,
created_at=None,
force_use_existing=False,
preserve_creation_date=False,
):
# If any previous alerts exist with the same type, key and sid, grab the most recently updated one.
existing_alert = cls.query.filter_by(sid=sid,
alert_type=alert_type,
key=key).order_by(
desc(cls.updated_at)).first()
# If the existing alert was only just deactivated in the last two hours, assume that the deactivation was part of the
# current refresh cycle, and go ahead and reactivate it. But if the alert was deactivated farther back in the past,
# assume that it represents a previous state of affairs, and create a new alert for current conditions.
if existing_alert and (force_use_existing or
(datetime.now(timezone.utc) -
existing_alert.updated_at).total_seconds() <
(2 * 3600)):
existing_alert.message = message
existing_alert.activate(
preserve_creation_date=preserve_creation_date)
else:
cls.create(
alert_type=alert_type,
created_at=created_at,
key=key,
message=message,
sid=sid,
)
@classmethod
def deactivate_all(cls, sid, term_id, alert_types):
query = (
cls.query.filter(cls.sid == sid).filter(
cls.alert_type.in_(alert_types)).filter(
cls.key.startswith(f'{term_id}_%')).filter(
cls.deleted_at == None) # noqa: E711
)
results = query.update({cls.deleted_at: datetime.now()},
synchronize_session='fetch')
std_commit()
return results
@classmethod
def get_alerts_per_date_range(cls, from_date_utc, to_date_utc):
criterion = and_(
cls.created_at >= from_date_utc,
cls.created_at <= to_date_utc,
)
return cls.query.filter(criterion).order_by(cls.created_at).all()
@classmethod
def infrequent_activity_alerts_enabled(cls):
if not app.config['ALERT_INFREQUENT_ACTIVITY_ENABLED']:
return False
if current_term_name().startswith('Summer'):
return False
days_into_session = (datetime.date(datetime.today()) -
_get_current_term_start()).days
return days_into_session >= app.config['ALERT_INFREQUENT_ACTIVITY_DAYS']
@classmethod
def no_activity_alerts_enabled(cls):
if not app.config['ALERT_NO_ACTIVITY_ENABLED']:
return False
if current_term_name().startswith('Summer'):
return False
days_into_session = (datetime.date(datetime.today()) -
_get_current_term_start()).days
return days_into_session >= app.config[
'ALERT_NO_ACTIVITY_DAYS_INTO_SESSION']
@classmethod
def deactivate_all_for_term(cls, term_id):
query = (
cls.query.filter(cls.key.startswith(f'{term_id}_%')).filter(
cls.deleted_at == None) # noqa: E711
)
results = query.update({cls.deleted_at: datetime.now()},
synchronize_session='fetch')
std_commit()
return results
@classmethod
def update_all_for_term(cls, term_id):
app.logger.info('Starting alert update')
enrollments_for_term = data_loch.get_enrollments_for_term(str(term_id))
no_activity_alerts_enabled = cls.no_activity_alerts_enabled()
infrequent_activity_alerts_enabled = cls.infrequent_activity_alerts_enabled(
)
for row in enrollments_for_term:
enrollments = json.loads(row['enrollment_term']).get(
'enrollments', [])
for enrollment in enrollments:
cls.update_alerts_for_enrollment(
sid=row['sid'],
term_id=term_id,
enrollment=enrollment,
no_activity_alerts_enabled=no_activity_alerts_enabled,
infrequent_activity_alerts_enabled=
infrequent_activity_alerts_enabled,
)
profiles = data_loch.get_student_profiles()
if app.config['ALERT_WITHDRAWAL_ENABLED'] and str(
term_id) == current_term_id():
for row in profiles:
profile_feed = json.loads(row['profile'])
if 'withdrawalCancel' in (profile_feed.get('sisProfile')
or {}):
cls.update_withdrawal_cancel_alerts(row['sid'], term_id)
sids = [p['sid'] for p in profiles]
for sid, academic_standing_list in get_academic_standing_by_sid(
sids).items():
standing = next((s for s in academic_standing_list
if s['termId'] == str(term_id)), None)
if standing and standing['status'] in ('DIS', 'PRO', 'SUB'):
cls.update_academic_standing_alerts(
action_date=standing['actionDate'],
sid=standing['sid'],
status=standing['status'],
term_id=term_id,
)
app.logger.info('Alert update complete')
@classmethod
def update_academic_standing_alerts(cls, action_date, sid, status,
term_id):
key = f'{term_id}_{action_date}_academic_standing_{status}'
status_description = ACADEMIC_STANDING_DESCRIPTIONS.get(status, status)
message = f"Student's academic standing is '{status_description}'."
datetime.strptime(action_date, '%Y-%m-%d')
cls.create_or_activate(
alert_type='academic_standing',
created_at=action_date,
force_use_existing=True,
key=key,
message=message,
preserve_creation_date=True,
sid=sid,
)
@classmethod
def update_alerts_for_enrollment(cls, sid, term_id, enrollment,
no_activity_alerts_enabled,
infrequent_activity_alerts_enabled):
for section in enrollment['sections']:
if section_is_eligible_for_alerts(enrollment=enrollment,
section=section):
# If the grade is in, what's done is done.
if section.get('grade'):
continue
if section.get('midtermGrade'):
cls.update_midterm_grade_alerts(sid, term_id,
section['ccn'],
enrollment['displayName'],
section['midtermGrade'])
last_activity = None
activity_percentile = None
for canvas_site in enrollment.get('canvasSites', []):
student_activity = canvas_site.get('analytics', {}).get(
'lastActivity', {}).get('student')
if not student_activity or student_activity.get(
'roundedUpPercentile') is None:
continue
raw_epoch = student_activity.get('raw')
if last_activity is None or raw_epoch > last_activity:
last_activity = raw_epoch
activity_percentile = student_activity.get(
'roundedUpPercentile')
if last_activity is None:
continue
if (no_activity_alerts_enabled and last_activity == 0
and activity_percentile <=
app.config['ALERT_NO_ACTIVITY_PERCENTILE_CUTOFF']):
cls.update_no_activity_alerts(sid, term_id,
enrollment['displayName'])
elif (infrequent_activity_alerts_enabled
and last_activity > 0):
localized_last_activity = unix_timestamp_to_localtime(
last_activity).date()
localized_today = unix_timestamp_to_localtime(
time.time()).date()
days_since = (localized_today -
localized_last_activity).days
if (days_since >=
app.config['ALERT_INFREQUENT_ACTIVITY_DAYS']
and activity_percentile <= app.config[
'ALERT_INFREQUENT_ACTIVITY_PERCENTILE_CUTOFF']
):
cls.update_infrequent_activity_alerts(
sid,
term_id,
enrollment['displayName'],
days_since,
)
@classmethod
def update_assignment_alerts(cls, sid, term_id, assignment_id, due_at,
status, course_site_name):
alert_type = status + '_assignment'
key = f'{term_id}_{assignment_id}'
due_at_date = utc_timestamp_to_localtime(due_at).strftime('%b %-d, %Y')
message = f'{course_site_name} assignment due on {due_at_date}.'
cls.create_or_activate(sid=sid,
alert_type=alert_type,
key=key,
message=message)
@classmethod
def update_midterm_grade_alerts(cls, sid, term_id, section_id, class_name,
grade):
key = f'{term_id}_{section_id}'
message = f'{class_name} midpoint deficient grade of {grade}.'
cls.create_or_activate(sid=sid,
alert_type='midterm',
key=key,
message=message,
preserve_creation_date=True)
@classmethod
def update_no_activity_alerts(cls, sid, term_id, class_name):
key = f'{term_id}_{class_name}'
message = f'No activity! Student has never visited the {class_name} bCourses site for {term_name_for_sis_id(term_id)}.'
cls.create_or_activate(sid=sid,
alert_type='no_activity',
key=key,
message=message)
@classmethod
def update_infrequent_activity_alerts(cls, sid, term_id, class_name,
days_since):
key = f'{term_id}_{class_name}'
message = f'Infrequent activity! Last {class_name} bCourses activity was {days_since} days ago.'
# If an active infrequent activity alert already exists and is more recent, skip the update.
existing_alert = cls.query.filter_by(sid=sid,
alert_type='infrequent_activity',
key=key,
deleted_at=None).first()
if existing_alert:
match = re.search('(\d+) days ago.$', message)
if match and match[1] and int(match[1]) < days_since:
return
cls.create_or_activate(sid=sid,
alert_type='infrequent_activity',
key=key,
message=message)
@classmethod
def update_withdrawal_cancel_alerts(cls, sid, term_id):
key = f'{term_id}_withdrawal'
message = f'Student is no longer enrolled in the {term_name_for_sis_id(term_id)} term.'
cls.create_or_activate(sid=sid,
alert_type='withdrawal',
key=key,
message=message,
preserve_creation_date=True)
@classmethod
def include_alert_counts_for_students(cls,
viewer_user_id,
group,
count_only=False,
offset=None,
limit=None):
sids = group.get('sids') if 'sids' in group else [
s['sid'] for s in group.get('students', [])
]
alert_counts = cls.current_alert_counts_for_sids(viewer_user_id,
sids,
count_only=count_only,
offset=offset,
limit=limit)
if 'students' in group:
counts_per_sid = {
s.get('sid'): s.get('alertCount')
for s in alert_counts
}
for student in group.get('students'):
sid = student['sid']
student['alertCount'] = counts_per_sid.get(
sid) if sid in counts_per_sid else 0
return alert_counts
class Appointment(Base):
__tablename__ = 'appointments'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
advisor_dept_codes = db.Column(ARRAY(db.String), nullable=True)
advisor_name = db.Column(db.String(255), nullable=True)
advisor_role = db.Column(db.String(255), nullable=True)
advisor_uid = db.Column(db.String(255), nullable=True)
appointment_type = db.Column(appointment_type_enum, nullable=False)
created_by = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False)
deleted_at = db.Column(db.DateTime, nullable=True)
deleted_by = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=True)
dept_code = db.Column(db.String(80), nullable=False)
details = db.Column(db.Text, nullable=True)
scheduled_time = db.Column(db.DateTime, nullable=True)
status = db.Column(appointment_event_type, nullable=False)
student_contact_info = db.Column(db.String(255), nullable=True)
student_contact_type = db.Column(appointment_student_contact_type_enum, nullable=True)
student_sid = db.Column(db.String(80), nullable=False)
updated_by = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=True)
topics = db.relationship(
'AppointmentTopic',
primaryjoin='and_(Appointment.id==AppointmentTopic.appointment_id, AppointmentTopic.deleted_at==None)',
back_populates='appointment',
order_by='AppointmentTopic.topic',
lazy=True,
)
def __init__(
self,
appointment_type,
created_by,
dept_code,
details,
status,
student_sid,
updated_by,
advisor_dept_codes=None,
advisor_name=None,
advisor_role=None,
advisor_uid=None,
scheduled_time=None,
student_contact_info=None,
student_contact_type=None,
):
self.advisor_dept_codes = advisor_dept_codes
self.advisor_name = advisor_name
self.advisor_role = advisor_role
self.advisor_uid = advisor_uid
self.appointment_type = appointment_type
self.created_by = created_by
self.dept_code = dept_code
self.details = details
self.scheduled_time = scheduled_time
self.status = status
self.student_contact_info = student_contact_info
self.student_contact_type = student_contact_type
self.student_sid = student_sid
self.updated_by = updated_by
@classmethod
def find_by_id(cls, appointment_id):
return cls.query.filter(and_(cls.id == appointment_id, cls.deleted_at == None)).first() # noqa: E711
@classmethod
def get_appointments_per_sid(cls, sid):
return cls.query.filter(and_(cls.student_sid == sid, cls.deleted_at == None)).order_by(cls.updated_at, cls.id).all() # noqa: E711
@classmethod
def get_drop_in_waitlist(cls, dept_code, statuses=()):
local_today = localize_datetime(datetime.now()).strftime('%Y-%m-%d')
start_of_today = localized_timestamp_to_utc(f'{local_today}T00:00:00')
criterion = and_(
cls.created_at >= start_of_today,
cls.appointment_type == 'Drop-in',
cls.status.in_(statuses),
cls.deleted_at == None, # noqa: E711
cls.dept_code == dept_code,
)
return cls.query.filter(criterion).order_by(cls.created_at).all()
@classmethod
def get_scheduled(cls, dept_code, local_date, advisor_uid=None):
date_str = local_date.strftime('%Y-%m-%d')
start_of_today = localized_timestamp_to_utc(f'{date_str}T00:00:00')
end_of_today = localized_timestamp_to_utc(f'{date_str}T23:59:59')
query = cls.query.filter(
and_(
cls.scheduled_time >= start_of_today,
cls.scheduled_time <= end_of_today,
cls.appointment_type == 'Scheduled',
cls.deleted_at == None, # noqa: E711
cls.dept_code == dept_code,
),
)
if advisor_uid:
query = query.filter(cls.advisor_uid == advisor_uid)
return query.order_by(cls.scheduled_time).all()
@classmethod
def create(
cls,
created_by,
dept_code,
details,
appointment_type,
student_sid,
advisor_attrs=None,
topics=(),
scheduled_time=None,
student_contact_info=None,
student_contact_type=None,
):
# If this appointment comes in already assigned to the intake desk, we treat it as resolved.
if advisor_attrs and advisor_attrs['role'] == 'Intake Desk':
status = 'checked_in'
elif advisor_attrs:
status = 'reserved'
else:
status = 'waiting'
appointment = cls(
advisor_uid=advisor_attrs and advisor_attrs['uid'],
advisor_name=advisor_attrs and advisor_attrs['name'],
advisor_role=advisor_attrs and advisor_attrs['role'],
advisor_dept_codes=advisor_attrs and advisor_attrs['deptCodes'],
appointment_type=appointment_type,
created_by=created_by,
dept_code=dept_code,
details=details,
scheduled_time=scheduled_time,
status=status,
student_contact_info=student_contact_info,
student_contact_type=student_contact_type,
student_sid=student_sid,
updated_by=created_by,
)
for topic in topics:
appointment.topics.append(
AppointmentTopic.create(appointment, topic),
)
db.session.add(appointment)
std_commit()
AppointmentEvent.create(
appointment_id=appointment.id,
advisor_id=advisor_attrs and advisor_attrs['id'],
user_id=created_by,
event_type=status,
)
cls.refresh_search_index()
return appointment
@classmethod
def check_in(cls, appointment_id, checked_in_by, advisor_attrs):
appointment = cls.find_by_id(appointment_id=appointment_id)
if appointment:
appointment.status = 'checked_in'
appointment.advisor_uid = advisor_attrs['uid']
appointment.advisor_name = advisor_attrs['name']
appointment.advisor_role = advisor_attrs['role']
appointment.advisor_dept_codes = advisor_attrs['deptCodes']
appointment.updated_by = checked_in_by
std_commit()
db.session.refresh(appointment)
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=checked_in_by,
advisor_id=advisor_attrs['id'],
event_type='checked_in',
)
return appointment
else:
return None
@classmethod
def cancel(cls, appointment_id, cancelled_by, cancel_reason, cancel_reason_explained):
appointment = cls.find_by_id(appointment_id=appointment_id)
if appointment:
event_type = 'cancelled'
appointment.status = event_type
appointment.updated_by = cancelled_by
appointment.advisor_uid = None
appointment.advisor_name = None
appointment.advisor_role = None
appointment.advisor_dept_codes = None
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=cancelled_by,
event_type=event_type,
cancel_reason=cancel_reason,
cancel_reason_explained=cancel_reason_explained,
)
std_commit()
db.session.refresh(appointment)
cls.refresh_search_index()
return appointment
else:
return None
@classmethod
def reserve(cls, appointment_id, reserved_by, advisor_attrs):
appointment = cls.find_by_id(appointment_id=appointment_id)
if appointment:
event_type = 'reserved'
appointment.status = event_type
appointment.updated_by = reserved_by
appointment.advisor_uid = advisor_attrs['uid']
appointment.advisor_name = advisor_attrs['name']
appointment.advisor_role = advisor_attrs['role']
appointment.advisor_dept_codes = advisor_attrs['deptCodes']
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=reserved_by,
advisor_id=advisor_attrs['id'],
event_type=event_type,
)
std_commit()
db.session.refresh(appointment)
return appointment
else:
return None
def set_to_waiting(self, updated_by):
event_type = 'waiting'
self.status = event_type
self.updated_by = updated_by
self.advisor_uid = None
self.advisor_name = None
self.advisor_role = None
self.advisor_dept_codes = None
AppointmentEvent.create(
appointment_id=self.id,
user_id=updated_by,
event_type=event_type,
)
std_commit()
db.session.refresh(self)
@classmethod
def unreserve_all_for_advisor(cls, advisor_uid, updated_by):
appointments = cls.query.filter(and_(cls.status == 'reserved', cls.advisor_uid == advisor_uid, cls.deleted_at == None)).all() # noqa: E711
event_type = 'waiting'
for appointment in appointments:
appointment.status = event_type
appointment.advisor_uid = None
appointment.advisor_name = None
appointment.advisor_role = None
appointment.advisor_dept_codes = None
appointment.updated_by = updated_by
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=updated_by,
event_type=event_type,
)
std_commit()
@classmethod
def search(
cls,
search_phrase,
advisor_uid=None,
student_csid=None,
topic=None,
datetime_from=None,
datetime_to=None,
limit=20,
offset=0,
):
if search_phrase:
search_terms = [t.group(0) for t in list(re.finditer(TEXT_SEARCH_PATTERN, search_phrase)) if t]
search_phrase = ' & '.join(search_terms)
fts_selector = """SELECT id, ts_rank(fts_index, plainto_tsquery('english', :search_phrase)) AS rank
FROM appointments_fts_index
WHERE fts_index @@ plainto_tsquery('english', :search_phrase)"""
params = {
'search_phrase': search_phrase,
}
else:
search_terms = []
fts_selector = 'SELECT id, 0 AS rank FROM appointments WHERE deleted_at IS NULL'
params = {}
if advisor_uid:
advisor_filter = 'AND appointments.advisor_uid = :advisor_uid'
params.update({'advisor_uid': advisor_uid})
else:
advisor_filter = ''
if student_csid:
student_filter = 'AND appointments.student_sid = :student_csid'
params.update({'student_csid': student_csid})
else:
student_filter = ''
date_filter = ''
if datetime_from:
date_filter += ' AND created_at >= :datetime_from'
params.update({'datetime_from': datetime_from})
if datetime_to:
date_filter += ' AND created_at < :datetime_to'
params.update({'datetime_to': datetime_to})
if topic:
topic_join = 'JOIN appointment_topics nt on nt.topic = :topic AND nt.appointment_id = appointments.id'
params.update({'topic': topic})
else:
topic_join = ''
query = text(f"""
SELECT appointments.* FROM ({fts_selector}) AS fts
JOIN appointments
ON fts.id = appointments.id
{advisor_filter}
{student_filter}
{date_filter}
{topic_join}
ORDER BY fts.rank DESC, appointments.id
LIMIT {limit} OFFSET {offset}
""").bindparams(**params)
result = db.session.execute(query)
keys = result.keys()
return [_to_json(search_terms, dict(zip(keys, row))) for row in result.fetchall()]
def update(
self,
updated_by,
details=None,
scheduled_time=None,
student_contact_info=None,
student_contact_type=None,
topics=(),
):
if details != self.details:
self.updated_at = utc_now()
self.updated_by = updated_by
self.details = details
self.scheduled_time = scheduled_time
self.student_contact_info = student_contact_info
self.student_contact_type = student_contact_type
_update_appointment_topics(self, topics, updated_by)
std_commit()
db.session.refresh(self)
self.refresh_search_index()
@classmethod
def refresh_search_index(cls):
def _refresh_search_index(db_session):
db_session.execute(text('REFRESH MATERIALIZED VIEW appointments_fts_index'))
db_session.execute(text('REFRESH MATERIALIZED VIEW advisor_author_index'))
std_commit(session=db_session)
bg_execute(_refresh_search_index)
@classmethod
def delete(cls, appointment_id):
appointment = cls.find_by_id(appointment_id)
if appointment:
now = utc_now()
appointment.deleted_at = now
for topic in appointment.topics:
topic.deleted_at = now
std_commit()
cls.refresh_search_index()
def status_change_available(self):
return self.status in ['reserved', 'waiting']
def to_api_json(self, current_user_id):
topics = [t.to_api_json() for t in self.topics if not t.deleted_at]
departments = None
if self.advisor_dept_codes:
departments = [{'code': c, 'name': BERKELEY_DEPT_CODE_TO_NAME.get(c, c)} for c in self.advisor_dept_codes]
api_json = {
'id': self.id,
'advisorId': AuthorizedUser.get_id_per_uid(self.advisor_uid),
'advisorName': self.advisor_name,
'advisorRole': self.advisor_role,
'advisorUid': self.advisor_uid,
'advisorDepartments': departments,
'appointmentType': self.appointment_type,
'createdAt': _isoformat(self.created_at),
'createdBy': self.created_by,
'deptCode': self.dept_code,
'details': self.details,
'read': AppointmentRead.was_read_by(current_user_id, self.id),
'student': {
'sid': self.student_sid,
},
'topics': topics,
'updatedAt': _isoformat(self.updated_at),
'updatedBy': self.updated_by,
}
if self.appointment_type == 'Scheduled':
api_json.update({
'scheduledTime': _isoformat(self.scheduled_time),
'studentContactInfo': self.student_contact_info,
'studentContactType': self.student_contact_type,
})
return {
**api_json,
**appointment_event_to_json(self.id, self.status),
}
class UniversityDeptMember(Base):
__tablename__ = 'university_dept_members'
university_dept_id = db.Column(db.Integer,
db.ForeignKey('university_depts.id'),
primary_key=True)
authorized_user_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
primary_key=True)
role = db.Column(university_dept_member_role_type, nullable=True)
automate_membership = db.Column(db.Boolean, nullable=False)
authorized_user = db.relationship('AuthorizedUser',
back_populates='department_memberships')
# Pre-load UniversityDept below to avoid 'failed to locate', as seen during routes.py init phase
university_dept = db.relationship(UniversityDept.__name__,
back_populates='authorized_users')
def __init__(
self,
university_dept_id,
authorized_user_id,
role,
automate_membership=True,
):
self.university_dept_id = university_dept_id
self.authorized_user_id = authorized_user_id
self.role = role
self.automate_membership = automate_membership
@classmethod
def create_or_update_membership(
cls,
university_dept_id,
authorized_user_id,
role=None,
automate_membership=True,
):
existing_membership = cls.query.filter_by(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id,
).first()
if existing_membership:
membership = existing_membership
membership.role = role
membership.automate_membership = automate_membership
else:
membership = cls(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id,
role=role,
automate_membership=automate_membership,
)
db.session.add(membership)
std_commit()
return membership
@classmethod
def get_existing_memberships(cls, authorized_user_id):
return cls.query.filter_by(authorized_user_id=authorized_user_id).all()
@classmethod
def update_membership(
cls,
university_dept_id,
authorized_user_id,
role,
automate_membership,
):
membership = cls.query.filter_by(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id).first()
if membership:
membership.role = membership.role if role is None else role
membership.automate_membership = membership.automate_membership if automate_membership is None else automate_membership
std_commit()
return membership
return None
@classmethod
def get_distinct_departments(
cls,
authorized_user_id=None,
role=None,
):
sql = """
SELECT DISTINCT dept_code FROM university_depts d
JOIN university_dept_members m ON m.university_dept_id = d.id
WHERE TRUE
"""
if authorized_user_id:
sql += ' AND m.authorized_user_id = :authorized_user_id'
else:
sql += ' AND d.id IN (SELECT DISTINCT university_dept_id FROM university_depts)'
if role is not None:
sql += f" AND m.role = '{role}'"
return [
row['dept_code'] for row in db.session.execute(
sql, {'authorized_user_id': authorized_user_id})
]
@classmethod
def delete_membership(cls, university_dept_id, authorized_user_id):
membership = cls.query.filter_by(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id).first()
if not membership:
return False
db.session.delete(membership)
std_commit()
return True
def to_api_json(self):
return {
'universityDeptId': self.university_dept_id,
'authorizedUserId': self.authorized_user_id,
'role': self.role,
'automateMembership': self.automate_membership,
}
class AuthorizedUser(Base):
__tablename__ = 'authorized_users'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
uid = db.Column(db.String(255), nullable=False, unique=True)
is_admin = db.Column(db.Boolean)
in_demo_mode = db.Column(db.Boolean, nullable=False)
deleted_at = db.Column(db.DateTime, nullable=True)
department_memberships = db.relationship(
'UniversityDeptMember',
back_populates='authorized_user',
lazy='joined',
)
cohort_filters = db.relationship(
'CohortFilter',
secondary=cohort_filter_owners,
back_populates='owners',
lazy='joined',
)
alert_views = db.relationship(
'AlertView',
back_populates='viewer',
lazy='joined',
)
def __init__(self, uid, is_admin=False, in_demo_mode=False):
self.uid = uid
self.is_admin = is_admin
self.in_demo_mode = in_demo_mode
def __repr__(self):
return f"""<AuthorizedUser {self.uid},
is_admin={self.is_admin},
in_demo_mode={self.in_demo_mode},
updated={self.updated_at},
created={self.created_at},
deleted={self.deleted_at}>
"""
@classmethod
def create_or_restore(cls, uid, is_admin=False, in_demo_mode=False):
existing_user = cls.query.filter_by(uid=uid).first()
if existing_user:
existing_user.deleted_at = None
return existing_user
else:
return cls(uid=uid, is_admin=is_admin, in_demo_mode=in_demo_mode)
@classmethod
def get_id_per_uid(cls, uid):
query = text(
f'SELECT id FROM authorized_users WHERE uid = :uid AND deleted_at IS NULL'
)
result = db.session.execute(query, {'uid': uid}).first()
return result and result['id']
@classmethod
def find_by_id(cls, db_id):
return AuthorizedUser.query.filter_by(id=db_id,
deleted_at=None).first()
@classmethod
def find_by_uid(cls, uid):
return AuthorizedUser.query.filter_by(uid=uid, deleted_at=None).first()
@classmethod
def get_all_active_users(cls):
return cls.query.filter_by(deleted_at=None).all()
@classmethod
def get_all_uids_in_scope(cls, scope=()):
sql = 'SELECT uid FROM authorized_users u '
if not scope:
return None
elif 'ADMIN' in scope:
sql += 'WHERE u.deleted_at IS NULL'
else:
sql += """
JOIN university_dept_members m ON m.authorized_user_id = u.id
JOIN university_depts d ON d.id = m.university_dept_id
WHERE
d.dept_code = ANY(:scope)
AND u.deleted_at IS NULL
"""
results = db.session.execute(sql, {'scope': scope})
return [row['uid'] for row in results]
class Note(Base):
__tablename__ = 'notes'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
author_uid = db.Column(db.String(255), nullable=False)
author_name = db.Column(db.String(255), nullable=False)
author_role = db.Column(db.String(255), nullable=False)
author_dept_codes = db.Column(ARRAY(db.String), nullable=False)
sid = db.Column(db.String(80), nullable=False)
subject = db.Column(db.String(255), nullable=False)
body = db.Column(db.Text, nullable=False)
deleted_at = db.Column(db.DateTime, nullable=True)
topics = db.relationship(
'NoteTopic',
primaryjoin=
'and_(Note.id==NoteTopic.note_id, NoteTopic.deleted_at==None)',
back_populates='note',
lazy=True,
)
attachments = db.relationship(
'NoteAttachment',
primaryjoin=
'and_(Note.id==NoteAttachment.note_id, NoteAttachment.deleted_at==None)',
back_populates='note',
lazy=True,
)
def __init__(self, author_uid, author_name, author_role, author_dept_codes,
sid, subject, body):
self.author_uid = author_uid
self.author_name = author_name
self.author_role = author_role
self.author_dept_codes = author_dept_codes
self.sid = sid
self.subject = subject
self.body = body
@classmethod
def find_by_id(cls, note_id):
return cls.query.filter(and_(
cls.id == note_id, cls.deleted_at == None)).first() # noqa: E711
@classmethod
def create(
cls,
author_uid,
author_name,
author_role,
author_dept_codes,
sid,
subject,
body,
topics=(),
attachments=(),
template_attachment_ids=(),
):
note = cls(author_uid, author_name, author_role, author_dept_codes,
sid, subject, body)
for topic in topics:
note.topics.append(
NoteTopic.create(note, titleize(vacuum_whitespace(topic)),
author_uid), )
for byte_stream_bundle in attachments:
note.attachments.append(
NoteAttachment.create(
note_id=note.id,
name=byte_stream_bundle['name'],
byte_stream=byte_stream_bundle['byte_stream'],
uploaded_by=author_uid,
), )
for template_attachment in NoteTemplateAttachment.get_attachments(
template_attachment_ids):
note.attachments.append(
NoteAttachment.create_using_template_attachment(
note_id=note.id,
template_attachment=template_attachment,
uploaded_by=author_uid,
), )
db.session.add(note)
std_commit()
cls.refresh_search_index()
return note
@classmethod
def create_batch(
cls,
author_id,
author_uid,
author_name,
author_role,
author_dept_codes,
sids,
subject,
body,
topics=(),
attachments=(),
template_attachment_ids=(),
):
t = time.time()
note_ids_per_sid = _create_notes(
author_id=author_id,
author_uid=author_uid,
author_name=author_name,
author_role=author_role,
author_dept_codes=author_dept_codes,
body=body,
sids=sids,
subject=subject,
)
note_ids = list(note_ids_per_sid.values())
_add_topics_to_notes(author_uid=author_uid,
note_ids=note_ids,
topics=topics)
_add_attachments_to_notes(
attachments=attachments,
template_attachment_ids=template_attachment_ids,
author_uid=author_uid,
note_ids=note_ids,
)
cls.refresh_search_index()
app.logger.info(
f'Batch note creation: {len(sids)} records inserted in {str(time.time() - t)} seconds'
)
return note_ids_per_sid
@classmethod
def search(cls, search_phrase, author_uid, student_csid, topic,
datetime_from, datetime_to):
if search_phrase:
fts_selector = """SELECT id, ts_rank(fts_index, plainto_tsquery('english', :search_phrase)) AS rank
FROM notes_fts_index
WHERE fts_index @@ plainto_tsquery('english', :search_phrase)"""
params = {
'search_phrase': search_phrase,
}
else:
fts_selector = 'SELECT id, 0 AS rank FROM notes WHERE deleted_at IS NULL'
params = {}
if author_uid:
author_filter = 'AND notes.author_uid = :author_uid'
params.update({'author_uid': author_uid})
else:
author_filter = ''
if student_csid:
student_filter = 'AND notes.sid = :student_csid'
params.update({'student_csid': student_csid})
else:
student_filter = ''
date_filter = ''
if datetime_from:
date_filter += ' AND updated_at >= :datetime_from'
params.update({'datetime_from': datetime_from})
if datetime_to:
date_filter += ' AND updated_at < :datetime_to'
params.update({'datetime_to': datetime_to})
if topic:
topic_join = 'JOIN note_topics nt on nt.topic = :topic AND nt.note_id = notes.id'
params.update({'topic': topic})
else:
topic_join = ''
query = text(f"""
SELECT notes.* FROM ({fts_selector}) AS fts
JOIN notes
ON fts.id = notes.id
{author_filter}
{student_filter}
{date_filter}
{topic_join}
ORDER BY fts.rank DESC, notes.id
""").bindparams(**params)
result = db.session.execute(query)
keys = result.keys()
return [dict(zip(keys, row)) for row in result.fetchall()]
@classmethod
def refresh_search_index(cls):
db.session.execute(text('REFRESH MATERIALIZED VIEW notes_fts_index'))
std_commit()
@classmethod
def update(cls, note_id, subject, body=None, topics=()):
note = cls.find_by_id(note_id=note_id)
if note:
note.subject = subject
note.body = body
cls._update_note_topics(note, topics)
std_commit()
db.session.refresh(note)
cls.refresh_search_index()
return note
else:
return None
@classmethod
def add_attachment(cls, note_id, attachment):
note = cls.find_by_id(note_id=note_id)
if note:
cls._add_attachment(note, attachment)
std_commit()
return note
else:
return None
@classmethod
def delete_attachment(cls, note_id, attachment_id):
note = cls.find_by_id(note_id=note_id)
if note:
cls._delete_attachments(note, (attachment_id, ))
std_commit()
return note
else:
return None
@classmethod
def _update_note_topics(cls, note, topics):
modified = False
now = utc_now()
topics = set([titleize(vacuum_whitespace(topic)) for topic in topics])
existing_topics = set(
note_topic.topic
for note_topic in NoteTopic.find_by_note_id(note.id))
topics_to_delete = existing_topics - topics
topics_to_add = topics - existing_topics
for topic in topics_to_delete:
topic_to_delete = next(
(t for t in note.topics if t.topic == topic), None)
if topic_to_delete:
topic_to_delete.deleted_at = now
modified = True
for topic in topics_to_add:
note.topics.append(NoteTopic.create(note, topic,
note.author_uid), )
modified = True
if modified:
note.updated_at = now
@classmethod
def _add_attachment(cls, note, attachment):
note.attachments.append(
NoteAttachment.create(
note_id=note.id,
name=attachment['name'],
byte_stream=attachment['byte_stream'],
uploaded_by=note.author_uid,
), )
note.updated_at = utc_now()
@classmethod
def _delete_attachments(cls, note, delete_attachment_ids):
modified = False
now = utc_now()
for attachment in note.attachments:
if attachment.id in delete_attachment_ids:
attachment.deleted_at = now
modified = True
if modified:
note.updated_at = now
@classmethod
def get_notes_by_sid(cls, sid):
# SQLAlchemy uses "magic methods" to create SQL; it requires '==' instead of 'is'.
return cls.query.filter(and_(cls.sid == sid,
cls.deleted_at == None)).order_by(
cls.updated_at,
cls.id).all() # noqa: E711
@classmethod
def delete(cls, note_id):
note = cls.find_by_id(note_id)
if note:
now = utc_now()
note.deleted_at = now
for attachment in note.attachments:
attachment.deleted_at = now
for topic in note.topics:
topic.deleted_at = now
std_commit()
cls.refresh_search_index()
def to_api_json(self):
attachments = [
a.to_api_json() for a in self.attachments if not a.deleted_at
]
topics = [t.to_api_json() for t in self.topics if not t.deleted_at]
return {
'id': self.id,
'attachments': attachments,
'authorUid': self.author_uid,
'authorName': self.author_name,
'authorRole': self.author_role,
'authorDeptCodes': self.author_dept_codes,
'sid': self.sid,
'subject': self.subject,
'body': self.body,
'topics': topics,
'createdAt': self.created_at,
'updatedAt': self.updated_at,
}
class UniversityDeptMember(Base):
__tablename__ = 'university_dept_members'
university_dept_id = db.Column(db.Integer,
db.ForeignKey('university_depts.id'),
primary_key=True)
authorized_user_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
primary_key=True)
is_advisor = db.Column(db.Boolean, nullable=False)
is_director = db.Column(db.Boolean, nullable=False)
is_scheduler = db.Column(db.Boolean, nullable=False)
automate_membership = db.Column(db.Boolean, nullable=False)
authorized_user = db.relationship('AuthorizedUser',
back_populates='department_memberships')
# Pre-load UniversityDept below to avoid 'failed to locate', as seen during routes.py init phase
university_dept = db.relationship(UniversityDept.__name__,
back_populates='authorized_users')
def __init__(self,
is_advisor,
is_director,
is_scheduler,
automate_membership=True):
self.is_advisor = is_advisor
self.is_director = is_director
self.is_scheduler = is_scheduler
self.automate_membership = automate_membership
@classmethod
def create_or_update_membership(
cls,
university_dept,
authorized_user,
is_advisor,
is_director,
is_scheduler,
automate_membership=True,
):
dept_id = university_dept.id
user_id = authorized_user.id
existing_membership = cls.query.filter_by(
university_dept_id=dept_id, authorized_user_id=user_id).first()
if existing_membership:
membership = existing_membership
membership.is_advisor = is_advisor
membership.is_director = is_director
membership.is_scheduler = is_scheduler
membership.automate_membership = automate_membership
else:
membership = cls(
is_advisor=is_advisor,
is_director=is_director,
is_scheduler=is_scheduler,
automate_membership=automate_membership,
)
membership.authorized_user = authorized_user
membership.university_dept = university_dept
authorized_user.department_memberships.append(membership)
university_dept.authorized_users.append(membership)
db.session.add(membership)
std_commit()
return membership
@classmethod
def update_membership(
cls,
university_dept_id,
authorized_user_id,
is_advisor,
is_director,
is_scheduler,
automate_membership,
):
membership = cls.query.filter_by(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id).first()
if membership:
membership.is_advisor = membership.is_advisor if is_advisor is None else is_advisor
membership.is_director = membership.is_director if is_director is None else is_director
membership.is_scheduler = membership.is_scheduler if is_scheduler is None else is_scheduler
membership.automate_membership = membership.automate_membership if automate_membership is None else automate_membership
std_commit()
return membership
return None
@classmethod
def delete_membership(cls, university_dept_id, authorized_user_id):
membership = cls.query.filter_by(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id).first()
if not membership:
return False
db.session.delete(membership)
std_commit()
return True
def to_api_json(self):
return {
'universityDeptId': self.university_dept_id,
'authorizedUserId': self.authorized_user_id,
'isAdvisor': self.is_advisor,
'isDirector': self.is_director,
'isScheduler': self.is_scheduler,
'automateMembership': self.automate_membership,
}
class UniversityDept(Base):
__tablename__ = 'university_depts'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
dept_code = db.Column(db.String(80), nullable=False)
dept_name = db.Column(db.String(255), nullable=False)
authorized_users = db.relationship(
'UniversityDeptMember',
back_populates='university_dept',
)
__table_args__ = (db.UniqueConstraint('dept_code', 'dept_name', name='university_depts_code_unique_constraint'),)
def __init__(self, dept_code, dept_name):
self.dept_code = dept_code
self.dept_name = dept_name
@classmethod
def find_by_dept_code(cls, dept_code):
return cls.query.filter_by(dept_code=dept_code).first()
@classmethod
def get_all(cls, exclude_empty=False):
if exclude_empty:
results = db.session.execute(text('select distinct university_dept_id from university_dept_members'))
dept_ids = [row['university_dept_id'] for row in results]
return cls.query.filter(cls.id.in_(dept_ids)).order_by(cls.dept_name).all()
else:
return cls.query.order_by(cls.dept_name).all()
@classmethod
def create(cls, dept_code, dept_name):
dept = cls(dept_code=dept_code, dept_name=dept_name)
db.session.add(dept)
std_commit()
return dept
def delete_automated_members(self):
sql = """
DELETE FROM university_dept_members
WHERE university_dept_id = :id
AND automate_membership IS TRUE;
UPDATE authorized_users SET deleted_at = now()
WHERE is_admin IS FALSE
AND deleted_at IS NULL
AND id NOT IN (SELECT authorized_user_id FROM university_dept_members);"""
db.session.execute(text(sql), {'id': self.id})
std_commit()
def memberships_from_loch(self):
program_affiliations = BERKELEY_DEPT_CODE_TO_PROGRAM_AFFILIATIONS.get(self.dept_code)
if not program_affiliations:
return []
advisors = data_loch.get_advisor_uids_for_affiliations(
program_affiliations.get('program'),
program_affiliations.get('affiliations'),
)
def _resolve(uid, rows):
rows = list(rows)
if len(rows) == 1:
return rows[0]
can_access_advising_data = reduce((lambda r, s: r['can_access_advising_data'] or s['can_access_advising_data']), rows)
can_access_canvas_data = reduce((lambda r, s: r['can_access_canvas_data'] or s['can_access_canvas_data']), rows)
degree_progress_permission = reduce((lambda r, s: r['degree_progress_permission'] or s['degree_progress_permission']), rows)
return {
'uid': uid,
'can_access_advising_data': can_access_advising_data,
'can_access_canvas_data': can_access_canvas_data,
'degree_progress_permission': degree_progress_permission,
}
advisors.sort(key=itemgetter('uid'))
return [_resolve(uid, rows) for (uid, rows) in groupby(advisors, itemgetter('uid'))]
class Note(Base):
__tablename__ = 'notes'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
author_uid = db.Column(db.String(255), nullable=False)
author_name = db.Column(db.String(255), nullable=False)
author_role = db.Column(db.String(255), nullable=False)
author_dept_codes = db.Column(ARRAY(db.String), nullable=False)
body = db.Column(db.Text, nullable=False)
is_private = db.Column(db.Boolean, nullable=False, default=False)
sid = db.Column(db.String(80), nullable=False)
subject = db.Column(db.String(255), nullable=False)
deleted_at = db.Column(db.DateTime, nullable=True)
topics = db.relationship(
'NoteTopic',
primaryjoin='and_(Note.id==NoteTopic.note_id, NoteTopic.deleted_at==None)',
back_populates='note',
lazy=True,
)
attachments = db.relationship(
'NoteAttachment',
primaryjoin='and_(Note.id==NoteAttachment.note_id, NoteAttachment.deleted_at==None)',
back_populates='note',
lazy=True,
)
def __init__(self, author_uid, author_name, author_role, author_dept_codes, body, sid, subject, is_private=False):
self.author_dept_codes = author_dept_codes
self.author_name = author_name
self.author_role = author_role
self.author_uid = author_uid
self.body = body
self.is_private = is_private
self.sid = sid
self.subject = subject
@classmethod
def find_by_id(cls, note_id):
return cls.query.filter(and_(cls.id == note_id, cls.deleted_at == None)).first() # noqa: E711
@classmethod
def create(
cls,
author_uid,
author_name,
author_role,
author_dept_codes,
sid,
subject,
body,
topics=(),
attachments=(),
is_private=False,
template_attachment_ids=(),
):
ids_by_sid = cls.create_batch(
author_id=AuthorizedUser.get_id_per_uid(author_uid),
author_uid=author_uid,
author_name=author_name,
author_role=author_role,
author_dept_codes=author_dept_codes,
sids=[sid],
subject=subject,
body=body,
is_private=is_private,
topics=topics,
attachments=attachments,
template_attachment_ids=template_attachment_ids,
)
def _get_note_id():
values = list(ids_by_sid.values())
return values[0] if len(values) > 0 else None
return cls.find_by_id(_get_note_id())
@classmethod
def create_batch(
cls,
author_id,
author_uid,
author_name,
author_role,
author_dept_codes,
body,
is_private,
sids,
subject,
attachments=(),
topics=(),
template_attachment_ids=(),
):
sid_count = len(sids)
benchmark = get_benchmarker('begin note creation' if sid_count == 1 else f'begin creation of {sid_count} notes')
ids_by_sid = _create_notes(
author_id=author_id,
author_uid=author_uid,
author_name=author_name,
author_role=author_role,
author_dept_codes=author_dept_codes,
body=body,
is_private=is_private,
sids=sids,
subject=subject,
)
note_ids = list(ids_by_sid.values())
benchmark('begin add 1 topic' if len(topics) == 1 else f'begin add {len(topics)} topics')
_add_topics_to_notes(author_uid=author_uid, note_ids=note_ids, topics=topics)
benchmark('begin add 1 attachment' if len(attachments) == 1 else f'begin add {len(attachments)} attachments')
_add_attachments_to_notes(
attachments=attachments,
template_attachment_ids=template_attachment_ids,
author_uid=author_uid,
note_ids=note_ids,
)
benchmark('begin refresh search index')
cls.refresh_search_index()
benchmark('end note creation' if sid_count == 1 else f'end creation of {sid_count} notes')
return ids_by_sid
@classmethod
def search(
cls,
search_phrase,
author_uid,
student_csid,
topic,
datetime_from,
datetime_to,
include_private_notes=False,
):
if search_phrase:
fts_selector = """SELECT id, ts_rank(fts_index, plainto_tsquery('english', :search_phrase)) AS rank
FROM notes_fts_index
WHERE fts_index @@ plainto_tsquery('english', :search_phrase)"""
params = {
'search_phrase': search_phrase,
}
else:
fts_selector = 'SELECT id, 0 AS rank FROM notes WHERE deleted_at IS NULL'
params = {}
if author_uid:
author_filter = 'AND notes.author_uid = :author_uid'
params.update({'author_uid': author_uid})
else:
author_filter = ''
if student_csid:
student_filter = 'AND notes.sid = :student_csid'
params.update({'student_csid': student_csid})
else:
student_filter = ''
date_filter = ''
if datetime_from:
date_filter += ' AND updated_at >= :datetime_from'
params.update({'datetime_from': datetime_from})
if datetime_to:
date_filter += ' AND updated_at < :datetime_to'
params.update({'datetime_to': datetime_to})
if topic:
topic_join = 'JOIN note_topics nt on nt.topic = :topic AND nt.note_id = notes.id'
params.update({'topic': topic})
else:
topic_join = ''
where_clause = '' if include_private_notes else 'WHERE notes.is_private IS FALSE'
query = text(f"""
SELECT notes.* FROM ({fts_selector}) AS fts
JOIN notes
ON fts.id = notes.id
{author_filter}
{student_filter}
{date_filter}
{topic_join}
{where_clause}
ORDER BY fts.rank DESC, notes.id
""").bindparams(**params)
result = db.session.execute(query)
keys = result.keys()
return [dict(zip(keys, row)) for row in result.fetchall()]
@classmethod
def refresh_search_index(cls):
def _refresh_search_index(db_session):
db_session.execute(text('REFRESH MATERIALIZED VIEW notes_fts_index'))
db_session.execute(text('REFRESH MATERIALIZED VIEW advisor_author_index'))
std_commit(session=db_session)
bg_execute(_refresh_search_index)
@classmethod
def update(cls, note_id, subject, body=None, is_private=False, topics=()):
note = cls.find_by_id(note_id=note_id)
if note:
note.body = body
note.is_private = is_private
note.subject = subject
cls._update_note_topics(note, topics)
std_commit()
db.session.refresh(note)
cls.refresh_search_index()
return note
else:
return None
@classmethod
def add_attachment(cls, note_id, attachment):
note = cls.find_by_id(note_id=note_id)
if note:
cls._add_attachment(note, attachment)
std_commit()
return note
else:
return None
@classmethod
def delete_attachment(cls, note_id, attachment_id):
note = cls.find_by_id(note_id=note_id)
if note:
cls._delete_attachments(note, (attachment_id,))
std_commit()
return note
else:
return None
@classmethod
def _update_note_topics(cls, note, topics):
modified = False
now = utc_now()
topics = set(topics)
existing_topics = set(note_topic.topic for note_topic in NoteTopic.find_by_note_id(note.id))
topics_to_delete = existing_topics - topics
topics_to_add = topics - existing_topics
for topic in topics_to_delete:
topic_to_delete = next((t for t in note.topics if t.topic == topic), None)
if topic_to_delete:
topic_to_delete.deleted_at = now
modified = True
for topic in topics_to_add:
note.topics.append(
NoteTopic.create(note, topic, note.author_uid),
)
modified = True
if modified:
note.updated_at = now
@classmethod
def _add_attachment(cls, note, attachment):
note.attachments.append(
NoteAttachment.create(
note_id=note.id,
name=attachment['name'],
byte_stream=attachment['byte_stream'],
uploaded_by=note.author_uid,
),
)
note.updated_at = utc_now()
@classmethod
def _delete_attachments(cls, note, delete_attachment_ids):
modified = False
now = utc_now()
for attachment in note.attachments:
if attachment.id in delete_attachment_ids:
attachment.deleted_at = now
modified = True
if modified:
note.updated_at = now
@classmethod
def get_notes_by_sid(cls, sid):
# SQLAlchemy uses "magic methods" to create SQL; it requires '==' instead of 'is'.
return cls.query.filter(and_(cls.sid == sid, cls.deleted_at == None)).order_by(cls.updated_at, cls.id).all() # noqa: E711
@classmethod
def delete(cls, note_id):
note = cls.find_by_id(note_id)
if note:
now = utc_now()
note.deleted_at = now
for attachment in note.attachments:
attachment.deleted_at = now
for topic in note.topics:
topic.deleted_at = now
std_commit()
cls.refresh_search_index()
def to_api_json(self):
attachments = self.attachments_to_api_json()
return {
'id': self.id,
'attachments': attachments,
'authorUid': self.author_uid,
'authorName': self.author_name,
'authorRole': self.author_role,
'authorDeptCodes': self.author_dept_codes,
'body': self.body,
'isPrivate': self.is_private,
'sid': self.sid,
'subject': self.subject,
'topics': [topic.topic for topic in self.topics],
'createdAt': self.created_at,
'deletedAt': self.deleted_at,
'updatedAt': self.updated_at,
}
def attachments_to_api_json(self):
return [a.to_api_json() for a in self.attachments if not a.deleted_at]
class CohortFilter(Base):
__tablename__ = 'cohort_filters'
__transient_sids = []
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
domain = db.Column(cohort_domain_type, nullable=False)
owner_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
name = db.Column(db.String(255), nullable=False)
filter_criteria = db.Column(JSONB, nullable=False)
# Fetching a large array literal from Postgres can be expensive. We defer until invoking code demands it.
sids = deferred(db.Column(ARRAY(db.String(80))))
student_count = db.Column(db.Integer)
alert_count = db.Column(db.Integer)
owner = db.relationship('AuthorizedUser', back_populates='cohort_filters')
def __init__(self, domain, name, filter_criteria):
self.domain = domain
self.name = name
self.filter_criteria = filter_criteria
def __repr__(self):
return f"""<CohortFilter {self.id},
domain={self.domain},
name={self.name},
owner_id={self.owner_id},
filter_criteria={self.filter_criteria},
sids={self.sids},
student_count={self.student_count},
alert_count={self.alert_count},
updated_at={self.updated_at},
created_at={self.created_at}>"""
@classmethod
def create(cls, uid, name, filter_criteria, domain='default', **kwargs):
if all(not isinstance(value, bool) and not value
for value in filter_criteria.values()):
raise InternalServerError(
'Cohort creation requires at least one filter specification.')
cohort = cls(domain=domain, name=name, filter_criteria=filter_criteria)
user = AuthorizedUser.find_by_uid(uid)
user.cohort_filters.append(cohort)
db.session.flush()
std_commit()
return cohort.to_api_json(**kwargs)
@classmethod
def update(cls,
cohort_id,
name=None,
filter_criteria=None,
alert_count=None,
**kwargs):
cohort = cls.query.filter_by(id=cohort_id).first()
if name:
cohort.name = name
if filter_criteria:
cohort.filter_criteria = filter_criteria
cohort.clear_sids_and_student_count()
if alert_count is not None:
cohort.alert_count = alert_count
else:
# Alert count will be refreshed
cohort.update_alert_count(None)
std_commit()
return cohort.to_api_json(**kwargs)
@classmethod
def get_sids(cls, cohort_id):
query = db.session.query(cls).options(undefer('sids'))
cohort = query.filter_by(id=cohort_id).first()
return cohort and cohort.sids
@classmethod
def get_domain_of_cohort(cls, cohort_id):
query = text('SELECT domain FROM cohort_filters WHERE id = :id')
result = db.session.execute(query, {'id': cohort_id}).first()
return result and result['domain']
def clear_sids_and_student_count(self):
self.__transient_sids = self.sids
self.update_sids_and_student_count(None, None)
def update_sids_and_student_count(self, sids, student_count):
self.sids = sids
self.student_count = student_count
std_commit()
return self
def update_alert_count(self, count):
self.alert_count = count
std_commit()
return self
def track_membership_changes(self):
# Track membership changes only if the cohort has been saved and has an id.
if self.id:
old_sids = set(self.__transient_sids)
new_sids = set(self.sids)
removed_sids = old_sids - new_sids
added_sids = new_sids - old_sids
CohortFilterEvent.create_bulk(self.id, added_sids, removed_sids)
self.__transient_sids = []
@classmethod
def get_cohorts_of_user_id(cls, user_id, domain='default'):
query = text("""
SELECT id, domain, name, filter_criteria, alert_count, student_count
FROM cohort_filters c
WHERE c.owner_id = :user_id AND c.domain = :domain
ORDER BY c.name
""")
results = db.session.execute(query, {
'domain': domain,
'user_id': user_id
})
def transform(row):
return {
'id': row['id'],
'domain': row['domain'],
'name': row['name'],
'criteria': row['filter_criteria'],
'alertCount': row['alert_count'],
'totalStudentCount': row['student_count'],
}
return [transform(row) for row in results]
@classmethod
def get_cohorts_owned_by_uids(cls, uids, domain='default'):
query = text("""
SELECT
c.id, c.domain, c.name, c.filter_criteria, c.alert_count, c.student_count, u.uid
FROM cohort_filters c
INNER JOIN authorized_users u ON c.owner_id = u.id
WHERE u.uid = ANY(:uids) AND c.domain = :domain
GROUP BY c.id, c.name, c.filter_criteria, c.alert_count, c.student_count, u.uid
""")
results = db.session.execute(query, {'domain': domain, 'uids': uids})
def transform(row):
return {
'id': row['id'],
'domain': row['domain'],
'name': row['name'],
'criteria': row['filter_criteria'],
'ownerUid': row['uid'],
'alertCount': row['alert_count'],
'totalStudentCount': row['student_count'],
}
return [transform(row) for row in results]
@classmethod
def is_cohort_owned_by(cls, cohort_id, user_id):
query = text("""
SELECT count(*) FROM cohort_filters c
WHERE c.owner_id = :user_id AND c.id = :cohort_id
""")
results = db.session.execute(
query,
{
'cohort_id': cohort_id,
'user_id': user_id,
},
)
return results.first()['count']
@classmethod
def refresh_alert_counts_for_owner(cls, owner_id):
query = text("""
UPDATE cohort_filters
SET alert_count = updated_cohort_counts.alert_count
FROM
(
SELECT cohort_filters.id AS cohort_filter_id, count(*) AS alert_count
FROM alerts
JOIN cohort_filters
ON alerts.sid = ANY(cohort_filters.sids)
AND alerts.key LIKE :key
AND alerts.deleted_at IS NULL
AND cohort_filters.owner_id = :owner_id
LEFT JOIN alert_views
ON alert_views.alert_id = alerts.id
AND alert_views.viewer_id = :owner_id
WHERE alert_views.dismissed_at IS NULL
GROUP BY cohort_filters.id
) updated_cohort_counts
WHERE cohort_filters.id = updated_cohort_counts.cohort_filter_id
""")
result = db.session.execute(query, {
'owner_id': owner_id,
'key': current_term_id() + '_%'
})
std_commit()
return result
@classmethod
def find_by_id(cls, cohort_id, **kwargs):
cohort = cls.query.filter_by(id=cohort_id).first()
return cohort and cohort.to_api_json(**kwargs)
@classmethod
def delete(cls, cohort_id):
cohort_filter = cls.query.filter_by(id=cohort_id).first()
db.session.delete(cohort_filter)
std_commit()
def to_base_json(self):
c = self.filter_criteria
c = c if isinstance(c, dict) else json.loads(c)
user_uid = self.owner.uid if self.owner else None
option_groups = CohortFilterOptions(
user_uid, scope_for_criteria()).get_filter_option_groups()
for label, option_group in option_groups.items():
for option in option_group:
key = option['key']
if key in c:
value = c.get(key)
if option['type']['db'] == 'boolean':
c[key] = util.to_bool_or_none(value)
else:
c[key] = value
def _owner_to_json(owner):
if not owner:
return None
return {
'uid':
owner.uid,
'deptCodes': [
m.university_dept.dept_code
for m in owner.department_memberships
],
}
return {
'id':
self.id,
'domain':
self.domain,
'name':
self.name,
'code':
self.id,
'criteria':
c,
'owner':
_owner_to_json(self.owner),
'teamGroups':
athletics.get_team_groups(c.get('groupCodes'))
if c.get('groupCodes') else [],
'alertCount':
self.alert_count,
}
def to_api_json(
self,
order_by=None,
offset=0,
limit=50,
term_id=None,
alert_offset=None,
alert_limit=None,
include_sids=False,
include_students=True,
include_profiles=False,
include_alerts_for_user_id=None,
):
benchmark = get_benchmarker(f'CohortFilter {self.id} to_api_json')
benchmark('begin')
cohort_json = self.to_base_json()
if not include_students and not include_alerts_for_user_id and self.student_count is not None:
# No need for a students query; return the database-stashed student count.
cohort_json.update({
'totalStudentCount': self.student_count,
})
benchmark('end')
return cohort_json
benchmark('begin students query')
sids_only = not include_students
if self.domain == 'admitted_students':
results = _query_admitted_students(
benchmark=benchmark,
criteria=cohort_json['criteria'],
limit=limit,
offset=offset,
order_by=order_by,
sids_only=sids_only,
)
else:
results = _query_students(
benchmark=benchmark,
criteria=cohort_json['criteria'],
include_profiles=include_profiles,
limit=limit,
offset=offset,
order_by=order_by,
owner=self.owner,
term_id=term_id,
sids_only=sids_only,
)
# If the cohort is new or cache refresh is underway then store student_count and sids in the db.
if self.student_count is None:
self.update_sids_and_student_count(
sids=results['sids'] if results else [],
student_count=results['totalStudentCount'] if results else 0,
)
if self.domain == 'default':
self.track_membership_changes()
if results:
# Cohort might have tens of thousands of SIDs.
if include_sids:
cohort_json['sids'] = results['sids']
cohort_json.update({
'totalStudentCount':
results['totalStudentCount'],
})
if include_students:
cohort_json.update({
'students': results['students'],
})
if include_alerts_for_user_id and self.domain == 'default':
benchmark('begin alerts query')
alert_count_per_sid = Alert.include_alert_counts_for_students(
viewer_user_id=include_alerts_for_user_id,
group=results,
offset=alert_offset,
limit=alert_limit,
)
benchmark('end alerts query')
cohort_json.update({
'alerts': alert_count_per_sid,
})
if self.alert_count is None:
alert_count = sum(student['alertCount']
for student in alert_count_per_sid)
self.update_alert_count(alert_count)
cohort_json.update({
'alertCount': alert_count,
})
benchmark('end')
return cohort_json
class AuthorizedUser(Base):
__tablename__ = 'authorized_users'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
uid = db.Column(db.String(255), nullable=False, unique=True)
is_admin = db.Column(db.Boolean)
in_demo_mode = db.Column(db.Boolean, nullable=False)
can_access_canvas_data = db.Column(db.Boolean, nullable=False)
created_by = db.Column(db.String(255), nullable=False)
deleted_at = db.Column(db.DateTime, nullable=True)
# When True, is_blocked prevents a deleted user from being revived by the automated refresh.
is_blocked = db.Column(db.Boolean, nullable=False, default=False)
department_memberships = db.relationship(
'UniversityDeptMember',
back_populates='authorized_user',
lazy='joined',
)
drop_in_departments = db.relationship(
'DropInAdvisor',
back_populates='authorized_user',
primaryjoin=
'and_(AuthorizedUser.id==DropInAdvisor.authorized_user_id, DropInAdvisor.deleted_at==None)',
lazy='joined',
)
cohort_filters = db.relationship(
'CohortFilter',
secondary=cohort_filter_owners,
back_populates='owners',
lazy='joined',
)
alert_views = db.relationship(
'AlertView',
back_populates='viewer',
lazy='joined',
)
def __init__(self,
uid,
created_by,
is_admin=False,
in_demo_mode=False,
can_access_canvas_data=True):
self.uid = uid
self.created_by = created_by
self.is_admin = is_admin
self.in_demo_mode = in_demo_mode
self.can_access_canvas_data = can_access_canvas_data
def __repr__(self):
return f"""<AuthorizedUser {self.uid},
is_admin={self.is_admin},
in_demo_mode={self.in_demo_mode},
can_access_canvas_data={self.can_access_canvas_data},
created={self.created_at},
created_by={self.created_by},
updated={self.updated_at},
deleted={self.deleted_at},
is_blocked={self.is_blocked}>
"""
@classmethod
def delete_and_block(cls, uid):
now = utc_now()
user = cls.query.filter_by(uid=uid).first()
user.deleted_at = now
user.is_blocked = True
std_commit()
return user
@classmethod
def create_or_restore(cls,
uid,
created_by,
is_admin=False,
can_access_canvas_data=True):
existing_user = cls.query.filter_by(uid=uid).first()
if existing_user:
if existing_user.is_blocked:
return False
# If restoring a previously deleted user, respect passed-in attributes.
if existing_user.deleted_at:
existing_user.is_admin = is_admin
existing_user.can_access_canvas_data = can_access_canvas_data
existing_user.created_by = created_by
existing_user.deleted_at = None
# If the user currently exists in a non-deleted state, attributes passed in as True
# should replace existing attributes set to False, but not vice versa.
else:
if can_access_canvas_data and not existing_user.can_access_canvas_data:
existing_user.can_access_canvas_data = True
if is_admin and not existing_user.is_admin:
existing_user.is_admin = True
existing_user.created_by = created_by
user = existing_user
else:
user = cls(
uid=uid,
created_by=created_by,
is_admin=is_admin,
in_demo_mode=False,
can_access_canvas_data=can_access_canvas_data,
)
std_commit()
return user
@classmethod
def get_id_per_uid(cls, uid):
query = text(
f'SELECT id FROM authorized_users WHERE uid = :uid AND deleted_at IS NULL'
)
result = db.session.execute(query, {'uid': uid}).first()
return result and result['id']
@classmethod
def get_uid_per_id(cls, user_id):
query = text(
f'SELECT uid FROM authorized_users WHERE id = :user_id AND deleted_at IS NULL'
)
result = db.session.execute(query, {'user_id': user_id}).first()
return result and result['uid']
@classmethod
def find_by_id(cls, db_id):
return AuthorizedUser.query.filter_by(id=db_id,
deleted_at=None).first()
@classmethod
def find_by_uid(cls, uid):
return AuthorizedUser.query.filter_by(uid=uid, deleted_at=None).first()
@classmethod
def get_all_active_users(cls):
return cls.query.filter_by(deleted_at=None).all()
@classmethod
def get_all_users(cls):
return cls.query.all()
@classmethod
def get_all_uids_in_scope(cls, scope=()):
sql = 'SELECT uid FROM authorized_users u '
if not scope:
return None
elif 'ADMIN' in scope:
sql += 'WHERE u.deleted_at IS NULL'
else:
sql += """
JOIN university_dept_members m ON m.authorized_user_id = u.id
JOIN university_depts d ON d.id = m.university_dept_id
WHERE
d.dept_code = ANY(:scope)
AND u.deleted_at IS NULL
"""
results = db.session.execute(sql, {'scope': scope})
return [row['uid'] for row in results]
def authorized_user(cls): # noqa: N805
return db.relationship('AuthorizedUser',
back_populates=cls.authorized_user_relationship)
class CohortFilter(Base):
__tablename__ = 'cohort_filters'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
name = db.Column(db.String(255), nullable=False)
filter_criteria = db.Column(JSONB, nullable=False)
# Fetching a large array literal from Postgres can be expensive. We defer until invoking code demands it.
sids = deferred(db.Column(ARRAY(db.String(80))))
student_count = db.Column(db.Integer)
alert_count = db.Column(db.Integer)
owners = db.relationship('AuthorizedUser',
secondary=cohort_filter_owners,
back_populates='cohort_filters')
def __init__(self, name, filter_criteria):
self.name = name
self.filter_criteria = filter_criteria
def __repr__(self):
return f"""<CohortFilter {self.id},
name={self.name},
owners={self.owners},
filter_criteria={self.filter_criteria},
sids={self.sids},
student_count={self.student_count},
alert_count={self.alert_count},
updated_at={self.updated_at},
created_at={self.created_at}>"""
@classmethod
def create(cls, uid, name, filter_criteria, **kwargs):
if all(not isinstance(value, bool) and not value
for value in filter_criteria.values()):
raise InternalServerError(
'Cohort creation requires at least one filter specification.')
cohort = cls(name=name, filter_criteria=filter_criteria)
user = AuthorizedUser.find_by_uid(uid)
user.cohort_filters.append(cohort)
db.session.flush()
std_commit()
return cohort.to_api_json(**kwargs)
@classmethod
def update(cls,
cohort_id,
name=None,
filter_criteria=None,
alert_count=None,
**kwargs):
cohort = cls.query.filter_by(id=cohort_id).first()
if name:
cohort.name = name
if filter_criteria:
cohort.filter_criteria = filter_criteria
cohort.sids = None
cohort.student_count = None
if alert_count is not None:
cohort.alert_count = alert_count
else:
# Alert count will be refreshed
cohort.update_alert_count(None)
std_commit()
return cohort.to_api_json(**kwargs)
@classmethod
def get_sids(cls, cohort_id):
query = db.session.query(cls).options(undefer('sids'))
cohort = query.filter_by(id=cohort_id).first()
return cohort and cohort.sids
def update_sids_and_student_count(self, sids, student_count):
self.sids = sids
self.student_count = student_count
std_commit()
return self
def update_alert_count(self, count):
self.alert_count = count
std_commit()
return self
@classmethod
def share(cls, cohort_id, user_id):
cohort = cls.query.filter_by(id=cohort_id).first()
user = AuthorizedUser.find_by_uid(user_id)
user.cohort_filters.append(cohort)
std_commit()
@classmethod
def get_cohorts_of_user_id(cls, user_id):
query = text(f"""
SELECT id, name, filter_criteria, alert_count, student_count FROM cohort_filters c
LEFT JOIN cohort_filter_owners o ON o.cohort_filter_id = c.id
WHERE o.user_id = :user_id
ORDER BY c.name
""")
results = db.session.execute(query, {'user_id': user_id})
def transform(row):
return {
'id': row['id'],
'name': row['name'],
'criteria': row['filter_criteria'],
'alertCount': row['alert_count'],
'totalStudentCount': row['student_count'],
}
return [transform(row) for row in results]
@classmethod
def get_cohorts_owned_by_uids(cls, uids):
query = text(f"""
SELECT c.id, c.name, c.filter_criteria, c.alert_count, c.student_count, ARRAY_AGG(uid) authorized_users
FROM cohort_filters c
INNER JOIN cohort_filter_owners o ON c.id = o.cohort_filter_id
INNER JOIN authorized_users u ON o.user_id = u.id
WHERE u.uid = ANY(:uids)
GROUP BY c.id, c.name, c.filter_criteria, c.alert_count, c.student_count
""")
results = db.session.execute(query, {'uids': uids})
def transform(row):
return {
'id': row['id'],
'name': row['name'],
'criteria': row['filter_criteria'],
'owners': row['authorized_users'],
'alertCount': row['alert_count'],
'totalStudentCount': row['student_count'],
}
return [transform(row) for row in results]
@classmethod
def is_cohort_owned_by(cls, cohort_id, user_id):
query = text(f"""
SELECT count(*) FROM cohort_filters c
LEFT JOIN cohort_filter_owners o ON o.cohort_filter_id = c.id
WHERE o.user_id = :user_id AND c.id = :cohort_id
""")
results = db.session.execute(
query,
{
'cohort_id': cohort_id,
'user_id': user_id,
},
)
return results.first()['count']
@classmethod
def refresh_alert_counts_for_owner(cls, owner_id):
query = text(f"""
UPDATE cohort_filters
SET alert_count = updated_cohort_counts.alert_count
FROM
(
SELECT cohort_filters.id AS cohort_filter_id, count(*) AS alert_count
FROM alerts
JOIN cohort_filters
ON alerts.sid = ANY(cohort_filters.sids)
AND alerts.key LIKE :key
AND alerts.active IS TRUE
JOIN cohort_filter_owners
ON cohort_filters.id = cohort_filter_owners.cohort_filter_id
AND cohort_filter_owners.user_id = :owner_id
LEFT JOIN alert_views
ON alert_views.alert_id = alerts.id
AND alert_views.viewer_id = :owner_id
WHERE alert_views.dismissed_at IS NULL
GROUP BY cohort_filters.id
) updated_cohort_counts
WHERE cohort_filters.id = updated_cohort_counts.cohort_filter_id
""")
result = db.session.execute(query, {
'owner_id': owner_id,
'key': current_term_id() + '_%'
})
std_commit()
return result
@classmethod
def find_by_id(cls, cohort_id, **kwargs):
cohort = cls.query.filter_by(id=cohort_id).first()
return cohort and cohort.to_api_json(**kwargs)
@classmethod
def delete(cls, cohort_id):
cohort_filter = cls.query.filter_by(id=cohort_id).first()
db.session.delete(cohort_filter)
std_commit()
def to_api_json(
self,
order_by=None,
offset=0,
limit=50,
alert_offset=None,
alert_limit=None,
include_sids=False,
include_students=True,
include_profiles=False,
include_alerts_for_user_id=None,
):
benchmark = get_benchmarker(f'CohortFilter {self.id} to_api_json')
benchmark('begin')
c = self.filter_criteria
c = c if isinstance(c, dict) else json.loads(c)
coe_advisor_ldap_uids = util.get(c, 'coeAdvisorLdapUids')
if not isinstance(coe_advisor_ldap_uids, list):
coe_advisor_ldap_uids = [coe_advisor_ldap_uids
] if coe_advisor_ldap_uids else None
cohort_name = self.name
cohort_json = {
'id': self.id,
'code': self.id,
'name': cohort_name,
'owners': [],
'alertCount': self.alert_count,
}
for owner in self.owners:
cohort_json['owners'].append({
'uid':
owner.uid,
'deptCodes': [
m.university_dept.dept_code
for m in owner.department_memberships
],
})
coe_ethnicities = c.get('coeEthnicities')
coe_genders = c.get('coeGenders')
coe_prep_statuses = c.get('coePrepStatuses')
coe_probation = util.to_bool_or_none(c.get('coeProbation'))
coe_underrepresented = util.to_bool_or_none(
c.get('coeUnderrepresented'))
cohort_owner_academic_plans = util.get(c, 'cohortOwnerAcademicPlans')
entering_terms = c.get('enteringTerms')
ethnicities = c.get('ethnicities')
expected_grad_terms = c.get('expectedGradTerms')
genders = c.get('genders')
gpa_ranges = c.get('gpaRanges')
group_codes = c.get('groupCodes')
in_intensive_cohort = util.to_bool_or_none(c.get('inIntensiveCohort'))
is_inactive_asc = util.to_bool_or_none(c.get('isInactiveAsc'))
is_inactive_coe = util.to_bool_or_none(c.get('isInactiveCoe'))
last_name_ranges = c.get('lastNameRanges')
last_term_gpa_ranges = c.get('lastTermGpaRanges')
levels = c.get('levels')
majors = c.get('majors')
midpoint_deficient_grade = util.to_bool_or_none(
c.get('midpointDeficient'))
team_groups = athletics.get_team_groups(
group_codes) if group_codes else []
transfer = util.to_bool_or_none(c.get('transfer'))
underrepresented = util.to_bool_or_none(c.get('underrepresented'))
unit_ranges = c.get('unitRanges')
cohort_json.update({
'criteria': {
'coeAdvisorLdapUids': coe_advisor_ldap_uids,
'coeEthnicities': coe_ethnicities,
'coeGenders': coe_genders,
'coePrepStatuses': coe_prep_statuses,
'coeProbation': coe_probation,
'coeUnderrepresented': coe_underrepresented,
'cohortOwnerAcademicPlans': cohort_owner_academic_plans,
'enteringTerms': entering_terms,
'ethnicities': ethnicities,
'expectedGradTerms': expected_grad_terms,
'genders': genders,
'gpaRanges': gpa_ranges,
'groupCodes': group_codes,
'inIntensiveCohort': in_intensive_cohort,
'isInactiveAsc': is_inactive_asc,
'isInactiveCoe': is_inactive_coe,
'lastNameRanges': last_name_ranges,
'lastTermGpaRanges': last_term_gpa_ranges,
'levels': levels,
'majors': majors,
'midpointDeficient': midpoint_deficient_grade,
'transfer': transfer,
'unitRanges': unit_ranges,
'underrepresented': underrepresented,
},
'teamGroups': team_groups,
})
if not include_students and not include_alerts_for_user_id and self.student_count is not None:
# No need for a students query; return the database-stashed student count.
cohort_json.update({
'totalStudentCount': self.student_count,
})
benchmark('end')
return cohort_json
benchmark('begin students query')
sids_only = not include_students
# Translate the "My Students" filter, if present, into queryable criteria. Although our database relationships allow
# for multiple cohort owners, we assume a single owner here since the "My Students" filter makes no sense
# in any other scenario.
if cohort_owner_academic_plans:
if self.owners:
owner_sid = get_csid_for_uid(app, self.owners[0].uid)
else:
owner_sid = current_user.get_csid()
advisor_plan_mappings = [{
'advisor_sid': owner_sid,
'academic_plan_code': plan
} for plan in cohort_owner_academic_plans]
else:
advisor_plan_mappings = None
results = query_students(
advisor_plan_mappings=advisor_plan_mappings,
coe_advisor_ldap_uids=coe_advisor_ldap_uids,
coe_ethnicities=coe_ethnicities,
coe_genders=coe_genders,
coe_prep_statuses=coe_prep_statuses,
coe_probation=coe_probation,
coe_underrepresented=coe_underrepresented,
entering_terms=entering_terms,
ethnicities=ethnicities,
expected_grad_terms=expected_grad_terms,
genders=genders,
gpa_ranges=gpa_ranges,
group_codes=group_codes,
in_intensive_cohort=in_intensive_cohort,
include_profiles=(include_students and include_profiles),
is_active_asc=None
if is_inactive_asc is None else not is_inactive_asc,
is_active_coe=None
if is_inactive_coe is None else not is_inactive_coe,
last_name_ranges=last_name_ranges,
last_term_gpa_ranges=last_term_gpa_ranges,
levels=levels,
limit=limit,
majors=majors,
midpoint_deficient_grade=midpoint_deficient_grade,
offset=offset,
order_by=order_by,
sids_only=sids_only,
transfer=transfer,
underrepresented=underrepresented,
unit_ranges=unit_ranges,
)
benchmark('end students query')
if results:
# Cohort might have tens of thousands of SIDs.
if include_sids:
cohort_json['sids'] = results['sids']
cohort_json.update({
'totalStudentCount':
results['totalStudentCount'],
})
# If the cohort is new or cache refresh is underway then store student_count and sids in the db.
if self.student_count is None:
self.update_sids_and_student_count(
results['sids'], results['totalStudentCount'])
if include_students:
cohort_json.update({
'students': results['students'],
})
if include_alerts_for_user_id:
benchmark('begin alerts query')
alert_count_per_sid = Alert.include_alert_counts_for_students(
viewer_user_id=include_alerts_for_user_id,
group=results,
offset=alert_offset,
limit=alert_limit,
)
benchmark('end alerts query')
cohort_json.update({
'alerts': alert_count_per_sid,
})
if self.alert_count is None:
alert_count = sum(student['alertCount']
for student in alert_count_per_sid)
self.update_alert_count(alert_count)
cohort_json.update({
'alertCount': alert_count,
})
benchmark('end')
return cohort_json
class DegreeProgressUnitRequirement(Base):
__tablename__ = 'degree_progress_unit_requirements'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
created_by = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
min_units = db.Column(db.Integer, nullable=False)
name = db.Column(db.String(255), nullable=False)
template_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_templates.id'),
nullable=False)
updated_by = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
categories = db.relationship(
'DegreeProgressCategoryUnitRequirement',
back_populates='unit_requirement',
)
courses = db.relationship(
'DegreeProgressCourseUnitRequirement',
back_populates='unit_requirement',
)
template = db.relationship('DegreeProgressTemplate',
back_populates='unit_requirements')
__table_args__ = (db.UniqueConstraint(
'name',
'template_id',
name='degree_progress_unit_requirements_name_template_id_unique_const',
), )
def __init__(self, created_by, min_units, name, template_id, updated_by):
self.created_by = created_by
self.min_units = min_units
self.name = name
self.template_id = template_id
self.updated_by = updated_by
def __repr__(self):
return f"""<DegreeProgressUnitRequirement id={self.id},
name={self.name},
min_units={self.min_units},
template_id={self.template_id},
created_at={self.created_at},
created_by={self.created_by},
updated_at={self.updated_at},
updated_by={self.updated_by}>"""
@classmethod
def create(cls, created_by, min_units, name, template_id):
unit_requirement = cls(
created_by=created_by,
min_units=min_units,
name=name,
template_id=template_id,
updated_by=created_by,
)
db.session.add(unit_requirement)
std_commit()
return unit_requirement
@classmethod
def delete(cls, unit_requirement_id):
unit_requirement = cls.query.filter_by(id=unit_requirement_id).first()
DegreeProgressCategoryUnitRequirement.delete_mappings(
unit_requirement_id=unit_requirement.id)
db.session.delete(unit_requirement)
std_commit()
@classmethod
def find_by_id(cls, unit_requirement_id):
return cls.query.filter_by(id=unit_requirement_id).first()
@classmethod
def update(cls, id_, min_units, name, updated_by):
unit_requirement = cls.query.filter_by(id=id_).first()
unit_requirement.min_units = min_units
unit_requirement.name = name
unit_requirement.updated_by = updated_by
std_commit()
db.session.refresh(unit_requirement)
return unit_requirement
def to_api_json(self):
return {
'id': self.id,
'name': self.name,
'minUnits': self.min_units,
'createdAt': _isoformat(self.created_at),
'createdBy': self.created_by,
'updatedAt': _isoformat(self.updated_at),
'updatedBy': self.updated_by,
'templateId': self.template_id,
}
class DegreeProgressCategory(Base):
__tablename__ = 'degree_progress_categories'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
accent_color = db.Column(db.String(255))
category_type = db.Column(degree_progress_category_type, nullable=False)
course_units = db.Column(NUMRANGE)
description = db.Column(db.Text)
grade = db.Column(db.String(50))
is_recommended = db.Column(db.Boolean, nullable=False, default=False)
name = db.Column(db.String(255), nullable=False)
note = db.Column(db.Text)
parent_category_id = db.Column(
db.Integer, db.ForeignKey('degree_progress_categories.id'))
position = db.Column(db.Integer, nullable=False)
template_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_templates.id'),
nullable=False)
unit_requirements = db.relationship(
DegreeProgressCategoryUnitRequirement.__name__,
back_populates='category',
lazy='joined',
)
def __init__(
self,
category_type,
name,
position,
template_id,
accent_color=None,
course_units=None,
description=None,
grade=None,
parent_category_id=None,
):
self.accent_color = accent_color
self.category_type = category_type
self.course_units = course_units
self.description = description
self.grade = grade
self.name = name
self.parent_category_id = parent_category_id
self.position = position
self.template_id = template_id
def __repr__(self):
return f"""<DegreeProgressCategory id={self.id},
accent_color={self.accent_color},
category_type={self.category_type},
course_units={self.course_units},
description={self.description},
grade={self.grade},
is_recommended={self.is_recommended},
name={self.name},
note={self.note},
parent_category_id={self.parent_category_id},
position={self.position},
template_id={self.template_id},
created_at={self.created_at},
updated_at={self.updated_at}>"""
@classmethod
def create(
cls,
category_type,
name,
position,
template_id,
accent_color=None,
course_units_lower=None,
course_units_upper=None,
description=None,
grade=None,
parent_category_id=None,
unit_requirement_ids=None,
):
course_units = None if course_units_lower is None else NumericRange(
float(course_units_lower),
float(course_units_upper or course_units_lower),
'[]',
)
category = cls(
accent_color=accent_color,
category_type=category_type,
course_units=course_units,
description=description,
grade=grade,
name=name,
parent_category_id=parent_category_id,
position=position,
template_id=template_id,
)
# TODO: Use 'unit_requirement_ids' in mapping this instance to 'unit_requirements' table
db.session.add(category)
std_commit()
for unit_requirement_id in unit_requirement_ids or []:
DegreeProgressCategoryUnitRequirement.create(
category_id=category.id,
unit_requirement_id=int(unit_requirement_id),
)
return category
@classmethod
def delete(cls, category_id):
for unit_requirement in DegreeProgressCategoryUnitRequirement.find_by_category_id(
category_id):
db.session.delete(unit_requirement)
for course in DegreeProgressCourse.find_by_category_id(category_id):
db.session.delete(course)
std_commit()
category = cls.query.filter_by(id=category_id).first()
db.session.delete(category)
std_commit()
@classmethod
def find_by_id(cls, category_id):
return cls.query.filter_by(id=category_id).first()
@classmethod
def find_by_parent_category_id(cls, parent_category_id):
return cls.query.filter_by(parent_category_id=parent_category_id).all()
@classmethod
def get_categories(cls, template_id):
hierarchy = []
categories = []
for category in cls.query.filter_by(template_id=template_id).order_by(
asc(cls.created_at)).all():
category_type = category.category_type
api_json = category.to_api_json()
if category_type == 'Category':
# A 'Category' can have both courses and subcategories. A 'Subcategory' can have courses.
api_json['courseRequirements'] = []
api_json['subcategories'] = []
elif category_type == 'Subcategory':
api_json['courseRequirements'] = []
categories.append(api_json)
categories_by_id = dict(
(category['id'], category) for category in categories)
for category in categories:
parent_category_id = category['parentCategoryId']
if parent_category_id:
parent = categories_by_id[parent_category_id]
key = 'subcategories' if category[
'categoryType'] == 'Subcategory' else 'courseRequirements'
parent[key].append(category)
else:
hierarchy.append(category)
return hierarchy
@classmethod
def recommend(
cls,
accent_color,
category_id,
course_units_lower,
course_units_upper,
grade,
is_recommended,
note,
):
category = cls.query.filter_by(id=category_id).first()
category.accent_color = accent_color
units_lower = to_float_or_none(course_units_lower)
category.course_units = None if units_lower is None else NumericRange(
units_lower,
to_float_or_none(course_units_upper) or units_lower,
'[]',
)
category.grade = grade
category.is_recommended = is_recommended
category.note = note
std_commit()
return cls.find_by_id(category_id=category_id)
@classmethod
def set_campus_requirement_satisfied(
cls,
category_id,
is_satisfied,
):
category = cls.query.filter_by(id=category_id).first()
category.category_type = 'Campus Requirement, Satisfied' if is_satisfied else 'Campus Requirement, Unsatisfied'
std_commit()
return cls.find_by_id(category_id=category_id)
@classmethod
def update(
cls,
category_id,
course_units_lower,
course_units_upper,
description,
name,
parent_category_id,
unit_requirement_ids,
):
category = cls.query.filter_by(id=category_id).first()
units_lower = to_float_or_none(course_units_lower)
category.course_units = None if units_lower is None else NumericRange(
units_lower,
to_float_or_none(course_units_upper) or units_lower,
'[]',
)
category.description = description
category.name = name
category.parent_category_id = parent_category_id
unit_requirement_id_set = set(unit_requirement_ids or [])
existing_unit_requirements = DegreeProgressCategoryUnitRequirement.find_by_category_id(
category_id)
existing_unit_requirement_id_set = set(
[u.unit_requirement_id for u in existing_unit_requirements])
for unit_requirement_id in (unit_requirement_id_set -
existing_unit_requirement_id_set):
DegreeProgressCategoryUnitRequirement.create(
category_id=category.id,
unit_requirement_id=unit_requirement_id,
)
for unit_requirement_id in (existing_unit_requirement_id_set -
unit_requirement_id_set):
delete_me = next(e for e in existing_unit_requirements
if e.unit_requirement_id == unit_requirement_id)
db.session.delete(delete_me)
std_commit()
return cls.find_by_id(category_id=category_id)
def to_api_json(self):
unit_requirements = [
m.unit_requirement.to_api_json()
for m in (self.unit_requirements or [])
]
return {
'id':
self.id,
'accentColor':
self.accent_color,
'categoryType':
self.category_type,
'courses': [
c.to_api_json()
for c in DegreeProgressCourse.find_by_category_id(
category_id=self.id)
],
'createdAt':
_isoformat(self.created_at),
'description':
self.description,
'grade':
self.grade,
'isRecommended':
self.is_recommended,
'name':
self.name,
'note':
self.note,
'parentCategoryId':
self.parent_category_id,
'position':
self.position,
'templateId':
self.template_id,
'unitsLower':
self.course_units and self.course_units.lower,
'unitsUpper':
self.course_units and self.course_units.upper,
'unitRequirements':
sorted(unit_requirements, key=lambda r: r['name']),
'updatedAt':
_isoformat(self.updated_at),
}
class DegreeProgressCourse(Base):
__tablename__ = 'degree_progress_courses'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
accent_color = db.Column(db.String(255))
category_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_categories.id'))
degree_check_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_templates.id'),
nullable=False)
display_name = db.Column(db.String(255), nullable=False)
grade = db.Column(db.String(50), nullable=False)
ignore = db.Column(db.Boolean, nullable=False)
note = db.Column(db.Text)
manually_created_at = db.Column(db.DateTime)
manually_created_by = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'))
section_id = db.Column(db.Integer)
sid = db.Column(db.String(80), nullable=False)
term_id = db.Column(db.Integer)
units = db.Column(db.Numeric, nullable=False)
unit_requirements = db.relationship(
DegreeProgressCourseUnitRequirement.__name__,
back_populates='course',
lazy='joined',
)
__table_args__ = (db.UniqueConstraint(
'category_id',
'degree_check_id',
'manually_created_at',
'manually_created_by',
'section_id',
'sid',
'term_id',
name='degree_progress_courses_category_id_course_unique_constraint',
), )
def __init__(
self,
degree_check_id,
display_name,
grade,
section_id,
sid,
term_id,
units,
accent_color=None,
category_id=None,
ignore=False,
manually_created_at=None,
manually_created_by=None,
note=None,
):
self.accent_color = accent_color
self.category_id = category_id
self.degree_check_id = degree_check_id
self.display_name = display_name
self.grade = grade
self.ignore = ignore
self.manually_created_by = manually_created_by
if self.manually_created_by and not manually_created_at:
raise ValueError(
'manually_created_at is required if manually_created_by is present.'
)
else:
self.manually_created_at = manually_created_at
self.note = note
self.section_id = section_id
self.sid = sid
self.term_id = term_id
self.units = units
def __repr__(self):
return f"""<DegreeProgressCourse id={self.id},
accent_color={self.accent_color},
category_id={self.category_id},
degree_check_id={self.degree_check_id},
display_name={self.display_name},
grade={self.grade},
ignore={self.ignore},
manually_created_at={self.manually_created_at},
manually_created_by={self.manually_created_by},
note={self.note},
section_id={self.section_id},
sid={self.sid},
term_id={self.term_id},
units={self.units},>"""
@classmethod
def assign(cls, category_id, course_id):
course = cls.query.filter_by(id=course_id).first()
course.category_id = category_id
course.ignore = False
std_commit()
DegreeProgressCourseUnitRequirement.delete(course_id)
for u in DegreeProgressCategoryUnitRequirement.find_by_category_id(
category_id):
DegreeProgressCourseUnitRequirement.create(course.id,
u.unit_requirement_id)
return course
@classmethod
def create(
cls,
degree_check_id,
display_name,
grade,
section_id,
sid,
term_id,
units,
accent_color=None,
category_id=None,
manually_created_at=None,
manually_created_by=None,
note=None,
unit_requirement_ids=(),
):
course = cls(
accent_color=accent_color,
category_id=category_id,
degree_check_id=degree_check_id,
display_name=display_name,
grade=grade,
manually_created_at=manually_created_at,
manually_created_by=manually_created_by,
note=note,
section_id=section_id,
sid=sid,
term_id=term_id,
units=units if (units is None or is_float(units)) else 0,
)
db.session.add(course)
std_commit()
for unit_requirement_id in unit_requirement_ids:
DegreeProgressCourseUnitRequirement.create(
course_id=course.id,
unit_requirement_id=unit_requirement_id,
)
return course
@classmethod
def delete(cls, course):
db.session.delete(course)
std_commit()
@classmethod
def find_by_id(cls, course_id):
return cls.query.filter_by(id=course_id).first()
@classmethod
def find_by_category_id(cls, category_id):
return cls.query.filter_by(category_id=category_id).all()
@classmethod
def find_by_sid(cls, degree_check_id, sid):
return cls.query.filter_by(degree_check_id=degree_check_id,
sid=sid).all()
@classmethod
def get_courses(cls, degree_check_id, manually_created_at,
manually_created_by, section_id, sid, term_id):
return cls.query.filter_by(
degree_check_id=degree_check_id,
manually_created_at=manually_created_at,
manually_created_by=manually_created_by,
section_id=section_id,
sid=sid,
term_id=term_id,
).all()
@classmethod
def unassign(cls, course_id, ignore=False):
course = cls.query.filter_by(id=course_id).first()
course.category_id = None
course.ignore = ignore
std_commit()
DegreeProgressCourseUnitRequirement.delete(course_id)
return course
@classmethod
def update(
cls,
accent_color,
course_id,
grade,
name,
note,
units,
unit_requirement_ids,
):
course = cls.query.filter_by(id=course_id).first()
course.accent_color = accent_color
course.grade = grade
course.display_name = name
course.note = note
course.units = units if (units is None or is_float(units)) else 0
existing_unit_requirements = DegreeProgressCourseUnitRequirement.find_by_course_id(
course_id)
existing_unit_requirement_id_set = set(
[u.unit_requirement_id for u in existing_unit_requirements])
unit_requirement_id_set = set(unit_requirement_ids or [])
for unit_requirement_id in (unit_requirement_id_set -
existing_unit_requirement_id_set):
DegreeProgressCourseUnitRequirement.create(
course_id=course.id,
unit_requirement_id=unit_requirement_id,
)
for unit_requirement_id in (existing_unit_requirement_id_set -
unit_requirement_id_set):
delete_me = next(e for e in existing_unit_requirements
if e.unit_requirement_id == unit_requirement_id)
db.session.delete(delete_me)
std_commit()
return course
def to_api_json(self):
unit_requirements = [
m.unit_requirement.to_api_json()
for m in (self.unit_requirements or [])
]
return {
'accentColor': self.accent_color,
'categoryId': self.category_id,
'createdAt': _isoformat(self.created_at),
'degreeCheckId': self.degree_check_id,
'grade': self.grade,
'id': self.id,
'ignore': self.ignore,
'manuallyCreatedAt': _isoformat(self.manually_created_at),
'manuallyCreatedBy': self.manually_created_by,
'name': self.display_name,
'note': self.note,
'sectionId': self.section_id,
'sid': self.sid,
'termId': self.term_id,
'termName': term_name_for_sis_id(self.term_id),
'unitRequirements': sorted(unit_requirements,
key=lambda r: r['name']),
'units': self.units,
'updatedAt': _isoformat(self.updated_at),
}
class AuthorizedUser(Base):
__tablename__ = 'authorized_users'
SEARCH_HISTORY_ITEM_MAX_LENGTH = 256
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
uid = db.Column(db.String(255), nullable=False, unique=True)
is_admin = db.Column(db.Boolean)
in_demo_mode = db.Column(db.Boolean, nullable=False)
can_access_advising_data = db.Column(db.Boolean, nullable=False)
can_access_canvas_data = db.Column(db.Boolean, nullable=False)
created_by = db.Column(db.String(255), nullable=False)
degree_progress_permission = db.Column(generic_permission_type_enum)
deleted_at = db.Column(db.DateTime, nullable=True)
# When True, is_blocked prevents a deleted user from being revived by the automated refresh.
is_blocked = db.Column(db.Boolean, nullable=False, default=False)
search_history = deferred(db.Column(ARRAY(db.String), nullable=True))
department_memberships = db.relationship(
'UniversityDeptMember',
back_populates='authorized_user',
lazy='joined',
)
drop_in_departments = db.relationship(
'DropInAdvisor',
back_populates='authorized_user',
lazy='joined',
)
same_day_departments = db.relationship(
'SameDayAdvisor',
back_populates='authorized_user',
lazy='joined',
)
scheduler_departments = db.relationship(
'Scheduler',
back_populates='authorized_user',
lazy='joined',
)
cohort_filters = db.relationship(
'CohortFilter',
back_populates='owner',
lazy='joined',
)
alert_views = db.relationship(
'AlertView',
back_populates='viewer',
lazy='joined',
)
def __init__(
self,
uid,
created_by,
is_admin=False,
is_blocked=False,
in_demo_mode=False,
can_access_advising_data=True,
can_access_canvas_data=True,
degree_progress_permission=None,
search_history=(),
):
self.uid = uid
self.created_by = created_by
self.is_admin = is_admin
self.is_blocked = is_blocked
self.in_demo_mode = in_demo_mode
self.can_access_advising_data = can_access_advising_data
self.can_access_canvas_data = can_access_canvas_data
self.degree_progress_permission = degree_progress_permission
self.search_history = search_history
def __repr__(self):
return f"""<AuthorizedUser {self.uid},
is_admin={self.is_admin},
in_demo_mode={self.in_demo_mode},
can_access_advising_data={self.can_access_advising_data},
can_access_canvas_data={self.can_access_canvas_data},
degree_progress_permission={self.degree_progress_permission},
search_history={self.search_history},
created={self.created_at},
created_by={self.created_by},
updated={self.updated_at},
deleted={self.deleted_at},
is_blocked={self.is_blocked}>
"""
@classmethod
def delete(cls, uid):
now = utc_now()
user = cls.query.filter_by(uid=uid).first()
user.deleted_at = now
std_commit()
return user
@classmethod
def un_delete(cls, uid):
user = cls.query.filter_by(uid=uid).first()
user.deleted_at = None
std_commit()
return user
@classmethod
def create_or_restore(
cls,
uid,
created_by,
is_admin=False,
is_blocked=False,
can_access_advising_data=True,
can_access_canvas_data=True,
degree_progress_permission=None,
):
existing_user = cls.query.filter_by(uid=uid).first()
if existing_user:
if existing_user.is_blocked:
return False
# If restoring a previously deleted user, respect passed-in attributes.
if existing_user.deleted_at:
existing_user.is_admin = is_admin
existing_user.is_blocked = is_blocked
existing_user.can_access_advising_data = can_access_advising_data
existing_user.can_access_canvas_data = can_access_canvas_data
existing_user.created_by = created_by
if not existing_user.degree_progress_permission:
existing_user.degree_progress_permission = degree_progress_permission
existing_user.deleted_at = None
# If the user currently exists in a non-deleted state, attributes passed in as True
# should replace existing attributes set to False, but not vice versa.
else:
if can_access_advising_data and not existing_user.can_access_advising_data:
existing_user.can_access_advising_data = True
if can_access_canvas_data and not existing_user.can_access_canvas_data:
existing_user.can_access_canvas_data = True
if not existing_user.degree_progress_permission:
existing_user.degree_progress_permission = degree_progress_permission
if is_admin and not existing_user.is_admin:
existing_user.is_admin = True
if is_blocked and not existing_user.is_blocked:
existing_user.is_blocked = True
existing_user.created_by = created_by
user = existing_user
else:
user = cls(
uid=uid,
created_by=created_by,
is_admin=is_admin,
is_blocked=is_blocked,
in_demo_mode=False,
can_access_advising_data=can_access_advising_data,
can_access_canvas_data=can_access_canvas_data,
degree_progress_permission=degree_progress_permission,
)
db.session.add(user)
std_commit()
return user
@classmethod
def get_id_per_uid(cls, uid, include_deleted=False):
sql = 'SELECT id FROM authorized_users WHERE uid = :uid'
if not include_deleted:
sql += ' AND deleted_at IS NULL'
query = text(sql)
result = db.session.execute(query, {'uid': uid}).first()
return result and result['id']
@classmethod
def get_uid_per_id(cls, user_id):
query = text(
'SELECT uid FROM authorized_users WHERE id = :user_id AND deleted_at IS NULL'
)
result = db.session.execute(query, {'user_id': user_id}).first()
return result and result['uid']
@classmethod
def find_by_id(cls, user_id, include_deleted=False):
query = cls.query.filter_by(
id=user_id) if include_deleted else cls.query.filter_by(
id=user_id, deleted_at=None)
return query.first()
@classmethod
def users_with_uid_like(cls, uid_snippet, include_deleted=False):
like_uid_snippet = cls.uid.like(f'%{uid_snippet}%')
criteria = like_uid_snippet if include_deleted else and_(
like_uid_snippet, cls.deleted_at == None) # noqa: E711
return cls.query.filter(criteria).all()
@classmethod
def find_by_uid(cls, uid, ignore_deleted=True):
query = cls.query.filter_by(
uid=uid,
deleted_at=None) if ignore_deleted else cls.query.filter_by(
uid=uid)
return query.first()
@classmethod
def get_all_active_users(cls, include_deleted=False):
return cls.query.all() if include_deleted else cls.query.filter_by(
deleted_at=None).all()
@classmethod
def get_admin_users(cls, ignore_deleted=True):
if ignore_deleted:
query = cls.query.filter(and_(
cls.is_admin, cls.deleted_at == None)) # noqa: E711
else:
query = cls.query.filter(cls.is_admin)
return query.all()
@classmethod
def add_to_search_history(cls, user_id, search_phrase):
search_phrase = vacuum_whitespace(search_phrase)
query = text(
'SELECT search_history FROM authorized_users WHERE id = :user_id')
result = db.session.execute(query, {'user_id': user_id}).first()
if result:
search_history = result['search_history'] or []
if len(search_phrase) > cls.SEARCH_HISTORY_ITEM_MAX_LENGTH:
if ' ' in search_phrase:
search_phrase = search_phrase[:cls.
SEARCH_HISTORY_ITEM_MAX_LENGTH
+ 1]
search_phrase = search_phrase[:search_phrase.rindex(' ') +
1].strip()
else:
search_phrase = search_phrase[:cls.
SEARCH_HISTORY_ITEM_MAX_LENGTH]
phrase_lowered = search_phrase.lower()
for idx, entry in enumerate(search_history):
if phrase_lowered == entry.lower():
del search_history[idx]
search_history.insert(0, search_phrase)
max_size = app.config['USER_SEARCH_HISTORY_MAX_SIZE']
if len(search_history) > max_size:
del search_history[max_size:]
sql_text = text(
'UPDATE authorized_users SET search_history = :history WHERE id = :id'
)
db.session.execute(sql_text, {
'history': search_history,
'id': user_id
})
return cls.get_search_history(user_id)
else:
return None
@classmethod
def get_search_history(cls, user_id):
query = text(
'SELECT search_history FROM authorized_users WHERE id = :id')
result = db.session.execute(query, {'id': user_id}).first()
return result and result['search_history']
@classmethod
def get_users(
cls,
deleted=None,
blocked=None,
dept_code=None,
role=None,
):
query_tables, query_filter, query_bindings = _users_sql(
blocked=blocked,
deleted=deleted,
dept_code=dept_code,
role=role,
)
query = text(f"""
SELECT u.id
{query_tables}
{query_filter}
""")
results = db.session.execute(query, query_bindings)
user_ids = [row['id'] for row in results]
return cls.query.filter(cls.id.in_(user_ids)).all(), len(user_ids)
@classmethod
def get_all_uids_in_scope(cls, scope=()):
sql = 'SELECT uid FROM authorized_users u '
if not scope:
return None
elif 'ADMIN' in scope:
sql += 'WHERE u.deleted_at IS NULL'
else:
sql += """
JOIN university_dept_members m ON m.authorized_user_id = u.id
JOIN university_depts d ON d.id = m.university_dept_id
WHERE
d.dept_code = ANY(:scope)
AND u.deleted_at IS NULL
"""
results = db.session.execute(sql, {'scope': scope})
return [row['uid'] for row in results]
@classmethod
def update_user(
cls,
user_id,
can_access_advising_data=False,
can_access_canvas_data=False,
degree_progress_permission=None,
is_admin=False,
is_blocked=False,
include_deleted=False,
):
user = AuthorizedUser.find_by_id(user_id, include_deleted)
user.can_access_advising_data = can_access_advising_data
user.can_access_canvas_data = can_access_canvas_data
user.degree_progress_permission = degree_progress_permission
user.is_admin = is_admin
user.is_blocked = is_blocked
std_commit()
return user