protected List<ValueEval> fetchValuesWithFullScan(
IDataSet set, Map<Integer, Object> where, int columnIndex) {
List<ValueEval> found = new ArrayList<>();
for (IDsRow row : set) {
boolean allFieldsMatch = true;
int allFieldsPresent = where.size();
for (Entry<Integer, Object> whereColumn : where.entrySet()) {
IDsCell cell = row.getCell(whereColumn.getKey());
if (cell != null) {
allFieldsPresent--;
Object extValue = coerceValueTo(whereColumn.getValue());
/* Such a strange conversion because of Number types - everything is Double in POI */
Object intValue = coerceValueTo(valueToValueEval(cell.getValue().get()));
if (!intValue.equals(extValue)) {
allFieldsMatch = false;
break;
}
}
}
if (allFieldsPresent == 0 && allFieldsMatch) {
found.add(valueToValueEval(row.getCell(columnIndex).getValue().get()));
break; // collecting only the first matching record according to product owner requirements
}
}
return found;
}
@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
log.debug("In evaluate() of DSLOOKUP function. Args = {}", Arrays.toString(args));
if (args.length < 4 || args.length % 2 != 0) {
log.warn(
"The number of input arguments of DSLOOKUP function should be even and no less than 4.");
return ErrorEval.VALUE_INVALID;
}
if (!(args[0] instanceof StringValueEval) && !(args[0] instanceof RefEval)) {
log.warn(
"The first input argument of DSLOOKUP function should be a string (or a reference to a cell) with a dataset name.");
return ErrorEval.VALUE_INVALID;
}
if (!(args[args.length - 1] instanceof StringValueEval)
&& !(args[args.length - 1] instanceof RefEval)) {
log.warn(
"The last input argument of DSLOOKUP function should be a string (or a reference to a cell) with a name of a column which values should be returned.");
return ErrorEval.VALUE_INVALID;
}
String datasetName;
try {
datasetName =
(String) coerceValueTo(getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex()));
} catch (EvaluationException e) {
log.error(String.format("Cannot get the value of DataSet name: %s", args[0]), e);
return ErrorEval.VALUE_INVALID;
}
String columnName;
try {
columnName =
(String)
coerceValueTo(
getSingleValue(args[args.length - 1], ec.getRowIndex(), ec.getColumnIndex()));
} catch (EvaluationException e) {
log.error(
String.format("Cannot get the value of target column name: %s", args[args.length - 1]),
e);
return ErrorEval.VALUE_INVALID;
}
Map<Object, ValueEval> pairs = new HashMap<>();
for (int i = 1; i < args.length - 1; i += 2) {
if (!(args[i] instanceof StringEval) && !(args[i] instanceof RefEval)) {
log.warn(
"The {}th input argument in DSLOOKUP function should be a string (or a reference to a cell) with a name of a condition field",
i);
return ErrorEval.VALUE_INVALID;
}
try {
String key =
(String) coerceValueTo(getSingleValue(args[i], ec.getRowIndex(), ec.getColumnIndex()));
ValueEval val = getSingleValue(args[i + 1], ec.getRowIndex(), ec.getColumnIndex());
pairs.put(key, val);
} catch (EvaluationException e) {
log.error(String.format("Cannot get the value of matcher column: %s", args[i]), e);
return ErrorEval.VALUE_INVALID;
}
}
DataSetAccessor dataSets =
(DataSetAccessor) ec.getCustomEvaluationContext().get(DataSetAccessor.class);
if (dataSets == null) {
dataSets = this.external.getDataSetAccessor();
}
IDataSet dataSet;
try {
dataSet = dataSets.get(datasetName);
} catch (Exception e) {
log.error(
"The DataSet with name = {} cannot be found\retrived from DataSet storage.", datasetName);
return ErrorEval.NA;
}
if (dataSet == null) {
DataModelAccessor dataModels =
(DataModelAccessor) ec.getCustomEvaluationContext().get(DataModelAccessor.class);
if (dataModels == null) {
dataModels = this.external.getDataModelAccessor();
}
dataSet = Converters.toDataSet(dataModels.get(datasetName));
}
if (dataSet == null) {
log.error(
"The DataSet with name = {} cannot found in DataSet/DataModel storage.", datasetName);
return ErrorEval.NA;
}
Iterator<IDsRow> rowrator = dataSet.iterator();
if (!rowrator.hasNext()) {
log.warn("The spreadsheet shoud have at least 2 rows to run DSLOOKUP function");
return ErrorEval.VALUE_INVALID;
}
int columnIndex = -1;
IDsRow titleRow = rowrator.next();
Map<Integer, Object> indexToValue = new HashMap<>();
for (IDsCell cell : titleRow) {
ICellValue value = cell.getValue();
if (pairs.containsKey(value.get())) {
indexToValue.put(cell.index(), pairs.get(value.get()));
}
if (columnName.equals(value.get())) {
columnIndex = cell.index();
}
}
if (columnIndex < 0) {
log.warn("No such column to retreive value from is found: {}.", columnName);
return ErrorEval.VALUE_INVALID;
}
if (indexToValue.isEmpty()) {
log.warn("No filter columns are found.");
return ErrorEval.VALUE_INVALID;
}
DsLookupParameters parameters =
new DsLookupParameters(dataSet.getName(), indexToValue, columnIndex);
List<ValueEval> fetchedValues = fetchValuesWithOptimisations(parameters, ec);
if (fetchedValues == null) {
fetchedValues = fetchValuesWithFullScan(dataSet, indexToValue, columnIndex);
updateOptimisationsCache(parameters, dataSet, fetchedValues, ec);
}
// This is per PO decision: DSLOOKUP should return only one value - first found.
return fetchedValues.isEmpty() ? ErrorEval.NA : fetchedValues.get(0);
}
