Admin Store Summary

[rhgraphicdesign]

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

[random183]

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

[random183]

Anyone have any idea on this? Would be really handy to know!

 

Thanks

 

Mat

[Jonojamesmac]

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 April 15, 2009 by Jonojamesmac

[Jonojamesmac]

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);

[Jonojamesmac]

oops, just realised i replace ot_subtotal with ot_shipping, you know what to do ;)

[random183]

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

[Jonojamesmac]

$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 April 17, 2009 by Jonojamesmac

[random183]

$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

[Jonojamesmac]

$month = date("m")-1;

Edited April 17, 2009 by Jonojamesmac

[Jonojamesmac]

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"); }

[Jonojamesmac]

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 April 17, 2009 by Jonojamesmac