I'm trying to create a dashboard to tell me how much I've made last year, this year so far, overall in total, month, last month, week, and today.

This is the coding i have so far..I cannt get day to work..it prints nothing, I havent even started on week (that looks scarey to code) as well as last year. So far overall sales, this month, and last month works.

$total_sales 	= $db->select('SELECT SUM(`prod_total`) as `total_sales` FROM `'.$config['dbprefix'].'CubeCart_order_sum` WHERE `status` > 1 ;');

$quick_stats['total_sales'] = $total_sales[0]['total_sales'];

$ave_order 	= $db->select('SELECT AVG(`prod_total`) as `ave_order` FROM `'.$config['dbprefix'].'CubeCart_order_sum` WHERE `status` > 1 ;');

$quick_stats['ave_order'] = $ave_order[0]['ave_order'];

$this_year 		= date('Y');

$this_month 		= date('m');

$this_month_start 	= mktime (0, 0, 0, $this_month, '01', $this_year);


## Work out prev month looks silly but should stop -1 month on 1st March returning January (28 Days in Feb) 
$last_month 		= date('m',strtotime("-1 month", mktime(12,0,0,$this_month,15,$this_month)));
$last_year 		= ($last_month < $this_month) ? $this_year : ($this_year - 1);
$last_month_start 	= mktime (0, 0, 0, $last_month, '01', $last_year);

$last_month_sales 	= $db->select('SELECT SUM(`prod_total`) as `last_month` FROM `'.$config['dbprefix'].'CubeCart_order_sum` WHERE `status` > 1 AND `time` > '.$last_month_start.' AND `time` < '.$this_month_start.';');

$quick_stats['last_month'] = $last_month_sales[0]['last_month'];

$this_month_sales 	= $db->select('SELECT SUM(`prod_total`) as `this_month` FROM `'.$config['dbprefix'].'CubeCart_order_sum` WHERE `status` > 1 AND `time` > '.$this_month_start.';');


$quick_stats['this_month'] = $this_month_sales[0]['this_month'];

##DAY

$this_day 		= date('d');

$this_day_start 	= strtotime ('00:00', $this_day);

$this_day_sales 	= $db->select('SELECT SUM(`prod_total`) as `this_day` FROM `'.$config['dbprefix'].'CubeCart_order_sum` WHERE `status` > 1 AND `time` > '.$this_day_start.';');

How do I do day and week?

    I don't really get how you're calculating $this_day_start. Surely you just set it to a string of "00:00" or even "00:00:00" depending on the format in your database? If your database is in unixtime then you use mktime() giving it the year, month and day and it should automatically be the start of the day.

      Well I'm not sure what to put there is the problem. The goal is to have the code take my database and pull all orders for a day and keep it as a running amount until midnight server time, when the countdown starts over for the new days totals. Or heck I'd actually be happy with just yesterdays sales total.

      I have used strtotime and mktime in the same php pages with nothing blowing up lol. I think my server is unixtime though.🙂

        I'm just redisplaying your code in PHP not CODE tags so it colours it:

        $total_sales 	= $db->select('SELECT SUM(`prod_total`) as `total_sales` FROM `'.$config['dbprefix'].'CubeCart_order_sum` WHERE `status` > 1 ;');

$quick_stats['total_sales'] = $total_sales[0]['total_sales'];

$ave_order 	= $db->select('SELECT AVG(`prod_total`) as `ave_order` FROM `'.$config['dbprefix'].'CubeCart_order_sum` WHERE `status` > 1 ;');

$quick_stats['ave_order'] = $ave_order[0]['ave_order'];

$this_year 		= date('Y');

$this_month 		= date('m');

$this_month_start 	= mktime (0, 0, 0, $this_month, '01', $this_year);


## Work out prev month looks silly but should stop -1 month on 1st March returning January (28 Days in Feb) 
$last_month 		= date('m',strtotime("-1 month", mktime(12,0,0,$this_month,15,$this_month)));
$last_year 		= ($last_month < $this_month) ? $this_year : ($this_year - 1);
$last_month_start 	= mktime (0, 0, 0, $last_month, '01', $last_year);

$last_month_sales 	= $db->select('SELECT SUM(`prod_total`) as `last_month` FROM `'.$config['dbprefix'].'CubeCart_order_sum` WHERE `status` > 1 AND `time` > '.$last_month_start.' AND `time` < '.$this_month_start.';');

$quick_stats['last_month'] = $last_month_sales[0]['last_month'];

$this_month_sales 	= $db->select('SELECT SUM(`prod_total`) as `this_month` FROM `'.$config['dbprefix'].'CubeCart_order_sum` WHERE `status` > 1 AND `time` > '.$this_month_start.';');


$quick_stats['this_month'] = $this_month_sales[0]['this_month'];

##DAY

$this_day 		= date('d');

$this_day_start 	= strtotime ('00:00', $this_day);

$this_day_sales 	= $db->select('SELECT SUM(`prod_total`) as `this_day` FROM `'.$config['dbprefix'].'CubeCart_order_sum` WHERE `status` > 1 AND `time` > '.$this_day_start.';');

        Well I would say that your database 'time' column would definitely appear to be a unixtime so try setting:

        $this_day_start = mktime(0,0,0,$this_month, $this_day, $this_year);

        Then inside your $db class can you use an echo statement to show your select statement before you pass it through to the database. Run that yourself over the database directly via phpMyAdmin or similar and make sure it's actually the right statement to try.

          Write a Reply...