File indexing completed on 2024-05-12 16:35:29
0001 /* This file is part of the KDE project 0002 Copyright (C) 1998-2003 The KSpread Team <calligra-devel@kde.org> 0003 Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com> 0004 0005 This library is free software; you can redistribute it and/or 0006 modify it under the terms of the GNU Library General Public 0007 License as published by the Free Software Foundation; only 0008 version 2 of the License. 0009 0010 This library is distributed in the hope that it will be useful, 0011 but WITHOUT ANY WARRANTY; without even the implied warranty of 0012 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 0013 Library General Public License for more details. 0014 0015 You should have received a copy of the GNU Library General Public License 0016 along with this library; see the file COPYING.LIB. If not, write to 0017 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 0018 Boston, MA 02110-1301, USA. 0019 */ 0020 0021 // built-in date/time functions 0022 0023 #include "DateTimeModule.h" 0024 0025 #include "CalculationSettings.h" 0026 #include "Function.h" 0027 #include "FunctionModuleRegistry.h" 0028 #include "functions/helper.h" 0029 #include "ValueCalc.h" 0030 #include "ValueConverter.h" 0031 0032 #include <kcalendarsystem.h> 0033 0034 using namespace Calligra::Sheets; 0035 0036 // prototypes, sorted 0037 Value func_currentDate(valVector args, ValueCalc *calc, FuncExtra *); 0038 Value func_currentDateTime(valVector args, ValueCalc *calc, FuncExtra *); 0039 Value func_currentTime(valVector args, ValueCalc *calc, FuncExtra *); 0040 Value func_date(valVector args, ValueCalc *calc, FuncExtra *); 0041 Value func_date2unix(valVector args, ValueCalc *calc, FuncExtra *); 0042 Value func_dateDif(valVector args, ValueCalc *calc, FuncExtra *); 0043 Value func_datevalue(valVector args, ValueCalc *calc, FuncExtra *); 0044 Value func_day(valVector args, ValueCalc *calc, FuncExtra *); 0045 Value func_dayname(valVector args, ValueCalc *calc, FuncExtra *); 0046 Value func_dayOfYear(valVector args, ValueCalc *calc, FuncExtra *); 0047 Value func_days(valVector args, ValueCalc *calc, FuncExtra *); 0048 Value func_days360(valVector args, ValueCalc *calc, FuncExtra *); 0049 Value func_daysInMonth(valVector args, ValueCalc *calc, FuncExtra *); 0050 Value func_daysInYear(valVector args, ValueCalc *calc, FuncExtra *); 0051 Value func_easterSunday(valVector args, ValueCalc *calc, FuncExtra *); 0052 Value func_edate(valVector args, ValueCalc *calc, FuncExtra *); 0053 Value func_eomonth(valVector args, ValueCalc *calc, FuncExtra *); 0054 Value func_hour(valVector args, ValueCalc *calc, FuncExtra *); 0055 Value func_hours(valVector args, ValueCalc *calc, FuncExtra *); 0056 Value func_isLeapYear(valVector args, ValueCalc *calc, FuncExtra *); 0057 Value func_isoWeekNum(valVector args, ValueCalc *calc, FuncExtra *); 0058 Value func_minute(valVector args, ValueCalc *calc, FuncExtra *); 0059 Value func_minutes(valVector args, ValueCalc *calc, FuncExtra *); 0060 Value func_month(valVector args, ValueCalc *calc, FuncExtra *); 0061 Value func_monthname(valVector args, ValueCalc *calc, FuncExtra *); 0062 Value func_months(valVector args, ValueCalc *calc, FuncExtra *); 0063 Value func_networkday(valVector args, ValueCalc *calc, FuncExtra *); 0064 Value func_second(valVector args, ValueCalc *calc, FuncExtra *); 0065 Value func_seconds(valVector args, ValueCalc *calc, FuncExtra *); 0066 Value func_time(valVector args, ValueCalc *calc, FuncExtra *); 0067 Value func_timevalue(valVector args, ValueCalc *calc, FuncExtra *); 0068 Value func_today(valVector args, ValueCalc *calc, FuncExtra *); 0069 Value func_unix2date(valVector args, ValueCalc *calc, FuncExtra *); 0070 Value func_weekday(valVector args, ValueCalc *calc, FuncExtra *); 0071 Value func_weekNum(valVector args, ValueCalc *calc, FuncExtra *); 0072 Value func_weeks(valVector args, ValueCalc *calc, FuncExtra *); 0073 Value func_weeksInYear(valVector args, ValueCalc *calc, FuncExtra *); 0074 Value func_workday(valVector args, ValueCalc *calc, FuncExtra *); 0075 Value func_year(valVector args, ValueCalc *calc, FuncExtra *); 0076 Value func_yearFrac(valVector args, ValueCalc *calc, FuncExtra *); 0077 Value func_years(valVector args, ValueCalc *calc, FuncExtra *); 0078 0079 0080 CALLIGRA_SHEETS_EXPORT_FUNCTION_MODULE("kspreaddatetimemodule.json", DateTimeModule) 0081 0082 0083 DateTimeModule::DateTimeModule(QObject* parent, const QVariantList&) 0084 : FunctionModule(parent) 0085 { 0086 Function *f; 0087 0088 f = new Function("CURRENTDATE", func_currentDate); 0089 f->setParamCount(0); 0090 add(f); 0091 f = new Function("CURRENTDATETIME", func_currentDateTime); 0092 f->setParamCount(0); 0093 add(f); 0094 f = new Function("CURRENTTIME", func_currentTime); 0095 f->setParamCount(0); 0096 add(f); 0097 f = new Function("DATE", func_date); 0098 f->setParamCount(3); 0099 add(f); 0100 f = new Function("DATE2UNIX", func_date2unix); 0101 f->setParamCount(1); 0102 add(f); 0103 f = new Function("DATEDIF", func_dateDif); 0104 f->setParamCount(3); 0105 add(f); 0106 f = new Function("DATEVALUE", func_datevalue); 0107 add(f); 0108 f = new Function("DAY", func_day); 0109 add(f); 0110 f = new Function("DAYNAME", func_dayname); 0111 add(f); 0112 f = new Function("DAYOFYEAR", func_dayOfYear); 0113 f->setParamCount(3); 0114 add(f); 0115 f = new Function("DAYS", func_days); 0116 f->setParamCount(2); 0117 add(f); 0118 f = new Function("DAYS360", func_days360); 0119 f->setParamCount(2, 3); 0120 add(f); 0121 f = new Function("DAYSINMONTH", func_daysInMonth); 0122 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDAYSINMONTH"); 0123 f->setParamCount(2); 0124 add(f); 0125 f = new Function("DAYSINYEAR", func_daysInYear); 0126 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDAYSINYEAR"); 0127 add(f); 0128 f = new Function("EASTERSUNDAY", func_easterSunday); 0129 add(f); 0130 f = new Function("EDATE", func_edate); 0131 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETEDATE"); 0132 f->setParamCount(2); 0133 add(f); 0134 f = new Function("EOMONTH", func_eomonth); 0135 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETEOMONTH"); 0136 f->setParamCount(2); 0137 add(f); 0138 f = new Function("HOUR", func_hour); 0139 f->setParamCount(0, 1); 0140 add(f); 0141 f = new Function("HOURS", func_hour); // same as HOUR 0142 f->setParamCount(0, 1); 0143 add(f); 0144 f = new Function("ISLEAPYEAR", func_isLeapYear); 0145 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETISLEAPYEAR"); 0146 add(f); 0147 f = new Function("ISOWEEKNUM", func_isoWeekNum); 0148 f->setParamCount(1, 2); 0149 add(f); 0150 f = new Function("MINUTE", func_minute); 0151 f->setParamCount(0, 1); 0152 add(f); 0153 f = new Function("MINUTES", func_minute); // same as MINUTE 0154 f->setParamCount(0, 1); 0155 add(f); 0156 f = new Function("MONTH", func_month); 0157 add(f); 0158 f = new Function("MONTHNAME", func_monthname); 0159 add(f); 0160 f = new Function("MONTHS", func_months); 0161 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDIFFMONTHS"); 0162 f->setParamCount(3); 0163 add(f); 0164 f = new Function("NETWORKDAY", func_networkday); 0165 //f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETNETWORKDAYS"); 0166 f->setParamCount(2, 3); 0167 f->setAcceptArray(); 0168 add(f); 0169 f = new Function("NOW", func_currentDateTime); 0170 f->setParamCount(0); 0171 add(f); 0172 f = new Function("SECOND", func_second); 0173 f->setParamCount(0, 1); 0174 add(f); 0175 f = new Function("SECONDS", func_second); // same as SECOND 0176 f->setParamCount(0, 1); 0177 add(f); 0178 f = new Function("TIME", func_time); 0179 f->setParamCount(3); 0180 add(f); 0181 f = new Function("TIMEVALUE", func_timevalue); 0182 add(f); 0183 f = new Function("TODAY", func_currentDate); 0184 f->setParamCount(0); 0185 add(f); 0186 f = new Function("UNIX2DATE", func_unix2date); 0187 f->setParamCount(1); 0188 add(f); 0189 f = new Function("WEEKDAY", func_weekday); 0190 f->setParamCount(1, 2); 0191 add(f); 0192 f = new Function("WEEKNUM", func_weekNum); 0193 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETWEEKNUM"); 0194 f->setParamCount(1, 2); 0195 add(f); 0196 f = new Function("WEEKS", func_weeks); 0197 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDIFFWEEKS"); 0198 f->setParamCount(3); 0199 add(f); 0200 f = new Function("WEEKSINYEAR", func_weeksInYear); 0201 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETWEEKSINYEAR"); 0202 add(f); 0203 f = new Function("WORKDAY", func_workday); 0204 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETWORKDAY"); 0205 f->setParamCount(2, 3); 0206 f->setAcceptArray(); 0207 add(f); 0208 f = new Function("YEAR", func_year); 0209 add(f); 0210 f = new Function("YEARFRAC", func_yearFrac); 0211 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETYEARFRAC"); 0212 f->setParamCount(2, 3); 0213 add(f); 0214 f = new Function("YEARS", func_years); 0215 f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDIFFYEARS"); 0216 f->setParamCount(3); 0217 add(f); 0218 } 0219 0220 QString DateTimeModule::descriptionFileName() const 0221 { 0222 return QString("datetime.xml"); 0223 } 0224 0225 0226 // Function: EDATE 0227 Value func_edate(valVector args, ValueCalc *calc, FuncExtra *) 0228 { 0229 QDate date = calc->conv()->asDate(args[0]).asDate(calc->settings()); 0230 int months = calc->conv()->asInteger(args[1]).asInteger(); 0231 0232 date = calc->settings()->locale()->calendar()->addMonths(date, months); 0233 0234 if (!date.isValid()) 0235 return Value::errorVALUE(); 0236 0237 return Value(date, calc->settings()); 0238 } 0239 0240 // Function: EOMONTH 0241 Value func_eomonth(valVector args, ValueCalc *calc, FuncExtra *) 0242 { 0243 // add months to date using EDATE 0244 Value modDate = func_edate(args, calc, 0); 0245 if (modDate.isError()) return modDate; 0246 0247 // modDate is currently in Date format 0248 QDate date = modDate.asDate(calc->settings()); 0249 date.setDate(date.year(), date.month(), date.daysInMonth()); 0250 0251 return Value(date, calc->settings()); 0252 } 0253 0254 // internal helper function 0255 static int func_days360_helper(const QDate& _date1, const QDate& _date2, bool european) 0256 { 0257 int day1, day2; 0258 int month1, month2; 0259 int year1, year2; 0260 QDate date1(_date1); 0261 QDate date2(_date2); 0262 0263 if (date1.daysTo(date2) < 0) { 0264 QDate tmp(date1); 0265 date1 = date2; 0266 date2 = tmp; 0267 } 0268 0269 day1 = date1.day(); 0270 day2 = date2.day(); 0271 month1 = date1.month(); 0272 month2 = date2.month(); 0273 year1 = date1.year(); 0274 year2 = date2.year(); 0275 0276 if (european) { 0277 if (day1 == 31) 0278 day1 = 30; 0279 if (day2 == 31) 0280 day2 = 30; 0281 } else { 0282 // thanks to the Gnumeric developers for this... 0283 if (month1 == 2 && month2 == 2 0284 && date1.daysInMonth() == day1 0285 && date2.daysInMonth() == day2) 0286 day2 = 30; 0287 0288 if (month1 == 2 && date1.daysInMonth() == day1) 0289 day1 = 30; 0290 0291 if (day2 == 31 && day1 >= 30) 0292 day2 = 30; 0293 0294 if (day1 == 31) 0295 day1 = 30; 0296 } 0297 0298 return ((year2 - year1) * 12 + (month2 - month1)) * 30 0299 + (day2 - day1); 0300 } 0301 0302 // Function: DAYS360 0303 // algorithm adapted from gnumeric 0304 Value func_days360(valVector args, ValueCalc *calc, FuncExtra *) 0305 { 0306 QDate date1 = calc->conv()->asDate(args[0]).asDate(calc->settings()); 0307 QDate date2 = calc->conv()->asDate(args[1]).asDate(calc->settings()); 0308 bool european = false; 0309 if (args.count() == 3) 0310 european = calc->conv()->asBoolean(args[2]).asBoolean(); 0311 0312 return Value(func_days360_helper(date1, date2, european)); 0313 } 0314 0315 // Function: YEAR 0316 Value func_year(valVector args, ValueCalc *calc, FuncExtra *) 0317 { 0318 Value v = calc->conv()->asDate(args[0]); 0319 if (v.isError()) return v; 0320 QDate date = v.asDate(calc->settings()); 0321 return Value(date.year()); 0322 } 0323 0324 // Function: MONTH 0325 Value func_month(valVector args, ValueCalc *calc, FuncExtra *) 0326 { 0327 Value v = calc->conv()->asDate(args[0]); 0328 if (v.isError()) return v; 0329 QDate date = v.asDate(calc->settings()); 0330 return Value(date.month()); 0331 } 0332 0333 // Function: DAY 0334 Value func_day(valVector args, ValueCalc *calc, FuncExtra *) 0335 { 0336 Value v = calc->conv()->asDate(args[0]); 0337 if (v.isError()) return v; 0338 QDate date = v.asDate(calc->settings()); 0339 return Value(date.day()); 0340 } 0341 0342 // Function: HOUR 0343 Value func_hour(valVector args, ValueCalc *calc, FuncExtra *) 0344 { 0345 QTime time; 0346 if (args.count() == 1) { 0347 Value v = calc->conv()->asTime(args[0]); 0348 if (v.isError()) return v; 0349 time = v.asTime(); 0350 } else 0351 time = QTime::currentTime(); 0352 return Value(time.hour()); 0353 } 0354 0355 // Function: MINUTE 0356 Value func_minute(valVector args, ValueCalc *calc, FuncExtra *) 0357 { 0358 QTime time; 0359 if (args.count() == 1) { 0360 Value v = calc->conv()->asTime(args[0]); 0361 if (v.isError()) return v; 0362 time = v.asTime(); 0363 } else 0364 time = QTime::currentTime(); 0365 return Value(time.minute()); 0366 } 0367 0368 // Function: SECOND 0369 Value func_second(valVector args, ValueCalc *calc, FuncExtra *) 0370 { 0371 QTime time; 0372 if (args.count() == 1) { 0373 Value v = calc->conv()->asTime(args[0]); 0374 if (v.isError()) return v; 0375 time = v.asTime(); 0376 } else 0377 time = QTime::currentTime(); 0378 return Value(time.second() + qRound(time.msec() * 0.001)); 0379 } 0380 0381 // Function: WEEKDAY 0382 Value func_weekday(valVector args, ValueCalc *calc, FuncExtra *) 0383 { 0384 Value v(calc->conv()->asDate(args[0])); 0385 if (v.isError()) return v; 0386 QDate date = v.asDate(calc->settings()); 0387 int method = 1; 0388 if (args.count() == 2) 0389 method = calc->conv()->asInteger(args[1]).asInteger(); 0390 0391 if (method < 1 || method > 3) 0392 return Value::errorVALUE(); 0393 0394 int result = date.dayOfWeek(); 0395 0396 if (method == 3) 0397 --result; 0398 else if (method == 1) { 0399 ++result; 0400 if (result > 7) result = result % 7; 0401 } 0402 0403 return Value(result); 0404 } 0405 0406 // Function: DATEVALUE 0407 // same result would be obtained by applying number format on a date value 0408 Value func_datevalue(valVector args, ValueCalc *calc, FuncExtra *) 0409 { 0410 if (args[0].isString()) { 0411 Value v = calc->conv()->asDate(args[0]); 0412 if (! v.isError()) 0413 return calc->conv()->asFloat(v); 0414 } 0415 return Value::errorVALUE(); 0416 } 0417 0418 // Function: timevalue 0419 // same result would be obtained by applying number format on a time value 0420 Value func_timevalue(valVector args, ValueCalc *calc, FuncExtra *) 0421 { 0422 if (args[0].isString()) { 0423 Value v = calc->conv()->asTime(args[0]); 0424 if (! v.isError()) 0425 return calc->conv()->asFloat(v); 0426 } 0427 return Value::errorVALUE(); 0428 } 0429 0430 // Function: YEARS 0431 Value func_years(valVector args, ValueCalc *calc, FuncExtra *) 0432 { 0433 QDate date1 = calc->conv()->asDate(args[0]).asDate(calc->settings()); 0434 QDate date2 = calc->conv()->asDate(args[1]).asDate(calc->settings()); 0435 if (!date1.isValid() || !date2.isValid()) 0436 return Value::errorVALUE(); 0437 0438 int type = calc->conv()->asInteger(args[2]).asInteger(); 0439 if (type == 0) { 0440 // max. possible years between both dates 0441 int years = date2.year() - date1.year(); 0442 0443 if (date2.month() < date1.month()) 0444 --years; 0445 else if ((date2.month() == date1.month()) && (date2.day() < date1.day())) 0446 --years; 0447 0448 return Value(years); 0449 } 0450 0451 // type is non-zero now 0452 // the number of full years in between, starting on 1/1/XXXX 0453 if (date1.year() == date2.year()) 0454 return Value(0); 0455 0456 if ((date1.month() != 1) || (date1.day() != 1)) 0457 date1.setDate(date1.year() + 1, 1, 1); 0458 date2.setDate(date2.year(), 1, 1); 0459 0460 return Value(date2.year() - date1.year()); 0461 } 0462 0463 // Function: MONTHS 0464 Value func_months(valVector args, ValueCalc *calc, FuncExtra *) 0465 { 0466 QDate date1 = calc->conv()->asDate(args[0]).asDate(calc->settings()); 0467 QDate date2 = calc->conv()->asDate(args[1]).asDate(calc->settings()); 0468 if (!date1.isValid() || !date2.isValid()) 0469 return Value::errorVALUE(); 0470 0471 int type = calc->conv()->asInteger(args[2]).asInteger(); 0472 if (type == 0) { 0473 int months = (date2.year() - date1.year()) * 12; 0474 months += date2.month() - date1.month(); 0475 0476 if (date2.day() < date1.day()) 0477 if (date2.day() != date2.daysInMonth()) 0478 --months; 0479 0480 return Value(months); 0481 } 0482 0483 // type is now non-zero 0484 // the number of full months in between, starting on 1/XX/XXXX 0485 if (date1.month() == 12) 0486 date1.setDate(date1.year() + 1, 1, 1); 0487 else 0488 date1.setDate(date1.year(), date1.month() + 1, 1); 0489 date2.setDate(date2.year(), date2.month(), 1); 0490 0491 int months = (date2.year() - date1.year()) * 12; 0492 months += date2.month() - date1.month(); 0493 0494 return Value(months); 0495 } 0496 0497 // Function: WEEKS 0498 Value func_weeks(valVector args, ValueCalc *calc, FuncExtra *) 0499 { 0500 QDate date1 = calc->conv()->asDate(args[0]).asDate(calc->settings()); 0501 QDate date2 = calc->conv()->asDate(args[1]).asDate(calc->settings()); 0502 if (!date1.isValid() || !date2.isValid()) 0503 return Value::errorVALUE(); 0504 0505 int type = calc->conv()->asInteger(args[2]).asInteger(); 0506 int days = date1.daysTo(date2); 0507 if (type == 0) 0508 // just the number of full weeks between 0509 return Value((int)(days / 7)); 0510 0511 // the number of full weeks between starting on mondays 0512 int weekStartDay = calc->settings()->locale()->weekStartDay(); 0513 0514 int dow1 = date1.dayOfWeek(); 0515 int dow2 = date2.dayOfWeek(); 0516 0517 days -= (7 + (weekStartDay % 7) - dow1); 0518 days -= ((dow2 - weekStartDay) % 7); 0519 0520 return Value((int)(days / 7)); 0521 } 0522 0523 // Function: DAYS 0524 Value func_days(valVector args, ValueCalc *calc, FuncExtra *) 0525 { 0526 QDate date1 = calc->conv()->asDate(args[0]).asDate(calc->settings()); 0527 QDate date2 = calc->conv()->asDate(args[1]).asDate(calc->settings()); 0528 if (!date1.isValid() || !date2.isValid()) 0529 return Value::errorVALUE(); 0530 0531 return Value(date2.daysTo(date1)); 0532 } 0533 0534 // Function: DATE 0535 Value func_date(valVector args, ValueCalc *calc, FuncExtra *) 0536 { 0537 int y = calc->conv()->asInteger(args[0]).asInteger(); 0538 int m = calc->conv()->asInteger(args[1]).asInteger(); 0539 int d = calc->conv()->asInteger(args[2]).asInteger(); 0540 0541 if (m == 0 || d == 0) 0542 return Value::errorVALUE(); // month or day zero is not allowed 0543 else { 0544 QDate tmpDate(y, 1, 1); 0545 tmpDate = tmpDate.addMonths(m - 1); 0546 tmpDate = tmpDate.addDays(d - 1); 0547 0548 //debugSheetsFormula <<"func_date:: date =" << tmpDate; 0549 return Value(tmpDate, calc->settings()); 0550 } 0551 } 0552 0553 // Function: DAY 0554 Value func_dayname(valVector args, ValueCalc *calc, FuncExtra *) 0555 { 0556 int number = calc->conv()->asInteger(args[0]).asInteger(); 0557 0558 QString weekName = calc->settings()->locale()->calendar()->weekDayName(number); 0559 if (weekName.isNull()) 0560 return Value::errorVALUE(); 0561 return Value(weekName); 0562 } 0563 0564 // Function: MONTHNAME 0565 Value func_monthname(valVector args, ValueCalc *calc, FuncExtra *) 0566 { 0567 int number = calc->conv()->asInteger(args[0]).asInteger(); 0568 0569 QString monthName = calc->settings()->locale()->calendar()->monthName(number, 0570 QDate::currentDate().year()); 0571 if (monthName.isNull()) 0572 return Value::errorVALUE(); 0573 return Value(monthName); 0574 } 0575 0576 // Function: TIME 0577 Value func_time(valVector args, ValueCalc *calc, FuncExtra *) 0578 { 0579 int h = calc->conv()->asInteger(args[0]).asInteger(); 0580 int m = calc->conv()->asInteger(args[1]).asInteger(); 0581 int s = calc->conv()->asInteger(args[2]).asInteger(); 0582 0583 QTime res(0, 0); 0584 res = res.addSecs(60 * 60 * h); 0585 res = res.addSecs(60 * m); 0586 res = res.addSecs(s); 0587 0588 return Value(res); 0589 } 0590 0591 // Function: CURRENTDATE 0592 Value func_currentDate(valVector, ValueCalc * calc, FuncExtra *) 0593 { 0594 return Value(QDate::currentDate(), calc->settings()); 0595 } 0596 0597 // Function: CURRENTTIME 0598 Value func_currentTime(valVector, ValueCalc * calc, FuncExtra *) 0599 { 0600 return Value(QTime::currentTime()); 0601 } 0602 0603 // Function: CURRENTDATETIME 0604 Value func_currentDateTime(valVector, ValueCalc * calc, FuncExtra *) 0605 { 0606 return Value(QDateTime::currentDateTime(), calc->settings()); 0607 } 0608 0609 // Function: DAYOFYEAR 0610 Value func_dayOfYear(valVector args, ValueCalc *calc, FuncExtra *) 0611 { 0612 Value date = func_date(args, calc, 0); 0613 if (date.isError()) return date; 0614 return Value(date.asDate(calc->settings()).dayOfYear()); 0615 } 0616 0617 // Function: DAYSINMONTH 0618 Value func_daysInMonth(valVector args, ValueCalc *calc, FuncExtra *) 0619 { 0620 int y = calc->conv()->asInteger(args[0]).asInteger(); 0621 int m = calc->conv()->asInteger(args[1]).asInteger(); 0622 QDate date(y, m, 1); 0623 return Value(date.daysInMonth()); 0624 } 0625 0626 // Function: ISLEAPYEAR 0627 Value func_isLeapYear(valVector args, ValueCalc *calc, FuncExtra *) 0628 { 0629 int y = calc->conv()->asInteger(args[0]).asInteger(); 0630 return Value(QDate::isLeapYear(y)); 0631 } 0632 0633 // Function: DAYSINYEAR 0634 Value func_daysInYear(valVector args, ValueCalc *calc, FuncExtra *) 0635 { 0636 int y = calc->conv()->asInteger(args[0]).asInteger(); 0637 return Value(QDate::isLeapYear(y) ? 366 : 365); 0638 } 0639 0640 // Function: WEEKSINYEAR 0641 Value func_weeksInYear(valVector args, ValueCalc *calc, FuncExtra *) 0642 { 0643 int y = calc->conv()->asInteger(args[0]).asInteger(); 0644 QDate date(y, 12, 31); // last day of the year 0645 int yearNumber; 0646 int weekNumber = date.weekNumber(&yearNumber); 0647 // day assigned to first week of next year? 0648 if (yearNumber != y) { 0649 // take weekday the week before then 0650 date = date.addDays(-7); 0651 weekNumber = date.weekNumber(); 0652 } 0653 return Value(weekNumber); 0654 } 0655 0656 // Function: EASTERSUNDAY 0657 Value func_easterSunday(valVector args, ValueCalc *calc, FuncExtra *) 0658 { 0659 int nDay, nMonth; 0660 int nYear = calc->conv()->asInteger(args[0]).asInteger(); 0661 0662 // (Tomas) the person who wrote this should be hanged :> 0663 int B, C, D, E, F, G, H, I, K, L, M, N, O; 0664 N = nYear % 19; 0665 B = int(nYear / 100); 0666 C = nYear % 100; 0667 D = int(B / 4); 0668 E = B % 4; 0669 F = int((B + 8) / 25); 0670 G = int((B - F + 1) / 3); 0671 H = (19 * N + B - D - G + 15) % 30; 0672 I = int(C / 4); 0673 K = C % 4; 0674 L = (32 + 2 * E + 2 * I - H - K) % 7; 0675 M = int((N + 11 * H + 22 * L) / 451); 0676 O = H + L - 7 * M + 114; 0677 nDay = O % 31 + 1; 0678 nMonth = int(O / 31); 0679 0680 return Value(QDate(nYear, nMonth, nDay), calc->settings()); 0681 } 0682 0683 // Function: ISOWEEKNUM 0684 // 0685 // method startday name of day 0686 // default: 1 1 sunday 0687 // 2 0 monday 0688 // 0689 Value func_isoWeekNum(valVector args, ValueCalc *calc, FuncExtra *) 0690 { 0691 QDate date = calc->conv()->asDate(args[0]).asDate(calc->settings()); 0692 if (!date.isValid()) 0693 return Value::errorVALUE(); 0694 0695 int method = 2; // default method = 2 0696 if (args.count() > 1) 0697 method = calc->conv()->asInteger(args[1]).asInteger(); 0698 0699 if (method < 1 || method > 2) 0700 return Value::errorVALUE(); 0701 0702 int startday = 1; 0703 if (method != 1) 0704 startday = 0; 0705 0706 int weeknum; 0707 int day; // current date 0708 int day4; // 4th of jan. 0709 int day0; // offset to 4th of jan. 0710 0711 // date to find 0712 day = date.toJulianDay(); 0713 0714 // 4th of jan. of current year 0715 day4 = QDate(date.year(), 1, 4).toJulianDay(); 0716 0717 // difference in days to the 4th of jan including correction of startday 0718 day0 = QDate::fromJulianDay(day4 - 1 + startday).dayOfWeek(); 0719 0720 // do we need to count from last year? 0721 if (day < day4 - day0) { // recalculate day4 and day0 0722 day4 = QDate(date.year() - 1, 1, 4).toJulianDay(); // 4th of jan. last year 0723 day0 = QDate::fromJulianDay(day4 - 1 + startday).dayOfWeek(); 0724 } 0725 0726 // calc weeeknum 0727 weeknum = (day - (day4 - day0)) / 7 + 1; 0728 0729 // if weeknum is greater 51, we have to do some extra checks 0730 if (weeknum >= 52) { 0731 day4 = QDate(date.year() + 1, 1, 4).toJulianDay(); // 4th of jan. next year 0732 day0 = QDate::fromJulianDay(day4 - 1 + startday).dayOfWeek(); 0733 0734 if (day >= day4 - day0) { // recalculate weeknum 0735 weeknum = (day - (day4 - day0)) / 7 + 1; 0736 } 0737 } 0738 0739 return Value(weeknum); 0740 } 0741 0742 // Function: WEEKNUM 0743 // 0744 // method startday name of day 0745 // default: 1 0 sunday 0746 // 2 -1 monday 0747 // 0748 // weeknum = (startday + 7 + dayOfWeek of New Year + difference in days) / 7 0749 // 0750 Value func_weekNum(valVector args, ValueCalc *calc, FuncExtra *) 0751 { 0752 Value v(calc->conv()->asDate(args[0])); 0753 if (v.isError()) return v; 0754 QDate date = v.asDate(calc->settings()); 0755 0756 if (!date.isValid()) 0757 return Value::errorVALUE(); 0758 0759 int method = 1; 0760 if (args.count() > 1) 0761 method = calc->conv()->asInteger(args[1]).asInteger(); 0762 0763 if (method < 1 || method > 2) 0764 return Value::errorVALUE(); 0765 0766 QDate date1(date.year(), 1, 1); 0767 int days = date1.daysTo(date); 0768 0769 int startday = 0; 0770 if (method == 2) 0771 startday = -1; 0772 0773 int res = (int)((startday + 7 + date1.dayOfWeek() + days) / 7); 0774 0775 if (date1.dayOfWeek() == 7 && method == 1) 0776 res--; 0777 0778 //debugSheetsFormula <<"weeknum = [startday(" << startday <<") + base(7) + New Year(" << date1.dayOfWeek() <<") + days(" << days <<")] / 7 =" << res; 0779 0780 return Value(res); 0781 } 0782 0783 // Function: DATEDIF 0784 // 0785 // interval difference | type description 0786 // --------------------|---------------------------------- 0787 // default: m | months 0788 // d | days 0789 // y | complete years 0790 // ym | months excluding years 0791 // yd | days excluding years 0792 // md | days excluding months and years 0793 // 0794 Value func_dateDif(valVector args, ValueCalc *calc, FuncExtra *) 0795 { 0796 Value v1(calc->conv()->asDate(args[0])); 0797 if (v1.isError()) return v1; 0798 QDate date1 = v1.asDate(calc->settings()); 0799 0800 if (!date1.isValid()) 0801 return Value::errorVALUE(); 0802 0803 Value v2(calc->conv()->asDate(args[1])); 0804 if (v2.isError()) return v2; 0805 QDate date2 = v2.asDate(calc->settings()); 0806 0807 if (!date2.isValid()) 0808 return Value::errorVALUE(); 0809 0810 // check if interval is valid 0811 QString interval = calc->conv()->asString(args[2]).asString(); 0812 if (!(interval == "m" || interval == "d" || interval == "y" || interval == "ym" || interval == "yd" || interval == "md")) 0813 return Value::errorVALUE(); 0814 0815 // local vars 0816 int y, m, d; 0817 int sign = 1; // default 0818 int res = 0; 0819 0820 QDate Temp1, Temp2; 0821 0822 //QDate date0(1899,12,30); // referenceDate 0823 QDate date0 = calc->settings()->referenceDate(); 0824 0825 if (date2 < date1) { 0826 // exchange values and set sign 0827 Temp1 = date1; 0828 date1 = date2; 0829 date2 = Temp1; 0830 sign = -1; 0831 } 0832 0833 // 0834 // calculate 0835 // 0836 0837 // Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1)) 0838 Temp1.setDate(date2.year(), date1.month(), date1.day()); 0839 0840 // Y = Year(Date2) - Year(Date1) + (Temp1 > Date2) 0841 y = date2.year() - date1.year() + (date0.daysTo(Temp1) > date0.daysTo(date2) ? -1 : 0); 0842 0843 // M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2)) 0844 m = date2.month() - date1.month() - (12 * (Temp1 > date2 ? -1 : 0)); 0845 0846 // D = Day(Date2) - Day(Date1) 0847 d = date2.day() - date1.day(); 0848 0849 if (d < 0) { 0850 // M = M - 1 0851 m--; 0852 // D = Day(DateSerial(Year(date2), Month(date2), 0)) + D 0853 Temp2.setDate(date2.year(), date2.month() - 1, 1); 0854 d = Temp2.daysInMonth() + d; 0855 } 0856 0857 // 0858 // output 0859 // 0860 0861 if (interval == "y") { 0862 // year 0863 res = y * sign; 0864 } else if (interval == "m") { 0865 // month 0866 res = (12 * y + m) * sign; 0867 } else if (interval == "d") { 0868 // days 0869 int days = date0.daysTo(date2) - date0.daysTo(date1); 0870 res = days * sign; 0871 } else if (interval == "ym") { 0872 // month excl. years 0873 res = m * sign; 0874 } else if (interval == "yd") { 0875 // days excl. years 0876 QDate Temp3(date2.year(), date1.month(), date1.day()); 0877 int days = date0.daysTo(date2) - date0.daysTo(Temp3); 0878 0879 res = days * sign; 0880 } else if (interval == "md") { 0881 // days excl. month and years 0882 res = d * sign; 0883 } 0884 return Value(res); 0885 } 0886 0887 // Function: YEARFRAC 0888 // 0889 // | basis | description day-count 0890 // -----------|--------|-------------------------------------------------------- 0891 // default: | 0 | US (NASD) system. 30 days/month, 360 days/year (30/360) 0892 // | 1 | Actual/actual (Euro), also known as AFB 0893 // | 2 | Actual/360 0894 // | 3 | Actual/365 0895 // | 4 | European 30/360 0896 // 0897 Value func_yearFrac(valVector args, ValueCalc *calc, FuncExtra *) 0898 { 0899 Value v1(calc->conv()->asDate(args[0])); 0900 if (v1.isError()) return v1; 0901 QDate date1 = v1.asDate(calc->settings()); 0902 0903 if (!date1.isValid()) 0904 return Value::errorVALUE(); 0905 0906 Value v2(calc->conv()->asDate(args[1])); 0907 if (v2.isError()) return v2; 0908 QDate date2 = v2.asDate(calc->settings()); 0909 0910 if (!date2.isValid()) 0911 return Value::errorVALUE(); 0912 0913 // check if basis is valid 0914 int basis = 0; 0915 if (args.count() > 2) 0916 basis = calc->conv()->asInteger(args[2]).asInteger(); 0917 if (basis < 0 || basis > 4) 0918 return Value::errorVALUE(); 0919 0920 QDate date0 = calc->settings()->referenceDate(); // referenceDate 0921 0922 return Value(yearFrac(date0, date1, date2, basis)); 0923 } 0924 0925 // Function: WORKDAY 0926 // 0927 // - negative days count backwards 0928 // - if holidays is not an array it is only added to days (neg. are not allowed) 0929 // 0930 Value func_workday(valVector args, ValueCalc *calc, FuncExtra *e) 0931 { 0932 Value v(calc->conv()->asDate(args[0])); 0933 0934 if (v.isError()) return v; 0935 QDate startdate = v.asDate(calc->settings()); 0936 0937 if (!startdate.isValid()) 0938 return Value::errorVALUE(); 0939 0940 // 0941 // vars 0942 // 0943 int days = calc->conv()->asInteger(args[1]).asInteger(); 0944 0945 QDate date0 = calc->settings()->referenceDate(); // referenceDate 0946 QDate enddate = startdate; // enddate 0947 valVector holidays; // stores holidays 0948 int sign = 1; // sign 1 = forward, -1 = backward 0949 0950 if (days < 0) { 0951 // change sign and set count to ccw 0952 days = days * -1; 0953 sign = -1; 0954 } 0955 0956 // 0957 // check for holidays 0958 // 0959 if (args.count() > 2) { 0960 if (args[2].type() == Value::Array) { // parameter is array 0961 unsigned int row1, col1, rows, cols; 0962 0963 row1 = e->ranges[2].row1; 0964 col1 = e->ranges[2].col1; 0965 rows = e->ranges[2].row2 - row1 + 1; 0966 cols = e->ranges[2].col2 - col1 + 1; 0967 0968 Value holiargs = args[2]; 0969 0970 for (unsigned r = 0; r < rows; ++r) { 0971 for (unsigned c = 0; c < cols; ++c) { 0972 // only append if element is a valid date 0973 if (!holiargs.element(c + col1, r + row1).isEmpty()) { 0974 Value v(calc->conv()->asDate(holiargs.element(c + col1, r + row1))); 0975 if (v.isError()) 0976 return Value::errorVALUE(); 0977 0978 if (v.asDate(calc->settings()).isValid()) 0979 holidays.append(v); 0980 } 0981 } // cols 0982 } // rows 0983 } else { 0984 // no array parameter 0985 if (args[2].isString()) { 0986 // isString 0987 Value v(calc->conv()->asDate(args[2])); 0988 if (v.isError()) 0989 return Value::errorVALUE(); 0990 0991 if (v.asDate(calc->settings()).isValid()) 0992 holidays.append(v); 0993 } else { 0994 // isNumber 0995 int hdays = calc->conv()->asInteger(args[2]).asInteger(); 0996 0997 if (hdays < 0) 0998 return Value::errorVALUE(); 0999 days = days + hdays; 1000 } 1001 } 1002 } 1003 1004 // 1005 // count days 1006 // 1007 while (days) { 1008 // exclude weekends and holidays 1009 do { 1010 enddate = enddate.addDays(1 * sign); 1011 } while (enddate.dayOfWeek() > 5 || holidays.contains(Value(date0.daysTo(enddate)))); 1012 1013 days--; 1014 } 1015 1016 return Value(enddate, calc->settings()); 1017 } 1018 1019 // Function: NETWORKDAY 1020 // 1021 // - if holidays is not an array it is only added to days (neg. are not allowed) 1022 // 1023 Value func_networkday(valVector args, ValueCalc *calc, FuncExtra *e) 1024 { 1025 Value v1(calc->conv()->asDate(args[0])); 1026 1027 if (v1.isError()) return v1; 1028 QDate startdate = v1.asDate(calc->settings()); 1029 1030 Value v2(calc->conv()->asDate(args[1])); 1031 1032 if (v2.isError()) return v2; 1033 QDate enddate = v2.asDate(calc->settings()); 1034 1035 if (!startdate.isValid() || !enddate.isValid()) 1036 return Value::errorVALUE(); 1037 1038 int days = 0; // workdays 1039 QDate date0 = calc->settings()->referenceDate(); // referenceDate 1040 valVector holidays; // stores holidays 1041 int sign = 1; // sign 1 = forward, -1 = backward 1042 1043 if (enddate < startdate) { 1044 // change sign and set count to ccw 1045 sign = -1; 1046 } 1047 1048 // 1049 // check for holidays 1050 // 1051 if (args.count() > 2) { 1052 if (args[2].type() == Value::Array) { 1053 // parameter is array 1054 unsigned int row1, col1, rows, cols; 1055 1056 row1 = e->ranges[2].row1; 1057 col1 = e->ranges[2].col1; 1058 rows = e->ranges[2].row2 - row1 + 1; 1059 cols = e->ranges[2].col2 - col1 + 1; 1060 1061 Value holiargs = args[2]; 1062 1063 for (unsigned r = 0; r < rows; ++r) { 1064 for (unsigned c = 0; c < cols; ++c) { 1065 // only append if element is a valid date 1066 if (!holiargs.element(c + col1, r + row1).isEmpty()) { 1067 Value v(calc->conv()->asDate(holiargs.element(c + col1, r + row1))); 1068 if (v.isError()) 1069 return Value::errorVALUE(); 1070 1071 if (v.asDate(calc->settings()).isValid()) 1072 holidays.append(v); 1073 } 1074 } // cols 1075 } // rows 1076 } else { 1077 // no array parameter 1078 if (args[2].isString()) { 1079 Value v(calc->conv()->asDate(args[2])); 1080 if (v.isError()) 1081 return Value::errorVALUE(); 1082 1083 if (v.asDate(calc->settings()).isValid()) 1084 holidays.append(v); 1085 1086 } else { 1087 // isNumber 1088 int hdays = calc->conv()->asInteger(args[2]).asInteger(); 1089 1090 if (hdays < 0) 1091 return Value::errorVALUE(); 1092 days = days - hdays; 1093 } 1094 } 1095 } 1096 1097 // 1098 // count days 1099 // 1100 while (startdate != enddate) { 1101 if (startdate.dayOfWeek() > 5 || holidays.contains(Value(date0.daysTo(startdate)))) { 1102 startdate = startdate.addDays(1 * sign); 1103 continue; 1104 } 1105 1106 startdate = startdate.addDays(1 * sign); 1107 days++; 1108 } 1109 return Value(days); 1110 } 1111 1112 // Function: DATE2UNIX 1113 // 1114 // Gnumeric docs says 01/01/2000 = 946656000 1115 // TODO: 1116 // - create FormatType mm/dd/yyyy hh:mm:ss 1117 // - add method tryParseDateTime 1118 Value func_unix2date(valVector args, ValueCalc *calc, FuncExtra *) 1119 { 1120 const Value v(calc->conv()->asInteger(args[0])); 1121 if (v.isError()) 1122 return v; 1123 1124 QDateTime datetime; 1125 datetime.setTimeSpec(Qt::UTC); 1126 datetime.setTime_t(v.asInteger()); 1127 1128 return Value(datetime, calc->settings()); 1129 } 1130 1131 // Function: UNIX2DATE 1132 Value func_date2unix(valVector args, ValueCalc *calc, FuncExtra *) 1133 { 1134 const Value v(calc->conv()->asDateTime(args[0])); 1135 if (v.isError()) 1136 return v; 1137 1138 const QDateTime datetime(v.asDateTime(calc->settings())); 1139 1140 return Value(static_cast<int>(datetime.toTime_t())); 1141 } 1142 1143 #include "datetime.moc"