3

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.

enter image description here

Note: in Magento 1 available but not Magento 2 available

Any help would be greatly appreciated.
Thanks!!

  • you want to show pending + processing + complete order's total, QTY and all in this graph Right ? – Kishan Savaliya Nov 19 '19 at 4:09
  • 1
    yes i want to all data working like screenshot @KishanSavaliya – Rakesh Donga Nov 19 '19 at 4:32
  • One more question here, You just want to show that data on dashboard or in reports as well ? – Kishan Savaliya Nov 19 '19 at 4:39
  • @KishanSavaliya i want to dashboard all graph details working expect when processing order like in screenshot red box – Rakesh Donga Nov 19 '19 at 4:40
  • 1
    @RakeshDonga, Please check my answer and let me know if it helps. Thanks – Kishan Savaliya Nov 19 '19 at 5:37
8
+150

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.

enter image description here

Hope this will help you!

  • Let me know if this works for you or you'll get any errors then. – Kishan Savaliya Nov 19 '19 at 6:48
  • 1
    kishan currently i am outside of my work and may be i am sure this is working because you have attached screenshot , Thank you once again!! – Rakesh Donga Nov 19 '19 at 6:53
  • Okay @RakeshDonga, You can ping me if you will get any issues then. :) – Kishan Savaliya Nov 19 '19 at 6:54
  • @RakeshDonga, I've made this changes with Magento 2.3.3 version. FYI. Thanks. – Kishan Savaliya Nov 19 '19 at 14:49
  • 1
    Thanks Brother, for accepting my answer and upvote. :) – Kishan Savaliya Nov 20 '19 at 5:58
1

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.