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

jeremye · #41 10-20-2005, 12:30 PM

It very well could be that you are using v4.x. I have only been able to test this on 3.4.x. Maybe other users that have run this script on 4.x can give some advice?

whsu716 · #42 10-20-2005, 12:44 PM

Hey Jeremy

I just got it to work!

I took out the first four lines (the logging in part)...what negative effects would this have on my site? Would hackers be able to get through via this channel?

Thanks!

jeremye · #43 10-20-2005, 12:47 PM

First, they would have to know the exactly name of the reports file. You can check this by logging out of your admin area and open up a new browser window and type in the reports URL directly and see if the reports come up. If they do, the report is not secure.

creative xpress · #44 02-14-2006, 04:27 PM

I have found that if you remove the first two require lines from this page it will then work with the 4.XX branch.

Hope this helps.

creative xpress · #45 02-14-2006, 04:30 PM

I have found that if you remove the first two require lines from this page it will then work with the 4.XX branch and it still requires auth to use it.

Hope this helps.

creative xpress · #46 02-14-2006, 05:14 PM

Quick Question. I have modified this form to include the ability to specify the sku number that I want to look at. Can anyone think of an easy way to specify a range of sku's?

For example instead of typing 38-9005 I could type 38-* or a range like 38-9000 - 38-9999.

This would be a big help!

Thanks.

jeremye · #47 02-14-2006, 05:24 PM

Quote:

Originally Posted by creative xpress

I have found that if you remove the first two require lines from this page it will then work with the 4.XX branch and it still requires auth to use it.

Thanks for the update. I'm sure other users will appreciate that.

jeremye · #48 02-14-2006, 05:29 PM

Hey, regarding your SKU question, that should be easy enough.

Modify the main SQL query to something like :

Code:

SQL QUERY...... WHERE productcode LIKE '38-%'

That will specify all products that start with "38-" (the % is wildcard).

I'm not sure the range query will always work, but you could try:

Code:

SQL QUERY...... WHERE productcode >= '38-9000' AND productcode <= '38-9999'

Hope that helps.

creative xpress · #49 02-15-2006, 08:05 AM

Ok here is the query that I have but it does not seem to work.

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%' OR tblproducts.productcode='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";

I am not experienced with the LIKE portion of this query so I am sure that I am doing it wrong. if I change this

Code:

tblproducts.productcode LIKE '$sku%' OR tblproducts.productcode='$sku'

to look like this

Code:

 tblproducts.productcode LIKE '38-%' OR tblproducts.productcode='$sku'

It will work. But I do not want to hard code the 38- portion because we have a large number of sku's that start with a range of numbers. Where did I go wrong?

Thanks!
Cameron

creative xpress · #50 02-15-2006, 08:06 AM

Ok here is the query that I have but it does not seem to work.

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%' OR tblproducts.productcode='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";

I am not experienced with the LIKE portion of this query so I am sure that I am doing it wrong. if I change this

Code:

tblproducts.productcode LIKE '$sku%' OR tblproducts.productcode='$sku'

to look like this

Code:

 tblproducts.productcode LIKE '38-%'

It will work. But I do not want to hard code the 38- portion because we have a large number of sku's that start with a range of numbers. Where did I go wrong?

Thanks!
Cameron