Show number of orders by statuses and by custom ACF field in Woocommerce

Question

I'm working on a kind of internal 'plugin' to show how many orders currently I have with a split by statuses and account manager. So I would like to present it as a table like:

Account Manager | No of pending orders | No of canceled orders etc

I have such a piece of code:

function display_woocommerce_order_count( $atts, $content = null ) {
    $args = shortcode_atts( array(
        'status' => 'completed',
    ), $atts );
    $statuses    = array_map( 'trim', explode( ',', $args['status'] ) );
    $order_count = 0;
    foreach ( $statuses as $status ) {
        // if we didn't get a wc- prefix, add one
        if ( 0 !== strpos( $status, 'wc-' ) ) {
            $status = 'wc-' . $status;
        }
        $order_count += wp_count_posts( 'shop_order' )->$status;
    }
    ob_start();
    echo number_format( $order_count );
    return ob_get_clean();
}
add_shortcode( 'wc_order_count', 'display_woocommerce_order_count' );

On top of that I saw this code but im not sure how to use it:

public function get_customer_total_order() {
    $customer_orders = get_posts( array(
        'numberposts' => - 1,
        'meta_key'    => '_customer_user',
        'meta_value'  => get_current_user_id(),
        'post_type'   => array( 'shop_order' ),
        'post_status' => array( 'wc-completed' )
    ) );

    $total = 0;
    foreach ( $customer_orders as $customer_order ) {
        $order = wc_get_order( $customer_order );
        $total += $order->get_total();
    }

    return $total;
}

which adds a shortcode to display in general order number but without a breakdown to account manager. Through ACF I've created a custom field called "handlowiec" and assigned it to order screen. How to make it work?

You want to use get_customer_total_order() in shortcode or want to add No of pending and No of cancel Orders in display_woocommerce_order_count() ? — Tejas Soni, May 02 '18 at 11:51
@TejasSoni - as said i've just found this piece of code. Will not use it as a shortcode (but as a php code inside template file). So all in all wanted to make a loop just to show raw numbers — Julia Galden, May 04 '18 at 06:53
@MathieudeLorimier - sure. Account manager is a custom field added to the order screen (added via ACF). The field name is accountmanager. Do i need to provide field key? — Julia Galden, May 04 '18 at 06:54

LoicTheAztec · Accepted Answer · 2018-05-06T05:00:58.047

The following code, will count orders by status for the current Account manager ID corresponding to "handlowiec" ACF field:

So you will have to assign to each order to an Account manager ID:

The function code with a very light SQL query (based on wp_count_posts() WordPress function):

function display_woocommerce_order_count( $atts, $content = null ) {
    $args = shortcode_atts( array(
        'status' => 'completed, processing, on-hold, cancelled',
        'handlowiec' => ''
    ), $atts );

    // Formatting the order statuses for the SQL query
    $statuses = $data = [];
    $statuses_array = array_map( 'trim', explode( ',', $args['status'] ) );

    foreach ( $statuses_array as $status ) {
        if ( 0 !== strpos( $status, 'wc-' ) )
            $statuses[] = 'wc-' . $status;
    }
    $statuses = implode("','", $statuses);

    ## -- 1. The SQL Query -- ##

    global $wpdb;
    $handlowiec_query = $join_postmeta = $output = '';

    // Handling the Account Manager ID (optionally)
    if( $args['handlowiec'] !== '' ){
        $join_postmeta = "INNER JOIN {$wpdb->prefix}postmeta pm ON p.ID = pm.post_id";
        $handlowiec_query = "AND meta_key like 'handlowiec' AND meta_value = ";
        $handlowiec_query .= $args['handlowiec'];
    }

    $results = $wpdb->get_results("
        SELECT post_status, COUNT( * ) AS num_posts
        FROM {$wpdb->prefix}posts as p
        $join_postmeta
        WHERE post_type = 'shop_order'
        AND post_status IN ('$statuses')
        $handlowiec_query
        GROUP BY post_status
    ");

    ## -- 2. Formatting the Output -- ##

    // Loop through each order status count
    foreach($results as $result){
        $status = str_replace( 'wc-', '', $result->post_status );
        $orders_count = (int) $result->num_posts;

        if( $orders_count > 0 )
            $data[] =  ucfirst($status) . ' ' . _n( 'order', 'orders', $orders_count ) . ': ' . $orders_count;
    }
    if( $args['handlowiec'] !== '' )
        $output = 'Account Manager ID ' . $args['handlowiec'] . ' | ';
    else
        $output = 'All Account Managers | ';

    if( sizeof($data) > 0 )
        $output .= implode( ' | ', $data );
    else
        $output .= 'No results';

    return '<p>' . $output . '</p>';
}
add_shortcode( 'wc_order_count', 'display_woocommerce_order_count' );

Code goes in function.php file of your active child theme (or active theme). Tested and works.

USAGE:

1) Without an "Account Manager ID" (for all managers):

 echo do_shortcode("[wc_order_count]");

You will get something like:

All Account Managers | On-hold orders: 2 | Processing orders: 7 | …

2) With a specific "Account Manager ID":

echo do_shortcode("[wc_order_count handlowiec='5']");

You will get something like:

Account Manager ID 5 | On-hold order: 1 | Processing orders: 3 | …

You can also use as before status argument to specify which orders status will be involved in the output…

Show number of orders by statuses and by custom ACF field in Woocommerce

1 Answers1