rename file to be more general

1 files changed, 434 insertions, 0 deletions

diff --git a/importGrades2gradebook.tcl b/importGrades2gradebook.tcl
new file mode 100755
index 0000000..a4944d6
--- /dev/null
+++ b/importGrades2gradebook.tcl
@@ -0,0 +1,434 @@
+#!/bin/sh
+# (C) 2011 by Eugeniy Mikhailov, <evgmik@gmail.com>
+# vim:set ft=tcl: \
+exec tclsh "$0" "$@"
+
+package require Tcl 8.5
+package require try         ;# Tcllib.
+package require cmdline 1.5 ;# First version with proper error-codes.
+package require json::write
+package require sqlite3
+package require md5
+source ./GradeBook_lib.tcl
+
+set options {
+	{w          "Write to database, disabled by default"}
+	{v          "Verbose"}
+	{d          "Debug write DB operations"}
+}
+set usage "
+Usage:
+  $argv0 \[options] gradebook.sqlitedb blackboard.sqlitedb
+
+  Import a foreign DB to gradebook format.
+  Expect both DB files to be sqlite tables.
+  Imported DB should have a single table 'export_table'
+
+Example:
+  $argv0  2020_Fall_Phys251 blackboard.db
+
+Options:"
+
+try {
+	array set params [::cmdline::getoptions argv $options $usage]
+	#parray params
+
+	# Note: argv is modified now. The recognized options are
+	# removed from it, leaving the non-option arguments behind.
+	if { [llength $argv] < 2 } {
+		throw {CMDLINE USAGE} [::cmdline::usage $options $usage]
+	}
+} trap {CMDLINE USAGE} {msg o} {
+	# Trap the usage signal, print the message, and exit the application.
+	# Note: Other errors are not caught and passed through to higher levels!
+	puts $msg
+	exit 1
+}
+
+################# Config ############################################
+set categories2import [list FinalExam HomeWork LabReport]
+#set categories2import [list FinalExam]
+
+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]
+######################################################################
+
+# if DRYRUN is true the database will not be modified
+set DRYRUN true
+set DRYRUN [expr {!$params(w)}]
+if { $DRYRUN} {
+	puts "DRYRUN: DB will not be modified"
+}
+
+set VERBOSE [expr {$params(v)}]
+set DBDEBUG [expr {$params(d)}]
+
+set classDB [lindex $argv 0] 
+set blackboardDB [lindex $argv 1]
+
+puts [list Categories to import: $categories2import]
+
+sqlite3 db $classDB
+sqlite3 foreignDB $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 dbg { msg } {
+	global VERBOSE
+	if { $VERBOSE } {
+		puts $msg
+	}
+}
+
+proc dbdbg { msg } {
+	global DBDEBUG
+	if { $DBDEBUG } {
+		puts -nonewline $msg
+	}
+}
+
+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 getGradescopeUsernames { db } {
+	set username_list {}
+	set eval_str [concat SELECT Email FROM export_table]
+	set err [catch {
+		$db eval $eval_str v {
+			lappend username_list $v(Email)
+		}
+	} 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 getGradescopeUserGrade { db uname col } {
+	set eval_str [concat SELECT \"$col\" FROM export_table where Email=='$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 parseBlackboardColName { 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 }
+			Hardware { set name "Final Project Hardware" ; set category FinalExam }
+			"Extra Credit" { set name "Final Project Bonus" ; set category FinalExam; set maxScore 0 }
+			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]
+}
+
+proc parseGradescopeColName { col } {
+	set shortCol $col
+	set category ColumnInfo
+	set maxScore 0
+	set type Score
+
+	set infoColumsMarkers {{ - Lateness \(H:M:S\)} { - Max Points} { - Submission Time} {Total Lateness \(H:M:S\)}}
+	if { [isInList $col $infoColumsMarkers] } {
+		return [list $shortCol $category $maxScore $type]
+	}
+
+	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 {
+		Homework { set name Homework ; set category HomeWork }
+		Lab      { set name Lab ;      set category LabReport}
+		"Final Project Design" { set category FinalExam }
+		"Final Project Report" { 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]
+}
+
+proc dbRequest {script} { 
+	# verbose evaluation of dbRequest with dry run capability
+	global DRYRUN
+	set cmd ""
+	foreach line [split $script \n] {
+		if {$line eq ""} {continue}
+		append cmd $line\n
+		if { [info complete $cmd] } {
+			if { ![info exists DRYRUN] || $DRYRUN} {
+				dbdbg "DRYRUN: $cmd"
+			} else {
+				dbdbg "EXECUTING: $cmd"
+				uplevel 1 $cmd
+			}
+			set cmd ""
+		}
+	}
+}
+
+## adding students if they do not exist
+proc addStudentsFromBlackboardDB { db } {
+	# blackboard does not provide the following info
+	set id unknownID
+	set section unknownSection
+	puts "Blackboard does not provide Student ID and Section number, skipping user addition"
+	return
+
+	set fname [list First Name]
+	set lname [list Last Name]
+	set eval_str [concat SELECT * FROM 'export_table']
+	set err [catch {
+		$db eval $eval_str v {
+			set username [blackboardUsername2local $v(Username)]
+			dbRequest [list AddUserNonWeb $v($fname) $v($lname) $username student $id $section]
+			}
+		} errStat ]
+	iferror $err $errStat
+}
+
+proc addStudentsFromGradescopeDB { db } {
+	# blackboard does not provide the following info
+	set section unknownSection
+	puts "Gradescope does not provide Section number, skipping user addition"
+	return
+
+	set fname [list First Name]
+	set lname [list Last Name]
+	set idnum [list SID]
+	set eval_str [concat SELECT * FROM 'export_table']
+	set err [catch {
+		$db eval $eval_str v {
+			set username [gradescopeUsername2local $v(Email)]
+			set id $v($idnum)
+			dbRequest [list AddUserNonWeb $v($fname) $v($lname) $username student $id $section]
+			}
+		} errStat ]
+	iferror $err $errStat
+}
+
+proc blackboardUsername2local { uname } {
+	return "$uname@email.wm.edu"
+}
+
+proc gradescopeUsername2local { uname } {
+	return $uname
+}
+
+
+proc updateGrade { col locUname grade } {
+	set oldGrade [SelectColValue4User $col $locUname ]
+	#puts "$locUname: $col oldGrade \{$oldGrade\} --> \{$grade\}"
+	if { $oldGrade == $grade } { return }
+	if { [regexp -nocase -- {excuse} $oldGrade] } {
+		# remote system do not handle excuses
+		# so local system take precedence
+		dbg [list "not updating \"excused\" grade:" $locUname  $col $oldGrade "-->" $grade]
+		return
+	}
+	if { $oldGrade != $grade } {
+		#puts "$locUname: $col oldGrade \{$oldGrade\} --> \{$grade\}"
+		puts [list $locUname  $col $oldGrade "-->" $grade]
+		dbRequest [list UpdateColValue4UserNameNonWeb $col $locUname $grade]
+	}
+}
+
+proc pickCols2import { cols2import skipCreationCol categories2import } {
+	set reduced_list {}
+	foreach col $cols2import {
+		if { [isInList $col $skipCreationCol] } {
+			dbg "skipping column $col"
+			continue
+		}
+		set colInfo  [parseColName $col]
+		set category [lindex $colInfo 1]
+		if { $category ni $categories2import } {
+			dbg [list skipping $col in $category, permitted categories: $categories2import]
+			continue
+		}
+		lappend reduced_list $col
+	}
+	return $reduced_list
+}
+
+proc normilizeBlackboardGrade { grade colInfo } {
+	set shortCol [lindex $colInfo 0]
+	set category [lindex $colInfo 1]
+	set maxScore [lindex $colInfo 2]
+	set scoreType [lindex $colInfo 3]
+	if { "" ne $grade } {
+		if { $scoreType eq "Percentage" } {
+			# change percentage to score
+			set grade [ expr {$grade/100*$maxScore} ]
+		}
+	}
+	return $grade
+}
+
+proc normilizeGradescopeGrade { grade colInfo } {
+	set shortCol [lindex $colInfo 0]
+	set category [lindex $colInfo 1]
+	set maxScore [lindex $colInfo 2]
+	set scoreType [lindex $colInfo 3]
+	# Gradescope always provides absolute Score, no need to change it
+	return $grade
+}
+
+
+proc alias {alias target} {
+    # makes alias to a proc, borrowed from
+    # https://wiki.tcl-lang.org/page/proc+alias
+    set fulltarget [uplevel [list namespace which $target]]
+    if {$fulltarget eq {}} {
+        return -code error [list {no such command} $target]
+    }
+    set save [namespace eval [namespace qualifiers $fulltarget] {
+        namespace export}]
+    namespace eval [namespace qualifiers $fulltarget] {namespace export *}
+    while {[namespace exists [
+        set tmpns [namespace current]::[info cmdcount]]]} {}
+    set code [catch {set newcmd [namespace eval $tmpns [
+        string map [list @{fulltarget} [list $fulltarget]] {
+        namespace import @{fulltarget}
+    }]]} cres copts]
+    namespace eval [namespace qualifiers $fulltarget] [
+        list namespace export {*}$save]
+    if {$code} {
+        return -options $copts $cres
+    }
+    uplevel [list rename ${tmpns}::[namespace tail $target] $alias]
+    namespace delete $tmpns 
+    return [uplevel [list namespace which $alias]]
+}
+
+######################### START of the execution #########################
+
+# using heuristic to detect foreign DB origin
+set allForeignCols [getColListFromAnyTable foreignDB export_table]; # important global
+set dbOrigin Blackboard; # default
+if { {SID} in $allForeignCols } { set dbOrigin Gradescope }
+puts [list Detected foreign DB origin: $dbOrigin]
+
+switch  $dbOrigin {
+	Blackboard {
+		alias foreignUsername2local      blackboardUsername2local
+		alias addStudentsFromForeignDB   addStudentsFromBlackboardDB
+		alias getForeignDBUsernames      getBlackboardUsernames
+		alias getForeignDBUserGrade      getBlackboardUserGrade
+		alias normilizeGrade             normilizeBlackboardGrade
+		alias parseColName               parseBlackboardColName
+	}
+	Gradescope {
+		alias foreignUsername2local      gradescopeUsername2local
+		alias addStudentsFromForeignDB   addStudentsFromGradescopeDB
+		alias getForeignDBUsernames      getGradescopeUsernames
+		alias getForeignDBUserGrade      getGradescopeUserGrade
+		alias normilizeGrade             normilizeGradescopeGrade
+		alias parseColName               parseGradescopeColName
+	}
+	default {
+		puts "Unknown DB origin, exiting"
+		exit 1
+	}
+}
+
+addStudentsFromForeignDB foreignDB
+
+# now for every user add their grades
+set username_list [getForeignDBUsernames foreignDB]
+set cols2import [getColListFromAnyTable foreignDB export_table]
+
+set cols2import [ pickCols2import $cols2import $skipCreationCol $categories2import]
+
+foreach col $cols2import {
+	set colInfo  [parseColName $col]
+	set shortCol [lindex $colInfo 0]
+	if { ![doesColumnExists $shortCol GradesTable] } {
+		dbRequest [list AddColumnNonWeb $shortCol $category $maxScore]
+	}
+	foreach uname $username_list {
+		set locUname [foreignUsername2local $uname]
+		set grade [getForeignDBUserGrade foreignDB $uname $col ]
+		set grade [normilizeGrade $grade $colInfo]
+		updateGrade $shortCol $locUname $grade
+	}
+}
+