[LON-CAPA-cvs] cvs: loncom /interface loncoursedata.pm
matthew
lon-capa-cvs@mail.lon-capa.org
Mon, 02 Feb 2004 19:50:33 -0000
This is a MIME encoded message
--matthew1075751433
Content-Type: text/plain
matthew Mon Feb 2 14:50:33 2004 EDT
Modified files:
/loncom/interface loncoursedata.pm
Log:
Added &get_student_scores(), which returns the sum of student scores in the
course.
Restructured tables:
Removed $studentdata_table and merged its functionality into an expanded
$student_table.
$student_table now includes the students section and status.
Modified get_student_id() to call the new function &populate_student_table
which loads the results of &get_classlist into $student_table.
Added &store_updatetime to encapsulate the table structure a little bit.
--matthew1075751433
Content-Type: text/plain
Content-Disposition: attachment; filename="matthew-20040202145033.txt"
Index: loncom/interface/loncoursedata.pm
diff -u loncom/interface/loncoursedata.pm:1.112 loncom/interface/loncoursedata.pm:1.113
--- loncom/interface/loncoursedata.pm:1.112 Mon Jan 19 11:31:25 2004
+++ loncom/interface/loncoursedata.pm Mon Feb 2 14:50:33 2004
@@ -1,6 +1,6 @@
# The LearningOnline Network with CAPA
#
-# $Id: loncoursedata.pm,v 1.112 2004/01/19 16:31:25 matthew Exp $
+# $Id: loncoursedata.pm,v 1.113 2004/02/02 19:50:33 matthew Exp $
#
# Copyright Michigan State University Board of Trustees
#
@@ -401,21 +401,13 @@
=item $student_table
-The student_table has two columns. The first is a 'student_id' and the second
-is the text description of the 'student' (typically username:domain) (less
-than 100 characters). The 'student_id' is automatically generated by MySQL.
-The use of the name 'student_id' is loaded, I know, but this ID is used ONLY
-internally to the MySQL database and is not the same as the students ID
-(stored in the students environment). This table has its PRIMARY KEY on the
-'student' (100 characters).
-
-=item $studentdata_table
-
-The studentdata_table has four columns: 'student_id' (the unique id of
-the student), 'updatetime' (the time the students data was last updated),
-'fullupdatetime' (the time the students full data was last updated),
-'section', and 'classification'( the students current classification).
-This table has its PRIMARY KEY on 'student_id'.
+The student_table has 7 columns. The first is a 'student_id' assigned by
+MySQL. The second is 'student' which is username:domain. The third through
+fifth are 'section', 'status' (enrollment status), and 'classification'
+(to be used in the future). The sixth and seventh ('updatetime' and
+'fullupdatetime') contain the time of last update and full update of student
+data. This table has its PRIMARY KEY on the 'student_id' column and is indexed
+on 'student', 'section', and 'status'.
=back
@@ -521,7 +513,6 @@
my $symb_table;
my $part_table;
my $student_table;
-my $studentdata_table;
my $performance_table;
my $parameters_table;
my $fulldump_response_table;
@@ -597,30 +588,24 @@
auto_inc => 'yes', },
{ name => 'student',
type => 'VARCHAR(100)',
+ restrictions => 'NOT NULL UNIQUE'},
+ { name => 'section',
+ type => 'VARCHAR(100)',
+ restrictions => 'NOT NULL'},
+ { name => 'status',
+ type => 'VARCHAR(15)',
restrictions => 'NOT NULL'},
{ name => 'classification',
type => 'varchar(100)', },
- ],
- 'PRIMARY KEY' => ['student (100)'],
- 'KEY' => [{ columns => ['student_id']},],
- };
- #
- my $studentdata_table_def = {
- id => $studentdata_table,
- permanent => 'no',
- columns => [{ name => 'student_id',
- type => 'MEDIUMINT UNSIGNED',
- restrictions => 'NOT NULL UNIQUE',},
{ name => 'updatetime',
type => 'INT UNSIGNED'},
{ name => 'fullupdatetime',
type => 'INT UNSIGNED'},
- { name => 'section',
- type => 'VARCHAR(100)'},
- { name => 'classification',
- type => 'VARCHAR(100)', },
],
'PRIMARY KEY' => ['student_id'],
+ 'KEY' => [{ columns => ['student (100)',
+ 'section (100)',
+ 'status (15)',]},],
};
#
my $performance_table_def = {
@@ -798,13 +783,6 @@
return 3;
}
#
- $tableid = &Apache::lonmysql::create_table($studentdata_table_def);
- if (! defined($tableid)) {
- &Apache::lonnet::logthis("error creating studentdata_table: ".
- &Apache::lonmysql::get_error());
- return 4;
- }
- #
$tableid = &Apache::lonmysql::create_table($performance_table_def);
if (! defined($tableid)) {
&Apache::lonnet::logthis("error creating preformance_table: ".
@@ -1041,9 +1019,9 @@
$have_read_student_table = 1;
}
if (! exists($ids_by_student{$student})) {
- &Apache::lonmysql::store_row($student_table,
- [undef,$student,undef]);
+ &populate_student_table();
undef(%ids_by_student);
+ undef(%students_by_id);
my @Result = &Apache::lonmysql::get_rows($student_table);
foreach (@Result) {
$ids_by_student{$_->[1]}=$_->[0];
@@ -1067,6 +1045,39 @@
return undef; # error
}
+sub populate_student_table {
+ my ($courseid) = @_;
+ if (! defined($courseid)) {
+ $courseid = $ENV{'request.course.id'};
+ }
+ #
+ &setup_table_names($courseid);
+ my $dbh = &Apache::lonmysql::get_dbh();
+ my $request = 'INSERT IGNORE INTO '.$student_table.
+ "(student,section,status) VALUES ";
+ my $classlist = &get_classlist($courseid);
+ my $student_count=0;
+ while (my ($student,$data) = each %$classlist) {
+ my ($section,$status) = ($data->[&CL_SECTION()],
+ $data->[&CL_STATUS()]);
+ if ($section eq '' || $section =~ /^\s*$/) {
+ $section = 'none';
+ }
+ $request .= "('".$student."','".$section."','".$status."'),";
+ $student_count++;
+ }
+ return if ($student_count == 0);
+ chop($request);
+ $dbh->do($request);
+ if ($dbh->err()) {
+ &Apache::lonnet::logthis("error ".$dbh->errstr().
+ " occured executing \n".
+ $request);
+ }
+ return;
+}
+
+
################################################
################################################
@@ -1125,8 +1136,7 @@
Once the "fulldump" tables are updated, the tables used for chart and
spreadsheet (which hold only the current state of the student on their
homework, not historical data) are updated. If all updates have occured
-successfully, the studentdata table is updated to reflect the time of the
-update.
+successfully, $student_table is updated to reflect the time of the update.
Notice we do not insert the data and immediately query it. This means it
is possible for there to be data returned this first time that is not
@@ -1321,9 +1331,14 @@
##
## Update the students time......
if ($returnstatus eq 'okay') {
- &Apache::lonmysql::replace_row
- ($studentdata_table,
- [$student_id,$time_of_retrieval,$time_of_retrieval,undef,undef]);
+ &store_updatetime($student_id,$time_of_retrieval,$time_of_retrieval);
+ if ($dbh->err) {
+ if ($returnstatus eq 'okay') {
+ $returnstatus = 'error updating student time';
+ } else {
+ $returnstatus = 'error updating student time';
+ }
+ }
}
return $returnstatus;
}
@@ -1391,13 +1406,31 @@
#
# Set the students update time
if ($Results[0] eq 'okay') {
- &Apache::lonmysql::replace_row($studentdata_table,
- [$student_id,$time_of_retrieval,undef,undef,undef]);
+ &store_updatetime($student_id,$time_of_retrieval,$time_of_retrieval);
}
#
return @Results;
}
+sub store_updatetime {
+ my ($student_id,$updatetime,$fullupdatetime)=@_;
+ my $values = '';
+ if (defined($updatetime)) {
+ $values = 'updatetime='.$updatetime.' ';
+ }
+ if (defined($fullupdatetime)) {
+ if ($values ne '') {
+ $values .= ',';
+ }
+ $values .= 'fullupdatetime='.$fullupdatetime.' ';
+ }
+ return if ($values eq '');
+ my $dbh = &Apache::lonmysql::get_dbh();
+ my $request = 'UPDATE '.$student_table.' SET '.$values.
+ ' WHERE student_id='.$student_id.' LIMIT 1';
+ $dbh->do($request);
+}
+
sub store_student_data {
my ($sname,$sdom,$courseid,$student_data) = @_;
#
@@ -1517,21 +1550,20 @@
#
# if the tables do not exist, make them
my @CurrentTable = &Apache::lonmysql::tables_in_db();
- my ($found_symb,$found_student,$found_part,$found_studentdata,
+ my ($found_symb,$found_student,$found_part,
$found_performance,$found_parameters,$found_fulldump_part,
$found_fulldump_response,$found_fulldump_timestamp);
foreach (@CurrentTable) {
$found_symb = 1 if ($_ eq $symb_table);
$found_student = 1 if ($_ eq $student_table);
$found_part = 1 if ($_ eq $part_table);
- $found_studentdata = 1 if ($_ eq $studentdata_table);
$found_performance = 1 if ($_ eq $performance_table);
$found_parameters = 1 if ($_ eq $parameters_table);
$found_fulldump_part = 1 if ($_ eq $fulldump_part_table);
$found_fulldump_response = 1 if ($_ eq $fulldump_response_table);
$found_fulldump_timestamp = 1 if ($_ eq $fulldump_timestamp_table);
}
- if (!$found_symb || !$found_studentdata ||
+ if (!$found_symb ||
!$found_student || !$found_part ||
!$found_performance || !$found_parameters ||
!$found_fulldump_part || !$found_fulldump_response ||
@@ -1554,7 +1586,7 @@
Output: $status, $data
This routine ensures the data for a given student is up to date.
-The $studentdata_table is queried to determine the time of the last update.
+The $student_table is queried to determine the time of the last update.
If the students data is out of date, &update_student_data() is called.
The return values from the call to &update_student_data() are returned.
@@ -1576,11 +1608,11 @@
$Apache::lonnet::perlvar{'lonUsersDir'});
#
my $student_id = &get_student_id($sname,$sdom);
- my @Result = &Apache::lonmysql::get_rows($studentdata_table,
+ my @Result = &Apache::lonmysql::get_rows($student_table,
"student_id ='$student_id'");
my $data = undef;
if (@Result) {
- $updatetime = $Result[0]->[1];
+ $updatetime = $Result[0]->[5]; # Ack! This is dumb!
}
if ($modifiedtime > $updatetime) {
($status,$data) = &update_student_data($sname,$sdom,$courseid);
@@ -1601,7 +1633,7 @@
This routine ensures the fulldata (the data from a lonnet::dump, not a
lonnet::currentdump) for a given student is up to date.
-The $studentdata_table is queried to determine the time of the last update.
+The $student_table is queried to determine the time of the last update.
If the students fulldata is out of date, &update_full_student_data() is
called.
@@ -1624,11 +1656,11 @@
$Apache::lonnet::perlvar{'lonUsersDir'});
#
my $student_id = &get_student_id($sname,$sdom);
- my @Result = &Apache::lonmysql::get_rows($studentdata_table,
+ my @Result = &Apache::lonmysql::get_rows($student_table,
"student_id ='$student_id'");
my $updatetime;
if (@Result && ref($Result[0]) eq 'ARRAY') {
- $updatetime = $Result[0]->[2];
+ $updatetime = $Result[0]->[6];
}
if (! defined($updatetime) || $modifiedtime > $updatetime) {
$status = &update_full_student_data($sname,$sdom,$courseid);
@@ -2132,6 +2164,73 @@
=pod
+=item &get_student_scores($Students,$Symbs,$courseid)
+
+=cut
+
+################################################
+################################################
+sub get_student_scores {
+ my ($Students,$Symbs,$courseid) = @_;
+ $courseid = $ENV{'request.course.id'} if (! defined($courseid));
+ &setup_table_names($courseid);
+ my $dbh = &Apache::lonmysql::get_dbh();
+ return (undef) if (! defined($dbh));
+ my $tmptable = $courseid.'_temp_'.time;
+ my ($symb_requirements,$student_requirements);
+ if (defined($Symbs) && @$Symbs) {
+ $symb_requirements = '('.
+ join(' OR ', map{ "(symb_id='".&get_symb_id($_->{'symb'}).
+ "' AND part_id='".&get_part_id($_->{'part'}).
+ "')"
+ } @$Symbs).')';
+ }
+ if (defined($Students)) {
+ $student_requirements = '('.
+ join(' OR ', map {'student_id='.
+ &get_student_id($_->{'username'},
+ $_->{'domain'})
+ } @$Students
+ ).')';
+ }
+ my $request = 'CREATE TEMPORARY TABLE IF NOT EXISTS '.$tmptable.
+ ' SELECT student_id,SUM(awarded) AS score FROM '.$performance_table;
+ if (defined($symb_requirements) || defined($student_requirements)) {
+ $request .= ' WHERE ';
+ if (defined($symb_requirements)) {
+ $request .= $symb_requirements;
+ if (defined($student_requirements)) {
+ $request .= ' AND '.$student_requirements;
+ }
+ } elsif (defined($student_requirements)) {
+ $request .= $student_requirements;
+ }
+ }
+ $request .= ' GROUP BY student_id';
+ &Apache::lonnet::logthis("request = \n".$request);
+ my $sth = $dbh->prepare($request);
+ $sth->execute();
+ if ($dbh->err) {
+ &Apache::lonnet::logthis('error = '.$dbh->errstr());
+ return undef;
+ }
+ $request = 'SELECT score,COUNT(*) FROM '.$tmptable.' GROUP BY score';
+# &Apache::lonnet::logthis("request = \n".$request);
+ $sth = $dbh->prepare($request);
+ $sth->execute();
+ if ($dbh->err) {
+ &Apache::lonnet::logthis('error = '.$dbh->errstr());
+ return undef;
+ }
+ my $dataset = $sth->fetchall_arrayref();
+ return $dataset;
+}
+
+################################################
+################################################
+
+=pod
+
=item &setup_table_names()
input: course id
@@ -2170,7 +2269,6 @@
$symb_table = $base_id.'_'.'symb';
$part_table = $base_id.'_'.'part';
$student_table = $base_id.'_'.'student';
- $studentdata_table = $base_id.'_'.'studentdata';
$performance_table = $base_id.'_'.'performance';
$parameters_table = $base_id.'_'.'parameters';
$fulldump_part_table = $base_id.'_'.'partdata';
@@ -2181,7 +2279,6 @@
$symb_table,
$part_table,
$student_table,
- $studentdata_table,
$performance_table,
$parameters_table,
$fulldump_part_table,
--matthew1075751433--