1
# Plugin for Foswiki - The Free and Open Source Wiki, http://foswiki.org/
3
# Copyright (C) 2001-2007 Peter Thoeny, peter@thoeny.org
4
# Copyright (C) 2008-2009 Foswiki Contributors
6
# This program is free software; you can redistribute it and/or
7
# modify it under the terms of the GNU General Public License
8
# as published by the Free Software Foundation; either version 2
9
# of the License, or (at your option) any later version. For
10
# more details read LICENSE in the root of this distribution.
12
# This program is distributed in the hope that it will be useful,
13
# but WITHOUT ANY WARRANTY; without even the implied warranty of
14
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
# GNU General Public License for more details, published at
16
# http://www.gnu.org/copyleft/gpl.html
18
# As per the GPL, removal of this notice is prohibited.
20
# =========================
22
# This is part of Foswiki's Spreadsheet Plugin.
24
# The code below is kept out of the main plugin module for
25
# performance reasons, so it doesn't get compiled until it
28
package Foswiki::Plugins::SpreadSheetPlugin::Calc;
34
# =========================
36
$web $topic $debug $dontSpaceRE
37
$renderingWeb @tableMatrix $cPos $rPos $escToken
38
%varStore @monArr @wdayArr %mon2num
44
@monArr = ( "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" );
45
@wdayArr = ( "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" );
47
%mon2num = map { $_ => $count++ } @monArr;
51
# =========================
54
( $web, $topic, $debug ) = @_;
56
# initialize variables, once per page view
61
Foswiki::Func::writeDebug( "- Foswiki::Plugins::SpreadSheetPlugin::Calc::init( $web.$topic )" ) if $debug;
65
# =========================
68
### my ( $text, $topic, $web ) = @_; # do not uncomment, use $_[0], $_[1]... instead
70
Foswiki::Func::writeDebug( "- SpreadSheetPlugin::Calc::CALC( $_[2].$_[1] )" ) if $debug;
86
$_[0] =~ s/\\\n//go; # Join lines ending in "\"
87
foreach( split( /\n/, $_[0] ) ) {
90
m|<pre>|i && ( $insidePRE = 1 );
91
m|<verbatim>|i && ( $insidePRE = 1 );
92
m|</pre>|i && ( $insidePRE = 0 );
93
m|</verbatim>|i && ( $insidePRE = 0 );
95
if( ! ( $insidePRE ) ) {
97
if( /^\s*\|.*\|\s*$/ ) {
99
if( ! $insideTABLE ) {
101
@tableMatrix = (); # reset table matrix
106
$line =~ s/^(\s*\|)(.*)\|\s*$/$2/o;
108
@row = split( /\|/o, $line, -1 );
109
push( @tableMatrix, [ @row ] );
112
for( $cPos = 0; $cPos < @row; $cPos++ ) {
114
$cell =~ s/%CALC\{(.*?)\}%/&doCalc($1)/geo;
124
s/%CALC\{(.*?)\}%/&doCalc($1)/geo;
129
$_[0] = join( "\n", @result );
132
# =========================
135
my( $theAttributes ) = @_;
136
my $text = &Foswiki::Func::extractNameValuePair( $theAttributes );
138
# Add nesting level to parenthesis,
139
# e.g. "A(B())" gets "A-esc-1(B-esc-2(-esc-2)-esc-1)"
141
$text =~ s/([\(\)])/addNestingLevel($1, \$level)/geo;
142
$text = doFunc( "MAIN", $text );
144
if( ( $rPos >= 0 ) && ( $cPos >= 0 ) ) {
145
# update cell in table matrix
146
$tableMatrix[$rPos][$cPos] = $text;
152
# =========================
155
my( $theParen, $theLevelRef ) = @_;
158
if( $theParen eq "(" ) {
160
$result = "$escToken$$theLevelRef$theParen";
162
$result = "$escToken$$theLevelRef$theParen";
168
# =========================
171
my( $theFunc, $theAttr ) = @_;
173
$theAttr = "" unless( defined $theAttr );
174
Foswiki::Func::writeDebug( "- SpreadSheetPlugin::Calc::doFunc: $theFunc( $theAttr ) start" ) if $debug;
176
unless( $theFunc =~ /^(IF|LISTIF|LISTMAP|NOEXEC)$/ ) {
177
# Handle functions recursively
178
$theAttr =~ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
179
# Clean up unbalanced mess
180
$theAttr =~ s/$escToken\-*[0-9]+([\(\)])/$1/go;
182
# else: delay the function handler to after parsing the parameters,
183
# in which case handling functions and cleaning up needs to be done later
187
if( $theFunc eq "MAIN" ) {
190
} elsif( $theFunc eq "EXEC" ) {
191
# add nesting level escapes
194
$result =~ s/([\(\)])/addNestingLevel($1, \$level)/geo;
195
# execute functions in attribute recursively and clean up unbalanced parenthesis
196
$result =~ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
197
$result =~ s/$escToken\-*[0-9]+([\(\)])/$1/go;
199
} elsif( $theFunc eq "NOEXEC" ) {
202
} elsif( $theFunc eq "T" ) {
204
my @arr = getTableRange( "$theAttr..$theAttr" );
209
} elsif( $theFunc eq "TRIM" ) {
210
$result = $theAttr || "";
211
$result =~ s/^\s*//o;
212
$result =~ s/\s*$//o;
213
$result =~ s/\s+/ /go;
215
} elsif( $theFunc eq "FORMAT" ) {
216
# Format FORMAT(TYPE, precision, value) returns formatted value -- JimStraus - 05 Jan 2003
217
my( $format, $res, $value ) = split( /,\s*/, $theAttr );
218
$format =~ s/^\s*(.*?)\s*$/$1/; #Strip leading and trailing spaces
219
$res =~ s/^\s*(.*?)\s*$/$1/;
220
$value =~ s/^\s*(.*?)\s*$/$1/;
221
if( $format eq "DOLLAR" ) {
222
my $neg = 1 if $value < 0;
223
$value = abs($value);
224
$result = sprintf("%0.${res}f", $value);
225
my $temp = reverse $result;
226
$temp =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g;
227
$result = "\$" . (scalar reverse $temp);
228
$result = "(".$result.")" if $neg;
229
} elsif( $format eq "COMMA" ) {
230
$result = sprintf("%0.${res}f", $value);
231
my $temp = reverse $result;
232
$temp =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g;
233
$result = scalar reverse $temp;
234
} elsif( $format eq "PERCENT" ) {
235
$result = sprintf("%0.${res}f%%", $value * 100);
236
} elsif( $format eq "NUMBER" ) {
237
$result = sprintf("%0.${res}f", $value);
238
} elsif( $format eq "K" ) {
239
$result = sprintf("%0.${res}f K", $value / 1024);
240
} elsif( $format eq "KB" ) {
241
$result = sprintf("%0.${res}f KB", $value / 1024);
242
} elsif ($format eq "MB") {
243
$result = sprintf("%0.${res}f MB", $value / (1024 * 1024));
244
} elsif( $format =~ /^KBMB/ ) {
246
my @lbls = ( "MB", "GB", "TB", "PB", "EB", "ZB" );
248
while( $value >= 1024 && @lbls ) {
252
$result = sprintf("%0.${res}f", $value) . " $lbl";
254
# FORMAT not recognized, just return value
258
} elsif( $theFunc eq "EMPTY" ) {
260
$result = 0 if( length( $theAttr ) > 0 );
262
} elsif( $theFunc eq "EXACT" ) {
264
my( $str1, $str2 ) = split( /,\s*/, $theAttr, 2 );
265
$str1 = "" unless( $str1 );
266
$str2 = "" unless( $str2 );
267
$str1 =~ s/^\s*(.*?)\s*$/$1/o; # cut leading and trailing spaces
268
$str2 =~ s/^\s*(.*?)\s*$/$1/o;
269
$result = 1 if( $str1 eq $str2 );
271
} elsif( $theFunc eq "RAND" ) {
272
my $max = _getNumber( $theAttr );
273
$max = 1 if( $max <= 0 );
274
$result = rand( $max );
276
} elsif( $theFunc eq "VALUE" ) {
277
$result = _getNumber( $theAttr );
279
} elsif( $theFunc =~ /^(EVAL|INT)$/ ) {
280
$result = safeEvalPerl( $theAttr );
281
unless( $result =~ /^ERROR/ ) {
282
$result = int( _getNumber( $result ) ) if( $theFunc eq "INT" );
285
} elsif( $theFunc eq "ROUND" ) {
287
my( $num, $digits ) = split( /,\s*/, $theAttr, 2 );
288
$result = safeEvalPerl( $num );
289
unless( $result =~ /^ERROR/ ) {
290
$result = _getNumber( $result );
291
if( ( $digits ) && ( $digits =~ s/^.*?(\-?[0-9]+).*$/$1/o ) && ( $digits ) ) {
292
my $factor = 10**$digits;
294
( $result >= 0 ) ? ( $result += 0.5 ) : ( $result -= 0.5 );
295
$result = int( $result );
298
( $result >= 0 ) ? ( $result += 0.5 ) : ( $result -= 0.5 );
299
$result = int( $result );
303
} elsif( $theFunc eq "MOD" ) {
305
my( $num1, $num2 ) = split( /,\s*/, $theAttr, 2 );
306
$num1 = _getNumber( $num1 );
307
$num2 = _getNumber( $num2 );
308
if( $num1 && $num2 ) {
309
$result = $num1 % $num2;
312
} elsif( $theFunc eq "ODD" ) {
313
$result = _getNumber( $theAttr ) % 2;
315
} elsif( $theFunc eq "EVEN" ) {
316
$result = ( _getNumber( $theAttr ) + 1 ) % 2;
318
} elsif( $theFunc eq "AND" ) {
320
my @arr = getListAsInteger( $theAttr );
329
} elsif( $theFunc eq "OR" ) {
331
my @arr = getListAsInteger( $theAttr );
339
} elsif( $theFunc eq "NOT" ) {
341
$result = 0 if( _getNumber( $theAttr ) );
343
} elsif( $theFunc eq "ABS" ) {
344
$result = abs( _getNumber( $theAttr ) );
346
} elsif( $theFunc eq "SIGN" ) {
347
$i = _getNumber( $theAttr );
349
$result = 1 if( $i > 0 );
350
$result = -1 if( $i < 0 );
352
} elsif( $theFunc eq "LN" ) {
353
$result = log(_getNumber( $theAttr ) );
355
} elsif( $theFunc eq "LOG" ) {
356
my( $num, $base ) = split( /,\s*/, $theAttr, 2 );
357
$num = _getNumber( $num );
358
$base = _getNumber( $base );
359
$base = 10 if( $base <= 0 );
360
$result = log( $num ) / log( $base );
362
} elsif( $theFunc eq "EXP" ) {
363
$result = exp( _getNumber( $theAttr ) );
365
} elsif( $theFunc eq "PI" ) {
366
$result = 3.1415926535897932384;
368
} elsif( $theFunc eq "SQRT" ) {
369
$result = sqrt( _getNumber( $theAttr ) );
371
} elsif( $theFunc eq "IF" ) {
372
# IF(condition, value if true, value if false)
373
my( $condition, $str1, $str2 ) = _properSplit( $theAttr, 3 );
374
# with delay, handle functions in condition recursively and clean up unbalanced parenthesis
375
$condition =~ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
376
$condition =~ s/$escToken\-*[0-9]+([\(\)])/$1/go;
377
$condition =~ s/^\s*(.*?)\s*$/$1/o;
378
$result = safeEvalPerl( $condition );
379
unless( $result =~ /^ERROR/ ) {
385
$result = "" unless( defined( $result ) );
386
# with delay, handle functions in result recursively and clean up unbalanced parenthesis
387
$result =~ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
388
$result =~ s/$escToken\-*[0-9]+([\(\)])/$1/go;
390
} # else return error message
392
} elsif( $theFunc eq "UPPER" ) {
393
$result = uc( $theAttr );
395
} elsif( $theFunc eq "LOWER" ) {
396
$result = lc( $theAttr );
398
} elsif( $theFunc eq "PROPER" ) {
400
$result = lc( $theAttr );
401
$result =~ s/(^|[^a-z])([a-z])/$1 . uc($2)/geo;
403
} elsif( $theFunc eq "PROPERSPACE" ) {
404
$result = _properSpace( $theAttr );
406
} elsif( $theFunc eq "CHAR" ) {
407
if( $theAttr =~ /([0-9]+)/ ) {
412
$i = 255 if $i > 255;
416
} elsif( $theFunc eq "REPEAT" ) {
417
my( $str, $num ) = split( /,\s*/, $theAttr, 2 );
418
$str = "" unless( defined( $str ) );
419
$num = _getNumber( $num );
420
$result = "$str" x $num;
422
} elsif( $theFunc eq "CODE" ) {
423
$result = ord( $theAttr );
425
} elsif( $theFunc eq "LENGTH" ) {
426
$result = length( $theAttr );
428
} elsif( $theFunc eq "ROW" ) {
430
$result = $rPos + $i + 1;
432
} elsif( $theFunc eq "COLUMN" ) {
434
$result = $cPos + $i + 1;
436
} elsif( $theFunc eq "LEFT" ) {
438
$result = "R$i:C0..R$i:C$cPos";
440
} elsif( $theFunc eq "ABOVE" ) {
442
$result = "R0:C$i..R$rPos:C$i";
444
} elsif( $theFunc eq "RIGHT" ) {
446
$result = "R$i:C$cPos..R$i:C32000";
448
} elsif( $theFunc eq "DEF" ) {
449
# Format DEF(list) returns first defined cell
450
# Added by MF 26/3/2002, fixed by PeterThoeny
451
my @arr = getList( $theAttr );
452
foreach my $cell ( @arr ) {
454
$cell =~ s/^\s*(.*?)\s*$/$1/o;
462
} elsif( $theFunc eq "MAX" ) {
463
my @arr = sort { $a <=> $b }
466
getListAsFloat( $theAttr );
467
$result = $arr[$#arr];
469
} elsif( $theFunc eq "MIN" ) {
470
my @arr = sort { $a <=> $b }
473
getListAsFloat( $theAttr );
476
} elsif( $theFunc eq "SUM" ) {
478
my @arr = getListAsFloat( $theAttr );
479
foreach $i ( @arr ) {
480
$result += $i if defined $i;
483
} elsif( $theFunc eq "SUMPRODUCT" ) {
486
my @lol = split( /,\s*/, $theAttr );
488
for $i (0 .. $#lol ) {
489
@arr = getListAsFloat( $lol[$i] );
490
$lol[$i] = [ @arr ]; # store reference to array
491
$size = @arr if( @arr < $size ); # remember smallest array
493
if( ( $size > 0 ) && ( $size < 32000 ) ) {
494
my $y; my $prod; my $val;
496
for $y (0 .. $size ) {
498
for $i (0 .. $#lol ) {
503
$prod = 0; # don't count empty cells
510
} elsif( $theFunc =~ /^(SUMDAYS|DURATION)$/ ) {
511
# DURATION is undocumented, is for SvenDowideit
512
# contributed by SvenDowideit - 07 Mar 2003; modified by PTh
514
my @arr = getListAsDays( $theAttr );
515
foreach $i ( @arr ) {
516
$result += $i if defined $i;
519
} elsif( $theFunc eq "WORKINGDAYS" ) {
520
my( $num1, $num2 ) = split( /,\s*/, $theAttr, 2 );
521
$result = _workingDays( _getNumber( $num1 ), _getNumber( $num2 ) );
523
} elsif( $theFunc =~ /^(MULT|PRODUCT)$/ ) { # MULT is deprecated, no not remove
525
my @arr = getListAsFloat( $theAttr );
527
foreach $i ( @arr ) {
528
$result *= $i if defined $i;
531
} elsif( $theFunc =~ /^(AVERAGE|MEAN)$/ ) {
534
my @arr = getListAsFloat( $theAttr );
535
foreach $i ( @arr ) {
542
$result = $result / $items;
545
} elsif( $theFunc eq "MEDIAN" ) {
546
my @arr = sort { $a <=> $b } grep { defined $_ } getListAsFloat( $theAttr );
548
if( ( $i % 2 ) > 0 ) {
549
$result = $arr[$i/2];
552
$result = ( $arr[$i] + $arr[$i-1] ) / 2;
555
} elsif( $theFunc eq "PERCENTILE" ) {
556
my( $percentile, $set ) = split( /,\s*/, $theAttr, 2 );
557
my @arr = sort { $a <=> $b } grep { defined $_ } getListAsFloat( $set );
560
my $size = scalar( @arr );
562
$i = $percentile / 100 * ( $size + 1 );
563
my $iInt = int( $i );
566
} elsif( $i >= $size ) {
567
$result = $arr[$size-1];
568
} elsif( $i == $iInt ) {
569
$result = $arr[$i-1];
571
# interpolate beween neighbors # Example: $i = 7.25
572
my $r1 = $iInt + 1 - $i; # 0.75 = 7 + 1 - 7.25
573
my $r2 = 1 - $r1; # 0.25 = 1 - 0.75
574
my $x1 = $arr[$iInt-1];
575
my $x2 = $arr[$iInt];
576
$result = ($r1 * $x1) + ($r2 * $x2);
580
} elsif( $theFunc eq "COUNTSTR" ) {
581
$result = 0; # count any string
582
$i = 0; # count string equal second attr
585
if( $theAttr =~ /^(.*),\s*(.*?)$/ ) { # greedy match for last comma
590
my @arr = getList( $list );
591
foreach my $cell ( @arr ) {
592
if( defined $cell ) {
593
$cell =~ s/^\s*(.*?)\s*$/$1/o;
594
$result++ if( $cell );
595
$i++ if( $cell eq $str );
598
$result = $i if( $str );
600
} elsif( $theFunc eq "COUNTITEMS" ) {
602
my @arr = getList( $theAttr );
605
foreach $key ( @arr ) {
606
$key =~ s/^\s*(.*?)\s*$/$1/o if( $key );
608
if( exists( $items{ $key } ) ) {
615
foreach $key ( sort keys %items ) {
616
$result .= "$key: $items{ $key }<br /> ";
618
$result =~ s|<br /> $||o;
620
} elsif( $theFunc =~ /^(FIND|SEARCH)$/ ) {
621
my( $searchString, $string, $pos ) = split( /,\s*/, $theAttr, 3 );
622
$string = '' unless ( defined $string );
623
$searchString = '' unless (defined $searchString );
626
$pos = 0 if( $pos < 0 );
627
$searchString = quotemeta( $searchString ) if( $theFunc eq "FIND" );
628
pos( $string ) = $pos if( $pos );
629
# using zero width lookahead '(?=...)' to keep pos at the beginning of match
630
if( $searchString ne '' && eval '$string =~ m/(?=$searchString)/g' ) {
631
$result = pos( $string ) + 1;
634
} elsif( $theFunc eq "REPLACE" ) {
635
my( $string, $start, $num, $replace ) = split ( /,\s*/, $theAttr, 4 );
636
$string = "" unless ( defined $string );
638
$start-- unless ($start < 1);
639
$num = 0 unless( $num );
640
$replace = "" unless( defined $replace );
641
eval 'substr( $string, $start, $num, $replace )';
644
} elsif( $theFunc eq "SUBSTITUTE" ) {
645
my( $string, $from, $to, $inst, $options ) = split( /,\s*/, $theAttr );
646
$string = "" unless ( defined $string );
648
$from = "" unless( defined $from );
649
$from = quotemeta( $from ) unless( $options && $options =~ /r/i);
650
$to = "" unless( defined $to );
652
# Note that the number 0 is valid string. An empty string as well as 0
653
# are valid return values
654
if ( $string ne "" && $from ne "" ) {
656
# replace Nth instance
658
if( eval '$string =~ s/($from)/if( ++$count == $inst ) { $to; } else { $1; }/gex;' ) {
663
if( eval '$string =~ s/$from/$to/g' ) {
669
} elsif( $theFunc =~ /^(MIDSTRING|SUBSTRING)$/ ) {
670
my( $string, $start, $num ) = split ( /,\s*/, $theAttr, 3 );
672
if( $start && $num ) {
673
$start-- unless ($start < 1);
674
eval '$result = substr( $string, $start, $num )';
677
} elsif( $theFunc =~ /^(LEFTSTRING)$/ ) {
678
my( $string, $num ) = split ( /,\s*/, $theAttr, 2 );
679
$string = "" unless ( defined $string );
680
$num = 1 if( !defined $num );
681
eval '$result = substr( $string, 0, $num )';
683
} elsif( $theFunc =~ /^(RIGHTSTRING)$/ ) {
684
my( $string, $num ) = split ( /,\s*/, $theAttr, 2 );
685
$string = "" unless ( defined $string );
686
$num = 1 if( !defined $num );
687
$num = 0 if( $num < 0);
688
my $start = length( $string ) - $num;
689
$start = 0 if $start <0;
690
eval '$result = substr( $string, $start, $num )';
692
} elsif( $theFunc eq "INSERTSTRING" ) {
693
my( $string, $start, $new ) = split ( /,\s*/, $theAttr, 3 );
694
$string = "" unless ( defined $string );
695
$start = _getNumber( $start );
696
eval 'substr( $string, $start, 0, $new )';
699
} elsif( $theFunc eq "TRANSLATE" ) {
701
# greedy match for comma separated parameters (in case first parameter has embedded commas)
702
if( $theAttr =~ /^(.*)\,\s*(.+)\,\s*(.+)$/ ) {
706
$from =~ s/\$comma/,/g; $from =~ s/\$sp/ /g; $from = quotemeta( $from );
707
$to =~ s/\$comma/,/g; $to =~ s/\$sp/ /g; $to = quotemeta( $to );
708
$from =~ s/([a-zA-Z0-9])\\\-([a-zA-Z0-9])/$1\-$2/g; # fix quotemeta (allow only ranges)
709
$to =~ s/([a-zA-Z0-9])\\\-([a-zA-Z0-9])/$1\-$2/g;
711
if( $string && eval "\$string =~ tr/$from/$to/" ) {
716
} elsif ( $theFunc eq "TIME" ) {
718
$result =~ s/^\s+//o;
719
$result =~ s/\s+$//o;
721
$result = _date2serial( $result );
726
} elsif ( $theFunc eq "TODAY" ) {
727
$result = _date2serial( _serial2date( time(), '$year/$month/$day GMT', 1 ) );
729
} elsif( $theFunc =~ /^(FORMATTIME|FORMATGMTIME)$/ ) {
730
my( $time, $str ) = split( /,\s*/, $theAttr, 2 );
731
if( $time =~ /([0-9]+)/ ) {
737
$isGmt = 1 if( ( $str =~ m/ gmt/i ) || ( $theFunc eq "FORMATGMTIME" ) );
738
$result = _serial2date( $time, $str, $isGmt );
740
} elsif( $theFunc eq "FORMATTIMEDIFF" ) {
741
my( $scale, $prec, $time ) = split( /,\s*/, $theAttr, 3 );
742
$scale = "" unless( $scale );
743
$prec = int( _getNumber( $prec ) - 1 );
744
$prec = 0 if( $prec < 0 );
745
$time = _getNumber( $time );
746
$time = 0 if( $time < 0 );
747
my @unit = ( 0, 0, 0, 0, 0, 0 ); # sec, min, hours, days, month, years
748
my @factor = ( 1, 60, 60, 24, 30.4166, 12 ); # sec, min, hours, days, month, years
749
my @singular = ( 'second', 'minute', 'hour', 'day', 'month', 'year' );
750
my @plural = ( 'seconds', 'minutes', 'hours', 'days', 'month', 'years' );
753
if( $scale =~ /^min/i ) {
756
} elsif( $scale =~ /^hou/i ) {
759
} elsif( $scale =~ /^day/i ) {
762
} elsif( $scale =~ /^mon/i ) {
765
} elsif( $scale =~ /^yea/i ) {
775
for( $i = $min; $i < 5; $i++ ) {
777
$val2 = $unit[$i+1] = int($val1 / $factor[$i+1]);
778
$val1 = $unit[$i] = $val1 - int($val2 * $factor[$i+1]);
780
push( @arr, "$val1 $singular[$i]" ) if( $val1 == 1 );
781
push( @arr, "$val1 $plural[$i]" ) if( $val1 > 1 );
783
push( @arr, "$val2 $singular[$i]" ) if( $val2 == 1 );
784
push( @arr, "$val2 $plural[$i]" ) if( $val2 > 1 );
785
push( @arr, "0 $plural[$min]" ) unless( @arr );
786
my @reverse = reverse( @arr );
787
$#reverse = $prec if( @reverse > $prec );
788
$result = join( ', ', @reverse );
789
$result =~ s/(.+)\, /$1 and /;
791
} elsif( $theFunc eq "TIMEADD" ) {
792
my( $time, $value, $scale ) = split( /,\s*/, $theAttr, 3 );
793
$time = 0 unless( $time );
794
$value = 0 unless( $value );
795
$scale = "" unless( $scale );
796
$time =~ s/.*?([0-9]+).*/$1/o || 0;
797
$value =~ s/.*?(\-?[0-9\.]+).*/$1/o || 0;
798
$value *= 60 if( $scale =~ /^min/i );
799
$value *= 3600 if( $scale =~ /^hou/i );
800
$value *= 3600*24 if( $scale =~ /^day/i );
801
$value *= 3600*24*7 if( $scale =~ /^week/i );
802
$value *= 3600*24*30.42 if( $scale =~ /^mon/i ); # FIXME: exact calc
803
$value *= 3600*24*365 if( $scale =~ /^year/i ); # FIXME: exact calc
804
$result = int( $time + $value );
806
} elsif( $theFunc eq "TIMEDIFF" ) {
807
my( $time1, $time2, $scale ) = split( /,\s*/, $theAttr, 3 );
809
$time1 = 0 unless( $time1 );
810
$time2 = 0 unless( $time2 );
811
$time1 =~ s/.*?([0-9]+).*/$1/o || 0;
812
$time2 =~ s/.*?([0-9]+).*/$1/o || 0;
813
$result = $time2 - $time1;
814
$result /= 60 if( $scale =~ /^min/i );
815
$result /= 3600 if( $scale =~ /^hou/i );
816
$result /= 3600*24 if( $scale =~ /^day/i );
817
$result /= 3600*24*7 if( $scale =~ /^week/i );
818
$result /= 3600*24*30.42 if( $scale =~ /^mon/i ); # FIXME: exact calc
819
$result /= 3600*24*365 if( $scale =~ /^year/i ); # FIXME: exact calc
821
} elsif( $theFunc eq "SET" ) {
822
my( $name, $value ) = split( /,\s*/, $theAttr, 2 );
823
$name =~ s/[^a-zA-Z0-9\_]//go;
824
if( $name && defined( $value ) ) {
826
$varStore{ $name } = $value;
829
} elsif( $theFunc eq "SETIFEMPTY" ) {
830
my( $name, $value ) = split( /,\s*/, $theAttr, 2 );
831
$name =~ s/[^a-zA-Z0-9\_]//go;
832
if( $name && defined( $value ) && ! $varStore{ $name } ) {
834
$varStore{ $name } = $value;
837
} elsif( $theFunc eq "SETM" ) {
838
my( $name, $value ) = split( /,\s*/, $theAttr, 2 );
839
$name =~ s/[^a-zA-Z0-9\_]//go;
841
my $old = $varStore{ $name };
842
$old = "" unless( defined( $old ) );
843
$value = safeEvalPerl( "$old $value" );
844
$varStore{ $name } = $value;
847
} elsif( $theFunc eq "GET" ) {
849
$name =~ s/[^a-zA-Z0-9\_]//go;
850
$result = $varStore{ $name } if( $name );
851
$result = "" unless( defined( $result ) );
853
} elsif( $theFunc eq "LIST" ) {
854
my @arr = getList( $theAttr );
855
$result = _listToDelimitedString( @arr );
857
} elsif( $theFunc eq "LISTITEM" ) {
858
my( $index, $str ) = _properSplit( $theAttr, 2 );
859
$index = _getNumber( $index );
860
$str = "" unless( defined( $str ) );
861
my @arr = getList( $str );
862
my $size = scalar @arr;
863
if( $index && $size ) {
864
$index-- if( $index > 0 ); # documented index starts at 1
865
$index = $size + $index if( $index < 0 ); # start from back if negative
866
$result = $arr[$index] if( ( $index >= 0 ) && ( $index < $size ) );
869
} elsif( $theFunc eq "LISTJOIN" ) {
870
my( $sep, $str ) = _properSplit( $theAttr, 2 );
871
$str = "" unless( defined( $str ) );
872
$result = _listToDelimitedString( getList( $str ) );
873
$sep = ", " unless( $sep );
874
$sep =~ s/\$comma/,/go;
877
$result =~ s/, /$sep/go;
879
} elsif( $theFunc eq "LISTSIZE" ) {
880
my @arr = getList( $theAttr );
881
$result = scalar @arr;
883
} elsif( $theFunc eq "LISTSORT" ) {
888
$isNumeric = 0 unless( $_ =~ /^[\+\-]?[0-9\.]+$/ );
890
} getList( $theAttr );
892
@arr = sort { $a <=> $b } @arr;
896
$result = _listToDelimitedString( @arr );
898
} elsif( $theFunc eq "LISTSHUFFLE" ) {
899
my @arr = getList( $theAttr );
900
my $size = scalar @arr;
902
for( $i = $size; $i--; ) {
903
my $j = int( rand( $i + 1 ) );
905
@arr[$i, $j] = @arr[$j, $i];
908
$result = _listToDelimitedString( @arr );
910
} elsif( $theFunc eq "LISTRAND" ) {
911
my @arr = getList( $theAttr );
912
my $size = scalar @arr;
914
$i = int( rand( $size - 1 ) + 0.5 );
916
} elsif( $size == 1 ) {
920
} elsif( $theFunc eq "LISTREVERSE" ) {
921
my @arr = reverse getList( $theAttr );
922
$result = _listToDelimitedString( @arr );
924
} elsif( $theFunc eq "LISTTRUNCATE" ) {
925
my( $index, $str ) = _properSplit( $theAttr, 2 );
926
$index = int( _getNumber( $index ) );
927
$str = "" unless( defined( $str ) );
928
my @arr = getList( $str );
929
my $size = scalar @arr;
931
$index = $size if( $index > $size );
933
$result = _listToDelimitedString( @arr );
934
} elsif( $index < 0 ) {
935
$index = - $size if( $index < - $size );
936
splice( @arr, 0, $size + $index );
937
$result = _listToDelimitedString( @arr );
940
} elsif( $theFunc eq "LISTUNIQUE" ) {
942
my @arr = grep { ! $seen{$_} ++ } getList( $theAttr );
943
$result = _listToDelimitedString( @arr );
945
} elsif( $theFunc eq "LISTMAP" ) {
946
# LISTMAP(action, item 1, item 2, ...)
947
my( $action, $str ) = _properSplit( $theAttr, 2 );
948
$action = "" unless( defined( $action ) );
949
$str = "" unless( defined( $str ) );
950
# with delay, handle functions in result recursively and clean up unbalanced parenthesis
951
$str =~ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
952
$str =~ s/$escToken\-*[0-9]+([\(\)])/$1/go;
961
$_ .= $item unless( s/\$item/$item/go );
962
s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
963
s/$escToken\-*[0-9]+([\(\)])/$1/go;
966
$result = _listToDelimitedString( @arr );
968
} elsif( $theFunc eq "LISTIF" ) {
969
# LISTIF(cmd, item 1, item 2, ...)
970
my( $cmd, $str ) = _properSplit( $theAttr, 2 );
971
$cmd = "" unless( defined( $cmd ) );
972
$cmd =~ s/^\s*(.*?)\s*$/$1/o;
973
$str = "" unless( defined( $str ) );
974
# with delay, handle functions in result recursively and clean up unbalanced parenthesis
975
$str =~ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
976
$str =~ s/$escToken\-*[0-9]+([\(\)])/$1/go;
981
grep { ! /^FOSWIKI_GREP_REMOVE$/ }
988
s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
989
s/$escToken\-*[0-9]+([\(\)])/$1/go;
990
$eval = safeEvalPerl( $_ );
991
if( $eval =~ /^ERROR/ ) {
996
$_ = "FOSWIKI_GREP_REMOVE";
999
$result = _listToDelimitedString( @arr );
1001
} elsif ( $theFunc eq "NOP" ) {
1002
# pass everything through, this will allow plugins to defy plugin order
1003
# for example the %SEARCH{}% variable
1004
$theAttr =~ s/\$per/%/g;
1007
} elsif ( $theFunc eq "EXISTS" ) {
1008
$result = Foswiki::Func::topicExists( $web, $theAttr );
1009
$result = 0 unless( $result );
1012
Foswiki::Func::writeDebug( "- SpreadSheetPlugin::Calc::doFunc: $theFunc( $theAttr ) returns: $result" ) if $debug;
1016
# =========================
1017
sub _listToDelimitedString
1019
my @arr = map { s/^\s*//o; s/\s*$//o; $_ } @_;
1020
my $text = join( ", ", @arr );
1024
# =========================
1027
my( $theAttr, $theLevel ) = @_;
1029
# escape commas inside functions
1030
$theAttr =~ s/(\$[A-Z]+$escToken([0-9]+)\(.*?$escToken\2\))/_escapeCommas($1)/geo;
1031
# split at commas and restore commas inside functions
1032
my @arr = map{ s/<$escToken>/\,/go; $_ } split( /,\s*/, $theAttr, $theLevel );
1036
# =========================
1039
my( $theText ) = @_;
1040
$theText =~ s/\,/<$escToken>/go;
1044
# =========================
1047
my( $theText ) = @_;
1048
return 0 unless( $theText );
1049
$theText =~ s/([0-9])\,(?=[0-9]{3})/$1/go; # "1,234,567" ==> "1234567"
1050
if( $theText =~ /[0-9]e/i ) { # "1.5e-3" ==> "0.0015"
1051
$theText = sprintf "%.20f", $theText;
1052
$theText =~ s/0+$//;
1054
unless( $theText =~ s/^.*?(\-?[0-9\.]+).*$/$1/o ) { # "xy-1.23zz" ==> "-1.23"
1057
$theText =~ s/^(\-?)0+([0-9])/$1$2/o; # "-0009.12" ==> "-9.12"
1058
$theText =~ s/^(\-?)\./${1}0\./o; # "-.25" ==> "-0.25"
1059
$theText =~ s/^\-0$/0/o; # "-0" ==> "0"
1060
$theText =~ s/\.$//o; # "123." ==> "123"
1064
# =========================
1067
my( $theText ) = @_;
1068
# Allow only simple math with operators - + * / % ( )
1069
$theText =~ s/\%\s*[^\-\+\*\/0-9\.\(\)]+//go; # defuse %hash but keep modulus
1070
# keep only numbers and operators (shh... don't tell anyone, we support comparison operators)
1071
$theText =~ s/[^\!\<\=\>\-\+\*\/\%0-9e\.\(\)]*//go;
1072
$theText =~ s/(^|[^0-9])e/$1/go; # remove "e"-s unless in expression such as "123e-4"
1074
$theText = $1; # untainted variable
1075
return "" unless( $theText );
1076
local $SIG{__DIE__} = sub { Foswiki::Func::writeDebug($_[0]); warn $_[0] };
1077
my $result = eval $theText;
1080
$result =~ s/[\n\r]//go;
1081
$result =~ s/\[[^\]]+.*view.*?\:\s?//o; # Cut "[Mon Mar 15 23:31:39 2004] view: "
1082
$result =~ s/\s?at \(eval.*?\)\sline\s?[0-9]*\.?\s?//go; # Cut "at (eval 51) line 2."
1083
$result = "ERROR: $result";
1086
$result = 0 unless( $result ); # logical false is "0"
1091
# =========================
1092
sub getListAsInteger
1094
my( $theAttr ) = @_;
1097
my @list = getList( $theAttr );
1098
(my $baz = "foo") =~ s/foo//; # reset search vars. defensive coding
1099
for my $i (0 .. $#list ) {
1101
# search first integer pattern, skip over HTML tags
1102
if( $val =~ /^\s*(?:<[^>]*>)*([\-\+]*[0-9]+).*/o ) {
1103
$list[$i] = $1; # untainted variable, possibly undef
1111
# =========================
1114
my( $theAttr ) = @_;
1117
my @list = getList( $theAttr );
1118
(my $baz = "foo") =~ s/foo//; # reset search vars. defensive coding
1119
for my $i (0 .. $#list ) {
1120
$val = $list[$i] || "";
1121
# search first float pattern, skip over HTML tags
1122
if( $val =~ /^\s*(?:<[^>]*>)*\$?([\-\+]*[0-9\.]+).*/o ) {
1123
$list[$i] = $1; # untainted variable, possibly undef
1131
# =========================
1134
my( $theAttr ) = @_;
1136
# contributed by by SvenDowideit - 07 Mar 2003; modified by PTh
1138
my @arr = getList( $theAttr );
1139
(my $baz = "foo") =~ s/foo//; # reset search vars. defensive coding
1140
for my $i (0 .. $#arr ) {
1141
$val = $arr[$i] || "";
1142
# search first float pattern
1143
if( $val =~ /^\s*([\-\+]*[0-9\.]+)\s*d/oi ) {
1144
$arr[$i] = $1; # untainted variable, possibly undef
1145
} elsif( $val =~ /^\s*([\-\+]*[0-9\.]+)\s*w/oi ) {
1146
$arr[$i] = 5 * $1; # untainted variable, possibly undef
1147
} elsif( $val =~ /^\s*([\-\+]*[0-9\.]+)\s*h/oi ) {
1148
$arr[$i] = $1 / 8; # untainted variable, possibly undef
1149
} elsif( $val =~ /^\s*([\-\+]*[0-9\.]+)/o ) {
1150
$arr[$i] = $1; # untainted variable, possibly undef
1158
# =========================
1161
my( $theAttr ) = @_;
1164
foreach( split( /,\s*/, $theAttr ) ) {
1165
if( m/\s*R([0-9]+)\:C([0-9]+)\s*\.\.+\s*R([0-9]+)\:C([0-9]+)/ ) {
1167
push( @list, getTableRange( $_ ) );
1170
$list[$#list+1] = $_;
1176
# =========================
1179
my( $theAttr ) = @_;
1186
Foswiki::Func::writeDebug( "- SpreadSheetPlugin::Calc::getTableRange( $theAttr )" ) if $debug;
1187
unless( $theAttr =~ /\s*R([0-9]+)\:C([0-9]+)\s*\.\.+\s*R([0-9]+)\:C([0-9]+)/ ) {
1196
if( $c1 < 0 ) { $c1 = 0; }
1197
if( $c2 < 0 ) { $c2 = 0; }
1198
if( $c2 < $c1 ) { $c = $c1; $c1 = $c2; $c2 = $c; }
1199
if( $r1 > $rPos ) { $r1 = $rPos; }
1200
if( $r1 < 0 ) { $r1 = 0; }
1201
if( $r2 > $rPos ) { $r2 = $rPos; }
1202
if( $r2 < 0 ) { $r2 = 0; }
1203
if( $r2 < $r1 ) { $r = $r1; $r1 = $r2; $r2 = $r; }
1206
for $r ( $r1 .. $r2 ) {
1207
$pRow = $tableMatrix[$r];
1208
for $c ( $c1 .. $c2 ) {
1210
push( @arr, $$pRow[$c] );
1214
Foswiki::Func::writeDebug( "- SpreadSheetPlugin::Calc::getTableRange() returns @arr" ) if $debug;
1218
# =========================
1221
my ( $theText ) = @_;
1223
my $sec = 0; my $min = 0; my $hour = 0; my $day = 1; my $mon = 0; my $year = 0;
1225
if( $theText =~ m|([0-9]{1,2})[-\s/]+([A-Z][a-z][a-z])[-\s/]+([0-9]{4})[-\s/]+([0-9]{1,2}):([0-9]{1,2})| ) {
1226
# "31 Dec 2003 - 23:59", "31-Dec-2003 - 23:59", "31 Dec 2003 - 23:59 - any suffix"
1227
$day = $1; $mon = $mon2num{$2} || 0; $year = $3 - 1900; $hour = $4; $min = $5;
1228
} elsif( $theText =~ m|([0-9]{1,2})[-\s/]+([A-Z][a-z][a-z])[-\s/]+([0-9]{2,4})| ) {
1229
# "31 Dec 2003", "31 Dec 03", "31-Dec-2003", "31/Dec/2003"
1230
$day = $1; $mon = $mon2num{$2} || 0; $year = $3;
1231
$year += 100 if( $year < 80 ); # "05" --> "105" (leave "99" as is)
1232
$year -= 1900 if( $year >= 1900 ); # "2005" --> "105"
1233
} elsif( $theText =~ m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.]([0-9]{1,2})[-\:/\.]([0-9]{1,2})| ) {
1234
# "2003/12/31 23:59:59", "2003-12-31-23-59-59", "2003.12.31.23.59.59"
1235
$year = $1 - 1900; $mon = $2 - 1; $day = $3; $hour = $4; $min = $5; $sec = $6;
1236
} elsif( $theText =~ m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.]([0-9]{1,2})| ) {
1237
# "2003/12/31 23:59", "2003-12-31-23-59", "2003.12.31.23.59"
1238
$year = $1 - 1900; $mon = $2 - 1; $day = $3; $hour = $4; $min = $5;
1239
} elsif( $theText =~ m|([0-9]{4})[-/]([0-9]{1,2})[-/]([0-9]{1,2})| ) {
1240
# "2003/12/31", "2003-12-31"
1241
$year = $1 - 1900; $mon = $2 - 1; $day = $3;
1242
} elsif( $theText =~ m|([0-9]{1,2})[-/]([0-9]{1,2})[-/]([0-9]{2,4})| ) {
1243
# "12/31/2003", "12/31/03", "12-31-2003"
1244
# (shh, don't tell anyone that we support ambiguous American dates, my boss asked me to)
1245
$year = $3; $mon = $1 - 1; $day = $2;
1246
$year += 100 if( $year < 80 ); # "05" --> "105" (leave "99" as is)
1247
$year -= 1900 if( $year >= 1900 ); # "2005" --> "105"
1249
# unsupported format
1252
if( ( $sec > 60 ) || ( $min > 59 ) || ( $hour > 23 ) || ( $day < 1 ) || ( $day > 31 ) || ( $mon > 11 ) ) {
1253
# unsupported, out of range
1257
# Flag to force the TIME function to convert entered dates to GMT.
1258
# This will normally cause trouble for users on a server installed
1259
# the east of Greenwich because dates entered without a time get
1260
# converted to the day before and this is usually not what the user
1261
# intended. Especially the function WORKINGDAYS suffer from this.
1262
# and it also causes surprises with respect to daylight saving time
1263
my $timeislocal = Foswiki::Func::getPreferencesFlag( "SPREADSHEETPLUGIN_TIMEISLOCAL" ) || 0;
1264
$timeislocal = Foswiki::Func::isTrue($timeislocal);
1266
if( $theText =~ /local/i ) {
1267
return timelocal( $sec, $min, $hour, $day, $mon, $year );
1268
} elsif( $theText =~ /gmt/i ) {
1269
return timegm( $sec, $min, $hour, $day, $mon, $year );
1270
} elsif( $timeislocal ) {
1271
return timelocal( $sec, $min, $hour, $day, $mon, $year );
1273
return timegm( $sec, $min, $hour, $day, $mon, $year );
1277
# =========================
1280
my ( $theTime, $theStr, $isGmt ) = @_;
1282
my( $sec, $min, $hour, $day, $mon, $year, $wday, $yday ) = localtime( $theTime );
1283
( $sec, $min, $hour, $day, $mon, $year, $wday, $yday ) = gmtime( $theTime ) if( $isGmt );
1285
$theStr =~ s/\$sec[o]?[n]?[d]?[s]?/sprintf("%.2u",$sec)/geoi;
1286
$theStr =~ s/\$min[u]?[t]?[e]?[s]?/sprintf("%.2u",$min)/geoi;
1287
$theStr =~ s/\$hou[r]?[s]?/sprintf("%.2u",$hour)/geoi;
1288
$theStr =~ s/\$day/sprintf("%.2u",$day)/geoi;
1289
$theStr =~ s/\$mon(?!t)/$monArr[$mon]/goi;
1290
$theStr =~ s/\$mo[n]?[t]?[h]?/sprintf("%.2u",$mon+1)/geoi;
1291
$theStr =~ s/\$yearday/$yday+1/geoi;
1292
$theStr =~ s/\$yea[r]?/sprintf("%.4u",$year+1900)/geoi;
1293
$theStr =~ s/\$ye/sprintf("%.2u",$year%100)/geoi;
1294
$theStr =~ s/\$wday/substr($wdayArr[$wday],0,3)/geoi;
1295
$theStr =~ s/\$wd/$wday+1/geoi;
1296
$theStr =~ s/\$weekday/$wdayArr[$wday]/goi;
1301
# =========================
1304
my ( $theStr ) = @_;
1308
unless( $dontSpaceRE ) {
1309
$dontSpaceRE = &Foswiki::Func::getPreferencesValue( "DONTSPACE" ) ||
1310
&Foswiki::Func::getPreferencesValue( "SPREADSHEETPLUGIN_DONTSPACE" ) ||
1311
"CodeWarrior, MacDonald, McIntosh, RedHat, SuSE";
1312
$dontSpaceRE =~ s/[^a-zA-Z0-9\,\s]//go;
1313
$dontSpaceRE = "(" . join( "|", split( /[\,\s]+/, $dontSpaceRE ) ) . ")";
1314
# Example: "(RedHat|McIntosh)"
1316
$theStr =~ s/$dontSpaceRE/_spaceWikiWord( $1, "<DONT_SPACE>" )/geo; # e.g. "Mc<DONT_SPACE>Intosh"
1317
$theStr =~ s/(^|[\s\(]|\]\[)([a-zA-Z0-9]+)/$1 . _spaceWikiWord( $2, " " )/geo;
1318
$theStr =~ s/<DONT_SPACE>//go; # remove "<DONT_SPACE>" marker
1323
# =========================
1326
my ( $theStr, $theSpacer ) = @_;
1328
$theStr =~ s/([a-z])([A-Z0-9])/$1$theSpacer$2/go;
1329
$theStr =~ s/([0-9])([a-zA-Z])/$1$theSpacer$2/go;
1334
# =========================
1337
my ( $start, $end ) = @_;
1339
# Calculate working days between two times.
1340
# Times are standard system times (secs since 1970).
1341
# Working days are Monday through Friday (sorry, Israel!)
1342
# A day has 60 * 60 * 24 = 86400 sec
1344
# We allow the two dates to be swapped around
1345
($start, $end) = ($end, $start) if ( $start > $end );
1347
my $elapsed_days = int( ( $end - $start ) / 86400 );
1348
my $whole_weeks = int( $elapsed_days / 7 );
1349
my $extra_days = $elapsed_days - ( $whole_weeks * 7 );
1350
my $work_days = $elapsed_days - ($whole_weeks * 2);
1352
for ( my $i = 0; $i < $extra_days; $i++ ) {
1353
my $tempwday = (gmtime( $end - $i * 86400))[6];
1354
if ( $tempwday == 6 || $tempwday == 0 ) {
1362
# =========================