How to include pending orders into Magento 2 'Order Report' table 'sales_order_aggregated_created'?

Question

I have to run 'pending' order status report, as checked by default Magento 2 doesn't allowed to run pending reports as this doesn't make any sense.

Magento 2 ignoring to list pending order status into multiselect field shown in the below attachment from the file :-

vendor/magento\module-sales\Block\Adminhtml\Report\Filter\Form.php

After commenting the code, I am able to list pending order status into multiselect field

But after selecting 'pending' order status and then click on 'Show Report', empty report is getting in the list which is wrong.

Debug further and found that, table 'sales_order_aggregated_created' is responsible to display report and this table doesn't include pending records that's why I am getting empty records for pending order status.

So can anyone please let me know which file is responsible for inserting records into table 'sales_order_aggregated_created' ? and the record is inserting after placing order OR changing the status of the order from backend ?

I tried to find the file and find following file related with my search :-

magento\module-reports\Model\ResourceModel\Order\Collection.php
magento\module-sales\Model\ResourceModel\Report\Order\Createdat.php

& removed pending restriction from both above file and place one 'COD' order but doesn't find any entry of this pending record into table 'sales_order_aggregated_created'.

Any help will be appreciated, thanks!

Wakar Ahamad · Accepted Answer · 2021-08-04 05:01:58Z

Here is the solution:-

To display 'pending' status into multiselect field(Magento Admin->Reports->Sales->Orders->Order Status->Specified->Multiselect):-

app\code\MyNamespace\Sales\etc\adminhtml\di.xml :-

<preference for="Magento\Sales\Block\Adminhtml\Report\Filter\Form\Order" type="MyNamespace\Sales\Block\Adminhtml\Rewrite\Report\Filter\FormExtended" />

Note :- Here instead of overriding method _prepareForm() of the class :-

magento\module-sales\Block\Adminhtml\Report\Filter\Form.php

i am overriding class :-

Magento\Sales\Block\Adminhtml\Rewrite\Report\Filter\Form\Order

As we can't override class :-

magento\module-sales\Block\Adminhtml\Report\Filter\Form.php method _prepareForm() as this is also using as parent for some other classes.

Throgh overriding, I have removed condition if (false === strpos($code, 'pending')) and doing this first of all I have removed original field 'order_statuses' and then adding new throgh the overrided file:- MyNamespace\Sales\Block\Adminhtml\Rewrite\Report\Filter\FormExtended including pending status.

app\code\MyNamespace\Sales\Block\Adminhtml\Rewrite\Report\Filter\FormExtended.php:-

<?php

namespace MyNamespace\Sales\Block\Adminhtml\Rewrite\Report\Filter;

use \Magento\Sales\Block\Adminhtml\Report\Filter\Form\Order;
use \MyNamespace\Sales\Helper\PendingReportConfig;
use \Magento\Framework\App\Request\Http;
use \Magento\Backend\Block\Template\Context;
use \Magento\Framework\Registry;
use \Magento\Framework\Data\FormFactory;
use \Magento\Sales\Model\Order\ConfigFactory;

class FormExtended extends Order
{
    /**
     * Order config
     *
     * @var \Magento\Sales\Model\Order\ConfigFactory
     */
    protected $_orderConfig;

    /**
     * @var PendingReportConfig
     */
    protected $pendingReportConfig;

    /**
     * @var Http
     */
    protected $requestParams;

    /**
     * @param Context $context
     * @param Registry $registry
     * @param FormFactory $formFactory
     * @param ConfigFactory $orderConfig
     * @param pendingReportConfig $pendingReportConfig
     * @param array $data
     */
    public function __construct(
        Context $context,
        Registry $registry,
        FormFactory $formFactory,
        ConfigFactory $orderConfig,
        PendingReportConfig $pendingReportConfig,
        Http $requestParams,
        array $data = []
    ) {
        $this->pendingReportConfig = $pendingReportConfig;
        $this->requestParams = $requestParams;
        parent::__construct($context, $registry, $formFactory, $orderConfig, $data);
    }

    protected function _prepareForm()
    {
        $params = $this->requestParams->getParams();

        $isPendingReportEnabled = $this->pendingReportConfig->isPendingReportEnabled($params);

        parent::_prepareForm();

        if ($isPendingReportEnabled) {
            $fieldset = $this->getForm()->getElement('base_fieldset');

            $fieldset->removeField('order_statuses');

            $statuses = $this->_orderConfig->create()->getStatuses();

            $values = [];
            foreach ($statuses as $code => $label) {
                $values[] = ['label' => __($label), 'value' => $code];
            }


            $fieldset->addField(
                'order_statuses',
                'multiselect',
                [
                    'name' => 'order_statuses',
                    'label' => '',
                    'values' => $values,
                    'display' => 'none'
                ],
                'show_order_statuses'
            );

            return $this;
        }
    }
}

On refreshing Order Statistics (Magento Admin->Reports->Statistics->Refresh Statistics), order records updated into the table 'sales_order_aggregated_created' and 'sales_order_aggregated_updated' so to update pending records here overrided core classes Createdat and Updatedat under:-

app\code\MyNamespace\Sales\etc\adminhtml\di.xml :-

<preference for="Magento\Sales\Model\ResourceModel\Report\Order\Createdat" type="MyNamespace\Sales\Model\Rewrite\ResourceModel\Report\Order\Createdat" /> 

<preference for="Magento\Sales\Model\ResourceModel\Report\Order\Updatedat" type="MyNamespace\Sales\Model\Rewrite\ResourceModel\Report\Order\Updatedat" />

MyNamespace\Sales\Model\Rewrite\ResourceModel\Report\Order\Createdat.php:-

<?php

namespace MyNamespace\Sales\Model\Rewrite\ResourceModel\Report\Order;

use \Magento\Sales\Model\ResourceModel\Report\Order\Createdat as SalesCreatedat;

class Createdat extends SalesCreatedat
{
    protected function _aggregateByField($aggregationField, $from, $to)
    {

        $connection = $this->getConnection();

        $connection->beginTransaction();
        try {
            if ($from !== null || $to !== null) {
                $subSelect = $this->_getTableDateRangeSelect(
                    $this->getTable('sales_order'),
                    $aggregationField,
                    $aggregationField,
                    $from,
                    $to
                );
            } else {
                $subSelect = null;
            }
            $this->_clearTableByDateRange($this->getMainTable(), $from, $to, $subSelect);

            $periodExpr = $connection->getDatePartSql(
                $this->getStoreTZOffsetQuery(
                    ['o' => $this->getTable('sales_order')],
                    'o.' . $aggregationField,
                    $from,
                    $to
                )
            );
            // Columns list
            $columns = [
                'period' => $periodExpr,
                'store_id' => 'o.store_id',
                'order_status' => 'o.status',
                'orders_count' => new \Zend_Db_Expr('COUNT(o.entity_id)'),
                'total_qty_ordered' => new \Zend_Db_Expr('SUM(oi.total_qty_ordered)'),
                'total_qty_invoiced' => new \Zend_Db_Expr('SUM(oi.total_qty_invoiced)'),
                'total_income_amount' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM((%s - %s) * %s)',
                        $connection->getIfNullSql('o.base_grand_total', 0),
                        $connection->getIfNullSql('o.base_total_canceled', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_revenue_amount' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM((%s - %s - %s - (%s - %s - %s)) * %s)',
                        $connection->getIfNullSql('o.base_total_invoiced', 0),
                        $connection->getIfNullSql('o.base_tax_invoiced', 0),
                        $connection->getIfNullSql('o.base_shipping_invoiced', 0),
                        $connection->getIfNullSql('o.base_total_refunded', 0),
                        $connection->getIfNullSql('o.base_tax_refunded', 0),
                        $connection->getIfNullSql('o.base_shipping_refunded', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_profit_amount' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM((%s - %s - %s - %s - %s) * %s)',
                        $connection->getIfNullSql('o.base_total_paid', 0),
                        $connection->getIfNullSql('o.base_total_refunded', 0),
                        $connection->getIfNullSql('o.base_tax_invoiced', 0),
                        $connection->getIfNullSql('o.base_shipping_invoiced', 0),
                        $connection->getIfNullSql('o.base_total_invoiced_cost', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_invoiced_amount' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM(%s * %s)',
                        $connection->getIfNullSql('o.base_total_invoiced', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_canceled_amount' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM(%s * %s)',
                        $connection->getIfNullSql('o.base_total_canceled', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_paid_amount' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM(%s * %s)',
                        $connection->getIfNullSql('o.base_total_paid', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_refunded_amount' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM(%s * %s)',
                        $connection->getIfNullSql('o.base_total_refunded', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_tax_amount' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM((%s - %s) * %s)',
                        $connection->getIfNullSql('o.base_tax_amount', 0),
                        $connection->getIfNullSql('o.base_tax_canceled', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_tax_amount_actual' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM((%s -%s) * %s)',
                        $connection->getIfNullSql('o.base_tax_invoiced', 0),
                        $connection->getIfNullSql('o.base_tax_refunded', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_shipping_amount' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM((%s - %s) * %s)',
                        $connection->getIfNullSql('o.base_shipping_amount', 0),
                        $connection->getIfNullSql('o.base_shipping_canceled', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_shipping_amount_actual' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM((%s - %s) * %s)',
                        $connection->getIfNullSql('o.base_shipping_invoiced', 0),
                        $connection->getIfNullSql('o.base_shipping_refunded', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_discount_amount' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM((ABS(%s) - %s) * %s)',
                        $connection->getIfNullSql('o.base_discount_amount', 0),
                        $connection->getIfNullSql('o.base_discount_canceled', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
                'total_discount_amount_actual' => new \Zend_Db_Expr(
                    sprintf(
                        'SUM((%s - %s) * %s)',
                        $connection->getIfNullSql('o.base_discount_invoiced', 0),
                        $connection->getIfNullSql('o.base_discount_refunded', 0),
                        $connection->getIfNullSql('o.base_to_global_rate', 0)
                    )
                ),
            ];

            $select = $connection->select();
            $selectOrderItem = $connection->select();

            $qtyCanceledExpr = $connection->getIfNullSql('qty_canceled', 0);
            $cols = [
                'order_id' => 'order_id',
                'total_qty_ordered' => new \Zend_Db_Expr("SUM(qty_ordered - {$qtyCanceledExpr})"),
                'total_qty_invoiced' => new \Zend_Db_Expr('SUM(qty_invoiced)'),
            ];
            $selectOrderItem->from(
                $this->getTable('sales_order_item'),
                $cols
            )->where(
                'parent_item_id IS NULL'
            )->group(
                'order_id'
            );

            $select->from(
                ['o' => $this->getTable('sales_order')],
                $columns
            )->join(
                ['oi' => $selectOrderItem],
                'oi.order_id = o.entity_id',
                []
            );

            if ($subSelect !== null) {
                $select->having($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
            }

            $select->group([$periodExpr, 'o.store_id', 'o.status']);

            $connection->query($select->insertFromSelect($this->getMainTable(), array_keys($columns)));

            // setup all columns to select SUM() except period, store_id and order_status
            foreach ($columns as $k => $v) {
                $columns[$k] = new \Zend_Db_Expr('SUM(' . $k . ')');
            }
            $columns['period'] = 'period';
            $columns['store_id'] = new \Zend_Db_Expr(\Magento\Store\Model\Store::DEFAULT_STORE_ID);
            $columns['order_status'] = 'order_status';

            $select->reset();
            $select->from($this->getMainTable(), $columns)->where('store_id <> 0');

            if ($subSelect !== null) {
                $select->where($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
            }

            $select->group(['period', 'order_status']);
            $connection->query($select->insertFromSelect($this->getMainTable(), array_keys($columns)));
            $connection->commit();
        } catch (\Exception $e) {
            $connection->rollBack();
            throw $e;
        }

        return $this;
    }
}

MyNamespace\Sales\Model\Rewrite\ResourceModel\Report\Order\Updatedat.php

<?php

namespace MyNamespace\Sales\Model\Rewrite\ResourceModel\Report\Order;

class Updatedat extends Createdat
{
    public function aggregate($from = null, $to = null)
    {
        return $this->_aggregateByField('updated_at', $from, $to);
    }
}

Also given configuration so that we can disabled this feature whenever required :-

code\MyNamespace\Sales\etc\adminhtml\system.xml

 <section id="reports" translate="label" type="text" sortOrder="2000" showInDefault="1" showInWebsite="1" showInStore="1">
             <group id="options" translate="label" type="text" sortOrder="1" showInDefault="1" showInWebsite="1" showInStore="1">
                <field id="enable_pending_report" translate="label comment" type="select" sortOrder="1" showInDefault="1" showInWebsite="1" showInStore="1">
                        <label>Enable Pending Reports</label>
                        <source_model>Magento\Config\Model\Config\Source\Yesno</source_model>
                        <comment>If disabled, pending reports can't be genrated</comment>
                </field>
             </group>
        </section>

code\MyNamespace\Sales\Helper\PendingReportConfig.php

<?php

namespace MyNamespace\Sales\Helper;

use Magento\Framework\App\Helper\AbstractHelper;
use Magento\Framework\App\Config\ScopeConfigInterface;
use Magento\Store\Model\ScopeInterface;

/**
 * Class PendingReportConfig
 * @package MyNamespace\Sales\Helper
 */
class PendingReportConfig extends AbstractHelper
{
    const PENDING_REPORTS = 'reports/options/enable_pending_report';
    const WEBSITE = 'website';
    const STORE = 'store';
    const GROUP = 'group';

    /**
     * @var ScopeConfigInterface
     */
    protected $scopeConfig;

    public function __construct(
        ScopeConfigInterface $scopeConfig
    ) {
        $this->scopeConfig = $scopeConfig;
    }


    public function getPendingReportConfigVal($scope = null, $scopeId = null)
    {
        switch ($scope) {
            case self::WEBSITE:
                $storeScope = ScopeInterface::SCOPE_WEBSITE;
                break;
            case self::STORE:
                $storeScope = ScopeInterface::SCOPE_STORE;
                break;
            case self::GROUP:
                $storeScope = ScopeInterface::SCOPE_GROUP;
                break;
        }
        return $this->scopeConfig->getValue(self::PENDING_REPORTS, $storeScope, $scopeId);
    }

    public function isPendingReportEnabled($params)
    {
        $pendingReportEnabled = false;
        $pendingReportEnabledGlobally = false;

        if (isset($params['website'])) {
            $pendingReportEnabled = $this->getPendingReportConfigVal(self::WEBSITE, $params['website']);
        } else if (isset($params['store_ids'])) {
            $pendingReportEnabled = $this->getPendingReportConfigVal(self::STORE, $params['store_ids']);
        } else if (isset($params['group'])) {
            $pendingReportEnabled = $this->getPendingReportConfigVal(self::GROUP, $params['group']);
        } else {
            $pendingReportEnabledGlobally = $this->isPendingReportEnabledGlobally();
        }

        if (
            (isset($params['website']) && $pendingReportEnabled) ||
            (isset($params['store_ids']) && $pendingReportEnabled) ||
            (isset($params['group']) && $pendingReportEnabled) ||
            $pendingReportEnabledGlobally

        ) {
            return true;
        }
    }

    public function isPendingReportEnabledGlobally()
    {
        $WEBSITE_PENDING_REPORTS_ENABLED = $this->scopeConfig->getValue(self::PENDING_REPORTS, ScopeInterface::SCOPE_WEBSITE);
        $STORE_PENDING_REPORTS_ENABLED = $this->scopeConfig->getValue(self::PENDING_REPORTS, ScopeInterface::SCOPE_STORE);
        $GROUP_PENDING_REPORTS_ENABLED = $this->scopeConfig->getValue(self::PENDING_REPORTS, ScopeInterface::SCOPE_GROUP);
        if (
            ($WEBSITE_PENDING_REPORTS_ENABLED) ||
            ($STORE_PENDING_REPORTS_ENABLED) ||
            ($GROUP_PENDING_REPORTS_ENABLED)

        ) {
            return true;
        }
    }
}

Stack Exchange Network

How to include pending orders into Magento 2 'Order Report' table 'sales_order_aggregated_created'?

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
orders
order-status
reports
magento2.3.5-p1
place-order
or ask your own question.

Hot Network Questions

How to include pending orders into Magento 2 'Order Report' table 'sales_order_aggregated_created'?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged ordersorder-statusreportsmagento2.3.5-p1place-order or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
orders
order-status
reports
magento2.3.5-p1
place-order
or ask your own question.