Database.php

Go to the documentation of this file.

<?php
if (!defined('PHPEXCEL_ROOT')) {
        define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
        require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
}


class PHPExcel_Calculation_Database {


        private static function __fieldExtract($database,$field) {
                $field = strtoupper(PHPExcel_Calculation_Functions::flattenSingleValue($field));
                $fieldNames = array_map('strtoupper',array_shift($database));

                if (is_numeric($field)) {
                        $keys = array_keys($fieldNames);
                        return $keys[$field-1];
                }
                $key = array_search($field,$fieldNames);
                return ($key) ? $key : null;
        }

        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;
        }


        public static function DAVERAGE($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_Statistical::AVERAGE($colData);
        }       //      function DAVERAGE()

        public static function DCOUNT($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_Statistical::COUNT($colData);
        }       //      function DCOUNT()

        public static function DCOUNTA($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_Statistical::COUNTA($colData);
        }       //      function DCOUNTA()

        public static function DGET($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                if (count($colData) > 1) {
                        return PHPExcel_Calculation_Functions::NaN();
                }

                return $colData[0];
        }       //      function DGET()

        public static function DMAX($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_Statistical::MAX($colData);
        }       //      function DMAX()

        public static function DMIN($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_Statistical::MIN($colData);
        }       //      function DMIN()

        public static function DPRODUCT($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_MathTrig::PRODUCT($colData);
        }       //      function DPRODUCT()

        public static function DSTDEV($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_Statistical::STDEV($colData);
        }       //      function DSTDEV()

        public static function DSTDEVP($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_Statistical::STDEVP($colData);
        }       //      function DSTDEVP()

        public static function DSUM($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_MathTrig::SUM($colData);
        }       //      function DSUM()

        public static function DVAR($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_Statistical::VARFunc($colData);
        }       //      function DVAR()

        public static function DVARP($database,$field,$criteria) {
                $field = self::__fieldExtract($database,$field);
                if (is_null($field)) {
                        return NULL;
                }

                //      reduce the database to a set of rows that match all the criteria
                $database = self::__filter($database,$criteria);
                //      extract an array of values for the requested column
                $colData = array();
                foreach($database as $row) {
                        $colData[] = $row[$field];
                }

                // Return
                return PHPExcel_Calculation_Statistical::VARP($colData);
        }       //      function DVARP()


}       //      class PHPExcel_Calculation_Database