00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024 #include "functions.h"
00025 #include "kspread_functions_helper.h"
00026 #include "valuecalc.h"
00027 #include "valueconverter.h"
00028
00029 #include <kcalendarsystem.h>
00030 #include <klocale.h>
00031
00032 using namespace KSpread;
00033
00034
00035 Value func_currentDate (valVector args, ValueCalc *calc, FuncExtra *);
00036 Value func_currentDateTime (valVector args, ValueCalc *calc, FuncExtra *);
00037 Value func_currentTime (valVector args, ValueCalc *calc, FuncExtra *);
00038 Value func_date (valVector args, ValueCalc *calc, FuncExtra *);
00039 Value func_datevalue (valVector args, ValueCalc *calc, FuncExtra *);
00040 Value func_day (valVector args, ValueCalc *calc, FuncExtra *);
00041 Value func_dayname (valVector args, ValueCalc *calc, FuncExtra *);
00042 Value func_dayOfYear (valVector args, ValueCalc *calc, FuncExtra *);
00043 Value func_days (valVector args, ValueCalc *calc, FuncExtra *);
00044 Value func_days360 (valVector args, ValueCalc *calc, FuncExtra *);
00045 Value func_daysInMonth (valVector args, ValueCalc *calc, FuncExtra *);
00046 Value func_daysInYear (valVector args, ValueCalc *calc, FuncExtra *);
00047 Value func_easterSunday (valVector args, ValueCalc *calc, FuncExtra *);
00048 Value func_edate (valVector args, ValueCalc *calc, FuncExtra *);
00049 Value func_eomonth (valVector args, ValueCalc *calc, FuncExtra *);
00050 Value func_hour (valVector args, ValueCalc *calc, FuncExtra *);
00051 Value func_hours (valVector args, ValueCalc *calc, FuncExtra *);
00052 Value func_isLeapYear (valVector args, ValueCalc *calc, FuncExtra *);
00053 Value func_isoWeekNum (valVector args, ValueCalc *calc, FuncExtra *);
00054 Value func_minute (valVector args, ValueCalc *calc, FuncExtra *);
00055 Value func_minutes (valVector args, ValueCalc *calc, FuncExtra *);
00056 Value func_month (valVector args, ValueCalc *calc, FuncExtra *);
00057 Value func_monthname (valVector args, ValueCalc *calc, FuncExtra *);
00058 Value func_months (valVector args, ValueCalc *calc, FuncExtra *);
00059 Value func_second (valVector args, ValueCalc *calc, FuncExtra *);
00060 Value func_seconds (valVector args, ValueCalc *calc, FuncExtra *);
00061 Value func_time (valVector args, ValueCalc *calc, FuncExtra *);
00062 Value func_timevalue (valVector args, ValueCalc *calc, FuncExtra *);
00063 Value func_today (valVector args, ValueCalc *calc, FuncExtra *);
00064 Value func_weekday (valVector args, ValueCalc *calc, FuncExtra *);
00065 Value func_weeks (valVector args, ValueCalc *calc, FuncExtra *);
00066 Value func_weeksInYear (valVector args, ValueCalc *calc, FuncExtra *);
00067 Value func_year (valVector args, ValueCalc *calc, FuncExtra *);
00068 Value func_years (valVector args, ValueCalc *calc, FuncExtra *);
00069
00070
00071
00072 void RegisterDateTimeFunctions()
00073 {
00074
00075
00076
00077 FunctionRepository* repo = FunctionRepository::self();
00078 Function *f;
00079
00080 f = new Function ("CURRENTDATE", func_currentDate);
00081 f->setParamCount (0);
00082 repo->add (f);
00083 f = new Function ("CURRENTDATETIME", func_currentDateTime);
00084 f->setParamCount (0);
00085 repo->add (f);
00086 f = new Function ("CURRENTTIME", func_currentTime);
00087 f->setParamCount (0);
00088 repo->add (f);
00089 f = new Function ("DATE", func_date);
00090 f->setParamCount (3);
00091 repo->add (f);
00092 f = new Function ("DATEVALUE", func_datevalue);
00093 repo->add (f);
00094 f = new Function ("DAY", func_day);
00095 repo->add (f);
00096 f = new Function ("DAYNAME", func_dayname);
00097 repo->add (f);
00098 f = new Function ("DAYOFYEAR", func_dayOfYear);
00099 f->setParamCount (3);
00100 repo->add (f);
00101 f = new Function ("DAYS", func_days);
00102 f->setParamCount (2);
00103 repo->add (f);
00104 f = new Function ("DAYS360", func_days360);
00105 f->setParamCount (2, 3);
00106 repo->add (f);
00107 f = new Function ("DAYSINMONTH", func_daysInMonth);
00108 f->setParamCount (2);
00109 repo->add (f);
00110 f = new Function ("DAYSINYEAR", func_daysInYear);
00111 repo->add (f);
00112 f = new Function ("EASTERSUNDAY", func_easterSunday);
00113 repo->add (f);
00114 f = new Function ("EDATE", func_edate);
00115 f->setParamCount (2);
00116 repo->add (f);
00117 f = new Function ("EOMONTH", func_eomonth);
00118 f->setParamCount (2);
00119 repo->add (f);
00120 f = new Function ("HOUR", func_hour);
00121 f->setParamCount (0, 1);
00122 repo->add (f);
00123 f = new Function ("HOURS", func_hour);
00124 f->setParamCount (0, 1);
00125 repo->add (f);
00126 f = new Function ("ISLEAPYEAR", func_isLeapYear);
00127 repo->add (f);
00128 f = new Function ("ISOWEEKNUM", func_isoWeekNum);
00129 repo->add (f);
00130 f = new Function ("MINUTE", func_minute);
00131 f->setParamCount (0, 1);
00132 repo->add (f);
00133 f = new Function ("MINUTES", func_minute);
00134 f->setParamCount (0, 1);
00135 repo->add (f);
00136 f = new Function ("MONTH", func_month);
00137 repo->add (f);
00138 f = new Function ("MONTHNAME", func_monthname);
00139 repo->add (f);
00140 f = new Function ("MONTHS", func_months);
00141 f->setParamCount (3);
00142 repo->add (f);
00143 f = new Function ("NOW", func_currentDateTime);
00144 f->setParamCount (0);
00145 repo->add (f);
00146 f = new Function ("SECOND", func_second);
00147 f->setParamCount (0, 1);
00148 repo->add (f);
00149 f = new Function ("SECONDS", func_second);
00150 f->setParamCount (0, 1);
00151 repo->add (f);
00152 f = new Function ("TIME", func_time);
00153 f->setParamCount (3);
00154 repo->add (f);
00155 f = new Function ("TIMEVALUE", func_timevalue);
00156 repo->add (f);
00157 f = new Function ("TODAY", func_currentDate);
00158 f->setParamCount (0);
00159 repo->add (f);
00160 f = new Function ("WEEKDAY", func_weekday);
00161 f->setParamCount (1, 2);
00162 repo->add (f);
00163 f = new Function ("WEEKS", func_weeks);
00164 f->setParamCount (3);
00165 repo->add (f);
00166 f = new Function ("WEEKSINYEAR", func_weeksInYear);
00167 repo->add (f);
00168 f = new Function ("YEAR", func_year);
00169 repo->add (f);
00170 f = new Function ("YEARS", func_years);
00171 f->setParamCount (3);
00172 repo->add (f);
00173 }
00174
00175
00176 Value func_edate (valVector args, ValueCalc *calc, FuncExtra *)
00177 {
00178 QDate date = calc->conv()->asDate (args[0]).asDate();
00179 int months = calc->conv()->asInteger (args[1]).asInteger();
00180
00181 date = calc->conv()->locale()->calendar()->addMonths (date, months);
00182
00183 if (!date.isValid())
00184 return Value::errorVALUE();
00185
00186 return Value (date);
00187 }
00188
00189
00190 Value func_eomonth (valVector args, ValueCalc *calc, FuncExtra *)
00191 {
00192
00193 Value modDate = func_edate (args, calc, 0);
00194 if (modDate.isError()) return modDate;
00195
00196
00197 QDate date = modDate.asDate();
00198 date.setYMD (date.year(), date.month(), date.daysInMonth());
00199
00200 return Value (date);
00201 }
00202
00203
00204
00205 Value func_days360 (valVector args, ValueCalc *calc, FuncExtra *)
00206 {
00207 QDate date1 = calc->conv()->asDate (args[0]).asDate();
00208 QDate date2 = calc->conv()->asDate (args[1]).asDate();
00209 bool european = false;
00210 if (args.count() == 3)
00211 european = calc->conv()->asBoolean (args[2]).asBoolean();
00212
00213 int day1, day2;
00214 int month1, month2;
00215 int year1, year2;
00216 bool negative = false;
00217
00218 if (date1.daysTo( date2 ) < 0)
00219 {
00220 QDate tmp( date1 );
00221 date1 = date2;
00222 date2 = tmp;
00223 negative = true;
00224 }
00225
00226 day1 = date1.day();
00227 day2 = date2.day();
00228 month1 = date1.month();
00229 month2 = date2.month();
00230 year1 = date1.year();
00231 year2 = date2.year();
00232
00233 if ( european )
00234 {
00235 if ( day1 == 31 )
00236 day1 = 30;
00237 if ( day2 == 31 )
00238 day2 = 30;
00239 }
00240 else
00241 {
00242
00243 if ( month1 == 2 && month2 == 2
00244 && date1.daysInMonth() == day1
00245 && date2.daysInMonth() == day2 )
00246 day2 = 30;
00247
00248 if ( month1 == 2 && date1.daysInMonth() == day1 )
00249 day1 = 30;
00250
00251 if ( day2 == 31 && day1 >= 30 )
00252 day2 = 30;
00253
00254 if ( day1 == 31 )
00255 day1 = 30;
00256 }
00257
00258 int result = ( ( year2 - year1 ) * 12 + ( month2 - month1 ) ) * 30
00259 + ( day2 - day1 );
00260
00261 return Value (result);
00262 }
00263
00264
00265 Value func_year (valVector args, ValueCalc *calc, FuncExtra *)
00266 {
00267 Value v = calc->conv()->asDate (args[0]);
00268 if (v.isError()) return v;
00269 QDate date = v.asDate();
00270 return Value (date.year ());
00271 }
00272
00273
00274 Value func_month (valVector args, ValueCalc *calc, FuncExtra *)
00275 {
00276 Value v = calc->conv()->asDate (args[0]);
00277 if (v.isError()) return v;
00278 QDate date = v.asDate();
00279 return Value (date.month ());
00280 }
00281
00282
00283 Value func_day (valVector args, ValueCalc *calc, FuncExtra *)
00284 {
00285 Value v = calc->conv()->asDate (args[0]);
00286 if (v.isError()) return v;
00287 QDate date = v.asDate();
00288 return Value (date.day ());
00289 }
00290
00291
00292 Value func_hour (valVector args, ValueCalc *calc, FuncExtra *)
00293 {
00294 QTime time;
00295 if (args.count() == 1)
00296 {
00297 Value v = calc->conv()->asTime (args[0]);
00298 if (v.isError()) return v;
00299 time = v.asTime();
00300 }
00301 else
00302 time = QTime::currentTime ();
00303 return Value (time.hour ());
00304 }
00305
00306
00307 Value func_minute (valVector args, ValueCalc *calc, FuncExtra *)
00308 {
00309 QTime time;
00310 if (args.count() == 1)
00311 {
00312 Value v = calc->conv()->asTime (args[0]);
00313 if (v.isError()) return v;
00314 time = v.asTime();
00315 }
00316 else
00317 time = QTime::currentTime ();
00318 return Value (time.minute ());
00319 }
00320
00321
00322 Value func_second (valVector args, ValueCalc *calc, FuncExtra *)
00323 {
00324 QTime time;
00325 if (args.count() == 1)
00326 {
00327 Value v = calc->conv()->asTime (args[0]);
00328 if (v.isError()) return v;
00329 time = v.asTime();
00330 }
00331 else
00332 time = QTime::currentTime ();
00333 return Value (time.second ());
00334 }
00335
00336
00337 Value func_weekday (valVector args, ValueCalc *calc, FuncExtra *)
00338 {
00339 Value v = calc->conv()->asDate (args[0]).asDate();
00340 if (v.isError()) return v;
00341 QDate date = v.asDate();
00342 int method = 1;
00343 if (args.count() == 2)
00344 method = calc->conv()->asInteger (args[1]).asInteger();
00345
00346 if ( method < 1 || method > 3 )
00347 return Value::errorVALUE();
00348
00349 int result = date.dayOfWeek();
00350
00351 if (method == 3)
00352 --result;
00353 else if (method == 1)
00354 {
00355 ++result;
00356 result = result % 7;
00357 }
00358
00359 return Value (result);
00360 }
00361
00362
00363
00364 Value func_datevalue (valVector args, ValueCalc *calc, FuncExtra *)
00365 {
00366 if (args[0].isString()) {
00367 Value v = calc->conv()->asDate (args[0]);
00368 if (! v.isError())
00369 return calc->conv()->asFloat (v);
00370 }
00371 return Value::errorVALUE();
00372 }
00373
00374
00375
00376 Value func_timevalue (valVector args, ValueCalc *calc, FuncExtra *)
00377 {
00378 if (args[0].isString()) {
00379 Value v = calc->conv()->asTime (args[0]);
00380 if (! v.isError())
00381 return calc->conv()->asFloat (v);
00382 }
00383 return Value::errorVALUE();
00384 }
00385
00386
00387 Value func_years (valVector args, ValueCalc *calc, FuncExtra *)
00388 {
00389 QDate date1 = calc->conv()->asDate (args[0]).asDate();
00390 QDate date2 = calc->conv()->asDate (args[1]).asDate();
00391 if (!date1.isValid() || !date2.isValid())
00392 return Value::errorVALUE();
00393
00394 int type = calc->conv()->asInteger (args[2]).asInteger();
00395 if (type == 0)
00396 {
00397
00398 int years = date2.year() - date1.year();
00399
00400 if (date2.month() < date1.month())
00401 --years;
00402 else if ( (date2.month() == date1.month()) && (date2.day() < date1.day()) )
00403 --years;
00404
00405 return Value (years);
00406 }
00407
00408
00409
00410 if ( date1.year() == date2.year() )
00411 return Value (0);
00412
00413 if ( (date1.month() != 1) || (date1.day() != 1) )
00414 date1.setYMD(date1.year() + 1, 1, 1);
00415 date2.setYMD(date2.year(), 1, 1);
00416
00417 return Value (date2.year() - date1.year());
00418 }
00419
00420
00421 Value func_months (valVector args, ValueCalc *calc, FuncExtra *)
00422 {
00423 QDate date1 = calc->conv()->asDate (args[0]).asDate();
00424 QDate date2 = calc->conv()->asDate (args[1]).asDate();
00425 if (!date1.isValid() || !date2.isValid())
00426 return Value::errorVALUE();
00427
00428 int type = calc->conv()->asInteger (args[2]).asInteger();
00429 if (type == 0)
00430 {
00431 int months = (date2.year() - date1.year()) * 12;
00432 months += date2.month() - date1.month();
00433
00434 if (date2.day() < date1.day())
00435 if (date2.day() != date2.daysInMonth())
00436 --months;
00437
00438 return Value (months);
00439 }
00440
00441
00442
00443 if (date1.month() == 12)
00444 date1.setYMD(date1.year() + 1, 1, 1);
00445 else
00446 date1.setYMD(date1.year(), date1.month() + 1, 1);
00447 date2.setYMD(date2.year(), date2.month(), 1);
00448
00449 int months = (date2.year() - date1.year()) * 12;
00450 months += date2.month() - date1.month();
00451
00452 return Value (months);
00453 }
00454
00455
00456 Value func_weeks (valVector args, ValueCalc *calc, FuncExtra *)
00457 {
00458 QDate date1 = calc->conv()->asDate (args[0]).asDate();
00459 QDate date2 = calc->conv()->asDate (args[1]).asDate();
00460 if (!date1.isValid() || !date2.isValid())
00461 return Value::errorVALUE();
00462
00463 int type = calc->conv()->asInteger (args[2]).asInteger();
00464 int days = date1.daysTo (date2);
00465 if (type == 0)
00466
00467 return Value ((int) (days / 7));
00468
00469
00470 int weekStartDay = calc->conv()->locale()->weekStartDay();
00471
00472 int dow1 = date1.dayOfWeek();
00473 int dow2 = date2.dayOfWeek();
00474
00475 days -= (7 + (weekStartDay % 7) - dow1);
00476 days -= ((dow2 - weekStartDay) % 7);
00477
00478 return Value ((int) (days / 7));
00479 }
00480
00481
00482 Value func_days (valVector args, ValueCalc *calc, FuncExtra *)
00483 {
00484 QDate date1 = calc->conv()->asDate (args[0]).asDate();
00485 QDate date2 = calc->conv()->asDate (args[1]).asDate();
00486 if (!date1.isValid() || !date2.isValid())
00487 return Value::errorVALUE();
00488
00489 return Value (date1.daysTo (date2));
00490 }
00491
00492
00493 Value func_date (valVector args, ValueCalc *calc, FuncExtra *)
00494 {
00495 int y = calc->conv()->asInteger (args[0]).asInteger();
00496 int m = calc->conv()->asInteger (args[1]).asInteger();
00497 int d = calc->conv()->asInteger (args[2]).asInteger();
00498
00499 QDate _date;
00500 if( _date.setYMD (y, m, d))
00501 return Value (_date);
00502 return Value::errorVALUE();
00503 }
00504
00505
00506 Value func_dayname (valVector args, ValueCalc *calc, FuncExtra *)
00507 {
00508 int number = calc->conv()->asInteger (args[0]).asInteger();
00509
00510 QString weekName = calc->conv()->locale()->calendar()->weekDayName (number);
00511 if (weekName.isNull())
00512 return Value::errorVALUE();
00513 return Value (weekName);
00514 }
00515
00516
00517 Value func_monthname (valVector args, ValueCalc *calc, FuncExtra *)
00518 {
00519 int number = calc->conv()->asInteger (args[0]).asInteger();
00520
00521 QString monthName = calc->conv()->locale()->calendar()->monthName (number,
00522 QDate::currentDate().year());
00523 if (monthName.isNull())
00524 return Value::errorVALUE();
00525 return Value (monthName);
00526 }
00527
00528
00529 Value func_time (valVector args, ValueCalc *calc, FuncExtra *)
00530 {
00531 int h = calc->conv()->asInteger (args[0]).asInteger();
00532 int m = calc->conv()->asInteger (args[1]).asInteger();
00533 int s = calc->conv()->asInteger (args[2]).asInteger();
00534
00535
00536 m += s / 60;
00537 s = s % 60;
00538 h += m / 60;
00539 m = m % 60;
00540
00541 h = h % 24;
00542
00543
00544 if (s < 0) {
00545 s += 60;
00546 m -= 1;
00547 }
00548 if (m < 0) {
00549 m += 60;
00550 h -= 1;
00551 }
00552 if (h < 0)
00553 h += 24;
00554
00555 return Value (QTime (h, m, s));
00556 }
00557
00558
00559 Value func_currentDate (valVector, ValueCalc *, FuncExtra *)
00560 {
00561 return Value (QDate::currentDate ());
00562 }
00563
00564
00565 Value func_currentTime (valVector, ValueCalc *, FuncExtra *)
00566 {
00567 return Value (QTime::currentTime ());
00568 }
00569
00570
00571 Value func_currentDateTime (valVector, ValueCalc *, FuncExtra *)
00572 {
00573 return Value (QDateTime::currentDateTime ());
00574 }
00575
00576
00577 Value func_dayOfYear (valVector args, ValueCalc *calc, FuncExtra *)
00578 {
00579 Value date = func_date (args, calc, 0);
00580 if (date.isError()) return date;
00581 return Value (date.asDate().dayOfYear());
00582 }
00583
00584
00585 Value func_daysInMonth (valVector args, ValueCalc *calc, FuncExtra *)
00586 {
00587 int y = calc->conv()->asInteger (args[0]).asInteger();
00588 int m = calc->conv()->asInteger (args[1]).asInteger();
00589 QDate date (y, m, 1);
00590 return Value (date.daysInMonth());
00591 }
00592
00593
00594 Value func_isLeapYear (valVector args, ValueCalc *calc, FuncExtra *)
00595 {
00596 int y = calc->conv()->asInteger (args[0]).asInteger();
00597 return Value (QDate::leapYear (y));
00598 }
00599
00600
00601 Value func_daysInYear (valVector args, ValueCalc *calc, FuncExtra *)
00602 {
00603 int y = calc->conv()->asInteger (args[0]).asInteger();
00604 return Value (QDate::leapYear (y) ? 366 : 365);
00605 }
00606
00607
00608 Value func_weeksInYear (valVector args, ValueCalc *calc, FuncExtra *)
00609 {
00610 int y = calc->conv()->asInteger (args[0]).asInteger();
00611 QDate date (y, 12, 31);
00612 return Value (date.weekNumber ());
00613 }
00614
00615
00616 Value func_easterSunday (valVector args, ValueCalc *calc, FuncExtra *)
00617 {
00618 int nDay, nMonth;
00619 int nYear = calc->conv()->asInteger (args[0]).asInteger();
00620
00621
00622 int B,C,D,E,F,G,H,I,K,L,M,N,O;
00623 N = nYear % 19;
00624 B = int(nYear / 100);
00625 C = nYear % 100;
00626 D = int(B / 4);
00627 E = B % 4;
00628 F = int((B + 8) / 25);
00629 G = int((B - F + 1) / 3);
00630 H = (19 * N + B - D - G + 15) % 30;
00631 I = int(C / 4);
00632 K = C % 4;
00633 L = (32 + 2 * E + 2 * I - H - K) % 7;
00634 M = int((N + 11 * H + 22 * L) / 451);
00635 O = H + L - 7 * M + 114;
00636 nDay = O % 31 + 1;
00637 nMonth = int(O / 31);
00638
00639 return Value (QDate (nYear, nMonth, nDay));
00640 }
00641
00642
00643 Value func_isoWeekNum (valVector args, ValueCalc *calc, FuncExtra *)
00644 {
00645 QDate date = calc->conv()->asDate (args[0]).asDate();
00646 if (!date.isValid())
00647 return Value::errorVALUE();
00648
00649 return Value (date.weekNumber());
00650 }