Inheritance: extends Zend_Db_Select
 /**
  * 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));
 }
Beispiel #2
0
 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 : '');
 }
Beispiel #3
0
 public function onStatistics($event)
 {
     $table = Engine_Api::_()->getDbTable('playlists', 'music');
     $select = new Zend_Db_Table_Select($table);
     $select->from($table->info('name'), array('COUNT(*) AS count'));
     $event->addResponse($select->query()->fetchColumn(0), 'playlist');
     $table = Engine_Api::_()->getDbTable('playlistSongs', 'music');
     $select = new Zend_Db_Table_Select($table);
     $select->from($table->info('name'), array('COUNT(*) AS count'));
     $event->addResponse($select->query()->fetchColumn(0), 'song');
 }
Beispiel #4
0
 function __construct(Zend_Db_Table_Select $select, $count = 15, $current = 1)
 {
     $table = $select->getTable();
     $this->select = $select->distinct();
     $this->row_count = $table->countRows($select);
     $this->pages_count = intval(ceil($this->row_count / $count));
     $current = min($current, $this->pages_count);
     // selection les tuples de cette pages.
     $select->limitPage($current, $count);
     $rowset = $table->fetchAll($select);
     parent::__construct($rowset, $count, $current);
     $this->pages_id = range(1, $this->pagesCount(), 1);
 }
Beispiel #5
0
 private function parseSelect(\Zend_Db_Table_Select $select)
 {
     $return = array();
     foreach ($select->getPart('columns') as $c) {
         if ($c[1] instanceof \Zend_Db_Expr) {
             $return[] = $c[1];
         } elseif ($c[1] == '*') {
             foreach ($this->table->info(\Zend_Db_Table_Abstract::COLS) as $col) {
                 $return[] = '`' . $c[0] . '`.`' . $col . '`';
             }
         } else {
             $return[] = '`' . $c[0] . '`.`' . $c[1] . '`';
         }
     }
     return $return;
 }
 public function limit($count = null, $offset = null)
 {
     if (is_array($count)) {
         $offset = $count['start'];
         $count = $count['limit'];
     }
     return parent::limit($count, $offset);
 }
Beispiel #7
0
 public function assemble()
 {
     $assembled = parent::assemble();
     /** @var $logger Logger_Application_Logger */
     $logger = Zend_Registry::get('logger');
     $logger->log(__CLASS__ . ":: " . $assembled, "system", Zend_log::DEBUG);
     return $assembled;
 }
Beispiel #8
0
 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;
 }
 /**
  * Builds order expression
  *
  * @param array $limitArray
  * 
  * @return Zend_Db_Select
  */
 private function _buildSelectObjLimitExpr($limitArray)
 {
     if (isset($limitArray['offset'])) {
         $this->select->limit($limitArray['limit'], $limitArray['offset']);
     } else {
         $this->select->limit($limitArray['limit']);
     }
     return $this->select;
 }
Beispiel #10
0
 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);
 }
Beispiel #11
0
 /**
  * 
  * @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);
 }
Beispiel #12
0
 /**
  * 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;
 }
Beispiel #13
0
 /**
  * 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;
 }
Beispiel #14
0
 protected function buildSQl()
 {
     $this->_buildServiceSql();
     $this->_filterSql();
     return $this->_select->__toString();
 }
Beispiel #15
0
 /**
  * Set bind variables
  *
  * @param mixed $bind
  * @return Zend_Db_Select
  */
 public function bind($bind)
 {
     if (!empty($bind)) {
         if (!is_array($bind)) {
             $bind = array($bind);
         }
         parent::bind($bind);
     }
     return $this;
 }
 public function addRule(Zend_Db_Table_Select &$select)
 {
     $select->join(array("tr" => "trader"), "tr.id = trader_id");
 }
Beispiel #17
0
    /**
     * Get a page
     *
     * @var int $page
     * @return Zend_Db_Table_Rowset_Abstract
     */
    public function getPage($page)
    {
        $this->_select->limitPage((int) $page, $this->getRowLimit());

        return $this->_table->fetchAll($this->_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;
 }
Beispiel #19
0
 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));
 }
Beispiel #20
0
 public function joinInner($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
 {
     parent::joinInner($name, $cond, $cols, $schema);
     Centurion_Signal::factory('on_select_joinInner')->send($this, array($this, $name));
     return $this;
 }
 public function addRule(Zend_Db_Table_Select &$select)
 {
     $select->join(array("acc" => "account"), "acc.id = account_id");
 }
Beispiel #22
0
 /**
  * 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;
 }
Beispiel #23
0
 protected function addAutoJoin(Zend_Db_Table_Select $select)
 {
     $tables = array();
     $columns = array();
     $references = $this->getDefaultAdapter()->getReferences(null, $this->_name);
     if ($references) {
         foreach ($references as $key => $reference) {
             $tableName = $reference['table'];
             $table = new La_Db_Table($tableName);
             $columnName = $table->getNameForOptionField();
             $comments = $this->getComments();
             $column = $reference['columns'];
             $columnAlias = $comments[$column];
             $tableAlias = $tableName . $key;
             $columns[$columnAlias] = $tableAlias . '.' . $columnName;
             $tables[$tableName] = $tableName;
             $joinTable = array($tableAlias => $tableName);
             $condition = sprintf('`%s`.`id` = `%s`.`%s`', $tableAlias, $this->_name, $column);
             $select->joinLeft($joinTable, $condition, array());
         }
         $select->setIntegrityCheck(false)->columns($columns);
     }
     return $select;
 }
Beispiel #24
0
 /**
  * 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;
 }
 /**
  * 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)));
 }
Beispiel #26
0
 /**
  * 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);
 }