Magento – Custom sales order report NOT matching Magento’s sales order report

collection-filteringreportssales-order

I'm building a custom report sales order using the following code as a start. I'm trying to get all orders that have been updated Yesterday, by passing filters from date and to date to sales/order collection.

$date = Mage::getSingleton('core/date');
$formatDate = 'Y-m-d H:i:s';
$from_date = $date->gmtDate($formatDate, 'Yesterday 12:00:00AM');
$to_date = $date->gmtDate($formatDate, 'Yesterday 11:59:59PM');

$updated_order_collection = Mage::getModel('sales/order')->getCollection()
    ->addAttributeToFilter('updated_at', array(
    'from' => $from_date,
    'to' => $to_date,
    'date' => true,
))->load();

$updated_count = count($updated_order_collection); // Total order is not matching Magento's sales order report as showon in the screenshot.

I was not able to get the same result as Magento's sales order report. The custom code total orders are less than Magento's total orders. See picture below for more details.

enter image description here

So I'm wondering what I'm missing to get an incorrect total results.

Best Answer

I believe you're not getting the same number of results returned because you're adjusting your time ($from_date, $to_date) to GMT.

As far as I can tell, Magento does not adjust the "from" and "to" dates to GMT when it queries the database for the reports. It really should, but sometimes Magento just lacks consistency. See Mage_Adminhtml_Block_Report_Grid_Abstract. I've inspected the collection's query, and it literally uses the inputs given.

i.e. WHERE (period >= '2014-02-01') AND (period <= '2016-03-26') (period is constructed somewhere in the process, but dates are not converted to GMT).

Correct me I'm wrong, of course!

Related Topic