gistfile1.rb

class XlsExport
  attr_accessor :interview

  def get_xls
    user_interviews = interview.user_interviews.all_finished.includes(:user).includes(:interview_ratings)
    users = User.find_all_by_id(user_interviews.map {|user_interview| user_interview.interview_ratings.includes(:criterion_ratings).map{|interview_rating| interview_rating.user_id if interview_rating.criterion_ratings.where("criterion_ratings.value IS NOT NULL").count > 0 }}.flatten.uniq)
    all_criterion_ratings = {}
    user_interviews.each do |user_interview|
      user_ratings = {}
      user_interview.interview_ratings.each do |interview_rating|
        user_hash = user_ratings[interview_rating.user_id] ||= user_ratings.store(interview_rating.user_id, {})
        interview_rating.criterion_ratings.each {|criterion_rating| user_hash.store(criterion_rating.interview_criterion_id, criterion_rating.value)}
      end
      all_criterion_ratings.store(user_interview.id, user_ratings)
    end
    criterion_rating_nouser = {}
    user_interviews.each do |user_interview|
      user_interview_criterion_ratings = {}
      all_criterion_ratings[user_interview.id].each do |user, interview_criterion|
        interview_criterion.each do |ic, value|
          ic_rating = user_interview_criterion_ratings[ic] ||= user_interview_criterion_ratings.store(ic, [])
          ic_rating << value unless value.nil?
        end
      end
      criterion_rating_nouser.store(user_interview.id, user_interview_criterion_ratings)
    end
    book = Spreadsheet::Workbook.new
    sheet = book.create_worksheet :name => interview.position
    bold_format = Spreadsheet::Format.new :weight => :bold
    title_format = Spreadsheet::Format.new :weight => :bold, :horizontal_align => :center_across, :merge_range => true
    right_format = Spreadsheet::Format.new :horizontal_align => :right
    current_row = 0
    (users.count + 5).times do |i|
      sheet.row(current_row).set_format(i, title_format)
      sheet.column(i).width = 20
    end
    sheet.row(current_row)[0] = "Assessment Criteria Results - Take the Interview"
    current_row += 1
    user_interviews.each do |user_interview|
      sheet.row(current_row).default_format = bold_format
      sheet.row(current_row).concat %w{Candidate Assessment\ Criteria Weight\ %}
      users.each{|user| sheet.row(current_row).push user.name}
      sheet.row(current_row).push "Total Score", "Average Score"
      current_row += 1
      first_criteria = true
      interview.interview_criteria.order(:text).each do |interview_criterion|
        row = sheet.row(current_row)
        if first_criteria
          first_criteria = false
          row.push(user_interview.user.name)
        else
          row.push ""
        end
        row.push interview_criterion.text
        row.push "#{interview_criterion.weight}%"
        users.each do |user|
          all_criterion_ratings[user_interview.id][user.id][interview_criterion.id] ||= 0
          this_rating = all_criterion_ratings[user_interview.id][user.id][interview_criterion.id]
          row.push this_rating ? this_rating : "-"
        end
        source = criterion_rating_nouser[user_interview.id][interview_criterion.id]
          unless source = []
            sum = source.sum
            row.push sum
            row.push (sum.to_f / source.size)
            current_row += 1
          else
            2.times do
              row.push 0
            end
            current_row += 1
          end
      end
      3.times {|i| sheet.row(current_row).push ""}
      users.each do |user|
        sheet.row(current_row).push all_criterion_ratings[user_interview.id][user.id].map{|ic, value| value}.reject{|i| i.nil?}.sum
      end
      sheet.row(current_row).push criterion_rating_nouser[user_interview.id].map{|ic, value| value.sum}.sum
      weighted_avg = 0.0
      interview.interview_criteria.each do |interview_criterion|
        unless source = []
          source = criterion_rating_nouser[user_interview.id][interview_criterion.id]
          avg = source.sum.to_f / source.size
          weight = interview_criterion.weight / 100.0
          weighted_avg += (weight * avg)
        end
      end
      sheet.row(current_row).push weighted_avg
      current_row += 2
    end
    final = StringIO.new
    book.write final
    final
  end

  def initialize(interview)
    self.interview = interview
  end
end