Custom Sales Reports by Product (item total and by qty)

creative xpress · #51 02-15-2006, 08:22 AM

I have resolved it. I was just being really stupid. here is my query now

Code:

 $sql = "SELECT tblproducts.product, tblproducts.productcode, tbldetails.productid, SUM(tbldetails.price*tbldetails.amount) AS total, SUM(tbldetails.amount) AS qty_purchased FROM xcart_order_details AS tbldetails, xcart_products AS tblproducts, xcart_orders AS tblorders WHERE tblproducts.productcode LIKE'$sku%' AND tblproducts.productid=tbldetails.productid AND tbldetails.orderid=tblorders.orderid AND tblorders.date >= $start_date AND tblorders.date <= $end_date AND tblorders.status != 'I' AND tblorders.status != 'D' AND tblorders.status != 'F' AND tblorders.status != 'Q' AND tblorders.status != 'B' GROUP BY tbldetails.productid ORDER BY $report_type desc";

Thanks for your help it works like a charm!
Cameron

jeremye · #52 02-15-2006, 09:03 AM

Try something like this:

Code:

$sql = "SELECT tblproducts.product, tblproducts.productcode, tbldetails.productid, SUM(tbldetails.price*tbldetails.amount) AS total, SUM(tbldetails.amount) AS qty_purchased FROM xcart_order_details AS tbldetails, xcart_products AS tblproducts, xcart_orders AS tblorders WHERE tblproducts.productcode LIKE '" . $partial_sku . "%' AND tblproducts.productid=tbldetails.productid AND tbldetails.orderid=tblorders.orderid AND tblorders.date >= $start_date AND tblorders.date <= $end_date AND tblorders.status != 'I' AND tblorders.status != 'D' AND tblorders.status != 'F' AND tblorders.status != 'Q' AND tblorders.status != 'B' GROUP BY tbldetails.productid ORDER BY $report_type desc";

OR:

Code:

$sql = "SELECT tblproducts.product, tblproducts.productcode, tbldetails.productid, SUM(tbldetails.price*tbldetails.amount) AS total, SUM(tbldetails.amount) AS qty_purchased FROM xcart_order_details AS tbldetails, xcart_products AS tblproducts, xcart_orders AS tblorders WHERE tblproducts.productcode = '" . $exact_sku . "' AND tblproducts.productid=tbldetails.productid AND tbldetails.orderid=tblorders.orderid AND tblorders.date >= $start_date AND tblorders.date <= $end_date AND tblorders.status != 'I' AND tblorders.status != 'D' AND tblorders.status != 'F' AND tblorders.status != 'Q' AND tblorders.status != 'B' GROUP BY tbldetails.productid ORDER BY $report_type desc";

TJ Stephens · #53 02-22-2006, 09:45 AM

Thank a lot for this mod. It works great. However, I am trying to modify it so that it only shows sales for a selected manufacturer. I would like to have a drop-down list of all the manufacturers and then limit the query/report to include only the products of the selected manufacturer.

Can anybody point me in the right direction on how to do a drop down list for the manufacturers and then take that selection and include it in the query. I'd appreciate any help.

Thanks a lot.

T.J.

2coolbaby · #54 02-28-2006, 07:27 AM

All I get is a blank page. I have tried everything I can think of. I tried the code as given.

I removed the first 2 required lines. I added the php to the <?php as the rest of my php files have. I changed the requireds to the same thing listed in my store admin php files:

require "./auth.php";
require $xcart_dir."/include/security.php";

I have changed permissions from 644 to 755 and back. Nothing will allow this to show anything. Just a blank page.

I am using 4.0.17. As of now no biggee. But soon I will be going into boxed sets which will have high shipping charges. I pay percentage royalties to writers and artists so I will not want the shipping charges included.

jeremye · #55 02-28-2006, 09:04 AM

Mary Lee, sorry for the trouble you are having. Unfortunately I don't have a copy of the 4.x branch to play around with to test. Hopefully there are some other users who got it working in the 4.x branch to help you out...

mjfontec · #56 08-01-2007, 07:23 PM

Fantastic mod, thank you!!

Just wondering, is it possible to add a category name into this (or product variants)? I have a book available as paperback, hardcover, and ebook all with the same title, so they show up as the same product in the report. Tried the SKU, but could not get the sql query to work.

Thanks
Mike

smrtsu · #57 11-26-2007, 12:59 PM

I'm having difficulty connecting to my db using this mod.
I keep getting a connection string error. I'm running 4.0.19

stewbiedoobiedoo · #58 12-04-2007, 02:54 PM

I tested this script in 4.1.9 and it works great. I contacted x-cart and they wanted $290 to make this happen for us. The only changes I had to make was to comment out the first 4 lines (as mentioned above) change the year drop down menu (change 2003 to 2006 and 2006 to whichever year you want for future).

Works like a charm. Thank you so much!

ScrapOrchard · #59 05-01-2008, 07:12 PM

This is fantastic! I wanted to know if anyone could help me figure out how to a) show the manufacturer b) show discounted/coupon totals (i.e. right now, we have a discounted product and the report is showing the total sales without taking into account the discount), c) subtract the discounted total from the total and d) be able to export is as a csv file?

Keys Care · #60 06-23-2008, 06:12 AM

Marvelous Tool!!!

I am wondering if there is an easy way to have the report sort by sku as an option.

I am also wondering if there is a way to search during a given period for shipping that is paid by the customers.

Thanks for the tool!

Regards,

Bob