/** * @param array $linksData * @return void */ public function saveLinksData($linksData) { $mainTable = $this->productLink->getMainTable(); $relationTable = $this->productLink->getTable('catalog_product_relation'); // save links and relations if ($linksData['product_ids']) { $this->deleteOldLinks(array_keys($linksData['product_ids'])); $mainData = []; foreach ($linksData['relation'] as $productData) { $mainData[] = ['product_id' => $productData['parent_id'], 'linked_product_id' => $productData['child_id'], 'link_type_id' => $this->getLinkTypeId()]; } $this->connection->insertOnDuplicate($mainTable, $mainData); $this->connection->insertOnDuplicate($relationTable, $linksData['relation']); } $attributes = $this->getAttributes(); // save positions and default quantity if ($linksData['attr_product_ids']) { $savedData = $this->connection->fetchPairs($this->connection->select()->from($mainTable, [new \Zend_Db_Expr('CONCAT_WS(" ", product_id, linked_product_id)'), 'link_id'])->where('product_id IN (?) AND link_type_id = ' . $this->connection->quote($this->getLinkTypeId()), array_keys($linksData['attr_product_ids']))); foreach ($savedData as $pseudoKey => $linkId) { if (isset($linksData['position'][$pseudoKey])) { $linksData['position'][$pseudoKey]['link_id'] = $linkId; } if (isset($linksData['qty'][$pseudoKey])) { $linksData['qty'][$pseudoKey]['link_id'] = $linkId; } } if (!empty($linksData['position'])) { $this->connection->insertOnDuplicate($attributes['position']['table'], $linksData['position']); } if (!empty($linksData['qty'])) { $this->connection->insertOnDuplicate($attributes['qty']['table'], $linksData['qty']); } } }
/** * Method for FULLTEXT search in Mysql, will generated MATCH ($columns) AGAINST ('$expression' $mode) * * @param string|string[] $columns Columns which add to MATCH () * @param string $expression Expression which add to AGAINST () * @param string $mode * @return string */ public function getMatchQuery($columns, $expression, $mode = self::FULLTEXT_MODE_NATURAL) { if (is_array($columns)) { $columns = implode(', ', $columns); } $expression = $this->connection->quote($expression); $condition = self::MATCH . " ({$columns}) " . self::AGAINST . " ({$expression} {$mode})"; return $condition; }
private function generateCondition($field, $from, $to, AdapterInterface $adapter) { $hasFromValue = !is_null($from); $hasToValue = !is_null($to); $condition = ''; if ($hasFromValue and $hasToValue) { $condition = sprintf('%s >= %s %s %s < %s', $field, $adapter->quote($from), \Zend_Db_Select::SQL_AND, $field, $adapter->quote($to)); } elseif ($hasFromValue and !$hasToValue) { $condition = sprintf('%s >= %s', $field, $adapter->quote($from)); } elseif (!$hasFromValue and $hasToValue) { $condition = sprintf('%s < %s', $field, $adapter->quote($to)); } return $condition; }
/** * Populate the temporary category tree index table * * @param string $temporaryName */ protected function fillTempCategoryTreeIndex($temporaryName) { // This finds all children (cc2) that descend from a parent (cc) by path. // For example, cc.path may be '1/2', and cc2.path may be '1/2/3/4/5'. $temporarySelect = $this->connection->select()->from(['cc' => $this->getTable('catalog_category_entity')], ['parent_id' => 'entity_id'])->joinInner(['cc2' => $this->getTable('catalog_category_entity')], 'cc2.path LIKE ' . $this->connection->getConcatSql([$this->connection->quoteIdentifier('cc.path'), $this->connection->quote('/%')]), ['child_id' => 'entity_id']); $this->connection->query($temporarySelect->insertFromSelect($temporaryName, ['parent_id', 'child_id'])); }
/** * 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; }
/** * Create anchor select * * @param \Magento\Store\Model\Store $store * @return \Magento\Framework\DB\Select */ protected function createAnchorSelect(\Magento\Store\Model\Store $store) { $isAnchorAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Category::ENTITY, 'is_anchor')->getId(); $statusAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'status')->getId(); $visibilityAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'visibility')->getId(); $rootCatIds = explode('/', $this->getPathFromCategoryId($store->getRootCategoryId())); array_pop($rootCatIds); return $this->connection->select()->from(['cc' => $this->getTable('catalog_category_entity')], [])->joinInner(['cc2' => $this->getTable('catalog_category_entity')], 'cc2.path LIKE ' . $this->connection->getConcatSql([$this->connection->quoteIdentifier('cc.path'), $this->connection->quote('/%')]) . ' AND cc.entity_id NOT IN (' . implode(',', $rootCatIds) . ')', [])->joinInner(['ccp' => $this->getTable('catalog_category_product')], 'ccp.category_id = cc2.entity_id', [])->joinInner(['cpw' => $this->getTable('catalog_product_website')], 'cpw.product_id = ccp.product_id', [])->joinInner(['cpsd' => $this->getTable('catalog_product_entity_int')], 'cpsd.entity_id = ccp.product_id AND cpsd.store_id = 0' . ' AND cpsd.attribute_id = ' . $statusAttributeId, [])->joinLeft(['cpss' => $this->getTable('catalog_product_entity_int')], 'cpss.entity_id = ccp.product_id AND cpss.attribute_id = cpsd.attribute_id' . ' AND cpss.store_id = ' . $store->getId(), [])->joinInner(['cpvd' => $this->getTable('catalog_product_entity_int')], 'cpvd.entity_id = ccp.product_id AND cpvd.store_id = 0' . ' AND cpvd.attribute_id = ' . $visibilityAttributeId, [])->joinLeft(['cpvs' => $this->getTable('catalog_product_entity_int')], 'cpvs.entity_id = ccp.product_id AND cpvs.attribute_id = cpvd.attribute_id ' . 'AND cpvs.store_id = ' . $store->getId(), [])->joinInner(['ccad' => $this->getTable('catalog_category_entity_int')], 'ccad.entity_id = cc.entity_id AND ccad.store_id = 0' . ' AND ccad.attribute_id = ' . $isAnchorAttributeId, [])->joinLeft(['ccas' => $this->getTable('catalog_category_entity_int')], 'ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id' . ' AND ccas.store_id = ' . $store->getId(), [])->where('cpw.website_id = ?', $store->getWebsiteId())->where($this->connection->getIfNullSql('cpss.value', 'cpsd.value') . ' = ?', \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED)->where($this->connection->getIfNullSql('cpvs.value', 'cpvd.value') . ' IN (?)', [\Magento\Catalog\Model\Product\Visibility::VISIBILITY_IN_CATALOG, \Magento\Catalog\Model\Product\Visibility::VISIBILITY_IN_SEARCH, \Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH])->where($this->connection->getIfNullSql('ccas.value', 'ccad.value') . ' = ?', 1)->columns(['category_id' => 'cc.entity_id', 'product_id' => 'ccp.product_id', 'position' => new \Zend_Db_Expr('ccp.position + 10000'), 'is_parent' => new \Zend_Db_Expr('0'), 'store_id' => new \Zend_Db_Expr($store->getId()), 'visibility' => new \Zend_Db_Expr($this->connection->getIfNullSql('cpvs.value', 'cpvd.value'))]); }
/** * @param Dimension $dimension * @param AdapterInterface $adapter * @return string */ private function generateExpression(Dimension $dimension, AdapterInterface $adapter) { $identifier = $dimension->getName(); $value = $dimension->getValue(); if (self::DEFAULT_DIMENSION_NAME === $identifier) { $identifier = self::STORE_FIELD_NAME; $value = $this->scopeResolver->getScope($value)->getId(); } return sprintf('%s = %s', $adapter->quoteIdentifier($identifier), $adapter->quote($value)); }
/** * @param FilterInterface $filter * @param bool $isNegation * @return string */ private function processTermSelect(FilterInterface $filter, $isNegation) { $alias = $this->tableMapper->getMappingAlias($filter); if (is_array($filter->getValue())) { $value = sprintf('%s IN (%s)', $isNegation ? 'NOT' : '', implode(',', array_map([$this->connection, 'quote'], $filter->getValue()))); } else { $value = ($isNegation ? '!' : '') . '= ' . $this->connection->quote($filter->getValue()); } $resultQuery = sprintf('%1$s.value %2$s', $alias, $value); return $resultQuery; }
/** * Get all database triggers * * @return void */ protected function loadTriggers() { $schema = $this->getSchemaName(); if ($schema) { $sqlFilter = $this->resourceAdapter->quoteIdentifier('TRIGGER_SCHEMA') . ' = ' . $this->resourceAdapter->quote($schema); } else { $sqlFilter = $this->resourceAdapter->quoteIdentifier('TRIGGER_SCHEMA') . ' != ' . $this->resourceAdapter->quote('INFORMATION_SCHEMA'); } $select = $this->getSelect()->from(new \Zend_Db_Expr($this->resourceAdapter->quoteIdentifier(['INFORMATION_SCHEMA', 'TRIGGERS'])))->where($sqlFilter); $results = $this->resourceAdapter->query($select); $data = []; foreach ($results as $row) { $row = array_change_key_case($row, CASE_LOWER); $row['action_statement'] = $this->convertStatement($row['action_statement']); $key = $row['event_object_table'] . $row['event_manipulation'] . $row['action_timing']; $data[$key] = $row; } $this->triggers = $data; }
/** * SELECT * pps.sale_id, * pps.date_paid, * sfo.base_grand_total, * ce.entity_id * FROM prxgt_pv_sale pps * LEFT JOIN sales_flat_order sfo * ON pps.sale_id = sfo.entity_id * LEFT JOIN customer_entity ce * ON sfo.customer_id = ce.entity_id * WHERE pps.date_paid >= '2016-01-01 00:00:00' * AND pps.date_paid <= '2016-01-31 23:59:59' * * @param $dsBegin - '20160101' * @param $dsEnd - '20160131' * * @return array [ $custId => [$orderId=>[$amount], ... ], ... ] */ public function getSaleOrdersForRebate($dsBegin, $dsEnd) { $result = []; /* aliases and tables */ $asPvSale = 'pps'; $asMageSale = 'sfo'; $asMageCust = 'ce'; $tblPvSale = $this->_resource->getTableName(PvSale::ENTITY_NAME); $tblMageSale = $this->_resource->getTableName(Cfg::ENTITY_MAGE_SALES_ORDER); $tblMageCust = $this->_resource->getTableName(Cfg::ENTITY_MAGE_CUSTOMER); // FROM prxgt_pv_sale pps $query = $this->_conn->select(); $cols = [PvSale::ATTR_SALE_ID, PvSale::ATTR_DATE_PAID]; $query->from([$asPvSale => $tblPvSale], $cols); // LEFT JOIN sales_flat_order sfo ON pps.sale_id = sfo.entity_id $on = "{$asPvSale}." . PvSale::ATTR_SALE_ID . "={$asMageSale}." . Cfg::E_COMMON_A_ENTITY_ID; $cols = [Cfg::E_SALE_ORDER_A_BASE_GRAND_TOTAL]; $query->joinLeft([$asMageSale => $tblMageSale], $on, $cols); // LEFT JOIN customer_entity ce ON sfo.customer_id = ce.entity_id $on = "{$asMageSale}." . Cfg::E_SALE_ORDER_A_CUSTOMER_ID . "={$asMageCust}." . Cfg::E_CUSTOMER_A_ENTITY_ID; $cols = [Cfg::E_CUSTOMER_A_ENTITY_ID]; $query->joinLeft([$asMageCust => $tblMageCust], $on, $cols); // where $from = $this->_toolPeriod->getTimestampFrom($dsBegin); $to = $this->_toolPeriod->getTimestampTo($dsEnd); $whereFrom = PvSale::ATTR_DATE_PAID . '>=' . $this->_conn->quote($from); $whereTo = PvSale::ATTR_DATE_PAID . '<=' . $this->_conn->quote($to); $wherePv = PvSale::ATTR_TOTAL . ">0"; $query->where("{$whereFrom} AND {$whereTo} AND {$wherePv}"); // $sql = (string)$query; $data = $this->_conn->fetchAll($query); foreach ($data as $item) { $custId = $item[Cfg::E_CUSTOMER_A_ENTITY_ID]; $saleId = $item[PvSale::ATTR_SALE_ID]; $amount = $item[Cfg::E_SALE_ORDER_A_BASE_GRAND_TOTAL]; $result[$custId][$saleId] = $amount; } return $result; }
/** * @param string $field * @param string $operator * @param mixed $value * @return string */ public function generateCondition($field, $operator, $value) { return sprintf(is_array($value) ? self::CONDITION_PATTERN_ARRAY : self::CONDITION_PATTERN_SIMPLE, $this->adapter->quoteIdentifier($field), $operator, $this->adapter->quote($value)); }