Neptune Web, Inc. logo

Problems with Order Reporting in Magento

We've identified a possible bug in Magento Enterprise 1.8 which is causing the order report to become inaccurate under certain conditions after performing the last day refresh. The issue is causing orders not to be included in the report if the order was created within the requested date range and updated after the selected end date. Here are our findings regarding the issue.

The order report with the "Match Period To" filter set to "Order Created Date" is generated using the sales_order_aggregated_created database table.

This table is populated when a Refresh Statistics action is performed, either Last Day or Lifetime refresh. The refreshLifetime and refreshRecent actions are defined here:

/app/code/core/Mage/Adminhtml/controllers/Report/StatisticsController.php

The aggregate() function (that reads the main order table sales_flat_order) used by the refresh actions is here:

/app/code/core/Mage/Sales/Model/Mysql4/Report/Order.php

By analyzing the aggregate() function you can see that for a last day refresh it's deleting some table rows using the _clearTableByDateRange() function found here:

/app/code/core/Mage/Reports/Model/Mysql4/Report/Abstract.php

The condition for what is deleted and recreated is coming from the _getTableDateRangeSelect() function. However, if you look at the parameters and the sql query it creates it seems that the deleted rows depend on the updated_at column in the sales_flat_order table. So it seems that the last day refresh action is refreshing orders updated within the last day as opposed to created within the last day. Since this refreshRecent action is invoked periodically by cron it means that the order report is constantly wrong and only correct after the lifetime refresh.

The workaround is to disable using the last day refresh. The refresh can be invoked from cron or from the admin interface.

For cron we change it to invoke the lifetime refresh instead. This is done by changing the file /app/code/core/Mage/Sales/Model/Observer.php.

In the function aggregateSalesReportOrderData() we need to change:

    public function aggregateSalesReportOrderData($schedule)
    {
        Mage::app()->getLocale()->emulate(0);
        $currentDate = Mage::app()->getLocale()->date();
        $date = $currentDate->subHour(25);
        Mage::getResourceModel('sales/report_order')->aggregate($date);
        Mage::app()->getLocale()->revert();
        return $this;
    }

to:

    public function aggregateSalesReportOrderData($schedule)
    {
        Mage::app()->getLocale()->emulate(0);
        $currentDate = Mage::app()->getLocale()->date();
#        $date = $currentDate->subHour(25);
$date = null;
        Mage::getResourceModel('sales/report_order')->aggregate($date);
        Mage::app()->getLocale()->revert();
        return $this;
    }


To disable invoking it from the web interface we add this to .htaccess:

# block access to refreshing recent statistics
RewriteEngine on
RewriteRule index.php/admin/report_sales/refreshRecent/ /magento-bug.html [R,L]
RewriteRule index.php/admin/report_statistics/refreshRecent/ /magento-bug.html [R,L]

And create a file magento-bug.html that will be shown when user tries to refresh last day statistics. Sample content for magento-bug.html:

<html>
<body>
Do not try to refresh daily statistics. This is broken. Refresh lifetime statistics instead.
</body>
</html>

We hope this may help if you are experiencing similar problems. Feel free to contact us with any Magento programming projects you may have.

Neptune Web is a full-service Boston-area interactive web and digital marketing agency with expertise in Website Design, Web Development, Digital Marketing Strategy and Execution.

We look forward to your comments and would be most happy to address and help solve any Digital Marketing or Website Design & Development challenges you may have.

comments powered by Disqus