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

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