aboutsummaryrefslogtreecommitdiff
path: root/importGrades2gradebook.tcl
diff options
context:
space:
mode:
Diffstat (limited to 'importGrades2gradebook.tcl')
-rwxr-xr-ximportGrades2gradebook.tcl434
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
+ }
+}
+
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-16 09:43:43 +0000