update student GPAs on database

calc_and_insert_gpas.rb

require 'byebug'
require 'json'
require 'tiny_tds'
require 'dotenv'

Dotenv.load

$letter_grades = {
  "AA" => 4.00,
  "BA" => 3.50,
  "BB" => 3.00,
  "CB" => 2.50,
  "CC" => 2.00,
  "DC" => 1.50,
  "DD" => 1.00,
  "F" => 0.00,
  "NA" => 0.00,
}

$client = TinyTds::Client.new(username: ENV["DB_USER"],password: ENV["DB_PASS"] ,host: ENV["DB_HOST"], port: ENV["DB_PORT"], database: END["DB_NAME"])


def get_grades
  results = $client.execute("""select  A.EMPLID, B.STRM  , B.CRSE_ID,  A.CRSE_GRADE_OFF, A.UNT_TAKEN, B.SUBJECT, B.CATALOG_NBR
                             from PS_STDNT_ENRL A,  PS_CLASS_TBL B
                             where A.CLASS_NBR = B.CLASS_NBR and B.SSR_COMPONENT = 'EXM'
                             and A.STRM = B.STRM and A.STDNT_ENRL_STATUS = 'E' and B.STRM <> '0151'
                             order by EMPLID, STRM""")

  grades_array = []
  results.each do |result|
    grades_array << result
  end
  results.cancel
  grades_array
end

def insert gpas
  data = gpas
  result = $client.execute("DELETE FROM PS_STDNT_SPCL_GPA")
  result.do
  result.cancel

  counter = 0
  data.keys.each do |key|
    cum_gpa = data[key]["CUM_GPA"]
    data[key]["SPAS"].keys.each do |term|
      term_career = nil
      acad_car = $client.execute("SELECT EMPLID, ACAD_CAREER, INSTITUTION, STRM
                       FROM PS_STDNT_CAR_TERM
                       WHERE EMPLID = #{key} and STRM=#{term}")
      acad_car.each do |career|
        career["ACAD_CAREER"] == "PREP" ? term_career = nil : term_career = career["ACAD_CAREER"]
      end
      next if term_career.nil?
      acad_car.cancel
      spa = data[key]["SPAS"][term]["spa"]
      gpa_after_term = data[key]["SPAS"][term]["gpa_after_term"]
      term_credits = data[key]["SPAS"][term]["term_credits"]
      result = $client.execute("INSERT INTO PS_STDNT_SPCL_GPA VALUES (#{key}, '#{term_career}', 'IKBUN', '#{term}', 1, 'CGPA','','','', 0.01,'', '2015-06-18 00:00:00','hasan.yasar')") #{gpa_after_term}
      result.insert
      result = $client.execute("INSERT INTO PS_STDNT_SPCL_GPA VALUES (#{key}, '#{term_career}', 'IKBUN', '#{term}', 2, 'TGPA','','','', #{spa},'', '2015-06-18 00:00:00','hasan.yasar')")
      result.insert
    end
    puts counter
    counter += 1
  end
  $client.close
end

def calculate_gpa student_data
  total_credits = 0.0
  total_points  = 0.0
  student_data.each do |course_instance|
    letter_grade = course_instance["CRSE_GRADE_OFF"]
    num_credits = course_instance["UNT_TAKEN"].to_f

    byebug if letter_grade.nil?
    next if (letter_grade.include?("*") || [" ", "U", "I", "S", "EX", "W", "NI", "P", "NGR"].include?(letter_grade))

    grade_point = calculate_points letter_grade

    byebug if grade_point.nil?

    total_credits += num_credits
    total_points  += grade_point * num_credits
  end
  return gpa = total_points / total_credits unless total_credits == 0
  gpa = 0
end

def calculate_points letter_grade
  $letter_grades[letter_grade]
end

def calculate_spa_for_term term
  term_credits = 0.0
  spa_credits  = 0.0
  term_points  = 0.0
  term.each do |course_instance|
    letter_grade = course_instance["CRSE_GRADE_OFF"].gsub("*", "")
    num_credits = course_instance["UNT_TAKEN"].to_f
    term_credits += num_credits
    next if ([" ", "U", "I", "S", "EX", "W", "NI", "P", "NGR"].include?(letter_grade))
    spa_credits += num_credits
    grade_point = calculate_points letter_grade
    term_points += grade_point * num_credits
  end
  if (spa_credits == 0)
    spa = 0
  else
    spa = term_points / spa_credits unless spa_credits == 0
  end
  {"spa" => spa.round(2), "term_credits" => term_credits}
end

def calculate_spas student_data
  terms = student_data.group_by {|instance| instance["STRM"]}
  spas = {}

  terms.keys.each do |term_key|
    spas[term_key] = calculate_spa_for_term(terms[term_key])
    gpa_after_term = calculate_gpa(student_data.select{ |instance| instance["STRM"].to_i <= term_key.to_i })
    spas[term_key]["gpa_after_term"] = gpa_after_term.round(2)
  end
  spas
end

def init_calc parsed_instances
  students = parsed_instances.group_by { |instance| instance["EMPLID"] }
  student_data = {}
  students.keys.each do |student_key|
    gpa = calculate_gpa(students[student_key]).round(2)
    spas = calculate_spas(students[student_key])
    student_data[student_key] = {"CUM_GPA" => gpa, "SPAS" => spas}
  end
  student_data
end

def init
  parsed_instances = get_grades
  gpas = init_calc parsed_instances
  insert gpas
end

init