在WooCommerce短码中显示产品id的总收入

Question

我试图通过短代码显示从WooCommerce给定的产品ID的总收入。我有代码到目前为止的代码，但我似乎不能通过选择个人ID让它正确工作，目前它正在显示它为整个商店。

function get_total_sales( $atts ) {
$atts = shortcode_atts( array(
'id' => ''), $atts );
global $wpdb;
$order_totals = apply_filters( 'woocommerce_reports_sales_overview_order_totals', $wpdb->get_row( "
SELECT SUM(meta.meta_value), ['id'], AS total_sales, COUNT(posts.ID) AS total_orders FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
WHERE   meta.meta_key       = '_order_total'
AND     posts.post_type     = 'shop_order'
AND     posts.post_status   IN ( 'wc-" . implode( "','wc-", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed','on-hold', 'processing' ) ) ) . "' )
" ) );
return $order_totals->total_sales;
}
add_shortcode('sales', 'get_total_sales');

我正在努力寻找如何使其特定于ID，并试图实现ID属性，但它似乎被忽略了。

提前感谢!

Answer 1

如果您只想获得所有总销售额，那么您可以使用元数据。此方法不按订单状态检查。

function get_total_sales_by_product_id( $atts ) {
return get_post_meta($atts['id'], 'total_sales', true);
}
add_shortcode('sales', 'get_total_sales_by_product_id');

按订单状态列表获取总销售额，请执行

function get_total_sales_by_product_id( $atts ){
$atts = shortcode_atts( array(
'id' => ''), $atts );

$product_id = $atts['id'];
if(empty($product_id)) return;
//Add remove order statuses 
$order_status = array( 'wc-completed', 'wc-processing' );
global $wpdb;

$order_ids = $wpdb->get_col("
SELECT order_items.order_id
FROM {$wpdb->prefix}woocommerce_order_items as order_items
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
WHERE posts.post_type = 'shop_order'
AND posts.post_status IN ( '" . implode( "','", $order_status ) . "' )
AND order_items.order_item_type = 'line_item'
AND order_item_meta.meta_key = '_product_id'
AND order_item_meta.meta_value = '$product_id'
");

$unique_order_ids = array_unique($order_ids);

$total_sales = 0;
foreach ($unique_order_ids as $order_id) {
$order = wc_get_order($order_id);
foreach ($order->get_items() as $item_key => $item ) {  
if ($item->get_product()->get_id() == $product_id) {
$total_sales = $total_sales +  $item->get_quantity();
}
}
}

return $total_sales;
}
add_shortcode('sales', 'get_total_sales_by_product_id');