Merge branch 'next' of qo.physics.wm.edu:evmik/GradeBook into next

1 files changed, 141 insertions, 0 deletions

diff --git a/gradescope2gradebook.tcl b/gradescope2gradebook.tcl
new file mode 100755
index 0000000..83e5b9a
--- /dev/null
+++ b/gradescope2gradebook.tcl
@@ -0,0 +1,141 @@
+#!/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 gradescope.sqlitedb"
+	puts {}
+	puts {  expect both files to be sqilte tables:}
+	puts {  gradescope should have a single table 'gradescope':}
+	puts {Example:}
+	puts [concat "  $argv0" { 2020_Fall_Phys251 gradescope.db}]
+	exit
+}
+
+set classDB [lindex $argv 0] 
+set gradescopeDB [lindex $argv 1]
+
+sqlite3 db $classDB
+sqlite3 gdb $gradescopeDB
+
+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 getGradescopeUsernames { db } {
+	set username_list {}
+	set eval_str [concat SELECT Email FROM gradescope]
+	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 getGradescopeUserGrade { db uname col } {
+	set username_list {}
+	set eval_str [concat SELECT \"$col\" FROM gradescope 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 trimColName { col } {
+	set result [regexp -nocase {(.*) due week.*} $col match submatch]
+	if { $result} {
+		#puts $col
+		#puts $match
+		#puts $submatch
+		return $submatch
+	}
+	return $col
+}
+
+
+set commonInfoCol {{First Name} {Last Name} {SID} {Email}}
+set infoColumsMarkers {{ - Lateness \(H:M:S\)} { - Max Points} { - Submission Time} {Total Lateness \(H:M:S\)}}
+
+set skipCreationCol [concat $commonInfoCol $infoColumsMarkers]
+
+# add columbs with reports to gradebook db
+foreach col [getColListFromAnyTable gdb gradescope] {
+	if { [isInList $col $skipCreationCol] } {
+		continue
+	}
+	#puts "$col"
+	set shortCol [trimColName $col]
+	#puts "$col => $shortCol"
+
+	if { ![doesColumnExists $shortCol GradesTable] } {
+		puts "Adding: ${shortCol}"
+		set result [regexp -nocase "pre-lab" $shortCol match]
+		if { $result} {
+			AddColumnNonWeb $shortCol Quiz 10
+			continue
+		}
+		# everything else is labreport
+		AddColumnNonWeb $shortCol LabReport 100
+	} else {
+		#puts "$col: exists"
+	}
+}
+
+# now for every user add their grades
+set username_list [getGradescopeUsernames gdb]
+foreach uname $username_list {
+	foreach col [getColListFromAnyTable gdb gradescope] {
+		if { [isInList $col $skipCreationCol] } {
+			continue
+		}
+		set shortCol [trimColName $col]
+		set grade [getGradescopeUserGrade gdb $uname $col ]
+		if { "" ne $grade } {
+			puts "setting $grade for $uname in $col"
+			UpdateColValue4UserNameNonWeb $shortCol $uname $grade
+		}
+	}
+}
+
+
+
+