問題描述

我正在尋找一個代碼片段來獲取今天每種產品的總銷售額，以便我可以在我的主題 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

這篇關于獲取今天 Woocommerce 中每種產品的總訂單數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！