From Get the customer's total spend for a period of 1 year in WooCommerce answer to my previous question:
// Utility function: Get customer orders total amount from specific status on a period
function get_orders_sum_amount_for( $user_id, $period = '1 year', $status = 'completed' ) {
global $wpdb;
return $wpdb->get_var( $wpdb->prepare( "
SELECT SUM(total_amount)
FROM {$wpdb->prefix}wc_orders
WHERE status = %s
AND customer_id = %d
AND date_created_gmt >= %s
", 'wc-'.$status, $user_id, date('Y-m-d H:i:s', strtotime('-'.$period) ) ) );
}
add_shortcode('user_year_total_spent', 'get_user_year_total_spent');
function get_user_year_total_spent( $atts ) {
extract( shortcode_atts( array(
'user_id' => get_current_user_id(),
), $atts, 'user_year_total_spent' ) );
if( ! $user_id ) return;
$total_spent = get_orders_sum_amount_for( $user_id ); // Get total spent amount
if( $total_spent >= 1200 ) {
$text = __('Congrats you are now tier 3.', 'woocommerce');
} elseif ( $total_spent >= 600 ) {
$text = __('Congrats you are now tier 2.', 'woocommerce');
} else {
$text = __('Congrats you are now tier 1.', 'woocommerce');
}
return sprintf(__('Total Amount Spent: %s. %s', 'woocommerce'), wc_price($total_spent), $text);
}
// SHORTCODE USAGE: [user_year_total_spent]
It receives the total spend for a certain period of time for all completed user orders.
But I was faced with the fact that if the order was partially returned, then the total spend is not reduced by the amount of the partial refund.
Example: Order 3T-shirts * 10 EU. Total spend 30 EU. Delivery costs are not included in the total spend. Further, the buyer in this order returned 1 T-shirt * 10 EU. The administrator issued a partial refund manually. Order status "Completed", 2 T-shirts * 20 EU. But the total spend is still 30 EU.
How to exclude a partial manual refund from the total spend?
To handle WooCommerce refunds in customer total spent, replace the first function with:
function get_orders_sum_amount_for( $user_id, $period = '1 year', $status = 'completed' ) {
global $wpdb;
$date = date('Y-m-d H:i:s', strtotime('-'.$period) );
$status = 'wc-'.$status;
$total_orders = (float) $wpdb->get_var( $wpdb->prepare( "
SELECT SUM(total_amount) FROM {$wpdb->prefix}wc_orders
WHERE status = %s AND customer_id = %d AND date_created_gmt >= %s
", $status, $user_id, $date ) );
$total_refunds = (float) $wpdb->get_var( $wpdb->prepare( "
SELECT SUM(r.total_amount)
FROM {$wpdb->prefix}wc_orders r
LEFT JOIN {$wpdb->prefix}wc_orders o ON r.parent_order_id = o.id
WHERE r.type = 'shop_order_refund' AND r.status = %s
AND o.customer_id = %d AND o.date_created_gmt >= %s
", $status, $user_id, $date) );
return $total_orders + $total_refunds;
}
Code goes in functions.php file of your active child theme (or active theme). Tested and works.