Adopt spreadsheet changes made in Balas development

[simantics/platform.git] / bundles / org.simantics.spreadsheet / src / org / simantics / spreadsheet / solver / formula / MatchFormulaFunction.java

diff --git a/bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/solver/formula/MatchFormulaFunction.java b/bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/solver/formula/MatchFormulaFunction.java
new file mode 100644 (file)
index 0000000..db7bf8c
--- /dev/null
+++ b/bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/solver/formula/MatchFormulaFunction.java
@@ -0,0 +1,200 @@
+package org.simantics.spreadsheet.solver.formula;
+
+import org.simantics.databoard.binding.mutable.Variant;
+import org.simantics.spreadsheet.SpreadsheetMatrix;
+import org.simantics.spreadsheet.Spreadsheets;
+import org.simantics.spreadsheet.solver.formula.parser.ast.AstArgList;
+
+public class MatchFormulaFunction implements CellFormulaFunction<Object>{
+
+    @Override
+    public Object evaluate(CellValueVisitor visitor, AstArgList args) {
+        if (args.values.size() < 2 || args.values.size() > 3) throw new IllegalStateException();
+
+        SpreadsheetMatrix lookup_array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
+        if(lookup_array.getWidth()!=1 && lookup_array.getHeight()!=1) return FormulaError2.NA.getString();
+
+        Object lookup_value = args.values.get(0).accept(visitor);
+
+        int match_type = 1;
+        try{
+            if(args.values.size()==3){
+                int matchArg = ((Number)args.values.get(2).accept(visitor)).intValue();
+                if(matchArg<0)
+                    match_type = -1;
+                else if(matchArg>0)
+                    match_type = 1;
+                else
+                    match_type = 0;
+            }
+        } catch(Exception e) {
+            return FormulaError2.NA.getString();
+        }
+
+        boolean useHeight = false;
+        if(lookup_array.getWidth()==1) useHeight = true;
+
+        int max = 0;
+        if(useHeight) max = lookup_array.getHeight();
+        else max = lookup_array.getWidth();
+
+        Integer pos = null;
+        if(lookup_value instanceof Variant){
+            Object obj = ((Variant)lookup_value).getValue();
+            Number nVal = Spreadsheets.asValidNumber(obj);
+            if(nVal!=null) obj = nVal;
+            else obj = obj.toString();
+        }
+
+        if(lookup_value instanceof Number){
+            Double previousValue = null;
+            Double closestMatch = null;
+            Double lookup = ((Number)lookup_value).doubleValue();
+            int indexWhereCorrectOrderStartsAt = 0;
+
+            if(match_type!=0) {
+                for(int i = 0; i < max;i++){
+                    Double currValue = null;
+                    Number currNum;
+                    if(useHeight){
+                        currNum = Spreadsheets.asValidNumber(lookup_array.get(i,0));
+                    } else {
+                        currNum = Spreadsheets.asValidNumber(lookup_array.get(0,i));
+                    }
+                    if(currNum!=null){
+                        currValue = currNum.doubleValue();
+                        if(currValue != null){
+                            if(previousValue!=null){
+                                if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue))
+                                    indexWhereCorrectOrderStartsAt = i;
+                                else
+                                    break;
+                            }
+                            previousValue = currValue;
+                        }
+                    }
+                }
+            }
+            int begin = indexWhereCorrectOrderStartsAt;
+
+            for(int i = begin; i < max;i++){
+                Double currValue = null;
+                Number currNum;
+                if(useHeight){
+                    currNum = Spreadsheets.asValidNumber(lookup_array.get(i,0));
+                } else {
+                    currNum = Spreadsheets.asValidNumber(lookup_array.get(0,i));
+                }
+                if(currNum!=null){
+                    currValue = currNum.doubleValue();
+                    if(currValue != null){
+                        if(previousValue==null) previousValue = currValue;
+                        if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue)){
+                            if(pos!=null)
+                                return pos+1;
+                            previousValue = currValue;
+                        }
+                        int comp = lookup.compareTo(currValue);
+                        if(comp == 0){
+                            closestMatch = currValue;
+                            pos = i;
+                        }
+                        else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){
+                            if(closestMatch==null && pos==null){
+                                closestMatch = currValue;
+                                pos = i;
+                            }
+                            else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){
+                                closestMatch = currValue;
+                                pos = i;
+                            }
+                        }
+                        previousValue = currValue;
+                    }
+                }
+            }
+        }
+
+        else if(lookup_value instanceof String){
+            String previousValue = null;
+            String closestMatch = null;
+            String lookup = (String)lookup_value;
+            int indexWhereCorrectOrderStartsAt = 0;
+
+            if(match_type!=0) {
+                for(int i = 0; i < max;i++){
+                    String currValue = null;
+                    Object obj;
+                    if(useHeight){
+                        obj = lookup_array.get(i,0);
+                    } else {
+                        obj = lookup_array.get(0,i);
+                    }
+                    if(obj instanceof Variant){
+                        obj = ((Variant)obj).getValue();
+                    }
+                    if(obj!=null && !(obj instanceof Number)){
+                        currValue = obj.toString();
+                        currValue = currValue.toLowerCase();
+                    }
+                    if(currValue != null && !currValue.equals("")){
+                        if(previousValue!=null){
+                            if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0))
+                                indexWhereCorrectOrderStartsAt = i;
+                            else
+                                break;
+                        }
+                        previousValue = currValue;
+                    }
+                }
+            }
+
+            int begin = indexWhereCorrectOrderStartsAt;
+
+            for(int i = begin; i < max;i++){
+                String currValue = null;
+                Object obj;
+                if(useHeight){
+                    obj = lookup_array.get(i,0);
+                } else {
+                    obj = lookup_array.get(0,i);
+                }
+                if(obj instanceof Variant){
+                    obj = ((Variant)obj).getValue();
+                }
+                if(obj!=null && !(obj instanceof Number)){
+                    currValue = obj.toString();
+                    currValue = currValue.toLowerCase();
+                }
+
+                if(currValue != null && !currValue.equals("")){
+                    if(previousValue==null) previousValue = currValue;
+                    if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0)){
+                        if(pos!=null)
+                            return pos+1;
+                        previousValue = currValue;
+                    }
+                    int comp = lookup.compareTo(currValue);
+                    if(comp == 0){
+                        closestMatch = currValue;
+                        pos = i;
+                    }
+                    else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){
+                        if(closestMatch==null && pos==null){
+                            closestMatch = currValue;
+                            pos = i;
+                        }
+                        else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){
+                            closestMatch = currValue;
+                            pos = i;
+                        }
+                    }
+                    previousValue = currValue;
+                }
+            }
+        }
+
+        if(pos==null)return FormulaError2.NA.getString();
+        return pos+1;
+    }
+}