loncom/interface/loncoursedata.pm - view

File: [LON-CAPA] / loncom / interface / loncoursedata.pm
Revision 1.133: download - view: text, annotated - select for diffs
Thu May 13 13:55:35 2004 UTC (20 years, 1 month ago) by matthew
Branches: MAIN
CVS tags: HEAD

Bug 3007: Degree of Difficulty should not be negative.  #YES (aka $Solved)
was being computed as the sum of the awards on the problem.  Now it is
computed as the sum of the awards on the problem minus the sum of the
awards given by override.

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