Dashboard Stats does not exclude orders that were trashed

Marketpress V 2.9.4.1
marketpress_stats
If orders are trashed in Manage Orders they are still being included in the dashboard stats. Trashed orders are typically never fulfilled. Hence, the query should include a filter for excluding them in the stats.

I added this to each query WHERE clause:

'AND p.post_status != 'trash'`

Your thoughts?
Merv (@lzw8bk)

  • lzw8bk

    I also added code to display yearly stats on the dashboard. Here's the complete stats display code to also include a Total Tax line on yearly:

    // Added logic to not include trash orders on 4/27/2014
    	function dashboard_widget() {
    		global $wpdb, $mp;
    		$year = date('Y');
    		$month = date('m');
    		$this_month = $wpdb->get_row("SELECT count(p.ID) as count, sum(m.meta_value) as total, avg(m.meta_value) as average FROM $wpdb->posts p JOIN $wpdb->postmeta m ON p.ID = m.post_id WHERE p.post_type = 'mp_order' AND p.post_status != 'trash' AND m.meta_key = 'mp_order_total' AND YEAR(p.post_date) = $year AND MONTH(p.post_date) = $month");
    
    		$year = date('Y', strtotime('-1 month'));
    		$month = date('m', strtotime('-1 month'));
    		$last_month = $wpdb->get_row("SELECT count(p.ID) as count, sum(m.meta_value) as total, avg(m.meta_value) as average FROM $wpdb->posts p JOIN $wpdb->postmeta m ON p.ID = m.post_id WHERE p.post_type = 'mp_order' AND p.post_status != 'trash' AND m.meta_key = 'mp_order_total' AND YEAR(p.post_date) = $year AND MONTH(p.post_date) = $month");
    
    		// Year stats added
    		$year = date('Y');
    		$this_year = $wpdb->get_row("SELECT count(p.ID) as count, sum(m.meta_value) as total, avg(m.meta_value) as average FROM $wpdb->posts p JOIN $wpdb->postmeta m ON p.ID = m.post_id WHERE p.post_type = 'mp_order' AND p.post_status != 'trash' AND m.meta_key = 'mp_order_total' AND YEAR(p.post_date) = $year");
    		// Year tax added
    		$year = date('Y');
    		$this_year_tax = $wpdb->get_row("SELECT count(p.ID) as count2, sum(m.meta_value) as tax FROM $wpdb->posts p JOIN $wpdb->postmeta m ON p.ID = m.post_id WHERE p.post_type = 'mp_order' AND p.post_status != 'trash' AND m.meta_key = 'mp_tax_total' AND YEAR(p.post_date) = $year");
    		// Last year stats added
    		$year = date('Y', strtotime('-1 year'));
    		$last_year = $wpdb->get_row("SELECT count(p.ID) as count, sum(m.meta_value) as total, avg(m.meta_value) as average FROM $wpdb->posts p JOIN $wpdb->postmeta m ON p.ID = m.post_id WHERE p.post_type = 'mp_order' AND p.post_status != 'trash' AND m.meta_key = 'mp_order_total' AND YEAR(p.post_date) = $year");
    		// Last Year Tax
    		$year = date('Y', strtotime('-1 year'));
    		$last_year_tax = $wpdb->get_row("SELECT count(p.ID) as count2, sum(m.meta_value) as tax FROM $wpdb->posts p JOIN $wpdb->postmeta m ON p.ID = m.post_id WHERE p.post_type = 'mp_order' AND p.post_status != 'trash' AND m.meta_key = 'mp_tax_total' AND YEAR(p.post_date) = $year");
    
    		//later get full stats and graph
    		//$stats = $wpdb->get_results("SELECT DATE_FORMAT(p.post_date, '%Y-%m') as date, count(p.ID) as count, sum(m.meta_value) as total, avg(m.meta_value) as average FROM $wpdb->posts p JOIN $wpdb->postmeta m ON p.ID = m.post_id WHERE p.post_type = 'mp_order' AND m.meta_key = 'mp_order_total' GROUP BY YEAR(p.post_date), MONTH(p.post_date) ORDER BY date DESC");
    		?>
    		<div class="table table_content">
    			<p class="sub"><?php printf(__('This Month (%s)', 'mp'), date_i18n('M, Y')); ?></p>
    			<table>
    				<tbody>
    					<tr class="first">
    						<td class="first b<?php echo ($this_month->count >= $last_month->count) ? ' green' : ' red'; ?>"><?php echo number_format_i18n($this_month->count); ?></td>
    						<td class="t"><?php _e('Orders', 'mp'); ?></td>
    					</tr>
    					<tr>
    						<td class="first b<?php echo ($this_month->total >= $last_month->total) ? ' green' : ' red'; ?>"><?php echo $mp->format_currency(false, $this_month->total); ?></td>
    						<td class="t"><?php _e('Orders Total', 'mp'); ?></td>
    					</tr>
    					<tr>
    						<td class="first b<?php echo ($this_month->average >= $last_month->average) ? ' green' : ' red'; ?>"><?php echo $mp->format_currency(false, $this_month->average); ?></td>
    						<td class="t"><?php _e('Average Order', 'mp'); ?></td>
    					</tr>
    				</tbody>
    			</table>
    		</div>
    
    		<div class="table table_discussion">
    			<p class="sub"><?php printf(__('Last Month (%s)', 'mp'), date_i18n('M, Y', strtotime('-1 month'))); ?></p>
    			<table>
    				<tbody>
    					<tr class="first">
    						<td class="first b"><?php echo intval($last_month->count); ?></td>
    						<td class="t"><?php _e('Orders', 'mp'); ?></td>
    					</tr>
    					<tr>
    						<td class="first b"><?php echo $mp->format_currency(false, $last_month->total); ?></td>
    						<td class="t"><?php _e('Orders Total', 'mp'); ?></td>
    					</tr>
    					<tr>
    						<td class="first b"><?php echo $mp->format_currency(false, $last_month->average); ?></td>
    						<td class="t"><?php _e('Average Order', 'mp'); ?></td>
    					</tr>
    				</tbody>
    			</table>
    		</div>
    		<br class="clear"/>
    
    		<div class="table table_content">
    			<p class="sub"><?php printf(__('This Year (%s)', 'mp'), date_i18n('Y')); ?></p>
    			<table>
    				<tbody>
    					<tr class="first">
    						<td class="first b<?php echo ($this_year->count >= $last_year->count) ? ' green' : ' red'; ?>"><?php echo number_format_i18n($this_year->count); ?></td>
    						<td class="t"><?php _e('Orders', 'mp'); ?></td>
    					</tr>
    					<tr>
    						<td class="first b<?php echo ($this_year->total >= $last_year->total) ? ' green' : ' red'; ?>"><?php echo $mp->format_currency(false, $this_year->total); ?></td>
    						<td class="t"><?php _e('Orders Total', 'mp'); ?></td>
    					</tr>
    					<tr>
    						<td class="first b<?php echo ($this_year->average >= $last_year->average) ? ' green' : ' red'; ?>"><?php echo $mp->format_currency(false, $this_year->average); ?></td>
    						<td class="t"><?php _e('Average Order', 'mp'); ?></td>
    					</tr>
    					<tr>
    						<td class="first b"><?php echo $mp->format_currency(false, $this_year_tax->tax); ?></td>
    						<td class="t"><?php _e('Tax Total', 'mp'); ?></td>
    					</tr>
    				</tbody>
    			</table>
    		</div>
    
    		<div class="table table_discussion">
    			<p class="sub"><?php printf(__('Last Year (%s)', 'mp'), date_i18n('Y', strtotime('-1 year'))); ?></p>
    			<table>
    				<tbody>
    					<tr class="first">
    						<td class="first b"><?php echo intval($last_year->count); ?></td>
    						<td class="t"><?php _e('Orders', 'mp'); ?></td>
    					</tr>
    					<tr>
    						<td class="first b"><?php echo $mp->format_currency(false, $last_year->total); ?></td>
    						<td class="t"><?php _e('Orders Total', 'mp'); ?></td>
    					</tr>
    					<tr>
    						<td class="first b"><?php echo $mp->format_currency(false, $last_year->average); ?></td>
    						<td class="t"><?php _e('Average Order', 'mp'); ?></td>
    					</tr>
    					<tr>
    						<td class="first b"><?php echo $mp->format_currency(false, $last_year_tax->tax); ?></td>
    						<td class="t"><?php _e('Tax Total', 'mp'); ?></td>
    					</tr>
    				</tbody>
    			</table>
    		</div>
    		<br class="clear"/>
    		<?php
    	}

    Hope this is helpful!