In my Magento 2 apps don't display the pending orders quantities and Lifetime Sales, Average Order,Tax,Revenue,Shipping values in the graph like they used to.
After some searching around, I figured it only displays "processing" (orders that have been invoiced) or "complete" orders since showing in charts.
I want to pending order details in graph.
Note: in Magento 1 available but not Magento 2 available
Any help would be greatly appreciated.
Thanks!!
You need to create di.xml file here in your custom module
app/code/Vendor/Module/etc/di.xml
Content for this file is..
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
<preference for="Magento\Reports\Model\ResourceModel\Order\Collection" type="Vendor\Module\Model\ResourceModel\Order\Collection" />
</config>
Now you need to create one Model file here
app/code/Vendor/Module/Model/ResourceModel/Order/Collection.php
Content for this file is..
<?php
namespace Vendor\Module\Model\ResourceModel\Order;
class Collection extends \Magento\Reports\Model\ResourceModel\Order\Collection
{
protected function _prepareSummaryLive($range, $customStart, $customEnd, $isFilter = 0)
{
$this->setMainTable('sales_order');
$connection = $this->getConnection();
/**
* Reset all columns, because result will group only by 'created_at' field
*/
$this->getSelect()->reset(\Magento\Framework\DB\Select::COLUMNS);
// $expression = $this->_getSalesAmountExpression();
$expression = sprintf('%s - %s - %s - (%s - %s - %s)',
$connection->getIfNullSql('main_table.base_total_invoiced', 'main_table.base_grand_total'),
$connection->getIfNullSql('main_table.base_tax_invoiced', 'main_table.base_tax_amount'),
$connection->getIfNullSql('main_table.base_shipping_invoiced', 'main_table.base_shipping_amount'),
$connection->getIfNullSql('main_table.base_total_refunded', 0),
$connection->getIfNullSql('main_table.base_tax_refunded', 0),
$connection->getIfNullSql('main_table.base_shipping_refunded', 0)
);
if ($isFilter == 0) {
$this->getSelect()->columns(
[
'revenue' => new \Zend_Db_Expr(
sprintf(
'SUM((%s) * %s)',
$expression,
$connection->getIfNullSql('main_table.base_to_global_rate', 0)
)
),
]
);
} else {
$this->getSelect()->columns(['revenue' => new \Zend_Db_Expr(sprintf('SUM(%s)', $expression))]);
}
$dateRange = $this->getDateRange($range, $customStart, $customEnd);
$tzRangeOffsetExpression = $this->_getTZRangeOffsetExpression(
$range,
'created_at',
$dateRange['from'],
$dateRange['to']
);
$this->getSelect()->columns(
['quantity' => 'COUNT(main_table.entity_id)', 'range' => $tzRangeOffsetExpression]
);
$this->getSelect()
->where('main_table.state NOT IN (?)', array(
\Magento\Sales\Model\Order::STATE_HOLDED,
\Magento\Sales\Model\Order::STATE_CANCELED)
);
$this->getSelect()->order(
'range',
\Magento\Framework\DB\Select::SQL_ASC
)->group(
$tzRangeOffsetExpression
);
$this->addFieldToFilter('created_at', $dateRange);
return $this;
}
protected function _calculateTotalsLive($isFilter = 0)
{
$this->setMainTable('sales_order');
$this->removeAllFieldsFromSelect();
$connection = $this->getConnection();
/*$baseTaxInvoiced = $connection->getIfNullSql('main_table.base_tax_invoiced', 0);
$baseTaxRefunded = $connection->getIfNullSql('main_table.base_tax_refunded', 0);
$baseShippingInvoiced = $connection->getIfNullSql('main_table.base_shipping_invoiced', 0);
$baseShippingRefunded = $connection->getIfNullSql('main_table.base_shipping_refunded', 0);
$revenueExp = $this->_getSalesAmountExpression();
$taxExp = sprintf('%s - %s', $baseTaxInvoiced, $baseTaxRefunded);
$shippingExp = sprintf('%s - %s', $baseShippingInvoiced, $baseShippingRefunded);*/
$baseTotalInvoiced = $connection->getIfNullSql('main_table.base_total_invoiced', 'main_table.base_grand_total'); // This will check if there is no invoice it will calculate based on the grand totals ( so when you generate and invoice u will have no issues with the numbers also )
$baseTotalRefunded = $connection->getIfNullSql('main_table.base_total_refunded', 0);
$baseTaxInvoiced = $connection->getIfNullSql('main_table.base_tax_invoiced', 'main_table.base_tax_amount'); // Same here for taxes
$baseTaxRefunded = $connection->getIfNullSql('main_table.base_tax_refunded', 0);
$baseShippingInvoiced = $connection->getIfNullSql('main_table.base_shipping_invoiced', 'main_table.base_shipping_amount'); // Same here for shipping
$baseShippingRefunded = $connection->getIfNullSql('main_table.base_shipping_refunded', 0);
$revenueExp = sprintf('%s - %s - %s - (%s - %s - %s)',
$baseTotalInvoiced,
$baseTaxInvoiced,
$baseShippingInvoiced,
$baseTotalRefunded,
$baseTaxRefunded,
$baseShippingRefunded
);
$taxExp = sprintf('%s - %s', $baseTaxInvoiced, $baseTaxRefunded);
$shippingExp = sprintf('%s - %s', $baseShippingInvoiced, $baseShippingRefunded);
if ($isFilter == 0) {
$rateExp = $connection->getIfNullSql('main_table.base_to_global_rate', 0);
$this->getSelect()->columns(
[
'revenue' => new \Zend_Db_Expr(sprintf('SUM((%s) * %s)', $revenueExp, $rateExp)),
'tax' => new \Zend_Db_Expr(sprintf('SUM((%s) * %s)', $taxExp, $rateExp)),
'shipping' => new \Zend_Db_Expr(sprintf('SUM((%s) * %s)', $shippingExp, $rateExp)),
]
);
} else {
$this->getSelect()->columns(
[
'revenue' => new \Zend_Db_Expr(sprintf('SUM(%s)', $revenueExp)),
'tax' => new \Zend_Db_Expr(sprintf('SUM(%s)', $taxExp)),
'shipping' => new \Zend_Db_Expr(sprintf('SUM(%s)', $shippingExp)),
]
);
}
$this->getSelect()->columns(
['quantity' => 'COUNT(main_table.entity_id)']
)->where('main_table.state NOT IN (?)', array(
\Magento\Sales\Model\Order::STATE_HOLDED,
\Magento\Sales\Model\Order::STATE_CANCELED)
);
return $this;
}
public function calculateSales($isFilter = 0)
{
$statuses = $this->_orderConfig->getStateStatuses(\Magento\Sales\Model\Order::STATE_CANCELED);
if (empty($statuses)) {
$statuses = [0];
}
$connection = $this->getConnection();
if ($this->_scopeConfig->getValue(
'sales/dashboard/use_aggregated_data',
\Magento\Store\Model\ScopeInterface::SCOPE_STORE
)
) {
$this->setMainTable('sales_order_aggregated_created');
$this->removeAllFieldsFromSelect();
$averageExpr = $connection->getCheckSql(
'SUM(main_table.orders_count) > 0',
'SUM(main_table.total_revenue_amount)/SUM(main_table.orders_count)',
0
);
$this->getSelect()->columns(
['lifetime' => 'SUM(main_table.total_revenue_amount)', 'average' => $averageExpr]
);
if (!$isFilter) {
$this->addFieldToFilter(
'store_id',
['eq' => $this->_storeManager->getStore(\Magento\Store\Model\Store::ADMIN_CODE)->getId()]
);
}
$this->getSelect()->where('main_table.order_status NOT IN(?)', $statuses);
} else {
$this->setMainTable('sales_order');
$this->removeAllFieldsFromSelect();
// $expr = $this->_getSalesAmountExpression();
$expr = sprintf('%s - %s - %s - (%s - %s - %s)',
$connection->getIfNullSql('main_table.base_total_invoiced', 'main_table.base_grand_total'),
$connection->getIfNullSql('main_table.base_tax_invoiced', 'main_table.base_tax_amount'),
$connection->getIfNullSql('main_table.base_shipping_invoiced', 'main_table.base_shipping_amount'),
$connection->getIfNullSql('main_table.base_total_refunded', 0),
$connection->getIfNullSql('main_table.base_tax_refunded', 0),
$connection->getIfNullSql('main_table.base_shipping_refunded', 0)
);
if ($isFilter == 0) {
$expr = '(' . $expr . ') * main_table.base_to_global_rate';
}
$this->getSelect()->columns(
['lifetime' => "SUM({$expr})", 'average' => "AVG({$expr})"]
)->where(
'main_table.status NOT IN(?)',
$statuses
)->where('main_table.state NOT IN(?)', array(
\Magento\Sales\Model\Order::STATE_HOLDED,
\Magento\Sales\Model\Order::STATE_CANCELED)
);
}
return $this;
}
}
After adding above both files Please run below commands once
php bin/magento setup:upgrade
php bin/magento setup:di:compile
php bin/magento cache:clean
php bin/magento cache:flush
After doing above changes you can see your pending orders as well same as Magento 1.
Hope this will help you!
For Magento version 2.2, 2.3 and latest 2.3.3
You need to look into the following Model,
Magento\vendor\magento\module-reports\Model\ResourceModel\Order\Collection.php
Search for text STATE_PENDING in this file and you will get three search results as below,
_prepareSummaryLive() // Prepares the query column list, date range, where condition
_calculateTotalsLive() // Calculates total Sales, tax, shipping and no.of sales WRT above query
calculateSales() // as of now you can ignore this method
Inside method _prepareSummaryLive() and _calculateTotalsLive()
Update the Where condition as per you needs
where(
'main_table.state NOT IN (?)',
[\Magento\Sales\Model\Order::STATE_PENDING_PAYMENT, \Magento\Sales\Model\Order::STATE_NEW]
)
List of status is defined in
The order status Constants are defined in the Magento\vendor\magento\module-sales\Model\Order.php
Consequences:
The report may be confusing for the Shop administrator because the Key terms like Revenue, Shipping and Tax will be [currrency]0.0.
But the quantity count shows the numbers including order pending state.
This is due to Magento calculates the terms like Revenue/Tax, Shipping after it is complete, shipped respectively.
