def refresh_one_course(cursor, course):
course_row = cursor.execute("""SELECT *
FROM pom.Courses
WHERE CourseCode = ?""", course.cx_code.encode('utf8')).fetchone()
logger.info("Populating information for [{0}] {1}".format(
course.cx_code, course_row.Name))
course.name = _sanitize(course_row.Name)
course.grading_style = _sanitize(course_row.GradingStyle)
course.description = _sanitize(course_row.Description)
course.note = BR_TAGS.sub('\n', _sanitize(course_row.Note)).strip()
course.credit = float(course_row.Credits)
course.number = int(course_row.Number)
course.spots = int(course_row.SeatsTotal)
course.filled = int(course_row.SeatsFilled)
course.primary_association = CAMPUSES_LOOKUP.get(course_row.PrimaryAssociation, -1)
course.save()
# Get the instructors for the course
instructors = cursor.execute("""SELECT DISTINCT(pi.Name), pc.CourseCode, pi.InstructorID
FROM pom.Instructors AS pi
JOIN pom.Courses AS pc
ON (pc.CourseCode = pi.CourseCode)
WHERE pi.CourseCode = ?
ORDER BY pi.InstructorID;""", course.cx_code.encode('utf8')).fetchall()
inames = []
for instructor in instructors:
inames.append(instructor.Name)
course.instructor = "; ".join(inames)
# TODO: Normalize instructors into their own table
# Check for fees or prerequisites
match = FEE_REGEX.findall(unicode(course.description))
if match:
course.fee = True
# TODO: add a prerequisites booleanfield
# if course_row.Requisites == "Y":
# course.prerequisites = True
# Populate meeting times and locations
refresh_meetings(cursor, course)
# Populate departments and requirement areas
try:
course.primary_department = Department.objects.get(code=course_row.Department)
except Department.DoesNotExist:
logger.warning("Tried to create a course record for {0} in the {1} "
"department, but {1} did not exist. Skipping.".format(
course.cx_code, course_row.Department))
course.delete()
return
# Clear secondary department/RA associations in case they've changed
course.departments.clear()
course.requirement_areas.clear()
dept_rows = cursor.execute("""SELECT CallingDepartment
FROM pom.Courses
WHERE CourseCode = ?;""", course.cx_code.encode('utf8')).fetchall()
for dept in dept_rows:
if _is_requirement_area(dept.CallingDepartment):
course.requirement_areas.add(
RequirementArea.objects.get(code=dept.CallingDepartment)
)
else:
course.departments.add(
Department.objects.get(code=dept.CallingDepartment)
)
course.save()
def handle(self, *args, **options):
Course.objects.all().delete() # do NOT want this any more, we should merge gracefully.
from aspc.data.scraped_courses import courses
for k, scraped_course in courses.items():
code_slug = slugify(scraped_course['code']).upper()
dept_code = self.deptrx.match(code_slug).groups()[0]
try:
course = Course.objects.get(code_slug=code_slug)
self.stdout.write('found existing for code: "%s", dept_code: "%s"\n' % (course.code, dept_code))
except Course.DoesNotExist:
course = Course(code=scraped_course['code'], code_slug=code_slug)
self.stdout.write('adding new for code: "%s", dept_code: "%s"\n' % (course.code, dept_code))
course.name = scraped_course['name']
course.instructor = scraped_course['instructor']
course.grading_style = scraped_course['grading_style']
course.description = scraped_course['description']
course.note = scraped_course['note']
course.credit = float(scraped_course['credit'])
course.spots = int(scraped_course['slots'])
try:
course.primary_department = Department.objects.get(code=dept_code)
except Department.DoesNotExist:
course.primary_department = None
course.save() # Save first then run m2m
course.departments.clear() # On the off chance that a course has been
# removed from one subject area / dept between imports
# we don't want to keep stale dept relationships around
for dcode in scraped_course['depts']:
course.departments.add(Department.objects.get(code=dcode))
if not course.primary_department:
if course.departments.count() == 0:
course.delete()
self.stdout.write('Failed to add %s because it wasn\'t in a department' % course.code)
else:
smallest_dept = course.departments.annotate(num_courses=Count('primary_course_set')).distinct().order_by('-num_courses')[0]
course.primary_department = smallest_dept
course.save()
course.meeting_set.all().delete() # don't want to keep stale meetings, can safely re-create all because schedule m2ms to course
for mtg in scraped_course['mtgs']:
meeting_breakout = parse_meeting(mtg)
if not meeting_breakout:
continue
m, t, w, r, f, begin, end, loc = meeting_breakout
new_meeting = Meeting(course=course)
new_meeting.monday = m
new_meeting.tuesday = t
new_meeting.wednesday = w
new_meeting.thursday = r
new_meeting.friday = f
new_meeting.begin = begin
new_meeting.end = end
campus_code = keyword_regex.findall(loc)[0]
if campus_code not in CAMPUSES_LOOKUP.keys():
continue
new_meeting.campus = CAMPUSES_LOOKUP[campus_code]
new_meeting.location = loc[11:]
new_meeting.save()
self.stdout.write('Successfully added course "%s"\n' % course.name.encode('utf-8'))
def refresh_meetings(cursor, course):
# Create Meeting objects for the course's meetings
meetings = cursor.execute("""SELECT DISTINCT(MeetTime), Weekdays, Campus, Building, Room
FROM pom.Courses AS pc
JOIN pom.Meetings AS pm
ON (pc.CourseCode = pm.CourseCode)
WHERE pm.CourseCode = ?
AND Weekdays IS NOT NULL
AND MeetTime NOT LIKE '%00:00-00:00AM. %';""",
course.cx_code.encode('utf8')).fetchall()
# Query explanation: Null weekdays can't be displayed on the schedule, so
# they don't make any sense to store (or try to parse). Non-existent
# meeting times likewise shouldn't be parsed.
#
# The DISTINCT() is to eliminate duplicate rows, at least until ITS
# fixes the bug in their updater.
# Clear old meetings
course.meeting_set.all().delete()
for mtg in meetings:
# Parse weekdays
weekdays = mtg.Weekdays
monday = True if weekdays.find('M') != -1 else False
tuesday = True if weekdays.find('T') != -1 else False
wednesday = True if weekdays.find('W') != -1 else False
thursday = True if weekdays.find('R') != -1 else False
friday = True if weekdays.find('F') != -1 else False
# Parse times
try:
start, start_pm, end, end_pm = TIME_REGEX.findall(mtg.MeetTime)[0]
except IndexError:
continue
if end_pm == 'PM':
end_pm = True
else:
end_pm = False
if start_pm in ('AM', 'PM'):
start_pm = True if start_pm == 'PM' else False
else:
start_pm = end_pm
start_h, start_m = [int(a) for a in start.split(':')]
end_h, end_m = [int(a) for a in end.split(':')]
# Correct times to 24hr form
if end_pm and end_h != 12:
end_h += 12
if start_pm and start_h != 12:
start_h += 12
begin = time(start_h, start_m)
end = time(end_h, end_m)
# Get campus
campus_code = mtg.Campus.split(' ')[0]
if not campus_code in CAMPUSES_LOOKUP.keys():
campus = CAMPUSES_LOOKUP['?']
else:
campus = CAMPUSES_LOOKUP[campus_code]
# Get location
if mtg.Room and mtg.Building:
room_number = ROOM_REGEX.findall(mtg.MeetTime)[0]
location = "{0}, {1}".format(mtg.Building, room_number)
else:
location = ''
meeting = Meeting(
course=course,
monday=monday,
tuesday=tuesday,
wednesday=wednesday,
thursday=thursday,
friday=friday,
begin=begin,
end=end,
campus=campus,
location=location
)
meeting.save()