Magento 2 Documentation  2.3
Documentation for Magento 2 CMS v2.3 (December 2018)
Bestsellers.php
Go to the documentation of this file.
1 <?php
7 
13 {
14  const AGGREGATION_DAILY = 'daily';
15 
16  const AGGREGATION_MONTHLY = 'monthly';
17 
18  const AGGREGATION_YEARLY = 'yearly';
19 
23  protected $_productResource;
24 
29 
35  protected $ignoredProductTypes = [
36  \Magento\Catalog\Model\Product\Type::TYPE_BUNDLE => \Magento\Catalog\Model\Product\Type::TYPE_BUNDLE,
37  ];
38 
52  public function __construct(
53  \Magento\Framework\Model\ResourceModel\Db\Context $context,
54  \Psr\Log\LoggerInterface $logger,
55  \Magento\Framework\Stdlib\DateTime\TimezoneInterface $localeDate,
56  \Magento\Reports\Model\FlagFactory $reportsFlagFactory,
57  \Magento\Framework\Stdlib\DateTime\Timezone\Validator $timezoneValidator,
58  \Magento\Framework\Stdlib\DateTime\DateTime $dateTime,
59  \Magento\Catalog\Model\ResourceModel\Product $productResource,
60  \Magento\Sales\Model\ResourceModel\Helper $salesResourceHelper,
61  $connectionName = null,
62  array $ignoredProductTypes = []
63  ) {
64  parent::__construct(
65  $context,
66  $logger,
67  $localeDate,
68  $reportsFlagFactory,
69  $timezoneValidator,
70  $dateTime,
72  );
73  $this->_productResource = $productResource;
74  $this->_salesResourceHelper = $salesResourceHelper;
75  $this->ignoredProductTypes = array_merge($this->ignoredProductTypes, $ignoredProductTypes);
76  }
77 
83  protected function _construct()
84  {
85  $this->_init('sales_bestsellers_aggregated_' . self::AGGREGATION_DAILY, 'id');
86  }
87 
97  public function aggregate($from = null, $to = null)
98  {
99  $connection = $this->getConnection();
100  //$this->getConnection()->beginTransaction();
101 
102  try {
103  if ($from !== null || $to !== null) {
104  $subSelect = $this->_getTableDateRangeSelect(
105  $this->getTable('sales_order'),
106  'created_at',
107  'updated_at',
108  $from,
109  $to
110  );
111  } else {
112  $subSelect = null;
113  }
114 
115  $this->_clearTableByDateRange($this->getMainTable(), $from, $to, $subSelect);
116  // convert dates to current admin timezone
117  $periodExpr = $connection->getDatePartSql(
118  $this->getStoreTZOffsetQuery(
119  ['source_table' => $this->getTable('sales_order')],
120  'source_table.created_at',
121  $from,
122  $to
123  )
124  );
125  $select = $connection->select();
126 
127  $select->group([$periodExpr, 'source_table.store_id', 'order_item.product_id']);
128 
129  $columns = [
130  'period' => $periodExpr,
131  'store_id' => 'source_table.store_id',
132  'product_id' => 'order_item.product_id',
133  'product_name' => new \Zend_Db_Expr('MIN(order_item.name)'),
134  'product_price' => new \Zend_Db_Expr(
135  'MIN(IF(order_item_parent.base_price, order_item_parent.base_price, order_item.base_price))' .
136  '* MIN(source_table.base_to_global_rate)'
137  ),
138  'qty_ordered' => new \Zend_Db_Expr('SUM(order_item.qty_ordered)'),
139  ];
140 
141  $select->from(
142  ['source_table' => $this->getTable('sales_order')],
143  $columns
144  )->joinInner(
145  ['order_item' => $this->getTable('sales_order_item')],
146  'order_item.order_id = source_table.entity_id',
147  []
148  )->joinLeft(
149  ['order_item_parent' => $this->getTable('sales_order_item')],
150  'order_item.parent_item_id = order_item_parent.item_id',
151  []
152  )->where(
153  'source_table.state != ?',
154  \Magento\Sales\Model\Order::STATE_CANCELED
155  )->where(
156  'order_item.product_type NOT IN(?)',
157  $this->ignoredProductTypes
158  );
159 
160  if ($subSelect !== null) {
161  $select->having($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
162  }
163 
164  $select->useStraightJoin();
165  // important!
166  $insertQuery = $select->insertFromSelect($this->getMainTable(), array_keys($columns));
167  $connection->query($insertQuery);
168 
169  $columns = [
170  'period' => 'period',
171  'store_id' => new \Zend_Db_Expr(\Magento\Store\Model\Store::DEFAULT_STORE_ID),
172  'product_id' => 'product_id',
173  'product_name' => new \Zend_Db_Expr('MIN(product_name)'),
174  'product_price' => new \Zend_Db_Expr('MIN(product_price)'),
175  'qty_ordered' => new \Zend_Db_Expr('SUM(qty_ordered)'),
176  ];
177 
178  $select->reset();
179  $select->from(
180  $this->getMainTable(),
181  $columns
182  )->where(
183  'store_id <> ?',
184  \Magento\Store\Model\Store::DEFAULT_STORE_ID
185  );
186 
187  if ($subSelect !== null) {
188  $select->where($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
189  }
190 
191  $select->group(['period', 'product_id']);
192  $insertQuery = $select->insertFromSelect($this->getMainTable(), array_keys($columns));
193  $connection->query($insertQuery);
194 
195  // update rating
196  $this->_updateRatingPos(self::AGGREGATION_DAILY);
197  $this->_updateRatingPos(self::AGGREGATION_MONTHLY);
198  $this->_updateRatingPos(self::AGGREGATION_YEARLY);
199  $this->_setFlagData(\Magento\Reports\Model\Flag::REPORT_BESTSELLERS_FLAG_CODE);
200  } catch (\Exception $e) {
201  throw $e;
202  }
203 
204  return $this;
205  }
206 
213  protected function _updateRatingPos($aggregation)
214  {
215  $aggregationTable = $this->getTable('sales_bestsellers_aggregated_' . $aggregation);
216 
217  $aggregationAliases = [
218  'daily' => self::AGGREGATION_DAILY,
219  'monthly' => self::AGGREGATION_MONTHLY,
220  'yearly' => self::AGGREGATION_YEARLY,
221  ];
222  $this->_salesResourceHelper->getBestsellersReportUpdateRatingPos(
223  $aggregation,
224  $aggregationAliases,
225  $this->getMainTable(),
226  $aggregationTable
227  );
228  return $this;
229  }
230 }
__construct(\Magento\Framework\Model\ResourceModel\Db\Context $context, \Psr\Log\LoggerInterface $logger, \Magento\Framework\Stdlib\DateTime\TimezoneInterface $localeDate, \Magento\Reports\Model\FlagFactory $reportsFlagFactory, \Magento\Framework\Stdlib\DateTime\Timezone\Validator $timezoneValidator, \Magento\Framework\Stdlib\DateTime\DateTime $dateTime, \Magento\Catalog\Model\ResourceModel\Product $productResource, \Magento\Sales\Model\ResourceModel\Helper $salesResourceHelper, $connectionName=null, array $ignoredProductTypes=[])
Definition: Bestsellers.php:52
_makeConditionFromDateRangeSelect($select, $periodColumn, $connection=null)
getStoreTZOffsetQuery( $table, $column, $from=null, $to=null, $store=null, $connection=null)
_clearTableByDateRange( $table, $from=null, $to=null, $subSelect=null, $doNotUseTruncate=false, $connection=null)
$logger
$columns
Definition: default.phtml:15
_getTableDateRangeSelect( $table, $column, $whereColumn, $from=null, $to=null, $additionalWhere=[], $alias='date_range_table')
$connection
Definition: bulk.php:13