excel_loader.rb 2.88 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
require 'rubyXL'

# Provides utilities for loading schedules from GMU's excel files.
class ExcelLoader
  def initialize(file_path)
    workbook = RubyXL::Parser.parse(file_path)
    @rows = workbook[0]
    @rows = @rows.drop(16) # the first 16 rows are junk data, so remove them
    @semester = Semester.create! season: 'Fall', year: '2018'
    @current_course = nil
  end

  # Loads all data from the Excel file into the database.
  def load_data
    @rows.each do |row|
      if (course = configure_course?(row)) # If this row contained a course, save it
        course.save!
        @current_course = course
      end
      configure_section?(row)&.save! # If this row contained a section, save it
    end
  end

  private

  # Prints the failure, deletes all data added during loading, and raises the failure error.
  def fail(error)
28 29
    logger.fatal error.message
    logger.fatal error.backtrace
30 31 32 33 34 35 36 37 38 39 40
    delete_all_records
    raise error
  end

  # Deletes all records from the database.
  def delete_all_records
    Semester.delete_all
    Course.delete_all
    Section.delete_all
  end

41
  # Tries to create a course from a given row.
42
  def configure_course?(row)
43
    course_name = row.cells[1]&.value
44 45 46
    course = nil

    # Ensure the course name is valid
47
    if course_name.present? && course_name != 'Total'
48 49 50 51 52 53 54 55 56 57
      # Split the name into its two components, i.e. "CS 112" => ["CS", "112"]
      name_components = course_name.split(' ')

      # Try to save the new course
      course = Course.new
      course.subject = name_components[0]
      course.course_number = name_components[1]
      course.semester = @semester
    end

58
    course
59 60
  end

61
  # Tries to create a section from a given row.
62
  def configure_section?(row)
63
    section_name = row.cells[2]&.value
64
    # If there is no valid section name, just continue to the next row
65
    unless section_name.blank? || section_name == 'Total'
66 67
      # The time field in the spreadsheet uses the format "start_time - end_time" i.e. "12:00 PM - 1:15 PM".
      # So, split the times string by the - character
68
      times = row.cells[23]&.value
69
      time_strs = times.split('-')
70 71 72 73 74 75 76 77 78 79 80 81
      section = Section.create name: section_name,
                               course: @current_course,
                               crn: row.cells[6]&.value,
                               section_type: row.cells[8]&.value,
                               title: row.cells[11]&.value,
                               instructor: row.cells[16]&.value,
                               start_date: row.cells[18]&.value,
                               end_date: row.cells[21]&.value,
                               days: row.cells[22]&.value,
                               start_time: time_strs[0].strip,
                               end_time: time_strs[1].strip,
                               location: row.cells[25]&.value
82

83
      # TODO: Add campus, notes, and size limit fields
84
    end
85
    section
86 87
  end
end