在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');