/**
* build the query for the avg caclculation - can be overwritten in plugin class (see date element for eg)
* @param model $listModel
* @param string $label the label to apply to each avg
* @return string sql statement
*/
protected function getAvgQuery(&$listModel, $label = "'calc'")
{
$table =& $listModel->getTable();
$joinSQL = $listModel->_buildQueryJoin();
$whereSQL = $listModel->_buildQueryWhere();
$name = $this->getFullName(false, false, false);
return "SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP({$name}))) AS value, {$label} AS label FROM `{$table->db_table_name}` {$joinSQL} {$whereSQL}";
}
/**
* build the query for the avg caclculation - can be overwritten in plugin class (see date element for eg)
* @param model $listModel
* @param string $label the label to apply to each avg
* @return string sql statement
*/
protected function getAvgQuery(&$listModel, $label = "'calc'")
{
$item = $listModel->getTable();
$joinSQL = $listModel->_buildQueryJoin();
$whereSQL = $listModel->_buildQueryWhere();
$name = $this->getFullName(false, false, false);
$groupModel = $this->getGroup();
$roundTo = (int) $this->getParams()->get('avg_round');
if ($groupModel->isJoin()) {
//element is in a joined column - lets presume the user wants to sum all cols, rather than reducing down to the main cols totals
return "SELECT ROUND(AVG({$name}), {$roundTo}) AS value, {$label} AS label FROM " . FabrikString::safeColName($item->db_table_name) . " {$joinSQL} {$whereSQL}";
} else {
// need to do first query to get distinct records as if we are doing left joins the sum is too large
return "SELECT ROUND(AVG(value), {$roundTo}) AS value, label\nFROM (SELECT DISTINCT {$item->db_primary_key}, {$name} AS value, {$label} AS label FROM " . FabrikString::safeColName($item->db_table_name) . " {$joinSQL} {$whereSQL}) AS t";
}
}
/**
* build the query for the avg caclculation - can be overwritten in plugin class (see date element for eg)
* @param model $tableModel
* @param string $label the label to apply to each avg
* @return string sql statement
*/
protected function getMedianQuery(&$tableModel, $label = "'calc'")
{
$table =& $tableModel->getTable();
$joinSQL = $tableModel->_buildQueryJoin();
$whereSQL = $tableModel->_buildQueryWhere();
$name = $this->getFullName(false, false, false);
//return "SELECT DATE_FORMAT(FROM_UNIXTIME((UNIX_TIMESTAMP($name))), '%H:%i:%s') AS value, $label AS label FROM `$table->db_table_name` $joinSQL $whereSQL";
return "SELECT SEC_TO_TIME(TIME_TO_SEC({$name})) AS value, {$label} AS label FROM `{$table->db_table_name}` {$joinSQL} {$whereSQL}";
}
/**
* build the query for the avg caclculation
* @param model $listModel
* @param string $label the label to apply to each avg
* @return string sql statement
*/
protected function getMedianQuery(&$listModel, $label = "'calc'")
{
$fields = $listModel->getDBFields($this->getTableName(), 'Field');
if ($fields[$this->getElement()->name]->Type == 'time') {
$name = $this->getFullName(false, false, false);
$table =& $listModel->getTable();
$joinSQL = $listModel->_buildQueryJoin();
$whereSQL = $listModel->_buildQueryWhere();
return "SELECT SEC_TO_TIME(TIME_TO_SEC({$name})) AS value, {$label} AS label FROM `{$table->db_table_name}` {$joinSQL} {$whereSQL}";
} else {
return parent::getMedianQuery($listModel, $label);
}
}
/**
* build the query for the avg caclculation - can be overwritten in plugin class (see date element for eg)
* @param model $tableModel
* @param string $label the label to apply to each avg
* @return string sql statement
*/
protected function getAvgQuery(&$tableModel, $label = "'calc'")
{
$table =& $tableModel->getTable();
$joinSQL = $tableModel->_buildQueryJoin();
$whereSQL = $tableModel->_buildQueryWhere();
$name = $this->getFullName(false, false, false);
$groupModel =& $this->getGroup();
if ($groupModel->isJoin()) {
//element is in a joined column - lets presume the user wants to sum all cols, rather than reducing down to the main cols totals
return "SELECT ROUND(AVG({$name})) AS value, {$label} AS label FROM " . FabrikString::safeColName($table->db_table_name) . " {$joinSQL} {$whereSQL}";
} else {
// need to do first query to get distinct records as if we are doing left joins the sum is too large
//return "SELECT ROUND(AVG(value)) AS value, label FROM (SELECT DISTINCT ".FabrikString::safeColName($table->db_table_name.'.'.$table->db_primary_key).", $name AS value, $label AS label FROM ".FabrikString::safeColName($table->db_table_name)." $joinSQL $whereSQL) AS t";
return "SELECT ROUND(AVG(value)) AS value, label FROM (SELECT DISTINCT " . FabrikString::safeColName($table->db_table_name . '.' . $table->db_primary_key) . ", {$name} AS value, {$label} AS label FROM " . FabrikString::safeColName($table->db_table_name) . " {$joinSQL} {$whereSQL}) AS t";
}
}
