/** * 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)); }
protected function _filterSql() { $date = new Zend_Date(); if (isset($this->filterdata['created_by']) && !empty($this->filterdata['created_by'])) { $this->_select->where("p.created_by = (?)", new Zend_Db_Expr("SELECT id FROM public.user WHERE upper(login) ilike '" . strtoupper($this->filterdata['created_by'] . "'"))); } if (isset($this->filterdata['name']) && !empty($this->filterdata['name'])) { $this->_select->where("p.name ilike ?", $this->filterdata['name']); } if (isset($this->filterdata['date_for']) && !empty($this->filterdata['date_for'])) { $this->filterdata['date_for'] = $date->setDate($this->filterdata['date_for'], 'YYYY-MM-dd')->toString(Zend_Date::ISO_8601); } if (isset($this->filterdata['date_to']) && !empty($this->filterdata['date_to'])) { $this->filterdata['date_to'] = $date->setDate($this->filterdata['date_to'], 'YYYY-MM-dd')->toString(Zend_Date::ISO_8601); } if (isset($this->filterdata['date_for']) && !empty($this->filterdata['date_for']) && isset($this->filterdata['date_to']) && !empty($this->filterdata['date_to'])) { $this->_select->where('created_at BETWEEN \'' . $this->filterdata['date_for'] . '\' AND \'' . $this->filterdata['date_to'] . '\''); } elseif (isset($this->filterdata['date_for']) && !empty($this->filterdata['date_for'])) { $this->_select->where('created_at > ?', $this->filterdata['date_for']); } elseif (isset($this->filterdata['date_to']) && !empty($this->filterdata['date_to'])) { $this->_select->where('created_at < ?', $this->filterdata['date_to']); } if (isset($this->filterdata['ws_service_set_id']) && !empty($this->filterdata['ws_service_set_id'])) { $this->_select->where("ws_service_set_id = ?", $this->filterdata['ws_service_set_id']); } if (isset($this->filterdata['ws_service_group_id']) && !empty($this->filterdata['ws_service_group_id'])) { $this->_select->where("ws_service_group_id = ?", $this->filterdata['ws_service_group_id']); } }
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; }
public function getCertPath($extension = null) { $select = new Zend_Db_Table_Select(new WsServiceSmsCert()); $select->where('ghost IS FALSE')->where('is_archived IS FALSE'); $row = $this->findDependentRowset('WsServiceSmsCert', null, $select)->current(); if (null === $row) { return null; } return APPLICATION_PATH . $row->file_path . DIRECTORY_SEPARATOR . $row->file_name . ($extension ? '.' . $extension : ''); }
/** * * @param string $groupType * @return type */ protected function _getServiceGroup($groupType, $groupId = null) { $matchTable = "WsService" . ucfirst($groupType) . "Group"; $referenceTable = "WsClientHasServiceGroup"; if ($groupType == Wsclient::GROUP_SERVICE_SET) { $matchTable = "WsServiceSet"; $referenceTable = "WsClientHasServiceSet"; } $select = null; if (false === class_exists($matchTable) || false === class_exists($referenceTable)) { throw new Logic_Exception("Class {$matchTable} or {$referenceTable} not found!"); } $select = new Zend_Db_Table_Select(new $matchTable()); $select->where('i.ghost=?', 'FALSE')->where('m.ghost=?', 'FALSE'); if (null !== $groupId) { $select->where('m.id=?', $groupId); } return $this->_client->findManyToManyRowset($matchTable, $referenceTable, null, null, $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); }
public function where($cond, $value = null, $type = null) { if (is_array($cond)) { foreach ($cond as $key => $val) { // is $key an int? if (is_int($key)) { // $val is the full condition $this->where($val); } else { // $key is the condition with placeholder, // and $val is quoted into the condition $this->where($key, $val); } } return $this; } else { return parent::where($cond, $value, $type); } }
/** * Generate WHERE clause from user-supplied string or array * * @param string|array $where OPTIONAL An SQL WHERE clause. * @return Zend_Db_Table_Select */ protected function _where(Zend_Db_Table_Select $select, $where) { $where = (array) $where; foreach ($where as $key => $val) { // is $key an int? if (is_int($key)) { // $val is the full condition $select->where($val); } else { // $key is the condition with placeholder, // and $val is quoted into the condition $select->where($key, $val); } } return $select; }
/** * Add some data from other table, tests the joinTables * property. If not empty add tables and join clauses. * * @param Zend_Db_Table_Select $select * @param array $params * * @return Zend_Db_Table_Select */ private function _addJoinQuery($select, array $params = array()) { if (isset($params['joinTables']) && count($params['joinTables'])) { $this->_joinTables = $params['joinTables']; } /* If needs to add some data from other table, tests the joinTables * property. If not empty add tables and join clauses. */ if (count($this->_joinTables) > 0) { // Get the constraint attribute = foreign key to link tables. $constraint = $params['constraint']; // Loop on tables list(given by object class) to build the query foreach ($this->_joinTables as $key => $object) { //Create an object and fetch data from object. $tmpObject = new $object(); $tmpDataTable = $tmpObject->getDataTableName(); $tmpIndexTable = $tmpObject->getIndexTableName(); $tmpColumnData = $tmpObject->getDataColumns(); $tmpColumnIndex = $tmpObject->getIndexColumns(); //Add data to tables list $tables[$tmpDataTable] = $tmpColumnData; $tables[$tmpIndexTable] = $tmpColumnIndex; //Get the primary key of the first data object to join table $tmpDataId = $tmpObject->getDataId(); // If it's the first loop, join first table to the current table if ($key == 0) { $select->joinLeft($tmpDataTable, $tmpDataId . ' = ' . $constraint); //If there's an index table then it too and filter according language if (!empty($tmpIndexTable)) { $tmpIndexId = $tmpObject->getIndexId(); $select->joinLeft($tmpIndexTable, $tmpDataId . ' = ' . $tmpIndexId); $select->where($tmpObject->getIndexLanguageId() . ' = ?', $this->_defaultEditLanguage); } /* If there's more than one table to link, store the current * table name for the next loop */ if (count($this->_joinTables) > 1) { $prevConstraint = $tmpObject->getConstraint(); } } elseif ($key > 0) { // We have an other table to join to previous. $tmpDataId = $tmpObject->getDataId(); $select->joinLeft($tmpDataTable, $prevConstraint . ' = ' . $tmpDataId); if (!empty($tmpIndexTable)) { $tmpIndexId = $tmpObject->getIndexId(); $select->joinLeft($tmpIndexTable, $constraint . ' = ' . $tmpIndexId); $select->where($tmpObject->getIndexLanguageId() . ' = ?', $this->_defaultEditLanguage); } } } } return $select; }
/** * Returns the next cron job based on the next date field * * @return null|Enlight_Components_Cron_Job */ public function getNextJob() { $sql = new Zend_Db_Table_Select($this); $sql->where($this->getAdapter()->quoteIdentifier($this->_columns['active']), 1)->where($this->getAdapter()->quoteIdentifier($this->_columns['end']), ' IS NOT NULL')->where($this->getAdapter()->quoteIdentifier($this->_columns['next']), new Zend_Date()); $row = $this->fetchRow($sql); if (count($row) === 0) { return null; } $row['next'] = new Zend_Date($row['next']); $row['start'] = new Zend_Date($row['start']); $row['end'] = new Zend_Date($row['end']); $row['data'] = unserialize($row['data']); $retVal = new Enlight_Components_Cron_Job($row->toArray()); return $retVal; }
/** * * @param Zend_Db_Table_Select $select * @return type */ protected function _fetch(Zend_Db_Table_Select $select) { if (in_array('deleted', $this->_getCols())) { $select->where($this->_name . '.deleted = 0'); } /* if ($this->getAutoRestrict()) { if (in_array($this->getAutoRestrict(), $this->_getCols())) { $identity = Zend_Auth::getInstance()->getIdentity(); if (isset($identity->empresa_id) && $identity->empresa_id) { $select->where($this->_name . '.' . $this->getAutoRestrict() .' = ?', $identity->empresa_id); } } } */ if ($this->getAutoJoin()) { $select = $this->addAutoJoin($select); } return parent::_fetch($select); }
/** * 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))); }
protected function addWhereActive(Zend_Db_Table_Select $oSelect, $prefix = '') { $prefix && ($prefix .= '.'); $date = new Zend_Date(); $date->add('1', Zend_Date::DAY); return $oSelect->where($prefix . 'PER > ?', $this->getDbTable()->PDO_dateFormat($date)); }
/** * Only return documents with at leat one file marked as visible in oai. * * @return Opus_DocumentFinder Fluent interface. */ public function setFilesVisibleInOai() { $subselect = "SELECT DISTINCT document_id\n FROM document_files AS f\n WHERE f.document_id = d.id\n AND f.visible_in_oai=1"; $this->select->where('d.id IN (' . $subselect . ')'); return $this; }