List woocommerce customer first name, last name and amount paid. WordPress
- SOLVED
I need to list every <strong>customer</strong> who has purchased and show only first, last, and total they have paid. Using woocommerce. Just need to list each name and amount. Just have no clue how to start to pull that content.
Here is an example that I have used to pull total amount of all sales. Everything I have tried and looked up isn't working.
<?php // All-time Sales for entire store
$query = array();
$query['fields'] = "SELECT SUM( postmeta.meta_value ) FROM {$wpdb->posts} as posts";
$query['join'] = "INNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id ";
$query['where'] = "WHERE posts.post_type IN ( '" . implode( "','", wc_get_order_types( 'reports' ) ) . "' ) ";
$query['where'] .= "AND posts.post_status IN ( 'wc-" . implode( "','wc-", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "' ) ";
$query['where'] .= "AND postmeta.meta_key = '_order_total' ";
$sales = $wpdb->get_var( implode( ' ', $query ) ); ?>
<span>$<?php echo($sales);?></span>
Answers (3)
Bob answers:
Don't know how we can achieve it with query. But here is another ruff idea which can be helpful to you.
1. you can get all customer and loop through it. [[LINK href="http://codex.wordpress.org/Function_Reference/get_users"]]get_users($arg)[[/LINK]] will be helpful to you here.
2. In that loop find each order of that customer and loop through all orders to create it's total.
useful code to loop through all orders by particular user
$args = array(
'numberposts' => -1,
'meta_key' => '_customer_user',
'meta_value' => $user_id,
'post_type' => 'shop_order',
'post_status' => 'publish',
'tax_query'=>array(
array(
'taxonomy' =>'shop_order_status',
'field' => 'slug',
'terms' =>$status
)
)
);
$orders=new WP_Query($args);
if($orders->have_posts()):
while($orders->have_posts()): $orders->the_post();
//get order total here and add it every time
endwhile;
endif;
3. before ending user loop display user related value like fristname lastname and the total
Casey Spitnale comments:
I placed the code in. Tried to modify and add code to display. Any ideas on how to display each user/total?
Bob comments:
sent you PM
Bob comments:
Hi following code will be helpful to you.
$order_totals = apply_filters( 'woocommerce_reports_sales_overview_order_totals', $wpdb->get_row( "
SELECT SUM(meta.meta_value) AS total_sales, COUNT(posts.ID) AS total_orders FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
WHERE meta.meta_key = '_order_total'
AND posts.post_type = 'shop_order'
AND posts.post_status = 'publish'
AND tax.taxonomy = 'shop_order_status'
AND term.slug IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
AND posts.ID IN( SELECT post_id from {$wpdb->postmeta} WHERE meta_key='_customer_user' AND meta_value = 1 )
" ) );
$total_sales = $order_totals->total_sales;
Note <strong>meta_value = 1</strong> here is 1 is id of user in most case 1 means admin user
loop through all users and change id of it at meta_value and print it.
Each time <strong>$total_sales</strong> variable will have total amount of purchase by that user.
In the end execute query with the meta_value = 0 This will return guest checkouts. so you can just display guest checkout instead of firstname and last name.
Casey Spitnale comments:
Still no luck with the code. what query did you use to get the name/ammount?
Bob comments:
The above query is for getting per user total only. You need to loop through all user. And use this query in it.
you will get user data by looping through users.
if you are willing to pay higher then i can try to make plugin for you.
Casey Spitnale comments:
Bob. I updated the prize amount. Thank You.
Bob comments:
Thanks I will start work on it tonight. Once done i will let you know. Are you available to chat on skype/gtalk? My gmail id is [email protected] and skype id is thevaghela.
Casey Spitnale comments:
yes. [email protected]
Bob comments:
A plugin suggested by Dbranes should work for you.
let us know if you require any modifications.
Thanks & Regards,
Bob
Arnav Joy answers:
Please see this link
http://fusedpress.com/blog/get-all-user-orders-and-products-bought-by-user-in-woocommerce/
is it helpful ?
Casey Spitnale comments:
Not really. I think that pulls a single customer. Could not get it to pull anything.
Dbranes answers:
Hi, did you try out the <strong>WooCommerce Top Customers:</strong> plugin:
[[LINK href="https://wordpress.org/plugins/woocommerce-top-customers/"]]https://wordpress.org/plugins/woocommerce-top-customers/[[/LINK]]
It uses the <em>[wc_top_customers]</em> shortcode to list customers by first and last name and uses for example the <em>_money_spent</em> and <em>_order_count</em> user meta keys.
The default attributes of the shortcode are:
[wc_top_customers number="12" columns="4" orderby="meta_key" orderby_meta_key="_order_count" order="asc" role="customer"]
The code for this plugin is very simple, so you should be able to adjust it to your needs. Just let me know if you need assistance doing that.
ps: [[LINK href="http://stackoverflow.com/questions/25420632/woocommerce-query-order-totals-grouped-by-users-with-specific-role"]]Here on StackOverflow[[/LINK]] you can find a complex SQL query similar to what you tried.