KOffice – TDE office suite
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

651 lines
18KB

  1. /* This file is part of the KDE project
  2. Copyright (C) 1998-2003 The KSpread Team
  3. www.koffice.org/kspread
  4. Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
  5. This library is free software; you can redistribute it and/or
  6. modify it under the terms of the GNU Library General Public
  7. License as published by the Free Software Foundation; either
  8. version 2 of the License.
  9. This library is distributed in the hope that it will be useful,
  10. but WITHOUT ANY WARRANTY; without even the implied warranty of
  11. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  12. Library General Public License for more details.
  13. You should have received a copy of the GNU Library General Public License
  14. along with this library; see the file COPYING.LIB. If not, write to
  15. the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
  16. * Boston, MA 02110-1301, USA.
  17. */
  18. // built-in date/time functions
  19. #include "functions.h"
  20. #include "kspread_functions_helper.h"
  21. #include "valuecalc.h"
  22. #include "valueconverter.h"
  23. #include <kcalendarsystem.h>
  24. #include <tdelocale.h>
  25. using namespace KSpread;
  26. // prototypes, sorted
  27. Value func_currentDate (valVector args, ValueCalc *calc, FuncExtra *);
  28. Value func_currentDateTime (valVector args, ValueCalc *calc, FuncExtra *);
  29. Value func_currentTime (valVector args, ValueCalc *calc, FuncExtra *);
  30. Value func_date (valVector args, ValueCalc *calc, FuncExtra *);
  31. Value func_datevalue (valVector args, ValueCalc *calc, FuncExtra *);
  32. Value func_day (valVector args, ValueCalc *calc, FuncExtra *);
  33. Value func_dayname (valVector args, ValueCalc *calc, FuncExtra *);
  34. Value func_dayOfYear (valVector args, ValueCalc *calc, FuncExtra *);
  35. Value func_days (valVector args, ValueCalc *calc, FuncExtra *);
  36. Value func_days360 (valVector args, ValueCalc *calc, FuncExtra *);
  37. Value func_daysInMonth (valVector args, ValueCalc *calc, FuncExtra *);
  38. Value func_daysInYear (valVector args, ValueCalc *calc, FuncExtra *);
  39. Value func_easterSunday (valVector args, ValueCalc *calc, FuncExtra *);
  40. Value func_edate (valVector args, ValueCalc *calc, FuncExtra *);
  41. Value func_eomonth (valVector args, ValueCalc *calc, FuncExtra *);
  42. Value func_hour (valVector args, ValueCalc *calc, FuncExtra *);
  43. Value func_hours (valVector args, ValueCalc *calc, FuncExtra *);
  44. Value func_isLeapYear (valVector args, ValueCalc *calc, FuncExtra *);
  45. Value func_isoWeekNum (valVector args, ValueCalc *calc, FuncExtra *);
  46. Value func_minute (valVector args, ValueCalc *calc, FuncExtra *);
  47. Value func_minutes (valVector args, ValueCalc *calc, FuncExtra *);
  48. Value func_month (valVector args, ValueCalc *calc, FuncExtra *);
  49. Value func_monthname (valVector args, ValueCalc *calc, FuncExtra *);
  50. Value func_months (valVector args, ValueCalc *calc, FuncExtra *);
  51. Value func_second (valVector args, ValueCalc *calc, FuncExtra *);
  52. Value func_seconds (valVector args, ValueCalc *calc, FuncExtra *);
  53. Value func_time (valVector args, ValueCalc *calc, FuncExtra *);
  54. Value func_timevalue (valVector args, ValueCalc *calc, FuncExtra *);
  55. Value func_today (valVector args, ValueCalc *calc, FuncExtra *);
  56. Value func_weekday (valVector args, ValueCalc *calc, FuncExtra *);
  57. Value func_weeks (valVector args, ValueCalc *calc, FuncExtra *);
  58. Value func_weeksInYear (valVector args, ValueCalc *calc, FuncExtra *);
  59. Value func_year (valVector args, ValueCalc *calc, FuncExtra *);
  60. Value func_years (valVector args, ValueCalc *calc, FuncExtra *);
  61. // registers all date/time functions
  62. // sadly, many of these functions aren't Excel compatible
  63. void RegisterDateTimeFunctions()
  64. {
  65. // missing: Excel: WORKDAY, NETWORKDAYS, WEEKNUM, DATEDIF
  66. // Gnumeric: UNIX2DATE, DATE2UNIX
  67. // TODO: do we really need DATEVALUE and TIMEVALUE ?
  68. FunctionRepository* repo = FunctionRepository::self();
  69. Function *f;
  70. f = new Function ("CURRENTDATE", func_currentDate);
  71. f->setParamCount (0);
  72. repo->add (f);
  73. f = new Function ("CURRENTDATETIME", func_currentDateTime);
  74. f->setParamCount (0);
  75. repo->add (f);
  76. f = new Function ("CURRENTTIME", func_currentTime);
  77. f->setParamCount (0);
  78. repo->add (f);
  79. f = new Function ("DATE", func_date);
  80. f->setParamCount (3);
  81. repo->add (f);
  82. f = new Function ("DATEVALUE", func_datevalue);
  83. repo->add (f);
  84. f = new Function ("DAY", func_day);
  85. repo->add (f);
  86. f = new Function ("DAYNAME", func_dayname);
  87. repo->add (f);
  88. f = new Function ("DAYOFYEAR", func_dayOfYear);
  89. f->setParamCount (3);
  90. repo->add (f);
  91. f = new Function ("DAYS", func_days);
  92. f->setParamCount (2);
  93. repo->add (f);
  94. f = new Function ("DAYS360", func_days360);
  95. f->setParamCount (2, 3);
  96. repo->add (f);
  97. f = new Function ("DAYSINMONTH", func_daysInMonth);
  98. f->setParamCount (2);
  99. repo->add (f);
  100. f = new Function ("DAYSINYEAR", func_daysInYear);
  101. repo->add (f);
  102. f = new Function ("EASTERSUNDAY", func_easterSunday);
  103. repo->add (f);
  104. f = new Function ("EDATE", func_edate);
  105. f->setParamCount (2);
  106. repo->add (f);
  107. f = new Function ("EOMONTH", func_eomonth);
  108. f->setParamCount (2);
  109. repo->add (f);
  110. f = new Function ("HOUR", func_hour);
  111. f->setParamCount (0, 1);
  112. repo->add (f);
  113. f = new Function ("HOURS", func_hour); // same as HOUR
  114. f->setParamCount (0, 1);
  115. repo->add (f);
  116. f = new Function ("ISLEAPYEAR", func_isLeapYear);
  117. repo->add (f);
  118. f = new Function ("ISOWEEKNUM", func_isoWeekNum);
  119. repo->add (f);
  120. f = new Function ("MINUTE", func_minute);
  121. f->setParamCount (0, 1);
  122. repo->add (f);
  123. f = new Function ("MINUTES", func_minute); // same as MINUTE
  124. f->setParamCount (0, 1);
  125. repo->add (f);
  126. f = new Function ("MONTH", func_month);
  127. repo->add (f);
  128. f = new Function ("MONTHNAME", func_monthname);
  129. repo->add (f);
  130. f = new Function ("MONTHS", func_months);
  131. f->setParamCount (3);
  132. repo->add (f);
  133. f = new Function ("NOW", func_currentDateTime);
  134. f->setParamCount (0);
  135. repo->add (f);
  136. f = new Function ("SECOND", func_second);
  137. f->setParamCount (0, 1);
  138. repo->add (f);
  139. f = new Function ("SECONDS", func_second); // same as SECOND
  140. f->setParamCount (0, 1);
  141. repo->add (f);
  142. f = new Function ("TIME", func_time);
  143. f->setParamCount (3);
  144. repo->add (f);
  145. f = new Function ("TIMEVALUE", func_timevalue);
  146. repo->add (f);
  147. f = new Function ("TODAY", func_currentDate);
  148. f->setParamCount (0);
  149. repo->add (f);
  150. f = new Function ("WEEKDAY", func_weekday);
  151. f->setParamCount (1, 2);
  152. repo->add (f);
  153. f = new Function ("WEEKS", func_weeks);
  154. f->setParamCount (3);
  155. repo->add (f);
  156. f = new Function ("WEEKSINYEAR", func_weeksInYear);
  157. repo->add (f);
  158. f = new Function ("YEAR", func_year);
  159. repo->add (f);
  160. f = new Function ("YEARS", func_years);
  161. f->setParamCount (3);
  162. repo->add (f);
  163. }
  164. // Function: EDATE
  165. Value func_edate (valVector args, ValueCalc *calc, FuncExtra *)
  166. {
  167. TQDate date = calc->conv()->asDate (args[0]).asDate();
  168. int months = calc->conv()->asInteger (args[1]).asInteger();
  169. date = calc->conv()->locale()->calendar()->addMonths (date, months);
  170. if (!date.isValid())
  171. return Value::errorVALUE();
  172. return Value (date);
  173. }
  174. // Function: EOMONTH
  175. Value func_eomonth (valVector args, ValueCalc *calc, FuncExtra *)
  176. {
  177. // add months to date using EDATE
  178. Value modDate = func_edate (args, calc, 0);
  179. if (modDate.isError()) return modDate;
  180. // modDate is currently in Date format
  181. TQDate date = modDate.asDate();
  182. date.setYMD (date.year(), date.month(), date.daysInMonth());
  183. return Value (date);
  184. }
  185. // Function: DAYS360
  186. // algorithm adapted from gnumeric
  187. Value func_days360 (valVector args, ValueCalc *calc, FuncExtra *)
  188. {
  189. TQDate date1 = calc->conv()->asDate (args[0]).asDate();
  190. TQDate date2 = calc->conv()->asDate (args[1]).asDate();
  191. bool european = false;
  192. if (args.count() == 3)
  193. european = calc->conv()->asBoolean (args[2]).asBoolean();
  194. int day1, day2;
  195. int month1, month2;
  196. int year1, year2;
  197. bool negative = false;
  198. if (date1.daysTo( date2 ) < 0)
  199. {
  200. TQDate tmp( date1 );
  201. date1 = date2;
  202. date2 = tmp;
  203. negative = true;
  204. }
  205. day1 = date1.day();
  206. day2 = date2.day();
  207. month1 = date1.month();
  208. month2 = date2.month();
  209. year1 = date1.year();
  210. year2 = date2.year();
  211. if ( european )
  212. {
  213. if ( day1 == 31 )
  214. day1 = 30;
  215. if ( day2 == 31 )
  216. day2 = 30;
  217. }
  218. else
  219. {
  220. // thanks to the Gnumeric developers for this...
  221. if ( month1 == 2 && month2 == 2
  222. && date1.daysInMonth() == day1
  223. && date2.daysInMonth() == day2 )
  224. day2 = 30;
  225. if ( month1 == 2 && date1.daysInMonth() == day1 )
  226. day1 = 30;
  227. if ( day2 == 31 && day1 >= 30 )
  228. day2 = 30;
  229. if ( day1 == 31 )
  230. day1 = 30;
  231. }
  232. int result = ( ( year2 - year1 ) * 12 + ( month2 - month1 ) ) * 30
  233. + ( day2 - day1 );
  234. return Value (result);
  235. }
  236. // Function: YEAR
  237. Value func_year (valVector args, ValueCalc *calc, FuncExtra *)
  238. {
  239. Value v = calc->conv()->asDate (args[0]);
  240. if (v.isError()) return v;
  241. TQDate date = v.asDate();
  242. return Value (date.year ());
  243. }
  244. // Function: MONTH
  245. Value func_month (valVector args, ValueCalc *calc, FuncExtra *)
  246. {
  247. Value v = calc->conv()->asDate (args[0]);
  248. if (v.isError()) return v;
  249. TQDate date = v.asDate();
  250. return Value (date.month ());
  251. }
  252. // Function: DAY
  253. Value func_day (valVector args, ValueCalc *calc, FuncExtra *)
  254. {
  255. Value v = calc->conv()->asDate (args[0]);
  256. if (v.isError()) return v;
  257. TQDate date = v.asDate();
  258. return Value (date.day ());
  259. }
  260. // Function: HOUR
  261. Value func_hour (valVector args, ValueCalc *calc, FuncExtra *)
  262. {
  263. TQTime time;
  264. if (args.count() == 1)
  265. {
  266. Value v = calc->conv()->asTime (args[0]);
  267. if (v.isError()) return v;
  268. time = v.asTime();
  269. }
  270. else
  271. time = TQTime::currentTime ();
  272. return Value (time.hour ());
  273. }
  274. // Function: MINUTE
  275. Value func_minute (valVector args, ValueCalc *calc, FuncExtra *)
  276. {
  277. TQTime time;
  278. if (args.count() == 1)
  279. {
  280. Value v = calc->conv()->asTime (args[0]);
  281. if (v.isError()) return v;
  282. time = v.asTime();
  283. }
  284. else
  285. time = TQTime::currentTime ();
  286. return Value (time.minute ());
  287. }
  288. // Function: SECOND
  289. Value func_second (valVector args, ValueCalc *calc, FuncExtra *)
  290. {
  291. TQTime time;
  292. if (args.count() == 1)
  293. {
  294. Value v = calc->conv()->asTime (args[0]);
  295. if (v.isError()) return v;
  296. time = v.asTime();
  297. }
  298. else
  299. time = TQTime::currentTime ();
  300. return Value (time.second ());
  301. }
  302. // Function: weekday
  303. Value func_weekday (valVector args, ValueCalc *calc, FuncExtra *)
  304. {
  305. Value v = calc->conv()->asDate (args[0]).asDate();
  306. if (v.isError()) return v;
  307. TQDate date = v.asDate();
  308. int method = 1;
  309. if (args.count() == 2)
  310. method = calc->conv()->asInteger (args[1]).asInteger();
  311. if ( method < 1 || method > 3 )
  312. return Value::errorVALUE();
  313. int result = date.dayOfWeek();
  314. if (method == 3)
  315. --result;
  316. else if (method == 1)
  317. {
  318. ++result;
  319. result = result % 7;
  320. }
  321. return Value (result);
  322. }
  323. // Function: datevalue
  324. // same result would be obtained by applying number format on a date value
  325. Value func_datevalue (valVector args, ValueCalc *calc, FuncExtra *)
  326. {
  327. if (args[0].isString()) {
  328. Value v = calc->conv()->asDate (args[0]);
  329. if (! v.isError())
  330. return calc->conv()->asFloat (v);
  331. }
  332. return Value::errorVALUE();
  333. }
  334. // Function: timevalue
  335. // same result would be obtained by applying number format on a time value
  336. Value func_timevalue (valVector args, ValueCalc *calc, FuncExtra *)
  337. {
  338. if (args[0].isString()) {
  339. Value v = calc->conv()->asTime (args[0]);
  340. if (! v.isError())
  341. return calc->conv()->asFloat (v);
  342. }
  343. return Value::errorVALUE();
  344. }
  345. // Function: years
  346. Value func_years (valVector args, ValueCalc *calc, FuncExtra *)
  347. {
  348. TQDate date1 = calc->conv()->asDate (args[0]).asDate();
  349. TQDate date2 = calc->conv()->asDate (args[1]).asDate();
  350. if (!date1.isValid() || !date2.isValid())
  351. return Value::errorVALUE();
  352. int type = calc->conv()->asInteger (args[2]).asInteger();
  353. if (type == 0)
  354. {
  355. // max. possible years between both dates
  356. int years = date2.year() - date1.year();
  357. if (date2.month() < date1.month())
  358. --years;
  359. else if ( (date2.month() == date1.month()) && (date2.day() < date1.day()) )
  360. --years;
  361. return Value (years);
  362. }
  363. // type is non-zero now
  364. // the number of full years in between, starting on 1/1/XXXX
  365. if ( date1.year() == date2.year() )
  366. return Value (0);
  367. if ( (date1.month() != 1) || (date1.day() != 1) )
  368. date1.setYMD(date1.year() + 1, 1, 1);
  369. date2.setYMD(date2.year(), 1, 1);
  370. return Value (date2.year() - date1.year());
  371. }
  372. // Function: months
  373. Value func_months (valVector args, ValueCalc *calc, FuncExtra *)
  374. {
  375. TQDate date1 = calc->conv()->asDate (args[0]).asDate();
  376. TQDate date2 = calc->conv()->asDate (args[1]).asDate();
  377. if (!date1.isValid() || !date2.isValid())
  378. return Value::errorVALUE();
  379. int type = calc->conv()->asInteger (args[2]).asInteger();
  380. if (type == 0)
  381. {
  382. int months = (date2.year() - date1.year()) * 12;
  383. months += date2.month() - date1.month();
  384. if (date2.day() < date1.day())
  385. if (date2.day() != date2.daysInMonth())
  386. --months;
  387. return Value (months);
  388. }
  389. // type is now non-zero
  390. // the number of full months in between, starting on 1/XX/XXXX
  391. if (date1.month() == 12)
  392. date1.setYMD(date1.year() + 1, 1, 1);
  393. else
  394. date1.setYMD(date1.year(), date1.month() + 1, 1);
  395. date2.setYMD(date2.year(), date2.month(), 1);
  396. int months = (date2.year() - date1.year()) * 12;
  397. months += date2.month() - date1.month();
  398. return Value (months);
  399. }
  400. // Function: weeks
  401. Value func_weeks (valVector args, ValueCalc *calc, FuncExtra *)
  402. {
  403. TQDate date1 = calc->conv()->asDate (args[0]).asDate();
  404. TQDate date2 = calc->conv()->asDate (args[1]).asDate();
  405. if (!date1.isValid() || !date2.isValid())
  406. return Value::errorVALUE();
  407. int type = calc->conv()->asInteger (args[2]).asInteger();
  408. int days = date1.daysTo (date2);
  409. if (type == 0)
  410. // just the number of full weeks between
  411. return Value ((int) (days / 7));
  412. // the number of full weeks between starting on mondays
  413. int weekStartDay = calc->conv()->locale()->weekStartDay();
  414. int dow1 = date1.dayOfWeek();
  415. int dow2 = date2.dayOfWeek();
  416. days -= (7 + (weekStartDay % 7) - dow1);
  417. days -= ((dow2 - weekStartDay) % 7);
  418. return Value ((int) (days / 7));
  419. }
  420. // Function: days
  421. Value func_days (valVector args, ValueCalc *calc, FuncExtra *)
  422. {
  423. TQDate date1 = calc->conv()->asDate (args[0]).asDate();
  424. TQDate date2 = calc->conv()->asDate (args[1]).asDate();
  425. if (!date1.isValid() || !date2.isValid())
  426. return Value::errorVALUE();
  427. return Value (date1.daysTo (date2));
  428. }
  429. // Function: date
  430. Value func_date (valVector args, ValueCalc *calc, FuncExtra *)
  431. {
  432. int y = calc->conv()->asInteger (args[0]).asInteger();
  433. int m = calc->conv()->asInteger (args[1]).asInteger();
  434. int d = calc->conv()->asInteger (args[2]).asInteger();
  435. TQDate _date;
  436. if( _date.setYMD (y, m, d))
  437. return Value (_date);
  438. return Value::errorVALUE();
  439. }
  440. // Function: day
  441. Value func_dayname (valVector args, ValueCalc *calc, FuncExtra *)
  442. {
  443. int number = calc->conv()->asInteger (args[0]).asInteger();
  444. TQString weekName = calc->conv()->locale()->calendar()->weekDayName (number);
  445. if (weekName.isNull())
  446. return Value::errorVALUE();
  447. return Value (weekName);
  448. }
  449. // Function: monthname
  450. Value func_monthname (valVector args, ValueCalc *calc, FuncExtra *)
  451. {
  452. int number = calc->conv()->asInteger (args[0]).asInteger();
  453. TQString monthName = calc->conv()->locale()->calendar()->monthName (number,
  454. TQDate::currentDate().year());
  455. if (monthName.isNull())
  456. return Value::errorVALUE();
  457. return Value (monthName);
  458. }
  459. // Function: time
  460. Value func_time (valVector args, ValueCalc *calc, FuncExtra *)
  461. {
  462. int h = calc->conv()->asInteger (args[0]).asInteger();
  463. int m = calc->conv()->asInteger (args[1]).asInteger();
  464. int s = calc->conv()->asInteger (args[2]).asInteger();
  465. /* normalize the data */
  466. m += s / 60;
  467. s = s % 60;
  468. h += m / 60;
  469. m = m % 60;
  470. // we'll lose hours data that carries over into days
  471. h = h % 24;
  472. // now carry down hours/minutes for negative minutes/seconds
  473. if (s < 0) {
  474. s += 60;
  475. m -= 1;
  476. }
  477. if (m < 0) {
  478. m += 60;
  479. h -= 1;
  480. }
  481. if (h < 0)
  482. h += 24;
  483. return Value (TQTime (h, m, s));
  484. }
  485. // Function: currentDate
  486. Value func_currentDate (valVector, ValueCalc *, FuncExtra *)
  487. {
  488. return Value (TQDate::currentDate ());
  489. }
  490. // Function: currentTime
  491. Value func_currentTime (valVector, ValueCalc *, FuncExtra *)
  492. {
  493. return Value (TQTime::currentTime ());
  494. }
  495. // Function: currentDateTime
  496. Value func_currentDateTime (valVector, ValueCalc *, FuncExtra *)
  497. {
  498. return Value (TQDateTime::currentDateTime ());
  499. }
  500. // Function: dayOfYear
  501. Value func_dayOfYear (valVector args, ValueCalc *calc, FuncExtra *)
  502. {
  503. Value date = func_date (args, calc, 0);
  504. if (date.isError()) return date;
  505. return Value (date.asDate().dayOfYear());
  506. }
  507. // Function: daysInMonth
  508. Value func_daysInMonth (valVector args, ValueCalc *calc, FuncExtra *)
  509. {
  510. int y = calc->conv()->asInteger (args[0]).asInteger();
  511. int m = calc->conv()->asInteger (args[1]).asInteger();
  512. TQDate date (y, m, 1);
  513. return Value (date.daysInMonth());
  514. }
  515. // Function: isLeapYear
  516. Value func_isLeapYear (valVector args, ValueCalc *calc, FuncExtra *)
  517. {
  518. int y = calc->conv()->asInteger (args[0]).asInteger();
  519. return Value (TQDate::leapYear (y));
  520. }
  521. // Function: daysInYear
  522. Value func_daysInYear (valVector args, ValueCalc *calc, FuncExtra *)
  523. {
  524. int y = calc->conv()->asInteger (args[0]).asInteger();
  525. return Value (TQDate::leapYear (y) ? 366 : 365);
  526. }
  527. // Function: weeksInYear
  528. Value func_weeksInYear (valVector args, ValueCalc *calc, FuncExtra *)
  529. {
  530. int y = calc->conv()->asInteger (args[0]).asInteger();
  531. TQDate date (y, 12, 31); // last day of the year
  532. return Value (date.weekNumber ());
  533. }
  534. // Function: easterSunday
  535. Value func_easterSunday (valVector args, ValueCalc *calc, FuncExtra *)
  536. {
  537. int nDay, nMonth;
  538. int nYear = calc->conv()->asInteger (args[0]).asInteger();
  539. // (Tomas) the person who wrote this should be hanged :>
  540. int B,C,D,E,F,G,H,I,K,L,M,N,O;
  541. N = nYear % 19;
  542. B = int(nYear / 100);
  543. C = nYear % 100;
  544. D = int(B / 4);
  545. E = B % 4;
  546. F = int((B + 8) / 25);
  547. G = int((B - F + 1) / 3);
  548. H = (19 * N + B - D - G + 15) % 30;
  549. I = int(C / 4);
  550. K = C % 4;
  551. L = (32 + 2 * E + 2 * I - H - K) % 7;
  552. M = int((N + 11 * H + 22 * L) / 451);
  553. O = H + L - 7 * M + 114;
  554. nDay = O % 31 + 1;
  555. nMonth = int(O / 31);
  556. return Value (TQDate (nYear, nMonth, nDay));
  557. }
  558. // Function: isoWeekNum
  559. Value func_isoWeekNum (valVector args, ValueCalc *calc, FuncExtra *)
  560. {
  561. TQDate date = calc->conv()->asDate (args[0]).asDate();
  562. if (!date.isValid())
  563. return Value::errorVALUE();
  564. return Value (date.weekNumber());
  565. }