Annotation of loncom/interface/loncoursedata.pm, revision 1.63

1.1       stredwic    1: # The LearningOnline Network with CAPA
                      2: #
1.63    ! matthew     3: # $Id: loncoursedata.pm,v 1.62 2003/03/26 16:25:06 matthew Exp $
1.1       stredwic    4: #
                      5: # Copyright Michigan State University Board of Trustees
                      6: #
                      7: # This file is part of the LearningOnline Network with CAPA (LON-CAPA).
                      8: #
                      9: # LON-CAPA is free software; you can redistribute it and/or modify
                     10: # it under the terms of the GNU General Public License as published by
                     11: # the Free Software Foundation; either version 2 of the License, or
                     12: # (at your option) any later version.
                     13: #
                     14: # LON-CAPA is distributed in the hope that it will be useful,
                     15: # but WITHOUT ANY WARRANTY; without even the implied warranty of
                     16: # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
                     17: # GNU General Public License for more details.
                     18: #
                     19: # You should have received a copy of the GNU General Public License
                     20: # along with LON-CAPA; if not, write to the Free Software
                     21: # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
                     22: #
                     23: # /home/httpd/html/adm/gpl.txt
                     24: #
                     25: # http://www.lon-capa.org/
                     26: #
                     27: ###
                     28: 
                     29: =pod
                     30: 
                     31: =head1 NAME
                     32: 
                     33: loncoursedata
                     34: 
                     35: =head1 SYNOPSIS
                     36: 
1.22      stredwic   37: Set of functions that download and process student and course information.
1.1       stredwic   38: 
                     39: =head1 PACKAGES USED
                     40: 
                     41:  Apache::Constants qw(:common :http)
                     42:  Apache::lonnet()
1.22      stredwic   43:  Apache::lonhtmlcommon
1.1       stredwic   44:  HTML::TokeParser
                     45:  GDBM_File
                     46: 
                     47: =cut
                     48: 
                     49: package Apache::loncoursedata;
                     50: 
                     51: use strict;
                     52: use Apache::Constants qw(:common :http);
                     53: use Apache::lonnet();
1.13      stredwic   54: use Apache::lonhtmlcommon;
1.57      matthew    55: use Time::HiRes;
                     56: use Apache::lonmysql;
1.1       stredwic   57: use HTML::TokeParser;
                     58: use GDBM_File;
                     59: 
                     60: =pod
                     61: 
                     62: =head1 DOWNLOAD INFORMATION
                     63: 
1.22      stredwic   64: This section contains all the functions that get data from other servers 
                     65: and/or itself.
1.1       stredwic   66: 
                     67: =cut
                     68: 
1.50      matthew    69: ####################################################
                     70: ####################################################
1.45      matthew    71: 
                     72: =pod
                     73: 
                     74: =item &get_sequence_assessment_data()
                     75: 
                     76: AT THIS TIME THE USE OF THIS FUNCTION IS *NOT* RECOMMENDED
                     77: 
                     78: Use lonnavmaps to build a data structure describing the order and 
                     79: assessment contents of each sequence in the current course.
                     80: 
                     81: The returned structure is a hash reference. 
                     82: 
1.61      matthew    83: { title => 'title',
                     84:   symb  => 'symb',
                     85:   src   => '/s/o/u/r/c/e',
1.45      matthew    86:   type  => (container|assessment),
1.50      matthew    87:   num_assess   => 2,               # only for container
1.45      matthew    88:   parts        => [11,13,15],      # only for assessment
1.50      matthew    89:   response_ids => [12,14,16],      # only for assessment
                     90:   contents     => [........]       # only for container
1.45      matthew    91: }
                     92: 
1.50      matthew    93: $hash->{'contents'} is a reference to an array of hashes of the same structure.
                     94: 
                     95: Also returned are array references to the sequences and assessments contained
                     96: in the course.
1.49      matthew    97: 
1.45      matthew    98: 
                     99: =cut
                    100: 
1.50      matthew   101: ####################################################
                    102: ####################################################
1.45      matthew   103: sub get_sequence_assessment_data {
                    104:     my $fn=$ENV{'request.course.fn'};
                    105:     ##
                    106:     ## use navmaps
1.61      matthew   107:     my $navmap = Apache::lonnavmaps::navmap->new(Apache->request,$fn.".db",
                    108:                                                  $fn."_parms.db",1,0);
1.45      matthew   109:     if (!defined($navmap)) {
                    110:         return 'Can not open Coursemap';
                    111:     }
                    112:     my $iterator = $navmap->getIterator(undef, undef, undef, 1);
1.61      matthew   113:     my $curRes = $iterator->next(); # Top level sequence
1.45      matthew   114:     ##
                    115:     ## Prime the pump 
                    116:     ## 
                    117:     ## We are going to loop until we run out of sequences/pages to explore for
                    118:     ## resources.  This means we have to start out with something to look
                    119:     ## at.
1.52      matthew   120:     my $title = $ENV{'course.'.$ENV{'request.course.id'}.'.description'};
                    121:     my $symb  = 'top';
                    122:     my $src   = 'not applicable';
1.45      matthew   123:     #
1.49      matthew   124:     my @Sequences; 
                    125:     my @Assessments;
1.45      matthew   126:     my @Nested_Sequences = ();   # Stack of sequences, keeps track of depth
                    127:     my $top = { title    => $title,
1.52      matthew   128:                 src      => $src,
1.45      matthew   129:                 symb     => $symb,
                    130:                 type     => 'container',
                    131:                 num_assess => 0,
1.53      matthew   132:                 num_assess_parts => 0,
1.45      matthew   133:                 contents   => [], };
1.49      matthew   134:     push (@Sequences,$top);
1.45      matthew   135:     push (@Nested_Sequences, $top);
                    136:     #
                    137:     # We need to keep track of which sequences contain homework problems
                    138:     # 
1.52      matthew   139:     my $previous;
1.61      matthew   140:     $curRes = $iterator->next(); # BEGIN_MAP
1.52      matthew   141:     $curRes = $iterator->next(); # The first item in the top level map.
1.45      matthew   142:     while (scalar(@Nested_Sequences)) {
1.50      matthew   143:         $previous = $curRes;
1.45      matthew   144:         $curRes = $iterator->next();
                    145:         my $currentmap = $Nested_Sequences[-1]; # Last one on the stack
                    146:         if ($curRes == $iterator->BEGIN_MAP()) {
                    147:             # get the map itself, instead of BEGIN_MAP
1.51      matthew   148:             $title = $previous->title();
                    149:             $symb  = $previous->symb();
                    150:             $src   = $previous->src();
1.45      matthew   151:             my $newmap = { title    => $title,
                    152:                            src      => $src,
                    153:                            symb     => $symb,
                    154:                            type     => 'container',
                    155:                            num_assess => 0,
                    156:                            contents   => [],
                    157:                        };
                    158:             push (@{$currentmap->{'contents'}},$newmap); # this is permanent
1.49      matthew   159:             push (@Sequences,$newmap);
1.45      matthew   160:             push (@Nested_Sequences, $newmap); # this is a stack
                    161:             next;
                    162:         }
                    163:         if ($curRes == $iterator->END_MAP()) {
                    164:             pop(@Nested_Sequences);
                    165:             next;
                    166:         }
                    167:         next if (! ref($curRes));
1.50      matthew   168:         next if (! $curRes->is_problem());# && !$curRes->randomout);
1.45      matthew   169:         # Okay, from here on out we only deal with assessments
                    170:         $title = $curRes->title();
                    171:         $symb  = $curRes->symb();
                    172:         $src   = $curRes->src();
                    173:         my $parts = $curRes->parts();
                    174:         my $assessment = { title => $title,
                    175:                            src   => $src,
                    176:                            symb  => $symb,
                    177:                            type  => 'assessment',
1.53      matthew   178:                            parts => $parts,
                    179:                            num_parts => scalar(@$parts),
1.45      matthew   180:                        };
1.49      matthew   181:         push(@Assessments,$assessment);
1.45      matthew   182:         push(@{$currentmap->{'contents'}},$assessment);
                    183:         $currentmap->{'num_assess'}++;
1.53      matthew   184:         $currentmap->{'num_assess_parts'}+= scalar(@$parts);
1.45      matthew   185:     }
1.58      matthew   186:     $navmap->untieHashes();
1.49      matthew   187:     return ($top,\@Sequences,\@Assessments);
1.45      matthew   188: }
1.50      matthew   189: 
1.4       stredwic  190: sub LoadDiscussion {
1.13      stredwic  191:     my ($courseID)=@_;
1.5       minaeibi  192:     my %Discuss=();
                    193:     my %contrib=&Apache::lonnet::dump(
                    194:                 $courseID,
                    195:                 $ENV{'course.'.$courseID.'.domain'},
                    196:                 $ENV{'course.'.$courseID.'.num'});
                    197: 				 
                    198:     #my %contrib=&DownloadCourseInformation($name, $courseID, 0);
                    199: 
1.4       stredwic  200:     foreach my $temp(keys %contrib) {
                    201: 	if ($temp=~/^version/) {
                    202: 	    my $ver=$contrib{$temp};
                    203: 	    my ($dummy,$prb)=split(':',$temp);
                    204: 	    for (my $idx=1; $idx<=$ver; $idx++ ) {
                    205: 		my $name=$contrib{"$idx:$prb:sendername"};
1.5       minaeibi  206: 		$Discuss{"$name:$prb"}=$idx;	
1.4       stredwic  207: 	    }
                    208: 	}
                    209:     }       
1.5       minaeibi  210: 
                    211:     return \%Discuss;
1.1       stredwic  212: }
                    213: 
                    214: =pod
                    215: 
                    216: =item &ProcessFullName()
                    217: 
                    218: Takes lastname, generation, firstname, and middlename (or some partial
                    219: set of this data) and returns the full name version as a string.  Format
                    220: is Lastname generation, firstname middlename or a subset of this.
                    221: 
                    222: =cut
                    223: 
                    224: sub ProcessFullName {
                    225:     my ($lastname, $generation, $firstname, $middlename)=@_;
                    226:     my $Str = '';
                    227: 
1.34      matthew   228:     # Strip whitespace preceeding & following name components.
                    229:     $lastname   =~ s/(\s+$|^\s+)//g;
                    230:     $generation =~ s/(\s+$|^\s+)//g;
                    231:     $firstname  =~ s/(\s+$|^\s+)//g;
                    232:     $middlename =~ s/(\s+$|^\s+)//g;
                    233: 
1.1       stredwic  234:     if($lastname ne '') {
1.34      matthew   235: 	$Str .= $lastname;
                    236: 	$Str .= ' '.$generation if ($generation ne '');
                    237: 	$Str .= ',';
                    238:         $Str .= ' '.$firstname  if ($firstname ne '');
                    239:         $Str .= ' '.$middlename if ($middlename ne '');
1.1       stredwic  240:     } else {
1.34      matthew   241:         $Str .= $firstname      if ($firstname ne '');
                    242:         $Str .= ' '.$middlename if ($middlename ne '');
                    243:         $Str .= ' '.$generation if ($generation ne '');
1.1       stredwic  244:     }
                    245: 
                    246:     return $Str;
                    247: }
                    248: 
1.46      matthew   249: ################################################
                    250: ################################################
                    251: 
                    252: =pod
                    253: 
1.47      matthew   254: =item &make_into_hash($values);
                    255: 
                    256: Returns a reference to a hash as described by $values.  $values is
                    257: assumed to be the result of 
1.57      matthew   258:     join(':',map {&Apache::lonnet::escape($_)} %orighash);
1.47      matthew   259: 
                    260: This is a helper function for get_current_state.
                    261: 
                    262: =cut
                    263: 
                    264: ################################################
                    265: ################################################
                    266: sub make_into_hash {
                    267:     my $values = shift;
                    268:     my %tmp = map { &Apache::lonnet::unescape($_); }
                    269:                                            split(':',$values);
                    270:     return \%tmp;
                    271: }
                    272: 
                    273: 
                    274: ################################################
                    275: ################################################
                    276: 
                    277: =pod
                    278: 
1.57      matthew   279: =head1 LOCAL DATA CACHING SUBROUTINES
                    280: 
                    281: The local caching is done using MySQL.  There is no fall-back implementation
                    282: if MySQL is not running.
                    283: 
                    284: The programmers interface is to call &get_current_state() or some other
                    285: primary interface subroutine (described below).  The internals of this 
                    286: storage system are documented here.
                    287: 
                    288: There are six tables used to store student performance data (the results of
                    289: a dumpcurrent).  Each of these tables is created in MySQL with a name of
                    290: $courseid_*****, where ***** is 'symb', 'part', or whatever is appropriate 
                    291: for the table.  The tables and their purposes are described below.
                    292: 
                    293: Some notes before we get started.
                    294: 
                    295: Each table must have a PRIMARY KEY, which is a column or set of columns which
                    296: will serve to uniquely identify a row of data.  NULL is not allowed!
                    297: 
                    298: INDEXes work best on integer data.
                    299: 
                    300: JOIN is used to combine data from many tables into one output.
                    301: 
                    302: lonmysql.pm is used for some of the interface, specifically the table creation
                    303: calls.  The inserts are done in bulk by directly calling the database handler.
                    304: The SELECT ... JOIN statement used to retrieve the data does not have an
                    305: interface in lonmysql.pm and I shudder at the thought of writing one.
                    306: 
                    307: =head3 Table Descriptions
                    308: 
                    309: =over 4
                    310: 
                    311: =item $symb_table
                    312: 
                    313: The symb_table has two columns.  The first is a 'symb_id' and the second
                    314: is the text name for the 'symb' (limited to 64k).  The 'symb_id' is generated
                    315: automatically by MySQL so inserts should be done on this table with an
                    316: empty first element.  This table has its PRIMARY KEY on the 'symb_id'.
                    317: 
                    318: =item $part_table
                    319: 
                    320: The part_table has two columns.  The first is a 'part_id' and the second
                    321: is the text name for the 'part' (limited to 100 characters).  The 'part_id' is
                    322: generated automatically by MySQL so inserts should be done on this table with
                    323: an empty first element.  This table has its PRIMARY KEY on the 'part' (100
                    324: characters) and a KEY on 'part_id'.
                    325: 
                    326: =item $student_table
                    327: 
                    328: The student_table has two columns.  The first is a 'student_id' and the second
                    329: is the text description of the 'student' (typically username:domain) (less
                    330: than 100 characters).  The 'student_id' is automatically generated by MySQL.
                    331: The use of the name 'student_id' is loaded, I know, but this ID is used ONLY 
                    332: internally to the MySQL database and is not the same as the students ID 
                    333: (stored in the students environment).  This table has its PRIMARY KEY on the
                    334: 'student' (100 characters).
                    335: 
                    336: =item $updatetime_table
                    337: 
                    338: The updatetime_table has two columns.  The first is 'student' (100 characters,
                    339: typically username:domain).  The second is 'updatetime', which is an unsigned
                    340: integer, NOT a MySQL date.  This table has its PRIMARY KEY on 'student' (100
                    341: characters).
                    342: 
                    343: =item $performance_table
                    344: 
                    345: The performance_table has 9 columns.  The first three are 'symb_id', 
                    346: 'student_id', and 'part_id'.  These comprise the PRIMARY KEY for this table
                    347: and are directly related to the $symb_table, $student_table, and $part_table
                    348: described above.  MySQL does better indexing on numeric items than text,
                    349: so we use these three "index tables".  The remaining columns are
                    350: 'solved', 'tries', 'awarded', 'award', 'awarddetail', and 'timestamp'.
                    351: These are either the MySQL type TINYTEXT or various integers ('tries' and 
                    352: 'timestamp').  This table has KEYs of 'student_id' and 'symb_id'.
                    353: For use of this table, see the functions described below.
                    354: 
                    355: =item $parameters_table
                    356: 
                    357: The parameters_table holds the data that does not fit neatly into the
                    358: performance_table.  The parameters table has four columns: 'symb_id',
                    359: 'student_id', 'parameter', and 'value'.  'symb_id', 'student_id', and
                    360: 'parameter' comprise the PRIMARY KEY for this table.  'parameter' is 
                    361: limited to 255 characters.  'value' is limited to 64k characters.
                    362: 
                    363: =back
                    364: 
                    365: =head3 Important Subroutines
                    366: 
                    367: Here is a brief overview of the subroutines which are likely to be of 
                    368: interest:
                    369: 
                    370: =over 4
                    371: 
                    372: =item &get_current_state(): programmers interface.
                    373: 
                    374: =item &init_dbs(): table creation
                    375: 
                    376: =item &update_student_data(): data storage calls
                    377: 
                    378: =item &get_student_data_from_performance_cache(): data retrieval
                    379: 
                    380: =back
                    381: 
                    382: =head3 Main Documentation
                    383: 
                    384: =over 4
                    385: 
                    386: =cut
                    387: 
                    388: ################################################
                    389: ################################################
                    390: 
                    391: ################################################
                    392: ################################################
                    393: {
                    394: 
                    395: my $current_course ='';
                    396: my $symb_table;
                    397: my $part_table;
                    398: my $student_table;
                    399: my $updatetime_table;
                    400: my $performance_table;
                    401: my $parameters_table;
                    402: 
                    403: ################################################
                    404: ################################################
                    405: 
                    406: =pod
                    407: 
                    408: =item &init_dbs()
                    409: 
                    410: Input: course id
                    411: 
                    412: Output: 0 on success, positive integer on error
                    413: 
                    414: This routine issues the calls to lonmysql to create the tables used to
                    415: store student data.
                    416: 
                    417: =cut
                    418: 
                    419: ################################################
                    420: ################################################
                    421: sub init_dbs {
                    422:     my $courseid = shift;
                    423:     &setup_table_names($courseid);
                    424:     #
                    425:     # Note - changes to this table must be reflected in the code that 
                    426:     # stores the data (calls &Apache::lonmysql::store_row with this table
                    427:     # id
                    428:     my $symb_table_def = {
                    429:         id => $symb_table,
                    430:         permanent => 'no',
                    431:         columns => [{ name => 'symb_id',
                    432:                       type => 'MEDIUMINT UNSIGNED',
                    433:                       restrictions => 'NOT NULL',
                    434:                       auto_inc     => 'yes', },
                    435:                     { name => 'symb',
                    436:                       type => 'MEDIUMTEXT',
                    437:                       restrictions => 'NOT NULL'},
                    438:                     ],
                    439:         'PRIMARY KEY' => ['symb_id'],
                    440:     };
                    441:     #
                    442:     my $part_table_def = {
                    443:         id => $part_table,
                    444:         permanent => 'no',
                    445:         columns => [{ name => 'part_id',
                    446:                       type => 'MEDIUMINT UNSIGNED',
                    447:                       restrictions => 'NOT NULL',
                    448:                       auto_inc     => 'yes', },
                    449:                     { name => 'part',
                    450:                       type => 'VARCHAR(100)',
                    451:                       restrictions => 'NOT NULL'},
                    452:                     ],
                    453:         'PRIMARY KEY' => ['part (100)'],
                    454:         'KEY' => [{ columns => ['part_id']},],
                    455:     };
                    456:     #
                    457:     my $student_table_def = {
                    458:         id => $student_table,
                    459:         permanent => 'no',
                    460:         columns => [{ name => 'student_id',
                    461:                       type => 'MEDIUMINT UNSIGNED',
                    462:                       restrictions => 'NOT NULL',
                    463:                       auto_inc     => 'yes', },
                    464:                     { name => 'student',
                    465:                       type => 'VARCHAR(100)',
                    466:                       restrictions => 'NOT NULL'},
                    467:                     ],
                    468:         'PRIMARY KEY' => ['student (100)'],
                    469:         'KEY' => [{ columns => ['student_id']},],
                    470:     };
                    471:     #
                    472:     my $updatetime_table_def = {
                    473:         id => $updatetime_table,
                    474:         permanent => 'no',
                    475:         columns => [{ name => 'student',
                    476:                       type => 'VARCHAR(100)',
                    477:                       restrictions => 'NOT NULL UNIQUE',},
                    478:                     { name => 'updatetime',
                    479:                       type => 'INT UNSIGNED',
                    480:                       restrictions => 'NOT NULL' },
                    481:                     ],
                    482:         'PRIMARY KEY' => ['student (100)'],
                    483:     };
                    484:     #
                    485:     my $performance_table_def = {
                    486:         id => $performance_table,
                    487:         permanent => 'no',
                    488:         columns => [{ name => 'symb_id',
                    489:                       type => 'MEDIUMINT UNSIGNED',
                    490:                       restrictions => 'NOT NULL'  },
                    491:                     { name => 'student_id',
                    492:                       type => 'MEDIUMINT UNSIGNED',
                    493:                       restrictions => 'NOT NULL'  },
                    494:                     { name => 'part_id',
                    495:                       type => 'MEDIUMINT UNSIGNED',
                    496:                       restrictions => 'NOT NULL' },
                    497:                     { name => 'solved',
                    498:                       type => 'TINYTEXT' },
                    499:                     { name => 'tries',
                    500:                       type => 'SMALLINT UNSIGNED' },
                    501:                     { name => 'awarded',
                    502:                       type => 'TINYTEXT' },
                    503:                     { name => 'award',
                    504:                       type => 'TINYTEXT' },
                    505:                     { name => 'awarddetail',
                    506:                       type => 'TINYTEXT' },
                    507:                     { name => 'timestamp',
                    508:                       type => 'INT UNSIGNED'},
                    509:                     ],
                    510:         'PRIMARY KEY' => ['symb_id','student_id','part_id'],
                    511:         'KEY' => [{ columns=>['student_id'] },
                    512:                   { columns=>['symb_id'] },],
                    513:     };
                    514:     #
                    515:     my $parameters_table_def = {
                    516:         id => $parameters_table,
                    517:         permanent => 'no',
                    518:         columns => [{ name => 'symb_id',
                    519:                       type => 'MEDIUMINT UNSIGNED',
                    520:                       restrictions => 'NOT NULL'  },
                    521:                     { name => 'student_id',
                    522:                       type => 'MEDIUMINT UNSIGNED',
                    523:                       restrictions => 'NOT NULL'  },
                    524:                     { name => 'parameter',
                    525:                       type => 'TINYTEXT',
                    526:                       restrictions => 'NOT NULL'  },
                    527:                     { name => 'value',
                    528:                       type => 'MEDIUMTEXT' },
                    529:                     ],
                    530:         'PRIMARY KEY' => ['symb_id','student_id','parameter (255)'],
                    531:     };
                    532:     #
                    533:     # Create the tables
                    534:     my $tableid;
                    535:     $tableid = &Apache::lonmysql::create_table($symb_table_def);
                    536:     if (! defined($tableid)) {
                    537:         &Apache::lonnet::logthis("error creating symb_table: ".
                    538:                                  &Apache::lonmysql::get_error());
                    539:         return 1;
                    540:     }
                    541:     #
                    542:     $tableid = &Apache::lonmysql::create_table($part_table_def);
                    543:     if (! defined($tableid)) {
                    544:         &Apache::lonnet::logthis("error creating part_table: ".
                    545:                                  &Apache::lonmysql::get_error());
                    546:         return 2;
                    547:     }
                    548:     #
                    549:     $tableid = &Apache::lonmysql::create_table($student_table_def);
                    550:     if (! defined($tableid)) {
                    551:         &Apache::lonnet::logthis("error creating student_table: ".
                    552:                                  &Apache::lonmysql::get_error());
                    553:         return 3;
                    554:     }
                    555:     #
                    556:     $tableid = &Apache::lonmysql::create_table($updatetime_table_def);
                    557:     if (! defined($tableid)) {
                    558:         &Apache::lonnet::logthis("error creating updatetime_table: ".
                    559:                                  &Apache::lonmysql::get_error());
                    560:         return 4;
                    561:     }
                    562:     #
                    563:     $tableid = &Apache::lonmysql::create_table($performance_table_def);
                    564:     if (! defined($tableid)) {
                    565:         &Apache::lonnet::logthis("error creating preformance_table: ".
                    566:                                  &Apache::lonmysql::get_error());
                    567:         return 5;
                    568:     }
                    569:     #
                    570:     $tableid = &Apache::lonmysql::create_table($parameters_table_def);
                    571:     if (! defined($tableid)) {
                    572:         &Apache::lonnet::logthis("error creating parameters_table: ".
                    573:                                  &Apache::lonmysql::get_error());
                    574:         return 6;
                    575:     }
                    576:     return 0;
                    577: }
                    578: 
                    579: ################################################
                    580: ################################################
                    581: 
                    582: =pod
                    583: 
                    584: =item &get_part_id()
                    585: 
                    586: Get the MySQL id of a problem part string.
                    587: 
                    588: Input: $part
                    589: 
                    590: Output: undef on error, integer $part_id on success.
                    591: 
                    592: =item &get_part()
                    593: 
                    594: Get the string describing a part from the MySQL id of the problem part.
                    595: 
                    596: Input: $part_id
                    597: 
                    598: Output: undef on error, $part string on success.
                    599: 
                    600: =cut
                    601: 
                    602: ################################################
                    603: ################################################
                    604: 
1.61      matthew   605: my $have_read_part_table = 0;
1.57      matthew   606: my %ids_by_part;
                    607: my %parts_by_id;
                    608: 
                    609: sub get_part_id {
                    610:     my ($part) = @_;
1.61      matthew   611:     $part = 0 if (! defined($part));
                    612:     if (! $have_read_part_table) {
                    613:         my @Result = &Apache::lonmysql::get_rows($part_table);
                    614:         foreach (@Result) {
                    615:             $ids_by_part{$_->[1]}=$_->[0];
                    616:         }
                    617:         $have_read_part_table = 1;
                    618:     }
1.57      matthew   619:     if (! exists($ids_by_part{$part})) {
                    620:         &Apache::lonmysql::store_row($part_table,[undef,$part]);
                    621:         undef(%ids_by_part);
                    622:         my @Result = &Apache::lonmysql::get_rows($part_table);
                    623:         foreach (@Result) {
                    624:             $ids_by_part{$_->[1]}=$_->[0];
                    625:         }
                    626:     }
                    627:     return $ids_by_part{$part} if (exists($ids_by_part{$part}));
                    628:     return undef; # error
                    629: }
                    630: 
                    631: sub get_part {
                    632:     my ($part_id) = @_;
                    633:     if (! exists($parts_by_id{$part_id})  || 
                    634:         ! defined($parts_by_id{$part_id}) ||
                    635:         $parts_by_id{$part_id} eq '') {
                    636:         my @Result = &Apache::lonmysql::get_rows($part_table);
                    637:         foreach (@Result) {
                    638:             $parts_by_id{$_->[0]}=$_->[1];
                    639:         }
                    640:     }
                    641:     return $parts_by_id{$part_id} if(exists($parts_by_id{$part_id}));
                    642:     return undef; # error
                    643: }
                    644: 
                    645: ################################################
                    646: ################################################
                    647: 
                    648: =pod
                    649: 
                    650: =item &get_symb_id()
                    651: 
                    652: Get the MySQL id of a symb.
                    653: 
                    654: Input: $symb
                    655: 
                    656: Output: undef on error, integer $symb_id on success.
                    657: 
                    658: =item &get_symb()
                    659: 
                    660: Get the symb associated with a MySQL symb_id.
                    661: 
                    662: Input: $symb_id
                    663: 
                    664: Output: undef on error, $symb on success.
                    665: 
                    666: =cut
                    667: 
                    668: ################################################
                    669: ################################################
                    670: 
1.61      matthew   671: my $have_read_symb_table = 0;
1.57      matthew   672: my %ids_by_symb;
                    673: my %symbs_by_id;
                    674: 
                    675: sub get_symb_id {
                    676:     my ($symb) = @_;
1.61      matthew   677:     if (! $have_read_symb_table) {
                    678:         my @Result = &Apache::lonmysql::get_rows($symb_table);
                    679:         foreach (@Result) {
                    680:             $ids_by_symb{$_->[1]}=$_->[0];
                    681:         }
                    682:         $have_read_symb_table = 1;
                    683:     }
1.57      matthew   684:     if (! exists($ids_by_symb{$symb})) {
                    685:         &Apache::lonmysql::store_row($symb_table,[undef,$symb]);
                    686:         undef(%ids_by_symb);
                    687:         my @Result = &Apache::lonmysql::get_rows($symb_table);
                    688:         foreach (@Result) {
                    689:             $ids_by_symb{$_->[1]}=$_->[0];
                    690:         }
                    691:     }
                    692:     return $ids_by_symb{$symb} if(exists( $ids_by_symb{$symb}));
                    693:     return undef; # error
                    694: }
                    695: 
                    696: sub get_symb {
                    697:     my ($symb_id) = @_;
                    698:     if (! exists($symbs_by_id{$symb_id})  || 
                    699:         ! defined($symbs_by_id{$symb_id}) ||
                    700:         $symbs_by_id{$symb_id} eq '') {
                    701:         my @Result = &Apache::lonmysql::get_rows($symb_table);
                    702:         foreach (@Result) {
                    703:             $symbs_by_id{$_->[0]}=$_->[1];
                    704:         }
                    705:     }
                    706:     return $symbs_by_id{$symb_id} if(exists( $symbs_by_id{$symb_id}));
                    707:     return undef; # error
                    708: }
                    709: 
                    710: ################################################
                    711: ################################################
                    712: 
                    713: =pod
                    714: 
                    715: =item &get_student_id()
                    716: 
                    717: Get the MySQL id of a student.
                    718: 
                    719: Input: $sname, $dom
                    720: 
                    721: Output: undef on error, integer $student_id on success.
                    722: 
                    723: =item &get_student()
                    724: 
                    725: Get student username:domain associated with the MySQL student_id.
                    726: 
                    727: Input: $student_id
                    728: 
                    729: Output: undef on error, string $student (username:domain) on success.
                    730: 
                    731: =cut
                    732: 
                    733: ################################################
                    734: ################################################
                    735: 
1.61      matthew   736: my $have_read_student_table = 0;
1.57      matthew   737: my %ids_by_student;
                    738: my %students_by_id;
                    739: 
                    740: sub get_student_id {
                    741:     my ($sname,$sdom) = @_;
                    742:     my $student = $sname.':'.$sdom;
1.61      matthew   743:     if (! $have_read_student_table) {
                    744:         my @Result = &Apache::lonmysql::get_rows($student_table);
                    745:         foreach (@Result) {
                    746:             $ids_by_student{$_->[1]}=$_->[0];
                    747:         }
                    748:         $have_read_student_table = 1;
                    749:     }
1.57      matthew   750:     if (! exists($ids_by_student{$student})) {
                    751:         &Apache::lonmysql::store_row($student_table,[undef,$student]);
                    752:         undef(%ids_by_student);
                    753:         my @Result = &Apache::lonmysql::get_rows($student_table);
                    754:         foreach (@Result) {
                    755:             $ids_by_student{$_->[1]}=$_->[0];
                    756:         }
                    757:     }
                    758:     return $ids_by_student{$student} if(exists( $ids_by_student{$student}));
                    759:     return undef; # error
                    760: }
                    761: 
                    762: sub get_student {
                    763:     my ($student_id) = @_;
                    764:     if (! exists($students_by_id{$student_id})  || 
                    765:         ! defined($students_by_id{$student_id}) ||
                    766:         $students_by_id{$student_id} eq '') {
                    767:         my @Result = &Apache::lonmysql::get_rows($student_table);
                    768:         foreach (@Result) {
                    769:             $students_by_id{$_->[0]}=$_->[1];
                    770:         }
                    771:     }
                    772:     return $students_by_id{$student_id} if(exists($students_by_id{$student_id}));
                    773:     return undef; # error
                    774: }
                    775: 
                    776: ################################################
                    777: ################################################
                    778: 
                    779: =pod
                    780: 
                    781: =item &update_student_data()
                    782: 
                    783: Input: $sname, $sdom, $courseid
                    784: 
                    785: Output: $returnstatus, \%student_data
                    786: 
                    787: $returnstatus is a string describing any errors that occured.  'okay' is the
                    788: default.
                    789: \%student_data is the data returned by a call to lonnet::currentdump.
                    790: 
                    791: This subroutine loads a students data using lonnet::currentdump and inserts
                    792: it into the MySQL database.  The inserts are done on two tables, 
                    793: $performance_table and $parameters_table.  $parameters_table holds the data 
                    794: that is not included in $performance_table.  See the description of 
                    795: $performance_table elsewhere in this file.  The INSERT calls are made
                    796: directly by this subroutine, not through lonmysql because we do a 'bulk'
                    797: insert which takes advantage of MySQLs non-SQL compliant INSERT command to 
                    798: insert multiple rows at a time.  If anything has gone wrong during this
                    799: process, $returnstatus is updated with a description of the error and
                    800: \%student_data is returned.  
                    801: 
                    802: Notice we do not insert the data and immediately query it.  This means it
                    803: is possible for there to be data returned this first time that is not 
                    804: available the second time.  CYA.
                    805: 
                    806: =cut
                    807: 
                    808: ################################################
                    809: ################################################
                    810: sub update_student_data {
                    811:     my ($sname,$sdom,$courseid) = @_;
                    812:     #
1.60      matthew   813:     # Set up database names
                    814:     &setup_table_names($courseid);
                    815:     #
1.57      matthew   816:     my $student_id = &get_student_id($sname,$sdom);
                    817:     my $student = $sname.':'.$sdom;
                    818:     #
                    819:     my $returnstatus = 'okay';
                    820:     #
                    821:     # Download students data
                    822:     my $time_of_retrieval = time;
                    823:     my @tmp = &Apache::lonnet::currentdump($courseid,$sdom,$sname);
                    824:     if ((scalar(@tmp) > 0) && ($tmp[0] =~ /^error:/)) {
                    825:         &Apache::lonnet::logthis('error getting data for '.
                    826:                                  $sname.':'.$sdom.' in course '.$courseid.
                    827:                                  ':'.$tmp[0]);
                    828:         $returnstatus = 'error getting data';
                    829:         return $returnstatus;
                    830:     }
                    831:     if (scalar(@tmp) < 1) {
                    832:         return ('no data',undef);
                    833:     }
                    834:     my %student_data = @tmp;
                    835:     #
                    836:     # Remove all of the students data from the table
1.60      matthew   837:     my $dbh = &Apache::lonmysql::get_dbh();
                    838:     $dbh->do('DELETE FROM '.$performance_table.' WHERE student_id='.
                    839:              $student_id);
                    840:     $dbh->do('DELETE FROM '.$parameters_table.' WHERE student_id='.
                    841:              $student_id);
1.57      matthew   842:     #
                    843:     # Store away the data
                    844:     #
                    845:     my $starttime = Time::HiRes::time;
                    846:     my $elapsed = 0;
                    847:     my $rows_stored;
                    848:     my $store_parameters_command  = 'INSERT INTO '.$parameters_table.
1.60      matthew   849:         ' VALUES '."\n";
1.61      matthew   850:     my $num_parameters = 0;
1.57      matthew   851:     my $store_performance_command = 'INSERT INTO '.$performance_table.
1.60      matthew   852:         ' VALUES '."\n";
1.57      matthew   853:     return 'error' if (! defined($dbh));
                    854:     while (my ($current_symb,$param_hash) = each(%student_data)) {
                    855:         #
                    856:         # make sure the symb is set up properly
                    857:         my $symb_id = &get_symb_id($current_symb);
                    858:         #
                    859:         # Load data into the tables
1.63    ! matthew   860:         while (my ($parameter,$value) = each(%$param_hash)) {
1.57      matthew   861:             my $newstring;
1.63    ! matthew   862:             if ($parameter !~ /(timestamp|resource\.(.*)\.(solved|tries|awarded|award|awarddetail|previous))/) {
1.57      matthew   863:                 $newstring = "('".join("','",
                    864:                                        $symb_id,$student_id,
1.60      matthew   865:                                        $parameter,$value)."'),\n";
1.61      matthew   866:                 $num_parameters ++;
1.57      matthew   867:                 if ($newstring !~ /''/) {
                    868:                     $store_parameters_command .= $newstring;
                    869:                     $rows_stored++;
                    870:                 }
                    871:             }
                    872:             next if ($parameter !~ /^resource\.(.*)\.solved$/);
                    873:             #
                    874:             my $part = $1;
                    875:             my $part_id = &get_part_id($part);
                    876:             next if (!defined($part_id));
                    877:             my $solved  = $value;
                    878:             my $tries   = $param_hash->{'resource.'.$part.'.tries'};
                    879:             my $awarded = $param_hash->{'resource.'.$part.'.awarded'};
                    880:             my $award   = $param_hash->{'resource.'.$part.'.award'};
                    881:             my $awarddetail = $param_hash->{'resource.'.$part.'.awarddetail'};
                    882:             my $timestamp = $param_hash->{'timestamp'};
1.60      matthew   883:             #
1.57      matthew   884:             $solved      = '' if (! defined($awarded));
                    885:             $tries       = '' if (! defined($tries));
                    886:             $awarded     = '' if (! defined($awarded));
                    887:             $award       = '' if (! defined($award));
                    888:             $awarddetail = '' if (! defined($awarddetail));
                    889:             $newstring = "('".join("','",$symb_id,$student_id,$part_id,
                    890:                                    $solved,$tries,$awarded,$award,
1.63    ! matthew   891:                                    $awarddetail,$timestamp)."'),\n";
1.57      matthew   892:             $store_performance_command .= $newstring;
                    893:             $rows_stored++;
                    894:         }
                    895:     }
                    896:     chop $store_parameters_command;
1.60      matthew   897:     chop $store_parameters_command;
                    898:     chop $store_performance_command;
1.57      matthew   899:     chop $store_performance_command;
                    900:     my $start = Time::HiRes::time;
1.61      matthew   901:     $dbh->do($store_parameters_command) if ($num_parameters>0);
1.57      matthew   902:     if ($dbh->err()) {
                    903:         &Apache::lonnet::logthis(' bigass insert error:'.$dbh->errstr());
1.61      matthew   904:         &Apache::lonnet::logthis('command = '.$store_parameters_command);
1.57      matthew   905:         $returnstatus = 'error: unable to insert parameters into database';
                    906:         return $returnstatus,\%student_data;
                    907:     }
                    908:     $dbh->do($store_performance_command);
                    909:     if ($dbh->err()) {
                    910:         &Apache::lonnet::logthis(' bigass insert error:'.$dbh->errstr());
1.61      matthew   911:         &Apache::lonnet::logthis('command = '.$store_performance_command);
1.57      matthew   912:         $returnstatus = 'error: unable to insert performance into database';
                    913:         return $returnstatus,\%student_data;
                    914:     }
                    915:     $elapsed += Time::HiRes::time - $start;
                    916:     #
                    917:     # Set the students update time
                    918:     &Apache::lonmysql::replace_row($updatetime_table,
                    919:                                    [$student,$time_of_retrieval]);
                    920:     return ($returnstatus,\%student_data);
                    921: }
                    922: 
                    923: ################################################
                    924: ################################################
                    925: 
                    926: =pod
                    927: 
                    928: =item &ensure_current_data()
                    929: 
                    930: Input: $sname, $sdom, $courseid
                    931: 
                    932: Output: $status, $data
                    933: 
                    934: This routine ensures the data for a given student is up to date.  It calls
                    935: &init_dbs() if the tables do not exist.  The $updatetime_table is queried
                    936: to determine the time of the last update.  If the students data is out of
                    937: date, &update_student_data() is called.  The return values from the call
                    938: to &update_student_data() are returned.
                    939: 
                    940: =cut
                    941: 
                    942: ################################################
                    943: ################################################
                    944: sub ensure_current_data {
                    945:     my ($sname,$sdom,$courseid) = @_;
                    946:     my $status = 'okay';   # return value
                    947:     #
1.61      matthew   948:     $courseid = $ENV{'request.course.id'} if (! defined($courseid));
                    949:     # 
                    950:     # Clean out package variables
1.57      matthew   951:     &setup_table_names($courseid);
                    952:     #
                    953:     # if the tables do not exist, make them
                    954:     my @CurrentTable = &Apache::lonmysql::tables_in_db();
                    955:     my ($found_symb,$found_student,$found_part,$found_update,
                    956:         $found_performance,$found_parameters);
                    957:     foreach (@CurrentTable) {
                    958:         $found_symb        = 1 if ($_ eq $symb_table);
                    959:         $found_student     = 1 if ($_ eq $student_table);
                    960:         $found_part        = 1 if ($_ eq $part_table);
                    961:         $found_update      = 1 if ($_ eq $updatetime_table);
                    962:         $found_performance = 1 if ($_ eq $performance_table);
                    963:         $found_parameters  = 1 if ($_ eq $parameters_table);
                    964:     }
                    965:     if (!$found_symb        || !$found_update || 
                    966:         !$found_student     || !$found_part   ||
                    967:         !$found_performance || !$found_parameters) {
                    968:         if (&init_dbs($courseid)) {
                    969:             return 'error';
                    970:         }
                    971:     }
                    972:     #
                    973:     # Get the update time for the user
                    974:     my $updatetime = 0;
1.60      matthew   975:     my $modifiedtime = &Apache::lonnet::GetFileTimestamp
                    976:         ($sdom,$sname,$courseid.'.db',
                    977:          $Apache::lonnet::perlvar{'lonUsersDir'});
1.57      matthew   978:     #
                    979:     my $student = $sname.':'.$sdom;
                    980:     my @Result = &Apache::lonmysql::get_rows($updatetime_table,
                    981:                                              "student ='$student'");
                    982:     my $data = undef;
                    983:     if (@Result) {
                    984:         $updatetime = $Result[0]->[1];
                    985:     }
                    986:     if ($modifiedtime > $updatetime) {
                    987:         ($status,$data) = &update_student_data($sname,$sdom,$courseid);
                    988:     }
                    989:     return ($status,$data);
                    990: }
                    991: 
                    992: ################################################
                    993: ################################################
                    994: 
                    995: =pod
                    996: 
                    997: =item &get_student_data_from_performance_cache()
                    998: 
                    999: Input: $sname, $sdom, $symb, $courseid
                   1000: 
                   1001: Output: hash reference containing the data for the given student.
                   1002: If $symb is undef, all the students data is returned.
                   1003: 
                   1004: This routine is the heart of the local caching system.  See the description
                   1005: of $performance_table, $symb_table, $student_table, and $part_table.  The
                   1006: main task is building the MySQL request.  The tables appear in the request
                   1007: in the order in which they should be parsed by MySQL.  When searching
                   1008: on a student the $student_table is used to locate the 'student_id'.  All
                   1009: rows in $performance_table which have a matching 'student_id' are returned,
                   1010: with data from $part_table and $symb_table which match the entries in
                   1011: $performance_table, 'part_id' and 'symb_id'.  When searching on a symb,
                   1012: the $symb_table is processed first, with matching rows grabbed from 
                   1013: $performance_table and filled in from $part_table and $student_table in
                   1014: that order.  
                   1015: 
                   1016: Running 'EXPLAIN ' on the 'SELECT' statements generated can be quite 
                   1017: interesting, especially if you play with the order the tables are listed.  
                   1018: 
                   1019: =cut
                   1020: 
                   1021: ################################################
                   1022: ################################################
                   1023: sub get_student_data_from_performance_cache {
                   1024:     my ($sname,$sdom,$symb,$courseid)=@_;
                   1025:     my $student = $sname.':'.$sdom if (defined($sname) && defined($sdom));
1.61      matthew  1026:     &setup_table_names($courseid);
1.57      matthew  1027:     #
                   1028:     # Return hash
                   1029:     my $studentdata;
                   1030:     #
                   1031:     my $dbh = &Apache::lonmysql::get_dbh();
                   1032:     my $request = "SELECT ".
                   1033:         "d.symb,c.part,a.solved,a.tries,a.awarded,a.award,a.awarddetail,".
1.63    ! matthew  1034:             "a.timestamp ";
1.57      matthew  1035:     if (defined($student)) {
                   1036:         $request .= "FROM $student_table AS b ".
                   1037:             "LEFT JOIN $performance_table AS a ON b.student_id=a.student_id ".
                   1038:             "LEFT JOIN $part_table AS c ON c.part_id = a.part_id ".
                   1039:             "LEFT JOIN $symb_table AS d ON d.symb_id = a.symb_id ".
                   1040:                 "WHERE student='$student'";
                   1041:         if (defined($symb) && $symb ne '') {
                   1042:             $request .= " AND d.symb='".$dbh->quote($symb)."'";
                   1043:         }
                   1044:     } elsif (defined($symb) && $symb ne '') {
                   1045:         $request .= "FROM $symb_table as d ".
                   1046:             "LEFT JOIN $performance_table AS a ON d.symb_id=a.symb_id ".
                   1047:             "LEFT JOIN $part_table    AS c ON c.part_id = a.part_id ".
                   1048:             "LEFT JOIN $student_table AS b ON b.student_id = a.student_id ".
                   1049:                 "WHERE symb='".$dbh->quote($symb)."'";
                   1050:     }
                   1051:     my $starttime = Time::HiRes::time;
                   1052:     my $rows_retrieved = 0;
                   1053:     my $sth = $dbh->prepare($request);
                   1054:     $sth->execute();
                   1055:     if ($sth->err()) {
                   1056:         &Apache::lonnet::logthis("Unable to execute MySQL request:");
                   1057:         &Apache::lonnet::logthis("\n".$request."\n");
                   1058:         &Apache::lonnet::logthis("error is:".$sth->errstr());
                   1059:         return undef;
                   1060:     }
                   1061:     foreach my $row (@{$sth->fetchall_arrayref}) {
                   1062:         $rows_retrieved++;
1.63    ! matthew  1063:         my ($symb,$part,$solved,$tries,$awarded,$award,$awarddetail,$time) = 
1.57      matthew  1064:             (@$row);
                   1065:         my $base = 'resource.'.$part;
                   1066:         $studentdata->{$symb}->{$base.'.solved'}  = $solved;
                   1067:         $studentdata->{$symb}->{$base.'.tries'}   = $tries;
                   1068:         $studentdata->{$symb}->{$base.'.awarded'} = $awarded;
                   1069:         $studentdata->{$symb}->{$base.'.award'}   = $award;
                   1070:         $studentdata->{$symb}->{$base.'.awarddetail'} = $awarddetail;
                   1071:         $studentdata->{$symb}->{'timestamp'} = $time if (defined($time) && $time ne '');
                   1072:     }
                   1073:     return $studentdata;
                   1074: }
                   1075: 
                   1076: ################################################
                   1077: ################################################
                   1078: 
                   1079: =pod
                   1080: 
                   1081: =item &get_current_state()
                   1082: 
                   1083: Input: $sname,$sdom,$symb,$courseid
                   1084: 
                   1085: Output: Described below
1.46      matthew  1086: 
1.47      matthew  1087: Retrieve the current status of a students performance.  $sname and
1.46      matthew  1088: $sdom are the only required parameters.  If $symb is undef the results
1.47      matthew  1089: of an &Apache::lonnet::currentdump() will be returned.  
1.46      matthew  1090: If $courseid is undef it will be retrieved from the environment.
                   1091: 
                   1092: The return structure is based on &Apache::lonnet::currentdump.  If
                   1093: $symb is unspecified, all the students data is returned in a hash of
                   1094: the form:
                   1095: ( 
                   1096:   symb1 => { param1 => value1, param2 => value2 ... },
                   1097:   symb2 => { param1 => value1, param2 => value2 ... },
                   1098: )
                   1099: 
                   1100: If $symb is specified, a hash of 
                   1101: (
                   1102:   param1 => value1, 
                   1103:   param2 => value2,
                   1104: )
                   1105: is returned.
                   1106: 
1.57      matthew  1107: If no data is found for $symb, or if the student has no performance data,
1.46      matthew  1108: an empty list is returned.
                   1109: 
                   1110: =cut
                   1111: 
                   1112: ################################################
                   1113: ################################################
                   1114: sub get_current_state {
1.47      matthew  1115:     my ($sname,$sdom,$symb,$courseid,$forcedownload)=@_;
                   1116:     #
1.46      matthew  1117:     $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1.47      matthew  1118:     #
1.61      matthew  1119:     return () if (! defined($sname) || ! defined($sdom));
                   1120:     #
1.57      matthew  1121:     my ($status,$data) = &ensure_current_data($sname,$sdom,$courseid);
1.47      matthew  1122:     #
1.57      matthew  1123:     if (defined($data)) {
                   1124:         return %$data;
                   1125:     } elsif ($status eq 'no data') {
                   1126:         return ();
                   1127:     } else {
                   1128:         if ($status ne 'okay' && $status ne '') {
                   1129:             &Apache::lonnet::logthis('status = '.$status);
1.47      matthew  1130:             return ();
                   1131:         }
1.57      matthew  1132:         my $returnhash = &get_student_data_from_performance_cache($sname,$sdom,
                   1133:                                                       $symb,$courseid);
                   1134:         return %$returnhash if (defined($returnhash));
1.46      matthew  1135:     }
1.57      matthew  1136:     return ();
1.61      matthew  1137: }
                   1138: 
                   1139: ################################################
                   1140: ################################################
                   1141: 
                   1142: =pod
                   1143: 
                   1144: =item &get_problem_statistics()
                   1145: 
                   1146: Gather data on a given problem.  The database is assumed to be 
                   1147: populated and all local caching variables are assumed to be set
                   1148: properly.  This means you need to call &ensure_current_data for
                   1149: the students you are concerned with prior to calling this routine.
                   1150: 
                   1151: Inputs: $students, $symb, $part, $courseid
                   1152: 
                   1153: =cut
                   1154: 
                   1155: ################################################
                   1156: ################################################
                   1157: sub get_problem_statistics {
                   1158:     my ($students,$symb,$part,$courseid) = @_;
                   1159:     return if (! defined($symb) || ! defined($part));
                   1160:     $courseid = $ENV{'request.course.id'} if (! defined($courseid));
                   1161:     #
                   1162:     my $symb_id = &get_symb_id($symb);
                   1163:     my $part_id = &get_part_id($part);
                   1164:     my $stats_table = $courseid.'_problem_stats';
                   1165:     #
                   1166:     my $dbh = &Apache::lonmysql::get_dbh();
                   1167:     return undef if (! defined($dbh));
                   1168:     #
                   1169:     # A) Number of Students attempting problem
                   1170:     # B) Total number of tries of students attempting problem
                   1171:     # C) Mod (largest number of tries for solving the problem)
                   1172:     # D) Mean (average number of tries for solving the problem)
                   1173:     # E) Number of students to solve the problem
                   1174:     # F) Number of students to solve the problem by override
                   1175:     # G) Number of students unable to solve the problem
                   1176:     # H) Degree of difficulty : 1-(E+F)/B
                   1177:     # I) Standard deviation of number of tries
                   1178:     # J) Skew of tries: sqrt(sum(Xi-D)^3)/A
                   1179:     #
                   1180:     $dbh->do('DROP TABLE '.$stats_table);  # May return an error
                   1181:     my $request = 
                   1182:         'CREATE TEMPORARY TABLE '.$stats_table.
                   1183:             ' SELECT student_id,solved,award,tries FROM '.$performance_table.
                   1184:                 ' WHERE symb_id='.$symb_id.' AND part_id='.$part_id;
                   1185: #    &Apache::lonnet::logthis($request);
                   1186:     $dbh->do($request);
                   1187:     my ($num,$tries,$mod,$mean,$STD) = &execute_SQL_request
                   1188:         ($dbh,
                   1189:          'SELECT COUNT(*),SUM(tries),MAX(tries),AVG(tries),STD(tries) FROM '.
                   1190:          $stats_table);
                   1191:     my ($Solved) = &execute_SQL_request($dbh,'SELECT COUNT(tries) FROM '.
                   1192:                                         $stats_table.
                   1193:                                         " WHERE solved='correct_by_student'");
                   1194:     my ($solved) = &execute_SQL_request($dbh,'SELECT COUNT(tries) FROM '.
                   1195:                                         $stats_table.
                   1196:                                         " WHERE solved='correct_by_override'");
                   1197:     $num    = 0 if (! defined($num));
                   1198:     $tries  = 0 if (! defined($tries));
                   1199:     $mod    = 0 if (! defined($mod));
                   1200:     $STD    = 0 if (! defined($STD));
                   1201:     $Solved = 0 if (! defined($Solved));
                   1202:     $solved = 0 if (! defined($solved));
                   1203:     #
                   1204:     my $DegOfDiff = 'nan';
                   1205:     $DegOfDiff = 1-($Solved + $solved)/$tries if ($tries>0);
                   1206: 
                   1207:     my $SKEW = 'nan';
                   1208:     if ($num > 0) {
                   1209:         ($SKEW) = &execute_SQL_request($dbh,'SELECT SQRT(SUM('.
                   1210:                                      'POWER(tries - '.$STD.',3)'.
                   1211:                                      '))/'.$num.' FROM '.$stats_table);
                   1212:     }
                   1213:     #
                   1214:     $dbh->do('DROP TABLE '.$stats_table);  # May return an error
                   1215:     return ($num,$tries,$mod,$mean,$Solved,$solved,$DegOfDiff,$STD,$SKEW);
                   1216: }
                   1217: 
                   1218: sub execute_SQL_request {
                   1219:     my ($dbh,$request)=@_;
                   1220: #    &Apache::lonnet::logthis($request);
                   1221:     my $sth = $dbh->prepare($request);
                   1222:     $sth->execute();
                   1223:     my $row = $sth->fetchrow_arrayref();
                   1224:     if (ref($row) eq 'ARRAY' && scalar(@$row)>0) {
                   1225:         return @$row;
                   1226:     }
                   1227:     return ();
                   1228: }
                   1229: 
                   1230: 
                   1231: ################################################
                   1232: ################################################
                   1233: 
                   1234: =pod
                   1235: 
                   1236: =item &setup_table_names()
                   1237: 
                   1238: input: course id
                   1239: 
                   1240: output: none
                   1241: 
                   1242: Cleans up the package variables for local caching.
                   1243: 
                   1244: =cut
                   1245: 
                   1246: ################################################
                   1247: ################################################
                   1248: sub setup_table_names {
                   1249:     my ($courseid) = @_;
                   1250:     if (! defined($courseid)) {
                   1251:         $courseid = $ENV{'request.course.id'};
                   1252:     }
                   1253:     #
                   1254:     if (! defined($current_course) || $current_course ne $courseid) {
                   1255:         # Clear out variables
                   1256:         $have_read_part_table = 0;
                   1257:         undef(%ids_by_part);
                   1258:         undef(%parts_by_id);
                   1259:         $have_read_symb_table = 0;
                   1260:         undef(%ids_by_symb);
                   1261:         undef(%symbs_by_id);
                   1262:         $have_read_student_table = 0;
                   1263:         undef(%ids_by_student);
                   1264:         undef(%students_by_id);
                   1265:         #
                   1266:         $current_course = $courseid;
                   1267:     }
                   1268:     #
                   1269:     # Set up database names
                   1270:     my $base_id = $courseid;
                   1271:     $symb_table        = $base_id.'_'.'symb';
                   1272:     $part_table        = $base_id.'_'.'part';
                   1273:     $student_table     = $base_id.'_'.'student';
                   1274:     $updatetime_table  = $base_id.'_'.'updatetime';
                   1275:     $performance_table = $base_id.'_'.'performance';
                   1276:     $parameters_table  = $base_id.'_'.'parameters';
                   1277:     return;
1.3       stredwic 1278: }
1.1       stredwic 1279: 
1.35      matthew  1280: ################################################
                   1281: ################################################
                   1282: 
                   1283: =pod
                   1284: 
1.57      matthew  1285: =back
                   1286: 
                   1287: =item End of Local Data Caching Subroutines
                   1288: 
                   1289: =cut
                   1290: 
                   1291: ################################################
                   1292: ################################################
                   1293: 
                   1294: 
                   1295: }
                   1296: ################################################
                   1297: ################################################
                   1298: 
                   1299: =pod
                   1300: 
                   1301: =head3 Classlist Subroutines
                   1302: 
1.35      matthew  1303: =item &get_classlist();
                   1304: 
                   1305: Retrieve the classist of a given class or of the current class.  Student
                   1306: information is returned from the classlist.db file and, if needed,
                   1307: from the students environment.
                   1308: 
                   1309: Optional arguments are $cid, $cdom, and $cnum (course id, course domain,
                   1310: and course number, respectively).  Any omitted arguments will be taken 
                   1311: from the current environment ($ENV{'request.course.id'},
                   1312: $ENV{'course.'.$cid.'.domain'}, and $ENV{'course.'.$cid.'.num'}).
                   1313: 
                   1314: Returns a reference to a hash which contains:
                   1315:  keys    '$sname:$sdom'
1.54      bowersj2 1316:  values  [$sdom,$sname,$end,$start,$id,$section,$fullname,$status]
                   1317: 
                   1318: The constant values CL_SDOM, CL_SNAME, CL_END, etc. can be used
                   1319: as indices into the returned list to future-proof clients against
                   1320: changes in the list order.
1.35      matthew  1321: 
                   1322: =cut
                   1323: 
                   1324: ################################################
                   1325: ################################################
1.54      bowersj2 1326: 
                   1327: sub CL_SDOM     { return 0; }
                   1328: sub CL_SNAME    { return 1; }
                   1329: sub CL_END      { return 2; }
                   1330: sub CL_START    { return 3; }
                   1331: sub CL_ID       { return 4; }
                   1332: sub CL_SECTION  { return 5; }
                   1333: sub CL_FULLNAME { return 6; }
                   1334: sub CL_STATUS   { return 7; }
1.35      matthew  1335: 
                   1336: sub get_classlist {
                   1337:     my ($cid,$cdom,$cnum) = @_;
                   1338:     $cid = $cid || $ENV{'request.course.id'};
                   1339:     $cdom = $cdom || $ENV{'course.'.$cid.'.domain'};
                   1340:     $cnum = $cnum || $ENV{'course.'.$cid.'.num'};
1.57      matthew  1341:     my $now = time;
1.35      matthew  1342:     #
                   1343:     my %classlist=&Apache::lonnet::dump('classlist',$cdom,$cnum);
                   1344:     while (my ($student,$info) = each(%classlist)) {
1.60      matthew  1345:         if ($student =~ /^(con_lost|error|no_such_host)/i) {
                   1346:             &Apache::lonnet::logthis('get_classlist error for '.$cid.':'.$student);
                   1347:             return undef;
                   1348:         }
1.35      matthew  1349:         my ($sname,$sdom) = split(/:/,$student);
                   1350:         my @Values = split(/:/,$info);
                   1351:         my ($end,$start,$id,$section,$fullname);
                   1352:         if (@Values > 2) {
                   1353:             ($end,$start,$id,$section,$fullname) = @Values;
                   1354:         } else { # We have to get the data ourselves
                   1355:             ($end,$start) = @Values;
1.37      matthew  1356:             $section = &Apache::lonnet::getsection($sdom,$sname,$cid);
1.35      matthew  1357:             my %info=&Apache::lonnet::get('environment',
                   1358:                                           ['firstname','middlename',
                   1359:                                            'lastname','generation','id'],
                   1360:                                           $sdom, $sname);
                   1361:             my ($tmp) = keys(%info);
                   1362:             if ($tmp =~/^(con_lost|error|no_such_host)/i) {
                   1363:                 $fullname = 'not available';
                   1364:                 $id = 'not available';
1.38      matthew  1365:                 &Apache::lonnet::logthis('unable to retrieve environment '.
                   1366:                                          'for '.$sname.':'.$sdom);
1.35      matthew  1367:             } else {
                   1368:                 $fullname = &ProcessFullName(@info{qw/lastname generation 
                   1369:                                                        firstname middlename/});
                   1370:                 $id = $info{'id'};
                   1371:             }
1.36      matthew  1372:             # Update the classlist with this students information
                   1373:             if ($fullname ne 'not available') {
                   1374:                 my $enrolldata = join(':',$end,$start,$id,$section,$fullname);
                   1375:                 my $reply=&Apache::lonnet::cput('classlist',
                   1376:                                                 {$student => $enrolldata},
                   1377:                                                 $cdom,$cnum);
                   1378:                 if ($reply !~ /^(ok|delayed)/) {
                   1379:                     &Apache::lonnet::logthis('Unable to update classlist for '.
                   1380:                                              'student '.$sname.':'.$sdom.
                   1381:                                              ' error:'.$reply);
                   1382:                 }
                   1383:             }
1.35      matthew  1384:         }
                   1385:         my $status='Expired';
                   1386:         if(((!$end) || $now < $end) && ((!$start) || ($now > $start))) {
                   1387:             $status='Active';
                   1388:         }
                   1389:         $classlist{$student} = 
                   1390:             [$sdom,$sname,$end,$start,$id,$section,$fullname,$status];
                   1391:     }
                   1392:     if (wantarray()) {
                   1393:         return (\%classlist,['domain','username','end','start','id',
                   1394:                              'section','fullname','status']);
                   1395:     } else {
                   1396:         return \%classlist;
                   1397:     }
                   1398: }
                   1399: 
1.1       stredwic 1400: # ----- END HELPER FUNCTIONS --------------------------------------------
                   1401: 
                   1402: 1;
                   1403: __END__
1.36      matthew  1404: 
1.35      matthew  1405: 

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>