/** * Itterate over object, build a relations, fillable and includes collection. * * @param model $object the model to iterate over * * @return array */ private function includes($object) { $fillable = $object->getFillable(); $includes = $object->getIncludes(); $table = $object->getTable(); $columns = $object->columns(); $results[$table] = []; if (!empty($includes)) { foreach ($includes as $include) { $results[$table] = ['object' => $object, 'includes' => $this->includes(new $include())]; } } $this->fillables->put($table, $fillable); $this->includes->push($table, $table); $this->columns->put($table, $columns); return $results; }
/** * 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 * @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 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 - 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"; } }