class ToolSetting(Base):
__tablename__ = 'tool_settings'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
key = db.Column(db.String(255), nullable=False, unique=True)
value = db.Column(db.String(255), nullable=False)
def __init__(self, key, value=None):
self.key = key
self.value = value
def __repr__(self):
return f'<ToolSettings {self.key}, value={self.value}>'
@classmethod
def get_tool_setting(cls, key):
setting = cls.query.filter(cls.key == key).first()
return setting and setting.value
@classmethod
def upsert(cls, key, value):
tool_setting = cls.query.filter_by(key=key).first()
if tool_setting:
tool_setting.value = str(value)
else:
tool_setting = cls(key=key, value=str(value))
db.session.add(tool_setting)
std_commit()
return tool_setting
def to_api_json(self):
return {camelize(self.key): self.value}
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 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 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 Topic(db.Model):
__tablename__ = 'topics'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
topic = db.Column(db.String(255), nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
deleted_at = db.Column(db.DateTime, nullable=True)
def __init__(self, topic):
self.topic = topic
@classmethod
def get_all(cls, include_deleted=False):
return cls.query.order_by(
cls.topic).all() if include_deleted else cls.query.filter_by(
deleted_at=None).order_by(cls.topic).all()
@classmethod
def delete(cls, topic_id):
topic = cls.query.filter_by(id=topic_id, deleted_at=None).first()
if topic:
now = utc_now()
topic.deleted_at = now
std_commit()
@classmethod
def create_topic(cls, topic):
topic = cls(topic=topic)
db.session.add(topic)
std_commit()
return topic
def to_api_json(self):
return self.topic
class CohortFilterEvent(db.Model):
__tablename__ = 'cohort_filter_events'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
cohort_filter_id = db.Column(db.Integer, db.ForeignKey('cohort_filters.id'), nullable=False)
sid = db.Column(db.String(80), nullable=False)
event_type = db.Column(cohort_filter_event_type, nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
def __init__(self, cohort_filter_id, sid, event_type):
self.cohort_filter_id = cohort_filter_id
self.sid = sid
self.event_type = event_type
@classmethod
def create_bulk(cls, cohort_filter_id, added_sids=(), removed_sids=()):
events = [cls(cohort_filter_id=cohort_filter_id, sid=sid, event_type='added') for sid in added_sids]
events.extend([cls(cohort_filter_id=cohort_filter_id, sid=sid, event_type='removed') for sid in removed_sids])
db.session.bulk_save_objects(events)
std_commit()
@classmethod
def events_for_cohort(cls, cohort_filter_id, offset=0, limit=50):
count = db.session.query(func.count(cls.id)).filter_by(cohort_filter_id=cohort_filter_id).scalar()
events = cls.query.filter_by(cohort_filter_id=cohort_filter_id).order_by(desc(cls.created_at)).offset(offset).limit(limit).all()
return {
'count': count,
'events': events,
}
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 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 CuratedGroupStudent(db.Model):
__tablename__ = 'student_group_members'
curated_group_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)
def __init__(self, curated_group_id, sid):
self.curated_group_id = curated_group_id
self.sid = sid
@classmethod
def get_sids(cls, curated_group_id):
return [row.sid for row in cls.query.filter_by(curated_group_id=curated_group_id).all()]
@classmethod
def add_student(cls, curated_group_id, sid):
db.session.add(cls(curated_group_id, sid))
std_commit()
@classmethod
def add_students(cls, curated_group_id, sids):
existing_sids = [row.sid for row in cls.query.filter_by(curated_group_id=curated_group_id).all()]
for sid in set(sids).difference(existing_sids):
db.session.add(cls(curated_group_id, sid))
std_commit()
@classmethod
def remove_student(cls, curated_group_id, sid):
row = cls.query.filter_by(sid=sid, curated_group_id=curated_group_id).first()
if row:
db.session.delete(row)
std_commit()
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 Topic(db.Model):
__tablename__ = 'topics'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
topic = db.Column(db.String(255), nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
deleted_at = db.Column(db.DateTime, nullable=True)
available_in_notes = db.Column(db.Boolean, nullable=False)
available_in_appointments = db.Column(db.Boolean, nullable=False)
def __init__(self, topic, available_in_notes, available_in_appointments):
self.topic = topic
self.available_in_notes = available_in_notes
self.available_in_appointments = available_in_appointments
@classmethod
def get_all(cls,
available_in_notes=None,
available_in_appointments=None,
include_deleted=False):
kwargs = {}
if available_in_appointments is not None:
kwargs['available_in_appointments'] = available_in_appointments
if available_in_notes is not None:
kwargs['available_in_notes'] = available_in_notes
if not include_deleted:
kwargs['deleted_at'] = None
return cls.query.filter_by(**kwargs).order_by(cls.topic).all()
@classmethod
def delete(cls, topic_id):
topic = cls.query.filter_by(id=topic_id, deleted_at=None).first()
if topic:
now = utc_now()
topic.deleted_at = now
std_commit()
@classmethod
def create_topic(cls,
topic,
available_in_notes=False,
available_in_appointments=False):
topic = cls(topic=topic,
available_in_notes=available_in_notes,
available_in_appointments=available_in_appointments)
db.session.add(topic)
std_commit()
return topic
def to_api_json(self):
return self.topic
class AuthorizedUserExtension(Base):
__abstract__ = True
@declared_attr
def authorized_user_id(cls): # noqa: N805
return db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False,
primary_key=True)
@declared_attr
def authorized_user(cls): # noqa: N805
return db.relationship('AuthorizedUser',
back_populates=cls.authorized_user_relationship)
dept_code = db.Column(db.String(80), nullable=False, primary_key=True)
@classmethod
def find_by_dept_and_user(cls, dept_code, authorized_user_id):
return cls.query.filter_by(authorized_user_id=authorized_user_id,
dept_code=dept_code).first()
@classmethod
def get_all(cls, authorized_user_id):
return cls.query.filter_by(authorized_user_id=authorized_user_id).all()
@classmethod
def delete(cls, authorized_user_id, dept_code):
row = cls.query.filter_by(authorized_user_id=authorized_user_id,
dept_code=dept_code).first()
if not row:
return False
db.session.delete(row)
std_commit()
return True
@classmethod
def delete_orphans(cls):
sql = f"""
DELETE FROM {cls.__tablename__} AS a
WHERE a.authorized_user_id NOT IN (
SELECT m.authorized_user_id
FROM university_depts AS d
JOIN university_dept_members AS m
ON m.university_dept_id = d.id
WHERE d.dept_code = a.dept_code
);"""
db.session.execute(sql)
std_commit()
class DropInAdvisor(Advisor):
__tablename__ = 'drop_in_advisors'
authorized_user_relationship = 'drop_in_departments'
status = db.Column(db.String(255))
def update_status(self, status):
self.status = status
std_commit()
def to_api_json(self):
return {
'deptCode': self.dept_code,
'available': self.is_available,
'status': self.status,
}
class UserLogin(db.Model):
__tablename__ = 'user_logins'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
uid = db.Column(db.String(255),
db.ForeignKey('authorized_users.uid'),
nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
def __init__(self, uid):
self.uid = uid
@classmethod
def record_user_login(cls, uid):
user_login = cls(uid=uid)
db.session.add(user_login)
std_commit()
return user_login
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 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 ManuallyAddedAdvisee(db.Model):
__tablename__ = 'manually_added_advisees'
sid = db.Column(db.String(80), nullable=False, primary_key=True)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
def __init__(self, sid):
self.sid = sid
@classmethod
def find_or_create(cls, sid):
manually_added_advisee = cls.query.filter(cls.sid == sid).one_or_none()
if not manually_added_advisee:
manually_added_advisee = cls(sid)
db.session.add(manually_added_advisee)
std_commit()
return manually_added_advisee
@classmethod
def get_all(cls):
return cls.query.all()
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 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 TeamMember(Base):
__tablename__ = 'team_members'
id = db.Column(db.Integer, nullable=False, primary_key=True)
code = db.Column(db.String(255), nullable=False)
member_uid = db.Column(db.String(80))
member_csid = db.Column(db.String(80), nullable=False)
member_name = db.Column(db.String(255))
asc_sport_code_core = db.Column(db.String(80))
asc_sport_code = db.Column(db.String(80))
asc_sport = db.Column(db.String(80))
asc_sport_core = db.Column(db.String(80))
UniqueConstraint('code', 'member_csid', name='team_member')
def __repr__(self):
return '<TeamMember {} ({}), asc_sport {} ({}), asc_sport_core {} ({}), uid={}, csid={}, name={}, updated={}, created={}>'.format(
self.team_definitions.get(self.code),
self.code,
self.asc_sport,
self.asc_sport_code,
self.asc_sport_core,
self.asc_sport_code_core,
self.member_uid,
self.member_csid,
self.member_name,
self.updated_at,
self.created_at,
)
team_definitions = {
'BAM': 'Baseball - Men',
'BBM': 'Basketball - Men',
'BBW': 'Basketball - Women',
'CCM': 'Cross Country - Men',
'CCW': 'Cross Country - Women',
'CRM': 'Crew - Men',
'CRW': 'Crew - Women',
'EMX': 'Equipment Managers',
'FBM': 'Football - Men',
'FHW': 'Field Hockey - Women',
'GOM': 'Golf - Men',
'GOW': 'Golf - Women',
'GYM': 'Gymnastics - Men',
'GYW': 'Gymnastics - Women',
'LCW': 'Lacrosse - Women',
'RGM': 'Rugby - Men',
'SBW': 'Softball - Women',
'SCM': 'Soccer - Men',
'SCW': 'Soccer - Women',
'SDM': 'Swimming & Diving - Men',
'SDW': 'Swimming & Diving - Women',
'STX': 'Student Trainers',
'SVW': 'Sand Volleyball - Women',
'TIM': 'Indoor Track & Field - Men',
'TIW': 'Indoor Track & Field - Women',
'TNM': 'Tennis - Men',
'TNW': 'Tennis - Women',
'TOM': 'Outdoor Track & Field - Men',
'TOW': 'Outdoor Track & Field - Women',
'VBW': 'Volleyball - Women',
'WPM': 'Water Polo - Men',
'WPW': 'Water Polo - Women',
}
@classmethod
def all_teams(cls, sort_by='name'):
results = db.session.query(cls.code, func.count(
cls.member_uid)).group_by(cls.code).all()
def translate_row(row):
return {
'code': row[0],
'totalMemberCount': row[1],
'name': cls.team_definitions.get(row[0], row[0]),
}
teams = [translate_row(row) for row in results]
return sorted(teams, key=lambda team: team[sort_by])
@classmethod
def all_athletes(cls, sort_by=None):
athletes = cls.query.order_by(cls.member_name).all()
athletes = [TeamMember.translate_row(athlete) for athlete in athletes]
if sort_by and len(athletes) > 0:
is_valid_key = sort_by in athletes[0]
athletes = sorted(athletes, key=lambda athlete: athlete[sort_by]
) if is_valid_key else athletes
return athletes
@classmethod
def for_code(cls, code, order_by='member_name', offset=0, limit=50):
members = cls.query.filter_by(
code=code).order_by(order_by).offset(offset).limit(limit).all()
return {
'code': code,
'members': [member.to_api_json() for member in members],
'name': cls.team_definitions.get(code, code),
}
@classmethod
def translate_row(cls, athlete):
return {
'id': athlete.id,
'name': athlete.member_name,
'sid': athlete.member_csid,
'sport': athlete.asc_sport,
'teamCode': athlete.code,
'uid': athlete.member_uid,
}
@classmethod
def summarize_team_members(cls,
team_codes,
order_by='member_name',
offset=0,
limit=50):
summary = {
'teams': [],
}
for code in team_codes:
team = TeamMember.for_code(code)
summary['teams'].append({
'code': code,
'name': team['name'],
})
o = TeamMember.member_uid if order_by == 'member_uid' else TeamMember.member_name
f = TeamMember.code.in_(team_codes)
results = TeamMember.query.distinct(o).order_by(o).filter(f).offset(
offset).limit(limit).all()
summary['members'] = []
for row in results:
summary['members'].append(TeamMember.translate_row(row))
summary['totalMemberCount'] = TeamMember.query.distinct(o).filter(
f).count()
db.session.commit()
return summary
def to_api_json(self):
return {
'name': self.member_name,
'uid': self.member_uid,
}
class AppointmentEvent(db.Model):
__tablename__ = 'appointment_events'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
appointment_id = db.Column(db.Integer,
db.ForeignKey('appointments.id'),
nullable=False,
primary_key=True)
user_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False,
primary_key=True)
event_type = db.Column(appointment_event_type, nullable=False)
cancel_reason = db.Column(db.String(255), nullable=True)
cancel_reason_explained = db.Column(db.String(255), nullable=True)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
def __init__(
self,
appointment_id,
user_id,
event_type,
cancel_reason=None,
cancel_reason_explained=None,
):
self.appointment_id = appointment_id
self.event_type = event_type
self.cancel_reason = cancel_reason
self.cancel_reason_explained = cancel_reason_explained
self.user_id = user_id
@classmethod
def create(
cls,
appointment_id,
user_id,
event_type,
cancel_reason=None,
cancel_reason_explained=None,
):
db.session.add(
cls(
appointment_id=appointment_id,
user_id=user_id,
event_type=event_type,
cancel_reason=cancel_reason,
cancel_reason_explained=cancel_reason_explained,
), )
std_commit()
@classmethod
def get_most_recent_per_type(cls, appointment_id, event_type):
return cls.query.filter(
cls.appointment_id == appointment_id,
cls.event_type == event_type,
).order_by(desc(cls.created_at)).limit(1).first()
def to_api_json(self):
return {
'appointmentId': self.appointment_id,
'cancelReason': self.cancel_reason,
'cancelReasonExplained': self.cancel_reason_explained,
'createdAt': _isoformat(self.created_at),
'userId': self.authorized_users_id,
}
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 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
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 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 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 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