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

Order totals Statistics

 
Reply
   X-Cart forums > X-Cart 4 > Dev Questions
 
Thread Tools Search this Thread
  #1  
Old 10-24-2012, 09:08 AM
 
Dougrun Dougrun is offline
 

X-Adept
  
Join Date: Apr 2012
Posts: 895
 

Thumbs up Order totals Statistics

This mod will add a section to your statistics page displaying order totals for the given time period (order status completed).
This is on 4.5.3 and 4.5.4

It modifies:
admin>statistics.php file
skin>common files>admin>main>statistics.tpl

See screenshot. Donations can be made to via paypal to slodoug at sbcglobal.net

admin>statistics.php
find the date picker code:
Code:
$date_cond = " date>='$start_date_off' AND date<='$end_date_off' ";

add this below it:
Code:
$statistics['ordersnet'] = func_query_first_cell("SELECT sum(subtotal) FROM $sql_tbl[orders] WHERE status='C'AND ($date_cond)"); $statistics['ordersfreight'] = func_query_first_cell("SELECT sum(shipping_cost) FROM $sql_tbl[orders] WHERE status='C'AND ($date_cond)"); $statistics['orderscoupdisc'] = func_query_first_cell("SELECT sum(coupon_discount) FROM $sql_tbl[orders] WHERE status='C' AND ($date_cond)"); $statistics['ordersdisc'] = func_query_first_cell("SELECT sum(discount) FROM $sql_tbl[orders] WHERE status='C' AND ($date_cond)"); $statistics['ordersgcused'] = func_query_first_cell("SELECT sum(giftcert_discount) FROM $sql_tbl[orders] WHERE status='C' AND ($date_cond)"); $statistics['ordersgross'] = func_query_first_cell("SELECT sum(total) FROM $sql_tbl[orders] WHERE status='C' AND ($date_cond)"); $statistics['orderstax'] = func_query_first_cell("SELECT sum(tax) FROM $sql_tbl[orders] WHERE status='C' AND ($date_cond)"); $statistics['orderspaysur'] = func_query_first_cell("SELECT sum(payment_surcharge) FROM $sql_tbl[orders] WHERE status='C' AND ($date_cond)");

You can modify your statistics tpl file as you like but here is mine, add this where you want the stats:
Code:
<tr> <th class="TableHead" colspan="2" align="left" height="16">Completed Order Totals: <i><font color="#000099">{$start_date|date_format:$config.Appearance.datetime_format} - {$end_date|date_format:$config.Appearance.datetime_format}</font></i></th> </tr> <tr> <td height="10" colspan="2"></td> </tr> <tr> <td valign="top" width="343" class="Text">Net Sales (Sum of Order subtotals)</td> <td valign="top" width="67" class="Text" align="right">{$statistics.ordersnet}</td> </tr> <tr> <td valign="top" width="343" class="Text">Shipping Total</td> <td valign="top" width="67" class="Text" align="right">{$statistics.ordersfreight}</td> </tr> <tr> <td valign="top" width="343" class="Text">Coupon Discounts Total</td> <td valign="top" width="67" class="Text" align="right">-{$statistics.orderscoupdisc}</td> </tr> <tr> <td valign="top" width="343" class="Text">Other Discounts Total</td> <td valign="top" width="67" class="Text" align="right">-{$statistics.ordersdisc}</td> </tr> <tr> <td valign="top" width="343" class="Text">Gift Certificates Used Total</td> <td valign="top" width="67" class="Text" align="right">-{$statistics.ordersgcused}</td> </tr> <tr> <td valign="top" width="343" class="Text">Tax Total</td> <td valign="top" width="67" class="Text" align="right">{$statistics.orderstax}</td> </tr> <tr> <td valign="top" width="343" class="Text">Payment Surcharges</td> <td valign="top" width="67" class="Text" align="right">{$statistics.orderspaysur}</td> </tr> <tr> <td valign="top" width="343" class="Text">Gross Sales</td> <td valign="top" width="67" class="Text" align="right">{$statistics.ordersgross}</td> </tr>
Attached Thumbnails
Click image for larger version

Name:	xcartstats.jpg
Views:	227
Size:	42.9 KB
ID:	3247  
__________________
4.7.x xcart store
Business 5.4xx
Reply With Quote

The following 3 users thank Dougrun for this useful post:
ADDISON (11-19-2012), Pyro (10-24-2012), stevep (11-11-2012)
  #2  
Old 11-19-2012, 12:31 PM
  Johnwiggity's Avatar 
Johnwiggity Johnwiggity is offline
 

Advanced Member
  
Join Date: Nov 2012
Posts: 48
 

Default Re: Order totals Statistics

Looks great, Can you define sales with multiple order statuses? I need Complete, and Processed to be calculated.
__________________
4.5.4

http://www.learningguitarnow.com
Reply With Quote
  #3  
Old 11-19-2012, 12:39 PM
 
Dougrun Dougrun is offline
 

X-Adept
  
Join Date: Apr 2012
Posts: 895
 

Default Re: Order totals Statistics

Maybe someone else can create a selection drop down for the other statuses, or you can add more sections, just change
WHERE status='C'
to the status code you want to show. I only need Completed to show.
__________________
4.7.x xcart store
Business 5.4xx
Reply With Quote
  #4  
Old 11-19-2012, 12:57 PM
  Johnwiggity's Avatar 
Johnwiggity Johnwiggity is offline
 

Advanced Member
  
Join Date: Nov 2012
Posts: 48
 

Default Re: Order totals Statistics

would that be like

WHERE status='C,P,N'

to define other statuses?
__________________
4.5.4

http://www.learningguitarnow.com
Reply With Quote
  #5  
Old 11-19-2012, 01:04 PM
 
Dougrun Dougrun is offline
 

X-Adept
  
Join Date: Apr 2012
Posts: 895
 

Default Re: Order totals Statistics

no, you can only define one status per query as far as I know. You would need someone to code the selection box which I don't know. Google it a bit and you should be able to make it work. Otherwise, just make a new section.
__________________
4.7.x xcart store
Business 5.4xx
Reply With Quote
  #6  
Old 11-19-2012, 03:38 PM
  Johnwiggity's Avatar 
Johnwiggity Johnwiggity is offline
 

Advanced Member
  
Join Date: Nov 2012
Posts: 48
 

Default Re: Order totals Statistics

Ok I'll check it out thanks.
__________________
4.5.4

http://www.learningguitarnow.com
Reply With Quote
  #7  
Old 11-30-2012, 02:13 PM
 
Dougrun Dougrun is offline
 

X-Adept
  
Join Date: Apr 2012
Posts: 895
 

Default Re: Order totals Statistics

fyi..
I'm trying to make a new Daily sales page that shows:
Date, total # of orders, net sales, shipping, coupons, discounts, GC, Tax, surcharges, and Gross sales.

Is there a way to Group the data by day within the date range?
__________________
4.7.x xcart store
Business 5.4xx
Reply With Quote
  #8  
Old 11-30-2012, 03:27 PM
 
Dougrun Dougrun is offline
 

X-Adept
  
Join Date: Apr 2012
Posts: 895
 

Default Re: Order totals Statistics

is there any way to modify this code:

Code:
$date_cond = " date>='$start_date_off' AND date<='$end_date_off' ";

to add a "for each day" exception???

I also see orders.php has Single day selection code, wonder if I can modify that for this purpose.
I just want it to sum the data per day FOR EACH day in the selected month, and show that date per day, one day per table row.
__________________
4.7.x xcart store
Business 5.4xx
Reply With Quote
  #9  
Old 12-01-2012, 06:50 AM
 
anandat anandat is offline
 

X-Adept
  
Join Date: Jan 2004
Posts: 914
 

Thumbs up Re: Order totals Statistics

Just put below code in phpmyadmin query window & it will display you stats for sales by products. very nice to see which are most sold products in your site.

Code:
SELECT p.productid, p.product, COUNT( od.orderid ) AS sales, FROM_UNIXTIME( MIN( o.date ) , '%Y %M %e' ) AS first_order, FROM_UNIXTIME( MAX( o.date ) , '%Y %M %e' ) AS last_order FROM xcart_products AS p LEFT JOIN xcart_order_details AS od ON p.productid = od.productid LEFT JOIN xcart_orders AS o ON o.orderid = od.orderid WHERE p.forsale = 'Y' GROUP BY od.productid ORDER BY sales DESC , p.productid DESC

credit to above code goes to Jon from webistecm http://www.websitecm.com/x-cart-tutorials/x-cart-code-snippets/determine-x-cart-product-sales-statistics/
__________________
X-Cart: 4.7.7 LIVE
Skin:Ultra by xcartmods.co.uk
X-cart Modules: | ACR, Rich Google Search, Customer Testimonials | Cloud Search, | Websitecm: CDSEO (2.1.9)
---------------
Server: Linux
php: 5.3
mysql: 5.0.89
----------------
Reply With Quote
  #10  
Old 12-03-2012, 09:16 AM
 
Dougrun Dougrun is offline
 

X-Adept
  
Join Date: Apr 2012
Posts: 895
 

Default Re: Order totals Statistics

thanks but thats not what i'm looking for.
__________________
4.7.x xcart store
Business 5.4xx
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 07:48 AM.

   

 
X-Cart forums © 2001-2020