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