PHP AdapterInterface::fetchOne方法代码示例

 /**
  * Set max_heap_table_size value in Bytes. By default value is 64M
  *
  * @param int|null $maxHeapTableSize
  * @throws \InvalidArgumentException
  * @throws \RuntimeException
  * @return void
  */
 public function set($maxHeapTableSize = null)
 {
     $maxHeapTableSize = (int) (null === $maxHeapTableSize ? $this->defaultMaxHeapTableSie : $maxHeapTableSize);
     if (!$maxHeapTableSize) {
         throw new \InvalidArgumentException('Wrong max_heap_table_size parameter');
     }
     $this->currentMaxHeapTableSize = (int) $this->connection->fetchOne('SELECT @@session.max_heap_table_size');
     if (!$this->currentMaxHeapTableSize) {
         throw new \RuntimeException('Can not extract max_heap_table_size');
     }
     $this->connection->query('SET SESSION max_heap_table_size = ' . $maxHeapTableSize);
 }

 /**
  * Move tree node
  *
  * @param Node $node
  * @param Node $newParent
  * @param Node $prevNode
  * @return void
  * @throws \Exception
  * @todo Use adapter for generate conditions
  */
 public function move($node, $newParent, $prevNode = null)
 {
     $position = 1;
     $oldPath = $node->getData($this->_pathField);
     $newPath = $newParent->getData($this->_pathField);
     $newPath = $newPath . '/' . $node->getId();
     $oldPathLength = strlen($oldPath);
     $newLevel = $newParent->getLevel() + 1;
     $levelDisposition = $newLevel - $node->getLevel();
     $data = [$this->_levelField => new \Zend_Db_Expr("{$this->_levelField} + '{$levelDisposition}'"), $this->_pathField => new \Zend_Db_Expr("CONCAT('{$newPath}', RIGHT({$this->_pathField}, LENGTH({$this->_pathField}) - {$oldPathLength}))")];
     $condition = $this->_conn->quoteInto("{$this->_pathField} REGEXP ?", "^{$oldPath}(/|\$)");
     $this->_conn->beginTransaction();
     $reorderData = [$this->_orderField => new \Zend_Db_Expr("{$this->_orderField} + 1")];
     try {
         if ($prevNode && $prevNode->getId()) {
             $reorderCondition = "{$this->_orderField} > {$prevNode->getData($this->_orderField)}";
             $position = $prevNode->getData($this->_orderField) + 1;
         } else {
             $reorderCondition = $this->_conn->quoteInto("{$this->_pathField} REGEXP ?", "^{$newParent->getData($this->_pathField)}/[0-9]+\$");
             $select = $this->_conn->select()->from($this->_table, new \Zend_Db_Expr("MIN({$this->_orderField})"))->where($reorderCondition);
             $position = (int) $this->_conn->fetchOne($select);
         }
         $this->_conn->update($this->_table, $reorderData, $reorderCondition);
         $this->_conn->update($this->_table, $data, $condition);
         $this->_conn->update($this->_table, [$this->_orderField => $position, $this->_levelField => $newLevel], $this->_conn->quoteInto("{$this->_idField} = ?", $node->getId()));
         $this->_conn->commit();
     } catch (\Exception $e) {
         $this->_conn->rollBack();
         throw new \Exception("Can't move tree node due to error: " . $e->getMessage());
     }
 }

 /**
  * Return category path by id
  *
  * @param int $categoryId
  * @return string
  */
 protected function getPathFromCategoryId($categoryId)
 {
     if (!isset($this->categoryPath[$categoryId])) {
         $this->categoryPath[$categoryId] = $this->connection->fetchOne($this->connection->select()->from($this->getTable('catalog_category_entity'), ['path'])->where('entity_id = ?', $categoryId));
     }
     return $this->categoryPath[$categoryId];
 }

 /**
  * @param string|int $eId
  * @param string|int $pId
  * @param string|int $aId
  * @return void
  * @SuppressWarnings(PHPMD.CyclomaticComplexity)
  * @SuppressWarnings(PHPMD.NPathComplexity)
  * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
  * @SuppressWarnings(PHPMD.UnusedLocalVariable)
  * @SuppressWarnings(PHPMD.ExitExpression)
  */
 public function moveNodes($eId, $pId, $aId = 0)
 {
     $eInfo = $this->getNodeInfo($eId);
     if ($pId != 0) {
         $pInfo = $this->getNodeInfo($pId);
     }
     if ($aId != 0) {
         $aInfo = $this->getNodeInfo($aId);
     }
     $level = $eInfo[$this->_level];
     $leftKey = $eInfo[$this->_left];
     $rightKey = $eInfo[$this->_right];
     if ($pId == 0) {
         $levelUp = 0;
     } else {
         $levelUp = $pInfo[$this->_level];
     }
     $rightKeyNear = 0;
     $leftKeyNear = 0;
     if ($pId == 0) {
         //move to root
         $rightKeyNear = $this->_db->fetchOne('SELECT MAX(' . $this->_right . ') FROM ' . $this->_table);
     } elseif ($aId != 0 && $pId == $eInfo[$this->_pid]) {
         // if we have after ID
         $rightKeyNear = $aInfo[$this->_right];
         $leftKeyNear = $aInfo[$this->_left];
     } elseif ($aId == 0 && $pId == $eInfo[$this->_pid]) {
         // if we do not have after ID
         $rightKeyNear = $pInfo[$this->_left];
     } elseif ($pId != $eInfo[$this->_pid]) {
         $rightKeyNear = $pInfo[$this->_right] - 1;
     }
     $skewLevel = $pInfo[$this->_level] - $eInfo[$this->_level] + 1;
     $skewTree = $eInfo[$this->_right] - $eInfo[$this->_left] + 1;
     echo "alert('" . $rightKeyNear . "');";
     if ($rightKeyNear > $rightKey) {
         // up
         echo "alert('move up');";
         $skewEdit = $rightKeyNear - $leftKey + 1;
         $sql = 'UPDATE ' . $this->_table . ' SET ' . $this->_right . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_right . ' + ' . $skewEdit . ', IF(' . $this->_right . ' < ' . $eInfo[$this->_left] . ', ' . $this->_right . ' + ' . $skewTree . ', ' . $this->_right . ')), ' . $this->_level . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_level . ' + ' . $skewLevel . ', ' . $this->_level . '), ' . $this->_left . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_left . ' + ' . $skewEdit . ', IF(' . $this->_left . ' > ' . $rightKeyNear . ', ' . $this->_left . ' + ' . $skewTree . ', ' . $this->_left . '))' . ' WHERE ' . $this->_right . ' > ' . $rightKeyNear . ' AND ' . $this->_left . ' < ' . $eInfo[$this->_right];
     } elseif ($rightKeyNear < $rightKey) {
         // down
         echo "alert('move down');";
         $skewEdit = $rightKeyNear - $leftKey + 1 - $skewTree;
         $sql = 'UPDATE ' . $this->_table . ' SET ' . $this->_left . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_left . ' + ' . $skewEdit . ', IF(' . $this->_left . ' > ' . $rightKey . ', ' . $this->_left . ' - ' . $skewTree . ', ' . $this->_left . ')), ' . $this->_level . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_level . ' + ' . $skewLevel . ', ' . $this->_level . '), ' . $this->_right . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_right . ' + ' . $skewEdit . ', IF(' . $this->_right . ' <= ' . $rightKeyNear . ', ' . $this->_right . ' - ' . $skewTree . ', ' . $this->_right . '))' . ' WHERE ' . $this->_right . ' > ' . $leftKey . ' AND ' . $this->_left . ' <= ' . $rightKeyNear;
     }
     $this->_db->beginTransaction();
     try {
         $this->_db->query($sql);
         $this->_db->commit();
     } catch (\Exception $e) {
         $this->_db->rollBack();
         echo $e->getMessage();
         echo "<br>\r\n";
         echo $sql;
         echo "<br>\r\n";
         exit;
     }
     echo "alert('node added')";
 }

 public function getLastCalculationIdForPeriod($calcTypeCode, $dsBegin, $dsEnd)
 {
     /* get calculation type ID for type code */
     $calcTypeId = $this->_repoTypeCalc->getIdByCode($calcTypeCode);
     /* aliases and tables */
     $asPeriod = 'pbbp';
     $asCalc = 'pbbc';
     $tblPeriod = $this->_resource->getTableName(Period::ENTITY_NAME);
     $tblCalc = $this->_resource->getTableName(Calculation::ENTITY_NAME);
     // FROM prxgt_bon_base_period
     $query = $this->_conn->select();
     $cols = [];
     $query->from([$asPeriod => $tblPeriod], $cols);
     // LEFT JOIN prxgt_bon_base_calc pbbc ON pbbp.id = pbbc.period_id
     $on = "{$asPeriod}." . Period::ATTR_ID . "={$asCalc}." . Calculation::ATTR_PERIOD_ID;
     $cols = [Calculation::ATTR_ID];
     $query->joinLeft([$asCalc => $tblCalc], $on, $cols);
     // where
     $whereCalcType = "({$asPeriod}." . Period::ATTR_CALC_TYPE_ID . "=:calcTypeId)";
     $wherePeriodBegin = "({$asPeriod}." . Period::ATTR_DSTAMP_BEGIN . "=:dsBegin)";
     $wherePeriodEnd = "({$asPeriod}." . Period::ATTR_DSTAMP_END . "=:dsEnd)";
     $query->where("{$whereCalcType} AND {$wherePeriodBegin} AND {$wherePeriodEnd}");
     // order by calculation id desc
     $query->order(Calculation::ATTR_ID . ' DESC');
     // limit
     $query->limit(1);
     // $sql = (string)$query;
     $result = $this->_conn->fetchOne($query, ['calcTypeId' => $calcTypeId, 'dsBegin' => $dsBegin, 'dsEnd' => $dsEnd]);
     return $result;
 }

 /**
  * Return timestamp for the first transaction related to PV.
  */
 public function getFirstDateForPvTransactions()
 {
     $asAcc = 'paa';
     $asTrans = 'pat';
     $asType = 'pata';
     $tblAcc = $this->_resource->getTableName(Account::ENTITY_NAME);
     $tblTrans = $this->_resource->getTableName(Transaction::ENTITY_NAME);
     $tblType = $this->_resource->getTableName(TypeAsset::ENTITY_NAME);
     // SELECT FROM prxgt_acc_transaction pat
     $query = $this->_conn->select();
     $query->from([$asTrans => $tblTrans], [Transaction::ATTR_DATE_APPLIED]);
     // LEFT JOIN prxgt_acc_account paa ON paa.id = pat.debit_acc_id
     $on = $asAcc . '.' . Account::ATTR_ID . '=' . $asTrans . '.' . Transaction::ATTR_DEBIT_ACC_ID;
     $query->join([$asAcc => $tblAcc], $on, null);
     // LEFT JOIN prxgt_acc_type_asset pata ON paa.asset_type_id = pata.id
     $on = $asAcc . '.' . Account::ATTR_ASSET_TYPE_ID . '=' . $asType . '.' . TypeAsset::ATTR_ID;
     $query->join([$asType => $tblType], $on, null);
     // WHERE
     $where = $asType . '.' . TypeAsset::ATTR_CODE . '=' . $this->_conn->quote(Cfg::CODE_TYPE_ASSET_PV);
     $query->where($where);
     // ORDER & LIMIT
     $query->order($asTrans . '.' . Transaction::ATTR_DATE_APPLIED . ' ASC');
     $query->limit(1);
     // $sql = (string)$query;
     $result = $this->_conn->fetchOne($query);
     return $result;
 }

 /**
  * @param Node $data
  * @param Node $parentNode
  * @param Node $prevNode
  * @return Node
  */
 public function appendChild($data, $parentNode, $prevNode = null)
 {
     $orderSelect = $this->_conn->select();
     $orderSelect->from($this->_table, new \Zend_Db_Expr('MAX(' . $this->_conn->quoteIdentifier($this->_orderField) . ')'))->where($this->_conn->quoteIdentifier($this->_parentField) . '=' . $parentNode->getId());
     $order = $this->_conn->fetchOne($orderSelect);
     $data[$this->_parentField] = $parentNode->getId();
     $data[$this->_levelField] = $parentNode->getData($this->_levelField) + 1;
     $data[$this->_orderField] = $order + 1;
     $this->_conn->insert($this->_table, $data);
     $data[$this->_idField] = $this->_conn->lastInsertId();
     return parent::appendChild($data, $parentNode, $prevNode);
 }

 /**
  * Update session
  *
  * @param string $sessionId
  * @param string $sessionData
  * @return bool
  */
 public function write($sessionId, $sessionData)
 {
     // need to use write connection to get the most fresh DB sessions
     $bindValues = ['session_id' => $sessionId];
     $select = $this->_write->select()->from($this->_sessionTable)->where('session_id = :session_id');
     $exists = $this->_write->fetchOne($select, $bindValues);
     // encode session serialized data to prevent insertion of incorrect symbols
     $sessionData = base64_encode($sessionData);
     $bind = ['session_expires' => time(), 'session_data' => $sessionData];
     if ($exists) {
         $this->_write->update($this->_sessionTable, $bind, ['session_id=?' => $sessionId]);
     } else {
         $bind['session_id'] = $sessionId;
         $this->_write->insert($this->_sessionTable, $bind);
     }
     return true;
 }

 /**
  * Returns current schema name
  *
  * @return string
  */
 protected function getCurrentSchema()
 {
     return $this->resourceAdapter->fetchOne('SELECT SCHEMA()');
 }

 /**
  * Validate and prepare data about super attributes and associated products.
  *
  * @return $this
  * @SuppressWarnings(PHPMD.CyclomaticComplexity)
  */
 protected function _processSuperData()
 {
     $metadata = $this->metadataPool->getMetadata(ProductInterface::class);
     if ($this->_productSuperData) {
         $usedCombs = [];
         // is associated products applicable?
         foreach (array_keys($this->_productSuperData['assoc_ids']) as $assocId) {
             if (!isset($this->_skuSuperAttributeValues[$this->_productSuperData['attr_set_code']][$assocId])) {
                 continue;
             }
             if ($this->_productSuperData['used_attributes']) {
                 $skuSuperValues = $this->_skuSuperAttributeValues[$this->_productSuperData['attr_set_code']][$assocId];
                 $usedCombParts = [];
                 foreach ($this->_productSuperData['used_attributes'] as $usedAttrId => $usedValues) {
                     if (empty($skuSuperValues[$usedAttrId]) || !isset($usedValues[$skuSuperValues[$usedAttrId]])) {
                         // invalid value or value does not exists for associated product
                         continue;
                     }
                     $usedCombParts[] = $skuSuperValues[$usedAttrId];
                     $this->_productSuperData['used_attributes'][$usedAttrId][$skuSuperValues[$usedAttrId]] = true;
                 }
                 $comb = implode('|', $usedCombParts);
                 if (isset($usedCombs[$comb])) {
                     // super attributes values combination was already used
                     continue;
                 }
                 $usedCombs[$comb] = true;
             }
             $this->_superAttributesData['super_link'][] = ['product_id' => $this->_productSuperData['assoc_entity_ids'][$assocId], 'parent_id' => $this->_productSuperData['product_id']];
             $subEntityId = $this->_connection->fetchOne($this->_connection->select()->from(['cpe' => $this->_resource->getTableName('catalog_product_entity')], ['entity_id'])->where($metadata->getLinkField() . ' = ?', $assocId));
             $this->_superAttributesData['relation'][] = ['parent_id' => $this->_productSuperData['product_id'], 'child_id' => $subEntityId];
         }
     }
     return $this;
 }