/**
* Add JOINs to original select.
*
* @param \Magento\Framework\DB\Select $select
* @return \Magento\Framework\DB\Select
*/
public function modifySelect(\Magento\Framework\DB\Select $select)
{
/* aliases for tables ... */
$tblEntity = 'e';
// this is alias for 'catalog_product_entity' table
$tblStockItem = $this->_resource->getTableName(self::TBL_STOCK_ITEM);
$tblWrhsQty = $this->_resource->getTableName(self::TBL_WRHS_QTY);
/* ... and fields */
$fldStockItemProdId = StockItem::PRODUCT_ID;
$fldStockItemId = StockItem::ITEM_ID;
$fldEntityId = \Magento\Eav\Model\Entity::DEFAULT_ENTITY_ID_FIELD;
$fldQty = self::FLD_QTY;
$fldStockItemRef = Quantity::ATTR_STOCK_ITEM_REF;
/* LEFT JOIN `cataloginventory_stock_item` */
$on = "`{$tblStockItem}`.`{$fldStockItemProdId}`=`{$tblEntity}`.`{$fldEntityId}`";
$fields = [];
$select->joinLeft($tblStockItem, $on, $fields);
/* LEFT JOIN `prxgt_wrhs_qty` */
$on = "`{$tblWrhsQty}`.`{$fldStockItemRef}`=`{$tblStockItem}`.`{$fldStockItemId}`";
$fields = [$fldQty => $this->getEquationQty()];
$select->joinLeft($tblWrhsQty, $on, $fields);
/* GROUP BY */
$select->group("{$tblEntity}.{$fldEntityId}");
return $select;
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return \Magento\Framework\DB\Select|bool
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!$this->_select instanceof \Magento\Framework\DB\Select) {
return false;
}
if (!isset($this->_attributesCache[$attributeCode])) {
$attribute = $this->_categoryResource->getAttribute($attributeCode);
$this->_attributesCache[$attributeCode] = ['entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'backend_type' => $attribute->getBackendType()];
}
$attribute = $this->_attributesCache[$attributeCode];
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
if ($attribute['backend_type'] == 'static') {
$this->_select->where('e.' . $attributeCode . $conditionRule, $value);
} else {
$this->_select->join(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id = 0', [])->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->getConnection()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], $this->getConnection()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), [])->where('(' . $ifCase . ')' . $conditionRule, $value);
}
}
return $this->_select;
}
/**
* Join attribute by code
*
* @param int $storeId
* @param string $attributeCode
* @return void
*/
protected function _joinAttribute($storeId, $attributeCode)
{
$connection = $this->getConnection();
$attribute = $this->_getAttribute($attributeCode);
$this->_select->joinLeft(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND ' . $connection->quoteInto(' t1_' . $attributeCode . '.store_id = ?', \Magento\Store\Model\Store::DEFAULT_STORE_ID) . $connection->quoteInto(' AND t1_' . $attributeCode . '.attribute_id = ?', $attribute['attribute_id']), []);
if (!$attribute['is_global']) {
$this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], $this->getConnection()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id = ?', $storeId), []);
}
}
/**
* Join attribute by code
*
* @param int $storeId
* @param string $attributeCode
* @return void
*/
protected function _joinAttribute($storeId, $attributeCode)
{
$connection = $this->getConnection();
$attribute = $this->_getAttribute($attributeCode);
$linkField = $this->_productResource->getLinkField();
$attrTableAlias = 't1_' . $attributeCode;
$this->_select->joinLeft([$attrTableAlias => $attribute['table']], "e.{$linkField} = {$attrTableAlias}.{$linkField}" . ' AND ' . $connection->quoteInto($attrTableAlias . '.store_id = ?', Store::DEFAULT_STORE_ID) . ' AND ' . $connection->quoteInto($attrTableAlias . '.attribute_id = ?', $attribute['attribute_id']), []);
if (!$attribute['is_global']) {
$attrTableAlias2 = 't2_' . $attributeCode;
$this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], "{$attrTableAlias}.{$linkField} = {$attrTableAlias2}.{$linkField}" . ' AND ' . $attrTableAlias . '.attribute_id = ' . $attrTableAlias2 . '.attribute_id' . ' AND ' . $connection->quoteInto($attrTableAlias2 . '.store_id = ?', $storeId), []);
}
}
/**
* @param Select $select
* @param RequestInterface $request
* @return Select
*/
public function addTables(Select $select, RequestInterface $request)
{
$mappedTables = [];
$filters = $this->getFilters($request->getQuery());
foreach ($filters as $filter) {
list($alias, $table, $mapOn, $mappedFields) = $this->getMappingData($filter);
if (!array_key_exists($alias, $mappedTables)) {
$select->joinLeft([$alias => $table], $mapOn, $mappedFields);
$mappedTables[$alias] = $table;
}
}
return $select;
}
private function _populateSelect(\Magento\Framework\DB\Select $select)
{
/* aliases and tables */
$asAcc = AggRepo::AS_ACCOUNT;
$asDwnl = AggRepo::AS_DOWNLINE;
//
$tblDwnl = [$asDwnl => $this->_resource->getTableName(ECustomer::ENTITY_NAME)];
/* LEFT JOIN prxgt_dwnl_customer */
$cond = $asDwnl . '.' . ECustomer::ATTR_CUSTOMER_ID . '=' . $asAcc . '.' . EAccount::ATTR_CUST_ID;
$cols = [AggEntity::AS_REF => ECustomer::ATTR_HUMAN_REF];
$select->joinLeft($tblDwnl, $cond, $cols);
return $select;
}
/**
* Add attribute join condition to select and return \Zend_Db_Expr
* attribute value definition
* If $condition is not empty apply limitation for select
*
* @param \Magento\Framework\DB\Select $select
* @param string $attrCode the attribute code
* @param string|\Zend_Db_Expr $entity the entity field or expression for condition
* @param string|\Zend_Db_Expr $store the store field or expression for condition
* @param \Zend_Db_Expr $condition the limitation condition
* @param bool $required if required or has condition used INNER join, else - LEFT
* @return \Zend_Db_Expr the attribute value expression
*/
protected function _addAttributeToSelect($select, $attrCode, $entity, $store, $condition = null, $required = false)
{
$attribute = $this->_getAttribute($attrCode);
$attributeId = $attribute->getAttributeId();
$attributeTable = $attribute->getBackend()->getTable();
$connection = $this->getConnection();
$joinType = $condition !== null || $required ? 'join' : 'joinLeft';
if ($attribute->isScopeGlobal()) {
$alias = 'ta_' . $attrCode;
$select->{$joinType}([$alias => $attributeTable], "{$alias}.entity_id = {$entity} AND {$alias}.attribute_id = {$attributeId}" . " AND {$alias}.store_id = 0", []);
$expression = new \Zend_Db_Expr("{$alias}.value");
} else {
$dAlias = 'tad_' . $attrCode;
$sAlias = 'tas_' . $attrCode;
$select->{$joinType}([$dAlias => $attributeTable], "{$dAlias}.entity_id = {$entity} AND {$dAlias}.attribute_id = {$attributeId}" . " AND {$dAlias}.store_id = 0", []);
$select->joinLeft([$sAlias => $attributeTable], "{$sAlias}.entity_id = {$entity} AND {$sAlias}.attribute_id = {$attributeId}" . " AND {$sAlias}.store_id = {$store}", []);
$expression = $connection->getCheckSql($connection->getIfNullSql("{$sAlias}.value_id", -1) . ' > 0', "{$sAlias}.value", "{$dAlias}.value");
}
if ($condition !== null) {
$select->where("{$expression}{$condition}");
}
return $expression;
}
/**
* Add stock status to prepare index select
*
* @param \Magento\Framework\DB\Select $select
* @param \Magento\Store\Model\Website $website
* @return Status
*/
public function addStockStatusToSelect(\Magento\Framework\DB\Select $select, \Magento\Store\Model\Website $website)
{
$websiteId = $website->getId();
$select->joinLeft(['stock_status' => $this->getMainTable()], 'e.entity_id = stock_status.product_id AND stock_status.website_id=' . $websiteId, ['salable' => 'stock_status.stock_status']);
return $this;
}
/**
* Add variable store and default value to select
*
* @param \Magento\Framework\DB\Select $select
* @param integer $storeId
* @return \Magento\Variable\Model\ResourceModel\Variable
*/
protected function _addValueToSelect(\Magento\Framework\DB\Select $select, $storeId = \Magento\Store\Model\Store::DEFAULT_STORE_ID)
{
$connection = $this->getConnection();
$ifNullPlainValue = $connection->getCheckSql('store.plain_value IS NULL', 'def.plain_value', 'store.plain_value');
$ifNullHtmlValue = $connection->getCheckSql('store.html_value IS NULL', 'def.html_value', 'store.html_value');
$select->joinLeft(['def' => $this->getTable('variable_value')], 'def.variable_id = ' . $this->getMainTable() . '.variable_id AND def.store_id = 0', [])->joinLeft(['store' => $this->getTable('variable_value')], 'store.variable_id = def.variable_id AND store.store_id = ' . $connection->quote($storeId), [])->columns(['plain_value' => $ifNullPlainValue, 'html_value' => $ifNullHtmlValue, 'store_plain_value' => 'store.plain_value', 'store_html_value' => 'store.html_value']);
return $this;
}
/**
* Joins attribute value to a select
*
* @param Select $select
* @param object $attribute
* @param string $tableAlias
* @return $this
*/
public function joinAttribute(Select $select, $attribute, $tableAlias = 'main')
{
$defaultAlias = sprintf('attribute_%s_default', $attribute->code);
$scopeAlias = sprintf('attribute_%s_scope', $attribute->code);
$attributeTable = $this->getTable(['entity', $attribute->type]);
if (1 - $attribute->required > 0.01) {
$select->joinLeft([$defaultAlias => $attributeTable], $this->andCondition([sprintf('%s.entity_id = %s.entity_id', $defaultAlias, $tableAlias), sprintf('%s.attribute_id = ?', $defaultAlias) => $attribute->id, sprintf('%s.scope_id = ?', $defaultAlias) => 0]), []);
} else {
$select->join([$defaultAlias => $attributeTable], $this->andCondition([sprintf('%s.entity_id = %s.entity_id', $defaultAlias, $tableAlias), sprintf('%s.attribute_id = ?', $defaultAlias) => $attribute->id, sprintf('%s.scope_id = ?', $defaultAlias) => 0]), []);
}
if ($attribute->scope && $this->scopeCode) {
$select->joinLeft([$scopeAlias => $attributeTable], $this->andCondition([sprintf('%s.entity_id = %s.entity_id', $defaultAlias, $scopeAlias), sprintf('%s.attribute_id = %s.attribute_id', $defaultAlias, $scopeAlias), sprintf('%s.scope_id = ?', $scopeAlias) => $this->scope->getId($this->scopeCode)]), []);
$valueExpression = sprintf('IF(%2$s.value_id IS NOT NULL, %2$s.value, %1$s.value)', $defaultAlias, $scopeAlias);
} else {
$valueExpression = sprintf('%s.value', $defaultAlias);
}
$select->columns([$attribute->code => $valueExpression]);
return $valueExpression;
}
