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),
}
