X-Cart forums - Custom Sales Reports by Product (item total and by qty)

X-Cart forums (https://forum.x-cart.com/index.php)

- Dev Questions (https://forum.x-cart.com/forumdisplay.php?f=20)

- - Custom Sales Reports by Product (item total and by qty) (https://forum.x-cart.com/showthread.php?t=7270)

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

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";

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.

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.

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...