class NoteTemplateAttachment(db.Model): __tablename__ = 'note_template_attachments' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 note_template_id = db.Column(db.Integer, db.ForeignKey('note_templates.id'), nullable=False) path_to_attachment = db.Column('path_to_attachment', db.String(255), nullable=False) uploaded_by_uid = db.Column('uploaded_by_uid', db.String(255), nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) deleted_at = db.Column(db.DateTime) note_template = db.relationship('NoteTemplate', back_populates='attachments') __table_args__ = ( db.UniqueConstraint( 'note_template_id', 'path_to_attachment', # Constraint name length is limited to 63 bytes in Postgres so we abbreviate the prefix. name='nta_note_template_id_path_to_attachment_unique_constraint', ), ) def __init__(self, note_template_id, path_to_attachment, uploaded_by_uid): self.note_template_id = note_template_id self.path_to_attachment = path_to_attachment self.uploaded_by_uid = uploaded_by_uid def get_user_filename(self): return get_attachment_filename(self.id, self.path_to_attachment) @classmethod def find_by_id(cls, attachment_id): return cls.query.filter( and_(cls.id == attachment_id, cls.deleted_at == None)).first() # noqa: E711 @classmethod def get_attachments(cls, attachment_ids): return cls.query.filter( and_(cls.id.in_(attachment_ids), cls.deleted_at == None)).all() # noqa: E711 @classmethod def create(cls, note_template_id, name, byte_stream, uploaded_by): return NoteTemplateAttachment( note_template_id=note_template_id, path_to_attachment=put_attachment_to_s3(name=name, byte_stream=byte_stream), uploaded_by_uid=uploaded_by, ) def to_api_json(self): return note_attachment_to_api_json(self)
class AppointmentRead(db.Model): __tablename__ = 'appointments_read' viewer_id = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False, primary_key=True) appointment_id = db.Column(db.Integer, db.ForeignKey('appointments.id'), nullable=False, primary_key=True) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) __table_args__ = (db.UniqueConstraint( 'viewer_id', 'appointment_id', name='appointments_read_viewer_id_appointment_id_unique_constraint', ), ) def __init__(self, viewer_id, appointment_id): self.viewer_id = viewer_id self.appointment_id = appointment_id @classmethod def find_or_create(cls, viewer_id, appointment_id): appointment_read = cls.query.filter( and_(cls.viewer_id == viewer_id, cls.appointment_id == str(appointment_id))).one_or_none() if not appointment_read: appointment_read = cls(viewer_id, appointment_id) db.session.add(appointment_read) std_commit() return appointment_read @classmethod def was_read_by(cls, viewer_id, appointment_id): appointment_read = cls.query.filter( AppointmentRead.viewer_id == viewer_id, AppointmentRead.appointment_id == appointment_id, ).first() return appointment_read is not None @classmethod def when_user_read_appointment(cls, viewer_id, appointment_id): appointment_read = cls.query.filter( AppointmentRead.viewer_id == viewer_id, AppointmentRead.appointment_id == appointment_id, ).first() return appointment_read and appointment_read.created_at def to_api_json(self): return { 'appointmentId': self.appointment_id, 'createdAt': _isoformat(self.created_at), 'viewerId': self.viewer_id, }
class UniversityDept(Base): __tablename__ = 'university_depts' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 dept_code = db.Column(db.String(80), nullable=False) dept_name = db.Column(db.String(255), nullable=False) authorized_users = db.relationship( 'UniversityDeptMember', back_populates='university_dept', ) automate_memberships = db.Column(db.Boolean, nullable=False, default=False) __table_args__ = (db.UniqueConstraint( 'dept_code', 'dept_name', name='university_depts_code_unique_constraint'), ) def __init__(self, dept_code, dept_name, automate_memberships): self.dept_code = dept_code self.dept_name = dept_name self.automate_memberships = automate_memberships @classmethod def find_by_dept_code(cls, dept_code): return cls.query.filter_by(dept_code=dept_code).first() @classmethod def create(cls, dept_code, dept_name, automate_memberships): dept = cls(dept_code=dept_code, dept_name=dept_name, automate_memberships=automate_memberships) db.session.add(dept) std_commit() return dept def delete_all_members(self): sql = """ DELETE FROM university_dept_members WHERE university_dept_id = :id; UPDATE authorized_users SET deleted_at = now() WHERE is_admin IS FALSE AND deleted_at IS NULL AND id NOT IN (SELECT authorized_user_id FROM university_dept_members);""" db.session.execute(text(sql), {'id': self.id}) std_commit() def memberships_from_loch(self): program_affiliations = BERKELEY_DEPT_CODE_TO_PROGRAM_AFFILIATIONS.get( self.dept_code) if not program_affiliations: return [] return data_loch.get_advisor_uids_for_affiliations( program_affiliations.get('program'), program_affiliations.get('affiliations'), )
class NoteRead(db.Model): __tablename__ = 'notes_read' viewer_id = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False, primary_key=True) note_id = db.Column(db.String(255), nullable=False, primary_key=True) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) __table_args__ = (db.UniqueConstraint( 'viewer_id', 'note_id', name='notes_read_viewer_id_note_id_unique_constraint', ), ) def __init__(self, viewer_id, note_id): self.viewer_id = viewer_id self.note_id = note_id @classmethod def find_or_create(cls, viewer_id, note_id): note_read = cls.query.filter( and_(cls.viewer_id == viewer_id, cls.note_id == str(note_id))).one_or_none() if not note_read: note_read = cls(viewer_id, note_id) db.session.add(note_read) std_commit() return note_read @classmethod def get_notes_read_by_user(cls, viewer_id, note_ids): return cls.query.filter(NoteRead.viewer_id == viewer_id, NoteRead.note_id.in_(note_ids)).all() @classmethod def when_user_read_note(cls, viewer_id, note_id): note_read = cls.query.filter(NoteRead.viewer_id == viewer_id, NoteRead.note_id == note_id).first() return note_read and note_read.created_at
class DegreeProgressCourse(Base): __tablename__ = 'degree_progress_courses' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 accent_color = db.Column(db.String(255)) category_id = db.Column(db.Integer, db.ForeignKey('degree_progress_categories.id')) degree_check_id = db.Column(db.Integer, db.ForeignKey('degree_progress_templates.id'), nullable=False) display_name = db.Column(db.String(255), nullable=False) grade = db.Column(db.String(50), nullable=False) ignore = db.Column(db.Boolean, nullable=False) note = db.Column(db.Text) manually_created_at = db.Column(db.DateTime) manually_created_by = db.Column(db.Integer, db.ForeignKey('authorized_users.id')) section_id = db.Column(db.Integer) sid = db.Column(db.String(80), nullable=False) term_id = db.Column(db.Integer) units = db.Column(db.Numeric, nullable=False) unit_requirements = db.relationship( DegreeProgressCourseUnitRequirement.__name__, back_populates='course', lazy='joined', ) __table_args__ = (db.UniqueConstraint( 'category_id', 'degree_check_id', 'manually_created_at', 'manually_created_by', 'section_id', 'sid', 'term_id', name='degree_progress_courses_category_id_course_unique_constraint', ), ) def __init__( self, degree_check_id, display_name, grade, section_id, sid, term_id, units, accent_color=None, category_id=None, ignore=False, manually_created_at=None, manually_created_by=None, note=None, ): self.accent_color = accent_color self.category_id = category_id self.degree_check_id = degree_check_id self.display_name = display_name self.grade = grade self.ignore = ignore self.manually_created_by = manually_created_by if self.manually_created_by and not manually_created_at: raise ValueError( 'manually_created_at is required if manually_created_by is present.' ) else: self.manually_created_at = manually_created_at self.note = note self.section_id = section_id self.sid = sid self.term_id = term_id self.units = units def __repr__(self): return f"""<DegreeProgressCourse id={self.id}, accent_color={self.accent_color}, category_id={self.category_id}, degree_check_id={self.degree_check_id}, display_name={self.display_name}, grade={self.grade}, ignore={self.ignore}, manually_created_at={self.manually_created_at}, manually_created_by={self.manually_created_by}, note={self.note}, section_id={self.section_id}, sid={self.sid}, term_id={self.term_id}, units={self.units},>""" @classmethod def assign(cls, category_id, course_id): course = cls.query.filter_by(id=course_id).first() course.category_id = category_id course.ignore = False std_commit() DegreeProgressCourseUnitRequirement.delete(course_id) for u in DegreeProgressCategoryUnitRequirement.find_by_category_id( category_id): DegreeProgressCourseUnitRequirement.create(course.id, u.unit_requirement_id) return course @classmethod def create( cls, degree_check_id, display_name, grade, section_id, sid, term_id, units, accent_color=None, category_id=None, manually_created_at=None, manually_created_by=None, note=None, unit_requirement_ids=(), ): course = cls( accent_color=accent_color, category_id=category_id, degree_check_id=degree_check_id, display_name=display_name, grade=grade, manually_created_at=manually_created_at, manually_created_by=manually_created_by, note=note, section_id=section_id, sid=sid, term_id=term_id, units=units if (units is None or is_float(units)) else 0, ) db.session.add(course) std_commit() for unit_requirement_id in unit_requirement_ids: DegreeProgressCourseUnitRequirement.create( course_id=course.id, unit_requirement_id=unit_requirement_id, ) return course @classmethod def delete(cls, course): db.session.delete(course) std_commit() @classmethod def find_by_id(cls, course_id): return cls.query.filter_by(id=course_id).first() @classmethod def find_by_category_id(cls, category_id): return cls.query.filter_by(category_id=category_id).all() @classmethod def find_by_sid(cls, degree_check_id, sid): return cls.query.filter_by(degree_check_id=degree_check_id, sid=sid).all() @classmethod def get_courses(cls, degree_check_id, manually_created_at, manually_created_by, section_id, sid, term_id): return cls.query.filter_by( degree_check_id=degree_check_id, manually_created_at=manually_created_at, manually_created_by=manually_created_by, section_id=section_id, sid=sid, term_id=term_id, ).all() @classmethod def unassign(cls, course_id, ignore=False): course = cls.query.filter_by(id=course_id).first() course.category_id = None course.ignore = ignore std_commit() DegreeProgressCourseUnitRequirement.delete(course_id) return course @classmethod def update( cls, accent_color, course_id, grade, name, note, units, unit_requirement_ids, ): course = cls.query.filter_by(id=course_id).first() course.accent_color = accent_color course.grade = grade course.display_name = name course.note = note course.units = units if (units is None or is_float(units)) else 0 existing_unit_requirements = DegreeProgressCourseUnitRequirement.find_by_course_id( course_id) existing_unit_requirement_id_set = set( [u.unit_requirement_id for u in existing_unit_requirements]) unit_requirement_id_set = set(unit_requirement_ids or []) for unit_requirement_id in (unit_requirement_id_set - existing_unit_requirement_id_set): DegreeProgressCourseUnitRequirement.create( course_id=course.id, unit_requirement_id=unit_requirement_id, ) for unit_requirement_id in (existing_unit_requirement_id_set - unit_requirement_id_set): delete_me = next(e for e in existing_unit_requirements if e.unit_requirement_id == unit_requirement_id) db.session.delete(delete_me) std_commit() return course def to_api_json(self): unit_requirements = [ m.unit_requirement.to_api_json() for m in (self.unit_requirements or []) ] return { 'accentColor': self.accent_color, 'categoryId': self.category_id, 'createdAt': _isoformat(self.created_at), 'degreeCheckId': self.degree_check_id, 'grade': self.grade, 'id': self.id, 'ignore': self.ignore, 'manuallyCreatedAt': _isoformat(self.manually_created_at), 'manuallyCreatedBy': self.manually_created_by, 'name': self.display_name, 'note': self.note, 'sectionId': self.section_id, 'sid': self.sid, 'termId': self.term_id, 'termName': term_name_for_sis_id(self.term_id), 'unitRequirements': sorted(unit_requirements, key=lambda r: r['name']), 'units': self.units, 'updatedAt': _isoformat(self.updated_at), }
class NoteTemplate(Base): __tablename__ = 'note_templates' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 body = db.Column(db.Text, nullable=False) creator_id = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False) deleted_at = db.Column(db.DateTime, nullable=True) is_private = db.Column(db.Boolean, nullable=False, default=False) subject = db.Column(db.String(255), nullable=False) title = db.Column(db.String(255), nullable=False) topics = db.relationship( 'NoteTemplateTopic', primaryjoin='and_(NoteTemplate.id==NoteTemplateTopic.note_template_id)', back_populates='note_template', lazy=True, ) attachments = db.relationship( 'NoteTemplateAttachment', primaryjoin= 'and_(NoteTemplate.id==NoteTemplateAttachment.note_template_id, NoteTemplateAttachment.deleted_at==None)', back_populates='note_template', lazy=True, ) __table_args__ = (db.UniqueConstraint( 'creator_id', 'title', 'deleted_at', name='student_groups_owner_id_name_unique_constraint', ), ) def __init__(self, body, creator_id, subject, title, is_private=False): self.body = body self.creator_id = creator_id self.is_private = is_private self.subject = subject self.title = title @classmethod def create(cls, creator_id, subject, title, attachments=(), body='', is_private=False, topics=()): creator = AuthorizedUser.find_by_id(creator_id) if creator: note_template = cls(body=body, creator_id=creator_id, is_private=is_private, subject=subject, title=title) for topic in topics: note_template.topics.append( NoteTemplateTopic.create( note_template.id, titleize(vacuum_whitespace(topic))), ) for byte_stream_bundle in attachments: note_template.attachments.append( NoteTemplateAttachment.create( note_template_id=note_template.id, name=byte_stream_bundle['name'], byte_stream=byte_stream_bundle['byte_stream'], uploaded_by=creator.uid, ), ) db.session.add(note_template) std_commit() return note_template @classmethod def find_by_id(cls, note_template_id): return cls.query.filter( and_(cls.id == note_template_id, cls.deleted_at == None)).first() # noqa: E711 @classmethod def get_templates_created_by(cls, creator_id): return cls.query.filter_by(creator_id=creator_id, deleted_at=None).order_by(cls.title).all() @classmethod def rename(cls, note_template_id, title): note_template = cls.find_by_id(note_template_id) if note_template: note_template.title = title std_commit() return note_template else: return None @classmethod def update( cls, body, note_template_id, subject, attachments=(), delete_attachment_ids=(), is_private=False, topics=(), ): note_template = cls.find_by_id(note_template_id) if note_template: creator = AuthorizedUser.find_by_id(note_template.creator_id) note_template.body = body note_template.is_private = is_private note_template.subject = subject cls._update_note_template_topics(note_template, topics) if delete_attachment_ids: cls._delete_attachments(note_template, delete_attachment_ids) for byte_stream_bundle in attachments: cls._add_attachment(note_template, byte_stream_bundle, creator.uid) std_commit() db.session.refresh(note_template) return note_template else: return None @classmethod def delete(cls, note_template_id): note_template = cls.find_by_id(note_template_id) if note_template: now = utc_now() note_template.deleted_at = now for attachment in note_template.attachments: attachment.deleted_at = now for topic in note_template.topics: db.session.delete(topic) std_commit() def to_api_json(self): attachments = [ a.to_api_json() for a in self.attachments if not a.deleted_at ] topics = [t.to_api_json() for t in self.topics] return { 'id': self.id, 'attachments': attachments, 'body': self.body, 'isPrivate': self.is_private, 'subject': self.subject, 'title': self.title, 'topics': topics, 'createdAt': self.created_at.astimezone(tzutc()).isoformat(), 'updatedAt': self.updated_at.astimezone(tzutc()).isoformat(), } @classmethod def _update_note_template_topics(cls, note_template, topics): modified = False now = utc_now() topics = set([titleize(vacuum_whitespace(topic)) for topic in topics]) existing_topics = set( note_topic.topic for note_topic in NoteTemplateTopic.find_by_note_template_id( note_template.id)) topics_to_delete = existing_topics - topics topics_to_add = topics - existing_topics for topic in topics_to_delete: topic_to_delete = next( (t for t in note_template.topics if t.topic == topic), None) if topic_to_delete: NoteTemplateTopic.delete(topic_to_delete.id) modified = True for topic in topics_to_add: note_template.topics.append( NoteTemplateTopic.create(note_template, topic), ) modified = True if modified: note_template.updated_at = now @classmethod def _add_attachment(cls, note_template, attachment, uploaded_by_uid): note_template.attachments.append( NoteTemplateAttachment.create( note_template_id=note_template.id, name=attachment['name'], byte_stream=attachment['byte_stream'], uploaded_by=uploaded_by_uid, ), ) note_template.updated_at = utc_now() @classmethod def _delete_attachments(cls, note_template, delete_attachment_ids): modified = False now = utc_now() for attachment in note_template.attachments: if attachment.id in delete_attachment_ids: attachment.deleted_at = now modified = True if modified: note_template.updated_at = now
class Alert(Base): __tablename__ = 'alerts' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 sid = db.Column(db.String(80), nullable=False) alert_type = db.Column(db.String(80), nullable=False) key = db.Column(db.String(255), nullable=False) message = db.Column(db.Text, nullable=False) deleted_at = db.Column(db.DateTime) views = db.relationship( 'AlertView', back_populates='alert', lazy=True, ) __table_args__ = (db.UniqueConstraint( 'sid', 'alert_type', 'key', 'created_at', name='alerts_sid_alert_type_key_created_at_unique_constraint', ), ) @classmethod def create(cls, sid, alert_type, key=None, message=None, deleted_at=None, created_at=None): # Alerts must contain a key, unique per SID and alert type, which will allow them to be located # and modified on updates to the data that originally generated the alert. The key defaults # to a string representation of today's date, but will more often (depending on the alert type) # contain a reference to a related resource, such as a course or assignment id. if key is None: key = datetime.now().strftime('%Y-%m-%d') else: # If we get a blank string as key, deliver a stern warning to the code that submitted it. key = key.strip() if not key: raise ValueError('Blank string submitted for alert key') alert = cls(sid, alert_type, key, message, deleted_at) if created_at: alert.created_at = created_at alert.updated_at = created_at db.session.add(alert) std_commit() def __init__(self, sid, alert_type, key, message=None, deleted_at=None): self.sid = sid self.alert_type = alert_type self.key = key self.message = message self.deleted_at = deleted_at def __repr__(self): return f"""<Alert {self.id}, sid={self.sid}, alert_type={self.alert_type}, key={self.key}, message={self.message}, deleted_at={self.deleted_at}, updated={self.updated_at}, created={self.created_at}> """ @classmethod def dismiss(cls, alert_id, viewer_id): alert = cls.query.filter_by(id=alert_id).first() if alert: alert_view = AlertView.query.filter_by(viewer_id=viewer_id, alert_id=alert_id).first() if alert_view: alert_view.dismissed_at = datetime.now() else: db.session.add( AlertView(viewer_id=viewer_id, alert_id=alert_id, dismissed_at=datetime.now())) std_commit() else: raise BadRequestError(f'No alert found for id {alert_id}') @classmethod def current_alert_counts_for_viewer(cls, viewer_id): query = """ SELECT alerts.sid, count(*) as alert_count FROM alerts LEFT JOIN alert_views ON alert_views.alert_id = alerts.id AND alert_views.viewer_id = :viewer_id WHERE alerts.deleted_at IS NULL AND alerts.key LIKE :key AND alert_views.dismissed_at IS NULL GROUP BY alerts.sid """ params = {'viewer_id': viewer_id, 'key': current_term_id() + '_%'} return cls.alert_counts_by_query(query, params) @classmethod def current_alert_counts_for_sids(cls, viewer_id, sids, count_only=False, offset=None, limit=None): query = """ SELECT alerts.sid, count(*) as alert_count FROM alerts LEFT JOIN alert_views ON alert_views.alert_id = alerts.id AND alert_views.viewer_id = :viewer_id WHERE alerts.deleted_at IS NULL AND alerts.key LIKE :key AND alerts.sid = ANY(:sids) AND alert_views.dismissed_at IS NULL GROUP BY alerts.sid ORDER BY alert_count DESC, alerts.sid """ if offset: query += ' OFFSET :offset' if limit: query += ' LIMIT :limit' params = { 'viewer_id': viewer_id, 'key': current_term_id() + '_%', 'sids': sids, 'offset': offset, 'limit': limit, } return cls.alert_counts_by_query(query, params, count_only=count_only) @classmethod def alert_counts_by_query(cls, query, params, count_only=False): results = db.session.execute(text(query), params) # If we're only interested in the alert count, skip the student data fetch below. if count_only: return [{ 'sid': row['sid'], 'alertCount': row['alert_count'] } for row in results] alert_counts_by_sid = { row['sid']: row['alert_count'] for row in results } sids = list(alert_counts_by_sid.keys()) def result_to_dict(result): result_dict = { 'sid': result.get('sid'), 'uid': result.get('uid'), 'firstName': result.get('first_name'), 'lastName': result.get('last_name'), 'alertCount': alert_counts_by_sid.get(result.get('sid')), } return result_dict return [ result_to_dict(result) for result in data_loch.get_basic_student_data(sids) ] @classmethod def current_alerts_for_sid(cls, viewer_id, sid): query = text(""" SELECT alerts.*, alert_views.dismissed_at FROM alerts LEFT JOIN alert_views ON alert_views.alert_id = alerts.id AND alert_views.viewer_id = :viewer_id WHERE alerts.deleted_at IS NULL AND alerts.key LIKE :key AND alerts.sid = :sid ORDER BY alerts.created_at """) results = db.session.execute(query, { 'viewer_id': viewer_id, 'key': current_term_id() + '_%', 'sid': sid }) feed = [] def result_to_dict(result): return { camelize(key): result[key] for key in ['id', 'alert_type', 'key', 'message'] } for result in results: dismissed_at = result['dismissed_at'] alert = { **result_to_dict(result), **{ 'dismissed': dismissed_at and dismissed_at.strftime('%Y-%m-%d %H:%M:%S'), 'createdAt': result['created_at'].strftime('%Y-%m-%d %H:%M:%S'), 'updatedAt': result['updated_at'].strftime('%Y-%m-%d %H:%M:%S'), }, } feed.append(alert) return feed def activate(self, preserve_creation_date=False): self.deleted_at = None # Some alert types, such as withdrawals and midpoint deficient grades, don't include a time-shifted message # and shouldn't be treated as updated after creation. if preserve_creation_date: self.updated_at = self.created_at std_commit() def deactivate(self): self.deleted_at = datetime.now() std_commit() @classmethod def create_or_activate( cls, alert_type, key, message, sid, created_at=None, force_use_existing=False, preserve_creation_date=False, ): # If any previous alerts exist with the same type, key and sid, grab the most recently updated one. existing_alert = cls.query.filter_by(sid=sid, alert_type=alert_type, key=key).order_by( desc(cls.updated_at)).first() # If the existing alert was only just deactivated in the last two hours, assume that the deactivation was part of the # current refresh cycle, and go ahead and reactivate it. But if the alert was deactivated farther back in the past, # assume that it represents a previous state of affairs, and create a new alert for current conditions. if existing_alert and (force_use_existing or (datetime.now(timezone.utc) - existing_alert.updated_at).total_seconds() < (2 * 3600)): existing_alert.message = message existing_alert.activate( preserve_creation_date=preserve_creation_date) else: cls.create( alert_type=alert_type, created_at=created_at, key=key, message=message, sid=sid, ) @classmethod def deactivate_all(cls, sid, term_id, alert_types): query = ( cls.query.filter(cls.sid == sid).filter( cls.alert_type.in_(alert_types)).filter( cls.key.startswith(f'{term_id}_%')).filter( cls.deleted_at == None) # noqa: E711 ) results = query.update({cls.deleted_at: datetime.now()}, synchronize_session='fetch') std_commit() return results @classmethod def get_alerts_per_date_range(cls, from_date_utc, to_date_utc): criterion = and_( cls.created_at >= from_date_utc, cls.created_at <= to_date_utc, ) return cls.query.filter(criterion).order_by(cls.created_at).all() @classmethod def infrequent_activity_alerts_enabled(cls): if not app.config['ALERT_INFREQUENT_ACTIVITY_ENABLED']: return False if current_term_name().startswith('Summer'): return False days_into_session = (datetime.date(datetime.today()) - _get_current_term_start()).days return days_into_session >= app.config['ALERT_INFREQUENT_ACTIVITY_DAYS'] @classmethod def no_activity_alerts_enabled(cls): if not app.config['ALERT_NO_ACTIVITY_ENABLED']: return False if current_term_name().startswith('Summer'): return False days_into_session = (datetime.date(datetime.today()) - _get_current_term_start()).days return days_into_session >= app.config[ 'ALERT_NO_ACTIVITY_DAYS_INTO_SESSION'] @classmethod def deactivate_all_for_term(cls, term_id): query = ( cls.query.filter(cls.key.startswith(f'{term_id}_%')).filter( cls.deleted_at == None) # noqa: E711 ) results = query.update({cls.deleted_at: datetime.now()}, synchronize_session='fetch') std_commit() return results @classmethod def update_all_for_term(cls, term_id): app.logger.info('Starting alert update') enrollments_for_term = data_loch.get_enrollments_for_term(str(term_id)) no_activity_alerts_enabled = cls.no_activity_alerts_enabled() infrequent_activity_alerts_enabled = cls.infrequent_activity_alerts_enabled( ) for row in enrollments_for_term: enrollments = json.loads(row['enrollment_term']).get( 'enrollments', []) for enrollment in enrollments: cls.update_alerts_for_enrollment( sid=row['sid'], term_id=term_id, enrollment=enrollment, no_activity_alerts_enabled=no_activity_alerts_enabled, infrequent_activity_alerts_enabled= infrequent_activity_alerts_enabled, ) profiles = data_loch.get_student_profiles() if app.config['ALERT_WITHDRAWAL_ENABLED'] and str( term_id) == current_term_id(): for row in profiles: profile_feed = json.loads(row['profile']) if 'withdrawalCancel' in (profile_feed.get('sisProfile') or {}): cls.update_withdrawal_cancel_alerts(row['sid'], term_id) sids = [p['sid'] for p in profiles] for sid, academic_standing_list in get_academic_standing_by_sid( sids).items(): standing = next((s for s in academic_standing_list if s['termId'] == str(term_id)), None) if standing and standing['status'] in ('DIS', 'PRO', 'SUB'): cls.update_academic_standing_alerts( action_date=standing['actionDate'], sid=standing['sid'], status=standing['status'], term_id=term_id, ) app.logger.info('Alert update complete') @classmethod def update_academic_standing_alerts(cls, action_date, sid, status, term_id): key = f'{term_id}_{action_date}_academic_standing_{status}' status_description = ACADEMIC_STANDING_DESCRIPTIONS.get(status, status) message = f"Student's academic standing is '{status_description}'." datetime.strptime(action_date, '%Y-%m-%d') cls.create_or_activate( alert_type='academic_standing', created_at=action_date, force_use_existing=True, key=key, message=message, preserve_creation_date=True, sid=sid, ) @classmethod def update_alerts_for_enrollment(cls, sid, term_id, enrollment, no_activity_alerts_enabled, infrequent_activity_alerts_enabled): for section in enrollment['sections']: if section_is_eligible_for_alerts(enrollment=enrollment, section=section): # If the grade is in, what's done is done. if section.get('grade'): continue if section.get('midtermGrade'): cls.update_midterm_grade_alerts(sid, term_id, section['ccn'], enrollment['displayName'], section['midtermGrade']) last_activity = None activity_percentile = None for canvas_site in enrollment.get('canvasSites', []): student_activity = canvas_site.get('analytics', {}).get( 'lastActivity', {}).get('student') if not student_activity or student_activity.get( 'roundedUpPercentile') is None: continue raw_epoch = student_activity.get('raw') if last_activity is None or raw_epoch > last_activity: last_activity = raw_epoch activity_percentile = student_activity.get( 'roundedUpPercentile') if last_activity is None: continue if (no_activity_alerts_enabled and last_activity == 0 and activity_percentile <= app.config['ALERT_NO_ACTIVITY_PERCENTILE_CUTOFF']): cls.update_no_activity_alerts(sid, term_id, enrollment['displayName']) elif (infrequent_activity_alerts_enabled and last_activity > 0): localized_last_activity = unix_timestamp_to_localtime( last_activity).date() localized_today = unix_timestamp_to_localtime( time.time()).date() days_since = (localized_today - localized_last_activity).days if (days_since >= app.config['ALERT_INFREQUENT_ACTIVITY_DAYS'] and activity_percentile <= app.config[ 'ALERT_INFREQUENT_ACTIVITY_PERCENTILE_CUTOFF'] ): cls.update_infrequent_activity_alerts( sid, term_id, enrollment['displayName'], days_since, ) @classmethod def update_assignment_alerts(cls, sid, term_id, assignment_id, due_at, status, course_site_name): alert_type = status + '_assignment' key = f'{term_id}_{assignment_id}' due_at_date = utc_timestamp_to_localtime(due_at).strftime('%b %-d, %Y') message = f'{course_site_name} assignment due on {due_at_date}.' cls.create_or_activate(sid=sid, alert_type=alert_type, key=key, message=message) @classmethod def update_midterm_grade_alerts(cls, sid, term_id, section_id, class_name, grade): key = f'{term_id}_{section_id}' message = f'{class_name} midpoint deficient grade of {grade}.' cls.create_or_activate(sid=sid, alert_type='midterm', key=key, message=message, preserve_creation_date=True) @classmethod def update_no_activity_alerts(cls, sid, term_id, class_name): key = f'{term_id}_{class_name}' message = f'No activity! Student has never visited the {class_name} bCourses site for {term_name_for_sis_id(term_id)}.' cls.create_or_activate(sid=sid, alert_type='no_activity', key=key, message=message) @classmethod def update_infrequent_activity_alerts(cls, sid, term_id, class_name, days_since): key = f'{term_id}_{class_name}' message = f'Infrequent activity! Last {class_name} bCourses activity was {days_since} days ago.' # If an active infrequent activity alert already exists and is more recent, skip the update. existing_alert = cls.query.filter_by(sid=sid, alert_type='infrequent_activity', key=key, deleted_at=None).first() if existing_alert: match = re.search('(\d+) days ago.$', message) if match and match[1] and int(match[1]) < days_since: return cls.create_or_activate(sid=sid, alert_type='infrequent_activity', key=key, message=message) @classmethod def update_withdrawal_cancel_alerts(cls, sid, term_id): key = f'{term_id}_withdrawal' message = f'Student is no longer enrolled in the {term_name_for_sis_id(term_id)} term.' cls.create_or_activate(sid=sid, alert_type='withdrawal', key=key, message=message, preserve_creation_date=True) @classmethod def include_alert_counts_for_students(cls, viewer_user_id, group, count_only=False, offset=None, limit=None): sids = group.get('sids') if 'sids' in group else [ s['sid'] for s in group.get('students', []) ] alert_counts = cls.current_alert_counts_for_sids(viewer_user_id, sids, count_only=count_only, offset=offset, limit=limit) if 'students' in group: counts_per_sid = { s.get('sid'): s.get('alertCount') for s in alert_counts } for student in group.get('students'): sid = student['sid'] student['alertCount'] = counts_per_sid.get( sid) if sid in counts_per_sid else 0 return alert_counts
class CuratedGroup(Base): __tablename__ = 'student_groups' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 owner_id = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False) name = db.Column(db.String(255), nullable=False) __table_args__ = (db.UniqueConstraint( 'owner_id', 'name', name='student_groups_owner_id_name_unique_constraint', ), ) def __init__(self, name, owner_id): self.name = name self.owner_id = owner_id @classmethod def find_by_id(cls, curated_group_id): return cls.query.filter_by(id=curated_group_id).first() @classmethod def get_curated_groups_by_owner_id(cls, owner_id): return cls.query.filter_by(owner_id=owner_id).order_by(cls.name).all() @classmethod def get_groups_owned_by_uids(cls, uids): query = text(f""" SELECT sg.id, sg.name, count(sgm.sid) AS student_count, au.uid AS owner_uid FROM student_groups sg LEFT JOIN student_group_members sgm ON sg.id = sgm.student_group_id JOIN authorized_users au ON sg.owner_id = au.id WHERE au.uid = ANY(:uids) GROUP BY sg.id, sg.name, au.id, au.uid """) results = db.session.execute(query, {'uids': uids}) def transform(row): return { 'id': row['id'], 'name': row['name'], 'totalStudentCount': row['student_count'], 'ownerUid': row['owner_uid'], } return [transform(row) for row in results] @classmethod def curated_group_ids_per_sid(cls, user_id, sid): query = text(f"""SELECT student_group_id as id FROM student_group_members m JOIN student_groups g ON g.id = m.student_group_id WHERE g.owner_id = :user_id AND m.sid = :sid""") results = db.session.execute(query, {'user_id': user_id, 'sid': sid}) return [row['id'] for row in results] @classmethod def create(cls, owner_id, name): curated_group = cls(name, owner_id) db.session.add(curated_group) std_commit() return curated_group @classmethod def get_all_sids(cls, curated_group_id): return CuratedGroupStudent.get_sids(curated_group_id=curated_group_id) @classmethod def add_student(cls, curated_group_id, sid): curated_group = cls.query.filter_by(id=curated_group_id).first() if curated_group: CuratedGroupStudent.add_student(curated_group_id=curated_group_id, sid=sid) @classmethod def add_students(cls, curated_group_id, sids): curated_group = cls.query.filter_by(id=curated_group_id).first() if curated_group: CuratedGroupStudent.add_students(curated_group_id=curated_group_id, sids=sids) std_commit() @classmethod def remove_student(cls, curated_group_id, sid): if cls.find_by_id(curated_group_id): CuratedGroupStudent.remove_student(curated_group_id, sid) @classmethod def rename(cls, curated_group_id, name): curated_group = cls.query.filter_by(id=curated_group_id).first() curated_group.name = name std_commit() @classmethod def delete(cls, curated_group_id): curated_group = cls.query.filter_by(id=curated_group_id).first() if curated_group: db.session.delete(curated_group) std_commit() def to_api_json(self, order_by='last_name', offset=0, limit=50, include_students=True): feed = { 'id': self.id, 'ownerId': self.owner_id, 'name': self.name, } if include_students: sids = CuratedGroupStudent.get_sids(curated_group_id=self.id) if sids: result = query_students(sids=sids, order_by=order_by, offset=offset, limit=limit, include_profiles=False) feed['students'] = result['students'] feed['totalStudentCount'] = result['totalStudentCount'] # Attempt to supplement with historical student rows if we seem to be missing something. if result['totalStudentCount'] < len(sids): remaining_sids = list(set(sids) - set(result['sids'])) historical_sid_rows = query_historical_sids(remaining_sids) if len(historical_sid_rows): for row in historical_sid_rows: ManuallyAddedAdvisee.find_or_create(row['sid']) feed['totalStudentCount'] += len(historical_sid_rows) page_shortfall = max(0, limit - len(result['students'])) feed[ 'students'] += historical_sid_rows[:page_shortfall] else: feed['students'] = [] feed['totalStudentCount'] = 0 return feed
class DegreeProgressUnitRequirement(Base): __tablename__ = 'degree_progress_unit_requirements' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 created_by = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False) min_units = db.Column(db.Integer, nullable=False) name = db.Column(db.String(255), nullable=False) template_id = db.Column(db.Integer, db.ForeignKey('degree_progress_templates.id'), nullable=False) updated_by = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False) categories = db.relationship( 'DegreeProgressCategoryUnitRequirement', back_populates='unit_requirement', ) courses = db.relationship( 'DegreeProgressCourseUnitRequirement', back_populates='unit_requirement', ) template = db.relationship('DegreeProgressTemplate', back_populates='unit_requirements') __table_args__ = (db.UniqueConstraint( 'name', 'template_id', name='degree_progress_unit_requirements_name_template_id_unique_const', ), ) def __init__(self, created_by, min_units, name, template_id, updated_by): self.created_by = created_by self.min_units = min_units self.name = name self.template_id = template_id self.updated_by = updated_by def __repr__(self): return f"""<DegreeProgressUnitRequirement id={self.id}, name={self.name}, min_units={self.min_units}, template_id={self.template_id}, created_at={self.created_at}, created_by={self.created_by}, updated_at={self.updated_at}, updated_by={self.updated_by}>""" @classmethod def create(cls, created_by, min_units, name, template_id): unit_requirement = cls( created_by=created_by, min_units=min_units, name=name, template_id=template_id, updated_by=created_by, ) db.session.add(unit_requirement) std_commit() return unit_requirement @classmethod def delete(cls, unit_requirement_id): unit_requirement = cls.query.filter_by(id=unit_requirement_id).first() DegreeProgressCategoryUnitRequirement.delete_mappings( unit_requirement_id=unit_requirement.id) db.session.delete(unit_requirement) std_commit() @classmethod def find_by_id(cls, unit_requirement_id): return cls.query.filter_by(id=unit_requirement_id).first() @classmethod def update(cls, id_, min_units, name, updated_by): unit_requirement = cls.query.filter_by(id=id_).first() unit_requirement.min_units = min_units unit_requirement.name = name unit_requirement.updated_by = updated_by std_commit() db.session.refresh(unit_requirement) return unit_requirement def to_api_json(self): return { 'id': self.id, 'name': self.name, 'minUnits': self.min_units, 'createdAt': _isoformat(self.created_at), 'createdBy': self.created_by, 'updatedAt': _isoformat(self.updated_at), 'updatedBy': self.updated_by, 'templateId': self.template_id, }
class UniversityDept(Base): __tablename__ = 'university_depts' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 dept_code = db.Column(db.String(80), nullable=False) dept_name = db.Column(db.String(255), nullable=False) authorized_users = db.relationship( 'UniversityDeptMember', back_populates='university_dept', ) __table_args__ = (db.UniqueConstraint('dept_code', 'dept_name', name='university_depts_code_unique_constraint'),) def __init__(self, dept_code, dept_name): self.dept_code = dept_code self.dept_name = dept_name @classmethod def find_by_dept_code(cls, dept_code): return cls.query.filter_by(dept_code=dept_code).first() @classmethod def get_all(cls, exclude_empty=False): if exclude_empty: results = db.session.execute(text('select distinct university_dept_id from university_dept_members')) dept_ids = [row['university_dept_id'] for row in results] return cls.query.filter(cls.id.in_(dept_ids)).order_by(cls.dept_name).all() else: return cls.query.order_by(cls.dept_name).all() @classmethod def create(cls, dept_code, dept_name): dept = cls(dept_code=dept_code, dept_name=dept_name) db.session.add(dept) std_commit() return dept def delete_automated_members(self): sql = """ DELETE FROM university_dept_members WHERE university_dept_id = :id AND automate_membership IS TRUE; UPDATE authorized_users SET deleted_at = now() WHERE is_admin IS FALSE AND deleted_at IS NULL AND id NOT IN (SELECT authorized_user_id FROM university_dept_members);""" db.session.execute(text(sql), {'id': self.id}) std_commit() def memberships_from_loch(self): program_affiliations = BERKELEY_DEPT_CODE_TO_PROGRAM_AFFILIATIONS.get(self.dept_code) if not program_affiliations: return [] advisors = data_loch.get_advisor_uids_for_affiliations( program_affiliations.get('program'), program_affiliations.get('affiliations'), ) def _resolve(uid, rows): rows = list(rows) if len(rows) == 1: return rows[0] can_access_advising_data = reduce((lambda r, s: r['can_access_advising_data'] or s['can_access_advising_data']), rows) can_access_canvas_data = reduce((lambda r, s: r['can_access_canvas_data'] or s['can_access_canvas_data']), rows) degree_progress_permission = reduce((lambda r, s: r['degree_progress_permission'] or s['degree_progress_permission']), rows) return { 'uid': uid, 'can_access_advising_data': can_access_advising_data, 'can_access_canvas_data': can_access_canvas_data, 'degree_progress_permission': degree_progress_permission, } advisors.sort(key=itemgetter('uid')) return [_resolve(uid, rows) for (uid, rows) in groupby(advisors, itemgetter('uid'))]
class CuratedCohort(Base): __tablename__ = 'student_groups' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 owner_id = db.Column(db.String(80), db.ForeignKey('authorized_users.id'), nullable=False) name = db.Column(db.String(255), nullable=False) students = db.relationship('CuratedCohortStudent', back_populates='curated_cohort', cascade='all') __table_args__ = (db.UniqueConstraint( 'owner_id', 'name', name='student_groups_owner_id_name_unique_constraint', ),) def __init__(self, name, owner_id): self.name = name self.owner_id = owner_id @classmethod def find_by_id(cls, curated_cohort_id): return cls.query.filter_by(id=curated_cohort_id).first() @classmethod def get_curated_cohorts_by_owner_id(cls, owner_id): return cls.query.filter_by(owner_id=owner_id).order_by(cls.name).all() @classmethod def create(cls, owner_id, name): curated_cohort = cls(name, owner_id) db.session.add(curated_cohort) std_commit() return curated_cohort @classmethod def add_student(cls, curated_cohort_id, sid): curated_cohort = cls.query.filter_by(id=curated_cohort_id).first() if curated_cohort: try: membership = CuratedCohortStudent(sid=sid, curated_cohort_id=curated_cohort_id) db.session.add(membership) std_commit() except (FlushError, IntegrityError): app.logger.warn(f'Database error in add_student with curated_cohort_id={curated_cohort_id}, sid {sid}') return curated_cohort @classmethod def add_students(cls, curated_cohort_id, sids): curated_cohort = cls.query.filter_by(id=curated_cohort_id).first() if curated_cohort: try: for sid in set(sids): membership = CuratedCohortStudent(sid=sid, curated_cohort_id=curated_cohort_id) db.session.add(membership) std_commit() except (FlushError, IntegrityError): app.logger.warn(f'Database error in add_students with curated_cohort_id={curated_cohort_id}, sid {sid}') return curated_cohort @classmethod def remove_student(cls, curated_cohort_id, sid): curated_cohort = cls.find_by_id(curated_cohort_id) membership = CuratedCohortStudent.query.filter_by(sid=sid, curated_cohort_id=curated_cohort_id).first() if curated_cohort and membership: db.session.delete(membership) std_commit() @classmethod def rename(cls, curated_cohort_id, name): curated_cohort = cls.query.filter_by(id=curated_cohort_id).first() curated_cohort.name = name std_commit() return curated_cohort @classmethod def delete(cls, curated_cohort_id): curated_cohort = cls.query.filter_by(id=curated_cohort_id).first() if curated_cohort: db.session.delete(curated_cohort) std_commit() def to_api_json(self, sids_only=False, include_students=True): api_json = { 'id': self.id, 'ownerId': self.owner_id, 'name': self.name, 'studentCount': len(self.students), } if sids_only: api_json['students'] = [{'sid': s.sid} for s in self.students] elif include_students: api_json['students'] = get_api_json([s.sid for s in self.students]) return api_json
class Alert(Base): __tablename__ = 'alerts' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 sid = db.Column(db.String(80), nullable=False) alert_type = db.Column(db.String(80), nullable=False) key = db.Column(db.String(255), nullable=False) message = db.Column(db.Text, nullable=False) active = db.Column(db.Boolean, nullable=False) views = db.relationship( 'AlertView', back_populates='alert', lazy=True, ) __table_args__ = (db.UniqueConstraint( 'sid', 'alert_type', 'key', name='alerts_sid_alert_type_key_unique_constraint', ), ) @classmethod def create(cls, sid, alert_type, key=None, message=None, active=True): # Alerts must contain a key, unique per SID and alert type, which will allow them to be located # and modified on updates to the data that originally generated the alert. The key defaults # to a string representation of today's date, but will more often (depending on the alert type) # contain a reference to a related resource, such as a course or assignment id. if key is None: key = datetime.now().strftime('%Y-%m-%d') else: # If we get a blank string as key, deliver a stern warning to the code that submitted it. key = key.strip() if not key: raise ValueError('Blank string submitted for alert key') alert = cls(sid, alert_type, key, message, active) db.session.add(alert) std_commit() def __init__(self, sid, alert_type, key, message=None, active=True): self.sid = sid self.alert_type = alert_type self.key = key self.message = message self.active = active def __repr__(self): return f"""<Alert {self.id}, sid={self.sid}, alert_type={self.alert_type}, key={self.key}, message={self.message}, active={self.active}, updated={self.updated_at}, created={self.created_at}> """ @classmethod def dismiss(cls, alert_id, viewer_id): alert = cls.query.filter_by(id=alert_id).first() if alert: alert_view = AlertView.query.filter_by(viewer_id=viewer_id, alert_id=alert_id).first() if alert_view: alert_view.dismissed_at = datetime.now() else: db.session.add( AlertView(viewer_id=viewer_id, alert_id=alert_id, dismissed_at=datetime.now())) std_commit() else: raise BadRequestError(f'No alert found for id {alert_id}') @classmethod def current_alert_counts_for_viewer(cls, viewer_id): query = """ SELECT alerts.sid, count(*) as alert_count FROM alerts LEFT JOIN alert_views ON alert_views.alert_id = alerts.id AND alert_views.viewer_id = :viewer_id WHERE alerts.active = true AND alerts.key LIKE :key AND alert_views.dismissed_at IS NULL GROUP BY alerts.sid """ params = {'viewer_id': viewer_id, 'key': current_term_id() + '_%'} return cls.alert_counts_by_query(query, params) @classmethod def current_alert_counts_for_sids(cls, viewer_id, sids): query = """ SELECT alerts.sid, count(*) as alert_count FROM alerts LEFT JOIN alert_views ON alert_views.alert_id = alerts.id AND alert_views.viewer_id = :viewer_id WHERE alerts.active = true AND alerts.key LIKE :key AND alerts.sid = ANY(:sids) AND alert_views.dismissed_at IS NULL GROUP BY alerts.sid """ params = { 'viewer_id': viewer_id, 'key': current_term_id() + '_%', 'sids': sids } return cls.alert_counts_by_query(query, params) @classmethod def alert_counts_by_query(cls, query, params): results = db.session.execute(text(query), params) alert_counts_by_sid = { row['sid']: row['alert_count'] for row in results } sids = list(alert_counts_by_sid.keys()) def result_to_dict(result): result_dict = { 'sid': result.get('sid'), 'uid': result.get('uid'), 'firstName': result.get('firstName'), 'lastName': result.get('lastName'), 'alertCount': alert_counts_by_sid.get(result.get('sid')), } scope = get_student_query_scope() if 'UWASC' in scope or 'ADMIN' in scope: result_dict['isActiveAsc'] = result.get( 'athleticsProfile', {}).get('isActiveAsc') return result_dict return [ result_to_dict(result) for result in get_full_student_profiles(sids) ] @classmethod def current_alerts_for_sid(cls, viewer_id, sid): query = text(""" SELECT alerts.*, alert_views.dismissed_at FROM alerts LEFT JOIN alert_views ON alert_views.alert_id = alerts.id AND alert_views.viewer_id = :viewer_id WHERE alerts.active = true AND alerts.key LIKE :key AND alerts.sid = :sid ORDER BY alerts.created_at """) results = db.session.execute(query, { 'viewer_id': viewer_id, 'key': current_term_id() + '_%', 'sid': sid }) def result_to_dict(result): return { camelize(key): result[key] for key in ['id', 'alert_type', 'key', 'message'] } feed = { 'dismissed': [], 'shown': [], } for result in results: if result['dismissed_at']: feed['dismissed'].append(result_to_dict(result)) else: feed['shown'].append(result_to_dict(result)) return feed def activate(self): self.active = True std_commit() def deactivate(self): self.active = False std_commit() @classmethod def create_or_activate(cls, sid, alert_type, key, message): existing_alert = cls.query.filter_by(sid=sid, alert_type=alert_type, key=key).first() if existing_alert: existing_alert.message = message existing_alert.activate() else: cls.create(sid=sid, alert_type=alert_type, key=key, message=message) @classmethod def deactivate_all(cls, sid, term_id, alert_types): query = (cls.query.filter(cls.sid == sid).filter( cls.alert_type.in_(alert_types)).filter( cls.key.startswith(f'{term_id}_%'))) results = query.update({cls.active: False}, synchronize_session='fetch') std_commit() return results @classmethod def infrequent_activity_alerts_enabled(cls): return (app.config['ALERT_INFREQUENT_ACTIVITY_ENABLED'] and not app. config['CANVAS_CURRENT_ENROLLMENT_TERM'].startswith('Summer')) @classmethod def no_activity_alerts_enabled(cls): session = data_loch.get_regular_undergraduate_session( current_term_id())[0] days_into_session = (datetime.date(datetime.today()) - session['session_begins']).days return (app.config['ALERT_NO_ACTIVITY_ENABLED'] and not app. config['CANVAS_CURRENT_ENROLLMENT_TERM'].startswith('Summer') and days_into_session >= app.config['ALERT_NO_ACTIVITY_DAYS_INTO_SESSION']) @classmethod def deactivate_all_for_term(cls, term_id): query = (cls.query.filter(cls.key.startswith(f'{term_id}_%'))) results = query.update({cls.active: False}, synchronize_session='fetch') std_commit() return results @classmethod def update_all_for_term(cls, term_id): app.logger.info('Starting alert update') enrollments_for_term = data_loch.get_enrollments_for_term(str(term_id)) no_activity_alerts_enabled = cls.no_activity_alerts_enabled() infrequent_activity_alerts_enabled = cls.infrequent_activity_alerts_enabled( ) for row in enrollments_for_term: enrollments = json.loads(row['enrollment_term']).get( 'enrollments', []) for enrollment in enrollments: cls.update_alerts_for_enrollment( row['sid'], term_id, enrollment, no_activity_alerts_enabled, infrequent_activity_alerts_enabled) if app.config['ALERT_HOLDS_ENABLED'] and str( term_id) == current_term_id(): holds = data_loch.get_sis_holds() for row in holds: hold_feed = json.loads(row['feed']) cls.update_hold_alerts(row['sid'], term_id, hold_feed.get('type'), hold_feed.get('reason')) if app.config['ALERT_WITHDRAWAL_ENABLED'] and str( term_id) == current_term_id(): profiles = data_loch.get_student_profiles() for row in profiles: profile_feed = json.loads(row['profile']) if 'withdrawalCancel' in (profile_feed.get('sisProfile') or {}): cls.update_withdrawal_cancel_alerts(row['sid'], term_id) app.logger.info('Alert update complete') @classmethod def update_alerts_for_enrollment(cls, sid, term_id, enrollment, no_activity_alerts_enabled, infrequent_activity_alerts_enabled): for section in enrollment['sections']: if section.get('midtermGrade'): cls.update_midterm_grade_alerts(sid, term_id, section['ccn'], enrollment['displayName'], section['midtermGrade']) last_activity = None activity_percentile = None for canvas_site in enrollment.get('canvasSites', []): student_activity = canvas_site.get('analytics', {}).get('lastActivity', {}).get('student') if not student_activity or student_activity.get( 'roundedUpPercentile') is None: continue raw_epoch = student_activity.get('raw') if last_activity is None or raw_epoch > last_activity: last_activity = raw_epoch activity_percentile = student_activity.get( 'roundedUpPercentile') if last_activity is None: continue if (no_activity_alerts_enabled and last_activity == 0 and activity_percentile <= app.config['ALERT_NO_ACTIVITY_PERCENTILE_CUTOFF']): cls.update_no_activity_alerts(sid, term_id, enrollment['displayName']) elif infrequent_activity_alerts_enabled: localized_last_activity = unix_timestamp_to_localtime( last_activity).date() localized_today = unix_timestamp_to_localtime( time.time()).date() days_since = (localized_today - localized_last_activity).days if (days_since >= app.config['ALERT_INFREQUENT_ACTIVITY_DAYS'] and activity_percentile <= app. config['ALERT_INFREQUENT_ACTIVITY_PERCENTILE_CUTOFF']): cls.update_infrequent_activity_alerts( sid, term_id, enrollment['displayName'], days_since, ) @classmethod def update_assignment_alerts(cls, sid, term_id, assignment_id, due_at, status, course_site_name): alert_type = status + '_assignment' key = f'{term_id}_{assignment_id}' due_at_date = utc_timestamp_to_localtime(due_at).strftime('%b %-d, %Y') message = f'{course_site_name} assignment due on {due_at_date}.' cls.create_or_activate(sid=sid, alert_type=alert_type, key=key, message=message) @classmethod def update_midterm_grade_alerts(cls, sid, term_id, section_id, class_name, grade): key = f'{term_id}_{section_id}' message = f'{class_name} midterm grade of {grade}.' cls.create_or_activate(sid=sid, alert_type='midterm', key=key, message=message) @classmethod def update_no_activity_alerts(cls, sid, term_id, class_name): key = f'{term_id}_{class_name}' message = f'No activity! Student has never visited the {class_name} bCourses site for {term_name_for_sis_id(term_id)}.' cls.create_or_activate(sid=sid, alert_type='no_activity', key=key, message=message) @classmethod def update_infrequent_activity_alerts(cls, sid, term_id, class_name, days_since): key = f'{term_id}_{class_name}' message = f'Infrequent activity! Last {class_name} bCourses activity was {days_since} days ago.' # If an active infrequent activity alert already exists and is more recent, skip the update. existing_alert = cls.query.filter_by(sid=sid, alert_type='infrequent_activity', key=key, active=True).first() if existing_alert: match = re.search('(\d+) days ago.$', message) if match and match[1] and int(match[1]) < days_since: return cls.create_or_activate(sid=sid, alert_type='infrequent_activity', key=key, message=message) @classmethod def update_hold_alerts(cls, sid, term_id, hold_type, hold_reason): key = f"{term_id}_{hold_type.get('code')}_{hold_reason.get('code')}" message = f"Hold: {hold_reason.get('description')}! {hold_reason.get('formalDescription')}." cls.create_or_activate(sid=sid, alert_type='hold', key=key, message=message) @classmethod def update_withdrawal_cancel_alerts(cls, sid, term_id): key = f'{term_id}_withdrawal' message = f'Withdrawal! Student has withdrawn from the {term_name_for_sis_id(term_id)} term.' cls.create_or_activate(sid=sid, alert_type='withdrawal', key=key, message=message) @classmethod def include_alert_counts_for_students(cls, viewer_uid, cohort): alert_counts = None viewer = AuthorizedUser.find_by_uid(viewer_uid) if viewer: sids = cohort.get('sids') if 'sids' in cohort else [ s['sid'] for s in cohort.get('students', []) ] alert_counts = cls.current_alert_counts_for_sids(viewer.id, sids) if 'students' in cohort: counts_per_sid = { s.get('sid'): s.get('alertCount') for s in alert_counts } for student in cohort.get('students'): sid = student['sid'] student['alertCount'] = counts_per_sid.get( sid) if sid in counts_per_sid else 0 return alert_counts
class CuratedGroup(Base): __tablename__ = 'student_groups' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 domain = db.Column(cohort_domain_type, nullable=False) name = db.Column(db.String(255), nullable=False) owner_id = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False) __table_args__ = (db.UniqueConstraint( 'owner_id', 'name', name='student_groups_owner_id_name_unique_constraint', ),) def __init__(self, domain, name, owner_id): self.domain = domain self.name = name self.owner_id = owner_id def __repr__(self): return f"""<CohortFilter {self.id}, domain={self.domain}, name={self.name}, owner_id={self.owner_id}, updated_at={self.updated_at}, created_at={self.created_at}>""" @classmethod def find_by_id(cls, curated_group_id): return cls.query.filter_by(id=curated_group_id).first() @classmethod def get_curated_groups(cls, owner_id): if app.config['FEATURE_FLAG_ADMITTED_STUDENTS']: filter_by = cls.query.filter_by(owner_id=owner_id) else: filter_by = cls.query.filter_by(domain='default', owner_id=owner_id) return filter_by.order_by(cls.name).all() @classmethod def get_groups_owned_by_uids(cls, uids): domain_clause = 'true' if app.config['FEATURE_FLAG_ADMITTED_STUDENTS'] else "sg.domain = 'default'" query = text(f""" SELECT sg.id, sg.domain, sg.name, count(sgm.sid) AS student_count, au.uid AS owner_uid FROM student_groups sg LEFT JOIN student_group_members sgm ON sg.id = sgm.student_group_id JOIN authorized_users au ON sg.owner_id = au.id WHERE au.uid = ANY(:uids) AND {domain_clause} GROUP BY sg.id, sg.name, au.id, au.uid """) results = db.session.execute(query, {'uids': uids}) def transform(row): return { 'id': row['id'], 'domain': row['domain'], 'name': row['name'], 'totalStudentCount': row['student_count'], 'ownerUid': row['owner_uid'], } return [transform(row) for row in results] @classmethod def curated_group_ids_per_sid(cls, domain, sid, user_id): query = text("""SELECT student_group_id as id FROM student_group_members m JOIN student_groups g ON g.id = m.student_group_id WHERE g.domain = :domain AND g.owner_id = :user_id AND m.sid = :sid""") results = db.session.execute(query, { 'domain': domain, 'sid': sid, 'user_id': user_id, }) return [row['id'] for row in results] @classmethod def create(cls, owner_id, name, domain='default'): curated_group = cls(domain=domain, name=name, owner_id=owner_id) db.session.add(curated_group) std_commit() return curated_group @classmethod def get_all_sids(cls, curated_group_id): return CuratedGroupStudent.get_sids(curated_group_id=curated_group_id) @classmethod def add_student(cls, curated_group_id, sid): curated_group = cls.query.filter_by(id=curated_group_id).first() if curated_group: CuratedGroupStudent.add_student(curated_group_id=curated_group_id, sid=sid) _refresh_related_cohorts(curated_group) @classmethod def add_students(cls, curated_group_id, sids): curated_group = cls.query.filter_by(id=curated_group_id).first() if curated_group: CuratedGroupStudent.add_students(curated_group_id=curated_group_id, sids=sids) std_commit() _refresh_related_cohorts(curated_group) @classmethod def remove_student(cls, curated_group_id, sid): curated_group = cls.find_by_id(curated_group_id) if curated_group: CuratedGroupStudent.remove_student(curated_group_id, sid) _refresh_related_cohorts(curated_group) @classmethod def rename(cls, curated_group_id, name): curated_group = cls.query.filter_by(id=curated_group_id).first() curated_group.name = name std_commit() @classmethod def delete(cls, curated_group_id): curated_group = cls.query.filter_by(id=curated_group_id).first() if curated_group: db.session.delete(curated_group) std_commit() # Delete all cohorts that reference the deleted group for cohort_filter_id in curated_group.get_referencing_cohort_ids(): CohortFilter.delete(cohort_filter_id) std_commit() def get_referencing_cohort_ids(self): query = text("""SELECT c.id, c.filter_criteria FROM cohort_filters c WHERE filter_criteria->>'curatedGroupIds' IS NOT NULL AND owner_id = :user_id""") results = db.session.execute(query, {'user_id': self.owner_id}) cohort_filter_ids = [] for row in results: if self.id in row['filter_criteria'].get('curatedGroupIds', []): cohort_filter_ids.append(row['id']) return cohort_filter_ids def to_api_json(self, include_students, order_by='last_name', offset=0, limit=50): benchmark = get_benchmarker(f'CuratedGroup {self.id} to_api_json') benchmark('begin') sids = CuratedGroupStudent.get_sids(curated_group_id=self.id) feed = { 'domain': self.domain, 'id': self.id, 'name': self.name, 'ownerId': self.owner_id, 'sids': sids, 'totalStudentCount': len(sids), } if include_students: if sids: if self.domain == 'admitted_students': feed['students'] = get_admitted_students_by_sids( limit=limit, offset=offset, order_by=order_by, sids=sids, ) else: result = query_students( sids=sids, academic_career_status=('all'), include_profiles=False, order_by=order_by, offset=offset, limit=limit, ) feed['students'] = result['students'] else: feed['students'] = [] benchmark('end') return feed
class CuratedGroup(Base): __tablename__ = 'student_groups' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 owner_id = db.Column(db.Integer, db.ForeignKey('authorized_users.id'), nullable=False) name = db.Column(db.String(255), nullable=False) __table_args__ = (db.UniqueConstraint( 'owner_id', 'name', name='student_groups_owner_id_name_unique_constraint', ),) def __init__(self, name, owner_id): self.name = name self.owner_id = owner_id @classmethod def find_by_id(cls, curated_group_id): return cls.query.filter_by(id=curated_group_id).first() @classmethod def get_curated_groups_by_owner_id(cls, owner_id): return cls.query.filter_by(owner_id=owner_id).order_by(cls.name).all() @classmethod def curated_group_ids_per_sid(cls, user_id, sid): query = text(f"""SELECT student_group_id as id FROM student_group_members m JOIN student_groups g ON g.id = m.student_group_id WHERE g.owner_id = :user_id AND m.sid = :sid""") results = db.session.execute(query, {'user_id': user_id, 'sid': sid}) return [row['id'] for row in results] @classmethod def create(cls, owner_id, name): curated_group = cls(name, owner_id) db.session.add(curated_group) std_commit() return curated_group @classmethod def get_all_sids(cls, curated_group_id): return CuratedGroupStudent.get_sids(curated_group_id=curated_group_id) @classmethod def add_student(cls, curated_group_id, sid): curated_group = cls.query.filter_by(id=curated_group_id).first() if curated_group: CuratedGroupStudent.add_student(curated_group_id=curated_group_id, sid=sid) @classmethod def add_students(cls, curated_group_id, sids): curated_group = cls.query.filter_by(id=curated_group_id).first() if curated_group: CuratedGroupStudent.add_students(curated_group_id=curated_group_id, sids=sids) std_commit() @classmethod def remove_student(cls, curated_group_id, sid): if cls.find_by_id(curated_group_id): CuratedGroupStudent.remove_student(curated_group_id, sid) @classmethod def rename(cls, curated_group_id, name): curated_group = cls.query.filter_by(id=curated_group_id).first() curated_group.name = name std_commit() @classmethod def delete(cls, curated_group_id): curated_group = cls.query.filter_by(id=curated_group_id).first() if curated_group: db.session.delete(curated_group) std_commit() def to_api_json(self, order_by='last_name', offset=0, limit=50, include_students=True): feed = { 'id': self.id, 'ownerId': self.owner_id, 'name': self.name, } if include_students: sids = CuratedGroupStudent.get_sids(curated_group_id=self.id) if sids: result = query_students(sids=sids, order_by=order_by, offset=offset, limit=limit, include_profiles=False) feed['students'] = result['students'] feed['studentCount'] = result['totalStudentCount'] else: feed['students'] = [] feed['studentCount'] = 0 return feed