Esempio n. 1
0
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}
Esempio n. 2
0
class NoteTopic(db.Model):
    __tablename__ = 'note_topics'

    id = db.Column(db.Integer, nullable=False, primary_key=True)  # noqa: A003
    note_id = db.Column(db.Integer, db.ForeignKey('notes.id'), nullable=False)
    topic = db.Column(db.String(50), nullable=False)
    author_uid = db.Column(db.String(255),
                           db.ForeignKey('authorized_users.uid'),
                           nullable=False)
    note = db.relationship('Note', back_populates='topics')
    deleted_at = db.Column(db.DateTime)

    def __init__(self, note_id, topic, author_uid):
        self.note_id = note_id
        self.topic = topic
        self.author_uid = author_uid

    @classmethod
    def create_note_topic(cls, note, topic, author_uid):
        return NoteTopic(
            note_id=note.id,
            topic=topic,
            author_uid=author_uid,
        )

    @classmethod
    def find_by_note_id(cls, note_id):
        return cls.query.filter(
            and_(cls.note_id == note_id,
                 cls.deleted_at == None)).all()  # noqa: E711

    def to_api_json(self):
        return self.topic
class NoteTemplateAttachment(db.Model):
    __tablename__ = 'note_template_attachments'

    id = db.Column(db.Integer, nullable=False, primary_key=True)  # noqa: A003
    note_template_id = db.Column(db.Integer,
                                 db.ForeignKey('note_templates.id'),
                                 nullable=False)
    path_to_attachment = db.Column('path_to_attachment',
                                   db.String(255),
                                   nullable=False)
    uploaded_by_uid = db.Column('uploaded_by_uid',
                                db.String(255),
                                nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
    deleted_at = db.Column(db.DateTime)
    note_template = db.relationship('NoteTemplate',
                                    back_populates='attachments')

    __table_args__ = (
        db.UniqueConstraint(
            'note_template_id',
            'path_to_attachment',
            # Constraint name length is limited to 63 bytes in Postgres so we abbreviate the prefix.
            name='nta_note_template_id_path_to_attachment_unique_constraint',
        ), )

    def __init__(self, note_template_id, path_to_attachment, uploaded_by_uid):
        self.note_template_id = note_template_id
        self.path_to_attachment = path_to_attachment
        self.uploaded_by_uid = uploaded_by_uid

    def get_user_filename(self):
        return get_attachment_filename(self.id, self.path_to_attachment)

    @classmethod
    def find_by_id(cls, attachment_id):
        return cls.query.filter(
            and_(cls.id == attachment_id,
                 cls.deleted_at == None)).first()  # noqa: E711

    @classmethod
    def get_attachments(cls, attachment_ids):
        return cls.query.filter(
            and_(cls.id.in_(attachment_ids),
                 cls.deleted_at == None)).all()  # noqa: E711

    @classmethod
    def create(cls, note_template_id, name, byte_stream, uploaded_by):
        return NoteTemplateAttachment(
            note_template_id=note_template_id,
            path_to_attachment=put_attachment_to_s3(name=name,
                                                    byte_stream=byte_stream),
            uploaded_by_uid=uploaded_by,
        )

    def to_api_json(self):
        return note_attachment_to_api_json(self)
Esempio n. 4
0
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'),
        )
Esempio n. 5
0
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)
Esempio n. 6
0
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
Esempio n. 7
0
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,
        }
Esempio n. 8
0
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
Esempio n. 9
0
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
Esempio n. 10
0
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()
Esempio n. 11
0
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')
Esempio n. 12
0
class Topic(db.Model):
    __tablename__ = 'topics'

    id = db.Column(db.Integer, nullable=False, primary_key=True)  # noqa: A003
    topic = db.Column(db.String(255), nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.now)
    deleted_at = db.Column(db.DateTime, nullable=True)
    available_in_notes = db.Column(db.Boolean, nullable=False)
    available_in_appointments = db.Column(db.Boolean, nullable=False)

    def __init__(self, topic, available_in_notes, available_in_appointments):
        self.topic = topic
        self.available_in_notes = available_in_notes
        self.available_in_appointments = available_in_appointments

    @classmethod
    def get_all(cls,
                available_in_notes=None,
                available_in_appointments=None,
                include_deleted=False):
        kwargs = {}
        if available_in_appointments is not None:
            kwargs['available_in_appointments'] = available_in_appointments
        if available_in_notes is not None:
            kwargs['available_in_notes'] = available_in_notes
        if not include_deleted:
            kwargs['deleted_at'] = None

        return cls.query.filter_by(**kwargs).order_by(cls.topic).all()

    @classmethod
    def delete(cls, topic_id):
        topic = cls.query.filter_by(id=topic_id, deleted_at=None).first()
        if topic:
            now = utc_now()
            topic.deleted_at = now
            std_commit()

    @classmethod
    def create_topic(cls,
                     topic,
                     available_in_notes=False,
                     available_in_appointments=False):
        topic = cls(topic=topic,
                    available_in_notes=available_in_notes,
                    available_in_appointments=available_in_appointments)
        db.session.add(topic)
        std_commit()
        return topic

    def to_api_json(self):
        return self.topic
class AuthorizedUserExtension(Base):
    __abstract__ = True

    @declared_attr
    def authorized_user_id(cls):  # noqa: N805
        return db.Column(db.Integer,
                         db.ForeignKey('authorized_users.id'),
                         nullable=False,
                         primary_key=True)

    @declared_attr
    def authorized_user(cls):  # noqa: N805
        return db.relationship('AuthorizedUser',
                               back_populates=cls.authorized_user_relationship)

    dept_code = db.Column(db.String(80), nullable=False, primary_key=True)

    @classmethod
    def find_by_dept_and_user(cls, dept_code, authorized_user_id):
        return cls.query.filter_by(authorized_user_id=authorized_user_id,
                                   dept_code=dept_code).first()

    @classmethod
    def get_all(cls, authorized_user_id):
        return cls.query.filter_by(authorized_user_id=authorized_user_id).all()

    @classmethod
    def delete(cls, authorized_user_id, dept_code):
        row = cls.query.filter_by(authorized_user_id=authorized_user_id,
                                  dept_code=dept_code).first()
        if not row:
            return False
        db.session.delete(row)
        std_commit()
        return True

    @classmethod
    def delete_orphans(cls):
        sql = f"""
            DELETE FROM {cls.__tablename__} AS a
                WHERE a.authorized_user_id NOT IN (
                    SELECT m.authorized_user_id
                    FROM university_depts AS d
                    JOIN university_dept_members AS m
                    ON m.university_dept_id = d.id
                    WHERE d.dept_code = a.dept_code
                );"""
        db.session.execute(sql)
        std_commit()
class DropInAdvisor(Advisor):
    __tablename__ = 'drop_in_advisors'
    authorized_user_relationship = 'drop_in_departments'

    status = db.Column(db.String(255))

    def update_status(self, status):
        self.status = status
        std_commit()

    def to_api_json(self):
        return {
            'deptCode': self.dept_code,
            'available': self.is_available,
            'status': self.status,
        }
Esempio n. 15
0
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
Esempio n. 16
0
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
Esempio n. 17
0
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
Esempio n. 18
0
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()
Esempio n. 19
0
class AuthorizedUser(Base, UserMixin):
    __tablename__ = 'authorized_users'

    id = db.Column(db.Integer, nullable=False, primary_key=True)
    uid = db.Column(db.String(255), nullable=False, unique=True)
    is_advisor = db.Column(db.Boolean)
    is_admin = db.Column(db.Boolean)
    is_director = db.Column(db.Boolean)
    cohort_filters = db.relationship('CohortFilter',
                                     secondary=cohort_filter_owners,
                                     back_populates='owners')

    def __init__(self,
                 uid,
                 is_advisor=True,
                 is_admin=False,
                 is_director=False):
        self.uid = uid
        self.is_advisor = is_advisor
        self.is_admin = is_admin
        self.is_director = is_director

    def __repr__(self):
        return '<AuthorizedUser {}, is_advisor={}, is_admin={}, is_director={}, updated={}, created={}>'.format(
            self.uid,
            self.is_advisor,
            self.is_admin,
            self.is_director,
            self.updated_at,
            self.created_at,
        )

    def get_id(self):
        """Override UserMixin, since our DB conventionally reserves 'id' for generated keys."""
        return self.uid

    @classmethod
    def find_by_uid(cls, uid):
        return AuthorizedUser.query.filter_by(uid=uid).first()
class DegreeProgressCourse(Base):
    __tablename__ = 'degree_progress_courses'

    id = db.Column(db.Integer, nullable=False, primary_key=True)  # noqa: A003
    accent_color = db.Column(db.String(255))
    category_id = db.Column(db.Integer,
                            db.ForeignKey('degree_progress_categories.id'))
    degree_check_id = db.Column(db.Integer,
                                db.ForeignKey('degree_progress_templates.id'),
                                nullable=False)
    display_name = db.Column(db.String(255), nullable=False)
    grade = db.Column(db.String(50), nullable=False)
    ignore = db.Column(db.Boolean, nullable=False)
    note = db.Column(db.Text)
    manually_created_at = db.Column(db.DateTime)
    manually_created_by = db.Column(db.Integer,
                                    db.ForeignKey('authorized_users.id'))
    section_id = db.Column(db.Integer)
    sid = db.Column(db.String(80), nullable=False)
    term_id = db.Column(db.Integer)
    units = db.Column(db.Numeric, nullable=False)
    unit_requirements = db.relationship(
        DegreeProgressCourseUnitRequirement.__name__,
        back_populates='course',
        lazy='joined',
    )

    __table_args__ = (db.UniqueConstraint(
        'category_id',
        'degree_check_id',
        'manually_created_at',
        'manually_created_by',
        'section_id',
        'sid',
        'term_id',
        name='degree_progress_courses_category_id_course_unique_constraint',
    ), )

    def __init__(
        self,
        degree_check_id,
        display_name,
        grade,
        section_id,
        sid,
        term_id,
        units,
        accent_color=None,
        category_id=None,
        ignore=False,
        manually_created_at=None,
        manually_created_by=None,
        note=None,
    ):
        self.accent_color = accent_color
        self.category_id = category_id
        self.degree_check_id = degree_check_id
        self.display_name = display_name
        self.grade = grade
        self.ignore = ignore
        self.manually_created_by = manually_created_by
        if self.manually_created_by and not manually_created_at:
            raise ValueError(
                'manually_created_at is required if manually_created_by is present.'
            )
        else:
            self.manually_created_at = manually_created_at
        self.note = note
        self.section_id = section_id
        self.sid = sid
        self.term_id = term_id
        self.units = units

    def __repr__(self):
        return f"""<DegreeProgressCourse id={self.id},
            accent_color={self.accent_color},
            category_id={self.category_id},
            degree_check_id={self.degree_check_id},
            display_name={self.display_name},
            grade={self.grade},
            ignore={self.ignore},
            manually_created_at={self.manually_created_at},
            manually_created_by={self.manually_created_by},
            note={self.note},
            section_id={self.section_id},
            sid={self.sid},
            term_id={self.term_id},
            units={self.units},>"""

    @classmethod
    def assign(cls, category_id, course_id):
        course = cls.query.filter_by(id=course_id).first()
        course.category_id = category_id
        course.ignore = False
        std_commit()
        DegreeProgressCourseUnitRequirement.delete(course_id)
        for u in DegreeProgressCategoryUnitRequirement.find_by_category_id(
                category_id):
            DegreeProgressCourseUnitRequirement.create(course.id,
                                                       u.unit_requirement_id)
        return course

    @classmethod
    def create(
            cls,
            degree_check_id,
            display_name,
            grade,
            section_id,
            sid,
            term_id,
            units,
            accent_color=None,
            category_id=None,
            manually_created_at=None,
            manually_created_by=None,
            note=None,
            unit_requirement_ids=(),
    ):
        course = cls(
            accent_color=accent_color,
            category_id=category_id,
            degree_check_id=degree_check_id,
            display_name=display_name,
            grade=grade,
            manually_created_at=manually_created_at,
            manually_created_by=manually_created_by,
            note=note,
            section_id=section_id,
            sid=sid,
            term_id=term_id,
            units=units if (units is None or is_float(units)) else 0,
        )
        db.session.add(course)
        std_commit()

        for unit_requirement_id in unit_requirement_ids:
            DegreeProgressCourseUnitRequirement.create(
                course_id=course.id,
                unit_requirement_id=unit_requirement_id,
            )
        return course

    @classmethod
    def delete(cls, course):
        db.session.delete(course)
        std_commit()

    @classmethod
    def find_by_id(cls, course_id):
        return cls.query.filter_by(id=course_id).first()

    @classmethod
    def find_by_category_id(cls, category_id):
        return cls.query.filter_by(category_id=category_id).all()

    @classmethod
    def find_by_sid(cls, degree_check_id, sid):
        return cls.query.filter_by(degree_check_id=degree_check_id,
                                   sid=sid).all()

    @classmethod
    def get_courses(cls, degree_check_id, manually_created_at,
                    manually_created_by, section_id, sid, term_id):
        return cls.query.filter_by(
            degree_check_id=degree_check_id,
            manually_created_at=manually_created_at,
            manually_created_by=manually_created_by,
            section_id=section_id,
            sid=sid,
            term_id=term_id,
        ).all()

    @classmethod
    def unassign(cls, course_id, ignore=False):
        course = cls.query.filter_by(id=course_id).first()
        course.category_id = None
        course.ignore = ignore
        std_commit()
        DegreeProgressCourseUnitRequirement.delete(course_id)
        return course

    @classmethod
    def update(
        cls,
        accent_color,
        course_id,
        grade,
        name,
        note,
        units,
        unit_requirement_ids,
    ):
        course = cls.query.filter_by(id=course_id).first()
        course.accent_color = accent_color
        course.grade = grade
        course.display_name = name
        course.note = note
        course.units = units if (units is None or is_float(units)) else 0

        existing_unit_requirements = DegreeProgressCourseUnitRequirement.find_by_course_id(
            course_id)
        existing_unit_requirement_id_set = set(
            [u.unit_requirement_id for u in existing_unit_requirements])
        unit_requirement_id_set = set(unit_requirement_ids or [])
        for unit_requirement_id in (unit_requirement_id_set -
                                    existing_unit_requirement_id_set):
            DegreeProgressCourseUnitRequirement.create(
                course_id=course.id,
                unit_requirement_id=unit_requirement_id,
            )
        for unit_requirement_id in (existing_unit_requirement_id_set -
                                    unit_requirement_id_set):
            delete_me = next(e for e in existing_unit_requirements
                             if e.unit_requirement_id == unit_requirement_id)
            db.session.delete(delete_me)

        std_commit()
        return course

    def to_api_json(self):
        unit_requirements = [
            m.unit_requirement.to_api_json()
            for m in (self.unit_requirements or [])
        ]
        return {
            'accentColor': self.accent_color,
            'categoryId': self.category_id,
            'createdAt': _isoformat(self.created_at),
            'degreeCheckId': self.degree_check_id,
            'grade': self.grade,
            'id': self.id,
            'ignore': self.ignore,
            'manuallyCreatedAt': _isoformat(self.manually_created_at),
            'manuallyCreatedBy': self.manually_created_by,
            'name': self.display_name,
            'note': self.note,
            'sectionId': self.section_id,
            'sid': self.sid,
            'termId': self.term_id,
            'termName': term_name_for_sis_id(self.term_id),
            'unitRequirements': sorted(unit_requirements,
                                       key=lambda r: r['name']),
            'units': self.units,
            'updatedAt': _isoformat(self.updated_at),
        }
Esempio n. 21
0
class TeamMember(Base):
    __tablename__ = 'team_members'

    id = db.Column(db.Integer, nullable=False, primary_key=True)
    code = db.Column(db.String(255), nullable=False)
    member_uid = db.Column(db.String(80))
    member_csid = db.Column(db.String(80), nullable=False)
    member_name = db.Column(db.String(255))
    asc_sport_code_core = db.Column(db.String(80))
    asc_sport_code = db.Column(db.String(80))
    asc_sport = db.Column(db.String(80))
    asc_sport_core = db.Column(db.String(80))
    UniqueConstraint('code', 'member_csid', name='team_member')

    def __repr__(self):
        return '<TeamMember {} ({}), asc_sport {} ({}), asc_sport_core {} ({}), uid={}, csid={}, name={}, updated={}, created={}>'.format(
            self.team_definitions.get(self.code),
            self.code,
            self.asc_sport,
            self.asc_sport_code,
            self.asc_sport_core,
            self.asc_sport_code_core,
            self.member_uid,
            self.member_csid,
            self.member_name,
            self.updated_at,
            self.created_at,
        )

    team_definitions = {
        'BAM': 'Baseball - Men',
        'BBM': 'Basketball - Men',
        'BBW': 'Basketball - Women',
        'CCM': 'Cross Country - Men',
        'CCW': 'Cross Country - Women',
        'CRM': 'Crew - Men',
        'CRW': 'Crew - Women',
        'EMX': 'Equipment Managers',
        'FBM': 'Football - Men',
        'FHW': 'Field Hockey - Women',
        'GOM': 'Golf - Men',
        'GOW': 'Golf - Women',
        'GYM': 'Gymnastics - Men',
        'GYW': 'Gymnastics - Women',
        'LCW': 'Lacrosse - Women',
        'RGM': 'Rugby - Men',
        'SBW': 'Softball - Women',
        'SCM': 'Soccer - Men',
        'SCW': 'Soccer - Women',
        'SDM': 'Swimming & Diving - Men',
        'SDW': 'Swimming & Diving - Women',
        'STX': 'Student Trainers',
        'SVW': 'Sand Volleyball - Women',
        'TIM': 'Indoor Track & Field - Men',
        'TIW': 'Indoor Track & Field - Women',
        'TNM': 'Tennis - Men',
        'TNW': 'Tennis - Women',
        'TOM': 'Outdoor Track & Field - Men',
        'TOW': 'Outdoor Track & Field - Women',
        'VBW': 'Volleyball - Women',
        'WPM': 'Water Polo - Men',
        'WPW': 'Water Polo - Women',
    }

    @classmethod
    def all_teams(cls, sort_by='name'):
        results = db.session.query(cls.code, func.count(
            cls.member_uid)).group_by(cls.code).all()

        def translate_row(row):
            return {
                'code': row[0],
                'totalMemberCount': row[1],
                'name': cls.team_definitions.get(row[0], row[0]),
            }

        teams = [translate_row(row) for row in results]
        return sorted(teams, key=lambda team: team[sort_by])

    @classmethod
    def all_athletes(cls, sort_by=None):
        athletes = cls.query.order_by(cls.member_name).all()

        athletes = [TeamMember.translate_row(athlete) for athlete in athletes]
        if sort_by and len(athletes) > 0:
            is_valid_key = sort_by in athletes[0]
            athletes = sorted(athletes, key=lambda athlete: athlete[sort_by]
                              ) if is_valid_key else athletes

        return athletes

    @classmethod
    def for_code(cls, code, order_by='member_name', offset=0, limit=50):
        members = cls.query.filter_by(
            code=code).order_by(order_by).offset(offset).limit(limit).all()
        return {
            'code': code,
            'members': [member.to_api_json() for member in members],
            'name': cls.team_definitions.get(code, code),
        }

    @classmethod
    def translate_row(cls, athlete):
        return {
            'id': athlete.id,
            'name': athlete.member_name,
            'sid': athlete.member_csid,
            'sport': athlete.asc_sport,
            'teamCode': athlete.code,
            'uid': athlete.member_uid,
        }

    @classmethod
    def summarize_team_members(cls,
                               team_codes,
                               order_by='member_name',
                               offset=0,
                               limit=50):
        summary = {
            'teams': [],
        }
        for code in team_codes:
            team = TeamMember.for_code(code)
            summary['teams'].append({
                'code': code,
                'name': team['name'],
            })
        o = TeamMember.member_uid if order_by == 'member_uid' else TeamMember.member_name
        f = TeamMember.code.in_(team_codes)
        results = TeamMember.query.distinct(o).order_by(o).filter(f).offset(
            offset).limit(limit).all()

        summary['members'] = []
        for row in results:
            summary['members'].append(TeamMember.translate_row(row))

        summary['totalMemberCount'] = TeamMember.query.distinct(o).filter(
            f).count()
        db.session.commit()
        return summary

    def to_api_json(self):
        return {
            'name': self.member_name,
            'uid': self.member_uid,
        }
Esempio n. 22
0
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,
        }
Esempio n. 23
0
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
Esempio n. 24
0
class AuthorizedUser(Base):
    __tablename__ = 'authorized_users'

    SEARCH_HISTORY_ITEM_MAX_LENGTH = 256

    id = db.Column(db.Integer, nullable=False, primary_key=True)  # noqa: A003
    uid = db.Column(db.String(255), nullable=False, unique=True)
    is_admin = db.Column(db.Boolean)
    in_demo_mode = db.Column(db.Boolean, nullable=False)
    can_access_advising_data = db.Column(db.Boolean, nullable=False)
    can_access_canvas_data = db.Column(db.Boolean, nullable=False)
    created_by = db.Column(db.String(255), nullable=False)
    degree_progress_permission = db.Column(generic_permission_type_enum)
    deleted_at = db.Column(db.DateTime, nullable=True)
    # When True, is_blocked prevents a deleted user from being revived by the automated refresh.
    is_blocked = db.Column(db.Boolean, nullable=False, default=False)
    search_history = deferred(db.Column(ARRAY(db.String), nullable=True))
    department_memberships = db.relationship(
        'UniversityDeptMember',
        back_populates='authorized_user',
        lazy='joined',
    )
    drop_in_departments = db.relationship(
        'DropInAdvisor',
        back_populates='authorized_user',
        lazy='joined',
    )
    same_day_departments = db.relationship(
        'SameDayAdvisor',
        back_populates='authorized_user',
        lazy='joined',
    )
    scheduler_departments = db.relationship(
        'Scheduler',
        back_populates='authorized_user',
        lazy='joined',
    )
    cohort_filters = db.relationship(
        'CohortFilter',
        back_populates='owner',
        lazy='joined',
    )
    alert_views = db.relationship(
        'AlertView',
        back_populates='viewer',
        lazy='joined',
    )

    def __init__(
            self,
            uid,
            created_by,
            is_admin=False,
            is_blocked=False,
            in_demo_mode=False,
            can_access_advising_data=True,
            can_access_canvas_data=True,
            degree_progress_permission=None,
            search_history=(),
    ):
        self.uid = uid
        self.created_by = created_by
        self.is_admin = is_admin
        self.is_blocked = is_blocked
        self.in_demo_mode = in_demo_mode
        self.can_access_advising_data = can_access_advising_data
        self.can_access_canvas_data = can_access_canvas_data
        self.degree_progress_permission = degree_progress_permission
        self.search_history = search_history

    def __repr__(self):
        return f"""<AuthorizedUser {self.uid},
                    is_admin={self.is_admin},
                    in_demo_mode={self.in_demo_mode},
                    can_access_advising_data={self.can_access_advising_data},
                    can_access_canvas_data={self.can_access_canvas_data},
                    degree_progress_permission={self.degree_progress_permission},
                    search_history={self.search_history},
                    created={self.created_at},
                    created_by={self.created_by},
                    updated={self.updated_at},
                    deleted={self.deleted_at},
                    is_blocked={self.is_blocked}>
                """

    @classmethod
    def delete(cls, uid):
        now = utc_now()
        user = cls.query.filter_by(uid=uid).first()
        user.deleted_at = now
        std_commit()
        return user

    @classmethod
    def un_delete(cls, uid):
        user = cls.query.filter_by(uid=uid).first()
        user.deleted_at = None
        std_commit()
        return user

    @classmethod
    def create_or_restore(
        cls,
        uid,
        created_by,
        is_admin=False,
        is_blocked=False,
        can_access_advising_data=True,
        can_access_canvas_data=True,
        degree_progress_permission=None,
    ):
        existing_user = cls.query.filter_by(uid=uid).first()
        if existing_user:
            if existing_user.is_blocked:
                return False
            # If restoring a previously deleted user, respect passed-in attributes.
            if existing_user.deleted_at:
                existing_user.is_admin = is_admin
                existing_user.is_blocked = is_blocked
                existing_user.can_access_advising_data = can_access_advising_data
                existing_user.can_access_canvas_data = can_access_canvas_data
                existing_user.created_by = created_by
                if not existing_user.degree_progress_permission:
                    existing_user.degree_progress_permission = degree_progress_permission
                existing_user.deleted_at = None
            # If the user currently exists in a non-deleted state, attributes passed in as True
            # should replace existing attributes set to False, but not vice versa.
            else:
                if can_access_advising_data and not existing_user.can_access_advising_data:
                    existing_user.can_access_advising_data = True
                if can_access_canvas_data and not existing_user.can_access_canvas_data:
                    existing_user.can_access_canvas_data = True
                if not existing_user.degree_progress_permission:
                    existing_user.degree_progress_permission = degree_progress_permission
                if is_admin and not existing_user.is_admin:
                    existing_user.is_admin = True
                if is_blocked and not existing_user.is_blocked:
                    existing_user.is_blocked = True
                existing_user.created_by = created_by
            user = existing_user
        else:
            user = cls(
                uid=uid,
                created_by=created_by,
                is_admin=is_admin,
                is_blocked=is_blocked,
                in_demo_mode=False,
                can_access_advising_data=can_access_advising_data,
                can_access_canvas_data=can_access_canvas_data,
                degree_progress_permission=degree_progress_permission,
            )
        db.session.add(user)
        std_commit()
        return user

    @classmethod
    def get_id_per_uid(cls, uid, include_deleted=False):
        sql = 'SELECT id FROM authorized_users WHERE uid = :uid'
        if not include_deleted:
            sql += ' AND deleted_at IS NULL'
        query = text(sql)
        result = db.session.execute(query, {'uid': uid}).first()
        return result and result['id']

    @classmethod
    def get_uid_per_id(cls, user_id):
        query = text(
            'SELECT uid FROM authorized_users WHERE id = :user_id AND deleted_at IS NULL'
        )
        result = db.session.execute(query, {'user_id': user_id}).first()
        return result and result['uid']

    @classmethod
    def find_by_id(cls, user_id, include_deleted=False):
        query = cls.query.filter_by(
            id=user_id) if include_deleted else cls.query.filter_by(
                id=user_id, deleted_at=None)
        return query.first()

    @classmethod
    def users_with_uid_like(cls, uid_snippet, include_deleted=False):
        like_uid_snippet = cls.uid.like(f'%{uid_snippet}%')
        criteria = like_uid_snippet if include_deleted else and_(
            like_uid_snippet, cls.deleted_at == None)  # noqa: E711
        return cls.query.filter(criteria).all()

    @classmethod
    def find_by_uid(cls, uid, ignore_deleted=True):
        query = cls.query.filter_by(
            uid=uid,
            deleted_at=None) if ignore_deleted else cls.query.filter_by(
                uid=uid)
        return query.first()

    @classmethod
    def get_all_active_users(cls, include_deleted=False):
        return cls.query.all() if include_deleted else cls.query.filter_by(
            deleted_at=None).all()

    @classmethod
    def get_admin_users(cls, ignore_deleted=True):
        if ignore_deleted:
            query = cls.query.filter(and_(
                cls.is_admin, cls.deleted_at == None))  # noqa: E711
        else:
            query = cls.query.filter(cls.is_admin)
        return query.all()

    @classmethod
    def add_to_search_history(cls, user_id, search_phrase):
        search_phrase = vacuum_whitespace(search_phrase)
        query = text(
            'SELECT search_history FROM authorized_users WHERE id = :user_id')
        result = db.session.execute(query, {'user_id': user_id}).first()
        if result:
            search_history = result['search_history'] or []
            if len(search_phrase) > cls.SEARCH_HISTORY_ITEM_MAX_LENGTH:
                if ' ' in search_phrase:
                    search_phrase = search_phrase[:cls.
                                                  SEARCH_HISTORY_ITEM_MAX_LENGTH
                                                  + 1]
                    search_phrase = search_phrase[:search_phrase.rindex(' ') +
                                                  1].strip()
                else:
                    search_phrase = search_phrase[:cls.
                                                  SEARCH_HISTORY_ITEM_MAX_LENGTH]
            phrase_lowered = search_phrase.lower()
            for idx, entry in enumerate(search_history):
                if phrase_lowered == entry.lower():
                    del search_history[idx]
            search_history.insert(0, search_phrase)

            max_size = app.config['USER_SEARCH_HISTORY_MAX_SIZE']
            if len(search_history) > max_size:
                del search_history[max_size:]

            sql_text = text(
                'UPDATE authorized_users SET search_history = :history WHERE id = :id'
            )
            db.session.execute(sql_text, {
                'history': search_history,
                'id': user_id
            })
            return cls.get_search_history(user_id)
        else:
            return None

    @classmethod
    def get_search_history(cls, user_id):
        query = text(
            'SELECT search_history FROM authorized_users WHERE id = :id')
        result = db.session.execute(query, {'id': user_id}).first()
        return result and result['search_history']

    @classmethod
    def get_users(
        cls,
        deleted=None,
        blocked=None,
        dept_code=None,
        role=None,
    ):
        query_tables, query_filter, query_bindings = _users_sql(
            blocked=blocked,
            deleted=deleted,
            dept_code=dept_code,
            role=role,
        )
        query = text(f"""
            SELECT u.id
            {query_tables}
            {query_filter}
        """)
        results = db.session.execute(query, query_bindings)
        user_ids = [row['id'] for row in results]
        return cls.query.filter(cls.id.in_(user_ids)).all(), len(user_ids)

    @classmethod
    def get_all_uids_in_scope(cls, scope=()):
        sql = 'SELECT uid FROM authorized_users u '
        if not scope:
            return None
        elif 'ADMIN' in scope:
            sql += 'WHERE u.deleted_at IS NULL'
        else:
            sql += """
                JOIN university_dept_members m ON m.authorized_user_id = u.id
                JOIN university_depts d ON d.id = m.university_dept_id
                WHERE
                d.dept_code = ANY(:scope)
                AND u.deleted_at IS NULL
            """
        results = db.session.execute(sql, {'scope': scope})
        return [row['uid'] for row in results]

    @classmethod
    def update_user(
        cls,
        user_id,
        can_access_advising_data=False,
        can_access_canvas_data=False,
        degree_progress_permission=None,
        is_admin=False,
        is_blocked=False,
        include_deleted=False,
    ):
        user = AuthorizedUser.find_by_id(user_id, include_deleted)
        user.can_access_advising_data = can_access_advising_data
        user.can_access_canvas_data = can_access_canvas_data
        user.degree_progress_permission = degree_progress_permission
        user.is_admin = is_admin
        user.is_blocked = is_blocked
        std_commit()
        return user
Esempio n. 25
0
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
Esempio n. 26
0
class Alert(Base):
    __tablename__ = 'alerts'

    id = db.Column(db.Integer, nullable=False, primary_key=True)  # noqa: A003
    sid = db.Column(db.String(80), nullable=False)
    alert_type = db.Column(db.String(80), nullable=False)
    key = db.Column(db.String(255), nullable=False)
    message = db.Column(db.Text, nullable=False)
    deleted_at = db.Column(db.DateTime)
    views = db.relationship(
        'AlertView',
        back_populates='alert',
        lazy=True,
    )

    __table_args__ = (db.UniqueConstraint(
        'sid',
        'alert_type',
        'key',
        'created_at',
        name='alerts_sid_alert_type_key_created_at_unique_constraint',
    ), )

    @classmethod
    def create(cls,
               sid,
               alert_type,
               key=None,
               message=None,
               deleted_at=None,
               created_at=None):
        # Alerts must contain a key, unique per SID and alert type, which will allow them to be located
        # and modified on updates to the data that originally generated the alert. The key defaults
        # to a string representation of today's date, but will more often (depending on the alert type)
        # contain a reference to a related resource, such as a course or assignment id.
        if key is None:
            key = datetime.now().strftime('%Y-%m-%d')
        else:
            # If we get a blank string as key, deliver a stern warning to the code that submitted it.
            key = key.strip()
            if not key:
                raise ValueError('Blank string submitted for alert key')
        alert = cls(sid, alert_type, key, message, deleted_at)
        if created_at:
            alert.created_at = created_at
            alert.updated_at = created_at
        db.session.add(alert)
        std_commit()

    def __init__(self, sid, alert_type, key, message=None, deleted_at=None):
        self.sid = sid
        self.alert_type = alert_type
        self.key = key
        self.message = message
        self.deleted_at = deleted_at

    def __repr__(self):
        return f"""<Alert {self.id},
                    sid={self.sid},
                    alert_type={self.alert_type},
                    key={self.key},
                    message={self.message},
                    deleted_at={self.deleted_at},
                    updated={self.updated_at},
                    created={self.created_at}>
                """

    @classmethod
    def dismiss(cls, alert_id, viewer_id):
        alert = cls.query.filter_by(id=alert_id).first()
        if alert:
            alert_view = AlertView.query.filter_by(viewer_id=viewer_id,
                                                   alert_id=alert_id).first()
            if alert_view:
                alert_view.dismissed_at = datetime.now()
            else:
                db.session.add(
                    AlertView(viewer_id=viewer_id,
                              alert_id=alert_id,
                              dismissed_at=datetime.now()))
            std_commit()
        else:
            raise BadRequestError(f'No alert found for id {alert_id}')

    @classmethod
    def current_alert_counts_for_viewer(cls, viewer_id):
        query = """
            SELECT alerts.sid, count(*) as alert_count
            FROM alerts LEFT JOIN alert_views
                ON alert_views.alert_id = alerts.id
                AND alert_views.viewer_id = :viewer_id
            WHERE alerts.deleted_at IS NULL
                AND alerts.key LIKE :key
                AND alert_views.dismissed_at IS NULL
            GROUP BY alerts.sid
        """
        params = {'viewer_id': viewer_id, 'key': current_term_id() + '_%'}
        return cls.alert_counts_by_query(query, params)

    @classmethod
    def current_alert_counts_for_sids(cls,
                                      viewer_id,
                                      sids,
                                      count_only=False,
                                      offset=None,
                                      limit=None):
        query = """
            SELECT alerts.sid, count(*) as alert_count
            FROM alerts LEFT JOIN alert_views
                ON alert_views.alert_id = alerts.id
                AND alert_views.viewer_id = :viewer_id
            WHERE alerts.deleted_at IS NULL
                AND alerts.key LIKE :key
                AND alerts.sid = ANY(:sids)
                AND alert_views.dismissed_at IS NULL
            GROUP BY alerts.sid
            ORDER BY alert_count DESC, alerts.sid
        """
        if offset:
            query += ' OFFSET :offset'
        if limit:
            query += ' LIMIT :limit'
        params = {
            'viewer_id': viewer_id,
            'key': current_term_id() + '_%',
            'sids': sids,
            'offset': offset,
            'limit': limit,
        }
        return cls.alert_counts_by_query(query, params, count_only=count_only)

    @classmethod
    def alert_counts_by_query(cls, query, params, count_only=False):
        results = db.session.execute(text(query), params)

        # If we're only interested in the alert count, skip the student data fetch below.
        if count_only:
            return [{
                'sid': row['sid'],
                'alertCount': row['alert_count']
            } for row in results]

        alert_counts_by_sid = {
            row['sid']: row['alert_count']
            for row in results
        }
        sids = list(alert_counts_by_sid.keys())

        def result_to_dict(result):
            result_dict = {
                'sid': result.get('sid'),
                'uid': result.get('uid'),
                'firstName': result.get('first_name'),
                'lastName': result.get('last_name'),
                'alertCount': alert_counts_by_sid.get(result.get('sid')),
            }
            return result_dict

        return [
            result_to_dict(result)
            for result in data_loch.get_basic_student_data(sids)
        ]

    @classmethod
    def current_alerts_for_sid(cls, viewer_id, sid):
        query = text("""
            SELECT alerts.*, alert_views.dismissed_at
            FROM alerts LEFT JOIN alert_views
                ON alert_views.alert_id = alerts.id
                AND alert_views.viewer_id = :viewer_id
            WHERE alerts.deleted_at IS NULL
                AND alerts.key LIKE :key
                AND alerts.sid = :sid
            ORDER BY alerts.created_at
        """)
        results = db.session.execute(query, {
            'viewer_id': viewer_id,
            'key': current_term_id() + '_%',
            'sid': sid
        })
        feed = []

        def result_to_dict(result):
            return {
                camelize(key): result[key]
                for key in ['id', 'alert_type', 'key', 'message']
            }

        for result in results:
            dismissed_at = result['dismissed_at']
            alert = {
                **result_to_dict(result),
                **{
                    'dismissed':
                    dismissed_at and dismissed_at.strftime('%Y-%m-%d %H:%M:%S'),
                    'createdAt':
                    result['created_at'].strftime('%Y-%m-%d %H:%M:%S'),
                    'updatedAt':
                    result['updated_at'].strftime('%Y-%m-%d %H:%M:%S'),
                },
            }
            feed.append(alert)
        return feed

    def activate(self, preserve_creation_date=False):
        self.deleted_at = None
        # Some alert types, such as withdrawals and midpoint deficient grades, don't include a time-shifted message
        # and shouldn't be treated as updated after creation.
        if preserve_creation_date:
            self.updated_at = self.created_at
        std_commit()

    def deactivate(self):
        self.deleted_at = datetime.now()
        std_commit()

    @classmethod
    def create_or_activate(
        cls,
        alert_type,
        key,
        message,
        sid,
        created_at=None,
        force_use_existing=False,
        preserve_creation_date=False,
    ):
        # If any previous alerts exist with the same type, key and sid, grab the most recently updated one.
        existing_alert = cls.query.filter_by(sid=sid,
                                             alert_type=alert_type,
                                             key=key).order_by(
                                                 desc(cls.updated_at)).first()
        # If the existing alert was only just deactivated in the last two hours, assume that the deactivation was part of the
        # current refresh cycle, and go ahead and reactivate it. But if the alert was deactivated farther back in the past,
        # assume that it represents a previous state of affairs, and create a new alert for current conditions.
        if existing_alert and (force_use_existing or
                               (datetime.now(timezone.utc) -
                                existing_alert.updated_at).total_seconds() <
                               (2 * 3600)):
            existing_alert.message = message
            existing_alert.activate(
                preserve_creation_date=preserve_creation_date)
        else:
            cls.create(
                alert_type=alert_type,
                created_at=created_at,
                key=key,
                message=message,
                sid=sid,
            )

    @classmethod
    def deactivate_all(cls, sid, term_id, alert_types):
        query = (
            cls.query.filter(cls.sid == sid).filter(
                cls.alert_type.in_(alert_types)).filter(
                    cls.key.startswith(f'{term_id}_%')).filter(
                        cls.deleted_at == None)  # noqa: E711
        )
        results = query.update({cls.deleted_at: datetime.now()},
                               synchronize_session='fetch')
        std_commit()
        return results

    @classmethod
    def get_alerts_per_date_range(cls, from_date_utc, to_date_utc):
        criterion = and_(
            cls.created_at >= from_date_utc,
            cls.created_at <= to_date_utc,
        )
        return cls.query.filter(criterion).order_by(cls.created_at).all()

    @classmethod
    def infrequent_activity_alerts_enabled(cls):
        if not app.config['ALERT_INFREQUENT_ACTIVITY_ENABLED']:
            return False
        if current_term_name().startswith('Summer'):
            return False
        days_into_session = (datetime.date(datetime.today()) -
                             _get_current_term_start()).days
        return days_into_session >= app.config['ALERT_INFREQUENT_ACTIVITY_DAYS']

    @classmethod
    def no_activity_alerts_enabled(cls):
        if not app.config['ALERT_NO_ACTIVITY_ENABLED']:
            return False
        if current_term_name().startswith('Summer'):
            return False
        days_into_session = (datetime.date(datetime.today()) -
                             _get_current_term_start()).days
        return days_into_session >= app.config[
            'ALERT_NO_ACTIVITY_DAYS_INTO_SESSION']

    @classmethod
    def deactivate_all_for_term(cls, term_id):
        query = (
            cls.query.filter(cls.key.startswith(f'{term_id}_%')).filter(
                cls.deleted_at == None)  # noqa: E711
        )
        results = query.update({cls.deleted_at: datetime.now()},
                               synchronize_session='fetch')
        std_commit()
        return results

    @classmethod
    def update_all_for_term(cls, term_id):
        app.logger.info('Starting alert update')
        enrollments_for_term = data_loch.get_enrollments_for_term(str(term_id))
        no_activity_alerts_enabled = cls.no_activity_alerts_enabled()
        infrequent_activity_alerts_enabled = cls.infrequent_activity_alerts_enabled(
        )
        for row in enrollments_for_term:
            enrollments = json.loads(row['enrollment_term']).get(
                'enrollments', [])
            for enrollment in enrollments:
                cls.update_alerts_for_enrollment(
                    sid=row['sid'],
                    term_id=term_id,
                    enrollment=enrollment,
                    no_activity_alerts_enabled=no_activity_alerts_enabled,
                    infrequent_activity_alerts_enabled=
                    infrequent_activity_alerts_enabled,
                )
        profiles = data_loch.get_student_profiles()
        if app.config['ALERT_WITHDRAWAL_ENABLED'] and str(
                term_id) == current_term_id():
            for row in profiles:
                profile_feed = json.loads(row['profile'])
                if 'withdrawalCancel' in (profile_feed.get('sisProfile')
                                          or {}):
                    cls.update_withdrawal_cancel_alerts(row['sid'], term_id)

        sids = [p['sid'] for p in profiles]
        for sid, academic_standing_list in get_academic_standing_by_sid(
                sids).items():
            standing = next((s for s in academic_standing_list
                             if s['termId'] == str(term_id)), None)
            if standing and standing['status'] in ('DIS', 'PRO', 'SUB'):
                cls.update_academic_standing_alerts(
                    action_date=standing['actionDate'],
                    sid=standing['sid'],
                    status=standing['status'],
                    term_id=term_id,
                )
        app.logger.info('Alert update complete')

    @classmethod
    def update_academic_standing_alerts(cls, action_date, sid, status,
                                        term_id):
        key = f'{term_id}_{action_date}_academic_standing_{status}'
        status_description = ACADEMIC_STANDING_DESCRIPTIONS.get(status, status)
        message = f"Student's academic standing is '{status_description}'."
        datetime.strptime(action_date, '%Y-%m-%d')
        cls.create_or_activate(
            alert_type='academic_standing',
            created_at=action_date,
            force_use_existing=True,
            key=key,
            message=message,
            preserve_creation_date=True,
            sid=sid,
        )

    @classmethod
    def update_alerts_for_enrollment(cls, sid, term_id, enrollment,
                                     no_activity_alerts_enabled,
                                     infrequent_activity_alerts_enabled):
        for section in enrollment['sections']:
            if section_is_eligible_for_alerts(enrollment=enrollment,
                                              section=section):
                # If the grade is in, what's done is done.
                if section.get('grade'):
                    continue
                if section.get('midtermGrade'):
                    cls.update_midterm_grade_alerts(sid, term_id,
                                                    section['ccn'],
                                                    enrollment['displayName'],
                                                    section['midtermGrade'])
                last_activity = None
                activity_percentile = None
                for canvas_site in enrollment.get('canvasSites', []):
                    student_activity = canvas_site.get('analytics', {}).get(
                        'lastActivity', {}).get('student')
                    if not student_activity or student_activity.get(
                            'roundedUpPercentile') is None:
                        continue
                    raw_epoch = student_activity.get('raw')
                    if last_activity is None or raw_epoch > last_activity:
                        last_activity = raw_epoch
                        activity_percentile = student_activity.get(
                            'roundedUpPercentile')
                if last_activity is None:
                    continue
                if (no_activity_alerts_enabled and last_activity == 0
                        and activity_percentile <=
                        app.config['ALERT_NO_ACTIVITY_PERCENTILE_CUTOFF']):
                    cls.update_no_activity_alerts(sid, term_id,
                                                  enrollment['displayName'])
                elif (infrequent_activity_alerts_enabled
                      and last_activity > 0):
                    localized_last_activity = unix_timestamp_to_localtime(
                        last_activity).date()
                    localized_today = unix_timestamp_to_localtime(
                        time.time()).date()
                    days_since = (localized_today -
                                  localized_last_activity).days
                    if (days_since >=
                            app.config['ALERT_INFREQUENT_ACTIVITY_DAYS']
                            and activity_percentile <= app.config[
                                'ALERT_INFREQUENT_ACTIVITY_PERCENTILE_CUTOFF']
                        ):
                        cls.update_infrequent_activity_alerts(
                            sid,
                            term_id,
                            enrollment['displayName'],
                            days_since,
                        )

    @classmethod
    def update_assignment_alerts(cls, sid, term_id, assignment_id, due_at,
                                 status, course_site_name):
        alert_type = status + '_assignment'
        key = f'{term_id}_{assignment_id}'
        due_at_date = utc_timestamp_to_localtime(due_at).strftime('%b %-d, %Y')
        message = f'{course_site_name} assignment due on {due_at_date}.'
        cls.create_or_activate(sid=sid,
                               alert_type=alert_type,
                               key=key,
                               message=message)

    @classmethod
    def update_midterm_grade_alerts(cls, sid, term_id, section_id, class_name,
                                    grade):
        key = f'{term_id}_{section_id}'
        message = f'{class_name} midpoint deficient grade of {grade}.'
        cls.create_or_activate(sid=sid,
                               alert_type='midterm',
                               key=key,
                               message=message,
                               preserve_creation_date=True)

    @classmethod
    def update_no_activity_alerts(cls, sid, term_id, class_name):
        key = f'{term_id}_{class_name}'
        message = f'No activity! Student has never visited the {class_name} bCourses site for {term_name_for_sis_id(term_id)}.'
        cls.create_or_activate(sid=sid,
                               alert_type='no_activity',
                               key=key,
                               message=message)

    @classmethod
    def update_infrequent_activity_alerts(cls, sid, term_id, class_name,
                                          days_since):
        key = f'{term_id}_{class_name}'
        message = f'Infrequent activity! Last {class_name} bCourses activity was {days_since} days ago.'
        # If an active infrequent activity alert already exists and is more recent, skip the update.
        existing_alert = cls.query.filter_by(sid=sid,
                                             alert_type='infrequent_activity',
                                             key=key,
                                             deleted_at=None).first()
        if existing_alert:
            match = re.search('(\d+) days ago.$', message)
            if match and match[1] and int(match[1]) < days_since:
                return
        cls.create_or_activate(sid=sid,
                               alert_type='infrequent_activity',
                               key=key,
                               message=message)

    @classmethod
    def update_withdrawal_cancel_alerts(cls, sid, term_id):
        key = f'{term_id}_withdrawal'
        message = f'Student is no longer enrolled in the {term_name_for_sis_id(term_id)} term.'
        cls.create_or_activate(sid=sid,
                               alert_type='withdrawal',
                               key=key,
                               message=message,
                               preserve_creation_date=True)

    @classmethod
    def include_alert_counts_for_students(cls,
                                          viewer_user_id,
                                          group,
                                          count_only=False,
                                          offset=None,
                                          limit=None):
        sids = group.get('sids') if 'sids' in group else [
            s['sid'] for s in group.get('students', [])
        ]
        alert_counts = cls.current_alert_counts_for_sids(viewer_user_id,
                                                         sids,
                                                         count_only=count_only,
                                                         offset=offset,
                                                         limit=limit)
        if 'students' in group:
            counts_per_sid = {
                s.get('sid'): s.get('alertCount')
                for s in alert_counts
            }
            for student in group.get('students'):
                sid = student['sid']
                student['alertCount'] = counts_per_sid.get(
                    sid) if sid in counts_per_sid else 0
        return alert_counts
Esempio n. 27
0
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),
        }
Esempio n. 28
0
class AuthorizedUser(Base):
    __tablename__ = 'authorized_users'

    id = db.Column(db.Integer, nullable=False, primary_key=True)  # noqa: A003
    uid = db.Column(db.String(255), nullable=False, unique=True)
    is_admin = db.Column(db.Boolean)
    in_demo_mode = db.Column(db.Boolean, nullable=False)
    deleted_at = db.Column(db.DateTime, nullable=True)
    department_memberships = db.relationship(
        'UniversityDeptMember',
        back_populates='authorized_user',
        lazy='joined',
    )
    cohort_filters = db.relationship(
        'CohortFilter',
        secondary=cohort_filter_owners,
        back_populates='owners',
        lazy='joined',
    )
    alert_views = db.relationship(
        'AlertView',
        back_populates='viewer',
        lazy='joined',
    )

    def __init__(self, uid, is_admin=False, in_demo_mode=False):
        self.uid = uid
        self.is_admin = is_admin
        self.in_demo_mode = in_demo_mode

    def __repr__(self):
        return f"""<AuthorizedUser {self.uid},
                    is_admin={self.is_admin},
                    in_demo_mode={self.in_demo_mode},
                    updated={self.updated_at},
                    created={self.created_at},
                    deleted={self.deleted_at}>
                """

    @classmethod
    def create_or_restore(cls, uid, is_admin=False, in_demo_mode=False):
        existing_user = cls.query.filter_by(uid=uid).first()
        if existing_user:
            existing_user.deleted_at = None
            return existing_user
        else:
            return cls(uid=uid, is_admin=is_admin, in_demo_mode=in_demo_mode)

    @classmethod
    def get_id_per_uid(cls, uid):
        query = text(
            f'SELECT id FROM authorized_users WHERE uid = :uid AND deleted_at IS NULL'
        )
        result = db.session.execute(query, {'uid': uid}).first()
        return result and result['id']

    @classmethod
    def find_by_id(cls, db_id):
        return AuthorizedUser.query.filter_by(id=db_id,
                                              deleted_at=None).first()

    @classmethod
    def find_by_uid(cls, uid):
        return AuthorizedUser.query.filter_by(uid=uid, deleted_at=None).first()

    @classmethod
    def get_all_active_users(cls):
        return cls.query.filter_by(deleted_at=None).all()

    @classmethod
    def get_all_uids_in_scope(cls, scope=()):
        sql = 'SELECT uid FROM authorized_users u '
        if not scope:
            return None
        elif 'ADMIN' in scope:
            sql += 'WHERE u.deleted_at IS NULL'
        else:
            sql += """
                JOIN university_dept_members m ON m.authorized_user_id = u.id
                JOIN university_depts d ON d.id = m.university_dept_id
                WHERE
                d.dept_code = ANY(:scope)
                AND u.deleted_at IS NULL
            """
        results = db.session.execute(sql, {'scope': scope})
        return [row['uid'] for row in results]
Esempio n. 29
0
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),
        }
Esempio n. 30
0
class CohortFilter(Base):
    __tablename__ = 'cohort_filters'

    id = db.Column(db.Integer, nullable=False, primary_key=True)  # noqa: A003
    name = db.Column(db.String(255), nullable=False)
    filter_criteria = db.Column(JSONB, nullable=False)
    # Fetching a large array literal from Postgres can be expensive. We defer until invoking code demands it.
    sids = deferred(db.Column(ARRAY(db.String(80))))
    student_count = db.Column(db.Integer)
    alert_count = db.Column(db.Integer)
    owners = db.relationship('AuthorizedUser',
                             secondary=cohort_filter_owners,
                             back_populates='cohort_filters')

    def __init__(self, name, filter_criteria):
        self.name = name
        self.filter_criteria = filter_criteria

    def __repr__(self):
        return f"""<CohortFilter {self.id},
            name={self.name},
            owners={self.owners},
            filter_criteria={self.filter_criteria},
            sids={self.sids},
            student_count={self.student_count},
            alert_count={self.alert_count},
            updated_at={self.updated_at},
            created_at={self.created_at}>"""

    @classmethod
    def create(cls, uid, name, filter_criteria, **kwargs):
        if all(not isinstance(value, bool) and not value
               for value in filter_criteria.values()):
            raise InternalServerError(
                'Cohort creation requires at least one filter specification.')
        cohort = cls(name=name, filter_criteria=filter_criteria)
        user = AuthorizedUser.find_by_uid(uid)
        user.cohort_filters.append(cohort)
        db.session.flush()
        std_commit()
        return cohort.to_api_json(**kwargs)

    @classmethod
    def update(cls,
               cohort_id,
               name=None,
               filter_criteria=None,
               alert_count=None,
               **kwargs):
        cohort = cls.query.filter_by(id=cohort_id).first()
        if name:
            cohort.name = name
        if filter_criteria:
            cohort.filter_criteria = filter_criteria
        cohort.sids = None
        cohort.student_count = None
        if alert_count is not None:
            cohort.alert_count = alert_count
        else:
            # Alert count will be refreshed
            cohort.update_alert_count(None)
        std_commit()
        return cohort.to_api_json(**kwargs)

    @classmethod
    def get_sids(cls, cohort_id):
        query = db.session.query(cls).options(undefer('sids'))
        cohort = query.filter_by(id=cohort_id).first()
        return cohort and cohort.sids

    def update_sids_and_student_count(self, sids, student_count):
        self.sids = sids
        self.student_count = student_count
        std_commit()
        return self

    def update_alert_count(self, count):
        self.alert_count = count
        std_commit()
        return self

    @classmethod
    def share(cls, cohort_id, user_id):
        cohort = cls.query.filter_by(id=cohort_id).first()
        user = AuthorizedUser.find_by_uid(user_id)
        user.cohort_filters.append(cohort)
        std_commit()

    @classmethod
    def get_cohorts_of_user_id(cls, user_id):
        query = text(f"""
            SELECT id, name, filter_criteria, alert_count, student_count FROM cohort_filters c
            LEFT JOIN cohort_filter_owners o ON o.cohort_filter_id = c.id
            WHERE o.user_id = :user_id
            ORDER BY c.name
        """)
        results = db.session.execute(query, {'user_id': user_id})

        def transform(row):
            return {
                'id': row['id'],
                'name': row['name'],
                'criteria': row['filter_criteria'],
                'alertCount': row['alert_count'],
                'totalStudentCount': row['student_count'],
            }

        return [transform(row) for row in results]

    @classmethod
    def get_cohorts_owned_by_uids(cls, uids):
        query = text(f"""
            SELECT c.id, c.name, c.filter_criteria, c.alert_count, c.student_count, ARRAY_AGG(uid) authorized_users
            FROM cohort_filters c
            INNER JOIN cohort_filter_owners o ON c.id = o.cohort_filter_id
            INNER JOIN authorized_users u ON o.user_id = u.id
            WHERE u.uid = ANY(:uids)
            GROUP BY c.id, c.name, c.filter_criteria, c.alert_count, c.student_count
        """)
        results = db.session.execute(query, {'uids': uids})

        def transform(row):
            return {
                'id': row['id'],
                'name': row['name'],
                'criteria': row['filter_criteria'],
                'owners': row['authorized_users'],
                'alertCount': row['alert_count'],
                'totalStudentCount': row['student_count'],
            }

        return [transform(row) for row in results]

    @classmethod
    def is_cohort_owned_by(cls, cohort_id, user_id):
        query = text(f"""
            SELECT count(*) FROM cohort_filters c
            LEFT JOIN cohort_filter_owners o ON o.cohort_filter_id = c.id
            WHERE o.user_id = :user_id AND c.id = :cohort_id
        """)
        results = db.session.execute(
            query,
            {
                'cohort_id': cohort_id,
                'user_id': user_id,
            },
        )
        return results.first()['count']

    @classmethod
    def refresh_alert_counts_for_owner(cls, owner_id):
        query = text(f"""
            UPDATE cohort_filters
            SET alert_count = updated_cohort_counts.alert_count
            FROM
            (
                SELECT cohort_filters.id AS cohort_filter_id, count(*) AS alert_count
                FROM alerts
                JOIN cohort_filters
                    ON alerts.sid = ANY(cohort_filters.sids)
                    AND alerts.key LIKE :key
                    AND alerts.active IS TRUE
                JOIN cohort_filter_owners
                    ON cohort_filters.id = cohort_filter_owners.cohort_filter_id
                    AND cohort_filter_owners.user_id = :owner_id
                LEFT JOIN alert_views
                    ON alert_views.alert_id = alerts.id
                    AND alert_views.viewer_id = :owner_id
                WHERE alert_views.dismissed_at IS NULL
                GROUP BY cohort_filters.id
            ) updated_cohort_counts
            WHERE cohort_filters.id = updated_cohort_counts.cohort_filter_id
        """)
        result = db.session.execute(query, {
            'owner_id': owner_id,
            'key': current_term_id() + '_%'
        })
        std_commit()
        return result

    @classmethod
    def find_by_id(cls, cohort_id, **kwargs):
        cohort = cls.query.filter_by(id=cohort_id).first()
        return cohort and cohort.to_api_json(**kwargs)

    @classmethod
    def delete(cls, cohort_id):
        cohort_filter = cls.query.filter_by(id=cohort_id).first()
        db.session.delete(cohort_filter)
        std_commit()

    def to_api_json(
        self,
        order_by=None,
        offset=0,
        limit=50,
        alert_offset=None,
        alert_limit=None,
        include_sids=False,
        include_students=True,
        include_profiles=False,
        include_alerts_for_user_id=None,
    ):
        benchmark = get_benchmarker(f'CohortFilter {self.id} to_api_json')
        benchmark('begin')
        c = self.filter_criteria
        c = c if isinstance(c, dict) else json.loads(c)
        coe_advisor_ldap_uids = util.get(c, 'coeAdvisorLdapUids')
        if not isinstance(coe_advisor_ldap_uids, list):
            coe_advisor_ldap_uids = [coe_advisor_ldap_uids
                                     ] if coe_advisor_ldap_uids else None
        cohort_name = self.name
        cohort_json = {
            'id': self.id,
            'code': self.id,
            'name': cohort_name,
            'owners': [],
            'alertCount': self.alert_count,
        }
        for owner in self.owners:
            cohort_json['owners'].append({
                'uid':
                owner.uid,
                'deptCodes': [
                    m.university_dept.dept_code
                    for m in owner.department_memberships
                ],
            })
        coe_ethnicities = c.get('coeEthnicities')
        coe_genders = c.get('coeGenders')
        coe_prep_statuses = c.get('coePrepStatuses')
        coe_probation = util.to_bool_or_none(c.get('coeProbation'))
        coe_underrepresented = util.to_bool_or_none(
            c.get('coeUnderrepresented'))
        cohort_owner_academic_plans = util.get(c, 'cohortOwnerAcademicPlans')
        entering_terms = c.get('enteringTerms')
        ethnicities = c.get('ethnicities')
        expected_grad_terms = c.get('expectedGradTerms')
        genders = c.get('genders')
        gpa_ranges = c.get('gpaRanges')
        group_codes = c.get('groupCodes')
        in_intensive_cohort = util.to_bool_or_none(c.get('inIntensiveCohort'))
        is_inactive_asc = util.to_bool_or_none(c.get('isInactiveAsc'))
        is_inactive_coe = util.to_bool_or_none(c.get('isInactiveCoe'))
        last_name_ranges = c.get('lastNameRanges')
        last_term_gpa_ranges = c.get('lastTermGpaRanges')
        levels = c.get('levels')
        majors = c.get('majors')
        midpoint_deficient_grade = util.to_bool_or_none(
            c.get('midpointDeficient'))
        team_groups = athletics.get_team_groups(
            group_codes) if group_codes else []
        transfer = util.to_bool_or_none(c.get('transfer'))
        underrepresented = util.to_bool_or_none(c.get('underrepresented'))
        unit_ranges = c.get('unitRanges')
        cohort_json.update({
            'criteria': {
                'coeAdvisorLdapUids': coe_advisor_ldap_uids,
                'coeEthnicities': coe_ethnicities,
                'coeGenders': coe_genders,
                'coePrepStatuses': coe_prep_statuses,
                'coeProbation': coe_probation,
                'coeUnderrepresented': coe_underrepresented,
                'cohortOwnerAcademicPlans': cohort_owner_academic_plans,
                'enteringTerms': entering_terms,
                'ethnicities': ethnicities,
                'expectedGradTerms': expected_grad_terms,
                'genders': genders,
                'gpaRanges': gpa_ranges,
                'groupCodes': group_codes,
                'inIntensiveCohort': in_intensive_cohort,
                'isInactiveAsc': is_inactive_asc,
                'isInactiveCoe': is_inactive_coe,
                'lastNameRanges': last_name_ranges,
                'lastTermGpaRanges': last_term_gpa_ranges,
                'levels': levels,
                'majors': majors,
                'midpointDeficient': midpoint_deficient_grade,
                'transfer': transfer,
                'unitRanges': unit_ranges,
                'underrepresented': underrepresented,
            },
            'teamGroups': team_groups,
        })
        if not include_students and not include_alerts_for_user_id and self.student_count is not None:
            # No need for a students query; return the database-stashed student count.
            cohort_json.update({
                'totalStudentCount': self.student_count,
            })
            benchmark('end')
            return cohort_json

        benchmark('begin students query')
        sids_only = not include_students

        # Translate the "My Students" filter, if present, into queryable criteria. Although our database relationships allow
        # for multiple cohort owners, we assume a single owner here since the "My Students" filter makes no sense
        # in any other scenario.
        if cohort_owner_academic_plans:
            if self.owners:
                owner_sid = get_csid_for_uid(app, self.owners[0].uid)
            else:
                owner_sid = current_user.get_csid()
            advisor_plan_mappings = [{
                'advisor_sid': owner_sid,
                'academic_plan_code': plan
            } for plan in cohort_owner_academic_plans]
        else:
            advisor_plan_mappings = None

        results = query_students(
            advisor_plan_mappings=advisor_plan_mappings,
            coe_advisor_ldap_uids=coe_advisor_ldap_uids,
            coe_ethnicities=coe_ethnicities,
            coe_genders=coe_genders,
            coe_prep_statuses=coe_prep_statuses,
            coe_probation=coe_probation,
            coe_underrepresented=coe_underrepresented,
            entering_terms=entering_terms,
            ethnicities=ethnicities,
            expected_grad_terms=expected_grad_terms,
            genders=genders,
            gpa_ranges=gpa_ranges,
            group_codes=group_codes,
            in_intensive_cohort=in_intensive_cohort,
            include_profiles=(include_students and include_profiles),
            is_active_asc=None
            if is_inactive_asc is None else not is_inactive_asc,
            is_active_coe=None
            if is_inactive_coe is None else not is_inactive_coe,
            last_name_ranges=last_name_ranges,
            last_term_gpa_ranges=last_term_gpa_ranges,
            levels=levels,
            limit=limit,
            majors=majors,
            midpoint_deficient_grade=midpoint_deficient_grade,
            offset=offset,
            order_by=order_by,
            sids_only=sids_only,
            transfer=transfer,
            underrepresented=underrepresented,
            unit_ranges=unit_ranges,
        )
        benchmark('end students query')

        if results:
            # Cohort might have tens of thousands of SIDs.
            if include_sids:
                cohort_json['sids'] = results['sids']
            cohort_json.update({
                'totalStudentCount':
                results['totalStudentCount'],
            })
            # If the cohort is new or cache refresh is underway then store student_count and sids in the db.
            if self.student_count is None:
                self.update_sids_and_student_count(
                    results['sids'], results['totalStudentCount'])
            if include_students:
                cohort_json.update({
                    'students': results['students'],
                })
            if include_alerts_for_user_id:
                benchmark('begin alerts query')
                alert_count_per_sid = Alert.include_alert_counts_for_students(
                    viewer_user_id=include_alerts_for_user_id,
                    group=results,
                    offset=alert_offset,
                    limit=alert_limit,
                )
                benchmark('end alerts query')
                cohort_json.update({
                    'alerts': alert_count_per_sid,
                })
                if self.alert_count is None:
                    alert_count = sum(student['alertCount']
                                      for student in alert_count_per_sid)
                    self.update_alert_count(alert_count)
                    cohort_json.update({
                        'alertCount': alert_count,
                    })
        benchmark('end')
        return cohort_json