/** * Display a list of all users in the system. * */ public function indexAction() { $filterStatus = $this->_getParam('status', 'any'); $filterTrigger = $this->_getParam('trigger', 'any'); $filterSort = $this->_getParam('sort', 'queueDt'); $filterDirection = $this->_getParam('direction', 'asc'); $form = new Ot_Form_EmailqueueSearch(); $form->populate($_GET); $eq = new Ot_Model_DbTable_EmailQueue(); $select = new Zend_Db_Table_Select($eq); if ($filterStatus != '' && $filterStatus != 'any') { $select->where('status = ?', $filterStatus); } if ($filterTrigger != '' && $filterTrigger != 'any') { $select->where('attributeName = ?', 'triggerActionId'); $select->where('attributeId = ?', $filterTrigger); } $select->order($filterSort . ' ' . $filterDirection); $adapter = new Zend_Paginator_Adapter_DbSelect($select); $paginator = new Zend_Paginator($adapter); $paginator->setCurrentPageNumber($this->_getParam('page', 1)); $ta = new Ot_Model_DbTable_TriggerAction(); $actions = $ta->fetchAll(); $triggers = array(); foreach ($actions as $a) { $triggers[$a->triggerActionId] = $a; } $this->_helper->pageTitle('ot-emailqueue-index:title'); $this->view->assign(array('paginator' => $paginator, 'form' => $form, 'interface' => true, 'sort' => $filterSort, 'direction' => $filterDirection, 'triggers' => $triggers)); }
/** * Builds select order expression * * @param array $orderArray * * @return Zend_Db_Select */ private function _buildSelectObjOrderExpr($orderArray = array()) { if (!empty($orderArray)) { $this->select->order($orderArray); } return $this->select; }
protected function _applySelectOptions(Zend_Db_Table_Select $select, array $options = array()) { if (isset($options[self::OPTION_WHERE])) { foreach ((array) $options[self::OPTION_WHERE] as $where) { $select->where($where); } } if (isset($options[self::OPTION_OR_WHERE])) { foreach ((array) $options[self::OPTION_OR_WHERE] as $orWhere) { $select->orWhere($orWhere); } } if (isset($options[self::OPTION_HAVING])) { foreach ((array) $options[self::OPTION_HAVING] as $having) { $select->having($having); } } if (isset($options[self::OPTION_ORDER])) { foreach ((array) $options[self::OPTION_ORDER] as $order) { $select->order($order); } } $limit = isset($options[self::OPTION_LIMIT]) ? $options[self::OPTION_LIMIT] : null; $offset = isset($options[self::OPTION_OFFSET]) ? $options[self::OPTION_OFFSET] : null; $select->limit($limit, $offset); return $select; }
function __construct(Zend_Controller_Request_Abstract $request, array $orderFields, array $searchFields, Zend_Db_Table_Select $select, Zend_Db_Table_Select $selectFiltered) { // Load the params $iDisplayStart = $request->getParam('iDisplayStart'); $iDisplayLength = $request->getParam('iDisplayLength'); $iSortingCols = intval($request->getParam('iSortingCols')); $sSearch = $request->getParam('sSearch'); // Build sort array $order = array(); if ($iSortingCols > 0) { for ($i = 0; $i < $iSortingCols; $i++) { if (array_key_exists($request->getParam('iSortCol_' . $i), $orderFields)) { $order[] = $orderFields[$request->getParam('iSortCol_' . $i)] . ' ' . strtoupper($request->getParam('sSortDir_' . $i)); } } } // Count the total rows $selectCount = clone $select; if (count($selectCount->getPart(Zend_Db_Table_Select::GROUP)) <= 0) { $selectCount->reset(Zend_Db_Table_Select::COLUMNS)->columns(array('count' => 'COUNT(*)')); $this->_totalCount = $selectCount->getTable()->fetchRow($selectCount)->count; } else { $this->_totalCount = $selectCount->getTable()->fetchAll($selectCount)->count(); } // Append search if ($sSearch !== null && $sSearch != '') { $sSearch = '\'%' . $sSearch . '%\''; $selectFiltered->where('(' . implode(' LIKE ' . $sSearch . ') OR (', $searchFields) . ' LIKE ' . $sSearch . ')'); } // Count the filtered rows $selectCount = clone $selectFiltered; if (count($selectCount->getPart(Zend_Db_Table_Select::GROUP)) <= 0) { $selectCount->reset(Zend_Db_Table_Select::COLUMNS)->columns(array('count' => 'COUNT(*)')); $this->_filteredCount = $selectCount->getTable()->fetchRow($selectCount)->count; } else { $this->_filteredCount = $selectCount->getTable()->fetchAll($selectCount)->count(); } // Load the limited result $selectFiltered->limit($iDisplayLength, $iDisplayStart); if (count($order) > 0) { $selectFiltered->order($order); } $this->_data = $selectFiltered->getTable()->fetchAll($selectFiltered); }
/** * Generate ORDER clause from user-supplied string or array * * @param string|array $order OPTIONAL An SQL ORDER clause. * @return Zend_Db_Table_Select */ protected function _order(Zend_Db_Table_Select $select, $order) { if (!is_array($order)) { $order = array($order); } foreach ($order as $val) { $select->order($val); } return $select; }
/** * Get DataTable data * * @param Zend_Db_Table_Select $select * @param array|null $columns * @return array */ public function direct(Zend_Db_Table_Select $select, $columns = null) { $table = $select->getTable(); if (!$columns) { $columns = $table->info(Zend_Db_Table_Abstract::COLS); } $colCount = count($columns); $request = $this->getRequest(); /* * Ordering */ if (null !== $request->getParam('iSortCol_0', null)) { for ($i = 0, $l = $request->getParam('iSortingCols'); $i < $l; $i++) { if ($request->getParam('bSortable_' . (int) $request->getParam('iSortCol_' . $i))) { $select->order($columns[(int) $request->getParam('iSortCol_' . $i)] . " " . $request->getParam('sSortDir_' . $i)); } } } /* * Filtering * NOTE this does not match the built-in DataTables filtering which does it * word by word on any field. It's possible to do here, but concerned about efficiency * on very large tables, and MySQL's regex functionality is very limited */ if ($search = $request->getParam('sSearch')) { for ($i = 0; $i < $colCount; $i++) { $select->orHaving("{$columns[$i]} LIKE ?", '%' . $search . '%'); } } /* Individual column filtering */ for ($i = 0; $i < $colCount; $i++) { if ($request->getParam('bSearchable_' . $i) == "true") { if ($search = $request->getParam('sSearch_' . $i)) { $select->having("{$columns[$i]} LIKE ?", '%' . $search . '%'); } } } //save current query for fetching data $query = clone $select; $tableName = $table->info(Zend_Db_Table::NAME); $expr = new Zend_Db_Expr('COUNT(*) as total'); /* Data set length after filtering */ if ($select->getPart(Zend_Db_Select::FROM)) { $select->columns($expr); } else { $select->from($tableName, $expr); } /* Data no result */ if ($iFilteredTotal = $table->fetchRow($select)) { $iFilteredTotal = $iFilteredTotal->total; } else { $iFilteredTotal = 0; } $query->limit($request->getParam('iDisplayLength'), $request->getParam('iDisplayStart')); /* * SQL queries * Get data to display */ if ($query->getPart(Zend_Db_Select::FROM)) { $query->columns($columns); } else { $query->from($tableName, $columns); } // Get total rows count $select = $table->select()->from($tableName, $expr); $iTotalRecords = $table->fetchRow($select)->total; return array("sEcho" => (int) $request->getParam('sEcho'), "aaData" => $table->fetchAll($query)->toArray(), "iTotalRecords" => $iTotalRecords, "iTotalDisplayRecords" => $iFilteredTotal); }
/** * Display a list of all users in the system. * */ public function allAction() { $this->view->acl = array('add' => $this->_helper->hasAccess('add'), 'edit' => $this->_helper->hasAccess('edit'), 'delete' => $this->_helper->hasAccess('delete')); $filterUsername = $this->_getParam('username'); $filterFirstName = $this->_getParam('firstName'); $filterLastName = $this->_getParam('lastName'); $filterRole = $this->_getParam('role', 'any'); $filterSort = $this->_getParam('sort', 'username'); $filterDirection = $this->_getParam('direction', 'asc'); $form = new Ot_Form_UserSearch(); $form->populate($this->getAllParams()); $account = new Ot_Model_DbTable_Account(); $accountTbl = $account->info('name'); $select = new Zend_Db_Table_Select($account); $select->from($accountTbl); if ($filterUsername != '') { $select->where($accountTbl . '.username LIKE ?', '%' . $filterUsername . '%'); } if ($filterFirstName != '') { $select->where($accountTbl . '.firstName LIKE ?', '%' . $filterFirstName . '%'); } if ($filterLastName != '') { $select->where($accountTbl . '.lastName LIKE ?', '%' . $filterLastName . '%'); } if ($filterRole != '' && $filterRole != 'any') { $otRole = new Ot_Model_DbTable_AccountRoles(); $roleTbl = $otRole->info('name'); $select->join($roleTbl, $accountTbl . '.accountId = ' . $roleTbl . '.accountId', array()); $select->where($roleTbl . '.roleId = ?', $filterRole); $select->distinct(); } if ($filterSort == 'name') { $select->order('firstName ' . $filterDirection); $select->order('lastName ' . $filterDirection); } else { $select->order($filterSort . ' ' . $filterDirection); } $filterOptions = array('username' => $filterUsername, 'lastname' => $filterLastName, 'firstname' => $filterFirstName, 'direction' => $filterDirection, 'role' => $filterRole, 'sort' => $filterSort); foreach ($filterOptions as $key => $value) { if (!$value) { unset($filterOptions[$key]); } } $adapter = new Zend_Paginator_Adapter_DbSelect($select); $paginator = new Zend_Paginator($adapter); $paginator->setCurrentPageNumber($this->_getParam('page', 1)); $aa = new Ot_Model_DbTable_AuthAdapter(); $adapters = $aa->fetchAll(); $adapterMap = array(); foreach ($adapters as $a) { $adapterMap[$a->adapterKey] = $a; } $this->_helper->pageTitle('ot-account-all:title'); $this->view->assign(array('paginator' => $paginator, 'form' => $form, 'interface' => true, 'sort' => $filterSort, 'direction' => $filterDirection, 'adapters' => $adapterMap, 'filterOptions' => array('urlParams' => $filterOptions))); }
/** * Pobranie listy delegacji i zwrócenie paginatora * @param Zend_Db_Table_Select $statement - warunki dla których ma być pobrana lista * @return Zend_Paginator - lista delegacji */ private function get_items_list_paginator(Zend_Db_Table_Select $statement) { $request = Zend_Controller_Front::getInstance()->getRequest(); $page = $request->getParam('page', 1); $sort = $request->getParam('sort', 'id'); $order = $request->getParam('order', 'asc'); $statement->order("{$sort} {$order}"); $adapter = new Zend_Paginator_Adapter_DbTableSelect($statement); $paginator = new Base_Paginator($adapter); $paginator->setCurrentPageNumber($page); return $paginator; }
/** * Ordering to be applied on the result set. * * @param boolean $order Sort ascending if true, descending otherwise. * @return Opus_DocumentFinder Fluent interface. */ public function orderByServerDatePublished($order = true) { $this->select->order('d.server_date_published ' . ($order ? 'ASC' : 'DESC')); return $this; }