[TWiki-Dev] [SVN] PeterThoeny r18078 - in twiki/branches/TWikiRelease04x03/twikiplugins/SpreadSheetPlugin: data/TWiki lib/TWiki/Plugins/SpreadSheetPlugin

[<de...@de...>]

Author: PeterThoeny
Date: 2009-05-09 21:50:28 -0500 (Sat, 09 May 2009)
New Revision: 18078
Trac url: http://develop.twiki.org/trac/changeset/18078

Modified:
   twiki/branches/TWikiRelease04x03/twikiplugins/SpreadSheetPlugin/data/TWiki/SpreadSheetPlugin.txt
   twiki/branches/TWikiRelease04x03/twikiplugins/SpreadSheetPlugin/lib/TWiki/Plugins/SpreadSheetPlugin/Calc.pm
Log:
Item6253: Fixed bug in $WORKINGDAYS(): Incorrect number of days if daylight savings time change happens between start date and end date

Modified: twiki/branches/TWikiRelease04x03/twikiplugins/SpreadSheetPlugin/data/TWiki/SpreadSheetPlugin.txt
===================================================================
--- twiki/branches/TWikiRelease04x03/twikiplugins/SpreadSheetPlugin/data/TWiki/SpreadSheetPlugin.txt	2009-05-10 02:49:39 UTC (rev 18077)
+++ twiki/branches/TWikiRelease04x03/twikiplugins/SpreadSheetPlugin/data/TWiki/SpreadSheetPlugin.txt	2009-05-10 02:50:28 UTC (rev 18078)
@@ -1,4 +1,4 @@
-%META:TOPICINFO{author="TWikiContributor" date="1238130865" format="1.1" version="$Rev$"}%
+%META:TOPICINFO{author="TWikiContributor" date="1241923164" format="1.1" version="$Rev$"}%
 <!--
    Contributions to this plugin are appreciated. Please update the plugin page
    at http://twiki.org/cgi-bin/view/Plugins/SpreadSheetPlugin or provide feedback
@@ -641,9 +641,11 @@
 
 #FuncTIMEDIFF
 ---+++ TIMEDIFF( serial_1, serial_2, unit ) -- time difference between two serialized dates
-   * The =unit= is seconds if not specified; unit can be specified as in =$TIMEADD()=. Note: An approximation is used for month and year calculations. Use =$FORMAT()=, =$FORMATTIMEDIFF()= or =$INT()= to format real numbers
+   * The =unit= is seconds if not specified; unit can be specified as in =$TIMEADD()=.
+   * Notes: An approximation is used for month and year calculations. Use =$ROUND()= to round =day= unit to account for daylight savings time change. Use =$FORMAT()=, =$FORMATTIMEDIFF()= or =$INT()= to format real numbers
    * Syntax: ==$TIMEDIFF( serial_1, serial_2, _unit_ )==
    * Example: ==%<nop>CALC{"$TIMEDIFF($TIME(), $EVAL($TIME()+90), minute)"}%== returns ==1.5==
+   * Example: ==%<nop>CALC{"$ROUND($TIMEDIFF($TIME(2009/03/06),$TIME(2009/03/13), day))"}%== returns ==7== (or ==6.95833333333333== without the =$ROUND()=)
    * Related: =[[#FuncFORMAT][$FORMAT()]]=, =[[#FuncFORMATGMTIME][$FORMATGMTIME()]]=, =[[#FuncFORMATTIME][$FORMATTIME()]]=, =[[#FuncFORMATTIMEDIFF][$FORMATTIMEDIFF()]]=, =[[#FuncINT][$INT()]]=, =[[#FuncTIME][$TIME()]]=, =[[#FuncTIMEADD][$TIMEADD()]]=, =[[#FuncTODAY][$TODAY()]]=, =[[#FuncWORKINGDAYS][$WORKINGDAYS()]]=
 
 #FuncTODAY
@@ -767,8 +769,9 @@
 |  Plugin Author: | TWiki:Main/PeterThoeny |
 |  Copyright: | &copy; 2001-2009, Peter Thoeny, [[http://www.twiki.net/][TWIKI.NET]] |
 |  License: | GPL ([[http://www.gnu.org/copyleft/gpl.html][GNU General Public License]]) |
-|  Plugin Version: | 26 Mar 2009 (17910) |
+|  Plugin Version: | 09 May 2009 (18078) |
 |  Change History: | <!-- specify latest version first -->&nbsp; |
+|  09 May 2009: | Fixed bug in $WORKINGDAYS(): Incorrect number of days if daylight savings time change happens between start date and end date |
 |  26 Mar 2009: | Added $INSERTSTRING() |
 |  25 Mar 2009: | Added $EMPTY(), $LEFTSTRING(), $RIGHTSTRING(), $SUBSTRING() |
 |  24 Mar 2009: | Fixed bug in $REPLACE() if to-be-replaced string is "0"; fixed bug in $SUBSTITUTE() if replace string is empty; improved docs |

Modified: twiki/branches/TWikiRelease04x03/twikiplugins/SpreadSheetPlugin/lib/TWiki/Plugins/SpreadSheetPlugin/Calc.pm
===================================================================
--- twiki/branches/TWikiRelease04x03/twikiplugins/SpreadSheetPlugin/lib/TWiki/Plugins/SpreadSheetPlugin/Calc.pm	2009-05-10 02:49:39 UTC (rev 18077)
+++ twiki/branches/TWikiRelease04x03/twikiplugins/SpreadSheetPlugin/lib/TWiki/Plugins/SpreadSheetPlugin/Calc.pm	2009-05-10 02:50:28 UTC (rev 18078)
@@ -1302,27 +1302,28 @@
 {
     my ( $start, $end ) = @_;
 
-    # Contributed by CrawfordCurrie - 17 Jul 2004
+    # Rewritten by PeterThoeny - 2009-05-03 (previous implementation was buggy)
     # Calculate working days between two times. Times are standard system times (secs since 1970). 
     # Working days are Monday through Friday (sorry, Israel!)
-
-    use integer;
-    my $elapsed_days = ( $end - $start ) / ( 60 * 60 * 24 );
-    # total number of elapsed 7-day weeks
-    my $whole_weeks = $elapsed_days / 7;
-    my $extra_days = $elapsed_days - ( $whole_weeks * 7 );
-    if( $extra_days > 0 ) {
-      my @lt = gmtime( $start );
-      my $wday = $lt[6]; # weekday, 0 is sunday
-
+    # A day has 60 * 60 * 24 sec
+    # Adding 3601 sec to account for daylight saving change in March in Northern Hemisphere
+    my $days = int( ( abs( $end - $start ) + 3601 ) / 86400 );
+    my $weeks = int( $days / 7 );
+    my $fullWeekWorkingDays = 5 * $weeks;
+    my $extra = $days % 7;
+    if( $extra > 0 ) {
+      $start = $end if( $start > $end );
+      my @tm = gmtime( $start );
+      my $wday = $tm[6]; # 0 is Sun, 6 is Sat
       if( $wday == 0 ) {
-        $extra_days-- if( $extra_days > 0 );
+        $extra--;
       } else {
-        $extra_days-- if( $extra_days > ( 6 - $wday ) );
-        $extra_days-- if( $extra_days > ( 6 - $wday ) );
+        my $sum = $wday + $extra;
+        $extra-- if( $sum > 6 );
+        $extra-- if( $sum > 7 );
       }
     }
-    return $whole_weeks * 5 + $extra_days;
+    return $fullWeekWorkingDays + $extra;
 }
 
 # =========================