File:  [LON-CAPA] / loncom / interface / loncoursedata.pm
Revision 1.194: download - view: text, annotated - select for diffs
Fri Mar 1 04:58:08 2013 UTC (11 years, 4 months ago) by raeburn
Branches: MAIN
CVS tags: version_2_11_0_RC1, HEAD
- Records in classlist.db file can contain an additional item (credits), if
  credits are in use in the domain, and the credits earned by the particular
  student are different from the course default.

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

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