[LON-CAPA-cvs] cvs: loncom /interface/statistics lonstudentassessment.pm
matthew
lon-capa-cvs@mail.lon-capa.org
Fri, 05 Dec 2003 21:04:34 -0000
This is a MIME encoded message
--matthew1070658274
Content-Type: text/plain
matthew Fri Dec 5 16:04:34 2003 EDT
Modified files:
/loncom/interface/statistics lonstudentassessment.pm
Log:
Part of Bug 2139: Chart now has a "Grand Total" column output in the
Excel format. The sums on all the sequences are done with formulas, so
changing a students grade on a resource will propagate to their total on
the sequence and in the course. Should go in 1.1.
--matthew1070658274
Content-Type: text/plain
Content-Disposition: attachment; filename="matthew-20031205160434.txt"
Index: loncom/interface/statistics/lonstudentassessment.pm
diff -u loncom/interface/statistics/lonstudentassessment.pm:1.75 loncom/interface/statistics/lonstudentassessment.pm:1.76
--- loncom/interface/statistics/lonstudentassessment.pm:1.75 Thu Nov 20 15:21:17 2003
+++ loncom/interface/statistics/lonstudentassessment.pm Fri Dec 5 16:04:34 2003
@@ -1,6 +1,6 @@
# The LearningOnline Network with CAPA
#
-# $Id: lonstudentassessment.pm,v 1.75 2003/11/20 20:21:17 matthew Exp $
+# $Id: lonstudentassessment.pm,v 1.76 2003/12/05 21:04:34 matthew Exp $
#
# Copyright Michigan State University Board of Trustees
#
@@ -56,6 +56,7 @@
use Apache::lonnet; # for logging porpoises
use Apache::lonlocal;
use Spreadsheet::WriteExcel;
+use Spreadsheet::WriteExcel::Utility();
#######################################################
#######################################################
@@ -859,6 +860,8 @@
my %prog_state; # progress window state
my $request_aborted;
+my $total_formula;
+
sub excel_initialize {
my ($r) = @_;
#
@@ -869,6 +872,7 @@
undef ($cols_output);
undef (%prog_state);
undef ($request_aborted);
+ undef ($total_formula);
#
my $total_columns = scalar(&get_student_fields_to_show());
foreach my $seq (&Apache::lonstatistics::Sequences_with_Assess()) {
@@ -1000,10 +1004,17 @@
}
#
# Add the remaining column headers
+ my $total_formula_string = '=0';
foreach my $seq (&Apache::lonstatistics::Sequences_with_Assess()) {
$excel_sheet->write($rows_output+$row_offset,
$cols_output,$seq->{'title'});
if ($data eq 'tries' || $data eq 'scores') {
+ # Determine starting cell
+ $seq->{'Excel:startcell'}=
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output,$cols_output);
+ $seq->{'Excel:startcol'}=$cols_output;
+ # Put the names of the problems and parts into the sheet
foreach my $res (@{$seq->{'contents'}}) {
next if ($res->{'type'} ne 'assessment');
if (scalar(@{$res->{'parts'}}) > 1) {
@@ -1018,16 +1029,46 @@
$res->{'title'});
}
}
+ # Determine ending cell
+ $seq->{'Excel:endcell'} =
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output,$cols_output-1);
+ $seq->{'Excel:endcol'}=$cols_output-1;
+ # Determine cell the score is held in
+ $seq->{'Excel:scorecell'} =
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output,$cols_output);
+ $seq->{'Excel:scorecol'}=$cols_output;
$excel_sheet->write($rows_output,$cols_output++,'score');
+ # Create the formula for summing up this sequence
+ $seq->{'Excel:sum'}= $excel_sheet->store_formula
+ ('=SUM('.$seq->{'Excel:startcell'}.
+ ':'.$seq->{'Excel:endcell'}.')');
+ #
+ $total_formula_string.='+'.
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output,$cols_output-1);
$excel_sheet->write($rows_output,$cols_output++,'maximum');
- } elsif ($data eq 'sum and total' || $data eq 'parts correct total') {
+ } elsif ($data eq 'sum and total') {
$excel_sheet->write($rows_output+1,$cols_output,'score');
+ $total_formula_string.='+'.
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output+1,$cols_output);
+ $excel_sheet->write($rows_output+1,$cols_output+1,'maximum');
+ $cols_output += 2;
+ } elsif ($data eq 'parts correct total') {
+ $excel_sheet->write($rows_output+1,$cols_output,'parts correct');
+ $total_formula_string.='+'.
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output+1,$cols_output);
$excel_sheet->write($rows_output+1,$cols_output+1,'maximum');
$cols_output += 2;
} else {
$cols_output++;
}
}
+ $excel_sheet->write($rows_output,$cols_output,'Grand Total');
+ $total_formula = $excel_sheet->store_formula($total_formula_string);
#
# Bookkeeping
if ($data eq 'sum and total' || $data eq 'parts correct total') {
@@ -1048,7 +1089,11 @@
}
#
# Add the maximums for each sequence or assessment
+ my %total_cell_translation;
foreach my $seq (&Apache::lonstatistics::Sequences_with_Assess()) {
+ $total_cell_translation{$seq->{'Excel:scorecell'}} =
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output,$seq->{'Excel:scorecol'});
my $weight;
my $max = 0;
foreach my $resource (@{$seq->{'contents'}}) {
@@ -1074,8 +1119,20 @@
if (! ($data eq 'sum only' || $data eq 'parts correct')) {
$excel_sheet->write($rows_output,$cols_output++,'');
}
- $excel_sheet->write($rows_output,$cols_output++,$max);
- }
+ my %replaceCells;
+ $replaceCells{$seq->{'Excel:startcell'}} =
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output,$seq->{'Excel:startcol'});
+ $replaceCells{$seq->{'Excel:endcell'}} =
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output,$seq->{'Excel:endcol'});
+ $excel_sheet->repeat_formula($rows_output,$cols_output++,
+ $seq->{'Excel:sum'},undef,
+ %replaceCells);
+ }
+ $excel_sheet->repeat_formula($rows_output,$cols_output++,
+ $total_formula,undef,
+ %total_cell_translation);
$rows_output++;
#
# Let the user know what we are doing
@@ -1119,7 +1176,13 @@
}
#
# Write out sequence scores and totals data
+ my %total_cell_translation;
foreach my $seq (&Apache::lonstatistics::Sequences_with_Assess()) {
+ # Keep track of cells to translate in total cell
+ $total_cell_translation{$seq->{'Excel:scorecell'}} =
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output,$seq->{'Excel:scorecol'});
+ #
my ($performance,$performance_length,$score,$seq_max,$rawdata);
if ($base eq 'tries') {
($performance,$performance_length,$score,$seq_max,$rawdata) =
@@ -1134,7 +1197,19 @@
foreach my $value (@$rawdata) {
$excel_sheet->write($rows_output,$cols_output++,$value);
}
- $excel_sheet->write($rows_output,$cols_output++,$score);
+ # Write a formula for the sum of this sequence
+ my %replaceCells;
+ $replaceCells{$seq->{'Excel:startcell'}} =
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output,$seq->{'Excel:startcol'});
+ $replaceCells{$seq->{'Excel:endcell'}} =
+ &Spreadsheet::WriteExcel::Utility::xl_rowcol_to_cell
+ ($rows_output,$seq->{'Excel:endcol'});
+ # The undef is for the format
+ $excel_sheet->repeat_formula($rows_output,$cols_output++,
+ $seq->{'Excel:sum'},undef,
+ %replaceCells);
+ #
$excel_sheet->write($rows_output,$cols_output++,$seq_max);
} elsif ($data eq 'sum and total' || $data eq 'sum only' ||
$data eq 'parts correct' || $data eq 'parts correct total') {
@@ -1144,6 +1219,11 @@
$excel_sheet->write($rows_output,$cols_output++,$seq_max);
}
}
+ #
+ $excel_sheet->repeat_formula($rows_output,$cols_output++,
+ $total_formula,undef,
+ %total_cell_translation);
+
#
# Bookkeeping
$rows_output++;
--matthew1070658274--