<commit_before>require 'gradesheet'
require 'audit'
require 'spreadsheet'

class CourseSpreadsheet < Spreadsheet
  def initialize(course)
    @sheets = []
    @course = course
    @users = @course.students.order(:last_name, :first_name).to_a
    @users_by_id = @users.map {|u| [u.id, u]}.to_h
    @sections = @course.sections
    @sections_by_type = @sections.group_by(&:type)
    @sections_by_crn = @sections.map{|s| [s.crn, s]}.to_h

    @regs = @course.registrations.joins(:sections)
           .select(:user_id, :crn, :type, :dropped_date, :created_at)
           .group_by(&:user_id)
           .map do |uid, sections|
      [uid, sections.map{|s| [s.type, {section: @sections_by_crn[s.crn], joined_date: s.created_at, dropped_date: s.dropped_date}]}.to_h]
    end.to_h

    exams, exam_cols = create_exams(Sheet.new("Exams"))
    hws, hw_cols = create_hws(Sheet.new("Homework"), exams)
    summary = create_summary(Sheet.new("Summary"), exams, exam_cols, hws, hw_cols)
    @sheets.push(summary, exams, hws)
    @sheets.each do |s|
      s.assign_coords
    end
  end

  def sanity_check
    @sheets.each do |s|
      s.sanity_check
    end
  end

  def create_exams(sheet)
    labels, weight = create_name_columns(sheet)

    exam_cols = []

    # Skip two rows after all the user rows, to make room for stats-per-section rows
    sheet.push_row(nil, [])
    sheet.push_row(nil, [])

    stats_per_section = {}
    stats_rows = []
      

    @course.assignments.where(type: ["Exam", "Questions"]).order(:due_date).each do |exam|
      used_subs = exam.all_used_subs.to_a
      grades = Gradesheet.new(exam, used_subs)
      subs_for_grading = UsedSub.where(assignment: exam).to_a

      sheet.columns.push(Col.new(exam.name, "Number"))
      questions = exam.flattened_questions
      questions.each_with_index do |q, i|
        sheet.columns.push(Col.new("", "Number")) if i > 0
        if q["name"]
          labels.push(Cell.new(q["name"] + (if q["extra"] then " (E.C.)" else "" end)))
        else
          labels.push(Cell.new("Question #{i + 1}"))
        end
        weight.push(Cell.new(q["weight"]))
      end
      sheet.columns.push(Col.new("", "Number"), Col.new("", "Percent"), Col.new("", "Percent"), Col.new("", "Percent"))
      labels.push(Cell.new("Total"), Cell.new("Computed%"), Cell.new("Curved"), Cell.new("OnServer%"))
      normal_questions_and_indices = questions.zip(0..(questions.count - 1)).delete_if do |q, idx| q["extra"] end
      tot_weight = normal_questions_and_indices.map{|q, idx| q["weight"]}.sum()
      weight.push(Cell.new(nil, Formula.new(tot_weight, "SUM",
                                            *(normal_questions_and_indices.map do |q, idx|
                                                CellRef.new(nil,
                                                            Spreadsheet.col_name(weight.count - questions.count + idx), true,
                                                            3, true,
                                                            q["weight"])
                                              end))),
                  Cell.new(""), Cell.new(""), Cell.new(""))
      exam_cols.push [exam, weight.count - 2]

      all_grade_comments = InlineComment.where(submission_id: subs_for_grading.map(&:submission_id))
                           .group_by(&:submission_id)
      subs_by_user = subs_for_grading.map{|sfg| [sfg.user_id, sfg]}.to_h

      @users.each_with_index do |u, i|
        sub_id = subs_by_user[u.id]
        begin
          sub = grades.grades[:grades][sub_id.submission_id] unless sub_id.nil?
        rescue Exception => e
          sub = nil
          Audit.log("Failed in query: #{grades.grades} and #{sub_id and sub_id.submission_id}\n#{e}")
        end
        if sub.nil?
          questions.each do |g| sheet.push_row(i, "") end
          sheet.push_row(i, ["No submission", "No submission"])
          curved = Cell.new(nil,
                            CellRef.new(nil,
                                        Spreadsheet.col_name(weight.count - 3), true, i + sheet.header_rows.length + 2, false,
                                        nil))
          sheet.push_row(i, [curved, 0])
        elsif all_grade_comments[sub_id.submission_id].nil?
          questions.each do |g| sheet.push_row(i, "") end
          sheet.push_row(i, [0, "Not yet graded"])
          curved = Cell.new(nil,
                            CellRef.new(nil,
                                        Spreadsheet.col_name(weight.count - 3), true, i + sheet.header_rows.length + 2, false,
                                        nil))
          sheet.push_row(i, [curved, 0])
        else
          grade_comments = all_grade_comments[sub_id.submission_id].sort_by{|c| c.line}
          q_grades = grade_comments.slice(0..questions.count-1).map{|c| (c && c["weight"])}
          q_grades.each do |g|
            sheet.push_row(i, g || "<none>")
          end
          sum_grade = Formula.new(nil, "SUM",
                                  Range.new(Spreadsheet.col_name(weight.count - questions.count - 4), true,
                                            i + sheet.header_rows.length + 2, false,
                                            Spreadsheet.col_name(weight.count - 5), true,
                                            i + sheet.header_rows.length + 2, false))
          sheet.push_row(i, Cell.new(nil, sum_grade))
          sum_grade = Formula.new(sub[:sub].score, "/",
                                  sum_grade, CellRef.new(nil, Spreadsheet.col_name(weight.count - 4), true, 3, true, tot_weight))

          sheet.push_row(i, Cell.new(nil, sum_grade))
          curve = grade_comments[questions.count]
          if curve
            curved = Cell.new(nil, Formula.new(sub[:sub].score, "/", curve["weight"], tot_weight))
          else
            curved = Cell.new(nil,
                              CellRef.new(nil,
                                          Spreadsheet.col_name(weight.count - 3), true, i + sheet.header_rows.length + 2, false,
                                          nil))
          end
          sheet.push_row(i, curved)
          if sub[:sub].score
            sheet.push_row(i, sub[:sub].score / 100.0)
          else
            sheet.push_row(i, 0)
          end
        end
      end<commit_msg>Big oops.<commit_after>require 'gradesheet'
require 'audit'
require 'spreadsheet'

class CourseSpreadsheet < Spreadsheet
  def initialize(course)
    @sheets = []
    @course = course
    @users = @course.students.order(:last_name, :first_name).to_a
    @users_by_id = @users.map {|u| [u.id, u]}.to_h
    @sections = @course.sections
    @sections_by_type = @sections.group_by(&:type)
    @sections_by_crn = @sections.map{|s| [s.crn, s]}.to_h

    @regs = @course.registrations.joins(:sections)
           .select(:user_id, :crn, :type, :dropped_date, :created_at)
           .group_by(&:user_id)
           .map do |uid, sections|
      [uid, sections.map{|s| [s.type, {section: @sections_by_crn[s.crn], joined_date: s.created_at, dropped_date: s.dropped_date}]}.to_h]
    end.to_h

    exams, exam_cols = create_exams(Sheet.new("Exams"))
    hws, hw_cols = create_hws(Sheet.new("Homework"), exams)
    summary = create_summary(Sheet.new("Summary"), exams, exam_cols, hws, hw_cols)
    @sheets.push(summary, exams, hws)
    @sheets.each do |s|
      s.assign_coords
    end
  end

  def sanity_check
    @sheets.each do |s|
      s.sanity_check
    end
  end

  def create_exams(sheet)
    labels, weight = create_name_columns(sheet)

    exam_cols = []

    # Skip two rows after all the user rows, to make room for stats-per-section rows
    sheet.push_row(nil, [])
    sheet.push_row(nil, [])

    stats_per_section = {}
    stats_rows = []
      

    @course.assignments.where(type: ["Exam", "Questions"]).order(:due_date).each do |exam|
      used_subs = exam.all_used_subs.to_a
      grades = Gradesheet.new(exam, used_subs)
      subs_for_grading = UsedSub.where(assignment: exam).to_a

      sheet.columns.push(Col.new(exam.name, "Number"))
      questions = exam.flattened_questions
      questions.each_with_index do |q, i|
        sheet.columns.push(Col.new("", "Number")) if i > 0
        if q["name"]
          labels.push(Cell.new(q["name"] + (if q["extra"] then " (E.C.)" else "" end)))
        else
          labels.push(Cell.new("Question #{i + 1}"))
        end
        weight.push(Cell.new(q["weight"]))
      end
      sheet.columns.push(Col.new("", "Number"), Col.new("", "Percent"), Col.new("", "Percent"), Col.new("", "Percent"))
      labels.push(Cell.new("Total"), Cell.new("Computed%"), Cell.new("Curved"), Cell.new("OnServer%"))
      normal_questions_and_indices = questions.zip(0..(questions.count - 1)).delete_if do |q, idx| q["extra"] end
      tot_weight = normal_questions_and_indices.map{|q, idx| q["weight"]}.sum()
      weight.push(Cell.new(nil, Formula.new(tot_weight, "SUM",
                                            *(normal_questions_and_indices.map do |q, idx|
                                                CellRef.new(nil,
                                                            Spreadsheet.col_name(weight.count - questions.count + idx), true,
                                                            3, true,
                                                            q["weight"])
                                              end))),
                  Cell.new(""), Cell.new(""), Cell.new(""))
      exam_cols.push [exam, weight.count - 2]

      all_grade_comments = InlineComment.where(submission_id: subs_for_grading.map(&:submission_id))
                           .group_by(&:submission_id)
      subs_by_user = subs_for_grading.map{|sfg| [sfg.user_id, sfg]}.to_h

      @users.each_with_index do |u, i|
        sub_id = subs_by_user[u.id]
        begin
          sub = grades.grades[:grades][sub_id.submission_id] unless sub_id.nil?
        rescue Exception => e
          sub = nil
          Audit.log("Failed in query: #{grades.grades} and #{sub_id and sub_id.submission_id}\n#{e}")
        end
        if sub.nil?
          questions.each do |g| sheet.push_row(i, "") end
          sheet.push_row(i, ["No submission", "No submission"])
          curved = Cell.new(nil,
                            CellRef.new(nil,
                                        Spreadsheet.col_name(weight.count - 3), true, i + sheet.header_rows.length + 2, false,
                                        nil))
          sheet.push_row(i, [curved, 0])
        elsif all_grade_comments[sub_id.submission_id].nil?
          questions.each do |g| sheet.push_row(i, "") end
          sheet.push_row(i, [0, "Not yet graded"])
          curved = Cell.new(nil,
                            CellRef.new(nil,
                                        Spreadsheet.col_name(weight.count - 3), true, i + sheet.header_rows.length + 2, false,
                                        nil))
          sheet.push_row(i, [curved, 0])
        else
          grade_comments = all_grade_comments[sub_id.submission_id].sort_by{|c| c.line}
          if exam.is_a? Exam
            # Exam scores are absolute scores, so just sum them up
            q_grades = grade_comments.slice(0..questions.count-1).map{|c| (c && c["weight"])}
          else
            # Questions grades are percentages (like codereviews), so sum must be weighted first
            q_grades = grade_comments.slice(0..questions.count-1).zip(questions)
                       .map{|c, q| c && (c["weight"] * q["weight"])}
          end
          q_grades.each do |g|
            sheet.push_row(i, g || "<none>")
          end
          sum_grade = Formula.new(nil, "SUM",
                                  Range.new(Spreadsheet.col_name(weight.count - questions.count - 4), true,
                                            i + sheet.header_rows.length + 2, false,
                                            Spreadsheet.col_name(weight.count - 5), true,
                                            i + sheet.header_rows.length + 2, false))
          sheet.push_row(i, Cell.new(nil, sum_grade))
          sum_grade = Formula.new(sub[:sub].score, "/",
                                  sum_grade, CellRef.new(nil, Spreadsheet.col_name(weight.count - 4), true, 3, true, tot_weight))

          sheet.push_row(i, Cell.new(nil, sum_grade))
          curve = grade_comments[questions.count]
          if curve
            curved = Cell.new(nil, Formula.new(sub[:sub].score, "/", curve["weight"], tot_weight))
          else
            curved = Cell.new(nil,
                              CellRef.new(nil,
                                          Spreadsheet.col_name(weight.count - 3), true, i + sheet.header_rows.length + 2, false,
                                          nil))
          end
          sheet.push_row(i, curved)
          if sub[:sub].score
            sheet.push_row(i, sub[:sub].score / 100.0)
          else
            sheet.push_row(i, 0)
          end
        end
      end
{'fraction_non_alphanumeric': 0.1327331486611265, 'fraction_numerical': 0.0044629116651277315, 'mean_word_length': 1.7884574125724093, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 56, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '343263', 'n_tokens_mistral': 4062, 'n_tokens_neox': 3935, 'n_words': 762}