Annotation of loncom/interface/loncoursedata.pm, revision 1.141.2.2
1.1 stredwic 1: # The LearningOnline Network with CAPA
2: #
1.141.2.2! albertel 3: # $Id: loncoursedata.pm,v 1.141.2.1 2005/04/19 21:13:24 albertel Exp $
1.1 stredwic 4: #
5: # Copyright Michigan State University Board of Trustees
6: #
7: # This file is part of the LearningOnline Network with CAPA (LON-CAPA).
8: #
9: # LON-CAPA is free software; you can redistribute it and/or modify
10: # it under the terms of the GNU General Public License as published by
11: # the Free Software Foundation; either version 2 of the License, or
12: # (at your option) any later version.
13: #
14: # LON-CAPA is distributed in the hope that it will be useful,
15: # but WITHOUT ANY WARRANTY; without even the implied warranty of
16: # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17: # GNU General Public License for more details.
18: #
19: # You should have received a copy of the GNU General Public License
20: # along with LON-CAPA; if not, write to the Free Software
21: # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
22: #
23: # /home/httpd/html/adm/gpl.txt
24: #
25: # http://www.lon-capa.org/
26: #
27: ###
28:
29: =pod
30:
31: =head1 NAME
32:
33: loncoursedata
34:
35: =head1 SYNOPSIS
36:
1.22 stredwic 37: Set of functions that download and process student and course information.
1.1 stredwic 38:
39: =head1 PACKAGES USED
40:
41: Apache::Constants qw(:common :http)
42: Apache::lonnet()
1.22 stredwic 43: Apache::lonhtmlcommon
1.1 stredwic 44: HTML::TokeParser
45: GDBM_File
46:
47: =cut
48:
49: package Apache::loncoursedata;
50:
51: use strict;
52: use Apache::Constants qw(:common :http);
53: use Apache::lonnet();
1.13 stredwic 54: use Apache::lonhtmlcommon;
1.57 matthew 55: use Time::HiRes;
56: use Apache::lonmysql;
1.1 stredwic 57: use HTML::TokeParser;
58: use GDBM_File;
59:
60: =pod
61:
62: =head1 DOWNLOAD INFORMATION
63:
1.22 stredwic 64: This section contains all the functions that get data from other servers
65: and/or itself.
1.1 stredwic 66:
67: =cut
68:
1.50 matthew 69: ####################################################
70: ####################################################
1.45 matthew 71:
72: =pod
73:
74: =item &get_sequence_assessment_data()
75:
76: Use lonnavmaps to build a data structure describing the order and
77: assessment contents of each sequence in the current course.
78:
79: The returned structure is a hash reference.
80:
1.61 matthew 81: { title => 'title',
82: symb => 'symb',
83: src => '/s/o/u/r/c/e',
1.45 matthew 84: type => (container|assessment),
1.50 matthew 85: num_assess => 2, # only for container
1.45 matthew 86: parts => [11,13,15], # only for assessment
1.50 matthew 87: response_ids => [12,14,16], # only for assessment
88: contents => [........] # only for container
1.45 matthew 89: }
90:
1.50 matthew 91: $hash->{'contents'} is a reference to an array of hashes of the same structure.
92:
93: Also returned are array references to the sequences and assessments contained
94: in the course.
1.49 matthew 95:
1.45 matthew 96:
97: =cut
98:
1.50 matthew 99: ####################################################
100: ####################################################
1.45 matthew 101: sub get_sequence_assessment_data {
102: my $fn=$ENV{'request.course.fn'};
103: ##
104: ## use navmaps
1.83 bowersj2 105: my $navmap = Apache::lonnavmaps::navmap->new();
1.45 matthew 106: if (!defined($navmap)) {
107: return 'Can not open Coursemap';
108: }
1.75 matthew 109: # We explicity grab the top level map because I am not sure we
110: # are pulling it from the iterator.
111: my $top_level_map = $navmap->getById('0.0');
112: #
1.45 matthew 113: my $iterator = $navmap->getIterator(undef, undef, undef, 1);
1.61 matthew 114: my $curRes = $iterator->next(); # Top level sequence
1.45 matthew 115: ##
116: ## Prime the pump
117: ##
118: ## We are going to loop until we run out of sequences/pages to explore for
119: ## resources. This means we have to start out with something to look
120: ## at.
1.76 matthew 121: my $title = $ENV{'course.'.$ENV{'request.course.id'}.'.description'};
1.75 matthew 122: my $symb = $top_level_map->symb();
123: my $src = $top_level_map->src();
124: my $randompick = $top_level_map->randompick();
1.45 matthew 125: #
1.49 matthew 126: my @Sequences;
127: my @Assessments;
1.45 matthew 128: my @Nested_Sequences = (); # Stack of sequences, keeps track of depth
129: my $top = { title => $title,
1.52 matthew 130: src => $src,
1.45 matthew 131: symb => $symb,
132: type => 'container',
133: num_assess => 0,
1.53 matthew 134: num_assess_parts => 0,
1.75 matthew 135: contents => [],
136: randompick => $randompick,
137: };
1.49 matthew 138: push (@Sequences,$top);
1.45 matthew 139: push (@Nested_Sequences, $top);
140: #
141: # We need to keep track of which sequences contain homework problems
142: #
1.78 matthew 143: my $previous_too;
1.52 matthew 144: my $previous;
1.45 matthew 145: while (scalar(@Nested_Sequences)) {
1.78 matthew 146: $previous_too = $previous;
1.50 matthew 147: $previous = $curRes;
1.141.2.1 albertel 148: $curRes = $iterator->next(1);
1.141.2.2! albertel 149: if (!$curRes) { last; }
1.45 matthew 150: my $currentmap = $Nested_Sequences[-1]; # Last one on the stack
151: if ($curRes == $iterator->BEGIN_MAP()) {
1.78 matthew 152: if (! ref($previous)) {
153: $previous = $previous_too;
154: }
155: if (! ref($previous)) {
156: next;
157: }
1.45 matthew 158: # get the map itself, instead of BEGIN_MAP
1.139 matthew 159: $title = $previous->compTitle;
1.51 matthew 160: $symb = $previous->symb();
161: $src = $previous->src();
1.75 matthew 162: $randompick = $previous->randompick();
1.45 matthew 163: my $newmap = { title => $title,
164: src => $src,
165: symb => $symb,
166: type => 'container',
167: num_assess => 0,
1.75 matthew 168: randompick => $randompick,
1.45 matthew 169: contents => [],
170: };
171: push (@{$currentmap->{'contents'}},$newmap); # this is permanent
1.49 matthew 172: push (@Sequences,$newmap);
1.45 matthew 173: push (@Nested_Sequences, $newmap); # this is a stack
174: next;
175: }
176: if ($curRes == $iterator->END_MAP()) {
177: pop(@Nested_Sequences);
178: next;
179: }
180: next if (! ref($curRes));
1.121 matthew 181: next if (! $curRes->is_problem() && $curRes->src() !~ /\.survey$/);
1.45 matthew 182: # Okay, from here on out we only deal with assessments
1.139 matthew 183: $title = $curRes->compTitle();
1.45 matthew 184: $symb = $curRes->symb();
185: $src = $curRes->src();
186: my $parts = $curRes->parts();
1.87 matthew 187: my %partdata;
188: foreach my $part (@$parts) {
1.88 matthew 189: my @Responses = $curRes->responseType($part);
190: my @Ids = $curRes->responseIds($part);
191: $partdata{$part}->{'ResponseTypes'}= \@Responses;
192: $partdata{$part}->{'ResponseIds'} = \@Ids;
1.135 matthew 193: $partdata{$part}->{'Survey'} = $curRes->is_survey($part);
1.91 matthew 194: # Count how many responses of each type there are in this part
195: foreach (@Responses) {
196: $partdata{$part}->{$_}++;
197: }
1.87 matthew 198: }
1.45 matthew 199: my $assessment = { title => $title,
200: src => $src,
201: symb => $symb,
202: type => 'assessment',
1.53 matthew 203: parts => $parts,
204: num_parts => scalar(@$parts),
1.87 matthew 205: partdata => \%partdata,
1.45 matthew 206: };
1.49 matthew 207: push(@Assessments,$assessment);
1.45 matthew 208: push(@{$currentmap->{'contents'}},$assessment);
209: $currentmap->{'num_assess'}++;
1.53 matthew 210: $currentmap->{'num_assess_parts'}+= scalar(@$parts);
1.45 matthew 211: }
1.49 matthew 212: return ($top,\@Sequences,\@Assessments);
1.45 matthew 213: }
1.50 matthew 214:
1.4 stredwic 215: sub LoadDiscussion {
1.13 stredwic 216: my ($courseID)=@_;
1.5 minaeibi 217: my %Discuss=();
218: my %contrib=&Apache::lonnet::dump(
219: $courseID,
220: $ENV{'course.'.$courseID.'.domain'},
221: $ENV{'course.'.$courseID.'.num'});
222:
223: #my %contrib=&DownloadCourseInformation($name, $courseID, 0);
224:
1.4 stredwic 225: foreach my $temp(keys %contrib) {
226: if ($temp=~/^version/) {
227: my $ver=$contrib{$temp};
228: my ($dummy,$prb)=split(':',$temp);
229: for (my $idx=1; $idx<=$ver; $idx++ ) {
230: my $name=$contrib{"$idx:$prb:sendername"};
1.5 minaeibi 231: $Discuss{"$name:$prb"}=$idx;
1.4 stredwic 232: }
233: }
234: }
1.5 minaeibi 235:
236: return \%Discuss;
1.1 stredwic 237: }
238:
1.71 matthew 239: ################################################
240: ################################################
241:
242: =pod
243:
1.47 matthew 244: =item &make_into_hash($values);
245:
246: Returns a reference to a hash as described by $values. $values is
247: assumed to be the result of
1.57 matthew 248: join(':',map {&Apache::lonnet::escape($_)} %orighash);
1.47 matthew 249:
250: This is a helper function for get_current_state.
251:
252: =cut
253:
254: ################################################
255: ################################################
256: sub make_into_hash {
257: my $values = shift;
258: my %tmp = map { &Apache::lonnet::unescape($_); }
259: split(':',$values);
260: return \%tmp;
261: }
262:
263:
264: ################################################
265: ################################################
266:
267: =pod
268:
1.57 matthew 269: =head1 LOCAL DATA CACHING SUBROUTINES
270:
271: The local caching is done using MySQL. There is no fall-back implementation
272: if MySQL is not running.
273:
274: The programmers interface is to call &get_current_state() or some other
275: primary interface subroutine (described below). The internals of this
276: storage system are documented here.
277:
278: There are six tables used to store student performance data (the results of
279: a dumpcurrent). Each of these tables is created in MySQL with a name of
280: $courseid_*****, where ***** is 'symb', 'part', or whatever is appropriate
281: for the table. The tables and their purposes are described below.
282:
283: Some notes before we get started.
284:
285: Each table must have a PRIMARY KEY, which is a column or set of columns which
286: will serve to uniquely identify a row of data. NULL is not allowed!
287:
288: INDEXes work best on integer data.
289:
290: JOIN is used to combine data from many tables into one output.
291:
292: lonmysql.pm is used for some of the interface, specifically the table creation
293: calls. The inserts are done in bulk by directly calling the database handler.
294: The SELECT ... JOIN statement used to retrieve the data does not have an
295: interface in lonmysql.pm and I shudder at the thought of writing one.
296:
297: =head3 Table Descriptions
298:
299: =over 4
300:
1.89 matthew 301: =item Tables used to store meta information
302:
303: The following tables hold data required to keep track of the current status
304: of a students data in the tables or to look up the students data in the tables.
305:
306: =over 4
307:
1.57 matthew 308: =item $symb_table
309:
310: The symb_table has two columns. The first is a 'symb_id' and the second
311: is the text name for the 'symb' (limited to 64k). The 'symb_id' is generated
312: automatically by MySQL so inserts should be done on this table with an
313: empty first element. This table has its PRIMARY KEY on the 'symb_id'.
314:
315: =item $part_table
316:
317: The part_table has two columns. The first is a 'part_id' and the second
318: is the text name for the 'part' (limited to 100 characters). The 'part_id' is
319: generated automatically by MySQL so inserts should be done on this table with
320: an empty first element. This table has its PRIMARY KEY on the 'part' (100
321: characters) and a KEY on 'part_id'.
322:
323: =item $student_table
324:
1.113 matthew 325: The student_table has 7 columns. The first is a 'student_id' assigned by
326: MySQL. The second is 'student' which is username:domain. The third through
327: fifth are 'section', 'status' (enrollment status), and 'classification'
328: (to be used in the future). The sixth and seventh ('updatetime' and
329: 'fullupdatetime') contain the time of last update and full update of student
330: data. This table has its PRIMARY KEY on the 'student_id' column and is indexed
331: on 'student', 'section', and 'status'.
1.89 matthew 332:
333: =back
334:
335: =item Tables used to store current status data
336:
337: The following tables store data only about the students current status on
338: a problem, meaning only the data related to the last attempt on a problem.
339:
340: =over 4
1.57 matthew 341:
342: =item $performance_table
343:
344: The performance_table has 9 columns. The first three are 'symb_id',
345: 'student_id', and 'part_id'. These comprise the PRIMARY KEY for this table
346: and are directly related to the $symb_table, $student_table, and $part_table
347: described above. MySQL does better indexing on numeric items than text,
348: so we use these three "index tables". The remaining columns are
349: 'solved', 'tries', 'awarded', 'award', 'awarddetail', and 'timestamp'.
350: These are either the MySQL type TINYTEXT or various integers ('tries' and
351: 'timestamp'). This table has KEYs of 'student_id' and 'symb_id'.
352: For use of this table, see the functions described below.
353:
354: =item $parameters_table
355:
356: The parameters_table holds the data that does not fit neatly into the
357: performance_table. The parameters table has four columns: 'symb_id',
358: 'student_id', 'parameter', and 'value'. 'symb_id', 'student_id', and
359: 'parameter' comprise the PRIMARY KEY for this table. 'parameter' is
360: limited to 255 characters. 'value' is limited to 64k characters.
361:
362: =back
363:
1.89 matthew 364: =item Tables used for storing historic data
365:
366: The following tables are used to store almost all of the transactions a student
367: has made on a homework problem. See loncapa/docs/homework/datastorage for
368: specific information about each of the parameters stored.
369:
370: =over 4
371:
372: =item $fulldump_response_table
373:
374: The response table holds data (documented in loncapa/docs/homework/datastorage)
375: associated with a particular response id which is stored when a student
376: attempts a problem. The following are the columns of the table, in order:
377: 'symb_id','part_id','response_id','student_id','transaction','tries',
1.93 matthew 378: 'awarddetail', 'response_specific' (data particular to the response
1.89 matthew 379: type), 'response_specific_value', and 'submission (the text of the students
380: submission). The primary key is based on the first five columns listed above.
381:
382: =item $fulldump_part_table
383:
384: The part table holds data (documented in loncapa/docs/homework/datastorage)
385: associated with a particular part id which is stored when a student attempts
386: a problem. The following are the columns of the table, in order:
387: 'symb_id','part_id','student_id','transaction','tries','award','awarded',
388: and 'previous'. The primary key is based on the first five columns listed
389: above.
390:
391: =item $fulldump_timestamp_table
392:
393: The timestamp table holds the timestamps of the transactions which are
394: stored in $fulldump_response_table and $fulldump_part_table. This data is
395: about both the response and part data. Columns: 'symb_id','student_id',
396: 'transaction', and 'timestamp'.
397: The primary key is based on the first 3 columns.
398:
1.127 matthew 399: =item $weight_table
400:
401: The weight table holds the weight for the problems used in the class.
402: Whereas the weight of a problem can vary by section and student the data
403: here is applied to the class as a whole.
404: Columns: 'symb_id','part_id','response_id','weight'.
405:
1.89 matthew 406: =back
407:
408: =back
409:
1.57 matthew 410: =head3 Important Subroutines
411:
412: Here is a brief overview of the subroutines which are likely to be of
413: interest:
414:
415: =over 4
416:
417: =item &get_current_state(): programmers interface.
418:
419: =item &init_dbs(): table creation
420:
421: =item &update_student_data(): data storage calls
422:
423: =item &get_student_data_from_performance_cache(): data retrieval
424:
425: =back
426:
427: =head3 Main Documentation
428:
429: =over 4
430:
431: =cut
432:
433: ################################################
434: ################################################
435:
436: ################################################
437: ################################################
1.89 matthew 438: { # Begin scope of table identifiers
1.57 matthew 439:
440: my $current_course ='';
441: my $symb_table;
442: my $part_table;
443: my $student_table;
444: my $performance_table;
445: my $parameters_table;
1.89 matthew 446: my $fulldump_response_table;
447: my $fulldump_part_table;
448: my $fulldump_timestamp_table;
1.127 matthew 449: my $weight_table;
1.57 matthew 450:
1.89 matthew 451: my @Tables;
1.57 matthew 452: ################################################
453: ################################################
454:
455: =pod
456:
457: =item &init_dbs()
458:
459: Input: course id
460:
461: Output: 0 on success, positive integer on error
462:
463: This routine issues the calls to lonmysql to create the tables used to
464: store student data.
465:
466: =cut
467:
468: ################################################
469: ################################################
470: sub init_dbs {
1.134 matthew 471: my ($courseid,$drop) = @_;
1.57 matthew 472: &setup_table_names($courseid);
473: #
1.73 matthew 474: # Drop any of the existing tables
1.134 matthew 475: if ($drop) {
476: foreach my $table (@Tables) {
477: &Apache::lonmysql::drop_table($table);
478: }
1.73 matthew 479: }
480: #
1.57 matthew 481: # Note - changes to this table must be reflected in the code that
482: # stores the data (calls &Apache::lonmysql::store_row with this table
483: # id
484: my $symb_table_def = {
485: id => $symb_table,
486: permanent => 'no',
487: columns => [{ name => 'symb_id',
488: type => 'MEDIUMINT UNSIGNED',
489: restrictions => 'NOT NULL',
490: auto_inc => 'yes', },
491: { name => 'symb',
492: type => 'MEDIUMTEXT',
493: restrictions => 'NOT NULL'},
494: ],
495: 'PRIMARY KEY' => ['symb_id'],
496: };
497: #
498: my $part_table_def = {
499: id => $part_table,
500: permanent => 'no',
501: columns => [{ name => 'part_id',
502: type => 'MEDIUMINT UNSIGNED',
503: restrictions => 'NOT NULL',
504: auto_inc => 'yes', },
505: { name => 'part',
1.132 matthew 506: type => 'VARCHAR(100) BINARY',
1.57 matthew 507: restrictions => 'NOT NULL'},
508: ],
509: 'PRIMARY KEY' => ['part (100)'],
510: 'KEY' => [{ columns => ['part_id']},],
511: };
512: #
513: my $student_table_def = {
514: id => $student_table,
515: permanent => 'no',
516: columns => [{ name => 'student_id',
517: type => 'MEDIUMINT UNSIGNED',
518: restrictions => 'NOT NULL',
519: auto_inc => 'yes', },
520: { name => 'student',
1.132 matthew 521: type => 'VARCHAR(100) BINARY',
1.113 matthew 522: restrictions => 'NOT NULL UNIQUE'},
523: { name => 'section',
1.132 matthew 524: type => 'VARCHAR(100) BINARY',
1.113 matthew 525: restrictions => 'NOT NULL'},
526: { name => 'status',
1.132 matthew 527: type => 'VARCHAR(15) BINARY',
1.57 matthew 528: restrictions => 'NOT NULL'},
1.85 matthew 529: { name => 'classification',
1.132 matthew 530: type => 'VARCHAR(100) BINARY', },
1.57 matthew 531: { name => 'updatetime',
1.89 matthew 532: type => 'INT UNSIGNED'},
533: { name => 'fullupdatetime',
534: type => 'INT UNSIGNED'},
1.57 matthew 535: ],
1.87 matthew 536: 'PRIMARY KEY' => ['student_id'],
1.113 matthew 537: 'KEY' => [{ columns => ['student (100)',
538: 'section (100)',
539: 'status (15)',]},],
1.57 matthew 540: };
541: #
542: my $performance_table_def = {
543: id => $performance_table,
544: permanent => 'no',
545: columns => [{ name => 'symb_id',
546: type => 'MEDIUMINT UNSIGNED',
547: restrictions => 'NOT NULL' },
548: { name => 'student_id',
549: type => 'MEDIUMINT UNSIGNED',
550: restrictions => 'NOT NULL' },
551: { name => 'part_id',
552: type => 'MEDIUMINT UNSIGNED',
553: restrictions => 'NOT NULL' },
1.73 matthew 554: { name => 'part',
1.132 matthew 555: type => 'VARCHAR(100) BINARY',
1.73 matthew 556: restrictions => 'NOT NULL'},
1.57 matthew 557: { name => 'solved',
558: type => 'TINYTEXT' },
559: { name => 'tries',
560: type => 'SMALLINT UNSIGNED' },
561: { name => 'awarded',
1.127 matthew 562: type => 'REAL' },
1.57 matthew 563: { name => 'award',
564: type => 'TINYTEXT' },
565: { name => 'awarddetail',
566: type => 'TINYTEXT' },
567: { name => 'timestamp',
568: type => 'INT UNSIGNED'},
569: ],
570: 'PRIMARY KEY' => ['symb_id','student_id','part_id'],
571: 'KEY' => [{ columns=>['student_id'] },
572: { columns=>['symb_id'] },],
573: };
574: #
1.89 matthew 575: my $fulldump_part_table_def = {
576: id => $fulldump_part_table,
577: permanent => 'no',
578: columns => [
579: { name => 'symb_id',
580: type => 'MEDIUMINT UNSIGNED',
581: restrictions => 'NOT NULL' },
582: { name => 'part_id',
583: type => 'MEDIUMINT UNSIGNED',
584: restrictions => 'NOT NULL' },
585: { name => 'student_id',
586: type => 'MEDIUMINT UNSIGNED',
587: restrictions => 'NOT NULL' },
588: { name => 'transaction',
589: type => 'MEDIUMINT UNSIGNED',
590: restrictions => 'NOT NULL' },
591: { name => 'tries',
592: type => 'SMALLINT UNSIGNED',
593: restrictions => 'NOT NULL' },
594: { name => 'award',
595: type => 'TINYTEXT' },
596: { name => 'awarded',
1.127 matthew 597: type => 'REAL' },
1.89 matthew 598: { name => 'previous',
599: type => 'SMALLINT UNSIGNED' },
600: # { name => 'regrader',
601: # type => 'TINYTEXT' },
602: # { name => 'afterduedate',
603: # type => 'TINYTEXT' },
604: ],
605: 'PRIMARY KEY' => ['symb_id','part_id','student_id','transaction'],
606: 'KEY' => [
607: { columns=>['symb_id'] },
608: { columns=>['part_id'] },
609: { columns=>['student_id'] },
610: ],
611: };
612: #
613: my $fulldump_response_table_def = {
614: id => $fulldump_response_table,
615: permanent => 'no',
616: columns => [
617: { name => 'symb_id',
618: type => 'MEDIUMINT UNSIGNED',
619: restrictions => 'NOT NULL' },
620: { name => 'part_id',
621: type => 'MEDIUMINT UNSIGNED',
622: restrictions => 'NOT NULL' },
623: { name => 'response_id',
624: type => 'MEDIUMINT UNSIGNED',
625: restrictions => 'NOT NULL' },
626: { name => 'student_id',
627: type => 'MEDIUMINT UNSIGNED',
628: restrictions => 'NOT NULL' },
629: { name => 'transaction',
630: type => 'MEDIUMINT UNSIGNED',
631: restrictions => 'NOT NULL' },
632: { name => 'awarddetail',
633: type => 'TINYTEXT' },
634: # { name => 'message',
1.132 matthew 635: # type => 'CHAR BINARY'},
1.89 matthew 636: { name => 'response_specific',
637: type => 'TINYTEXT' },
638: { name => 'response_specific_value',
639: type => 'TINYTEXT' },
640: { name => 'submission',
641: type => 'TEXT'},
642: ],
643: 'PRIMARY KEY' => ['symb_id','part_id','response_id','student_id',
644: 'transaction'],
645: 'KEY' => [
646: { columns=>['symb_id'] },
647: { columns=>['part_id','response_id'] },
648: { columns=>['student_id'] },
649: ],
650: };
651: my $fulldump_timestamp_table_def = {
652: id => $fulldump_timestamp_table,
653: permanent => 'no',
654: columns => [
655: { name => 'symb_id',
656: type => 'MEDIUMINT UNSIGNED',
657: restrictions => 'NOT NULL' },
658: { name => 'student_id',
659: type => 'MEDIUMINT UNSIGNED',
660: restrictions => 'NOT NULL' },
661: { name => 'transaction',
662: type => 'MEDIUMINT UNSIGNED',
663: restrictions => 'NOT NULL' },
664: { name => 'timestamp',
665: type => 'INT UNSIGNED'},
666: ],
667: 'PRIMARY KEY' => ['symb_id','student_id','transaction'],
668: 'KEY' => [
669: { columns=>['symb_id'] },
670: { columns=>['student_id'] },
671: { columns=>['transaction'] },
672: ],
673: };
674: #
1.57 matthew 675: my $parameters_table_def = {
676: id => $parameters_table,
677: permanent => 'no',
678: columns => [{ name => 'symb_id',
679: type => 'MEDIUMINT UNSIGNED',
680: restrictions => 'NOT NULL' },
681: { name => 'student_id',
682: type => 'MEDIUMINT UNSIGNED',
683: restrictions => 'NOT NULL' },
684: { name => 'parameter',
685: type => 'TINYTEXT',
686: restrictions => 'NOT NULL' },
687: { name => 'value',
688: type => 'MEDIUMTEXT' },
689: ],
690: 'PRIMARY KEY' => ['symb_id','student_id','parameter (255)'],
691: };
692: #
1.127 matthew 693: my $weight_table_def = {
694: id => $weight_table,
695: permanent => 'no',
696: columns => [{ name => 'symb_id',
697: type => 'MEDIUMINT UNSIGNED',
698: restrictions => 'NOT NULL' },
699: { name => 'part_id',
700: type => 'MEDIUMINT UNSIGNED',
701: restrictions => 'NOT NULL' },
702: { name => 'weight',
703: type => 'REAL',
704: restrictions => 'NOT NULL' },
705: ],
706: 'PRIMARY KEY' => ['symb_id','part_id'],
707: };
708: #
1.57 matthew 709: # Create the tables
710: my $tableid;
711: $tableid = &Apache::lonmysql::create_table($symb_table_def);
712: if (! defined($tableid)) {
713: &Apache::lonnet::logthis("error creating symb_table: ".
714: &Apache::lonmysql::get_error());
715: return 1;
716: }
717: #
718: $tableid = &Apache::lonmysql::create_table($part_table_def);
719: if (! defined($tableid)) {
720: &Apache::lonnet::logthis("error creating part_table: ".
721: &Apache::lonmysql::get_error());
722: return 2;
723: }
724: #
725: $tableid = &Apache::lonmysql::create_table($student_table_def);
726: if (! defined($tableid)) {
727: &Apache::lonnet::logthis("error creating student_table: ".
728: &Apache::lonmysql::get_error());
729: return 3;
730: }
731: #
732: $tableid = &Apache::lonmysql::create_table($performance_table_def);
733: if (! defined($tableid)) {
734: &Apache::lonnet::logthis("error creating preformance_table: ".
735: &Apache::lonmysql::get_error());
736: return 5;
737: }
738: #
739: $tableid = &Apache::lonmysql::create_table($parameters_table_def);
740: if (! defined($tableid)) {
741: &Apache::lonnet::logthis("error creating parameters_table: ".
742: &Apache::lonmysql::get_error());
743: return 6;
744: }
1.89 matthew 745: #
746: $tableid = &Apache::lonmysql::create_table($fulldump_part_table_def);
747: if (! defined($tableid)) {
748: &Apache::lonnet::logthis("error creating fulldump_part_table: ".
749: &Apache::lonmysql::get_error());
750: return 7;
751: }
752: #
753: $tableid = &Apache::lonmysql::create_table($fulldump_response_table_def);
754: if (! defined($tableid)) {
755: &Apache::lonnet::logthis("error creating fulldump_response_table: ".
756: &Apache::lonmysql::get_error());
757: return 8;
758: }
759: $tableid = &Apache::lonmysql::create_table($fulldump_timestamp_table_def);
760: if (! defined($tableid)) {
761: &Apache::lonnet::logthis("error creating fulldump_timestamp_table: ".
762: &Apache::lonmysql::get_error());
763: return 9;
764: }
1.127 matthew 765: $tableid = &Apache::lonmysql::create_table($weight_table_def);
766: if (! defined($tableid)) {
767: &Apache::lonnet::logthis("error creating weight_table: ".
768: &Apache::lonmysql::get_error());
769: return 10;
770: }
1.57 matthew 771: return 0;
1.70 matthew 772: }
773:
774: ################################################
775: ################################################
776:
777: =pod
778:
779: =item &delete_caches()
780:
781: =cut
782:
783: ################################################
784: ################################################
785: sub delete_caches {
786: my $courseid = shift;
787: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
788: #
789: &setup_table_names($courseid);
790: #
791: my $dbh = &Apache::lonmysql::get_dbh();
1.89 matthew 792: foreach my $table (@Tables) {
1.70 matthew 793: my $command = 'DROP TABLE '.$table.';';
794: $dbh->do($command);
795: if ($dbh->err) {
796: &Apache::lonnet::logthis($command.' resulted in error: '.$dbh->errstr);
797: }
798: }
799: return;
1.57 matthew 800: }
801:
802: ################################################
803: ################################################
804:
805: =pod
806:
807: =item &get_part_id()
808:
809: Get the MySQL id of a problem part string.
810:
811: Input: $part
812:
813: Output: undef on error, integer $part_id on success.
814:
815: =item &get_part()
816:
817: Get the string describing a part from the MySQL id of the problem part.
818:
819: Input: $part_id
820:
821: Output: undef on error, $part string on success.
822:
823: =cut
824:
825: ################################################
826: ################################################
827:
1.61 matthew 828: my $have_read_part_table = 0;
1.57 matthew 829: my %ids_by_part;
830: my %parts_by_id;
831:
832: sub get_part_id {
833: my ($part) = @_;
1.61 matthew 834: $part = 0 if (! defined($part));
835: if (! $have_read_part_table) {
836: my @Result = &Apache::lonmysql::get_rows($part_table);
837: foreach (@Result) {
838: $ids_by_part{$_->[1]}=$_->[0];
839: }
840: $have_read_part_table = 1;
841: }
1.57 matthew 842: if (! exists($ids_by_part{$part})) {
843: &Apache::lonmysql::store_row($part_table,[undef,$part]);
844: undef(%ids_by_part);
845: my @Result = &Apache::lonmysql::get_rows($part_table);
846: foreach (@Result) {
847: $ids_by_part{$_->[1]}=$_->[0];
848: }
849: }
850: return $ids_by_part{$part} if (exists($ids_by_part{$part}));
851: return undef; # error
852: }
853:
854: sub get_part {
855: my ($part_id) = @_;
856: if (! exists($parts_by_id{$part_id}) ||
857: ! defined($parts_by_id{$part_id}) ||
858: $parts_by_id{$part_id} eq '') {
859: my @Result = &Apache::lonmysql::get_rows($part_table);
860: foreach (@Result) {
861: $parts_by_id{$_->[0]}=$_->[1];
862: }
863: }
864: return $parts_by_id{$part_id} if(exists($parts_by_id{$part_id}));
865: return undef; # error
866: }
867:
868: ################################################
869: ################################################
870:
871: =pod
872:
873: =item &get_symb_id()
874:
875: Get the MySQL id of a symb.
876:
877: Input: $symb
878:
879: Output: undef on error, integer $symb_id on success.
880:
881: =item &get_symb()
882:
883: Get the symb associated with a MySQL symb_id.
884:
885: Input: $symb_id
886:
887: Output: undef on error, $symb on success.
888:
889: =cut
890:
891: ################################################
892: ################################################
893:
1.61 matthew 894: my $have_read_symb_table = 0;
1.57 matthew 895: my %ids_by_symb;
896: my %symbs_by_id;
897:
898: sub get_symb_id {
899: my ($symb) = @_;
1.61 matthew 900: if (! $have_read_symb_table) {
901: my @Result = &Apache::lonmysql::get_rows($symb_table);
902: foreach (@Result) {
903: $ids_by_symb{$_->[1]}=$_->[0];
904: }
905: $have_read_symb_table = 1;
906: }
1.57 matthew 907: if (! exists($ids_by_symb{$symb})) {
908: &Apache::lonmysql::store_row($symb_table,[undef,$symb]);
909: undef(%ids_by_symb);
910: my @Result = &Apache::lonmysql::get_rows($symb_table);
911: foreach (@Result) {
912: $ids_by_symb{$_->[1]}=$_->[0];
913: }
914: }
915: return $ids_by_symb{$symb} if(exists( $ids_by_symb{$symb}));
916: return undef; # error
917: }
918:
919: sub get_symb {
920: my ($symb_id) = @_;
921: if (! exists($symbs_by_id{$symb_id}) ||
922: ! defined($symbs_by_id{$symb_id}) ||
923: $symbs_by_id{$symb_id} eq '') {
924: my @Result = &Apache::lonmysql::get_rows($symb_table);
925: foreach (@Result) {
926: $symbs_by_id{$_->[0]}=$_->[1];
927: }
928: }
929: return $symbs_by_id{$symb_id} if(exists( $symbs_by_id{$symb_id}));
930: return undef; # error
931: }
932:
933: ################################################
934: ################################################
935:
936: =pod
937:
938: =item &get_student_id()
939:
940: Get the MySQL id of a student.
941:
942: Input: $sname, $dom
943:
944: Output: undef on error, integer $student_id on success.
945:
946: =item &get_student()
947:
948: Get student username:domain associated with the MySQL student_id.
949:
950: Input: $student_id
951:
952: Output: undef on error, string $student (username:domain) on success.
953:
954: =cut
955:
956: ################################################
957: ################################################
958:
1.61 matthew 959: my $have_read_student_table = 0;
1.57 matthew 960: my %ids_by_student;
961: my %students_by_id;
962:
963: sub get_student_id {
964: my ($sname,$sdom) = @_;
965: my $student = $sname.':'.$sdom;
1.61 matthew 966: if (! $have_read_student_table) {
967: my @Result = &Apache::lonmysql::get_rows($student_table);
968: foreach (@Result) {
969: $ids_by_student{$_->[1]}=$_->[0];
970: }
971: $have_read_student_table = 1;
972: }
1.57 matthew 973: if (! exists($ids_by_student{$student})) {
1.113 matthew 974: &populate_student_table();
1.57 matthew 975: undef(%ids_by_student);
1.113 matthew 976: undef(%students_by_id);
1.57 matthew 977: my @Result = &Apache::lonmysql::get_rows($student_table);
978: foreach (@Result) {
979: $ids_by_student{$_->[1]}=$_->[0];
980: }
981: }
982: return $ids_by_student{$student} if(exists( $ids_by_student{$student}));
983: return undef; # error
984: }
985:
986: sub get_student {
987: my ($student_id) = @_;
988: if (! exists($students_by_id{$student_id}) ||
989: ! defined($students_by_id{$student_id}) ||
990: $students_by_id{$student_id} eq '') {
991: my @Result = &Apache::lonmysql::get_rows($student_table);
992: foreach (@Result) {
993: $students_by_id{$_->[0]}=$_->[1];
994: }
995: }
996: return $students_by_id{$student_id} if(exists($students_by_id{$student_id}));
997: return undef; # error
998: }
1.99 matthew 999:
1.113 matthew 1000: sub populate_student_table {
1001: my ($courseid) = @_;
1002: if (! defined($courseid)) {
1003: $courseid = $ENV{'request.course.id'};
1004: }
1005: #
1006: &setup_table_names($courseid);
1.134 matthew 1007: &init_dbs($courseid,0);
1.113 matthew 1008: my $dbh = &Apache::lonmysql::get_dbh();
1009: my $request = 'INSERT IGNORE INTO '.$student_table.
1010: "(student,section,status) VALUES ";
1011: my $classlist = &get_classlist($courseid);
1012: my $student_count=0;
1013: while (my ($student,$data) = each %$classlist) {
1014: my ($section,$status) = ($data->[&CL_SECTION()],
1015: $data->[&CL_STATUS()]);
1016: if ($section eq '' || $section =~ /^\s*$/) {
1017: $section = 'none';
1018: }
1019: $request .= "('".$student."','".$section."','".$status."'),";
1020: $student_count++;
1021: }
1022: return if ($student_count == 0);
1023: chop($request);
1024: $dbh->do($request);
1025: if ($dbh->err()) {
1026: &Apache::lonnet::logthis("error ".$dbh->errstr().
1027: " occured executing \n".
1028: $request);
1029: }
1030: return;
1031: }
1032:
1.99 matthew 1033: ################################################
1034: ################################################
1035:
1036: =pod
1037:
1038: =item &clear_internal_caches()
1039:
1040: Causes the internal caches used in get_student_id, get_student,
1041: get_symb_id, get_symb, get_part_id, and get_part to be undef'd.
1042:
1043: Needs to be called before the first operation with the MySQL database
1044: for a given Apache request.
1045:
1046: =cut
1047:
1048: ################################################
1049: ################################################
1050: sub clear_internal_caches {
1051: $have_read_part_table = 0;
1052: undef(%ids_by_part);
1053: undef(%parts_by_id);
1054: $have_read_symb_table = 0;
1055: undef(%ids_by_symb);
1056: undef(%symbs_by_id);
1057: $have_read_student_table = 0;
1058: undef(%ids_by_student);
1059: undef(%students_by_id);
1060: }
1061:
1.57 matthew 1062:
1063: ################################################
1064: ################################################
1065:
1066: =pod
1067:
1.89 matthew 1068: =item &update_full_student_data($sname,$sdom,$courseid)
1069:
1070: Does a lonnet::dump on a student to populate the courses tables.
1071:
1072: Input: $sname, $sdom, $courseid
1073:
1074: Output: $returnstatus
1075:
1076: $returnstatus is a string describing any errors that occured. 'okay' is the
1077: default.
1078:
1079: This subroutine loads a students data using lonnet::dump and inserts
1080: it into the MySQL database. The inserts are done on three tables,
1081: $fulldump_response_table, $fulldump_part_table, and $fulldump_timestamp_table.
1082: The INSERT calls are made directly by this subroutine, not through lonmysql
1083: because we do a 'bulk'insert which takes advantage of MySQLs non-SQL
1084: compliant INSERT command to insert multiple rows at a time.
1085: If anything has gone wrong during this process, $returnstatus is updated with
1086: a description of the error.
1087:
1088: Once the "fulldump" tables are updated, the tables used for chart and
1089: spreadsheet (which hold only the current state of the student on their
1090: homework, not historical data) are updated. If all updates have occured
1.113 matthew 1091: successfully, $student_table is updated to reflect the time of the update.
1.89 matthew 1092:
1093: Notice we do not insert the data and immediately query it. This means it
1094: is possible for there to be data returned this first time that is not
1095: available the second time. CYA.
1096:
1097: =cut
1098:
1099: ################################################
1100: ################################################
1101: sub update_full_student_data {
1102: my ($sname,$sdom,$courseid) = @_;
1103: #
1104: # Set up database names
1105: &setup_table_names($courseid);
1106: #
1107: my $student_id = &get_student_id($sname,$sdom);
1108: my $student = $sname.':'.$sdom;
1109: #
1110: my $returnstatus = 'okay';
1111: #
1112: # Download students data
1113: my $time_of_retrieval = time;
1114: my @tmp = &Apache::lonnet::dump($courseid,$sdom,$sname);
1115: if (@tmp && $tmp[0] =~ /^error/) {
1116: $returnstatus = 'error retrieving full student data';
1117: return $returnstatus;
1118: } elsif (! @tmp) {
1119: $returnstatus = 'okay: no student data';
1120: return $returnstatus;
1121: }
1122: my %studentdata = @tmp;
1123: #
1124: # Get database handle and clean out the tables
1125: my $dbh = &Apache::lonmysql::get_dbh();
1126: $dbh->do('DELETE FROM '.$fulldump_response_table.' WHERE student_id='.
1127: $student_id);
1128: $dbh->do('DELETE FROM '.$fulldump_part_table.' WHERE student_id='.
1129: $student_id);
1130: $dbh->do('DELETE FROM '.$fulldump_timestamp_table.' WHERE student_id='.
1131: $student_id);
1132: #
1133: # Parse and store the data into a form we can handle
1134: my $partdata;
1135: my $respdata;
1136: while (my ($key,$value) = each(%studentdata)) {
1137: next if ($key =~ /^(\d+):(resource$|subnum$|keys:)/);
1138: my ($transaction,$symb,$parameter) = split(':',$key);
1139: my $symb_id = &get_symb_id($symb);
1140: if ($parameter eq 'timestamp') {
1141: # We can deal with 'timestamp' right away
1142: my @timestamp_storage = ($symb_id,$student_id,
1143: $transaction,$value);
1.98 matthew 1144: my $store_command = 'INSERT IGNORE INTO '.$fulldump_timestamp_table.
1.89 matthew 1145: " VALUES ('".join("','",@timestamp_storage)."');";
1146: $dbh->do($store_command);
1147: if ($dbh->err()) {
1148: &Apache::lonnet::logthis('unable to execute '.$store_command);
1149: &Apache::lonnet::logthis($dbh->errstr());
1150: }
1151: next;
1152: } elsif ($parameter eq 'version') {
1153: next;
1.90 matthew 1154: } elsif ($parameter =~ /^resource\.(.*)\.(tries|
1155: award|
1156: awarded|
1157: previous|
1158: solved|
1159: awarddetail|
1160: submission|
1161: submissiongrading|
1162: molecule)\s*$/x){
1.89 matthew 1163: # we do not have enough information to store an
1164: # entire row, so we save it up until later.
1165: my ($part_and_resp_id,$field) = ($1,$2);
1166: my ($part,$part_id,$resp,$resp_id);
1167: if ($part_and_resp_id =~ /\./) {
1168: ($part,$resp) = split(/\./,$part_and_resp_id);
1169: $part_id = &get_part_id($part);
1170: $resp_id = &get_part_id($resp);
1171: } else {
1172: $part_id = &get_part_id($part_and_resp_id);
1173: }
1.90 matthew 1174: # Deal with part specific data
1.89 matthew 1175: if ($field =~ /^(tries|award|awarded|previous)$/) {
1176: $partdata->{$symb_id}->{$part_id}->{$transaction}->{$field}=$value;
1177: }
1.90 matthew 1178: # deal with response specific data
1.89 matthew 1179: if (defined($resp_id) &&
1.100 matthew 1180: $field =~ /^(awarddetail|
1.90 matthew 1181: submission|
1182: submissiongrading|
1183: molecule)$/x) {
1.89 matthew 1184: if ($field eq 'submission') {
1185: # We have to be careful with user supplied input.
1186: # most of the time we are okay because it is escaped.
1187: # However, there is one wrinkle: submissions which end in
1188: # and odd number of '\' cause insert errors to occur.
1189: # Best trap this somehow...
1.116 matthew 1190: $value = $dbh->quote($value);
1.89 matthew 1191: }
1.90 matthew 1192: if ($field eq 'submissiongrading' ||
1193: $field eq 'molecule') {
1194: $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{'response_specific'}=$field;
1195: $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{'response_specific_value'}=$value;
1196: } else {
1197: $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{$field}=$value;
1198: }
1.89 matthew 1199: }
1200: }
1201: }
1202: ##
1203: ## Store the part data
1.98 matthew 1204: my $store_command = 'INSERT IGNORE INTO '.$fulldump_part_table.
1.89 matthew 1205: ' VALUES '."\n";
1206: my $store_rows = 0;
1207: while (my ($symb_id,$hash1) = each (%$partdata)) {
1208: while (my ($part_id,$hash2) = each (%$hash1)) {
1209: while (my ($transaction,$data) = each (%$hash2)) {
1210: $store_command .= "('".join("','",$symb_id,$part_id,
1211: $student_id,
1212: $transaction,
1.101 matthew 1213: $data->{'tries'},
1.89 matthew 1214: $data->{'award'},
1215: $data->{'awarded'},
1216: $data->{'previous'})."'),";
1217: $store_rows++;
1218: }
1219: }
1220: }
1221: if ($store_rows) {
1222: chop($store_command);
1223: $dbh->do($store_command);
1224: if ($dbh->err) {
1225: $returnstatus = 'error storing part data';
1226: &Apache::lonnet::logthis('insert error '.$dbh->errstr());
1227: &Apache::lonnet::logthis("While attempting\n".$store_command);
1228: }
1229: }
1230: ##
1231: ## Store the response data
1.98 matthew 1232: $store_command = 'INSERT IGNORE INTO '.$fulldump_response_table.
1.89 matthew 1233: ' VALUES '."\n";
1234: $store_rows = 0;
1235: while (my ($symb_id,$hash1) = each (%$respdata)) {
1236: while (my ($part_id,$hash2) = each (%$hash1)) {
1237: while (my ($resp_id,$hash3) = each (%$hash2)) {
1238: while (my ($transaction,$data) = each (%$hash3)) {
1.112 matthew 1239: my $submission = $data->{'submission'};
1240: # We have to be careful with user supplied input.
1241: # most of the time we are okay because it is escaped.
1242: # However, there is one wrinkle: submissions which end in
1243: # and odd number of '\' cause insert errors to occur.
1244: # Best trap this somehow...
1245: $submission = $dbh->quote($submission);
1246: $store_command .= "('".
1247: join("','",$symb_id,$part_id,
1248: $resp_id,$student_id,
1249: $transaction,
1250: $data->{'awarddetail'},
1251: $data->{'response_specific'},
1252: $data->{'response_specific_value'}).
1253: "',".$submission."),";
1.89 matthew 1254: $store_rows++;
1255: }
1256: }
1257: }
1258: }
1259: if ($store_rows) {
1260: chop($store_command);
1261: $dbh->do($store_command);
1262: if ($dbh->err) {
1263: $returnstatus = 'error storing response data';
1264: &Apache::lonnet::logthis('insert error '.$dbh->errstr());
1265: &Apache::lonnet::logthis("While attempting\n".$store_command);
1266: }
1267: }
1268: ##
1269: ## Update the students "current" data in the performance
1270: ## and parameters tables.
1271: my ($status,undef) = &store_student_data
1272: ($sname,$sdom,$courseid,
1273: &Apache::lonnet::convert_dump_to_currentdump(\%studentdata));
1274: if ($returnstatus eq 'okay' && $status ne 'okay') {
1275: $returnstatus = 'error storing current data:'.$status;
1276: } elsif ($status ne 'okay') {
1277: $returnstatus .= ' error storing current data:'.$status;
1278: }
1279: ##
1280: ## Update the students time......
1281: if ($returnstatus eq 'okay') {
1.113 matthew 1282: &store_updatetime($student_id,$time_of_retrieval,$time_of_retrieval);
1283: if ($dbh->err) {
1284: if ($returnstatus eq 'okay') {
1285: $returnstatus = 'error updating student time';
1286: } else {
1287: $returnstatus = 'error updating student time';
1288: }
1289: }
1.89 matthew 1290: }
1291: return $returnstatus;
1292: }
1293:
1294: ################################################
1295: ################################################
1296:
1297: =pod
1298:
1.57 matthew 1299: =item &update_student_data()
1300:
1301: Input: $sname, $sdom, $courseid
1302:
1303: Output: $returnstatus, \%student_data
1304:
1305: $returnstatus is a string describing any errors that occured. 'okay' is the
1306: default.
1307: \%student_data is the data returned by a call to lonnet::currentdump.
1308:
1309: This subroutine loads a students data using lonnet::currentdump and inserts
1310: it into the MySQL database. The inserts are done on two tables,
1311: $performance_table and $parameters_table. $parameters_table holds the data
1312: that is not included in $performance_table. See the description of
1313: $performance_table elsewhere in this file. The INSERT calls are made
1314: directly by this subroutine, not through lonmysql because we do a 'bulk'
1315: insert which takes advantage of MySQLs non-SQL compliant INSERT command to
1316: insert multiple rows at a time. If anything has gone wrong during this
1317: process, $returnstatus is updated with a description of the error and
1318: \%student_data is returned.
1319:
1320: Notice we do not insert the data and immediately query it. This means it
1321: is possible for there to be data returned this first time that is not
1322: available the second time. CYA.
1323:
1324: =cut
1325:
1326: ################################################
1327: ################################################
1328: sub update_student_data {
1329: my ($sname,$sdom,$courseid) = @_;
1330: #
1.60 matthew 1331: # Set up database names
1332: &setup_table_names($courseid);
1333: #
1.57 matthew 1334: my $student_id = &get_student_id($sname,$sdom);
1335: my $student = $sname.':'.$sdom;
1336: #
1337: my $returnstatus = 'okay';
1338: #
1339: # Download students data
1340: my $time_of_retrieval = time;
1341: my @tmp = &Apache::lonnet::currentdump($courseid,$sdom,$sname);
1342: if ((scalar(@tmp) > 0) && ($tmp[0] =~ /^error:/)) {
1343: &Apache::lonnet::logthis('error getting data for '.
1344: $sname.':'.$sdom.' in course '.$courseid.
1345: ':'.$tmp[0]);
1346: $returnstatus = 'error getting data';
1.79 matthew 1347: return ($returnstatus,undef);
1.57 matthew 1348: }
1349: if (scalar(@tmp) < 1) {
1350: return ('no data',undef);
1351: }
1352: my %student_data = @tmp;
1.89 matthew 1353: my @Results = &store_student_data($sname,$sdom,$courseid,\%student_data);
1354: #
1355: # Set the students update time
1.96 matthew 1356: if ($Results[0] eq 'okay') {
1.113 matthew 1357: &store_updatetime($student_id,$time_of_retrieval,$time_of_retrieval);
1.95 matthew 1358: }
1.89 matthew 1359: #
1360: return @Results;
1361: }
1362:
1.113 matthew 1363: sub store_updatetime {
1364: my ($student_id,$updatetime,$fullupdatetime)=@_;
1365: my $values = '';
1366: if (defined($updatetime)) {
1367: $values = 'updatetime='.$updatetime.' ';
1368: }
1369: if (defined($fullupdatetime)) {
1370: if ($values ne '') {
1371: $values .= ',';
1372: }
1373: $values .= 'fullupdatetime='.$fullupdatetime.' ';
1374: }
1375: return if ($values eq '');
1376: my $dbh = &Apache::lonmysql::get_dbh();
1377: my $request = 'UPDATE '.$student_table.' SET '.$values.
1378: ' WHERE student_id='.$student_id.' LIMIT 1';
1379: $dbh->do($request);
1380: }
1381:
1.89 matthew 1382: sub store_student_data {
1383: my ($sname,$sdom,$courseid,$student_data) = @_;
1384: #
1385: my $student_id = &get_student_id($sname,$sdom);
1386: my $student = $sname.':'.$sdom;
1387: #
1388: my $returnstatus = 'okay';
1.57 matthew 1389: #
1390: # Remove all of the students data from the table
1.60 matthew 1391: my $dbh = &Apache::lonmysql::get_dbh();
1392: $dbh->do('DELETE FROM '.$performance_table.' WHERE student_id='.
1393: $student_id);
1394: $dbh->do('DELETE FROM '.$parameters_table.' WHERE student_id='.
1395: $student_id);
1.57 matthew 1396: #
1397: # Store away the data
1398: #
1399: my $starttime = Time::HiRes::time;
1400: my $elapsed = 0;
1401: my $rows_stored;
1.98 matthew 1402: my $store_parameters_command = 'INSERT IGNORE INTO '.$parameters_table.
1.60 matthew 1403: ' VALUES '."\n";
1.61 matthew 1404: my $num_parameters = 0;
1.98 matthew 1405: my $store_performance_command = 'INSERT IGNORE INTO '.$performance_table.
1.60 matthew 1406: ' VALUES '."\n";
1.79 matthew 1407: return ('error',undef) if (! defined($dbh));
1.89 matthew 1408: while (my ($current_symb,$param_hash) = each(%{$student_data})) {
1.57 matthew 1409: #
1410: # make sure the symb is set up properly
1411: my $symb_id = &get_symb_id($current_symb);
1412: #
1413: # Load data into the tables
1.63 matthew 1414: while (my ($parameter,$value) = each(%$param_hash)) {
1.57 matthew 1415: my $newstring;
1.63 matthew 1416: if ($parameter !~ /(timestamp|resource\.(.*)\.(solved|tries|awarded|award|awarddetail|previous))/) {
1.57 matthew 1417: $newstring = "('".join("','",
1418: $symb_id,$student_id,
1.69 matthew 1419: $parameter)."',".
1420: $dbh->quote($value)."),\n";
1.61 matthew 1421: $num_parameters ++;
1.57 matthew 1422: if ($newstring !~ /''/) {
1423: $store_parameters_command .= $newstring;
1424: $rows_stored++;
1425: }
1426: }
1427: next if ($parameter !~ /^resource\.(.*)\.solved$/);
1428: #
1429: my $part = $1;
1430: my $part_id = &get_part_id($part);
1431: next if (!defined($part_id));
1432: my $solved = $value;
1433: my $tries = $param_hash->{'resource.'.$part.'.tries'};
1434: my $awarded = $param_hash->{'resource.'.$part.'.awarded'};
1435: my $award = $param_hash->{'resource.'.$part.'.award'};
1436: my $awarddetail = $param_hash->{'resource.'.$part.'.awarddetail'};
1437: my $timestamp = $param_hash->{'timestamp'};
1.60 matthew 1438: #
1.74 matthew 1439: $solved = '' if (! defined($solved));
1.57 matthew 1440: $tries = '' if (! defined($tries));
1441: $awarded = '' if (! defined($awarded));
1442: $award = '' if (! defined($award));
1443: $awarddetail = '' if (! defined($awarddetail));
1.73 matthew 1444: $newstring = "('".join("','",$symb_id,$student_id,$part_id,$part,
1.57 matthew 1445: $solved,$tries,$awarded,$award,
1.63 matthew 1446: $awarddetail,$timestamp)."'),\n";
1.57 matthew 1447: $store_performance_command .= $newstring;
1448: $rows_stored++;
1449: }
1450: }
1451: chop $store_parameters_command;
1.60 matthew 1452: chop $store_parameters_command;
1453: chop $store_performance_command;
1.57 matthew 1454: chop $store_performance_command;
1455: my $start = Time::HiRes::time;
1.94 matthew 1456: $dbh->do($store_performance_command);
1457: if ($dbh->err()) {
1458: &Apache::lonnet::logthis(' bigass insert error:'.$dbh->errstr());
1459: &Apache::lonnet::logthis('command = '.$store_performance_command);
1460: $returnstatus = 'error: unable to insert performance into database';
1461: return ($returnstatus,$student_data);
1462: }
1.61 matthew 1463: $dbh->do($store_parameters_command) if ($num_parameters>0);
1.57 matthew 1464: if ($dbh->err()) {
1465: &Apache::lonnet::logthis(' bigass insert error:'.$dbh->errstr());
1.61 matthew 1466: &Apache::lonnet::logthis('command = '.$store_parameters_command);
1.87 matthew 1467: &Apache::lonnet::logthis('rows_stored = '.$rows_stored);
1468: &Apache::lonnet::logthis('student_id = '.$student_id);
1.57 matthew 1469: $returnstatus = 'error: unable to insert parameters into database';
1.89 matthew 1470: return ($returnstatus,$student_data);
1.57 matthew 1471: }
1472: $elapsed += Time::HiRes::time - $start;
1.89 matthew 1473: return ($returnstatus,$student_data);
1.57 matthew 1474: }
1475:
1.89 matthew 1476: ######################################
1477: ######################################
1.57 matthew 1478:
1479: =pod
1480:
1.89 matthew 1481: =item &ensure_tables_are_set_up($courseid)
1.57 matthew 1482:
1.89 matthew 1483: Checks to be sure the MySQL tables for the given class are set up.
1484: If $courseid is omitted it will be obtained from the environment.
1.57 matthew 1485:
1.89 matthew 1486: Returns nothing on success and 'error' on failure
1.57 matthew 1487:
1488: =cut
1489:
1.89 matthew 1490: ######################################
1491: ######################################
1492: sub ensure_tables_are_set_up {
1493: my ($courseid) = @_;
1.61 matthew 1494: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1495: #
1496: # Clean out package variables
1.57 matthew 1497: &setup_table_names($courseid);
1498: #
1499: # if the tables do not exist, make them
1500: my @CurrentTable = &Apache::lonmysql::tables_in_db();
1.113 matthew 1501: my ($found_symb,$found_student,$found_part,
1.89 matthew 1502: $found_performance,$found_parameters,$found_fulldump_part,
1.127 matthew 1503: $found_fulldump_response,$found_fulldump_timestamp,
1504: $found_weight);
1.57 matthew 1505: foreach (@CurrentTable) {
1506: $found_symb = 1 if ($_ eq $symb_table);
1507: $found_student = 1 if ($_ eq $student_table);
1508: $found_part = 1 if ($_ eq $part_table);
1509: $found_performance = 1 if ($_ eq $performance_table);
1510: $found_parameters = 1 if ($_ eq $parameters_table);
1.89 matthew 1511: $found_fulldump_part = 1 if ($_ eq $fulldump_part_table);
1512: $found_fulldump_response = 1 if ($_ eq $fulldump_response_table);
1513: $found_fulldump_timestamp = 1 if ($_ eq $fulldump_timestamp_table);
1.127 matthew 1514: $found_weight = 1 if ($_ eq $weight_table);
1.57 matthew 1515: }
1.127 matthew 1516: if (!$found_symb ||
1517: !$found_student || !$found_part ||
1518: !$found_performance || !$found_parameters ||
1.89 matthew 1519: !$found_fulldump_part || !$found_fulldump_response ||
1.127 matthew 1520: !$found_fulldump_timestamp || !$found_weight ) {
1.134 matthew 1521: if (&init_dbs($courseid,1)) {
1.89 matthew 1522: return 'error';
1.57 matthew 1523: }
1524: }
1.89 matthew 1525: }
1526:
1527: ################################################
1528: ################################################
1529:
1530: =pod
1531:
1532: =item &ensure_current_data()
1533:
1534: Input: $sname, $sdom, $courseid
1535:
1536: Output: $status, $data
1537:
1538: This routine ensures the data for a given student is up to date.
1.113 matthew 1539: The $student_table is queried to determine the time of the last update.
1.89 matthew 1540: If the students data is out of date, &update_student_data() is called.
1541: The return values from the call to &update_student_data() are returned.
1542:
1543: =cut
1544:
1545: ################################################
1546: ################################################
1547: sub ensure_current_data {
1548: my ($sname,$sdom,$courseid) = @_;
1549: my $status = 'okay'; # return value
1550: #
1551: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1552: &ensure_tables_are_set_up($courseid);
1.57 matthew 1553: #
1554: # Get the update time for the user
1555: my $updatetime = 0;
1.60 matthew 1556: my $modifiedtime = &Apache::lonnet::GetFileTimestamp
1557: ($sdom,$sname,$courseid.'.db',
1558: $Apache::lonnet::perlvar{'lonUsersDir'});
1.57 matthew 1559: #
1.87 matthew 1560: my $student_id = &get_student_id($sname,$sdom);
1.113 matthew 1561: my @Result = &Apache::lonmysql::get_rows($student_table,
1.87 matthew 1562: "student_id ='$student_id'");
1.57 matthew 1563: my $data = undef;
1564: if (@Result) {
1.113 matthew 1565: $updatetime = $Result[0]->[5]; # Ack! This is dumb!
1.57 matthew 1566: }
1567: if ($modifiedtime > $updatetime) {
1568: ($status,$data) = &update_student_data($sname,$sdom,$courseid);
1569: }
1570: return ($status,$data);
1571: }
1572:
1573: ################################################
1574: ################################################
1575:
1576: =pod
1577:
1.89 matthew 1578: =item &ensure_current_full_data($sname,$sdom,$courseid)
1579:
1580: Input: $sname, $sdom, $courseid
1581:
1582: Output: $status
1583:
1584: This routine ensures the fulldata (the data from a lonnet::dump, not a
1585: lonnet::currentdump) for a given student is up to date.
1.113 matthew 1586: The $student_table is queried to determine the time of the last update.
1.89 matthew 1587: If the students fulldata is out of date, &update_full_student_data() is
1588: called.
1589:
1590: The return value from the call to &update_full_student_data() is returned.
1591:
1592: =cut
1593:
1594: ################################################
1595: ################################################
1596: sub ensure_current_full_data {
1597: my ($sname,$sdom,$courseid) = @_;
1598: my $status = 'okay'; # return value
1599: #
1600: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1601: &ensure_tables_are_set_up($courseid);
1602: #
1603: # Get the update time for the user
1604: my $modifiedtime = &Apache::lonnet::GetFileTimestamp
1605: ($sdom,$sname,$courseid.'.db',
1606: $Apache::lonnet::perlvar{'lonUsersDir'});
1607: #
1608: my $student_id = &get_student_id($sname,$sdom);
1.113 matthew 1609: my @Result = &Apache::lonmysql::get_rows($student_table,
1.89 matthew 1610: "student_id ='$student_id'");
1611: my $updatetime;
1612: if (@Result && ref($Result[0]) eq 'ARRAY') {
1.113 matthew 1613: $updatetime = $Result[0]->[6];
1.89 matthew 1614: }
1615: if (! defined($updatetime) || $modifiedtime > $updatetime) {
1616: $status = &update_full_student_data($sname,$sdom,$courseid);
1617: }
1618: return $status;
1619: }
1620:
1621: ################################################
1622: ################################################
1623:
1624: =pod
1625:
1.57 matthew 1626: =item &get_student_data_from_performance_cache()
1627:
1628: Input: $sname, $sdom, $symb, $courseid
1629:
1630: Output: hash reference containing the data for the given student.
1631: If $symb is undef, all the students data is returned.
1632:
1633: This routine is the heart of the local caching system. See the description
1634: of $performance_table, $symb_table, $student_table, and $part_table. The
1635: main task is building the MySQL request. The tables appear in the request
1636: in the order in which they should be parsed by MySQL. When searching
1637: on a student the $student_table is used to locate the 'student_id'. All
1638: rows in $performance_table which have a matching 'student_id' are returned,
1639: with data from $part_table and $symb_table which match the entries in
1640: $performance_table, 'part_id' and 'symb_id'. When searching on a symb,
1641: the $symb_table is processed first, with matching rows grabbed from
1642: $performance_table and filled in from $part_table and $student_table in
1643: that order.
1644:
1645: Running 'EXPLAIN ' on the 'SELECT' statements generated can be quite
1646: interesting, especially if you play with the order the tables are listed.
1647:
1648: =cut
1649:
1650: ################################################
1651: ################################################
1652: sub get_student_data_from_performance_cache {
1653: my ($sname,$sdom,$symb,$courseid)=@_;
1654: my $student = $sname.':'.$sdom if (defined($sname) && defined($sdom));
1.61 matthew 1655: &setup_table_names($courseid);
1.57 matthew 1656: #
1657: # Return hash
1658: my $studentdata;
1659: #
1660: my $dbh = &Apache::lonmysql::get_dbh();
1661: my $request = "SELECT ".
1.73 matthew 1662: "d.symb,a.part,a.solved,a.tries,a.awarded,a.award,a.awarddetail,".
1.63 matthew 1663: "a.timestamp ";
1.57 matthew 1664: if (defined($student)) {
1665: $request .= "FROM $student_table AS b ".
1666: "LEFT JOIN $performance_table AS a ON b.student_id=a.student_id ".
1.73 matthew 1667: # "LEFT JOIN $part_table AS c ON c.part_id = a.part_id ".
1.57 matthew 1668: "LEFT JOIN $symb_table AS d ON d.symb_id = a.symb_id ".
1669: "WHERE student='$student'";
1670: if (defined($symb) && $symb ne '') {
1.67 matthew 1671: $request .= " AND d.symb=".$dbh->quote($symb);
1.57 matthew 1672: }
1673: } elsif (defined($symb) && $symb ne '') {
1674: $request .= "FROM $symb_table as d ".
1675: "LEFT JOIN $performance_table AS a ON d.symb_id=a.symb_id ".
1.73 matthew 1676: # "LEFT JOIN $part_table AS c ON c.part_id = a.part_id ".
1.57 matthew 1677: "LEFT JOIN $student_table AS b ON b.student_id = a.student_id ".
1678: "WHERE symb='".$dbh->quote($symb)."'";
1679: }
1680: my $starttime = Time::HiRes::time;
1681: my $rows_retrieved = 0;
1682: my $sth = $dbh->prepare($request);
1683: $sth->execute();
1684: if ($sth->err()) {
1685: &Apache::lonnet::logthis("Unable to execute MySQL request:");
1686: &Apache::lonnet::logthis("\n".$request."\n");
1687: &Apache::lonnet::logthis("error is:".$sth->errstr());
1688: return undef;
1689: }
1690: foreach my $row (@{$sth->fetchall_arrayref}) {
1691: $rows_retrieved++;
1.63 matthew 1692: my ($symb,$part,$solved,$tries,$awarded,$award,$awarddetail,$time) =
1.57 matthew 1693: (@$row);
1694: my $base = 'resource.'.$part;
1695: $studentdata->{$symb}->{$base.'.solved'} = $solved;
1696: $studentdata->{$symb}->{$base.'.tries'} = $tries;
1697: $studentdata->{$symb}->{$base.'.awarded'} = $awarded;
1698: $studentdata->{$symb}->{$base.'.award'} = $award;
1699: $studentdata->{$symb}->{$base.'.awarddetail'} = $awarddetail;
1700: $studentdata->{$symb}->{'timestamp'} = $time if (defined($time) && $time ne '');
1.67 matthew 1701: }
1.97 matthew 1702: ## Get misc parameters
1703: $request = 'SELECT c.symb,a.parameter,a.value '.
1704: "FROM $student_table AS b ".
1705: "LEFT JOIN $parameters_table AS a ON b.student_id=a.student_id ".
1706: "LEFT JOIN $symb_table AS c ON c.symb_id = a.symb_id ".
1707: "WHERE student='$student'";
1708: if (defined($symb) && $symb ne '') {
1709: $request .= " AND c.symb=".$dbh->quote($symb);
1710: }
1711: $sth = $dbh->prepare($request);
1712: $sth->execute();
1713: if ($sth->err()) {
1714: &Apache::lonnet::logthis("Unable to execute MySQL request:");
1715: &Apache::lonnet::logthis("\n".$request."\n");
1716: &Apache::lonnet::logthis("error is:".$sth->errstr());
1717: if (defined($symb) && $symb ne '') {
1718: $studentdata = $studentdata->{$symb};
1719: }
1720: return $studentdata;
1721: }
1722: #
1723: foreach my $row (@{$sth->fetchall_arrayref}) {
1724: $rows_retrieved++;
1725: my ($symb,$parameter,$value) = (@$row);
1726: $studentdata->{$symb}->{$parameter} = $value;
1727: }
1728: #
1.67 matthew 1729: if (defined($symb) && $symb ne '') {
1730: $studentdata = $studentdata->{$symb};
1.57 matthew 1731: }
1732: return $studentdata;
1733: }
1734:
1735: ################################################
1736: ################################################
1737:
1738: =pod
1739:
1740: =item &get_current_state()
1741:
1742: Input: $sname,$sdom,$symb,$courseid
1743:
1744: Output: Described below
1.46 matthew 1745:
1.47 matthew 1746: Retrieve the current status of a students performance. $sname and
1.46 matthew 1747: $sdom are the only required parameters. If $symb is undef the results
1.47 matthew 1748: of an &Apache::lonnet::currentdump() will be returned.
1.46 matthew 1749: If $courseid is undef it will be retrieved from the environment.
1750:
1751: The return structure is based on &Apache::lonnet::currentdump. If
1752: $symb is unspecified, all the students data is returned in a hash of
1753: the form:
1754: (
1755: symb1 => { param1 => value1, param2 => value2 ... },
1756: symb2 => { param1 => value1, param2 => value2 ... },
1757: )
1758:
1759: If $symb is specified, a hash of
1760: (
1761: param1 => value1,
1762: param2 => value2,
1763: )
1764: is returned.
1765:
1.57 matthew 1766: If no data is found for $symb, or if the student has no performance data,
1.46 matthew 1767: an empty list is returned.
1768:
1769: =cut
1770:
1771: ################################################
1772: ################################################
1773: sub get_current_state {
1.47 matthew 1774: my ($sname,$sdom,$symb,$courseid,$forcedownload)=@_;
1775: #
1.46 matthew 1776: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1.47 matthew 1777: #
1.61 matthew 1778: return () if (! defined($sname) || ! defined($sdom));
1779: #
1.57 matthew 1780: my ($status,$data) = &ensure_current_data($sname,$sdom,$courseid);
1.77 matthew 1781: # &Apache::lonnet::logthis
1782: # ('sname = '.$sname.
1783: # ' domain = '.$sdom.
1784: # ' status = '.$status.
1785: # ' data is '.(defined($data)?'defined':'undefined'));
1.73 matthew 1786: # while (my ($symb,$hash) = each(%$data)) {
1787: # &Apache::lonnet::logthis($symb."\n----------------------------------");
1788: # while (my ($key,$value) = each (%$hash)) {
1789: # &Apache::lonnet::logthis(" ".$key." = ".$value);
1790: # }
1791: # }
1.47 matthew 1792: #
1.79 matthew 1793: if (defined($data) && defined($symb) && ref($data->{$symb})) {
1794: return %{$data->{$symb}};
1795: } elsif (defined($data) && ! defined($symb) && ref($data)) {
1796: return %$data;
1797: }
1798: if ($status eq 'no data') {
1.57 matthew 1799: return ();
1800: } else {
1801: if ($status ne 'okay' && $status ne '') {
1802: &Apache::lonnet::logthis('status = '.$status);
1.47 matthew 1803: return ();
1804: }
1.57 matthew 1805: my $returnhash = &get_student_data_from_performance_cache($sname,$sdom,
1806: $symb,$courseid);
1807: return %$returnhash if (defined($returnhash));
1.46 matthew 1808: }
1.57 matthew 1809: return ();
1.61 matthew 1810: }
1811:
1812: ################################################
1813: ################################################
1814:
1815: =pod
1816:
1817: =item &get_problem_statistics()
1818:
1819: Gather data on a given problem. The database is assumed to be
1820: populated and all local caching variables are assumed to be set
1821: properly. This means you need to call &ensure_current_data for
1822: the students you are concerned with prior to calling this routine.
1823:
1.124 matthew 1824: Inputs: $Sections, $status, $symb, $part, $courseid, $starttime, $endtime
1.61 matthew 1825:
1.64 matthew 1826: =over 4
1827:
1.124 matthew 1828: =item $Sections Array ref containing section names for students.
1829: 'all' is allowed to be the first (and only) item in the array.
1830:
1831: =item $status String describing the status of students
1.64 matthew 1832:
1833: =item $symb is the symb for the problem.
1834:
1835: =item $part is the part id you need statistics for
1836:
1837: =item $courseid is the course id, of course!
1838:
1.122 matthew 1839: =item $starttime and $endtime are unix times which to use to limit
1840: the statistical data.
1841:
1.64 matthew 1842: =back
1843:
1.66 matthew 1844: Outputs: See the code for up to date information. A hash reference is
1845: returned. The hash has the following keys defined:
1.64 matthew 1846:
1847: =over 4
1848:
1.66 matthew 1849: =item num_students The number of students attempting the problem
1850:
1851: =item tries The total number of tries for the students
1852:
1853: =item max_tries The maximum number of tries taken
1854:
1855: =item mean_tries The average number of tries
1856:
1857: =item num_solved The number of students able to solve the problem
1858:
1859: =item num_override The number of students whose answer is 'correct_by_override'
1860:
1861: =item deg_of_diff The degree of difficulty of the problem
1862:
1863: =item std_tries The standard deviation of the number of tries
1864:
1865: =item skew_tries The skew of the number of tries
1.64 matthew 1866:
1.66 matthew 1867: =item per_wrong The number of students attempting the problem who were not
1868: able to answer it correctly.
1.64 matthew 1869:
1870: =back
1871:
1.61 matthew 1872: =cut
1873:
1874: ################################################
1875: ################################################
1876: sub get_problem_statistics {
1.122 matthew 1877: my ($Sections,$status,$symb,$part,$courseid,$starttime,$endtime) = @_;
1.61 matthew 1878: return if (! defined($symb) || ! defined($part));
1879: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1880: #
1.100 matthew 1881: &setup_table_names($courseid);
1.61 matthew 1882: my $symb_id = &get_symb_id($symb);
1883: my $part_id = &get_part_id($part);
1884: my $stats_table = $courseid.'_problem_stats';
1885: #
1886: my $dbh = &Apache::lonmysql::get_dbh();
1887: return undef if (! defined($dbh));
1888: #
1.123 matthew 1889: # Clean out the table
1.61 matthew 1890: $dbh->do('DROP TABLE '.$stats_table); # May return an error
1891: my $request =
1.115 matthew 1892: 'CREATE TEMPORARY TABLE '.$stats_table.' '.
1893: 'SELECT a.student_id,a.solved,a.award,a.awarded,a.tries '.
1894: 'FROM '.$performance_table.' AS a ';
1.123 matthew 1895: #
1896: # See if we need to include some requirements on the students
1.115 matthew 1897: if ((defined($Sections) && lc($Sections->[0]) ne 'all') ||
1898: (defined($status) && lc($status) ne 'any')) {
1899: $request .= 'NATURAL LEFT JOIN '.$student_table.' AS b ';
1900: }
1901: $request .= ' WHERE a.symb_id='.$symb_id.' AND a.part_id='.$part_id;
1.123 matthew 1902: #
1903: # Limit the students included to those specified
1.115 matthew 1904: if (defined($Sections) && lc($Sections->[0]) ne 'all') {
1.64 matthew 1905: $request .= ' AND ('.
1.115 matthew 1906: join(' OR ', map { "b.section='".$_."'" } @$Sections
1.64 matthew 1907: ).')';
1908: }
1.115 matthew 1909: if (defined($status) && lc($status) ne 'any') {
1910: $request .= " AND b.status='".$status."'";
1.122 matthew 1911: }
1912: #
1.123 matthew 1913: # Limit by starttime and endtime
1.122 matthew 1914: my $time_requirements = undef;
1915: if (defined($starttime)) {
1916: $time_requirements .= 'a.timestamp>='.$starttime;
1917: if (defined($endtime)) {
1918: $time_requirements .= ' AND a.timestamp<='.$endtime;
1919: }
1920: } elsif (defined($endtime)) {
1921: $time_requirements .= 'a.timestamp<='.$endtime;
1922: }
1923: if (defined($time_requirements)) {
1924: $request .= ' AND '.$time_requirements;
1.115 matthew 1925: }
1.123 matthew 1926: #
1927: # Finally, execute the request to create the temporary table
1.61 matthew 1928: $dbh->do($request);
1.123 matthew 1929: #
1930: # Collect the first suite of statistics
1.128 matthew 1931: $request = 'SELECT COUNT(*),SUM(tries),'.
1932: 'AVG(tries),STD(tries) '.
1.109 matthew 1933: 'FROM '.$stats_table;
1.128 matthew 1934: my ($num,$tries,$mean,$STD) = &execute_SQL_request
1.109 matthew 1935: ($dbh,$request);
1.128 matthew 1936: #
1937: $request = 'SELECT MAX(tries),MIN(tries) FROM '.$stats_table.
1938: ' WHERE awarded>0';
1939: if (defined($time_requirements)) {
1940: $request .= ' AND '.$time_requirements;
1941: }
1942: my ($max,$min) = &execute_SQL_request($dbh,$request);
1943: #
1.109 matthew 1944: $request = 'SELECT SUM(awarded) FROM '.$stats_table;
1.128 matthew 1945: if (defined($time_requirements)) {
1946: $request .= ' AND '.$time_requirements;
1947: }
1.109 matthew 1948: my ($Solved) = &execute_SQL_request($dbh,$request);
1.128 matthew 1949: #
1.109 matthew 1950: $request = 'SELECT SUM(awarded) FROM '.$stats_table.
1951: " WHERE solved='correct_by_override'";
1.128 matthew 1952: if (defined($time_requirements)) {
1953: $request .= ' AND '.$time_requirements;
1954: }
1.109 matthew 1955: my ($solved) = &execute_SQL_request($dbh,$request);
1956: #
1.133 matthew 1957: $Solved -= $solved;
1958: #
1.61 matthew 1959: $num = 0 if (! defined($num));
1960: $tries = 0 if (! defined($tries));
1.128 matthew 1961: $max = 0 if (! defined($max));
1962: $min = 0 if (! defined($min));
1.61 matthew 1963: $STD = 0 if (! defined($STD));
1.133 matthew 1964: $Solved = 0 if (! defined($Solved) || $Solved < 0);
1.61 matthew 1965: $solved = 0 if (! defined($solved));
1966: #
1.123 matthew 1967: # Compute the more complicated statistics
1.61 matthew 1968: my $DegOfDiff = 'nan';
1.66 matthew 1969: $DegOfDiff = 1-($Solved)/$tries if ($tries>0);
1.123 matthew 1970: #
1.61 matthew 1971: my $SKEW = 'nan';
1.66 matthew 1972: my $wrongpercent = 0;
1.128 matthew 1973: my $numwrong = 'nan';
1.61 matthew 1974: if ($num > 0) {
1975: ($SKEW) = &execute_SQL_request($dbh,'SELECT SQRT(SUM('.
1976: 'POWER(tries - '.$STD.',3)'.
1977: '))/'.$num.' FROM '.$stats_table);
1.128 matthew 1978: $numwrong = $num-$Solved;
1979: $wrongpercent=int(10*100*$numwrong/$num)/10;
1.61 matthew 1980: }
1981: #
1.123 matthew 1982: # Drop the temporary table
1983: $dbh->do('DROP TABLE '.$stats_table); # May return an error
1.81 matthew 1984: #
1985: # Return result
1.66 matthew 1986: return { num_students => $num,
1987: tries => $tries,
1.128 matthew 1988: max_tries => $max,
1989: min_tries => $min,
1.66 matthew 1990: mean_tries => $mean,
1991: std_tries => $STD,
1992: skew_tries => $SKEW,
1993: num_solved => $Solved,
1994: num_override => $solved,
1.128 matthew 1995: num_wrong => $numwrong,
1.66 matthew 1996: per_wrong => $wrongpercent,
1.81 matthew 1997: deg_of_diff => $DegOfDiff };
1.61 matthew 1998: }
1999:
1.127 matthew 2000: ##
2001: ## This is a helper for get_statistics
1.61 matthew 2002: sub execute_SQL_request {
2003: my ($dbh,$request)=@_;
2004: # &Apache::lonnet::logthis($request);
2005: my $sth = $dbh->prepare($request);
2006: $sth->execute();
2007: my $row = $sth->fetchrow_arrayref();
2008: if (ref($row) eq 'ARRAY' && scalar(@$row)>0) {
2009: return @$row;
2010: }
2011: return ();
2012: }
1.123 matthew 2013:
1.127 matthew 2014: ######################################################
2015: ######################################################
2016:
2017: =pod
2018:
2019: =item &populate_weight_table
2020:
2021: =cut
2022:
2023: ######################################################
2024: ######################################################
2025: sub populate_weight_table {
2026: my ($courseid) = @_;
2027: if (! defined($courseid)) {
2028: $courseid = $ENV{'request.course.id'};
2029: }
2030: #
2031: &setup_table_names($courseid);
2032: my ($top,$sequences,$assessments) = get_sequence_assessment_data();
2033: if (! defined($top) || ! ref($top)) {
2034: # There has been an error, better report it
2035: &Apache::lonnet::logthis('top is undefined');
2036: return;
2037: }
2038: # Since we use lonnet::EXT to retrieve problem weights,
2039: # to ensure current data we must clear the caches out.
2040: &Apache::lonnet::clear_EXT_cache_status();
2041: my $dbh = &Apache::lonmysql::get_dbh();
2042: my $request = 'INSERT IGNORE INTO '.$weight_table.
2043: "(symb_id,part_id,weight) VALUES ";
2044: my $weight;
2045: foreach my $res (@$assessments) {
2046: my $symb_id = &get_symb_id($res->{'symb'});
2047: foreach my $part (@{$res->{'parts'}}) {
2048: my $part_id = &get_part_id($part);
2049: $weight = &Apache::lonnet::EXT('resource.'.$part.'.weight',
2050: $res->{'symb'},
2051: undef,undef,undef);
2052: if (!defined($weight) || ($weight eq '')) {
2053: $weight=1;
2054: }
2055: $request .= "('".$symb_id."','".$part_id."','".$weight."'),";
2056: }
2057: }
2058: $request =~ s/(,)$//;
2059: # &Apache::lonnet::logthis('request = '.$/.$request);
2060: $dbh->do($request);
2061: if ($dbh->err()) {
2062: &Apache::lonnet::logthis("error ".$dbh->errstr().
2063: " occured executing \n".
2064: $request);
2065: }
2066: return;
2067: }
2068:
2069: ##########################################################
2070: ##########################################################
1.61 matthew 2071:
1.127 matthew 2072: =pod
2073:
1.129 matthew 2074: =item &limit_by_start_end_times
2075:
2076: Build SQL WHERE condition which limits the data collected by the start
2077: and end times provided
2078:
2079: Inputs: $starttime, $endtime, $table
2080:
2081: Returns: $time_limits
2082:
2083: =cut
2084:
2085: ##########################################################
2086: ##########################################################
2087: sub limit_by_start_end_time {
2088: my ($starttime,$endtime,$table) = @_;
2089: my $time_requirements = undef;
2090: if (defined($starttime)) {
2091: $time_requirements .= $table.".timestamp>='".$starttime."'";
2092: if (defined($endtime)) {
2093: $time_requirements .= " AND ".$table.".timestamp<='".$endtime."'";
2094: }
2095: } elsif (defined($endtime)) {
2096: $time_requirements .= $table.".timestamp<='".$endtime."'";
2097: }
2098: return $time_requirements;
2099: }
2100:
2101: ##########################################################
2102: ##########################################################
2103:
2104: =pod
2105:
1.127 matthew 2106: =item &limit_by_section_and_status
2107:
2108: Build SQL WHERE condition which limits the data collected by section and
2109: student status.
2110:
2111: Inputs: $Sections (array ref)
2112: $enrollment (string: 'any', 'expired', 'active')
2113: $tablename The name of the table that holds the student data
2114:
2115: Returns: $student_requirements,$enrollment_requirements
2116:
2117: =cut
2118:
2119: ##########################################################
2120: ##########################################################
2121: sub limit_by_section_and_status {
2122: my ($Sections,$enrollment,$tablename) = @_;
2123: my $student_requirements = undef;
2124: if ( (defined($Sections) && $Sections->[0] ne 'all')) {
2125: $student_requirements = '('.
2126: join(' OR ', map { $tablename.".section='".$_."'" } @$Sections
2127: ).')';
2128: }
2129: #
2130: my $enrollment_requirements=undef;
2131: if (defined($enrollment) && $enrollment ne 'Any') {
2132: $enrollment_requirements = $tablename.".status='".$enrollment."'";
2133: }
2134: return ($student_requirements,$enrollment_requirements);
2135: }
2136:
2137: ######################################################
2138: ######################################################
2139:
2140: =pod
2141:
2142: =item rank_students_by_scores_on_resources
2143:
2144: Inputs:
2145: $resources: array ref of hash ref. Each hash ref needs key 'symb'.
2146: $Sections: array ref of sections to include,
2147: $enrollment: string,
2148: $courseid (may be omitted)
2149:
2150: Returns; An array of arrays. The sub arrays contain a student name and
2151: their score on the resources.
2152:
2153: =cut
2154:
2155: ######################################################
2156: ######################################################
2157: sub RNK_student { return 0; };
2158: sub RNK_score { return 1; };
2159:
2160: sub rank_students_by_scores_on_resources {
1.130 matthew 2161: my ($resources,$Sections,$enrollment,$courseid,$starttime,$endtime) = @_;
1.127 matthew 2162: return if (! defined($resources) || ! ref($resources) eq 'ARRAY');
2163: if (! defined($courseid)) {
2164: $courseid = $ENV{'request.course.id'};
2165: }
2166: #
2167: &setup_table_names($courseid);
2168: my $dbh = &Apache::lonmysql::get_dbh();
2169: my ($section_limits,$enrollment_limits)=
2170: &limit_by_section_and_status($Sections,$enrollment,'b');
2171: my $symb_limits = '('.join(' OR ',map {'a.symb_id='.&get_symb_id($_);
2172: } @$resources
2173: ).')';
1.130 matthew 2174: my $time_limits = &limit_by_start_end_time($starttime,$endtime,'a');
1.127 matthew 2175: my $request = 'SELECT b.student,SUM(a.awarded*w.weight) AS score FROM '.
2176: $performance_table.' AS a '.
2177: 'NATURAL LEFT JOIN '.$weight_table.' AS w '.
2178: 'LEFT JOIN '.$student_table.' AS b ON a.student_id=b.student_id '.
2179: 'WHERE ';
2180: if (defined($section_limits)) {
2181: $request .= $section_limits.' AND ';
2182: }
2183: if (defined($enrollment_limits)) {
2184: $request .= $enrollment_limits.' AND ';
2185: }
1.130 matthew 2186: if (defined($time_limits)) {
2187: $request .= $time_limits.' AND ';
2188: }
1.127 matthew 2189: if ($symb_limits ne '()') {
2190: $request .= $symb_limits.' AND ';
2191: }
2192: $request =~ s/( AND )$//; # Remove extra conjunction
2193: $request =~ s/( WHERE )$//; # In case there were no limits placed on it
2194: $request .= ' GROUP BY a.student_id ORDER BY score';
2195: #&Apache::lonnet::logthis('request = '.$/.$request);
2196: my $sth = $dbh->prepare($request);
2197: $sth->execute();
2198: my $rows = $sth->fetchall_arrayref();
2199: return ($rows);
2200: }
2201:
2202: ########################################################
2203: ########################################################
2204:
2205: =pod
2206:
2207: =item &get_sum_of_scores
2208:
2209: Inputs: $resource (hash ref, needs {'symb'} key),
2210: $part, (the part id),
2211: $students (array ref, contents of array are scalars holding 'sname:sdom'),
2212: $courseid
2213:
2214: Returns: the sum of the score on the problem part over the students and the
2215: maximum possible value for the sum (taken from the weight table).
2216:
2217: =cut
2218:
2219: ########################################################
2220: ########################################################
2221: sub get_sum_of_scores {
1.130 matthew 2222: my ($resource,$part,$students,$courseid,$starttime,$endtime) = @_;
1.127 matthew 2223: if (! defined($courseid)) {
2224: $courseid = $ENV{'request.course.id'};
2225: }
1.138 matthew 2226: if (defined($students) &&
2227: ((@$students == 0) ||
2228: (@$students == 1 && (! defined($students->[0]) ||
2229: $students->[0] eq ''))
2230: )
2231: ){
2232: undef($students);
2233: }
1.127 matthew 2234: #
2235: &setup_table_names($courseid);
2236: my $dbh = &Apache::lonmysql::get_dbh();
1.130 matthew 2237: my $time_limits = &limit_by_start_end_time($starttime,$endtime,'a');
1.127 matthew 2238: my $request = 'SELECT SUM(a.awarded*w.weight),SUM(w.weight) FROM '.
2239: $performance_table.' AS a '.
2240: 'NATURAL LEFT JOIN '.$weight_table.' AS w ';
2241: $request .= 'WHERE a.symb_id='.&get_symb_id($resource->{'symb'}).
2242: ' AND a.part_id='.&get_part_id($part);
1.130 matthew 2243: if (defined($time_limits)) {
2244: $request .= ' AND '.$time_limits;
2245: }
1.127 matthew 2246: if (defined($students)) {
2247: $request .= ' AND ('.
2248: join(' OR ',map {'a.student_id='.&get_student_id(split(':',$_));
2249: } @$students).
2250: ')';
2251: }
2252: my $sth = $dbh->prepare($request);
2253: $sth->execute();
2254: my $rows = $sth->fetchrow_arrayref();
2255: if ($dbh->err) {
1.138 matthew 2256: &Apache::lonnet::logthis('error 1 = '.$dbh->errstr());
2257: &Apache::lonnet::logthis('prepared then executed, fetchrow_arrayrefed'.
2258: $/.$request);
1.127 matthew 2259: return (undef,undef);
2260: }
2261: return ($rows->[0],$rows->[1]);
2262: }
2263:
1.129 matthew 2264: ########################################################
2265: ########################################################
2266:
2267: =pod
2268:
2269: =item &score_stats
2270:
2271: Inputs: $Sections, $enrollment, $symbs, $starttime,
2272: $endtime, $courseid
2273:
2274: $Sections, $enrollment, $starttime, $endtime, and $courseid are the same as
2275: elsewhere in this module.
2276: $symbs is an array ref of symbs
2277:
2278: Returns: minimum, maximum, mean, s.d., number of students, and maximum
2279: possible of student scores on the given resources
2280:
2281: =cut
2282:
2283: ########################################################
2284: ########################################################
2285: sub score_stats {
2286: my ($Sections,$enrollment,$symbs,$starttime,$endtime,$courseid)=@_;
2287: if (! defined($courseid)) {
2288: $courseid = $ENV{'request.course.id'};
2289: }
2290: #
2291: &setup_table_names($courseid);
2292: my $dbh = &Apache::lonmysql::get_dbh();
2293: #
2294: my ($section_limits,$enrollment_limits)=
2295: &limit_by_section_and_status($Sections,$enrollment,'b');
2296: my $time_limits = &limit_by_start_end_time($starttime,$endtime,'a');
2297: my @Symbids = map { &get_symb_id($_); } @{$symbs};
2298: #
2299: my $stats_table = $courseid.'_problem_stats';
2300: my $symb_restriction = join(' OR ',map {'a.symb_id='.$_;} @Symbids);
2301: my $request = 'DROP TABLE '.$stats_table;
2302: $dbh->do($request);
2303: $request =
2304: 'CREATE TEMPORARY TABLE '.$stats_table.' '.
2305: 'SELECT a.student_id,'.
2306: 'SUM(a.awarded*w.weight) AS score FROM '.
2307: $performance_table.' AS a '.
2308: 'NATURAL LEFT JOIN '.$weight_table.' AS w '.
2309: 'LEFT JOIN '.$student_table.' AS b ON a.student_id=b.student_id '.
2310: 'WHERE ('.$symb_restriction.')';
2311: if ($time_limits) {
2312: $request .= ' AND '.$time_limits;
2313: }
2314: if ($section_limits) {
2315: $request .= ' AND '.$section_limits;
2316: }
2317: if ($enrollment_limits) {
2318: $request .= ' AND '.$enrollment_limits;
2319: }
2320: $request .= ' GROUP BY a.student_id';
2321: # &Apache::lonnet::logthis('request = '.$/.$request);
2322: my $sth = $dbh->prepare($request);
2323: $sth->execute();
2324: $request =
2325: 'SELECT AVG(score),STD(score),MAX(score),MIN(score),COUNT(score) '.
2326: 'FROM '.$stats_table;
2327: my ($ave,$std,$max,$min,$count) = &execute_SQL_request($dbh,$request);
2328: # &Apache::lonnet::logthis('request = '.$/.$request);
2329:
2330: $request = 'SELECT SUM(weight) FROM '.$weight_table.
2331: ' WHERE ('.$symb_restriction.')';
2332: my ($max_possible) = &execute_SQL_request($dbh,$request);
2333: # &Apache::lonnet::logthis('request = '.$/.$request);
2334: return($min,$max,$ave,$std,$count,$max_possible);
2335: }
2336:
2337:
2338: ########################################################
2339: ########################################################
2340:
2341: =pod
2342:
2343: =item &count_stats
2344:
2345: Inputs: $Sections, $enrollment, $symbs, $starttime,
2346: $endtime, $courseid
2347:
2348: $Sections, $enrollment, $starttime, $endtime, and $courseid are the same as
2349: elsewhere in this module.
2350: $symbs is an array ref of symbs
2351:
2352: Returns: minimum, maximum, mean, s.d., and number of students
2353: of the number of items correct on the given resources
2354:
2355: =cut
2356:
2357: ########################################################
2358: ########################################################
2359: sub count_stats {
2360: my ($Sections,$enrollment,$symbs,$starttime,$endtime,$courseid)=@_;
2361: if (! defined($courseid)) {
2362: $courseid = $ENV{'request.course.id'};
2363: }
2364: #
2365: &setup_table_names($courseid);
2366: my $dbh = &Apache::lonmysql::get_dbh();
2367: #
2368: my ($section_limits,$enrollment_limits)=
2369: &limit_by_section_and_status($Sections,$enrollment,'b');
2370: my $time_limits = &limit_by_start_end_time($starttime,$endtime,'a');
2371: my @Symbids = map { &get_symb_id($_); } @{$symbs};
2372: #
2373: my $stats_table = $courseid.'_problem_stats';
2374: my $symb_restriction = join(' OR ',map {'a.symb_id='.$_;} @Symbids);
2375: my $request = 'DROP TABLE '.$stats_table;
2376: $dbh->do($request);
2377: $request =
2378: 'CREATE TEMPORARY TABLE '.$stats_table.' '.
2379: 'SELECT a.student_id,'.
2380: 'COUNT(a.award) AS count FROM '.
2381: $performance_table.' AS a '.
2382: 'LEFT JOIN '.$student_table.' AS b ON a.student_id=b.student_id '.
2383: 'WHERE ('.$symb_restriction.')'.
2384: " AND a.award!='INCORRECT_ATTEMPTED'";
2385: if ($time_limits) {
2386: $request .= ' AND '.$time_limits;
2387: }
2388: if ($section_limits) {
2389: $request .= ' AND '.$section_limits;
2390: }
2391: if ($enrollment_limits) {
2392: $request .= ' AND '.$enrollment_limits;
2393: }
2394: $request .= ' GROUP BY a.student_id';
1.131 matthew 2395: # &Apache::lonnet::logthis('request = '.$/.$request);
1.129 matthew 2396: my $sth = $dbh->prepare($request);
2397: $sth->execute();
2398: $request =
2399: 'SELECT AVG(count),STD(count),MAX(count),MIN(count),COUNT(count) '.
2400: 'FROM '.$stats_table;
2401: my ($ave,$std,$max,$min,$count) = &execute_SQL_request($dbh,$request);
1.131 matthew 2402: # &Apache::lonnet::logthis('request = '.$/.$request);
1.129 matthew 2403: return($min,$max,$ave,$std,$count);
2404: }
1.127 matthew 2405:
2406: ######################################################
2407: ######################################################
2408:
2409: =pod
2410:
2411: =item get_student_data
2412:
2413: =cut
2414:
2415: ######################################################
2416: ######################################################
1.105 matthew 2417: sub get_student_data {
2418: my ($students,$courseid) = @_;
2419: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
2420: &setup_table_names($courseid);
2421: my $dbh = &Apache::lonmysql::get_dbh();
2422: return undef if (! defined($dbh));
2423: my $request = 'SELECT '.
2424: 'student_id, student '.
2425: 'FROM '.$student_table;
2426: if (defined($students)) {
2427: $request .= ' WHERE ('.
2428: join(' OR ', map {'student_id='.
2429: &get_student_id($_->{'username'},
2430: $_->{'domain'})
2431: } @$students
2432: ).')';
2433: }
2434: $request.= ' ORDER BY student_id';
2435: my $sth = $dbh->prepare($request);
2436: $sth->execute();
2437: if ($dbh->err) {
1.138 matthew 2438: &Apache::lonnet::logthis('error 2 = '.$dbh->errstr());
2439: &Apache::lonnet::logthis('prepared then executed '.$/.$request);
1.105 matthew 2440: return undef;
2441: }
2442: my $dataset = $sth->fetchall_arrayref();
2443: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) {
2444: return $dataset;
2445: }
2446: }
2447:
1.108 matthew 2448: sub RD_student_id { return 0; }
2449: sub RD_awarddetail { return 1; }
2450: sub RD_response_eval { return 2; }
2451: sub RD_submission { return 3; }
2452: sub RD_timestamp { return 4; }
2453: sub RD_tries { return 5; }
2454: sub RD_sname { return 6; }
2455:
2456: sub get_response_data {
1.126 matthew 2457: my ($Sections,$enrollment,$symb,$response,$courseid) = @_;
1.103 matthew 2458: return undef if (! defined($symb) ||
1.100 matthew 2459: ! defined($response));
2460: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
2461: #
2462: &setup_table_names($courseid);
2463: my $symb_id = &get_symb_id($symb);
1.137 matthew 2464: if (! defined($symb_id)) {
2465: &Apache::lonnet::logthis('Unable to find symb for '.$symb.' in '.$courseid);
2466: return undef;
2467: }
1.100 matthew 2468: my $response_id = &get_part_id($response);
1.137 matthew 2469: if (! defined($response_id)) {
2470: &Apache::lonnet::logthis('Unable to find id for '.$response.' in '.$courseid);
2471: return undef;
2472: }
1.100 matthew 2473: #
2474: my $dbh = &Apache::lonmysql::get_dbh();
2475: return undef if (! defined($dbh));
1.126 matthew 2476: #
1.127 matthew 2477: my ($student_requirements,$enrollment_requirements) =
2478: &limit_by_section_and_status($Sections,$enrollment,'d');
1.100 matthew 2479: my $request = 'SELECT '.
1.105 matthew 2480: 'a.student_id, a.awarddetail, a.response_specific_value, '.
1.108 matthew 2481: 'a.submission, b.timestamp, c.tries, d.student '.
1.100 matthew 2482: 'FROM '.$fulldump_response_table.' AS a '.
2483: 'LEFT JOIN '.$fulldump_timestamp_table.' AS b '.
2484: 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '.
2485: 'a.transaction = b.transaction '.
2486: 'LEFT JOIN '.$fulldump_part_table.' AS c '.
2487: 'ON a.symb_id=c.symb_id AND a.student_id=c.student_id AND '.
2488: 'a.part_id=c.part_id AND a.transaction = c.transaction '.
1.108 matthew 2489: 'LEFT JOIN '.$student_table.' AS d '.
2490: 'ON a.student_id=d.student_id '.
1.100 matthew 2491: 'WHERE '.
2492: 'a.symb_id='.$symb_id.' AND a.response_id='.$response_id;
1.126 matthew 2493: if (defined($student_requirements) || defined($enrollment_requirements)) {
2494: $request .= ' AND ';
2495: if (defined($student_requirements)) {
2496: $request .= $student_requirements.' AND ';
2497: }
2498: if (defined($enrollment_requirements)) {
2499: $request .= $enrollment_requirements.' AND ';
2500: }
2501: $request =~ s/( AND )$//;
1.100 matthew 2502: }
2503: $request .= ' ORDER BY b.timestamp';
1.103 matthew 2504: # &Apache::lonnet::logthis("request =\n".$request);
1.100 matthew 2505: my $sth = $dbh->prepare($request);
2506: $sth->execute();
1.105 matthew 2507: if ($dbh->err) {
1.138 matthew 2508: &Apache::lonnet::logthis('error 3 = '.$dbh->errstr());
2509: &Apache::lonnet::logthis('prepared then executed '.$/.$request);
1.105 matthew 2510: return undef;
2511: }
1.100 matthew 2512: my $dataset = $sth->fetchall_arrayref();
2513: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) {
1.117 matthew 2514: # Clear the \'s from around the submission
2515: for (my $i =0;$i<scalar(@$dataset);$i++) {
2516: $dataset->[$i]->[3] =~ s/(\'$|^\')//g;
2517: }
1.103 matthew 2518: return $dataset;
1.100 matthew 2519: }
1.118 matthew 2520: }
2521:
2522:
2523: sub RDs_awarddetail { return 3; }
2524: sub RDs_submission { return 2; }
2525: sub RDs_timestamp { return 1; }
2526: sub RDs_tries { return 0; }
1.119 matthew 2527: sub RDs_awarded { return 4; }
1.118 matthew 2528:
2529: sub get_response_data_by_student {
2530: my ($student,$symb,$response,$courseid) = @_;
2531: return undef if (! defined($symb) ||
2532: ! defined($response));
2533: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
2534: #
2535: &setup_table_names($courseid);
2536: my $symb_id = &get_symb_id($symb);
2537: my $response_id = &get_part_id($response);
2538: #
2539: my $student_id = &get_student_id($student->{'username'},
2540: $student->{'domain'});
2541: #
2542: my $dbh = &Apache::lonmysql::get_dbh();
2543: return undef if (! defined($dbh));
2544: my $request = 'SELECT '.
1.119 matthew 2545: 'c.tries, b.timestamp, a.submission, a.awarddetail, e.awarded '.
1.118 matthew 2546: 'FROM '.$fulldump_response_table.' AS a '.
2547: 'LEFT JOIN '.$fulldump_timestamp_table.' AS b '.
2548: 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '.
2549: 'a.transaction = b.transaction '.
2550: 'LEFT JOIN '.$fulldump_part_table.' AS c '.
2551: 'ON a.symb_id=c.symb_id AND a.student_id=c.student_id AND '.
2552: 'a.part_id=c.part_id AND a.transaction = c.transaction '.
2553: 'LEFT JOIN '.$student_table.' AS d '.
2554: 'ON a.student_id=d.student_id '.
1.119 matthew 2555: 'LEFT JOIN '.$performance_table.' AS e '.
2556: 'ON a.symb_id=e.symb_id AND a.part_id=e.part_id AND '.
2557: 'a.student_id=e.student_id AND c.tries=e.tries '.
1.118 matthew 2558: 'WHERE '.
2559: 'a.symb_id='.$symb_id.' AND a.response_id='.$response_id.
2560: ' AND a.student_id='.$student_id.' ORDER BY b.timestamp';
1.125 matthew 2561: # &Apache::lonnet::logthis("request =\n".$request);
1.118 matthew 2562: my $sth = $dbh->prepare($request);
2563: $sth->execute();
2564: if ($dbh->err) {
1.138 matthew 2565: &Apache::lonnet::logthis('error 4 = '.$dbh->errstr());
2566: &Apache::lonnet::logthis('prepared then executed '.$/.$request);
1.118 matthew 2567: return undef;
2568: }
2569: my $dataset = $sth->fetchall_arrayref();
2570: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) {
2571: # Clear the \'s from around the submission
2572: for (my $i =0;$i<scalar(@$dataset);$i++) {
2573: $dataset->[$i]->[2] =~ s/(\'$|^\')//g;
2574: }
2575: return $dataset;
2576: }
2577: return undef; # error occurred
1.106 matthew 2578: }
1.108 matthew 2579:
2580: sub RT_student_id { return 0; }
2581: sub RT_awarded { return 1; }
2582: sub RT_tries { return 2; }
2583: sub RT_timestamp { return 3; }
1.106 matthew 2584:
2585: sub get_response_time_data {
1.107 matthew 2586: my ($students,$symb,$part,$courseid) = @_;
1.106 matthew 2587: return undef if (! defined($symb) ||
1.107 matthew 2588: ! defined($part));
1.106 matthew 2589: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
2590: #
2591: &setup_table_names($courseid);
2592: my $symb_id = &get_symb_id($symb);
1.107 matthew 2593: my $part_id = &get_part_id($part);
1.106 matthew 2594: #
2595: my $dbh = &Apache::lonmysql::get_dbh();
2596: return undef if (! defined($dbh));
2597: my $request = 'SELECT '.
1.107 matthew 2598: 'a.student_id, a.awarded, a.tries, b.timestamp '.
2599: 'FROM '.$fulldump_part_table.' AS a '.
1.106 matthew 2600: 'NATURAL LEFT JOIN '.$fulldump_timestamp_table.' AS b '.
2601: # 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '.
2602: # 'a.transaction = b.transaction '.
2603: 'WHERE '.
1.107 matthew 2604: 'a.symb_id='.$symb_id.' AND a.part_id='.$part_id;
1.106 matthew 2605: if (defined($students)) {
2606: $request .= ' AND ('.
2607: join(' OR ', map {'a.student_id='.
2608: &get_student_id($_->{'username'},
2609: $_->{'domain'})
2610: } @$students
2611: ).')';
2612: }
2613: $request .= ' ORDER BY b.timestamp';
2614: # &Apache::lonnet::logthis("request =\n".$request);
2615: my $sth = $dbh->prepare($request);
2616: $sth->execute();
2617: if ($dbh->err) {
1.138 matthew 2618: &Apache::lonnet::logthis('error 5 = '.$dbh->errstr());
2619: &Apache::lonnet::logthis('prepared then executed '.$/.$request);
1.106 matthew 2620: return undef;
2621: }
2622: my $dataset = $sth->fetchall_arrayref();
2623: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) {
2624: return $dataset;
2625: }
2626:
1.100 matthew 2627: }
1.61 matthew 2628:
2629: ################################################
2630: ################################################
2631:
2632: =pod
2633:
1.116 matthew 2634: =item &get_student_scores($Sections,$Symbs,$enrollment,$courseid)
1.113 matthew 2635:
2636: =cut
2637:
2638: ################################################
2639: ################################################
2640: sub get_student_scores {
1.121 matthew 2641: my ($Sections,$Symbs,$enrollment,$courseid,$starttime,$endtime) = @_;
1.113 matthew 2642: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
2643: &setup_table_names($courseid);
2644: my $dbh = &Apache::lonmysql::get_dbh();
2645: return (undef) if (! defined($dbh));
2646: my $tmptable = $courseid.'_temp_'.time;
1.114 matthew 2647: #
2648: my $symb_requirements;
1.113 matthew 2649: if (defined($Symbs) && @$Symbs) {
2650: $symb_requirements = '('.
1.114 matthew 2651: join(' OR ', map{ "(a.symb_id='".&get_symb_id($_->{'symb'}).
1.121 matthew 2652: "' AND a.part_id='".&get_part_id($_->{'part'}).
2653: "')"
1.113 matthew 2654: } @$Symbs).')';
2655: }
1.114 matthew 2656: #
2657: my $student_requirements;
2658: if ( (defined($Sections) && $Sections->[0] ne 'all')) {
1.113 matthew 2659: $student_requirements = '('.
1.114 matthew 2660: join(' OR ', map { "b.section='".$_."'" } @$Sections
1.113 matthew 2661: ).')';
2662: }
1.114 matthew 2663: #
2664: my $enrollment_requirements=undef;
2665: if (defined($enrollment) && $enrollment ne 'Any') {
2666: $enrollment_requirements = "b.status='".$enrollment."'";
2667: }
1.121 matthew 2668: #
2669: my $time_requirements = undef;
2670: if (defined($starttime)) {
2671: $time_requirements .= "a.timestamp>='".$starttime."'";
2672: if (defined($endtime)) {
2673: $time_requirements .= " AND a.timestamp<='".$endtime."'";
2674: }
2675: } elsif (defined($endtime)) {
2676: $time_requirements .= "a.timestamp<='".$endtime."'";
2677: }
1.114 matthew 2678: ##
2679: ##
1.113 matthew 2680: my $request = 'CREATE TEMPORARY TABLE IF NOT EXISTS '.$tmptable.
1.114 matthew 2681: ' SELECT a.student_id,SUM(a.awarded) AS score FROM '.
2682: $performance_table.' AS a ';
2683: if (defined($student_requirements) || defined($enrollment_requirements)) {
2684: $request .= ' NATURAL LEFT JOIN '.$student_table.' AS b ';
2685: }
2686: if (defined($symb_requirements) ||
2687: defined($student_requirements) ||
2688: defined($enrollment_requirements) ) {
1.113 matthew 2689: $request .= ' WHERE ';
2690: }
1.114 matthew 2691: if (defined($symb_requirements)) {
2692: $request .= $symb_requirements.' AND ';
2693: }
2694: if (defined($student_requirements)) {
2695: $request .= $student_requirements.' AND ';
2696: }
2697: if (defined($enrollment_requirements)) {
2698: $request .= $enrollment_requirements.' AND ';
2699: }
1.121 matthew 2700: if (defined($time_requirements)) {
2701: $request .= $time_requirements.' AND ';
2702: }
2703: $request =~ s/ AND $//; # Strip of the trailing ' AND '.
1.114 matthew 2704: $request .= ' GROUP BY a.student_id';
2705: # &Apache::lonnet::logthis("request = \n".$request);
1.113 matthew 2706: my $sth = $dbh->prepare($request);
2707: $sth->execute();
2708: if ($dbh->err) {
1.138 matthew 2709: &Apache::lonnet::logthis('error 6 = '.$dbh->errstr());
2710: &Apache::lonnet::logthis('prepared then executed '.$/.$request);
1.113 matthew 2711: return undef;
2712: }
2713: $request = 'SELECT score,COUNT(*) FROM '.$tmptable.' GROUP BY score';
2714: # &Apache::lonnet::logthis("request = \n".$request);
2715: $sth = $dbh->prepare($request);
2716: $sth->execute();
2717: if ($dbh->err) {
1.138 matthew 2718: &Apache::lonnet::logthis('error 7 = '.$dbh->errstr());
2719: &Apache::lonnet::logthis('prepared then executed '.$/.$request);
1.113 matthew 2720: return undef;
2721: }
2722: my $dataset = $sth->fetchall_arrayref();
2723: return $dataset;
2724: }
2725:
2726: ################################################
2727: ################################################
2728:
2729: =pod
2730:
1.61 matthew 2731: =item &setup_table_names()
2732:
2733: input: course id
2734:
2735: output: none
2736:
2737: Cleans up the package variables for local caching.
2738:
2739: =cut
2740:
2741: ################################################
2742: ################################################
2743: sub setup_table_names {
2744: my ($courseid) = @_;
2745: if (! defined($courseid)) {
2746: $courseid = $ENV{'request.course.id'};
2747: }
2748: #
2749: if (! defined($current_course) || $current_course ne $courseid) {
2750: # Clear out variables
2751: $have_read_part_table = 0;
2752: undef(%ids_by_part);
2753: undef(%parts_by_id);
2754: $have_read_symb_table = 0;
2755: undef(%ids_by_symb);
2756: undef(%symbs_by_id);
2757: $have_read_student_table = 0;
2758: undef(%ids_by_student);
2759: undef(%students_by_id);
2760: #
2761: $current_course = $courseid;
2762: }
2763: #
2764: # Set up database names
2765: my $base_id = $courseid;
2766: $symb_table = $base_id.'_'.'symb';
2767: $part_table = $base_id.'_'.'part';
2768: $student_table = $base_id.'_'.'student';
2769: $performance_table = $base_id.'_'.'performance';
2770: $parameters_table = $base_id.'_'.'parameters';
1.89 matthew 2771: $fulldump_part_table = $base_id.'_'.'partdata';
2772: $fulldump_response_table = $base_id.'_'.'responsedata';
2773: $fulldump_timestamp_table = $base_id.'_'.'timestampdata';
1.127 matthew 2774: $weight_table = $base_id.'_'.'weight';
1.89 matthew 2775: #
2776: @Tables = (
2777: $symb_table,
2778: $part_table,
2779: $student_table,
2780: $performance_table,
2781: $parameters_table,
2782: $fulldump_part_table,
2783: $fulldump_response_table,
2784: $fulldump_timestamp_table,
1.127 matthew 2785: $weight_table,
1.89 matthew 2786: );
1.61 matthew 2787: return;
1.3 stredwic 2788: }
1.1 stredwic 2789:
1.35 matthew 2790: ################################################
2791: ################################################
2792:
2793: =pod
2794:
1.57 matthew 2795: =back
2796:
2797: =item End of Local Data Caching Subroutines
2798:
2799: =cut
2800:
2801: ################################################
2802: ################################################
2803:
1.89 matthew 2804: } # End scope of table identifiers
1.57 matthew 2805:
2806: ################################################
2807: ################################################
2808:
2809: =pod
2810:
2811: =head3 Classlist Subroutines
2812:
1.35 matthew 2813: =item &get_classlist();
2814:
2815: Retrieve the classist of a given class or of the current class. Student
2816: information is returned from the classlist.db file and, if needed,
2817: from the students environment.
2818:
2819: Optional arguments are $cid, $cdom, and $cnum (course id, course domain,
2820: and course number, respectively). Any omitted arguments will be taken
2821: from the current environment ($ENV{'request.course.id'},
2822: $ENV{'course.'.$cid.'.domain'}, and $ENV{'course.'.$cid.'.num'}).
2823:
2824: Returns a reference to a hash which contains:
2825: keys '$sname:$sdom'
1.136 raeburn 2826: values [$sdom,$sname,$end,$start,$id,$section,$fullname,$status,$type,$lockedtype]
1.54 bowersj2 2827:
2828: The constant values CL_SDOM, CL_SNAME, CL_END, etc. can be used
2829: as indices into the returned list to future-proof clients against
2830: changes in the list order.
1.35 matthew 2831:
2832: =cut
2833:
2834: ################################################
2835: ################################################
1.54 bowersj2 2836:
2837: sub CL_SDOM { return 0; }
2838: sub CL_SNAME { return 1; }
2839: sub CL_END { return 2; }
2840: sub CL_START { return 3; }
2841: sub CL_ID { return 4; }
2842: sub CL_SECTION { return 5; }
2843: sub CL_FULLNAME { return 6; }
2844: sub CL_STATUS { return 7; }
1.111 raeburn 2845: sub CL_TYPE { return 8; }
1.136 raeburn 2846: sub CL_LOCKEDTYPE { return 9; }
1.35 matthew 2847:
2848: sub get_classlist {
2849: my ($cid,$cdom,$cnum) = @_;
2850: $cid = $cid || $ENV{'request.course.id'};
2851: $cdom = $cdom || $ENV{'course.'.$cid.'.domain'};
2852: $cnum = $cnum || $ENV{'course.'.$cid.'.num'};
1.57 matthew 2853: my $now = time;
1.35 matthew 2854: #
2855: my %classlist=&Apache::lonnet::dump('classlist',$cdom,$cnum);
2856: while (my ($student,$info) = each(%classlist)) {
1.60 matthew 2857: if ($student =~ /^(con_lost|error|no_such_host)/i) {
2858: &Apache::lonnet::logthis('get_classlist error for '.$cid.':'.$student);
2859: return undef;
2860: }
1.35 matthew 2861: my ($sname,$sdom) = split(/:/,$student);
2862: my @Values = split(/:/,$info);
1.136 raeburn 2863: my ($end,$start,$id,$section,$fullname,$type,$lockedtype);
1.35 matthew 2864: if (@Values > 2) {
1.136 raeburn 2865: ($end,$start,$id,$section,$fullname,$type,$lockedtype) = @Values;
1.35 matthew 2866: } else { # We have to get the data ourselves
2867: ($end,$start) = @Values;
1.37 matthew 2868: $section = &Apache::lonnet::getsection($sdom,$sname,$cid);
1.35 matthew 2869: my %info=&Apache::lonnet::get('environment',
2870: ['firstname','middlename',
2871: 'lastname','generation','id'],
2872: $sdom, $sname);
2873: my ($tmp) = keys(%info);
2874: if ($tmp =~/^(con_lost|error|no_such_host)/i) {
2875: $fullname = 'not available';
2876: $id = 'not available';
1.38 matthew 2877: &Apache::lonnet::logthis('unable to retrieve environment '.
2878: 'for '.$sname.':'.$sdom);
1.35 matthew 2879: } else {
1.141 albertel 2880: $fullname = &Apache::lonnet::format_name(@info{qw/firstname middlename lastname generation/},'lastname');
1.35 matthew 2881: $id = $info{'id'};
2882: }
1.36 matthew 2883: # Update the classlist with this students information
2884: if ($fullname ne 'not available') {
1.141 albertel 2885: my $enrolldata = join(':',$end,$start,$id,$section,$fullname);
2886: my $reply=&Apache::lonnet::cput('classlist',
1.36 matthew 2887: {$student => $enrolldata},
2888: $cdom,$cnum);
2889: if ($reply !~ /^(ok|delayed)/) {
2890: &Apache::lonnet::logthis('Unable to update classlist for '.
2891: 'student '.$sname.':'.$sdom.
2892: ' error:'.$reply);
2893: }
2894: }
1.35 matthew 2895: }
2896: my $status='Expired';
2897: if(((!$end) || $now < $end) && ((!$start) || ($now > $start))) {
2898: $status='Active';
2899: }
2900: $classlist{$student} =
1.136 raeburn 2901: [$sdom,$sname,$end,$start,$id,$section,$fullname,$status,$type,$lockedtype];
1.35 matthew 2902: }
2903: if (wantarray()) {
2904: return (\%classlist,['domain','username','end','start','id',
1.136 raeburn 2905: 'section','fullname','status','type','lockedtype']);
1.35 matthew 2906: } else {
2907: return \%classlist;
2908: }
2909: }
2910:
1.1 stredwic 2911: # ----- END HELPER FUNCTIONS --------------------------------------------
2912:
2913: 1;
2914: __END__
1.36 matthew 2915:
1.35 matthew 2916:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/back.gif){kind=icon}
Return to loncoursedata.pm CVS log ![[TXT]](/icons/text.gif){kind=icon}
![[DIR]](/icons/dir.gif){kind=icon}
Up to [LON-CAPA] / loncom / interface