2

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.

  • If you run a query on the DB do you get the same result as your custom report or Magento report? What's the timezone on the DB, on the Admin machine and the one actually configured in the Admin, System -> Configuration -> General -> Locale Options -> Timezone? Also, is count($collection) different than $collection->getSize()? – Adi Aug 2 '15 at 11:48
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!

1

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.

  • good afternoon. I was not able to figured this out yet. I'm aware of your suggestion, but still was not able to fine out why I have miss match between my custom report and magento's total ordered report. I'm more interested in detailed report thats why I'm not using the aggregated table to generate my report. – Aboodred1 Jan 9 '15 at 17:48

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.