public function show()
{
$vars = array("name" => $this->name);
//get data from DB
$this->parseURLParameters();
$totalPages = $totalRows = 0;
$pagingEnabled = $this->pageSize > 0;
$countSQLInMainSelect = false;
if ($pagingEnabled) {
//use direct count if there's no distinct inside of SELECT statement
//that's 10 times faster than count loaded results
$countSQLType = $this->countSQLType;
if (!$countSQLType) {
if (in_array(get_class($this->db), array('Zend_Db_Adapter_Mysqli', 'Zend_Db_Adapter_Pdo_Mysql'))) {
$countSQLType = "SQL_CALC_FOUND_ROWS";
} elseif (preg_match("/\\s((distinct)|(group by))\\s/i", $this->selectSQL)) {
$countSQLType = "subselect";
} else {
$countSQLType = "simple";
}
}
switch ($countSQLType) {
case 'SQL_CALC_FOUND_ROWS':
$countSQL = preg_replace("/(^\\s*)SELECT/smi", '$1SELECT SQL_CALC_FOUND_ROWS ', $this->selectSQL);
$countSQLInMainSelect = true;
break;
case 'simple':
//SELECT.+\\sFROM is greedy to replace cases with multi selects
$countSQL = "SELECT COUNT(*) FROM " . preg_replace("/^\\s*SELECT.+\\sFROM\\s/smi", "", $this->selectSQL);
break;
case 'subselect':
default:
$countSQL = "select count(*) from (" . $this->selectSQL . ") a";
}
if (!$countSQLInMainSelect) {
$totalRows = $this->db->fetchOne($countSQL);
$totalPages = ceil($totalRows / $this->pageSize);
} else {
$totalRows = $this->page * $this->pageSize + 1;
$totalPages = $this->page + 1;
}
}
$sql = $countSQLInMainSelect ? $countSQL : $this->selectSQL;
if ($this->sortOrder && array_key_exists($this->sortOrder, $this->sortOrders)) {
if (is_array($this->sortOrders[$this->sortOrder])) {
$sql .= " ORDER BY " . $this->sortOrders[$this->sortOrder][$this->sortDirection == "ASC" ? 0 : 1];
} else {
$sql .= " ORDER BY " . $this->sortOrders[$this->sortOrder] . ($this->sortDirection == "ASC" ? "" : " DESC");
}
} elseif ($this->defaultSortOrder) {
$sql .= " ORDER BY " . $this->defaultSortOrder;
}
if ($totalPages == 0) {
$this->page = 1;
} elseif ($this->page > $totalPages) {
$this->page = $totalPages;
}
if ($pagingEnabled) {
$sql .= " LIMIT " . ($this->page - 1) * $this->pageSize . ", " . $this->pageSize;
}
$vars["rows"] = $this->db->fetchAll($sql);
if (!$pagingEnabled) {
$totalRows = count($vars["rows"]);
$totalPages = ceil($totalRows / $this->pageSize);
} elseif ($countSQLInMainSelect) {
$totalRows = $this->db->fetchOne("SELECT FOUND_ROWS()");
$totalPages = ceil($totalRows / $this->pageSize);
}
$vars["totalRows"] = $totalRows;
$vars["totalPages"] = $totalPages;
//set URI for subcomponents(navigator, sorter)
$params = $this->request->getParams();
//remove empty variables
foreach ($params as $name => $value) {
if (empty($value) && $value !== "0") {
unset($params[$name]);
}
}
$params['controller'] = $this->request->getControllerName();
$params['action'] = $this->request->getActionName();
if (array_key_exists($this->name . 'Page', $params)) {
unset($params[$this->name . 'Page']);
}
$vars["pagerURI"] = $this->actionController->getHelper('Url')->url($params, null, true) . '/';
//remove sorter parameters
if (array_key_exists($this->name . 'Order', $params)) {
unset($params[$this->name . 'Order']);
}
if (array_key_exists($this->name . 'Dir', $params)) {
unset($params[$this->name . 'Dir']);
}
$vars["sorterURI"] = $this->actionController->getHelper('Url')->url($params, null, true) . '/';
$vars["page"] = $this->page;
$vars["pageSize"] = $this->pageSize;
$vars["sortOrder"] = $this->sortOrder;
$vars["sortDirection"] = $this->sortDirection;
$this->view->{$this->name} = $vars;
}
