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

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