/**
* Retrieve product attribute
*
* @param string $attributeCode
* @param int|array $productIds
* @param string $storeId
* @return array
*/
public function _getProductAttribute($attributeCode, $productIds, $storeId)
{
$adapter = $this->_getReadAdapter();
if (!isset($this->_productAttributes[$attributeCode])) {
$attribute = $this->productResource->getAttribute($attributeCode);
$this->_productAttributes[$attributeCode] = ['entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal()];
unset($attribute);
}
if (!is_array($productIds)) {
$productIds = [$productIds];
}
$bind = ['attribute_id' => $this->_productAttributes[$attributeCode]['attribute_id']];
$select = $adapter->select();
$attributeTable = $this->_productAttributes[$attributeCode]['table'];
if ($this->_productAttributes[$attributeCode]['is_global'] || $storeId == 0) {
$select->from($attributeTable, ['entity_id', 'value'])->where('attribute_id = :attribute_id')->where('store_id = ?', 0)->where('entity_id IN(?)', $productIds);
} else {
$valueExpr = $adapter->getCheckSql('t2.value_id > 0', 't2.value', 't1.value');
$select->from(['t1' => $attributeTable], ['entity_id', 'value' => $valueExpr])->joinLeft(['t2' => $attributeTable], 't1.entity_id = t2.entity_id AND t1.attribute_id = t2.attribute_id AND t2.store_id=:store_id', [])->where('t1.store_id = ?', 0)->where('t1.attribute_id = :attribute_id')->where('t1.entity_id IN(?)', $productIds);
$bind['store_id'] = $storeId;
}
$rowSet = $adapter->fetchAll($select, $bind);
$attributes = [];
foreach ($rowSet as $row) {
$attributes[$row['entity_id']] = $row['value'];
}
unset($rowSet);
foreach ($productIds as $productId) {
if (!isset($attributes[$productId])) {
$attributes[$productId] = null;
}
}
return $attributes;
}
/**
* Get attribute data by attribute code
*
* @param string $attributeCode
* @return array
*/
protected function _getAttribute($attributeCode)
{
if (!isset($this->_attributesCache[$attributeCode])) {
$attribute = $this->_productResource->getAttribute($attributeCode);
$this->_attributesCache[$attributeCode] = ['entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal() == \Magento\Catalog\Model\Resource\Eav\Attribute::SCOPE_GLOBAL, 'backend_type' => $attribute->getBackendType()];
}
return $this->_attributesCache[$attributeCode];
}
/**
* Aggregate products view data
*
* @param null|mixed $from
* @param null|mixed $to
* @return $this
*/
public function aggregate($from = null, $to = null)
{
$mainTable = $this->getMainTable();
$adapter = $this->_getWriteAdapter();
// convert input dates to UTC to be comparable with DATETIME fields in DB
$from = $this->_dateToUtc($from);
$to = $this->_dateToUtc($to);
$this->_checkDates($from, $to);
if ($from !== null || $to !== null) {
$subSelect = $this->_getTableDateRangeSelect($this->getTable('report_event'), 'logged_at', 'logged_at', $from, $to);
} else {
$subSelect = null;
}
$this->_clearTableByDateRange($mainTable, $from, $to, $subSelect);
// convert dates from UTC to current admin timezone
$periodExpr = $adapter->getDatePartSql($this->getStoreTZOffsetQuery(array('source_table' => $this->getTable('report_event')), 'source_table.logged_at', $from, $to));
$select = $adapter->select();
$select->group(array($periodExpr, 'source_table.store_id', 'source_table.object_id'));
$viewsNumExpr = new \Zend_Db_Expr('COUNT(source_table.event_id)');
$columns = array('period' => $periodExpr, 'store_id' => 'source_table.store_id', 'product_id' => 'source_table.object_id', 'product_name' => new \Zend_Db_Expr(sprintf('MIN(%s)', $adapter->getIfNullSql('product_name.value', 'product_default_name.value'))), 'product_price' => new \Zend_Db_Expr(sprintf('MIN(%s)', $adapter->getIfNullSql($adapter->getIfNullSql('product_price.value', 'product_default_price.value'), 0))), 'views_num' => $viewsNumExpr);
$select->from(array('source_table' => $this->getTable('report_event')), $columns)->where('source_table.event_type_id = ?', \Magento\Reports\Model\Event::EVENT_PRODUCT_VIEW);
$select->joinInner(array('product' => $this->getTable('catalog_product_entity')), 'product.entity_id = source_table.object_id', array());
// join product attributes Name & Price
$nameAttribute = $this->_productResource->getAttribute('name');
$joinExprProductName = array('product_name.entity_id = product.entity_id', 'product_name.store_id = source_table.store_id', $adapter->quoteInto('product_name.attribute_id = ?', $nameAttribute->getAttributeId()));
$joinExprProductName = implode(' AND ', $joinExprProductName);
$joinProductName = array('product_default_name.entity_id = product.entity_id', 'product_default_name.store_id = 0', $adapter->quoteInto('product_default_name.attribute_id = ?', $nameAttribute->getAttributeId()));
$joinProductName = implode(' AND ', $joinProductName);
$select->joinLeft(array('product_name' => $nameAttribute->getBackend()->getTable()), $joinExprProductName, array())->joinLeft(array('product_default_name' => $nameAttribute->getBackend()->getTable()), $joinProductName, array());
$priceAttribute = $this->_productResource->getAttribute('price');
$joinExprProductPrice = array('product_price.entity_id = product.entity_id', 'product_price.store_id = source_table.store_id', $adapter->quoteInto('product_price.attribute_id = ?', $priceAttribute->getAttributeId()));
$joinExprProductPrice = implode(' AND ', $joinExprProductPrice);
$joinProductPrice = array('product_default_price.entity_id = product.entity_id', 'product_default_price.store_id = 0', $adapter->quoteInto('product_default_price.attribute_id = ?', $priceAttribute->getAttributeId()));
$joinProductPrice = implode(' AND ', $joinProductPrice);
$select->joinLeft(array('product_price' => $priceAttribute->getBackend()->getTable()), $joinExprProductPrice, array())->joinLeft(array('product_default_price' => $priceAttribute->getBackend()->getTable()), $joinProductPrice, array());
$havingPart = array($adapter->prepareSqlCondition($viewsNumExpr, array('gt' => 0)));
if (null !== $subSelect) {
$subSelectHavingPart = $this->_makeConditionFromDateRangeSelect($subSelect, 'period');
if ($subSelectHavingPart) {
$havingPart[] = '(' . $subSelectHavingPart . ')';
}
}
$select->having(implode(' AND ', $havingPart));
$select->useStraightJoin();
$insertQuery = $select->insertFromSelect($this->getMainTable(), array_keys($columns));
$adapter->query($insertQuery);
$this->_resourceHelper->updateReportRatingPos('day', 'views_num', $mainTable, $this->getTable(self::AGGREGATION_DAILY));
$this->_resourceHelper->updateReportRatingPos('month', 'views_num', $mainTable, $this->getTable(self::AGGREGATION_MONTHLY));
$this->_resourceHelper->updateReportRatingPos('year', 'views_num', $mainTable, $this->getTable(self::AGGREGATION_YEARLY));
$this->_setFlagData(\Magento\Reports\Model\Flag::REPORT_PRODUCT_VIEWED_FLAG_CODE);
return $this;
}
/**
* Aggregate Orders data by order created at
*
* @param string|int|\DateTime|array|null $from
* @param string|int|\DateTime|array|null $to
* @return $this
* @throws \Exception
* @SuppressWarnings(PHPMD.ExcessiveMethodLength)
*/
public function aggregate($from = null, $to = null)
{
// convert input dates to UTC to be comparable with DATETIME fields in DB
$from = $this->_dateToUtc($from);
$to = $this->_dateToUtc($to);
$adapter = $this->_getWriteAdapter();
//$this->_getWriteAdapter()->beginTransaction();
try {
if ($from !== null || $to !== null) {
$subSelect = $this->_getTableDateRangeSelect($this->getTable('sales_order'), 'created_at', 'updated_at', $from, $to);
} else {
$subSelect = null;
}
$this->_clearTableByDateRange($this->getMainTable(), $from, $to, $subSelect);
// convert dates from UTC to current admin timezone
$periodExpr = $adapter->getDatePartSql($this->getStoreTZOffsetQuery(['source_table' => $this->getTable('sales_order')], 'source_table.created_at', $from, $to));
$select = $adapter->select();
$select->group([$periodExpr, 'source_table.store_id', 'order_item.product_id']);
$columns = ['period' => $periodExpr, 'store_id' => 'source_table.store_id', 'product_id' => 'order_item.product_id', 'product_name' => new \Zend_Db_Expr(sprintf('MIN(%s)', $adapter->getIfNullSql('product_name.value', 'product_default_name.value'))), 'product_price' => new \Zend_Db_Expr(sprintf('%s * %s', new \Zend_Db_Expr(sprintf('MIN(%s)', $adapter->getIfNullSql($adapter->getIfNullSql('product_price.value', 'product_default_price.value'), 0))), new \Zend_Db_Expr(sprintf('MIN(%s)', $adapter->getIfNullSql('source_table.base_to_global_rate', '0'))))), 'qty_ordered' => new \Zend_Db_Expr('SUM(order_item.qty_ordered)')];
$select->from(['source_table' => $this->getTable('sales_order')], $columns)->joinInner(['order_item' => $this->getTable('sales_order_item')], 'order_item.order_id = source_table.entity_id', [])->where('source_table.state != ?', \Magento\Sales\Model\Order::STATE_CANCELED);
$joinExpr = ['product.entity_id = order_item.product_id', $adapter->quoteInto('product.type_id NOT IN(?)', $this->ignoredProductTypes)];
$joinExpr = implode(' AND ', $joinExpr);
$select->joinInner(['product' => $this->getTable('catalog_product_entity')], $joinExpr, []);
// join product attributes Name & Price
$attr = $this->_productResource->getAttribute('name');
$joinExprProductName = ['product_name.entity_id = product.entity_id', 'product_name.store_id = source_table.store_id', $adapter->quoteInto('product_name.attribute_id = ?', $attr->getAttributeId())];
$joinExprProductName = implode(' AND ', $joinExprProductName);
$joinProductName = ['product_default_name.entity_id = product.entity_id', 'product_default_name.store_id = 0', $adapter->quoteInto('product_default_name.attribute_id = ?', $attr->getAttributeId())];
$joinProductName = implode(' AND ', $joinProductName);
$select->joinLeft(['product_name' => $attr->getBackend()->getTable()], $joinExprProductName, [])->joinLeft(['product_default_name' => $attr->getBackend()->getTable()], $joinProductName, []);
$attr = $this->_productResource->getAttribute('price');
$joinExprProductPrice = ['product_price.entity_id = product.entity_id', 'product_price.store_id = source_table.store_id', $adapter->quoteInto('product_price.attribute_id = ?', $attr->getAttributeId())];
$joinExprProductPrice = implode(' AND ', $joinExprProductPrice);
$joinProductPrice = ['product_default_price.entity_id = product.entity_id', 'product_default_price.store_id = 0', $adapter->quoteInto('product_default_price.attribute_id = ?', $attr->getAttributeId())];
$joinProductPrice = implode(' AND ', $joinProductPrice);
$select->joinLeft(['product_price' => $attr->getBackend()->getTable()], $joinExprProductPrice, [])->joinLeft(['product_default_price' => $attr->getBackend()->getTable()], $joinProductPrice, []);
if ($subSelect !== null) {
$select->having($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
}
$select->useStraightJoin();
// important!
$insertQuery = $select->insertFromSelect($this->getMainTable(), array_keys($columns));
$adapter->query($insertQuery);
$columns = ['period' => 'period', 'store_id' => new \Zend_Db_Expr(\Magento\Store\Model\Store::DEFAULT_STORE_ID), 'product_id' => 'product_id', 'product_name' => new \Zend_Db_Expr('MIN(product_name)'), 'product_price' => new \Zend_Db_Expr('MIN(product_price)'), 'qty_ordered' => new \Zend_Db_Expr('SUM(qty_ordered)')];
$select->reset();
$select->from($this->getMainTable(), $columns)->where('store_id <> ?', \Magento\Store\Model\Store::DEFAULT_STORE_ID);
if ($subSelect !== null) {
$select->where($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
}
$select->group(['period', 'product_id']);
$insertQuery = $select->insertFromSelect($this->getMainTable(), array_keys($columns));
$adapter->query($insertQuery);
// update rating
$this->_updateRatingPos(self::AGGREGATION_DAILY);
$this->_updateRatingPos(self::AGGREGATION_MONTHLY);
$this->_updateRatingPos(self::AGGREGATION_YEARLY);
$this->_setFlagData(\Magento\Reports\Model\Flag::REPORT_BESTSELLERS_FLAG_CODE);
} catch (\Exception $e) {
throw $e;
}
return $this;
}