001 /*
002 // This software is subject to the terms of the Common Public License
003 // Agreement, available at the following URL:
004 // http://www.opensource.org/licenses/cpl.html.
005 // Copyright (C) 2007-2008 Julian Hyde
006 // All Rights Reserved.
007 // You must accept the terms of that agreement to use this software.
008 */
009 package mondrian.olap.fun.vba;
010
011 import mondrian.olap.Util;
012 import mondrian.olap.InvalidArgumentException;
013 import static mondrian.olap.fun.JavaFunDef.*;
014
015 import java.util.*;
016 import java.util.regex.Matcher;
017 import java.util.regex.Pattern;
018 import java.text.*;
019
020 /**
021 * Implementations of functions in the Visual Basic for Applications (VBA)
022 * specification.
023 *
024 * @author jhyde
025 * @version $Id: //open/mondrian/src/main/mondrian/olap/fun/vba/Vba.java#8 $
026 * @since Dec 31, 2007
027 */
028 public class Vba {
029 private static final long MILLIS_IN_A_DAY = 24 * 60 * 60 * 1000;
030
031 private static final DateFormatSymbols DATE_FORMAT_SYMBOLS
032 = new DateFormatSymbols(Locale.getDefault());
033
034 // Conversion
035
036 @FunctionName("CBool")
037 @Signature("CBool(expression)")
038 @Description("Returns an expression that has been converted to a Variant of subtype Boolean.")
039 public static boolean cBool(Object expression) {
040 if (expression instanceof Boolean) {
041 return (Boolean) expression;
042 } else {
043 int i = cInt(expression);
044 return i != 0;
045 }
046 }
047
048 // Conversion functions
049
050 @FunctionName("CByte")
051 @Signature("CByte(expression)")
052 @Description("Returns an expression that has been converted to a Variant of subtype Byte.")
053 public static byte cByte(Object expression) {
054 if (expression instanceof Byte) {
055 return (Byte) expression;
056 } else {
057 int i = cInt(expression);
058 return (byte) i;
059 }
060 }
061
062 // public Currency cCur(Object expression)
063
064 @FunctionName("CDate")
065 @Signature("CDate(date)")
066 @Description("Returns an expression that has been converted to a Variant of subtype Date.")
067 public static Date cDate(Object expression) {
068 String str = String.valueOf(expression);
069 if (expression instanceof Date) {
070 return (Date) expression;
071 } else if (expression == null) {
072 return null;
073 } else {
074 // note that this currently only supports a limited set of dates and
075 // times
076 // "October 19, 1962"
077 // "4:35:47 PM"
078 try {
079 return DateFormat.getTimeInstance().parse(str);
080 } catch (ParseException ex0) {
081 try {
082 return DateFormat.getDateTimeInstance().parse(str);
083 } catch (ParseException ex1) {
084 try {
085 return DateFormat.getDateInstance().parse(str);
086 } catch (ParseException ex2) {
087 throw new InvalidArgumentException(
088 "Invalid parameter. "
089 + "expression parameter of CDate function must be "
090 + "formatted correctly ("
091 + String.valueOf(expression) + ")");
092 }
093 }
094 }
095 }
096 }
097
098 @FunctionName("CDbl")
099 @Signature("CDbl(expression)")
100 @Description("Returns an expression that has been converted to a Variant of subtype Double.")
101 public static double cDbl(Object expression) {
102 if (expression instanceof Number) {
103 Number number = (Number) expression;
104 return number.doubleValue();
105 } else {
106 final String s = String.valueOf(expression);
107 return new Double(s).intValue();
108 }
109 }
110
111 @FunctionName("CInt")
112 @Signature("CInt(expression)")
113 @Description("Returns an expression that has been converted to a Variant of subtype Integer.")
114 public static int cInt(Object expression) {
115 if (expression instanceof Number) {
116 Number number = (Number) expression;
117 final int intValue = number.intValue();
118 if (number instanceof Float || number instanceof Double) {
119 final double doubleValue = number.doubleValue();
120 if (doubleValue == (double) intValue) {
121 // Number is already an integer
122 return intValue;
123 }
124 final double doubleDouble = doubleValue * 2d;
125 if (doubleDouble == Math.floor(doubleDouble)) {
126 // Number ends in .5 - round towards even required
127 return (int) Math.round(doubleValue / 2d) * 2;
128 }
129 return (int) Math.round(doubleValue);
130 }
131 return intValue;
132 } else {
133 // Try to parse as integer before parsing as double. More
134 // efficient, and avoids loss of precision.
135 final String s = String.valueOf(expression);
136 try {
137 return Integer.parseInt(s);
138 } catch (NumberFormatException e) {
139 return new Double(s).intValue();
140 }
141 }
142 }
143
144 // public int cLng(Object expression)
145 // public float cSng(Object expression)
146 // public String cStr(Object expression)
147 // public Object cVDate(Object expression)
148 // public Object cVErr(Object expression)
149 // public Object cVar(Object expression)
150 // public String error$(Object errorNumber)
151 // public Object error(Object errorNumber)
152
153 @FunctionName("Fix")
154 @Signature("Fix(number)")
155 @Description("Returns the integer portion of a number. If negative, returns the negative number greater than or equal to the number.")
156 public static int fix(Object number) {
157 if (number instanceof Number) {
158 int v = ((Number) number).intValue();
159 double dv = ((Number) number).doubleValue();
160 if (v < 0 && v < dv) {
161 v++;
162 }
163 return v;
164 } else {
165 throw new InvalidArgumentException("Invalid parameter. "
166 + "number parameter " + number
167 + " of Int function must be " + "of type number");
168 }
169 }
170
171 @FunctionName("Hex")
172 @Description("Returns a String representing the hexadecimal value of a number.")
173 @Signature("Hex(number)")
174 public static String hex(Object number) {
175 if (number instanceof Number) {
176 return Integer.toHexString(((Number) number).intValue())
177 .toUpperCase();
178 } else {
179 throw new InvalidArgumentException("Invalid parameter. "
180 + "number parameter " + number
181 + " of Hex function must be " + "of type number");
182 }
183 }
184
185 @FunctionName("Int")
186 @Signature("Int(number)")
187 @Description("Returns the integer portion of a number. If negative, returns the negative number less than or equal to the number.")
188 public static int int_(Object number) {
189 if (number instanceof Number) {
190 int v = ((Number) number).intValue();
191 double dv = ((Number) number).doubleValue();
192 if (v < 0 && v > dv) {
193 v--;
194 }
195 return v;
196 } else {
197 throw new InvalidArgumentException("Invalid parameter. "
198 + "number parameter " + number
199 + " of Int function must be " + "of type number");
200 }
201 }
202
203 // public String oct$(Object number)
204
205 @FunctionName("Oct")
206 @Signature("Oct(number)")
207 @Description("Returns a Variant (String) representing the octal value of a number.")
208 public static String oct(Object number) {
209 if (number instanceof Number) {
210 return Integer.toOctalString(((Number) number).intValue());
211 } else {
212 throw new InvalidArgumentException("Invalid parameter. "
213 + "number parameter " + number
214 + " of Oct function must be " + "of type number");
215 }
216 }
217
218 // public String str$(Object number)
219
220 @FunctionName("Str")
221 @Signature("Str(number)")
222 @Description("Returns a Variant (String) representation of a number.")
223 public static String str(Object number) {
224 // When numbers are converted to strings, a leading space is always
225 // reserved for the sign of number. If number is positive, the returned
226 // string contains a leading space and the plus sign is implied.
227 //
228 // Use the Format function to convert numeric values you want formatted
229 // as dates, times, or currency or in other user-defined formats.
230 // Unlike Str, the Format function doesn't include a leading space for
231 // the sign of number.
232 //
233 // Note The Str function recognizes only the period (.) as a valid
234 // decimal separator. When different decimal separators may be used
235 // (for example, in international applications), use CStr to convert a
236 // number to a string.
237 if (number instanceof Number) {
238 if (((Number) number).doubleValue() >= 0) {
239 return " " + number.toString();
240 } else {
241 return number.toString();
242 }
243 } else {
244 throw new InvalidArgumentException("Invalid parameter. "
245 + "number parameter " + number
246 + " of Str function must be " + "of type number");
247 }
248 }
249
250 @FunctionName("Val")
251 @Signature("Val(string)")
252 @Description("Returns the numbers contained in a string as a numeric value of appropriate type.")
253 public static double val(String string) {
254 // The Val function stops reading the string at the first character it
255 // can't recognize as part of a number. Symbols and characters that are
256 // often considered parts of numeric values, such as dollar signs and
257 // commas, are not recognized. However, the function recognizes the
258 // radix prefixes &O (for octal) and &H (for hexadecimal). Blanks,
259 // tabs, and linefeed characters are stripped from the argument.
260 //
261 // The following returns the value 1615198:
262 //
263 // Val(" 1615 198th Street N.E.")
264 // In the code below, Val returns the decimal value -1 for the
265 // hexadecimal value shown:
266 //
267 // Val("&HFFFF")
268 // Note The Val function recognizes only the period (.) as a valid
269 // decimal separator. When different decimal separators are used, as in
270 // international applications, use CDbl instead to convert a string to
271 // a number.
272
273 string = string.replaceAll("\\s", ""); // remove all whitespace
274 if (string.startsWith("&H")) {
275 string = string.substring(2);
276 Pattern p = Pattern.compile("[0-9a-fA-F]*");
277 Matcher m = p.matcher(string);
278 m.find();
279 return Integer.parseInt(m.group(), 16);
280 } else if (string.startsWith("&O")) {
281 string = string.substring(2);
282 Pattern p = Pattern.compile("[0-7]*");
283 Matcher m = p.matcher(string);
284 m.find();
285 return Integer.parseInt(m.group(), 8);
286 } else {
287 // find the first number
288 Pattern p = Pattern.compile("-?[0-9]*[.]?[0-9]*");
289 Matcher m = p.matcher(string);
290 m.find();
291 return Double.parseDouble(m.group());
292
293 }
294 }
295
296 // DateTime
297
298 // public Calendar calendar()
299 // public void calendar(Calendar val)
300 // public String date$()
301 // public void date$(String val)
302
303 @FunctionName("DateAdd")
304 @Signature("DateAdd(interval, number, date)")
305 @Description("Returns a Variant (Date) containing a date to which a specified time interval has been added.")
306 public static Date dateAdd(String intervalName, double number, Date date) {
307 Interval interval = Interval.valueOf(intervalName);
308 final double floor = Math.floor(number);
309 Calendar calendar = Calendar.getInstance();
310 calendar.setTime(date);
311 if (floor != number) {
312 final double ceil = Math.ceil(number);
313 interval.add(calendar, (int) ceil);
314 final long ceilMillis = calendar.getTimeInMillis();
315
316 calendar.setTime(date);
317 interval.add(calendar, (int) floor);
318 final long floorMillis = calendar.getTimeInMillis();
319
320 final long amount =
321 (long)
322 (((double) (ceilMillis - floorMillis)) * (number - floor));
323 calendar
324 .add(Calendar.DAY_OF_YEAR, (int) (amount / MILLIS_IN_A_DAY));
325 calendar
326 .add(Calendar.MILLISECOND, (int) (amount % MILLIS_IN_A_DAY));
327 } else {
328 interval.add(calendar, (int) floor);
329 }
330 return calendar.getTime();
331 }
332
333 @FunctionName("DateDiff")
334 @Signature("DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])")
335 @Description("Returns a Variant (Long) specifying the number of time intervals between two specified dates.")
336 public static long dateDiff(String interval, Date date1, Date date2) {
337 return _dateDiff(interval, date1, date2, Calendar.SUNDAY,
338 FirstWeekOfYear.vbFirstJan1);
339 }
340
341 @FunctionName("DateDiff")
342 @Signature("DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])")
343 @Description("Returns a Variant (Long) specifying the number of time intervals between two specified dates.")
344 public static long dateDiff(String interval, Date date1, Date date2,
345 int firstDayOfWeek) {
346 return _dateDiff(interval, date1, date2, firstDayOfWeek,
347 FirstWeekOfYear.vbFirstJan1);
348 }
349
350 @FunctionName("DateDiff")
351 @Signature("DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])")
352 @Description("Returns a Variant (Long) specifying the number of time intervals between two specified dates.")
353 public static long dateDiff(String interval, Date date1, Date date2,
354 int firstDayOfWeek, int firstWeekOfYear) {
355 return _dateDiff(interval, date1, date2, firstDayOfWeek,
356 FirstWeekOfYear.values()[firstWeekOfYear]);
357 }
358
359 private static long _dateDiff(String intervalName, Date date1, Date date2,
360 int firstDayOfWeek, FirstWeekOfYear firstWeekOfYear) {
361 Interval interval = Interval.valueOf(intervalName);
362 Calendar calendar1 = Calendar.getInstance();
363 firstWeekOfYear.apply(calendar1);
364 calendar1.setTime(date1);
365 Calendar calendar2 = Calendar.getInstance();
366 firstWeekOfYear.apply(calendar2);
367 calendar2.setTime(date2);
368 return interval.diff(calendar1, calendar2, firstDayOfWeek);
369 }
370
371 @FunctionName("DatePart")
372 @Signature("DatePart(interval, date[,firstdayofweek[, firstweekofyear]])")
373 @Description("Returns a Variant (Integer) containing the specified part of a given date.")
374 public static int datePart(String interval, Date date) {
375 return _datePart(interval, date, Calendar.SUNDAY,
376 FirstWeekOfYear.vbFirstJan1);
377 }
378
379 @FunctionName("DatePart")
380 @Signature("DatePart(interval, date[,firstdayofweek[, firstweekofyear]])")
381 @Description("Returns a Variant (Integer) containing the specified part of a given date.")
382 public static int datePart(String interval, Date date, int firstDayOfWeek) {
383 return _datePart(interval, date, firstDayOfWeek,
384 FirstWeekOfYear.vbFirstJan1);
385 }
386
387 @FunctionName("DatePart")
388 @Signature("DatePart(interval, date[,firstdayofweek[, firstweekofyear]])")
389 @Description("Returns a Variant (Integer) containing the specified part of a given date.")
390 public static int datePart(String interval, Date date, int firstDayOfWeek,
391 int firstWeekOfYear) {
392 return _datePart(interval, date, firstDayOfWeek, FirstWeekOfYear
393 .values()[firstWeekOfYear]);
394 }
395
396 private static int _datePart(String intervalName, Date date,
397 int firstDayOfWeek, FirstWeekOfYear firstWeekOfYear) {
398 Interval interval = Interval.valueOf(intervalName);
399 Calendar calendar = Calendar.getInstance();
400 calendar.setTime(date);
401 switch (interval) {
402 case w:
403 case ww:
404 // firstWeekOfYear and firstDayOfWeek only matter for 'w' and 'ww'
405 firstWeekOfYear.apply(calendar);
406 calendar.setFirstDayOfWeek(firstDayOfWeek);
407 break;
408 }
409 return interval.datePart(calendar);
410 }
411
412 @FunctionName("Date")
413 @Signature("Date")
414 @Description("Returns a Variant (Date) containing the current system date.")
415 public static Date date() {
416 Calendar calendar = Calendar.getInstance();
417 calendar.clear();
418 calendar.set(Calendar.HOUR_OF_DAY, 0);
419 calendar.set(Calendar.MINUTE, 0);
420 calendar.set(Calendar.SECOND, 0);
421 calendar.set(Calendar.MILLISECOND, 0);
422 return calendar.getTime();
423 }
424
425 // public void date(Object val)
426
427 @FunctionName("DateSerial")
428 @Signature("DateSerial(year, month, day)")
429 @Description("Returns a Variant (Date) for a specified year, month, and day.")
430 public static Date dateSerial(int year, int month, int day) {
431 Calendar calendar = Calendar.getInstance();
432 calendar.clear();
433 calendar.set(year, month - 1, day);
434 return calendar.getTime();
435 }
436
437 @FunctionName("DateValue")
438 @Signature("DateValue(date)")
439 @Description("Returns a Variant (Date).")
440 public static Date dateValue(Date date) {
441 final Calendar calendar = Calendar.getInstance();
442 calendar.clear();
443 calendar.setTime(date);
444 calendar.set(Calendar.HOUR_OF_DAY, 0);
445 calendar.set(Calendar.MINUTE, 0);
446 calendar.set(Calendar.SECOND, 0);
447 calendar.set(Calendar.MILLISECOND, 0);
448 return calendar.getTime();
449 }
450
451 @FunctionName("Day")
452 @Signature("Day(date)")
453 @Description("Returns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month.")
454 public static int day(Date date) {
455 final Calendar calendar = Calendar.getInstance();
456 calendar.setTime(date);
457 return calendar.get(Calendar.DAY_OF_MONTH);
458 }
459
460 @FunctionName("Hour")
461 @Signature("Hour(time)")
462 @Description("Returns a Variant (Integer) specifying a whole number between 0 and 23, inclusive, representing the hour of the day.")
463 public static int hour(Date time) {
464 final Calendar calendar = Calendar.getInstance();
465 calendar.setTime(time);
466 return calendar.get(Calendar.HOUR_OF_DAY);
467 }
468
469 @FunctionName("Minute")
470 @Signature("Minute(time)")
471 @Description("Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the minute of the hour.")
472 public static int minute(Date time) {
473 final Calendar calendar = Calendar.getInstance();
474 calendar.setTime(time);
475 return calendar.get(Calendar.MINUTE);
476 }
477
478 @FunctionName("Month")
479 @Signature("Month(date)")
480 @Description("Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year.")
481 public static int month(Date date) {
482 final Calendar calendar = Calendar.getInstance();
483 calendar.setTime(date);
484 final int month = calendar.get(Calendar.MONTH);
485 return month + 1; // convert from 0- to 1-based
486 }
487
488 @FunctionName("Now")
489 @Signature("Now()")
490 @Description("Returns a Variant (Date) specifying the current date and time according your computer's system date and time.")
491 public static Date now() {
492 return new Date();
493 }
494
495 @FunctionName("Second")
496 @Signature("Second(time)")
497 @Description("Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the second of the minute.")
498 public static int second(Date time) {
499 final Calendar calendar = Calendar.getInstance();
500 calendar.setTime(time);
501 return calendar.get(Calendar.SECOND);
502 }
503
504 // public String time$()
505 // public void time$(String val)
506
507 @FunctionName("Time")
508 @Signature("Time()")
509 @Description("Returns a Variant (Date) indicating the current system time.")
510 public static Date time() {
511 return new Date();
512 }
513
514 // public void time(Object val)
515
516 @FunctionName("TimeSerial")
517 @Signature("TimeSerial(hour, minute, second)")
518 @Description("Returns a Variant (Date) containing the time for a specific hour, minute, and second.")
519 public static Date timeSerial(int hour, int minute, int second) {
520 final Calendar calendar = Calendar.getInstance();
521 calendar.clear();
522 calendar.set(Calendar.HOUR_OF_DAY, hour);
523 calendar.set(Calendar.MINUTE, minute);
524 calendar.set(Calendar.SECOND, second);
525 return calendar.getTime();
526 }
527
528 @FunctionName("TimeValue")
529 @Signature("TimeValue(time)")
530 @Description("Returns a Variant (Date) containing the time.")
531 public static Date timeValue(Date time) {
532 final Calendar calendar = Calendar.getInstance();
533 calendar.clear();
534 calendar.setTime(time);
535 calendar.set(1970, 0, 1);
536 return calendar.getTime();
537 }
538
539 @FunctionName("Timer")
540 @Signature("Timer()")
541 @Description("Returns a Single representing the number of seconds elapsed since midnight.")
542 public static float timer() {
543 final Calendar calendar = Calendar.getInstance();
544 final long now = calendar.getTimeInMillis();
545 calendar.set(Calendar.HOUR_OF_DAY, 0);
546 calendar.set(Calendar.MINUTE, 0);
547 calendar.set(Calendar.SECOND, 0);
548 calendar.set(Calendar.MILLISECOND, 0);
549 final long midnight = calendar.getTimeInMillis();
550 return ((float) (now - midnight)) / 1000f;
551 }
552
553 @FunctionName("Weekday")
554 @Signature("Weekday(date[, firstDayOfWeek])")
555 @Description("Returns a Variant (Integer) containing a whole number representing the day of the week.")
556 public static int weekday(Date date) {
557 return weekday(date, Calendar.SUNDAY);
558 }
559
560 @FunctionName("Weekday")
561 @Signature("Weekday(date[, firstDayOfWeek])")
562 @Description("Returns a Variant (Integer) containing a whole number representing the day of the week.")
563 public static int weekday(Date date, int firstDayOfWeek) {
564 final Calendar calendar = Calendar.getInstance();
565 calendar.setTime(date);
566 int weekday = calendar.get(Calendar.DAY_OF_WEEK);
567 // adjust for start of week
568 weekday -= (firstDayOfWeek - 1);
569 // bring into range 1..7
570 weekday = (weekday + 6) % 7 + 1;
571 return weekday;
572 }
573
574 @FunctionName("Year")
575 @Signature("Year(date)")
576 @Description("Returns a Variant (Integer) containing a whole number representing the year.")
577 public static int year(Date date) {
578 final Calendar calendar = Calendar.getInstance();
579 calendar.setTime(date);
580 return calendar.get(Calendar.YEAR);
581 }
582
583 //
584 // /* FileSystem */
585 // public void chDir(String path)
586 // public void chDrive(String drive)
587 // public String curDir$(Object drive)
588 // public Object curDir(Object drive)
589 // public String dir(Object pathName, FileAttribute attributes /* default
590 // FileAttribute.Normal */)
591 // public boolean EOF(int fileNumber)
592 // public int fileAttr(int fileNumber, int returnType /* default 1 */)
593 // public void fileCopy(String source, String destination)
594 // public Object fileDateTime(String pathName)
595 // public int fileLen(String pathName)
596 // public int freeFile(Object rangeNumber)
597 // public FileAttribute getAttr(String pathName)
598 // public void kill(Object pathName)
599 // public int LOF(int fileNumber)
600 // public int loc(int fileNumber)
601 // public void mkDir(String path)
602 // public void reset()
603 // public void rmDir(String path)
604 // public int seek(int fileNumber)
605 // public void setAttr(String pathName, FileAttribute attributes)
606 //
607 // Financial
608
609 @FunctionName("DDB")
610 @Signature("DDB(cost, salvage, life, period[, factor])")
611 @Description("Returns a Double specifying the depreciation of an asset for a specific time period using the double-declining balance method or some other method you specify.")
612 public static double dDB(double cost, double salvage, double life,
613 double period) {
614 return dDB(cost, salvage, life, period, 2.0);
615 }
616
617 @FunctionName("DDB")
618 @Signature("DDB(cost, salvage, life, period[, factor])")
619 @Description("Returns a Double specifying the depreciation of an asset for a specific time period using the double-declining balance method or some other method you specify.")
620 public static double dDB(double cost, double salvage, double life,
621 double period, double factor) {
622 return (((cost - salvage) * factor) / life) * period;
623 }
624
625 @FunctionName("FV")
626 @Signature("FV(rate, nper, pmt[, pv[, type]])")
627 @Description("Returns a Double specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate.")
628 public static double fV(double rate, double nPer, double pmt) {
629 return fV(rate, nPer, pmt, 0d, false);
630 }
631
632 @FunctionName("FV")
633 @Signature("FV(rate, nper, pmt[, pv[, type]])")
634 @Description("Returns a Double specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate.")
635 public static double fV(double rate, double nPer, double pmt, double pv) {
636 return fV(rate, nPer, pmt, pv, false);
637 }
638
639 @FunctionName("FV")
640 @Signature("FV(rate, nper, pmt[, pv[, type]])")
641 @Description("Returns a Double specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate.")
642 public static double fV(double rate, double nPer, double pmt, double pv,
643 boolean type) {
644 if (rate == 0) {
645 return -(pv + (nPer * pmt));
646 } else {
647 double r1 = rate + 1;
648 return ((1 - Math.pow(r1, nPer)) * (type ? r1 : 1) * pmt) / rate
649 - pv * Math.pow(r1, nPer);
650 }
651 }
652
653 @FunctionName("IPmt")
654 @Signature("IPmt(rate, per, nper, pv[, fv[, type]])")
655 @Description("Returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.")
656 public static double iPmt(double rate, double per, double nPer, double PV) {
657 return iPmt(rate, per, nPer, PV, 0);
658 }
659
660
661 @FunctionName("IPmt")
662 @Signature("IPmt(rate, per, nper, pv[, fv[, type]])")
663 @Description("Returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.")
664 public static double iPmt(double rate, double per, double nPer, double PV,
665 double fv) {
666 return iPmt(rate, per, nPer, PV, fv, false);
667 }
668
669
670 @FunctionName("IPmt")
671 @Signature("IPmt(rate, per, nper, pv[, fv[, type]])")
672 @Description("Returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.")
673 public static double iPmt(double rate, double per, double nPer, double PV,
674 double fv, boolean due) {
675 double pmtVal = pmt(rate, nPer, PV, fv, due);
676 double pValm1 = PV - pV(rate, per - 1, pmtVal, fv, due);
677 return - pValm1 * rate;
678 }
679
680 @FunctionName("IRR")
681 @Signature("IRR(values()[, guess])")
682 @Description("Returns a Double specifying the internal rate of return for a series of periodic cash flows (payments and receipts).")
683 public static double IRR(double[] valueArray) {
684 return IRR(valueArray, 0.10);
685 }
686
687
688 @FunctionName("IRR")
689 @Signature("IRR(values()[, guess])")
690 @Description("Returns a Double specifying the internal rate of return for a series of periodic cash flows (payments and receipts).")
691 public static double IRR(double[] valueArray, double guess) {
692
693 // calc pV of stream (sum of pV's for valueArray) ((1 + guess) ^ index)
694 double minGuess = 0.0;
695 double maxGuess = 1.0;
696
697 // i'm not certain
698 int r = 1;
699 if (valueArray[0] > 0) {
700 r = -1;
701 }
702
703 for (int i = 0; i < 30; i++) {
704 // first calculate overall return based on guess
705 double totalPv = 0;
706 for (int j = 0; j < valueArray.length; j++) {
707 totalPv += valueArray[j] / Math.pow(1.0 + guess, j);
708 }
709 if ((maxGuess - minGuess) < 0.0000001) {
710 return guess;
711 } else if (totalPv * r < 0) {
712 maxGuess = guess;
713 } else {
714 minGuess = guess;
715 }
716 // avg max min to determine next step
717 guess = (maxGuess + minGuess) / 2;
718 }
719 // unable to find a match
720 return -1;
721 }
722
723 @FunctionName("MIRR")
724 @Signature("MIRR(values(), finance_rate, reinvest_rate)")
725 @Description("Returns a Double specifying the modified internal rate of return for a series of periodic cash flows (payments and receipts).")
726 public static double MIRR(double valueArray[], double financeRate,
727 double reinvestRate) {
728 // based on http://en.wikipedia.org/wiki/Modified_Internal_Rate_of_Return
729 double reNPV = 0.0;
730 double fiNPV = 0.0;
731 for (int j = 0; j < valueArray.length; j++) {
732 if (valueArray[j] > 0) {
733 reNPV += valueArray[j] / Math.pow(1.0 + reinvestRate, j);
734 } else {
735 fiNPV += valueArray[j] / Math.pow(1.0 + financeRate, j);
736 }
737 }
738
739 double ratio = (- reNPV * Math.pow(1 + reinvestRate, valueArray.length)) /
740 (fiNPV * (1 + financeRate));
741
742 return Math.pow(ratio, 1.0 / (valueArray.length - 1)) - 1.0;
743
744 }
745
746 @FunctionName("NPer")
747 @Signature("NPer(rate, pmt, pv[, fv[, type]])")
748 @Description("Returns a Double specifying the number of periods for an annuity based on periodic, fixed payments and a fixed interest rate.")
749 public static double nPer(double rate, double pmt, double pv, double fv,
750 boolean due) {
751 if (rate == 0) {
752 return -(fv + pv) / pmt;
753 } else {
754 double r1 = rate + 1;
755 double ryr = (due ? r1 : 1) * pmt / rate;
756 double a1 = ((ryr - fv) < 0) ? Math.log(fv - ryr) : Math.log(ryr
757 - fv);
758 double a2 = ((ryr - fv) < 0) ? Math.log(-pv - ryr) : Math.log(pv
759 + ryr);
760 double a3 = Math.log(r1);
761 return (a1 - a2) / a3;
762 }
763 }
764
765 @FunctionName("NPV")
766 @Signature("NPV(rate, values())")
767 @Description("Returns a Double specifying the net present value of an investment based on a series of periodic cash flows (payments and receipts) and a discount rate.")
768 public static double nPV(double r, double[] cfs) {
769 double npv = 0;
770 double r1 = r + 1;
771 double trate = r1;
772 for (int i = 0, iSize = cfs.length; i < iSize; i++) {
773 npv += cfs[i] / trate;
774 trate *= r1;
775 }
776 return npv;
777 }
778
779 @FunctionName("PPmt")
780 @Signature("PPmt(rate, per, nper, pv[, fv[, type]])")
781 @Description("Returns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.")
782 public static double pPmt(double rate, double per, double nPer, double PV) {
783 return pPmt(rate, per, nPer, PV, 0);
784 }
785
786 @FunctionName("PPmt")
787 @Signature("PPmt(rate, per, nper, pv[, fv[, type]])")
788 @Description("Returns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.")
789 public static double pPmt(double rate, double per, double nPer, double PV,
790 double fv) {
791 return pPmt(rate, per, nPer, PV, fv, false);
792 }
793
794 @FunctionName("PPmt")
795 @Signature("PPmt(rate, per, nper, pv[, fv[, type]])")
796 @Description("Returns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.")
797 public static double pPmt(double rate, double per, double nPer, double PV,
798 double fv, boolean due) {
799 return pmt(rate, nPer, PV, fv, due) - iPmt(rate, per, nPer, PV, fv, due);
800 }
801
802 @FunctionName("Pmt")
803 @Signature("Pmt(rate, nper, pv[, fv[, type]])")
804 @Description("Returns a Double specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate.")
805 public static double pmt(double rate, double nPer, double pv, double fv,
806 boolean due) {
807 if (rate == 0) {
808 return -(fv + pv) / nPer;
809 } else {
810 double r1 = rate + 1;
811 return (fv + pv * Math.pow(r1, nPer)) * rate
812 / ((due ? r1 : 1) * (1 - Math.pow(r1, nPer)));
813 }
814 }
815
816 @FunctionName("PV")
817 @Signature("PV(rate, nper, pmt[, fv[, type]])")
818 @Description("Returns a Double specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate.")
819 public static double pV(double rate, double nper, double pmt, double fv,
820 boolean due) {
821 if (rate == 0) {
822 return -((nper * pmt) + fv);
823 } else {
824 double r1 = rate + 1;
825 return
826 (((1 - Math.pow(r1, nper)) / rate) * (due ? r1 : 1) * pmt - fv)
827 / Math.pow(r1, nper);
828 }
829 }
830
831 @FunctionName("Rate")
832 @Signature("Rate(nper, pmt, pv[, fv[, type[, guess]]])")
833 @Description("Returns a Double specifying the interest rate per period for an annuity.")
834 public static double rate(double nPer, double pmt, double PV) {
835 return rate(nPer, pmt, PV, 0);
836 }
837
838 @FunctionName("Rate")
839 @Signature("Rate(nper, pmt, pv[, fv[, type[, guess]]])")
840 @Description("Returns a Double specifying the interest rate per period for an annuity.")
841 public static double rate(double nPer, double pmt, double PV, double fv) {
842 return rate(nPer, pmt, PV, fv, false);
843 }
844
845 @FunctionName("Rate")
846 @Signature("Rate(nper, pmt, pv[, fv[, type[, guess]]])")
847 @Description("Returns a Double specifying the interest rate per period for an annuity.")
848 public static double rate(double nPer, double pmt, double PV, double fv,
849 boolean type) {
850 return rate(nPer, pmt, PV, fv, type, 0.1);
851 }
852
853 @FunctionName("Rate")
854 @Signature("Rate(nper, pmt, pv[, fv[, type[, guess]]])")
855 @Description("Returns a Double specifying the interest rate per period for an annuity.")
856 public static double rate(double nPer, // specifies the number of payment
857 // periods
858 double pmt, // payment per period of annuity
859 double PV, // the present value of the annuity (0 if a loan)
860 double fv, // the future value of the annuity ($ if savings)
861 boolean due, double guess) {
862 if (nPer <= 0) {
863 throw new InvalidArgumentException(
864 "number of payment periods must be larger than 0");
865 }
866 double minGuess = 0.0;
867 double maxGuess = 1.0;
868
869 // converge on the correct answer should use Newton's Method
870 // for now use a binary search
871 int r = 1;
872 if (PV < fv) {
873 r = -1;
874 }
875
876 // the vb method uses 20 iterations, but they also probably use newton's
877 // method,
878 // so i've bumped it up to 30 iterations.
879 for (int n = 0; n < 30; n++) {
880 double gFV = fV(guess, nPer, pmt, PV, due);
881 double diff = gFV - fv;
882 if ((maxGuess - minGuess) < 0.0000001) {
883 return guess;
884 } else {
885 if (diff * r < 0) {
886 maxGuess = guess;
887 } else {
888 minGuess = guess;
889 }
890 guess = (maxGuess + minGuess) / 2;
891 }
892 }
893 // fail, not sure how VB fails
894 return -1;
895 }
896
897 @FunctionName("SLN")
898 @Signature("SLN(cost, salvage, life)")
899 @Description("Returns a Double specifying the straight-line depreciation of an asset for a single period.")
900 public static double sLN(double cost, double salvage, double life) {
901 return (cost - salvage) / life;
902 }
903
904 @FunctionName("SYD")
905 @Signature("SYD(cost, salvage, life, period)")
906 @Description("Returns a Double specifying the sum-of-years' digits depreciation of an asset for a specified period.")
907 public static double sYD(double cost, double salvage, double life,
908 double period) {
909 return (cost - salvage) * (life / (period * (period + 1) / 2));
910 }
911
912 // Information
913
914 // public Throwable err()
915 // public Object iMEStatus()
916
917 @FunctionName("IsArray")
918 @Signature("IsArray(varname)")
919 @Description("Returns a Boolean value indicating whether a variable is an array.")
920 public boolean isArray(Object varName) {
921 // arrays are not supported at present
922 return false;
923 }
924
925 @FunctionName("IsDate")
926 @Signature("IsDate(varname)")
927 @Description("Returns a Boolean value indicating whether an expression can be converted to a date..")
928 public static boolean isDate(Object expression) {
929 // IsDate returns True if Expression represents a valid date, a valid
930 // time, or a valid date and time.
931 try {
932 Date val = cDate(expression);
933 return (val != null);
934 } catch (InvalidArgumentException e) {
935 return false;
936 }
937 }
938
939 // use mondrian's implementation of IsEmpty
940 // public boolean isEmpty(Object expression)
941
942 @FunctionName("IsError")
943 @Signature("IsError(varname)")
944 @Description("Returns a Boolean value indicating whether an expression is an error value.")
945 public boolean isError(Object expression) {
946 return expression instanceof Throwable;
947 }
948
949 @FunctionName("IsMissing")
950 @Signature("IsMissing(varname)")
951 @Description("Returns a Boolean value indicating whether an optional Variant argument has been passed to a procedure.")
952 public boolean isMissing(Object argName) {
953 // We have no way to detect missing arguments.
954 return false;
955 }
956
957 @FunctionName("IsNull")
958 @Signature("IsNull(varname)")
959 @Description("Returns a Boolean value that indicates whether an expression contains no valid data (Null).")
960 public boolean isNull(Object expression) {
961 return expression == null;
962 }
963
964 @FunctionName("IsNumeric")
965 @Signature("IsNumeric(varname)")
966 @Description("Returns a Boolean value indicating whether an expression can be evaluated as a number.")
967 public boolean isNumeric(Object expression) {
968 return expression instanceof Number;
969 }
970
971 @FunctionName("IsObject")
972 @Signature("IsObject(varname)")
973 @Description("Returns a Boolean value indicating whether an identifier represents an object variable.")
974 public boolean isObject(Object expression) {
975 return false;
976 }
977
978 // public int qBColor(int color)
979 // public int RGB(int red, int green, int blue)
980
981 @FunctionName("TypeName")
982 @Signature("TypeName(varname)")
983 @Description("Returns a String that provides information about a variable.")
984 public static String typeName(Object varName) {
985 // The string returned by TypeName can be any one of the following:
986 //
987 // String returned Variable
988 // object type An object whose type is objecttype
989 // Byte Byte value
990 // Integer Integer
991 // Long Long integer
992 // Single Single-precision floating-point number
993 // Double Double-precision floating-point number
994 // Currency Currency value
995 // Decimal Decimal value
996 // Date Date value
997 // String String
998 // Boolean Boolean value
999 // Error An error value
1000 // Empty Uninitialized
1001 // Null No valid data
1002 // Object An object
1003 // Unknown An object whose type is unknown
1004 // Nothing Object variable that doesn't refer to an object
1005
1006 if (varName == null) {
1007 return "NULL";
1008 } else {
1009 // strip off the package information
1010 String name = varName.getClass().getName();
1011 if (name.lastIndexOf(".") >= 0) {
1012 name = name.substring(name.lastIndexOf(".") + 1);
1013 }
1014 return name;
1015 }
1016 }
1017
1018 // public VarType varType(Object varName)
1019
1020 // Interaction
1021
1022 // public void appActivate(Object title, Object wait)
1023 // public void beep()
1024 // public Object callByName(Object object, String procName, CallType
1025 // callType, Object args, int lcid)
1026 // public Object choose(float index, Object choice)
1027 // public String command$()
1028 // public Object command()
1029 // public Object createObject(String Class, String serverName)
1030 // public int doEvents()
1031 // public String environ$(Object expression)
1032 // public Object environ(Object expression)
1033 // public Object getAllSettings(String appName, String section)
1034 // public Object getObject(Object pathName, Object Class)
1035 // public String getSetting(String appName, String section, String key,
1036 // Object Default)
1037 // public Object iIf(Object expression, Object truePart, Object falsePart)
1038 // public String inputBox(Object prompt, Object title, Object Default,
1039 // Object xPos, Object yPos, Object helpFile, Object context)
1040 // public String macScript(String script)
1041 // public MsgBoxResult msgBox(Object prompt, MsgBoxStyle buttons /* default
1042 // MsgBoxStyle.OKOnly */, Object title, Object helpFile, Object context)
1043 // public Object partition(Object number, Object start, Object stop, Object
1044 // interval)
1045 // public void saveSetting(String appName, String section, String key,
1046 // String setting)
1047 // public void sendKeys(String string, Object wait)
1048 // public double shell(Object pathName, AppWinStyle windowStyle /* default
1049 // AppWinStyle.MinimizedFocus */)
1050 // public Object Switch(Object varExpr)
1051
1052 // Mathematical
1053
1054 @FunctionName("Abs")
1055 @Signature("Abs(number)")
1056 @Description("Returns a value of the same type that is passed to it specifying the absolute value of a number.")
1057 public static double abs(double number) {
1058 return Math.abs(number);
1059 }
1060
1061 @FunctionName("Atn")
1062 @Signature("Atn(number)")
1063 @Description("Returns a Double specifying the arctangent of a number.")
1064 public static double atn(double number) {
1065 return Math.atan(number);
1066 }
1067
1068 @FunctionName("Cos")
1069 @Signature("Cos(number)")
1070 @Description("Returns a Double specifying the cosine of an angle.")
1071 public static double cos(double number) {
1072 return Math.cos(number);
1073 }
1074
1075 @FunctionName("Exp")
1076 @Signature("Exp(number)")
1077 @Description("Returns a Double specifying e (the base of natural logarithms) raised to a power.")
1078 public static double exp(double number) {
1079 return Math.exp(number);
1080 }
1081
1082 @FunctionName("Log")
1083 @Signature("Log(number)")
1084 @Description("Returns a Double specifying the natural logarithm of a number.")
1085 public static double log(double number) {
1086 return Math.log(number);
1087 }
1088
1089 // Cannot implement randomize and rnd - we require context to hold the
1090 // seed
1091
1092 // public void randomize(Object number)
1093 // public float rnd(Object number)
1094
1095 @FunctionName("Round")
1096 @Signature("Round(number[, numDigitsAfterDecimal])")
1097 @Description("Returns a number rounded to a specified number of decimal places.")
1098 public static double round(double number) {
1099 return Math.round(number);
1100 }
1101
1102 @FunctionName("Round")
1103 @Signature("Round(number[, numDigitsAfterDecimal])")
1104 @Description("Returns a number rounded to a specified number of decimal places.")
1105 public static double round(double number, int numDigitsAfterDecimal) {
1106 if (numDigitsAfterDecimal == 0) {
1107 return Math.round(number);
1108 }
1109 final double shift = Math.pow(10d, numDigitsAfterDecimal);
1110 double numberScaled = number * shift;
1111 double resultScaled = Math.round(numberScaled);
1112 return resultScaled / shift;
1113 }
1114
1115 @FunctionName("Sgn")
1116 @Signature("Sgn(number)")
1117 @Description("Returns a Variant (Integer) indicating the sign of a number.")
1118 public static int sgn(double number) {
1119 // We could use Math.signum(double) from JDK 1.5 onwards.
1120 return number < 0.0d ? -1 : number > 0.0d ? 1 : 0;
1121 }
1122
1123 @FunctionName("Sin")
1124 @Signature("Sin(number)")
1125 @Description("Returns a Double specifying the sine of an angle.")
1126 public static double sin(double number) {
1127 return Math.sin(number);
1128 }
1129
1130 @FunctionName("Sqr")
1131 @Signature("Sqr(number)")
1132 @Description("Returns a Double specifying the square root of a number.")
1133 public static double sqr(double number) {
1134 return Math.sqrt(number);
1135 }
1136
1137 @FunctionName("Tan")
1138 @Signature("Tan(number)")
1139 @Description("Returns a Double specifying the tangent of an angle.")
1140 public static double tan(double number) {
1141 return Math.tan(number);
1142 }
1143
1144 // Strings
1145
1146 @FunctionName("Asc")
1147 @Signature("Asc(string)")
1148 @Description("Returns an Integer representing the character code corresponding to the first letter in a string.")
1149 public static int asc(String string) {
1150 return string.charAt(0);
1151 }
1152
1153 @FunctionName("AscB")
1154 @Signature("AscB(string)")
1155 @Description("See Asc.")
1156 public static int ascB(String string) {
1157 return (byte) string.charAt(0);
1158 }
1159
1160 @FunctionName("AscW")
1161 @Signature("AscW(string)")
1162 @Description("See Asc.")
1163 public static int ascW(String string) {
1164 return asc(string);
1165 }
1166
1167 // public String chr$(int charCode)
1168 // public String chrB$(int charCode)
1169
1170 @FunctionName("Chr")
1171 @Signature("Chr(charcode)")
1172 @Description("Returns a String containing the character associated with the specified character code.")
1173 public static String chr(int charCode) {
1174 return new String(new char[] { (char) charCode });
1175 }
1176
1177 @FunctionName("ChrB")
1178 @Signature("ChrB(charcode)")
1179 @Description("See Chr.")
1180 public static String chrB(int charCode) {
1181 return new String(new byte[] { (byte) charCode });
1182 }
1183
1184 // public String chrW$(int charCode)
1185
1186 @FunctionName("ChrW")
1187 @Signature("ChrW(charcode)")
1188 @Description("See Chr.")
1189 public static String chrW(int charCode) {
1190 return new String(new char[] { (char) charCode });
1191 }
1192
1193 // public Object filter(Object sourceArray, String match, boolean include /*
1194 // default 1 */, int compare /* default BinaryCompare */)
1195 // public String format$(Object expression, Object format, int
1196 // firstDayOfWeek /* default Sunday */, int firstWeekOfYear /* default
1197 // FirstJan1 */)
1198
1199 @FunctionName("FormatCurrency")
1200 @Signature("FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1201 @Description("Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.")
1202 public static String formatCurrency(Object expression) {
1203 return formatCurrency(expression, -1, -2, -2, -2);
1204 }
1205
1206 @FunctionName("FormatCurrency")
1207 @Signature("FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1208 @Description("Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.")
1209 public static String formatCurrency(Object expression,
1210 int numDigitsAfterDecimal) {
1211 return formatCurrency(expression, numDigitsAfterDecimal, -2, -2, -2);
1212 }
1213
1214 @FunctionName("FormatCurrency")
1215 @Signature("FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1216 @Description("Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.")
1217 public static String formatCurrency(Object expression,
1218 int numDigitsAfterDecimal, int includeLeadingDigit) {
1219 return formatCurrency(expression, numDigitsAfterDecimal,
1220 includeLeadingDigit, -2, -2);
1221 }
1222
1223 @FunctionName("FormatCurrency")
1224 @Signature("FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1225 @Description("Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.")
1226 public static String formatCurrency(Object expression,
1227 int numDigitsAfterDecimal, int includeLeadingDigit,
1228 int useParensForNegativeNumbers) {
1229 return formatCurrency(expression, numDigitsAfterDecimal,
1230 includeLeadingDigit, useParensForNegativeNumbers, -2);
1231 }
1232
1233 @FunctionName("FormatCurrency")
1234 @Signature("FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1235 @Description("Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.")
1236 public static String formatCurrency(Object expression,
1237 int numDigitsAfterDecimal, int includeLeadingDigit,
1238 int useParensForNegativeNumbers, int groupDigits) {
1239 DecimalFormat format = (DecimalFormat) NumberFormat
1240 .getCurrencyInstance();
1241 if (numDigitsAfterDecimal != -1) {
1242 format.setMaximumFractionDigits(numDigitsAfterDecimal);
1243 format.setMinimumFractionDigits(numDigitsAfterDecimal);
1244 }
1245 if (includeLeadingDigit != -2) {
1246 if (includeLeadingDigit != 0) {
1247 format.setMinimumIntegerDigits(1);
1248 } else {
1249 format.setMinimumIntegerDigits(0);
1250 }
1251 }
1252 if (useParensForNegativeNumbers != -2) {
1253 // todo: implement.
1254 // This will require tweaking of the currency expression
1255 }
1256 if (groupDigits != -2) {
1257 if (groupDigits != 0) {
1258 format.setGroupingUsed(false);
1259 } else {
1260 format.setGroupingUsed(true);
1261 }
1262 }
1263 return format.format(expression);
1264 }
1265
1266 @FunctionName("FormatDateTime")
1267 @Signature("FormatDateTime(Date[,NamedFormat])")
1268 @Description("Returns an expression formatted as a date or time.")
1269 public static String formatDateTime(Date date) {
1270 return formatDateTime(date, 0);
1271
1272 }
1273
1274 @FunctionName("FormatDateTime")
1275 @Signature("FormatDateTime(Date[,NamedFormat])")
1276 @Description("Returns an expression formatted as a date or time.")
1277 public static String formatDateTime(
1278 Date date,
1279 int namedFormat /* default 0, GeneralDate */)
1280 {
1281 // todo: test
1282 // todo: how do we support VB Constants? Strings or Ints?
1283 switch (namedFormat) {
1284 // vbLongDate, 1
1285 // Display a date using the long date format specified in your
1286 // computer's regional settings.
1287
1288 case 1:
1289 return DateFormat.getDateInstance(DateFormat.LONG).format(date);
1290
1291 // vbShortDate, 2
1292 // Display a date using the short date format specified in your
1293 // computer's regional settings.
1294 case 2:
1295 return DateFormat.getDateInstance(DateFormat.SHORT).format(date);
1296
1297 // vbLongTime, 3
1298 // Display a time using the time format specified in your computer's
1299 // regional settings.
1300 case 3:
1301 return DateFormat.getTimeInstance(DateFormat.LONG).format(date);
1302
1303 // vbShortTime, 4
1304 // Display a time using the 24-hour format (hh:mm).
1305 case 4:
1306 return DateFormat.getTimeInstance(DateFormat.SHORT).format(date);
1307
1308 // vbGeneralDate, 0
1309 // Display a date and/or time. If there is a date part, display it as a
1310 // short date. If there is a time part, display it as a long time. If
1311 // present, both parts are displayed.
1312
1313 // todo: how do we determine if there is a "time part" in java?
1314 case 0:
1315 default:
1316 return DateFormat.getDateTimeInstance().format(date);
1317 }
1318 }
1319
1320 // Format is implemented with FormatFunDef, third and fourth params are not
1321 // supported
1322 // @FunctionName("Format")
1323 // @Signature("Format(expression[, format[, firstdayofweek[,
1324 // firstweekofyear]]])")
1325 // @Description("Returns a Variant (String) containing an expression
1326 // formatted according to instructions contained in a format expression.")
1327
1328
1329 @FunctionName("FormatNumber")
1330 @Signature("FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1331 @Description("Returns an expression formatted as a number.")
1332 public static String formatNumber(Object expression) {
1333 return formatNumber(expression, -1);
1334 }
1335
1336 @FunctionName("FormatNumber")
1337 @Signature("FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1338 @Description("Returns an expression formatted as a number.")
1339 public static String formatNumber(Object expression,
1340 int numDigitsAfterDecimal) {
1341 return formatNumber(expression, numDigitsAfterDecimal, -1);
1342 }
1343
1344 @FunctionName("FormatNumber")
1345 @Signature("FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1346 @Description("Returns an expression formatted as a number.")
1347 public static String formatNumber(
1348 Object expression,
1349 int numDigitsAfterDecimal,
1350 int includeLeadingDigit)
1351 {
1352 return formatNumber(expression, numDigitsAfterDecimal,
1353 includeLeadingDigit, -1);
1354 }
1355
1356 @FunctionName("FormatNumber")
1357 @Signature("FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1358 @Description("Returns an expression formatted as a number.")
1359 public static String formatNumber(
1360 Object expression,
1361 int numDigitsAfterDecimal,
1362 int includeLeadingDigit,
1363 int useParensForNegativeNumbers)
1364 {
1365 return formatNumber(expression, numDigitsAfterDecimal,
1366 includeLeadingDigit, useParensForNegativeNumbers, -1);
1367 }
1368
1369 @FunctionName("FormatNumber")
1370 @Signature("FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1371 @Description("Returns an expression formatted as a number.")
1372 public static String formatNumber(
1373 Object expression,
1374 int numDigitsAfterDecimal /* default -1 */,
1375 int includeLeadingDigit /* default usedefault */,
1376 int useParensForNegativeNumbers /* default UseDefault */,
1377 int groupDigits /* default UseDefault */)
1378 {
1379 NumberFormat format = NumberFormat.getNumberInstance();
1380 if (numDigitsAfterDecimal != -1) {
1381 format.setMaximumFractionDigits(numDigitsAfterDecimal);
1382 format.setMinimumFractionDigits(numDigitsAfterDecimal);
1383 }
1384
1385 if (includeLeadingDigit != -1) {
1386 if (includeLeadingDigit != 0) {
1387 // true
1388 format.setMinimumIntegerDigits(1);
1389 } else {
1390 format.setMinimumIntegerDigits(0);
1391 }
1392 }
1393
1394 if (useParensForNegativeNumbers != -1) {
1395 if (useParensForNegativeNumbers != 0) {
1396 DecimalFormat dformat = (DecimalFormat)format;
1397 dformat.setNegativePrefix("(");
1398 dformat.setNegativeSuffix(")");
1399 } else {
1400 DecimalFormat dformat = (DecimalFormat)format;
1401 dformat.setNegativePrefix(
1402 "" + dformat.getDecimalFormatSymbols().getMinusSign());
1403 dformat.setNegativeSuffix("");
1404 }
1405 }
1406
1407 if (groupDigits != -1) {
1408 format.setGroupingUsed(groupDigits != 0);
1409 }
1410
1411 return format.format(expression);
1412 }
1413
1414 @FunctionName("FormatPercent")
1415 @Signature("FormatPercent(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1416 @Description("Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.")
1417 public static String formatPercent(Object expression) {
1418 return formatPercent(expression, -1);
1419 }
1420
1421 @FunctionName("FormatPercent")
1422 @Signature("FormatPercent(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1423 @Description("Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.")
1424 public static String formatPercent(
1425 // todo: impl & test
1426 Object expression, int numDigitsAfterDecimal /* default -1 */) {
1427 return formatPercent(expression, numDigitsAfterDecimal, -1);
1428
1429 }
1430
1431 @FunctionName("FormatPercent")
1432 @Signature("FormatPercent(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1433 @Description("Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.")
1434 public static String formatPercent(
1435 // todo: impl & test
1436 Object expression, int numDigitsAfterDecimal /* default -1 */,
1437 int includeLeadingDigit /* default UseDefault */) {
1438 return formatPercent(expression, numDigitsAfterDecimal,
1439 includeLeadingDigit, -1);
1440
1441 }
1442
1443 @FunctionName("FormatPercent")
1444 @Signature("FormatPercent(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1445 @Description("Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.")
1446 public static String formatPercent(
1447 // todo: impl & test
1448 Object expression, int numDigitsAfterDecimal /* default -1 */,
1449 int includeLeadingDigit /* default UseDefault */,
1450 int useParensForNegativeNumbers /* default UseDefault */) {
1451 return formatPercent(expression, numDigitsAfterDecimal,
1452 includeLeadingDigit, useParensForNegativeNumbers, -1);
1453 }
1454
1455 @FunctionName("FormatPercent")
1456 @Signature("FormatPercent(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])")
1457 @Description("Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.")
1458 public static String formatPercent(
1459 Object expression, int numDigitsAfterDecimal /* default -1 */,
1460 int includeLeadingDigit /* default UseDefault */,
1461 int useParensForNegativeNumbers /* default UseDefault */,
1462 int groupDigits /* default UseDefault */) {
1463
1464 NumberFormat format = NumberFormat.getPercentInstance();
1465 if (numDigitsAfterDecimal != -1) {
1466 format.setMaximumFractionDigits(numDigitsAfterDecimal);
1467 format.setMinimumFractionDigits(numDigitsAfterDecimal);
1468 }
1469
1470 if (includeLeadingDigit != -1) {
1471 if (includeLeadingDigit != 0) {
1472 // true
1473 format.setMinimumIntegerDigits(1);
1474 } else {
1475 format.setMinimumIntegerDigits(0);
1476 }
1477 }
1478
1479 if (useParensForNegativeNumbers != -1) {
1480 if (useParensForNegativeNumbers != 0) {
1481 DecimalFormat dformat = (DecimalFormat)format;
1482 dformat.setNegativePrefix("(");
1483 dformat.setNegativeSuffix(
1484 "" + dformat.getDecimalFormatSymbols().getPercent() +
1485 ")");
1486 } else {
1487 DecimalFormat dformat = (DecimalFormat)format;
1488 dformat.setNegativePrefix(
1489 "" + dformat.getDecimalFormatSymbols().getMinusSign());
1490 dformat.setNegativeSuffix(
1491 "" + dformat.getDecimalFormatSymbols().getPercent());
1492 }
1493 }
1494
1495 if (groupDigits != -1) {
1496 format.setGroupingUsed(groupDigits != 0);
1497 }
1498
1499 return format.format(expression);
1500
1501 }
1502
1503 // instr is already implemented in BuiltinFunTable... defer
1504
1505 // public Object inStrB(Object start, Object string1, Object string2, int
1506 // compare /* default BinaryCompare */)
1507
1508 // @FunctionName("InStr")
1509 // @Signature("InStr([start, ]string1, string2[, compare])")
1510 // @Description("Returns a Variant (Long) specifying the position of the first occurrence of one string within another.")
1511
1512 @FunctionName("InStrRev")
1513 @Signature("InstrRev(stringcheck, stringmatch[, start[, compare]])")
1514 @Description("Returns the position of an occurrence of one string within another, from the end of string.")
1515 public static int inStrRev(String stringCheck, String stringMatch) {
1516 return inStrRev(stringCheck, stringMatch, -1);
1517 }
1518
1519 @FunctionName("InStrRev")
1520 @Signature("InstrRev(stringcheck, stringmatch[, start[, compare]])")
1521 @Description("Returns the position of an occurrence of one string within another, from the end of string.")
1522 public static int inStrRev(String stringCheck, String stringMatch, int start /*
1523 * default
1524 * -1
1525 */) {
1526 return inStrRev(stringCheck, stringMatch, start, 0);
1527 }
1528
1529 @FunctionName("InStrRev")
1530 @Signature("InstrRev(stringcheck, stringmatch[, start[, compare]])")
1531 @Description("Returns the position of an occurrence of one string within another, from the end of string.")
1532 public static int inStrRev(String stringCheck, String stringMatch,
1533 int start /* default -1 */, int compare /* default BinaryCompare */) {
1534 // todo: implement binary vs. text compare
1535 if (start == 0 || start < -1) {
1536 throw new InvalidArgumentException(
1537 "start must be -1 or a location in the string to start");
1538 }
1539 if (start != -1) {
1540 return stringCheck.lastIndexOf(stringMatch, start - 1) + 1;
1541 } else {
1542 return stringCheck.lastIndexOf(stringMatch) + 1;
1543 }
1544 }
1545
1546 // public String join(Object sourceArray, Object delimiter)
1547
1548 @FunctionName("LCase")
1549 @Signature("LCase(string)")
1550 @Description("Returns a String that has been converted to lowercase.")
1551 public static String lCase(String string) {
1552 return string.toLowerCase();
1553 }
1554
1555 // public Object lCase$(Object string)
1556 // public String lTrim$(String string)
1557
1558 @FunctionName("LTrim")
1559 @Signature("LTrim(string)")
1560 @Description("Returns a Variant (String) containing a copy of a specified string without leading spaces.")
1561 public static String lTrim(String string) {
1562 int i = 0, n = string.length();
1563 while (i < n) {
1564 if (string.charAt(i) > ' ') {
1565 break;
1566 }
1567 i++;
1568 }
1569 return string.substring(i);
1570 }
1571
1572 // public String left$(String string, int length)
1573 // public String leftB$(String string, int length)
1574 // public Object leftB(Object string, int length)
1575
1576 @FunctionName("Left")
1577 @Signature("Left(string, length)")
1578 @Description("Returns a specified number of characters from the left side of a string.")
1579 public static String left(String string, int length) {
1580 final int stringLength = string.length();
1581 if (length >= stringLength) {
1582 return string;
1583 }
1584 return string.substring(0, length);
1585 }
1586
1587 // public Object lenB(Object expression)
1588
1589 // len is already implemented in BuiltinFunTable... defer
1590
1591 // @FunctionName("Len")
1592 // @Signature("Len(String)")
1593 // @Description("Returns a Long containing the number of characters in a
1594 // string.")
1595 // public static int len(String expression) {
1596 // return expression.length();
1597 // }
1598
1599 // public String mid$(String string, int start, Object length)
1600 // public String midB$(String string, int start, Object length)
1601 // public Object midB(Object string, int start, Object length)
1602
1603 @FunctionName("Mid")
1604 @Signature("Mid(value, beginIndex[, length])")
1605 @Description("Returns a specified number of characters from a string.")
1606 public static String mid(String value, int beginIndex) {
1607 // If we used 'value.length() - beginIndex' as the default value for
1608 // length, we'd have problems if beginIndex is huge;
1609 // so 'value.length()' looks like an overestimate - but will always
1610 // return the correct result.
1611 final int length = value.length();
1612 return mid(value, beginIndex, length);
1613 }
1614
1615 @FunctionName("Mid")
1616 @Signature("Mid(value, beginIndex[, length])")
1617 @Description("Returns a specified number of characters from a string.")
1618 public static String mid(String value, int beginIndex, int length) {
1619 if (beginIndex < 0) {
1620 throw new InvalidArgumentException("Invalid parameter. "
1621 + "Start parameter of Mid function can't " + "be negative");
1622 }
1623 if (length < 0) {
1624 throw new InvalidArgumentException("Invalid parameter. "
1625 + "Length parameter of Mid function can't " + "be negative");
1626 }
1627
1628 if (beginIndex >= value.length()) {
1629 return "";
1630 }
1631
1632 if (beginIndex != 0) {
1633 --beginIndex;
1634 }
1635 int endIndex = beginIndex + length;
1636 return endIndex >= value.length() ? value.substring(beginIndex) : value
1637 .substring(beginIndex, endIndex);
1638 }
1639
1640 @FunctionName("MonthName")
1641 @Signature("MonthName(month, abbreviate)")
1642 @Description("Returns a string indicating the specified month.")
1643 public static String monthName(int month, boolean abbreviate) {
1644 // VB months are 1-based, Java months are 0-based
1645 --month;
1646 return (abbreviate ? getDateFormatSymbols().getShortMonths()
1647 : getDateFormatSymbols().getMonths())[month];
1648 }
1649
1650 /**
1651 * Returns an instance of {@link DateFormatSymbols} for the current locale.
1652 *
1653 * <p>
1654 * Todo: inherit locale from connection.
1655 *
1656 * @return a DateFormatSymbols object
1657 */
1658 private static DateFormatSymbols getDateFormatSymbols() {
1659 // We would use DataFormatSymbols.getInstance(), but it is only
1660 // available from JDK 1.6 onwards.
1661 return DATE_FORMAT_SYMBOLS;
1662 }
1663
1664 // public String rTrim$(String string)
1665
1666 @FunctionName("RTrim")
1667 @Signature("RTrim(string)")
1668 @Description("Returns a Variant (String) containing a copy of a specified string without trailing spaces.")
1669 public static String rTrim(String string) {
1670 int i = string.length() - 1;
1671 while (i >= 0) {
1672 if (string.charAt(i) > ' ') {
1673 break;
1674 }
1675 i--;
1676 }
1677 return string.substring(0, i + 1);
1678 }
1679
1680 @FunctionName("Replace")
1681 @Signature("Replace(expression, find, replace[, start[, count[, compare]]])")
1682 @Description("Returns a string in which a specified substring has been replaced with another substring a specified number of times.")
1683 public static String replace(String expression, String find,
1684 String replace, int start, int count, int compare) {
1685 // compare is currently ignored
1686 Util.discard(compare);
1687 return _replace(expression, find, replace, start, count);
1688 }
1689
1690 @FunctionName("Replace")
1691 @Signature("Replace(expression, find, replace[, start[, count[, compare]]])")
1692 @Description("Returns a string in which a specified substring has been replaced with another substring a specified number of times.")
1693 public static String replace(String expression, String find,
1694 String replace, int start, int count) {
1695 return _replace(expression, find, replace, start, count);
1696 }
1697
1698 @FunctionName("Replace")
1699 @Signature("Replace(expression, find, replace[, start[, count[, compare]]])")
1700 @Description("Returns a string in which a specified substring has been replaced with another substring a specified number of times.")
1701 public static String replace(String expression, String find,
1702 String replace, int start) {
1703 return _replace(expression, find, replace, start, -1);
1704 }
1705
1706 @FunctionName("Replace")
1707 @Signature("Replace(expression, find, replace[, start[, count[, compare]]])")
1708 @Description("")
1709 public static String replace(String expression, String find, String replace) {
1710 // compare is currently ignored
1711 return _replace(expression, find, replace, 1, -1);
1712 }
1713
1714 private static String _replace(
1715 String expression,
1716 String find,
1717 String replace,
1718 int start /* default 1 */,
1719 int count /* default -1 */)
1720 {
1721 final StringBuilder buf = new StringBuilder(expression);
1722 int i = 0;
1723 int pos = start - 1;
1724 while (true) {
1725 if (i++ == count) {
1726 break;
1727 }
1728 final int j = buf.indexOf(find, pos);
1729 if (j == -1) {
1730 break;
1731 }
1732 buf.replace(j, j + find.length(), replace);
1733 pos = j + replace.length();
1734 }
1735 return buf.toString();
1736 }
1737
1738 // public String right$(String string, int length)
1739 // public String rightB$(String string, int length)
1740 // public Object rightB(Object string, int length)
1741
1742 @FunctionName("Right")
1743 @Signature("Right(string, length)")
1744 @Description("Returns a Variant (String) containing a specified number of characters from the right side of a string.")
1745 public static String right(String string, int length) {
1746 final int stringLength = string.length();
1747 if (length >= stringLength) {
1748 return string;
1749 }
1750 return string.substring(stringLength - length, stringLength);
1751 }
1752
1753 // public String space$(int number)
1754
1755 @FunctionName("Space")
1756 @Signature("Space(number)")
1757 @Description("Returns a Variant (String) consisting of the specified number of spaces.")
1758 public static String space(int number) {
1759 return string(number, ' ');
1760 }
1761
1762 // public Object split(String expression, Object delimiter, int limit /*
1763 // default -1 */, int compare /* default BinaryCompare */)
1764
1765 @FunctionName("StrComp")
1766 @Signature("StrComp(string1, string2[, compare])")
1767 @Description("Returns a Variant (Integer) indicating the result of a string comparison.")
1768 public static int strComp(String string1, String string2) {
1769 return strComp(string1, string2, 0);
1770 }
1771
1772 @FunctionName("StrComp")
1773 @Signature("StrComp(string1, string2[, compare])")
1774 @Description("Returns a Variant (Integer) indicating the result of a string comparison.")
1775 public static int strComp(String string1, String string2, int compare/*
1776 * default
1777 * BinaryCompare
1778 */) {
1779 // Note: compare is currently ignored
1780 // Wrapper already checked whether args are null
1781 assert string1 != null;
1782 assert string2 != null;
1783 return string1.compareTo(string2);
1784 }
1785
1786 // public Object strConv(Object string, StrConv conversion, int localeID)
1787
1788 @FunctionName("StrReverse")
1789 @Signature("StrReverse(string)")
1790 @Description("Returns a string in which the character order of a specified string is reversed.")
1791 public static String strReverse(String expression) {
1792 final char[] chars = expression.toCharArray();
1793 for (int i = 0, j = chars.length - 1; i < j; i++, j--) {
1794 char c = chars[i];
1795 chars[i] = chars[j];
1796 chars[j] = c;
1797 }
1798 return new String(chars);
1799 }
1800
1801 // public String string$(int number, Object character)
1802
1803 @FunctionName("String")
1804 @Signature("String(number, character)")
1805 @Description("")
1806 public static String string(int number, char character) {
1807 if (character == 0) {
1808 return "";
1809 }
1810 final char[] chars = new char[number];
1811 Arrays.fill(chars, (char) (character % 256));
1812 return new String(chars);
1813 }
1814
1815 // public String trim$(String string)
1816
1817 @FunctionName("Trim")
1818 @Signature("Trim(string)")
1819 @Description("Returns a Variant (String) containing a copy of a specified string without leading and trailing spaces.")
1820 public static String trim(String string) {
1821 // JDK has a method for trim, but not ltrim or rtrim
1822 return string.trim();
1823 }
1824
1825 // ucase is already implemented in BuiltinFunTable... defer
1826
1827 // public String uCase$(String string)
1828
1829 // @FunctionName("UCase")
1830 // @Signature("UCase(string)")
1831 // @Description("Returns a String that has been converted to uppercase.")
1832 // public String uCase(String string) {
1833 // return string.toUpperCase();
1834 // }
1835
1836 // TODO: should use connection's locale to determine first day of week,
1837 // not the JVM's default
1838
1839 @FunctionName("WeekdayName")
1840 @Signature("WeekdayName(weekday, abbreviate, firstdayofweek)")
1841 @Description("Returns a string indicating the specified day of the week.")
1842 public static String weekdayName(int weekday, boolean abbreviate,
1843 int firstDayOfWeek) {
1844 // Java and VB agree: SUNDAY = 1, ... SATURDAY = 7
1845 final Calendar calendar = Calendar.getInstance();
1846 if (firstDayOfWeek == 0) {
1847 firstDayOfWeek = calendar.getFirstDayOfWeek();
1848 }
1849 // compensate for start of week
1850 weekday += (firstDayOfWeek - 1);
1851 // bring into range 1..7
1852 weekday = (weekday - 1) % 7 + 1;
1853 if (weekday <= 0) {
1854 // negative numbers give negative modulo
1855 weekday += 7;
1856 }
1857 return (abbreviate ? getDateFormatSymbols().getShortWeekdays()
1858 : getDateFormatSymbols().getWeekdays())[weekday];
1859 }
1860
1861 // Misc
1862
1863 // public Object array(Object argList)
1864 // public String input$(int number, int fileNumber)
1865 // public String inputB$(int number, int fileNumber)
1866 // public Object inputB(int number, int fileNumber)
1867 // public Object input(int number, int fileNumber)
1868 // public void width(int fileNumber, int width)
1869
1870 // ~ Inner classes
1871
1872 private enum Interval {
1873 yyyy("Year", Calendar.YEAR), q("Quarter", -1), m("Month",
1874 Calendar.MONTH), y("Day of year", Calendar.DAY_OF_YEAR), d(
1875 "Day", Calendar.DAY_OF_MONTH), w("Weekday",
1876 Calendar.DAY_OF_WEEK), ww("Week", Calendar.WEEK_OF_YEAR), h(
1877 "Hour", Calendar.HOUR_OF_DAY), n("Minute", Calendar.MINUTE), s(
1878 "Second", Calendar.SECOND);
1879 private final int dateField;
1880
1881 Interval(String desc, int dateField) {
1882 Util.discard(desc);
1883 this.dateField = dateField;
1884 }
1885
1886 void add(Calendar calendar, int amount) {
1887 switch (this) {
1888 case q:
1889 calendar.add(Calendar.MONTH, amount * 3);
1890 break;
1891 default:
1892 calendar.add(dateField, amount);
1893 break;
1894 }
1895 }
1896
1897 Calendar floor(Calendar calendar) {
1898 Calendar calendar2 = Calendar.getInstance();
1899 calendar2.setTime(calendar.getTime());
1900 floorInplace(calendar2);
1901 return calendar2;
1902 }
1903
1904 private void floorInplace(Calendar calendar) {
1905 switch (this) {
1906 case yyyy:
1907 calendar.set(Calendar.DAY_OF_YEAR, 1);
1908 d.floorInplace(calendar);
1909 break;
1910 case q:
1911 int month = calendar.get(Calendar.MONTH);
1912 month -= month % 3;
1913 calendar.set(Calendar.MONTH, month);
1914 calendar.set(Calendar.DAY_OF_MONTH, 1);
1915 d.floorInplace(calendar);
1916 break;
1917 case m:
1918 calendar.set(Calendar.DAY_OF_MONTH, 1);
1919 d.floorInplace(calendar);
1920 break;
1921 case w:
1922 final int dow = calendar.get(Calendar.DAY_OF_WEEK);
1923 final int firstDayOfWeek = calendar.getFirstDayOfWeek();
1924 if (dow == firstDayOfWeek) {
1925 // nothing to do
1926 } else if (dow > firstDayOfWeek) {
1927 final int roll = firstDayOfWeek - dow;
1928 assert roll < 0;
1929 calendar.roll(Calendar.DAY_OF_WEEK, roll);
1930 } else {
1931 final int roll = firstDayOfWeek - dow - 7;
1932 assert roll < 0;
1933 calendar.roll(Calendar.DAY_OF_WEEK, roll);
1934 }
1935 d.floorInplace(calendar);
1936 break;
1937 case y:
1938 case d:
1939 calendar.set(Calendar.HOUR_OF_DAY, 0);
1940 calendar.set(Calendar.MINUTE, 0);
1941 calendar.set(Calendar.SECOND, 0);
1942 calendar.set(Calendar.MILLISECOND, 0);
1943 break;
1944 case h:
1945 calendar.set(Calendar.MINUTE, 0);
1946 calendar.set(Calendar.SECOND, 0);
1947 calendar.set(Calendar.MILLISECOND, 0);
1948 break;
1949 case n:
1950 calendar.set(Calendar.SECOND, 0);
1951 calendar.set(Calendar.MILLISECOND, 0);
1952 break;
1953 case s:
1954 calendar.set(Calendar.MILLISECOND, 0);
1955 break;
1956 }
1957 }
1958
1959 int diff(Calendar calendar1, Calendar calendar2, int firstDayOfWeek) {
1960 switch (this) {
1961 case q:
1962 return m.diff(calendar1, calendar2, firstDayOfWeek) / 3;
1963 default:
1964 return floor(calendar1).get(dateField)
1965 - floor(calendar2).get(dateField);
1966 }
1967 }
1968
1969 int datePart(Calendar calendar) {
1970 switch (this) {
1971 case q:
1972 return (m.datePart(calendar) + 2) / 3;
1973 case m:
1974 return calendar.get(dateField) + 1;
1975 case w:
1976 int dayOfWeek = calendar.get(dateField);
1977 dayOfWeek -= (calendar.getFirstDayOfWeek() - 1);
1978 dayOfWeek = dayOfWeek % 7;
1979 if (dayOfWeek <= 0) {
1980 dayOfWeek += 7;
1981 }
1982 return dayOfWeek;
1983 default:
1984 return calendar.get(dateField);
1985 }
1986 }
1987 }
1988
1989 private enum FirstWeekOfYear {
1990 vbUseSystem(0, "Use the NLS API setting."), vbFirstJan1(1,
1991 "Start with week in which January 1 occurs (default)."), vbFirstFourDays(
1992 2,
1993 "Start with the first week that has at least four days in the new year."), vbFirstFullWeek(
1994 3, "Start with first full week of the year.");
1995
1996 FirstWeekOfYear(int code, String desc) {
1997 assert code == ordinal();
1998 assert desc != null;
1999 }
2000
2001 void apply(Calendar calendar) {
2002 switch (this) {
2003 case vbUseSystem:
2004 break;
2005 case vbFirstJan1:
2006 calendar.setMinimalDaysInFirstWeek(1);
2007 break;
2008 case vbFirstFourDays:
2009 calendar.setMinimalDaysInFirstWeek(4);
2010 break;
2011 case vbFirstFullWeek:
2012 calendar.setMinimalDaysInFirstWeek(7);
2013 break;
2014 }
2015 }
2016 }
2017 }
2018
2019 // End Vba.java