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 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 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 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 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 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 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 CuratedCohortStudent(db.Model):
__tablename__ = 'student_group_members'
curated_cohort_id = db.Column('student_group_id',
db.Integer,
db.ForeignKey('student_groups.id'),
primary_key=True)
sid = db.Column('sid', db.String(80), primary_key=True)
curated_cohort = db.relationship('CuratedCohort',
back_populates='students')
class DegreeProgressNote(Base):
__tablename__ = 'degree_progress_notes'
body = db.Column(db.Text, nullable=False)
template_id = db.Column(db.Integer,
db.ForeignKey('degree_progress_templates.id'),
nullable=False,
primary_key=True)
updated_by = db.Column(db.Integer,
db.ForeignKey('authorized_users.id'),
nullable=False)
template = db.relationship('DegreeProgressTemplate', back_populates='note')
def __init__(self, body, template_id, updated_by):
self.body = body
self.template_id = template_id
self.updated_by = updated_by
def __repr__(self):
return f"""<DegreeProgressNote template_id={self.template_id},
body={self.body},
created_at={self.created_at},
updated_at={self.updated_at},
updated_by={self.updated_by}>"""
@classmethod
def upsert(
cls,
body,
template_id,
updated_by,
):
note = cls.query.filter_by(template_id=template_id).first()
if note:
note.body = body
note.updated_by = updated_by
else:
note = cls(
body=body,
template_id=template_id,
updated_by=updated_by,
)
db.session.add(note)
std_commit()
return note
def to_api_json(self):
return {
'body': self.body,
'createdAt': _isoformat(self.created_at),
'templateId': self.template_id,
'updatedAt': _isoformat(self.updated_at),
'updatedBy': self.updated_by,
}
class UniversityDept(Base):
__tablename__ = 'university_depts'
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
dept_code = db.Column(db.String(80), nullable=False)
dept_name = db.Column(db.String(255), nullable=False)
authorized_users = db.relationship(
'UniversityDeptMember',
back_populates='university_dept',
)
automate_memberships = db.Column(db.Boolean, nullable=False, default=False)
__table_args__ = (db.UniqueConstraint(
'dept_code',
'dept_name',
name='university_depts_code_unique_constraint'), )
def __init__(self, dept_code, dept_name, automate_memberships):
self.dept_code = dept_code
self.dept_name = dept_name
self.automate_memberships = automate_memberships
@classmethod
def find_by_dept_code(cls, dept_code):
return cls.query.filter_by(dept_code=dept_code).first()
@classmethod
def create(cls, dept_code, dept_name, automate_memberships):
dept = cls(dept_code=dept_code,
dept_name=dept_name,
automate_memberships=automate_memberships)
db.session.add(dept)
std_commit()
return dept
def delete_all_members(self):
sql = """
DELETE FROM university_dept_members WHERE university_dept_id = :id;
UPDATE authorized_users SET deleted_at = now()
WHERE is_admin IS FALSE
AND deleted_at IS NULL
AND id NOT IN (SELECT authorized_user_id FROM university_dept_members);"""
db.session.execute(text(sql), {'id': self.id})
std_commit()
def memberships_from_loch(self):
program_affiliations = BERKELEY_DEPT_CODE_TO_PROGRAM_AFFILIATIONS.get(
self.dept_code)
if not program_affiliations:
return []
return data_loch.get_advisor_uids_for_affiliations(
program_affiliations.get('program'),
program_affiliations.get('affiliations'),
)
class JsonCacheBase(object):
id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003
key = db.Column(db.String, nullable=False, unique=True)
json = db.Column(JSONB)
def __init__(self, key, json=None):
self.key = key
self.json = json
def __repr__(self):
return f'<JsonCache {self.key}, json={self.json}>'
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 Base(db.Model):
__abstract__ = True
created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.now,
onupdate=datetime.now)
@classmethod
def load_csv(cls, filename):
with open(filename) as csvfile:
reader = csv.DictReader(csvfile)
for csvrow in reader:
record = cls(**csvrow)
db.session.add(record)
std_commit()
class JsonCache(Base):
__tablename__ = 'json_cache'
id = db.Column(db.Integer, nullable=False, primary_key=True)
key = db.Column(db.String, nullable=False, unique=True)
json = db.Column(JSONB)
def __init__(self, key, json=None):
self.key = key
self.json = json
def __repr__(self):
return '<JsonCache {}, json={}, updated={}, created={}>'.format(
self.key,
self.json,
self.updated_at,
self.created_at,
)
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 Scheduler(AuthorizedUserExtension):
__tablename__ = 'schedulers'
authorized_user_relationship = 'scheduler_departments'
drop_in = db.Column(db.Boolean, default=False, nullable=False)
same_day = db.Column(db.Boolean, default=False, nullable=False)
def update_availability(self, drop_in, same_day):
self.drop_in = drop_in
self.same_day = same_day
std_commit()
@classmethod
def create_or_update_membership(cls,
dept_code,
authorized_user_id,
drop_in=False,
same_day=False):
existing_membership = cls.query.filter_by(
dept_code=dept_code,
authorized_user_id=authorized_user_id).first()
if existing_membership:
new_membership = existing_membership
new_membership.drop_in = drop_in
new_membership.same_day = same_day
else:
new_membership = cls(
authorized_user_id=authorized_user_id,
dept_code=dept_code,
drop_in=drop_in,
same_day=same_day,
)
db.session.add(new_membership)
std_commit()
return new_membership
def to_api_json(self):
return {
'deptCode': self.dept_code,
'dropIn': self.drop_in,
'sameDay': self.same_day,
}
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 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 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 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 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 Advisor(AuthorizedUserExtension):
__abstract__ = True
is_available = db.Column(db.Boolean, default=False, nullable=False)
def __init__(self, authorized_user_id, dept_code, is_available):
self.authorized_user_id = authorized_user_id
self.dept_code = dept_code
self.is_available = is_available
def update_availability(self, available):
self.is_available = available
std_commit()
@classmethod
def create_or_update_membership(cls,
dept_code,
authorized_user_id,
is_available=False):
existing_membership = cls.query.filter_by(
dept_code=dept_code,
authorized_user_id=authorized_user_id).first()
if existing_membership:
new_membership = existing_membership
new_membership.is_available = is_available
else:
new_membership = cls(
authorized_user_id=authorized_user_id,
dept_code=dept_code,
is_available=is_available,
)
db.session.add(new_membership)
std_commit()
return new_membership
@classmethod
def advisors_for_dept_code(cls, dept_code):
return cls.query.filter_by(dept_code=dept_code).all()
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),
}
