I’ve been playing around with WooCommerce a lot lately, and twigged on something that I think is a little design flaw.
It all started when I created a duplicate of one of the built-in reports (the Customer List report), with a few bits of different logic which aren’t really important right now. (Incidentally, creating a report is dead easy with just a few action hooks like woocommerce_admin_reports and wc_admin_reports_path, and can be done right in your theme). This new report keeps the same columns as Customer List, including:
- Orders, the total number of orders for that customer
- Money Spent, the total money spent by that customer
- Last Order (date and link to the customer’s last order)
But while testing my report I noticed that some of these numbers weren’t quite right; they weren’t right in the Customer List report as well. What was happening is that some of this client’s customers had submitted orders using their account email but without logging in. This meant that those orders did not get counted against their account totals, even though I think they should have because they have to be the same person.
Here’s how WooCommerce links orders to accounts: if the user is logged in, the order gets a piece of meta data with key ‘_customer_user’, and the value is the user ID. So far so good. But if the order was submitted anonymously, then the best we have is the billing email (meta key ‘_billing_email’). To display the relevant data, the Customer List report uses two built-in functions, wc_get_customer_total_spent() and wc_get_customer_order_count(), defined in file woocommerce/includes/wc-user-functions.php. Both take a user ID as parameter, and run similar SQL queries. For example:
global $wpdb;
$spent = $wpdb->get_var( "SELECT SUM(meta2.meta_value)
FROM $wpdb->posts as posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
LEFT JOIN {$wpdb->postmeta} AS meta2 ON posts.ID = meta2.post_id
WHERE meta.meta_key = '_customer_user'
AND meta.meta_value = $user_id
AND posts.post_type IN ('" . implode( "','", wc_get_order_types( 'reports' ) ) . "')
AND posts.post_status IN ( 'wc-completed', 'wc-processing' )
AND meta2.meta_key = '_order_total'
" );
The logic to get the latest order is defined in woocommerce/includes/admin/reports/class-wc-report-customer-list.php and uses get_posts() with a meta query on key ‘_customer_user’, like so:
$order_ids = get_posts( array(
'posts_per_page' => 1,
'post_type' => 'shop_order',
'orderby' => 'date',
'order' => 'desc',
'post_status' => array( 'wc-completed', 'wc-processing' ),
'meta_query' => array(
array(
'key' => '_customer_user',
'value' => $user->ID
)
),
'fields' => 'ids'
) );
So my solution is simply to tweak those three queries. The meta query part should change to:
'meta_query' => array(
'relation' => 'OR',
array(
'key' => '_customer_user',
'value' => $user->ID
),
array(
'key' => '_billing_email',
'value' => $user->user_email
)
),
Both functions wc_get_customer_total_spent() and wc_get_customer_order_count() will need to load the user object, so that we can get the email. So the queries should look like:
$user = get_user_by( 'id', $user_id );
global $wpdb;
$spent = $wpdb->get_var( "SELECT SUM(meta2.meta_value)
FROM $wpdb->posts as posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
LEFT JOIN {$wpdb->postmeta} AS meta2 ON posts.ID = meta2.post_id
WHERE (
(meta.meta_key = '_customer_user' AND meta_value = $user_id)
OR
(meta.meta_key = '_billing_email' AND meta_value = '{$user->user_email}')
)
AND posts.post_type IN ('" . implode( "','", wc_get_order_types( 'reports' ) ) . "')
AND posts.post_status IN ( 'wc-completed', 'wc-processing' )
AND meta2.meta_key = '_order_total'
" );