Module: TmpUpdateFacultyAssignments

Defined in:
lib/tmp_update_faculty_assignments.rb

Class Method Summary collapse

Class Method Details

.connect(db, user, pw) ⇒ Object 



15
16
17
 
# File 'lib/tmp_update_faculty_assignments.rb', line 15

def self.connect(db, user, pw)
  PGconn.new('localhost', 5432, '', '', db, user, pw)
end

.connect_database_url(url) ⇒ Object 



19
20
21
22
23
24
25
26
 
# File 'lib/tmp_update_faculty_assignments.rb', line 19

def self.connect_database_url(url)
 parts = /(.*)\:\/\/(.*)\:(.*)\@(.*)\/(.*)/.match(url)
 puts "parts 4: #{parts[4]}"
 puts "parts 5: #{parts[5]}"
 puts "parts 2: #{parts[2]}"
 puts "parts 3: #{parts[3]}"
 PGconn.new(parts[4], 5432, '', '', parts[5], parts[2], parts[3])
end

.query_table(conn) ⇒ Object 



31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
# File 'lib/tmp_update_faculty_assignments.rb', line 31

def self.query_table(conn)

  sql = "SELECT courses.id , primary_faculty_id as faculty_id
                      FROM courses, teams
                      WHERE courses.id = teams.course_id
                      UNION
                      SELECT courses.id, Secondary_faculty_id as faculty_id
                      FROM courses, teams
                      WHERE courses.id = teams.course_id"

  result = conn.exec(sql)

  n=0
  result.each do |row|
    course_id = row['id']
    faculty_id = row['faculty_id']

    next if faculty_id.nil?

    sql2 = "SELECT * FROM faculty_assignments WHERE faculty_assignments.course_id = #{course_id} AND faculty_assignments.person_id = #{faculty_id}"
    result2 = conn.exec(sql2)

    if result2.values.length > 0
      puts "Course_id => #{course_id} and faculty_id => #{faculty_id} present in faculty assignments"

    else
      puts "Course_id => #{course_id} and faculty_id => #{faculty_id} not present in faculty assignments"
      now = Time.now
      sql3 = "INSERT INTO faculty_assignments (course_id, person_id, created_at, updated_at)
                               VALUES ('#{course_id}','#{faculty_id}','#{now}','#{now}')"
      result3 = conn.exec(sql3)
      if result3
        n += 1
        puts "successfully added #{n} records"
      else
        puts "oops couldn't write"
      end

    end
  end
  result.clear

end