Custom sales order report NOT matching Magento's sales order report

Question

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.

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

Answer 1

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!

Answer 2

Good Morning,

I am not sure if you ever figured this out or not but if you take a look in The Mage_Sales_Model_Resource_Report_Order_Collection class the __construct method is using the sales_order_aggregated_created table as well as the resource model Mage_Sales_Model_Resource_Report_Order.

I have not created a custom report for this type before but came across these while researching something else.

Hope it helps.