/**
* DVARP
*
* Calculates the variance of a population based on the entire population by using the numbers
* in a column of a list or database that match conditions that you specify.
*
* Excel Function:
* DVARP(database,field,criteria)
*
* @access public
* @category Database Functions
* @param mixed[] $database The range of cells that makes up the list or database.
* A database is a list of related data in which rows of related
* information are records, and columns of data are fields. The
* first row of the list contains labels for each column.
* @param string|integer $field Indicates which column is used in the function. Enter the
* column label enclosed between double quotation marks, such as
* "Age" or "Yield," or a number (without quotation marks) that
* represents the position of the column within the list: 1 for
* the first column, 2 for the second column, and so on.
* @param mixed[] $criteria The range of cells that contains the conditions you specify.
* You can use any range for the criteria argument, as long as it
* includes at least one column label and at least one cell below
* the column label in which you specify a condition for the
* column.
* @return float
*
*/
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);
}
private static function _logGamma($x)
{
// Log Gamma related constants
static $lg_d1 = -0.5772156649015329;
static $lg_d2 = 0.42278433509846713;
static $lg_d4 = 1.791759469228055;
static $lg_p1 = array(4.945235359296727, 201.8112620856775, 2290.8383738313464, 11319.672059033808, 28557.246356716354, 38484.962284437934, 26377.487876241954, 7225.813979700288);
static $lg_p2 = array(4.974607845568932, 542.4138599891071, 15506.93864978365, 184793.29044456323, 1088204.7694688288, 3338152.96798703, 5106661.678927353, 3074109.0548505397);
static $lg_p4 = array(14745.0216605994, 2426813.3694867045, 121475557.40450932, 2663432449.630977, 29403789566.34554, 170266573776.5399, 492612579337.7431, 560625185622.3951);
static $lg_q1 = array(67.48212550303778, 1113.3323938571993, 7738.757056935398, 27639.870744033407, 54993.102062261576, 61611.22180066002, 36351.2759150194, 8785.536302431014);
static $lg_q2 = array(183.03283993705926, 7765.049321445006, 133190.38279660742, 1136705.8213219696, 5267964.117437947, 13467014.543111017, 17827365.303532742, 9533095.591844354);
static $lg_q4 = array(2690.5301758708993, 639388.5654300093, 41355999.30241388, 1120872109.616148, 14886137286.788137, 101680358627.24382, 341747634550.73773, 446315818741.9713);
static $lg_c = array(-0.001910444077728, 0.0008417138778129501, -0.0005952379913043012, 0.0007936507935003503, -0.0027777777777776816, 0.08333333333333333, 0.0057083835261);
// Rough estimate of the fourth root of logGamma_xBig
static $lg_frtbig = 2.25E+76;
static $pnt68 = 0.6796875;
if ($x == self::$_logGammaCache_x) {
return self::$_logGammaCache_result;
}
$y = $x;
if ($y > 0.0 && $y <= LOG_GAMMA_X_MAX_VALUE) {
if ($y <= EPS) {
$res = -log(y);
} elseif ($y <= 1.5) {
// ---------------------
// EPS .LT. X .LE. 1.5
// ---------------------
if ($y < $pnt68) {
$corr = -log($y);
$xm1 = $y;
} else {
$corr = 0.0;
$xm1 = $y - 1.0;
}
if ($y <= 0.5 || $y >= $pnt68) {
$xden = 1.0;
$xnum = 0.0;
for ($i = 0; $i < 8; ++$i) {
$xnum = $xnum * $xm1 + $lg_p1[$i];
$xden = $xden * $xm1 + $lg_q1[$i];
}
$res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden));
} else {
$xm2 = $y - 1.0;
$xden = 1.0;
$xnum = 0.0;
for ($i = 0; $i < 8; ++$i) {
$xnum = $xnum * $xm2 + $lg_p2[$i];
$xden = $xden * $xm2 + $lg_q2[$i];
}
$res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
}
} elseif ($y <= 4.0) {
// ---------------------
// 1.5 .LT. X .LE. 4.0
// ---------------------
$xm2 = $y - 2.0;
$xden = 1.0;
$xnum = 0.0;
for ($i = 0; $i < 8; ++$i) {
$xnum = $xnum * $xm2 + $lg_p2[$i];
$xden = $xden * $xm2 + $lg_q2[$i];
}
$res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
} elseif ($y <= 12.0) {
// ----------------------
// 4.0 .LT. X .LE. 12.0
// ----------------------
$xm4 = $y - 4.0;
$xden = -1.0;
$xnum = 0.0;
for ($i = 0; $i < 8; ++$i) {
$xnum = $xnum * $xm4 + $lg_p4[$i];
$xden = $xden * $xm4 + $lg_q4[$i];
}
$res = $lg_d4 + $xm4 * ($xnum / $xden);
} else {
// ---------------------------------
// Evaluate for argument .GE. 12.0
// ---------------------------------
$res = 0.0;
if ($y <= $lg_frtbig) {
$res = $lg_c[6];
$ysq = $y * $y;
for ($i = 0; $i < 6; ++$i) {
$res = $res / $ysq + $lg_c[$i];
}
}
$res /= $y;
$corr = log($y);
$res = $res + log(SQRT2PI) - 0.5 * $corr;
$res += $y * ($corr - 1.0);
}
} else {
// --------------------------
// Return for bad arguments
// --------------------------
$res = MAX_VALUE;
}
// ------------------------------
// Final adjustments and return
// ------------------------------
self::$_logGammaCache_x = $x;
self::$_logGammaCache_result = $res;
return $res;
}
/**
* SUBTOTAL
*
* Returns a subtotal in a list or database.
*
* @param int the number 1 to 11 that specifies which function to
* use in calculating subtotals within a list.
* @param array of mixed Data Series
* @return float
*/
public static function SUBTOTAL()
{
$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
// Calculate
$subtotal = array_shift($aArgs);
if (is_numeric($subtotal) && !is_string($subtotal)) {
switch ($subtotal) {
case 1:
return PHPExcel_Calculation_Statistical::AVERAGE($aArgs);
break;
case 2:
return PHPExcel_Calculation_Statistical::COUNT($aArgs);
break;
case 3:
return PHPExcel_Calculation_Statistical::COUNTA($aArgs);
break;
case 4:
return PHPExcel_Calculation_Statistical::MAX($aArgs);
break;
case 5:
return PHPExcel_Calculation_Statistical::MIN($aArgs);
break;
case 6:
return self::PRODUCT($aArgs);
break;
case 7:
return PHPExcel_Calculation_Statistical::STDEV($aArgs);
break;
case 8:
return PHPExcel_Calculation_Statistical::STDEVP($aArgs);
break;
case 9:
return self::SUM($aArgs);
break;
case 10:
return PHPExcel_Calculation_Statistical::VARFunc($aArgs);
break;
case 11:
return PHPExcel_Calculation_Statistical::VARP($aArgs);
break;
}
}
return PHPExcel_Calculation_Functions::VALUE();
}
function listar_oportunidades_por_puntaje($fecha = array(), $dashboard = NULL)
{
$dataset = array();
$results = array();
$fields = array("opp.id_oportunidad", "opp.nombre", "HEX(opp.uuid_oportunidad) AS uuid_oportunidad", "HEX(cl.uuid_cliente) AS uuid_cliente", "HEX(opp.uuid_sociedad) AS uuid_sociedad", "HEX(opp.uuid_contacto) AS uuid_contacto", "ccat.etiqueta AS tipo_cliente", "cl.nombre AS nombre_cliente", "clso.nombre_comercial", "ocat.id_cat AS id_etapa", "ocat.etiqueta AS etapa", "opp.fecha_cierre", "opp.fecha_creacion", "prop.valor_alquiler", "CONCAT_WS(' ', IF(usr.nombre != '', usr.nombre, ''), IF(usr.apellido != '', usr.apellido, '')) AS usuario_asignado", "CONCAT_WS(' ', IF(con.nombre != '', con.nombre, ''), IF(con.apellido != '', con.apellido, '')) AS nombre_contacto");
/**
* Permiso Default: ver las asignadas o las que ha creado
*/
$clause = array("ocat.valor NOT IN('vendido','venta_perdida')" => NULL);
if (!empty($fecha)) {
$replace = Grafica_usuario::condicion_fecha($fecha);
$str = str_replace("fecha_creacion", "opp.fecha_creacion", $replace);
$clause[$str] = NULL;
} else {
$clause["MONTH(opp.fecha_creacion) = '" . date('n') . "'"] = NULL;
}
/**
* Array con los uuid de usuarios
* que el usario actual puede ver.
*/
if (empty($dashboard)) {
$ver_usuarios = @CRM_Controller::andrea_ACL();
} else {
$ver_usuarios = @CRM_Controller::andrea_ACL('dashboard');
}
$this->db->select($fields)->distinct()->from('opp_oportunidades AS opp')->join('cl_clientes AS cl', 'cl.uuid_cliente = opp.uuid_cliente', 'LEFT')->join('cl_cliente_sociedades_contactos AS clcon', 'clcon.uuid_cliente = cl.uuid_cliente', 'LEFT')->join('con_contactos AS con', 'con.uuid_contacto = clcon.uuid_contacto', 'LEFT')->join('opp_oportunidades_cat AS ocat', 'ocat.id_cat = opp.id_etapa_venta', 'LEFT')->join('usuarios AS usr', 'usr.uuid_usuario = opp.id_asignado', 'LEFT')->join('cl_clientes_cat AS ccat', 'ccat.id_cat = cl.id_tipo_cliente', 'LEFT')->join('rpo_propiedades AS prop', 'prop.uuid_propiedad = opp.uuid_propiedad', 'LEFT')->join('cl_clientes_sociedades AS clso', 'clso.uuid_sociedad = opp.uuid_sociedad', 'LEFT');
/**
* verificar array $ver_usuarios
*/
if (!empty($ver_usuarios["uuid_usuario"])) {
$usuarios = $ver_usuarios["uuid_usuario"];
$usuarios = !empty($usuarios) ? implode(", ", array_map(function ($usuarios) {
return "'" . $usuarios . "'";
}, $usuarios)) : "";
$clause["HEX(opp.id_asignado) IN(" . $usuarios . ")"] = NULL;
}
$this->db->where($clause);
$oportunidades = $this->db->group_by('opp.id_oportunidad', 'DESC')->get()->result_array();
if (!empty($oportunidades)) {
$i = 0;
foreach ($oportunidades as $oportunidad) {
$uuid_oportunidad = !empty($oportunidad["uuid_oportunidad"]) ? $oportunidad["uuid_oportunidad"] : "";
$results[$i]["id_oportunidad"] = !empty($oportunidad["id_oportunidad"]) ? $oportunidad["id_oportunidad"] : "";
$results[$i]["uuid_oportunidad"] = !empty($oportunidad["uuid_oportunidad"]) ? $oportunidad["uuid_oportunidad"] : "";
$results[$i]["nombre"] = !empty($oportunidad["nombre"]) ? $oportunidad["nombre"] : "";
$results[$i]["nombre_comercial"] = !empty($oportunidad["nombre_comercial"]) ? $oportunidad["nombre_comercial"] : "";
$results[$i]["tipo_cliente"] = !empty($oportunidad["tipo_cliente"]) ? $oportunidad["tipo_cliente"] : "";
$results[$i]["nombre_cliente"] = !empty($oportunidad["nombre_cliente"]) ? $oportunidad["nombre_cliente"] : "";
$results[$i]["fecha_creacion"] = !empty($oportunidad["fecha_creacion"]) ? $oportunidad["fecha_creacion"] : "";
//-------------------------
// Seleccionar la suma total
// de las actividades completadas
// de esta oportunidad.
//-------------------------
$fields = array("COALESCE(SUM(actta.puntaje), 0) AS puntaje_total");
$clause = array("actcat1.valor" => "oportunidades", "act.uuid_oportunidad = UNHEX('{$uuid_oportunidad}')" => NULL, "act.completada" => 1);
$actividad = $this->db->select($fields)->distinct()->from('act_actividades AS act')->join("act_actividades_cat AS actcat1", "actcat1.id_cat = act.relacionado_con", 'LEFT')->join("act_tipo_actividades AS actta", "actta.uuid_tipo_actividad = act.uuid_tipo_actividad", 'LEFT')->where($clause)->get()->result_array();
$results[$i]["puntaje_total"] = 0;
if (!empty($actividad[0]["puntaje_total"]) && $actividad[0]["puntaje_total"] > 0) {
$results[$i]["puntaje_total"] = !empty($actividad[0]["puntaje_total"]) ? (int) $actividad[0]["puntaje_total"] : 0;
$dataset[$i] = (int) $actividad[0]["puntaje_total"];
$i++;
} else {
unset($results[$i]);
unset($dataset[$i]);
}
}
//Si no tiene actividad retornar false
/*if(@Util::is_array_empty($hasActividades)){
return false;
}*/
/**
* Hack: para que la funcion PHPExcel_Calculation_Statistical::PERCENTRANK
* No envie el siguiente error, si el array $dataset contiene un solo valor:
*
* A PHP Error was encountered
* Severity: Warning
* Message: Division by zero
* Filename: Calculation/Statistical.php
*/
if (count($dataset) == 1) {
$dataset[] = 0;
}
//ordenar los puntajes
//de menor a mayor
ksort($dataset);
//Recorrer los resultados y calcular el PERCENTRANK
//Usando una clase de la libreria PHPEXcel y introducir
//el resultado en el array de $results
$j = 0;
$percentagerank = "";
foreach ($results as $result) {
/*if(empty($result) || empty($result["puntaje_total"]) && $result["puntaje_total"] == ""
|| empty($result["puntaje_total"]) && $result["puntaje_total"] <= 0
|| !empty($result["puntaje_total"]) && count($dataset) == 1 && $dataset[0] == $result["puntaje_total"]){
continue;
}*/
if (!empty($result["puntaje_total"]) && $result["puntaje_total"] != "" && $result["puntaje_total"] > 0) {
$percentagerank = PHPExcel_Calculation_Statistical::PERCENTRANK($dataset, $result["puntaje_total"], 2) * 100;
//$percentagerank = PHPExcel_Calculation_Statistical::PERCENTRANK($dataset, $result["puntaje_total"], 1);
$results[$j]["percentagerank"] = !empty($percentagerank) ? $percentagerank : 0;
} else {
unset($result);
}
$j++;
}
}
//Ordenar el arreglo segun los valores de porcentaje. (mayor -> menor)
usort($results, function ($a, $b) {
$array_a = !empty($a['percentagerank']) ? $a['percentagerank'] : 0;
$array_b = !empty($b['percentagerank']) ? $b['percentagerank'] : 0;
if ($array_a == $array_b) {
return 0;
} else {
if ($array_a > $array_b) {
return -1;
} else {
return 1;
}
}
});
return $results;
}
public static function listar_oportunidades_por_puntaje($fecha = array(), $subordinados)
{
$dataset = array();
$results = array();
$replace = Grafica_usuario::condicion_fecha($fecha);
$fecha_clause = str_replace("fecha_creacion", "opp.fecha_creacion", $replace);
$uuid_subordinados = !empty($subordinados) ? implode(", ", array_map(function ($subordinados) {
return "'" . $subordinados . "'";
}, $subordinados)) : "";
$clause = " AND {$fecha_clause}";
if (!empty($uuid_subordinados)) {
$clause .= " AND HEX(opp.id_asignado) IN(" . $uuid_subordinados . ")";
}
$sql = "SELECT DISTINCT `opp`.`id_oportunidad`, `opp`.`nombre`, HEX(opp.uuid_oportunidad) AS uuid_oportunidad, HEX(cl.uuid_cliente) AS uuid_cliente, HEX(opp.uuid_sociedad) AS uuid_sociedad, HEX(opp.uuid_contacto) AS uuid_contacto,\n\t\t\t\t`ccat`.`etiqueta` AS tipo_cliente, `cl`.`nombre` AS nombre_cliente, `clso`.`nombre_comercial`, `ocat`.`id_cat` AS id_etapa, `ocat`.`etiqueta` AS etapa, `opp`.`fecha_cierre`, `opp`.`fecha_creacion`, `prop`.`valor_alquiler`,\n\t\t\t\tCONCAT_WS(' ', IF(usr.nombre != '', usr.nombre, ''), IF(usr.apellido != '', usr.apellido, '')) AS usuario_asignado,\n\t\t\t\tCONCAT_WS(' ', IF(con.nombre != '', con.nombre, ''), IF(con.apellido != '', con.apellido, '')) AS nombre_contacto\n\t\t\t\tFROM `opp_oportunidades` AS opp\n\t\t\t\tLEFT JOIN `cl_clientes` AS cl ON `cl`.`uuid_cliente` = `opp`.`uuid_cliente`\n\t\t\t\tLEFT JOIN `cl_cliente_sociedades_contactos` AS clcon ON `clcon`.`uuid_cliente` = `cl`.`uuid_cliente`\n\t\t\t\tLEFT JOIN `con_contactos` AS con ON `con`.`uuid_contacto` = `clcon`.`uuid_contacto`\n\t\t\t\tLEFT JOIN `opp_oportunidades_cat` AS ocat ON `ocat`.`id_cat` = `opp`.`id_etapa_venta`\n\t\t\t\tLEFT JOIN `usuarios` AS usr ON `usr`.`uuid_usuario` = `opp`.`id_asignado`\n\t\t\t\tLEFT JOIN `cl_clientes_cat` AS ccat ON `ccat`.`id_cat` = `cl`.`id_tipo_cliente`\n\t\t\t\tLEFT JOIN `rpo_propiedades` AS prop ON `prop`.`uuid_propiedad` = `opp`.`uuid_propiedad`\n\t\t\t\tLEFT JOIN `cl_clientes_sociedades` AS clso ON `clso`.`uuid_sociedad` = `opp`.`uuid_sociedad`\n\t\t\t\tWHERE `ocat`.`valor` NOT IN('vendido','venta_perdida') {$clause}\n\t\t\t\tGROUP BY `opp`.`id_oportunidad`";
self::$db->sql($sql);
$oportunidades = self::$db->getResult();
if (!empty($oportunidades)) {
$i = 0;
foreach ($oportunidades as $oportunidad) {
$uuid_oportunidad = !empty($oportunidad["uuid_oportunidad"]) ? $oportunidad["uuid_oportunidad"] : "";
$results[$i]["id_oportunidad"] = !empty($oportunidad["id_oportunidad"]) ? $oportunidad["id_oportunidad"] : "";
$results[$i]["uuid_oportunidad"] = !empty($oportunidad["uuid_oportunidad"]) ? $oportunidad["uuid_oportunidad"] : "";
$results[$i]["nombre"] = !empty($oportunidad["nombre"]) ? $oportunidad["nombre"] : "";
$results[$i]["nombre_comercial"] = !empty($oportunidad["nombre_comercial"]) ? $oportunidad["nombre_comercial"] : "";
$results[$i]["tipo_cliente"] = !empty($oportunidad["tipo_cliente"]) ? $oportunidad["tipo_cliente"] : "";
$results[$i]["nombre_cliente"] = !empty($oportunidad["nombre_cliente"]) ? $oportunidad["nombre_cliente"] : "";
$results[$i]["fecha_creacion"] = !empty($oportunidad["fecha_creacion"]) ? $oportunidad["fecha_creacion"] : "";
$results[$i]["usuario_asignado"] = !empty($oportunidad["usuario_asignado"]) ? $oportunidad["usuario_asignado"] : "";
$actividad = array();
if (!empty($oportunidad["uuid_oportunidad"])) {
$sql = "SELECT DISTINCT COALESCE(SUM(actta.puntaje), 0) AS puntaje_total\n\t\t\t\t\t\t\tFROM (`act_actividades` AS act)\n\t\t\t\t\t\t\tLEFT JOIN `act_actividades_cat` AS actcat1 ON `actcat1`.`id_cat` = `act`.`relacionado_con`\n\t\t\t\t\t\t\tLEFT JOIN `act_tipo_actividades` AS actta ON `actta`.`uuid_tipo_actividad` = `act`.`uuid_tipo_actividad`\n\t\t\t\t\t\t\tWHERE `actcat1`.`valor` = 'oportunidades'\n\t\t\t\t\t\t\tAND `act`.`uuid_oportunidad` = UNHEX('" . $oportunidad["uuid_oportunidad"] . "')\n\t\t\t\t\t\t\tAND `act`.`completada` = 1";
self::$db->sql($sql);
$actividad = self::$db->getResult();
}
$results[$i]["puntaje_total"] = 0;
if (!empty($actividad[0]["puntaje_total"]) && $actividad[0]["puntaje_total"] > 0) {
$results[$i]["puntaje_total"] = !empty($actividad[0]["puntaje_total"]) ? (int) $actividad[0]["puntaje_total"] : 0;
$dataset[$i] = (int) $actividad[0]["puntaje_total"];
$i++;
} else {
unset($results[$i]);
unset($dataset[$i]);
}
}
/**
* Hack: para que la funcion PHPExcel_Calculation_Statistical::PERCENTRANK
* No envie el siguiente error, si el array $dataset contiene un solo valor:
*
* A PHP Error was encountered
* Severity: Warning
* Message: Division by zero
* Filename: Calculation/Statistical.php
*/
if (count($dataset) == 1) {
$dataset[] = 0;
}
//ordenar los puntajes
//de menor a mayor
ksort($dataset);
//Recorrer los resultados y calcular el PERCENTRANK
//Usando una clase de la libreria PHPEXcel y introducir
//el resultado en el array de $results
$j = 0;
$percentagerank = "";
foreach ($results as $result) {
if (!empty($result["puntaje_total"]) && $result["puntaje_total"] != "" && $result["puntaje_total"] > 0) {
$percentagerank = PHPExcel_Calculation_Statistical::PERCENTRANK($dataset, $result["puntaje_total"], 2) * 100;
$results[$j]["percentagerank"] = !empty($percentagerank) ? $percentagerank : 0;
} else {
unset($result);
}
$j++;
}
}
return $results;
}
/**
* DVARP
*
* Calculates the variance of a population based on the entire population by using the numbers
* in a column of a list or database that match conditions that you specify.
*
* Excel Function:
* DVARP(database,field,criteria)
*
* @access public
* @category Database Functions
* @param mixed[] $database The range of cells that makes up the list or database.
* A database is a list of related data in which rows of related
* information are records, and columns of data are fields. The
* first row of the list contains labels for each column.
* @param string|integer $field Indicates which column is used in the function. Enter the
* column label enclosed between double quotation marks, such as
* "Age" or "Yield," or a number (without quotation marks) that
* represents the position of the column within the list: 1 for
* the first column, 2 for the second column, and so on.
* @param mixed[] $criteria The range of cells that contains the conditions you specify.
* You can use any range for the criteria argument, as long as it
* includes at least one column label and at least one cell below
* the column label in which you specify a condition for the
* column.
* @return float
*
*/
public static function DVARP($database, $field, $criteria)
{
$field = self::fieldExtract($database, $field);
if (is_null($field)) {
return null;
}
// Return
return PHPExcel_Calculation_Statistical::VARP(self::getFilteredColumn($database, $field, $criteria));
}
public static function CalcAllStats1($returnTarget, $returnBenchmark)
{
// initialize the variables
$returnCount = 0;
$sumTarget = 0.0;
$sumBench = 0.0;
$sumTargetMinusBench = 0.0;
$sumTargetTimesBench = 0.0;
$sumBenchMinusTargetOnBadDays = 0.0;
$sumBenchMinusTargetSquaredOnBadDays = 0.0;
$sumTargetSquared = 0.0;
$sumBenchSquared = 0.0;
$returnTargetCumulative = 1.0;
$returnBenchCumulative = 1.0;
$countGoodDays = 0;
$returnMax = -10000.0;
$returnMin = 10000.0;
$varianceTarget = 0.0;
$varianceBench = 0.0;
$varianceTargetMinusBench = 0.0;
// do the first loop
foreach ($returnTarget as $r) {
$sumTarget += $r;
$sumBench += $returnBenchmark[$returnCount];
$returnTargetCumulative *= $r;
$returnBenchCumulative *= $returnBenchmark[$returnCount];
$sumTargetMinusBench += $r - $returnBenchmark[$returnCount];
$sumTargetTimesBench += $r * $returnBenchmark[$returnCount];
$sumTargetSquared += pow($r, 2);
$sumBenchSquared += pow($returnBenchmark[$returnCount], 2);
if ($r > $returnBenchmark[$returnCount]) {
// A good day
$countGoodDays++;
}
if ($r < 1) {
//threshold = 0
$sumBenchMinusTargetOnBadDays += $returnBenchmark[$returnCount] - $r;
$sumBenchMinusTargetSquaredOnBadDays += pow($returnBenchmark[$returnCount] - $r, 2);
}
if ($r > $returnMax) {
$returnMax = $r;
}
if ($r < $returnMin) {
$returnMin = $r;
}
$returnCount++;
}
if ($returnCount > 1) {
// calculate some figures
$returnTargetCumulative -= 1.0;
$returnBenchCumulative -= 1.0;
$averageTarget = $sumTarget / $returnCount;
$averageBench = $sumBench / $returnCount;
$averageTargetMinusBench = $sumTargetMinusBench / $returnCount;
$averageBenchMinusTargetOnBadDays = $sumBenchMinusTargetOnBadDays / $returnCount;
$averageBenchMinusTargetSquaredOnBadDays = $sumBenchMinusTargetSquaredOnBadDays / $returnCount;
$covarTargetBench = ($sumTargetTimesBench - $sumTarget * $sumBench / $returnCount) / ($returnCount - 1);
// second loop
$i = 0;
foreach ($returnTarget as $r) {
$varianceTarget += pow($r - $averageTarget, 2);
$varianceBench += pow($r - $averageBench, 2);
$varianceTargetMinusBench += pow($r - $averageBench - $averageTargetMinusBench, 2);
$i++;
}
// calculate the output
$varianceTarget /= $returnCount - 1;
$varianceBench /= $returnCount - 1;
$varianceTargetMinusBench /= $returnCount - 1;
$volTarget = sqrt($varianceTarget) * sqrt(240);
$volBench = sqrt($varianceBench) * sqrt(240);
$volTargetMinusBench = sqrt($varianceTargetMinusBench) * sqrt(240);
if ($varianceBench == 0) {
$beta = 0;
} else {
$beta = $covarTargetBench / $varianceBench;
}
if ($volTarget > 0) {
$sharpe = $returnTargetCumulative / $volTarget;
} else {
$sharpe = 0;
}
$alpha = $averageTarget - $beta * $averageBench;
if ($beta == 0) {
$treynor = 0;
} else {
$treynor = $returnTargetCumulative / $beta;
}
$trackingError = $volTargetMinusBench;
if ($volTargetMinusBench > 0) {
$infoQuota = ($returnTargetCumulative - $returnBenchCumulative) / $volTargetMinusBench;
} else {
$infoQuota = 0;
}
$consistency = $countGoodDays / $returnCount;
$x = pow(($returnCount * $sumTargetSquared - pow($sumTarget, 2)) * ($returnCount * $sumBenchSquared - pow($sumBench, 2)), 1 / 2);
//$x = pow(($returnCount*$sumTargetTimesBench-$sumTarget*$sumBench)/pow(($returnCount*$sumTargetSquared-pow($sumTarget,2))*($returnCount*$sumBenchSquared-pow($sumBench,2)),1/2),2);
if ($x > 0) {
$R2 = pow(($returnCount * $sumTargetTimesBench - $sumTarget * $sumBench) / $x, 2);
} else {
$R2 = 0;
}
if ($averageBenchMinusTargetOnBadDays == 0) {
$omega = 0;
$sortino = 0;
// "Div/Null!";
} else {
$omega = 1 + $averageTargetMinusBench / $averageBenchMinusTargetOnBadDays;
$sortino = $averageTargetMinusBench / pow($averageBenchMinusTargetOnBadDays, 1 / 2);
}
$VaR = $averageTargetMinusBench + $volTarget * PHPExcel_Calculation_Statistical::NORMSINV(0.01);
//$VaR=$averageTargetMinusBench+$volTarget*$this->inverse_ncdf(0.01);
} else {
$volTarget = 0;
$sharpe = 0;
$beta = 0;
$treynor = 0;
$trackingError = 0;
$infoQuota = 0;
$consistency = 0;
$R2 = 0;
$VaR = 0;
$averageTarget = 1;
$returnMax = 1;
$returnMin = 1;
$sortino = 0;
$omega = 0;
$averageTargetMinusBench = 0;
$averageBenchMinusTargetOnBadDays = 0;
}
return array($volTarget, $sharpe, $alpha, $beta, $treynor, $trackingError, $infoQuota, $consistency, $R2, $VaR, $averageTarget - 1, $returnMax - 1, $returnMin - 1, $sortino, $omega, $averageTargetMinusBench, $averageBenchMinusTargetOnBadDays);
}
