Display total revenue of a product id in WooCommerce shortcode-CodePudding

I am trying to display the total revenue of a given product ID from WooCommerce via a shortcode. I have code this far with the code however I cannot seem to get it to work correctly by selecting the individual ID, at the moment it is showing it for the whole store.

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

I am struggling to find how I can made it specific to the ID and have tried to implement an ID attribute, but it seems to be ignored.

Thank you in advance!

CodePudding user response：

If you just want to get all total sales there is a meta that you can use. This method doesnt check by order statuses.

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

To get total sales by list of order statuses try this

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