From 45f64df5c56c518a9829f80cadf710f76e58e464 Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Mon, 29 Oct 2018 00:55:17 +0000 Subject: [PATCH] finish DateAdd function git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@1213 f203690c-595d-4dc9-a70b-905162fa7fd2 --- .../jackcess/expr/package-info.java | 2 +- .../impl/expr/DefaultDateFunctions.java | 2 - .../jackcess/impl/expr/DefaultFunctions.java | 39 +++++++++++++++++++ .../jackcess/impl/expr/StringValue.java | 20 +++------- .../jackcess/impl/expr/ValueSupport.java | 20 ++++++++-- .../impl/expr/DefaultFunctionsTest.java | 17 ++++++++ 6 files changed, 80 insertions(+), 20 deletions(-) diff --git a/src/main/java/com/healthmarketscience/jackcess/expr/package-info.java b/src/main/java/com/healthmarketscience/jackcess/expr/package-info.java index 0b58159..4db1b0d 100644 --- a/src/main/java/com/healthmarketscience/jackcess/expr/package-info.java +++ b/src/main/java/com/healthmarketscience/jackcess/expr/package-info.java @@ -132,7 +132,7 @@ limitations under the License. * FunctionSupported * DayY * Date Y - * DateAdd + * DateAddY * DateDiff * DatePartY * DateSerialY diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java index fe28aa7..5752d58 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java @@ -168,10 +168,8 @@ public class DefaultDateFunctions String intv = param1.getAsString(ctx).trim(); int val = param2.getAsLongInt(ctx); - int result = -1; Calendar cal = nonNullToCalendar(ctx, param3); - // FIXME if(intv.equalsIgnoreCase(INTV_YEAR)) { cal.add(Calendar.YEAR, val); } else if(intv.equalsIgnoreCase(INTV_QUARTER)) { diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java index 331a47e..ae4d268 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java @@ -17,8 +17,10 @@ limitations under the License. package com.healthmarketscience.jackcess.impl.expr; import java.math.BigDecimal; +import java.math.BigInteger; 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; @@ -348,6 +350,43 @@ public class DefaultFunctions } }); + // public static final Function VAL = registerStringFunc(new Func1NullIsNull("Val") { + // @Override + // protected Value eval1(EvalContext ctx, Value param1) { + // String str = param1.getAsString(ctx).trim(); + + // if(str.length() == 0) { + // return ValueSupport.ZERO_VAL; + // } + + // Matcher m = null; + + // if(str.charAt(0) == ValueSupport.NUMBER_BASE_PREFIX) { + // // see if we can parse as a radix format + // if((m = ValueSupport.HEX_PAT.matcher(str)).find()) { + // BigInteger bi = ValueSupport.parseIntegerString(m.group(), 16); + // // FIXME, what to do with large numbers? to double or decimal? + // return ValueSupport.toValue(bi.intValue()); + // } else if((m = ValueSupport.OCTAL_PAT.matcher(str)).find()) { + // BigInteger bi = ValueSupport.parseIntegerString(m.group(), 8); + // // FIXME, what to do with large numbers? to double or decimal? + // return ValueSupport.toValue(bi.intValue()); + // } + + // return ValueSupport.ZERO_VAL; + // } + + // // parse ase normal "decimal" number. + // // FIXME - leading '+'? exponent? + // if((m = ValueSupport.NUMBER_PAT.matcher(str)).find()) { + + // } + + // // return ValueSupport.toValue(Integer.toHexString(lv).toUpperCase()); + // return null; + // } + // }); + private static boolean stringIsNumeric(EvalContext ctx, Value param) { try { param.getAsBigDecimal(ctx); diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java index 12a5efb..19b22eb 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java @@ -19,7 +19,6 @@ package com.healthmarketscience.jackcess.impl.expr; import java.math.BigDecimal; import java.math.BigInteger; import java.text.DecimalFormatSymbols; -import java.util.regex.Pattern; import com.healthmarketscience.jackcess.expr.EvalException; import com.healthmarketscience.jackcess.expr.LocaleContext; @@ -34,13 +33,6 @@ public class StringValue extends BaseValue { private static final Object NOT_A_NUMBER = new Object(); - private static final char NUMBER_BASE_PREFIX = '&'; - private static final Pattern OCTAL_PAT = - Pattern.compile(NUMBER_BASE_PREFIX + "[oO][0-7]+"); - private static final Pattern HEX_PAT = - Pattern.compile(NUMBER_BASE_PREFIX + "[hH]\\p{XDigit}+"); - private static final char CANON_DEC_SEP = '.'; - private final String _val; private Object _num; @@ -119,7 +111,7 @@ public class StringValue extends BaseValue String tmpVal = _val.trim(); if(tmpVal.length() > 0) { - if(tmpVal.charAt(0) != NUMBER_BASE_PREFIX) { + if(tmpVal.charAt(0) != ValueSupport.NUMBER_BASE_PREFIX) { // convert to standard numeric support for parsing tmpVal = toCanonicalNumberFormat(ctx, tmpVal); _num = ValueSupport.normalize(new BigDecimal(tmpVal)); @@ -127,9 +119,9 @@ public class StringValue extends BaseValue } // parse as hex/octal symbolic value - if(HEX_PAT.matcher(tmpVal).matches()) { + if(ValueSupport.HEX_PAT.matcher(tmpVal).matches()) { return parseIntegerString(tmpVal, 16); - } else if(OCTAL_PAT.matcher(tmpVal).matches()) { + } else if(ValueSupport.OCTAL_PAT.matcher(tmpVal).matches()) { return parseIntegerString(tmpVal, 8); } @@ -144,7 +136,7 @@ public class StringValue extends BaseValue } private BigDecimal parseIntegerString(String tmpVal, int radix) { - _num = new BigDecimal(new BigInteger(tmpVal.substring(2), radix)); + _num = new BigDecimal(ValueSupport.parseIntegerString(tmpVal, radix)); return (BigDecimal)_num; } @@ -158,8 +150,8 @@ public class StringValue extends BaseValue tmpVal = StringUtils.remove(tmpVal, groupSepChar); char decSepChar = syms.getDecimalSeparator(); - if((decSepChar != CANON_DEC_SEP) && (tmpVal.indexOf(decSepChar) >= 0)) { - tmpVal = tmpVal.replace(decSepChar, CANON_DEC_SEP); + if((decSepChar != ValueSupport.CANON_DEC_SEP) && (tmpVal.indexOf(decSepChar) >= 0)) { + tmpVal = tmpVal.replace(decSepChar, ValueSupport.CANON_DEC_SEP); } return tmpVal; diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/ValueSupport.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/ValueSupport.java index 83f88b8..e2a7173 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/ValueSupport.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/ValueSupport.java @@ -17,9 +17,11 @@ limitations under the License. package com.healthmarketscience.jackcess.impl.expr; import java.math.BigDecimal; +import java.math.BigInteger; import java.text.DateFormat; import java.util.Calendar; import java.util.Date; +import java.util.regex.Pattern; import com.healthmarketscience.jackcess.expr.EvalException; import com.healthmarketscience.jackcess.expr.LocaleContext; @@ -52,6 +54,14 @@ public class ValueSupport public static final Value NEG_ONE_VAL = TRUE_VAL; public static final Value ONE_VAL = new LongValue(1); + static final char NUMBER_BASE_PREFIX = '&'; + static final Pattern OCTAL_PAT = + Pattern.compile("^" + NUMBER_BASE_PREFIX + "[oO][0-7]+"); + static final Pattern HEX_PAT = + Pattern.compile("^" + NUMBER_BASE_PREFIX + "[hH]\\p{XDigit}+"); + static final char CANON_DEC_SEP = '.'; + static final Pattern NUMBER_PAT = Pattern.compile("^[-+]?[0-9]*[.]?[0-9]*"); + private ValueSupport() {} public static Value toValue(boolean b) { @@ -93,14 +103,14 @@ public class ValueSupport } public static Value toValue(Calendar cal) { - boolean hasTime = ((cal.get(Calendar.HOUR) != 0) || + boolean hasTime = ((cal.get(Calendar.HOUR_OF_DAY) != 0) || (cal.get(Calendar.MINUTE) != 0) || - (cal.get(Calendar.SECOND) == 0)); + (cal.get(Calendar.SECOND) != 0)); boolean hasDate = ((cal.get(Calendar.YEAR) != ExpressionTokenizer.BASE_DATE_YEAR) || ((cal.get(Calendar.MONTH) + 1) != ExpressionTokenizer.BASE_DATE_MONTH) || - (cal.get(Calendar.DAY_OF_MONTH) == ExpressionTokenizer.BASE_DATE_DAY)); + (cal.get(Calendar.DAY_OF_MONTH) != ExpressionTokenizer.BASE_DATE_DAY)); Value.Type type = (hasDate ? (hasTime ? Value.Type.DATE_TIME : Value.Type.DATE) : @@ -148,4 +158,8 @@ public class ValueSupport } return bd; } + + static BigInteger parseIntegerString(String val, int radix) { + return new BigInteger(val.substring(2), radix); + } } diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java index c3f5f68..5f0a045 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java @@ -306,6 +306,23 @@ public class DefaultFunctionsTest extends TestCase assertEquals(5, eval("=DatePart('h',#11/22/2003 5:45:13 AM#)")); assertEquals(45, eval("=DatePart('n',#11/22/2003 5:45:13 AM#)")); assertEquals(13, eval("=DatePart('s',#11/22/2003 5:45:13 AM#)")); + + assertEquals("11/22/2005 5:45:13 AM", eval("CStr(DateAdd('yyyy',2,#11/22/2003 5:45:13 AM#))")); + assertEquals("2/22/2004 5:45:13 AM", eval("CStr(DateAdd('q',1,#11/22/2003 5:45:13 AM#))")); + assertEquals("1/22/2004 5:45:13 AM", eval("CStr(DateAdd('m',2,#11/22/2003 5:45:13 AM#))")); + assertEquals("12/12/2003 5:45:13 AM", eval("CStr(DateAdd('d',20,#11/22/2003 5:45:13 AM#))")); + assertEquals("12/12/2003 5:45:13 AM", eval("CStr(DateAdd('w',20,#11/22/2003 5:45:13 AM#))")); + assertEquals("12/12/2003 5:45:13 AM", eval("CStr(DateAdd('y',20,#11/22/2003 5:45:13 AM#))")); + assertEquals("12/27/2003 5:45:13 AM", eval("CStr(DateAdd('ww',5,#11/22/2003 5:45:13 AM#))")); + assertEquals("11/22/2003 3:45:13 PM", eval("CStr(DateAdd('h',10,#11/22/2003 5:45:13 AM#))")); + assertEquals("11/22/2003 6:19:13 AM", eval("CStr(DateAdd('n',34,#11/22/2003 5:45:13 AM#))")); + assertEquals("11/22/2003 5:46:27 AM", eval("CStr(DateAdd('s',74,#11/22/2003 5:45:13 AM#))")); + + assertEquals("12/12/2003", eval("CStr(DateAdd('d',20,#11/22/2003#))")); + assertEquals("11/22/2003 10:00:00 AM", eval("CStr(DateAdd('h',10,#11/22/2003#))")); + assertEquals("11/23/2003", eval("CStr(DateAdd('h',24,#11/22/2003#))")); + assertEquals("3:45:13 PM", eval("CStr(DateAdd('h',10,#5:45:13 AM#))")); + assertEquals("12/31/1899 11:45:13 AM", eval("CStr(DateAdd('h',30,#5:45:13 AM#))")); } public void testFinancialFuncs() throws Exception -- 2.39.5