blob: dd49c80db29b241f0e5898421344ef32d63882c3 (
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
|
#!/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]
}
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]
## adding students if they do not exist
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 {
bdb eval $eval_str v {
set email $v(Username)@email.wm.edu
puts [list AddUserNonWeb $v($fname) $v($lname) $email student $v($idnum) 1]
AddUserNonWeb $v($fname) $v($lname) $email student $v($idnum) 1
}
} errStat ]
iferror $err $errStat
# 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] } {
AddColumnNonWeb $shortCol $category $maxScore
#puts [list AddColumnNonWeb $shortCol $category $maxScore]
}
foreach uname $username_list {
set locUname $uname@email.wm.edu
set grade [getBlackboardUserGrade bdb $uname $col ]
if { "" ne $grade } {
if { $scoreType eq "Percentage" } {
# change percentage to score
set grade [ expr {$grade/100*$maxScore} ]
}
UpdateColValue4UserNameNonWeb $shortCol $locUname $grade
#puts [list UpdateColValue4UserNameNonWeb $shortCol $locUname $grade]
}
}
}
|
