General developer forum

Hello, all,

I have a very puzzling issue regarding SQL queries and a Ruby script that is being used at my institution for a student orientation Moodle course. I'm hoping to get anyone's feedback and insight on this.

At our institution, we have an online Moodle orientation course for students who aren't able to make it to campus to complete the normal face-to-face student orientation process. Before being cleared to register for courses, they must complete this particular course and all of its activities and a final quiz. A previous developer (with whom we no longer have contact) wrote several scripts that automatically enrolled students into this course from our student information system, and a SQL query was written and run on a daily basis to check to see if students completed the course activities. If they did, the "completed" status would be sent back to our SIS which would automatically clear them to register for courses. This workflow used a combination of various SQL queries, a PHP script, and a Ruby script to collect and push the status of the course completion back to the SIS. While everything has been working, I was asked to replicate the same scripts for 2 new courses. While I am able to make changes to most of the scripts with no issues, my main issue is tackling the Ruby script which is pulling the completion status from the course back to our SIS. Given that my SQL and Ruby knowledge as it relates to Moodle is fairly low, I would love to know if anyone would be willing to provide insight on what values I need to change in the Ruby script for it to work with other courses. What is very puzzling to me is the fact that the Ruby script (shown below) is not referencing a course ID (which I assumed it would do), but is rather looking at a range of values in mdl_course_modules_completion.coursemoduleid. Does anyone know why that would be, and how I could either change/modify this to work with another course?

Thanks!

-Jeff

Ruby script:

mark_as_moved = true
ARGV.each do |a|
  if a == "donotupdate"
    mark_as_moved = false
    puts "Records will not be updated"
  end
end

require 'rubygems'
require "active_record"
require 'fastercsv'

def moodle_db
  establish_connection(
    :host =>
    :adapter =>
    :encoding =>
    :database =>  
    :username =>
    :password =>
  )
end

class CourseModuleCompletion < ActiveRecord::Base
  moodle_db
  self.table_name = "mdl_course_modules_completion"
end

class SOCompletion < ActiveRecord::Base
  moodle_db
  self.table_name = "student_orientation_completion"
end

has_records = false
csv_string = FasterCSV.generate do |csv|
  completions = CourseModuleCompletion.select("mdl_user.idnumber") \
                  .joins("LEFT JOIN mdl_user ON mdl_course_modules_completion.userid = mdl_user.id") \
                  .where("mdl_user.idnumber <> ''") \
                  .where("mdl_course_modules_completion.coursemoduleid >= 171029") \
                  .where("mdl_course_modules_completion.coursemoduleid <= '171047'") \
                  .where("mdl_course_modules_completion.completionstate = '1'")

  completions.each do |c|
    u = SOCompletion.where("idnumber = ?", c.idnumber)
    if u.blank?
      #has not been sent so send and add to table

      csv << [c.idnumber]
      has_records = true

      if mark_as_moved == true
        SOCompletion.create(:idnumber => c.idnumber)
      end
    end
  end
end

if has_records == true
  FasterCSV.open("orientation_completion.txt", "w") do |c|
    c << ["oasis"]
    FasterCSV.parse(csv_string) do |csv|
      c << [csv[0]]
    end
  end
  puts "File orientation_completion.txt created"
else
  FasterCSV.open("orientation_completion.txt", "w") do |c|
  end

  puts "No Records to export"
end