From ac6811e50d1f32a4eb14785282099c316dcfc7fd Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Tue, 13 Nov 2018 04:59:29 +0000 Subject: [PATCH] implement FormatDateTime function git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@1221 f203690c-595d-4dc9-a70b-905162fa7fd2 --- .../jackcess/expr/TemporalConfig.java | 82 ++++++++++++++++--- .../jackcess/expr/package-info.java | 8 +- .../jackcess/impl/expr/DefaultFunctions.java | 67 +++++++++++++-- .../jackcess/impl/expr/ValueSupport.java | 12 +-- .../jackcess/PropertyExpressionTest.java | 1 + .../impl/expr/DefaultFunctionsTest.java | 8 ++ 6 files changed, 152 insertions(+), 26 deletions(-) diff --git a/src/main/java/com/healthmarketscience/jackcess/expr/TemporalConfig.java b/src/main/java/com/healthmarketscience/jackcess/expr/TemporalConfig.java index b7de667..db7806f 100644 --- a/src/main/java/com/healthmarketscience/jackcess/expr/TemporalConfig.java +++ b/src/main/java/com/healthmarketscience/jackcess/expr/TemporalConfig.java @@ -32,28 +32,42 @@ public class TemporalConfig { public static final String US_DATE_FORMAT = "M/d/yyyy"; public static final String US_DATE_IMPLICIT_YEAR_FORMAT = "M/d"; - public static final String US_TIME_FORMAT_12 = "h:mm:ss a"; - public static final String US_TIME_FORMAT_24 = "H:mm:ss"; + public static final String US_TIME_FORMAT_12_FORMAT = "h:mm:ss a"; + public static final String US_TIME_FORMAT_24_FORMAT = "H:mm:ss"; + public static final String US_LONG_DATE_FORMAT = "EEEE, MMMM dd, yyyy"; + + public static final String MEDIUM_DATE_FORMAT = "dd-MMM-yy"; + public static final String MEDIUM_TIME_FORMAT = "hh:mm a"; + public static final String SHORT_TIME_FORMAT = "HH:mm"; /** default implementation which is configured for the US locale */ public static final TemporalConfig US_TEMPORAL_CONFIG = new TemporalConfig( - US_DATE_FORMAT, US_DATE_IMPLICIT_YEAR_FORMAT, - US_TIME_FORMAT_12, US_TIME_FORMAT_24, '/', ':', Locale.US); + US_DATE_FORMAT, US_DATE_IMPLICIT_YEAR_FORMAT, US_LONG_DATE_FORMAT, + US_TIME_FORMAT_12_FORMAT, US_TIME_FORMAT_24_FORMAT, '/', ':', Locale.US); public enum Type { - DATE, TIME, DATE_TIME, TIME_12, TIME_24, DATE_TIME_12, DATE_TIME_24; + DATE, TIME, DATE_TIME, TIME_12, TIME_24, DATE_TIME_12, DATE_TIME_24, + GENERAL_DATE, LONG_DATE, MEDIUM_DATE, SHORT_DATE, + LONG_TIME, MEDIUM_TIME, SHORT_TIME; public Type getDefaultType() { switch(this) { case DATE: + case LONG_DATE: + case MEDIUM_DATE: + case SHORT_DATE: return DATE; case TIME: case TIME_12: case TIME_24: + case LONG_TIME: + case MEDIUM_TIME: + case SHORT_TIME: return TIME; case DATE_TIME: case DATE_TIME_12: case DATE_TIME_24: + case GENERAL_DATE: return DATE_TIME; default: throw new RuntimeException("invalid type " + this); @@ -63,41 +77,55 @@ public class TemporalConfig public Value.Type getValueType() { switch(this) { case DATE: + case LONG_DATE: + case MEDIUM_DATE: + case SHORT_DATE: return Value.Type.DATE; case TIME: case TIME_12: case TIME_24: + case LONG_TIME: + case MEDIUM_TIME: + case SHORT_TIME: return Value.Type.TIME; case DATE_TIME: case DATE_TIME_12: case DATE_TIME_24: + case GENERAL_DATE: return Value.Type.DATE_TIME; default: throw new RuntimeException("invalid type " + this); } } + public boolean includesTime() { + return !isDateOnly(); + } + public boolean includesDate() { + return !isTimeOnly(); + } + + public boolean isDateOnly() { switch(this) { case DATE: - case DATE_TIME: - case DATE_TIME_12: - case DATE_TIME_24: + case LONG_DATE: + case MEDIUM_DATE: + case SHORT_DATE: return true; default: return false; } } - public boolean includesTime() { - return (this != DATE); - } - public boolean isTimeOnly() { switch(this) { case TIME: case TIME_12: case TIME_24: + case LONG_TIME: + case MEDIUM_TIME: + case SHORT_TIME: return true; default: return false; @@ -107,6 +135,7 @@ public class TemporalConfig private final String _dateFormat; private final String _dateImplicitYearFormat; + private final String _longDateFormat; private final String _timeFormat12; private final String _timeFormat24; private final char _dateSeparator; @@ -135,11 +164,13 @@ public class TemporalConfig * dateSeparator. */ public TemporalConfig(String dateFormat, String dateImplicitYearFormat, + String longDateFormat, String timeFormat12, String timeFormat24, char dateSeparator, char timeSeparator, Locale locale) { _dateFormat = dateFormat; _dateImplicitYearFormat = dateImplicitYearFormat; + _longDateFormat = longDateFormat; _timeFormat12 = timeFormat12; _timeFormat24 = timeFormat24; _dateSeparator = dateSeparator; @@ -192,12 +223,15 @@ public class TemporalConfig public String getDateTimeFormat(Type type) { switch(type) { case DATE: + case SHORT_DATE: return getDefaultDateFormat(); case TIME: return getDefaultTimeFormat(); case DATE_TIME: + case GENERAL_DATE: return getDefaultDateTimeFormat(); case TIME_12: + case LONG_TIME: return getTimeFormat12(); case TIME_24: return getTimeFormat24(); @@ -205,6 +239,14 @@ public class TemporalConfig return getDateTimeFormat12(); case DATE_TIME_24: return getDateTimeFormat24(); + case LONG_DATE: + return getLongDateFormat(); + case MEDIUM_DATE: + return getMediumDateFormat(); + case MEDIUM_TIME: + return getMediumTimeFormat(); + case SHORT_TIME: + return getShortTimeFormat(); default: throw new IllegalArgumentException("unknown date/time type " + type); } @@ -233,4 +275,20 @@ public class TemporalConfig private static String toDateTimeFormat(String dateFormat, String timeFormat) { return dateFormat + " " + timeFormat; } + + protected String getLongDateFormat() { + return _longDateFormat; + } + + protected String getMediumDateFormat() { + return MEDIUM_DATE_FORMAT; + } + + protected String getMediumTimeFormat() { + return MEDIUM_TIME_FORMAT; + } + + protected String getShortTimeFormat() { + return SHORT_TIME_FORMAT; + } } 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 048e606..28d9a86 100644 --- a/src/main/java/com/healthmarketscience/jackcess/expr/package-info.java +++ b/src/main/java/com/healthmarketscience/jackcess/expr/package-info.java @@ -101,10 +101,10 @@ limitations under the License. * ChrY * ChrWY * EuroConvert - * FormatCurrency - * FormatDateTime - * FormatNumber - * FormatPercent + * FormatCurrencyY + * FormatDateTimeY + * FormatNumberY + * FormatPercentY * GUIDFromString * Hex[$]Y * NzY 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 c75bb14..c5d0e8f 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java @@ -18,7 +18,10 @@ package com.healthmarketscience.jackcess.impl.expr; import java.math.BigDecimal; import java.math.BigInteger; +import java.text.DateFormat; import java.text.DecimalFormat; +import java.util.Calendar; +import java.util.Date; import java.util.HashMap; import java.util.Map; import java.util.regex.Matcher; @@ -28,6 +31,7 @@ import com.healthmarketscience.jackcess.expr.EvalException; import com.healthmarketscience.jackcess.expr.Function; import com.healthmarketscience.jackcess.expr.FunctionLookup; 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 com.healthmarketscience.jackcess.impl.NumberFormatter; @@ -290,7 +294,6 @@ public class DefaultFunctions public static final Function FORMATPERCENT = registerFunc(new FuncVar("FormatPercent", 1, 6) { @Override protected Value evalVar(EvalContext ctx, Value[] params) { - // FIXME, are defaults same for percent & currency? return formatNumber(ctx, params, true, false); } }); @@ -298,11 +301,65 @@ public class DefaultFunctions public static final Function FORMATCURRENCY = registerFunc(new FuncVar("FormatCurrency", 1, 6) { @Override protected Value evalVar(EvalContext ctx, Value[] params) { - // FIXME, are defaults same for percent & currency? return formatNumber(ctx, params, false, true); } }); + 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; + } + + Date d = param1.getAsDateTime(ctx); + + int fmtType = getOptionalIntParam(ctx, params, 1, 0); + TemporalConfig.Type tempType = null; + switch(fmtType) { + case 0: + // vbGeneralDate + Calendar cal = ctx.getCalendar(); + cal.setTime(d); + Value.Type valType = ValueSupport.getDateTimeType(cal); + 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); + } + + DateFormat sdf = ctx.createDateFormat( + ctx.getTemporalConfig().getDateTimeFormat(tempType)); + return ValueSupport.toValue(sdf.format(d)); + } + }); + public static final Function VARTYPE = registerFunc(new Func1("VarType") { @Override protected Value eval1(EvalContext ctx, Value param1) { @@ -491,7 +548,7 @@ public class DefaultFunctions if(isCurrency) { fmt.append("\u00A4"); } - + fmt.append(incLeadDigit ? "0" : "#"); if(numDecDigits > 0) { fmt.append("."); @@ -503,14 +560,14 @@ public class DefaultFunctions if(isPercent) { fmt.append("%"); } - + if(negParens) { // the javadocs claim the second pattern does not need to be fully // defined, but it doesn't seem to work that way String mainPat = fmt.toString(); fmt.append(";(").append(mainPat).append(")"); } - + // Note, DecimalFormat rounding mode uses HALF_EVEN by default DecimalFormat df = new DecimalFormat( fmt.toString(), cfg.getDecimalFormatSymbols()); 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 d0f8bf8..3040920 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/ValueSupport.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/ValueSupport.java @@ -106,6 +106,10 @@ public class ValueSupport } public static Value toValue(Calendar cal) { + return new DateTimeValue(getDateTimeType(cal), cal.getTime()); + } + + public static Value.Type getDateTimeType(Calendar cal) { boolean hasTime = ((cal.get(Calendar.HOUR_OF_DAY) != 0) || (cal.get(Calendar.MINUTE) != 0) || (cal.get(Calendar.SECOND) != 0)); @@ -115,11 +119,9 @@ public class ValueSupport ((cal.get(Calendar.MONTH) + 1) != ExpressionTokenizer.BASE_DATE_MONTH) || (cal.get(Calendar.DAY_OF_MONTH) != ExpressionTokenizer.BASE_DATE_DAY)); - Value.Type type = (hasDate ? - (hasTime ? Value.Type.DATE_TIME : Value.Type.DATE) : - Value.Type.TIME); - - return new DateTimeValue(type, cal.getTime()); + return (hasDate ? + (hasTime ? Value.Type.DATE_TIME : Value.Type.DATE) : + Value.Type.TIME); } public static Value toValue(Value.Type type, Date d) { diff --git a/src/test/java/com/healthmarketscience/jackcess/PropertyExpressionTest.java b/src/test/java/com/healthmarketscience/jackcess/PropertyExpressionTest.java index ffb2dd3..384386e 100644 --- a/src/test/java/com/healthmarketscience/jackcess/PropertyExpressionTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/PropertyExpressionTest.java @@ -295,6 +295,7 @@ public class PropertyExpressionTest extends TestCase public static void testCustomEvalConfig() throws Exception { TemporalConfig tempConf = new TemporalConfig("yyyy/M/d", "M/d", + "yyyy-MMM-d", "hh.mm.ss a", "HH.mm.ss", '/', '.', Locale.US); 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 f40a1de..fdab104 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java @@ -253,6 +253,14 @@ public class DefaultFunctionsTest extends TestCase assertEval("($12.34)", "=FormatCurrency(-12.345,-1,True,True)"); assertEval("$12", "=FormatCurrency(12.345,0,True,True)"); assertEval("-$.123", "=FormatCurrency(-0.12345,3,False)"); + + assertEval("1/1/1973 1:37:25 PM", "=FormatDateTime(#1/1/1973 1:37:25 PM#)"); + assertEval("1:37:25 PM", "=FormatDateTime(#1:37:25 PM#,0)"); + assertEval("1/1/1973", "=FormatDateTime(#1/1/1973#,0)"); + assertEval("Monday, January 01, 1973", "=FormatDateTime(#1/1/1973 1:37:25 PM#,1)"); + assertEval("1/1/1973", "=FormatDateTime(#1/1/1973 1:37:25 PM#,2)"); + assertEval("1:37:25 PM", "=FormatDateTime(#1/1/1973 1:37:25 PM#,3)"); + assertEval("13:37", "=FormatDateTime(#1/1/1973 1:37:25 PM#,4)"); } public void testNumberFuncs() throws Exception -- 2.39.5