获取今天 Woocommerce 中每种产品的总订单数
我正在寻找一个代码片段来获取今天每种产品的总销售额,以便我可以在我的主题 functions.php 文件中使用它.
输出应该是这样的(每件商品的总销售额):
Product xxx = 25 个订单产品 yyy = 18 个订单产品 zzz = 8 个订单
解决方案 这可以通过以下非常简单的 SQL 查询和 foreach 循环来完成.
这将为您提供过去 24 小时内按产品计数的产品(和产品变体,但不是父变量产品)的产品列表:
全局$wpdb;$results = $wpdb->get_results( "SELECT DISTINCT woim.meta_value 作为id,COUNT(woi.order_id) 作为计数,woi.order_item_name 作为名称FROM {$wpdb->prefix}woocommerce_order_itemmeta as woimINNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON woi.order_item_id = woim.order_item_idINNER JOIN {$wpdb->prefix}posts as p ON p.ID = woi.order_idWHERE 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)或 (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')))按 woim.meta_value 分组");//遍历每个产品foreach( $results 作为 $result ){$product_id = $result->id;$product_name = $result->name;$orders_count = $result->count;//格式化输出回声'产品:'.$product_name .'(' . $product_id . ') = ' .$orders_count .'
';}
经过测试并有效.
如果您想获得基于今天"的总数日期,您将在代码中替换这一行:
AND UNIX_TIMESTAMP(p.post_date) >= (UNIX_TIMESTAMP(NOW()) - (86400))
通过这一行:
AND DATE(p.post_date) >= CURDATE()
<块引用>
时区调整使用CONVERT_TZ()
SQL 函数
(您将在哪里调整 '+10:00'
最后一个参数作为偏移量以匹配时区)
AND DATE(p.post_date) >= DATE(CONVERT_TZ( NOW(),'+00:00','+10:00'))
相关类似答案:
- 获取订单总购买金额Woocommerce 的一天
- 总计数Woocommerce 上循环中的每个订单项
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.
Output should be like this (Total Sales Per Item):
Product xxx = 25 orders
Product yyy = 18 orders
Product zzz = 8 orders
解决方案
This can be done with the following very light SQL query and a foreach loop.
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))
by this line:
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:
- Get orders total purchases amount for the day in Woocommerce
- Total count for each order item in a loop on Woocommerce
相关文章