X-Cart: shopping cart software
Page 1 of 2 1 2 >
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)
-   -   Order totals Statistics (https://forum.x-cart.com/showthread.php?t=65186)

Dougrun 10-24-2012 09:08 AM
Order totals Statistics
 
1 Attachment(s)
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>

Johnwiggity 11-19-2012 12:31 PM
Re: Order totals Statistics
 
Looks great, Can you define sales with multiple order statuses? I need Complete, and Processed to be calculated.

Dougrun 11-19-2012 12:39 PM
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.

Johnwiggity 11-19-2012 12:57 PM
Re: Order totals Statistics
 
would that be like

WHERE status='C,P,N'

to define other statuses?

Dougrun 11-19-2012 01:04 PM
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.

Johnwiggity 11-19-2012 03:38 PM
Re: Order totals Statistics
 
Ok I'll check it out thanks.

Dougrun 11-30-2012 02:13 PM
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?

Dougrun 11-30-2012 03:27 PM
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.

anandat 12-01-2012 06:50 AM
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/

Dougrun 12-03-2012 09:16 AM
Re: Order totals Statistics
 
thanks but thats not what i'm looking for.


All times are GMT -8. The time now is 10:07 AM.
Page 1 of 2 1 2 >
Show 40 posts from this thread on one page

Powered by vBulletin Version 3.5.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.