class AdminUser(Base): __tablename__ = 'admin_users' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 uid = db.Column(db.String(255), nullable=False, unique=True) deleted_at = db.Column(db.DateTime, nullable=True) def __init__(self, uid): self.uid = uid def __repr__(self): return f"""<AdminUser uid={self.uid}, created_at={self.created_at}, updated_at={self.updated_at}> """ @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 all_admin_users(cls, include_deleted=False): query = cls.query if include_deleted else cls.query.filter_by(deleted_at=None) return query.order_by(cls.uid).all() @classmethod def is_admin(cls, uid, include_deleted=False): query = cls.query.filter_by(uid=uid) if include_deleted else cls.query.filter_by(uid=uid, deleted_at=None) return query.first() is not None
class Base(db.Model): __abstract__ = True created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) updated_at = db.Column(db.DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
class CanvasCourseSite(db.Model): __tablename__ = 'canvas_course_sites' canvas_course_site_id = db.Column(db.Integer, nullable=False, primary_key=True) section_id = db.Column(db.Integer, nullable=False, primary_key=True) term_id = db.Column(db.Integer, nullable=False, primary_key=True) canvas_course_site_name = db.Column(db.String, nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) def __init__(self, canvas_course_site_id, section_id, term_id, canvas_course_site_name): self.canvas_course_site_id = canvas_course_site_id self.section_id = section_id self.term_id = term_id self.canvas_course_site_name = canvas_course_site_name def __repr__(self): return f"""<CanvasCourseSite canvas_course_site_id={self.canvas_course_site_id} canvas_course_site_name={self.canvas_course_site_name} section_id={self.section_id}, term_id={self.term_id}, created_at={self.created_at} """ @classmethod def get_canvas_course_sites(cls, term_id, section_id): return cls.query.filter_by(term_id=term_id, section_id=section_id).all() @classmethod def refresh_term_data(cls, term_id, canvas_course_sites): for canvas_course_site in cls.query.filter_by(term_id=term_id).all(): db.session.delete(canvas_course_site) for course_site_id, summary in canvas_course_sites.items(): canvas_course_site_name = summary['canvas_course_site_name'] for section_id in summary['section_ids']: db.session.add( cls( canvas_course_site_id=course_site_id, section_id=section_id, term_id=term_id, canvas_course_site_name=canvas_course_site_name, ), ) std_commit() def to_api_json(self): return { 'canvasCourseSiteId': self.canvas_course_site_id, 'canvasCourseSiteName': self.canvas_course_site_name, 'sectionId': self.section_id, 'termId': self.term_id, 'createdAt': to_isoformat(self.created_at), }
class CoursePreference(db.Model): __tablename__ = 'course_preferences' term_id = db.Column(db.Integer, nullable=False, primary_key=True) section_id = db.Column(db.Integer, nullable=False, primary_key=True) has_opted_out = db.Column(db.Boolean, nullable=False, default=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) def __init__(self, term_id, section_id, has_opted_out): self.term_id = term_id self.section_id = section_id self.has_opted_out = has_opted_out def __repr__(self): return f"""<CoursePreferences term_id={self.term_id}, section_id={self.section_id}, has_opted_out={self.has_opted_out} created_at={self.created_at} """ @classmethod def get_section_ids_opted_out(cls, term_id): return [ p.section_id for p in cls.query.filter_by( term_id=term_id, has_opted_out=True).all() ] @classmethod def update_opt_out(cls, term_id, section_id, opt_out): section_ids = CrossListing.get_cross_listed_sections( section_id=section_id, term_id=term_id) + [section_id] criteria = and_(cls.section_id.in_(section_ids), cls.term_id == term_id) for row in cls.query.filter(criteria).all(): row.has_opted_out = opt_out section_ids.remove(row.section_id) for section_id in section_ids: preferences = cls( term_id=term_id, section_id=section_id, has_opted_out=opt_out, ) db.session.add(preferences) std_commit() return cls.query.filter_by(term_id=term_id, section_id=section_id).first() def to_api_json(self): return { 'termId': self.term_id, 'sectionId': self.section_id, 'hasOptedOut': self.has_opted_out, 'createdAt': to_isoformat(self.created_at), }
class Job(Base): __tablename__ = 'jobs' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 disabled = db.Column(db.Boolean, nullable=False) job_schedule_type = db.Column(job_schedule_types, nullable=False) job_schedule_value = db.Column(db.String(80), nullable=False) key = db.Column(db.String(80), nullable=False, unique=True) def __init__(self, disabled, job_schedule_type, job_schedule_value, key): self.disabled = disabled self.job_schedule_type = job_schedule_type self.job_schedule_value = job_schedule_value self.key = key def __repr__(self): return f"""<Job disabled={self.disabled}, job_schedule_type={self.job_schedule_type}, job_schedule_value={self.job_schedule_value}, key={self.key}> """ @classmethod def create(cls, job_schedule_type, job_schedule_value, key, disabled=False): job = cls( job_schedule_type=job_schedule_type, job_schedule_value=job_schedule_value, key=key, disabled=disabled, ) db.session.add(job) std_commit() return job @classmethod def update_disabled(cls, job_id, disable): job = cls.query.filter_by(id=job_id).first() job.disabled = disable db.session.add(job) std_commit() return job @classmethod def update_schedule(cls, job_id, schedule_type, schedule_value): job = cls.query.filter_by(id=job_id).first() job.job_schedule_type = schedule_type job.job_schedule_value = schedule_value db.session.add(job) std_commit() return job @classmethod def get_all(cls, include_disabled=False): if include_disabled: return cls.query.order_by(cls.key).all() else: return cls.query.filter_by(disabled=False).order_by(cls.key).all() @classmethod def get_job(cls, job_id): return cls.query.filter_by(id=job_id).first() @classmethod def get_job_by_key(cls, key): return cls.query.filter_by(key=key).first() def to_api_json(self): return { 'id': self.id, 'disabled': self.disabled, 'key': self.key, 'schedule': { 'type': self.job_schedule_type, 'value': int(self.job_schedule_value) if self.job_schedule_type in ['minutes', 'seconds'] else self.job_schedule_value, }, 'createdAt': to_isoformat(self.created_at), 'updatedAt': to_isoformat(self.updated_at), }
class SisSection(db.Model): __tablename__ = 'sis_sections' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 allowed_units = db.Column(db.String) course_name = db.Column(db.String) course_title = db.Column(db.Text) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) deleted_at = db.Column(db.DateTime) instruction_format = db.Column(db.String) instructor_name = db.Column(db.Text) instructor_role_code = db.Column(db.String) instructor_uid = db.Column(db.String) is_primary = db.Column(db.Boolean) is_principal_listing = db.Column(db.Boolean, nullable=False, default=True) meeting_days = db.Column(db.String) meeting_end_date = db.Column(db.DateTime) meeting_end_time = db.Column(db.String) meeting_location = db.Column(db.String) meeting_start_date = db.Column(db.DateTime) meeting_start_time = db.Column(db.String) section_id = db.Column(db.Integer, nullable=False) section_num = db.Column(db.String) term_id = db.Column(db.Integer, nullable=False) def __init__( self, allowed_units, course_name, course_title, instruction_format, instructor_name, instructor_role_code, instructor_uid, is_primary, meeting_days, meeting_end_date, meeting_end_time, meeting_location, meeting_start_date, meeting_start_time, section_id, section_num, term_id, ): self.allowed_units = allowed_units self.course_name = course_name self.course_title = course_title self.instruction_format = instruction_format self.instructor_name = instructor_name self.instructor_role_code = instructor_role_code self.instructor_uid = instructor_uid self.is_primary = is_primary self.meeting_days = meeting_days self.meeting_end_date = meeting_end_date self.meeting_end_time = meeting_end_time self.meeting_location = meeting_location self.meeting_start_date = meeting_start_date self.meeting_start_time = meeting_start_time self.section_id = section_id self.section_num = section_num self.term_id = term_id def __repr__(self): return f"""<SisSection id={self.id} allowed_units={self.allowed_units}, course_name={self.course_name}, course_title={self.course_title}, instruction_format={self.instruction_format}, instructor_name={self.instructor_name}, instructor_role_code={self.instructor_role_code}, instructor_uid={self.instructor_uid}, is_primary={self.is_primary}, meeting_days={self.meeting_days}, meeting_end_date={self.meeting_end_date}, meeting_end_time={self.meeting_end_time}, meeting_location={self.meeting_location}, meeting_start_date={self.meeting_start_date}, meeting_start_time={self.meeting_start_time}, section_id={self.section_id}, section_num={self.section_num}, term_id={self.term_id}, created_at={self.created_at}> """ @classmethod def set_non_principal_listings(cls, section_ids, term_id): sql = 'UPDATE sis_sections SET is_principal_listing = FALSE WHERE term_id = :term_id AND section_id = ANY(:section_ids)' db.session.execute( text(sql), { 'section_ids': section_ids, 'term_id': term_id, }, ) @classmethod def get_distinct_meeting_locations(cls): sql = """ SELECT DISTINCT meeting_location FROM sis_sections WHERE meeting_location IS NOT NULL AND meeting_location != '' AND instructor_role_code IN ('ICNT', 'PI', 'TNIC') ORDER BY meeting_location """ return [ row['meeting_location'] for row in db.session.execute(text(sql)) ] @classmethod def get_distinct_instructor_uids(cls): sql = 'SELECT DISTINCT instructor_uid FROM sis_sections WHERE instructor_uid IS NOT NULL' return [row['instructor_uid'] for row in db.session.execute(text(sql))] @classmethod def get_course(cls, term_id, section_id, include_deleted=False): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN rooms r ON r.location = s.meeting_location LEFT JOIN instructors i ON i.uid = s.instructor_uid WHERE s.term_id = :term_id AND s.section_id = :section_id AND (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE {'' if include_deleted else ' AND s.deleted_at IS NULL '} ORDER BY s.course_name, s.section_id, s.instructor_uid, r.capability NULLS LAST """ rows = db.session.execute( text(sql), { 'section_id': section_id, 'term_id': term_id, }, ) api_json = _to_api_json(term_id=term_id, rows=rows) return api_json[0] if api_json else None @classmethod def get_course_changes(cls, term_id): sql = """ SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN rooms r ON r.location = s.meeting_location JOIN scheduled d ON d.section_id = s.section_id AND d.term_id = :term_id AND d.deleted_at IS NULL LEFT JOIN instructors i ON i.uid = s.instructor_uid WHERE s.term_id = :term_id AND ( s.deleted_at IS NOT NULL OR ( (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE ) ) ORDER BY s.course_name, s.section_id, s.instructor_uid, r.capability NULLS LAST """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) courses = [] obsolete_instructor_uids = set() for course in _to_api_json(term_id=term_id, rows=rows): scheduled = course['scheduled'] if scheduled['hasObsoleteRoom'] \ or scheduled['hasObsoleteInstructors'] \ or scheduled['hasObsoleteDates'] \ or scheduled['hasObsoleteTimes'] \ or course['deletedAt']: courses.append(course) if scheduled['hasObsoleteInstructors']: obsolete_instructor_uids.update(scheduled['instructorUids']) scheduled['instructors'] = [] if obsolete_instructor_uids: obsolete_instructors = {} instructor_query = 'SELECT uid, first_name, last_name from instructors where uid = any(:uids)' for row in db.session.execute( text(instructor_query), {'uids': list(obsolete_instructor_uids)}): obsolete_instructors[row['uid']] = { 'name': ' '.join([row['first_name'], row['last_name']]), 'uid': row['uid'], } for course in courses: if course['scheduled']['hasObsoleteInstructors']: course['scheduled']['instructors'] = [] for uid in course['scheduled']['instructorUids']: course['scheduled']['instructors'].append( obsolete_instructors.get(uid, { 'name': '', 'uid': uid })) return courses @classmethod def get_courses(cls, term_id, include_deleted=False, section_ids=None): params = {'term_id': term_id} if section_ids is None: # If no section IDs are specified, return any section with at least one eligible room. course_filter = f's.section_id IN ({_sections_with_at_least_one_eligible_room()})' else: course_filter = 's.section_id = ANY(:section_ids)' params['section_ids'] = section_ids sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN rooms r ON r.location = s.meeting_location LEFT JOIN instructors i ON i.uid = s.instructor_uid WHERE {course_filter} AND s.term_id = :term_id AND (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE {'' if include_deleted else ' AND s.deleted_at IS NULL '} ORDER BY s.course_name, s.section_id, s.instructor_uid, r.capability NULLS LAST """ rows = db.session.execute(text(sql), params) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_courses_invited(cls, term_id): sql = """ SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN rooms r ON r.location = s.meeting_location AND r.capability IS NOT NULL JOIN sent_emails e ON e.section_id = s.section_id AND e.term_id = s.term_id AND e.template_type = 'invitation' LEFT JOIN instructors i ON i.uid = s.instructor_uid -- Omit approved courses, scheduled courses and opt-outs. LEFT JOIN approvals a ON a.section_id = s.section_id AND a.term_id = s.term_id AND a.deleted_at IS NULL LEFT JOIN scheduled d ON d.section_id = s.section_id AND d.term_id = s.term_id AND d.deleted_at IS NULL LEFT JOIN course_preferences cp ON cp.section_id = s.section_id AND cp.term_id = s.term_id AND cp.has_opted_out IS TRUE WHERE s.term_id = :term_id AND (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE AND a.section_id IS NULL AND d.section_id IS NULL AND cp.section_id IS NULL AND s.deleted_at IS NULL ORDER BY s.course_name, s.section_id, s.instructor_uid, r.capability NULLS LAST """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_eligible_courses_not_invited(cls, term_id): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN rooms r ON r.location = s.meeting_location LEFT JOIN instructors i ON i.uid = s.instructor_uid -- Omit sent invitations, approved courses, scheduled courses and opt-outs. LEFT JOIN approvals a ON a.section_id = s.section_id AND a.term_id = s.term_id AND a.deleted_at IS NULL LEFT JOIN scheduled d ON d.section_id = s.section_id AND d.term_id = s.term_id AND d.deleted_at IS NULL LEFT JOIN sent_emails e ON e.section_id = s.section_id AND e.term_id = s.term_id AND e.template_type = 'invitation' LEFT JOIN course_preferences cp ON cp.section_id = s.section_id AND cp.term_id = s.term_id AND cp.has_opted_out IS TRUE WHERE s.term_id = :term_id AND s.section_id IN ({_sections_with_at_least_one_eligible_room()}) AND (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE AND a.section_id IS NULL AND d.section_id IS NULL AND e.section_id IS NULL AND cp.section_id IS NULL AND s.deleted_at IS NULL ORDER BY s.course_name, s.section_id, s.instructor_uid, r.capability NULLS LAST """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_courses_opted_out(cls, term_id): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN rooms r ON r.location = s.meeting_location JOIN course_preferences c ON c.section_id = s.section_id AND c.term_id = :term_id AND c.has_opted_out IS TRUE LEFT JOIN instructors i ON i.uid = s.instructor_uid -- Omit scheduled courses. LEFT JOIN scheduled d ON d.section_id = s.section_id AND d.term_id = :term_id AND d.deleted_at IS NULL WHERE d.section_id IS NULL AND s.term_id = :term_id AND (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE AND s.section_id IN ({_sections_with_at_least_one_eligible_room()}) AND s.deleted_at IS NULL ORDER BY s.course_name, s.section_id, s.instructor_uid, r.capability NULLS LAST """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_courses_partially_approved(cls, term_id): # Courses, including scheduled, that have at least one current instructor who has approved, and at least one # current instructor who has not approved. Admins and previous instructors are ignored. sql = """ SELECT DISTINCT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location, r.capability AS room_capability FROM sis_sections s JOIN approvals a ON s.term_id = :term_id AND s.instructor_role_code IN ('ICNT', 'PI', 'TNIC') AND s.is_principal_listing IS TRUE AND a.section_id = s.section_id AND a.term_id = :term_id AND a.deleted_at IS NULL JOIN instructors i ON i.uid = s.instructor_uid -- This second course/instructor join is not for data display purposes, but to screen for the existence -- of any current instructor who has not approved. JOIN sis_sections s2 ON s.term_id = s2.term_id AND s.section_id = s2.section_id AND s2.instructor_role_code IN ('ICNT', 'PI', 'TNIC') JOIN instructors i2 ON i2.uid = s2.instructor_uid AND i2.uid NOT IN ( SELECT approved_by_uid FROM approvals WHERE section_id = s.section_id AND term_id = :term_id ) -- And a final join to ensure that at least one current instructor has approved. JOIN sis_sections s3 ON s3.term_id = s2.term_id AND s3.section_id = s2.section_id AND s3.instructor_uid = a.approved_by_uid JOIN rooms r ON r.location = s.meeting_location WHERE s.deleted_at IS NULL ORDER BY s.course_name, s.section_id, s.instructor_uid, r.capability NULLS LAST """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_courses_queued_for_scheduling(cls, term_id): sql = """ SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN approvals a ON s.term_id = :term_id AND (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE AND a.section_id = s.section_id AND a.term_id = :term_id AND a.deleted_at IS NULL AND ( -- If approved by an admin, the course is considered queued. s.section_id IN ( SELECT DISTINCT s.section_id FROM sis_sections s JOIN approvals a ON a.section_id = s.section_id AND a.term_id = :term_id AND s.term_id = :term_id AND a.deleted_at IS NULL JOIN admin_users au ON a.approved_by_uid = au.uid ) -- If not approved by an admin, we must screen out any courses with an instructor who has not approved. OR s.section_id NOT IN ( SELECT DISTINCT s.section_id FROM sis_sections s LEFT JOIN approvals a ON a.section_id = s.section_id AND a.term_id = :term_id AND a.approved_by_uid = s.instructor_uid AND a.deleted_at IS NULL WHERE s.term_id = :term_id AND (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE AND a.section_id IS NULL ) ) JOIN rooms r ON r.location = s.meeting_location LEFT JOIN instructors i ON i.uid = s.instructor_uid -- Omit scheduled courses. LEFT JOIN scheduled d ON d.section_id = s.section_id AND d.term_id = :term_id AND d.deleted_at IS NULL WHERE d.section_id IS NULL ORDER BY s.course_name, s.section_id, s.instructor_uid, r.capability NULLS LAST """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_courses_per_instructor_uid(cls, term_id, instructor_uid): # Find all section_ids, including cross-listings sql = """ SELECT s.section_id, s.is_principal_listing FROM sis_sections s JOIN instructors i ON i.uid = s.instructor_uid AND s.term_id = :term_id AND s.instructor_uid = :instructor_uid AND s.instructor_role_code IN ('ICNT', 'PI', 'TNIC') WHERE s.deleted_at IS NULL """ non_principal_section_ids = [] section_ids = [] for row in db.session.execute(text(sql), { 'instructor_uid': instructor_uid, 'term_id': term_id }): if row['is_principal_listing'] is False: non_principal_section_ids.append(row['section_id']) else: section_ids.append(row['section_id']) if non_principal_section_ids: # Instructor is associated with cross-listed section_ids sql = f""" SELECT section_id FROM cross_listings WHERE term_id = :term_id AND cross_listed_section_ids && ARRAY[{','.join(str(id_) for id_ in non_principal_section_ids)}] """ for row in db.session.execute(text(sql), { 'section_ids': instructor_uid, 'term_id': term_id }): section_ids.append(row['section_id']) return cls.get_courses(term_id=term_id, section_ids=section_ids) @classmethod def get_courses_per_location(cls, term_id, location): sql = """ SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN rooms r ON r.location = s.meeting_location LEFT JOIN instructors i ON i.uid = s.instructor_uid WHERE s.term_id = :term_id AND (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE AND s.meeting_location = :location AND s.deleted_at IS NULL ORDER BY CASE LEFT(s.meeting_days, 2) WHEN 'MO' THEN 1 WHEN 'TU' THEN 2 WHEN 'WE' THEN 3 WHEN 'TH' THEN 4 WHEN 'FR' THEN 5 WHEN 'SA' THEN 6 WHEN 'SU' THEN 7 ELSE 8 END, s.meeting_start_time, s.section_id """ rows = db.session.execute( text(sql), { 'location': location, 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows, include_rooms=False) @classmethod def get_courses_scheduled(cls, term_id): scheduled_section_ids = list(cls._section_ids_scheduled(term_id)) return cls.get_courses(term_id, include_deleted=True, section_ids=scheduled_section_ids) @classmethod def get_courses_scheduled_standard_dates(cls, term_id): scheduled_section_ids = cls._section_ids_scheduled(term_id) nonstandard_dates_section_ids = cls._section_ids_with_nonstandard_dates( term_id) section_ids = list(scheduled_section_ids - nonstandard_dates_section_ids) return cls.get_courses(term_id, include_deleted=True, section_ids=section_ids) @classmethod def get_courses_scheduled_nonstandard_dates(cls, term_id): scheduled_section_ids = cls._section_ids_scheduled(term_id) nonstandard_dates_section_ids = cls._section_ids_with_nonstandard_dates( term_id) section_ids = list( scheduled_section_ids.intersection(nonstandard_dates_section_ids)) return cls.get_courses(term_id, include_deleted=True, section_ids=section_ids) @classmethod def get_random_co_taught_course(cls, term_id): def _get_section_id(in_eligible_room=True): sql = f""" SELECT section_id FROM ( SELECT s.section_id, s.instructor_uid FROM sis_sections s {'JOIN rooms r ON r.location = s.meeting_location' if in_eligible_room else ''} WHERE s.term_id = :term_id AND s.deleted_at IS NULL AND s.is_principal_listing IS TRUE {'AND r.capability IS NOT NULL' if in_eligible_room else ''} GROUP BY s.section_id, s.instructor_uid ) sections_by_instructor GROUP BY section_id HAVING COUNT(*) > 2 LIMIT 1 """ return db.session.execute(text(sql), {'term_id': term_id}).scalar() section_id = _get_section_id() or _get_section_id( in_eligible_room=False) return cls.get_course(section_id=section_id, term_id=term_id) @classmethod def is_teaching(cls, term_id, uid): sql = """ SELECT id FROM sis_sections WHERE term_id = :term_id AND instructor_uid = :uid AND instructor_role_code IN ('ICNT', 'PI', 'TNIC') AND deleted_at IS NULL LIMIT 1 """ results = db.session.execute( text(sql), { 'uid': uid, 'term_id': term_id, }, ) return results.rowcount > 0 @classmethod def _section_ids_with_nonstandard_dates(cls, term_id): if str(term_id) != str(app.config['CURRENT_TERM_ID']): app.logger.warn( f'Dates for term id {term_id} not configured; cannot query for nonstandard dates.' ) return set() sql = """ SELECT DISTINCT s.section_id FROM sis_sections s JOIN scheduled d ON d.section_id = s.section_id AND d.term_id = s.term_id WHERE s.term_id = :term_id AND (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE AND ( (s.meeting_start_date::text NOT LIKE :term_begin AND d.created_at < s.meeting_start_date) OR s.meeting_end_date::text NOT LIKE :term_end ) AND s.deleted_at IS NULL ORDER BY s.section_id """ rows = db.session.execute( text(sql), { 'term_id': term_id, 'term_begin': f"{app.config['CURRENT_TERM_BEGIN']}%", 'term_end': f"{app.config['CURRENT_TERM_END']}%", }, ) return set([row['section_id'] for row in rows]) @classmethod def _section_ids_scheduled(cls, term_id): sql = """ SELECT DISTINCT s.section_id FROM sis_sections s JOIN rooms r ON r.location = s.meeting_location JOIN scheduled d ON d.section_id = s.section_id AND d.term_id = :term_id AND d.deleted_at IS NULL LEFT JOIN instructors i ON i.uid = s.instructor_uid WHERE s.term_id = :term_id AND (s.instructor_uid IS NULL OR s.instructor_role_code IN ('ICNT', 'PI', 'TNIC')) AND s.is_principal_listing IS TRUE ORDER BY s.section_id """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) return set([row['section_id'] for row in rows])
class Scheduled(db.Model): __tablename__ = 'scheduled' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 section_id = db.Column(db.Integer, nullable=False) term_id = db.Column(db.Integer, nullable=False) alerts = db.Column(ARRAY(email_template_type)) instructor_uids = db.Column(ARRAY(db.String(80)), nullable=False) kaltura_schedule_id = db.Column(db.Integer, nullable=False) meeting_days = db.Column(db.String, nullable=False) meeting_end_date = db.Column(db.DateTime, nullable=False) meeting_end_time = db.Column(db.String, nullable=False) meeting_start_date = db.Column(db.DateTime, nullable=False) meeting_start_time = db.Column(db.String, nullable=False) publish_type = db.Column(publish_type, nullable=False) recording_type = db.Column(recording_type, nullable=False) room_id = db.Column(db.Integer, db.ForeignKey('rooms.id'), nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) deleted_at = db.Column(db.DateTime, nullable=True) def __init__( self, section_id, term_id, instructor_uids, kaltura_schedule_id, meeting_days, meeting_end_date, meeting_end_time, meeting_start_date, meeting_start_time, publish_type_, recording_type_, room_id, ): self.section_id = section_id self.term_id = term_id self.instructor_uids = instructor_uids self.kaltura_schedule_id = kaltura_schedule_id self.meeting_days = meeting_days self.meeting_end_date = meeting_end_date self.meeting_end_time = meeting_end_time self.meeting_start_date = meeting_start_date self.meeting_start_time = meeting_start_time self.publish_type = publish_type_ self.recording_type = recording_type_ self.room_id = room_id def __repr__(self): return f"""<Scheduled id={self.id}, section_id={self.section_id}, term_id={self.term_id}, alerts={', '.join(self.alerts or [])}, instructor_uids={', '.join(self.instructor_uids)}, kaltura_schedule_id={self.kaltura_schedule_id} meeting_days={self.meeting_days}, meeting_end_date={self.meeting_end_date}, meeting_end_time={self.meeting_end_time}, meeting_start_date={self.meeting_start_date}, meeting_start_time={self.meeting_start_time}, publish_type={self.publish_type}, recording_type={self.recording_type}, room_id={self.room_id}, created_at={self.created_at}> """ @classmethod def create( cls, section_id, term_id, instructor_uids, kaltura_schedule_id, meeting_days, meeting_end_date, meeting_end_time, meeting_start_date, meeting_start_time, publish_type_, recording_type_, room_id, ): scheduled = cls( instructor_uids=instructor_uids, kaltura_schedule_id=kaltura_schedule_id, meeting_days=meeting_days, meeting_end_date=meeting_end_date, meeting_end_time=meeting_end_time, meeting_start_date=meeting_start_date, meeting_start_time=meeting_start_time, publish_type_=publish_type_, recording_type_=recording_type_, room_id=room_id, section_id=section_id, term_id=term_id, ) db.session.add(scheduled) std_commit() return scheduled @classmethod def get_all_scheduled(cls, term_id): return cls.query.filter_by(term_id=term_id, deleted_at=None).all() @classmethod def get_scheduled_per_section_ids(cls, section_ids, term_id): criteria = and_(cls.section_id.in_(section_ids), cls.term_id == term_id, cls.deleted_at == None) # noqa: E711 return cls.query.filter(criteria).order_by(cls.created_at).all() @classmethod def get_scheduled(cls, section_id, term_id): return cls.query.filter_by(section_id=section_id, term_id=term_id, deleted_at=None).first() @classmethod def delete(cls, section_id, term_id): sql = """UPDATE scheduled SET deleted_at = now() WHERE term_id = :term_id AND section_id = :section_id AND deleted_at IS NULL""" db.session.execute( text(sql), { 'section_id': section_id, 'term_id': term_id, }, ) @classmethod def add_alert(cls, scheduled_id, template_type): row = cls.query.filter_by(id=scheduled_id).first() if row.alerts: row.alerts = list(set(row.alerts + [template_type])) else: row.alerts = [template_type] db.session.add(row) std_commit() def to_api_json(self, rooms_by_id=None): room_feed = None if self.room_id: if rooms_by_id: room_feed = rooms_by_id.get(self.room_id, None).to_api_json() else: room_feed = Room.get_room(self.room_id).to_api_json() formatted_days = format_days(self.meeting_days) return { 'id': self.id, 'alerts': self.alerts or [], 'createdAt': to_isoformat(self.created_at), 'instructorUids': self.instructor_uids, 'kalturaScheduleId': self.kaltura_schedule_id, 'meetingDays': formatted_days, 'meetingDaysNames': get_names_of_days(formatted_days), 'meetingEndDate': datetime.strftime(self.meeting_end_date, '%Y-%m-%d'), 'meetingEndTime': self.meeting_end_time, 'meetingEndTimeFormatted': format_time(self.meeting_end_time), 'meetingStartDate': datetime.strftime(self.meeting_start_date, '%Y-%m-%d'), 'meetingStartTime': self.meeting_start_time, 'meetingStartTimeFormatted': format_time(self.meeting_start_time), 'publishType': self.publish_type, 'publishTypeName': NAMES_PER_PUBLISH_TYPE[self.publish_type], 'recordingType': self.recording_type, 'recordingTypeName': NAMES_PER_RECORDING_TYPE[self.recording_type], 'room': room_feed, 'sectionId': self.section_id, 'termId': self.term_id, }
class Instructor(Base): __tablename__ = 'instructors' uid = db.Column(db.String(255), primary_key=True) dept_code = db.Column(db.String(80)) email = db.Column(db.String(255)) first_name = db.Column(db.String(255)) last_name = db.Column(db.String(255)) def __init__( self, dept_code, email, first_name, last_name, uid, ): self.dept_code = dept_code self.email = email self.first_name = first_name self.last_name = last_name self.uid = uid def __repr__(self): return f"""<Instructor dept_code={', '.join(self.dept_code)}, email={self.email}, first_name={self.first_name}, last_name={self.last_name}, uid={self.uid}, created_at={self.created_at}, updated_at={self.updated_at}> """ @classmethod def upsert(cls, rows): now = utc_now().strftime('%Y-%m-%dT%H:%M:%S+00') count_per_chunk = 10000 for chunk in range(0, len(rows), count_per_chunk): rows_subset = rows[chunk:chunk + count_per_chunk] query = """ INSERT INTO instructors ( created_at, dept_code, email, first_name, last_name, uid, updated_at ) SELECT created_at, dept_code, email, first_name, last_name, uid, updated_at FROM json_populate_recordset(null::instructors, :json_dumps) ON CONFLICT(uid) DO UPDATE SET dept_code = EXCLUDED.dept_code, email = EXCLUDED.email, first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name; """ data = [{ 'created_at': now, 'dept_code': row['dept_code'], 'email': row['email'], 'first_name': row['first_name'], 'last_name': row['last_name'], 'uid': row['uid'], 'updated_at': now, } for row in rows_subset] db.session.execute(query, {'json_dumps': json.dumps(data)})
class SentEmail(db.Model): __tablename__ = 'sent_emails' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 recipient_uids = db.Column(ARRAY(db.String(80)), nullable=False) section_id = db.Column(db.Integer) template_type = db.Column(email_template_type) term_id = db.Column(db.Integer, nullable=False) sent_at = db.Column(db.DateTime, nullable=False, default=datetime.now) def __init__(self, recipient_uids, section_id, template_type, term_id): self.recipient_uids = recipient_uids self.template_type = template_type self.section_id = section_id self.term_id = term_id def __repr__(self): return f"""<SentEmail id={self.id} recipient_uids={', '.join(self.recipient_uids)} section_id={self.section_id}, template_type={self.template_type} term_id={self.term_id}, sent_at={self.sent_at} """ @classmethod def create(cls, recipient_uids, section_id, template_type, term_id): sent_email = cls( recipient_uids=recipient_uids, section_id=section_id, template_type=template_type, term_id=term_id, ) db.session.add(sent_email) std_commit() return sent_email @classmethod def get_emails_sent_to(cls, uid): return cls.query.filter(cls.recipient_uids.any(uid)).order_by( cls.sent_at).all() @classmethod def get_emails_of_type(cls, section_id, template_type, term_id): return cls.query.filter_by( section_id=section_id, template_type=template_type, term_id=term_id, ).order_by(cls.sent_at).all() def to_api_json(self): return { 'id': self.id, 'recipientUids': self.recipient_uids, 'sectionId': self.section_id, 'templateType': self.template_type, 'templateTypeName': EmailTemplate.get_template_type_options()[self.template_type], 'termId': self.term_id, 'sentAt': to_isoformat(self.sent_at), }
class JobHistory(db.Model): __tablename__ = 'job_history' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 job_key = db.Column(db.String(80), nullable=False) failed = db.Column(db.Boolean, nullable=False, default=False) started_at = db.Column(db.DateTime, nullable=False, default=datetime.now) finished_at = db.Column(db.DateTime) def __init__(self, job_key): self.job_key = job_key self.failed = False self.started_at = datetime.now() def __repr__(self): return f"""<Room id={self.id}, job_key={self.job_key}, failed={self.failed}, started_at={self.started_at}, finished_at={self.finished_at}, """ @classmethod def is_job_running(cls, job_key): return cls.query.filter_by(job_key=job_key, finished_at=None).first() is not None @classmethod def job_started(cls, job_key): row = cls(job_key=job_key) db.session.add(row) std_commit() return row @classmethod def job_finished(cls, id_, failed=False): row = cls.query.filter_by(id=id_).first() row.failed = failed row.finished_at = datetime.now() db.session.add(row) std_commit() return row @classmethod def get_job_history(cls): return cls.query.order_by(desc(cls.started_at)).all() @classmethod def last_successful_run_of(cls, job_key): criteria = and_(cls.job_key == job_key, cls.failed == False, cls.finished_at != None) # noqa: E711, E712 return cls.query.filter(criteria).order_by(desc(cls.finished_at)).limit(1).first() @staticmethod def fail_orphans(): sql = """ UPDATE job_history SET failed = TRUE, finished_at = now() WHERE finished_at IS NULL""" db.session.execute(text(sql)) @staticmethod def expire_old_rows(days): sql = f"DELETE FROM job_history WHERE started_at < (now() - INTERVAL '{days} DAYS')" db.session.execute(text(sql)) def to_api_json(self): return { 'id': self.id, 'jobKey': self.job_key, 'failed': self.failed, 'startedAt': to_isoformat(self.started_at), 'finishedAt': self.finished_at and to_isoformat(self.finished_at), }
class QueuedEmail(db.Model): __tablename__ = 'queued_emails' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 subject_line = db.Column(db.String(255)) message = db.Column(db.Text) recipient = db.Column(JSONB) section_id = db.Column(db.Integer, nullable=False) template_type = db.Column(email_template_type, nullable=False) term_id = db.Column(db.Integer, nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) __table_args__ = (db.UniqueConstraint( 'section_id', 'template_type', 'term_id', name='queued_emails_section_id_template_type_unique_constraint', ),) def __init__(self, section_id, template_type, term_id, recipient, message=None, subject_line=None): self.template_type = template_type self.section_id = section_id self.term_id = term_id self.recipient = recipient self.message = message self.subject_line = subject_line def __repr__(self): return f"""<QueuedEmail id={self.id} section_id={self.section_id}, template_type={self.template_type} term_id={self.term_id}, recipient={self.recipient}, message={self.message}, subject_line={self.subject_line}, created_at={self.created_at} """ @classmethod def create(cls, section_id, template_type, term_id, recipient, message=None, subject_line=None): course = SisSection.get_course(term_id, section_id, include_deleted=True) if not course: app.logger.error(f'Attempt to queue email for unknown course (term_id={term_id}, section_id={section_id})') return if not course['instructors']: app.logger.error(f'Attempt to queue email for course without instructors (term_id={term_id}, section_id={section_id})') return queued_email = cls( section_id=section_id, template_type=template_type, term_id=term_id, recipient=recipient, message=message, subject_line=subject_line, ) course = SisSection.get_course(term_id, queued_email.section_id, include_deleted=True) if not queued_email.is_interpolated() and not queued_email.interpolate(course): app.logger.error(f'Failed to interpolate all required values for queued email ({queued_email})') return db.session.add(queued_email) std_commit() return queued_email @classmethod def delete(cls, queued_email): db.session.delete(queued_email) std_commit() @classmethod def get_all(cls, term_id): return cls.query.filter_by(term_id=term_id).order_by(cls.created_at).all() @classmethod def get_all_section_ids(cls, template_type, term_id): return [row.section_id for row in cls.query.filter_by(template_type=template_type, term_id=term_id).all()] def is_interpolated(self): return not(self.subject_line is None or self.message is None or self.recipient is None) def interpolate(self, course): template = _get_email_template(course=course, template_type=self.template_type) if template: self.subject_line = interpolate_content( course=course, templated_string=template.subject_line, recipient_name=self.recipient['name'], ) self.message = interpolate_content( course=course, templated_string=template.message, recipient_name=self.recipient['name'], ) db.session.add(self) std_commit() # Return True only if all required data has been set. return self.is_interpolated def to_api_json(self): return { 'id': self.id, 'sectionId': self.section_id, 'templateType': self.template_type, 'templateTypeName': EmailTemplate.get_template_type_options()[self.template_type], 'termId': self.term_id, 'createdAt': to_isoformat(self.created_at), }
class Scheduled(db.Model): __tablename__ = 'scheduled' section_id = db.Column(db.Integer, nullable=False, primary_key=True) term_id = db.Column(db.Integer, nullable=False, primary_key=True) cross_listed_section_ids = db.Column(ARRAY(db.Integer)) instructor_uids = db.Column(ARRAY(db.String(80)), nullable=False) meeting_days = db.Column(db.String, nullable=False) meeting_start_time = db.Column(db.String, nullable=False) meeting_end_time = db.Column(db.String, nullable=False) publish_type = db.Column(publish_type, nullable=False) recording_type = db.Column(recording_type, nullable=False) room_id = db.Column(db.Integer, db.ForeignKey('rooms.id'), nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) def __init__( self, section_id, term_id, cross_listed_section_ids, instructor_uids, meeting_days, meeting_start_time, meeting_end_time, publish_type_, recording_type_, room_id, ): self.section_id = section_id self.term_id = term_id self.cross_listed_section_ids = cross_listed_section_ids self.instructor_uids = instructor_uids self.meeting_days = meeting_days self.meeting_start_time = meeting_start_time self.meeting_end_time = meeting_end_time self.publish_type = publish_type_ self.recording_type = recording_type_ self.room_id = room_id def __repr__(self): return f"""<Scheduled section_id={self.section_id}, term_id={self.term_id}, cross_listed_section_ids={self.cross_listed_section_ids}, instructor_uids={', '.join(self.instructor_uids)}, meeting_days={self.meeting_days}, meeting_start_time={self.meeting_start_time}, meeting_end_time={self.meeting_end_time}, publish_type={self.publish_type}, recording_type={self.recording_type}, room_id={self.room_id}, created_at={self.created_at}> """ @classmethod def create( cls, section_id, term_id, cross_listed_section_ids, instructor_uids, meeting_days, meeting_start_time, meeting_end_time, publish_type_, recording_type_, room_id, ): scheduled = cls( cross_listed_section_ids=cross_listed_section_ids, instructor_uids=instructor_uids, meeting_days=meeting_days, meeting_start_time=meeting_start_time, meeting_end_time=meeting_end_time, publish_type_=publish_type_, recording_type_=recording_type_, room_id=room_id, section_id=section_id, term_id=term_id, ) db.session.add(scheduled) std_commit() return scheduled @classmethod def get_all_scheduled(cls, term_id): return cls.query.filter_by(term_id=term_id).all() @classmethod def get_scheduled_per_section_ids(cls, section_ids, term_id): criteria = and_(cls.section_id.in_(section_ids), cls.term_id == term_id) return cls.query.filter(criteria).order_by(cls.created_at).all() @classmethod def get_scheduled(cls, section_id, term_id): return cls.query.filter_by(section_id=section_id, term_id=term_id).first() def to_api_json(self): return { 'createdAt': to_isoformat(self.created_at), 'crossListedSectionIds': self.cross_listed_section_ids, 'instructorUids': self.instructor_uids, 'meetingDays': format_days(self.meeting_days), 'meetingEndTime': format_time(self.meeting_end_time), 'meetingStartTime': format_time(self.meeting_start_time), 'publishType': self.publish_type, 'publishTypeName': NAMES_PER_PUBLISH_TYPE[self.publish_type], 'recordingType': self.recording_type, 'recordingTypeName': NAMES_PER_RECORDING_TYPE[self.recording_type], 'room': Room.get_room(self.room_id).to_api_json() if self.room_id else None, 'sectionId': self.section_id, 'termId': self.term_id, }
class Approval(db.Model): __tablename__ = 'approvals' term_id = db.Column(db.Integer, nullable=False, primary_key=True) section_id = db.Column(db.Integer, nullable=False, primary_key=True) approved_by_uid = db.Column(db.String, nullable=False, primary_key=True) approver_type = db.Column(approver_type, nullable=False) cross_listed_section_ids = db.Column(ARRAY(db.Integer)) room_id = db.Column(db.Integer, db.ForeignKey('rooms.id'), nullable=False) publish_type = db.Column(publish_type, nullable=False) recording_type = db.Column(recording_type, nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) def __init__( self, term_id, section_id, approved_by_uid, approver_type_, cross_listed_section_ids, room_id, publish_type_, recording_type_, ): self.term_id = term_id self.section_id = section_id self.approved_by_uid = approved_by_uid self.approver_type = approver_type_ self.cross_listed_section_ids = cross_listed_section_ids self.room_id = room_id self.publish_type = publish_type_ self.recording_type = recording_type_ def __repr__(self): return f"""<Approval term_id={self.term_id}, section_id={self.section_id}, approved_by_uid={self.approved_by_uid}, approver_type={self.approver_type}, cross_listed_section_ids={self.cross_listed_section_ids} publish_type={self.publish_type}, recording_type={self.recording_type}, room_id={self.room_id}, created_at={self.created_at}> """ @classmethod def create( cls, term_id, section_id, approved_by_uid, approver_type_, cross_listed_section_ids, publish_type_, recording_type_, room_id, ): approval = cls( term_id=term_id, section_id=section_id, approved_by_uid=approved_by_uid, approver_type_=approver_type_, cross_listed_section_ids=cross_listed_section_ids, publish_type_=publish_type_, recording_type_=recording_type_, room_id=room_id, ) db.session.add(approval) std_commit() return approval @classmethod def get_approval(cls, approved_by_uid, section_id, term_id): return cls.query.filter_by(approved_by_uid=approved_by_uid, section_id=section_id, term_id=term_id).first() @classmethod def get_approvals_per_section_ids(cls, section_ids, term_id): criteria = and_(cls.section_id.in_(section_ids), cls.term_id == term_id) return cls.query.filter(criteria).order_by(cls.created_at).all() @classmethod def get_approvals_per_term(cls, term_id): return cls.query.filter_by(term_id=int(term_id)).order_by( cls.section_id, cls.created_at).all() def to_api_json(self): return { 'approvedBy': get_calnet_user_for_uid(app, self.approved_by_uid), 'wasApprovedByAdmin': self.approver_type == 'admin', 'createdAt': to_isoformat(self.created_at), 'crossListedSectionIds': self.cross_listed_section_ids, 'publishType': self.publish_type, 'publishTypeName': NAMES_PER_PUBLISH_TYPE[self.publish_type], 'recordingType': self.recording_type, 'recordingTypeName': NAMES_PER_RECORDING_TYPE[self.recording_type], 'room': Room.get_room(self.room_id).to_api_json() if self.room_id else None, 'sectionId': self.section_id, 'termId': self.term_id, }
class Room(db.Model): __tablename__ = 'rooms' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 capability = db.Column(room_capability_type) is_auditorium = db.Column(db.Boolean, nullable=False) kaltura_resource_id = db.Column(db.Integer) location = db.Column(db.String(255), nullable=False, unique=True) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) def __init__( self, capability, is_auditorium, kaltura_resource_id, location, ): self.capability = capability self.is_auditorium = is_auditorium self.kaltura_resource_id = kaltura_resource_id self.location = location def __repr__(self): return f"""<Room id={self.id}, capability={self.capability}, location={self.location}, is_auditorium={self.is_auditorium}, kaltura_resource_id={self.kaltura_resource_id}, created_at={self.created_at}> """ @classmethod def create(cls, location, is_auditorium=False, kaltura_resource_id=None, capability=None): room = cls( capability=capability, is_auditorium=is_auditorium, kaltura_resource_id=kaltura_resource_id, location=location, ) db.session.add(room) std_commit() return room @classmethod def find_room(cls, location): return cls.query.filter_by(location=location).first() @classmethod def get_room(cls, room_id): return cls.query.filter_by(id=room_id).first() @classmethod def get_rooms(cls, room_ids): return cls.query.filter(cls.id.in_(room_ids)).all() @classmethod def get_rooms_in_locations(cls, locations): return cls.query.filter(cls.location.in_(locations)).all() @classmethod def all_rooms(cls): return cls.query.order_by(cls.capability, cls.location).all() @classmethod def total_room_count(cls): return db.session.query(func.count(cls.id)).scalar() @classmethod def get_all_locations(cls): result = db.session.execute(text('SELECT location FROM rooms')) return [row['location'] for row in result] @classmethod def get_room_id(cls, section_id, term_id): sql = """ SELECT r.id AS room_id FROM rooms r JOIN sis_sections s ON s.meeting_location = r.location WHERE s.section_id = :section_id AND s.term_id = :term_id """ rows = db.session.execute( text(sql), { 'section_id': section_id, 'term_id': term_id, }, ) ids_ = [row['room_id'] for row in rows] return ids_[0] if ids_ else None @classmethod def update_capability(cls, room_id, capability): room = cls.query.filter_by(id=room_id).first() room.capability = capability db.session.add(room) std_commit() return room @classmethod def update_kaltura_resource_mappings(cls, kaltura_resource_ids_per_room): # First, set kaltura_resource_id = null on all rows cls.query.update({cls.kaltura_resource_id: None}) # Next, insert the latest mappings all_rooms_per_id = dict((room.id, room) for room in cls.all_rooms()) for room_id, kaltura_resource_id in kaltura_resource_ids_per_room.items( ): room = all_rooms_per_id[room_id] room.kaltura_resource_id = kaltura_resource_id db.session.add(room) std_commit() @classmethod def set_auditorium(cls, room_id, is_auditorium): room = cls.query.filter_by(id=room_id).first() room.is_auditorium = is_auditorium db.session.add(room) std_commit() return room @classmethod def get_room_capability_options(cls): return { 'screencast': 'Screencast', 'screencast_and_video': 'Screencast + Video', } def to_api_json(self): recording_type_options = { 'presentation_audio': 'Presentation + Audio', } if self.is_auditorium: recording_type_options['presenter_audio'] = 'Presenter + Audio' recording_type_options[ 'presenter_presentation_audio'] = 'Presenter + Presentation + Audio' return { 'id': self.id, 'location': self.location, 'capability': self.capability, 'capabilityName': self.get_room_capability_options()[self.capability] if self.capability else None, 'createdAt': to_isoformat(self.created_at), 'isAuditorium': self.is_auditorium, 'kalturaResourceId': self.kaltura_resource_id, 'recordingTypeOptions': recording_type_options, }
class EmailTemplate(Base): __tablename__ = 'email_templates' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 template_type = db.Column(email_template_type, nullable=False) name = db.Column(db.String(255), nullable=False, unique=True) subject_line = db.Column(db.String(255), nullable=False) message = db.Column(db.Text, nullable=False) def __init__( self, template_type, name, subject_line, message, ): self.template_type = template_type self.name = name self.subject_line = subject_line self.message = message def __repr__(self): return f"""<EmailTemplate id={self.id}, template_type={self.template_type}, name={self.name}, subject_line={self.subject_line} message={self.message}> """ @classmethod def create(cls, template_type, name, subject_line, message): email_template = cls( template_type=template_type, name=name, subject_line=subject_line, message=message, ) db.session.add(email_template) std_commit() return email_template @classmethod def delete_template(cls, template_id): db.session.delete(cls.query.filter_by(id=template_id).first()) std_commit() @classmethod def get_template(cls, template_id): return cls.query.filter_by(id=template_id).first() @classmethod def get_template_by_type(cls, template_type): return cls.query.filter_by(template_type=template_type).first() @classmethod def get_all_templates_names(cls): return cls.query.with_entities(cls.id, cls.name).order_by(cls.name).all() @classmethod def all_templates(cls): return cls.query.order_by().all() @classmethod def update(cls, template_id, template_type, name, subject_line, message): email_template = cls.query.filter_by(id=template_id).first() email_template.template_type = template_type email_template.name = name email_template.subject_line = subject_line email_template.message = message db.session.add(email_template) std_commit() return email_template @classmethod def get_template_type_options(cls): return { 'admin_alert_instructor_change': 'Admin alert of instructor change', 'admin_alert_room_change': 'Admin alert: Room change', 'invitation': 'Invitation', 'notify_instructor_of_changes': 'Notify instructor of changes', 'recordings_scheduled': 'Recordings scheduled', 'room_change_no_longer_eligible': 'Room change: No longer eligible', 'waiting_for_approval': 'Waiting for approval', } def to_api_json(self): return { 'id': self.id, 'templateType': self.template_type, 'typeName': self.get_template_type_options()[self.template_type], 'name': self.name, 'subjectLine': self.subject_line, 'message': self.message, 'createdAt': to_isoformat(self.created_at), 'updatedAt': to_isoformat(self.updated_at), }
class CrossListing(db.Model): __tablename__ = 'cross_listings' term_id = db.Column(db.Integer, nullable=False, primary_key=True) section_id = db.Column(db.Integer, nullable=False, primary_key=True) cross_listed_section_ids = db.Column(ARRAY(db.Integer), nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) def __init__( self, section_id, term_id, cross_listed_section_ids, ): self.section_id = section_id self.term_id = term_id self.cross_listed_section_ids = cross_listed_section_ids def __repr__(self): return f"""<CrossListing section_id={self.section_id}, term_id={self.term_id}, cross_listed_section_ids={','.join(self.cross_listed_section_ids)}, created_at={self.created_at}> """ @classmethod def create( cls, term_id, section_id, cross_listed_section_ids, ): approval = cls( section_id=section_id, term_id=term_id, cross_listed_section_ids=cross_listed_section_ids, ) db.session.add(approval) std_commit() return approval @classmethod def get_cross_listed_sections(cls, section_id, term_id): row = cls.query.filter_by(section_id=section_id, term_id=term_id).first() return row.cross_listed_section_ids if row else [] @classmethod def refresh(cls, term_id): # First group section IDs by schedule (time and location) sql = f""" SELECT section_id, trim(concat(meeting_days, meeting_end_date, meeting_end_time, meeting_location, meeting_start_date, meeting_start_time)) as schedule FROM sis_sections WHERE term_id = :term_id AND meeting_days <> '' AND meeting_end_date <> '' AND meeting_end_time <> '' AND meeting_location <> '' AND meeting_start_date <> '' AND meeting_start_time <> '' """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) section_ids_per_schedule = {} for row in rows: schedule = row['schedule'] if schedule not in section_ids_per_schedule: section_ids_per_schedule[schedule] = set() section_ids_per_schedule[schedule].add(row['section_id']) # Begin refresh by deleting existing rows per term_id db.session.execute(cls.__table__.delete().where(cls.term_id == term_id)) # Next, populate 'cross_listings' table. # If section_ids (123, 234, 345) comprise a set of cross-listed section_ids then we construct: # { # 123: [234, 345], # 234: [123, 345], # 345: [123, 234] # } # The 'cross_listings' table will get the same three rows. cross_listings = {} for cross_listed_section_ids in list(section_ids_per_schedule.values()): if len(cross_listed_section_ids) > 1: for section_id in cross_listed_section_ids: cross_listings[section_id] = [str(id_) for id_ in cross_listed_section_ids if id_ != section_id] def chunks(data, chunk_size=500): iterator = iter(data) for i in range(0, len(data), chunk_size): yield {k: data[k] for k in islice(iterator, chunk_size)} for cross_listings_chunk in chunks(cross_listings): cross_listing_count = len(cross_listings_chunk) query = 'INSERT INTO cross_listings (term_id, section_id, cross_listed_section_ids, created_at) VALUES' for index, (section_id, cross_listed_section_ids) in enumerate(cross_listings_chunk.items()): query += f' (:term_id, {section_id}, ' + "'{" + ', '.join(cross_listed_section_ids) + "}', now())" if index < cross_listing_count - 1: query += ',' db.session.execute(query, {'term_id': term_id}) std_commit() def to_api_json(self): return { 'sectionId': self.section_id, 'termId': self.term_id, 'crossListedSectionIds': self.cross_listed_section_ids, 'createdAt': to_isoformat(self.created_at), }
class SisSection(db.Model): __tablename__ = 'sis_sections' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 allowed_units = db.Column(db.String) course_name = db.Column(db.String) course_title = db.Column(db.Text) instruction_format = db.Column(db.String) instructor_name = db.Column(db.Text) instructor_role_code = db.Column(db.String) instructor_uid = db.Column(db.String) is_primary = db.Column(db.Boolean) meeting_days = db.Column(db.String) meeting_end_date = db.Column(db.String) meeting_end_time = db.Column(db.String) meeting_location = db.Column(db.String) meeting_start_date = db.Column(db.String) meeting_start_time = db.Column(db.String) section_id = db.Column(db.Integer, nullable=False) section_num = db.Column(db.String) term_id = db.Column(db.Integer, nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) def __init__( self, allowed_units, course_name, course_title, instruction_format, instructor_name, instructor_role_code, instructor_uid, is_primary, meeting_days, meeting_end_date, meeting_end_time, meeting_location, meeting_start_date, meeting_start_time, section_id, section_num, term_id, ): self.allowed_units = allowed_units self.course_name = course_name self.course_title = course_title self.instruction_format = instruction_format self.instructor_name = instructor_name self.instructor_role_code = instructor_role_code self.instructor_uid = instructor_uid self.is_primary = is_primary self.meeting_days = meeting_days self.meeting_end_date = meeting_end_date self.meeting_end_time = meeting_end_time self.meeting_location = meeting_location self.meeting_start_date = meeting_start_date self.meeting_start_time = meeting_start_time self.section_id = section_id self.section_num = section_num self.term_id = term_id def __repr__(self): return f"""<SisSection id={self.id} allowed_units={self.allowed_units}, course_name={self.course_name}, course_title={self.course_title}, instruction_format={self.instruction_format}, instructor_name={self.instructor_name}, instructor_role_code={self.instructor_role_code}, instructor_uid={self.instructor_uid}, is_primary={self.is_primary}, meeting_days={self.meeting_days}, meeting_end_date={self.meeting_end_date}, meeting_end_time={self.meeting_end_time}, meeting_location={self.meeting_location}, meeting_start_date={self.meeting_start_date}, meeting_start_time={self.meeting_start_time}, section_id={self.section_id}, section_num={self.section_num}, term_id={self.term_id}, created_at={self.created_at}> """ @classmethod def get_meeting_times(cls, term_id, section_id): course = cls.query.filter_by(term_id=term_id, section_id=section_id).first() if course: return course.meeting_days, course.meeting_start_time, course.meeting_end_time else: return None @classmethod def get_distinct_meeting_locations(cls): sql = """ SELECT DISTINCT meeting_location FROM sis_sections WHERE meeting_location IS NOT NULL AND meeting_location != '' AND instructor_role_code IN ('ICNT', 'PI', 'TNIC') ORDER BY meeting_location """ return [ row['meeting_location'] for row in db.session.execute(text(sql)) ] @classmethod def get_distinct_instructor_uids(cls): sql = 'SELECT DISTINCT instructor_uid FROM sis_sections WHERE instructor_uid IS NOT NULL' return [row['instructor_uid'] for row in db.session.execute(text(sql))] @classmethod def get_instructor_uids(cls, term_id, section_id): sql = """ SELECT DISTINCT instructor_uid FROM sis_sections WHERE term_id = :term_id AND section_id = :section_id AND instructor_uid IS NOT NULL """ rows = db.session.execute( text(sql), { 'section_id': section_id, 'term_id': term_id, }, ) return [row['instructor_uid'] for row in rows] @classmethod def get_courses_per_location(cls, term_id, location): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN instructors i ON i.uid = s.instructor_uid JOIN rooms r ON r.location = s.meeting_location WHERE s.term_id = :term_id AND s.meeting_location = :location ORDER BY s.course_title, s.section_id, s.instructor_uid """ rows = db.session.execute( text(sql), { 'location': location, 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows, include_rooms=False) @classmethod def get_courses_invited(cls, term_id): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN instructors i ON i.uid = s.instructor_uid JOIN rooms r ON r.location = s.meeting_location JOIN sent_emails e ON e.section_id = s.section_id WHERE s.term_id = :term_id AND s.instructor_role_code IN ('ICNT', 'PI', 'TNIC') AND e.template_type = 'invitation' AND r.capability IS NOT NULL AND NOT EXISTS ( SELECT FROM approvals WHERE section_id = s.section_id AND term_id = s.term_id ) AND NOT EXISTS ( SELECT FROM scheduled WHERE section_id = s.section_id AND term_id = s.term_id ) AND NOT EXISTS ( SELECT FROM course_preferences WHERE section_id = s.section_id AND term_id = s.term_id AND has_opted_out IS TRUE ) ORDER BY s.course_title, s.section_id, s.instructor_uid """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_courses_opted_out(cls, term_id): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN instructors i ON i.uid = s.instructor_uid JOIN rooms r ON r.location = s.meeting_location JOIN course_preferences c ON c.section_id = s.section_id AND c.term_id = :term_id WHERE s.term_id = :term_id AND c.has_opted_out IS TRUE AND s.instructor_role_code IN ('ICNT', 'PI', 'TNIC') AND r.capability IS NOT NULL AND NOT EXISTS ( SELECT FROM scheduled WHERE section_id = s.section_id AND term_id = s.term_id ) ORDER BY s.course_title, s.section_id, s.instructor_uid """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_eligible_courses_not_invited(cls, term_id): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN instructors i ON i.uid = s.instructor_uid JOIN rooms r ON r.location = s.meeting_location WHERE s.term_id = :term_id AND s.instructor_role_code IN ('ICNT', 'PI', 'TNIC') AND r.capability IS NOT NULL AND NOT EXISTS ( SELECT FROM sent_emails WHERE template_type = 'invitation' AND section_id = s.section_id ) AND NOT EXISTS ( SELECT FROM approvals WHERE section_id = s.section_id AND term_id = s.term_id ) AND NOT EXISTS ( SELECT FROM scheduled WHERE section_id = s.section_id AND term_id = s.term_id ) AND NOT EXISTS ( SELECT FROM course_preferences WHERE section_id = s.section_id AND term_id = s.term_id AND has_opted_out IS TRUE ) ORDER BY s.course_title, s.section_id, s.instructor_uid """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_course(cls, term_id, section_id): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN instructors i ON i.uid = s.instructor_uid JOIN rooms r ON r.location = s.meeting_location WHERE s.term_id = :term_id AND s.section_id = :section_id AND s.instructor_role_code IN ('ICNT', 'PI', 'TNIC') ORDER BY s.course_title, s.section_id, s.instructor_uid """ rows = db.session.execute( text(sql), { 'section_id': section_id, 'term_id': term_id, }, ) api_json = _to_api_json(term_id=term_id, rows=rows) return api_json[0] if api_json else None @classmethod def get_course_changes(cls, term_id): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN instructors i ON i.uid = s.instructor_uid JOIN rooms r ON r.location = s.meeting_location JOIN scheduled d ON d.section_id = s.section_id AND d.term_id = :term_id WHERE s.term_id = :term_id ORDER BY s.course_title, s.section_id, s.instructor_uid """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) courses = [] for course in _to_api_json(term_id=term_id, rows=rows): scheduled = course['scheduled'] if scheduled['hasObsoleteRoom'] \ or scheduled['hasObsoleteInstructors'] \ or scheduled['hasObsoleteMeetingTimes']: courses.append(course) return courses @classmethod def get_courses(cls, term_id, section_ids): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN instructors i ON i.uid = s.instructor_uid JOIN rooms r ON r.location = s.meeting_location WHERE s.term_id = :term_id AND s.section_id = ANY(:section_ids) AND s.instructor_role_code IN ('ICNT', 'PI', 'TNIC') ORDER BY s.course_title, s.section_id, s.instructor_uid """ rows = db.session.execute( text(sql), { 'section_ids': section_ids, 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_courses_partially_approved(cls, term_id): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN approvals a ON a.section_id = s.section_id AND a.term_id = :term_id JOIN instructors i ON i.uid = s.instructor_uid JOIN sent_emails e ON e.section_id = s.section_id JOIN rooms r ON r.location = s.meeting_location WHERE s.term_id = :term_id AND e.template_type = 'invitation' AND r.capability IS NOT NULL AND i.uid NOT IN ( SELECT approved_by_uid FROM approvals WHERE section_id = s.section_id AND term_id = :term_id ) ORDER BY s.course_title, s.section_id, s.instructor_uid """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) return _to_api_json(term_id=term_id, rows=rows) @classmethod def get_courses_per_instructor_uid(cls, term_id, instructor_uid): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN instructors i ON i.uid = s.instructor_uid JOIN rooms r ON r.location = s.meeting_location WHERE s.term_id = :term_id AND s.instructor_uid = :instructor_uid AND s.instructor_role_code IN ('ICNT', 'PI', 'TNIC') ORDER BY s.course_title, s.section_id, s.instructor_uid """ rows = db.session.execute( text(sql), { 'instructor_uid': instructor_uid, 'term_id': term_id, }, ) section_ids = [] for row in rows: section_ids.append(row['section_id']) return cls.get_courses(term_id=term_id, section_ids=section_ids) @classmethod def get_courses_scheduled(cls, term_id): sql = f""" SELECT s.*, i.dept_code AS instructor_dept_code, i.email AS instructor_email, i.first_name || ' ' || i.last_name AS instructor_name, i.uid AS instructor_uid, r.id AS room_id, r.location AS room_location FROM sis_sections s JOIN instructors i ON i.uid = s.instructor_uid JOIN rooms r ON r.location = s.meeting_location JOIN scheduled d ON d.section_id = s.section_id AND d.term_id = :term_id WHERE s.term_id = :term_id ORDER BY s.course_title, s.section_id, s.instructor_uid """ rows = db.session.execute( text(sql), { 'term_id': term_id, }, ) section_ids = [] for row in rows: section_ids.append(row['section_id']) return cls.get_courses(term_id, section_ids) @classmethod def refresh(cls, sis_sections, term_id): db.session.execute( cls.__table__.delete().where(cls.term_id == term_id)) now = utc_now().strftime('%Y-%m-%dT%H:%M:%S+00') count_per_chunk = 10000 for chunk in range(0, len(sis_sections), count_per_chunk): rows_subset = sis_sections[chunk:chunk + count_per_chunk] query = """ INSERT INTO sis_sections ( allowed_units, course_name, course_title, created_at, instruction_format, instructor_name, instructor_role_code, instructor_uid, is_primary, meeting_days, meeting_end_date, meeting_end_time, meeting_location, meeting_start_date, meeting_start_time, section_id, section_num, term_id ) SELECT allowed_units, course_name, course_title, created_at, instruction_format, instructor_name, instructor_role_code, instructor_uid, is_primary, meeting_days, meeting_end_date, meeting_end_time, meeting_location, meeting_start_date, meeting_start_time, section_id, section_num, term_id FROM json_populate_recordset(null::sis_sections, :json_dumps) """ data = [{ 'allowed_units': row['allowed_units'], 'course_name': row['course_name'], 'course_title': row['course_title'], 'created_at': now, 'instruction_format': row['instruction_format'], 'instructor_name': row['instructor_name'], 'instructor_role_code': row['instructor_role_code'], 'instructor_uid': row['instructor_uid'], 'is_primary': row['is_primary'], 'meeting_days': row['meeting_days'], 'meeting_end_date': row['meeting_end_date'], 'meeting_end_time': row['meeting_end_time'], 'meeting_location': row['meeting_location'], 'meeting_start_date': row['meeting_start_date'], 'meeting_start_time': row['meeting_start_time'], 'section_id': int(row['section_id']), 'section_num': row['section_num'], 'term_id': int(row['term_id']), } for row in rows_subset] db.session.execute(query, {'json_dumps': json.dumps(data)})
class Approval(db.Model): __tablename__ = 'approvals' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 approved_by_uid = db.Column(db.String, nullable=False) approver_type = db.Column(approver_type, nullable=False) course_display_name = db.Column(db.String, nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) deleted_at = db.Column(db.DateTime, nullable=True) publish_type = db.Column(publish_type, nullable=False) recording_type = db.Column(recording_type, nullable=False) room_id = db.Column(db.Integer, db.ForeignKey('rooms.id'), nullable=False) section_id = db.Column(db.Integer, nullable=False) term_id = db.Column(db.Integer, nullable=False) def __init__( self, approved_by_uid, approver_type_, course_display_name, publish_type_, recording_type_, room_id, section_id, term_id, ): self.approved_by_uid = approved_by_uid self.approver_type = approver_type_ self.course_display_name = course_display_name self.publish_type = publish_type_ self.recording_type = recording_type_ self.room_id = room_id self.section_id = section_id self.term_id = term_id def __repr__(self): return f"""<Approval id={self.id}, approved_by_uid={self.approved_by_uid}, approver_type={self.approver_type}, course_display_name={self.course_display_name}, created_at={self.created_at}, publish_type={self.publish_type}, recording_type={self.recording_type}, room_id={self.room_id}, section_id={self.section_id}, term_id={self.term_id}> """ @classmethod def create( cls, approved_by_uid, approver_type_, course_display_name, publish_type_, recording_type_, room_id, section_id, term_id, ): approval = cls( approved_by_uid=approved_by_uid, approver_type_=approver_type_, course_display_name=course_display_name, publish_type_=publish_type_, recording_type_=recording_type_, room_id=room_id, section_id=section_id, term_id=term_id, ) db.session.add(approval) std_commit() return approval @classmethod def get_approval(cls, approved_by_uid, section_id, term_id): return cls.query.filter_by(approved_by_uid=approved_by_uid, section_id=section_id, term_id=term_id, deleted_at=None).first() @classmethod def get_approvals(cls, section_id, term_id): return cls.query.filter_by(section_id=section_id, term_id=term_id, deleted_at=None).all() @classmethod def get_approvals_per_section_ids(cls, section_ids, term_id): criteria = and_(cls.section_id.in_(section_ids), cls.term_id == term_id, cls.deleted_at == None) # noqa: E711 return cls.query.filter(criteria).order_by(cls.created_at).all() @classmethod def get_approvals_per_term(cls, term_id): return cls.query.filter_by(term_id=int(term_id), deleted_at=None).order_by( cls.section_id, cls.created_at).all() @classmethod def delete(cls, section_id, term_id): sql = """UPDATE approvals SET deleted_at = now() WHERE term_id = :term_id AND section_id = :section_id AND deleted_at IS NULL""" db.session.execute( text(sql), { 'section_id': section_id, 'term_id': term_id, }, ) def to_api_json(self, rooms_by_id=None): room_feed = None if self.room_id: if rooms_by_id: room_feed = rooms_by_id.get(self.room_id, None).to_api_json() else: room_feed = Room.get_room(self.room_id).to_api_json() return { 'approvedBy': self.approved_by_uid, 'courseDisplayName': self.course_display_name, 'createdAt': to_isoformat(self.created_at), 'publishType': self.publish_type, 'publishTypeName': NAMES_PER_PUBLISH_TYPE[self.publish_type], 'recordingType': self.recording_type, 'recordingTypeName': NAMES_PER_RECORDING_TYPE[self.recording_type], 'room': room_feed, 'sectionId': self.section_id, 'termId': self.term_id, 'wasApprovedByAdmin': self.approver_type == 'admin', }
class CrossListing(db.Model): __tablename__ = 'cross_listings' term_id = db.Column(db.Integer, nullable=False, primary_key=True) section_id = db.Column(db.Integer, nullable=False, primary_key=True) cross_listed_section_ids = db.Column(ARRAY(db.Integer), nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) def __init__( self, section_id, term_id, cross_listed_section_ids, ): self.section_id = section_id self.term_id = term_id self.cross_listed_section_ids = cross_listed_section_ids def __repr__(self): return f"""<CrossListing section_id={self.section_id}, term_id={self.term_id}, cross_listed_section_ids={','.join(self.cross_listed_section_ids)}, created_at={self.created_at}> """ @classmethod def create( cls, cross_listed_section_ids, section_id, term_id, ): cross_listing = cls( cross_listed_section_ids=cross_listed_section_ids, section_id=section_id, term_id=term_id, ) db.session.add(cross_listing) std_commit() return cross_listing @classmethod def get_cross_listed_sections(cls, section_id, term_id): row = cls.query.filter_by(section_id=section_id, term_id=term_id).first() return row.cross_listed_section_ids if row else [] @classmethod def get_cross_listings_for_section_ids(cls, section_ids, term_id): criteria = and_(cls.section_id.in_(section_ids), cls.term_id == term_id) rows = cls.query.filter(criteria).all() return {row.section_id: row.cross_listed_section_ids for row in rows} def to_api_json(self): return { 'sectionId': self.section_id, 'termId': self.term_id, 'crossListedSectionIds': self.cross_listed_section_ids, 'createdAt': to_isoformat(self.created_at), }
class QueuedEmail(db.Model): __tablename__ = 'queued_emails' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 section_id = db.Column(db.Integer, nullable=False) template_type = db.Column(email_template_type, nullable=False) term_id = db.Column(db.Integer, nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=datetime.now) __table_args__ = (db.UniqueConstraint( 'section_id', 'template_type', 'term_id', name='queued_emails_section_id_template_type_unique_constraint', ),) def __init__(self, section_id, template_type, term_id): self.template_type = template_type self.section_id = section_id self.term_id = term_id def __repr__(self): return f"""<QueuedEmail id={self.id} section_id={self.section_id}, template_type={self.template_type} term_id={self.term_id}, created_at={self.created_at} """ @classmethod def create(cls, section_id, template_type, term_id): queued_email = cls( section_id=section_id, template_type=template_type, term_id=term_id, ) db.session.add(queued_email) std_commit() return queued_email @classmethod def delete(cls, queued_email): db.session.delete(queued_email) std_commit() @classmethod def get_all(cls, term_id): return cls.query.filter_by(term_id=term_id).order_by(cls.created_at).all() @classmethod def get_all_section_ids(cls, template_type, term_id): return [row.section_id for row in cls.query.filter_by(template_type=template_type, term_id=term_id).all()] def to_api_json(self): return { 'id': self.id, 'sectionId': self.section_id, 'templateType': self.template_type, 'templateTypeName': EmailTemplate.get_template_type_options()[self.template_type], 'termId': self.term_id, 'createdAt': to_isoformat(self.created_at), }
class Blackout(Base): __tablename__ = 'blackouts' id = db.Column(db.Integer, nullable=False, primary_key=True) # noqa: A003 name = db.Column(db.String(255), nullable=False, unique=True) start_date = db.Column(db.DateTime, nullable=False) end_date = db.Column(db.DateTime, nullable=False) def __init__(self, name, start_date, end_date): self.name = name self.start_date = start_date self.end_date = end_date def __repr__(self): return f"""<Blackout id={self.id}, name={self.name}, start_date={to_isoformat(self.start_date)}, end_date={to_isoformat(self.end_date)}, """ @classmethod def create(cls, name, start_date, end_date): blackout = cls( name=name, start_date=start_date, end_date=end_date, ) db.session.add(blackout) std_commit() return blackout @classmethod def delete_blackout(cls, blackout_id): db.session.delete(cls.query.filter_by(id=blackout_id).first()) std_commit() @classmethod def get_blackout(cls, blackout_id): return cls.query.filter_by(id=blackout_id).first() @classmethod def get_all_blackouts_names(cls): return cls.query.with_entities(cls.id, cls.name).order_by(cls.name).all() @classmethod def all_blackouts(cls): return cls.query.order_by().all() @classmethod def update(cls, blackout_id, name, start_date, end_date): blackout = cls.query.filter_by(id=blackout_id).first() blackout.name = name blackout.start_date = start_date blackout.end_date = end_date db.session.add(blackout) std_commit() return blackout def to_api_json(self): def _format(date): return localize_datetime(date).strftime('%Y-%m-%d') return { 'id': self.id, 'name': self.name, 'startDate': _format(self.start_date), 'endDate': _format(self.end_date), 'createdAt': to_isoformat(self.created_at), 'updatedAt': to_isoformat(self.updated_at), }