Annotation of loncom/interface/spreadsheet/Spreadsheet.pm, revision 1.73

1.1       matthew     1: #
1.73    ! albertel    2: # $Id: Spreadsheet.pm,v 1.72 2006/09/03 00:54:05 albertel Exp $
1.1       matthew     3: #
                      4: # Copyright Michigan State University Board of Trustees
                      5: #
                      6: # This file is part of the LearningOnline Network with CAPA (LON-CAPA).
                      7: #
                      8: # LON-CAPA is free software; you can redistribute it and/or modify
                      9: # it under the terms of the GNU General Public License as published by
                     10: # the Free Software Foundation; either version 2 of the License, or
                     11: # (at your option) any later version.
                     12: #
                     13: # LON-CAPA is distributed in the hope that it will be useful,
                     14: # but WITHOUT ANY WARRANTY; without even the implied warranty of
                     15: # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
                     16: # GNU General Public License for more details.
                     17: #
                     18: # You should have received a copy of the GNU General Public License
                     19: # along with LON-CAPA; if not, write to the Free Software
                     20: # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
                     21: #
                     22: # /home/httpd/html/adm/gpl.txt
                     23: #
                     24: # http://www.lon-capa.org/
                     25: #
                     26: # The LearningOnline Network with CAPA
                     27: # Spreadsheet/Grades Display Handler
                     28: #
                     29: # POD required stuff:
                     30: 
                     31: =head1 NAME
                     32: 
                     33: Spreadsheet
                     34: 
                     35: =head1 SYNOPSIS
                     36: 
                     37: =head1 DESCRIPTION
                     38: 
                     39: =over 4
                     40: 
                     41: =cut
                     42: 
                     43: ###################################################
                     44: ###################################################
                     45: ###                 Spreadsheet                 ###
                     46: ###################################################
                     47: ###################################################
                     48: package Apache::Spreadsheet;
                     49: 
                     50: use strict;
1.24      matthew    51: #use warnings FATAL=>'all';
                     52: #no warnings 'uninitialized';
1.1       matthew    53: use Apache::Constants qw(:common :http);
                     54: use Apache::lonnet;
                     55: use Safe;
                     56: use Safe::Hole;
                     57: use Opcode;
                     58: use HTML::Entities();
                     59: use HTML::TokeParser;
                     60: use Spreadsheet::WriteExcel;
                     61: use Time::HiRes;
1.28      www        62: use Apache::lonlocal;
1.69      www        63: use lib '/home/httpd/lib/perl/';
                     64: use LONCAPA;
                     65:  
1.1       matthew    66: 
                     67: ##
                     68: ## Package Variables
                     69: ##
                     70: my %expiredates;
                     71: 
                     72: my @UC_Columns = split(//,'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
                     73: my @LC_Columns = split(//,'abcdefghijklmnopqrstuvwxyz');
                     74: 
                     75: ######################################################
                     76: 
                     77: =pod
                     78: 
                     79: =item &new
                     80: 
                     81: Returns a new spreadsheet object.
                     82: 
                     83: =cut
                     84: 
                     85: ######################################################
                     86: sub new {
                     87:     my $this = shift;
                     88:     my $class = ref($this) || $this;
                     89:     my ($stype) = ($class =~ /Apache::(.*)$/);
                     90:     #
1.68      raeburn    91:     my ($name,$domain,$filename,$usymb,$section,$groups)=@_;
1.39      matthew    92:     if (defined($usymb) && ref($usymb)) {
                     93:         $usymb = $usymb->symb;
                     94:     }
1.36      matthew    95:     if (! defined($name) || $name eq '') {
1.41      albertel   96:         $name = $env{'user.name'};
1.36      matthew    97:     }
                     98:     if (! defined($domain) || $domain eq '') {
1.41      albertel   99:         $domain = $env{'user.domain'};
1.36      matthew   100:     }
1.66      albertel  101:     if (! defined($section) || $section eq '') {
                    102:         $section = &Apache::lonnet::getsection($domain,$name,
                    103: 					       $env{'request.course.id'});
                    104:     }
1.68      raeburn   105:     if (! defined($groups)) {
                    106: 
                    107:         my @usersgroups = &Apache::lonnet::get_users_groups($domain,$name,
1.67      raeburn   108:                                                     $env{'request.course.id'});
1.68      raeburn   109:         $groups = \@usersgroups;
1.67      raeburn   110:     }
1.1       matthew   111:     #
                    112:     my $self = {
                    113:         name     => $name,
                    114:         domain   => $domain,
1.66      albertel  115:         section  => $section,
1.68      raeburn   116:         groups   => $groups, 
1.1       matthew   117:         type     => $stype,
                    118:         symb     => $usymb,
                    119:         errorlog => '',
1.22      matthew   120:         maxrow   => 0,
1.41      albertel  121:         cid      => $env{'request.course.id'},
                    122:         cnum     => $env{'course.'.$env{'request.course.id'}.'.num'},
                    123:         cdom     => $env{'course.'.$env{'request.course.id'}.'.domain'},
                    124:         coursedesc => $env{'course.'.$env{'request.course.id'}.'.description'},
                    125:         coursefilename => $env{'request.course.fn'},
1.12      matthew   126:         #
                    127:         # Flags
                    128:         temporary => 0,  # true if this sheet has been modified but not saved
1.49      albertel  129:         new_rows  => 0,  # true if this sheet has new rows
                    130: 	loaded    => 0,  # true if the formulas have been loaded
1.1       matthew   131:         #
1.3       matthew   132:         # blackout is used to determine if any data needs to be hidden from the
                    133:         # student.
                    134:         blackout => 0,
                    135:         #
1.1       matthew   136:         # Data storage
                    137:         formulas    => {},
                    138:         constants   => {},
                    139:         rows        => [],
                    140:         row_source  => {}, 
                    141:         othersheets => [],
                    142:     };
                    143:     #
                    144:     bless($self,$class);
1.50      albertel  145:     $self->filename($filename);
1.1       matthew   146:     #
                    147:     return $self;
                    148: }
                    149: 
                    150: ######################################################
                    151: 
                    152: =pod
                    153: 
                    154: =item &filename
                    155: 
                    156: get or set the filename for a spreadsheet.
                    157: 
                    158: =cut
                    159: 
                    160: ######################################################
                    161: sub filename {
                    162:     my $self = shift();
                    163:     if (@_) {
                    164:         my ($newfilename) = @_;
                    165:         if (! defined($newfilename) || $newfilename eq 'Default' ||
1.13      matthew   166:             $newfilename !~ /\w/ || $newfilename eq '') {
                    167:             my $key = 'course.'.$self->{'cid'}.'.spreadsheet_default_'.
                    168:                 $self->{'type'};
1.41      albertel  169:             if (exists($env{$key}) && $env{$key} ne '') {
                    170:                 $newfilename = $env{$key};
1.13      matthew   171:             } else {
                    172:                 $newfilename = 'default_'.$self->{'type'};
1.1       matthew   173:             }
1.13      matthew   174:         }
1.71      albertel  175: 	if ($newfilename eq &mt('LON-CAPA Standard')) {
                    176: 	    undef($newfilename);
                    177: 	} else {
                    178: 	    if ($newfilename !~ /\w/ || $newfilename =~ /^\W*$/) {
                    179: 		$newfilename = 'default_'.$self->{'type'};
                    180: 	    }
                    181: 	    if ($newfilename !~ /^default\.$self->{'type'}$/ &&
                    182: 		$newfilename !~ /^\/res\/(.*)spreadsheet$/) {
                    183: 		if ($newfilename !~ /_$self->{'type'}$/) {
                    184: 		    $newfilename =~ s/[\s_]*$//;
                    185: 		    $newfilename .= '_'.$self->{'type'};
                    186: 		}
                    187: 	    }
                    188: 	}
1.1       matthew   189:         $self->{'filename'} = $newfilename;
                    190:         return;
                    191:     }
                    192:     return $self->{'filename'};
                    193: }
                    194: 
                    195: ######################################################
                    196: 
                    197: =pod
                    198: 
                    199: =item &make_default()
                    200: 
                    201: Make the current spreadsheet file the default for the course.  Expires all the
                    202: default spreadsheets.......!
                    203: 
                    204: =cut
                    205: 
                    206: ######################################################
                    207: sub make_default {
                    208:     my $self = shift();
                    209:     my $result = &Apache::lonnet::put('environment',
1.13      matthew   210:             {'spreadsheet_default_'.$self->{'type'} => $self->filename()},
1.1       matthew   211:                                      $self->{'cdom'},$self->{'cnum'});
                    212:     return $result if ($result ne 'ok');
1.55      albertel  213:     &Apache::lonnet::appenv('course.'.$self->{'cid'}.'.spreadsheet_default_'.
                    214: 			    $self->{'type'} => $self->filename());
1.1       matthew   215:     my $symb = $self->{'symb'};
                    216:     $symb = '' if (! defined($symb));
                    217:     &Apache::lonnet::expirespread('','',$self->{'type'},$symb);    
                    218: }
                    219: 
                    220: ######################################################
                    221: 
                    222: =pod
                    223: 
                    224: =item &is_default()
                    225: 
                    226: Returns 1 if the current spreadsheet is the default as specified in the
                    227: course environment.  Returns 0 otherwise.
                    228: 
                    229: =cut
                    230: 
                    231: ######################################################
                    232: sub is_default {
                    233:     my $self = shift;
                    234:     # Check to find out if we are the default spreadsheet (filenames match)
1.56      albertel  235:     my $default_filename = $env{'course.'.$self->{'cid'}.
                    236: 				    '.spreadsheet_default_'.$self->{'type'}};
1.13      matthew   237:     if ($default_filename =~ /^\s*$/) {
                    238:         $default_filename = 'default_'.$self->{'type'};
                    239:     }
1.1       matthew   240:     return 1 if ($self->filename() eq $default_filename);
                    241:     return 0;
1.11      matthew   242: }
                    243: 
                    244: sub initialize {
                    245:     # This method is here to remind you that it will be overridden by
                    246:     # the descendents of the spreadsheet class.
1.1       matthew   247: }
                    248: 
1.23      matthew   249: sub clear_package {
                    250:     # This method is here to remind you that it will be overridden by
                    251:     # the descendents of the spreadsheet class.
                    252: }
                    253: 
                    254: sub cleanup {
                    255:     my $self = shift();
                    256:     $self->clear_package();
                    257: }
                    258: 
1.1       matthew   259: sub initialize_spreadsheet_package {
                    260:     &load_spreadsheet_expirationdates();
                    261:     &clear_spreadsheet_definition_cache();
                    262: }
                    263: 
                    264: sub load_spreadsheet_expirationdates {
                    265:     undef %expiredates;
1.41      albertel  266:     my $cid=$env{'request.course.id'};
1.1       matthew   267:     my @tmp = &Apache::lonnet::dump('nohist_expirationdates',
1.41      albertel  268:                                     $env{'course.'.$cid.'.domain'},
                    269:                                     $env{'course.'.$cid.'.num'});
1.1       matthew   270:     if (lc($tmp[0]) !~ /^error/){
                    271:         %expiredates = @tmp;
                    272:     }
                    273: }
                    274: 
                    275: sub check_expiration_time {
                    276:     my $self = shift;
                    277:     my ($time)=@_;
1.22      matthew   278:     return 0 if (! defined($time));
1.19      matthew   279:     my ($key1,$key2,$key3,$key4,$key5);
                    280:     # Description of keys
                    281:     #
                    282:     # key1: all sheets of this type have expired
                    283:     # key2: all sheets of this type for this student
                    284:     # key3: all sheets of this type in this map for this student
                    285:     # key4: this assessment sheet for this student
                    286:     # key5: this assessment sheet for all students
1.1       matthew   287:     $key1 = '::'.$self->{'type'}.':';
                    288:     $key2 = $self->{'name'}.':'.$self->{'domain'}.':'.$self->{'type'}.':';
                    289:     $key3 = $key2.$self->{'container'} if (defined($self->{'container'}));
1.19      matthew   290:     $key4 = $key2.$self->{'symb'} if (defined($self->{'symb'}));
                    291:     $key5 = $key1.$self->{'symb'} if (defined($self->{'symb'}));
                    292:     my $returnvalue = 1; # default to okay
                    293:     foreach my $key ($key1,$key2,$key3,$key4,$key5) {
1.1       matthew   294:         next if (! defined($key));
1.19      matthew   295:         if (exists($expiredates{$key}) && $expiredates{$key} > $time) {
                    296:             $returnvalue = 0; # need to recompute
1.1       matthew   297:         }
                    298:     }
1.19      matthew   299:     return $returnvalue;
1.1       matthew   300: }
                    301: 
                    302: ######################################################
                    303: 
                    304: =pod
                    305: 
                    306: =item &initialize_safe_space
                    307: 
                    308: Returns the safe space required by a Spreadsheet object.
                    309: 
                    310: =head 2 Safe Space Functions
                    311: 
                    312: =over 4
                    313: 
                    314: =cut
                    315: 
                    316: ######################################################
1.25      matthew   317: { 
                    318: 
                    319:     my $safeeval;
                    320: 
1.1       matthew   321: sub initialize_safe_space {
1.25      matthew   322:   my $self = shift;
1.38      matthew   323:   my $usection = &Apache::lonnet::getsection($self->{'domain'},
                    324:                                              $self->{'name'},
1.41      albertel  325:                                              $env{'request.course.id'});
1.25      matthew   326:   if (! defined($safeeval)) {
                    327:       $safeeval = new Safe(shift);
                    328:       my $safehole = new Safe::Hole;
                    329:       $safeeval->permit("entereval");
                    330:       $safeeval->permit(":base_math");
                    331:       $safeeval->permit("sort");
                    332:       $safeeval->deny(":base_io");
1.38      matthew   333:       $safehole->wrap(\&Apache::lonnet::EXT,$safeeval,'&Apache::lonnet::EXT');
1.25      matthew   334:       $safehole->wrap(\&mask,$safeeval,'&mask');
1.44      albertel  335:       $safehole->wrap(\&Apache::lonnet::logthis,$safeeval,'&logthis');
1.25      matthew   336:       $safeeval->share('$@');
1.46      albertel  337:     # Holds the (computed, final) values for the sheet
                    338:     # This is only written to by &calc, the spreadsheet computation routine.
                    339:     # It is read by many functions
                    340:       $safeeval->share('%sheet_values');
1.25      matthew   341:       my $code=<<'ENDDEFS';
1.1       matthew   342: # ---------------------------------------------------- Inside of the safe space
                    343: #
                    344: # f: formulas
                    345: # t: intermediate format (variable references expanded)
                    346: # v: output values
                    347: # c: preloaded constants (A-column)
                    348: # rl: row label
                    349: # os: other spreadsheets (for student spreadsheet only)
1.46      albertel  350: undef %t; # Holds the forumlas of the spreadsheet to be computed. Set in
                    351:     # &sett, which does the translation of strings like C5 into the value
                    352:     # in C5. Used in &calc - %t holds the values that are actually eval'd.
1.1       matthew   353: undef %f;    # Holds the formulas for each cell.  This is the users
                    354:     # (spreadsheet authors) data for each cell.
                    355: undef %c; # Holds the constants for a sheet.  In the assessment
                    356:     # sheets, this is the A column.  Used in &MINPARM, &MAXPARM, &expandnamed,
                    357:     # &sett, and &constants.  There is no &getconstants.
                    358:     # &constants is called by &loadstudent, &loadcourse, &load assessment,
                    359: undef @os;  # Holds the names of other spreadsheets - this is used to specify
                    360:     # the spreadsheets that are available for the assessment sheet.
                    361:     # Set by &setothersheets.  &setothersheets is called by &handler.  A
                    362:     # related subroutine is &othersheets.
                    363: $errorlog = '';
                    364: #
                    365: $maxrow = 0;
                    366: $type = '';
                    367: #
                    368: # filename/reference of the sheet
                    369: $filename = '';
                    370: #
                    371: # user data
                    372: $name = '';
                    373: $domain  = '';
                    374: #
                    375: # course data
                    376: $csec = '';
                    377: $cnum = '';
                    378: $cdom = '';
                    379: $cid  = '';
                    380: $coursefilename  = '';
                    381: #
                    382: # symb
                    383: $usymb = '';
                    384: #
                    385: # error messages
                    386: $errormsg = '';
                    387: #
                    388: #-------------------------------------------------------
                    389: 
                    390: =pod
                    391: 
1.38      matthew   392: =item EXT(parameter)
                    393: 
                    394: Calls the system EXT function to determine the value of the given parameter.
                    395: 
                    396: =cut
                    397: 
                    398: #-------------------------------------------------------
                    399: sub EXT {
1.61      albertel  400:     my ($parameter,$specific_symb) = @_;
1.38      matthew   401:     return '' if (! defined($parameter) || $parameter eq '');
                    402:     $parameter =~ s/^parameter\./resource\./;
1.61      albertel  403:     if ($specific_symb eq '') { $specific_symb = $symb; }
                    404:     my $value = &Apache::lonnet::EXT($parameter,$specific_symb,$domain,$name,
                    405: 				     $usection);
1.38      matthew   406:     return $value;
                    407: }
                    408: 
                    409: #-------------------------------------------------------
                    410: 
                    411: =pod
                    412: 
1.1       matthew   413: =item NUM(range)
                    414: 
                    415: returns the number of items in the range.
                    416: 
                    417: =cut
                    418: 
                    419: #-------------------------------------------------------
                    420: sub NUM {
1.44      albertel  421:     my $values=&get_values(@_);
                    422:     my $num= scalar(@$values);
1.1       matthew   423:     return $num;   
                    424: }
                    425: 
                    426: #-------------------------------------------------------
                    427: 
                    428: =pod
                    429: 
                    430: =item BIN(low,high,lower,upper)
                    431: 
                    432: =cut
                    433: 
                    434: #-------------------------------------------------------
                    435: sub BIN {
                    436:     my ($low,$high,$lower,$upper)=@_;
1.44      albertel  437:     my $values=&get_values($lower,$upper);
1.1       matthew   438:     my $num=0;
1.44      albertel  439:     foreach (@$values) {
                    440:         if (($_>=$low) && ($_<=$high)) {
1.1       matthew   441:             $num++;
                    442:         }
                    443:     }
                    444:     return $num;   
                    445: }
                    446: 
                    447: #-------------------------------------------------------
                    448: 
                    449: =pod
                    450: 
                    451: =item SUM(range)
                    452: 
                    453: returns the sum of items in the range.
                    454: 
                    455: =cut
                    456: 
                    457: #-------------------------------------------------------
                    458: sub SUM {
1.44      albertel  459:     my $values=&get_values(@_);
1.1       matthew   460:     my $sum=0;
1.44      albertel  461:     foreach (@$values) {
                    462:         $sum+=$_;
1.1       matthew   463:     }
                    464:     return $sum;   
                    465: }
                    466: 
                    467: #-------------------------------------------------------
                    468: 
                    469: =pod
                    470: 
                    471: =item MEAN(range)
                    472: 
                    473: compute the average of the items in the range.
                    474: 
                    475: =cut
                    476: 
                    477: #-------------------------------------------------------
                    478: sub MEAN {
1.44      albertel  479:     my $values=&get_values(@_);
1.1       matthew   480:     my $sum=0; 
                    481:     my $num=0;
1.44      albertel  482:     foreach (@$values) {
                    483:         $sum+=$_;
1.1       matthew   484:         $num++;
                    485:     }
                    486:     if ($num) {
                    487:        return $sum/$num;
                    488:     } else {
                    489:        return undef;
                    490:     }   
                    491: }
                    492: 
                    493: #-------------------------------------------------------
                    494: 
                    495: =pod
                    496: 
                    497: =item STDDEV(range)
                    498: 
                    499: compute the standard deviation of the items in the range.
                    500: 
                    501: =cut
                    502: 
                    503: #-------------------------------------------------------
                    504: sub STDDEV {
1.44      albertel  505:     my $values=&get_values(@_);
1.1       matthew   506:     my $sum=0; my $num=0;
1.44      albertel  507:     foreach (@$values) {
                    508:         $sum+=$_;
1.1       matthew   509:         $num++;
                    510:     }
                    511:     unless ($num>1) { return undef; }
                    512:     my $mean=$sum/$num;
                    513:     $sum=0;
1.44      albertel  514:     foreach (@$values) {
                    515:         $sum+=($_-$mean)**2;
1.1       matthew   516:     }
                    517:     return sqrt($sum/($num-1));    
                    518: }
                    519: 
                    520: #-------------------------------------------------------
                    521: 
                    522: =pod
                    523: 
                    524: =item PROD(range)
                    525: 
                    526: compute the product of the items in the range.
                    527: 
                    528: =cut
                    529: 
                    530: #-------------------------------------------------------
                    531: sub PROD {
1.44      albertel  532:     my $values=&get_values(@_);
1.1       matthew   533:     my $prod=1;
1.44      albertel  534:     foreach (@$values) {
                    535:         $prod*=$_;
1.1       matthew   536:     }
                    537:     return $prod;   
                    538: }
                    539: 
                    540: #-------------------------------------------------------
                    541: 
                    542: =pod
                    543: 
                    544: =item MAX(range)
                    545: 
                    546: compute the maximum of the items in the range.
                    547: 
                    548: =cut
                    549: 
                    550: #-------------------------------------------------------
                    551: sub MAX {
1.44      albertel  552:     my $values=&get_values(@_);
1.1       matthew   553:     my $max='-';
1.44      albertel  554:     foreach (@$values) {
                    555:         if (($_>$max) || ($max eq '-')) { 
                    556:             $max=$_; 
1.1       matthew   557:         }
                    558:     } 
                    559:     return $max;   
                    560: }
                    561: 
                    562: #-------------------------------------------------------
                    563: 
                    564: =pod
                    565: 
                    566: =item MIN(range)
                    567: 
                    568: compute the minimum of the items in the range.
                    569: 
                    570: =cut
                    571: 
                    572: #-------------------------------------------------------
                    573: sub MIN {
1.44      albertel  574:     my $values=&get_values(@_);
1.1       matthew   575:     my $min='-';
1.44      albertel  576:     foreach (@$values) {
                    577:         if (($_<$min) || ($min eq '-')) { 
                    578:             $min=$_; 
1.1       matthew   579:         }
                    580:     }
                    581:     return $min;   
                    582: }
                    583: 
                    584: #-------------------------------------------------------
                    585: 
                    586: =pod
                    587: 
                    588: =item SUMMAX(num,lower,upper)
                    589: 
                    590: compute the sum of the largest 'num' items in the range from
                    591: 'lower' to 'upper'
                    592: 
                    593: =cut
                    594: 
                    595: #-------------------------------------------------------
                    596: sub SUMMAX {
                    597:     my ($num,$lower,$upper)=@_;
1.44      albertel  598:     my $values=&get_values($lower,$upper);
                    599:     my @inside=sort {$a <=> $b} (@$values);
1.1       matthew   600:     my $sum=0; my $i;
                    601:     for ($i=$#inside;(($i>$#inside-$num) && ($i>=0));$i--) { 
                    602:         $sum+=$inside[$i];
                    603:     }
                    604:     return $sum;   
                    605: }
                    606: 
                    607: #-------------------------------------------------------
                    608: 
                    609: =pod
                    610: 
                    611: =item SUMMIN(num,lower,upper)
                    612: 
                    613: compute the sum of the smallest 'num' items in the range from
                    614: 'lower' to 'upper'
                    615: 
                    616: =cut
                    617: 
                    618: #-------------------------------------------------------
                    619: sub SUMMIN {
                    620:     my ($num,$lower,$upper)=@_;
1.44      albertel  621:     my $values=&get_values($lower,$upper);
                    622:     my @inside=sort {$a <=> $b} (@$values);
1.1       matthew   623:     my $sum=0; my $i;
                    624:     for ($i=0;(($i<$num) && ($i<=$#inside));$i++) { 
                    625:         $sum+=$inside[$i];
                    626:     }
                    627:     return $sum;   
                    628: }
                    629: 
                    630: #-------------------------------------------------------
                    631: 
                    632: =pod
                    633: 
                    634: =item MINPARM(parametername)
                    635: 
                    636: Returns the minimum value of the parameters matching the parametername.
                    637: parametername should be a string such as 'duedate'.
                    638: 
                    639: =cut
                    640: 
                    641: #-------------------------------------------------------
                    642: sub MINPARM {
                    643:     my ($expression) = @_;
                    644:     my $min = undef;
                    645:     foreach $parameter (keys(%c)) {
                    646:         next if ($parameter !~ /$expression/);
                    647:         if ((! defined($min)) || ($min > $c{$parameter})) {
                    648:             $min = $c{$parameter} 
                    649:         }
                    650:     }
                    651:     return $min;
                    652: }
                    653: 
                    654: #-------------------------------------------------------
                    655: 
                    656: =pod
                    657: 
                    658: =item MAXPARM(parametername)
                    659: 
                    660: Returns the maximum value of the parameters matching the input parameter name.
                    661: parametername should be a string such as 'duedate'.
                    662: 
                    663: =cut
                    664: 
                    665: #-------------------------------------------------------
                    666: sub MAXPARM {
                    667:     my ($expression) = @_;
                    668:     my $max = undef;
                    669:     foreach $parameter (keys(%c)) {
                    670:         next if ($parameter !~ /$expression/);
                    671:         if ((! defined($min)) || ($max < $c{$parameter})) {
                    672:             $max = $c{$parameter} 
                    673:         }
                    674:     }
                    675:     return $max;
                    676: }
                    677: 
1.45      albertel  678: #-------------------------------------------------------
                    679: 
                    680: =pod
                    681: 
                    682: =item  &get_values($lower,$upper)
                    683: 
                    684: Inputs: $lower and $upper, cell names ("X12" or "a150") or globs ("X*").
                    685: 
                    686: Returns: an array ref of the values of the cells that exist in the 
                    687:          speced range
                    688: 
                    689: =cut
                    690: 
                    691: #-------------------------------------------------------
1.44      albertel  692: sub get_values {
                    693:     my ($lower,$upper)=@_;
1.45      albertel  694:     $upper = $lower if (! defined($upper));
1.44      albertel  695:     my @values;
1.45      albertel  696:     my ($la,$ld) = ($lower=~/([A-z]|\*)(\d+|\*)/);
                    697:     my ($ua,$ud) = ($upper=~/([A-z]|\*)(\d+|\*)/);
                    698:     my ($alpha,$num);
                    699:     if ($ld ne '*' && $ud ne '*') {
1.44      albertel  700: 	my @alpha;
                    701: 	if (($la eq '*') || ($ua eq '*')) {
                    702: 	    @alpha=('A'..'z');
                    703: 	} else {
1.45      albertel  704: 	    if ($la gt $ua) { ($la,$ua)=($ua,$la); }
                    705: 	    if ((lc($la) ne $la) && (lc($ua) eq $ua)) {
                    706: 		@alpha=($la..'Z','a'..$ua);
                    707: 	    } else {
                    708: 		@alpha=($la..$ua);
                    709:             }
                    710: 	}
                    711: 	my @num=($ld..$ud);
                    712: 	foreach my $a (@alpha) {
                    713: 	    foreach my $n (@num) {
                    714: 		if (exists($sheet_values{$a.$n})) {
                    715: 		    push(@values,$sheet_values{$a.$n});
                    716: 		}
1.44      albertel  717: 	    }
                    718: 	}
1.45      albertel  719: 	return \@values;
                    720:     } else {
1.60      albertel  721: 	$num = '([1-9]\d*)';
1.45      albertel  722:     }
                    723:     if (($la eq '*') || ($ua eq '*')) {
                    724:         $alpha='[A-z]';
                    725:     } else {
                    726: 	if ($la gt $ua) { ($la,$ua)=($ua,$la); }
                    727:         $alpha=qq/[$la-$ua]/;
                    728:     }
                    729:     my $expression = '^'.$alpha.$num.'$';
                    730:     foreach (grep /$expression/,keys(%sheet_values)) {
                    731: 	push(@values,$sheet_values{$_});
1.44      albertel  732:     }
1.45      albertel  733:     return \@values;
1.44      albertel  734: }
1.1       matthew   735: 
                    736: sub calc {
                    737:     my $notfinished = 1;
                    738:     my $lastcalc = '';
                    739:     my $depth = 0;
                    740:     while ($notfinished) {
                    741: 	$notfinished=0;
                    742:         while (my ($cell,$value) = each(%t)) {
                    743:             my $old=$sheet_values{$cell};
1.65      bowersj2  744:             $sheet_values{$cell}=eval $value;
1.1       matthew   745: #            $errorlog .= $cell.' = '.$old.'->'.$sheet_values{$cell}."\n";
                    746: 	    if ($@) {
                    747: 		undef %sheet_values;
                    748:                 return $cell.': '.$@;
                    749:             }
                    750: 	    if ($sheet_values{$cell} ne $old) { 
                    751:                 $notfinished=1; 
                    752:                 $lastcalc=$cell; 
                    753:             }
                    754:         }
                    755: #        $errorlog.="------------------------------------------------";
                    756: 
                    757:         $depth++;
                    758:         if ($depth>100) {
                    759: 	    undef %sheet_values;
                    760:             return $lastcalc.': Maximum calculation depth exceeded';
                    761:         }
                    762:     }
1.30      matthew   763:     return 'okay';
1.1       matthew   764: }
                    765: 
                    766: # ------------------------------------------- End of "Inside of the safe space"
                    767: ENDDEFS
1.25      matthew   768:         $safeeval->reval($code);
                    769:     }
1.1       matthew   770:     $self->{'safe'} = $safeeval;
                    771:     $self->{'root'} = $self->{'safe'}->root();
                    772:     #
                    773:     # Place some of the %$self  items into the safe space except the safe space
                    774:     # itself
                    775:     my $initstring = '';
1.38      matthew   776:     foreach (qw/name domain type symb cid csec coursefilename
1.48      albertel  777:              cnum cdom/) {
1.1       matthew   778:         $initstring.= qq{\$$_="$self->{$_}";};
                    779:     }
1.38      matthew   780:     $initstring.=qq{\$usection="$usection";};
1.1       matthew   781:     $self->{'safe'}->reval($initstring);
                    782:     return $self;
                    783: }
1.25      matthew   784: 
                    785: }
                    786: 
1.1       matthew   787: ######################################################
                    788: 
                    789: =pod
                    790: 
                    791: =back
                    792: 
                    793: =cut
                    794: 
                    795: ######################################################
                    796: 
                    797: ##
                    798: ## sub add_hash_to_safe {} # spreadsheet, would like to destroy
                    799: ##
                    800: 
                    801: #
                    802: # expandnamed used to reside in the safe space
                    803: #
                    804: sub expandnamed {
                    805:     my $self = shift;
                    806:     my $expression=shift;
                    807:     if ($expression=~/^\&/) {
                    808: 	my ($func,$var,$formula)=($expression=~/^\&(\w+)\(([^\;]+)\;(.*)\)/);
                    809: 	my @vars=split(/\W+/,$formula);
1.73    ! albertel  810: 	# make the list uniq
        !           811: 	@vars = keys(%{{ map { $_ => 1 } @vars }});
1.1       matthew   812:         my %values=();
                    813: 	foreach my $varname ( @vars ) {
1.20      matthew   814:             if ($varname=~/^(parameter|stores|timestamp)/) {
                    815:                 $formula=~s/$varname/'$c{\''.$varname.'\'}'/ge;
1.1       matthew   816:                $varname=~s/$var/\([\\w:\\- ]\+\)/g;
                    817: 	       foreach (keys(%{$self->{'constants'}})) {
                    818: 		  if ($_=~/$varname/) {
                    819: 		      $values{$1}=1;
                    820:                   }
                    821:                }
                    822: 	    }
                    823:         }
                    824:         if ($func eq 'EXPANDSUM') {
                    825:             my $result='';
                    826: 	    foreach (keys(%values)) {
                    827:                 my $thissum=$formula;
                    828:                 $thissum=~s/$var/$_/g;
                    829:                 $result.=$thissum.'+';
                    830:             } 
                    831:             $result=~s/\+$//;
1.64      bowersj2  832:             return '('.$result.')';
1.1       matthew   833:         } else {
                    834: 	    return 0;
                    835:         }
                    836:     } else {
                    837:         # it is not a function, so it is a parameter name
                    838:         # We should do the following:
                    839:         #    1. Take the list of parameter names
                    840:         #    2. look through the list for ones that match the parameter we want
                    841:         #    3. If there are no collisions, return the one that matches
                    842:         #    4. If there is a collision, return 'bad parameter name error'
                    843:         my $returnvalue = '';
                    844:         my @matches = ();
1.14      matthew   845:         my @values = ();
1.1       matthew   846:         $#matches = -1;
1.14      matthew   847:         while (my($parameter,$value) = each(%{$self->{'constants'}})) {
                    848:             next if ($parameter !~ /$expression/);
                    849:             push(@matches,$parameter);
                    850:             push(@values,$value);
1.1       matthew   851:         }
                    852:         if (scalar(@matches) == 0) {
1.10      matthew   853:             $returnvalue = '""';#'"unmatched parameter: '.$parameter.'"';
1.1       matthew   854:         } elsif (scalar(@matches) == 1) {
                    855:             # why do we not do this lookup here, instead of delaying it?
1.14      matthew   856:             $returnvalue = $values[0];
1.1       matthew   857:         } elsif (scalar(@matches) > 0) {
                    858:             # more than one match.  Look for a concise one
                    859:             $returnvalue =  "'non-unique parameter name : $expression'";
1.14      matthew   860:             for (my $i=0; $i<=$#matches;$i++) {
                    861:                 if ($matches[$i] =~ /^$expression$/) {
1.1       matthew   862:                     # why do we not do this lookup here?
1.14      matthew   863:                     $returnvalue = $values[$i];
1.1       matthew   864:                 }
                    865:             }
                    866:         } else {
                    867:             # There was a negative number of matches, which indicates 
                    868:             # something is wrong with reality.  Better warn the user.
1.14      matthew   869:             $returnvalue = '"bizzare parameter: '.$expression.'"';
1.1       matthew   870:         }
                    871:         return $returnvalue;
                    872:     }
                    873: }
                    874: 
                    875: sub sett {
                    876:     my $self = shift;
                    877:     my %t=();
1.46      albertel  878:     undef(%Apache::Spreadsheet::sheet_values);
1.1       matthew   879:     #
                    880:     # Deal with the template row
                    881:     foreach my $col ($self->template_cells()) {
                    882:         next if ($col=~/^[A-Z]/);
                    883:         foreach my $row ($self->rows()) {
                    884:             # Get the name of this cell
                    885:             my $cell=$col.$row;
                    886:             # Grab the template declaration
                    887:             $t{$cell}=$self->formula('template_'.$col);
                    888:             # Replace '#' with the row number
                    889:             $t{$cell}=~s/\#/$row/g;
                    890:             # Replace '....' with ','
                    891:             $t{$cell}=~s/\.\.+/\,/g;
                    892:             # Replace 'A0' with the value from 'A0'
                    893:             $t{$cell}=~s/(^|[^\"\'])([A-Za-z]\d+)/$1\$sheet_values\{\'$2\'\}/g;
                    894:             # Replace parameters
                    895:             $t{$cell}=~s/(^|[^\"\'])\[([^\]]+)\]/$1.$self->expandnamed($2)/ge;
                    896:         }
                    897:     }
                    898:     #
                    899:     # Deal with the normal cells
                    900:     while (my($cell,$formula) = each(%{$self->{'formulas'}})) {
                    901: 	next if ($_=~/^template\_/);
                    902:         my ($col,$row) = ($cell =~ /^([A-z])(\d+)$/);
                    903:         if ($row eq '0') {
                    904:             $t{$cell}=$formula;
                    905:             $t{$cell}=~s/\.\.+/\,/g;
                    906:             $t{$cell}=~s/(^|[^\"\'])([A-Za-z]\d+)/$1\$sheet_values\{\'$2\'\}/g;
                    907:             $t{$cell}=~s/(^|[^\"\'])\[([^\]]+)\]/$1.$self->expandnamed($2)/ge;
                    908:         } elsif  ( $col  =~ /^[A-Z]$/  ) {
1.42      albertel  909:             if ($formula !~ /^\!/ && exists($self->{'constants'}->{$cell})
                    910: 		&& $self->{'constants'}->{$cell} ne '') {
1.46      albertel  911: 		$Apache::Spreadsheet::sheet_values{$cell}=
                    912: 		    eval($self->{'constants'}->{$cell});
1.1       matthew   913:             }
                    914:         } else { # $row > 1 and $col =~ /[a-z]
                    915:             $t{$cell}=$formula;
                    916:             $t{$cell}=~s/\.\.+/\,/g;
                    917:             $t{$cell}=~s/(^|[^\"\'])([A-Za-z]\d+)/$1\$sheet_values\{\'$2\'\}/g;
                    918:             $t{$cell}=~s/(^|[^\"\'])\[([^\]]+)\]/$1.$self->expandnamed($2)/ge;
                    919:         }
                    920:     }
                    921:     %{$self->{'safe'}->varglob('t')}=%t;
                    922: }
                    923: 
                    924: ##
                    925: ## sync_safe_space:  Called by calcsheet to make sure all the data we 
                    926: #  need to calculate is placed into the safe space
                    927: ##
                    928: sub sync_safe_space {
                    929:     my $self = shift;
                    930:     # Inside the safe space 'formulas' has a diabolical alter-ego named 'f'.
1.47      albertel  931:     #%{$self->{'safe'}->varglob('f')}=%{$self->{'formulas'}};
1.1       matthew   932:     # 'constants' leads a peaceful hidden life of 'c'.
                    933:     %{$self->{'safe'}->varglob('c')}=%{$self->{'constants'}};
                    934:     # 'othersheets' hides as 'os', a disguise few can penetrate.
1.47      albertel  935:     #@{$self->{'safe'}->varglob('os')}=@{$self->{'othersheets'}};
1.1       matthew   936: }
                    937: 
                    938: ##
                    939: ## Retrieve the error log from the safe space (used for debugging)
                    940: ##
                    941: sub get_errorlog {
                    942:     my $self = shift;
                    943:     $self->{'errorlog'} = $ { $self->{'safe'}->varglob('errorlog') };
                    944:     return $self->{'errorlog'};
                    945: }
                    946: 
                    947: ##
                    948: ## Clear the error log inside the safe space
                    949: ##
                    950: sub clear_errorlog {
                    951:     my $self = shift;
                    952:     $ {$self->{'safe'}->varglob('errorlog')} = '';
                    953:     $self->{'errorlog'} = '';
                    954: }
                    955: 
                    956: ##
                    957: ## constants:  either set or get the constants
                    958: ##
                    959: sub constants {
                    960:     my $self=shift;
                    961:     my ($constants) = @_;
                    962:     if (defined($constants)) {
                    963:         if (! ref($constants)) {
                    964:             my %tmp = @_;
                    965:             $constants = \%tmp;
                    966:         }
                    967:         $self->{'constants'} = $constants;
                    968:         return;
                    969:     } else {
                    970:         return %{$self->{'constants'}};
                    971:     }
                    972: }
                    973: 
                    974: ##
                    975: ## formulas: either set or get the formulas
                    976: ##
                    977: sub formulas {
                    978:     my $self=shift;
                    979:     my ($formulas) = @_;
                    980:     if (defined($formulas)) {
                    981:         if (! ref($formulas)) {
                    982:             my %tmp = @_;
                    983:             $formulas = \%tmp;
                    984:         }
                    985:         $self->{'formulas'} = $formulas;
                    986:         $self->{'rows'} = [];
                    987:         $self->{'template_cells'} = [];
1.54      albertel  988: 	$self->{'loaded'} = 1;
1.1       matthew   989:         return;
                    990:     } else {
1.54      albertel  991: 	$self->check_formulas_loaded();
                    992:         return %{$self->{'formulas'}};
                    993:     }
                    994: }
                    995: 
                    996: sub check_formulas_loaded {
                    997:     my $self=shift;
                    998:     if (!$self->{'loaded'}) {
                    999: 	$self->{'loaded'}=1;
                   1000: 	# Load in the spreadsheet definition
                   1001: 	if (exists($env{'form.workcopy'}) && 
                   1002: 	    $self->{'type'} eq $env{'form.workcopy'}) {
                   1003: 	    $self->load_tmp();
                   1004: 	} else {
                   1005: 	    $self->load();
1.49      albertel 1006: 	}
1.1       matthew  1007:     }
                   1008: }
                   1009: 
                   1010: sub set_formula {
                   1011:     my $self = shift;
                   1012:     my ($cell,$formula) = @_;
1.54      albertel 1013:     $self->check_formulas_loaded();
1.1       matthew  1014:     $self->{'formulas'}->{$cell}=$formula;
                   1015:     return;
                   1016: }
                   1017: 
                   1018: ##
                   1019: ## formulas_keys:  Return the keys to the formulas hash.
                   1020: ##
                   1021: sub formulas_keys {
                   1022:     my $self = shift;
1.54      albertel 1023:     $self->check_formulas_loaded();
1.1       matthew  1024:     return keys(%{$self->{'formulas'}});
                   1025: }
                   1026: 
                   1027: ##
                   1028: ## formula:  Return the formula for a given cell in the spreadsheet
                   1029: ## returns '' if the cell does not have a formula or does not exist
                   1030: ##
                   1031: sub formula {
                   1032:     my $self = shift;
                   1033:     my $cell = shift;
1.54      albertel 1034:     $self->check_formulas_loaded();
1.1       matthew  1035:     if (defined($cell) && exists($self->{'formulas'}->{$cell})) {
                   1036:         return $self->{'formulas'}->{$cell};
                   1037:     }
                   1038:     return '';
                   1039: }
                   1040: 
                   1041: ##
                   1042: ## logthis: write the input to lonnet.log
                   1043: ##
                   1044: sub logthis {
                   1045:     my $self = shift;
                   1046:     my $message = shift;
                   1047:     &Apache::lonnet::logthis($self->{'type'}.':'.
                   1048:                              $self->{'name'}.':'.$self->{'domain'}.':'.
                   1049:                              $message);
                   1050:     return;
                   1051: }
                   1052: 
                   1053: ##
                   1054: ## dump_formulas_to_log: makes lonnet.log huge...
                   1055: ##
                   1056: sub dump_formulas_to_log {
                   1057:     my $self =shift;
                   1058:     $self->logthis("Spreadsheet formulas");
                   1059:     $self->logthis("--------------------------------------------------------");
                   1060:     while (my ($cell, $formula) = each(%{$self->{'formulas'}})) {
                   1061:         $self->logthis('    '.$cell.' = '.$formula);
                   1062:     }
                   1063:     $self->logthis("--------------------------------------------------------");}
                   1064: 
                   1065: ##
                   1066: ## value: returns the computed value of a particular cell
                   1067: ##
                   1068: sub value {
                   1069:     my $self = shift;
                   1070:     my $cell = shift;
                   1071:     if (defined($cell) && exists($self->{'values'}->{$cell})) {
                   1072:         return $self->{'values'}->{$cell};
                   1073:     }
                   1074:     return '';
                   1075: }
                   1076: 
                   1077: ##
                   1078: ## dump_values_to_log: makes lonnet.log huge...
                   1079: ##
                   1080: sub dump_values_to_log {
                   1081:     my $self =shift;
                   1082:     $self->logthis("Spreadsheet Values");
                   1083:     $self->logthis("------------------------------------------------------");
                   1084:     while (my ($cell, $value) = each(%{$self->{'values'}})) {
                   1085:         $self->logthis('    '.$cell.' = '.$value);
                   1086:     }
                   1087:     $self->logthis("------------------------------------------------------");
                   1088: }
                   1089: 
                   1090: ##
                   1091: ## Yet another debugging function
                   1092: ##
                   1093: sub dump_hash_to_log {
                   1094:     my $self= shift();
                   1095:     my %tmp = @_;
                   1096:     if (@_<2) {
                   1097:         %tmp = %{$_[0]};
                   1098:     }
                   1099:     $self->logthis('---------------------------- (begin hash dump)');
                   1100:     while (my ($key,$val) = each (%tmp)) {
                   1101:         $self->logthis(' '.$key.' = '.$val.':');
                   1102:     }
                   1103:     $self->logthis('---------------------------- (finished hash dump)');
                   1104: }
                   1105: 
                   1106: ##
                   1107: ## rebuild_stats: rebuilds the rows and template_cells arrays
                   1108: ##
                   1109: sub rebuild_stats {
                   1110:     my $self = shift;
                   1111:     $self->{'rows'}=[];
                   1112:     $self->{'template_cells'}=[];
1.54      albertel 1113:     $self->check_formulas_loaded();
1.1       matthew  1114:     while (my ($cell,$formula) = each(%{$self->{'formulas'}})) {
                   1115:         push(@{$self->{'rows'}},$1) if ($cell =~ /^A(\d+)/ && $1 != 0);
                   1116:         push(@{$self->{'template_cells'}},$1) if ($cell =~ /^template_(\w+)/);
                   1117:     }
                   1118:     return;
                   1119: }
                   1120: 
                   1121: ##
                   1122: ## template_cells returns a list of the cells defined in the template row
                   1123: ##
                   1124: sub template_cells {
                   1125:     my $self = shift;
                   1126:     $self->rebuild_stats() if (! defined($self->{'template_cells'}) ||
                   1127:                                ! @{$self->{'template_cells'}});
                   1128:     return @{$self->{'template_cells'}};
                   1129: }
                   1130: 
                   1131: ##
                   1132: ## Sigh.... 
                   1133: ##
                   1134: sub setothersheets {
                   1135:     my $self = shift;
                   1136:     my @othersheets = @_;
                   1137:     $self->{'othersheets'} = \@othersheets;
                   1138: }
                   1139: 
                   1140: ##
                   1141: ## rows returns a list of the names of cells defined in the A column
                   1142: ##
                   1143: sub rows {
                   1144:     my $self = shift;
                   1145:     $self->rebuild_stats() if (!@{$self->{'rows'}});
                   1146:     return @{$self->{'rows'}};
                   1147: }
                   1148: 
                   1149: #
                   1150: # calcsheet: makes all the calls to compute the spreadsheet.
                   1151: #
                   1152: sub calcsheet {
                   1153:     my $self = shift;
                   1154:     $self->sync_safe_space();
                   1155:     $self->clear_errorlog();
                   1156:     $self->sett();
                   1157:     my $result =  $self->{'safe'}->reval('&calc();');
                   1158: #    $self->logthis($self->get_errorlog());
                   1159:     %{$self->{'values'}} = %{$self->{'safe'}->varglob('sheet_values')};
                   1160: #    $self->logthis($self->get_errorlog());
1.30      matthew  1161:     if ($result ne 'okay') {
                   1162:         $self->set_calcerror($result);
                   1163:     }
1.1       matthew  1164:     return $result;
                   1165: }
                   1166: 
1.30      matthew  1167: sub set_badcalc {
                   1168:     my $self = shift();
                   1169:     $self->{'badcalc'} =1;
                   1170:     return;
                   1171: }
                   1172: 
                   1173: sub badcalc {
                   1174:     my $self = shift;
                   1175:     if (exists($self->{'badcalc'}) && $self->{'badcalc'}) {
                   1176:         return 1;
                   1177:     } else {
                   1178:         return 0;
                   1179:     }
                   1180: }
                   1181: 
                   1182: sub set_calcerror {
                   1183:     my $self = shift;
                   1184:     if (@_) {
                   1185:         $self->set_badcalc();
                   1186:         if (exists($self->{'calcerror'})) {
                   1187:             $self->{'calcerror'}.="\n".$_[0];
                   1188:         } else {
                   1189:             $self->{'calcerror'}.=$_[0];
                   1190:         }
                   1191:     }
                   1192: }
                   1193: 
                   1194: sub calcerror {
                   1195:     my $self = shift;
                   1196:     if ($self->badcalc()) {
                   1197:         if (exists($self->{'calcerror'})) {
                   1198:             return $self->{'calcerror'};
                   1199:         }
                   1200:     }
                   1201:     return;
                   1202: }
                   1203: 
1.1       matthew  1204: ###########################################################
                   1205: ##
                   1206: ## Output Helpers
                   1207: ##
                   1208: ###########################################################
1.5       matthew  1209: sub display {
                   1210:     my $self = shift;
                   1211:     my ($r) = @_;
                   1212:     my $outputmode = 'html';
1.31      matthew  1213:     foreach ($self->output_options()) {
1.41      albertel 1214:         if ($env{'form.output_format'} eq $_->{'value'}) {
1.31      matthew  1215:             $outputmode = $_->{'value'};
                   1216:             last;
                   1217:         }
1.5       matthew  1218:     }
1.62      bowersj2 1219:     $self->{outputmode} = $outputmode;
1.5       matthew  1220:     if ($outputmode eq 'html') {
1.31      matthew  1221:         $self->compute($r);
1.5       matthew  1222:         $self->outsheet_html($r);
1.31      matthew  1223:     } elsif ($outputmode eq 'htmlclasslist') {
                   1224:         # No computation neccessary...  This is kludgy
                   1225:         $self->outsheet_htmlclasslist($r);
1.62      bowersj2 1226:     } elsif ($outputmode eq 'source') {
                   1227:         # No computation necessary. Rumor has it that this is some
                   1228:         # sort of kludge w.r.t. not "computing". It's also
                   1229:         # a bit of of a kludge that we call "outsheet_html" and 
                   1230:         # let the 'outputmode' cause the outputting of source.
                   1231:         $self->outsheet_html($r);
1.5       matthew  1232:     } elsif ($outputmode eq 'excel') {
1.31      matthew  1233:         $self->compute($r);
1.5       matthew  1234:         $self->outsheet_excel($r);
                   1235:     } elsif ($outputmode eq 'csv') {
1.31      matthew  1236:         $self->compute($r);
1.5       matthew  1237:         $self->outsheet_csv($r);
1.33      matthew  1238:     } elsif ($outputmode eq 'xml') {
                   1239: #        $self->compute($r);
                   1240:         $self->outsheet_xml($r);
1.5       matthew  1241:     }
1.22      matthew  1242:     $self->cleanup();
1.5       matthew  1243:     return;
                   1244: }
                   1245: 
1.1       matthew  1246: ############################################
                   1247: ##         HTML output routines           ##
                   1248: ############################################
1.30      matthew  1249: sub html_report_error {
                   1250:     my $self = shift();
                   1251:     my $Str = '';
                   1252:     if ($self->badcalc()) {
                   1253:         $Str = '<h3 style="color:red">'.
                   1254:             &mt('An error occurred while calculating this spreadsheet').
                   1255:             "</h3>\n".
                   1256:             '<pre>'.$self->calcerror()."</pre>\n";
                   1257:     }
                   1258:     return $Str;
                   1259: }
                   1260: 
1.1       matthew  1261: sub html_export_row {
                   1262:     my $self = shift();
1.17      matthew  1263:     my ($color) = @_;
                   1264:     $color = '#CCCCFF' if (! defined($color));
1.41      albertel 1265:     my $allowed = &Apache::lonnet::allowed('mgr',$env{'request.course.id'});
1.1       matthew  1266:     my $row_html;
                   1267:     my @rowdata = $self->get_row(0);
                   1268:     foreach my $cell (@rowdata) {
                   1269:         if ($cell->{'name'} =~ /^[A-Z]/) {
1.17      matthew  1270: 	    $row_html .= '<td bgcolor="'.$color.'">'.
1.62      bowersj2 1271:                 &html_editable_cell($cell,$color,$allowed,
                   1272:                                     $self->{outputmode} eq 'source').'</td>';
1.1       matthew  1273:         } else {
                   1274: 	    $row_html .= '<td bgcolor="#DDCCFF">'.
1.62      bowersj2 1275:                 &html_editable_cell($cell,'#DDCCFF',$allowed,
                   1276:                                     $self->{outputmode} eq 'source').'</td>';
1.1       matthew  1277:         }
                   1278:     }
                   1279:     return $row_html;
                   1280: }
                   1281: 
                   1282: sub html_template_row {
                   1283:     my $self = shift();
1.41      albertel 1284:     my $allowed = &Apache::lonnet::allowed('mgr',$env{'request.course.id'});
1.17      matthew  1285:     my ($num_uneditable,$importcolor) = @_;
1.1       matthew  1286:     my $row_html;
                   1287:     my @rowdata = $self->get_template_row();
                   1288:     my $count = 0;
                   1289:     for (my $i = 0; $i<=$#rowdata; $i++) {
                   1290:         my $cell = $rowdata[$i];
                   1291:         if ($i < $num_uneditable) {
1.17      matthew  1292: 	    $row_html .= '<td bgcolor="'.$importcolor.'">'.
1.7       matthew  1293:                 &html_uneditable_cell($cell,'#FFDDDD',$allowed).'</td>';
1.1       matthew  1294:         } else {
1.70      albertel 1295: 	    $row_html .= '<td bgcolor="#E0FFDD">'.
                   1296:                 &html_editable_cell($cell,'#E0FFDD',$allowed,
1.62      bowersj2 1297:                                     $self->{outputmode} eq 'source').'</td>';
1.1       matthew  1298:         }
                   1299:     }
                   1300:     return $row_html;
                   1301: }
                   1302: 
                   1303: sub html_editable_cell {
1.62      bowersj2 1304:     my ($cell,$bgcolor,$allowed,$showsource) = @_;
1.1       matthew  1305:     my $result;
                   1306:     my ($name,$formula,$value);
                   1307:     if (defined($cell)) {
                   1308:         $name    = $cell->{'name'};
                   1309:         $formula = $cell->{'formula'};
                   1310:         $value   = $cell->{'value'};
                   1311:     }
                   1312:     $name    = '' if (! defined($name));
                   1313:     $formula = '' if (! defined($formula));
1.65      bowersj2 1314:     if ($showsource) {
1.62      bowersj2 1315:         if (!defined($formula) || $formula =~ /^\s*$/) {
                   1316:             $value = '<font color="'.$bgcolor.'">#</font>';
                   1317:         } else {
                   1318:             $value = &HTML::Entities::encode($formula, '<>&"');
                   1319:         }
1.65      bowersj2 1320:     } elsif (! defined($value)) {
1.1       matthew  1321:         $value = '<font color="'.$bgcolor.'">#</font>';
                   1322:         if ($formula ne '') {
                   1323:             $value = '<i>undefined value</i>';
                   1324:         }
                   1325:     } elsif ($value =~ /^\s*$/ ) {
                   1326:         $value = '<font color="'.$bgcolor.'">#</font>';
                   1327:     } else {
1.37      albertel 1328:         $value = &HTML::Entities::encode($value,'<>&"') if ($value !~/&nbsp;/);
1.1       matthew  1329:     }
                   1330:     return $value if (! $allowed);
1.18      matthew  1331:     #
1.1       matthew  1332:     # The formula will be parsed by the browser twice before being 
1.18      matthew  1333:     # displayed to the user for editing. 
                   1334:     #
                   1335:     # The encoding string "^A-blah" is placed in []'s inside a regexp, so 
                   1336:     # we specify the characters we want left alone by putting a '^' in front.
1.21      matthew  1337:     $formula = &HTML::Entities::encode($formula,'^A-z0-9 !#$%-;=?~');
                   1338:     # HTML::Entities::encode does not catch everything - we need '\' encoded
                   1339:     $formula =~ s/\\/&\#092/g;
1.18      matthew  1340:     # Escape it again - this time the only encodable character is '&'
                   1341:     $formula =~ s/\&/\&amp;/g;
1.1       matthew  1342:     # Glue everything together
                   1343:     $result .= "<a href=\"javascript:celledit(\'".
                   1344:         $name."','".$formula."');\">".$value."</a>";
                   1345:     return $result;
                   1346: }
                   1347: 
                   1348: sub html_uneditable_cell {
                   1349:     my ($cell,$bgcolor) = @_;
                   1350:     my $value = (defined($cell) ? $cell->{'value'} : '');
1.37      albertel 1351:     $value = &HTML::Entities::encode($value,'<>&"') if ($value !~/&nbsp;/);
1.1       matthew  1352:     return '&nbsp;'.$value.'&nbsp;';
                   1353: }
                   1354: 
                   1355: sub html_row {
                   1356:     my $self = shift();
1.17      matthew  1357:     my ($num_uneditable,$row,$exportcolor,$importcolor) = @_;
1.41      albertel 1358:     my $allowed = &Apache::lonnet::allowed('mgr',$env{'request.course.id'});
1.1       matthew  1359:     my @rowdata = $self->get_row($row);
                   1360:     my $num_cols_output = 0;
                   1361:     my $row_html;
1.17      matthew  1362:     my $color = $importcolor;
                   1363:     if ($row == 0) {
                   1364:         $color = $exportcolor;
                   1365:     }
                   1366:     $color = '#FFDDDD' if (! defined($color));
1.1       matthew  1367:     foreach my $cell (@rowdata) {
                   1368: 	if ($num_cols_output++ < $num_uneditable) {
1.17      matthew  1369: 	    $row_html .= '<td bgcolor="'.$color.'">';
1.1       matthew  1370: 	    $row_html .= &html_uneditable_cell($cell,'#FFDDDD');
                   1371: 	} else {
1.70      albertel 1372: 	    $row_html .= '<td bgcolor="#E0FFDD">';
1.62      bowersj2 1373: 	    $row_html .= &html_editable_cell($cell,'#E0FFDD',$allowed,
                   1374:                                              $self->{outputmode} eq 'source');
1.1       matthew  1375: 	}
                   1376: 	$row_html .= '</td>';
                   1377:     }
                   1378:     return $row_html;
                   1379: }
                   1380: 
1.5       matthew  1381: sub html_header {
                   1382:     my $self = shift;
1.41      albertel 1383:     return '' if (! $env{'request.role.adv'});
1.5       matthew  1384:     return "<table>\n".
1.29      matthew  1385:         '<tr><th align="center">'.&mt('Output Format').'</th></tr>'."\n".
1.31      matthew  1386:         '<tr><td>'.$self->output_selector()."</td></tr>\n".
1.5       matthew  1387:         "</table>\n";
                   1388: }
                   1389: 
1.31      matthew  1390: ##
                   1391: ## Default output types are HTML, Excel, and CSV
                   1392: sub output_options {
                   1393:     my $self = shift();
                   1394:     return  ({value       => 'html',
                   1395:               description => 'HTML'},
                   1396:              {value       => 'excel',
                   1397:               description => 'Excel'},
1.62      bowersj2 1398:              {value       => 'source',
                   1399:               description => 'Show Source'},
1.33      matthew  1400: #             {value       => 'xml',
                   1401: #              description => 'XML'},
1.31      matthew  1402:              {value       => 'csv',
                   1403:               description => 'Comma Separated Values'},);
                   1404: }
                   1405: 
1.5       matthew  1406: sub output_selector {
1.31      matthew  1407:     my $self = shift();
1.5       matthew  1408:     my $output_selector = '<select name="output_format" size="3">'."\n";
                   1409:     my $default = 'html';
1.41      albertel 1410:     if (exists($env{'form.output_format'})) {
                   1411:         $default = $env{'form.output_format'} 
1.5       matthew  1412:     } else {
1.41      albertel 1413:         $env{'form.output_format'} = $default;
1.5       matthew  1414:     }
1.31      matthew  1415:     foreach  ($self->output_options()) {
                   1416:         $output_selector.='<option value="'.$_->{'value'}.'"';
                   1417:         if ($_->{'value'} eq $default) {
1.5       matthew  1418:             $output_selector .= ' selected';
                   1419:         }
1.31      matthew  1420:         $output_selector .= ">".&mt($_->{'description'})."</option>\n";
1.5       matthew  1421:     }
                   1422:     $output_selector .= "</select>\n";
                   1423:     return $output_selector;
                   1424: }
                   1425: 
                   1426: ################################################
                   1427: ##          Excel output routines             ##
                   1428: ################################################
                   1429: sub excel_output_row {
                   1430:     my $self = shift;
                   1431:     my ($worksheet,$rownum,$rows_output,@prepend) = @_;
                   1432:     my $cols_output = 0;
                   1433:     #
                   1434:     my @rowdata = $self->get_row($rownum);
                   1435:     foreach my $cell (@prepend,@rowdata) {
                   1436:         my $value = $cell;
                   1437:         $value = $cell->{'value'} if (ref($value));
                   1438:         $value =~ s/\&nbsp;/ /gi;
                   1439:         $worksheet->write($rows_output,$cols_output++,$value);
                   1440:     }
                   1441:     return;
                   1442: }
                   1443: 
1.31      matthew  1444: #
                   1445: # This routine is just a stub 
                   1446: sub outsheet_htmlclasslist {
                   1447:     my $self = shift;
                   1448:     my ($r) = @_;
                   1449:     $r->print('<h2>'.&mt("This output is not supported").'</h2>');
                   1450:     $r->rflush();
                   1451:     return;
1.5       matthew  1452: }
                   1453: 
                   1454: sub outsheet_excel {
                   1455:     my $self = shift;
                   1456:     my ($r) = @_;
1.23      matthew  1457:     my $connection = $r->connection();
1.32      matthew  1458:     #
                   1459:     $r->print($self->html_report_error());
                   1460:     $r->rflush();
                   1461:     #
1.28      www      1462:     $r->print("<h2>".&mt('Preparing Excel Spreadsheet')."</h2>");
1.5       matthew  1463:     #
1.40      matthew  1464:     # Create excel workbook
                   1465:     my ($workbook,$filename,$format)=&Apache::loncommon::create_workbook($r);
1.5       matthew  1466:     return if (! defined($workbook));
                   1467:     #
                   1468:     # Create main worksheet
                   1469:     my $worksheet = $workbook->addworksheet('main');
                   1470:     my $rows_output = 0;
                   1471:     my $cols_output = 0;
                   1472:     #
                   1473:     # Write excel header
                   1474:     foreach my $value ($self->get_title()) {
                   1475:         $cols_output = 0;
1.40      matthew  1476:         $worksheet->write($rows_output++,$cols_output,$value,$format->{'h1'});
1.5       matthew  1477:     }
                   1478:     $rows_output++;    # skip a line
                   1479:     #
                   1480:     # Write summary/export row
                   1481:     $cols_output = 0;
1.40      matthew  1482:     $self->excel_output_row($worksheet,0,$rows_output++,'Summary',
                   1483:                             $format->{'b'});
1.5       matthew  1484:     $rows_output++;    # skip a line
                   1485:     #
1.40      matthew  1486:     $self->excel_rows($connection,$worksheet,$cols_output,$rows_output,
                   1487:                       $format);
1.5       matthew  1488:     #
                   1489:     #
                   1490:     # Close the excel file
                   1491:     $workbook->close();
                   1492:     #
                   1493:     # Write a link to allow them to download it
                   1494:     $r->print('<br />'.
                   1495:               '<a href="'.$filename.'">Your Excel spreadsheet.</a>'."\n");
1.6       matthew  1496:     return;
                   1497: }
                   1498: 
                   1499: #################################
                   1500: ## CSV output routines         ##
                   1501: #################################
                   1502: sub outsheet_csv   {
                   1503:     my $self = shift;
                   1504:     my ($r) = @_;
1.23      matthew  1505:     my $connection = $r->connection();
1.32      matthew  1506:     #
                   1507:     $r->print($self->html_report_error());
                   1508:     $r->rflush();
                   1509:     #
1.6       matthew  1510:     my $csvdata = '';
                   1511:     my @Values;
                   1512:     #
                   1513:     # Open the csv file
                   1514:     my $filename = '/prtspool/'.
1.41      albertel 1515:         $env{'user.name'}.'_'.$env{'user.domain'}.'_'.
1.6       matthew  1516:         time.'_'.rand(1000000000).'.csv';
                   1517:     my $file;
                   1518:     unless ($file = Apache::File->new('>'.'/home/httpd'.$filename)) {
                   1519:         $r->log_error("Couldn't open $filename for output $!");
1.28      www      1520:         $r->print(&mt("Problems occured in writing the csv file.  ".
1.6       matthew  1521:                   "This error has been logged.  ".
1.28      www      1522:                   "Please alert your LON-CAPA administrator."));
1.6       matthew  1523:         $r->print("<pre>\n".$csvdata."</pre>\n");
                   1524:         return 0;
                   1525:     }
                   1526:     #
                   1527:     # Output the title information
                   1528:     foreach my $value ($self->get_title()) {
                   1529:         print $file "'".&Apache::loncommon::csv_translate($value)."'\n";
                   1530:     }
                   1531:     #
                   1532:     # Output the body of the spreadsheet
1.23      matthew  1533:     $self->csv_rows($connection,$file);
1.6       matthew  1534:     #
                   1535:     # Close the csv file
                   1536:     close($file);
                   1537:     $r->print('<br /><br />'.
1.28      www      1538:               '<a href="'.$filename.'">'.&mt('Your CSV spreadsheet.').'</a>'."\n");
1.6       matthew  1539:     #
                   1540:     return 1;
                   1541: }
                   1542: 
                   1543: sub csv_output_row {
                   1544:     my $self = shift;
                   1545:     my ($filehandle,$rownum,@prepend) = @_;
                   1546:     #
                   1547:     my @rowdata = ();
                   1548:     if (defined($rownum)) {
                   1549:         @rowdata = $self->get_row($rownum);
                   1550:     }
                   1551:     my @output = ();
                   1552:     foreach my $cell (@prepend,@rowdata) {
                   1553:         my $value = $cell;
                   1554:         $value = $cell->{'value'} if (ref($value));
                   1555:         $value =~ s/\&nbsp;/ /gi;
                   1556:         $value = "'".$value."'";
                   1557:         push (@output,$value);
                   1558:     }
                   1559:     print $filehandle join(',',@output )."\n";
1.5       matthew  1560:     return;
1.1       matthew  1561: }
                   1562: 
                   1563: ############################################
                   1564: ##          XML output routines           ##
                   1565: ############################################
                   1566: sub outsheet_xml   {
                   1567:     my $self = shift;
                   1568:     my ($r) = @_;
                   1569:     ## Someday XML
                   1570:     ## Will be rendered for the user
                   1571:     ## But not on this day
                   1572:     my $Str = '<spreadsheet type="'.$self->{'type'}.'">'."\n";
1.54      albertel 1573:     $self->check_formulas_loaded();
1.1       matthew  1574:     while (my ($cell,$formula) = each(%{$self->{'formulas'}})) {
1.34      matthew  1575:         if ($cell =~ /^template_(\w+)/) {
1.1       matthew  1576:             my $col = $1;
                   1577:             $Str .= '<template col="'.$col.'">'.$formula.'</template>'."\n";
                   1578:         } else {
1.33      matthew  1579:             my ($col,$row) = ($cell =~ /^([A-z])(\d+)/);
1.1       matthew  1580:             next if (! defined($row) || ! defined($col));
1.33      matthew  1581:             next if ($row != 0);
                   1582:             $Str .= 
                   1583:                 '<field row="'.$row.'" col="'.$col.'" >'.$formula.'</field>'
1.1       matthew  1584:                 ."\n";
                   1585:         }
                   1586:     }
                   1587:     $Str.="</spreadsheet>";
1.34      matthew  1588:     $r->print("<pre>\n\n\n".$Str."\n\n\n</pre>");
1.1       matthew  1589:     return $Str;
                   1590: }
                   1591: 
                   1592: ############################################
                   1593: ###        Filesystem routines           ###
                   1594: ############################################
                   1595: sub parse_sheet {
                   1596:     # $sheetxml is a scalar reference or a scalar
                   1597:     my ($sheetxml) = @_;
                   1598:     if (! ref($sheetxml)) {
                   1599:         my $tmp = $sheetxml;
                   1600:         $sheetxml = \$tmp;
                   1601:     }
                   1602:     my %formulas;
                   1603:     my %sources;
                   1604:     my $parser=HTML::TokeParser->new($sheetxml);
                   1605:     my $token;
                   1606:     while ($token=$parser->get_token) {
                   1607:         if ($token->[0] eq 'S') {
                   1608:             if ($token->[1] eq 'field') {
                   1609:                 my $cell = $token->[2]->{'col'}.$token->[2]->{'row'};
                   1610:                 my $source = $token->[2]->{'source'};
                   1611:                 my $formula = $parser->get_text('/field');
                   1612:                 $formulas{$cell} = $formula;
                   1613:                 $sources{$cell}  = $source if (defined($source));
                   1614:                 $parser->get_text('/field');
1.34      matthew  1615:             } elsif ($token->[1] eq 'template') {
1.1       matthew  1616:                 $formulas{'template_'.$token->[2]->{'col'}}=
                   1617:                     $parser->get_text('/template');
                   1618:             }
                   1619:         }
                   1620:     }
                   1621:     return (\%formulas,\%sources);
                   1622: }
                   1623: 
                   1624: {
                   1625: 
                   1626: my %spreadsheets;
                   1627: 
                   1628: sub clear_spreadsheet_definition_cache {
                   1629:     undef(%spreadsheets);
                   1630: }
                   1631: 
1.13      matthew  1632: sub load_system_default_sheet {
                   1633:     my $self = shift;
                   1634:     my $includedir = $Apache::lonnet::perlvar{'lonIncludes'};
                   1635:     # load in the default defined spreadsheet
                   1636:     my $sheetxml='';
                   1637:     my $fh;
                   1638:     if ($fh=Apache::File->new($includedir.'/default_'.$self->{'type'})) {
                   1639:         $sheetxml=join('',<$fh>);
                   1640:         $fh->close();
                   1641:     } else {
                   1642:         # $sheetxml='<field row="0" col="A">"Error"</field>';
                   1643:         $sheetxml='<field row="0" col="A"></field>';
                   1644:     }
                   1645:     $self->filename('default_');
                   1646:     my ($formulas,undef) = &parse_sheet(\$sheetxml);
                   1647:     return $formulas;
                   1648: }
                   1649: 
1.1       matthew  1650: sub load {
                   1651:     my $self = shift;
                   1652:     #
                   1653:     my $stype = $self->{'type'};
                   1654:     my $cnum  = $self->{'cnum'};
                   1655:     my $cdom  = $self->{'cdom'};
                   1656:     #
1.13      matthew  1657:     my $filename = $self->filename();
1.1       matthew  1658:     my $cachekey = join('_',($cnum,$cdom,$stype,$filename));
                   1659:     #
                   1660:     # see if sheet is cached
                   1661:     my ($formulas);
                   1662:     if (exists($spreadsheets{$cachekey})) {
                   1663:         $formulas = $spreadsheets{$cachekey}->{'formulas'};
1.51      albertel 1664: 	$self->formulas($formulas);
                   1665:         $self->{'row_source'}=$spreadsheets{$cachekey}->{'row_source'};
                   1666:         $self->{'row_numbers'}=$spreadsheets{$cachekey}->{'row_numbers'};
                   1667:         $self->{'maxrow'}=$spreadsheets{$cachekey}->{'maxrow'};
1.57      albertel 1668:     } else {
1.1       matthew  1669:         # Not cached, need to read
1.13      matthew  1670:         if (! defined($filename)) {
                   1671:             $formulas = $self->load_system_default_sheet();
1.33      matthew  1672:         } elsif($filename =~ /^\/res\/.*\.spreadsheet$/) {
1.1       matthew  1673:             # Load a spreadsheet definition file
                   1674:             my $sheetxml=&Apache::lonnet::getfile
                   1675:                 (&Apache::lonnet::filelocation('',$filename));
                   1676:             if ($sheetxml == -1) {
                   1677:                 $sheetxml='<field row="0" col="A">"Error loading spreadsheet '
                   1678:                     .$self->filename().'"</field>';
                   1679:             }
                   1680:             ($formulas,undef) = &parse_sheet(\$sheetxml);
1.13      matthew  1681:             # Get just the filename and set the sheets filename
                   1682:             my ($newfilename) = ($filename =~ /\/([^\/]*)\.spreadsheet$/);
                   1683:             if ($self->is_default()) {
                   1684:                 $self->filename($newfilename);
                   1685:                 $self->make_default();
                   1686:             } else {
                   1687:                 $self->filename($newfilename);
                   1688:             }
1.1       matthew  1689:         } else {
                   1690:             # Load the spreadsheet definition file from the save file
1.13      matthew  1691:             my %tmphash = &Apache::lonnet::dump($filename,$cdom,$cnum);
1.1       matthew  1692:             my ($tmp) = keys(%tmphash);
1.71      albertel 1693:             if (%tmphash
                   1694: 		&& $tmp !~ /^(con_lost|error|no_such_host)/i) {
1.1       matthew  1695:                 while (my ($cell,$formula) = each(%tmphash)) {
                   1696:                     $formulas->{$cell}=$formula;
                   1697:                 }
                   1698:             } else {
1.13      matthew  1699:                 $formulas = $self->load_system_default_sheet();
1.1       matthew  1700:             }
                   1701:         }
1.51      albertel 1702: 	$self->formulas($formulas);
                   1703: 	$self->set_row_sources();
                   1704: 	$self->set_row_numbers();
1.57      albertel 1705: 	$self->cache_sheet($formulas);
                   1706:     }
                   1707: }
                   1708: 
                   1709: sub cache_sheet {
                   1710:     my $self = shift;
1.59      albertel 1711:     my ($formulas) = @_;
1.57      albertel 1712:     my $stype = $self->{'type'};
                   1713:     my $cnum  = $self->{'cnum'};
                   1714:     my $cdom  = $self->{'cdom'};
                   1715:     #
                   1716:     my $filename = $self->filename();
                   1717:     my $cachekey = join('_',($cnum,$cdom,$stype,$filename));
                   1718: 
1.58      albertel 1719:     if (ref($formulas) eq 'HASH') {
1.57      albertel 1720: 	%{$spreadsheets{$cachekey}->{'formulas'}} = %{$formulas};
                   1721:     }
                   1722:     if (ref($self->{'row_source'})) {
                   1723: 	%{$spreadsheets{$cachekey}->{'row_source'}} =%{$self->{'row_source'}};
                   1724:     }
                   1725:     if (ref($self->{'row_numbers'})) {
                   1726: 	%{$spreadsheets{$cachekey}->{'row_numbers'}}=%{$self->{'row_numbers'}};
1.1       matthew  1727:     }
1.57      albertel 1728:     $spreadsheets{$cachekey}->{'maxrow'} = $self->{'maxrow'};
1.1       matthew  1729: }
                   1730: 
                   1731: sub set_row_sources {
                   1732:     my $self = shift;
1.54      albertel 1733:     $self->check_formulas_loaded();
1.1       matthew  1734:     while (my ($cell,$value) = each(%{$self->{'formulas'}})) {
1.22      matthew  1735:         next if ($cell !~ /^A(\d+)/ || $1 < 1);
1.1       matthew  1736:         my $row = $1;
                   1737:         $self->{'row_source'}->{$row} = $value;
                   1738:     }
                   1739:     return;
                   1740: }
                   1741: 
1.12      matthew  1742: sub set_row_numbers {
                   1743:     my $self = shift;
1.54      albertel 1744:     $self->check_formulas_loaded();
1.12      matthew  1745:     while (my ($cell,$value) = each(%{$self->{'formulas'}})) {
                   1746: 	next if ($cell !~ /^A(\d+)$/);
                   1747:         next if (! defined($value));
                   1748: 	$self->{'row_numbers'}->{$value} = $1;
                   1749:         $self->{'maxrow'} = $1 if ($1 > $self->{'maxrow'});
                   1750:     }
                   1751: }
                   1752: 
1.1       matthew  1753: ##
                   1754: ## exportrow is *not* used to get the export row from a computed sub-sheet.
                   1755: ##
                   1756: sub exportrow {
                   1757:     my $self = shift;
1.30      matthew  1758:     if (exists($self->{'badcalc'}) && $self->{'badcalc'}) {
                   1759:         return ();
                   1760:     }
1.1       matthew  1761:     my @exportarray;
                   1762:     foreach my $column (@UC_Columns) {
                   1763:         push(@exportarray,$self->value($column.'0'));
                   1764:     }
                   1765:     return @exportarray;
                   1766: }
                   1767: 
                   1768: sub save {
                   1769:     my $self = shift;
                   1770:     my ($makedef)=@_;
                   1771:     my $cid=$self->{'cid'};
1.4       matthew  1772:     # If we are saving it, it must not be temporary
                   1773:     $self->temporary(0);
1.1       matthew  1774:     if (&Apache::lonnet::allowed('opa',$cid)) {
                   1775:         my %f=$self->formulas();
                   1776:         my $stype = $self->{'type'};
                   1777:         my $cnum  = $self->{'cnum'};
                   1778:         my $cdom  = $self->{'cdom'};
1.12      matthew  1779:         my $filename    = $self->{'filename'};
1.1       matthew  1780:         # Cache new sheet
1.57      albertel 1781: 	$self->cache_sheet(\%f);
1.1       matthew  1782:         # Write sheet
                   1783:         foreach (keys(%f)) {
                   1784:             delete($f{$_}) if ($f{$_} eq 'import');
                   1785:         }
1.12      matthew  1786:         my $reply = &Apache::lonnet::put($filename,\%f,$cdom,$cnum);
1.1       matthew  1787:         return $reply if ($reply ne 'ok');
                   1788:         $reply = &Apache::lonnet::put($stype.'_spreadsheets',
1.41      albertel 1789:                      {$filename => $env{'user.name'}.'@'.$env{'user.domain'}},
1.1       matthew  1790:                                       $cdom,$cnum);
                   1791:         return $reply if ($reply ne 'ok');
                   1792:         if ($makedef) { 
                   1793:             $reply = &Apache::lonnet::put('environment',
1.12      matthew  1794:                                 {'spreadsheet_default_'.$stype => $filename },
1.1       matthew  1795:                                           $cdom,$cnum);
                   1796:             return $reply if ($reply ne 'ok');
1.55      albertel 1797: 	    &Apache::lonnet::appenv('course.'.$self->{'cid'}.'.spreadsheet_default_'.
                   1798: 				    $self->{'type'} => $self->filename());
1.1       matthew  1799:         } 
                   1800:         if ($self->is_default()) {
1.22      matthew  1801:             if ($self->{'type'} eq 'studentcalc') {
                   1802:                 &Apache::lonnet::expirespread('','','studentcalc','');
                   1803:             } elsif ($self->{'type'} eq 'assesscalc') {
                   1804:                 &Apache::lonnet::expirespread('','','assesscalc','');
1.16      matthew  1805:                 &Apache::lonnet::expirespread('','','studentcalc','');
                   1806:             }
1.1       matthew  1807:         }
                   1808:         return $reply;
                   1809:     }
                   1810:     return 'unauthorized';
                   1811: }
                   1812: 
                   1813: } # end of scope for %spreadsheets
                   1814: 
                   1815: sub save_tmp {
                   1816:     my $self = shift;
1.41      albertel 1817:     my $filename=$env{'user.name'}.'_'.
                   1818:         $env{'user.domain'}.'_spreadsheet_'.$self->{'symb'}.'_'.
1.1       matthew  1819:            $self->{'filename'};
1.9       matthew  1820:     $filename=~s/\W/\_/g;
                   1821:     $filename=$Apache::lonnet::tmpdir.$filename.'.tmp';
1.4       matthew  1822:     $self->temporary(1);
1.1       matthew  1823:     my $fh;
1.9       matthew  1824:     if ($fh=Apache::File->new('>'.$filename)) {
1.1       matthew  1825:         my %f = $self->formulas();
                   1826:         while( my ($cell,$formula) = each(%f)) {
                   1827:             next if ($formula eq 'import');
1.69      www      1828:             print $fh &escape($cell)."=".
                   1829:                 &escape($formula)."\n";
1.1       matthew  1830:         }
                   1831:         $fh->close();
                   1832:     }
                   1833: }
                   1834: 
                   1835: sub load_tmp {
                   1836:     my $self = shift;
1.41      albertel 1837:     my $filename=$env{'user.name'}.'_'.
                   1838:         $env{'user.domain'}.'_spreadsheet_'.$self->{'symb'}.'_'.
1.1       matthew  1839:             $self->{'filename'};
                   1840:     $filename=~s/\W/\_/g;
                   1841:     $filename=$Apache::lonnet::tmpdir.$filename.'.tmp';
                   1842:     my %formulas = ();
                   1843:     if (my $spreadsheet_file = Apache::File->new($filename)) {
                   1844:         while (<$spreadsheet_file>) {
                   1845: 	    chomp;
                   1846:             my ($cell,$formula) = split(/=/);
1.69      www      1847:             $cell    = &unescape($cell);
                   1848:             $formula = &unescape($formula);
1.1       matthew  1849:             $formulas{$cell} = $formula;
                   1850:         }
                   1851:         $spreadsheet_file->close();
                   1852:     }
1.4       matthew  1853:     # flag the sheet as temporary
                   1854:     $self->temporary(1);
1.1       matthew  1855:     $self->formulas(\%formulas);
                   1856:     $self->set_row_sources();
                   1857:     $self->set_row_numbers();
                   1858:     return;
1.4       matthew  1859: }
                   1860: 
                   1861: sub temporary {
                   1862:     my $self=shift;
                   1863:     if (@_) {
                   1864:         ($self->{'temporary'})= @_;
                   1865:     }
                   1866:     return $self->{'temporary'};
1.1       matthew  1867: }
                   1868: 
                   1869: sub modify_cell {
                   1870:     # studentcalc overrides this
                   1871:     my $self = shift;
                   1872:     my ($cell,$formula) = @_;
                   1873:     if ($cell =~ /([A-z])\-/) {
                   1874:         $cell = 'template_'.$1;
                   1875:     } elsif ($cell !~ /^([A-z](\d+)|template_[A-z])$/) {
                   1876:         return;
                   1877:     }
                   1878:     $self->set_formula($cell,$formula);
                   1879:     $self->rebuild_stats();
                   1880:     return;
                   1881: }
                   1882: 
                   1883: ###########################################
                   1884: # othersheets: Returns the list of other spreadsheets available 
                   1885: ###########################################
                   1886: sub othersheets {
                   1887:     my $self = shift(); 
                   1888:     my ($stype) = @_;
                   1889:     $stype = $self->{'type'} if (! defined($stype) || $stype !~ /calc$/);
                   1890:     #
1.71      albertel 1891:     my @alternatives=(&mt('Default'), &mt('LON-CAPA Standard'));
1.1       matthew  1892:     my %results=&Apache::lonnet::dump($stype.'_spreadsheets',
                   1893:                                       $self->{'cdom'}, $self->{'cnum'});
                   1894:     my ($tmp) = keys(%results);
1.71      albertel 1895:     if (%results
1.72      albertel 1896: 	&& $tmp !~ /^(con_lost|error|no_such_host)/i ) {
1.71      albertel 1897:         push(@alternatives, sort(keys(%results)));
1.1       matthew  1898:     }
                   1899:     return @alternatives; 
1.3       matthew  1900: }
                   1901: 
                   1902: sub blackout {
                   1903:     my $self = shift;
                   1904:     $self->{'blackout'} = $_[0] if (@_);
                   1905:     return $self->{'blackout'};
1.1       matthew  1906: }
                   1907: 
                   1908: sub get_row {
                   1909:     my $self = shift;
                   1910:     my ($n)=@_;
                   1911:     my @cols=();
                   1912:     foreach my $col (@UC_Columns,@LC_Columns) {
                   1913:         my $cell = $col.$n;
                   1914:         push(@cols,{ name    => $cell,
                   1915:                      formula => $self->formula($cell),
                   1916:                      value   => $self->value($cell)});
                   1917:     }
                   1918:     return @cols;
                   1919: }
                   1920: 
                   1921: sub get_template_row {
                   1922:     my $self = shift;
                   1923:     my @cols=();
                   1924:     foreach my $col (@UC_Columns,@LC_Columns) {
                   1925:         my $cell = 'template_'.$col;
                   1926:         push(@cols,{ name    => $cell,
                   1927:                      formula => $self->formula($cell),
                   1928:                      value   => $self->formula($cell) });
                   1929:     }
                   1930:     return @cols;
                   1931: }
                   1932: 
1.12      matthew  1933: sub need_to_save {
1.1       matthew  1934:     my $self = shift;
1.12      matthew  1935:     if ($self->{'new_rows'} && ! $self->temporary()) {
                   1936:         return 1;
1.1       matthew  1937:     }
1.12      matthew  1938:     return 0;
1.1       matthew  1939: }
                   1940: 
                   1941: sub get_row_number_from_key {
                   1942:     my $self = shift;
                   1943:     my ($key) = @_;
                   1944:     if (! exists($self->{'row_numbers'}->{$key}) ||
                   1945:         ! defined($self->{'row_numbers'}->{$key})) {
                   1946:         # I used to set $f here to the new value, but the key passed for lookup
                   1947:         # may not be the key we need to save
                   1948: 	$self->{'maxrow'}++;
                   1949: 	$self->{'row_numbers'}->{$key} = $self->{'maxrow'};
1.13      matthew  1950: #        $self->logthis('added row '.$self->{'row_numbers'}->{$key}.
                   1951: #                       ' for '.$key);
1.12      matthew  1952:         $self->{'new_rows'} = 1;
1.1       matthew  1953:     }
                   1954:     return $self->{'row_numbers'}->{$key};
                   1955: }
                   1956: 
                   1957: 1;
                   1958: 
                   1959: __END__

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