Jump to content
Sign in to follow this  
rhgraphicdesign

Admin Store Summary

Recommended Posts

If you need any help with the Admin Store Summary contribution or have any questions please post them here. You can post comments about the module here Robert Heath Development

 

Finally found the support thread for this!

 

I'm trying to add a few extra stats onto this, suce as previous months amount in the total sales, that kind of thing.

 

So Essentially I think I need to tweak this code to make a new variable for "last Months Sales" for example.

 

$sales_query_raw = "select ot.value
							   from " . TABLE_ORDERS . " o, 
							   " . TABLE_ORDERS_TOTAL . " ot 
							   where o.orders_id = ot.orders_id  
							   and ot.class = 'ot_subtotal' 
							   and o.date_purchased >= '" . date("Y-m") . "-01'  
							   and o.date_purchased <= '" . date("Y-m") . "-31' 
";

$sales_query = tep_db_query($sales_query_raw);
while ($sales = tep_db_fetch_array($sales_query)) {
	$monthlyTotal = $monthlyTotal + $sales['value'];
}

 

Or More specifically this bit..

and o.date_purchased >= '" . date("Y-m") . "-01'  
and o.date_purchased <= '" . date("Y-m") . "-31'

 

I've tried loads of different permutations, and just can't get it to do last month! (Or Last year for that matter!)

 

Does anyone who actually knows how SQL Queries work got any ideas?

 

Cheers

 

Mat

Share this post


Link to post
Share on other sites
Anyone have any idea on this? Would be really handy to know!

 

Thanks

 

Mat

$sales_query_raw = "select ot.value
							   from " . TABLE_ORDERS . " o,
							   " . TABLE_ORDERS_TOTAL . " ot
							   where o.orders_id = ot.orders_id and ot.class = 'ot_subtotal' and o.date_purchased >= date_add(curdate(), interval -1 month)";

Edited by Jonojamesmac

Share this post


Link to post
Share on other sites

in fact the whole thing is here

 

$sales_query_raw = "select SUM(ot.value)
							   from " . TABLE_ORDERS . " o,
							   " . TABLE_ORDERS_TOTAL . " ot
							   where o.orders_id = ot.orders_id and ot.class = 'ot_shipping' 
								 and o.date_purchased >= date_add(curdate(), interval -1 month)";

$sales_query = tep_db_query($sales_query_raw);
$sales = tep_db_fetch_array($sales_query);
echo number_format($sales['SUM(ot.value)'],2);

Share this post


Link to post
Share on other sites
in fact the whole thing is here

 

$sales_query_raw = "select SUM(ot.value)
							   from " . TABLE_ORDERS . " o,
							   " . TABLE_ORDERS_TOTAL . " ot
							   where o.orders_id = ot.orders_id and ot.class = 'ot_shipping' 
								 and o.date_purchased >= date_add(curdate(), interval -1 month)";

$sales_query = tep_db_query($sales_query_raw);
$sales = tep_db_fetch_array($sales_query);
echo number_format($sales['SUM(ot.value)'],2);

 

Thanks for that, very useful, it's the SQL Queries I always have problems with!

This seems to be returning averything for one month behind the date it is, so for example today it would do data from 17th March to 17th April?

I was trying to set it so it would do figures for March

 

I'm probably asking too much for it to do this though..

 

Mat

Share this post


Link to post
Share on other sites

$month = date("m");
$year = date("y");
$sales_query_raw = "select SUM(ot.value)
							   from " . TABLE_ORDERS . " o,
							   " . TABLE_ORDERS_TOTAL . " ot
							   where o.orders_id = ot.orders_id and ot.class = 'ot_subtotal' 
								 and date_format(o.date_purchased, '%m')= '".$month."' 
								 and date_format(o.date_purchased, '%y')= '".$year."'";

$sales_query = tep_db_query($sales_query_raw);
$sales = tep_db_fetch_array($sales_query);
echo number_format($sales['SUM(ot.value)'],2);

 

If you want to set it for a particular month change the $month variable to - 01-12 , as it is at the moment it takes the current month 04 into the variable and works from that.

 

So for march:

 

$month = '03';

Edited by Jonojamesmac

Share this post


Link to post
Share on other sites
$month = date("m");
$year = date("y");
$sales_query_raw = "select SUM(ot.value)
							   from " . TABLE_ORDERS . " o,
							   " . TABLE_ORDERS_TOTAL . " ot
							   where o.orders_id = ot.orders_id and ot.class = 'ot_subtotal' 
								 and date_format(o.date_purchased, '%m')= '".$month."' 
								 and date_format(o.date_purchased, '%y')= '".$year."'";

$sales_query = tep_db_query($sales_query_raw);
$sales = tep_db_fetch_array($sales_query);
echo number_format($sales['SUM(ot.value)'],2);

 

If you want to set it for a particular month change the $month variable to - 01-12 , as it is at the moment it takes the current month 04 into the variable and works from that.

 

So for march:

 

$month = '03';

 

 

Thats great thanks.

would $month = date("m-1"); work? IE So you had a rolling "Last Month" query.

 

Cheers

 

Mat

Share this post


Link to post
Share on other sites

Also need to take into account, if its the first of the month you'll be wanting previous month and year total

 

replace:

 

$year = date("y");

 

with:

if ($month == 01) {
$year = date("y")-1; }
else {
$year = date("y"); }

Share this post


Link to post
Share on other sites

Mmm, tested it out and when you do ("m")-1; it takes the 0 away from the month away, and doing so fails the mysql query.

 

Change this at the top.

 

$month = date("m")-1;
if (($month == 10) || ($month == 11) || ($month == 12)) {
$month = $month; }
else {
$month = '0' .$month; }

$year = date("Y")-1;
if ($month == 12) {
$year = $year; }
else {
$year = date("Y"); }

Edited by Jonojamesmac

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×