DefaultFunctions.java

/*
Copyright (c) 2016 James Ahlborn

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

package com.healthmarketscience.jackcess.impl.expr;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;

import com.healthmarketscience.jackcess.expr.EvalContext;
import com.healthmarketscience.jackcess.expr.EvalException;
import com.healthmarketscience.jackcess.expr.Function;
import com.healthmarketscience.jackcess.expr.FunctionLookup;
import com.healthmarketscience.jackcess.expr.LocaleContext;
import com.healthmarketscience.jackcess.expr.NumericConfig;
import com.healthmarketscience.jackcess.expr.TemporalConfig;
import com.healthmarketscience.jackcess.expr.Value;
import com.healthmarketscience.jackcess.impl.DatabaseImpl;
import static com.healthmarketscience.jackcess.impl.expr.FunctionSupport.*;

/**
 *
 * @author James Ahlborn
 */
public class DefaultFunctions
{
  private static final Map<String,Function> FUNCS =
    new HashMap<String,Function>();

  static {
    // load all default functions
    DefaultTextFunctions.init();
    DefaultNumberFunctions.init();
    DefaultDateFunctions.init();
    DefaultFinancialFunctions.init();
  }

  public static final FunctionLookup LOOKUP = new FunctionLookup() {
    @Override
    public Function getFunction(String name) {
      return FUNCS.get(DatabaseImpl.toLookupName(name));
    }
  };

  private DefaultFunctions() {}


  public static final Function IIF = registerFunc(new Func3("IIf") {
    @Override
    protected Value eval3(EvalContext ctx,
                          Value param1, Value param2, Value param3) {
      // null is false
      return ((!param1.isNull() && param1.getAsBoolean(ctx)) ? param2 : param3);
    }
  });

  public static final Function HEX = registerStringFunc(new Func1NullIsNull("Hex") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      if(param1.getType().isString() &&
         (param1.getAsString(ctx).length() == 0)) {
        return ValueSupport.ZERO_VAL;
      }
      int lv = param1.getAsLongInt(ctx);
      return ValueSupport.toValue(Integer.toHexString(lv).toUpperCase());
    }
  });

  public static final Function NZ = registerFunc(new FuncVar("Nz", 1, 2) {
    @Override
    protected Value evalVar(EvalContext ctx, Value[] params) {
      Value param1 = params[0];
      if(!param1.isNull()) {
        return param1;
      }
      if(params.length > 1) {
        return params[1];
      }
      Value.Type resultType = ctx.getResultType();
      return (((resultType == null) || resultType.isString()) ?
              ValueSupport.EMPTY_STR_VAL : ValueSupport.ZERO_VAL);
    }
  });

  public static final Function CHOOSE = registerFunc(new FuncVar("Choose", 1, Integer.MAX_VALUE) {
    @Override
    protected Value evalVar(EvalContext ctx, Value[] params) {
      Value param1 = params[0];
      int idx = param1.getAsLongInt(ctx);
      if((idx < 1) || (idx >= params.length)) {
        return ValueSupport.NULL_VAL;
      }
      return params[idx];
    }
  });

  public static final Function SWITCH = registerFunc(new FuncVar("Switch") {
    @Override
    protected Value evalVar(EvalContext ctx, Value[] params) {
      if((params.length % 2) != 0) {
        throw new EvalException("Odd number of parameters");
      }
      for(int i = 0; i < params.length; i+=2) {
        if(params[i].getAsBoolean(ctx)) {
          return params[i + 1];
        }
      }
      return ValueSupport.NULL_VAL;
    }
  });

  public static final Function OCT = registerStringFunc(new Func1NullIsNull("Oct") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      if(param1.getType().isString() &&
         (param1.getAsString(ctx).length() == 0)) {
        return ValueSupport.ZERO_VAL;
      }
      int lv = param1.getAsLongInt(ctx);
      return ValueSupport.toValue(Integer.toOctalString(lv));
    }
  });

  public static final Function CBOOL = registerFunc(new Func1("CBool") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      boolean b = param1.getAsBoolean(ctx);
      return ValueSupport.toValue(b);
    }
  });

  public static final Function CBYTE = registerFunc(new Func1("CByte") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      int lv = param1.getAsLongInt(ctx);
      if((lv < 0) || (lv > 255)) {
        throw new EvalException("Byte code '" + lv + "' out of range ");
      }
      return ValueSupport.toValue(lv);
    }
  });

  public static final Function CCUR = registerFunc(new Func1("CCur") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      BigDecimal bd = param1.getAsBigDecimal(ctx);
      bd = bd.setScale(4, NumberFormatter.ROUND_MODE);
      return ValueSupport.toValue(bd);
    }
  });

  public static final Function CDATE = registerFunc(new Func1("CDate") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      return param1.getAsDateTimeValue(ctx);
    }
  });
  static {
    registerFunc("CVDate", CDATE);
  }

  public static final Function CDBL = registerFunc(new Func1("CDbl") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      Double dv = param1.getAsDouble(ctx);
      return ValueSupport.toValue(dv);
    }
  });

  public static final Function CDEC = registerFunc(new Func1("CDec") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      BigDecimal bd = param1.getAsBigDecimal(ctx);
      return ValueSupport.toValue(bd);
    }
  });

  public static final Function CINT = registerFunc(new Func1("CInt") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      int lv = param1.getAsLongInt(ctx);
      if((lv < Short.MIN_VALUE) || (lv > Short.MAX_VALUE)) {
        throw new EvalException("Int value '" + lv + "' out of range ");
      }
      return ValueSupport.toValue(lv);
    }
  });

  public static final Function CLNG = registerFunc(new Func1("CLng") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      int lv = param1.getAsLongInt(ctx);
      return ValueSupport.toValue(lv);
    }
  });

  public static final Function CSNG = registerFunc(new Func1("CSng") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      Double dv = param1.getAsDouble(ctx);
      if((dv < Float.MIN_VALUE) || (dv > Float.MAX_VALUE)) {
        throw new EvalException("Single value '" + dv + "' out of range ");
      }
      return ValueSupport.toValue(dv.floatValue());
    }
  });

  public static final Function CSTR = registerFunc(new Func1("CStr") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      return ValueSupport.toValue(param1.getAsString(ctx));
    }
  });

  public static final Function CVAR = registerFunc(new Func1("CVar") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      return param1;
    }
  });

  public static final Function ISNULL = registerFunc(new Func1("IsNull") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      return ValueSupport.toValue(param1.isNull());
    }
  });

  public static final Function ISDATE = registerFunc(new Func1("IsDate") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      if(param1.getType().isTemporal()) {
        return ValueSupport.TRUE_VAL;
      }

      // for the purposes of this method, a string literal should only
      // return true if it is explicitly a date/time, not if it is just a
      // number (even though casting a number string to a date/time works in
      // general)
      if(param1.getType().isString() &&
         !stringIsNumeric(ctx, param1) &&
         stringIsTemporal(ctx, param1)) {
        return ValueSupport.TRUE_VAL;
      }

      return ValueSupport.FALSE_VAL;
    }
  });

  public static final Function ISNUMERIC = registerFunc(new Func1("IsNumeric") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      if(param1.getType().isNumeric()) {
        return ValueSupport.TRUE_VAL;
      }

      // note, only a string can be considered numberic for this function,
      // even though a date/time can be cast to a number in general
      if(param1.getType().isString() && stringIsNumeric(ctx, param1)) {
        return ValueSupport.TRUE_VAL;
      }

      return ValueSupport.FALSE_VAL;
    }
  });

  public static final Function FORMATNUMBER = registerFunc(new FuncVar("FormatNumber", 1, 6) {
    @Override
    protected Value evalVar(EvalContext ctx, Value[] params) {
      return formatNumber(ctx, params, FormatUtil.NumPatternType.GENERAL);
    }
  });

  public static final Function FORMATPERCENT = registerFunc(new FuncVar("FormatPercent", 1, 6) {
    @Override
    protected Value evalVar(EvalContext ctx, Value[] params) {
      return formatNumber(ctx, params, FormatUtil.NumPatternType.PERCENT);
    }
  });

  public static final Function FORMATCURRENCY = registerFunc(new FuncVar("FormatCurrency", 1, 6) {
    @Override
    protected Value evalVar(EvalContext ctx, Value[] params) {
      return formatNumber(ctx, params, FormatUtil.NumPatternType.CURRENCY);
    }
  });

  public static final Function FORMATDATETIME = registerFunc(new FuncVar("FormatDateTime", 1, 2) {
    @Override
    protected Value evalVar(EvalContext ctx, Value[] params) {
      Value param1 = params[0];
      if(param1.isNull()) {
        return ValueSupport.NULL_VAL;
      }

      LocalDateTime ldt = param1.getAsLocalDateTime(ctx);

      int fmtType = getOptionalIntParam(ctx, params, 1, 0);
      TemporalConfig.Type tempType = null;
      switch(fmtType) {
      case 0:
        // vbGeneralDate
        Value.Type valType = ValueSupport.getDateTimeType(ldt);
        switch(valType) {
        case DATE:
          tempType = TemporalConfig.Type.SHORT_DATE;
          break;
        case TIME:
          tempType = TemporalConfig.Type.LONG_TIME;
          break;
        default:
          tempType = TemporalConfig.Type.GENERAL_DATE;
        }
        break;
      case 1:
        // vbLongDate
        tempType = TemporalConfig.Type.LONG_DATE;
        break;
      case 2:
        // vbShortDate
        tempType = TemporalConfig.Type.SHORT_DATE;
        break;
      case 3:
        // vbLongTime
        tempType = TemporalConfig.Type.LONG_TIME;
        break;
      case 4:
        // vbShortTime
        tempType = TemporalConfig.Type.SHORT_TIME;
        break;
      default:
        throw new EvalException("Unknown format " + fmtType);
      }

      DateTimeFormatter dtf = ctx.createDateFormatter(
          ctx.getTemporalConfig().getDateTimeFormat(tempType));
      return ValueSupport.toValue(dtf.format(ldt));
    }
  });

  public static final Function VARTYPE = registerFunc(new Func1("VarType") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      Value.Type type = param1.getType();
      int vType = 0;
      switch(type) {
      case NULL:
        // vbNull
        vType = 1;
        break;
      case STRING:
        // vbString
        vType = 8;
        break;
      case DATE:
      case TIME:
      case DATE_TIME:
        // vbDate
        vType = 7;
        break;
      case LONG:
        // vbLong
        vType = 3;
        break;
      case DOUBLE:
        // vbDouble
        vType = 5;
        break;
      case BIG_DEC:
        // vbDecimal
        vType = 14;
        break;
      default:
        throw new EvalException("Unknown type " + type);
      }
      return ValueSupport.toValue(vType);
    }
  });

  public static final Function TYPENAME = registerFunc(new Func1("TypeName") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {
      Value.Type type = param1.getType();
      String tName = null;
      switch(type) {
      case NULL:
        tName = "Null";
        break;
      case STRING:
        tName = "String";
        break;
      case DATE:
      case TIME:
      case DATE_TIME:
        tName = "Date";
        break;
      case LONG:
        tName = "Long";
        break;
      case DOUBLE:
        tName = "Double";
        break;
      case BIG_DEC:
        tName = "Decimal";
        break;
      default:
        throw new EvalException("Unknown type " + type);
      }
      return ValueSupport.toValue(tName);
    }
  });

  public static final Function VAL = registerStringFunc(new Func1NullIsNull("Val") {
    @Override
    protected Value eval1(EvalContext ctx, Value param1) {

      // strip all whitespace from string
      String str = ValueSupport.WHITESPACE_PAT.matcher(param1.getAsString(ctx))
        .replaceAll("");

      if(str.length() == 0) {
        return ValueSupport.ZERO_D_VAL;
      }

      Matcher m = null;

      if(str.charAt(0) == ValueSupport.NUMBER_BASE_PREFIX) {

        // see if we can parse as a radix format
        BigInteger bi = null;
        if((m = ValueSupport.HEX_PAT.matcher(str)).find()) {
          bi = ValueSupport.parseIntegerString(m.group(), 16);
        } else if((m = ValueSupport.OCTAL_PAT.matcher(str)).find()) {
          bi = ValueSupport.parseIntegerString(m.group(), 8);
        }

        if(bi != null) {
          // this function works differently than normal string to number
          // conversion.  it seems to coerce these values to a short/long int
          // depending on the size of the number (which creates
          // positive/negative values dependent on the value length)
          int iVal = ((bi.bitLength() <= 16) ? bi.shortValue() : bi.intValue());
          return ValueSupport.toValue((double)iVal);
        }

      } else {

        // parse as normal "decimal" number.
        if((m = ValueSupport.NUMBER_PAT.matcher(str)).find()) {
          BigDecimal bd = new BigDecimal(m.group());
          return ValueSupport.toValue(bd.doubleValue());
        }
      }

      return ValueSupport.ZERO_D_VAL;
    }
  });

  private static boolean stringIsNumeric(LocaleContext ctx, Value param) {
    return (maybeGetAsBigDecimal(ctx, param) != null);
  }

  static BigDecimal maybeGetAsBigDecimal(LocaleContext ctx, Value param) {
    try {
      return param.getAsBigDecimal(ctx);
    } catch(EvalException ignored) {
      // not a number
    }
    return null;
  }

  private static boolean stringIsTemporal(EvalContext ctx, Value param) {
    return (maybeGetAsDateTimeValue(ctx, param) != null);
  }

  static Value maybeGetAsDateTimeValue(LocaleContext ctx, Value param) {
    try {
      // see if we can coerce to date/time
      return param.getAsDateTimeValue(ctx);
    } catch(EvalException ignored) {
      // not a date/time
    }
    return null;
  }

  private static boolean getOptionalTriStateBoolean(
      EvalContext ctx, Value[] params, int idx, boolean defValue) {
    boolean bv = defValue;
    if(params.length > idx) {
      int val = params[idx].getAsLongInt(ctx);
      switch(val) {
      case 0:
        // vbFalse
        bv = false;
        break;
      case -1:
        // vbTrue
        bv = true;
        break;
      case -2:
        // vbUseDefault
        bv = defValue;
        break;
      default:
        throw new EvalException("Unsupported tri-state boolean value " + val);
      }
    }
    return bv;
  }

  private static Value formatNumber(
      EvalContext ctx, Value[] params, FormatUtil.NumPatternType numPatType) {

    Value param1 = params[0];
    if(param1.isNull()) {
      return ValueSupport.NULL_VAL;
    }

    NumericConfig cfg = ctx.getNumericConfig();
    int numDecDigits = getOptionalIntParam(
        ctx, params, 1, cfg.getNumDecimalDigits(), -1);
    boolean incLeadDigit = getOptionalTriStateBoolean(
        ctx, params, 2, cfg.includeLeadingDigit());
    boolean defNegParens = numPatType.useParensForNegatives(cfg);
    boolean negParens = getOptionalTriStateBoolean(
        ctx, params, 3, defNegParens);
    int defNumGroupDigits = cfg.getNumGroupingDigits();
    boolean groupDigits = getOptionalTriStateBoolean(
        ctx, params, 4, (defNumGroupDigits > 0));
    int numGroupDigits = (groupDigits ? defNumGroupDigits : 0);

    String fmtStr = FormatUtil.createNumberFormatPattern(
        numPatType, numDecDigits, incLeadDigit, negParens, numGroupDigits);

    DecimalFormat df = ctx.createDecimalFormat(fmtStr);

    return ValueSupport.toValue(df.format(param1.getAsBigDecimal(ctx)));
  }

  // https://www.techonthenet.com/access/functions/
  // https://support.office.com/en-us/article/Access-Functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83

  static Function registerFunc(Function func) {
    registerFunc(func.getName(), func);
    return func;
  }

  static Function registerStringFunc(Function func) {
    registerFunc(func.getName(), func);
    registerFunc(new StringFuncWrapper(func));
    return func;
  }

  private static void registerFunc(String fname, Function func) {
    String lookupFname = DatabaseImpl.toLookupName(fname);
    if(FUNCS.put(lookupFname, func) != null) {
      throw new IllegalStateException("Duplicate function " + fname);
    }
  }
}