Total count for each order item in a loop on Wooco

Hi I'm learning PHP and Woocommerce at the same time! I'm trying to get all the orders which have status processing and the total count for each and display this on a page.

Thus far I can loop through all the orders and get the name and quantity of each.

but as I don't know which product is going to be in the list, I'm not sure how I would compare the name and then add the quantity.

My current output is like this:

prod1 - v1 x 1
Prod2 - v3 x 1
prod2 - v3 x 1
prod3 - v2 x 11
prod3 - v2 x 1

What I would like is:

prod1 - v1 x 1
Prod2 - v3 x 2
prod3 - v2 x 12

The code currently is:

    <?php
/*
Template Name: Print Supplier Order
*/
if (!is_user_logged_in() || !current_user_can('manage_options')) wp_die('This page is private.');
?>
<!DOCTYPE HTML>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <title><?php _e('Processing Orders'); ?></title>
    <style>
        body { background:white; color:black; width: 95%; margin: 0 auto; }
    </style>
</head>
<body>
    <header>
        <?php if (have_posts()) : while (have_posts()) : the_post(); ?>

            <h1 class="title"><?php the_title(); ?></h1>

            <?php the_content(); ?>

        <?php endwhile; endif; ?>
    </header>
    <section>
    <?php 

    global $woocommerce;

$args = array( 'post_type' => 'shop_order', 'post_status' => 'wc-processing', 'posts_per_page' => -1 );


    $loop = new WP_Query( $args );

    while ( $loop->have_posts() ) : $loop->the_post();

        $order_id = $loop->post->ID;

        $order = new WC_Order($order_id);

        $product_details = array();
        $order_items = $order->get_items();
        foreach( $order_items as $product ) {
                echo $product['name']." x ".$product['qty'];
                echo '<br>';

            }
        ?>
    <?php endwhile; ?>
    </section>
</body>
</html>

标签： php sql wordpress woocommerce orders

2条回答

smile是对你的礼貌

2楼-- · 2019-08-24 04:41

Updated (Now the SQL query gives also the product name, make it even lighter)

Instead of using a WP_Query and some heavy code to get your calculations, you should better use this much more lighter and effective version code, using WPDB Class (a SQL query):

global $wpdb;

$results = $wpdb->get_results( "
    SELECT DISTINCT woim2.meta_value as id, SUM(woim.meta_value) as count, woi.order_item_name as name
    FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim
    INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON woi.order_item_id = woim.order_item_id
    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta as woim2 ON woi.order_item_id = woim2.order_item_id
    INNER JOIN {$wpdb->prefix}posts as p ON p.ID = woi.order_id
    WHERE p.post_status IN ('wc-processing','wc-on-hold')
    AND woim.meta_key LIKE '_qty'
    AND woim2.meta_key LIKE '_product_id'
    GROUP BY woim2.meta_value
" );

foreach( $results as $result ){
    echo $result->name . ' (' . $result->id . ') x ' . $result->count . '<br>';
}

The product count will be based only on the processing oder status and not on product total sales.

Tested and work.

Get the product variations instead (as asked in your comment)

As asked in your comment to get the product variations that are in the order, you will replace the line:

 AND woim2.meta_key LIKE '_product_id'

by the following line:

 AND woim2.meta_key LIKE '_variation_id'

Get all products and variations (Excluding Variable Products)

To get all products including Product Variations but excluding Variable Products use:

global $wpdb;

$results = $wpdb->get_results( "
    SELECT DISTINCT woim2.meta_value as id, SUM(woim.meta_value) as count, woi.order_item_name as name
    FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim
    INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON woi.order_item_id = woim.order_item_id
    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta as woim2 ON woi.order_item_id = woim2.order_item_id
    INNER JOIN {$wpdb->prefix}posts as p ON p.ID = woi.order_id
    WHERE p.post_status IN ('wc-processing','wc-on-hold') 
    AND woim.meta_key LIKE '_qty'
    AND ((woim2.meta_key LIKE '_variation_id' AND woim2.meta_value > 0)
    OR (woim2.meta_key LIKE '_product_id'
    AND woim2.meta_value NOT IN (SELECT DISTINCT post_parent FROM {$wpdb->prefix}posts WHERE post_type LIKE 'product_variation')))
    GROUP BY woim2.meta_value
" );

foreach( $results as $result ){
    echo $result->name . ' (' . $result->id . ') x ' . $result->count . '<br>';
}

Tested and work.

To get them all (even product variations and variable products):

global $wpdb;

$results = $wpdb->get_results( "
    SELECT DISTINCT woim2.meta_value as id, SUM(woim.meta_value) as count, woi.order_item_name as name
    FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim
    INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON woi.order_item_id = woim.order_item_id
    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta as woim2 ON woi.order_item_id = woim2.order_item_id
    INNER JOIN {$wpdb->prefix}posts as p ON p.ID = woi.order_id
    WHERE p.post_status IN ('wc-processing','wc-on-hold')
    AND woim.meta_key LIKE '_qty'
    AND ((woim2.meta_key LIKE '_variation_id' AND woim2.meta_value > 0)
    OR woim2.meta_key LIKE '_product_id' )
    GROUP BY woim2.meta_value
" );

foreach( $results as $result ){
    echo $result->name . ' (' . $result->id . ') x ' . $result->count . '<br>';
}

Tested and work.

0人赞添加讨论(0) 举报

Deceive 欺骗

3楼-- · 2019-08-24 04:47

Method 1

There are a few different ways you could tackle this, but if we want to adjust your code only slightly, then we can achieve this by adding your products in the product loop to a custom array, using the product name as a key, and then working through that array.

Here's an example:

global $woocommerce;

//custom array to list products:
$products_array = array();

//start wp query
$args = array( 
    'post_type'     => 'shop_order', 
    'post_status'   => 'wc-processing', 
    'posts_per_page'=> -1 
);
$loop = new WP_Query( $args );

//loop through orders
while ( $loop->have_posts() ) {
    $loop->the_post();

    //vars
    $order_id           = $loop->post->ID;
    $order              = new WC_Order($order_id);
    $product_details    = array();
    $order_items        = $order->get_items();

    //loop through products
    foreach( $order_items as $product ) {
        //if product has been captured to custom array before, increment value, else add it.
        if (array_key_exists($product['name'], $products_array)) {
            $products_array[$product['name']] += $product['qty'];
        } else {
            $products_array[$product['name']] = $product['qty'];
        }

    }
}

//end wp_query
wp_reset_postdata();

//display list:
foreach ($products_array as $title => $quantity) {
    echo $title.' x '.$quantity;
}

Method 2

Loop through all products, instead of all your orders (after a while, assuming the store is successful, there will probably be a lot more orders than products, so this method might be faster in the long run). Each product stores it's total sales as a meta value, so accessing it is quite simple:

//WP Query args
$args = array(
    'post_type'     => 'product',
    'posts_per_page'=> -1,
    'meta_key'      => 'total_sales',
    'orderby'       => 'title',
    'order'         => 'ASC',
    'meta_query'    => array( //make sure we only grab items that have actually sold
        array(
            'key'       => 'total_sales',
            'value'     => 0,
            'compare'   => '>'
        )
    )
);
$query = new WP_Query($args);

//loop through products
if($query->have_posts()) {
    while($query->have_posts()) {
        $query->the_post();
        //use built in Wordpress functions for get_the_title() and get_post_meta()
        echo get_the_title().' x '.get_post_meta(get_the_ID(), 'total_sales', true);
    }
}

//end wp query
wp_reset_postdata();

0人赞添加讨论(0) 举报