View Javadoc
1   /*
2   Copyright (c) 2016 James Ahlborn
3   
4   Licensed under the Apache License, Version 2.0 (the "License");
5   you may not use this file except in compliance with the License.
6   You may obtain a copy of the License at
7   
8       http://www.apache.org/licenses/LICENSE-2.0
9   
10  Unless required by applicable law or agreed to in writing, software
11  distributed under the License is distributed on an "AS IS" BASIS,
12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  See the License for the specific language governing permissions and
14  limitations under the License.
15  */
16  
17  package com.healthmarketscience.jackcess.impl.expr;
18  
19  import java.math.BigDecimal;
20  import java.math.BigInteger;
21  import java.text.DecimalFormat;
22  import java.time.LocalDateTime;
23  import java.time.format.DateTimeFormatter;
24  import java.util.HashMap;
25  import java.util.Map;
26  import java.util.regex.Matcher;
27  
28  import com.healthmarketscience.jackcess.expr.EvalContext;
29  import com.healthmarketscience.jackcess.expr.EvalException;
30  import com.healthmarketscience.jackcess.expr.Function;
31  import com.healthmarketscience.jackcess.expr.FunctionLookup;
32  import com.healthmarketscience.jackcess.expr.LocaleContext;
33  import com.healthmarketscience.jackcess.expr.NumericConfig;
34  import com.healthmarketscience.jackcess.expr.TemporalConfig;
35  import com.healthmarketscience.jackcess.expr.Value;
36  import com.healthmarketscience.jackcess.impl.DatabaseImpl;
37  import static com.healthmarketscience.jackcess.impl.expr.FunctionSupport.*;
38  
39  /**
40   *
41   * @author James Ahlborn
42   */
43  public class DefaultFunctions
44  {
45    private static final Map<String,Function> FUNCS =
46      new HashMap<String,Function>();
47  
48    static {
49      // load all default functions
50      DefaultTextFunctions.init();
51      DefaultNumberFunctions.init();
52      DefaultDateFunctions.init();
53      DefaultFinancialFunctions.init();
54    }
55  
56    public static final FunctionLookupexpr/FunctionLookup.html#FunctionLookup">FunctionLookup LOOKUP = new FunctionLookup() {
57      @Override
58      public Function getFunction(String name) {
59        return FUNCS.get(DatabaseImpl.toLookupName(name));
60      }
61    };
62  
63    private DefaultFunctions() {}
64  
65  
66    public static final Function IIF = registerFunc(new Func3("IIf") {
67      @Override
68      protected Value eval3(EvalContext ctx,
69                            Valuef="../../../../../com/healthmarketscience/jackcess/expr/Value.html#Value">Valuef="../../../../../com/healthmarketscience/jackcess/expr/Value.html#Value">Value param1, Valuef="../../../../../com/healthmarketscience/jackcess/expr/Value.html#Value">Value param2, Value param3) {
70        // null is false
71        return ((!param1.isNull() && param1.getAsBoolean(ctx)) ? param2 : param3);
72      }
73    });
74  
75    public static final Function HEX = registerStringFunc(new Func1NullIsNull("Hex") {
76      @Override
77      protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
78        if(param1.getType().isString() &&
79           (param1.getAsString(ctx).length() == 0)) {
80          return ValueSupport.ZERO_VAL;
81        }
82        int lv = param1.getAsLongInt(ctx);
83        return ValueSupport.toValue(Integer.toHexString(lv).toUpperCase());
84      }
85    });
86  
87    public static final Function NZ = registerFunc(new FuncVar("Nz", 1, 2) {
88      @Override
89      protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
90        Value param1 = params[0];
91        if(!param1.isNull()) {
92          return param1;
93        }
94        if(params.length > 1) {
95          return params[1];
96        }
97        Value.Type resultType = ctx.getResultType();
98        return (((resultType == null) || resultType.isString()) ?
99                ValueSupport.EMPTY_STR_VAL : ValueSupport.ZERO_VAL);
100     }
101   });
102 
103   public static final Function CHOOSE = registerFunc(new FuncVar("Choose", 1, Integer.MAX_VALUE) {
104     @Override
105     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
106       Value param1 = params[0];
107       int idx = param1.getAsLongInt(ctx);
108       if((idx < 1) || (idx >= params.length)) {
109         return ValueSupport.NULL_VAL;
110       }
111       return params[idx];
112     }
113   });
114 
115   public static final Function SWITCH = registerFunc(new FuncVar("Switch") {
116     @Override
117     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
118       if((params.length % 2) != 0) {
119         throw new EvalException("Odd number of parameters");
120       }
121       for(int i = 0; i < params.length; i+=2) {
122         if(params[i].getAsBoolean(ctx)) {
123           return params[i + 1];
124         }
125       }
126       return ValueSupport.NULL_VAL;
127     }
128   });
129 
130   public static final Function OCT = registerStringFunc(new Func1NullIsNull("Oct") {
131     @Override
132     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
133       if(param1.getType().isString() &&
134          (param1.getAsString(ctx).length() == 0)) {
135         return ValueSupport.ZERO_VAL;
136       }
137       int lv = param1.getAsLongInt(ctx);
138       return ValueSupport.toValue(Integer.toOctalString(lv));
139     }
140   });
141 
142   public static final Function CBOOL = registerFunc(new Func1("CBool") {
143     @Override
144     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
145       boolean b = param1.getAsBoolean(ctx);
146       return ValueSupport.toValue(b);
147     }
148   });
149 
150   public static final Function CBYTE = registerFunc(new Func1("CByte") {
151     @Override
152     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
153       int lv = param1.getAsLongInt(ctx);
154       if((lv < 0) || (lv > 255)) {
155         throw new EvalException("Byte code '" + lv + "' out of range ");
156       }
157       return ValueSupport.toValue(lv);
158     }
159   });
160 
161   public static final Function CCUR = registerFunc(new Func1("CCur") {
162     @Override
163     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
164       BigDecimal bd = param1.getAsBigDecimal(ctx);
165       bd = bd.setScale(4, NumberFormatter.ROUND_MODE);
166       return ValueSupport.toValue(bd);
167     }
168   });
169 
170   public static final Function CDATE = registerFunc(new Func1("CDate") {
171     @Override
172     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
173       return param1.getAsDateTimeValue(ctx);
174     }
175   });
176   static {
177     registerFunc("CVDate", CDATE);
178   }
179 
180   public static final Function CDBL = registerFunc(new Func1("CDbl") {
181     @Override
182     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
183       Double dv = param1.getAsDouble(ctx);
184       return ValueSupport.toValue(dv);
185     }
186   });
187 
188   public static final Function CDEC = registerFunc(new Func1("CDec") {
189     @Override
190     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
191       BigDecimal bd = param1.getAsBigDecimal(ctx);
192       return ValueSupport.toValue(bd);
193     }
194   });
195 
196   public static final Function CINT = registerFunc(new Func1("CInt") {
197     @Override
198     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
199       int lv = param1.getAsLongInt(ctx);
200       if((lv < Short.MIN_VALUE) || (lv > Short.MAX_VALUE)) {
201         throw new EvalException("Int value '" + lv + "' out of range ");
202       }
203       return ValueSupport.toValue(lv);
204     }
205   });
206 
207   public static final Function CLNG = registerFunc(new Func1("CLng") {
208     @Override
209     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
210       int lv = param1.getAsLongInt(ctx);
211       return ValueSupport.toValue(lv);
212     }
213   });
214 
215   public static final Function CSNG = registerFunc(new Func1("CSng") {
216     @Override
217     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
218       Double dv = param1.getAsDouble(ctx);
219       if((dv < Float.MIN_VALUE) || (dv > Float.MAX_VALUE)) {
220         throw new EvalException("Single value '" + dv + "' out of range ");
221       }
222       return ValueSupport.toValue(dv.floatValue());
223     }
224   });
225 
226   public static final Function CSTR = registerFunc(new Func1("CStr") {
227     @Override
228     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
229       return ValueSupport.toValue(param1.getAsString(ctx));
230     }
231   });
232 
233   public static final Function CVAR = registerFunc(new Func1("CVar") {
234     @Override
235     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
236       return param1;
237     }
238   });
239 
240   public static final Function ISNULL = registerFunc(new Func1("IsNull") {
241     @Override
242     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
243       return ValueSupport.toValue(param1.isNull());
244     }
245   });
246 
247   public static final Function ISDATE = registerFunc(new Func1("IsDate") {
248     @Override
249     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
250       if(param1.getType().isTemporal()) {
251         return ValueSupport.TRUE_VAL;
252       }
253 
254       // for the purposes of this method, a string literal should only
255       // return true if it is explicitly a date/time, not if it is just a
256       // number (even though casting a number string to a date/time works in
257       // general)
258       if(param1.getType().isString() &&
259          !stringIsNumeric(ctx, param1) &&
260          stringIsTemporal(ctx, param1)) {
261         return ValueSupport.TRUE_VAL;
262       }
263 
264       return ValueSupport.FALSE_VAL;
265     }
266   });
267 
268   public static final Function ISNUMERIC = registerFunc(new Func1("IsNumeric") {
269     @Override
270     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
271       if(param1.getType().isNumeric()) {
272         return ValueSupport.TRUE_VAL;
273       }
274 
275       // note, only a string can be considered numberic for this function,
276       // even though a date/time can be cast to a number in general
277       if(param1.getType().isString() && stringIsNumeric(ctx, param1)) {
278         return ValueSupport.TRUE_VAL;
279       }
280 
281       return ValueSupport.FALSE_VAL;
282     }
283   });
284 
285   public static final Function FORMATNUMBER = registerFunc(new FuncVar("FormatNumber", 1, 6) {
286     @Override
287     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
288       return formatNumber(ctx, params, FormatUtil.NumPatternType.GENERAL);
289     }
290   });
291 
292   public static final Function FORMATPERCENT = registerFunc(new FuncVar("FormatPercent", 1, 6) {
293     @Override
294     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
295       return formatNumber(ctx, params, FormatUtil.NumPatternType.PERCENT);
296     }
297   });
298 
299   public static final Function FORMATCURRENCY = registerFunc(new FuncVar("FormatCurrency", 1, 6) {
300     @Override
301     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
302       return formatNumber(ctx, params, FormatUtil.NumPatternType.CURRENCY);
303     }
304   });
305 
306   public static final Function FORMATDATETIME = registerFunc(new FuncVar("FormatDateTime", 1, 2) {
307     @Override
308     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
309       Value param1 = params[0];
310       if(param1.isNull()) {
311         return ValueSupport.NULL_VAL;
312       }
313 
314       LocalDateTime ldt = param1.getAsLocalDateTime(ctx);
315 
316       int fmtType = getOptionalIntParam(ctx, params, 1, 0);
317       TemporalConfig.Type tempType = null;
318       switch(fmtType) {
319       case 0:
320         // vbGeneralDate
321         Value.Type valType = ValueSupport.getDateTimeType(ldt);
322         switch(valType) {
323         case DATE:
324           tempType = TemporalConfig.Type.SHORT_DATE;
325           break;
326         case TIME:
327           tempType = TemporalConfig.Type.LONG_TIME;
328           break;
329         default:
330           tempType = TemporalConfig.Type.GENERAL_DATE;
331         }
332         break;
333       case 1:
334         // vbLongDate
335         tempType = TemporalConfig.Type.LONG_DATE;
336         break;
337       case 2:
338         // vbShortDate
339         tempType = TemporalConfig.Type.SHORT_DATE;
340         break;
341       case 3:
342         // vbLongTime
343         tempType = TemporalConfig.Type.LONG_TIME;
344         break;
345       case 4:
346         // vbShortTime
347         tempType = TemporalConfig.Type.SHORT_TIME;
348         break;
349       default:
350         throw new EvalException("Unknown format " + fmtType);
351       }
352 
353       DateTimeFormatter dtf = ctx.createDateFormatter(
354           ctx.getTemporalConfig().getDateTimeFormat(tempType));
355       return ValueSupport.toValue(dtf.format(ldt));
356     }
357   });
358 
359   public static final Function VARTYPE = registerFunc(new Func1("VarType") {
360     @Override
361     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
362       Value.Type type = param1.getType();
363       int vType = 0;
364       switch(type) {
365       case NULL:
366         // vbNull
367         vType = 1;
368         break;
369       case STRING:
370         // vbString
371         vType = 8;
372         break;
373       case DATE:
374       case TIME:
375       case DATE_TIME:
376         // vbDate
377         vType = 7;
378         break;
379       case LONG:
380         // vbLong
381         vType = 3;
382         break;
383       case DOUBLE:
384         // vbDouble
385         vType = 5;
386         break;
387       case BIG_DEC:
388         // vbDecimal
389         vType = 14;
390         break;
391       default:
392         throw new EvalException("Unknown type " + type);
393       }
394       return ValueSupport.toValue(vType);
395     }
396   });
397 
398   public static final Function TYPENAME = registerFunc(new Func1("TypeName") {
399     @Override
400     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
401       Value.Type type = param1.getType();
402       String tName = null;
403       switch(type) {
404       case NULL:
405         tName = "Null";
406         break;
407       case STRING:
408         tName = "String";
409         break;
410       case DATE:
411       case TIME:
412       case DATE_TIME:
413         tName = "Date";
414         break;
415       case LONG:
416         tName = "Long";
417         break;
418       case DOUBLE:
419         tName = "Double";
420         break;
421       case BIG_DEC:
422         tName = "Decimal";
423         break;
424       default:
425         throw new EvalException("Unknown type " + type);
426       }
427       return ValueSupport.toValue(tName);
428     }
429   });
430 
431   public static final Function VAL = registerStringFunc(new Func1NullIsNull("Val") {
432     @Override
433     protected Value../com/healthmarketscience/jackcess/expr/Value.html#Value">Value eval1(EvalContext ctx, Value param1) {
434 
435       // strip all whitespace from string
436       String str = ValueSupport.WHITESPACE_PAT.matcher(param1.getAsString(ctx))
437         .replaceAll("");
438 
439       if(str.length() == 0) {
440         return ValueSupport.ZERO_D_VAL;
441       }
442 
443       Matcher m = null;
444 
445       if(str.charAt(0) == ValueSupport.NUMBER_BASE_PREFIX) {
446 
447         // see if we can parse as a radix format
448         BigInteger bi = null;
449         if((m = ValueSupport.HEX_PAT.matcher(str)).find()) {
450           bi = ValueSupport.parseIntegerString(m.group(), 16);
451         } else if((m = ValueSupport.OCTAL_PAT.matcher(str)).find()) {
452           bi = ValueSupport.parseIntegerString(m.group(), 8);
453         }
454 
455         if(bi != null) {
456           // this function works differently than normal string to number
457           // conversion.  it seems to coerce these values to a short/long int
458           // depending on the size of the number (which creates
459           // positive/negative values dependent on the value length)
460           int iVal = ((bi.bitLength() <= 16) ? bi.shortValue() : bi.intValue());
461           return ValueSupport.toValue((double)iVal);
462         }
463 
464       } else {
465 
466         // parse as normal "decimal" number.
467         if((m = ValueSupport.NUMBER_PAT.matcher(str)).find()) {
468           BigDecimal bd = new BigDecimal(m.group());
469           return ValueSupport.toValue(bd.doubleValue());
470         }
471       }
472 
473       return ValueSupport.ZERO_D_VAL;
474     }
475   });
476 
477   private static boolean stringIsNumeric(LocaleContext ctx, Value param) {
478     return (maybeGetAsBigDecimal(ctx, param) != null);
479   }
480 
481   static BigDecimal maybeGetAsBigDecimal(LocaleContext ctx, Value param) {
482     try {
483       return param.getAsBigDecimal(ctx);
484     } catch(EvalException ignored) {
485       // not a number
486     }
487     return null;
488   }
489 
490   private static boolean stringIsTemporal(EvalContext ctx, Value param) {
491     return (maybeGetAsDateTimeValue(ctx, param) != null);
492   }
493 
494   static Valuecience/jackcess/expr/Value.html#Value">Value maybeGetAsDateTimeValue(LocaleContext ctx, Value param) {
495     try {
496       // see if we can coerce to date/time
497       return param.getAsDateTimeValue(ctx);
498     } catch(EvalException ignored) {
499       // not a date/time
500     }
501     return null;
502   }
503 
504   private static boolean getOptionalTriStateBoolean(
505       EvalContext ctx, Value[] params, int idx, boolean defValue) {
506     boolean bv = defValue;
507     if(params.length > idx) {
508       int val = params[idx].getAsLongInt(ctx);
509       switch(val) {
510       case 0:
511         // vbFalse
512         bv = false;
513         break;
514       case -1:
515         // vbTrue
516         bv = true;
517         break;
518       case -2:
519         // vbUseDefault
520         bv = defValue;
521         break;
522       default:
523         throw new EvalException("Unsupported tri-state boolean value " + val);
524       }
525     }
526     return bv;
527   }
528 
529   private static Value formatNumber(
530       EvalContext ctx, Value[] params, FormatUtil.NumPatternType numPatType) {
531 
532     Value param1 = params[0];
533     if(param1.isNull()) {
534       return ValueSupport.NULL_VAL;
535     }
536 
537     NumericConfig cfg = ctx.getNumericConfig();
538     int numDecDigits = getOptionalIntParam(
539         ctx, params, 1, cfg.getNumDecimalDigits(), -1);
540     boolean incLeadDigit = getOptionalTriStateBoolean(
541         ctx, params, 2, cfg.includeLeadingDigit());
542     boolean defNegParens = numPatType.useParensForNegatives(cfg);
543     boolean negParens = getOptionalTriStateBoolean(
544         ctx, params, 3, defNegParens);
545     int defNumGroupDigits = cfg.getNumGroupingDigits();
546     boolean groupDigits = getOptionalTriStateBoolean(
547         ctx, params, 4, (defNumGroupDigits > 0));
548     int numGroupDigits = (groupDigits ? defNumGroupDigits : 0);
549 
550     String fmtStr = FormatUtil.createNumberFormatPattern(
551         numPatType, numDecDigits, incLeadDigit, negParens, numGroupDigits);
552 
553     DecimalFormat df = ctx.createDecimalFormat(fmtStr);
554 
555     return ValueSupport.toValue(df.format(param1.getAsBigDecimal(ctx)));
556   }
557 
558   // https://www.techonthenet.com/access/functions/
559   // https://support.office.com/en-us/article/Access-Functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83
560 
561   static Function/../../../com/healthmarketscience/jackcess/expr/Function.html#Function">Function registerFunc(Function func) {
562     registerFunc(func.getName(), func);
563     return func;
564   }
565 
566   static Function/../com/healthmarketscience/jackcess/expr/Function.html#Function">Function registerStringFunc(Function func) {
567     registerFunc(func.getName(), func);
568     registerFunc(new StringFuncWrapper(func));
569     return func;
570   }
571 
572   private static void registerFunc(String fname, Function func) {
573     String lookupFname = DatabaseImpl.toLookupName(fname);
574     if(FUNCS.put(lookupFname, func) != null) {
575       throw new IllegalStateException("Duplicate function " + fname);
576     }
577   }
578 }