CODEWITHSUNDEEP
phpmysqlsqlwordpresswoocommerce
Patrick Joseph Aragones
Patrick Joseph Aragones

Reputation: 29

WooCommerce my-accounts Page - get the current user for orders query

I'm trying to sum specific items for each user, but it seems it doesn't recognize the current user and it sums all the orders for all customers.

How can I solve this? What I am missing?

Here's the code I am using: 

$order_items = apply_filters( 'woocommerce_reports_top_earners_order_items', $wpdb->get_results( "

SELECT order_item_meta_2.meta_value as product_id, SUM( order_item_meta.meta_value ) as line_total FROM {$wpdb->prefix}woocommerce_order_items as order_items

LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id

LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id

LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID

WHERE posts.post_type = 'shop_order'

AND posts.post_status IN ( '" . implode( "','", array( 'wc-completed', 'wc-processing', 'wc-on-hold' ) ) . "' )

AND order_items.order_item_type = 'line_item'

AND order_item_meta.meta_key = '_line_total'

AND order_item_meta_2.meta_key = '_product_id'

GROUP BY order_item_meta_2.meta_value

" ));
$totalPR = 0;
$Products = array(1507, 1406, 1506);

foreach ($order_items as $item) {

 if (in_array($item->product_id, $Products)) {

        $totalPR = $item->line_total + $totalPR;
         echo $totalPR;

    }

}

Upvotes: 3

Views: 842

Answers (2)

jinkal
jinkal

Reputation: 1

$current_user = wp_get_current_user();
$current_user_id = $current_user->ID;
$args = array(
            'customer_id' => $current_user_id
        );
$orders = wc_get_orders($args);

You Can Use wp_get_current_user(); for Get Current User And Get Order Data With Args May be It's Work For You.$current_user_id = $current_user->ID;echo $current_user_id;//check current user id get or notecho $current_user_id;//check current user id get or not

Upvotes: 0

LoicTheAztec
LoicTheAztec

Reputation: 253773

Update 2 - It's working making this:

  1. Get the list of the customers
  2. Go through each customer in a foreach loop
  3. Get the customer ID

  4. Add a LEFT JOIN for wp_postmeta table on the post_id:

    LEFT JOIN {$wpdb->postmeta} AS postmeta ON order_items.order_id = postmeta.post_id

  5. insert this 2 lines into your WHERE: 

    AND postmeta.meta_key = '_customer_user'

AND postmeta.meta_value = '$customer_id'

  6. Then comes your loop inside the customers loop, to get the sum by customer

This is the code:

global $wpdb;

// Set here your product ids
$products = array( 1507, 1406, 1506 );
$all_customers = get_users( 'role=customer' );

foreach ( $all_customers as $customer ) {
    $customer_id = $customer->ID;
    $total_pr = 0;

    $query = $wpdb->get_results("

        SELECT order_item_meta_2.meta_value as product_id,

        SUM( order_item_meta.meta_value ) as line_total

        FROM {$wpdb->prefix}woocommerce_order_items as order_items

        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id

        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id

        LEFT JOIN {$wpdb->postmeta} AS postmeta ON order_items.order_id = postmeta.post_id

        LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID

        WHERE posts.post_type = 'shop_order'

        AND posts.post_status IN ( '" . implode( "','", array( 'wc-completed', 'wc-processing', 'wc-on-hold' ) ) . "' )

        AND postmeta.meta_key = '_customer_user'

        AND postmeta.meta_value = '$customer_id'

        AND order_items.order_item_type = 'line_item'

        AND order_item_meta.meta_key = '_line_total'

        AND order_item_meta_2.meta_key = '_product_id'

        GROUP BY order_item_meta_2.meta_value

    ");

    $results = apply_filters( 'woocommerce_reports_top_earners_order_items', $query );

    foreach ( $results as $values ) {
         if ( in_array( $values->product_id, $products ) ) {
             $total_pr += $values->line_total;
        }
    }
    echo 'customer ID: ' . $customer->ID . ' | Total PR: ' . number_format( $total_pr, 2 ) . '<br>';
}

Tested and works. It will output the list with the Customer IDs and the corresponding Total PR.

References:

Upvotes: 1

Related Questions