path: root/blackboard2gradebook.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 json::write
package require sqlite3
package require md5
source ./GradeBook_lib.tcl

################# Config ############################################
# if dryrun is true the database will not be modified
set dryrun true

#set categories2export [list FinalExam HomeWork LabReport]
set categories2export [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 { $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 }
			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]
}

set wasDryRunAnounced false
proc dbRequest {script} { 
	# verbose evaluation of dbRequest with dry run capability
	global dryrun
	global wasDryRunAnounced
	set cmd ""
	foreach line [split $script \n] {
		if {$line eq ""} {continue}
		append cmd $line\n
		if { [info complete $cmd] } {
			if { [info exists dryrun] && $dryrun} {
				if { ! $wasDryRunAnounced } {
					set wasDryRunAnounced true
					puts "DRYRUN: DB will not be modified"
				}
				#puts -nonewline "DRYRUN: $cmd"
			} else {
				puts -nonewline "Executing: $cmd"
				uplevel 1 $cmd
			}
			set cmd ""
		}
	}
}

## adding students if they do not exist
proc addStudentsFromDB { 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 email $v(Username)@email.wm.edu
			dbRequest [list AddUserNonWeb $v($fname) $v($lname) $email student $id $section]
			}
		} errStat ]
	iferror $err $errStat
}

proc foreignUsername2local { uname } {
	return "$uname@email.wm.edu"
}

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
		#puts [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 categories2export } {
	set reduced_list {}
	foreach col $cols2import {
		if { [isInList $col $skipCreationCol] } {
			puts "skipping column $col"
			continue
		}
		set colInfo  [trimColName $col]
		set category [lindex $colInfo 1]
		if { $category ni $categories2export } {
			puts [list skipping $col in $category permitted categories are $categories2export]
			continue
		}
		lappend reduced_list $col
	}
	return $reduced_list
}

proc normilizeGrade { 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
}

######################### START of the execution #########################

addStudentsFromDB bdb

# now for every user add their grades
set username_list [getBlackboardUsernames bdb]
set cols2import [getColListFromAnyTable bdb export_table]

set cols2import [ pickCols2import $cols2import $skipCreationCol $categories2export]

foreach col $cols2import {
	set colInfo  [trimColName $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 [getBlackboardUserGrade bdb $uname $col ]
		set grade [normilizeGrade $grade $colInfo]
		updateGrade $shortCol $locUname $grade
	}
}