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 AppointmentRead(db.Model):
__tablename__ = 'appointments_read'
viewer_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False,
primary_key=True)
appointment_id = db.Column(db.Integer,
db.ForeignKey('appointments.id'),
nullable=False,
primary_key=True)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
__table_args__ = (db.UniqueConstraint(
'viewer_id',
'appointment_id',
name='appointments_read_viewer_id_appointment_id_unique_constraint',
), )
def __init__(self, viewer_id, appointment_id):
self.viewer_id = viewer_id
self.appointment_id = appointment_id
@classmethod
def find_or_create(cls, viewer_id, appointment_id):
appointment_read = cls.query.filter(
and_(cls.viewer_id == viewer_id,
cls.appointment_id == str(appointment_id))).one_or_none()
if not appointment_read:
appointment_read = cls(viewer_id, appointment_id)
db.session.add(appointment_read)
std_commit()
return appointment_read
@classmethod
def was_read_by(cls, viewer_id, appointment_id):
appointment_read = cls.query.filter(
AppointmentRead.viewer_id == viewer_id,
AppointmentRead.appointment_id == appointment_id,
).first()
return appointment_read is not None
@classmethod
def when_user_read_appointment(cls, viewer_id, appointment_id):
appointment_read = cls.query.filter(
AppointmentRead.viewer_id == viewer_id,
AppointmentRead.appointment_id == appointment_id,
).first()
return appointment_read and appointment_read.created_at
def to_api_json(self):
return {
'appointmentId': self.appointment_id,
'createdAt': _isoformat(self.created_at),
'viewerId': self.viewer_id,
}
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 NoteRead(db.Model):
__tablename__ = 'notes_read'
viewer_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False,
primary_key=True)
note_id = db.Column(db.String(255), nullable=False, primary_key=True)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
__table_args__ = (db.UniqueConstraint(
'viewer_id',
'note_id',
name='notes_read_viewer_id_note_id_unique_constraint',
), )
def __init__(self, viewer_id, note_id):
self.viewer_id = viewer_id
self.note_id = note_id
@classmethod
def find_or_create(cls, viewer_id, note_id):
note_read = cls.query.filter(
and_(cls.viewer_id == viewer_id,
cls.note_id == str(note_id))).one_or_none()
if not note_read:
note_read = cls(viewer_id, note_id)
db.session.add(note_read)
std_commit()
return note_read
@classmethod
def get_notes_read_by_user(cls, viewer_id, note_ids):
return cls.query.filter(NoteRead.viewer_id == viewer_id,
NoteRead.note_id.in_(note_ids)).all()
@classmethod
def when_user_read_note(cls, viewer_id, note_id):
note_read = cls.query.filter(NoteRead.viewer_id == viewer_id,
NoteRead.note_id == note_id).first()
return note_read and note_read.created_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 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 CuratedGroup(Base):
__tablename__ = 'student_groups'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
owner_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
name = db.Column(db.String(255), nullable=False)
__table_args__ = (db.UniqueConstraint(
'owner_id',
'name',
name='student_groups_owner_id_name_unique_constraint',
), )
def __init__(self, name, owner_id):
self.name = name
self.owner_id = owner_id
@classmethod
def find_by_id(cls, curated_group_id):
return cls.query.filter_by(id=curated_group_id).first()
@classmethod
def get_curated_groups_by_owner_id(cls, owner_id):
return cls.query.filter_by(owner_id=owner_id).order_by(cls.name).all()
@classmethod
def get_groups_owned_by_uids(cls, uids):
query = text(f"""
SELECT sg.id, sg.name, count(sgm.sid) AS student_count, au.uid AS owner_uid
FROM student_groups sg
LEFT JOIN student_group_members sgm ON sg.id = sgm.student_group_id
JOIN authorized_users au ON sg.owner_id = au.id
WHERE au.uid = ANY(:uids)
GROUP BY sg.id, sg.name, au.id, au.uid
""")
results = db.session.execute(query, {'uids': uids})
def transform(row):
return {
'id': row['id'],
'name': row['name'],
'totalStudentCount': row['student_count'],
'ownerUid': row['owner_uid'],
}
return [transform(row) for row in results]
@classmethod
def curated_group_ids_per_sid(cls, user_id, sid):
query = text(f"""SELECT
student_group_id as id
FROM student_group_members m
JOIN student_groups g ON g.id = m.student_group_id
WHERE g.owner_id = :user_id AND m.sid = :sid""")
results = db.session.execute(query, {'user_id': user_id, 'sid': sid})
return [row['id'] for row in results]
@classmethod
def create(cls, owner_id, name):
curated_group = cls(name, owner_id)
db.session.add(curated_group)
std_commit()
return curated_group
@classmethod
def get_all_sids(cls, curated_group_id):
return CuratedGroupStudent.get_sids(curated_group_id=curated_group_id)
@classmethod
def add_student(cls, curated_group_id, sid):
curated_group = cls.query.filter_by(id=curated_group_id).first()
if curated_group:
CuratedGroupStudent.add_student(curated_group_id=curated_group_id,
sid=sid)
@classmethod
def add_students(cls, curated_group_id, sids):
curated_group = cls.query.filter_by(id=curated_group_id).first()
if curated_group:
CuratedGroupStudent.add_students(curated_group_id=curated_group_id,
sids=sids)
std_commit()
@classmethod
def remove_student(cls, curated_group_id, sid):
if cls.find_by_id(curated_group_id):
CuratedGroupStudent.remove_student(curated_group_id, sid)
@classmethod
def rename(cls, curated_group_id, name):
curated_group = cls.query.filter_by(id=curated_group_id).first()
curated_group.name = name
std_commit()
@classmethod
def delete(cls, curated_group_id):
curated_group = cls.query.filter_by(id=curated_group_id).first()
if curated_group:
db.session.delete(curated_group)
std_commit()
def to_api_json(self,
order_by='last_name',
offset=0,
limit=50,
include_students=True):
feed = {
'id': self.id,
'ownerId': self.owner_id,
'name': self.name,
}
if include_students:
sids = CuratedGroupStudent.get_sids(curated_group_id=self.id)
if sids:
result = query_students(sids=sids,
order_by=order_by,
offset=offset,
limit=limit,
include_profiles=False)
feed['students'] = result['students']
feed['totalStudentCount'] = result['totalStudentCount']
# Attempt to supplement with historical student rows if we seem to be missing something.
if result['totalStudentCount'] < len(sids):
remaining_sids = list(set(sids) - set(result['sids']))
historical_sid_rows = query_historical_sids(remaining_sids)
if len(historical_sid_rows):
for row in historical_sid_rows:
ManuallyAddedAdvisee.find_or_create(row['sid'])
feed['totalStudentCount'] += len(historical_sid_rows)
page_shortfall = max(0,
limit - len(result['students']))
feed[
'students'] += historical_sid_rows[:page_shortfall]
else:
feed['students'] = []
feed['totalStudentCount'] = 0
return feed
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 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 CuratedCohort(Base):
__tablename__ = 'student_groups'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
owner_id = db.Column(db.String(80), db.ForeignKey('authorized_users.id'), nullable=False)
name = db.Column(db.String(255), nullable=False)
students = db.relationship('CuratedCohortStudent', back_populates='curated_cohort', cascade='all')
__table_args__ = (db.UniqueConstraint(
'owner_id',
'name',
name='student_groups_owner_id_name_unique_constraint',
),)
def __init__(self, name, owner_id):
self.name = name
self.owner_id = owner_id
@classmethod
def find_by_id(cls, curated_cohort_id):
return cls.query.filter_by(id=curated_cohort_id).first()
@classmethod
def get_curated_cohorts_by_owner_id(cls, owner_id):
return cls.query.filter_by(owner_id=owner_id).order_by(cls.name).all()
@classmethod
def create(cls, owner_id, name):
curated_cohort = cls(name, owner_id)
db.session.add(curated_cohort)
std_commit()
return curated_cohort
@classmethod
def add_student(cls, curated_cohort_id, sid):
curated_cohort = cls.query.filter_by(id=curated_cohort_id).first()
if curated_cohort:
try:
membership = CuratedCohortStudent(sid=sid, curated_cohort_id=curated_cohort_id)
db.session.add(membership)
std_commit()
except (FlushError, IntegrityError):
app.logger.warn(f'Database error in add_student with curated_cohort_id={curated_cohort_id}, sid {sid}')
return curated_cohort
@classmethod
def add_students(cls, curated_cohort_id, sids):
curated_cohort = cls.query.filter_by(id=curated_cohort_id).first()
if curated_cohort:
try:
for sid in set(sids):
membership = CuratedCohortStudent(sid=sid, curated_cohort_id=curated_cohort_id)
db.session.add(membership)
std_commit()
except (FlushError, IntegrityError):
app.logger.warn(f'Database error in add_students with curated_cohort_id={curated_cohort_id}, sid {sid}')
return curated_cohort
@classmethod
def remove_student(cls, curated_cohort_id, sid):
curated_cohort = cls.find_by_id(curated_cohort_id)
membership = CuratedCohortStudent.query.filter_by(sid=sid, curated_cohort_id=curated_cohort_id).first()
if curated_cohort and membership:
db.session.delete(membership)
std_commit()
@classmethod
def rename(cls, curated_cohort_id, name):
curated_cohort = cls.query.filter_by(id=curated_cohort_id).first()
curated_cohort.name = name
std_commit()
return curated_cohort
@classmethod
def delete(cls, curated_cohort_id):
curated_cohort = cls.query.filter_by(id=curated_cohort_id).first()
if curated_cohort:
db.session.delete(curated_cohort)
std_commit()
def to_api_json(self, sids_only=False, include_students=True):
api_json = {
'id': self.id,
'ownerId': self.owner_id,
'name': self.name,
'studentCount': len(self.students),
}
if sids_only:
api_json['students'] = [{'sid': s.sid} for s in self.students]
elif include_students:
api_json['students'] = get_api_json([s.sid for s in self.students])
return api_json
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)
active = db.Column(db.Boolean, nullable=False)
views = db.relationship(
'AlertView',
back_populates='alert',
lazy=True,
)
__table_args__ = (db.UniqueConstraint(
'sid',
'alert_type',
'key',
name='alerts_sid_alert_type_key_unique_constraint',
), )
@classmethod
def create(cls, sid, alert_type, key=None, message=None, active=True):
# 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, active)
db.session.add(alert)
std_commit()
def __init__(self, sid, alert_type, key, message=None, active=True):
self.sid = sid
self.alert_type = alert_type
self.key = key
self.message = message
self.active = active
def __repr__(self):
return f"""<Alert {self.id},
sid={self.sid},
alert_type={self.alert_type},
key={self.key},
message={self.message},
active={self.active},
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.active = true
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):
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.active = true
AND alerts.key LIKE :key
AND alerts.sid = ANY(:sids)
AND alert_views.dismissed_at IS NULL
GROUP BY alerts.sid
"""
params = {
'viewer_id': viewer_id,
'key': current_term_id() + '_%',
'sids': sids
}
return cls.alert_counts_by_query(query, params)
@classmethod
def alert_counts_by_query(cls, query, params):
results = db.session.execute(text(query), params)
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('firstName'),
'lastName': result.get('lastName'),
'alertCount': alert_counts_by_sid.get(result.get('sid')),
}
scope = get_student_query_scope()
if 'UWASC' in scope or 'ADMIN' in scope:
result_dict['isActiveAsc'] = result.get(
'athleticsProfile', {}).get('isActiveAsc')
return result_dict
return [
result_to_dict(result)
for result in get_full_student_profiles(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.active = true
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
})
def result_to_dict(result):
return {
camelize(key): result[key]
for key in ['id', 'alert_type', 'key', 'message']
}
feed = {
'dismissed': [],
'shown': [],
}
for result in results:
if result['dismissed_at']:
feed['dismissed'].append(result_to_dict(result))
else:
feed['shown'].append(result_to_dict(result))
return feed
def activate(self):
self.active = True
std_commit()
def deactivate(self):
self.active = False
std_commit()
@classmethod
def create_or_activate(cls, sid, alert_type, key, message):
existing_alert = cls.query.filter_by(sid=sid,
alert_type=alert_type,
key=key).first()
if existing_alert:
existing_alert.message = message
existing_alert.activate()
else:
cls.create(sid=sid,
alert_type=alert_type,
key=key,
message=message)
@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}_%')))
results = query.update({cls.active: False},
synchronize_session='fetch')
std_commit()
return results
@classmethod
def infrequent_activity_alerts_enabled(cls):
return (app.config['ALERT_INFREQUENT_ACTIVITY_ENABLED'] and not app.
config['CANVAS_CURRENT_ENROLLMENT_TERM'].startswith('Summer'))
@classmethod
def no_activity_alerts_enabled(cls):
session = data_loch.get_regular_undergraduate_session(
current_term_id())[0]
days_into_session = (datetime.date(datetime.today()) -
session['session_begins']).days
return (app.config['ALERT_NO_ACTIVITY_ENABLED'] and not app.
config['CANVAS_CURRENT_ENROLLMENT_TERM'].startswith('Summer')
and 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}_%')))
results = query.update({cls.active: False},
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(
row['sid'], term_id, enrollment,
no_activity_alerts_enabled,
infrequent_activity_alerts_enabled)
if app.config['ALERT_HOLDS_ENABLED'] and str(
term_id) == current_term_id():
holds = data_loch.get_sis_holds()
for row in holds:
hold_feed = json.loads(row['feed'])
cls.update_hold_alerts(row['sid'], term_id,
hold_feed.get('type'),
hold_feed.get('reason'))
if app.config['ALERT_WITHDRAWAL_ENABLED'] and str(
term_id) == current_term_id():
profiles = data_loch.get_student_profiles()
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)
app.logger.info('Alert update complete')
@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.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:
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} midterm grade of {grade}.'
cls.create_or_activate(sid=sid,
alert_type='midterm',
key=key,
message=message)
@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,
active=True).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_hold_alerts(cls, sid, term_id, hold_type, hold_reason):
key = f"{term_id}_{hold_type.get('code')}_{hold_reason.get('code')}"
message = f"Hold: {hold_reason.get('description')}! {hold_reason.get('formalDescription')}."
cls.create_or_activate(sid=sid,
alert_type='hold',
key=key,
message=message)
@classmethod
def update_withdrawal_cancel_alerts(cls, sid, term_id):
key = f'{term_id}_withdrawal'
message = f'Withdrawal! Student has withdrawn from the {term_name_for_sis_id(term_id)} term.'
cls.create_or_activate(sid=sid,
alert_type='withdrawal',
key=key,
message=message)
@classmethod
def include_alert_counts_for_students(cls, viewer_uid, cohort):
alert_counts = None
viewer = AuthorizedUser.find_by_uid(viewer_uid)
if viewer:
sids = cohort.get('sids') if 'sids' in cohort else [
s['sid'] for s in cohort.get('students', [])
]
alert_counts = cls.current_alert_counts_for_sids(viewer.id, sids)
if 'students' in cohort:
counts_per_sid = {
s.get('sid'): s.get('alertCount')
for s in alert_counts
}
for student in cohort.get('students'):
sid = student['sid']
student['alertCount'] = counts_per_sid.get(
sid) if sid in counts_per_sid else 0
return alert_counts
class CuratedGroup(Base):
__tablename__ = 'student_groups'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
domain = db.Column(cohort_domain_type, nullable=False)
name = db.Column(db.String(255), nullable=False)
owner_id = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False)
__table_args__ = (db.UniqueConstraint(
'owner_id',
'name',
name='student_groups_owner_id_name_unique_constraint',
),)
def __init__(self, domain, name, owner_id):
self.domain = domain
self.name = name
self.owner_id = owner_id
def __repr__(self):
return f"""<CohortFilter {self.id},
domain={self.domain},
name={self.name},
owner_id={self.owner_id},
updated_at={self.updated_at},
created_at={self.created_at}>"""
@classmethod
def find_by_id(cls, curated_group_id):
return cls.query.filter_by(id=curated_group_id).first()
@classmethod
def get_curated_groups(cls, owner_id):
if app.config['FEATURE_FLAG_ADMITTED_STUDENTS']:
filter_by = cls.query.filter_by(owner_id=owner_id)
else:
filter_by = cls.query.filter_by(domain='default', owner_id=owner_id)
return filter_by.order_by(cls.name).all()
@classmethod
def get_groups_owned_by_uids(cls, uids):
domain_clause = 'true' if app.config['FEATURE_FLAG_ADMITTED_STUDENTS'] else "sg.domain = 'default'"
query = text(f"""
SELECT sg.id, sg.domain, sg.name, count(sgm.sid) AS student_count, au.uid AS owner_uid
FROM student_groups sg
LEFT JOIN student_group_members sgm ON sg.id = sgm.student_group_id
JOIN authorized_users au ON sg.owner_id = au.id
WHERE au.uid = ANY(:uids) AND {domain_clause}
GROUP BY sg.id, sg.name, au.id, au.uid
""")
results = db.session.execute(query, {'uids': uids})
def transform(row):
return {
'id': row['id'],
'domain': row['domain'],
'name': row['name'],
'totalStudentCount': row['student_count'],
'ownerUid': row['owner_uid'],
}
return [transform(row) for row in results]
@classmethod
def curated_group_ids_per_sid(cls, domain, sid, user_id):
query = text("""SELECT
student_group_id as id
FROM student_group_members m
JOIN student_groups g ON g.id = m.student_group_id
WHERE g.domain = :domain AND g.owner_id = :user_id AND m.sid = :sid""")
results = db.session.execute(query, {
'domain': domain,
'sid': sid,
'user_id': user_id,
})
return [row['id'] for row in results]
@classmethod
def create(cls, owner_id, name, domain='default'):
curated_group = cls(domain=domain, name=name, owner_id=owner_id)
db.session.add(curated_group)
std_commit()
return curated_group
@classmethod
def get_all_sids(cls, curated_group_id):
return CuratedGroupStudent.get_sids(curated_group_id=curated_group_id)
@classmethod
def add_student(cls, curated_group_id, sid):
curated_group = cls.query.filter_by(id=curated_group_id).first()
if curated_group:
CuratedGroupStudent.add_student(curated_group_id=curated_group_id, sid=sid)
_refresh_related_cohorts(curated_group)
@classmethod
def add_students(cls, curated_group_id, sids):
curated_group = cls.query.filter_by(id=curated_group_id).first()
if curated_group:
CuratedGroupStudent.add_students(curated_group_id=curated_group_id, sids=sids)
std_commit()
_refresh_related_cohorts(curated_group)
@classmethod
def remove_student(cls, curated_group_id, sid):
curated_group = cls.find_by_id(curated_group_id)
if curated_group:
CuratedGroupStudent.remove_student(curated_group_id, sid)
_refresh_related_cohorts(curated_group)
@classmethod
def rename(cls, curated_group_id, name):
curated_group = cls.query.filter_by(id=curated_group_id).first()
curated_group.name = name
std_commit()
@classmethod
def delete(cls, curated_group_id):
curated_group = cls.query.filter_by(id=curated_group_id).first()
if curated_group:
db.session.delete(curated_group)
std_commit()
# Delete all cohorts that reference the deleted group
for cohort_filter_id in curated_group.get_referencing_cohort_ids():
CohortFilter.delete(cohort_filter_id)
std_commit()
def get_referencing_cohort_ids(self):
query = text("""SELECT
c.id, c.filter_criteria
FROM cohort_filters c
WHERE filter_criteria->>'curatedGroupIds' IS NOT NULL AND owner_id = :user_id""")
results = db.session.execute(query, {'user_id': self.owner_id})
cohort_filter_ids = []
for row in results:
if self.id in row['filter_criteria'].get('curatedGroupIds', []):
cohort_filter_ids.append(row['id'])
return cohort_filter_ids
def to_api_json(self, include_students, order_by='last_name', offset=0, limit=50):
benchmark = get_benchmarker(f'CuratedGroup {self.id} to_api_json')
benchmark('begin')
sids = CuratedGroupStudent.get_sids(curated_group_id=self.id)
feed = {
'domain': self.domain,
'id': self.id,
'name': self.name,
'ownerId': self.owner_id,
'sids': sids,
'totalStudentCount': len(sids),
}
if include_students:
if sids:
if self.domain == 'admitted_students':
feed['students'] = get_admitted_students_by_sids(
limit=limit,
offset=offset,
order_by=order_by,
sids=sids,
)
else:
result = query_students(
sids=sids,
academic_career_status=('all'),
include_profiles=False,
order_by=order_by,
offset=offset,
limit=limit,
)
feed['students'] = result['students']
else:
feed['students'] = []
benchmark('end')
return feed
class CuratedGroup(Base):
__tablename__ = 'student_groups'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
owner_id = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False)
name = db.Column(db.String(255), nullable=False)
__table_args__ = (db.UniqueConstraint(
'owner_id',
'name',
name='student_groups_owner_id_name_unique_constraint',
),)
def __init__(self, name, owner_id):
self.name = name
self.owner_id = owner_id
@classmethod
def find_by_id(cls, curated_group_id):
return cls.query.filter_by(id=curated_group_id).first()
@classmethod
def get_curated_groups_by_owner_id(cls, owner_id):
return cls.query.filter_by(owner_id=owner_id).order_by(cls.name).all()
@classmethod
def curated_group_ids_per_sid(cls, user_id, sid):
query = text(f"""SELECT
student_group_id as id
FROM student_group_members m
JOIN student_groups g ON g.id = m.student_group_id
WHERE g.owner_id = :user_id AND m.sid = :sid""")
results = db.session.execute(query, {'user_id': user_id, 'sid': sid})
return [row['id'] for row in results]
@classmethod
def create(cls, owner_id, name):
curated_group = cls(name, owner_id)
db.session.add(curated_group)
std_commit()
return curated_group
@classmethod
def get_all_sids(cls, curated_group_id):
return CuratedGroupStudent.get_sids(curated_group_id=curated_group_id)
@classmethod
def add_student(cls, curated_group_id, sid):
curated_group = cls.query.filter_by(id=curated_group_id).first()
if curated_group:
CuratedGroupStudent.add_student(curated_group_id=curated_group_id, sid=sid)
@classmethod
def add_students(cls, curated_group_id, sids):
curated_group = cls.query.filter_by(id=curated_group_id).first()
if curated_group:
CuratedGroupStudent.add_students(curated_group_id=curated_group_id, sids=sids)
std_commit()
@classmethod
def remove_student(cls, curated_group_id, sid):
if cls.find_by_id(curated_group_id):
CuratedGroupStudent.remove_student(curated_group_id, sid)
@classmethod
def rename(cls, curated_group_id, name):
curated_group = cls.query.filter_by(id=curated_group_id).first()
curated_group.name = name
std_commit()
@classmethod
def delete(cls, curated_group_id):
curated_group = cls.query.filter_by(id=curated_group_id).first()
if curated_group:
db.session.delete(curated_group)
std_commit()
def to_api_json(self, order_by='last_name', offset=0, limit=50, include_students=True):
feed = {
'id': self.id,
'ownerId': self.owner_id,
'name': self.name,
}
if include_students:
sids = CuratedGroupStudent.get_sids(curated_group_id=self.id)
if sids:
result = query_students(sids=sids, order_by=order_by, offset=offset, limit=limit, include_profiles=False)
feed['students'] = result['students']
feed['studentCount'] = result['totalStudentCount']
else:
feed['students'] = []
feed['studentCount'] = 0
return feed
