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