/**
* Returns SQL expression
* TRIM(CONCAT_WS(separator, IF(str1 <> '', str1, NULL), IF(str2 <> '', str2, NULL) ...))
*
* @return string
*/
public function __toString()
{
$columns = [];
foreach ($this->columns as $key => $part) {
if (isset($part['columnName']) && $part['columnName'] instanceof \Zend_Db_Expr) {
$column = $part['columnName'];
} else {
$column = $this->adapter->quoteIdentifier((isset($part['tableAlias']) ? $part['tableAlias'] . '.' : '') . (isset($part['columnName']) ? $part['columnName'] : $key));
}
$columns[] = $this->adapter->getCheckSql($column . " <> ''", $column, 'NULL');
}
return sprintf('TRIM(%s)', $this->adapter->getConcatSql($columns, ' '));
}
/**
* Get select for all products
*
* @param \Magento\Store\Model\Store $store
* @return \Magento\Framework\DB\Select
*/
protected function getAllProducts(\Magento\Store\Model\Store $store)
{
if (!isset($this->productsSelects[$store->getId()])) {
$statusAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'status')->getId();
$visibilityAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'visibility')->getId();
$select = $this->connection->select()->from(['cp' => $this->getTable('catalog_product_entity')], [])->joinInner(['cpw' => $this->getTable('catalog_product_website')], 'cpw.product_id = cp.entity_id', [])->joinInner(['cpsd' => $this->getTable('catalog_product_entity_int')], 'cpsd.entity_id = cp.entity_id AND cpsd.store_id = 0' . ' AND cpsd.attribute_id = ' . $statusAttributeId, [])->joinLeft(['cpss' => $this->getTable('catalog_product_entity_int')], 'cpss.entity_id = cp.entity_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 = cp.entity_id AND cpvd.store_id = 0' . ' AND cpvd.attribute_id = ' . $visibilityAttributeId, [])->joinLeft(['cpvs' => $this->getTable('catalog_product_entity_int')], 'cpvs.entity_id = cp.entity_id AND cpvs.attribute_id = cpvd.attribute_id ' . ' AND cpvs.store_id = ' . $store->getId(), [])->joinLeft(['ccp' => $this->getTable('catalog_category_product')], 'ccp.product_id = cp.entity_id', [])->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])->group('cp.entity_id')->columns(['category_id' => new \Zend_Db_Expr($store->getRootCategoryId()), 'product_id' => 'cp.entity_id', 'position' => new \Zend_Db_Expr($this->connection->getCheckSql('ccp.product_id IS NOT NULL', 'ccp.position', '0')), 'is_parent' => new \Zend_Db_Expr($this->connection->getCheckSql('ccp.product_id IS NOT NULL', '1', '0')), 'store_id' => new \Zend_Db_Expr($store->getId()), 'visibility' => new \Zend_Db_Expr($this->connection->getIfNullSql('cpvs.value', 'cpvd.value'))]);
$this->productsSelects[$store->getId()] = $select;
}
return $this->productsSelects[$store->getId()];
}
/**
* Prepare tier price index table
*
* @param int|array $entityIds the entity ids limitation
* @return \Magento\Catalog\Model\Indexer\Product\Price\AbstractAction
*/
protected function _prepareTierPriceIndex($entityIds = null)
{
$table = $this->_defaultIndexerResource->getTable('catalog_product_index_tier_price');
$this->_emptyTable($table);
$websiteExpression = $this->_connection->getCheckSql('tp.website_id = 0', 'ROUND(tp.value * cwd.rate, 4)', 'tp.value');
$select = $this->_connection->select()->from(['tp' => $this->_defaultIndexerResource->getTable(['catalog_product_entity', 'tier_price'])], ['entity_id'])->join(['cg' => $this->_defaultIndexerResource->getTable('customer_group')], 'tp.all_groups = 1 OR (tp.all_groups = 0 AND tp.customer_group_id = cg.customer_group_id)', ['customer_group_id'])->join(['cw' => $this->_defaultIndexerResource->getTable('store_website')], 'tp.website_id = 0 OR tp.website_id = cw.website_id', ['website_id'])->join(['cwd' => $this->_defaultIndexerResource->getTable('catalog_product_index_website')], 'cw.website_id = cwd.website_id', [])->where('cw.website_id != 0')->columns(new \Zend_Db_Expr("MIN({$websiteExpression})"))->group(['tp.entity_id', 'cg.customer_group_id', 'cw.website_id']);
if (!empty($entityIds)) {
$select->where('tp.entity_id IN(?)', $entityIds);
}
$query = $select->insertFromSelect($table);
$this->_connection->query($query);
return $this;
}
/**
* Load product(s) attributes
*
* @param int $storeId
* @param array $productIds
* @param array $attributeTypes
* @return array
*/
public function getProductAttributes($storeId, array $productIds, array $attributeTypes)
{
$result = [];
$selects = [];
$ifStoreValue = $this->connection->getCheckSql('t_store.value_id > 0', 't_store.value', 't_default.value');
foreach ($attributeTypes as $backendType => $attributeIds) {
if ($attributeIds) {
$tableName = $this->getTable('catalog_product_entity_' . $backendType);
$selects[] = $this->connection->select()->from(['t_default' => $tableName], ['entity_id', 'attribute_id'])->joinLeft(['t_store' => $tableName], $this->connection->quoteInto('t_default.entity_id=t_store.entity_id' . ' AND t_default.attribute_id=t_store.attribute_id' . ' AND t_store.store_id = ?', $storeId), ['value' => $this->unifyField($ifStoreValue, $backendType)])->where('t_default.store_id = ?', 0)->where('t_default.attribute_id IN (?)', $attributeIds)->where('t_default.entity_id IN (?)', $productIds);
}
}
if ($selects) {
$select = $this->connection->select()->union($selects, \Magento\Framework\DB\Select::SQL_UNION_ALL);
$query = $this->connection->query($select);
while ($row = $query->fetch()) {
$result[$row['entity_id']][$row['attribute_id']] = $row['value'];
}
}
return $result;
}
/**
* 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);
}
/**
* @param AdapterInterface $connection
* @param bool $isAggregate
* @return mixed
*/
protected function getStatusExpression(AdapterInterface $connection, $isAggregate = false)
{
$isInStockExpression = $isAggregate ? 'MAX(cisi.is_in_stock)' : 'cisi.is_in_stock';
if ($this->_isManageStock()) {
$statusExpr = $connection->getCheckSql('cisi.use_config_manage_stock = 0 AND cisi.manage_stock = 0', 1, $isInStockExpression);
} else {
$statusExpr = $connection->getCheckSql('cisi.use_config_manage_stock = 0 AND cisi.manage_stock = 1', $isInStockExpression, 1);
}
return $statusExpr;
}
