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

Page 3 of 10

Last »

Show 40 posts from this thread on one page

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)

jeremye

05-03-2004 01:40 PM

Quote:

Originally Posted by deniz

I am using 3.3.3 and the second sql didnot work. any help idea?

Exactly what SQL query are you using, and what error is it giving you?

deniz

05-03-2004 01:50 PM

Code:

$sql = "SELECT tblproducts.product, 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.productid=tbldetails.productid AND tbldetails.orderid=tblorders.orderid AND tblorders.date >= $start_date AND tblorders.date <= $end_date AND tblorders.status = 'C' OR tblorders.status = 'P' GROUP BY tbldetails.productid ORDER BY $report_type desc";

I am using this sql query , the page not finishing when I run this.

But in the first example the page works fine , I only need to eliminate status C and P position in sql query.

jeremye

05-03-2004 01:54 PM

You are ok, it's no biggie. I think the reason is because the database structure is different in your version. If you want, look in your orders table and find the column that lists C, P, Q, etc. It may be named different, but I don't have your version so I can't check.

Let me know if you need more assistance.

deniz

05-03-2004 02:20 PM

its writen same 'status'

jeremye

05-03-2004 02:34 PM

Quote:

Originally Posted by deniz

its writen same 'status'

You can just ignore the status == C and status == P part of the query. This was not in the original code, someone else suggested this, so you will be fine without it in there.

Jeremy

deniz

05-04-2004 02:13 AM

But I need to use P and C filter

jeremye

05-04-2004 02:37 AM

Can anyone using 3.3.3 check the database structure for the status of orders? Is the table/field name xcart_orders.status? Have the abbreviations changed (are they different from C,P,Q,D,F,etc)?

As I do not have 3.3.3 I am stuck here. Any help?

Jeremy

xcell67

05-13-2004 02:25 AM

Quote:

Notice that I added two statements:
Code:
AND tblorders.status = 'C' OR tblorders.status = 'P'

Hi,
People seem to be having trouble implementing this, I just wanted to let you know that it works perfect for me so you wouldn't feel that the script only worked on your server. Couple of comments though, the above code I mentioned above does not work, it will cause the script to only list one product and credit it with insane sales of 99 in qty and 300,000 in total. The right code is:

Code:

AND tblorders.status = 'C' OR 'P'

I don't think it would be that difficult to implement this into xcart's admin area, you just have to take out the non php related material and save that as the tpl file, create a link in the menu, and link it in the home.tpl area. I'm pretty sure someone will crack it.

Thanks for the mod, its excellent!

jeremye

05-13-2004 02:45 AM

Thanks for the SQL update. I appreciate it. Glad it's working on your site...

shirtshop

05-14-2004 01:06 AM

When i try to run it i get this error message:

Report Range: .. through ..
Product Item Total Quantity
Error in query.You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc' at line 1

The year are falling back to 0000 and there's no other year to choose.

All times are GMT -8. The time now is 07:55 PM.

Page 3 of 10

Last »

Show 40 posts from this thread on one page