kspread Library API Documentation

kspread_functions_helper.cc

00001 /* This file is part of the KDE project
00002    Copyright (C) 1998-2002 The KSpread Team
00003                            www.koffice.org/kspread
00004 
00005    This library is free software; you can redistribute it and/or
00006    modify it under the terms of the GNU Library General Public
00007    License as published by the Free Software Foundation; either
00008    version 2 of the License.
00009 
00010    This library is distributed in the hope that it will be useful,
00011    but WITHOUT ANY WARRANTY; without even the implied warranty of
00012    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00013    Library General Public License for more details.
00014 
00015    You should have received a copy of the GNU Library General Public License
00016    along with this library; see the file COPYING.LIB.  If not, write to
00017    the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
00018    Boston, MA 02111-1307, USA.
00019 */
00020 
00021 #include <stdlib.h>
00022 #include <math.h>
00023 #include <float.h>
00024 
00025 #include <kdebug.h>
00026 #include <klocale.h>
00027 
00028 #include "kspread_functions.h"
00029 #include "kspread_functions_helper.h"
00030 #include "kspread_util.h"
00031 
00032 
00033 // copied from gnumeric: src/format.c:
00034 static const int g_dateSerial_19000228 = 59;
00035 /* One less that the Julian day number of 19000101.  */
00036 static int g_dateOrigin = 0;
00037 
00038 uint EDate::greg2jul( int y, int m, int d )
00039 {
00040   return QDate::gregorianToJulian( y, m, d ) - g_dateOrigin;
00041 }
00042 
00043 uint EDate::greg2jul( QDate const & date )
00044 {
00045   // reference is 31 Dec, 1899 midnight
00046   QDate refDate = QDate( 1899, 12, 31 );
00047 
00048   return refDate.daysTo( date ) + 1;
00049 }
00050 
00051 void EDate::jul2greg( double num, int & y, int & m, int & d )
00052 {
00053   QDate date = QDate( 1899, 12, 31 );
00054 
00055   date = date.addDays( (int) num );
00056 
00057   y = date.year();
00058   m = date.month();
00059   d = date.day();
00060 
00061   return;
00062 
00063   if ( g_dateOrigin == 0 )
00064     g_dateOrigin = EDate::greg2jul( 1900, 1, 1 ) - 1;
00065 
00066   int i = (int) floor( num + HALFSEC );
00067   if (i > g_dateSerial_19000228)
00068     --i;
00069   else if (i == g_dateSerial_19000228 + 1)
00070     kdWarning() << "Request for date 02/29/1900." << endl;
00071 
00072   QDate::julianToGregorian( i + g_dateOrigin, y, m, d );
00073 }
00074 
00075 
00076 
00077 bool getDate( KSContext & context, KSValue::Ptr & arg, QDate & date )
00078 {
00079   if ( !KSUtil::checkType( context, arg, KSValue::DateType, true ) )
00080   {
00081     if ( !KSUtil::checkType( context, arg, KSValue::StringType, true ) )
00082     {
00083       if ( !KSUtil::checkType( context, arg, KSValue::DoubleType, true ) )
00084         return false;
00085 
00086       double d = arg->doubleValue();
00087 
00088       int y = 0;
00089       int m = 0;
00090       int day = 0;
00091       EDate::jul2greg( d, y, m, day );
00092 
00093       date.setYMD( y, m, day );
00094 
00095       return true;
00096     }
00097     else
00098     {
00099       QString s = arg->stringValue();
00100       bool valid = false;
00101 
00102       date = KGlobal::locale()->readDate( s, &valid );
00103       if ( !valid )
00104         return false;
00105 
00106       return true;
00107     }
00108   }
00109   else
00110   {
00111     date = arg->dateValue();
00112     return true;
00113   }
00114 
00115   return false;
00116 }
00117 
00118 bool getTime( KSContext & context, KSValue::Ptr & arg, QTime & time )
00119 {
00120   if ( !KSUtil::checkType( context, arg, KSValue::TimeType, true ) )
00121   {
00122     if ( !KSUtil::checkType( context, arg, KSValue::StringType, true ) )
00123     {
00124       if ( !KSUtil::checkType( context, arg, KSValue::DoubleType, true ) )
00125         return false;
00126 
00127       double d = arg->doubleValue();
00128 
00129       KSpreadValue v(d);
00130       time = v.asDateTime().time();
00131 
00132       return true;
00133     }
00134     else
00135     {
00136       QString s = arg->stringValue();
00137       bool valid = false;
00138 
00139       time = KGlobal::locale()->readTime( s, &valid );
00140       if ( !valid )
00141         return false;
00142 
00143       return true;
00144     }
00145   }
00146   else
00147   {
00148     time = arg->timeValue();
00149     return true;
00150   }
00151 
00152   return false;
00153 }
00154 
00155 void addMonths( QDate & date, int months )
00156 {
00157   int d = date.day();
00158   int m = date.month() + months;
00159   int y = date.year();
00160 
00161   if ( m > 12 )
00162   {
00163     y += (int) ( m / 12 );
00164     m %= 12;
00165   }
00166 
00167   // e.g. 31 Feb: decrease day...
00168   while ( !QDate::isValid( y, m, d ) && d > 0 )
00169     --d;
00170 
00171   date.setYMD( y, m, d );
00172 }
00173 
00174 void subMonths( QDate & date, int months )
00175 {
00176   int d = date.day();
00177   int m = date.month() - months;
00178   int y = date.year();
00179 
00180   while ( m < 1 )
00181   {
00182     m += 12;
00183     y -= 1;
00184   }
00185 
00186   // e.g. 31 Feb: decrease day
00187   while ( !QDate::isValid( y, m, d ) && d > 0 )
00188     --d;
00189 
00190   date.setYMD( y, m, d );
00191 }
00192 
00193 int daysPerYear(QDate const & date, int basis)
00194 {
00195   switch( basis )
00196   {
00197    case 0:
00198     return 360;
00199 
00200    case 1:
00201     if ( QDate::leapYear( date.year() ) )
00202       return 366;
00203     return 365;
00204 
00205    case 2:
00206     return 360;
00207    case 3:
00208     return 365;
00209    case 4:
00210     return 360;
00211   }
00212 
00213   return -1;
00214 }
00215 
00216 int daysBetweenDates(QDate const & date1, QDate const & date2, int basis)
00217 {
00218   int day1, day2, month1, month2, year1, year2;
00219   bool isLeapYear = false;
00220   int days, months, years;
00221 
00222   day1   = date1.day();
00223   month1 = date1.month();
00224   year1  = date1.year();
00225   day2   = date2.day();
00226   month2 = date2.month();
00227   year2  = date2.year();
00228 
00229   years  = year2  - year1;
00230   months = month2 - month1 + years * 12;
00231   days   = day2   - day1;
00232 
00233   isLeapYear = QDate::leapYear( year1 );
00234 
00235   switch (basis)
00236   {
00237    case 0:
00238     if ( month1 == 2 && month2 != 2 && year1 == year2 )
00239     {
00240       if ( isLeapYear )
00241         return months * 30 + days - 1;
00242       else
00243         return months * 30 + days - 2;
00244     }
00245     return months * 30 + days;
00246 
00247    case 1: // TODO: real days for difference between months!
00248     //    return ( month2 - month1 ) * 30 + years * 360 + days;
00249 
00250    case 2: // TODO: real days for difference between months!
00251     //    return ( month2 - month1 ) * 30 + years * 365 + days;
00252 
00253    case 3:
00254     return date1.daysTo( date2 );
00255 
00256    case 4:
00257     return months * 30 + days;
00258   }
00259 
00260   return -1;
00261 }
00262 
00263 
00264 double fact(int n)
00265 {
00266   return ( n != 0 ? n * fact(n - 1) : 1 );
00267 }
00268 
00269 double combin(int n, int k)
00270 {
00271   if (n >= 15)
00272   {
00273     double result = exp(lgamma (n + 1) - lgamma (k + 1) - lgamma (n - k + 1));
00274     return floor(result + 0.5);
00275   }
00276   else
00277   {
00278     double result = fact( n ) / fact( k ) / fact( n - k );
00279     return result;
00280   }
00281 }
00282 
00283 double gaussinv_helper (double x)
00284 {
00285   double q,t,z;
00286 
00287   q=x-0.5;
00288 
00289   if(fabs(q)<=.425)
00290   {
00291     t=0.180625-q*q;
00292 
00293     z=
00294     q*
00295     (
00296       (
00297         (
00298           (
00299             (
00300               (
00301                 (
00302                   t*2509.0809287301226727+33430.575583588128105
00303                 )
00304                 *t+67265.770927008700853
00305               )
00306               *t+45921.953931549871457
00307             )
00308             *t+13731.693765509461125
00309           )
00310           *t+1971.5909503065514427
00311         )
00312         *t+133.14166789178437745
00313       )
00314       *t+3.387132872796366608
00315     )
00316     /
00317     (
00318       (
00319         (
00320           (
00321             (
00322               (
00323                 (
00324                   t*5226.495278852854561+28729.085735721942674
00325                 )
00326                 *t+39307.89580009271061
00327               )
00328               *t+21213.794301586595867
00329             )
00330             *t+5394.1960214247511077
00331           )
00332           *t+687.1870074920579083
00333         )
00334         *t+42.313330701600911252
00335       )
00336       *t+1.0
00337     );
00338 
00339   }
00340   else
00341   {
00342     if(q>0)  t=1-x;
00343     else    t=x;
00344 
00345     t=sqrt(-log(t));
00346 
00347     if(t<=5.0)
00348     {
00349       t+=-1.6;
00350 
00351       z=
00352       (
00353         (
00354           (
00355             (
00356               (
00357                 (
00358                   (
00359                     t*7.7454501427834140764e-4+0.0227238449892691845833
00360                   )
00361                   *t+0.24178072517745061177
00362                 )
00363                 *t+1.27045825245236838258
00364               )
00365               *t+3.64784832476320460504
00366             )
00367             *t+5.7694972214606914055
00368           )
00369           *t+4.6303378461565452959
00370         )
00371         *t+1.42343711074968357734
00372       )
00373       /
00374       (
00375         (
00376           (
00377             (
00378               (
00379                 (
00380                   (
00381                     t*1.05075007164441684324e-9+5.475938084995344946e-4
00382                   )
00383                   *t+0.0151986665636164571966
00384                 )
00385                 *t+0.14810397642748007459
00386               )
00387               *t+0.68976733498510000455
00388             )
00389             *t+1.6763848301838038494
00390           )
00391           *t+2.05319162663775882187
00392         )
00393         *t+1.0
00394       );
00395 
00396     }
00397     else
00398     {
00399       t+=-5.0;
00400 
00401       z=
00402       (
00403         (
00404           (
00405             (
00406               (
00407                 (
00408                   (
00409                     t*2.01033439929228813265e-7+2.71155556874348757815e-5
00410                   )
00411                   *t+0.0012426609473880784386
00412                 )
00413                 *t+0.026532189526576123093
00414               )
00415               *t+0.29656057182850489123
00416             )
00417             *t+1.7848265399172913358
00418           )
00419           *t+5.4637849111641143699
00420         )
00421         *t+6.6579046435011037772
00422       )
00423       /
00424       (
00425         (
00426           (
00427             (
00428               (
00429                 (
00430                   (
00431                     t*2.04426310338993978564e-15+1.4215117583164458887e-7
00432                   )
00433                   *t+1.8463183175100546818e-5
00434                 )
00435                 *t+7.868691311456132591e-4
00436               )
00437               *t+0.0148753612908506148525
00438             )
00439             *t+0.13692988092273580531
00440           )
00441           *t+0.59983220655588793769
00442         )
00443         *t+1.0
00444       );
00445 
00446     }
00447 
00448     if(q<0.0) z=-z;
00449   }
00450 
00451   return z;
00452 }
00453 
00454 bool approx_equal(double a, double b)
00455 {
00456   if ( a == b )
00457     return TRUE;
00458   double x = a - b;
00459   return (x < 0.0 ? -x : x)  <  ((a < 0.0 ? -a : a) * DBL_EPSILON);
00460 }
00461 
00462 bool kspreadfunc_average_helper( KSContext & context, QValueList<KSValue::Ptr> & args,
00463                                  double & result, int & number, bool aMode )
00464 {
00465   QValueList<KSValue::Ptr>::Iterator it  = args.begin();
00466   QValueList<KSValue::Ptr>::Iterator end = args.end();
00467 
00468   for( ; it != end; ++it )
00469   {
00470     if ( KSUtil::checkType( context, *it, KSValue::ListType, false ) )
00471     {
00472       if ( !kspreadfunc_average_helper( context, (*it)->listValue(), result, number, aMode ) )
00473         return false;
00474     }
00475     else if ( KSUtil::checkType( context, *it, KSValue::DoubleType, true ) )
00476     {
00477       result += (*it)->doubleValue();
00478       ++number;
00479     }
00480     else if ( aMode )
00481     {
00482       if ( KSUtil::checkType( context, *it, KSValue::StringType, true ) )
00483       {
00484         ++number;
00485       }
00486       else if ( KSUtil::checkType( context, *it, KSValue::BoolType, true ) )
00487       {
00488         result += ( (*it)->boolValue() ? 1.0 : 0.0 );
00489         ++number;
00490       }
00491     }
00492   }
00493 
00494   return true;
00495 }
00496 
00497 bool kspreadfunc_stddev_helper( KSContext & context, QValueList<KSValue::Ptr> & args,
00498                                 double & result, double & avera, bool aMode )
00499 {
00500   QValueList<KSValue::Ptr>::Iterator it  = args.begin();
00501   QValueList<KSValue::Ptr>::Iterator end = args.end();
00502 
00503   for( ; it != end; ++it )
00504   {
00505     if ( KSUtil::checkType( context, *it, KSValue::ListType, false ) )
00506     {
00507       if ( !kspreadfunc_stddev_helper( context, (*it)->listValue(), result, avera, aMode ) )
00508         return false;
00509 
00510     }
00511     else if ( KSUtil::checkType( context, *it, KSValue::DoubleType, true ) )
00512     {
00513       result += ( ( (*it)->doubleValue() - avera ) * ( (*it)->doubleValue() - avera ) );
00514     }
00515     else if ( aMode )
00516     {
00517       if ( KSUtil::checkType( context, *it, KSValue::StringType, false ) )
00518       {
00519         result += ( ( 0.0 - avera ) * ( 0.0 - avera ) );
00520       }
00521       else if ( KSUtil::checkType( context, *it, KSValue::BoolType, false ) )
00522       {
00523         double a = ( (*it)->boolValue() ? 1.0 : 0.0 );
00524         result += ( ( a - avera ) * ( a - avera ) );
00525       }
00526     }
00527   }
00528 
00529   return true;
00530 }
00531 
00532 bool kspreadfunc_variance_helper( KSContext & context, QValueList<KSValue::Ptr> & args,
00533                                   double & result, double avera, bool aMode )
00534 {
00535   QValueList<KSValue::Ptr>::Iterator it  = args.begin();
00536   QValueList<KSValue::Ptr>::Iterator end = args.end();
00537 
00538   for( ; it != end; ++it )
00539   {
00540     if ( KSUtil::checkType( context, *it, KSValue::ListType, false ) )
00541     {
00542       if ( !kspreadfunc_variance_helper( context, (*it)->listValue(), result, avera, aMode ) )
00543         return false;
00544     }
00545     else if ( KSUtil::checkType( context, *it, KSValue::DoubleType, true ) )
00546     {
00547       result += ( (*it)->doubleValue() - avera ) * ( (*it)->doubleValue() - avera );
00548     }
00549     else if ( aMode )
00550     {
00551       if ( KSUtil::checkType( context, *it, KSValue::StringType, false ) )
00552       {
00553         result += ( ( 0.0 - avera ) * ( 0.0 - avera ) );
00554       }
00555       else if ( KSUtil::checkType( context, *it, KSValue::BoolType, false ) )
00556       {
00557         double a = ( (*it)->boolValue() ? 1.0 : 0.0 );
00558         result += ( ( a - avera ) * ( a - avera ) );
00559       }
00560     }
00561   }
00562 
00563   return true;
00564 }
00565 
00566 void getCond( KSpreadDB::Condition & cond, QString text )
00567 {
00568   cond.comp = KSpreadDB::isEqual;
00569   text = text.stripWhiteSpace();
00570 
00571   if ( text.startsWith( "<=" ) )
00572   {
00573     cond.comp = KSpreadDB::lessEqual;
00574     text = text.remove( 0, 2 );
00575   }
00576   else if ( text.startsWith( ">=" ) )
00577   {
00578     cond.comp = KSpreadDB::greaterEqual;
00579     text = text.remove( 0, 2 );
00580   }
00581   else if ( text.startsWith( "!=" ) || text.startsWith( "<>" ) )
00582   {
00583     cond.comp = KSpreadDB::notEqual;
00584     text = text.remove( 0, 2 );
00585   }
00586   else if ( text.startsWith( "==" ) )
00587   {
00588     cond.comp = KSpreadDB::isEqual;
00589     text = text.remove( 0, 2 );
00590   }
00591   else if ( text.startsWith( "<" ) )
00592   {
00593     cond.comp = KSpreadDB::isLess;
00594     text = text.remove( 0, 1 );
00595   }
00596   else if ( text.startsWith( ">" ) )
00597   {
00598     cond.comp = KSpreadDB::isGreater;
00599     text = text.remove( 0, 1 );
00600   }
00601   else if ( text.startsWith( "=" ) )
00602   {
00603     cond.comp = KSpreadDB::isEqual;
00604     text = text.remove( 0, 1 );
00605   }
00606 
00607   text = text.stripWhiteSpace();
00608 
00609   bool ok = false;
00610   double d = text.toDouble( &ok );
00611   if ( ok )
00612   {
00613     cond.type = KSpreadDB::numeric;
00614     cond.value = d;
00615     kdDebug() << "Numeric: " << d << ", Op: " << cond.comp << endl;
00616   }
00617   else
00618   {
00619     cond.type = KSpreadDB::string;
00620     cond.stringValue = text;
00621     kdDebug() << "String: " << text << ", Op: " << cond.comp << endl;
00622   }
00623 }
00624 
00625 bool conditionMatches( KSpreadDB::Condition &cond, const double &d )
00626 {
00627   kdDebug() << "Comparing: " << d << " - " << cond.value << "; Comp: " << cond.comp << endl;
00628 
00629   switch ( cond.comp )
00630   {
00631     case KSpreadDB::isEqual:
00632     if ( approx_equal( d, cond.value ) )
00633       return true;
00634 
00635     return false;
00636 
00637     case KSpreadDB::isLess:
00638     if ( d < cond.value )
00639       return true;
00640 
00641     return false;
00642 
00643     case KSpreadDB::isGreater:
00644     if ( d > cond.value )
00645       return true;
00646 
00647     return false;
00648 
00649     case KSpreadDB::lessEqual:
00650     if ( d <= cond.value )
00651       return true;
00652 
00653     return false;
00654 
00655     case KSpreadDB::greaterEqual:
00656     if ( d >= cond.value )
00657       return true;
00658 
00659     return false;
00660 
00661     case KSpreadDB::notEqual:
00662     if ( d != cond.value )
00663       return true;
00664 
00665     return false;
00666 
00667     default:
00668     return false;
00669   }
00670 }
00671 
00672 bool conditionMatches( KSpreadDB::Condition &cond, const QString &d )
00673 {
00674   kdDebug() << "String: " << d << endl;
00675 
00676   switch ( cond.comp )
00677   {
00678     case KSpreadDB::isEqual:
00679     if ( d == cond.stringValue )
00680       return true;
00681 
00682     return false;
00683 
00684     case KSpreadDB::isLess:
00685     if ( d < cond.stringValue )
00686       return true;
00687 
00688     return false;
00689 
00690     case KSpreadDB::isGreater:
00691     if ( d > cond.stringValue )
00692       return true;
00693 
00694     return false;
00695 
00696     case KSpreadDB::lessEqual:
00697     if ( d <= cond.stringValue )
00698       return true;
00699 
00700     return false;
00701 
00702     case KSpreadDB::greaterEqual:
00703     if ( d >= cond.stringValue )
00704       return true;
00705 
00706     return false;
00707 
00708     case KSpreadDB::notEqual:
00709     if ( d != cond.stringValue )
00710       return true;
00711 
00712     return false;
00713 
00714     default:
00715     return false;
00716   }
00717 
00718   return true;
00719 }
KDE Logo
This file is part of the documentation for kspread Library Version 1.4.2.
Documentation copyright © 1996-2004 the KDE developers.
Generated on Mon Feb 13 09:43:10 2006 by doxygen 1.4.2 written by Dimitri van Heesch, © 1997-2003