获取Woocommerce中每种产品的今天的总订单数 [英] Get today's total orders count for each product in Woocommerce

问题描述

我正在寻找一个代码片段以获取今天每种产品的总销售额，因此可以在我的theme functions.php文件中使用它.

I'm looking for a code snippet to get total sales of each product for today, so I can use it in my theme functions.php file.

输出应如下所示(每个商品的销售总额):

Product xxx = 25 orders
Product yyy = 18 orders
Product zzz = 8 orders

推荐答案

这可以通过以下非常简单的SQL查询和foreach循环来完成.

This can be done with the following very light SQL query and a foreach loop.

这将为您提供过去24小时内按产品计数的订单产品(以及产品变体，但没有父变量产品)的列表:

That will give you the list of products (and product variations, but not parent variable products) of orders count by product for the past 24 hours:

global $wpdb;

$results = $wpdb->get_results( "
    SELECT DISTINCT woim.meta_value as id, COUNT(woi.order_id) 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}posts as p ON p.ID = woi.order_id
    WHERE p.post_status IN ('wc-processing','wc-on-hold')
    AND UNIX_TIMESTAMP(p.post_date) >= (UNIX_TIMESTAMP(NOW()) - (86400))
    AND ((woim.meta_key LIKE '_variation_id' AND woim.meta_value > 0)
    OR (woim.meta_key LIKE '_product_id'
    AND woim.meta_value NOT IN (SELECT DISTINCT post_parent FROM {$wpdb->prefix}posts WHERE post_type LIKE 'product_variation')))
    GROUP BY woim.meta_value
" );

// Loop though each product
foreach( $results as $result ){
    $product_id   = $result->id;
    $product_name = $result->name;
    $orders_count = $result->count;
    
    // Formatted Output
    echo 'Product: ' . $product_name .' (' . $product_id . ') = ' . $orders_count . '<br>';
}

经过测试，可以正常工作.

Tested and works.

如果您想获取基于今天"的总数，日期，您将在代码中替换以下行:

If you want to get instead the total based on the "today" date, you will replace in the code this line:

AND UNIX_TIMESTAMP(p.post_date) >= (UNIX_TIMESTAMP(NOW()) - (86400))

通过此行:

AND DATE(p.post_date) >= CURDATE()

时区调整，使用

Time zone ajustement using CONVERT_TZ() SQL function
(Where you will adjust '+10:00' the last argument as an offset to match the timezone)

AND DATE(p.post_date) >= DATE(CONVERT_TZ( NOW(),'+00:00','+10:00'))

---

相关的类似答案:

---

Related similar answers:

• 获取订单总购买金额Woocommerce中的一天
• 每个订单项都在Woocommerce上循环

• Get orders total purchases amount for the day in Woocommerce
• Total count for each order item in a loop on Woocommerce

这篇关于获取Woocommerce中每种产品的今天的总订单数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！