def test_course_with_partial_approval(self, client, db, admin_session):
"""Course with two instructors and one approval."""
with test_approvals_workflow(app):
# If course has approvals but not scheduled then it will show up in the feed.
approved_by_uid = _get_instructor_uids(section_id=section_1_id, term_id=self.term_id)[0]
room_id = Room.get_room_id(section_id=section_1_id, term_id=self.term_id)
Approval.create(
approved_by_uid=approved_by_uid,
approver_type_='instructor',
cross_listed_section_ids=[],
publish_type_='canvas',
recording_type_='presentation_audio',
room_id=room_id,
section_id=section_1_id,
term_id=self.term_id,
)
std_commit(allow_test_environment=True)
api_json = api_get_course(
client,
term_id=self.term_id,
section_id=section_1_id,
)
assert [i['uid'] for i in api_json['instructors']] == ['234567', '8765432']
approvals = api_json['approvals']
assert len(approvals) == 1
assert approved_by_uid == approvals[0]['approvedBy']['uid']
assert api_json['room']['id'] == room_id
assert api_json['room']['location'] == 'Barrows 106'
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
def test_course_has_opted_out(self):
"""Do not send email to courses that have opted out."""
def _emails_sent():
return _get_emails_sent(email_template_type=email_template_type,
section_id=section_id,
term_id=term_id)
term_id = app.config['CURRENT_TERM_ID']
section_id = 50000
CoursePreference.update_opt_out(term_id=term_id,
section_id=section_id,
opt_out=True)
email_template_type = 'invitation'
recipient = {
'name': 'William Peter Blatty',
'uid': '10001',
}
QueuedEmail.create(section_id,
email_template_type,
term_id,
recipient=recipient)
std_commit(allow_test_environment=True)
emails_sent_before = _emails_sent()
# Run the job
QueuedEmailsJob(simply_yield).run()
std_commit(allow_test_environment=True)
# Expect no emails sent
emails_sent_after = _emails_sent()
assert len(emails_sent_after) == len(emails_sent_before)
assert list(map(lambda e: e.id, emails_sent_before)) == list(
map(lambda e: e.id, emails_sent_after))
def test_scheduled_filter(self, client, admin_session):
"""Scheduled filter: Courses with recordings scheduled."""
with test_approvals_workflow(app):
# Send invites
for section_id in [section_1_id, section_6_id]:
self._send_invitation_email(section_id)
self._create_approval(section_id)
# Feed will only include courses that were scheduled.
mock_scheduled(
section_id=section_1_id,
term_id=self.term_id,
)
# Deleted records will be ignored
mock_scheduled(
section_id=section_2_id,
term_id=self.term_id,
)
Scheduled.delete(section_id=section_2_id, term_id=self.term_id)
std_commit(allow_test_environment=True)
api_json = self._api_courses(client, term_id=self.term_id, filter_='Scheduled')
assert len(api_json) == 1
course = _find_course(api_json=api_json, section_id=section_1_id)
assert course['approvalStatus'] == 'Partially Approved'
assert course['schedulingStatus'] == 'Scheduled'
assert not _find_course(api_json=api_json, section_id=section_6_id)
def test_has_obsolete_instructors(self, client, admin_session):
"""Admins can see instructor changes that might disrupt scheduled recordings."""
with test_approvals_workflow(app):
meeting = get_eligible_meeting(section_id=section_1_id, term_id=self.term_id)
instructor_uids = get_instructor_uids(term_id=self.term_id, section_id=section_1_id)
# Course has multiple instructors; we will schedule using only one instructor UID.
assert len(instructor_uids) > 1
scheduled_with_uid = instructor_uids[0]
Scheduled.create(
instructor_uids=[scheduled_with_uid],
kaltura_schedule_id=random.randint(1, 10),
meeting_days=meeting['days'],
meeting_end_date=get_recording_end_date(meeting),
meeting_end_time=meeting['endTime'],
meeting_start_date=get_recording_start_date(meeting, return_today_if_past_start=True),
meeting_start_time=meeting['startTime'],
publish_type_='kaltura_my_media',
recording_type_='presenter_audio',
room_id=Room.get_room_id(section_id=section_1_id, term_id=self.term_id),
section_id=section_1_id,
term_id=self.term_id,
)
std_commit(allow_test_environment=True)
api_json = self._api_course_changes(client, term_id=self.term_id)
course = _find_course(api_json=api_json, section_id=section_1_id)
assert course
assert course['scheduled']['hasObsoleteRoom'] is False
assert course['scheduled']['hasObsoleteDates'] is False
assert course['scheduled']['hasObsoleteTimes'] is False
assert course['scheduled']['hasObsoleteInstructors'] is True
assert len(course['instructors']) == 2
assert len(course['scheduled']['instructors']) == 1
assert course['scheduled']['instructors'][0]['uid'] == scheduled_with_uid
def add_sis_sections_rows(section):
instruction_format = section.number.split(' ')[0]
section_num = section.number.split(' ')[1]
for instructor in section.instructors:
instructor_name = f'{instructor.first_name} {instructor.last_name}'
for meeting in section.meetings:
days = meeting.days.replace(',', '').replace(' ', '')
start_date = meeting.start_date.strftime('%Y-%m-%d %H:%M:%S')
end_date = meeting.end_date.strftime('%Y-%m-%d %H:%M:%S')
start_time = datetime.strptime(meeting.start_time,
'%I:%M %p').strftime('%H:%M')
end_time = datetime.strptime(meeting.end_time,
'%I:%M %p').strftime('%H:%M')
sql = f"""
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
'4', '{section.code}', '{section.title}', now(), '{instruction_format}', '{instructor_name}',
'{instructor.role}', '{instructor.uid}', TRUE, '{days}', '{end_date}', '{end_time}',
'{meeting.room.name}', '{start_date}', '{start_time}', {section.ccn}, '{section_num}', {section.term.id}
"""
app.logger.info(sql)
db.session.execute(text(sql))
std_commit(allow_test_environment=True)
def test_invited_filter(self, client, admin_session):
"""Invited filter: Course in an eligible room, have received invitation. No approvals. Not scheduled."""
with test_approvals_workflow(app):
# Course with approval is NOT expected in results
self._send_invitation_email(section_5_id)
self._create_approval(section_5_id)
# Course in ineligible room is NOT expected in results
self._send_invitation_email(section_2_id)
# Course in eligible room
eligible_section_id = section_4_id
self._send_invitation_email(eligible_section_id)
std_commit(allow_test_environment=True)
api_json = self._api_courses(client, term_id=self.term_id, filter_='Invited')
assert len(api_json) == 1
assert api_json[0]['sectionId'] == eligible_section_id
# Section with ZERO approvals will show up in search results
course = _find_course(api_json=api_json, section_id=section_4_id)
assert course
assert course['label'] == 'CHEM C110L, LAB 001'
assert course['approvalStatus'] == 'Invited'
assert course['schedulingStatus'] == 'Not Scheduled'
# The section with approval will NOT show up in search results
assert not _find_course(api_json=api_json, section_id=section_5_id)
def test_currently_no_person_teaching_course(self):
"""If course does not have a proper instructor then the email remains queued."""
def _emails_sent():
return _get_emails_sent(email_template_type=email_template_type,
section_id=section_id,
term_id=term_id)
term_id = app.config['CURRENT_TERM_ID']
section_id = 22460
email_template_type = 'invitation'
# Courses with no proper instructor are excluded from query results.
assert not SisSection.get_course(term_id=term_id,
section_id=section_id)
queued_email = QueuedEmail.create(section_id, email_template_type,
term_id)
std_commit(allow_test_environment=True)
emails_sent_before = _emails_sent()
# Run the job
QueuedEmailsJob(app.app_context).run()
std_commit(allow_test_environment=True)
# Expect no email sent
emails_sent_after = _emails_sent()
assert len(emails_sent_after) == len(emails_sent_before)
# Assert that email is still queued
assert section_id in QueuedEmail.get_all_section_ids(
template_type=email_template_type, term_id=term_id)
# Clean up
QueuedEmail.delete(queued_email)
def test_no_email_template_available(self):
"""If email_template is not available then keep related emails in the queue."""
def _emails_sent():
return _get_emails_sent(email_template_type=email_template_type,
section_id=section_id,
term_id=term_id)
term_id = app.config['CURRENT_TERM_ID']
section_id = 22287
email_template_type = 'waiting_for_approval'
queued_email = QueuedEmail.create(section_id, email_template_type,
term_id)
std_commit(allow_test_environment=True)
emails_sent_before = _emails_sent()
# Run the job
QueuedEmailsJob(app.app_context).run()
std_commit(allow_test_environment=True)
# Expect no email sent
emails_sent_after = _emails_sent()
assert len(emails_sent_after) == len(emails_sent_before)
# Assert that email is still queued
assert section_id in QueuedEmail.get_all_section_ids(
template_type=email_template_type, term_id=term_id)
# Clean up
QueuedEmail.delete(queued_email)
def test_course_has_opted_out(self):
"""Do not send email to courses that have opted out."""
def _emails_sent():
return _get_emails_sent(email_template_type=email_template_type,
section_id=section_id,
term_id=term_id)
term_id = app.config['CURRENT_TERM_ID']
section_id = 28602
CoursePreference.update_opt_out(term_id=term_id,
section_id=section_id,
opt_out=True)
email_template_type = 'invitation'
QueuedEmail.create(section_id, email_template_type, term_id)
std_commit(allow_test_environment=True)
before = utc_now()
emails_sent_before = _emails_sent()
# Run the job
QueuedEmailsJob(app.app_context).run()
std_commit(allow_test_environment=True)
# Expect no emails sent
emails_sent_after = _emails_sent()
assert len(emails_sent_after) == len(emails_sent_before)
assert not next(
(e for e in emails_sent_after
if e.section_id == section_id and e.sent_at > before), None)
def test_queued_email_for_admin(self):
"""Certain email template types are for admin recipients only."""
def _emails_sent():
return _get_emails_sent(email_template_type=email_template_type,
section_id=section_id,
term_id=term_id)
term_id = app.config['CURRENT_TERM_ID']
section_id = 22287
email_template_type = 'admin_alert_room_change'
QueuedEmail.create(section_id, email_template_type, term_id)
std_commit(allow_test_environment=True)
before = utc_now()
emails_sent_before = _emails_sent()
# Run the job
QueuedEmailsJob(app.app_context).run()
std_commit(allow_test_environment=True)
# Expect email to admin email address
emails_sent_after = _emails_sent()
assert len(emails_sent_after) == len(emails_sent_before) + 1
sent_email = next(
(e for e in emails_sent_after
if e.section_id == section_id and e.sent_at > before), None)
assert sent_email
json_ = sent_email.to_api_json()
assert json_['recipientUids'] == [app.config['EMAIL_DIABLO_ADMIN_UID']]
assert json_['sectionId'] == section_id
assert json_['templateType'] == email_template_type
assert json_['termId'] == term_id
assert json_['sentAt']
def test_admin_approval(self):
"""Course is scheduled for recording if an admin user has approved."""
with test_approvals_workflow(app):
section_id = 22287
term_id = app.config['CURRENT_TERM_ID']
course = SisSection.get_course(section_id=section_id,
term_id=term_id)
instructors = course['instructors']
assert len(instructors) == 2
# Verify that course is not scheduled
assert Scheduled.get_scheduled(section_id=section_id,
term_id=term_id) is None
Approval.create(
approved_by_uid=admin_uid,
approver_type_='admin',
cross_listed_section_ids=[],
publish_type_='canvas',
recording_type_='presentation_audio',
room_id=Room.find_room('Barker 101').id,
section_id=section_id,
term_id=term_id,
)
KalturaJob(app.app_context).run()
std_commit(allow_test_environment=True)
# Admin approval is all we need.
assert Scheduled.get_scheduled(section_id=section_id,
term_id=term_id)
def test_opt_out_cross_listings(self, client, admin_session):
"""If a section opts out then its cross-listings are automatically opted out."""
with test_approvals_workflow(app):
# First, opt out
cross_listed_section_ids = [28475, 27950, 32827]
self._api_opt_out_update(
client,
term_id=self.term_id,
section_id=cross_listed_section_ids[-1],
opt_out=True,
)
section_ids_opted_out = CoursePreference.get_section_ids_opted_out(term_id=self.term_id)
for section_id in cross_listed_section_ids:
assert section_id in section_ids_opted_out
std_commit(allow_test_environment=True)
# Opt back in
self._api_opt_out_update(
client,
term_id=self.term_id,
section_id=cross_listed_section_ids[0],
opt_out=False,
)
section_ids_opted_out = CoursePreference.get_section_ids_opted_out(term_id=self.term_id)
for section_id in cross_listed_section_ids:
assert section_id not in section_ids_opted_out
def test_has_instructors(self, client, admin_session):
"""Admins can see instructor changes that might disrupt scheduled recordings."""
with test_approvals_workflow(app):
meeting_days, meeting_start_time, meeting_end_time = SisSection.get_meeting_times(
term_id=self.term_id,
section_id=section_3_id,
)
instructor_uids = SisSection.get_instructor_uids(term_id=self.term_id, section_id=section_3_id)
Scheduled.create(
cross_listed_section_ids=[],
instructor_uids=instructor_uids + ['999999'],
meeting_days=meeting_days,
meeting_start_time=meeting_start_time,
meeting_end_time=meeting_end_time,
publish_type_='canvas',
recording_type_='presenter_audio',
room_id=Room.get_room_id(section_id=section_3_id, term_id=self.term_id),
section_id=section_3_id,
term_id=self.term_id,
)
std_commit(allow_test_environment=True)
api_json = self._api_course_changes(client, term_id=self.term_id)
course = _find_course(api_json=api_json, section_id=section_3_id)
assert course
assert course['scheduled']['hasObsoleteRoom'] is False
assert course['scheduled']['hasObsoleteMeetingTimes'] is False
assert course['scheduled']['hasObsoleteInstructors'] is True
def mock_scheduled(
section_id,
term_id,
meeting=None,
override_days=None,
override_end_date=None,
override_end_time=None,
override_room_id=None,
override_start_date=None,
override_start_time=None,
publish_type='kaltura_media_gallery',
recording_type='presenter_presentation_audio',
):
meeting = meeting or get_eligible_meeting(section_id=section_id,
term_id=term_id)
Scheduled.create(
course_display_name=f'term_id:{term_id} section_id:{section_id}',
instructor_uids=get_instructor_uids(term_id=term_id,
section_id=section_id),
kaltura_schedule_id=random.randint(1, 10),
meeting_days=override_days or meeting['days'],
meeting_end_date=override_end_date or get_recording_end_date(meeting),
meeting_end_time=override_end_time or meeting['endTime'],
meeting_start_date=override_start_date
or get_recording_start_date(meeting, return_today_if_past_start=True),
meeting_start_time=override_start_time or meeting['startTime'],
publish_type_=publish_type,
recording_type_=recording_type,
room_id=override_room_id
or Room.get_room_id(section_id=section_id, term_id=term_id),
section_id=section_id,
term_id=term_id,
)
std_commit(allow_test_environment=True)
def test_admin_approval(self):
"""Course is scheduled for recording if an admin user has approved."""
with test_approvals_workflow(app):
section_id = 50005
term_id = app.config['CURRENT_TERM_ID']
course = SisSection.get_course(section_id=section_id,
term_id=term_id)
instructors = course['instructors']
assert len(instructors) == 2
# Verify that course is not scheduled
assert Scheduled.get_scheduled(section_id=section_id,
term_id=term_id) is None
Approval.create(
approved_by_uid=admin_uid,
approver_type_='admin',
course_display_name=course['label'],
publish_type_='kaltura_my_media',
recording_type_='presentation_audio',
room_id=Room.find_room('Barker 101').id,
section_id=section_id,
term_id=term_id,
)
KalturaJob(simply_yield).run()
std_commit(allow_test_environment=True)
# Admin approval is all we need.
assert Scheduled.get_scheduled(section_id=section_id,
term_id=term_id)
def change_course_instructor(section,
old_instructor=None,
new_instructor=None):
conditional = f" AND instructor_uid = '{old_instructor.uid}'" if old_instructor else ''
if new_instructor:
sql = f"""UPDATE sis_sections
SET instructor_uid = '{new_instructor.uid}',
instructor_name = '{new_instructor.first_name} {new_instructor.last_name}',
instructor_role_code = '{new_instructor.role}'
WHERE section_id = {section.ccn}
AND term_id = {section.term.id}
{conditional}
"""
else:
sql = f"""UPDATE sis_sections
SET instructor_uid = NULL,
instructor_name = NULL,
instructor_role_code = NULL
WHERE section_id = {section.ccn}
AND term_id = {section.term.id}
AND instructor_uid = '{old_instructor.uid}'
"""
app.logger.info(sql)
db.session.execute(text(sql))
std_commit(allow_test_environment=True)
def _create_email_templates():
EmailTemplate.create(
template_type='admin_alert_instructor_change',
name='Alert admin instructor approval needed',
subject_line='Instructor approval needed',
message='<code>course.name</code> has new instructor(s).',
)
EmailTemplate.create(
template_type='admin_alert_room_change',
name='Alert admin when room change',
subject_line='Room change alert',
message=
'<code>course.name</code> has changed to a new room: <code>course.room</code>',
)
EmailTemplate.create(
template_type='notify_instructor_of_changes',
name='I\'m the Devil. Now kindly undo these straps.',
subject_line='If you\'re the Devil, why not make the straps disappear?',
message='That\'s much too vulgar a display of power.',
)
EmailTemplate.create(
template_type='invitation',
name='What an excellent day for an exorcism.',
subject_line='You would like that?',
message='Intensely.',
)
EmailTemplate.create(
template_type='recordings_scheduled',
name='Recordings scheduled',
subject_line='Course scheduled for Course Capture',
message=
'Recordings of type <code>recording.type</code> will be published to <code>publish.type</code>.',
)
std_commit(allow_test_environment=True)
def test_course_with_partial_approval(self, client, admin_session):
"""Course with two instructors and one approval."""
with test_approvals_workflow(app):
# If course has approvals but not scheduled then it will show up in the feed.
approved_by_uid = get_instructor_uids(section_id=section_1_id, term_id=self.term_id)[0]
room_id = Room.get_room_id(section_id=section_1_id, term_id=self.term_id)
Approval.create(
approved_by_uid=approved_by_uid,
approver_type_='instructor',
publish_type_='kaltura_my_media',
recording_type_='presentation_audio',
room_id=room_id,
section_id=section_1_id,
term_id=self.term_id,
)
std_commit(allow_test_environment=True)
api_json = api_get_course(
client,
term_id=self.term_id,
section_id=section_1_id,
)
assert [i['uid'] for i in api_json['instructors']] == ['10001', '10002']
approvals = api_json['approvals']
assert len(approvals) == 1
assert approved_by_uid == approvals[0]['approvedBy']['uid']
assert api_json['approvalStatus'] == 'Partially Approved'
assert api_json['schedulingStatus'] == 'Not Scheduled'
assert api_json['meetings']['eligible'][0]['room']['id'] == room_id
assert api_json['meetings']['eligible'][0]['room']['location'] == 'Barrows 106'
def _create_blackouts():
Blackout.create(
name='An excellent day for an exorcism',
start_date='12/25/2021',
end_date='12/25/2021',
)
std_commit(allow_test_environment=True)
def test_not_invited_filter(self, client, admin_session):
"""Not-invited filter: Courses in eligible rooms, never sent an invitation. No approval. Not scheduled."""
with test_approvals_workflow(app):
# The first course gets an invitation
self._send_invitation_email(section_1_id)
# The second course did not receive an invitation BUT it does have approval.
invite = SentEmail.get_emails_of_type(
section_ids=[section_4_id],
template_type='invitation',
term_id=self.term_id,
)
assert not invite
self._create_approval(section_4_id)
std_commit(allow_test_environment=True)
api_json = self._api_courses(client, term_id=self.term_id, filter_='Not Invited')
assert not _find_course(api_json=api_json, section_id=section_1_id)
assert not _find_course(api_json=api_json, section_id=section_4_id)
# Zero instructors is acceptable
assert _find_course(api_json=api_json, section_id=eligible_course_with_no_instructors)
# Third course is in enabled room and has not received an invite. Therefore, it is in the feed.
assert _is_course_in_enabled_room(section_id=section_3_id, term_id=self.term_id)
course = _find_course(api_json=api_json, section_id=section_3_id)
assert course['approvalStatus'] == 'Not Invited'
assert course['schedulingStatus'] == 'Not Scheduled'
assert course['label'] == 'BIO 1B, LEC 001'
def _create_users():
for test_user in _test_users:
user = AdminUser(uid=test_user['uid'])
db.session.add(user)
if test_user['deleted_at']:
AdminUser.delete(user.uid)
std_commit(allow_test_environment=True)
def test_has_obsolete_meeting_dates(self, client, admin_session):
"""Admins can see meeting date changes that might disrupt scheduled recordings."""
with test_approvals_workflow(app):
meeting = get_eligible_meeting(section_id=section_1_id, term_id=self.term_id)
obsolete_meeting_end_date = '2020-04-01'
assert meeting['endDate'] != obsolete_meeting_end_date
Scheduled.create(
instructor_uids=get_instructor_uids(term_id=self.term_id, section_id=section_1_id),
kaltura_schedule_id=random.randint(1, 10),
meeting_days=meeting['days'],
meeting_end_date=obsolete_meeting_end_date,
meeting_end_time=meeting['endTime'],
meeting_start_date=meeting['startDate'],
meeting_start_time=meeting['startTime'],
publish_type_='kaltura_my_media',
recording_type_='presentation_audio',
room_id=Room.get_room_id(section_id=section_1_id, term_id=self.term_id),
section_id=section_1_id,
term_id=self.term_id,
)
std_commit(allow_test_environment=True)
api_json = self._api_course_changes(client, term_id=self.term_id)
course = _find_course(api_json=api_json, section_id=section_1_id)
assert course
assert course['scheduled']['hasObsoleteRoom'] is False
assert course['scheduled']['hasObsoleteDates'] is True
assert course['scheduled']['hasObsoleteTimes'] is False
assert course['scheduled']['hasObsoleteInstructors'] is False
def _set_up_and_run_jobs():
Job.create(job_schedule_type='day_at',
job_schedule_value='15:00',
key='kaltura')
Job.create(job_schedule_type='day_at',
job_schedule_value='04:30',
key='queued_emails')
Job.create(job_schedule_type='day_at',
job_schedule_value='22:00',
key='house_keeping')
Job.create(job_schedule_type='minutes',
job_schedule_value='120',
key='instructor_emails')
Job.create(job_schedule_type='minutes',
job_schedule_value='120',
key='invitations')
Job.create(disabled=True,
job_schedule_type='minutes',
job_schedule_value='120',
key='admin_emails')
Job.create(job_schedule_type='day_at',
job_schedule_value='16:00',
key='canvas')
Job.create(disabled=True,
job_schedule_type='minutes',
job_schedule_value='5',
key='doomed_to_fail')
background_job_manager.start(app)
HouseKeepingJob(app_context=simply_yield).run()
CanvasJob(app_context=simply_yield).run()
std_commit(allow_test_environment=True)
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
def _load_courses():
term_id = app.config['CURRENT_TERM_ID']
db.session.execute(
SisSection.__table__.delete().where(SisSection.term_id == term_id))
save_mock_courses(f"{app.config['FIXTURES_PATH']}/sis/courses.json")
SisDataRefreshJob.after_sis_data_refresh(term_id=term_id)
std_commit(allow_test_environment=True)
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
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 test_refresh_term_data(self):
"""Refreshes term data without duplicate key violation in db."""
CanvasJob(simply_yield).run()
std_commit(allow_test_environment=True)
CanvasJob(simply_yield).run()
std_commit(allow_test_environment=True)
def get_test_section(test_data):
sql = f"""SELECT sis_sections.section_id AS ccn,
sis_sections.course_name AS code,
sis_sections.course_title AS title,
sis_sections.instruction_format AS format,
sis_sections.section_num AS num
FROM sis_sections
WHERE sis_sections.term_id = {app.config['CURRENT_TERM_ID']}
AND sis_sections.course_name LIKE '{test_data["dept_code"]} %'
AND sis_sections.course_name NOT LIKE '{test_data["dept_code"]} C%'
AND sis_sections.instruction_format = 'LEC'
AND sis_sections.is_principal_listing IS TRUE
AND sis_sections.deleted_at IS NULL
ORDER BY code, ccn
LIMIT 1;
"""
app.logger.info(sql)
result = db.session.execute(text(sql)).first()
std_commit(allow_test_environment=True)
app.logger.info(f'{result}')
sis_data = {
'ccn': f'{result["ccn"]}',
'code': result['code'],
'title': result['title'],
'number': f'{result["format"]} {result["num"]}',
'is_primary': 'TRUE',
'is_primary_listing': True,
}
test_data.update(sis_data)
return Section(test_data)