implement FormatCurrency and FormatPercent

git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@1220 f203690c-595d-4dc9-a70b-905162fa7fd2

2 files changed, 83 insertions, 43 deletions

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 132daeb..c75bb14 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
@@ -283,51 +283,23 @@ public class DefaultFunctions
   public static final Function FORMATNUMBER = registerFunc(new FuncVar("FormatNumber", 1, 6) {
     @Override
     protected Value evalVar(EvalContext ctx, Value[] params) {
-      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 negParens = getOptionalTriStateBoolean(
-          ctx, params, 3, cfg.useParensForNegatives());
-      int numGroupDigits = cfg.getNumGroupingDigits();
-      boolean groupDigits = getOptionalTriStateBoolean(
-          ctx, params, 4, (numGroupDigits > 0));
-
-      StringBuilder fmt = new StringBuilder();
-
-      fmt.append(incLeadDigit ? "0" : "#");
-      if(numDecDigits > 0) {
-        fmt.append(".");
-        for(int i = 0; i < numDecDigits; ++i) {
-          fmt.append("0");
-        }
-      }
+      return formatNumber(ctx, params, false, false);
+    }
+  });
 
-      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());
-      if(groupDigits) {
-        df.setGroupingUsed(true);
-        df.setGroupingSize(numGroupDigits);
-      } else {
-        df.setGroupingUsed(false);
-        df.setGroupingSize(numGroupDigits);
-      }
+  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);
+    }
+  });
 
-      return ValueSupport.toValue(df.format(param1.getAsBigDecimal(ctx)));
+  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);
     }
   });
 
@@ -495,6 +467,64 @@ public class DefaultFunctions
     return bv;
   }
 
+  private static Value formatNumber(
+      EvalContext ctx, Value[] params, boolean isPercent, boolean isCurrency) {
+
+    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 negParens = getOptionalTriStateBoolean(
+        ctx, params, 3, cfg.useParensForNegatives());
+    int numGroupDigits = cfg.getNumGroupingDigits();
+    boolean groupDigits = getOptionalTriStateBoolean(
+        ctx, params, 4, (numGroupDigits > 0));
+
+    StringBuilder fmt = new StringBuilder();
+
+    if(isCurrency) {
+      fmt.append("\u00A4");
+    }
+    
+    fmt.append(incLeadDigit ? "0" : "#");
+    if(numDecDigits > 0) {
+      fmt.append(".");
+      for(int i = 0; i < numDecDigits; ++i) {
+        fmt.append("0");
+      }
+    }
+
+    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());
+    if(groupDigits) {
+      df.setGroupingUsed(true);
+      df.setGroupingSize(numGroupDigits);
+    } else {
+      df.setGroupingUsed(false);
+      df.setGroupingSize(numGroupDigits);
+    }
+
+    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
 
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 163f0f2..f40a1de 100644
--- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
+++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
@@ -243,6 +243,16 @@ public class DefaultFunctionsTest extends TestCase
     assertEval("(12)", "=FormatNumber(-12.345,0,True,True)");
     assertEval("12,345.00", "=FormatNumber(12345,-1,-2,-2,True)");
     assertEval("12345.00", "=FormatNumber(12345,-1,-2,-2,False)");
+
+    assertEval("1,234,500.00%", "=FormatPercent(12345)");
+    assertEval("(1,234.50%)", "=FormatPercent(-12.345,-1,True,True)");
+    assertEval("34%", "=FormatPercent(0.345,0,True,True)");
+    assertEval("-.123%", "=FormatPercent(-0.0012345,3,False)");
+
+    assertEval("$12,345.00", "=FormatCurrency(12345)");
+    assertEval("($12.34)", "=FormatCurrency(-12.345,-1,True,True)");
+    assertEval("$12", "=FormatCurrency(12.345,0,True,True)");
+    assertEval("-$.123", "=FormatCurrency(-0.12345,3,False)");
   }
 
   public void testNumberFuncs() throws Exception