Created
June 1, 2023 12:49
-
-
Save jayachandraoggy/422060264bcab772571672a495e945c1 to your computer and use it in GitHub Desktop.
to print mysql query for order reports in the magento admin panel
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/** | |
* vendor/magento/module-sales/Model/ResourceModel/Report/Order/Collection.php | |
* Copyright © Magento, Inc. All rights reserved. | |
* See COPYING.txt for license details. | |
*/ | |
namespace Magento\Sales\Model\ResourceModel\Report\Order; | |
/** | |
* Report order collection | |
* | |
* @author Magento Core Team <core@magentocommerce.com> | |
*/ | |
class Collection extends \Magento\Sales\Model\ResourceModel\Report\Collection\AbstractCollection | |
{ | |
/** | |
* Period format | |
* | |
* @var string | |
*/ | |
protected $_periodFormat; | |
/** | |
* Aggregated Data Table | |
* | |
* @var string | |
*/ | |
protected $_aggregationTable = 'sales_order_aggregated_created'; | |
/** | |
* Selected columns | |
* | |
* @var array | |
*/ | |
protected $_selectedColumns = []; | |
/** | |
* @param \Magento\Framework\Data\Collection\EntityFactory $entityFactory | |
* @param \Psr\Log\LoggerInterface $logger | |
* @param \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy | |
* @param \Magento\Framework\Event\ManagerInterface $eventManager | |
* @param \Magento\Sales\Model\ResourceModel\Report $resource | |
* @param \Magento\Framework\DB\Adapter\AdapterInterface $connection | |
*/ | |
public function __construct( | |
\Magento\Framework\Data\Collection\EntityFactory $entityFactory, | |
\Psr\Log\LoggerInterface $logger, | |
\Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy, | |
\Magento\Framework\Event\ManagerInterface $eventManager, | |
\Magento\Sales\Model\ResourceModel\Report $resource, | |
\Magento\Framework\DB\Adapter\AdapterInterface $connection = null | |
) { | |
$resource->init($this->_aggregationTable); | |
parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $resource, $connection); | |
} | |
/** | |
* Get selected columns | |
* | |
* @return array | |
*/ | |
protected function _getSelectedColumns() | |
{ | |
$connection = $this->getConnection(); | |
if ('month' == $this->_period) { | |
$this->_periodFormat = $connection->getDateFormatSql('period', '%Y-%m'); | |
} elseif ('year' == $this->_period) { | |
$this->_periodFormat = $connection->getDateExtractSql( | |
'period', | |
\Magento\Framework\DB\Adapter\AdapterInterface::INTERVAL_YEAR | |
); | |
} else { | |
$this->_periodFormat = $connection->getDateFormatSql('period', '%Y-%m-%d'); | |
} | |
if (!$this->isTotals()) { | |
$this->_selectedColumns = [ | |
'period' => $this->_periodFormat, | |
'orders_count' => 'SUM(orders_count)', | |
'total_qty_ordered' => 'SUM(total_qty_ordered)', | |
'total_qty_invoiced' => 'SUM(total_qty_invoiced)', | |
'total_income_amount' => 'SUM(total_income_amount)', | |
'total_revenue_amount' => 'SUM(total_revenue_amount)', | |
'total_profit_amount' => 'SUM(total_profit_amount)', | |
'total_invoiced_amount' => 'SUM(total_invoiced_amount)', | |
'total_canceled_amount' => 'SUM(total_canceled_amount)', | |
'total_paid_amount' => 'SUM(total_paid_amount)', | |
'total_refunded_amount' => 'SUM(total_refunded_amount)', | |
'total_tax_amount' => 'SUM(total_tax_amount)', | |
'total_tax_amount_actual' => 'SUM(total_tax_amount_actual)', | |
'total_shipping_amount' => 'SUM(total_shipping_amount)', | |
'total_shipping_amount_actual' => 'SUM(total_shipping_amount_actual)', | |
'total_discount_amount' => 'SUM(total_discount_amount)', | |
'total_discount_amount_actual' => 'SUM(total_discount_amount_actual)', | |
]; | |
} | |
if ($this->isTotals()) { | |
$this->_selectedColumns = $this->getAggregatedColumns(); | |
} | |
return $this->_selectedColumns; | |
} | |
/** | |
* Apply custom columns before load | |
* | |
* @return $this | |
*/ | |
protected function _beforeLoad() | |
{ | |
$this->getSelect()->from($this->getResource()->getMainTable(), $this->_getSelectedColumns()); | |
if (!$this->isTotals()) { | |
$this->getSelect()->group($this->_periodFormat); | |
} | |
return parent::_beforeLoad(); | |
} | |
/** | |
* print mysql select query | |
* @return Collection | |
*/ | |
protected function _afterLoad() | |
{ | |
echo $this->getSelect(); | |
return parent::_afterLoad(); // TODO: Change the autogenerated stub | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
SELECT DATE_FORMAT(period, '%Y-%m-%d') AS
period, SUM(orders_count) AS
orders_count, SUM(total_qty_ordered) AS
total_qty_ordered, SUM(total_qty_invoiced) AS
total_qty_invoiced, SUM(total_income_amount) AS
total_income_amount, SUM(total_revenue_amount) AS
total_revenue_amount, SUM(total_profit_amount) AS
total_profit_amount, SUM(total_invoiced_amount) AS
total_invoiced_amount, SUM(total_canceled_amount) AS
total_canceled_amount, SUM(total_paid_amount) AS
total_paid_amount, SUM(total_refunded_amount) AS
total_refunded_amount, SUM(total_tax_amount) AS
total_tax_amount, SUM(total_tax_amount_actual) AS
total_tax_amount_actual, SUM(total_shipping_amount) AS
total_shipping_amount, SUM(total_shipping_amount_actual) AS
total_shipping_amount_actual, SUM(total_discount_amount) AS
total_discount_amount, SUM(total_discount_amount_actual) AS
total_discount_amount_actualFROM
sales_order_aggregated_createdWHERE (period >= '2023-05-22') AND (period <= '2023-05-28') AND (store_id IN(1, 2, 3)) AND (order_status IN('closed', 'complete', 'fraud', 'holded', 'payment_review', 'paypal_canceled_reversal', 'paypal_reversed', 'pending', 'pending_payment', 'pending_paypal', 'processing', 'received')) GROUP BY DATE_FORMAT(period, '%Y-%m-%d');