cld/protected/class/PHPExcel/Calculation/Functions.php

<?php
/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2011 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
 *
 * @category	PHPExcel
 * @package		PHPExcel_Calculation
 * @copyright	Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license		http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version		1.7.6, 2011-02-27
 */


/** PHPExcel root directory */
if (!defined('PHPEXCEL_ROOT')) {
	/**
	 * @ignore
	 */
	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
}


/** MAX_VALUE */
define('MAX_VALUE', 1.2e308);

/** 2 / PI */
define('M_2DIVPI', 0.63661977236758134307553505349006);

/** MAX_ITERATIONS */
define('MAX_ITERATIONS', 256);

/** PRECISION */
define('PRECISION', 8.88E-016);


/**
 * PHPExcel_Calculation_Functions
 *
 * @category	PHPExcel
 * @package		PHPExcel_Calculation
 * @copyright	Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 */
class PHPExcel_Calculation_Functions {

	/** constants */
	const COMPATIBILITY_EXCEL		= 'Excel';
	const COMPATIBILITY_GNUMERIC	= 'Gnumeric';
	const COMPATIBILITY_OPENOFFICE	= 'OpenOfficeCalc';

	const RETURNDATE_PHP_NUMERIC	= 'P';
	const RETURNDATE_PHP_OBJECT		= 'O';
	const RETURNDATE_EXCEL			= 'E';


	/**
	 *	Compatibility mode to use for error checking and responses
	 *
	 *	@access	private
	 *	@var string
	 */
	protected static $compatibilityMode	= self::COMPATIBILITY_EXCEL;

	/**
	 *	Data Type to use when returning date values
	 *
	 *	@access	private
	 *	@var string
	 */
	protected static $ReturnDateType	= self::RETURNDATE_EXCEL;

	/**
	 *	List of error codes
	 *
	 *	@access	private
	 *	@var array
	 */
	protected static $_errorCodes	= array( 'null'				=> '#NULL!',
											 'divisionbyzero'	=> '#DIV/0!',
											 'value'			=> '#VALUE!',
											 'reference'		=> '#REF!',
											 'name'				=> '#NAME?',
											 'num'				=> '#NUM!',
											 'na'				=> '#N/A',
											 'gettingdata'		=> '#GETTING_DATA'
										   );


	/**
	 *	Set the Compatibility Mode
	 *
	 *	@access	public
	 *	@category Function Configuration
	 *	@param	 string		$compatibilityMode		Compatibility Mode
	 *												Permitted values are:
	 *													PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL			'Excel'
	 *													PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC		'Gnumeric'
	 *													PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE	'OpenOfficeCalc'
	 *	@return	 boolean	(Success or Failure)
	 */
	public static function setCompatibilityMode($compatibilityMode) {
		if (($compatibilityMode == self::COMPATIBILITY_EXCEL) ||
			($compatibilityMode == self::COMPATIBILITY_GNUMERIC) ||
			($compatibilityMode == self::COMPATIBILITY_OPENOFFICE)) {
			self::$compatibilityMode = $compatibilityMode;
			return True;
		}
		return False;
	}	//	function setCompatibilityMode()


	/**
	 *	Return the current Compatibility Mode
	 *
	 *	@access	public
	 *	@category Function Configuration
	 *	@return	 string		Compatibility Mode
	 *							Possible Return values are:
	 *								PHPExcel_Calculation_Functions::COMPATIBILITY_EXCEL			'Excel'
	 *								PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC		'Gnumeric'
	 *								PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE	'OpenOfficeCalc'
	 */
	public static function getCompatibilityMode() {
		return self::$compatibilityMode;
	}	//	function getCompatibilityMode()


	/**
	 *	Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
	 *
	 *	@access	public
	 *	@category Function Configuration
	 *	@param	 string	$returnDateType			Return Date Format
	 *												Permitted values are:
	 *													PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC		'P'
	 *													PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT		'O'
	 *													PHPExcel_Calculation_Functions::RETURNDATE_EXCEL			'E'
	 *	@return	 boolean							Success or failure
	 */
	public static function setReturnDateType($returnDateType) {
		if (($returnDateType == self::RETURNDATE_PHP_NUMERIC) ||
			($returnDateType == self::RETURNDATE_PHP_OBJECT) ||
			($returnDateType == self::RETURNDATE_EXCEL)) {
			self::$ReturnDateType = $returnDateType;
			return True;
		}
		return False;
	}	//	function setReturnDateType()


	/**
	 *	Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object)
	 *
	 *	@access	public
	 *	@category Function Configuration
	 *	@return	 string		Return Date Format
	 *							Possible Return values are:
	 *								PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC		'P'
	 *								PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT		'O'
	 *								PHPExcel_Calculation_Functions::RETURNDATE_EXCEL			'E'
	 */
	public static function getReturnDateType() {
		return self::$ReturnDateType;
	}	//	function getReturnDateType()


	/**
	 *	DUMMY
	 *
	 *	@access	public
	 *	@category Error Returns
	 *	@return	string	#Not Yet Implemented
	 */
	public static function DUMMY() {
		return '#Not Yet Implemented';
	}	//	function DUMMY()


	/**
	 *	DIV0
	 *
	 *	@access	public
	 *	@category Error Returns
	 *	@return	string	#Not Yet Implemented
	 */
	public static function DIV0() {
		return self::$_errorCodes['divisionbyzero'];
	}	//	function DIV0()


	/**
	 *	NA
	 *
	 *	Excel Function:
	 *		=NA()
	 *
	 *	Returns the error value #N/A
	 *		#N/A is the error value that means "no value is available."
	 *
	 *	@access	public
	 *	@category Logical Functions
	 *	@return	string	#N/A!
	 */
	public static function NA() {
		return self::$_errorCodes['na'];
	}	//	function NA()


	/**
	 *	NaN
	 *
	 *	Returns the error value #NUM!
	 *
	 *	@access	public
	 *	@category Error Returns
	 *	@return	string	#NUM!
	 */
	public static function NaN() {
		return self::$_errorCodes['num'];
	}	//	function NaN()


	/**
	 *	NAME
	 *
	 *	Returns the error value #NAME?
	 *
	 *	@access	public
	 *	@category Error Returns
	 *	@return	string	#NAME?
	 */
	public static function NAME() {
		return self::$_errorCodes['name'];
	}	//	function NAME()


	/**
	 *	REF
	 *
	 *	Returns the error value #REF!
	 *
	 *	@access	public
	 *	@category Error Returns
	 *	@return	string	#REF!
	 */
	public static function REF() {
		return self::$_errorCodes['reference'];
	}	//	function REF()


	/**
	 *	NULL
	 *
	 *	Returns the error value #NULL!
	 *
	 *	@access	public
	 *	@category Error Returns
	 *	@return	string	#REF!
	 */
	public static function NULL() {
		return self::$_errorCodes['null'];
	}	//	function NULL()


	/**
	 *	VALUE
	 *
	 *	Returns the error value #VALUE!
	 *
	 *	@access	public
	 *	@category Error Returns
	 *	@return	string	#VALUE!
	 */
	public static function VALUE() {
		return self::$_errorCodes['value'];
	}	//	function VALUE()


	public static function isMatrixValue($idx) {
		return ((substr_count($idx,'.') <= 1) || (preg_match('/\.[A-Z]/',$idx) > 0));
	}


	public static function isValue($idx) {
		return (substr_count($idx,'.') == 0);
	}


	public static function isCellValue($idx) {
		return (substr_count($idx,'.') > 1);
	}


	public static function _ifCondition($condition) {
		$condition	= PHPExcel_Calculation_Functions::flattenSingleValue($condition);
		if (!in_array($condition{0},array('>', '<', '='))) {
			if (!is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition)); }
			return '='.$condition;
		} else {
			preg_match('/([<>=]+)(.*)/',$condition,$matches);
			list(,$operator,$operand) = $matches;
			if (!is_numeric($operand)) { $operand = PHPExcel_Calculation::_wrapResult(strtoupper($operand)); }
			return $operator.$operand;
		}
	}	//	function _ifCondition()


	/**
	 *	ERROR_TYPE
	 *
	 *	@param	mixed	$value	Value to check
	 *	@return	boolean
	 */
	public static function ERROR_TYPE($value = '') {
		$value	= self::flattenSingleValue($value);

		$i = 1;
		foreach(self::$_errorCodes as $errorCode) {
			if ($value == $errorCode) {
				return $i;
			}
			++$i;
		}
		return self::$_errorCodes['na'];
	}	//	function ERROR_TYPE()


	/**
	 *	IS_BLANK
	 *
	 *	@param	mixed	$value	Value to check
	 *	@return	boolean
	 */
	public static function IS_BLANK($value=null) {
		if (!is_null($value)) {
			$value	= self::flattenSingleValue($value);
		}

		return is_null($value);
	}	//	function IS_BLANK()


	/**
	 *	IS_ERR
	 *
	 *	@param	mixed	$value	Value to check
	 *	@return	boolean
	 */
	public static function IS_ERR($value = '') {
		$value		= self::flattenSingleValue($value);

		return self::IS_ERROR($value) && (!self::IS_NA($value));
	}	//	function IS_ERR()


	/**
	 *	IS_ERROR
	 *
	 *	@param	mixed	$value	Value to check
	 *	@return	boolean
	 */
	public static function IS_ERROR($value = '') {
		$value		= self::flattenSingleValue($value);

		return in_array($value, array_values(self::$_errorCodes));
	}	//	function IS_ERROR()


	/**
	 *	IS_NA
	 *
	 *	@param	mixed	$value	Value to check
	 *	@return	boolean
	 */
	public static function IS_NA($value = '') {
		$value		= self::flattenSingleValue($value);

		return ($value === self::$_errorCodes['na']);
	}	//	function IS_NA()


	/**
	 *	IS_EVEN
	 *
	 *	@param	mixed	$value	Value to check
	 *	@return	boolean
	 */
	public static function IS_EVEN($value = 0) {
		$value		= self::flattenSingleValue($value);

		if ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value)))) {
			return self::$_errorCodes['value'];
		}
		return ($value % 2 == 0);
	}	//	function IS_EVEN()


	/**
	 *	IS_ODD
	 *
	 *	@param	mixed	$value	Value to check
	 *	@return	boolean
	 */
	public static function IS_ODD($value = null) {
		$value	= self::flattenSingleValue($value);

		if ((is_bool($value)) || ((is_string($value)) && (!is_numeric($value)))) {
			return self::$_errorCodes['value'];
		}
		return (abs($value) % 2 == 1);
	}	//	function IS_ODD()


	/**
	 *	IS_NUMBER
	 *
	 *	@param	mixed	$value		Value to check
	 *	@return	boolean
	 */
	public static function IS_NUMBER($value = 0) {
		$value		= self::flattenSingleValue($value);

		if (is_string($value)) {
			return False;
		}
		return is_numeric($value);
	}	//	function IS_NUMBER()


	/**
	 *	IS_LOGICAL
	 *
	 *	@param	mixed	$value		Value to check
	 *	@return	boolean
	 */
	public static function IS_LOGICAL($value = true) {
		$value		= self::flattenSingleValue($value);

		return is_bool($value);
	}	//	function IS_LOGICAL()


	/**
	 *	IS_TEXT
	 *
	 *	@param	mixed	$value		Value to check
	 *	@return	boolean
	 */
	public static function IS_TEXT($value = '') {
		$value		= self::flattenSingleValue($value);

		return is_string($value);
	}	//	function IS_TEXT()


	/**
	 *	IS_NONTEXT
	 *
	 *	@param	mixed	$value		Value to check
	 *	@return	boolean
	 */
	public static function IS_NONTEXT($value = '') {
		return !self::IS_TEXT($value);
	}	//	function IS_NONTEXT()


	/**
	 *	VERSION
	 *
	 *	@return	string	Version information
	 */
	public static function VERSION() {
		return 'PHPExcel 1.7.6, 2011-02-27';
	}	//	function VERSION()


	/**
	 *	N
	 *
	 *	Returns a value converted to a number
	 *
	 *	@param	value		The value you want converted
	 *	@return	number		N converts values listed in the following table
	 *		If value is or refers to N returns
	 *		A number			That number
	 *		A date				The serial number of that date
	 *		TRUE				1
	 *		FALSE				0
	 *		An error value		The error value
	 *		Anything else		0
	 */
	public static function N($value) {
		while (is_array($value)) {
			$value = array_shift($value);
		}

		switch (gettype($value)) {
			case 'double'	:
			case 'float'	:
			case 'integer'	:
				return $value;
				break;
			case 'boolean'	:
				return (integer) $value;
				break;
			case 'string'	:
				//	Errors
				if ((strlen($value) > 0) && ($value{0} == '#')) {
					return $value;
				}
				break;
		}
		return 0;
	}	//	function N()


	/**
	 *	TYPE
	 *
	 *	Returns a number that identifies the type of a value
	 *
	 *	@param	value		The value you want tested
	 *	@return	number		N converts values listed in the following table
	 *		If value is or refers to N returns
	 *		A number			1
	 *		Text				2
	 *		Logical Value		4
	 *		An error value		16
	 *		Array or Matrix		64
	 */
	public static function TYPE($value) {
		$value	= self::flattenArrayIndexed($value);
		if (is_array($value) && (count($value) > 1)) {
			$a = array_keys($value);
			$a = array_pop($a);
			//	Range of cells is an error
			if (self::isCellValue($a)) {
				return 16;
			//	Test for Matrix
			} elseif (self::isMatrixValue($a)) {
				return 64;
			}
		} elseif(count($value) == 0) {
			//	Empty Cell
			return 1;
		}
		$value	= self::flattenSingleValue($value);

		if ((is_float($value)) || (is_int($value))) {
				return 1;
		} elseif(is_bool($value)) {
				return 4;
		} elseif(is_array($value)) {
				return 64;
				break;
		} elseif(is_string($value)) {
			//	Errors
			if ((strlen($value) > 0) && ($value{0} == '#')) {
				return 16;
			}
			return 2;
		}
		return 0;
	}	//	function TYPE()


	/**
	 *	Convert a multi-dimensional array to a simple 1-dimensional array
	 *
	 *	@param	array	$array	Array to be flattened
	 *	@return	array	Flattened array
	 */
	public static function flattenArray($array) {
		if (!is_array($array)) {
			return (array) $array;
		}

		$arrayValues = array();
		foreach ($array as $value) {
			if (is_array($value)) {
				foreach ($value as $val) {
					if (is_array($val)) {
						foreach ($val as $v) {
							$arrayValues[] = $v;
						}
					} else {
						$arrayValues[] = $val;
					}
				}
			} else {
				$arrayValues[] = $value;
			}
		}

		return $arrayValues;
	}	//	function flattenArray()


	/**
	 *	Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing
	 *
	 *	@param	array	$array	Array to be flattened
	 *	@return	array	Flattened array
	 */
	public static function flattenArrayIndexed($array) {
		if (!is_array($array)) {
			return (array) $array;
		}

		$arrayValues = array();
		foreach ($array as $k1 => $value) {
			if (is_array($value)) {
				foreach ($value as $k2 => $val) {
					if (is_array($val)) {
						foreach ($val as $k3 => $v) {
							$arrayValues[$k1.'.'.$k2.'.'.$k3] = $v;
						}
					} else {
						$arrayValues[$k1.'.'.$k2] = $val;
					}
				}
			} else {
				$arrayValues[$k1] = $value;
			}
		}

		return $arrayValues;
	}	//	function flattenArrayIndexed()


	/**
	 *	Convert an array to a single scalar value by extracting the first element
	 *
	 *	@param	mixed		$value		Array or scalar value
	 *	@return	mixed
	 */
	public static function flattenSingleValue($value = '') {
		while (is_array($value)) {
			$value = array_pop($value);
		}

		return $value;
	}	//	function flattenSingleValue()

}	//	class PHPExcel_Calculation_Functions


//
//	There are a few mathematical functions that aren't available on all versions of PHP for all platforms
//	These functions aren't available in Windows implementations of PHP prior to version 5.3.0
//	So we test if they do exist for this version of PHP/operating platform; and if not we create them
//
if (!function_exists('acosh')) {
	function acosh($x) {
		return 2 * log(sqrt(($x + 1) / 2) + sqrt(($x - 1) / 2));
	}	//	function acosh()
}

if (!function_exists('asinh')) {
	function asinh($x) {
		return log($x + sqrt(1 + $x * $x));
	}	//	function asinh()
}

if (!function_exists('atanh')) {
	function atanh($x) {
		return (log(1 + $x) - log(1 - $x)) / 2;
	}	//	function atanh()
}

if (!function_exists('money_format')) {
	function money_format($format, $number) {
		$regex = array( '/%((?:[\^!\-]|\+|\(|\=.)*)([0-9]+)?(?:#([0-9]+))?',
						 '(?:\.([0-9]+))?([in%])/'
					  );
		$regex = implode('', $regex);
		if (setlocale(LC_MONETARY, null) == '') {
			setlocale(LC_MONETARY, '');
		}
		$locale = localeconv();
		$number = floatval($number);
		if (!preg_match($regex, $format, $fmatch)) {
			trigger_error("No format specified or invalid format", E_USER_WARNING);
			return $number;
		}
		$flags = array( 'fillchar'	=> preg_match('/\=(.)/', $fmatch[1], $match) ? $match[1] : ' ',
						'nogroup'	=> preg_match('/\^/', $fmatch[1]) > 0,
						'usesignal'	=> preg_match('/\+|\(/', $fmatch[1], $match) ? $match[0] : '+',
						'nosimbol'	=> preg_match('/\!/', $fmatch[1]) > 0,
						'isleft'	=> preg_match('/\-/', $fmatch[1]) > 0
					  );
		$width	= trim($fmatch[2]) ? (int)$fmatch[2] : 0;
		$left	= trim($fmatch[3]) ? (int)$fmatch[3] : 0;
		$right	= trim($fmatch[4]) ? (int)$fmatch[4] : $locale['int_frac_digits'];
		$conversion = $fmatch[5];
		$positive = true;
		if ($number < 0) {
			$positive = false;
			$number *= -1;
		}
		$letter = $positive ? 'p' : 'n';
		$prefix = $suffix = $cprefix = $csuffix = $signal = '';
		if (!$positive) {
			$signal = $locale['negative_sign'];
			switch (true) {
				case $locale['n_sign_posn'] == 0 || $flags['usesignal'] == '(':
					$prefix = '(';
					$suffix = ')';
					break;
				case $locale['n_sign_posn'] == 1:
					$prefix = $signal;
					break;
				case $locale['n_sign_posn'] == 2:
					$suffix = $signal;
					break;
				case $locale['n_sign_posn'] == 3:
					$cprefix = $signal;
					break;
				case $locale['n_sign_posn'] == 4:
					$csuffix = $signal;
					break;
			}
		}
		if (!$flags['nosimbol']) {
			$currency = $cprefix;
			$currency .= ($conversion == 'i' ? $locale['int_curr_symbol'] : $locale['currency_symbol']);
			$currency .= $csuffix;
			$currency = iconv('ISO-8859-1','UTF-8',$currency);
		} else {
			$currency = '';
		}
		$space = $locale["{$letter}_sep_by_space"] ? ' ' : '';

		$number = number_format($number, $right, $locale['mon_decimal_point'], $flags['nogroup'] ? '' : $locale['mon_thousands_sep'] );
		$number = explode($locale['mon_decimal_point'], $number);

		$n = strlen($prefix) + strlen($currency);
		if ($left > 0 && $left > $n) {
			if ($flags['isleft']) {
				$number[0] .= str_repeat($flags['fillchar'], $left - $n);
			} else {
				$number[0] = str_repeat($flags['fillchar'], $left - $n) . $number[0];
			}
		}
		$number = implode($locale['mon_decimal_point'], $number);
		if ($locale["{$letter}_cs_precedes"]) {
			$number = $prefix . $currency . $space . $number . $suffix;
		} else {
			$number = $prefix . $number . $space . $currency . $suffix;
		}
		if ($width > 0) {
			$number = str_pad($number, $width, $flags['fillchar'], $flags['isleft'] ? STR_PAD_RIGHT : STR_PAD_LEFT);
		}
		$format = str_replace($fmatch[0], $number, $format);
		return $format;
	}	//	function money_format()
}


//
//	Strangely, PHP doesn't have a mb_str_replace multibyte function
//	As we'll only ever use this function with UTF-8 characters, we can simply "hard-code" the character set
//
if ((!function_exists('mb_str_replace')) &&
	(function_exists('mb_substr')) && (function_exists('mb_strlen')) && (function_exists('mb_strpos'))) {
	function mb_str_replace($search, $replace, $subject) {
		if(is_array($subject)) {
			$ret = array();
			foreach($subject as $key => $val) {
				$ret[$key] = mb_str_replace($search, $replace, $val);
			}
			return $ret;
		}

		foreach((array) $search as $key => $s) {
			if($s == '') {
				continue;
			}
			$r = !is_array($replace) ? $replace : (array_key_exists($key, $replace) ? $replace[$key] : '');
			$pos = mb_strpos($subject, $s, 0, 'UTF-8');
			while($pos !== false) {
				$subject = mb_substr($subject, 0, $pos, 'UTF-8') . $r . mb_substr($subject, $pos + mb_strlen($s, 'UTF-8'), 65535, 'UTF-8');
				$pos = mb_strpos($subject, $s, $pos + mb_strlen($r, 'UTF-8'), 'UTF-8');
			}
		}
		return $subject;
	}
}