PHP Zend_Db_Adapter_Abstract::fetchCol方法代码示例

 protected function store()
 {
     $fileTable = IcingaConfigFile::$table;
     $fileKey = IcingaConfigFile::$keyName;
     $this->db->beginTransaction();
     try {
         $existingQuery = $this->db->select()->from($fileTable, 'checksum')->where('checksum IN (?)', array_map(array($this, 'dbBin'), $this->getFilesChecksums()));
         $existing = $this->db->fetchCol($existingQuery);
         foreach ($existing as $key => $val) {
             if (is_resource($val)) {
                 $existing[$key] = stream_get_contents($val);
             }
         }
         $missing = array_diff($this->getFilesChecksums(), $existing);
         /** @var IcingaConfigFile $file */
         foreach ($this->files as $name => $file) {
             $checksum = $file->getChecksum();
             if (!in_array($checksum, $missing)) {
                 continue;
             }
             $this->db->insert($fileTable, array($fileKey => $this->dbBin($checksum), 'content' => $file->getContent()));
         }
         $this->db->insert(self::$table, array('duration' => $this->generationTime, 'last_activity_checksum' => $this->dbBin($this->getLastActivityChecksum()), 'checksum' => $this->dbBin($this->getChecksum())));
         /** @var IcingaConfigFile $file */
         foreach ($this->files as $name => $file) {
             $this->db->insert('director_generated_config_file', array('config_checksum' => $this->dbBin($this->getChecksum()), 'file_checksum' => $this->dbBin($file->getChecksum()), 'file_path' => $name));
         }
         $this->db->commit();
     } catch (Exception $e) {
         $this->db->rollBack();
         throw $e;
         var_dump($e->getMessage());
     }
     return $this;
 }

 public function loadGroupByRelationValues($fieldname, $condition, $countValues = false)
 {
     if ($condition) {
         $condition = "WHERE " . $condition;
     }
     if ($countValues) {
         if ($this->model->getVariantMode() == OnlineShop_Framework_IProductList::VARIANT_MODE_INCLUDE_PARENT_OBJECT) {
             $query = "SELECT dest as `value`, count(DISTINCT src_virtualProductId) as `count` FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
         } else {
             $query = "SELECT dest as `value`, count(*) as `count` FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
         }
         $subquery = "SELECT a.o_id FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition;
         $query .= " AND src IN (" . $subquery . ") GROUP BY dest";
         OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO);
         $result = $this->db->fetchAssoc($query);
         OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO);
         return $result;
     } else {
         $query = "SELECT dest FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
         $subquery = "SELECT a.o_id FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition;
         $query .= " AND src IN (" . $subquery . ") GROUP BY dest";
         OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO);
         $result = $this->db->fetchCol($query);
         OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO);
         return $result;
     }
 }

 protected function _getSqlDropSequence(Zend_Db_Adapter_Abstract $db, $sequenceName)
 {
     $seqList = $db->fetchCol('SELECT sequence_name FROM ALL_SEQUENCES');
     if (in_array($sequenceName, $seqList)) {
         return 'DROP SEQUENCE';
     }
     return null;
 }

 /**
  * remove all relations for application
  *
  * @param string $applicationName
  *
  * @return void
  */
 public function removeApplication($applicationName)
 {
     $tableName = SQL_TABLE_PREFIX . 'relations';
     $select = $this->_db->select()->from($tableName)->columns('rel_id')->where($this->_db->quoteIdentifier('own_model') . ' LIKE ?', $applicationName . '_%');
     $relation_ids = $this->_db->fetchCol($select);
     if (is_array($relation_ids) && count($relation_ids) > 0) {
         $this->_db->delete($tableName, $this->_db->quoteInto($this->_db->quoteIdentifier('rel_id') . ' IN (?)', $relation_ids));
     }
 }

 /**
  * Test the Adapter's fetchCol() method.
  */
 public function testAdapterFetchCol()
 {
     $id = $this->getResultSetKey('id');
     $table = $this->getIdentifier(self::TABLE_NAME);
     $result = $this->_db->fetchCol('SELECT * FROM ' . $this->_db->quoteIdentifier($table) . ' WHERE date_created > ? ORDER BY id', array('2006-01-01'));
     $this->assertEquals(2, count($result));
     // count rows
     $this->assertEquals(1, $result[0]);
     $this->assertEquals(2, $result[1]);
 }

 /**
  * @param $oo_id
  * @param bool $createMissingChildrenRows
  * @throws \Zend_Db_Adapter_Exception
  */
 public function doUpdate($oo_id, $createMissingChildrenRows = false)
 {
     if (empty($this->fields) && empty($this->relations) && !$createMissingChildrenRows) {
         return;
     }
     $this->idTree = [];
     $fields = implode("`,`", $this->fields);
     if (!empty($fields)) {
         $fields = ", `" . $fields . "`";
     }
     $result = $this->db->fetchRow("SELECT " . $this->idField . " AS id" . $fields . " FROM " . $this->storetable . " WHERE " . $this->idField . " = ?", $oo_id);
     $o = new \stdClass();
     $o->id = $result['id'];
     $o->values = $result;
     $o->childs = $this->buildTree($result['id'], $fields);
     if (!empty($this->fields)) {
         foreach ($this->fields as $fieldname) {
             foreach ($o->childs as $c) {
                 $this->getIdsToUpdateForValuefields($c, $fieldname);
             }
             $this->updateQueryTable($oo_id, $this->fieldIds[$fieldname], $fieldname);
         }
     }
     if (!empty($this->relations)) {
         foreach ($this->relations as $fieldname => $fields) {
             foreach ($o->childs as $c) {
                 $this->getIdsToUpdateForRelationfields($c, $fieldname);
             }
             if (is_array($fields)) {
                 foreach ($fields as $f) {
                     $this->updateQueryTable($oo_id, $this->fieldIds[$fieldname], $f);
                 }
             } else {
                 $this->updateQueryTable($oo_id, $this->fieldIds[$fieldname], $fieldname);
             }
         }
     }
     // check for missing entries which can occur in object bricks and localized fields
     // this happens especially in the following case:
     // parent object has no brick, add child to parent, add brick to parent & click save
     // without this code there will not be an entry in the query table for the child object
     if ($createMissingChildrenRows) {
         $idsToUpdate = $this->extractObjectIdsFromTreeChildren($o->childs);
         if (!empty($idsToUpdate)) {
             $idsInTable = $this->db->fetchCol("SELECT " . $this->idField . " FROM " . $this->querytable . " WHERE " . $this->idField . " IN (" . implode(",", $idsToUpdate) . ")");
             $diff = array_diff($idsToUpdate, $idsInTable);
             // create entries for children that don't have an entry yet
             $originalEntry = $this->db->fetchRow("SELECT * FROM " . $this->querytable . " WHERE " . $this->idField . " = ?", $oo_id);
             foreach ($diff as $id) {
                 $originalEntry[$this->idField] = $id;
                 $this->db->insert($this->querytable, $originalEntry);
             }
         }
     }
 }

 /**
  * @param mixed $parentId
  * @param int $parentLevel
  * @return $this
  */
 protected function _updateChildLevels($parentId, $parentLevel)
 {
     $select = $this->_conn->select()->from($this->_table, $this->_idField)->where($this->_parentField . '=?', $parentId);
     $ids = $this->_conn->fetchCol($select);
     if (!empty($ids)) {
         $this->_conn->update($this->_table, [$this->_levelField => $parentLevel + 1], $this->_conn->quoteInto($this->_idField . ' IN (?)', $ids));
         foreach ($ids as $id) {
             $this->_updateChildLevels($id, $parentLevel + 1);
         }
     }
     return $this;
 }

 /**
  * remove all relations for application
  *
  * @param string $applicationName
  *
  * @return void
  */
 public function removeApplication($applicationName)
 {
     $tableName = SQL_TABLE_PREFIX . 'relations';
     $select = $this->_db->select()->from($tableName, array('rel_id'))->where($this->_db->quoteIdentifier('own_model') . ' LIKE ?', $applicationName . '_%')->limit(10000);
     do {
         $relation_ids = $this->_db->fetchCol($select);
         if (is_array($relation_ids) && count($relation_ids) > 0) {
             $this->_db->delete($tableName, $this->_db->quoteInto($this->_db->quoteIdentifier('rel_id') . ' IN (?)', $relation_ids));
         } else {
             break;
         }
     } while (true);
 }

 protected function _update($type)
 {
     $countSelect = $this->_read->select()->from($this->_summaryTable, 'summary_id')->where('type_id=?', $type['type_id'])->having("('" . now() . "' - INTERVAL {$type['period']} {$type['period_type']}) <= MAX(add_date)");
     $summaryIds = $this->_read->fetchCol($countSelect);
     $customerSelect = $this->_read->select()->from($this->_customerTable, 'visitor_id')->where("? - INTERVAL {$type['period']} {$type['period_type']} <= login_at", now())->where("logout_at IS NULL OR logout_at <= ? - INTERVAL {$type['period']} {$type['period_type']}", now());
     $customers = $this->_read->fetchCol($customerSelect);
     $customerCount = count($customers);
     $customers = $customerCount > 0 ? $customers : 0;
     $customersCondition = $this->_read->quoteInto('visitor_id NOT IN(?)', $customers);
     $visitorCount = $this->_read->fetchOne("SELECT COUNT(visitor_id) FROM {$this->_visitorTable} WHERE ('" . now() . "' - INTERVAL {$type['period']} {$type['period_type']}) <= first_visit_at OR (NOW() - INTERVAL {$type['period']} {$type['period_type']}) <= last_visit_at AND {$customersCondition}");
     if ($customerCount == 0 && $visitorCount == 0) {
         return;
     }
     $data = array('type_id' => $type['type_id'], 'visitor_count' => $visitorCount, 'customer_count' => $customerCount, 'add_date' => now());
     if (count($summaryIds) == 0) {
         $this->_write->insert($this->_summaryTable, $data);
     } else {
         $conditionSql = $this->_write->quoteInto('summary_id in (?)', $summaryIds);
         $this->_write->update($this->_summaryTable, $data, $conditionSql);
     }
 }

 /**
  * Returns a list of the tables in the database.
  *
  * @return array
  */
 public function listTables()
 {
     $sql = "SELECT tabname " . "FROM systables ";
     return $this->_adapter->fetchCol($sql);
 }

 /**
  * Fetch the first column of all rows of the result set as an array
  *
  * @param   DbQuery   $query
  *
  * @return  array
  */
 public function fetchColumn(DbQuery $query)
 {
     return $this->dbAdapter->fetchCol($query->getSelectQuery());
 }

 /**
  * Recalculates all token dates, timing and results
  * and outputs text messages.
  *
  * Does not reflect changes to tracks or rounds.
  *
  * @param int $sourceId A source identifier
  * @param int $userId Id of the user who takes the action (for logging)
  * @return \Gems_Task_TaskRunnerBatch A batch to process the synchronization
  */
 public function synchronizeSources($sourceId = null, $userId = null)
 {
     $batch_id = 'source_synch' . ($sourceId ? '_' . $sourceId : '');
     $batch = $this->loader->getTaskRunnerBatch($batch_id);
     if (!$batch->isLoaded()) {
         if ($sourceId) {
             $sources = array($sourceId);
         } else {
             $select = $this->db->select();
             $select->from('gems__sources', array('gso_id_source'))->where('gso_active = 1');
             $sources = $this->db->fetchCol($select);
         }
         foreach ($sources as $source) {
             $batch->addTask('Tracker_SourceSyncSurveys', $source, $userId);
             // Reset cache after basic synch
             $batch->addTask('CleanCache');
             // Reset cache after field synch
             $batch->addTask('AddTask', 'CleanCache');
             $batch->addTask('AddTask', 'Tracker\\UpdateSyncDate', $source, $userId);
         }
     }
     return $batch;
 }

 /**
  * Get a list of suggested tags to a request entered by the user
  * 
  * @param Zend_Db_Adapter_Abstract $db
  * @param string $partialTag //request entered by the user
  * @param int $limit // count records
  * @return array 
  */
 public static function GetTagSuggestions($db, $partialTag, $limit = 0)
 {
     $partialTag = trim($partialTag);
     if (strlen($partialTag) == 0) {
         return array();
     }
     // Переведем строку к нижнему регистру
     $partialTag = mb_convert_case($partialTag, MB_CASE_LOWER, "UTF-8");
     $select = $db->select();
     $select->distinct();
     $select->from(array('t' => 'blog_posts_tags'), 'lower(label)')->joinInner(array('p' => 'blog_posts'), 't.post_id = p.id', array())->where('lower(t.label) like lower(?)', $partialTag . '%')->where('p.status = ?', self::STATUS_LIVE)->order('lower(t.label)');
     if ($limit > 0) {
         $select->limit($limit);
     }
     return $db->fetchCol($select);
 }

 /**
  * Get the tables in the database.
  *
  * @param string $prefix Optionally, show only tables with this prefix.
  * @return array
  */
 public function getTableNames()
 {
     $prefix = $this->getPrefix();
     $sql = "SHOW TABLES " . ($prefix ? "LIKE '{$prefix}%'" : '');
     return $this->_dbAdapter->fetchCol($sql);
 }

 /**
  * Convert the submitted form-data to a filter to be used for retrieving the data to export
  *
  * Now only handles the organization ID and consent codes, but can be extended to
  * include track info or perform some checks
  *
  * @param array $data
  * @return array
  */
 protected function _getFilter($data)
 {
     $filter = array();
     if (isset($data['ids'])) {
         $idStrings = $data['ids'];
         $idArray = preg_split('/[\\s,;]+/', $idStrings, -1, PREG_SPLIT_NO_EMPTY);
         if ($idArray) {
             // Make sure output is OK
             // $idArray = array_map(array($this->db, 'quote'), $idArray);
             $filter['respondentid'] = $idArray;
         }
     }
     if ($this->project->hasResponseDatabase()) {
         $this->_getResponseDatabaseFilter($data, $filter);
     }
     if (isset($data['tid'])) {
         $select = $this->db->select();
         $select->from('gems__respondent2track', array('gr2t_id_respondent_track'))->where('gr2t_id_track = ?', $data['tid']);
         if ($trackArray = $this->db->fetchCol($select)) {
             $filter['resptrackid'] = $trackArray;
         }
     }
     if (isset($data['oid'])) {
         $filter['organizationid'] = $data['oid'];
     } else {
         //Invalid id so when nothing selected... we get nothing
         // $filter['organizationid'] = '-1';
     }
     // Consent codes
     $filter['consentcode'] = array_diff((array) $this->util->getConsentTypes(), (array) $this->util->getConsentRejected());
     if (isset($data['rounds']) && !empty($data['rounds'])) {
         $select = $this->loader->getTracker()->getTokenSelect(array('gto_id_token'));
         // Only get positive receptioncodes
         $select->andReceptionCodes(array())->onlySucces();
         // Apply track filter
         if (isset($data['tid']) && !empty($data['tid'])) {
             $select->forWhere('gto_id_track = ?', (int) $data['tid']);
         }
         // Apply survey filter
         if (isset($data['sid']) && !empty($data['sid'])) {
             $select->forSurveyId((int) $data['sid']);
         }
         // Apply organization filter
         if (isset($data['oid'])) {
             $select->forWhere('gto_id_organization in (?)', $data['oid']);
         }
         // Apply round description filter
         if ($data['rounds'] == self::NoRound) {
             $select->forWhere('gto_round_description IS NULL OR gto_round_description = ""');
         } else {
             $select->forWhere('gto_round_description = ?', $data['rounds']);
         }
         $tokens = array();
         $result = $select->getSelect()->query();
         while ($row = $result->fetch(\Zend_Db::FETCH_NUM)) {
             $tokens[] = $row[0];
         }
         if (empty($tokens)) {
             // Add invalid filter
             $filter['organizationid'] = -1;
         }
         $filter['token'] = $tokens;
     }
     // \Gems_Tracker::$verbose = true;
     return $filter;
 }