显示客户在Woocommerce中未花费的总支出

Question

I'm trying to show how much each customer has spent in total WITHOUT including the shipping cost. Everything from the code below, which creates a new column and makes it sortable, works fine except for the calculation.

I get an error on line 37 which is this one:

$money_spent = wc_get_customer_total_spent( $user_id ) - $order->get_total_tax() - $order->get_total_shipping() - $order->get_shipping_tax(), wc_get_price_decimals(), '.', '' );

Here is all the code I'm using =>

class Total_Spent_By_Customer_WooCommerce {

    public function __construct() {
        add_action( 'init', array( &$this, 'init' ) );
    }

    public function init() {
    add_filter( 'manage_users_columns', array( $this,'users_columns') );
    add_action( 'manage_users_custom_column',  array( $this ,'users_custom_column'), 10, 3);
    add_filter( 'manage_users_sortable_columns', array( $this ,'users_sortable_columns') );
    add_filter( 'users_list_table_query_args', array( $this ,'users_orderby_column'), 10, 1 );
    add_action( 'plugins_loaded', array( $this ,'load_this_textdomain') );
  }

  public static function users_columns( $columns ) {
    unset($columns['posts']);
    $columns['money_spent'] = _x( 'Money Spent', 'user', 'total-spent-by-customer-for-woocommerce' );
    return $columns;
  }

  public static function users_custom_column( $value, $column_name, $user_id ) {
    if ( 'money_spent' != $column_name ) {
      return $value;
    } else {
      $money_spent = wc_get_customer_total_spent( $user_id ) - $order->get_total_tax() - $order->get_total_shipping() - $order->get_shipping_tax(), wc_get_price_decimals(), '.', '' );
      return wc_price( wc_get_customer_total_spent ( $user_id ));
    }
  }

  public static function users_sortable_columns($columns) {
    $custom = array(
      'money_spent'    => 'money_spent',
    );

    return wp_parse_args( $custom, $columns );
  }

  public static function users_orderby_column( $args ) {
    if ( isset( $args['orderby'] ) && 'money_spent' == $args['orderby'] ) {
      $args = array_merge( $args, array(
        'meta_key' => '_money_spent',
        'orderby'    => 'meta_value_num',
      ));
    }

    return $args;
  }

  public function load_this_textdomain() {
    load_plugin_textdomain( 'total-spent-by-customer-for-woocommerce' );
  }
}

new Total_Spent_By_Customer_WooCommerce();

I would really appreciate any type of help with this.

Answer 1

Update 2

You can not get Customer total spent without shipping and taxes totals using wc_get_customer_total_spent( $user_id ) function, as you should need to remove the taxes and shipping for each order that the current customer has made.

Also you can not get and use The WC_Order object in your function. So the WC_Order methods get_total_tax(), get_total_shipping() and get_shipping_tax() can't be used.

---

The alternative to get the customer total spent without shipping and taxes:

Based on the SQL Woocommerce query source code involved in wc_get_customer_total_spent() function we can build your own query.

So you will replace your function by this:

public static function users_custom_column( $value, $column_name, $user_id ) {
    if ( 'money_spent' == $column_name ) {
        global $wpdb;

        $statuses = array_map( 'esc_sql', wc_get_is_paid_statuses() );

        $spent    = $wpdb->get_var("
            SELECT SUM(pm2.meta_value - (pm3.meta_value + pm4.meta_value))
            FROM $wpdb->posts as p
            LEFT JOIN {$wpdb->postmeta} AS pm ON p.ID = pm.post_id
            LEFT JOIN {$wpdb->postmeta} AS pm2 ON p.ID = pm2.post_id
            LEFT JOIN {$wpdb->postmeta} AS pm3 ON p.ID = pm3.post_id
            LEFT JOIN {$wpdb->postmeta} AS pm4 ON p.ID = pm4.post_id
            WHERE   pm.meta_key   = '_customer_user'
            AND     pm.meta_value = '" . esc_sql( $user_id ) . "'
            AND     p.post_type   = 'shop_order'
            AND     p.post_status IN ( 'wc-" . implode( "','wc-", $statuses ) . "' )
            AND     pm2.meta_key  = '_order_total'
            AND     pm3.meta_key  = '_order_tax'
            AND     pm4.meta_key  = '_order_shipping'
        ");

        if ( ! $spent ) {
            $spent = 0;
        }
        $value = wc_price( $spent );
    }
    return $value;
}

Tested and perfectly works.

Calculations note:

As you are removing the total taxes from the orders you don't need to remove the shipping taxes (as they are included in the total taxes).

So we just remove the total taxes and the total shipping (excluding taxes).