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 UniversityDeptMember(Base):
__tablename__ = 'university_dept_members'
university_dept_id = db.Column(db.Integer,
db.ForeignKey('university_depts.id'),
primary_key=True)
authorized_user_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
primary_key=True)
is_advisor = db.Column(db.Boolean, nullable=False)
is_director = db.Column(db.Boolean, nullable=False)
authorized_user = db.relationship('AuthorizedUser',
back_populates='department_memberships')
# Pre-load UniversityDept below to avoid 'failed to locate', as seen during routes.py init phase
university_dept = db.relationship(UniversityDept.__name__,
back_populates='authorized_users')
@classmethod
def create_membership(cls, university_dept, authorized_user, is_advisor,
is_director):
if not len(authorized_user.department_memberships):
mapping = cls(is_advisor=is_advisor, is_director=is_director)
mapping.authorized_user = authorized_user
mapping.university_dept = university_dept
authorized_user.department_memberships.append(mapping)
university_dept.authorized_users.append(mapping)
db.session.add(mapping)
std_commit()
class DegreeProgressCategoryUnitRequirement(db.Model):
__tablename__ = 'degree_progress_category_unit_requirements'
category_id = db.Column(db.Integer, db.ForeignKey('degree_progress_categories.id'), primary_key=True)
unit_requirement_id = db.Column(db.Integer, db.ForeignKey('degree_progress_unit_requirements.id'), primary_key=True)
category = db.relationship('DegreeProgressCategory', back_populates='unit_requirements')
unit_requirement = db.relationship('DegreeProgressUnitRequirement', back_populates='categories')
def __init__(
self,
category_id,
unit_requirement_id,
):
self.category_id = category_id
self.unit_requirement_id = unit_requirement_id
@classmethod
def create(cls, category_id, unit_requirement_id):
db.session.add(cls(category_id=category_id, unit_requirement_id=unit_requirement_id))
std_commit()
@classmethod
def delete_mappings(cls, unit_requirement_id):
for mapping in cls.query.filter_by(unit_requirement_id=unit_requirement_id).all():
db.session.delete(mapping)
std_commit()
@classmethod
def find_by_category_id(cls, category_id):
return cls.query.filter_by(category_id=category_id).all()
class AppointmentRead(db.Model):
__tablename__ = 'appointments_read'
viewer_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False,
primary_key=True)
appointment_id = db.Column(db.Integer,
db.ForeignKey('appointments.id'),
nullable=False,
primary_key=True)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
__table_args__ = (db.UniqueConstraint(
'viewer_id',
'appointment_id',
name='appointments_read_viewer_id_appointment_id_unique_constraint',
), )
def __init__(self, viewer_id, appointment_id):
self.viewer_id = viewer_id
self.appointment_id = appointment_id
@classmethod
def find_or_create(cls, viewer_id, appointment_id):
appointment_read = cls.query.filter(
and_(cls.viewer_id == viewer_id,
cls.appointment_id == str(appointment_id))).one_or_none()
if not appointment_read:
appointment_read = cls(viewer_id, appointment_id)
db.session.add(appointment_read)
std_commit()
return appointment_read
@classmethod
def was_read_by(cls, viewer_id, appointment_id):
appointment_read = cls.query.filter(
AppointmentRead.viewer_id == viewer_id,
AppointmentRead.appointment_id == appointment_id,
).first()
return appointment_read is not None
@classmethod
def when_user_read_appointment(cls, viewer_id, appointment_id):
appointment_read = cls.query.filter(
AppointmentRead.viewer_id == viewer_id,
AppointmentRead.appointment_id == appointment_id,
).first()
return appointment_read and appointment_read.created_at
def to_api_json(self):
return {
'appointmentId': self.appointment_id,
'createdAt': _isoformat(self.created_at),
'viewerId': self.viewer_id,
}
class DegreeProgressNote(Base):
__tablename__ = 'degree_progress_notes'
body = db.Column(db.Text, nullable=False)
template_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_templates.id'),
nullable=False,
primary_key=True)
updated_by = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
template = db.relationship('DegreeProgressTemplate', back_populates='note')
def __init__(self, body, template_id, updated_by):
self.body = body
self.template_id = template_id
self.updated_by = updated_by
def __repr__(self):
return f"""<DegreeProgressNote template_id={self.template_id},
body={self.body},
created_at={self.created_at},
updated_at={self.updated_at},
updated_by={self.updated_by}>"""
@classmethod
def upsert(
cls,
body,
template_id,
updated_by,
):
note = cls.query.filter_by(template_id=template_id).first()
if note:
note.body = body
note.updated_by = updated_by
else:
note = cls(
body=body,
template_id=template_id,
updated_by=updated_by,
)
db.session.add(note)
std_commit()
return note
def to_api_json(self):
return {
'body': self.body,
'createdAt': _isoformat(self.created_at),
'templateId': self.template_id,
'updatedAt': _isoformat(self.updated_at),
'updatedBy': self.updated_by,
}
class AlertView(db.Model):
__tablename__ = 'alert_views'
alert_id = db.Column(db.Integer,
db.ForeignKey('alerts.id'),
primary_key=True)
viewer_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
primary_key=True)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
dismissed_at = db.Column(db.DateTime)
viewer = db.relationship('AuthorizedUser', back_populates='alert_views')
alert = db.relationship('Alert', back_populates='views')
class 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 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 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 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 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 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 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 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 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 CuratedCohort(Base):
__tablename__ = 'student_groups'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
owner_id = db.Column(db.String(80), db.ForeignKey('authorized_users.id'), nullable=False)
name = db.Column(db.String(255), nullable=False)
students = db.relationship('CuratedCohortStudent', back_populates='curated_cohort', cascade='all')
__table_args__ = (db.UniqueConstraint(
'owner_id',
'name',
name='student_groups_owner_id_name_unique_constraint',
),)
def __init__(self, name, owner_id):
self.name = name
self.owner_id = owner_id
@classmethod
def find_by_id(cls, curated_cohort_id):
return cls.query.filter_by(id=curated_cohort_id).first()
@classmethod
def get_curated_cohorts_by_owner_id(cls, owner_id):
return cls.query.filter_by(owner_id=owner_id).order_by(cls.name).all()
@classmethod
def create(cls, owner_id, name):
curated_cohort = cls(name, owner_id)
db.session.add(curated_cohort)
std_commit()
return curated_cohort
@classmethod
def add_student(cls, curated_cohort_id, sid):
curated_cohort = cls.query.filter_by(id=curated_cohort_id).first()
if curated_cohort:
try:
membership = CuratedCohortStudent(sid=sid, curated_cohort_id=curated_cohort_id)
db.session.add(membership)
std_commit()
except (FlushError, IntegrityError):
app.logger.warn(f'Database error in add_student with curated_cohort_id={curated_cohort_id}, sid {sid}')
return curated_cohort
@classmethod
def add_students(cls, curated_cohort_id, sids):
curated_cohort = cls.query.filter_by(id=curated_cohort_id).first()
if curated_cohort:
try:
for sid in set(sids):
membership = CuratedCohortStudent(sid=sid, curated_cohort_id=curated_cohort_id)
db.session.add(membership)
std_commit()
except (FlushError, IntegrityError):
app.logger.warn(f'Database error in add_students with curated_cohort_id={curated_cohort_id}, sid {sid}')
return curated_cohort
@classmethod
def remove_student(cls, curated_cohort_id, sid):
curated_cohort = cls.find_by_id(curated_cohort_id)
membership = CuratedCohortStudent.query.filter_by(sid=sid, curated_cohort_id=curated_cohort_id).first()
if curated_cohort and membership:
db.session.delete(membership)
std_commit()
@classmethod
def rename(cls, curated_cohort_id, name):
curated_cohort = cls.query.filter_by(id=curated_cohort_id).first()
curated_cohort.name = name
std_commit()
return curated_cohort
@classmethod
def delete(cls, curated_cohort_id):
curated_cohort = cls.query.filter_by(id=curated_cohort_id).first()
if curated_cohort:
db.session.delete(curated_cohort)
std_commit()
def to_api_json(self, sids_only=False, include_students=True):
api_json = {
'id': self.id,
'ownerId': self.owner_id,
'name': self.name,
'studentCount': len(self.students),
}
if sids_only:
api_json['students'] = [{'sid': s.sid} for s in self.students]
elif include_students:
api_json['students'] = get_api_json([s.sid for s in self.students])
return api_json
def authorized_user_id(cls): # noqa: N805
return db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False,
primary_key=True)
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(db.String(255), nullable=True)
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)
status = db.Column(appointment_event_type, nullable=False)
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',
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,
):
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.status = status
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 find_advisors_by_name(cls, tokens, limit=None):
benchmark = get_benchmarker('appointments find_advisors_by_name')
benchmark('begin')
token_conditions = []
params = {}
for idx, token in enumerate(tokens):
token_conditions.append(
f"""JOIN appointments a{idx}
ON UPPER(a{idx}.advisor_name) LIKE :token_{idx}
AND a{idx}.advisor_uid = a.advisor_uid""", )
params[f'token_{idx}'] = f'%{token}%'
sql = f"""SELECT DISTINCT a.advisor_name, a.advisor_uid
FROM appointments a
{' '.join(token_conditions)}
ORDER BY a.advisor_name"""
if limit:
sql += f' LIMIT {limit}'
benchmark('execute query')
results = db.session.execute(sql, params)
benchmark('end')
return results
@classmethod
def get_appointments_per_sid(cls, sid):
return cls.query.filter(
and_(cls.student_sid == sid,
cls.deleted_at == None)).all() # noqa: E711
@classmethod
def get_waitlist(cls, dept_code, statuses=()):
start_of_today = datetime.now().replace(hour=0,
minute=0,
second=0,
microsecond=0)
criterion = and_(
cls.created_at >= start_of_today.astimezone(pytz.utc),
cls.status.in_(statuses),
cls.deleted_at == None,
cls.dept_code == dept_code,
) # noqa: E711
return cls.query.filter(criterion).order_by(desc(cls.created_at)).all()
@classmethod
def create(
cls,
created_by,
dept_code,
details,
appointment_type,
student_sid,
advisor_uid=None,
topics=(),
):
if advisor_uid:
status = 'reserved'
status_by = AuthorizedUser.get_id_per_uid(advisor_uid)
else:
status = 'waiting'
status_by = created_by
appointment = cls(
advisor_uid=advisor_uid,
appointment_type=appointment_type,
created_by=created_by,
dept_code=dept_code,
details=details,
status=status,
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,
user_id=status_by,
event_type=status,
)
cls.refresh_search_index()
return appointment
@classmethod
def check_in(cls, appointment_id, checked_in_by, advisor_uid, advisor_name,
advisor_role, advisor_dept_codes):
appointment = cls.find_by_id(appointment_id=appointment_id)
if appointment:
appointment.status = 'checked_in'
appointment.advisor_uid = advisor_uid
appointment.advisor_name = advisor_name
appointment.advisor_role = advisor_role
appointment.advisor_dept_codes = advisor_dept_codes
appointment.updated_by = checked_in_by
std_commit()
db.session.refresh(appointment)
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=checked_in_by,
event_type='checked_in',
)
return appointment
else:
return None
@classmethod
def cancel(cls, appointment_id, canceled_by, cancel_reason,
cancel_reason_explained):
appointment = cls.find_by_id(appointment_id=appointment_id)
if appointment:
event_type = 'canceled'
appointment.status = event_type
appointment.updated_by = canceled_by
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=canceled_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):
appointment = cls.find_by_id(appointment_id=appointment_id)
if appointment:
event_type = 'reserved'
appointment.status = event_type
appointment.updated_by = reserved_by
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=reserved_by,
event_type=event_type,
)
std_commit()
db.session.refresh(appointment)
return appointment
else:
return None
@classmethod
def unreserve(cls, appointment_id, unreserved_by):
appointment = cls.find_by_id(appointment_id=appointment_id)
if appointment:
event_type = 'waiting'
appointment.status = event_type
appointment.updated_by = unreserved_by
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=unreserved_by,
event_type=event_type,
)
std_commit()
db.session.refresh(appointment)
return appointment
else:
return None
@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(APPOINTMENT_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()
response = [
_to_json(search_terms, dict(zip(keys, row)))
for row in result.fetchall()
]
return response
@classmethod
def refresh_search_index(cls):
db.session.execute(
text('REFRESH MATERIALIZED VIEW appointments_fts_index'))
std_commit()
@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 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,
'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,
}
return {
**api_json,
**_appointment_event_to_json(self.id, self.status),
}
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 Appointment(Base):
__tablename__ = 'appointments'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
advisor_dept_codes = db.Column(ARRAY(db.String), nullable=True)
advisor_name = db.Column(db.String(255), nullable=True)
advisor_role = db.Column(db.String(255), nullable=True)
advisor_uid = db.Column(db.String(255), nullable=True)
appointment_type = db.Column(appointment_type_enum, nullable=False)
created_by = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False)
deleted_at = db.Column(db.DateTime, nullable=True)
deleted_by = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=True)
dept_code = db.Column(db.String(80), nullable=False)
details = db.Column(db.Text, nullable=True)
scheduled_time = db.Column(db.DateTime, nullable=True)
status = db.Column(appointment_event_type, nullable=False)
student_contact_info = db.Column(db.String(255), nullable=True)
student_contact_type = db.Column(appointment_student_contact_type_enum, nullable=True)
student_sid = db.Column(db.String(80), nullable=False)
updated_by = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=True)
topics = db.relationship(
'AppointmentTopic',
primaryjoin='and_(Appointment.id==AppointmentTopic.appointment_id, AppointmentTopic.deleted_at==None)',
back_populates='appointment',
order_by='AppointmentTopic.topic',
lazy=True,
)
def __init__(
self,
appointment_type,
created_by,
dept_code,
details,
status,
student_sid,
updated_by,
advisor_dept_codes=None,
advisor_name=None,
advisor_role=None,
advisor_uid=None,
scheduled_time=None,
student_contact_info=None,
student_contact_type=None,
):
self.advisor_dept_codes = advisor_dept_codes
self.advisor_name = advisor_name
self.advisor_role = advisor_role
self.advisor_uid = advisor_uid
self.appointment_type = appointment_type
self.created_by = created_by
self.dept_code = dept_code
self.details = details
self.scheduled_time = scheduled_time
self.status = status
self.student_contact_info = student_contact_info
self.student_contact_type = student_contact_type
self.student_sid = student_sid
self.updated_by = updated_by
@classmethod
def find_by_id(cls, appointment_id):
return cls.query.filter(and_(cls.id == appointment_id, cls.deleted_at == None)).first() # noqa: E711
@classmethod
def get_appointments_per_sid(cls, sid):
return cls.query.filter(and_(cls.student_sid == sid, cls.deleted_at == None)).order_by(cls.updated_at, cls.id).all() # noqa: E711
@classmethod
def get_drop_in_waitlist(cls, dept_code, statuses=()):
local_today = localize_datetime(datetime.now()).strftime('%Y-%m-%d')
start_of_today = localized_timestamp_to_utc(f'{local_today}T00:00:00')
criterion = and_(
cls.created_at >= start_of_today,
cls.appointment_type == 'Drop-in',
cls.status.in_(statuses),
cls.deleted_at == None, # noqa: E711
cls.dept_code == dept_code,
)
return cls.query.filter(criterion).order_by(cls.created_at).all()
@classmethod
def get_scheduled(cls, dept_code, local_date, advisor_uid=None):
date_str = local_date.strftime('%Y-%m-%d')
start_of_today = localized_timestamp_to_utc(f'{date_str}T00:00:00')
end_of_today = localized_timestamp_to_utc(f'{date_str}T23:59:59')
query = cls.query.filter(
and_(
cls.scheduled_time >= start_of_today,
cls.scheduled_time <= end_of_today,
cls.appointment_type == 'Scheduled',
cls.deleted_at == None, # noqa: E711
cls.dept_code == dept_code,
),
)
if advisor_uid:
query = query.filter(cls.advisor_uid == advisor_uid)
return query.order_by(cls.scheduled_time).all()
@classmethod
def create(
cls,
created_by,
dept_code,
details,
appointment_type,
student_sid,
advisor_attrs=None,
topics=(),
scheduled_time=None,
student_contact_info=None,
student_contact_type=None,
):
# If this appointment comes in already assigned to the intake desk, we treat it as resolved.
if advisor_attrs and advisor_attrs['role'] == 'Intake Desk':
status = 'checked_in'
elif advisor_attrs:
status = 'reserved'
else:
status = 'waiting'
appointment = cls(
advisor_uid=advisor_attrs and advisor_attrs['uid'],
advisor_name=advisor_attrs and advisor_attrs['name'],
advisor_role=advisor_attrs and advisor_attrs['role'],
advisor_dept_codes=advisor_attrs and advisor_attrs['deptCodes'],
appointment_type=appointment_type,
created_by=created_by,
dept_code=dept_code,
details=details,
scheduled_time=scheduled_time,
status=status,
student_contact_info=student_contact_info,
student_contact_type=student_contact_type,
student_sid=student_sid,
updated_by=created_by,
)
for topic in topics:
appointment.topics.append(
AppointmentTopic.create(appointment, topic),
)
db.session.add(appointment)
std_commit()
AppointmentEvent.create(
appointment_id=appointment.id,
advisor_id=advisor_attrs and advisor_attrs['id'],
user_id=created_by,
event_type=status,
)
cls.refresh_search_index()
return appointment
@classmethod
def check_in(cls, appointment_id, checked_in_by, advisor_attrs):
appointment = cls.find_by_id(appointment_id=appointment_id)
if appointment:
appointment.status = 'checked_in'
appointment.advisor_uid = advisor_attrs['uid']
appointment.advisor_name = advisor_attrs['name']
appointment.advisor_role = advisor_attrs['role']
appointment.advisor_dept_codes = advisor_attrs['deptCodes']
appointment.updated_by = checked_in_by
std_commit()
db.session.refresh(appointment)
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=checked_in_by,
advisor_id=advisor_attrs['id'],
event_type='checked_in',
)
return appointment
else:
return None
@classmethod
def cancel(cls, appointment_id, cancelled_by, cancel_reason, cancel_reason_explained):
appointment = cls.find_by_id(appointment_id=appointment_id)
if appointment:
event_type = 'cancelled'
appointment.status = event_type
appointment.updated_by = cancelled_by
appointment.advisor_uid = None
appointment.advisor_name = None
appointment.advisor_role = None
appointment.advisor_dept_codes = None
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=cancelled_by,
event_type=event_type,
cancel_reason=cancel_reason,
cancel_reason_explained=cancel_reason_explained,
)
std_commit()
db.session.refresh(appointment)
cls.refresh_search_index()
return appointment
else:
return None
@classmethod
def reserve(cls, appointment_id, reserved_by, advisor_attrs):
appointment = cls.find_by_id(appointment_id=appointment_id)
if appointment:
event_type = 'reserved'
appointment.status = event_type
appointment.updated_by = reserved_by
appointment.advisor_uid = advisor_attrs['uid']
appointment.advisor_name = advisor_attrs['name']
appointment.advisor_role = advisor_attrs['role']
appointment.advisor_dept_codes = advisor_attrs['deptCodes']
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=reserved_by,
advisor_id=advisor_attrs['id'],
event_type=event_type,
)
std_commit()
db.session.refresh(appointment)
return appointment
else:
return None
def set_to_waiting(self, updated_by):
event_type = 'waiting'
self.status = event_type
self.updated_by = updated_by
self.advisor_uid = None
self.advisor_name = None
self.advisor_role = None
self.advisor_dept_codes = None
AppointmentEvent.create(
appointment_id=self.id,
user_id=updated_by,
event_type=event_type,
)
std_commit()
db.session.refresh(self)
@classmethod
def unreserve_all_for_advisor(cls, advisor_uid, updated_by):
appointments = cls.query.filter(and_(cls.status == 'reserved', cls.advisor_uid == advisor_uid, cls.deleted_at == None)).all() # noqa: E711
event_type = 'waiting'
for appointment in appointments:
appointment.status = event_type
appointment.advisor_uid = None
appointment.advisor_name = None
appointment.advisor_role = None
appointment.advisor_dept_codes = None
appointment.updated_by = updated_by
AppointmentEvent.create(
appointment_id=appointment.id,
user_id=updated_by,
event_type=event_type,
)
std_commit()
@classmethod
def search(
cls,
search_phrase,
advisor_uid=None,
student_csid=None,
topic=None,
datetime_from=None,
datetime_to=None,
limit=20,
offset=0,
):
if search_phrase:
search_terms = [t.group(0) for t in list(re.finditer(TEXT_SEARCH_PATTERN, search_phrase)) if t]
search_phrase = ' & '.join(search_terms)
fts_selector = """SELECT id, ts_rank(fts_index, plainto_tsquery('english', :search_phrase)) AS rank
FROM appointments_fts_index
WHERE fts_index @@ plainto_tsquery('english', :search_phrase)"""
params = {
'search_phrase': search_phrase,
}
else:
search_terms = []
fts_selector = 'SELECT id, 0 AS rank FROM appointments WHERE deleted_at IS NULL'
params = {}
if advisor_uid:
advisor_filter = 'AND appointments.advisor_uid = :advisor_uid'
params.update({'advisor_uid': advisor_uid})
else:
advisor_filter = ''
if student_csid:
student_filter = 'AND appointments.student_sid = :student_csid'
params.update({'student_csid': student_csid})
else:
student_filter = ''
date_filter = ''
if datetime_from:
date_filter += ' AND created_at >= :datetime_from'
params.update({'datetime_from': datetime_from})
if datetime_to:
date_filter += ' AND created_at < :datetime_to'
params.update({'datetime_to': datetime_to})
if topic:
topic_join = 'JOIN appointment_topics nt on nt.topic = :topic AND nt.appointment_id = appointments.id'
params.update({'topic': topic})
else:
topic_join = ''
query = text(f"""
SELECT appointments.* FROM ({fts_selector}) AS fts
JOIN appointments
ON fts.id = appointments.id
{advisor_filter}
{student_filter}
{date_filter}
{topic_join}
ORDER BY fts.rank DESC, appointments.id
LIMIT {limit} OFFSET {offset}
""").bindparams(**params)
result = db.session.execute(query)
keys = result.keys()
return [_to_json(search_terms, dict(zip(keys, row))) for row in result.fetchall()]
def update(
self,
updated_by,
details=None,
scheduled_time=None,
student_contact_info=None,
student_contact_type=None,
topics=(),
):
if details != self.details:
self.updated_at = utc_now()
self.updated_by = updated_by
self.details = details
self.scheduled_time = scheduled_time
self.student_contact_info = student_contact_info
self.student_contact_type = student_contact_type
_update_appointment_topics(self, topics, updated_by)
std_commit()
db.session.refresh(self)
self.refresh_search_index()
@classmethod
def refresh_search_index(cls):
def _refresh_search_index(db_session):
db_session.execute(text('REFRESH MATERIALIZED VIEW appointments_fts_index'))
db_session.execute(text('REFRESH MATERIALIZED VIEW advisor_author_index'))
std_commit(session=db_session)
bg_execute(_refresh_search_index)
@classmethod
def delete(cls, appointment_id):
appointment = cls.find_by_id(appointment_id)
if appointment:
now = utc_now()
appointment.deleted_at = now
for topic in appointment.topics:
topic.deleted_at = now
std_commit()
cls.refresh_search_index()
def status_change_available(self):
return self.status in ['reserved', 'waiting']
def to_api_json(self, current_user_id):
topics = [t.to_api_json() for t in self.topics if not t.deleted_at]
departments = None
if self.advisor_dept_codes:
departments = [{'code': c, 'name': BERKELEY_DEPT_CODE_TO_NAME.get(c, c)} for c in self.advisor_dept_codes]
api_json = {
'id': self.id,
'advisorId': AuthorizedUser.get_id_per_uid(self.advisor_uid),
'advisorName': self.advisor_name,
'advisorRole': self.advisor_role,
'advisorUid': self.advisor_uid,
'advisorDepartments': departments,
'appointmentType': self.appointment_type,
'createdAt': _isoformat(self.created_at),
'createdBy': self.created_by,
'deptCode': self.dept_code,
'details': self.details,
'read': AppointmentRead.was_read_by(current_user_id, self.id),
'student': {
'sid': self.student_sid,
},
'topics': topics,
'updatedAt': _isoformat(self.updated_at),
'updatedBy': self.updated_by,
}
if self.appointment_type == 'Scheduled':
api_json.update({
'scheduledTime': _isoformat(self.scheduled_time),
'studentContactInfo': self.student_contact_info,
'studentContactType': self.student_contact_type,
})
return {
**api_json,
**appointment_event_to_json(self.id, self.status),
}
class UniversityDeptMember(Base):
__tablename__ = 'university_dept_members'
university_dept_id = db.Column(db.Integer,
db.ForeignKey('university_depts.id'),
primary_key=True)
authorized_user_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
primary_key=True)
role = db.Column(university_dept_member_role_type, nullable=True)
automate_membership = db.Column(db.Boolean, nullable=False)
authorized_user = db.relationship('AuthorizedUser',
back_populates='department_memberships')
# Pre-load UniversityDept below to avoid 'failed to locate', as seen during routes.py init phase
university_dept = db.relationship(UniversityDept.__name__,
back_populates='authorized_users')
def __init__(
self,
university_dept_id,
authorized_user_id,
role,
automate_membership=True,
):
self.university_dept_id = university_dept_id
self.authorized_user_id = authorized_user_id
self.role = role
self.automate_membership = automate_membership
@classmethod
def create_or_update_membership(
cls,
university_dept_id,
authorized_user_id,
role=None,
automate_membership=True,
):
existing_membership = cls.query.filter_by(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id,
).first()
if existing_membership:
membership = existing_membership
membership.role = role
membership.automate_membership = automate_membership
else:
membership = cls(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id,
role=role,
automate_membership=automate_membership,
)
db.session.add(membership)
std_commit()
return membership
@classmethod
def get_existing_memberships(cls, authorized_user_id):
return cls.query.filter_by(authorized_user_id=authorized_user_id).all()
@classmethod
def update_membership(
cls,
university_dept_id,
authorized_user_id,
role,
automate_membership,
):
membership = cls.query.filter_by(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id).first()
if membership:
membership.role = membership.role if role is None else role
membership.automate_membership = membership.automate_membership if automate_membership is None else automate_membership
std_commit()
return membership
return None
@classmethod
def get_distinct_departments(
cls,
authorized_user_id=None,
role=None,
):
sql = """
SELECT DISTINCT dept_code FROM university_depts d
JOIN university_dept_members m ON m.university_dept_id = d.id
WHERE TRUE
"""
if authorized_user_id:
sql += ' AND m.authorized_user_id = :authorized_user_id'
else:
sql += ' AND d.id IN (SELECT DISTINCT university_dept_id FROM university_depts)'
if role is not None:
sql += f" AND m.role = '{role}'"
return [
row['dept_code'] for row in db.session.execute(
sql, {'authorized_user_id': authorized_user_id})
]
@classmethod
def delete_membership(cls, university_dept_id, authorized_user_id):
membership = cls.query.filter_by(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id).first()
if not membership:
return False
db.session.delete(membership)
std_commit()
return True
def to_api_json(self):
return {
'universityDeptId': self.university_dept_id,
'authorizedUserId': self.authorized_user_id,
'role': self.role,
'automateMembership': self.automate_membership,
}
"AS IS". REGENTS HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
ENHANCEMENTS, OR MODIFICATIONS.
"""
from datetime import datetime
from boac import db, std_commit
from boac.models.base import Base
from boac.models.university_dept import UniversityDept
cohort_filter_owners = db.Table(
'cohort_filter_owners',
Base.metadata,
db.Column('cohort_filter_id',
db.Integer,
db.ForeignKey('cohort_filters.id'),
primary_key=True),
db.Column('user_id',
db.Integer,
db.ForeignKey('authorized_users.id'),
primary_key=True),
)
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)
class CohortFilter(Base):
__tablename__ = 'cohort_filters'
__transient_sids = []
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
domain = db.Column(cohort_domain_type, nullable=False)
owner_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
name = db.Column(db.String(255), nullable=False)
filter_criteria = db.Column(JSONB, nullable=False)
# Fetching a large array literal from Postgres can be expensive. We defer until invoking code demands it.
sids = deferred(db.Column(ARRAY(db.String(80))))
student_count = db.Column(db.Integer)
alert_count = db.Column(db.Integer)
owner = db.relationship('AuthorizedUser', back_populates='cohort_filters')
def __init__(self, domain, name, filter_criteria):
self.domain = domain
self.name = name
self.filter_criteria = filter_criteria
def __repr__(self):
return f"""<CohortFilter {self.id},
domain={self.domain},
name={self.name},
owner_id={self.owner_id},
filter_criteria={self.filter_criteria},
sids={self.sids},
student_count={self.student_count},
alert_count={self.alert_count},
updated_at={self.updated_at},
created_at={self.created_at}>"""
@classmethod
def create(cls, uid, name, filter_criteria, domain='default', **kwargs):
if all(not isinstance(value, bool) and not value
for value in filter_criteria.values()):
raise InternalServerError(
'Cohort creation requires at least one filter specification.')
cohort = cls(domain=domain, name=name, filter_criteria=filter_criteria)
user = AuthorizedUser.find_by_uid(uid)
user.cohort_filters.append(cohort)
db.session.flush()
std_commit()
return cohort.to_api_json(**kwargs)
@classmethod
def update(cls,
cohort_id,
name=None,
filter_criteria=None,
alert_count=None,
**kwargs):
cohort = cls.query.filter_by(id=cohort_id).first()
if name:
cohort.name = name
if filter_criteria:
cohort.filter_criteria = filter_criteria
cohort.clear_sids_and_student_count()
if alert_count is not None:
cohort.alert_count = alert_count
else:
# Alert count will be refreshed
cohort.update_alert_count(None)
std_commit()
return cohort.to_api_json(**kwargs)
@classmethod
def get_sids(cls, cohort_id):
query = db.session.query(cls).options(undefer('sids'))
cohort = query.filter_by(id=cohort_id).first()
return cohort and cohort.sids
@classmethod
def get_domain_of_cohort(cls, cohort_id):
query = text('SELECT domain FROM cohort_filters WHERE id = :id')
result = db.session.execute(query, {'id': cohort_id}).first()
return result and result['domain']
def clear_sids_and_student_count(self):
self.__transient_sids = self.sids
self.update_sids_and_student_count(None, None)
def update_sids_and_student_count(self, sids, student_count):
self.sids = sids
self.student_count = student_count
std_commit()
return self
def update_alert_count(self, count):
self.alert_count = count
std_commit()
return self
def track_membership_changes(self):
# Track membership changes only if the cohort has been saved and has an id.
if self.id:
old_sids = set(self.__transient_sids)
new_sids = set(self.sids)
removed_sids = old_sids - new_sids
added_sids = new_sids - old_sids
CohortFilterEvent.create_bulk(self.id, added_sids, removed_sids)
self.__transient_sids = []
@classmethod
def get_cohorts_of_user_id(cls, user_id, domain='default'):
query = text("""
SELECT id, domain, name, filter_criteria, alert_count, student_count
FROM cohort_filters c
WHERE c.owner_id = :user_id AND c.domain = :domain
ORDER BY c.name
""")
results = db.session.execute(query, {
'domain': domain,
'user_id': user_id
})
def transform(row):
return {
'id': row['id'],
'domain': row['domain'],
'name': row['name'],
'criteria': row['filter_criteria'],
'alertCount': row['alert_count'],
'totalStudentCount': row['student_count'],
}
return [transform(row) for row in results]
@classmethod
def get_cohorts_owned_by_uids(cls, uids, domain='default'):
query = text("""
SELECT
c.id, c.domain, c.name, c.filter_criteria, c.alert_count, c.student_count, u.uid
FROM cohort_filters c
INNER JOIN authorized_users u ON c.owner_id = u.id
WHERE u.uid = ANY(:uids) AND c.domain = :domain
GROUP BY c.id, c.name, c.filter_criteria, c.alert_count, c.student_count, u.uid
""")
results = db.session.execute(query, {'domain': domain, 'uids': uids})
def transform(row):
return {
'id': row['id'],
'domain': row['domain'],
'name': row['name'],
'criteria': row['filter_criteria'],
'ownerUid': row['uid'],
'alertCount': row['alert_count'],
'totalStudentCount': row['student_count'],
}
return [transform(row) for row in results]
@classmethod
def is_cohort_owned_by(cls, cohort_id, user_id):
query = text("""
SELECT count(*) FROM cohort_filters c
WHERE c.owner_id = :user_id AND c.id = :cohort_id
""")
results = db.session.execute(
query,
{
'cohort_id': cohort_id,
'user_id': user_id,
},
)
return results.first()['count']
@classmethod
def refresh_alert_counts_for_owner(cls, owner_id):
query = text("""
UPDATE cohort_filters
SET alert_count = updated_cohort_counts.alert_count
FROM
(
SELECT cohort_filters.id AS cohort_filter_id, count(*) AS alert_count
FROM alerts
JOIN cohort_filters
ON alerts.sid = ANY(cohort_filters.sids)
AND alerts.key LIKE :key
AND alerts.deleted_at IS NULL
AND cohort_filters.owner_id = :owner_id
LEFT JOIN alert_views
ON alert_views.alert_id = alerts.id
AND alert_views.viewer_id = :owner_id
WHERE alert_views.dismissed_at IS NULL
GROUP BY cohort_filters.id
) updated_cohort_counts
WHERE cohort_filters.id = updated_cohort_counts.cohort_filter_id
""")
result = db.session.execute(query, {
'owner_id': owner_id,
'key': current_term_id() + '_%'
})
std_commit()
return result
@classmethod
def find_by_id(cls, cohort_id, **kwargs):
cohort = cls.query.filter_by(id=cohort_id).first()
return cohort and cohort.to_api_json(**kwargs)
@classmethod
def delete(cls, cohort_id):
cohort_filter = cls.query.filter_by(id=cohort_id).first()
db.session.delete(cohort_filter)
std_commit()
def to_base_json(self):
c = self.filter_criteria
c = c if isinstance(c, dict) else json.loads(c)
user_uid = self.owner.uid if self.owner else None
option_groups = CohortFilterOptions(
user_uid, scope_for_criteria()).get_filter_option_groups()
for label, option_group in option_groups.items():
for option in option_group:
key = option['key']
if key in c:
value = c.get(key)
if option['type']['db'] == 'boolean':
c[key] = util.to_bool_or_none(value)
else:
c[key] = value
def _owner_to_json(owner):
if not owner:
return None
return {
'uid':
owner.uid,
'deptCodes': [
m.university_dept.dept_code
for m in owner.department_memberships
],
}
return {
'id':
self.id,
'domain':
self.domain,
'name':
self.name,
'code':
self.id,
'criteria':
c,
'owner':
_owner_to_json(self.owner),
'teamGroups':
athletics.get_team_groups(c.get('groupCodes'))
if c.get('groupCodes') else [],
'alertCount':
self.alert_count,
}
def to_api_json(
self,
order_by=None,
offset=0,
limit=50,
term_id=None,
alert_offset=None,
alert_limit=None,
include_sids=False,
include_students=True,
include_profiles=False,
include_alerts_for_user_id=None,
):
benchmark = get_benchmarker(f'CohortFilter {self.id} to_api_json')
benchmark('begin')
cohort_json = self.to_base_json()
if not include_students and not include_alerts_for_user_id and self.student_count is not None:
# No need for a students query; return the database-stashed student count.
cohort_json.update({
'totalStudentCount': self.student_count,
})
benchmark('end')
return cohort_json
benchmark('begin students query')
sids_only = not include_students
if self.domain == 'admitted_students':
results = _query_admitted_students(
benchmark=benchmark,
criteria=cohort_json['criteria'],
limit=limit,
offset=offset,
order_by=order_by,
sids_only=sids_only,
)
else:
results = _query_students(
benchmark=benchmark,
criteria=cohort_json['criteria'],
include_profiles=include_profiles,
limit=limit,
offset=offset,
order_by=order_by,
owner=self.owner,
term_id=term_id,
sids_only=sids_only,
)
# If the cohort is new or cache refresh is underway then store student_count and sids in the db.
if self.student_count is None:
self.update_sids_and_student_count(
sids=results['sids'] if results else [],
student_count=results['totalStudentCount'] if results else 0,
)
if self.domain == 'default':
self.track_membership_changes()
if results:
# Cohort might have tens of thousands of SIDs.
if include_sids:
cohort_json['sids'] = results['sids']
cohort_json.update({
'totalStudentCount':
results['totalStudentCount'],
})
if include_students:
cohort_json.update({
'students': results['students'],
})
if include_alerts_for_user_id and self.domain == 'default':
benchmark('begin alerts query')
alert_count_per_sid = Alert.include_alert_counts_for_students(
viewer_user_id=include_alerts_for_user_id,
group=results,
offset=alert_offset,
limit=alert_limit,
)
benchmark('end alerts query')
cohort_json.update({
'alerts': alert_count_per_sid,
})
if self.alert_count is None:
alert_count = sum(student['alertCount']
for student in alert_count_per_sid)
self.update_alert_count(alert_count)
cohort_json.update({
'alertCount': alert_count,
})
benchmark('end')
return cohort_json
class 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 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 AppointmentAvailability(Base):
__tablename__ = 'appointment_availability'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
authorized_user_id = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False)
dept_code = db.Column(db.String(80), nullable=False)
weekday = db.Column(weekday_types_enum, nullable=False)
# A null date_override indicates a recurring weekday value.
date_override = db.Column(db.Date, nullable=True)
# A null start_time and end_time indicates unavailability for the day (meaningful only when date_override is not null).
start_time = db.Column(db.Date, nullable=True)
end_time = db.Column(db.Date, nullable=True)
def __init__(
self,
authorized_user_id,
dept_code,
start_time,
end_time,
weekday,
date_override,
):
self.authorized_user_id = authorized_user_id
self.dept_code = dept_code
self.start_time = start_time
self.end_time = end_time
self.weekday = weekday
self.date_override = date_override
@classmethod
def create(
cls,
authorized_user_id,
dept_code,
start_time,
end_time,
weekday=None,
date_override=None,
):
start_time, end_time = cls._parse_and_validate(start_time, end_time, allow_null=(date_override is not None))
slot = cls(
authorized_user_id=authorized_user_id,
dept_code=dept_code,
date_override=date_override,
end_time=end_time,
start_time=start_time,
weekday=weekday,
)
db.session.add(slot)
std_commit()
cls._merge_overlaps(slot.authorized_user_id, slot.dept_code, slot.weekday, slot.date_override)
return True
@classmethod
def update(cls, id_, start_time, end_time):
start_time, end_time = cls._parse_and_validate(start_time, end_time, allow_null=False)
slot = cls.query.filter_by(id=id_).first()
slot.start_time = start_time
slot.end_time = end_time
std_commit()
db.session.refresh(slot)
cls._merge_overlaps(slot.authorized_user_id, slot.dept_code, slot.weekday, slot.date_override)
return True
@classmethod
def delete(cls, id_):
db.session.execute(cls.__table__.delete().where(cls.id == id_))
std_commit()
return True
@classmethod
def availability_for_advisor(cls, authorized_user_id, dept_code):
results = cls.query.filter_by(authorized_user_id=authorized_user_id, dept_code=dept_code).order_by(
cls.weekday,
nullsfirst(cls.date_override),
cls.start_time,
)
availability = {}
for weekday, group_by_weekday in groupby(results, lambda x: x.weekday):
availability[weekday] = {}
for date_key, group_by_date_override in groupby(group_by_weekday, lambda x: x.date_override):
if date_key is None:
date_key = 'recurring'
else:
date_key = str(date_key)
availability[weekday][date_key] = [cls.to_api_json(a.id, a.start_time, a.end_time) for a in group_by_date_override]
return availability
@classmethod
def daily_availability_for_department(cls, dept_code, date_):
results = cls._query_availability(dept_code, date_)
availability = {}
for uid, group_by_uid in groupby(results, lambda x: x.uid):
availability_for_uid = [cls.to_api_json(a['id'], a['start_time'], a['end_time']) for a in group_by_uid if a['start_time']]
if len(availability_for_uid):
availability[uid] = availability_for_uid
return availability
@classmethod
def get_openings(cls, dept_code, date_, appointments):
results = cls._query_availability(dept_code, date_)
openings = []
for uid, group_by_uid in groupby(results, lambda x: x.uid):
for a in group_by_uid:
if a['start_time'] and a['end_time']:
start_opening = datetime.combine(date_, a['start_time']).replace(tzinfo=date_.tzinfo).astimezone(pytz.utc)
end_availability = datetime.combine(date_, a['end_time']).replace(tzinfo=date_.tzinfo).astimezone(pytz.utc)
while (end_availability - start_opening).total_seconds() >= app.config['SCHEDULED_APPOINTMENT_LENGTH'] * 60:
end_opening = start_opening + timedelta(minutes=app.config['SCHEDULED_APPOINTMENT_LENGTH'])
start_time_str = _isoformat(start_opening)
if next((a for a in appointments if a['scheduledTime'] == start_time_str and a['advisorUid'] == uid), None) is None:
openings.append({
'uid': uid,
'startTime': start_time_str,
'endTime': str(end_opening),
})
start_opening = end_opening
return sorted(openings, key=lambda i: (i['startTime'], i['uid']))
@classmethod
def _query_availability(cls, dept_code, date_):
# Per distinct UID, select availability slots for the provided date if present as date_override; otherwise
# fall back to slots with null date_override, indicating recurring per-weekday values.
sql = """SELECT u.uid, a.id, a.start_time, a.end_time FROM appointment_availability a
JOIN (
SELECT authorized_user_id, weekday, dept_code, MAX(date_override) AS date_override
FROM appointment_availability
WHERE weekday = :weekday
AND dept_code = :dept_code
AND (date_override = :date_ OR date_override IS NULL)
GROUP BY authorized_user_id, weekday, dept_code
) t
ON a.authorized_user_id = t.authorized_user_id
AND a.weekday = t.weekday
AND a.dept_code = t.dept_code
AND (a.date_override = t.date_override OR (a.date_override IS NULL AND t.date_override IS NULL))
JOIN authorized_users u on a.authorized_user_id = u.id
ORDER BY uid, start_time"""
return db.session.execute(text(sql), {'date_': str(date_), 'weekday': date_.strftime('%a'), 'dept_code': dept_code})
@classmethod
def to_api_json(cls, id_, start_time, end_time):
return {
'id': id_,
'startTime': start_time and str(start_time),
'endTime': start_time and str(end_time),
}
@classmethod
def _parse_and_validate(cls, start_time, end_time, allow_null):
if start_time is None and (not allow_null or end_time is not None):
raise ValueError('Start time cannot be null')
elif end_time is None and (not allow_null or end_time is not None):
raise ValueError('End time cannot be null')
elif start_time is None and end_time is None:
return None, None
try:
start_time = time(*[int(i) for i in start_time.split(':')])
except Exception:
raise ValueError('Could not parse start time')
try:
end_time = time(*[int(i) for i in end_time.split(':')])
except Exception:
raise ValueError('Could not parse end time')
if start_time >= end_time:
raise ValueError('Start time must be before end time')
return start_time, end_time
@classmethod
def _merge_overlaps(cls, authorized_user_id, dept_code, weekday, date_override):
previous_slot = None
for slot in cls.query.filter_by(
authorized_user_id=authorized_user_id,
dept_code=dept_code,
weekday=weekday,
date_override=date_override,
).order_by(cls.start_time):
if previous_slot is not None and previous_slot.end_time >= slot.start_time:
if previous_slot.end_time < slot.end_time:
previous_slot.end_time = slot.end_time
db.session.delete(slot)
else:
previous_slot = slot
std_commit()
class UniversityDeptMember(Base):
__tablename__ = 'university_dept_members'
university_dept_id = db.Column(db.Integer,
db.ForeignKey('university_depts.id'),
primary_key=True)
authorized_user_id = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
primary_key=True)
is_advisor = db.Column(db.Boolean, nullable=False)
is_director = db.Column(db.Boolean, nullable=False)
is_scheduler = db.Column(db.Boolean, nullable=False)
automate_membership = db.Column(db.Boolean, nullable=False)
authorized_user = db.relationship('AuthorizedUser',
back_populates='department_memberships')
# Pre-load UniversityDept below to avoid 'failed to locate', as seen during routes.py init phase
university_dept = db.relationship(UniversityDept.__name__,
back_populates='authorized_users')
def __init__(self,
is_advisor,
is_director,
is_scheduler,
automate_membership=True):
self.is_advisor = is_advisor
self.is_director = is_director
self.is_scheduler = is_scheduler
self.automate_membership = automate_membership
@classmethod
def create_or_update_membership(
cls,
university_dept,
authorized_user,
is_advisor,
is_director,
is_scheduler,
automate_membership=True,
):
dept_id = university_dept.id
user_id = authorized_user.id
existing_membership = cls.query.filter_by(
university_dept_id=dept_id, authorized_user_id=user_id).first()
if existing_membership:
membership = existing_membership
membership.is_advisor = is_advisor
membership.is_director = is_director
membership.is_scheduler = is_scheduler
membership.automate_membership = automate_membership
else:
membership = cls(
is_advisor=is_advisor,
is_director=is_director,
is_scheduler=is_scheduler,
automate_membership=automate_membership,
)
membership.authorized_user = authorized_user
membership.university_dept = university_dept
authorized_user.department_memberships.append(membership)
university_dept.authorized_users.append(membership)
db.session.add(membership)
std_commit()
return membership
@classmethod
def update_membership(
cls,
university_dept_id,
authorized_user_id,
is_advisor,
is_director,
is_scheduler,
automate_membership,
):
membership = cls.query.filter_by(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id).first()
if membership:
membership.is_advisor = membership.is_advisor if is_advisor is None else is_advisor
membership.is_director = membership.is_director if is_director is None else is_director
membership.is_scheduler = membership.is_scheduler if is_scheduler is None else is_scheduler
membership.automate_membership = membership.automate_membership if automate_membership is None else automate_membership
std_commit()
return membership
return None
@classmethod
def delete_membership(cls, university_dept_id, authorized_user_id):
membership = cls.query.filter_by(
university_dept_id=university_dept_id,
authorized_user_id=authorized_user_id).first()
if not membership:
return False
db.session.delete(membership)
std_commit()
return True
def to_api_json(self):
return {
'universityDeptId': self.university_dept_id,
'authorizedUserId': self.authorized_user_id,
'isAdvisor': self.is_advisor,
'isDirector': self.is_director,
'isScheduler': self.is_scheduler,
'automateMembership': self.automate_membership,
}
class DegreeProgressCategory(Base):
__tablename__ = 'degree_progress_categories'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
accent_color = db.Column(db.String(255))
category_type = db.Column(degree_progress_category_type, nullable=False)
course_units = db.Column(NUMRANGE)
description = db.Column(db.Text)
grade = db.Column(db.String(50))
is_recommended = db.Column(db.Boolean, nullable=False, default=False)
name = db.Column(db.String(255), nullable=False)
note = db.Column(db.Text)
parent_category_id = db.Column(
db.Integer, db.ForeignKey('degree_progress_categories.id'))
position = db.Column(db.Integer, nullable=False)
template_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_templates.id'),
nullable=False)
unit_requirements = db.relationship(
DegreeProgressCategoryUnitRequirement.__name__,
back_populates='category',
lazy='joined',
)
def __init__(
self,
category_type,
name,
position,
template_id,
accent_color=None,
course_units=None,
description=None,
grade=None,
parent_category_id=None,
):
self.accent_color = accent_color
self.category_type = category_type
self.course_units = course_units
self.description = description
self.grade = grade
self.name = name
self.parent_category_id = parent_category_id
self.position = position
self.template_id = template_id
def __repr__(self):
return f"""<DegreeProgressCategory id={self.id},
accent_color={self.accent_color},
category_type={self.category_type},
course_units={self.course_units},
description={self.description},
grade={self.grade},
is_recommended={self.is_recommended},
name={self.name},
note={self.note},
parent_category_id={self.parent_category_id},
position={self.position},
template_id={self.template_id},
created_at={self.created_at},
updated_at={self.updated_at}>"""
@classmethod
def create(
cls,
category_type,
name,
position,
template_id,
accent_color=None,
course_units_lower=None,
course_units_upper=None,
description=None,
grade=None,
parent_category_id=None,
unit_requirement_ids=None,
):
course_units = None if course_units_lower is None else NumericRange(
float(course_units_lower),
float(course_units_upper or course_units_lower),
'[]',
)
category = cls(
accent_color=accent_color,
category_type=category_type,
course_units=course_units,
description=description,
grade=grade,
name=name,
parent_category_id=parent_category_id,
position=position,
template_id=template_id,
)
# TODO: Use 'unit_requirement_ids' in mapping this instance to 'unit_requirements' table
db.session.add(category)
std_commit()
for unit_requirement_id in unit_requirement_ids or []:
DegreeProgressCategoryUnitRequirement.create(
category_id=category.id,
unit_requirement_id=int(unit_requirement_id),
)
return category
@classmethod
def delete(cls, category_id):
for unit_requirement in DegreeProgressCategoryUnitRequirement.find_by_category_id(
category_id):
db.session.delete(unit_requirement)
for course in DegreeProgressCourse.find_by_category_id(category_id):
db.session.delete(course)
std_commit()
category = cls.query.filter_by(id=category_id).first()
db.session.delete(category)
std_commit()
@classmethod
def find_by_id(cls, category_id):
return cls.query.filter_by(id=category_id).first()
@classmethod
def find_by_parent_category_id(cls, parent_category_id):
return cls.query.filter_by(parent_category_id=parent_category_id).all()
@classmethod
def get_categories(cls, template_id):
hierarchy = []
categories = []
for category in cls.query.filter_by(template_id=template_id).order_by(
asc(cls.created_at)).all():
category_type = category.category_type
api_json = category.to_api_json()
if category_type == 'Category':
# A 'Category' can have both courses and subcategories. A 'Subcategory' can have courses.
api_json['courseRequirements'] = []
api_json['subcategories'] = []
elif category_type == 'Subcategory':
api_json['courseRequirements'] = []
categories.append(api_json)
categories_by_id = dict(
(category['id'], category) for category in categories)
for category in categories:
parent_category_id = category['parentCategoryId']
if parent_category_id:
parent = categories_by_id[parent_category_id]
key = 'subcategories' if category[
'categoryType'] == 'Subcategory' else 'courseRequirements'
parent[key].append(category)
else:
hierarchy.append(category)
return hierarchy
@classmethod
def recommend(
cls,
accent_color,
category_id,
course_units_lower,
course_units_upper,
grade,
is_recommended,
note,
):
category = cls.query.filter_by(id=category_id).first()
category.accent_color = accent_color
units_lower = to_float_or_none(course_units_lower)
category.course_units = None if units_lower is None else NumericRange(
units_lower,
to_float_or_none(course_units_upper) or units_lower,
'[]',
)
category.grade = grade
category.is_recommended = is_recommended
category.note = note
std_commit()
return cls.find_by_id(category_id=category_id)
@classmethod
def set_campus_requirement_satisfied(
cls,
category_id,
is_satisfied,
):
category = cls.query.filter_by(id=category_id).first()
category.category_type = 'Campus Requirement, Satisfied' if is_satisfied else 'Campus Requirement, Unsatisfied'
std_commit()
return cls.find_by_id(category_id=category_id)
@classmethod
def update(
cls,
category_id,
course_units_lower,
course_units_upper,
description,
name,
parent_category_id,
unit_requirement_ids,
):
category = cls.query.filter_by(id=category_id).first()
units_lower = to_float_or_none(course_units_lower)
category.course_units = None if units_lower is None else NumericRange(
units_lower,
to_float_or_none(course_units_upper) or units_lower,
'[]',
)
category.description = description
category.name = name
category.parent_category_id = parent_category_id
unit_requirement_id_set = set(unit_requirement_ids or [])
existing_unit_requirements = DegreeProgressCategoryUnitRequirement.find_by_category_id(
category_id)
existing_unit_requirement_id_set = set(
[u.unit_requirement_id for u in existing_unit_requirements])
for unit_requirement_id in (unit_requirement_id_set -
existing_unit_requirement_id_set):
DegreeProgressCategoryUnitRequirement.create(
category_id=category.id,
unit_requirement_id=unit_requirement_id,
)
for unit_requirement_id in (existing_unit_requirement_id_set -
unit_requirement_id_set):
delete_me = next(e for e in existing_unit_requirements
if e.unit_requirement_id == unit_requirement_id)
db.session.delete(delete_me)
std_commit()
return cls.find_by_id(category_id=category_id)
def to_api_json(self):
unit_requirements = [
m.unit_requirement.to_api_json()
for m in (self.unit_requirements or [])
]
return {
'id':
self.id,
'accentColor':
self.accent_color,
'categoryType':
self.category_type,
'courses': [
c.to_api_json()
for c in DegreeProgressCourse.find_by_category_id(
category_id=self.id)
],
'createdAt':
_isoformat(self.created_at),
'description':
self.description,
'grade':
self.grade,
'isRecommended':
self.is_recommended,
'name':
self.name,
'note':
self.note,
'parentCategoryId':
self.parent_category_id,
'position':
self.position,
'templateId':
self.template_id,
'unitsLower':
self.course_units and self.course_units.lower,
'unitsUpper':
self.course_units and self.course_units.upper,
'unitRequirements':
sorted(unit_requirements, key=lambda r: r['name']),
'updatedAt':
_isoformat(self.updated_at),
}
class DegreeProgressCourse(Base):
__tablename__ = 'degree_progress_courses'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
accent_color = db.Column(db.String(255))
category_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_categories.id'))
degree_check_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_templates.id'),
nullable=False)
display_name = db.Column(db.String(255), nullable=False)
grade = db.Column(db.String(50), nullable=False)
ignore = db.Column(db.Boolean, nullable=False)
note = db.Column(db.Text)
manually_created_at = db.Column(db.DateTime)
manually_created_by = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'))
section_id = db.Column(db.Integer)
sid = db.Column(db.String(80), nullable=False)
term_id = db.Column(db.Integer)
units = db.Column(db.Numeric, nullable=False)
unit_requirements = db.relationship(
DegreeProgressCourseUnitRequirement.__name__,
back_populates='course',
lazy='joined',
)
__table_args__ = (db.UniqueConstraint(
'category_id',
'degree_check_id',
'manually_created_at',
'manually_created_by',
'section_id',
'sid',
'term_id',
name='degree_progress_courses_category_id_course_unique_constraint',
), )
def __init__(
self,
degree_check_id,
display_name,
grade,
section_id,
sid,
term_id,
units,
accent_color=None,
category_id=None,
ignore=False,
manually_created_at=None,
manually_created_by=None,
note=None,
):
self.accent_color = accent_color
self.category_id = category_id
self.degree_check_id = degree_check_id
self.display_name = display_name
self.grade = grade
self.ignore = ignore
self.manually_created_by = manually_created_by
if self.manually_created_by and not manually_created_at:
raise ValueError(
'manually_created_at is required if manually_created_by is present.'
)
else:
self.manually_created_at = manually_created_at
self.note = note
self.section_id = section_id
self.sid = sid
self.term_id = term_id
self.units = units
def __repr__(self):
return f"""<DegreeProgressCourse id={self.id},
accent_color={self.accent_color},
category_id={self.category_id},
degree_check_id={self.degree_check_id},
display_name={self.display_name},
grade={self.grade},
ignore={self.ignore},
manually_created_at={self.manually_created_at},
manually_created_by={self.manually_created_by},
note={self.note},
section_id={self.section_id},
sid={self.sid},
term_id={self.term_id},
units={self.units},>"""
@classmethod
def assign(cls, category_id, course_id):
course = cls.query.filter_by(id=course_id).first()
course.category_id = category_id
course.ignore = False
std_commit()
DegreeProgressCourseUnitRequirement.delete(course_id)
for u in DegreeProgressCategoryUnitRequirement.find_by_category_id(
category_id):
DegreeProgressCourseUnitRequirement.create(course.id,
u.unit_requirement_id)
return course
@classmethod
def create(
cls,
degree_check_id,
display_name,
grade,
section_id,
sid,
term_id,
units,
accent_color=None,
category_id=None,
manually_created_at=None,
manually_created_by=None,
note=None,
unit_requirement_ids=(),
):
course = cls(
accent_color=accent_color,
category_id=category_id,
degree_check_id=degree_check_id,
display_name=display_name,
grade=grade,
manually_created_at=manually_created_at,
manually_created_by=manually_created_by,
note=note,
section_id=section_id,
sid=sid,
term_id=term_id,
units=units if (units is None or is_float(units)) else 0,
)
db.session.add(course)
std_commit()
for unit_requirement_id in unit_requirement_ids:
DegreeProgressCourseUnitRequirement.create(
course_id=course.id,
unit_requirement_id=unit_requirement_id,
)
return course
@classmethod
def delete(cls, course):
db.session.delete(course)
std_commit()
@classmethod
def find_by_id(cls, course_id):
return cls.query.filter_by(id=course_id).first()
@classmethod
def find_by_category_id(cls, category_id):
return cls.query.filter_by(category_id=category_id).all()
@classmethod
def find_by_sid(cls, degree_check_id, sid):
return cls.query.filter_by(degree_check_id=degree_check_id,
sid=sid).all()
@classmethod
def get_courses(cls, degree_check_id, manually_created_at,
manually_created_by, section_id, sid, term_id):
return cls.query.filter_by(
degree_check_id=degree_check_id,
manually_created_at=manually_created_at,
manually_created_by=manually_created_by,
section_id=section_id,
sid=sid,
term_id=term_id,
).all()
@classmethod
def unassign(cls, course_id, ignore=False):
course = cls.query.filter_by(id=course_id).first()
course.category_id = None
course.ignore = ignore
std_commit()
DegreeProgressCourseUnitRequirement.delete(course_id)
return course
@classmethod
def update(
cls,
accent_color,
course_id,
grade,
name,
note,
units,
unit_requirement_ids,
):
course = cls.query.filter_by(id=course_id).first()
course.accent_color = accent_color
course.grade = grade
course.display_name = name
course.note = note
course.units = units if (units is None or is_float(units)) else 0
existing_unit_requirements = DegreeProgressCourseUnitRequirement.find_by_course_id(
course_id)
existing_unit_requirement_id_set = set(
[u.unit_requirement_id for u in existing_unit_requirements])
unit_requirement_id_set = set(unit_requirement_ids or [])
for unit_requirement_id in (unit_requirement_id_set -
existing_unit_requirement_id_set):
DegreeProgressCourseUnitRequirement.create(
course_id=course.id,
unit_requirement_id=unit_requirement_id,
)
for unit_requirement_id in (existing_unit_requirement_id_set -
unit_requirement_id_set):
delete_me = next(e for e in existing_unit_requirements
if e.unit_requirement_id == unit_requirement_id)
db.session.delete(delete_me)
std_commit()
return course
def to_api_json(self):
unit_requirements = [
m.unit_requirement.to_api_json()
for m in (self.unit_requirements or [])
]
return {
'accentColor': self.accent_color,
'categoryId': self.category_id,
'createdAt': _isoformat(self.created_at),
'degreeCheckId': self.degree_check_id,
'grade': self.grade,
'id': self.id,
'ignore': self.ignore,
'manuallyCreatedAt': _isoformat(self.manually_created_at),
'manuallyCreatedBy': self.manually_created_by,
'name': self.display_name,
'note': self.note,
'sectionId': self.section_id,
'sid': self.sid,
'termId': self.term_id,
'termName': term_name_for_sis_id(self.term_id),
'unitRequirements': sorted(unit_requirements,
key=lambda r: r['name']),
'units': self.units,
'updatedAt': _isoformat(self.updated_at),
}
class DegreeProgressUnitRequirement(Base):
__tablename__ = 'degree_progress_unit_requirements'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
created_by = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
min_units = db.Column(db.Integer, nullable=False)
name = db.Column(db.String(255), nullable=False)
template_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_templates.id'),
nullable=False)
updated_by = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
categories = db.relationship(
'DegreeProgressCategoryUnitRequirement',
back_populates='unit_requirement',
)
courses = db.relationship(
'DegreeProgressCourseUnitRequirement',
back_populates='unit_requirement',
)
template = db.relationship('DegreeProgressTemplate',
back_populates='unit_requirements')
__table_args__ = (db.UniqueConstraint(
'name',
'template_id',
name='degree_progress_unit_requirements_name_template_id_unique_const',
), )
def __init__(self, created_by, min_units, name, template_id, updated_by):
self.created_by = created_by
self.min_units = min_units
self.name = name
self.template_id = template_id
self.updated_by = updated_by
def __repr__(self):
return f"""<DegreeProgressUnitRequirement id={self.id},
name={self.name},
min_units={self.min_units},
template_id={self.template_id},
created_at={self.created_at},
created_by={self.created_by},
updated_at={self.updated_at},
updated_by={self.updated_by}>"""
@classmethod
def create(cls, created_by, min_units, name, template_id):
unit_requirement = cls(
created_by=created_by,
min_units=min_units,
name=name,
template_id=template_id,
updated_by=created_by,
)
db.session.add(unit_requirement)
std_commit()
return unit_requirement
@classmethod
def delete(cls, unit_requirement_id):
unit_requirement = cls.query.filter_by(id=unit_requirement_id).first()
DegreeProgressCategoryUnitRequirement.delete_mappings(
unit_requirement_id=unit_requirement.id)
db.session.delete(unit_requirement)
std_commit()
@classmethod
def find_by_id(cls, unit_requirement_id):
return cls.query.filter_by(id=unit_requirement_id).first()
@classmethod
def update(cls, id_, min_units, name, updated_by):
unit_requirement = cls.query.filter_by(id=id_).first()
unit_requirement.min_units = min_units
unit_requirement.name = name
unit_requirement.updated_by = updated_by
std_commit()
db.session.refresh(unit_requirement)
return unit_requirement
def to_api_json(self):
return {
'id': self.id,
'name': self.name,
'minUnits': self.min_units,
'createdAt': _isoformat(self.created_at),
'createdBy': self.created_by,
'updatedAt': _isoformat(self.updated_at),
'updatedBy': self.updated_by,
'templateId': self.template_id,
}
