private static function __filter($database, $criteria) {
$fieldNames = array_shift ( $database );
$criteriaNames = array_shift ( $criteria );
// Convert the criteria into a set of AND/OR conditions with
// [:placeholders]
$testConditions = $testValues = array ();
$testConditionsCount = 0;
foreach ( $criteriaNames as $key => $criteriaName ) {
$testCondition = array ();
$testConditionCount = 0;
foreach ( $criteria as $row => $criterion ) {
if ($criterion [$key] > '') {
$testCondition [] = '[:' . $criteriaName . ']' . PHPExcel_Calculation_Functions::_ifCondition ( $criterion [$key] );
$testConditionCount ++;
}
}
if ($testConditionCount > 1) {
$testConditions [] = 'OR(' . implode ( ',', $testCondition ) . ')';
$testConditionsCount ++;
} elseif ($testConditionCount == 1) {
$testConditions [] = $testCondition [0];
$testConditionsCount ++;
}
}
if ($testConditionsCount > 1) {
$testConditionSet = 'AND(' . implode ( ',', $testConditions ) . ')';
} elseif ($testConditionsCount == 1) {
$testConditionSet = $testConditions [0];
}
// Loop through each row of the database
foreach ( $database as $dataRow => $dataValues ) {
// Substitute actual values from the database row for our
// [:placeholders]
$testConditionList = $testConditionSet;
foreach ( $criteriaNames as $key => $criteriaName ) {
$k = array_search ( $criteriaName, $fieldNames );
if (isset ( $dataValues [$k] )) {
$dataValue = $dataValues [$k];
$dataValue = (is_string ( $dataValue )) ? PHPExcel_Calculation::_wrapResult ( strtoupper ( $dataValue ) ) : $dataValue;
$testConditionList = str_replace ( '[:' . $criteriaName . ']', $dataValue, $testConditionList );
}
}
// evaluate the criteria against the row data
$result = PHPExcel_Calculation::getInstance ()->_calculateFormulaValue ( '=' . $testConditionList );
// If the row failed to meet the criteria, remove it from the
// database
if (! $result) {
unset ( $database [$dataRow] );
}
}
return $database;
}
/**
* MINIF
*
* Returns the minimum value within a range of cells that contain numbers within the list of arguments
*
* Excel Function:
* MINIF(value1[,value2[, ...]],condition)
*
* @access public
* @category Mathematical and Trigonometric Functions
* @param mixed $arg,... Data values
* @param string $condition The criteria that defines which cells will be checked.
* @return float
*/
public static function MINIF($aArgs, $condition, $sumArgs = array())
{
// Return value
$returnValue = null;
$aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
$sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
if (count($sumArgs) == 0) {
$sumArgs = $aArgs;
}
$condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
// Loop through arguments
foreach ($aArgs as $key => $arg) {
if (!is_numeric($arg)) {
$arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
}
$testCondition = '=' . $arg . $condition;
if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
if (is_null($returnValue) || $arg < $returnValue) {
$returnValue = $arg;
}
}
}
// Return
return $returnValue;
}
/**
* SUMIF
*
* Counts the number of cells that contain numbers within the list of arguments
*
* Excel Function:
* SUMIF(value1[,value2[, ...]],condition)
*
* @access public
* @category Mathematical and Trigonometric Functions
* @param mixed $arg,... Data values
* @param string $condition The criteria that defines which cells will be summed.
* @return float
*/
public static function SUMIF($aArgs, $condition, $sumArgs = array())
{
// Return value
$returnValue = 0;
$aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
$sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
if (empty($sumArgs)) {
$sumArgs = $aArgs;
}
$condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
// Loop through arguments
foreach ($aArgs as $key => $arg) {
if (!is_numeric($arg)) {
$arg = str_replace('"', '""', $arg);
$arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg));
}
$testCondition = '=' . $arg . $condition;
if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
// Is it a value within our criteria
$returnValue += $sumArgs[$key];
}
}
// Return
return $returnValue;
}
