path: root/importGrades2gradebook.tcl

#!/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"}
	{s          "Add student, even if user info is incomplete"}
}
set usage "
Usage:
  $argv0 \[options] gradebook.sqlitedb blackboard.sqlitedb

  Import a foreign DB to gradebook format.
  Currently supports Gradescope and Blackboard imports.
  Expect both DB files to be sqlite tables.
  Imported DB should have a single table 'export_table'

Note:
  By default, the database will not be modified.
  Use '-w' option to enable modification.

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 LogBook MidTerm Participation]
#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 ADDSTUDENT [expr {$params(s)}]

set classDB [lindex $argv 0] 
set dbFileToExport [lindex $argv 1]

puts [list Categories to import: $categories2import]

sqlite3 db $classDB
sqlite3 foreignDB $dbFileToExport

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 {level 1}} {
	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 getColValuesFromAnyTable {db table col} {
	set values {}
	set eval_str [concat SELECT $col FROM $table]
	puts $eval_str
	set err [catch {
		$db eval $eval_str v {
			lappend values $v($col)
		}
	} errStat ]
	iferror $err $errStat
	if { $err } { return false }
	return $values
}

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 getWebassignUsernames { 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 getTheExperTAUsernames { db } {
	return [getColValuesFromAnyTable $db export_table UserName]
}


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 getWebassignUserGrade { db uname col } {
	set eval_str [concat SELECT \"$col\" FROM export_table where UserName=='$uname']
	set val [$db onecolumn $eval_str]
	return $val
}

proc getTheExperTAUserGrade { 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 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} {
		# Blackboard has column names in two formats
		# 1: Mid-term Exam 2 - TOTAL [Total Pts: 100 Score]
		# 2: Mid-term Exam 2 - TOTAL [Total Pts: up to 100 Score]
		# they are different by "up to" insertion but both allow to see max score
		set result [regexp -nocase {\[Total Pts:( | up to )(\d+)} $scoreStr match spacing maxScore]
		set result [regexp -nocase {\[Total Pts: (\d+) Percentage} $scoreStr match]
		if { $result } { set type Percentage }
		set number {}
		set name $shortCol
		# split away count/number, e.g. "Homework 01" -> "Homework " and "01"
		puts "sc: $shortCol"
		set result [regexp -nocase {(\D*\d*\D+)(\d+)} $shortCol match name number]
		# remove trailing whitespace
		set result [regsub -nocase {\s+$} $name "" name]
		puts "n:  $name"
		switch $name {
			Homework     { set name Homework ; set category HomeWork }
			HW     { set name Homework ; set category HomeWork }
			"Lab report" { set name Lab ;      set category LabReport}
			"Lab Report" { set name Lab ;      set category LabReport}
			"Midterm" { set name "MidTerm Exam" ; set category MidTerm }
			"Midterm1p" { set category MidTerm }
			"Midterm2p" { set category MidTerm }
			"Midterm Exam" { set name "MidTerm Exam" ; set category MidTerm }
			"Mid-term Exam" { set name "MidTerm Exam" ; set category MidTerm }
			HW     { set name Homework ; set category HomeWork }
			"Homework Total" { 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 }
			"Final project circuit design" { set category FinalExam }
			"Final circuit building and debugging" { set category FinalExam }
			"Final written report" { set category FinalExam }
			"Final Exam - Q" { set category FinalExam }
			"Final exam" { 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 }
			"HW PhysFest Bonus"  {  set category HomeWork; set maxScore 0 }
			"HW bonus problem"  { set category HomeWork; set maxScore 0 }
			"Problem Section Bonus" { set category Participation; }
			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

	global username_list

	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 colWithMaxScore "$shortCol - Max Points" 
	set maxScore [getForeignDBUserGrade foreignDB [lindex $username_list 0] $colWithMaxScore ]

	set number {}
	set name $shortCol
	set result [regexp -nocase {(\D+)(\d+)} $shortCol match name number]
	set result [regexp -nocase {(\S+)\s+$} $name match name]
	if { [regexp -nocase {Pre-lab} $name match dummy] } {
		set category HomeWork;
	}
	if { [regexp -nocase {Report} $name match dummy] } {
		set category LabReport;
	}
	if { [regexp -nocase {Labbook} $name match dummy] } {
		set category LogBook;
	}
	switch $name {
		HW { set name Homework ; set category HomeWork }
		Homework { set name Homework ; set category HomeWork }
		Lab      { set name Lab ;      set category LabReport}
		Labbook  { set name Logbook ;      set category LogBook}
		"ndTimeHW"  { set name "2ndTimeHW"; set category HomeWork }
		"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 parseWebassignColName { col } {
	set shortCol $col
	set category ColumnInfo
	set maxScore 0
	set type Score

	global username_list

	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 colWithMaxScore "$shortCol - Max Points" 
	set maxScore [getForeignDBUserGrade foreignDB MaxScore $col ]

	set number {}
	set name $shortCol
	set result [regexp -nocase {(\D+)(\d+)} $shortCol match name number]
	set result [regexp -nocase {(\S+)\s+$} $name match name]
	if { [regexp -nocase {Pre-lab} $name match dummy] } {
		set category HomeWork;
	}
	if { [regexp -nocase {Report} $name match dummy] } {
		set category LabReport;
	}
	if { [regexp -nocase {Labbook} $name match dummy] } {
		set category LogBook;
	}
	switch $name {
		HW { set name Homework ; set category HomeWork }
		Homework { set name Homework ; set category HomeWork }
		Lab      { set name Lab ;      set category LabReport}
		Labbook  { set name Logbook ;      set category LogBook}
		"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 parseTheExperTAColName { col db } {
	set shortCol $col
	set category ColumnInfo
	set maxScore 0
	set type Score

	global username_list

	set maxScore [getTheExperTAUserGrade $db _Max_Points_ $col]
	set category [getTheExperTAUserGrade $db _Col_Category_ $col]

	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
	global ADDSTUDENT
	if { ! $ADDSTUDENT } {
		puts "Skipping user addition: Blackboard does not provide Section number"
		return
	}

	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 {
		$db eval $eval_str v {
			set username [blackboardUsername2local $v(Username)]
			dbRequest [list AddUserNonWeb $v($fname) $v($lname) $username student $v($idnum) $section]
			}
		} errStat ]
	iferror $err $errStat
}

proc addStudentsFromGradescopeDB { db } {
	# blackboard does not provide the following info
	set section unknownSection
	global ADDSTUDENT
	if { ! $ADDSTUDENT } {
		puts "Skipping user addition: Gradescope does not provide Section number"
		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 $v($idnum) $section]
			}
		} errStat ]
	iferror $err $errStat
}

proc addStudentsFromWebassignDB { db } {
	# blackboard does not provide the following info
	# adding students from WebAssign is not implemented
}

proc addStudentsFromTheExperTADB { db } {
	# blackboard does not provide the following info
	set section unknownSection
	global ADDSTUDENT
	if { ! $ADDSTUDENT } {
		puts "Skipping user addition: Gradescope does not provide Section number"
		return
	}

	set fname [list FirstName]
	set lname [list LastName]
	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 $v($idnum) $section]
			}
		} errStat ]
	iferror $err $errStat
}


proc blackboardUsername2local { uname } {
	#return "$uname@email.wm.edu"
	# since 2022
	return "$uname@wm.edu"
}

proc noTransformationNeededUsername2local { uname } {
	return $uname
}

proc updateGrade { col locUname grade } {
	if { ! [isThereUserNameInTheGradesTable $locUname] } {
		set foreignFirstName [getForeignDBUserGrade foreignDB $locUname FirstName]
		set foreignLastName [getForeignDBUserGrade foreignDB $locUname LastName]
		dbg [list "GradesDB does not have user" $locUname "-->" $foreignFirstName $foreignLastName]
		return
	}
	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 db} {
	set reduced_list {}
	foreach col $cols2import {
		if { "$col" eq "" } {
			dbg "skipping column with empty name"
			continue
		}
		if { [isInList $col $skipCreationCol] } {
			dbg "skipping column $col, it is in $skipCreationCol list"
			continue
		}
		set colInfo  [parseColName $col $db]
		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]
set dbOrigin Blackboard; # default
if { {SID} in $allForeignCols } { set dbOrigin Gradescope }
if { $dbFileToExport == "WebAssign.db" }  { set dbOrigin WebAssign }
if { $dbFileToExport == "TheExperTA.db" }  { set dbOrigin TheExpertTA }
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      noTransformationNeededUsername2local
		alias addStudentsFromForeignDB   addStudentsFromGradescopeDB
		alias getForeignDBUsernames      getGradescopeUsernames
		alias getForeignDBUserGrade      getGradescopeUserGrade
		alias normilizeGrade             normilizeGradescopeGrade
		alias parseColName               parseGradescopeColName
	}
	WebAssign {
		alias foreignUsername2local      noTransformationNeededUsername2local
		alias addStudentsFromForeignDB   addStudentsFromWebassignDB
		alias getForeignDBUsernames      getWebassignUsernames
		alias getForeignDBUserGrade      getWebassignUserGrade
		alias normilizeGrade             normilizeGradescopeGrade
		alias parseColName               parseWebassignColName
	}
	TheExpertTA {
		alias foreignUsername2local      noTransformationNeededUsername2local
		alias addStudentsFromForeignDB   addStudentsFromTheExperTADB
		alias getForeignDBUsernames      getTheExperTAUsernames
		alias getForeignDBUserGrade      getTheExperTAUserGrade
		alias normilizeGrade             normilizeGradescopeGrade
		alias parseColName               parseTheExperTAColName
	}
	default {
		puts "Unknown DB origin, exiting"
		exit 1
	}
}

addStudentsFromForeignDB foreignDB

# now for every user add their grades
set username_list [getForeignDBUsernames foreignDB]; # important, will be used as global
set cols2import [getColListFromAnyTable foreignDB export_table]

set cols2import [ pickCols2import $cols2import $skipCreationCol $categories2import foreignDB]

foreach col $cols2import {
	set colInfo  [parseColName $col foreignDB]
	set shortCol [lindex $colInfo 0]
	set category [lindex $colInfo 1]
	set maxScore [lindex $colInfo 2]
	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
	}
}