Return to studentcalc.pm CVS log

Up to [LON-CAPA] / loncom / interface / spreadsheet

Nearly complete reworking of spreadsheet.

lonspreadsheet.pm holds a few utility functions and the handler, which
creates a spreadsheet and tells it to display itself.

Spreadsheet.pm is the base definition of the spreadsheet object.
classcalc.pm, studentcalc.pm, and assesscalc.pm are implementations of the
spreadsheets.

There are missing pieces - excel and csv output, limiting by section
permissions, the ability to add extra 'header' rows to the student and
course level sheets, and students are still allowed to view the assessment
level sheets (will disable this soon).

Computing and editing of the spreadsheet have been tested and have been
given a preliminary seal of approval.

#
# $Id: studentcalc.pm,v 1.1 2003/05/16 20:55:11 matthew Exp $
#
# Copyright Michigan State University Board of Trustees
#
# This file is part of the LearningOnline Network with CAPA (LON-CAPA).
#
# LON-CAPA is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# LON-CAPA is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with LON-CAPA; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
# /home/httpd/html/adm/gpl.txt
#
# http://www.lon-capa.org/
#
# The LearningOnline Network with CAPA
# Spreadsheet/Grades Display Handler
#
# POD required stuff:

=head1 NAME

studentcalc

=head1 SYNOPSIS

=head1 DESCRIPTION

=over 4

=cut

###################################################
###                 StudentSheet                ###
###################################################
package Apache::studentcalc;

use strict;
use Apache::Constants qw(:common :http);
use Apache::lonnet;
use Apache::lonnavmaps;
use Apache::Spreadsheet;
use Apache::assesscalc;
use HTML::Entities();
use Spreadsheet::WriteExcel;
use Time::HiRes;

@Apache::studentcalc::ISA = ('Apache::Spreadsheet');

my @Sequences = ();
my %Exportrows = ();

my $current_course;

sub initialize_package {
    $current_course = $ENV{'request.course.id'};
    &initialize_sequence_cache();
    &load_cached_export_rows();
}

sub initialize_sequence_cache {
    #
    # Set up the sequences and assessments
    @Sequences = ();
    my ($top,$sequences,$assessments) = 
        &Apache::loncoursedata::get_sequence_assessment_data();
    if (! defined($top) || ! ref($top)) {
        # There has been an error, better report it
        &Apache::lonnet::logthis('top is undefined');
        return;
    }
    @Sequences = @{$sequences} if (ref($sequences) eq 'ARRAY');
}

sub clear_package {
    @Sequences = undef;
    %Exportrows = undef;
}

sub get_title {
    my $self = shift;
    my $title = '';
    $title .= '<h2>Spreadsheet for '.
        $self->{'name'}.'@'.$self->{'domain'}.'</h2>';
    return $title;
}

sub parent_link {
    my $self = shift;
    my $link .= '<p><a href="/adm/classcalc?'.
        'sname='.$self->{'name'}.
            '&sdomain='.$self->{'domain'}.'">'.
                'Course level sheet</a></p>'."\n";
    return $link;
}

sub outsheet_html {
    my $self = shift;
    my ($r) = @_;
    ####################################
    # Get the list of assessment files #
    ####################################
    my @AssessFileNames = $self->othersheets('assesscalc');
    ####################################
    # Determine table structure        #
    ####################################
    my $num_uneditable = 26;
    my $num_left = 52-$num_uneditable;
    my $tableheader =<<"END";
<table border="2">
<tr>
  <th colspan="2" rowspan="2"><font size="+2">Student</font></th>
  <td bgcolor="#FFDDDD" colspan="$num_uneditable">
      <b><font size="+1">Import</font></b></td>
  <td colspan="$num_left">
      <b><font size="+1">Calculations</font></b></td>
</tr><tr>
END
    my $label_num = 0;
    foreach (split(//,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')){
        if ($label_num<$num_uneditable) { 
            $tableheader .='<td bgcolor="#FFDDDD">';
        } else {
            $tableheader .='<td>';
        }
        $tableheader .="<b><font size=+1>$_</font></b></td>";
        $label_num++;
    }
    $tableheader .="</tr>\n";
    #
    $r->print($tableheader);
    #
    # Print out template row
    $r->print('<tr><td>Template</td><td>&nbsp;</td>'.
	      $self->html_template_row($num_uneditable)."</tr>\n");
    #
    # Print out summary/export row
    $r->print('<tr><td>Export</td><td>0</td>'.
	      $self->html_export_row()."</tr>\n");
    $r->print("</table>\n");
    #
    # Prepare to output rows
    $tableheader =<<"END";
<table border="2">
<tr><th>Row</th><th>Assessment</th>
END
    foreach (split(//,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')){
	if ($label_num<$num_uneditable) { 
            $tableheader.='<td bgcolor="#FFDDDD">';
        } else {
            $tableheader.='<td>';
        }
        $tableheader.="<b><font size=+1>$_</font></b></td>";
    }
    $tableheader.="\n";
    #
    my $num_output = 1;
    if (scalar(@Sequences)< 1) {
        &initialize_sequence_cache();
    }
    foreach my $Sequence (@Sequences) {
	next if ($Sequence->{'num_assess'} < 1);
	$r->print("<h3>".$Sequence->{'title'}."</h3>\n");
 	$r->print($tableheader);
	foreach my $resource (@{$Sequence->{'contents'}}) {
	    next if ($resource->{'type'} ne 'assessment');
	    my $rownum = $self->get_row_number_from_key($resource->{'symb'});
            my $assess_filename = $self->{'row_source'}->{$rownum};
	    my $row_output = '<tr><td>'.$rownum.'</td>';
            $row_output .= '<td>'.
                '<a href="/adm/assesscalc?sname='.$self->{'name'}.
                '&sdomain='.$self->{'domain'}.
                '&filename='.$assess_filename.
                '&usymb='.$resource->{'symb'}.'">'.
                $resource->{'title'}.'</a>';
            $row_output .= &assess_file_selector($rownum,
                                                 $assess_filename,
                                                 \@AssessFileNames);
            $row_output .= '</td>'.$self->html_row($num_uneditable,$rownum).
                "</tr>\n";
            $r->print($row_output);
	}
	$r->print("</table>\n");
    }
    return;
}

########################################################
########################################################

=pod

=item &assess_file_selector()

=cut

########################################################
########################################################
sub assess_file_selector {
    my ($row,$default,$AssessFiles)=@_;
    return '' if (!defined($AssessFiles) || ! @$AssessFiles);
    return '' if (! &Apache::lonnet::allowed('mgr',$ENV{'request.course.id'}));
    my $element_name = 'FileSelect_'.$row;
    my $load_dialog = '<select size="1" name="'.$element_name.'" '.
        'onchange="'.
        "document.sheet.cell.value='source_$row';".
        "document.sheet.newformula.value=document.sheet.$element_name\.value;".
        'document.sheet.submit()" '.'>'."\n";
    foreach my $file (@{$AssessFiles}) {
        $load_dialog .= '    <option name="'.$file.'"';
        $load_dialog .= ' selected' if ($default eq $file);
        $load_dialog .= '>'.$file."</option>\n";
    }
    $load_dialog .= "</select>\n";
    return $load_dialog;
}

sub modify_cell {
    my $self = shift;
    my ($cell,$formula) = @_;
    if ($cell =~ /^source_(\d+)$/) {
        # Need to make sure $formula is a valid filename....
        my $row = $1;
        $cell = 'A'.$row;
        $self->{'row_source'}->{$row} = $formula;
        my $original_source = $self->formula($cell);
        if ($original_source =~ /__&&&__/) {
            ($original_source,undef) = split('__&&&__',$original_source);
        }
        $formula = $original_source.'__&&&__'.$formula;
    } elsif ($cell =~ /([A-z])\-/) {
        $cell = 'template_'.$1;
    } elsif ($cell !~ /^([A-z](\d+)|template_[A-z])$/) {
        return;
    }
    $self->set_formula($cell,$formula);
    $self->rebuild_stats();
    return;
}

sub outsheet_csv {
    my $self = shift;
    my ($r) = @_;
}
sub outsheet_excel {
    my $self = shift;
    my ($r) = @_;
}
sub outsheet_recursive_excel {
    my $self = shift;
    my ($r) = @_;
} 

sub display {
    my $self = shift;
    my ($r) = @_;
    $self->compute();
    $self->outsheet_html($r);
    return;
}

sub set_row_sources {
    my $self = shift;
    while (my ($cell,$value) = each(%{$self->{'formulas'}})) {
        next if ($cell !~ /^A(\d+)/ && $1 > 0);
        my $row = $1;
        (undef,$value) = split('__&&&__',$value);
        $value = 'Default' if (! defined($value));
        $self->{'row_source'}->{$row} = $value;
    }
    return;
}

sub compute {
    my $self = shift;
    $self->logthis('computing');
    if (! defined($current_course) ||
        $current_course ne $ENV{'request.course.id'}) {
        $current_course = $ENV{'request.course.id'};
        &clear_package();
        &initialize_sequence_cache();
    }
    $self->initialize_safe_space();
    my @sequences = @Sequences;
    if (@sequences < 1) {
        my ($top,$sequences,$assessments) = 
            &Apache::loncoursedata::get_sequence_assessment_data();
        if (! defined($top) || ! ref($top)) {
            &Apache::lonnet::logthis('top is undefined');
            return;
        }
        @sequences = @{$sequences} if (ref($sequences) eq 'ARRAY');
    }
    &Apache::assesscalc::initialize_package($self->{'name'},$self->{'domain'});
    my %f = $self->formulas();
    #
    # Process the formulas list - 
    #   the formula for the A column of a row is symb__&&__filename
    my %c = $self->constants();
    foreach my $seq (@sequences) {
        next if ($seq->{'num_assess'}<1);
        foreach my $resource (@{$seq->{'contents'}}) {
            next if ($resource->{'type'} ne 'assessment');
            my $rownum = $self->get_row_number_from_key($resource->{'symb'});
            my $cell = 'A'.$rownum;
            my $assess_filename = 'Default';
            if (exists($self->{'row_source'}->{$rownum})) {
                $assess_filename = $self->{'row_source'}->{$rownum};
            } else {
                $self->{'row_source'}->{$rownum} = $assess_filename;
            }
            $f{$cell} = $resource->{'symb'}.'__&&&__'.$assess_filename;
            my $assessSheet = Apache::assesscalc->new($self->{'name'},
                                                      $self->{'domain'},
                                                      $assess_filename,
                                                      $resource->{'symb'});
            my @exportdata = $assessSheet->export_data();
            #
            # Be sure not to disturb the formulas in the 'A' column
            my $data = shift(@exportdata);
            $c{$cell} = $data if (defined($data));
            #
            # Deal with the remaining columns
            my $i=0;
            foreach (split(//,'BCDEFGHIJKLMNOPQRSTUVWXYZ')) {
                my $cell = $_.$rownum;
                my $data = shift(@exportdata);
                if (defined($data)) {
                    $f{$cell} = 'import';
                    $c{$cell} = $data;
                }
                $i++;
            }
        }
    }
    $self->constants(\%c);
    $self->formulas(\%f);
    $self->calcsheet();
    #
    # Store export row in cache
    my @exportarray=$self->exportrow();
    my $student = $self->{'name'}.':'.$self->{'domain'};
    $Exportrows{$student}->{'time'} = time;
    $Exportrows{$student}->{'data'} = \@exportarray;
    # save export row
    $self->save_export_data();
    return;
}

sub set_row_numbers {
    my $self = shift;
    while (my ($cell,$formula) = each(%{$self->{'formulas'}})) {
        next if ($cell !~ /^A(\d+)/);
        my $row = $1;
        next if ($row == 0);
        my ($symb,undef) = split('__&&&__',$formula);
        $self->{'row_numbers'}->{$symb} = $row;
    }
}

sub get_row_number_from_symb {
    my $self = shift;
    my ($key) = @_;
    ($key,undef) = split('__&&&__',$key) if ($key =~ /__&&&__/);
    return $self->get_row_number_from_key($key);
}

#############################################
#############################################

=pod

=item &load_cached_export_rows

Retrieves and parsers the export rows of the student spreadsheets.
These rows are saved in the courses directory in the format:

 sname:sdom:studentcalc:.time => time

 sname:sdom:studentcalc => ___=___Adata___;___Bdata___;___Cdata___;___ .....

=cut

#############################################
#############################################
sub load_cached_export_rows {
    %Exportrows = undef;
    my @tmp = &Apache::lonnet::dump('nohist_calculatedsheets',
		     $ENV{'course.'.$ENV{'request.course.id'}.'.domain'},
		     $ENV{'course.'.$ENV{'request.course.id'}.'.num'},undef);
    my %Selected_Assess_Sheet;
    if ($tmp[0] =~ /^error/) {
        &Apache::lonnet::logthis('unable to read cached student export rows '.
                                 'for course '.$ENV{'request.course.id'});
        return;
    }
    my %tmp = @tmp;
    while (my ($key,$sheetdata) = each(%tmp)) {
        my ($sname,$sdom,$sheettype,$remainder) = split(':',$key);
        my $student = $sname.':'.$sdom;
        if ($remainder =~ /\.time/) {
            $Exportrows{$student}->{'time'} = $sheetdata;
        } else {
            $sheetdata =~ s/^___=___//;
            my @Data = split('___;___',$sheetdata);
            $Exportrows{$student}->{'data'} = \@Data;
        }
    }
}

#############################################
#############################################

=pod

=item &save_export_data()

Writes the export data for this student to the course cache.

=cut

#############################################
#############################################
sub save_export_data {
    my $self = shift;
    my $student = $self->{'name'}.':'.$self->{'domain'};
    return if (! exists($Exportrows{$student}));
    return if (! $self->is_default());
    my $key = join(':',($self->{'name'},$self->{'domain'},'studentcalc')).':';
    my $timekey = $key.'.time';
    my $newstore = join('___;___',
                        @{$Exportrows{$student}->{'data'}});
    $newstore = '___=___'.$newstore;
    &Apache::lonnet::put('nohist_calculatedsheets',
                         { $key     => $newstore,
                           $timekey => $Exportrows{$student}->{'time'} },
                         $self->{'cdom'},
                         $self->{'cnum'});
    return;
}

#############################################
#############################################

=pod

=item &export_data()

Returns the export data associated with the spreadsheet.  Computes the
spreadsheet only if necessary.

=cut

#############################################
#############################################
sub export_data {
    my $self = shift;
    my $student = $self->{'name'}.':'.$self->{'domain'};
    if (! exists($Exportrows{$student}) ||
        ! $self->check_expiration_time($Exportrows{$student}->{'time'})) {
        $self->compute();
    }
    my @Data = @{$Exportrows{$student}->{'data'}};
    for (my $i=0; $i<=$#Data;$i++) {
        $Data[$i]="'".$Data[$i]."'" if ($Data[$i]=~/\D/ && defined($Data[$i]));
    }
    return @Data;
}

1;

__END__