Follow us on Twitter X-Cart on Facebook Wiki
Shopping cart software Solutions for online shops and malls

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

 
Reply
   X-Cart forums > X-Cart 4 > Dev Questions
 
Thread Tools Search this Thread
  #21  
Old 05-03-2004, 01:40 PM
 
jeremye jeremye is offline
 

Senior Member
  
Join Date: Apr 2004
Location: Philadelphia, PA
Posts: 158
 

Default

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?
__________________
Jeremy
X-Cart Gold v3.4.14 [Linux/Apache]
Heavily Modded
Reply With Quote
  #22  
Old 05-03-2004, 01:50 PM
 
deniz deniz is offline
 

Advanced Member
  
Join Date: Sep 2003
Posts: 70
 

Default

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.
Reply With Quote
  #23  
Old 05-03-2004, 01:54 PM
 
jeremye jeremye is offline
 

Senior Member
  
Join Date: Apr 2004
Location: Philadelphia, PA
Posts: 158
 

Default

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.
__________________
Jeremy
X-Cart Gold v3.4.14 [Linux/Apache]
Heavily Modded
Reply With Quote
  #24  
Old 05-03-2004, 02:20 PM
 
deniz deniz is offline
 

Advanced Member
  
Join Date: Sep 2003
Posts: 70
 

Default

its writen same 'status'
Reply With Quote
  #25  
Old 05-03-2004, 02:34 PM
 
jeremye jeremye is offline
 

Senior Member
  
Join Date: Apr 2004
Location: Philadelphia, PA
Posts: 158
 

Default

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
__________________
Jeremy
X-Cart Gold v3.4.14 [Linux/Apache]
Heavily Modded
Reply With Quote
  #26  
Old 05-04-2004, 02:13 AM
 
deniz deniz is offline
 

Advanced Member
  
Join Date: Sep 2003
Posts: 70
 

Default

But I need to use P and C filter
Reply With Quote
  #27  
Old 05-04-2004, 02:37 AM
 
jeremye jeremye is offline
 

Senior Member
  
Join Date: Apr 2004
Location: Philadelphia, PA
Posts: 158
 

Default

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
__________________
Jeremy
X-Cart Gold v3.4.14 [Linux/Apache]
Heavily Modded
Reply With Quote
  #28  
Old 05-13-2004, 02:25 AM
 
xcell67 xcell67 is offline
 

Senior Member
  
Join Date: Dec 2003
Posts: 149
 

Default

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!
Reply With Quote
  #29  
Old 05-13-2004, 02:45 AM
 
jeremye jeremye is offline
 

Senior Member
  
Join Date: Apr 2004
Location: Philadelphia, PA
Posts: 158
 

Default

Thanks for the SQL update. I appreciate it. Glad it's working on your site...
__________________
Jeremy
X-Cart Gold v3.4.14 [Linux/Apache]
Heavily Modded
Reply With Quote
  #30  
Old 05-14-2004, 01:06 AM
 
shirtshop shirtshop is offline
 

Senior Member
  
Join Date: Nov 2002
Posts: 127
 

Default

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.
Reply With Quote
Reply
   X-Cart forums > X-Cart 4 > Dev Questions


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -8. The time now is 04:30 AM.

   

 
X-Cart forums © 2001-2020