PHP Zend_Db_Adapter_Abstract::fetchAll方法代码示例

 public function isValid($value, $context = null)
 {
     $result = true;
     $this->_value = $value;
     $whereAnd[] = $this->dbAdapter->quoteInto("ti.box_id = ? ", $this->_data['box_id']);
     if (isset($this->_data['start_dt']) && $this->_data['start_dt'] != '' && $this->_data['start_dt'] == $value) {
         $whereOr[] = $this->dbAdapter->quoteInto("(ti.start_dt < ? AND  ti.end_dt > ? )", HCMS_Utils_Date::dateLocalToIso($this->_data['start_dt']));
     }
     if (isset($this->_data['end_dt']) && $this->_data['end_dt'] != '' && $this->_data['end_dt'] == $value) {
         $whereOr[] = $this->dbAdapter->quoteInto("(ti.start_dt < ? AND  ti.end_dt > ? )", HCMS_Utils_Date::dateLocalToIso($this->_data['end_dt']));
     }
     if (isset($this->_data['id']) && $this->_data['end_dt'] != '') {
         $whereAnd[] = $this->dbAdapter->quoteInto("ti.id != ? ", $this->_data['id']);
     }
     if (isset($this->_data['end_dt']) && $this->_data['end_dt'] != '' && isset($this->_data['start_dt']) && $this->_data['start_dt'] != '') {
         $whereOr[] = $this->dbAdapter->quoteInto("(ti.start_dt >= ?  AND ", HCMS_Utils_Date::dateLocalToIso($this->_data['start_dt'])) . $this->dbAdapter->quoteInto(" ti.end_dt <= ? )", HCMS_Utils_Date::dateLocalToIso($this->_data['end_dt']));
     }
     $whereOr = implode(' OR ', $whereOr);
     $whereAnd = implode(' AND ', $whereAnd);
     $sql = "SELECT COUNT(ti.id) AS doubles\n                FROM teaser_item AS ti\n                WHERE ({$whereOr} )AND {$whereAnd}";
     //        echo "\n\n$sql\n\n"; die();
     $resultSet = $this->dbAdapter->fetchAll($sql);
     if (0 == count($resultSet)) {
         return false;
     }
     $row = $resultSet[0];
     $result = $row['doubles'] == 0;
     if (!$result) {
         $this->_error(self::PERIOD_OCCUPIED);
     }
     return $result;
 }

 public function getTableDataDump($tableName, $step = 100)
 {
     $sql = '';
     if ($this->_read) {
         $quotedTableName = $this->_read->quoteIdentifier($tableName);
         $colunms = $this->_read->fetchRow('SELECT * FROM ' . $quotedTableName . ' LIMIT 1');
         if ($colunms) {
             $arrSql = array();
             $colunms = array_keys($colunms);
             $quote = $this->_read->getQuoteIdentifierSymbol();
             $sql = 'INSERT INTO ' . $quotedTableName . ' (' . $quote . implode($quote . ', ' . $quote, $colunms) . $quote . ')';
             $sql .= ' VALUES ';
             $startRow = 0;
             $select = $this->_read->select();
             $select->from($tableName)->limit($step, $startRow);
             while ($data = $this->_read->fetchAll($select)) {
                 $dataSql = array();
                 foreach ($data as $row) {
                     $dataSql[] = $this->_read->quoteInto('(?)', $row);
                 }
                 $arrSql[] = $sql . implode(', ', $dataSql) . ';';
                 $startRow += $step;
                 $select->limit($step, $startRow);
             }
             $sql = implode("\n", $arrSql) . "\n";
         }
     }
     return $sql;
 }

 public function isValid($value, $context = null)
 {
     $result = true;
     $this->_value = $value;
     $where = array();
     $where[] = $this->dbAdapter->quoteInto("(p.url_id = ? OR pt.url_id = ?)", $value);
     if (isset($this->_data['id'])) {
         $where[] = $this->dbAdapter->quoteInto("(p.id <> ?)", $this->_data['id']);
     }
     if (isset($this->_data['type_id'])) {
         $where[] = $this->dbAdapter->quoteInto("(p.type_id = ?)", $this->_data['type_id']);
     }
     $where = implode(' AND ', $where);
     $sql = "SELECT COUNT(p.id) AS doubles\n                FROM cms_page AS p\n                    LEFT JOIN cms_page_tr AS pt ON (p.id = pt.translation_id AND pt.language = '" . $this->_lang . "')\n                WHERE {$where}";
     //echo "\n\n$sql\n\n"; die();
     $resultSet = $this->dbAdapter->fetchAll($sql);
     if (0 == count($resultSet)) {
         return false;
     }
     $row = $resultSet[0];
     $result = $row['doubles'] == 0;
     if (!$result) {
         $this->_error(self::URLID_EXISTS);
     }
     return $result;
 }

 public function load($printQuery = false, $logQuery = false)
 {
     $this->_select = $this->_read->select();
     $entityTable = $this->getEntity()->getEntityTable();
     $paidTable = $this->getAttribute('grand_total')->getBackend()->getTable();
     $idField = $this->getEntity()->getIdFieldName();
     $this->getSelect()->from(array('sales' => $entityTable), array('store_id', 'lifetime' => 'sum(sales.base_grand_total)', 'avgsale' => 'avg(sales.base_grand_total)', 'num_orders' => 'count(sales.base_grand_total)'))->where('sales.entity_type_id=?', $this->getEntity()->getTypeId())->group('sales.store_id');
     if ($this->_customer instanceof Mage_Customer_Model_Customer) {
         $this->getSelect()->where('sales.customer_id=?', $this->_customer->getId());
     }
     $this->printLogQuery($printQuery, $logQuery);
     try {
         $values = $this->_read->fetchAll($this->getSelect()->__toString());
     } catch (Exception $e) {
         $this->printLogQuery(true, true, $this->getSelect()->__toString());
         throw $e;
     }
     $stores = Mage::getResourceModel('core/store_collection')->setWithoutDefaultFilter()->load()->toOptionHash();
     if (!empty($values)) {
         foreach ($values as $v) {
             $obj = new Varien_Object($v);
             $storeName = isset($stores[$obj->getStoreId()]) ? $stores[$obj->getStoreId()] : null;
             $this->_items[$v['store_id']] = $obj;
             $this->_items[$v['store_id']]->setStoreName($storeName);
             $this->_items[$v['store_id']]->setAvgNormalized($obj->getAvgsale() * $obj->getNumOrders());
             foreach ($this->_totals as $key => $value) {
                 $this->_totals[$key] += $obj->getData($key);
             }
         }
         if ($this->_totals['num_orders']) {
             $this->_totals['avgsale'] = $this->_totals['lifetime'] / $this->_totals['num_orders'];
         }
     }
     return $this;
 }

 /**
  * Run all sql scripts from scripts/dbupdates
  *
  * @return mixed
  */
 public function dbupdAction()
 {
     $console = $this->getConsoleOptions(array('simulation|s=s' => 'Just simulate yes|no'));
     $simulation = $console->getOption("simulation") != 'no';
     if ($simulation) {
         $this->writeLn('RUNNING in SIMULATION mode', null, 'yellow');
     } else {
         $this->writeLn('RUNNING in LIVE mode', null, 'green');
     }
     $appOptions = $this->getInvokeArg('bootstrap')->getOptions();
     $dir = APPLICATION_PATH . '/../scripts/dbupdates';
     //get files
     $sqlFiles = glob($dir . '/*.sql', GLOB_MARK);
     array_walk($sqlFiles, function (&$item) {
         $item = basename($item);
     });
     usort($sqlFiles, function ($a, $b) {
         $al = strtolower($a);
         $bl = strtolower($b);
         if ($al == $bl) {
             return 0;
         }
         return $al > $bl ? +1 : -1;
     });
     $sql = 'SELECT * FROM upgrade_db_log';
     try {
         $result = $this->db->fetchAll($sql);
         foreach ($result as $row) {
             $file = trim($row['file']);
             $index = array_search($file, $sqlFiles);
             if ($index !== FALSE) {
                 unset($sqlFiles[$index]);
             }
         }
     } catch (Exception $exc) {
         if ($exc->getCode() != 42) {
             $this->writeLn($exc->getMessage(), null, 'red');
             exit(1);
         }
     }
     //check if any files
     if (!count($sqlFiles)) {
         $this->writeLn('No files for update', 'yellow');
         exit(0);
     }
     $this->writeLn('Pending SQL files:', null, 'green');
     foreach ($sqlFiles as $sqlFile) {
         $this->writeLn($sqlFile, 'green');
     }
     if (!$simulation) {
         //get files
         foreach ($sqlFiles as $sqlFile) {
             $this->executeQuery($sqlFile, $dir);
         }
     } else {
         $this->writeLn('Please run [composer dbup-exec] to execute scripts.', 'yellow');
     }
     exit(0);
 }

 /**
  * Get all migrated version numbers
  *
  * @return array
  */
 public function fetchAll()
 {
     $select = $this->adapter->select();
     $select->from($this->tableName, 'version');
     $select->order('version ASC');
     $all = $this->adapter->fetchAll($select);
     return array_map(function ($v) {
         return $v['version'];
     }, $all);
 }

 /**
  * Adds all jobs to the queue
  */
 public function execute()
 {
     $sql = "SELECT gcj_id_job\n            FROM gems__comm_jobs\n            WHERE gcj_active = 1\n            ORDER BY gcj_id_order, \n                CASE WHEN gcj_id_survey IS NULL THEN 1 ELSE 0 END,\n                CASE WHEN gcj_round_description IS NULL THEN 1 ELSE 0 END,\n                CASE WHEN gcj_id_track IS NULL THEN 1 ELSE 0 END,\n                CASE WHEN gcj_id_organization IS NULL THEN 1 ELSE 0 END";
     $jobs = $this->db->fetchAll($sql);
     if ($jobs) {
         $batch = $this->getBatch();
         foreach ($jobs as $job) {
             $batch->addTask('Mail\\ExecuteMailJobTask', $job['gcj_id_job']);
         }
     } else {
         $this->getBatch()->addMessage($this->_('Nothing to do, please create a mail job first.'));
     }
 }

 public function load($reviewId)
 {
     $select = $this->_read->select();
     $select->from($this->_reviewTable)->join($this->_reviewDetailTable, "{$this->_reviewTable}.review_id = {$this->_reviewDetailTable}.review_id")->where("{$this->_reviewTable}.review_id = ?", $reviewId);
     $data = $this->_read->fetchRow($select);
     $data['stores'] = array();
     $storesSelect = $this->_read->select();
     $storesSelect->from($this->_reviewStoreTable)->where('review_id=?', $reviewId);
     $stores = $this->_read->fetchAll($storesSelect);
     foreach ($stores as $store) {
         $data['stores'][] = $store['store_id'];
     }
     return $data;
 }

 public function loadEnsuredNodes($category, $rootNode)
 {
     $pathIds = $category->getPathIds();
     $rootNodeId = $rootNode->getId();
     $rootNodePath = $rootNode->getData($this->_pathField);
     $select = clone $this->_select;
     // $select->order($this->_table.'.'.$this->_orderField . ' ASC');
     if ($pathIds) {
         $condition = $this->_conn->quoteInto("{$this->_table}.{$this->_idField} in (?)", $pathIds);
         $select->where($condition);
     }
     $select->joinLeft(array('t6' => 'catalog_category_entity_varchar'), '(catalog_category_entity.entity_id = t6.entity_id and t6.attribute_id = 111)', array('t6.value as name'));
     $select->order('' . $this->_orderField . ' ASC');
     // print_r((string)$select);die;
     $arrNodes = $this->_conn->fetchAll($select);
     if ($arrNodes) {
         $childrenItems = array();
         foreach ($arrNodes as $nodeInfo) {
             $nodeId = $nodeInfo[$this->_idField];
             if ($nodeId <= $rootNodeId) {
                 continue;
             }
             $pathToParent = explode('/', $nodeInfo[$this->_pathField]);
             array_pop($pathToParent);
             $pathToParent = implode('/', $pathToParent);
             $childrenItems[$pathToParent][] = $nodeInfo;
         }
         $this->_addChildNodes($childrenItems, $rootNodePath, $rootNode, true);
     }
 }

 public function getChildren($ID, $start_level = 0, $end_level = 0)
 {
     try {
         $info = $this->getNodeInfo($ID);
     } catch (Exception $e) {
         echo $e->getMessage();
         exit;
     }
     $dbSelect = new Zend_Db_Select($this->_db);
     $dbSelect->from($this->_table)->where($this->_left . ' >= :left')->where($this->_right . ' <= :right')->order($this->_left);
     $this->_addExtTablesToSelect($dbSelect);
     $data = array();
     $data['left'] = $info[$this->_left];
     $data['right'] = $info[$this->_right];
     if (!empty($start_level) && empty($end_level)) {
         $dbSelect->where($this->_level . ' = :minLevel');
         $data['minLevel'] = $info[$this->_level] + $start_level;
     }
     //echo $dbSelect->__toString();
     $data = $this->_db->fetchAll($dbSelect, $data);
     $nodeSet = new Varien_Db_Tree_NodeSet();
     foreach ($data as $node) {
         $nodeSet->addNode(new Varien_Db_Tree_Node($node, $this->getKeys()));
     }
     return $nodeSet;
 }

 public function addConfigField($path, $label, array $data = array(), $default = null)
 {
     $data['level'] = sizeof(explode('/', $path));
     $data['path'] = $path;
     $data['frontend_label'] = $label;
     if ($id = $this->getTableRow('core/config_field', 'path', $path, 'field_id')) {
         $this->updateTableRow('core/config_field', 'field_id', $id, $data);
     } else {
         if (empty($data['sort_order'])) {
             $sql = "select max(sort_order) cnt from " . $this->getTable('core/config_field') . " where level=" . ($data['level'] + 1);
             if ($data['level'] > 1) {
                 $sql .= $this->_conn->quoteInto(" and path like ?", dirname($path) . '/%');
             }
             $result = $this->_conn->raw_fetchRow($sql);
             $this->_conn->fetchAll($sql);
             #print_r($result); die;
             $data['sort_order'] = $result['cnt'] + 1;
             /*
             // Triggers "Command out of sync" mysql error for next statement!?!?
                             $data['sort_order'] = $this->_conn->fetchOne("select max(sort_order)
                                 from ".$this->getTable('core/config_field')."
                                 where level=?".$parentWhere, $data['level'])+1;
             */
         }
         #$this->_conn->raw_query("insert into ".$this->getTable('core/config_field')." (".join(',', array_keys($data)).") values ('".join("','", array_values($data))."')");
         $this->_conn->insert($this->getTable('core/config_field'), $data);
     }
     if (!is_null($default)) {
         $this->setConfigData($path, $default);
     }
     return $this;
 }

 /**
  * Load tree
  *
  * @param   int|Varien_Data_Tree_Node $parentNode
  * @return  Varien_Data_Tree_Dbp
  */
 public function load($parentNode = null)
 {
     $parentPath = '';
     if ($parentNode instanceof Varien_Data_Tree_Node) {
         $parentPath = $parentNode->getData($this->_pathField);
     } elseif (is_numeric($parentNode)) {
         $parentNode = null;
         $select = $this->_conn->select();
         $select->from($this->_table, $this->_pathField)->where("{$this->_idField} = ?", $parentNode);
         $parentPath = $this->_conn->fetchOne($select);
     } elseif (is_string($parentNode)) {
         $parentNode = null;
         $parentPath = $parentNode;
     }
     $select = clone $this->_select;
     $select->order($this->_table . '.' . $this->_orderField . ' ASC');
     if ($parentPath) {
         $condition = $this->_conn->quoteInto("{$this->_table}.{$this->_pathField} like ?", "{$parentPath}/%");
         $select->where($condition);
     }
     $arrNodes = $this->_conn->fetchAll($select);
     $childrenItems = array();
     foreach ($arrNodes as $nodeInfo) {
         $pathToParent = explode('/', $nodeInfo[$this->_pathField]);
         array_pop($pathToParent);
         $pathToParent = implode('/', $pathToParent);
         $childrenItems[$pathToParent][] = $nodeInfo;
     }
     $this->addChildNodes($childrenItems, $parentPath, $parentNode);
     return $this;
 }

 /**
  * Resolve credentials
  *
  * Only the first matching username/realm combination in the file is
  * returned. If the file contains credentials for Digest authentication,
  * the returned string is the password hash, or h(a1) from RFC 2617. The
  * returned string is the plain-text password for Basic authentication.
  *
  * The expected format of the file is:
  *   username:realm:sharedSecret
  *
  * That is, each line consists of the user's username, the applicable
  * authentication realm, and the password or hash, each delimited by
  * colons.
  *
  * @param  string $username Username
  * @param  string $realm    Authentication Realm
  * @throws Zend_Auth_Adapter_Http_Resolver_Exception
  * @return string|false User's shared secret, if the user is found in the
  *         realm, false otherwise.
  */
 public function resolve($username, $realm)
 {
     $exception = null;
     if ($this->_tableName == '') {
         $exception = 'A table must be supplied for the Zend_Auth_Adapter_DbTable authentication adapter.';
     } elseif ($this->_identityColumn == '') {
         $exception = 'An identity column must be supplied for the Zend_Auth_Adapter_DbTable authentication adapter.';
     } elseif ($this->_credentialColumn == '') {
         $exception = 'A credential column must be supplied for the Zend_Auth_Adapter_DbTable authentication adapter.';
     }
     if (null !== $exception) {
         throw new Zend_Auth_Adapter_Http_Resolver_Exception($exception);
     }
     // create result array
     $authResult = array('code' => Zend_Auth_Result::FAILURE, 'identity' => $username, 'messages' => array());
     // get select
     $select = $this->_zendDb->select();
     $select->from($this->_tableName, array('credential' => $this->_credentialColumn))->where($this->_zendDb->quoteIdentifier($this->_identityColumn) . ' = ?', $username);
     // query for the identity
     try {
         $resultIdentities = $this->_zendDb->fetchAll($select->__toString());
     } catch (Exception $e) {
         /**
          * @see Zend_Auth_Adapter_Exception
          */
         require_once 'Zend/Auth/Adapter/Exception.php';
         throw new Zend_Auth_Adapter_Exception('The supplied parameters to Zend_Auth_Adapter_DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.');
     }
     if (count($resultIdentities) != 1) {
         return false;
     }
     $resultIdentity = $resultIdentities[0];
     return $resultIdentity['credential'];
 }

 /**
  * Creates all tokens that should exist, but do not exist
  *
  * NOTE: When overruling this function you should not create tokens because they
  * were deleted by the user
  *
  * @param \Gems_Tracker_RespondentTrack $respTrack The respondent track to check
  * @param int $userId Id of the user who takes the action (for logging)
  * @return int The number of tokens created by this code
  */
 protected function addNewTokens(\Gems_Tracker_RespondentTrack $respTrack, $userId)
 {
     $orgId = $respTrack->getOrganizationId();
     $respId = $respTrack->getRespondentId();
     $respTrackId = $respTrack->getRespondentTrackId();
     // $this->t
     $sql = "SELECT gro_id_round, gro_id_survey, gro_id_order, gro_icon_file, gro_round_description\n            FROM gems__rounds\n            WHERE gro_id_track = ? AND\n                gro_active = 1 AND\n                gro_id_round NOT IN (SELECT gto_id_round FROM gems__tokens WHERE gto_id_respondent_track = ?) AND\n                (gro_organizations IS NULL OR gro_organizations LIKE CONCAT('%|',?,'|%'))\n            ORDER BY gro_id_order";
     $newRounds = $this->db->fetchAll($sql, array($this->_trackId, $respTrackId, $orgId));
     $this->db->beginTransaction();
     foreach ($newRounds as $round) {
         $values = array();
         // From the respondent track
         $values['gto_id_respondent_track'] = $respTrackId;
         $values['gto_id_respondent'] = $respId;
         $values['gto_id_organization'] = $orgId;
         $values['gto_id_track'] = $this->_trackId;
         // From the rounds
         $values['gto_id_round'] = $round['gro_id_round'];
         $values['gto_id_survey'] = $round['gro_id_survey'];
         $values['gto_round_order'] = $round['gro_id_order'];
         $values['gto_icon_file'] = $round['gro_icon_file'];
         $values['gto_round_description'] = $round['gro_round_description'];
         // All other values are not changed by this query and get the default DB value on insertion
         $this->tracker->createToken($values, $userId);
     }
     $this->db->commit();
     return count($newRounds);
 }

 public function loadEnsuredNodes($category, $rootNode)
 {
     $pathIds = $category->getPathIds();
     $rootNodeId = $rootNode->getId();
     $rootNodePath = $rootNode->getData($this->_pathField);
     $select = clone $this->_select;
     $select->order($this->_table . '.' . $this->_orderField . ' ASC');
     if ($pathIds) {
         $condition = $this->_conn->quoteInto("{$this->_table}.{$this->_idField} in (?)", $pathIds);
         $select->where($condition);
     }
     $arrNodes = $this->_conn->fetchAll($select);
     if ($arrNodes) {
         $childrenItems = array();
         foreach ($arrNodes as $nodeInfo) {
             $nodeId = $nodeInfo[$this->_idField];
             if ($nodeId <= $rootNodeId) {
                 continue;
             }
             $pathToParent = explode('/', $nodeInfo[$this->_pathField]);
             array_pop($pathToParent);
             $pathToParent = implode('/', $pathToParent);
             $childrenItems[$pathToParent][] = $nodeInfo;
         }
         $this->_addChildNodes($childrenItems, $rootNodePath, $rootNode, true);
     }
 }