bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/MatchFormulaFunction.java

   1 package org.simantics.spreadsheet.graph.formula;
   2
   3 import org.simantics.databoard.binding.mutable.Variant;
   4 import org.simantics.spreadsheet.graph.CellFormulaFunction;
   5 import org.simantics.spreadsheet.graph.CellValueVisitor;
   6 import org.simantics.spreadsheet.graph.SpreadsheetGraphUtils;
   7 import org.simantics.spreadsheet.graph.SpreadsheetMatrix;
   8 import org.simantics.spreadsheet.graph.parser.ast.AstArgList;
   9
  10 public class MatchFormulaFunction implements CellFormulaFunction<Object>{
  11
  12         @Override
  13     public Object evaluate(CellValueVisitor visitor, AstArgList args) {
  14         if (args.values.size() < 2 || args.values.size() > 3) throw new IllegalStateException();
  15
  16         SpreadsheetMatrix lookup_array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
  17         if(lookup_array.getWidth()!=1 && lookup_array.getHeight()!=1) return FormulaError2.NA.getString();
  18
  19         Object lookup_value = args.values.get(0).accept(visitor);
  20
  21         int match_type = 1;
  22         try{
  23                 if(args.values.size()==3){
  24                         int matchArg = ((Number)args.values.get(2).accept(visitor)).intValue();
  25                         if(matchArg<0)
  26                                 match_type = -1;
  27                         else if(matchArg>0)
  28                                 match_type = 1;
  29                         else
  30                                 match_type = 0;
  31                 }
  32         } catch(Exception e) {
  33                 return FormulaError2.NA.getString();
  34         }
  35
  36         boolean useHeight = false;
  37         if(lookup_array.getWidth()==1) useHeight = true;
  38
  39         int max = 0;
  40         if(useHeight) max = lookup_array.getHeight();
  41         else max = lookup_array.getWidth();
  42
  43         Integer pos = null;
  44         if(lookup_value instanceof Variant){
  45                 Object obj = ((Variant)lookup_value).getValue();
  46                 Number nVal = SpreadsheetGraphUtils.asValidNumber(obj);
  47                 if(nVal!=null) obj = nVal;
  48                 else obj = obj.toString();
  49         }
  50
  51         if(lookup_value instanceof Number){
  52                 Double previousValue = null;
  53                 Double closestMatch = null;
  54             Double lookup = ((Number)lookup_value).doubleValue();
  55             int indexWhereCorrectOrderStartsAt = 0;
  56
  57             if(match_type!=0) {
  58                 for(int i = 0; i < max;i++){
  59                         Double currValue = null;
  60                         Number currNum;
  61                                 if(useHeight){
  62                                         currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(i,0));
  63                                 } else {
  64                                         currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(0,i));
  65                                 }
  66                                 if(currNum!=null){
  67                                         currValue = currNum.doubleValue();
  68                                 if(currValue != null){
  69                                         if(previousValue!=null){
  70                                                 if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue))
  71                                                         indexWhereCorrectOrderStartsAt = i;
  72                                                 else
  73                                                         break;
  74                                         }
  75                                         previousValue = currValue;
  76                                 }
  77                         }
  78                 }
  79             }
  80             int begin = indexWhereCorrectOrderStartsAt;
  81
  82             for(int i = begin; i < max;i++){
  83                 Double currValue = null;
  84                 Number currNum;
  85                         if(useHeight){
  86                                 currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(i,0));
  87                         } else {
  88                                 currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(0,i));
  89                         }
  90                         if(currNum!=null){
  91                                 currValue = currNum.doubleValue();
  92                         if(currValue != null){
  93                                 if(previousValue==null) previousValue = currValue;
  94                                 if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue)){
  95                                         if(pos!=null)
  96                                                 return pos+1;
  97                                 previousValue = currValue;
  98                                 }
  99                                         int comp = lookup.compareTo(currValue);
 100                                         if(comp == 0){
 101                                                 closestMatch = currValue;
 102                                                 pos = i;
 103                                         }
 104                                         else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){
 105                                                 if(closestMatch==null && pos==null){
 106                                                         closestMatch = currValue;
 107                                                         pos = i;
 108                                                 }
 109                                                 else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){
 110                                                         closestMatch = currValue;
 111                                                         pos = i;
 112                                                 }
 113                                         }
 114                                 previousValue = currValue;
 115                         }
 116                 }
 117             }
 118         }
 119
 120         else if(lookup_value instanceof String){
 121                 String previousValue = null;
 122                 String closestMatch = null;
 123             String lookup = (String)lookup_value;
 124             int indexWhereCorrectOrderStartsAt = 0;
 125
 126             if(match_type!=0) {
 127                 for(int i = 0; i < max;i++){
 128                         String currValue = null;
 129                         Object obj;
 130                         if(useHeight){
 131                                 obj = lookup_array.get(i,0);
 132                         } else {
 133                                 obj = lookup_array.get(0,i);
 134                         }
 135                         if(obj instanceof Variant){
 136                                 obj = ((Variant)obj).getValue();
 137                         }
 138                         if(obj!=null && !(obj instanceof Number)){
 139                                 currValue = obj.toString();
 140                                 currValue = currValue.toLowerCase();
 141                         }
 142                         if(currValue != null && !currValue.equals("")){
 143                                 if(previousValue!=null){
 144                                         if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0))
 145                                                 indexWhereCorrectOrderStartsAt = i;
 146                                         else
 147                                                 break;
 148                                 }
 149                                 previousValue = currValue;
 150                         }
 151                 }
 152             }
 153
 154             int begin = indexWhereCorrectOrderStartsAt;
 155
 156             for(int i = begin; i < max;i++){
 157                 String currValue = null;
 158                         Object obj;
 159                         if(useHeight){
 160                                 obj = lookup_array.get(i,0);
 161                         } else {
 162                                 obj = lookup_array.get(0,i);
 163                         }
 164                         if(obj instanceof Variant){
 165                                 obj = ((Variant)obj).getValue();
 166                         }
 167                         if(obj!=null && !(obj instanceof Number)){
 168                                 currValue = obj.toString();
 169                                 currValue = currValue.toLowerCase();
 170                         }
 171
 172                 if(currValue != null && !currValue.equals("")){
 173                         if(previousValue==null) previousValue = currValue;
 174                         if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0)){
 175                                 if(pos!=null)
 176                                         return pos+1;
 177                             previousValue = currValue;
 178                         }
 179                         int comp = lookup.compareTo(currValue);
 180                         if(comp == 0){
 181                                 closestMatch = currValue;
 182                                 pos = i;
 183                         }
 184                                 else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){
 185                                         if(closestMatch==null && pos==null){
 186                                                 closestMatch = currValue;
 187                                                 pos = i;
 188                                         }
 189                                         else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){
 190                                                 closestMatch = currValue;
 191                                                 pos = i;
 192                                         }
 193                                 }
 194                         previousValue = currValue;
 195                 }
 196             }
 197         }
 198
 199         if(pos==null)return FormulaError2.NA.getString();
 200         return pos+1;
 201     }
 202 }