public function fetchAll($request, $paginated = false) { if ($paginated) { // create a new Select object for the table album $select = new Select('album'); $select->order($request['sort'] . " " . $request['order']); // Search if ($request['search']) { $x = $request['search'] . '%'; $where = new \Zend\Db\Sql\Where(); $where->like('title', $x); $where->or->like('artist', $x); $select->where($where); } // New result set based on the Album entity $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Album()); // New pagination adapter object $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); $paginator = new Paginator($paginatorAdapter); return $paginator; } $resultSet = $this->tableGateway->select(); return $resultSet; }
public function search($profile_name) { $sql = new Sql($this->tableGateway->adapter); $where = new \Zend\Db\Sql\Where(); $select = $sql->select(); $select->from('users'); $where->like('profile_name', '%' . $profile_name . '%'); $select->where($where); $select->order('profile_name'); $select->limit(15); $statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute(); return $results; }
public function adminAction() { $request = $this->params()->fromQuery(); if ($request['search']) { $search = $request['search']; } $prequest = $this->getRequest(); if ($prequest->isPost()) { $search = $prequest->getPost('search'); } if (!$request['page']) { $page = 1; } else { $page = $request['page']; } $username = $this->params()->fromRoute('emp', 0); //Get Records from table $limit = 5; // $adapter = $this->getDbAdapter(); $sql = new Sql($adapter); $select = $sql->select('timetable'); //table name = timetable if ($search) { $where = new \Zend\Db\Sql\Where(); $where->like('username', $search . "%"); $select->where($where); } if ($username) { $select->where(array('username' => $username)); } $selectString = $sql->getSqlStringForSqlObject($select); $result = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE); $count = count($result); $select->limit($limit); $select->offset(($page - 1) * $limit); $selectString = $sql->getSqlStringForSqlObject($select); $result = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE); //Pagination $pages = ceil($count / $limit); $paginator = array('page' => $page, 'pages' => $pages); $form = new EmployeeForm(); //Return results return array('result' => $result, 'paginator' => $paginator, 'form' => $form, 'search' => $search, 'username' => $username); }
public function countPhieuNhapByMaPhieuNhap($array = array()) { $ma_phieu_nhap = $array['ma_phieu_nhap']; $adapter = $this->tableGateway->adapter; $sql = new Sql($adapter); // select $sqlSelect = $sql->select(); $sqlSelect->from(array('t1' => 'phieu_nhap')); $sqlSelect->columns(array(new Expression('COUNT(*) as num'))); $where = new \Zend\Db\Sql\Where(); $where->like('ma_phieu_nhap', '%' . $ma_phieu_nhap . '%'); $sqlSelect->where($where); $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect); $resultSets = $statement->execute(); if ($resultSets->current()) { return $resultSets->current(); } return false; }
/** * Update package assignments * * Sets a new package on existing assignments. Updated assignments have * their status reset to "not notified" and their options (force, schedule, * post cmd) removed. * * @param integer $oldPackageId package to be replaced * @param integer $newPackageId new package * @param bool $deployNonnotified Update assignments with status 'not notified' * @param bool $deploySuccess Update assignments with status 'success' * @param bool $deployNotified Update assignments with status 'notified' * @param bool $deployError Update assignments with status 'error' * @param bool $deployGroups Update assignments for groups * @throws RuntimeException if an error occurs */ public function updateAssignments($oldPackageId, $newPackageId, $deployNonnotified, $deploySuccess, $deployNotified, $deployError, $deployGroups) { if (!($deployNonnotified or $deploySuccess or $deployNotified or $deployError or $deployGroups)) { return; // nothing to do } $clientConfig = $this->_serviceManager->get('Database\\Table\\ClientConfig'); $groupInfo = $this->_serviceManager->get('Database\\Table\\GroupInfo'); $where = new \Zend\Db\Sql\Where(); $where->equalTo('ivalue', $oldPackageId); // Additional filters are only necessary if not all conditions are set if (!($deployNonnotified and $deploySuccess and $deployNotified and $deployError and $deployGroups)) { $groups = $groupInfo->getSql()->select()->columns(array('hardware_id')); $filters = new \Zend\Db\Sql\Where(null, \Zend\Db\Sql\Where::COMBINED_BY_OR); if ($deployNonnotified) { $filters->isNull('tvalue')->and->notIn('hardware_id', $groups); } if ($deploySuccess) { $filters->equalTo('tvalue', \Model\Package\Assignment::SUCCESS); } if ($deployNotified) { $filters->equalTo('tvalue', \Model\Package\Assignment::NOTIFIED); } if ($deployError) { $filters->like('tvalue', \Model\Package\Assignment::ERROR_PREFIX . '%'); } if ($deployGroups) { $filters->in('hardware_id', $groups); } $where->addPredicate($filters); } $now = $this->_serviceManager->get('Library\\Now')->format(\Model\Package\Assignment::DATEFORMAT); try { // Remove DOWNLOAD_* options from updated assignments $subquery = $clientConfig->getSql()->select()->columns(array('hardware_id'))->where(array('name' => 'DOWNLOAD', $where)); $delete = new \Zend\Db\Sql\Where(); $delete->equalTo('ivalue', $oldPackageId)->in('hardware_id', $subquery)->notEqualTo('name', 'DOWNLOAD_SWITCH')->like('name', 'DOWNLOAD_%'); $clientConfig->delete($delete); // Update package ID and reset status $clientConfig->update(array('ivalue' => $newPackageId, 'tvalue' => \Model\Package\Assignment::NOT_NOTIFIED, 'comments' => $now), array('name' => 'DOWNLOAD', $where)); } catch (\Exception $e) { throw new RuntimeException($e->getMessage(), (int) $e->getCode(), $e); } }
/** * * @param type $value * @param type $format * @param type $limit * @param type $offset * @param type $category * @param type $key * @return array */ public function searchValue($value, $format = Format::FORMAT_TEXT, $limit = 30, $offset = 0, $category = "", $key = "") { // init $adapter = $this->getAdapter(); $returnArray = array(); // where $where = new \Zend\Db\Sql\Where(); $where->like('value', $value); if ($format != "" && !is_null($format)) { $where->like("format", $format); } $sql = new \Zend\Db\Sql\Sql($adapter, $this->_tableData); $select = $sql->select(); $select->where($where)->limit($limit)->offset($offset); $stmt = $sql->prepareStatementForSqlObject($select); $resultSet = new ResultSet(); $resultSet->initialize($stmt->execute()); $keyId = $this->_tableId; foreach ($resultSet as $row) { $returnArray[$row->{$keyId}][$row->category][$row->key] = Format::formatValue($row->value, $row->format); } return $returnArray; }
/** * Update package assignments * * Sets a new package on existing assignments. Updated assignments have * their status reset to "pending" and their options (force, schedule, * post cmd) removed. * * @param integer $oldPackageId package to be replaced * @param integer $newPackageId new package * @param bool $deployPending Update assignments with status 'pending' * @param bool $deployRunning Update assignments with status 'running' * @param bool $deploySuccess Update assignments with status 'success' * @param bool $deployError Update assignments with status 'error' * @param bool $deployGroups Update assignments for groups * @throws RuntimeException if an error occurs */ public function updateAssignments($oldPackageId, $newPackageId, $deployPending, $deployRunning, $deploySuccess, $deployError, $deployGroups) { if (!($deployPending or $deployRunning or $deploySuccess or $deployError or $deployGroups)) { return; // nothing to do } $clientConfig = $this->_serviceManager->get('Database\\Table\\ClientConfig'); $groupInfo = $this->_serviceManager->get('Database\\Table\\GroupInfo'); $where = new \Zend\Db\Sql\Where(); $where->equalTo('ivalue', $oldPackageId); $where->equalTo('name', 'DOWNLOAD'); // Additional filters are only necessary if not all conditions are set if (!($deployPending and $deployRunning and $deploySuccess and $deployError and $deployGroups)) { $groups = $groupInfo->getSql()->select()->columns(array('hardware_id')); $filters = new \Zend\Db\Sql\Where(null, \Zend\Db\Sql\Where::COMBINED_BY_OR); if ($deployPending) { $filters->isNull('tvalue')->and->notIn('hardware_id', $groups); } if ($deployRunning) { $filters->equalTo('tvalue', \Model\Package\Assignment::RUNNING); } if ($deploySuccess) { $filters->equalTo('tvalue', \Model\Package\Assignment::SUCCESS); } if ($deployError) { $filters->like('tvalue', \Model\Package\Assignment::ERROR_PREFIX . '%'); } if ($deployGroups) { $filters->in('hardware_id', $groups); } $where->addPredicate($filters); } $now = $this->_serviceManager->get('Library\\Now')->format(\Model\Package\Assignment::DATEFORMAT); try { // Remove DOWNLOAD_* options from updated assignments $subquery = $clientConfig->getSql()->select()->columns(array('hardware_id'))->where($where); // @codeCoverageIgnoreStart if ($clientConfig->getAdapter()->getPlatform()->getName() == 'MySQL') { // MySQL does not allow subquery on the same table for DELETE // statements. Fetch result as a list instead. $subquery = array_column($clientConfig->selectWith($subquery)->toArray(), 'hardware_id'); } // @codeCoverageIgnoreEnd if ($subquery) { // $subquery is either an SQL statement or a non-empty array. $delete = new \Zend\Db\Sql\Where(); $delete->equalTo('ivalue', $oldPackageId)->notEqualTo('name', 'DOWNLOAD_SWITCH')->in('hardware_id', $subquery)->like('name', 'DOWNLOAD_%'); $clientConfig->delete($delete); } // Update package ID and reset status $clientConfig->update(array('ivalue' => $newPackageId, 'tvalue' => \Model\Package\Assignment::PENDING, 'comments' => $now), $where); } catch (\Exception $e) { throw new RuntimeException($e->getMessage(), (int) $e->getCode(), $e); } }