Magento querying sales or order volume

Question

I am looking for a way to be able to get approx values of sales volume (basically how much money a store is generating) upon which I would like to base my rough calculations to show user some more data.

(Option A) I know I can directly query the database and then cache the results, but is Magento already doing that & caching it somewhere which I can just use? (Option B), like Magento saves the value of lifetime sales and average sales value somewhere.

Any data like daily/weekly/monthly average of sales or average order amount & daily/weekly/monthly average of orders would suffice here.

If the option A is only way to go here, how should I be querying this data? I have seen examples doing it like the following:

• Mage::getResourceModel('sales/order_collection')
• Mage::getModel('sales/order')->getCollection()

Which is faster & more lightweight for my needs? And any link where can I actually see detailed or well explained example of using them so that I can understand what parameters are available for querying in each case?

---

Update: I still don't understand clearly whats the difference between the above 2 methods, but my guess is one is more abstracted than the other & probably uses the other one internally but I am not sure. Anyways, I have this code snippet pulling in the data but I have a problem with filtering the data for a date range:

<?php

require_once 'app/Mage.php';

#umask(0);

Mage::app('default');

$orderTotals = Mage::getModel( 'sales/order' )->getCollection()
->addAttributeToFilter( 'status', Mage_Sales_Model_Order::STATE_COMPLETE )
->addAttributeToFilter( 'status', 'complete' )
//->addAttributeToFilter( 'created_at', array( 'from'  => date( 'Y-m-d', strtotime( '-100 days' ) ) ) )
//->addAttributeToFilter( 'created_at', array( 'from' => date( 'Y-m-d', strtotime( '-100 days' ) ), 'to' => date( 'Y-m-d' ) ) )
->addAttributeToSelect( 'grand_total' )
->getColumnValues( 'grand_total' )
;

$totalSum = array_sum( $orderTotals );
$totalSum = Mage::helper( 'core' )->currency( $totalSum, true, false );

echo $totalSum . "\n";

---

Update: This code snippet is working now. I had orders in the range but their order status was "processing", so I couldn't see them. I got the snippet from this question.

I am still looking for an explanation to what differs in the above 2 methods and which one is good to use?

Answer 1

In case someone comes here via Google like I did, this question motivated me to make my report page I've been wanting. I typically do not use the built in MVC (more below!) and instead will plop pages in var/export or something. I eventually came up with the below query after going through all the sales_flat_order* and sales_flat_invoice* tables finding a solution which represented the products we sold and shipped.

Sales report for this week:

SELECT sfi.grand_total as 'total',sfii.base_row_total as 'sales', sfii.sku as 'sku', sfii.qty as 'qty', sfo.increment_id as 'order' 
FROM sales_flat_invoice_item sfii
LEFT JOIN sales_flat_invoice sfi ON sfii.parent_id = sfi.entity_id
LEFT JOIN sales_flat_order sfo ON sfi.order_id = sfo.entity_id
WHERE sfo.status = 'complete'
AND sfi.updated_at > "2013-06-01"

The date is this past Friday night at midnight. Note: if you add sfi.* to the SELECT you can do the math to double check shipping and taxes against the totals. The problem with sales_flat_order is that table doesn't represent cash in your checking account. We're not on accrual (and probably few small businesses are), so this is better for me understanding cash accounting basis.

For my report script, I also build a table of values and use Google Charts to make it pretty. The below page loads almost instantly:

Edited to add: While creating this above query, I discovered that a previous order querying tool built USING the proper means of collections, etc, misses a vast number of orders without any reason we could uncover. More and more, I'm finding the only way to get a job done correctly is to do it myself -- and do it the hard way.

Answer 2

Did you take a look @ Admin -> Report -> Sales

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

/app/code/core/Mage/Adminhtml/Block/Report/Sales/Sales/Grid.php

Collection: 'sales/report_order_collection'

Tables:

sales_bestsellers_aggregated_*

sales_order_aggregated_*

report_viewed_product_*