00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
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
00034 static const int g_dateSerial_19000228 = 59;
00035
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
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
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
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:
00248
00249
00250 case 2:
00251
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 }