blob: c0a88c678b4b77d894e43daa41c93bd4a49c5b07 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
|
#!/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 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]
}
proc veval {script} {
# verbose eval
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} {
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
veval [list AddUserNonWeb $v($fname) $v($lname) $email student $id $section]
}
} errStat ]
iferror $err $errStat
}
proc foreignUsername2local { uname } {
return "$uname@email.wm.edu"
}
######################### START of the execution #########################
set dryrun true
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]
addStudentsFromDB bdb
# now for every user add their grades
set username_list [getBlackboardUsernames bdb]
foreach col [getColListFromAnyTable bdb export_table] {
if { [isInList $col $skipCreationCol] } {
puts "skipping column $col"
continue
}
set colInfo [trimColName $col]
set shortCol [lindex $colInfo 0]
set category [lindex $colInfo 1]
set maxScore [lindex $colInfo 2]
set scoreType [lindex $colInfo 3]
#if { $category ne "FinalExam" } {
#puts "skipping non FinalExam column: $col"
#continue
#}
if { ![doesColumnExists $shortCol GradesTable] } {
veval [list AddColumnNonWeb $shortCol $category $maxScore]
}
foreach uname $username_list {
set locUname [foreignUsername2local $uname]
set grade [getBlackboardUserGrade bdb $uname $col ]
if { "" ne $grade } {
if { $scoreType eq "Percentage" } {
# change percentage to score
set grade [ expr {$grade/100*$maxScore} ]
}
set oldGrade [SelectColValue4User $shortCol $locUname ]
#puts "$locUname: oldGrade \{$oldGrade\} --> $grade"
if { $oldGrade == $grade } { continue }
if { [regexp -nocase -- {excuse} $oldGrade] } {
# remote system do not handle excuses
# so local system take precedence
puts "not updating \"excused\" grade"
continue
}
if { $oldGrade != $grade } {
puts "TESTING: skipping grades which change"
puts "$locUname: $shortCol oldGrade \{$oldGrade\} --> $grade"
continue
}
veval [list UpdateColValue4UserNameNonWeb $shortCol $locUname $grade]
}
}
}
|
