1 files changed, 189 insertions, 0 deletions
diff --git a/blackboard2gradebook.tcl b/blackboard2gradebook.tcl
new file mode 100755
index 0000000..6c3413e
--- /dev/null
+++ b/blackboard2gradebook.tcl
@@ -0,0 +1,189 @@
+#!/bin/sh
+# (C) 2011 by Eugeniy Mikhailov, <evgmik@gmail.com>
+# vim:set ft=tcl: \
+exec tclsh "$0" "$@"
+
+package require Tcl 8.5
+package require json::write
+package require sqlite3
+package require md5
+source ./GradeBook_lib.tcl
+
+if { $argc < 2 } {	
+	puts {Usage:}
+	puts "  $argv0 gradebook.sqlitedb blackboard.sqlitedb"
+	puts {}
+	puts {  expect both files to be sqilte tables:}
+	puts {  blackboard should have a single table 'export_table':}
+	puts {Example:}
+	puts [concat "  $argv0" { 2020_Fall_Phys251 blackboard.db}]
+	exit
+}
+
+set classDB [lindex $argv 0] 
+set blackboardDB [lindex $argv 1]
+
+sqlite3 db $classDB
+sqlite3 bdb $blackboardDB
+
+proc iferror { err errStat {eval_str {""} }} {
+        #return; # comment out when debugging
+        if { $err } {
+                puts "Error: $errStat"
+                if { $eval_str ne "" } {
+                        puts "For query: $eval_str"
+                }
+        }
+}
+
+proc getColListFromAnyTable {db table} {
+	set all_column_names ""
+	set eval_str [concat SELECT * FROM \'$table\' LIMIT 1]
+	set err [catch {
+		$db eval $eval_str v {
+			set all_column_names $v(*)
+		}
+	} errStat ]
+	iferror $err $errStat
+	if { $err } { return false }
+	return $all_column_names
+}
+
+proc getBlackboardUsernames { db } {
+	set username_list {}
+	set eval_str [concat SELECT Username FROM export_table]
+	set err [catch {
+		$db eval $eval_str v {
+			lappend username_list $v(Username)
+		}
+	} errStat ]
+	iferror $err $errStat
+	if { $err } { return false }
+	return $username_list
+}
+
+proc getBlackboardUserGrade { db uname col } {
+	set eval_str [concat SELECT \"$col\" FROM export_table where Username=='$uname']
+	set val [$db onecolumn $eval_str]
+	return $val
+}
+
+proc isInList { col listCol} {
+	foreach sCol $listCol {
+		set result [regexp $sCol $col match]
+		if { $result } {
+			return true
+		}
+	}
+	return false
+
+}
+
+proc trimColName { col } {
+	set shortCol $col
+	set category Note
+	set maxScore 0
+	set type Score
+	set result [regexp -nocase {(.*) (\[Total Pts:.*)} $col match shortCol scoreStr]
+	if { $result} {
+		set result [regexp -nocase {\[Total Pts: (\d+)} $scoreStr match maxScore]
+		set result [regexp -nocase {\[Total Pts: (\d+) Percentage} $scoreStr match]
+		if { $result } { set type Percentage }
+		set number {}
+		set name $shortCol
+		set result [regexp -nocase {(\D+)(\d+)} $shortCol match name number]
+		set result [regexp -nocase {(\S+)\s+$} $name match name]
+		switch $name {
+			HW     { set name Homework ; set category HomeWork }
+			Lab    { set name Lab ;      set category LabReport}
+			Design { set name "Final Project Design" ; set category FinalExam }
+			Report { set name "Final Project Report" ; set category FinalExam }
+			Precision { set name "Final Project Precision" ; set category FinalExam }
+			"Extra Credit" { set name "Final Project Bonus" ; set category FinalExam }
+			default { }
+		}
+		if { $number eq "" } {
+			set shortCol "$name"
+		} else {
+			set shortCol "$name $number"
+		}
+	}
+	puts [list $col "--->" $shortCol $category $maxScore $type]
+	return [list $shortCol $category $maxScore $type]
+}
+
+
+set commonInfoCol {{First Name} {Last Name} {Student ID} {Last Access} Username Availability }
+set infoColumsMarkers {{ - Lateness \(H:M:S\)} { - Max Points} { - Submission Time} {Total Lateness \(H:M:S\)} {Current Weighted} {Total \[} }
+
+set skipCreationCol [concat $commonInfoCol $infoColumsMarkers]
+
+## adding students if they do not exist
+set fname [list First Name]
+set lname [list Last Name]
+set idnum [list Student ID]
+set eval_str [concat SELECT * FROM 'export_table']
+set err [catch {
+	bdb eval $eval_str v {
+		set email $v(Username)@email.wm.edu
+		puts [list AddUserNonWeb $v($fname) $v($lname) $email student $v($idnum) 1]
+		#AddUserNonWeb $v($fname) $v($lname) $email student $v($idnum) 1
+		}
+	} errStat ]
+iferror $err $errStat
+
+
+# add columns with reports to gradebook db
+foreach col [getColListFromAnyTable bdb export_table] {
+	if { [isInList $col $skipCreationCol] } {
+		continue
+	}
+	#puts "$col"
+	set colInfo  [trimColName $col]
+	set shortCol [lindex $colInfo 0]
+	set category [lindex $colInfo 1]
+	set maxScore [lindex $colInfo 2]
+	set scoreType [lindex $colInfo 3]
+
+	if { ![doesColumnExists $shortCol GradesTable] } {
+		#AddColumnNonWeb $shortCol $category $maxScore
+		puts [list AddColumnNonWeb $shortCol $category $maxScore]
+	} else {
+		#puts "$col: exists"
+	}
+}
+
+# now for every user add their grades
+set username_list [getBlackboardUsernames bdb]
+foreach col [getColListFromAnyTable bdb export_table] {
+	if { [isInList $col $skipCreationCol] } {
+		puts "skipping column $col"
+		continue
+	}
+	set colInfo  [trimColName $col]
+	set shortCol [lindex $colInfo 0]
+	set category [lindex $colInfo 1]
+	set maxScore [lindex $colInfo 2]
+	set scoreType [lindex $colInfo 3]
+	if { $category ne "FinalExam" } {
+		puts "skipping non FinalExam column: $col"
+		continue
+	}
+	foreach uname $username_list {
+		set locUname $uname@email.wm.edu
+		set grade [getBlackboardUserGrade bdb $uname $col ]
+		if { "" ne $grade } {
+			if { $scoreType eq "Percentage" } {
+				# change percentage to score
+				set grade [ expr {$grade/100*$maxScore} ]
+			}
+			puts "setting $grade for $locUname in $col"
+			#UpdateColValue4UserNameNonWeb $shortCol $locUname $grade
+			puts [list UpdateColValue4UserNameNonWeb $shortCol $locUname $grade]
+		}
+	}
+}
+
+
+
+