Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 2 of 3
How do I run a comprehensive sales report? 
 
Hombrone
Jr. Member
 
Total Posts:  15
Joined:  2008-04-02
 

Yes, because it’s a first step to create a new client… not only for report but also for other features. We are working on that project.

 
Magento Community Magento Community
Magento Community
Magento Community
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

Hombrone, quick question. Are you suggesting that only people who share the costs with you will benefit from your new features?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Hombrone
Jr. Member
 
Total Posts:  15
Joined:  2008-04-02
 

Honestly, yes. smile because we are still in development. And I’m paying programmers for that. It’s just an idea. Until I can find some programmers who work for free on my project. smile
There’s nothing bad in it. I saw many companies do the same.

 
Magento Community Magento Community
Magento Community
Magento Community
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

Well the idea of open source is that your programmers ARE free.  Everyone here should be pitching in with ideas and contributing to the code for such features. Its a difference of opinion obviously, but considering you need these features for your business anyway, a purist would argue that you are being forced to invest regardless - and that your with-holding of the fruits of this labour is not really adding anything to your business.

I think you’ll find more people open to the idea if you are transparent about this - if you cant afford to go alone, and financially need some assistance in a joint effort etc… then more people would (I imagine) be willing to particpate if the source was going back into the community. You will get people sitting waiting around for the freebie obviously too, but those who cant wait and have a little spare $$ will be open to taking the hit.  The idea is that gaining respect and appreciation makes it worth while.  I personally dont have much respect for closed-circuit capitalism in open source projects mate, sorry.

Just so you know, when/if I make my SQL query to pull active order CSV info from the database, I’ll be posting it back here.

 
Magento Community Magento Community
Magento Community
Magento Community
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

SOLUTION :

The attached file (unprocessed-orders.php) allows you to run a comprehensive report on your store, outputting a valid CSV file containing all the “unprocessed” orders which have yet to be shipped.  This report creates a breakdown of individual products to be shipped to customers and where they should be sent, including SKU codes.

To use…

Simply open the file in a text editor, update the database connection variables to match yours, put the file somewhere on your server (file security is up to you), and hit the script with your browser.  i.e. http://www.someserver.com/securefolder/unprocessed-orders.php.

It will pull the info from your databse and prompt you to download in CSV format.  This is especially handy for businesses with dispatch departments or third-party logistics teams such as ours.

Further Notes / Instructions

o.  When items are shipped (via the admin panel) the script will automatically reflect these changes.  Items will either decrease or the entire order will clear depending if you do a full or partial shipment.  (both work just as well)

o.  Run this script BEFORE you ship your items.  To clarify though - this script ONLY READS the information in your databse, it doesnt update or change anything.

o.  Currently it runs across all stores/websites but this could also be added as a condition if you need to narrow this.

o.  Could be extended/modified to just tally up the total of orders or create more general sales report (ask me)

o.  Pulls the full simple product SKU from the database not the configurable sku. 

o.  Currently country codes are just compared to a static list i have coded in the script (which you can update), waiting to hear how i get the country name from the database from the two letter code.  i.e. GB = United Kingdom

Good luck.

File Attachments
unproccessed-orders.php  (File Size: 6KB - Downloads: 365)
sample.zip  (File Size: 1KB - Downloads: 249)
 
Magento Community Magento Community
Magento Community
Magento Community
 
Brady
Guru
 
Avatar
Total Posts:  330
Joined:  2007-09-10
Orange County, CA
 

This is nearly PERFECT! The only thing that we’d need would be a total dollar amount for the order. Is this possible to run a report after the product ships? We need to run end-of-month sales reports.

Thanks!
Brady

IrishC - 04 October 2008 03:17 AM

SOLUTION :

The attached file (unprocessed-orders.php) allows you to run a comprehensive report on your store, outputting a valid CSV file containing all the “unprocessed” orders which have yet to be shipped.  This report creates a breakdown of individual products to be shipped to customers and where they should be sent, including SKU codes.

To use…

Simply open the file in a text editor, update the database connection variables to match yours, put the file somewhere on your server (file security is up to you), and hit the script with your browser.  i.e. http://www.someserver.com/securefolder/unprocessed-orders.php.

It will pull the info from your databse and prompt you to download in CSV format.  This is especially handy for businesses with dispatch departments or third-party logistics teams such as ours.

Further Notes / Instructions

o.  When items are shipped (via the admin panel) the script will automatically reflect these changes.  Items will either decrease or the entire order will clear depending if you do a full or partial shipment.  (both work just as well)

o.  Run this script BEFORE you ship your items.  To clarify though - this script ONLY READS the information in your databse, it doesnt update or change anything.

o.  Currently it runs across all stores/websites but this could also be added as a condition if you need to narrow this.

o.  Could be extended/modified to just tally up the total of orders or create more general sales report (ask me)

o.  Pulls the full simple product SKU from the database not the configurable sku. 

o.  Currently country codes are just compared to a static list i have coded in the script (which you can update), waiting to hear how i get the country name from the database from the two letter code.  i.e. GB = United Kingdom

Good luck.

 
Magento Community Magento Community
Magento Community
Magento Community
 
JoaoMagento
Jr. Member
 
Total Posts:  16
Joined:  2008-10-23
 
Brady - 10 October 2008 10:09 AM

This is nearly PERFECT! The only thing that we’d need would be a total dollar amount for the order. Is this possible to run a report after the product ships? We need to run end-of-month sales reports.

Thanks!
Brady

IrishC - 04 October 2008 03:17 AM
SOLUTION :

The attached file (unprocessed-orders.php) allows you to run a comprehensive report on your store, outputting a valid CSV file containing all the “unprocessed” orders ......

Hi there, I’ve tryied your script… it works just as you say… but it has a little bug…

If the admin cancels the order, it still comes on the csv…

so i hadded this rule at the end of the query:

<? ...
INNER JOIN sales_flat_quote_address
ON sales_flat_quote_item.quote_id = sales_flat_quote_address.quote_id
WHERE sales_flat_order_item.order_id = $orderid

// Cancelled orders check by JoaoMagento
AND sales_flat_order_item.qty_canceled = 0
// End of cancelling check

AND sales_flat_quote_address.address_type = 'shipping'
AND sales_flat_order_item.product_type <> 'configurable'
AND sales_flat_order_item.qty_shipped < sales_flat_order_item.qty_ordered";
.... ?>
thanks Joao

 
Magento Community Magento Community
Magento Community
Magento Community
 
joyously
Guru
 
Total Posts:  447
Joined:  2008-08-21
 
boom - 22 September 2008 04:12 PM

This cuts off at 5 products. Is there a way to make the Bestseller feature show more than 5 products?

I think it would be in app/code/core/Mage/Adminhtml/Block/Report/Product/Ordered.php. I added this line to the top of the __construct function

$this->setSubReportSize(false);
It didn’t bomb on me when I tried it, but I just got through working on the “delete all my test orders” problems, so there is nothing to show!  grin
 
Magento Community Magento Community
Magento Community
Magento Community
 
TPB
Jr. Member
 
Total Posts:  13
Joined:  2008-12-09
 

The MySQL query provided does not work for orders with multiple shipping addresses.  I’m struggling through the various tables trying to figure this out.  I did get it to work for items with multiple shipping addresses replacing all the INNER JOINS with the code below, BUT this code does not work for orders with single shipping addresses.

LEFT JOIN sales_flat_quote_item
ON sales_flat_order_item.quote_item_id = sales_flat_quote_item.item_id
LEFT JOIN sales_flat_quote_address_item
ON sales_flat_order_item.quote_item_id = sales_flat_quote_address_item.address_item_id
LEFT JOIN sales_flat_quote_address
ON sales_flat_quote_address_item.quote_address_id = sales_flat_quote_address.address_id
INNER JOIN catalog_product_entity_varchar
ON (sales_flat_order_item.product_id = catalog_product_entity_varchar.entity_id AND
catalog_product_entity_varchar.attribute_id = ‘457’)

If anyone has a solution to this, please let me know.  Otherwise, I’ll return to the query in the morning to see if I can work through it.

 
Magento Community Magento Community
Magento Community
Magento Community
 
TPB
Jr. Member
 
Total Posts:  13
Joined:  2008-12-09
 

We’ve all be to that “pulling your hair out” stage, and I’m visiting that space once again.

The goal is to get a report that can be sent to the shipper.  The report needs to show what was ordered and who it is being shipped to.  Sounds simple enough.

The query submitted by IrishC and amended by JoaoMagento works great for orders that are shipped to one address.  However, the query does not work for orders sent to multiple addresses.  Last night, I fixed that query so that it works for orders being sent to multiple addresses, only to realize my new query does not work for orders sent to single addresses.

So, today I’m trying to come up with a single query that can do both ... and I’m failing.  When orders are going to mutliple addresses, Magento records them as separate orders.  So, an order going to four addresses is recorded as four orders.

One of the issues I’m running into is here:

INNER JOIN sales_flat_quote_item
ON sales_flat_order_item.quote_item_id = sales_flat_quote_item.item_id

Although there are 4 orders, only 2 for some reason have a quote_item_id that shows up in the sales_flat_quote_item table.  The other 2 just aren’t there.  Why 2 and not 4?  Or 1?  The result, however, is that the IrishC / JoaoMagento query shows only these 2 items, but it shows each four times--one for each shipping address.

My modification to the code was to JOIN sales_flat_quote_address_item ON sales_flat_order_item.quote_item_id = sales_flat_quote_address_item.address_item_id.  However, there’s a record in this table only if we have a multi-ship order.

I have a script that runs automatically every day at 2:00 EST (New York time).  I’m trying to get this resolved before 2:00 today so that it will run correctly.

Can anyone help?

Thanks.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brady
Guru
 
Avatar
Total Posts:  330
Joined:  2007-09-10
Orange County, CA
 

I’ve tested IrishC’s script with v.1.2.0.1 and it works great.

I still would like to find a way to get this script to work with all ordered, not just new orders. Or ultimately all orders within a date range. Any ideas how to do that?

 
Magento Community Magento Community
Magento Community
Magento Community
 
chll
Sr. Member
 
Total Posts:  134
Joined:  2008-12-17
 
IrishC - 04 October 2008 03:17 AM

SOLUTION :

The attached file (unprocessed-orders.php) allows you to run a comprehensive report on your store, outputting a valid CSV file containing all the “unprocessed” orders which have yet to be shipped.  This report creates a breakdown of individual products to be shipped to customers and where they should be sent, including SKU codes.

To use…

Simply open the file in a text editor, update the database connection variables to match yours, put the file somewhere on your server (file security is up to you), and hit the script with your browser.  i.e. http://www.someserver.com/securefolder/unprocessed-orders.php.

It will pull the info from your databse and prompt you to download in CSV format.  This is especially handy for businesses with dispatch departments or third-party logistics teams such as ours.

Further Notes / Instructions

o.  When items are shipped (via the admin panel) the script will automatically reflect these changes.  Items will either decrease or the entire order will clear depending if you do a full or partial shipment.  (both work just as well)

o.  Run this script BEFORE you ship your items.  To clarify though - this script ONLY READS the information in your databse, it doesnt update or change anything.

o.  Currently it runs across all stores/websites but this could also be added as a condition if you need to narrow this.

o.  Could be extended/modified to just tally up the total of orders or create more general sales report (ask me)

o.  Pulls the full simple product SKU from the database not the configurable sku. 

o.  Currently country codes are just compared to a static list i have coded in the script (which you can update), waiting to hear how i get the country name from the database from the two letter code.  i.e. GB = United Kingdom

Good luck.

IrishC

Thank you for your contribution, I do think this function should be included in every Magento installation, everyone need it

 
Magento Community Magento Community
Magento Community
Magento Community
 
maugan
Member
 
Avatar
Total Posts:  31
Joined:  2008-09-05
London
 

I don’t know if anyone who needs the orders report has seen this post, about using the magento api to do the work instead of the joys of sql

http://www.westwideweb.com/wp/2008/12/10/magento-export-orders/

and the paid for version
http://www.sebastian-enzinger.de/magento-order-export-module/

I haven’t quite got my version to work yet but I think thats more to do with my php programming.

Maugan

 
Magento Community Magento Community
Magento Community
Magento Community
 
dradcliffe
Jr. Member
 
Avatar
Total Posts:  5
Joined:  2008-06-12
Indianapolis, IN
 
joyously - 17 November 2008 10:15 PM

boom - 22 September 2008 04:12 PM
This cuts off at 5 products. Is there a way to make the Bestseller feature show more than 5 products?

I think it would be in app/code/core/Mage/Adminhtml/Block/Report/Product/Ordered.php. I added this line to the top of the __construct function

$this->setSubReportSize(false);
It didn’t bomb on me when I tried it, but I just got through working on the “delete all my test orders” problems, so there is nothing to show!  grin

I tried this and it didn’t seem to work for me until i put in a number. Try this file:
app/code/core/Mage/Adminhtml/Block/Report/Grid.php
at the end of the __construct function

$this->setSubReportSize(999);

This worked for me.  I created a custom module that sets a custom config value and then I get that value and use it to set the number of results for this report.

$this->setSubReportSize(Mage::getStoreConfig('catalog/backend/bestseller_report_size'));

PM me if you want that code.

 
Magento Community Magento Community
Magento Community
Magento Community
 
tayto
Jr. Member
 
Avatar
Total Posts:  4
Joined:  2008-10-10
 
Brady - 05 January 2009 04:51 PM

I’ve tested IrishC’s script with v.1.2.0.1 and it works great.

I still would like to find a way to get this script to work with all ordered, not just new orders. Or ultimately all orders within a date range. Any ideas how to do that?

That’s exactly what i need, my client needs to be able to track which are the best selling items on his site - But we need to colour information as well as Size and product, the current bestseller list seems to show the Configurable Product, I need a list of Simple Products.

Anyone any luck with this at all?

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 2 of 3