/**
* @return the number of evaluated cells in the range that match the specified criteria
*/
public static int CountMatchingCellsInArea(TwoDEval areaEval, I_MatchPredicate criteriaPredicate)
{
int result = 0;
int height = areaEval.Height;
int width = areaEval.Width;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = areaEval.GetValue(rrIx, rcIx);
if (criteriaPredicate is I_MatchAreaPredicate)
{
I_MatchAreaPredicate areaPredicate = (I_MatchAreaPredicate)criteriaPredicate;
if (!areaPredicate.Matches(areaEval, rrIx, rcIx))
{
continue;
}
}
if (criteriaPredicate.Matches(ve))
{
result++;
}
}
}
return(result);
}
private static void CollectValues(ValueEval arg, IList temp)
{
if (arg is TwoDEval)
{
TwoDEval ae = (TwoDEval)arg;
int width = ae.Width;
int height = ae.Height;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve1 = ae.GetValue(rrIx, rcIx);
CollectValue(ve1, temp, false);
}
}
return;
}
if (arg is RefEval)
{
RefEval re = (RefEval)arg;
int firstSheetIndex = re.FirstSheetIndex;
int lastSheetIndex = re.LastSheetIndex;
for (int sIx = firstSheetIndex; sIx <= lastSheetIndex; sIx++)
{
CollectValue(re.GetInnerValueEval(sIx), temp, true);
}
return;
}
CollectValue(arg, temp, true);
}
protected override ValueEval GetItemInternal(int index)
{
int rowIx = index / _width;
int colIx = index % _width;
return(_ae.GetValue(rowIx, colIx));
}
private static void CollectValues(ValueEval arg, IList temp)
{
if (arg is TwoDEval)
{
TwoDEval ae = (TwoDEval)arg;
int width = ae.Width;
int height = ae.Height;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve1 = ae.GetValue(rrIx, rcIx);
CollectValue(ve1, temp, false);
}
}
return;
}
if (arg is RefEval)
{
RefEval re = (RefEval)arg;
CollectValue(re.InnerValueEval, temp, true);
return;
}
CollectValue(arg, temp, true);
}
/**
* Collects values from a single argument
*/
private void CollectValues(ValueEval operand, DoubleList temp)
{
if (operand is ThreeDEval)
{
ThreeDEval ae = (ThreeDEval)operand;
for (int sIx = ae.FirstSheetIndex; sIx <= ae.LastSheetIndex; sIx++)
{
int width = ae.Width;
int height = ae.Height;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = ae.GetValue(sIx, rrIx, rcIx);
if (!IsSubtotalCounted && ae.IsSubTotal(rrIx, rcIx))
{
continue;
}
CollectValue(ve, true, temp);
}
}
}
return;
}
if (operand is TwoDEval)
{
TwoDEval ae = (TwoDEval)operand;
int width = ae.Width;
int height = ae.Height;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = ae.GetValue(rrIx, rcIx);
if (!IsSubtotalCounted && ae.IsSubTotal(rrIx, rcIx))
{
continue;
}
CollectValue(ve, true, temp);
}
}
return;
}
if (operand is RefEval)
{
RefEval re = (RefEval)operand;
for (int sIx = re.FirstSheetIndex; sIx <= re.LastSheetIndex; sIx++)
{
CollectValue(re.GetInnerValueEval(sIx), true, temp);
}
return;
}
CollectValue((ValueEval)operand, false, temp);
}
/**
* For a given database returns the column number for a column heading.
*
* @param db Database.
* @param name Column heading.
* @return Corresponding column number.
* @If it's not possible to turn all headings into strings.
*/
private static int GetColumnForString(TwoDEval db, String name)
{
int resultColumn = -1;
for (int column = 0; column < db.Width; ++column)
{
ValueEval columnNameValueEval = db.GetValue(0, column);
String columnName = GetStringFromValueEval(columnNameValueEval);
if (name.Equals(columnName))
{
resultColumn = column;
break;
}
}
return(resultColumn);
}
private static void ThrowFirstError(TwoDEval areaEval)
{
int height = areaEval.Height;
int width = areaEval.Width;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = areaEval.GetValue(rrIx, rcIx);
if (ve is ErrorEval)
{
throw new EvaluationException((ErrorEval)ve);
}
}
}
}
private bool Calculate(ValueEval[] args)
{
bool result = InitialResultValue;
bool atleastOneNonBlank = false;
bool?tempVe;
/*
* Note: no short-circuit bool loop exit because any ErrorEvals will override the result
*/
for (int i = 0, iSize = args.Length; i < iSize; i++)
{
ValueEval arg = args[i];
if (arg is TwoDEval)
{
TwoDEval ae = (TwoDEval)arg;
int height = ae.Height;
int width = ae.Width;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = ae.GetValue(rrIx, rcIx);
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture));
atleastOneNonBlank = true;
}
}
}
continue;
}
if (arg is RefEval)
{
ValueEval ve = ((RefEval)arg).InnerValueEval;
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
}
else if (arg is ValueEval)
{
ValueEval ve = (ValueEval)arg;
tempVe = OperandResolver.CoerceValueToBoolean(ve, false);
}
else
{
throw new InvalidOperationException("Unexpected eval (" + arg.GetType().Name + ")");
}
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture));
atleastOneNonBlank = true;
}
}
if (!atleastOneNonBlank)
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
return(result);
}
private static void ThrowFirstError(TwoDEval areaEval)
{
int height = areaEval.Height;
int width = areaEval.Width;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = areaEval.GetValue(rrIx, rcIx);
if (ve is ErrorEval)
{
throw new EvaluationException((ErrorEval)ve);
}
}
}
}
private bool Calculate(ValueEval[] args)
{
bool result = InitialResultValue;
bool atleastOneNonBlank = false;
/*
* Note: no short-circuit bool loop exit because any ErrorEvals will override the result
*/
foreach (ValueEval arg in args)
{
bool?tempVe;
if (arg is TwoDEval)
{
TwoDEval ae = (TwoDEval)arg;
int height = ae.Height;
int width = ae.Width;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = ae.GetValue(rrIx, rcIx);
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture));
atleastOneNonBlank = true;
}
}
}
continue;
}
if (arg is RefEval)
{
RefEval re = (RefEval)arg;
int firstSheetIndex = re.FirstSheetIndex;
int lastSheetIndex = re.LastSheetIndex;
for (int sIx = firstSheetIndex; sIx <= lastSheetIndex; sIx++)
{
ValueEval ve = re.GetInnerValueEval(sIx);
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
if (tempVe != null)
{
result = PartialEvaluate(result, tempVe.Value);
atleastOneNonBlank = true;
}
}
continue;
}
//else if (arg is ValueEval)
//{
// ValueEval ve = (ValueEval)arg;
// tempVe = OperandResolver.CoerceValueToBoolean(ve, false);
//}
if (arg == MissingArgEval.instance)
{
tempVe = null; // you can leave out parameters, they are simply ignored
}
else
{
tempVe = OperandResolver.CoerceValueToBoolean(arg, false);
}
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture));
atleastOneNonBlank = true;
}
}
if (!atleastOneNonBlank)
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
return(result);
}
public ValueEval Evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval database, ValueEval filterColumn, ValueEval conditionDatabase)
{
// Input Processing and error Checks.
if (!(database is TwoDEval) || !(conditionDatabase is TwoDEval))
{
return(ErrorEval.VALUE_INVALID);
}
TwoDEval db = (TwoDEval)database;
TwoDEval cdb = (TwoDEval)conditionDatabase;
int fc;
try
{
fc = GetColumnForName(filterColumn, db);
}
catch (EvaluationException e)
{
return(ErrorEval.VALUE_INVALID);
}
if (fc == -1)
{ // column not found
return(ErrorEval.VALUE_INVALID);
}
// Reset algorithm.
algorithm.Reset();
// Iterate over all db entries.
for (int row = 1; row < db.Height; ++row)
{
bool matches = true;
try
{
matches = FullFillsConditions(db, row, cdb);
}
catch (EvaluationException e)
{
return(ErrorEval.VALUE_INVALID);
}
// Filter each entry.
if (matches)
{
try
{
ValueEval currentValueEval = solveReference(db.GetValue(row, fc));
// Pass the match to the algorithm and conditionally abort the search.
bool shouldContinue = algorithm.ProcessMatch(currentValueEval);
if (!shouldContinue)
{
break;
}
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
}
// Return the result of the algorithm.
return(algorithm.Result);
}
/**
* Checks a row in a database against a condition database.
*
* @param db Database.
* @param row The row in the database to Check.
* @param cdb The condition database to use for Checking.
* @return Whether the row matches the conditions.
* @If references could not be Resolved or comparison
* operators and operands didn't match.
*/
private static bool FullFillsConditions(TwoDEval db, int row, TwoDEval cdb)
{
// Only one row must match to accept the input, so rows are ORed.
// Each row is made up of cells where each cell is a condition,
// all have to match, so they are ANDed.
for (int conditionRow = 1; conditionRow < cdb.Height; ++conditionRow)
{
bool matches = true;
for (int column = 0; column < cdb.Width; ++column)
{ // columns are ANDed
// Whether the condition column matches a database column, if not it's a
// special column that accepts formulas.
bool columnCondition = true;
ValueEval condition = null;
try
{
// The condition to Apply.
condition = solveReference(cdb.GetValue(conditionRow, column));
}
catch (Exception e)
{
// It might be a special formula, then it is ok if it fails.
columnCondition = false;
}
// If the condition is empty it matches.
if (condition is BlankEval)
{
continue;
}
// The column in the DB to apply the condition to.
ValueEval targetHeader = solveReference(cdb.GetValue(0, column));
targetHeader = solveReference(targetHeader);
if (!(targetHeader is StringValueEval))
{
columnCondition = false;
}
else if (GetColumnForName(targetHeader, db) == -1)
{
// No column found, it's again a special column that accepts formulas.
columnCondition = false;
}
if (columnCondition == true)
{ // normal column condition
// Should not throw, Checked above.
ValueEval target = db.GetValue(
row, GetColumnForName(targetHeader, db));
// Must be a string.
String conditionString = GetStringFromValueEval(condition);
if (!testNormalCondition(target, conditionString))
{
matches = false;
break;
}
}
else
{ // It's a special formula condition.
throw new NotImplementedException(
"D* function with formula conditions");
}
}
if (matches == true)
{
return(true);
}
}
return(false);
}
/**
* Checks a row in a database against a condition database.
*
* @param db Database.
* @param row The row in the database to Check.
* @param cdb The condition database to use for Checking.
* @return Whether the row matches the conditions.
* @If references could not be Resolved or comparison
* operators and operands didn't match.
*/
private static bool FullFillsConditions(TwoDEval db, int row, TwoDEval cdb)
{
// Only one row must match to accept the input, so rows are ORed.
// Each row is made up of cells where each cell is a condition,
// all have to match, so they are ANDed.
for (int conditionRow = 1; conditionRow < cdb.Height; ++conditionRow)
{
bool matches = true;
for (int column = 0; column < cdb.Width; ++column)
{ // columns are ANDed
// Whether the condition column matches a database column, if not it's a
// special column that accepts formulas.
bool columnCondition = true;
ValueEval condition = null;
try
{
// The condition to Apply.
condition = solveReference(cdb.GetValue(conditionRow, column));
}
catch (Exception e)
{
// It might be a special formula, then it is ok if it fails.
columnCondition = false;
}
// If the condition is empty it matches.
if (condition is BlankEval)
continue;
// The column in the DB to apply the condition to.
ValueEval targetHeader = solveReference(cdb.GetValue(0, column));
targetHeader = solveReference(targetHeader);
if (!(targetHeader is StringValueEval))
columnCondition = false;
else if (GetColumnForName(targetHeader, db) == -1)
// No column found, it's again a special column that accepts formulas.
columnCondition = false;
if (columnCondition == true)
{ // normal column condition
// Should not throw, Checked above.
ValueEval target = db.GetValue(
row, GetColumnForName(targetHeader, db));
// Must be a string.
String conditionString = GetStringFromValueEval(condition);
if (!testNormalCondition(target, conditionString))
{
matches = false;
break;
}
}
else
{ // It's a special formula condition.
throw new NotImplementedException(
"D* function with formula conditions");
}
}
if (matches == true)
{
return true;
}
}
return false;
}
/**
* For a given database returns the column number for a column heading.
*
* @param db Database.
* @param name Column heading.
* @return Corresponding column number.
* @If it's not possible to turn all headings into strings.
*/
private static int GetColumnForString(TwoDEval db, String name)
{
int resultColumn = -1;
for (int column = 0; column < db.Width; ++column)
{
ValueEval columnNameValueEval = db.GetValue(0, column);
String columnName = GetStringFromValueEval(columnNameValueEval);
if (name.Equals(columnName))
{
resultColumn = column;
break;
}
}
return resultColumn;
}
/**
* @return the number of evaluated cells in the range that match the specified criteria
*/
public static int CountMatchingCellsInArea(TwoDEval areaEval, I_MatchPredicate criteriaPredicate)
{
int result = 0;
int height = areaEval.Height;
int width = areaEval.Width;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = areaEval.GetValue(rrIx, rcIx);
if (criteriaPredicate is I_MatchAreaPredicate)
{
I_MatchAreaPredicate areaPredicate = (I_MatchAreaPredicate)criteriaPredicate;
if (!areaPredicate.Matches(areaEval, rrIx, rcIx)) continue;
}
if (criteriaPredicate.Matches(ve))
{
result++;
}
}
}
return result;
}
