File:
[LON-CAPA] /
loncom /
interface /
Attic /
lonspreadsheet.pm
Revision
1.157:
download - view:
text,
annotated -
select for diffs
Mon Dec 9 16:06:56 2002 UTC (21 years, 7 months ago) by
matthew
Branches:
MAIN
CVS tags:
HEAD
Added 'else' clause to check for successful loading of perviously saved
spreadsheet files. If we are unable to load the proper spreadsheet file, we now load the default. If we are unable to load the default, we tell the user.
This change is mostly to make life easier for me as I test new default
spreadsheets.
1: #
2: # $Id: lonspreadsheet.pm,v 1.157 2002/12/09 16:06:56 matthew Exp $
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: lonspreadsheet
34:
35: =head1 SYNOPSIS
36:
37: Spreadsheet interface to internal LON-CAPA data
38:
39: =head1 DESCRIPTION
40:
41: Lonspreadsheet provides course coordinators the ability to manage their
42: students grades online. The students are able to view their own grades, but
43: not the grades of their peers. The spreadsheet is highly customizable,
44: offering the ability to use Perl code to manipulate data, as well as many
45: built-in functions.
46:
47: =head2 Functions available to user of lonspreadsheet
48:
49: =over 4
50:
51: =cut
52:
53: package Apache::lonspreadsheet;
54:
55: use strict;
56: use Apache::Constants qw(:common :http);
57: use Apache::lonnet;
58: use Apache::lonhtmlcommon;
59: use Apache::loncoursedata;
60: use Apache::File();
61: use Safe;
62: use Safe::Hole;
63: use Opcode;
64: use GDBM_File;
65: use HTML::Entities();
66: use HTML::TokeParser;
67: use Spreadsheet::WriteExcel;
68:
69: #
70: # Caches for coursewide information
71: #
72: my %Section;
73:
74: #
75: # Caches for previously calculated spreadsheets
76: #
77:
78: my %oldsheets;
79: my %loadedcaches;
80: my %expiredates;
81:
82: #
83: # Cache for stores of an individual user
84: #
85:
86: my $cachedassess;
87: my %cachedstores;
88:
89: #
90: # These cache hashes need to be independent of user, resource and course
91: # (user and course can/should be in the keys)
92: #
93:
94: my %spreadsheets;
95: my %courserdatas;
96: my %userrdatas;
97: my %defaultsheets;
98: my %rowlabel_cache;
99:
100: #
101: # These global hashes are dependent on user, course and resource,
102: # and need to be initialized every time when a sheet is calculated
103: #
104: my %courseopt;
105: my %useropt;
106: my %parmhash;
107:
108: #
109: # Some hashes for stats on timing and performance
110: #
111:
112: my %starttimes;
113: my %usedtimes;
114: my %numbertimes;
115:
116: # Stuff that only the screen handler can know
117:
118: my $includedir;
119: my $tmpdir;
120:
121: # =============================================================================
122: # ===================================== Implements an instance of a spreadsheet
123:
124: ##
125: ## mask - used to reside in the safe space.
126: ##
127: sub mask {
128: my ($lower,$upper)=@_;
129: $upper = $lower if (! defined($upper));
130: #
131: my ($la,$ld) = ($lower=~/([A-Za-z]|\*)(\d+|\*)/);
132: my ($ua,$ud) = ($upper=~/([A-Za-z]|\*)(\d+|\*)/);
133: #
134: my $alpha='';
135: my $num='';
136: #
137: if (($la eq '*') || ($ua eq '*')) {
138: $alpha='[A-Za-z]';
139: } else {
140: if (($la=~/[A-Z]/) && ($ua=~/[A-Z]/) ||
141: ($la=~/[a-z]/) && ($ua=~/[a-z]/)) {
142: $alpha='['.$la.'-'.$ua.']';
143: } else {
144: $alpha='['.$la.'-Za-'.$ua.']';
145: }
146: }
147: if (($ld eq '*') || ($ud eq '*')) {
148: $num='\d+';
149: } else {
150: if (length($ld)!=length($ud)) {
151: $num.='(';
152: foreach ($ld=~m/\d/g) {
153: $num.='['.$_.'-9]';
154: }
155: if (length($ud)-length($ld)>1) {
156: $num.='|\d{'.(length($ld)+1).','.(length($ud)-1).'}';
157: }
158: $num.='|';
159: foreach ($ud=~m/\d/g) {
160: $num.='[0-'.$_.']';
161: }
162: $num.=')';
163: } else {
164: my @lda=($ld=~m/\d/g);
165: my @uda=($ud=~m/\d/g);
166: my $i;
167: my $j=0;
168: my $notdone=1;
169: for ($i=0;($i<=$#lda)&&($notdone);$i++) {
170: if ($lda[$i]==$uda[$i]) {
171: $num.=$lda[$i];
172: $j=$i;
173: } else {
174: $notdone=0;
175: }
176: }
177: if ($j<$#lda-1) {
178: $num.='('.$lda[$j+1];
179: for ($i=$j+2;$i<=$#lda;$i++) {
180: $num.='['.$lda[$i].'-9]';
181: }
182: if ($uda[$j+1]-$lda[$j+1]>1) {
183: $num.='|['.($lda[$j+1]+1).'-'.($uda[$j+1]-1).']\d{'.
184: ($#lda-$j-1).'}';
185: }
186: $num.='|'.$uda[$j+1];
187: for ($i=$j+2;$i<=$#uda;$i++) {
188: $num.='[0-'.$uda[$i].']';
189: }
190: $num.=')';
191: } else {
192: if ($lda[-1]!=$uda[-1]) {
193: $num.='['.$lda[-1].'-'.$uda[-1].']';
194: }
195: }
196: }
197: }
198: return '^'.$alpha.$num."\$";
199: }
200:
201: sub initsheet {
202: my $safeeval = new Safe(shift);
203: my $safehole = new Safe::Hole;
204: $safeeval->permit("entereval");
205: $safeeval->permit(":base_math");
206: $safeeval->permit("sort");
207: $safeeval->deny(":base_io");
208: $safehole->wrap(\&Apache::lonnet::EXT,$safeeval,'&EXT');
209: $safehole->wrap(\&Apache::lonspreadsheet::mask,$safeeval,'&mask');
210: $safeeval->share('$@');
211: my $code=<<'ENDDEFS';
212: # ---------------------------------------------------- Inside of the safe space
213:
214: #
215: # f: formulas
216: # t: intermediate format (variable references expanded)
217: # v: output values
218: # c: preloaded constants (A-column)
219: # rl: row label
220: # os: other spreadsheets (for student spreadsheet only)
221:
222: undef %sheet_values; # Holds the (computed, final) values for the sheet
223: # This is only written to by &calc, the spreadsheet computation routine.
224: # It is read by many functions
225: undef %t; # Holds the values of the spreadsheet temporarily. Set in &sett,
226: # which does the translation of strings like C5 into the value in C5.
227: # Used in &calc - %t holds the values that are actually eval'd.
228: undef %f; # Holds the formulas for each cell. This is the users
229: # (spreadsheet authors) data for each cell.
230: # set by &setformulas and returned by &getformulas
231: # &setformulas is called by &readsheet, &tmpread, &updateclasssheet,
232: # &updatestudentassesssheet, &loadstudent, &loadcourse
233: # &getformulas is called by &writesheet, &tmpwrite, &updateclasssheet,
234: # &updatestudentassesssheet, &loadstudent, &loadcourse, &loadassessment,
235: undef %c; # Holds the constants for a sheet. In the assessment
236: # sheets, this is the A column. Used in &MINPARM, &MAXPARM, &expandnamed,
237: # &sett, and &setconstants. There is no &getconstants.
238: # &setconstants is called by &loadstudent, &loadcourse, &load assessment,
239: undef @os; # Holds the names of other spreadsheets - this is used to specify
240: # the spreadsheets that are available for the assessment sheet.
241: # Set by &setothersheets. &setothersheets is called by &handler. A
242: # related subroutine is &othersheets.
243: #$errorlog = '';
244:
245: $maxrow = 0;
246: $sheettype = '';
247:
248: # filename/reference of the sheet
249: $filename = '';
250:
251: # user data
252: $uname = '';
253: $uhome = '';
254: $udom = '';
255:
256: # course data
257:
258: $csec = '';
259: $chome= '';
260: $cnum = '';
261: $cdom = '';
262: $cid = '';
263: $coursefilename = '';
264:
265: # symb
266:
267: $usymb = '';
268:
269: # error messages
270: $errormsg = '';
271:
272:
273: #-------------------------------------------------------
274:
275: =item UWCALC(hashname,modules,units,date)
276:
277: returns the proportion of the module
278: weights not previously completed by the student.
279:
280: =over 4
281:
282: =item hashname
283:
284: name of the hash the module dates have been inserted into
285:
286: =item modules
287:
288: reference to a cell which contains a comma deliminated list of modules
289: covered by the assignment.
290:
291: =item units
292:
293: reference to a cell which contains a comma deliminated list of module
294: weights with respect to the assignment
295:
296: =item date
297:
298: reference to a cell which contains the date the assignment was completed.
299:
300: =back
301:
302: =cut
303:
304: #-------------------------------------------------------
305: sub UWCALC {
306: my ($hashname,$modules,$units,$date) = @_;
307: my @Modules = split(/,/,$modules);
308: my @Units = split(/,/,$units);
309: my $total_weight;
310: foreach (@Units) {
311: $total_weight += $_;
312: }
313: my $usum=0;
314: for (my $i=0; $i<=$#Modules; $i++) {
315: if (&HASH($hashname,$Modules[$i]) eq $date) {
316: $usum += $Units[$i];
317: }
318: }
319: return $usum/$total_weight;
320: }
321:
322: #-------------------------------------------------------
323:
324: =item CDLSUM(list)
325:
326: returns the sum of the elements in a cell which contains
327: a Comma Deliminate List of numerical values.
328: 'list' is a reference to a cell which contains a comma deliminated list.
329:
330: =cut
331:
332: #-------------------------------------------------------
333: sub CDLSUM {
334: my ($list)=@_;
335: my $sum;
336: foreach (split/,/,$list) {
337: $sum += $_;
338: }
339: return $sum;
340: }
341:
342: #-------------------------------------------------------
343:
344: =item CDLITEM(list,index)
345:
346: returns the item at 'index' in a Comma Deliminated List.
347:
348: =over 4
349:
350: =item list
351:
352: reference to a cell which contains a comma deliminated list.
353:
354: =item index
355:
356: the Perl index of the item requested (first element in list has
357: an index of 0)
358:
359: =back
360:
361: =cut
362:
363: #-------------------------------------------------------
364: sub CDLITEM {
365: my ($list,$index)=@_;
366: my @Temp = split/,/,$list;
367: return $Temp[$index];
368: }
369:
370: #-------------------------------------------------------
371:
372: =item CDLHASH(name,key,value)
373:
374: loads a comma deliminated list of keys into
375: the hash 'name', all with a value of 'value'.
376:
377: =over 4
378:
379: =item name
380:
381: name of the hash.
382:
383: =item key
384:
385: (a pointer to) a comma deliminated list of keys.
386:
387: =item value
388:
389: a single value to be entered for each key.
390:
391: =back
392:
393: =cut
394:
395: #-------------------------------------------------------
396: sub CDLHASH {
397: my ($name,$key,$value)=@_;
398: my @Keys;
399: my @Values;
400: # Check to see if we have multiple $key values
401: if ($key =~ /[A-z](\-[A-z])?\d+(\-\d+)?/) {
402: my $keymask = &mask($key);
403: # Assume the keys are addresses
404: my @Temp = grep /$keymask/,keys(%sheet_values);
405: @Keys = $sheet_values{@Temp};
406: } else {
407: $Keys[0]= $key;
408: }
409: my @Temp;
410: foreach $key (@Keys) {
411: @Temp = (@Temp, split/,/,$key);
412: }
413: @Keys = @Temp;
414: if ($value =~ /[A-z](\-[A-z])?\d+(\-\d+)?/) {
415: my $valmask = &mask($value);
416: my @Temp = grep /$valmask/,keys(%sheet_values);
417: @Values =$sheet_values{@Temp};
418: } else {
419: $Values[0]= $value;
420: }
421: $value = $Values[0];
422: # Add values to hash
423: for (my $i = 0; $i<=$#Keys; $i++) {
424: my $key = $Keys[$i];
425: if (! exists ($hashes{$name}->{$key})) {
426: $hashes{$name}->{$key}->[0]=$value;
427: } else {
428: my @Temp = sort(@{$hashes{$name}->{$key}},$value);
429: $hashes{$name}->{$key} = \@Temp;
430: }
431: }
432: return "hash '$name' updated";
433: }
434:
435: #-------------------------------------------------------
436:
437: =item GETHASH(name,key,index)
438:
439: returns the element in hash 'name'
440: reference by the key 'key', at index 'index' in the values list.
441:
442: =cut
443:
444: #-------------------------------------------------------
445: sub GETHASH {
446: my ($name,$key,$index)=@_;
447: if (! defined($index)) {
448: $index = 0;
449: }
450: if ($key =~ /^[A-z]\d+$/) {
451: $key = $sheet_values{$key};
452: }
453: return $hashes{$name}->{$key}->[$index];
454: }
455:
456: #-------------------------------------------------------
457:
458: =item CLEARHASH(name)
459:
460: clears all the values from the hash 'name'
461:
462: =item CLEARHASH(name,key)
463:
464: clears all the values from the hash 'name' associated with the given key.
465:
466: =cut
467:
468: #-------------------------------------------------------
469: sub CLEARHASH {
470: my ($name,$key)=@_;
471: if (defined($key)) {
472: if (exists($hashes{$name}->{$key})) {
473: $hashes{$name}->{$key}=undef;
474: return "hash '$name' key '$key' cleared";
475: }
476: } else {
477: if (exists($hashes{$name})) {
478: $hashes{$name}=undef;
479: return "hash '$name' cleared";
480: }
481: }
482: return "Error in clearing hash";
483: }
484:
485: #-------------------------------------------------------
486:
487: =item HASH(name,key,value)
488:
489: loads values into an internal hash. If a key
490: already has a value associated with it, the values are sorted numerically.
491:
492: =item HASH(name,key)
493:
494: returns the 0th value in the hash 'name' associated with 'key'.
495:
496: =cut
497:
498: #-------------------------------------------------------
499: sub HASH {
500: my ($name,$key,$value)=@_;
501: my @Keys;
502: undef @Keys;
503: my @Values;
504: # Check to see if we have multiple $key values
505: if ($key =~ /[A-z](\-[A-z])?\d+(\-\d+)?/) {
506: my $keymask = &mask($key);
507: # Assume the keys are addresses
508: my @Temp = grep /$keymask/,keys(%sheet_values);
509: @Keys = $sheet_values{@Temp};
510: } else {
511: $Keys[0]= $key;
512: }
513: # If $value is empty, return the first value associated
514: # with the first key.
515: if (! $value) {
516: return $hashes{$name}->{$Keys[0]}->[0];
517: }
518: # Check to see if we have multiple $value(s)
519: if ($value =~ /[A-z](\-[A-z])?\d+(\-\d+)?/) {
520: my $valmask = &mask($value);
521: my @Temp = grep /$valmask/,keys(%sheet_values);
522: @Values =$sheet_values{@Temp};
523: } else {
524: $Values[0]= $value;
525: }
526: # Add values to hash
527: for (my $i = 0; $i<=$#Keys; $i++) {
528: my $key = $Keys[$i];
529: my $value = ($i<=$#Values ? $Values[$i] : $Values[0]);
530: if (! exists ($hashes{$name}->{$key})) {
531: $hashes{$name}->{$key}->[0]=$value;
532: } else {
533: my @Temp = sort(@{$hashes{$name}->{$key}},$value);
534: $hashes{$name}->{$key} = \@Temp;
535: }
536: }
537: return $Values[-1];
538: }
539:
540: #-------------------------------------------------------
541:
542: =item NUM(range)
543:
544: returns the number of items in the range.
545:
546: =cut
547:
548: #-------------------------------------------------------
549: sub NUM {
550: my $mask=mask(@_);
551: my $num= $#{@{grep(/$mask/,keys(%sheet_values))}}+1;
552: return $num;
553: }
554:
555: sub BIN {
556: my ($low,$high,$lower,$upper)=@_;
557: my $mask=mask($lower,$upper);
558: my $num=0;
559: foreach (grep /$mask/,keys(%sheet_values)) {
560: if (($sheet_values{$_}>=$low) && ($sheet_values{$_}<=$high)) {
561: $num++;
562: }
563: }
564: return $num;
565: }
566:
567:
568: #-------------------------------------------------------
569:
570: =item SUM(range)
571:
572: returns the sum of items in the range.
573:
574: =cut
575:
576: #-------------------------------------------------------
577: sub SUM {
578: my $mask=mask(@_);
579: my $sum=0;
580: foreach (grep /$mask/,keys(%sheet_values)) {
581: $sum+=$sheet_values{$_};
582: }
583: return $sum;
584: }
585:
586: #-------------------------------------------------------
587:
588: =item MEAN(range)
589:
590: compute the average of the items in the range.
591:
592: =cut
593:
594: #-------------------------------------------------------
595: sub MEAN {
596: my $mask=mask(@_);
597: my $sum=0;
598: my $num=0;
599: foreach (grep /$mask/,keys(%sheet_values)) {
600: $sum+=$sheet_values{$_};
601: $num++;
602: }
603: if ($num) {
604: return $sum/$num;
605: } else {
606: return undef;
607: }
608: }
609:
610: #-------------------------------------------------------
611:
612: =item STDDEV(range)
613:
614: compute the standard deviation of the items in the range.
615:
616: =cut
617:
618: #-------------------------------------------------------
619: sub STDDEV {
620: my $mask=mask(@_);
621: my $sum=0; my $num=0;
622: foreach (grep /$mask/,keys(%sheet_values)) {
623: $sum+=$sheet_values{$_};
624: $num++;
625: }
626: unless ($num>1) { return undef; }
627: my $mean=$sum/$num;
628: $sum=0;
629: foreach (grep /$mask/,keys(%sheet_values)) {
630: $sum+=($sheet_values{$_}-$mean)**2;
631: }
632: return sqrt($sum/($num-1));
633: }
634:
635: #-------------------------------------------------------
636:
637: =item PROD(range)
638:
639: compute the product of the items in the range.
640:
641: =cut
642:
643: #-------------------------------------------------------
644: sub PROD {
645: my $mask=mask(@_);
646: my $prod=1;
647: foreach (grep /$mask/,keys(%sheet_values)) {
648: $prod*=$sheet_values{$_};
649: }
650: return $prod;
651: }
652:
653: #-------------------------------------------------------
654:
655: =item MAX(range)
656:
657: compute the maximum of the items in the range.
658:
659: =cut
660:
661: #-------------------------------------------------------
662: sub MAX {
663: my $mask=mask(@_);
664: my $max='-';
665: foreach (grep /$mask/,keys(%sheet_values)) {
666: unless ($max) { $max=$sheet_values{$_}; }
667: if (($sheet_values{$_}>$max) || ($max eq '-')) { $max=$sheet_values{$_}; }
668: }
669: return $max;
670: }
671:
672: #-------------------------------------------------------
673:
674: =item MIN(range)
675:
676: compute the minimum of the items in the range.
677:
678: =cut
679:
680: #-------------------------------------------------------
681: sub MIN {
682: my $mask=mask(@_);
683: my $min='-';
684: foreach (grep /$mask/,keys(%sheet_values)) {
685: unless ($max) { $max=$sheet_values{$_}; }
686: if (($sheet_values{$_}<$min) || ($min eq '-')) {
687: $min=$sheet_values{$_};
688: }
689: }
690: return $min;
691: }
692:
693: #-------------------------------------------------------
694:
695: =item SUMMAX(num,lower,upper)
696:
697: compute the sum of the largest 'num' items in the range from
698: 'lower' to 'upper'
699:
700: =cut
701:
702: #-------------------------------------------------------
703: sub SUMMAX {
704: my ($num,$lower,$upper)=@_;
705: my $mask=mask($lower,$upper);
706: my @inside=();
707: foreach (grep /$mask/,keys(%sheet_values)) {
708: push (@inside,$sheet_values{$_});
709: }
710: @inside=sort(@inside);
711: my $sum=0; my $i;
712: for ($i=$#inside;(($i>$#inside-$num) && ($i>=0));$i--) {
713: $sum+=$inside[$i];
714: }
715: return $sum;
716: }
717:
718: #-------------------------------------------------------
719:
720: =item SUMMIN(num,lower,upper)
721:
722: compute the sum of the smallest 'num' items in the range from
723: 'lower' to 'upper'
724:
725: =cut
726:
727: #-------------------------------------------------------
728: sub SUMMIN {
729: my ($num,$lower,$upper)=@_;
730: my $mask=mask($lower,$upper);
731: my @inside=();
732: foreach (grep /$mask/,keys(%sheet_values)) {
733: $inside[$#inside+1]=$sheet_values{$_};
734: }
735: @inside=sort(@inside);
736: my $sum=0; my $i;
737: for ($i=0;(($i<$num) && ($i<=$#inside));$i++) {
738: $sum+=$inside[$i];
739: }
740: return $sum;
741: }
742:
743: #-------------------------------------------------------
744:
745: =item MINPARM(parametername)
746:
747: Returns the minimum value of the parameters matching the parametername.
748: parametername should be a string such as 'duedate'.
749:
750: =cut
751:
752: #-------------------------------------------------------
753: sub MINPARM {
754: my ($expression) = @_;
755: my $min = undef;
756: study($expression);
757: foreach $parameter (keys(%c)) {
758: next if ($parameter !~ /$expression/);
759: if ((! defined($min)) || ($min > $c{$parameter})) {
760: $min = $c{$parameter}
761: }
762: }
763: return $min;
764: }
765:
766: #-------------------------------------------------------
767:
768: =item MAXPARM(parametername)
769:
770: Returns the maximum value of the parameters matching the input parameter name.
771: parametername should be a string such as 'duedate'.
772:
773: =cut
774:
775: #-------------------------------------------------------
776: sub MAXPARM {
777: my ($expression) = @_;
778: my $max = undef;
779: study($expression);
780: foreach $parameter (keys(%c)) {
781: next if ($parameter !~ /$expression/);
782: if ((! defined($min)) || ($max < $c{$parameter})) {
783: $max = $c{$parameter}
784: }
785: }
786: return $max;
787: }
788:
789: #--------------------------------------------------------
790: sub expandnamed {
791: my $expression=shift;
792: if ($expression=~/^\&/) {
793: my ($func,$var,$formula)=($expression=~/^\&(\w+)\(([^\;]+)\;(.*)\)/);
794: my @vars=split(/\W+/,$formula);
795: my %values=();
796: undef %values;
797: foreach ( @vars ) {
798: my $varname=$_;
799: if ($varname=~/\D/) {
800: $formula=~s/$varname/'$c{\''.$varname.'\'}'/ge;
801: $varname=~s/$var/\(\\w\+\)/g;
802: foreach (keys(%c)) {
803: if ($_=~/$varname/) {
804: $values{$1}=1;
805: }
806: }
807: }
808: }
809: if ($func eq 'EXPANDSUM') {
810: my $result='';
811: foreach (keys(%values)) {
812: my $thissum=$formula;
813: $thissum=~s/$var/$_/g;
814: $result.=$thissum.'+';
815: }
816: $result=~s/\+$//;
817: return $result;
818: } else {
819: return 0;
820: }
821: } else {
822: # it is not a function, so it is a parameter name
823: # We should do the following:
824: # 1. Take the list of parameter names
825: # 2. look through the list for ones that match the parameter we want
826: # 3. If there are no collisions, return the one that matches
827: # 4. If there is a collision, return 'bad parameter name error'
828: my $returnvalue = '';
829: my @matches = ();
830: $#matches = -1;
831: study $expression;
832: foreach $parameter (keys(%c)) {
833: push @matches,$parameter if ($parameter =~ /$expression/);
834: }
835: if (scalar(@matches) == 0) {
836: $returnvalue = 'unmatched parameter: '.$parameter;
837: } elsif (scalar(@matches) == 1) {
838: $returnvalue = '$c{\''.$matches[0].'\'}';
839: } elsif (scalar(@matches) > 0) {
840: # more than one match. Look for a concise one
841: $returnvalue = "'non-unique parameter name : $expression'";
842: foreach (@matches) {
843: if (/^$expression$/) {
844: $returnvalue = '$c{\''.$_.'\'}';
845: }
846: }
847: } else {
848: # There was a negative number of matches, which indicates
849: # something is wrong with reality. Better warn the user.
850: $returnvalue = 'bizzare parameter: '.$parameter;
851: }
852: return $returnvalue;
853: }
854: }
855:
856: sub sett {
857: %t=();
858: my $pattern='';
859: if ($sheettype eq 'assesscalc') {
860: $pattern='A';
861: } else {
862: $pattern='[A-Z]';
863: }
864: # Deal with the template row
865: foreach (keys(%f)) {
866: next if ($_!~/template\_(\w)/);
867: my $col=$1;
868: next if ($col=~/^$pattern/);
869: foreach (keys(%f)) {
870: next if ($_!~/A(\d+)/);
871: my $trow=$1;
872: next if (! $trow);
873: # Get the name of this cell
874: my $lb=$col.$trow;
875: # Grab the template declaration
876: $t{$lb}=$f{'template_'.$col};
877: # Replace '#' with the row number
878: $t{$lb}=~s/\#/$trow/g;
879: # Replace '....' with ','
880: $t{$lb}=~s/\.\.+/\,/g;
881: # Replace 'A0' with the value from 'A0'
882: $t{$lb}=~s/(^|[^\"\'])([A-Za-z]\d+)/$1\$sheet_values\{\'$2\'\}/g;
883: # Replace parameters
884: $t{$lb}=~s/(^|[^\"\'])\[([^\]]+)\]/$1.&expandnamed($2)/ge;
885: }
886: }
887: # Deal with the normal cells
888: foreach (keys(%f)) {
889: if (exists($f{$_}) && ($_!~/template\_/)) {
890: my $matches=($_=~/^$pattern(\d+)/);
891: if (($matches) && ($1)) {
892: unless ($f{$_}=~/^\!/) {
893: $t{$_}=$c{$_};
894: }
895: } else {
896: $t{$_}=$f{$_};
897: $t{$_}=~s/\.\.+/\,/g;
898: $t{$_}=~s/(^|[^\"\'])([A-Za-z]\d+)/$1\$sheet_values\{\'$2\'\}/g;
899: $t{$_}=~s/(^|[^\"\'])\[([^\]]+)\]/$1.&expandnamed($2)/ge;
900: }
901: }
902: }
903: # For inserted lines, [B-Z] is also valid
904: unless ($sheettype eq 'assesscalc') {
905: foreach (keys(%f)) {
906: if ($_=~/[B-Z](\d+)/) {
907: if ($f{'A'.$1}=~/^[\~\-]/) {
908: $t{$_}=$f{$_};
909: $t{$_}=~s/\.\.+/\,/g;
910: $t{$_}=~s/(^|[^\"\'])([A-Za-z]\d+)/$1\$sheet_values\{\'$2\'\}/g;
911: $t{$_}=~s/(^|[^\"\'])\[([^\]]+)\]/$1.&expandnamed($2)/ge;
912: }
913: }
914: }
915: }
916: # For some reason 'A0' gets special treatment... This seems superfluous
917: # but I imagine it is here for a reason.
918: $t{'A0'}=$f{'A0'};
919: $t{'A0'}=~s/\.\.+/\,/g;
920: $t{'A0'}=~s/(^|[^\"\'])([A-Za-z]\d+)/$1\$sheet_values\{\'$2\'\}/g;
921: $t{'A0'}=~s/(^|[^\"\'])\[([^\]]+)\]/$1.&expandnamed($2)/ge;
922: }
923:
924: sub calc {
925: undef %sheet_values;
926: &sett();
927: my $notfinished=1;
928: my $lastcalc='';
929: my $depth=0;
930: while ($notfinished) {
931: $notfinished=0;
932: foreach (keys(%t)) {
933: #$errorlog .= "$_:".$t{$_};
934: my $old=$sheet_values{$_};
935: $sheet_values{$_}=eval $t{$_};
936: if ($@) {
937: undef %sheet_values;
938: return $_.': '.$@;
939: }
940: if ($sheet_values{$_} ne $old) { $notfinished=1; $lastcalc=$_; }
941: #$errorlog .= ":".$sheet_values{$_}."\n";
942: }
943: $depth++;
944: if ($depth>100) {
945: undef %sheet_values;
946: return $lastcalc.': Maximum calculation depth exceeded';
947: }
948: }
949: return '';
950: }
951:
952: # ------------------------------------------- End of "Inside of the safe space"
953: ENDDEFS
954: $safeeval->reval($code);
955: return $safeeval;
956: }
957:
958: #
959: #
960: #
961: sub templaterow {
962: my $sheet = shift;
963: my @cols=();
964: my $rowlabel = 'Template';
965: foreach ('A','B','C','D','E','F','G','H','I','J','K','L','M',
966: 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
967: 'a','b','c','d','e','f','g','h','i','j','k','l','m',
968: 'n','o','p','q','r','s','t','u','v','w','x','y','z') {
969: push(@cols,{ name => 'template_'.$_,
970: formula => $sheet->{'f'}->{'template_'.$_},
971: value => $sheet->{'f'}->{'template_'.$_} });
972: }
973: return ($rowlabel,@cols);
974: }
975:
976: sub outrowassess {
977: # $n is the current row number
978: my ($sheet,$n) = @_;
979: my @cols=();
980: my $rowlabel='';
981: if ($n) {
982: my ($usy,$ufn)=split(/__&&&\__/,$sheet->{'f'}->{'A'.$n});
983: if (exists($sheet->{'rowlabel'}->{$usy})) {
984: # This is dumb, but we need the information when we output
985: # the html version of the studentcalc spreadsheet for the
986: # links to the assesscalc sheets.
987: $rowlabel = $sheet->{'rowlabel'}->{$usy}.':'.
988: &Apache::lonnet::escape($ufn);
989: } else {
990: $rowlabel = '';
991: }
992: } else {
993: $rowlabel = 'Export';
994: }
995: foreach ('A','B','C','D','E','F','G','H','I','J','K','L','M',
996: 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
997: 'a','b','c','d','e','f','g','h','i','j','k','l','m',
998: 'n','o','p','q','r','s','t','u','v','w','x','y','z') {
999: push(@cols,{ name => $_.$n,
1000: formula => $sheet->{'f'}->{$_.$n},
1001: value => $sheet->{'values'}->{$_.$n}});
1002: }
1003: return ($rowlabel,@cols);
1004: }
1005:
1006: sub outrow {
1007: my ($sheet,$n)=@_;
1008: my @cols=();
1009: my $rowlabel;
1010: if ($n) {
1011: $rowlabel = $sheet->{'rowlabel'}->{$sheet->{'f'}->{'A'.$n}};
1012: } else {
1013: if ($sheet->{'sheettype'} eq 'classcalc') {
1014: $rowlabel = 'Summary';
1015: } else {
1016: $rowlabel = 'Export';
1017: }
1018: }
1019: foreach ('A','B','C','D','E','F','G','H','I','J','K','L','M',
1020: 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
1021: 'a','b','c','d','e','f','g','h','i','j','k','l','m',
1022: 'n','o','p','q','r','s','t','u','v','w','x','y','z') {
1023: push(@cols,{ name => $_.$n,
1024: formula => $sheet->{'f'}->{$_.$n},
1025: value => $sheet->{'values'}->{$_.$n}});
1026: }
1027: return ($rowlabel,@cols);
1028: }
1029:
1030: # ------------------------------------------------ Add or change formula values
1031: sub setformulas {
1032: my ($sheet)=shift;
1033: %{$sheet->{'safe'}->varglob('f')}=%{$sheet->{'f'}};
1034: }
1035:
1036: # ------------------------------------------------ Add or change formula values
1037: sub setconstants {
1038: my ($sheet)=shift;
1039: my ($constants) = @_;
1040: if (! ref($constants)) {
1041: my %tmp = @_;
1042: $constants = \%tmp;
1043: }
1044: $sheet->{'constants'} = $constants;
1045: return %{$sheet->{'safe'}->varglob('c')}=%{$sheet->{'constants'}};
1046: }
1047:
1048: # --------------------------------------------- Set names of other spreadsheets
1049: sub setothersheets {
1050: my $sheet = shift;
1051: my @othersheets = @_;
1052: $sheet->{'othersheets'} = \@othersheets;
1053: @{$sheet->{'safe'}->varglob('os')}=@othersheets;
1054: return;
1055: }
1056:
1057: # ------------------------------------------------ Add or change formula values
1058: sub setrowlabels {
1059: my $sheet=shift;
1060: my ($rowlabel) = @_;
1061: if (! ref($rowlabel)) {
1062: my %tmp = @_;
1063: $rowlabel = \%tmp;
1064: }
1065: $sheet->{'rowlabel'}=$rowlabel;
1066: }
1067:
1068: # ------------------------------------------------------- Calculate spreadsheet
1069: sub calcsheet {
1070: my $sheet=shift;
1071: my $result = $sheet->{'safe'}->reval('&calc();');
1072: %{$sheet->{'values'}} = %{$sheet->{'safe'}->varglob('sheet_values')};
1073: return $result;
1074: }
1075:
1076: # ---------------------------------------------------------------- Get formulas
1077: # Return a copy of the formulas
1078: sub getformulas {
1079: my $sheet = shift;
1080: return %{$sheet->{'safe'}->varglob('f')};
1081: }
1082:
1083: sub geterrorlog {
1084: my $sheet = shift;
1085: return ${$sheet->{'safe'}->varglob('errorlog')};
1086: }
1087:
1088: sub gettitle {
1089: my $sheet = shift;
1090: if ($sheet->{'sheettype'} eq 'classcalc') {
1091: return $sheet->{'coursedesc'};
1092: } elsif ($sheet->{'sheettype'} eq 'studentcalc') {
1093: return 'Grades for '.$sheet->{'uname'}.'@'.$sheet->{'udom'};
1094: } elsif ($sheet->{'sheettype'} eq 'assesscalc') {
1095: if (($sheet->{'usymb'} eq '_feedback') ||
1096: ($sheet->{'usymb'} eq '_evaluation') ||
1097: ($sheet->{'usymb'} eq '_discussion') ||
1098: ($sheet->{'usymb'} eq '_tutoring')) {
1099: my $title = $sheet->{'usymb'};
1100: $title =~ s/^_//;
1101: $title = ucfirst($title);
1102: return $title;
1103: }
1104: return if (! defined($sheet->{'mapid'}) ||
1105: $sheet->{'mapid'} !~ /^\d+$/);
1106: my $mapid = $sheet->{'mapid'};
1107: return if (! defined($sheet->{'resid'}) ||
1108: $sheet->{'resid'} !~ /^\d+$/);
1109: my $resid = $sheet->{'resid'};
1110: my %course_db;
1111: tie(%course_db,'GDBM_File',$sheet->{'coursefilename'}.'.db',
1112: &GDBM_READER(),0640);
1113: return if (! tied(%course_db));
1114: my $key = 'title_'.$mapid.'.'.$resid;
1115: my $title = '';
1116: if (exists($course_db{$key})) {
1117: $title = $course_db{$key};
1118: } else {
1119: $title = $sheet->{'usymb'};
1120: }
1121: untie (%course_db);
1122: return $title;
1123: }
1124: }
1125:
1126: # ----------------------------------------------------- Get value of $f{'A'.$n}
1127: sub getfa {
1128: my $sheet = shift;
1129: my ($n)=@_;
1130: return $sheet->{'safe'}->reval('$f{"A'.$n.'"}');
1131: }
1132:
1133: # ------------------------------------------------------------- Export of A-row
1134: sub exportdata {
1135: my $sheet=shift;
1136: my @exportarray=();
1137: foreach ('A','B','C','D','E','F','G','H','I','J','K','L','M',
1138: 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z') {
1139: if (exists($sheet->{'values'}->{$_.'0'})) {
1140: push(@exportarray,$sheet->{'values'}->{$_.'0'});
1141: } else {
1142: push(@exportarray,'');
1143: }
1144: }
1145: return @exportarray;
1146: }
1147:
1148:
1149:
1150: sub update_student_sheet{
1151: my ($sheet,$r,$c) = @_;
1152: # Load in the studentcalc sheet
1153: &readsheet($sheet,'default_studentcalc');
1154: # Determine the structure (contained assessments, etc) of the sheet
1155: &updatesheet($sheet);
1156: # Load in the cached sheets for this student
1157: &cachedssheets($sheet);
1158: # Load in the (possibly cached) data from the assessment sheets
1159: &loadstudent($sheet,$r,$c);
1160: # Compute the sheet
1161: &calcsheet($sheet);
1162: }
1163:
1164: # ========================================================== End of Spreadsheet
1165: # =============================================================================
1166: #
1167: # Procedures for spreadsheet output
1168: #
1169: # --------------------------------------------- Produce output row n from sheet
1170:
1171: sub get_row {
1172: my ($sheet,$n) = @_;
1173: my ($rowlabel,@rowdata);
1174: if ($n eq '-') {
1175: ($rowlabel,@rowdata) = &templaterow($sheet);
1176: } elsif ($sheet->{'sheettype'} eq 'studentcalc') {
1177: ($rowlabel,@rowdata) = &outrowassess($sheet,$n);
1178: } else {
1179: ($rowlabel,@rowdata) = &outrow($sheet,$n);
1180: }
1181: return ($rowlabel,@rowdata);
1182: }
1183:
1184: ########################################################################
1185: ########################################################################
1186: sub sort_indicies {
1187: my $sheet = shift;
1188: my @sortidx=();
1189: #
1190: if ($sheet->{'sheettype'} eq 'classcalc') {
1191: my @sortby=(undef);
1192: # Skip row 0
1193: for (my $row=1;$row<=$sheet->{'maxrow'};$row++) {
1194: my (undef,$sname,$sdom,$fullname,$section,$id) =
1195: split(':',$sheet->{'rowlabel'}->{$sheet->{'f'}->{'A'.$row}});
1196: push (@sortby, lc($fullname));
1197: push (@sortidx, $row);
1198: }
1199: @sortidx = sort { $sortby[$a] cmp $sortby[$b]; } @sortidx;
1200: } elsif ($sheet->{'sheettype'} eq 'studentcalc') {
1201: my @sortby1=(undef);
1202: my @sortby2=(undef);
1203: # Skip row 0
1204: for (my $row=1;$row<=$sheet->{'maxrow'};$row++) {
1205: my ($key,undef) = split(/__&&&\__/,$sheet->{'f'}->{'A'.$row});
1206: my $rowlabel = $sheet->{'rowlabel'}->{$key};
1207: my (undef,$symb,$mapid,$resid,$title,$ufn) =
1208: split(':',$rowlabel);
1209: $ufn = &Apache::lonnet::unescape($ufn);
1210: $symb = &Apache::lonnet::unescape($symb);
1211: $title = &Apache::lonnet::unescape($title);
1212: my ($sequence) = ($symb =~ /\/([^\/]*\.sequence)/);
1213: if ($sequence eq '') {
1214: $sequence = $symb;
1215: }
1216: push (@sortby1, $sequence);
1217: push (@sortby2, $title);
1218: push (@sortidx, $row);
1219: }
1220: @sortidx = sort { $sortby1[$a] cmp $sortby1[$b] ||
1221: $sortby2[$a] cmp $sortby2[$b] } @sortidx;
1222: } else {
1223: my @sortby=(undef);
1224: # Skip row 0
1225: for (my $row=1;$row<=$sheet->{'maxrow'};$row++) {
1226: push (@sortby, $sheet->{'safe'}->reval('$f{"A'.$row.'"}'));
1227: push (@sortidx, $row);
1228: }
1229: @sortidx = sort { $sortby[$a] cmp $sortby[$b]; } @sortidx;
1230: }
1231: return @sortidx;
1232: }
1233:
1234: #############################################################
1235: ### ###
1236: ### Spreadsheet Output Routines ###
1237: ### ###
1238: #############################################################
1239:
1240: ############################################
1241: ## HTML output routines ##
1242: ############################################
1243: sub html_editable_cell {
1244: my ($cell,$bgcolor) = @_;
1245: my $result;
1246: my ($name,$formula,$value);
1247: if (defined($cell)) {
1248: $name = $cell->{'name'};
1249: $formula = $cell->{'formula'};
1250: $value = $cell->{'value'};
1251: }
1252: $name = '' if (! defined($name));
1253: $formula = '' if (! defined($formula));
1254: if (! defined($value)) {
1255: $value = '<font color="'.$bgcolor.'">#</font>';
1256: if ($formula ne '') {
1257: $value = '<i>undefined value</i>';
1258: }
1259: } elsif ($value =~ /^\s*$/ ) {
1260: $value = '<font color="'.$bgcolor.'">#</font>';
1261: } else {
1262: $value = &HTML::Entities::encode($value);
1263: }
1264: # Make the formula safe for outputting
1265: $formula =~ s/\'/\"/g;
1266: # The formula will be parsed by the browser *twice* before being
1267: # displayed to the user for editing.
1268: $formula = &HTML::Entities::encode(&HTML::Entities::encode($formula));
1269: # Escape newlines so they make it into the edit window
1270: $formula =~ s/\n/\\n/gs;
1271: # Glue everything together
1272: $result .= "<a href=\"javascript:celledit(\'".
1273: $name."','".$formula."');\">".$value."</a>";
1274: return $result;
1275: }
1276:
1277: sub html_uneditable_cell {
1278: my ($cell,$bgcolor) = @_;
1279: my $value = (defined($cell) ? $cell->{'value'} : '');
1280: $value = &HTML::Entities::encode($value);
1281: return ' '.$value.' ';
1282: }
1283:
1284: sub outsheet_html {
1285: my ($sheet,$r) = @_;
1286: my ($num_uneditable,$realm,$row_type);
1287: my $requester_is_student = ($ENV{'request.role'} =~ /^st\./);
1288: if ($sheet->{'sheettype'} eq 'assesscalc') {
1289: $num_uneditable = 1;
1290: $realm = 'Assessment';
1291: $row_type = 'Item';
1292: } elsif ($sheet->{'sheettype'} eq 'studentcalc') {
1293: $num_uneditable = 26;
1294: $realm = 'User';
1295: $row_type = 'Assessment';
1296: } elsif ($sheet->{'sheettype'} eq 'classcalc') {
1297: $num_uneditable = 26;
1298: $realm = 'Course';
1299: $row_type = 'Student';
1300: } else {
1301: return; # error
1302: }
1303: ####################################
1304: # Print out header table
1305: ####################################
1306: my $num_left = 52-$num_uneditable;
1307: my $tabledata =<<"END";
1308: <table border="2">
1309: <tr>
1310: <th colspan="1" rowspan="2"><font size="+2">$realm</font></th>
1311: <td bgcolor="#FFDDDD" colspan="$num_uneditable">
1312: <b><font size="+1">Import</font></b></td>
1313: <td colspan="$num_left">
1314: <b><font size="+1">Calculations</font></b></td>
1315: </tr><tr>
1316: END
1317: my $label_num = 0;
1318: foreach (split(//,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')){
1319: if ($label_num<$num_uneditable) {
1320: $tabledata.='<td bgcolor="#FFDDDD">';
1321: } else {
1322: $tabledata.='<td>';
1323: }
1324: $tabledata.="<b><font size=+1>$_</font></b></td>";
1325: $label_num++;
1326: }
1327: $tabledata.="</tr>\n";
1328: $r->print($tabledata);
1329: ####################################
1330: # Print out template row
1331: ####################################
1332: my ($num_cols_output,$row_html,$rowlabel,@rowdata);
1333:
1334: if (! $requester_is_student) {
1335: ($rowlabel,@rowdata) = &get_row($sheet,'-');
1336: $row_html = '<tr><td>'.&format_html_rowlabel($sheet,$rowlabel).'</td>';
1337: $num_cols_output = 0;
1338: foreach my $cell (@rowdata) {
1339: if ($requester_is_student ||
1340: $num_cols_output++ < $num_uneditable) {
1341: $row_html .= '<td bgcolor="#FFDDDD">';
1342: $row_html .= &html_uneditable_cell($cell,'#FFDDDD');
1343: } else {
1344: $row_html .= '<td bgcolor="#EOFFDD">';
1345: $row_html .= &html_editable_cell($cell,'#E0FFDD');
1346: }
1347: $row_html .= '</td>';
1348: }
1349: $row_html.= "</tr>\n";
1350: $r->print($row_html);
1351: }
1352: ####################################
1353: # Print out summary/export row
1354: ####################################
1355: ($rowlabel,@rowdata) = &get_row($sheet,'0');
1356: $row_html = '<tr><td>'.&format_html_rowlabel($sheet,'Summary').'</td>';
1357: $num_cols_output = 0;
1358: foreach my $cell (@rowdata) {
1359: if ($num_cols_output++ < 26 && ! $requester_is_student) {
1360: $row_html .= '<td bgcolor="#CCCCFF">';
1361: $row_html .= &html_editable_cell($cell,'#CCCCFF');
1362: } else {
1363: $row_html .= '<td bgcolor="#DDCCFF">';
1364: $row_html .= &html_uneditable_cell($cell,'#CCCCFF');
1365: }
1366: $row_html .= '</td>';
1367: }
1368: $row_html.= "</tr>\n";
1369: $r->print($row_html);
1370: $r->print('</table>');
1371: ####################################
1372: # Prepare to output rows
1373: ####################################
1374: my @Rows = &sort_indicies($sheet);
1375: #
1376: # Loop through the rows and output them one at a time
1377: my $rows_output=0;
1378: foreach my $rownum (@Rows) {
1379: my ($rowlabel,@rowdata) = &get_row($sheet,$rownum);
1380: next if ($rowlabel =~ /^\s*$/);
1381: next if (($sheet->{'sheettype'} eq 'assesscalc') &&
1382: (! $ENV{'form.showall'}) &&
1383: ($rowdata[0]->{'value'} =~ /^\s*$/));
1384: if (! $ENV{'form.showall'} &&
1385: $sheet->{'sheettype'} =~ /^(studentcalc|classcalc)$/) {
1386: my $row_is_empty = 1;
1387: foreach my $cell (@rowdata) {
1388: if ($cell->{'value'} !~ /^\s*$/) {
1389: $row_is_empty = 0;
1390: last;
1391: }
1392: }
1393: next if ($row_is_empty);
1394: }
1395: #
1396: my $defaultbg='#E0FF';
1397: #
1398: my $row_html ="\n".'<tr><td><b><font size=+1>'.$rownum.
1399: '</font></b></td>';
1400: #
1401: if ($sheet->{'sheettype'} eq 'classcalc') {
1402: $row_html.='<td>'.&format_html_rowlabel($sheet,$rowlabel).'</td>';
1403: # Output links for each student?
1404: # Nope, that is already done for us in format_html_rowlabel
1405: # (for now)
1406: } elsif ($sheet->{'sheettype'} eq 'studentcalc') {
1407: my $ufn = (split(/:/,$rowlabel))[5];
1408: $row_html.='<td>'.&format_html_rowlabel($sheet,$rowlabel);
1409: $row_html.= '<br>'.
1410: '<select name="sel_'.$rownum.'" '.
1411: 'onChange="changesheet('.$rownum.')">'.
1412: '<option name="default">Default</option>';
1413:
1414: foreach (@{$sheet->{'othersheets'}}) {
1415: $row_html.='<option name="'.$_.'"';
1416: if ($ufn eq $_) {
1417: $row_html.=' selected';
1418: }
1419: $row_html.='>'.$_.'</option>';
1420: }
1421: $row_html.='</select></td>';
1422: } elsif ($sheet->{'sheettype'} eq 'assesscalc') {
1423: $row_html.='<td>'.&format_html_rowlabel($sheet,$rowlabel).'</td>';
1424: }
1425: #
1426: my $shown_cells = 0;
1427: foreach my $cell (@rowdata) {
1428: my $value = $cell->{'value'};
1429: my $formula = $cell->{'formula'};
1430: my $cellname = $cell->{'name'};
1431: #
1432: my $bgcolor;
1433: if ($shown_cells && ($shown_cells/5 == int($shown_cells/5))) {
1434: $bgcolor = $defaultbg.'99';
1435: } else {
1436: $bgcolor = $defaultbg.'DD';
1437: }
1438: $bgcolor='#FFDDDD' if ($shown_cells < $num_uneditable);
1439: #
1440: $row_html.='<td bgcolor='.$bgcolor.'>';
1441: if ($requester_is_student || $shown_cells < $num_uneditable) {
1442: $row_html .= &html_uneditable_cell($cell,$bgcolor);
1443: } else {
1444: $row_html .= &html_editable_cell($cell,$bgcolor);
1445: }
1446: $row_html.='</td>';
1447: $shown_cells++;
1448: }
1449: if ($row_html) {
1450: if ($rows_output % 25 == 0) {
1451: $r->print("</table>\n<br>\n");
1452: $r->rflush();
1453: $r->print('<table border=2>'.
1454: '<tr><td> <td>'.$row_type.'</td>'.
1455: '<td>'.
1456: join('</td><td>',
1457: (split(//,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.
1458: 'abcdefghijklmnopqrstuvwxyz'))).
1459: "</td></tr>\n");
1460: }
1461: $rows_output++;
1462: $r->print($row_html);
1463: }
1464: }
1465: #
1466: $r->print('</table>');
1467: #
1468: # Debugging code (be sure to uncomment errorlog code in safe space):
1469: #
1470: # $r->print("\n<pre>");
1471: # $r->print(&geterrorlog($sheet));
1472: # $r->print("\n</pre>");
1473: return 1;
1474: }
1475:
1476: ############################################
1477: ## csv output routines ##
1478: ############################################
1479: sub outsheet_csv {
1480: my ($sheet,$r) = @_;
1481: my $csvdata = '';
1482: my @Values;
1483: ####################################
1484: # Prepare to output rows
1485: ####################################
1486: my @Rows = &sort_indicies($sheet);
1487: #
1488: # Loop through the rows and output them one at a time
1489: my $rows_output=0;
1490: foreach my $rownum (@Rows) {
1491: my ($rowlabel,@rowdata) = &get_row($sheet,$rownum);
1492: next if ($rowlabel =~ /^\s*$/);
1493: push (@Values,&format_csv_rowlabel($sheet,$rowlabel));
1494: foreach my $cell (@rowdata) {
1495: push (@Values,'"'.$cell->{'value'}.'"');
1496: }
1497: $csvdata.= join(',',@Values)."\n";
1498: @Values = ();
1499: }
1500: #
1501: # Write the CSV data to a file and serve up a link
1502: #
1503: my $filename = '/prtspool/'.
1504: $ENV{'user.name'}.'_'.$ENV{'user.domain'}.'_'.
1505: time.'_'.rand(1000000000).'.csv';
1506: my $file;
1507: unless ($file = Apache::File->new('>'.'/home/httpd'.$filename)) {
1508: $r->log_error("Couldn't open $filename for output $!");
1509: $r->print("Problems occured in writing the csv file. ".
1510: "This error has been logged. ".
1511: "Please alert your LON-CAPA administrator.");
1512: $r->print("<pre>\n".$csvdata."</pre>\n");
1513: return 0;
1514: }
1515: print $file $csvdata;
1516: close($file);
1517: $r->print('<br /><br />'.
1518: '<a href="'.$filename.'">Your CSV spreadsheet.</a>'."\n");
1519: #
1520: return 1;
1521: }
1522:
1523: ############################################
1524: ## Excel output routines ##
1525: ############################################
1526: sub outsheet_recursive_excel {
1527: my ($sheet,$r) = @_;
1528: my $c = $r->connection;
1529: return undef if ($sheet->{'sheettype'} ne 'classcalc');
1530: my ($workbook,$filename) = &create_excel_spreadsheet($sheet,$r);
1531: return undef if (! defined($workbook));
1532: #
1533: # Create main worksheet
1534: my $main_worksheet = $workbook->addworksheet('main');
1535: #
1536: # Figure out who the students are
1537: my %f=&getformulas($sheet);
1538: my $count = 0;
1539: $r->print(<<END);
1540: <p>
1541: Compiling Excel Workbook with a worksheet for each student.
1542: </p><p>
1543: This operation may take longer than a complete recalculation of the
1544: spreadsheet.
1545: </p><p>
1546: To abort this operation, hit the stop button on your browser.
1547: </p><p>
1548: A link to the spreadsheet will be available at the end of this process.
1549: </p>
1550: <p>
1551: END
1552: $r->rflush();
1553: my $starttime = time;
1554: foreach my $rownum (&sort_indicies($sheet)) {
1555: $count++;
1556: my ($sname,$sdom) = split(':',$f{'A'.$rownum});
1557: my $student_excel_worksheet=$workbook->addworksheet($sname.'@'.$sdom);
1558: # Create a new spreadsheet
1559: my $studentsheet = &makenewsheet($sname,$sdom,'studentcalc',undef);
1560: # Read in the spreadsheet definition
1561: &update_student_sheet($studentsheet,$r,$c);
1562: # Stuff the sheet into excel
1563: &export_sheet_as_excel($studentsheet,$student_excel_worksheet);
1564: my $totaltime = int((time - $starttime) / $count * $sheet->{'maxrow'});
1565: my $timeleft = int((time - $starttime) / $count * ($sheet->{'maxrow'} - $count));
1566: if ($count % 5 == 0) {
1567: $r->print($count.' students completed.'.
1568: ' Time remaining: '.$timeleft.' sec. '.
1569: ' Estimated total time: '.$totaltime." sec <br />\n");
1570: $r->rflush();
1571: }
1572: if(defined($c) && ($c->aborted())) {
1573: last;
1574: }
1575: }
1576: #
1577: if(! $c->aborted() ) {
1578: $r->print('All students spreadsheets completed!<br />');
1579: $r->rflush();
1580: #
1581: # &export_sheet_as_excel fills $worksheet with the data from $sheet
1582: &export_sheet_as_excel($sheet,$main_worksheet);
1583: #
1584: $workbook->close();
1585: # Okay, the spreadsheet is taken care of, so give the user a link.
1586: $r->print('<br /><br />'.
1587: '<a href="'.$filename.'">Your Excel spreadsheet.</a>'."\n");
1588: } else {
1589: $workbook->close(); # Not sure how necessary this is.
1590: #unlink('/home/httpd'.$filename); # No need to keep this around?
1591: }
1592: return 1;
1593: }
1594:
1595: sub outsheet_excel {
1596: my ($sheet,$r) = @_;
1597: my ($workbook,$filename) = &create_excel_spreadsheet($sheet,$r);
1598: return undef if (! defined($workbook));
1599: my $sheetname;
1600: if ($sheet->{'sheettype'} eq 'classcalc') {
1601: $sheetname = 'Main';
1602: } elsif ($sheet->{'sheettype'} eq 'studentcalc') {
1603: $sheetname = $sheet->{'uname'}.'@'.$sheet->{'udom'};
1604: } elsif ($sheet->{'sheettype'} eq 'assesscalc') {
1605: $sheetname = $sheet->{'uname'}.'@'.$sheet->{'udom'}.' assessment';
1606: }
1607: my $worksheet = $workbook->addworksheet($sheetname);
1608: #
1609: # &export_sheet_as_excel fills $worksheet with the data from $sheet
1610: &export_sheet_as_excel($sheet,$worksheet);
1611: #
1612: $workbook->close();
1613: # Okay, the spreadsheet is taken care of, so give the user a link.
1614: $r->print('<br /><br />'.
1615: '<a href="'.$filename.'">Your Excel spreadsheet.</a>'."\n");
1616: return 1;
1617: }
1618:
1619: sub create_excel_spreadsheet {
1620: my ($sheet,$r) = @_;
1621: my $filename = '/prtspool/'.
1622: $ENV{'user.name'}.'_'.$ENV{'user.domain'}.'_'.
1623: time.'_'.rand(1000000000).'.xls';
1624: my $workbook = Spreadsheet::WriteExcel->new('/home/httpd'.$filename);
1625: if (! defined($workbook)) {
1626: $r->log_error("Error creating excel spreadsheet $filename: $!");
1627: $r->print("Problems creating new Excel file. ".
1628: "This error has been logged. ".
1629: "Please alert your LON-CAPA administrator");
1630: return undef;
1631: }
1632: #
1633: # The spreadsheet stores temporary data in files, then put them
1634: # together. If needed we should be able to disable this (memory only).
1635: # The temporary directory must be specified before calling 'addworksheet'.
1636: # File::Temp is used to determine the temporary directory.
1637: $workbook->set_tempdir('/home/httpd/perl/tmp');
1638: #
1639: # Determine the name to give the worksheet
1640: return ($workbook,$filename);
1641: }
1642:
1643: sub export_sheet_as_excel {
1644: my $sheet = shift;
1645: my $worksheet = shift;
1646: #
1647: my $rows_output = 0;
1648: my $cols_output = 0;
1649: ####################################
1650: # Write an identifying row #
1651: ####################################
1652: my @Headerinfo = ($sheet->{'coursedesc'});
1653: my $title = &gettitle($sheet);
1654: $cols_output = 0;
1655: if (defined($title)) {
1656: $worksheet->write($rows_output++,$cols_output++,$title);
1657: }
1658: ####################################
1659: # Write the summary/export row #
1660: ####################################
1661: my ($rowlabel,@rowdata) = &get_row($sheet,'0');
1662: my $label = &format_excel_rowlabel($sheet,$rowlabel);
1663: $cols_output = 0;
1664: $worksheet->write($rows_output,$cols_output++,$label);
1665: foreach my $cell (@rowdata) {
1666: $worksheet->write($rows_output,$cols_output++,$cell->{'value'});
1667: }
1668: $rows_output+= 2; # Skip a row, just for fun
1669: ####################################
1670: # Prepare to output rows
1671: ####################################
1672: my @Rows = &sort_indicies($sheet);
1673: #
1674: # Loop through the rows and output them one at a time
1675: foreach my $rownum (@Rows) {
1676: my ($rowlabel,@rowdata) = &get_row($sheet,$rownum);
1677: next if ($rowlabel =~ /^[\s]*$/);
1678: $cols_output = 0;
1679: my $label = &format_excel_rowlabel($sheet,$rowlabel);
1680: if ( ! $ENV{'form.showall'} &&
1681: $sheet->{'sheettype'} =~ /^(studentcalc|classcalc)$/) {
1682: my $row_is_empty = 1;
1683: foreach my $cell (@rowdata) {
1684: if ($cell->{'value'} !~ /^\s*$/) {
1685: $row_is_empty = 0;
1686: last;
1687: }
1688: }
1689: next if ($row_is_empty);
1690: }
1691: $worksheet->write($rows_output,$cols_output++,$label);
1692: if (ref($label)) {
1693: $cols_output = (scalar(@$label));
1694: }
1695: foreach my $cell (@rowdata) {
1696: $worksheet->write($rows_output,$cols_output++,$cell->{'value'});
1697: }
1698: $rows_output++;
1699: }
1700: return;
1701: }
1702:
1703: ############################################
1704: ## XML output routines ##
1705: ############################################
1706: sub outsheet_xml {
1707: my ($sheet,$r) = @_;
1708: ## Someday XML
1709: ## Will be rendered for the user
1710: ## But not on this day
1711: }
1712:
1713: ##
1714: ## Outsheet - calls other outsheet_* functions
1715: ##
1716: sub outsheet {
1717: my ($sheet,$r)=@_;
1718: if (! exists($ENV{'form.output'})) {
1719: $ENV{'form.output'} = 'HTML';
1720: }
1721: if (lc($ENV{'form.output'}) eq 'csv') {
1722: &outsheet_csv($sheet,$r);
1723: } elsif (lc($ENV{'form.output'}) eq 'excel') {
1724: &outsheet_excel($sheet,$r);
1725: } elsif (lc($ENV{'form.output'}) eq 'recursive excel') {
1726: &outsheet_recursive_excel($sheet,$r);
1727: # } elsif (lc($ENV{'form.output'}) eq 'xml' ) {
1728: # &outsheet_xml($sheet,$r);
1729: } else {
1730: &outsheet_html($sheet,$r);
1731: }
1732: }
1733:
1734: ########################################################################
1735: ########################################################################
1736: sub othersheets {
1737: my ($sheet,$stype)=@_;
1738: $stype = $sheet->{'sheettype'} if (! defined($stype));
1739: #
1740: my $cnum = $sheet->{'cnum'};
1741: my $cdom = $sheet->{'cdom'};
1742: my $chome = $sheet->{'chome'};
1743: #
1744: my @alternatives=();
1745: my %results=&Apache::lonnet::dump($stype.'_spreadsheets',$cdom,$cnum);
1746: my ($tmp) = keys(%results);
1747: unless ($tmp =~ /^(con_lost|error|no_such_host)/i) {
1748: @alternatives = sort (keys(%results));
1749: }
1750: return @alternatives;
1751: }
1752:
1753: #
1754: # -------------------------------------- Parse a spreadsheet
1755: #
1756: sub parse_sheet {
1757: # $sheetxml is a scalar reference or a scalar
1758: my ($sheetxml) = @_;
1759: if (! ref($sheetxml)) {
1760: my $tmp = $sheetxml;
1761: $sheetxml = \$tmp;
1762: }
1763: my %f;
1764: my $parser=HTML::TokeParser->new($sheetxml);
1765: my $token;
1766: while ($token=$parser->get_token) {
1767: if ($token->[0] eq 'S') {
1768: if ($token->[1] eq 'field') {
1769: $f{$token->[2]->{'col'}.$token->[2]->{'row'}}=
1770: $parser->get_text('/field');
1771: }
1772: if ($token->[1] eq 'template') {
1773: $f{'template_'.$token->[2]->{'col'}}=
1774: $parser->get_text('/template');
1775: }
1776: }
1777: }
1778: return \%f;
1779: }
1780:
1781: #
1782: # -------------------------------------- Read spreadsheet formulas for a course
1783: #
1784: sub readsheet {
1785: my ($sheet,$fn)=@_;
1786: #
1787: my $stype = $sheet->{'sheettype'};
1788: my $cnum = $sheet->{'cnum'};
1789: my $cdom = $sheet->{'cdom'};
1790: my $chome = $sheet->{'chome'};
1791: #
1792: if (! defined($fn)) {
1793: # There is no filename. Look for defaults in course and global, cache
1794: unless ($fn=$defaultsheets{$cnum.'_'.$cdom.'_'.$stype}) {
1795: my %tmphash = &Apache::lonnet::get('environment',
1796: ['spreadsheet_default_'.$stype],
1797: $cdom,$cnum);
1798: my ($tmp) = keys(%tmphash);
1799: if ($tmp =~ /^(con_lost|error|no_such_host)/i) {
1800: $fn = 'default_'.$stype;
1801: } else {
1802: $fn = $tmphash{'spreadsheet_default_'.$stype};
1803: }
1804: unless (($fn) && ($fn!~/^error\:/)) {
1805: $fn='default_'.$stype;
1806: }
1807: $defaultsheets{$cnum.'_'.$cdom.'_'.$stype}=$fn;
1808: }
1809: }
1810: # $fn now has a value
1811: $sheet->{'filename'} = $fn;
1812: # see if sheet is cached
1813: my $fstring='';
1814: if ($fstring=$spreadsheets{$cnum.'_'.$cdom.'_'.$stype.'_'.$fn}) {
1815: my %tmp = split(/___;___/,$fstring);
1816: $sheet->{'f'} = \%tmp;
1817: &setformulas($sheet);
1818: } else {
1819: # Not cached, need to read
1820: my %f=();
1821: if ($fn=~/^default\_/) {
1822: my $sheetxml='';
1823: my $fh;
1824: my $dfn=$fn;
1825: $dfn=~s/\_/\./g;
1826: if ($fh=Apache::File->new($includedir.'/'.$dfn)) {
1827: $sheetxml=join('',<$fh>);
1828: } else {
1829: # $sheetxml='<field row="0" col="A">"Error"</field>';
1830: $sheetxml='<field row="0" col="A"></field>';
1831: }
1832: %f=%{&parse_sheet(\$sheetxml)};
1833: } elsif($fn=~/\/*\.spreadsheet$/) {
1834: my $sheetxml=&Apache::lonnet::getfile
1835: (&Apache::lonnet::filelocation('',$fn));
1836: if ($sheetxml == -1) {
1837: $sheetxml='<field row="0" col="A">"Error loading spreadsheet '
1838: .$fn.'"</field>';
1839: }
1840: %f=%{&parse_sheet(\$sheetxml)};
1841: } else {
1842: my %tmphash = &Apache::lonnet::dump($fn,$cdom,$cnum);
1843: my ($tmp) = keys(%tmphash);
1844: if ($tmp !~ /^(con_lost|error|no_such_host)/i) {
1845: foreach (keys(%tmphash)) {
1846: $f{$_}=$tmphash{$_};
1847: }
1848: } else {
1849: # Unable to grab the specified spreadsheet,
1850: # so we get the default ones instead.
1851: $fn = 'default_'.$stype;
1852: $sheet->{'filename'} = $fn;
1853: my $dfn = $fn;
1854: $dfn =~ s/\_/\./g;
1855: my $sheetxml;
1856: if (my $fh=Apache::File->new($includedir.'/'.$dfn)) {
1857: $sheetxml = join('',<$fh>);
1858: } else {
1859: $sheetxml='<field row="0" col="A">'.
1860: '"Unable to load spreadsheet"</field>';
1861: }
1862: %f=%{&parse_sheet(\$sheetxml)};
1863: }
1864: }
1865: # Cache and set
1866: $spreadsheets{$cnum.'_'.$cdom.'_'.$stype.'_'.$fn}=join('___;___',%f);
1867: $sheet->{'f'}=\%f;
1868: &setformulas($sheet);
1869: }
1870: }
1871:
1872: # -------------------------------------------------------- Make new spreadsheet
1873: sub makenewsheet {
1874: my ($uname,$udom,$stype,$usymb)=@_;
1875: my $sheet={};
1876: $sheet->{'uname'} = $uname;
1877: $sheet->{'udom'} = $udom;
1878: $sheet->{'sheettype'} = $stype;
1879: $sheet->{'usymb'} = $usymb;
1880: $sheet->{'mapid'} = $ENV{'form.mapid'};
1881: $sheet->{'resid'} = $ENV{'form.resid'};
1882: $sheet->{'cid'} = $ENV{'request.course.id'};
1883: $sheet->{'csec'} = $Section{$uname.':'.$udom};
1884: $sheet->{'coursefilename'} = $ENV{'request.course.fn'};
1885: $sheet->{'cnum'} = $ENV{'course.'.$ENV{'request.course.id'}.'.num'};
1886: $sheet->{'cdom'} = $ENV{'course.'.$ENV{'request.course.id'}.'.domain'};
1887: $sheet->{'chome'} = $ENV{'course.'.$ENV{'request.course.id'}.'.home'};
1888: $sheet->{'coursedesc'} = $ENV{'course.'.$ENV{'request.course.id'}.
1889: '.description'};
1890: $sheet->{'uhome'} = &Apache::lonnet::homeserver($uname,$udom);
1891: #
1892: #
1893: $sheet->{'f'} = {};
1894: $sheet->{'constants'} = {};
1895: $sheet->{'othersheets'} = [];
1896: $sheet->{'rowlabel'} = {};
1897: #
1898: #
1899: $sheet->{'safe'}=&initsheet($sheet->{'sheettype'});
1900: #
1901: # Place all the %$sheet items into the safe space except the safe space
1902: # itself
1903: my $initstring = '';
1904: foreach (qw/uname udom sheettype usymb cid csec coursefilename
1905: cnum cdom chome uhome/) {
1906: $initstring.= qq{\$$_="$sheet->{$_}";};
1907: }
1908: $sheet->{'safe'}->reval($initstring);
1909: return $sheet;
1910: }
1911:
1912: # ------------------------------------------------------------ Save spreadsheet
1913: sub writesheet {
1914: my ($sheet,$makedef)=@_;
1915: my $cid=$sheet->{'cid'};
1916: if (&Apache::lonnet::allowed('opa',$cid)) {
1917: my %f=&getformulas($sheet);
1918: my $stype= $sheet->{'sheettype'};
1919: my $cnum = $sheet->{'cnum'};
1920: my $cdom = $sheet->{'cdom'};
1921: my $chome= $sheet->{'chome'};
1922: my $fn = $sheet->{'filename'};
1923: # Cache new sheet
1924: $spreadsheets{$cnum.'_'.$cdom.'_'.$stype.'_'.$fn}=join('___;___',%f);
1925: # Write sheet
1926: foreach (keys(%f)) {
1927: delete($f{$_}) if ($f{$_} eq 'import');
1928: }
1929: my $reply = &Apache::lonnet::put($fn,\%f,$cdom,$cnum);
1930: if ($reply eq 'ok') {
1931: $reply = &Apache::lonnet::put($stype.'_spreadsheets',
1932: {$fn => $ENV{'user.name'}.'@'.$ENV{'user.domain'}},
1933: $cdom,$cnum);
1934: if ($reply eq 'ok') {
1935: if ($makedef) {
1936: $reply = &Apache::lonnet::put('environment',
1937: {'spreadsheet_default_'.$stype => $fn },
1938: $cdom,$cnum);
1939: if ($reply eq 'ok' &&
1940: ($sheet->{'sheettype'} eq 'studentcalc' ||
1941: $sheet->{'sheettype'} eq 'assesscalc')) {
1942: # Expire the spreadsheets of the other students.
1943: &Apache::lonnet::expirespread('','','studentcalc','');
1944: }
1945: return $reply;
1946: }
1947: return $reply;
1948: }
1949: return $reply;
1950: }
1951: return $reply;
1952: }
1953: return 'unauthorized';
1954: }
1955:
1956: # ----------------------------------------------- Make a temp copy of the sheet
1957: # "Modified workcopy" - interactive only
1958: #
1959: sub tmpwrite {
1960: my ($sheet) = @_;
1961: my $fn=$ENV{'user.name'}.'_'.
1962: $ENV{'user.domain'}.'_spreadsheet_'.$sheet->{'usymb'}.'_'.
1963: $sheet->{'filename'};
1964: $fn=~s/\W/\_/g;
1965: $fn=$tmpdir.$fn.'.tmp';
1966: my $fh;
1967: if ($fh=Apache::File->new('>'.$fn)) {
1968: my %f = &getformulas($sheet);
1969: while( my ($cell,$formula) = each(%f)) {
1970: print $fh &Apache::lonnet::escape($cell)."=".&Apache::lonnet::escape($formula)."\n";
1971: }
1972: }
1973: }
1974:
1975: # ---------------------------------------------------------- Read the temp copy
1976: sub tmpread {
1977: my ($sheet,$nfield,$nform)=@_;
1978: my $fn=$ENV{'user.name'}.'_'.
1979: $ENV{'user.domain'}.'_spreadsheet_'.$sheet->{'usymb'}.'_'.
1980: $sheet->{'filename'};
1981: $fn=~s/\W/\_/g;
1982: $fn=$tmpdir.$fn.'.tmp';
1983: my $fh;
1984: my %fo=();
1985: my $countrows=0;
1986: if ($fh=Apache::File->new($fn)) {
1987: while (<$fh>) {
1988: chomp;
1989: my ($cell,$formula) = split(/=/);
1990: $cell = &Apache::lonnet::unescape($cell);
1991: $formula = &Apache::lonnet::unescape($formula);
1992: $fo{$cell} = $formula;
1993: }
1994: }
1995: # chomp($value);
1996: # $fo{$name}=$value;
1997: # if ($name=~/^A(\d+)$/) {
1998: # if ($1>$countrows) {
1999: # $countrows=$1;
2000: # }
2001: # }
2002: # }
2003: # }
2004: if ($nform eq 'changesheet') {
2005: $fo{'A'.$nfield}=(split(/__&&&\__/,$fo{'A'.$nfield}))[0];
2006: unless ($ENV{'form.sel_'.$nfield} eq 'Default') {
2007: $fo{'A'.$nfield}.='__&&&__'.$ENV{'form.sel_'.$nfield};
2008: }
2009: # } elsif ($nfield eq 'insertrow') {
2010: # $countrows++;
2011: # my $newrow=substr('000000'.$countrows,-7);
2012: # if ($nform eq 'top') {
2013: # $fo{'A'.$countrows}='--- '.$newrow;
2014: # } else {
2015: # $fo{'A'.$countrows}='~~~ '.$newrow;
2016: # }
2017: } else {
2018: if ($nfield) { $fo{$nfield}=$nform; }
2019: }
2020: $sheet->{'f'}=\%fo;
2021: &setformulas($sheet);
2022: }
2023:
2024: ##################################################
2025: ##################################################
2026:
2027: =pod
2028:
2029: =item &parmval()
2030:
2031: Determine the value of a parameter.
2032:
2033: Inputs: $what, the parameter needed, $sheet, the safe space
2034:
2035: Returns: The value of a parameter, or '' if none.
2036:
2037: This function cascades through the possible levels searching for a value for
2038: a parameter. The levels are checked in the following order:
2039: user, course (at section level and course level), map, and lonnet::metadata.
2040: This function uses %parmhash, which must be tied prior to calling it.
2041: This function also requires %courseopt and %useropt to be initialized for
2042: this user and course.
2043:
2044: =cut
2045:
2046: ##################################################
2047: ##################################################
2048: sub parmval {
2049: my ($what,$sheet)=@_;
2050: my $symb = $sheet->{'usymb'};
2051: unless ($symb) { return ''; }
2052: #
2053: my $cid = $sheet->{'cid'};
2054: my $csec = $sheet->{'csec'};
2055: my $uname = $sheet->{'uname'};
2056: my $udom = $sheet->{'udom'};
2057: my $result='';
2058: #
2059: my ($mapname,$id,$fn)=split(/\_\_\_/,$symb);
2060: # Cascading lookup scheme
2061: my $rwhat=$what;
2062: $what =~ s/^parameter\_//;
2063: $what =~ s/\_([^\_]+)$/\.$1/;
2064: #
2065: my $symbparm = $symb.'.'.$what;
2066: my $mapparm = $mapname.'___(all).'.$what;
2067: my $usercourseprefix = $uname.'_'.$udom.'_'.$cid;
2068: #
2069: my $seclevel = $usercourseprefix.'.['.$csec.'].'.$what;
2070: my $seclevelr = $usercourseprefix.'.['.$csec.'].'.$symbparm;
2071: my $seclevelm = $usercourseprefix.'.['.$csec.'].'.$mapparm;
2072: #
2073: my $courselevel = $usercourseprefix.'.'.$what;
2074: my $courselevelr = $usercourseprefix.'.'.$symbparm;
2075: my $courselevelm = $usercourseprefix.'.'.$mapparm;
2076: # fourth, check user
2077: if (defined($uname)) {
2078: return $useropt{$courselevelr} if (defined($useropt{$courselevelr}));
2079: return $useropt{$courselevelm} if (defined($useropt{$courselevelm}));
2080: return $useropt{$courselevel} if (defined($useropt{$courselevel}));
2081: }
2082: # third, check course
2083: if (defined($csec)) {
2084: return $courseopt{$seclevelr} if (defined($courseopt{$seclevelr}));
2085: return $courseopt{$seclevelm} if (defined($courseopt{$seclevelm}));
2086: return $courseopt{$seclevel} if (defined($courseopt{$seclevel}));
2087: }
2088: #
2089: return $courseopt{$courselevelr} if (defined($courseopt{$courselevelr}));
2090: return $courseopt{$courselevelm} if (defined($courseopt{$courselevelm}));
2091: return $courseopt{$courselevel} if (defined($courseopt{$courselevel}));
2092: # second, check map parms
2093: my $thisparm = $parmhash{$symbparm};
2094: return $thisparm if (defined($thisparm));
2095: # first, check default
2096: return &Apache::lonnet::metadata($fn,$rwhat.'.default');
2097: }
2098:
2099:
2100: ##################################################################
2101: ## Row label formatting routines ##
2102: ##################################################################
2103: sub format_html_rowlabel {
2104: my $sheet = shift;
2105: my $rowlabel = shift;
2106: return '' if ($rowlabel eq '');
2107: my ($type,$labeldata) = split(':',$rowlabel,2);
2108: my $result = '';
2109: if ($type eq 'symb') {
2110: my ($symb,$mapid,$resid,$title,$ufn) = split(':',$labeldata);
2111: $ufn = 'default' if (!defined($ufn) || $ufn eq '');
2112: $ufn = &Apache::lonnet::unescape($ufn);
2113: $symb = &Apache::lonnet::unescape($symb);
2114: $title = &Apache::lonnet::unescape($title);
2115: $result = '<a href="/adm/assesscalc?usymb='.$symb.
2116: '&uname='.$sheet->{'uname'}.'&udom='.$sheet->{'udom'}.
2117: '&ufn='.$ufn.
2118: '&mapid='.$mapid.'&resid='.$resid.'">'.$title.'</a>';
2119: } elsif ($type eq 'student') {
2120: my ($sname,$sdom,$fullname,$section,$id) = split(':',$labeldata);
2121: if ($fullname =~ /^\s*$/) {
2122: $fullname = $sname.'@'.$sdom;
2123: }
2124: $result ='<a href="/adm/studentcalc?uname='.$sname.
2125: '&udom='.$sdom.'">';
2126: $result.=$section.' '.$id." ".$fullname.'</a>';
2127: } elsif ($type eq 'parameter') {
2128: $result = $labeldata;
2129: } else {
2130: $result = '<b><font size=+1>'.$rowlabel.'</font></b>';
2131: }
2132: return $result;
2133: }
2134:
2135: sub format_csv_rowlabel {
2136: my $sheet = shift;
2137: my $rowlabel = shift;
2138: return '' if ($rowlabel eq '');
2139: my ($type,$labeldata) = split(':',$rowlabel,2);
2140: my $result = '';
2141: if ($type eq 'symb') {
2142: my ($symb,$mapid,$resid,$title,$ufn) = split(':',$labeldata);
2143: $ufn = &Apache::lonnet::unescape($ufn);
2144: $symb = &Apache::lonnet::unescape($symb);
2145: $title = &Apache::lonnet::unescape($title);
2146: $result = $title;
2147: } elsif ($type eq 'student') {
2148: my ($sname,$sdom,$fullname,$section,$id) = split(':',$labeldata);
2149: $result = join('","',($sname,$sdom,$fullname,$section,$id));
2150: } elsif ($type eq 'parameter') {
2151: $labeldata =~ s/<br>/ /g;
2152: $result = $labeldata;
2153: } else {
2154: $result = $rowlabel;
2155: }
2156: return '"'.$result.'"';
2157: }
2158:
2159: sub format_excel_rowlabel {
2160: my $sheet = shift;
2161: my $rowlabel = shift;
2162: return '' if ($rowlabel eq '');
2163: my ($type,$labeldata) = split(':',$rowlabel,2);
2164: my $result = '';
2165: if ($type eq 'symb') {
2166: my ($symb,$mapid,$resid,$title,$ufn) = split(':',$labeldata);
2167: $ufn = &Apache::lonnet::unescape($ufn);
2168: $symb = &Apache::lonnet::unescape($symb);
2169: $title = &Apache::lonnet::unescape($title);
2170: $result = $title;
2171: } elsif ($type eq 'student') {
2172: my ($sname,$sdom,$fullname,$section,$id) = split(':',$labeldata);
2173: $section = '' if (! defined($section));
2174: $id = '' if (! defined($id));
2175: my @Data = ($sname,$sdom,$fullname,$section,$id);
2176: $result = \@Data;
2177: } elsif ($type eq 'parameter') {
2178: $labeldata =~ s/<br>/ /g;
2179: $result = $labeldata;
2180: } else {
2181: $result = $rowlabel;
2182: }
2183: return $result;
2184: }
2185:
2186: # ---------------------------------------------- Update rows for course listing
2187: sub updateclasssheet {
2188: my ($sheet) = @_;
2189: my $cnum =$sheet->{'cnum'};
2190: my $cdom =$sheet->{'cdom'};
2191: my $cid =$sheet->{'cid'};
2192: my $chome =$sheet->{'chome'};
2193: #
2194: %Section = ();
2195: #
2196: # Read class list and row labels
2197: my $classlist = &Apache::loncoursedata::get_classlist();
2198: if (! defined($classlist)) {
2199: return 'Could not access course classlist';
2200: }
2201: #
2202: my %currentlist=();
2203: foreach my $student (keys(%$classlist)) {
2204: my ($studentDomain,$studentName,$end,$start,$id,$studentSection,
2205: $fullname,$status) = @{$classlist->{$student}};
2206: $Section{$studentName.':'.$studentDomain} = $studentSection;
2207: if ($ENV{'form.Status'} eq $status || $ENV{'form.Status'} eq 'Any') {
2208: $currentlist{$student}=join(':',('student',$studentName,
2209: $studentDomain,$fullname,
2210: $studentSection,$id));
2211: }
2212: }
2213: #
2214: # Find discrepancies between the course row table and this
2215: #
2216: my %f=&getformulas($sheet);
2217: my $changed=0;
2218: #
2219: $sheet->{'maxrow'}=0;
2220: my %existing=();
2221: #
2222: # Now obsolete rows
2223: foreach (keys(%f)) {
2224: if ($_=~/^A(\d+)/) {
2225: if ($1 > $sheet->{'maxrow'}) {
2226: $sheet->{'maxrow'}= $1;
2227: }
2228: $existing{$f{$_}}=1;
2229: unless ((defined($currentlist{$f{$_}})) || (!$1) ||
2230: ($f{$_}=~/^(~~~|---)/)) {
2231: $f{$_}='!!! Obsolete';
2232: $changed=1;
2233: }
2234: }
2235: }
2236: #
2237: # New and unknown keys
2238: foreach my $student (sort keys(%currentlist)) {
2239: unless ($existing{$student}) {
2240: $changed=1;
2241: $sheet->{'maxrow'}++;
2242: $f{'A'.$sheet->{'maxrow'}}=$student;
2243: }
2244: }
2245: if ($changed) {
2246: $sheet->{'f'} = \%f;
2247: &setformulas($sheet,%f);
2248: }
2249: #
2250: &setrowlabels($sheet,\%currentlist);
2251: }
2252:
2253: # ----------------------------------- Update rows for student and assess sheets
2254: sub get_student_rowlabels {
2255: my ($sheet) = @_;
2256: #
2257: my %course_db;
2258: #
2259: my $stype = $sheet->{'sheettype'};
2260: my $uname = $sheet->{'uname'};
2261: my $udom = $sheet->{'udom'};
2262: #
2263: $sheet->{'rowlabel'} = {};
2264: #
2265: my $identifier =$sheet->{'coursefilename'}.'_'.$stype;
2266: if ($rowlabel_cache{$identifier}) {
2267: %{$sheet->{'rowlabel'}}=split(/___;___/,$rowlabel_cache{$identifier});
2268: } else {
2269: # Get the data and store it in the cache
2270: # Tie hash
2271: tie(%course_db,'GDBM_File',$sheet->{'coursefilename'}.'.db',
2272: &GDBM_READER(),0640);
2273: if (! tied(%course_db)) {
2274: return 'Could not access course data';
2275: }
2276: #
2277: my %assesslist = ();
2278: foreach ('Feedback','Evaluation','Tutoring','Discussion') {
2279: my $symb = '_'.lc($_);
2280: $assesslist{$symb} = join(':',('symb',$symb,0,0,
2281: &Apache::lonnet::escape($_)));
2282: }
2283: #
2284: while (my ($key,$srcf) = each(%course_db)) {
2285: next if ($key !~ /^src_(\d+)\.(\d+)$/);
2286: my $mapid = $1;
2287: my $resid = $2;
2288: my $id = $mapid.'.'.$resid;
2289: if ($srcf=~/\.(problem|exam|quiz|assess|survey|form)$/) {
2290: my $symb=
2291: &Apache::lonnet::declutter($course_db{'map_id_'.$mapid}).
2292: '___'.$resid.'___'.&Apache::lonnet::declutter($srcf);
2293: $assesslist{$symb} ='symb:'.&Apache::lonnet::escape($symb).':'
2294: .$mapid.':'.$resid.':'.
2295: &Apache::lonnet::escape($course_db{'title_'.$id});
2296: }
2297: }
2298: untie(%course_db);
2299: # Store away the data
2300: $sheet->{'rowlabel'} = \%assesslist;
2301: $rowlabel_cache{$identifier}=join('___;___',%{$sheet->{'rowlabel'}});
2302: }
2303:
2304: }
2305:
2306: sub get_assess_rowlabels {
2307: my ($sheet) = @_;
2308: #
2309: my %course_db;
2310: #
2311: my $stype = $sheet->{'sheettype'};
2312: my $uname = $sheet->{'uname'};
2313: my $udom = $sheet->{'udom'};
2314: my $usymb = $sheet->{'usymb'};
2315: #
2316: $sheet->{'rowlabel'} = {};
2317: my $identifier =$sheet->{'coursefilename'}.'_'.$stype.'_'.$usymb;
2318: #
2319: if ($rowlabel_cache{$identifier}) {
2320: %{$sheet->{'rowlabel'}}=split(/___;___/,$rowlabel_cache{$identifier});
2321: } else {
2322: # Get the data and store it in the cache
2323: # Tie hash
2324: tie(%course_db,'GDBM_File',$sheet->{'coursefilename'}.'.db',
2325: &GDBM_READER(),0640);
2326: if (! tied(%course_db)) {
2327: return 'Could not access course data';
2328: }
2329: #
2330: my %parameter_labels=
2331: ('timestamp' =>
2332: 'parameter:Timestamp of Last Transaction<br>timestamp',
2333: 'subnumber' =>
2334: 'parameter:Number of Submissions<br>subnumber',
2335: 'tutornumber' =>
2336: 'parameter:Number of Tutor Responses<br>tutornumber',
2337: 'totalpoints' =>
2338: 'parameter:Total Points Granted<br>totalpoints');
2339: while (my ($key,$srcf) = each(%course_db)) {
2340: next if ($key !~ /^src_(\d+)\.(\d+)$/);
2341: my $mapid = $1;
2342: my $resid = $2;
2343: my $id = $mapid.'.'.$resid;
2344: if ($srcf=~/\.(problem|exam|quiz|assess|survey|form)$/) {
2345: # Loop through the metadata for this key
2346: my @Metadata = split(/,/,
2347: &Apache::lonnet::metadata($srcf,'keys'));
2348: foreach my $key (@Metadata) {
2349: next if ($key !~ /^(stores|parameter)_/);
2350: my $display=
2351: &Apache::lonnet::metadata($srcf,$key.'.display');
2352: unless ($display) {
2353: $display.=
2354: &Apache::lonnet::metadata($srcf,$key.'.name');
2355: }
2356: $display.='<br>'.$key;
2357: $parameter_labels{$key}='parameter:'.$display;
2358: } # end of foreach
2359: }
2360: }
2361: untie(%course_db);
2362: # Store away the results
2363: $sheet->{'rowlabel'} = \%parameter_labels;
2364: $rowlabel_cache{$identifier}=join('___;___',%{$sheet->{'rowlabel'}});
2365: }
2366:
2367: }
2368:
2369: sub updatestudentassesssheet {
2370: my $sheet = shift;
2371: if ($sheet->{'sheettype'} eq 'studentcalc') {
2372: &get_student_rowlabels($sheet);
2373: } else {
2374: &get_assess_rowlabels($sheet);
2375: }
2376: # Determine if any of the information has changed
2377: my %f=&getformulas($sheet);
2378: my $changed=0;
2379:
2380: $sheet->{'maxrow'} = 0;
2381: my %existing=();
2382: # Now obsolete rows
2383: foreach my $cell (keys(%f)) {
2384: my $formula = $f{$cell};
2385: next if ($cell !~ /^A(\d+)/);
2386: $sheet->{'maxrow'} = $1 if ($1 > $sheet->{'maxrow'});
2387: my ($usy,$ufn)=split(/__&&&\__/,$formula);
2388: $existing{$usy}=1;
2389: unless ((exists($sheet->{'rowlabel'}->{$usy}) &&
2390: (defined($sheet->{'rowlabel'}->{$usy})) || (!$1) ||
2391: ($formula =~ /^(~~~|---)/) )) {
2392: $f{$_}='!!! Obsolete';
2393: $changed=1;
2394: }
2395: }
2396: # New and unknown keys
2397: foreach (keys(%{$sheet->{'rowlabel'}})) {
2398: unless ($existing{$_}) {
2399: $changed=1;
2400: $sheet->{'maxrow'}++;
2401: $f{'A'.$sheet->{'maxrow'}}=$_;
2402: }
2403: }
2404: if ($changed) {
2405: $sheet->{'f'} = \%f;
2406: &setformulas($sheet);
2407: }
2408: }
2409:
2410: # ------------------------------------------------ Load data for one assessment
2411:
2412: sub loadstudent{
2413: my ($sheet,$r,$c)=@_;
2414: my %constants=();
2415: my %formulas=&getformulas($sheet);
2416: $cachedassess=$sheet->{'uname'}.':'.$sheet->{'udom'};
2417: # Get ALL the student preformance data
2418: my @tmp = &Apache::lonnet::dump($sheet->{'cid'},
2419: $sheet->{'udom'},
2420: $sheet->{'uname'},
2421: undef);
2422: if ($tmp[0] !~ /^error:/) {
2423: %cachedstores = @tmp;
2424: }
2425: undef @tmp;
2426: #
2427: my @assessdata=();
2428: foreach my $cell (keys(%formulas)) {
2429: my $value = $formulas{$cell};
2430: if(defined($c) && ($c->aborted())) {
2431: last;
2432: }
2433: next if ($cell !~ /^A(\d+)/);
2434: my $row=$1;
2435: next if (($value =~ /^[!~-]/) || ($row==0));
2436: my ($usy,$ufn)=split(/__&&&\__/,$value);
2437: @assessdata=&exportsheet($sheet,$sheet->{'uname'},
2438: $sheet->{'udom'},
2439: 'assesscalc',$usy,$ufn,$r);
2440: my $index=0;
2441: foreach my $col ('A','B','C','D','E','F','G','H','I','J','K','L','M',
2442: 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z') {
2443: if (defined($assessdata[$index])) {
2444: if ($assessdata[$index]=~/\D/) {
2445: $constants{$col.$row}="'".$assessdata[$index]."'";
2446: } else {
2447: $constants{$col.$row}=$assessdata[$index];
2448: }
2449: $formulas{$col.$row}='import' if ($col ne 'A');
2450: }
2451: $index++;
2452: }
2453: }
2454: $cachedassess='';
2455: undef %cachedstores;
2456: $sheet->{'f'} = \%formulas;
2457: &setformulas($sheet);
2458: &setconstants($sheet,\%constants);
2459: }
2460:
2461: # --------------------------------------------------- Load data for one student
2462: #
2463: sub loadcourse {
2464: my ($sheet,$r,$c)=@_;
2465: #
2466: my %constants=();
2467: my %formulas=&getformulas($sheet);
2468: #
2469: my $total=0;
2470: foreach (keys(%formulas)) {
2471: if ($_=~/^A(\d+)/) {
2472: unless ($formulas{$_}=~/^[\!\~\-]/) { $total++; }
2473: }
2474: }
2475: my $now=0;
2476: my $since=time;
2477: $r->print(<<ENDPOP);
2478: <script>
2479: popwin=open('','popwin','width=400,height=100');
2480: popwin.document.writeln('<html><body bgcolor="#FFFFFF">'+
2481: '<h3>Spreadsheet Calculation Progress</h3>'+
2482: '<form name=popremain>'+
2483: '<input type=text size=35 name=remaining value=Starting></form>'+
2484: '</body></html>');
2485: popwin.document.close();
2486: </script>
2487: ENDPOP
2488: $r->rflush();
2489: foreach (keys(%formulas)) {
2490: if(defined($c) && ($c->aborted())) {
2491: last;
2492: }
2493: next if ($_!~/^A(\d+)/);
2494: my $row=$1;
2495: next if (($formulas{$_}=~/^[\!\~\-]/) || ($row==0));
2496: my ($sname,$sdom) = split(':',$formulas{$_});
2497: my @studentdata=&exportsheet($sheet,$sname,$sdom,'studentcalc',
2498: undef,undef,$r);
2499: undef %userrdatas;
2500: $now++;
2501: $r->print('<script>popwin.document.popremain.remaining.value="'.
2502: $now.'/'.$total.': '.int((time-$since)/$now*($total-$now)).
2503: ' secs remaining";</script>');
2504: $r->rflush();
2505: #
2506: my $index=0;
2507: foreach ('A','B','C','D','E','F','G','H','I','J','K','L','M',
2508: 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z') {
2509: if (defined($studentdata[$index])) {
2510: my $col=$_;
2511: if ($studentdata[$index]=~/\D/) {
2512: $constants{$col.$row}="'".$studentdata[$index]."'";
2513: } else {
2514: $constants{$col.$row}=$studentdata[$index];
2515: }
2516: unless ($col eq 'A') {
2517: $formulas{$col.$row}='import';
2518: }
2519: }
2520: $index++;
2521: }
2522: }
2523: $sheet->{'f'}=\%formulas;
2524: &setformulas($sheet);
2525: &setconstants($sheet,\%constants);
2526: $r->print('<script>popwin.close()</script>');
2527: $r->rflush();
2528: }
2529:
2530: # ------------------------------------------------ Load data for one assessment
2531: #
2532: sub loadassessment {
2533: my ($sheet,$r,$c)=@_;
2534:
2535: my $uhome = $sheet->{'uhome'};
2536: my $uname = $sheet->{'uname'};
2537: my $udom = $sheet->{'udom'};
2538: my $symb = $sheet->{'usymb'};
2539: my $cid = $sheet->{'cid'};
2540: my $cnum = $sheet->{'cnum'};
2541: my $cdom = $sheet->{'cdom'};
2542: my $chome = $sheet->{'chome'};
2543:
2544: my $namespace;
2545: unless ($namespace=$cid) { return ''; }
2546: # Get stored values
2547: my %returnhash=();
2548: if ($cachedassess eq $uname.':'.$udom) {
2549: #
2550: # get data out of the dumped stores
2551: #
2552: my $version=$cachedstores{'version:'.$symb};
2553: my $scope;
2554: for ($scope=1;$scope<=$version;$scope++) {
2555: foreach (split(/\:/,$cachedstores{$scope.':keys:'.$symb})) {
2556: $returnhash{$_}=$cachedstores{$scope.':'.$symb.':'.$_};
2557: }
2558: }
2559: } else {
2560: #
2561: # restore individual
2562: #
2563: %returnhash = &Apache::lonnet::restore($symb,$namespace,$udom,$uname);
2564: for (my $version=1;$version<=$returnhash{'version'};$version++) {
2565: foreach (split(/\:/,$returnhash{$version.':keys'})) {
2566: $returnhash{$_}=$returnhash{$version.':'.$_};
2567: }
2568: }
2569: }
2570: #
2571: # returnhash now has all stores for this resource
2572: # convert all "_" to "." to be able to use libraries, multiparts, etc
2573: #
2574: # This is dumb. It is also necessary :(
2575: my @oldkeys=keys %returnhash;
2576: #
2577: foreach my $name (@oldkeys) {
2578: my $value=$returnhash{$name};
2579: delete $returnhash{$name};
2580: $name=~s/\_/\./g;
2581: $returnhash{$name}=$value;
2582: }
2583: # initialize coursedata and userdata for this user
2584: undef %courseopt;
2585: undef %useropt;
2586:
2587: my $userprefix=$uname.'_'.$udom.'_';
2588:
2589: unless ($uhome eq 'no_host') {
2590: # Get coursedata
2591: unless ((time-$courserdatas{$cid.'.last_cache'})<240) {
2592: my %Tmp = &Apache::lonnet::dump('resourcedata',$cdom,$cnum);
2593: $courserdatas{$cid}=\%Tmp;
2594: $courserdatas{$cid.'.last_cache'}=time;
2595: }
2596: while (my ($name,$value) = each(%{$courserdatas{$cid}})) {
2597: $courseopt{$userprefix.$name}=$value;
2598: }
2599: # Get userdata (if present)
2600: unless ((time-$userrdatas{$uname.'@'.$udom.'.last_cache'})<240) {
2601: my %Tmp = &Apache::lonnet::dump('resourcedata',$udom,$uname);
2602: $userrdatas{$cid} = \%Tmp;
2603: # Most of the time the user does not have a 'resourcedata.db'
2604: # file. We need to cache that we got nothing instead of bothering
2605: # with requesting it every time.
2606: $userrdatas{$uname.'@'.$udom.'.last_cache'}=time;
2607: }
2608: while (my ($name,$value) = each(%{$userrdatas{$cid}})) {
2609: $useropt{$userprefix.$name}=$value;
2610: }
2611: }
2612: # now courseopt, useropt initialized for this user and course
2613: # (used by parmval)
2614: #
2615: # Load keys for this assessment only
2616: #
2617: my %thisassess=();
2618: my ($symap,$syid,$srcf)=split(/\_\_\_/,$symb);
2619: foreach (split(/\,/,&Apache::lonnet::metadata($srcf,'keys'))) {
2620: $thisassess{$_}=1;
2621: }
2622: #
2623: # Load parameters
2624: #
2625: my %c=();
2626: if (tie(%parmhash,'GDBM_File',
2627: $sheet->{'coursefilename'}.'_parms.db',&GDBM_READER(),0640)) {
2628: my %f=&getformulas($sheet);
2629: foreach my $cell (keys(%f)) {
2630: next if ($cell !~ /^A/);
2631: next if ($f{$cell} =~/^[\!\~\-]/);
2632: if ($f{$cell}=~/^parameter/) {
2633: if (defined($thisassess{$f{$cell}})) {
2634: my $val = &parmval($f{$cell},$sheet);
2635: $c{$cell} = $val;
2636: $c{$f{$cell}} = $val;
2637: }
2638: } else {
2639: my $key=$f{$cell};
2640: my $ckey=$key;
2641: $key=~s/^stores\_/resource\./;
2642: $key=~s/\_/\./g;
2643: $c{$cell}=$returnhash{$key};
2644: $c{$ckey}=$returnhash{$key};
2645: }
2646: }
2647: untie(%parmhash);
2648: }
2649: &setconstants($sheet,\%c);
2650: }
2651:
2652: # --------------------------------------------------------- Various form fields
2653:
2654: sub textfield {
2655: my ($title,$name,$value)=@_;
2656: return "\n<p><b>$title:</b><br>".
2657: '<input type=text name="'.$name.'" size=80 value="'.$value.'">';
2658: }
2659:
2660: sub hiddenfield {
2661: my ($name,$value)=@_;
2662: return "\n".'<input type=hidden name="'.$name.'" value="'.$value.'">';
2663: }
2664:
2665: sub selectbox {
2666: my ($title,$name,$value,%options)=@_;
2667: my $selout="\n<p><b>$title:</b><br>".'<select name="'.$name.'">';
2668: foreach (sort keys(%options)) {
2669: $selout.='<option value="'.$_.'"';
2670: if ($_ eq $value) { $selout.=' selected'; }
2671: $selout.='>'.$options{$_}.'</option>';
2672: }
2673: return $selout.'</select>';
2674: }
2675:
2676: # =============================================== Update information in a sheet
2677: #
2678: # Add new users or assessments, etc.
2679: #
2680:
2681: sub updatesheet {
2682: my ($sheet)=@_;
2683: if ($sheet->{'sheettype'} eq 'classcalc') {
2684: return &updateclasssheet($sheet);
2685: } else {
2686: return &updatestudentassesssheet($sheet);
2687: }
2688: }
2689:
2690: # =================================================== Load the rows for a sheet
2691: #
2692: # Import the data for rows
2693: #
2694:
2695: sub loadrows {
2696: my ($sheet,$r)=@_;
2697: my $c = $r->connection;
2698: my $stype=$sheet->{'sheettype'};
2699: if ($stype eq 'classcalc') {
2700: &loadcourse($sheet,$r,$c);
2701: } elsif ($stype eq 'studentcalc') {
2702: &loadstudent($sheet,$r,$c);
2703: } else {
2704: &loadassessment($sheet,$r,$c);
2705: }
2706: }
2707:
2708: # ======================================================= Forced recalculation?
2709:
2710: sub checkthis {
2711: my ($keyname,$time)=@_;
2712: if (! exists($expiredates{$keyname})) {
2713: return 0;
2714: } else {
2715: return ($time<$expiredates{$keyname});
2716: }
2717: }
2718:
2719: sub forcedrecalc {
2720: my ($uname,$udom,$stype,$usymb)=@_;
2721: my $key=$uname.':'.$udom.':'.$stype.':'.$usymb;
2722: my $time=$oldsheets{$key.'.time'};
2723: if ($ENV{'form.forcerecalc'}) { return 1; }
2724: unless ($time) { return 1; }
2725: if ($stype eq 'assesscalc') {
2726: my $map=(split(/___/,$usymb))[0];
2727: if (&checkthis('::assesscalc:',$time) ||
2728: &checkthis('::assesscalc:'.$map,$time) ||
2729: &checkthis('::assesscalc:'.$usymb,$time) ||
2730: &checkthis($uname.':'.$udom.':assesscalc:',$time) ||
2731: &checkthis($uname.':'.$udom.':assesscalc:'.$map,$time) ||
2732: &checkthis($uname.':'.$udom.':assesscalc:'.$usymb,$time)) {
2733: return 1;
2734: }
2735: } else {
2736: if (&checkthis('::studentcalc:',$time) ||
2737: &checkthis($uname.':'.$udom.':studentcalc:',$time)) {
2738: return 1;
2739: }
2740: }
2741: return 0;
2742: }
2743:
2744: # ============================================================== Export handler
2745: # exportsheet
2746: # returns the export row for a spreadsheet.
2747: #
2748: sub exportsheet {
2749: my ($sheet,$uname,$udom,$stype,$usymb,$fn,$r)=@_;
2750: my $flag = 0;
2751: $uname = $uname || $sheet->{'uname'};
2752: $udom = $udom || $sheet->{'udom'};
2753: $stype = $stype || $sheet->{'sheettype'};
2754: my @exportarr=();
2755: # This handles the assessment sheets for '_feedback', etc
2756: if (defined($usymb) && ($usymb=~/^\_(\w+)/) &&
2757: (!defined($fn) || $fn eq '')) {
2758: $fn='default_'.$1;
2759: }
2760: #
2761: # Check if cached
2762: #
2763: my $key=$uname.':'.$udom.':'.$stype.':'.$usymb;
2764: my $found='';
2765: if ($oldsheets{$key}) {
2766: foreach (split(/___&\___/,$oldsheets{$key})) {
2767: my ($name,$value)=split(/___=___/,$_);
2768: if ($name eq $fn) {
2769: $found=$value;
2770: }
2771: }
2772: }
2773: unless ($found) {
2774: &cachedssheets($sheet,$uname,$udom);
2775: if ($oldsheets{$key}) {
2776: foreach (split(/___&\___/,$oldsheets{$key})) {
2777: my ($name,$value)=split(/___=___/,$_);
2778: if ($name eq $fn) {
2779: $found=$value;
2780: }
2781: }
2782: }
2783: }
2784: #
2785: # Check if still valid
2786: #
2787: if ($found) {
2788: if (&forcedrecalc($uname,$udom,$stype,$usymb)) {
2789: $found='';
2790: }
2791: }
2792: if ($found) {
2793: #
2794: # Return what was cached
2795: #
2796: @exportarr=split(/___;___/,$found);
2797: return @exportarr;
2798: }
2799: #
2800: # Not cached
2801: #
2802: my ($newsheet)=&makenewsheet($uname,$udom,$stype,$usymb);
2803: &readsheet($newsheet,$fn);
2804: &updatesheet($newsheet);
2805: &loadrows($newsheet,$r);
2806: &calcsheet($newsheet);
2807: @exportarr=&exportdata($newsheet);
2808: ##
2809: ## Store now
2810: ##
2811: #
2812: # load in the old value
2813: #
2814: my %currentlystored=();
2815: if ($stype eq 'studentcalc') {
2816: my @tmp = &Apache::lonnet::get('nohist_calculatedsheets',
2817: [$key],
2818: $sheet->{'cdom'},$sheet->{'cnum'});
2819: if ($tmp[0]!~/^error/) {
2820: # We only got one key, so we will access it directly.
2821: foreach (split('___&___',$tmp[1])) {
2822: my ($key,$value) = split('___=___',$_);
2823: $key = '' if (! defined($key));
2824: $currentlystored{$key} = $value;
2825: }
2826: }
2827: } else {
2828: my @tmp = &Apache::lonnet::get('nohist_calculatedsheets_'.
2829: $sheet->{'cid'},[$key],
2830: $sheet->{'udom'},$sheet->{'uname'});
2831: if ($tmp[0]!~/^error/) {
2832: # We only got one key, so we will access it directly.
2833: foreach (split('___&___',$tmp[1])) {
2834: my ($key,$value) = split('___=___',$_);
2835: $key = '' if (! defined($key));
2836: $currentlystored{$key} = $value;
2837: }
2838: }
2839: }
2840: #
2841: # Add the new line
2842: #
2843: $currentlystored{$fn}=join('___;___',@exportarr);
2844: #
2845: # Stick everything back together
2846: #
2847: my $newstore='';
2848: foreach (keys(%currentlystored)) {
2849: if ($newstore) { $newstore.='___&___'; }
2850: $newstore.=$_.'___=___'.$currentlystored{$_};
2851: }
2852: my $now=time;
2853: #
2854: # Store away the new value
2855: #
2856: my $timekey = $key.'.time';
2857: if ($stype eq 'studentcalc') {
2858: my $result = &Apache::lonnet::put('nohist_calculatedsheets',
2859: { $key => $newstore,
2860: $timekey => $now },
2861: $sheet->{'cdom'},
2862: $sheet->{'cnum'});
2863: } else {
2864: my $result = &Apache::lonnet::put('nohist_calculatedsheets_'.$sheet->{'cid'},
2865: { $key => $newstore,
2866: $timekey => $now },
2867: $sheet->{'udom'},
2868: $sheet->{'uname'});
2869: }
2870: return @exportarr;
2871: }
2872:
2873: # ============================================================ Expiration Dates
2874: #
2875: # Load previously cached student spreadsheets for this course
2876: #
2877: sub load_spreadsheet_expirationdates {
2878: undef %expiredates;
2879: my $cid=$ENV{'request.course.id'};
2880: my @tmp = &Apache::lonnet::dump('nohist_expirationdates',
2881: $ENV{'course.'.$cid.'.domain'},
2882: $ENV{'course.'.$cid.'.num'});
2883: if (lc($tmp[0]) !~ /^error/){
2884: %expiredates = @tmp;
2885: }
2886: }
2887:
2888: # ===================================================== Calculated sheets cache
2889: #
2890: # Load previously cached student spreadsheets for this course
2891: #
2892:
2893: sub cachedcsheets {
2894: my $cid=$ENV{'request.course.id'};
2895: my @tmp = &Apache::lonnet::dump('nohist_calculatedsheets',
2896: $ENV{'course.'.$cid.'.domain'},
2897: $ENV{'course.'.$cid.'.num'});
2898: if ($tmp[0] !~ /^error/) {
2899: my %StupidTempHash = @tmp;
2900: while (my ($key,$value) = each %StupidTempHash) {
2901: $oldsheets{$key} = $value;
2902: }
2903: }
2904: }
2905:
2906: # ===================================================== Calculated sheets cache
2907: #
2908: # Load previously cached assessment spreadsheets for this student
2909: #
2910:
2911: sub cachedssheets {
2912: my ($sheet,$uname,$udom) = @_;
2913: $uname = $uname || $sheet->{'uname'};
2914: $udom = $udom || $sheet->{'udom'};
2915: if (! $loadedcaches{$uname.'_'.$udom}) {
2916: my @tmp = &Apache::lonnet::dump('nohist_calculatedsheets',
2917: $sheet->{'udom'},
2918: $sheet->{'uname'});
2919: if ($tmp[0] !~ /^error/) {
2920: my %TempHash = @tmp;
2921: my $count = 0;
2922: while (my ($key,$value) = each %TempHash) {
2923: $oldsheets{$key} = $value;
2924: $count++;
2925: }
2926: $loadedcaches{$sheet->{'uname'}.'_'.$sheet->{'udom'}}=1;
2927: }
2928: }
2929:
2930: }
2931:
2932: # ===================================================== Calculated sheets cache
2933: #
2934: # Load previously cached assessment spreadsheets for this student
2935: #
2936:
2937: # ================================================================ Main handler
2938: #
2939: # Interactive call to screen
2940: #
2941: #
2942: sub handler {
2943: my $r=shift;
2944:
2945: my ($sheettype) = ($r->uri=~/\/(\w+)$/);
2946:
2947: if (! exists($ENV{'form.Status'})) {
2948: $ENV{'form.Status'} = 'Active';
2949: }
2950: if ( ! exists($ENV{'form.output'}) ||
2951: ($sheettype ne 'classcalc' &&
2952: lc($ENV{'form.output'}) eq 'recursive excel')) {
2953: $ENV{'form.output'} = 'HTML';
2954: }
2955: #
2956: # Overload checking
2957: #
2958: # Check this server
2959: my $loaderror=&Apache::lonnet::overloaderror($r);
2960: if ($loaderror) { return $loaderror; }
2961: # Check the course homeserver
2962: $loaderror= &Apache::lonnet::overloaderror($r,
2963: $ENV{'course.'.$ENV{'request.course.id'}.'.home'});
2964: if ($loaderror) { return $loaderror; }
2965: #
2966: # HTML Header
2967: #
2968: if ($r->header_only) {
2969: $r->content_type('text/html');
2970: $r->send_http_header;
2971: return OK;
2972: }
2973: #
2974: # Global directory configs
2975: #
2976: $includedir = $r->dir_config('lonIncludes');
2977: $tmpdir = $r->dir_config('lonDaemons').'/tmp/';
2978: #
2979: # Roles Checking
2980: #
2981: # Needs to be in a course
2982: if (! $ENV{'request.course.fn'}) {
2983: # Not in a course, or not allowed to modify parms
2984: $ENV{'user.error.msg'}=
2985: $r->uri.":opa:0:0:Cannot modify spreadsheet";
2986: return HTTP_NOT_ACCEPTABLE;
2987: }
2988: #
2989: # Get query string for limited number of parameters
2990: #
2991: &Apache::loncommon::get_unprocessed_cgi
2992: ($ENV{'QUERY_STRING'},['uname','udom','usymb','ufn','mapid','resid']);
2993: #
2994: # Deal with restricted student permissions
2995: #
2996: if ($ENV{'request.role'} =~ /^st\./) {
2997: delete $ENV{'form.unewfield'} if (exists($ENV{'form.unewfield'}));
2998: delete $ENV{'form.unewformula'} if (exists($ENV{'form.unewformula'}));
2999: }
3000: #
3001: # Look for special assessment spreadsheets - '_feedback', etc.
3002: #
3003: if (($ENV{'form.usymb'}=~/^\_(\w+)/) && (!$ENV{'form.ufn'} ||
3004: $ENV{'form.ufn'} eq '' ||
3005: $ENV{'form.ufn'} eq 'default')) {
3006: $ENV{'form.ufn'}='default_'.$1;
3007: }
3008: if (!$ENV{'form.ufn'} || $ENV{'form.ufn'} eq 'default') {
3009: $ENV{'form.ufn'}='course_default_'.$sheettype;
3010: }
3011: #
3012: # Interactive loading of specific sheet?
3013: #
3014: if (($ENV{'form.load'}) && ($ENV{'form.loadthissheet'} ne 'Default')) {
3015: $ENV{'form.ufn'}=$ENV{'form.loadthissheet'};
3016: }
3017: #
3018: # Determine the user name and domain for the sheet.
3019: my $aname;
3020: my $adom;
3021: unless ($ENV{'form.uname'}) {
3022: $aname=$ENV{'user.name'};
3023: $adom=$ENV{'user.domain'};
3024: } else {
3025: $aname=$ENV{'form.uname'};
3026: $adom=$ENV{'form.udom'};
3027: }
3028: #
3029: # Open page, try to prevent browser cache.
3030: #
3031: $r->content_type('text/html');
3032: $r->header_out('Cache-control','no-cache');
3033: $r->header_out('Pragma','no-cache');
3034: $r->send_http_header;
3035: #
3036: # Header....
3037: #
3038: $r->print('<html><head><title>LON-CAPA Spreadsheet</title>');
3039: my $nothing = "''";
3040: if ($ENV{'browser.type'} eq 'explorer') {
3041: $nothing = "'javascript:void(0);'";
3042: }
3043:
3044: if ($ENV{'request.role'} !~ /^st\./) {
3045: $r->print(<<ENDSCRIPT);
3046: <script language="JavaScript">
3047:
3048: var editwin;
3049:
3050: function celledit(cellname,cellformula) {
3051: var edit_text = '';
3052: // cellformula may contain less-than and greater-than symbols, so
3053: // we need to escape them?
3054: edit_text +='<html><head><title>Cell Edit Window</title></head><body>';
3055: edit_text += '<form name="editwinform">';
3056: edit_text += '<center><h3>Cell '+cellname+'</h3>';
3057: edit_text += '<textarea name="newformula" cols="40" rows="6"';
3058: edit_text += ' wrap="off" >'+cellformula+'</textarea>';
3059: edit_text += '</br>';
3060: edit_text += '<input type="button" name="accept" value="Accept"';
3061: edit_text += ' onClick=\\\'javascript:';
3062: edit_text += 'opener.document.sheet.unewfield.value=';
3063: edit_text += '"'+cellname+'";';
3064: edit_text += 'opener.document.sheet.unewformula.value=';
3065: edit_text += 'document.editwinform.newformula.value;';
3066: edit_text += 'opener.document.sheet.submit();';
3067: edit_text += 'self.close()\\\' />';
3068: edit_text += ' ';
3069: edit_text += '<input type="button" name="abort" ';
3070: edit_text += 'value="Discard Changes"';
3071: edit_text += ' onClick="javascript:self.close()" />';
3072: edit_text += '</center></body></html>';
3073:
3074: if (editwin != null && !(editwin.closed) ) {
3075: editwin.close();
3076: }
3077:
3078: editwin = window.open($nothing,'CellEditWin','height=200,width=350,scrollbars=no,resizeable=yes,alwaysRaised=yes,dependent=yes',true);
3079: editwin.document.write(edit_text);
3080: }
3081:
3082: function changesheet(cn) {
3083: document.sheet.unewfield.value=cn;
3084: document.sheet.unewformula.value='changesheet';
3085: document.sheet.submit();
3086: }
3087:
3088: function insertrow(cn) {
3089: document.sheet.unewfield.value='insertrow';
3090: document.sheet.unewformula.value=cn;
3091: document.sheet.submit();
3092: }
3093:
3094: </script>
3095: ENDSCRIPT
3096: }
3097: $r->print('</head>'.&Apache::loncommon::bodytag('Grades Spreadsheet').
3098: '<form action="'.$r->uri.'" name="sheet" method="post">');
3099: $r->print(&hiddenfield('uname',$ENV{'form.uname'}).
3100: &hiddenfield('udom',$ENV{'form.udom'}).
3101: &hiddenfield('usymb',$ENV{'form.usymb'}).
3102: &hiddenfield('unewfield','').
3103: &hiddenfield('unewformula',''));
3104: $r->rflush();
3105: #
3106: # Full recalc?
3107: #
3108: if ($ENV{'form.forcerecalc'}) {
3109: $r->print('<h4>Completely Recalculating Sheet ...</h4>');
3110: undef %spreadsheets;
3111: undef %courserdatas;
3112: undef %userrdatas;
3113: undef %defaultsheets;
3114: undef %rowlabel_cache;
3115: }
3116: # Read new sheet or modified worksheet
3117: my ($sheet)=&makenewsheet($aname,$adom,$sheettype,$ENV{'form.usymb'});
3118: #
3119: # Check user permissions
3120: if (($sheet->{'sheettype'} eq 'classcalc' ) ||
3121: ($sheet->{'uname'} ne $ENV{'user.name'} ) ||
3122: ($sheet->{'udom'} ne $ENV{'user.domain'})) {
3123: unless (&Apache::lonnet::allowed('vgr',$sheet->{'cid'})) {
3124: $r->print('<h1>Access Permission Denied</h1>'.
3125: '</form></body></html>');
3126: return OK;
3127: }
3128: }
3129: # Print out user information
3130: $r->print('<h2>'.$sheet->{'coursedesc'}.'</h2>');
3131: if ($sheet->{'sheettype'} ne 'classcalc') {
3132: $r->print('<h2>'.&gettitle($sheet).'</h2><p>');
3133: }
3134: if ($sheet->{'sheettype'} eq 'assesscalc') {
3135: $r->print('<b>User:</b> '.$sheet->{'uname'}.
3136: '<br /><b>Domain:</b> '.$sheet->{'udom'}.'<br />');
3137: }
3138: if ($sheet->{'sheettype'} eq 'studentcalc' ||
3139: $sheet->{'sheettype'} eq 'assesscalc') {
3140: $r->print('<b>Section/Group:</b>'.$sheet->{'csec'}.'</p>');
3141: }
3142: #
3143: # If a new formula had been entered, go from work copy
3144: if ($ENV{'form.unewfield'}) {
3145: $r->print('<h2>Modified Workcopy</h2>');
3146: #$ENV{'form.unewformula'}=~s/\'/\"/g;
3147: $r->print('<p>Cell '.$ENV{'form.unewfield'}.' = <pre>');
3148: $r->print(&HTML::Entities::encode($ENV{'form.unewformula'}).
3149: '</pre></p>');
3150: $sheet->{'filename'} = $ENV{'form.ufn'};
3151: &tmpread($sheet,$ENV{'form.unewfield'},$ENV{'form.unewformula'});
3152: } elsif ($ENV{'form.saveas'}) {
3153: $sheet->{'filename'} = $ENV{'form.ufn'};
3154: &tmpread($sheet);
3155: } else {
3156: &readsheet($sheet,$ENV{'form.ufn'});
3157: }
3158: # Additional options
3159: if ($sheet->{'sheettype'} eq 'assesscalc') {
3160: $r->print('<p><font size=+2>'.
3161: '<a href="/adm/studentcalc?'.
3162: 'uname='.$sheet->{'uname'}.
3163: '&udom='.$sheet->{'udom'}.'">'.
3164: 'Level up: Student Sheet</a></font></p>');
3165: }
3166: if (($sheet->{'sheettype'} eq 'studentcalc') &&
3167: (&Apache::lonnet::allowed('vgr',$sheet->{'cid'}))) {
3168: $r->print ('<p><font size=+2><a href="/adm/classcalc">'.
3169: 'Level up: Course Sheet</a></font></p>');
3170: }
3171: # Recalc button
3172: $r->print('<br />'.
3173: '<input type="submit" name="forcerecalc" '.
3174: 'value="Completely Recalculate Sheet"></p>');
3175: # Save dialog
3176: if (&Apache::lonnet::allowed('opa',$ENV{'request.course.id'})) {
3177: my $fname=$ENV{'form.ufn'};
3178: $fname=~s/\_[^\_]+$//;
3179: if ($fname eq 'default') { $fname='course_default'; }
3180: $r->print('<input type=submit name=saveas value="Save as ...">'.
3181: '<input type=text size=20 name=newfn value="'.$fname.'">'.
3182: 'make default: <input type=checkbox name="makedefufn"><p>');
3183: }
3184: $r->print(&hiddenfield('ufn',$sheet->{'filename'}));
3185: # Load dialog
3186: if (&Apache::lonnet::allowed('opa',$ENV{'request.course.id'})) {
3187: $r->print('<p><input type=submit name=load value="Load ...">'.
3188: '<select name="loadthissheet">'.
3189: '<option name="default">Default</option>');
3190: foreach (&othersheets($sheet)) {
3191: $r->print('<option name="'.$_.'"');
3192: if ($ENV{'form.ufn'} eq $_) {
3193: $r->print(' selected');
3194: }
3195: $r->print('>'.$_.'</option>');
3196: }
3197: $r->print('</select><p>');
3198: if ($sheet->{'sheettype'} eq 'studentcalc') {
3199: &setothersheets($sheet,
3200: &othersheets($sheet,'assesscalc'));
3201: }
3202: }
3203: #
3204: # Set up caching mechanisms
3205: #
3206: &load_spreadsheet_expirationdates();
3207: # Clear out old caches if we have not seen this class before.
3208: if (exists($oldsheets{'course'}) &&
3209: $oldsheets{'course'} ne $sheet->{'cid'}) {
3210: undef %oldsheets;
3211: undef %loadedcaches;
3212: }
3213: $oldsheets{'course'} = $sheet->{'cid'};
3214: #
3215: if ($sheet->{'sheettype'} eq 'classcalc') {
3216: $r->print("Loading previously calculated student sheets ...\n");
3217: $r->rflush();
3218: &cachedcsheets();
3219: } elsif ($sheet->{'sheettype'} eq 'studentcalc') {
3220: $r->print("Loading previously calculated assessment sheets ...\n");
3221: $r->rflush();
3222: &cachedssheets($sheet);
3223: }
3224: # Update sheet, load rows
3225: $r->print("Loaded sheet(s), updating rows ...<br>\n");
3226: $r->rflush();
3227: #
3228: &updatesheet($sheet);
3229: $r->print("Updated rows, loading row data ...\n");
3230: $r->rflush();
3231: #
3232: &loadrows($sheet,$r);
3233: $r->print("Loaded row data, calculating sheet ...<br>\n");
3234: $r->rflush();
3235: #
3236: my $calcoutput=&calcsheet($sheet);
3237: $r->print('<h3><font color=red>'.$calcoutput.'</h3></font>');
3238: # See if something to save
3239: if (&Apache::lonnet::allowed('opa',$ENV{'request.course.id'})) {
3240: my $fname='';
3241: if ($ENV{'form.saveas'} && ($fname=$ENV{'form.newfn'})) {
3242: $fname=~s/\W/\_/g;
3243: if ($fname eq 'default') { $fname='course_default'; }
3244: $fname.='_'.$sheet->{'sheettype'};
3245: $sheet->{'filename'} = $fname;
3246: $ENV{'form.ufn'}=$fname;
3247: $r->print('<p>Saving spreadsheet: '.
3248: &writesheet($sheet,$ENV{'form.makedefufn'}).
3249: '<p>');
3250: }
3251: }
3252: #
3253: # Write the modified worksheet
3254: $r->print('<b>Current sheet:</b> '.$sheet->{'filename'}.'</p>');
3255: &tmpwrite($sheet);
3256: if ($sheet->{'sheettype'} eq 'assesscalc') {
3257: $r->print('<p>Show rows with empty A column: ');
3258: } else {
3259: $r->print('<p>Show empty rows: ');
3260: }
3261: #
3262: $r->print(&hiddenfield('userselhidden','true').
3263: '<input type="checkbox" name="showall" onClick="submit()"');
3264: #
3265: if ($ENV{'form.showall'}) {
3266: $r->print(' checked');
3267: } else {
3268: unless ($ENV{'form.userselhidden'}) {
3269: unless
3270: ($ENV{'course.'.$sheet->{'cid'}.'.hideemptyrows'} eq 'yes') {
3271: $r->print(' checked');
3272: $ENV{'form.showall'}=1;
3273: }
3274: }
3275: }
3276: $r->print('>');
3277: #
3278: # CSV format checkbox (classcalc sheets only)
3279: $r->print(' Output as <select name="output" size="1" onClick="submit()">'.
3280: "\n");
3281: foreach my $mode (qw/HTML CSV Excel/) {
3282: $r->print('<option value="'.$mode.'"');
3283: if ($ENV{'form.output'} eq $mode) {
3284: $r->print(' selected ');
3285: }
3286: $r->print('>'.$mode.'</option>'."\n");
3287: }
3288: #
3289: # Mulit-sheet excel takes too long and does not work at all for large
3290: # classes. Future inclusion of this option may be possible with the
3291: # Spreadsheet::WriteExcel::Big and speed improvements.
3292: #
3293: # if ($sheet->{'sheettype'} eq 'classcalc') {
3294: # $r->print('<option value="recursive excel"');
3295: # if ($ENV{'form.output'} eq 'recursive excel') {
3296: # $r->print(' selected ');
3297: # }
3298: # $r->print(">Multi-Sheet Excel</option>\n");
3299: # }
3300: $r->print("</select>\n");
3301: #
3302: if ($sheet->{'sheettype'} eq 'classcalc') {
3303: $r->print(' Student Status: '.
3304: &Apache::lonhtmlcommon::StatusOptions
3305: ($ENV{'form.Status'},'sheet'));
3306: }
3307: #
3308: # Buttons to insert rows
3309: # $r->print(<<ENDINSERTBUTTONS);
3310: #<br>
3311: #<input type='button' onClick='insertrow("top");'
3312: #value='Insert Row Top'>
3313: #<input type='button' onClick='insertrow("bottom");'
3314: #value='Insert Row Bottom'><br>
3315: #ENDINSERTBUTTONS
3316: # Print out sheet
3317: &outsheet($sheet,$r);
3318: $r->print('</form></body></html>');
3319: # Done
3320: return OK;
3321: }
3322:
3323: 1;
3324: __END__
3325:
3326:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/back.gif){kind=icon}
Return to lonspreadsheet.pm CVS log ![[TXT]](/icons/text.gif){kind=icon}
![[DIR]](/icons/dir.gif){kind=icon}
Up to [LON-CAPA] / loncom / interface