/**
* @magentoDataFixture Magento/Bundle/_files/product.php
* @covers \Magento\Indexer\Model\Indexer::reindexAll
* @covers \Magento\Bundle\Model\Product\Type::getSearchableData
*/
public function testPrepareProductIndexForBundleProduct()
{
$this->indexer->reindexAll();
$select = $this->connectionMock->select()->from($this->resource->getTableName('catalogsearch_fulltext_scope1'))->where('`data_index` LIKE ?', '%' . 'Bundle Product Items' . '%');
$result = $this->connectionMock->fetchAll($select);
$this->assertCount(1, $result);
}
/**
* Return best match (from database)
*
* @param string $query
* @return array
*/
public function getBestMatch($query)
{
$query = trim($query);
if (!$query) {
return ['keyword' => $query, 'diff' => 100];
}
$len = intval($this->text->strlen($query));
$trigram = $this->text->getTrigram($this->text->strtolower($query));
$tableName = $this->resource->getTableName('mst_misspell_index');
$select = $this->connection->select();
$relevance = '(-ABS(LENGTH(keyword) - ' . $len . ') + MATCH (trigram) AGAINST("' . $trigram . '"))';
$relevancy = new \Zend_Db_Expr($relevance . ' + frequency AS relevancy');
$select->from($tableName, ['keyword', $relevancy, 'frequency'])->order('relevancy desc')->limit(10);
$keywords = $this->connection->fetchAll($select);
$maxFreq = 0.0001;
foreach ($keywords as $keyword) {
$maxFreq = max($keyword['frequency'], $maxFreq);
}
$preResults = [];
foreach ($keywords as $keyword) {
$preResults[$keyword['keyword']] = $this->damerau->similarity($query, $keyword['keyword']) + $keyword['frequency'] * (10 / $maxFreq);
}
arsort($preResults);
$keys = array_keys($preResults);
if (count($keys) > 0) {
$keyword = $keys[0];
$keyword = $this->toSameRegister($keyword, $query);
$diff = $preResults[$keys[0]];
$result = ['keyword' => $keyword, 'diff' => $diff];
} else {
$result = ['keyword' => $query, 'diff' => 100];
}
return $result;
}
/**
* @param array $columns
* @param array $data
* @param array $expected
* @dataProvider insertArrayDataProvider
*/
public function testInsertArray(array $columns, array $data, array $expected)
{
$this->_connection->insertArray($this->_tableName, $columns, $data);
$select = $this->_connection->select()->from($this->_tableName, array_keys($expected[0]))->order('column1');
$result = $this->_connection->fetchAll($select);
$this->assertEquals($expected, $result);
}
/**
* Load ensured nodes
*
* @param object $category
* @param Node $rootNode
* @return void
*/
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 = [];
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);
}
}
/**
* @param string|int $nodeId
* @param int $startLevel
* @param int $endLevel
* @return NodeSet
* @SuppressWarnings(PHPMD.ExitExpression)
*/
public function getChildren($nodeId, $startLevel = 0, $endLevel = 0)
{
try {
$info = $this->getNodeInfo($nodeId);
} catch (\Exception $e) {
echo $e->getMessage();
exit;
}
$dbSelect = new Select($this->_db);
$dbSelect->from($this->_table)->where($this->_left . ' >= :left')->where($this->_right . ' <= :right')->order($this->_left);
$this->_addExtTablesToSelect($dbSelect);
$data = [];
$data['left'] = $info[$this->_left];
$data['right'] = $info[$this->_right];
if (!empty($startLevel) && empty($endLevel)) {
$dbSelect->where($this->_level . ' = :minLevel');
$data['minLevel'] = $info[$this->_level] + $startLevel;
}
//echo $dbSelect->__toString();
$data = $this->_db->fetchAll($dbSelect, $data);
$nodeSet = new NodeSet();
foreach ($data as $node) {
$nodeSet->addNode(new Node($node, $this->getKeys()));
}
return $nodeSet;
}
/**
* Prepare website current dates table
*
* @return \Magento\Catalog\Model\Indexer\Product\Price\AbstractAction
*/
protected function _prepareWebsiteDateTable()
{
$baseCurrency = $this->_config->getValue(\Magento\Directory\Model\Currency::XML_PATH_CURRENCY_BASE);
$select = $this->_connection->select()->from(['cw' => $this->_defaultIndexerResource->getTable('store_website')], ['website_id'])->join(['csg' => $this->_defaultIndexerResource->getTable('store_group')], 'cw.default_group_id = csg.group_id', ['store_id' => 'default_store_id'])->where('cw.website_id != 0');
$data = [];
foreach ($this->_connection->fetchAll($select) as $item) {
/** @var $website \Magento\Store\Model\Website */
$website = $this->_storeManager->getWebsite($item['website_id']);
if ($website->getBaseCurrencyCode() != $baseCurrency) {
$rate = $this->_currencyFactory->create()->load($baseCurrency)->getRate($website->getBaseCurrencyCode());
if (!$rate) {
$rate = 1;
}
} else {
$rate = 1;
}
/** @var $store \Magento\Store\Model\Store */
$store = $this->_storeManager->getStore($item['store_id']);
if ($store) {
$timestamp = $this->_localeDate->scopeTimeStamp($store);
$data[] = ['website_id' => $website->getId(), 'website_date' => $this->_dateTime->formatDate($timestamp, false), 'rate' => $rate];
}
}
$table = $this->_defaultIndexerResource->getTable('catalog_product_index_website');
$this->_emptyTable($table);
if ($data) {
foreach ($data as $row) {
$this->_connection->insertOnDuplicate($table, $row, array_keys($row));
}
}
return $this;
}
/**
* @return array
*/
public function getAttributes()
{
if (empty($this->attributes)) {
$select = $this->connection->select()->from($this->productLink->getTable('catalog_product_link_attribute'), ['id' => 'product_link_attribute_id', 'code' => 'product_link_attribute_code', 'type' => 'data_type'])->where('link_type_id = ?', $this->getLinkTypeId());
foreach ($this->connection->fetchAll($select) as $row) {
$this->attributes[$row['code']] = ['id' => $row['id'], 'table' => $this->productLink->getAttributeTypeTable($row['type'])];
}
}
return $this->attributes;
}
/**
* Retrieve searchable products per store
*
* @param int $storeId
* @param array $staticFields
* @param array|int $productIds
* @param int $lastProductId
* @param int $limit
* @return array
*/
public function getSearchableProducts($storeId, array $staticFields, $productIds = null, $lastProductId = 0, $limit = 100)
{
$websiteId = $this->storeManager->getStore($storeId)->getWebsiteId();
$select = $this->connection->select()->useStraightJoin(true)->from(['e' => $this->getTable('catalog_product_entity')], array_merge(['entity_id', 'type_id'], $staticFields))->join(['website' => $this->getTable('catalog_product_website')], $this->connection->quoteInto('website.product_id = e.entity_id AND website.website_id = ?', $websiteId), []);
if ($productIds !== null) {
$select->where('e.entity_id IN (?)', $productIds);
}
$select->where('e.entity_id > ?', $lastProductId)->limit($limit)->order('e.entity_id');
$result = $this->connection->fetchAll($select);
return $result;
}
/**
* @return $this
*/
protected function _loadFullTree()
{
$select = clone $this->_select;
$select->order($this->_table . '.' . $this->_levelField)->order($this->_table . '.' . $this->_orderField);
$arrNodes = $this->_conn->fetchAll($select);
foreach ($arrNodes as $nodeInfo) {
$node = new Node($nodeInfo, $this->_idField, $this);
$parentNode = $this->getNodeById($nodeInfo[$this->_parentField]);
$this->addNode($node, $parentNode);
}
return $this;
}
/**
* @param array $index
* @return $this
*/
protected function addCategoryData(&$index)
{
if (!$this->getIndex()->hasProperty('include_category')) {
return $this;
}
$entityTypeId = $this->objectManager->create('Magento\\Eav\\Model\\Entity')->setType(Category::ENTITY)->getTypeId();
/** @var \Magento\Catalog\Model\ResourceModel\Eav\Attribute $attribute */
$attribute = $this->objectManager->create('Magento\\Catalog\\Model\\ResourceModel\\Eav\\Attribute')->loadByCode($entityTypeId, 'name');
$productIds = array_keys($index);
$valueSelect = $this->connection->select()->from(['cc' => $this->resource->getTableName('catalog_category_entity')], [new \Zend_Db_Expr("GROUP_CONCAT(vc.value SEPARATOR ' ')")])->joinLeft(['vc' => $attribute->getBackend()->getTable()], 'cc.entity_id = vc.entity_id', [])->where("LOCATE(CONCAT('/', CONCAT(cc.entity_id, '/')), CONCAT(ce.path, '/'))")->where('vc.attribute_id = ?', $attribute->getId());
$columns = ['product_id' => 'product_id', 'category' => new \Zend_Db_Expr('(' . $valueSelect . ')')];
$select = $this->connection->select()->from([$this->resource->getTableName('catalog_category_product')], $columns)->joinLeft(['ce' => $this->resource->getTableName('catalog_category_entity')], 'category_id = ce.entity_id', [])->where('product_id IN (?)', $productIds);
foreach ($this->connection->fetchAll($select) as $row) {
if (!isset($index[$row['product_id']]['options'])) {
$index[$row['product_id']]['options'] = '';
}
$index[$row['product_id']]['options'] .= ' ' . $row['category'];
}
return $this;
}
/**
* 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;
}
/**
* Populate existing selections.
*
* @param array $existingOptions
*
* @return \Magento\CatalogImportExport\Model\Import\Product\Type\AbstractType
*/
protected function populateExistingSelections($existingOptions)
{
//@codingStandardsIgnoreStart
$existingSelections = $this->connection->fetchAll($this->connection->select()->from($this->_resource->getTableName('catalog_product_bundle_selection'))->where('option_id IN (?)', array_keys($existingOptions)));
foreach ($existingSelections as $existingSelection) {
$optionTitle = $existingOptions[$existingSelection['option_id']]['title'];
$cachedOptionsSelections = $this->_cachedOptions[$existingSelection['parent_product_id']][$optionTitle]['selections'];
foreach ($cachedOptionsSelections as $selectIndex => $selection) {
$productId = $this->_cachedSkuToProducts[$selection['sku']];
if ($productId == $existingSelection['product_id']) {
foreach (array_keys($existingSelection) as $origKey) {
$key = isset($this->_bundleFieldMapping[$origKey]) ? $this->_bundleFieldMapping[$origKey] : $origKey;
if (!isset($this->_cachedOptions[$existingSelection['parent_product_id']][$optionTitle]['selections'][$selectIndex][$key])) {
$this->_cachedOptions[$existingSelection['parent_product_id']][$optionTitle]['selections'][$selectIndex][$key] = $existingSelection[$origKey];
}
}
break;
}
}
}
// @codingStandardsIgnoreEnd
return $this;
}
/**
* Array of SKU to array of super attribute values for all products.
*
* @param array $bunch
* @return $this
*/
protected function _loadSkuSuperDataForBunch(array $bunch)
{
$newSku = $this->_entityModel->getNewSku();
$oldSku = $this->_entityModel->getOldSku();
$productIds = [];
foreach ($bunch as $rowData) {
$sku = $rowData[ImportProduct::COL_SKU];
$productData = isset($newSku[$sku]) ? $newSku[$sku] : $oldSku[$sku];
$productIds[] = $productData['entity_id'];
}
$this->_productSuperAttrs = [];
$this->_skuSuperData = [];
if (!empty($productIds)) {
$mainTable = $this->_resource->getTableName('catalog_product_super_attribute');
$optionTable = $this->_resource->getTableName('eav_attribute_option');
$select = $this->_connection->select()->from(['m' => $mainTable], ['product_id', 'attribute_id', 'product_super_attribute_id'])->joinLeft(['o' => $optionTable], $this->_connection->quoteIdentifier('o.attribute_id') . ' = ' . $this->_connection->quoteIdentifier('o.attribute_id'), ['option_id'])->where('product_id IN ( ? )', $productIds);
foreach ($this->_connection->fetchAll($select) as $row) {
$attrId = $row['attribute_id'];
$productId = $row['product_id'];
if ($row['option_id']) {
$this->_skuSuperData[$productId][$attrId][$row['option_id']] = true;
}
$this->_productSuperAttrs["{$productId}_{$attrId}"] = $row['product_super_attribute_id'];
}
}
return $this;
}
/**
* Return attribute values for given entities and store of specific attribute type
*
* @param string $type
* @param array $entityIds
* @param integer $storeId
* @return array
*/
protected function getAttributeTypeValues($type, $entityIds, $storeId)
{
$linkField = $this->getCategoryMetadata()->getLinkField();
$select = $this->connection->select()->from(['def' => $this->connection->getTableName($this->getTableName('catalog_category_entity_' . $type))], [$linkField, 'attribute_id'])->joinLeft(['e' => $this->connection->getTableName($this->getTableName('catalog_category_entity'))], "def.{$linkField} = e.{$linkField}")->joinLeft(['store' => $this->connection->getTableName($this->getTableName('catalog_category_entity_' . $type))], "store.{$linkField} = def.{$linkField} AND store.attribute_id = def.attribute_id " . 'AND store.store_id = ' . $storeId, ['value' => $this->connection->getCheckSql('store.value_id > 0', $this->connection->quoteIdentifier('store.value'), $this->connection->quoteIdentifier('def.value'))])->where("e.entity_id IN (?)", $entityIds)->where('def.store_id IN (?)', [\Magento\Store\Model\Store::DEFAULT_STORE_ID, $storeId]);
return $this->connection->fetchAll($select);
}
/**
* {@inheritdoc}
*/
protected function doFindAllByFilter($filter)
{
return $this->connection->fetchAll($this->prepareSelect($filter));
}
/**
* Load data from DB Select
*
* @param \Magento\Framework\DB\Select $select
* @return array
*/
public function loadDataFromSelect($select)
{
return $this->resourceAdapter->fetchAll($select);
}
/**
* {@inheritdoc}
*/
protected function doFindAllByData($data)
{
return $this->connection->fetchAll($this->prepareSelect($data));
}
