Woocommerce echo total retail sales per product

Question

Ive been searching around and cant seem to find anything. Right now I have a query to display my products from my store. I need to be able to also display in the query the total sales of each product and the total retail sales of each product. Im using this to create a leaderboard.

The total sales is easy, thats just the custom field that woocommerce puts in for you. Im having trouble finding an answer for the total retail sales.

I also need to display the total retail sales of the whole store, but nothing seems to be doing the trick on that either.

I had the code on this page working, but then the plugin updated and it stopped functioning. It was just showing 0's and now it is showing nothing.

Heres my query right now, using the code form the page mentioned above.

    <?php $my_query = new WP_Query( array( 'post_type' => 'product', 'posts_per_page' => -1 ) );?>
     <?php if ( $my_query->have_posts() ) : ?> 
     <?php $order_items = apply_filters( 'woocommerce_reports_top_earners_order_items', $wpdb->get_results( "
        SELECT order_item_meta_2.meta_value as product_id, SUM( order_item_meta.meta_value ) as line_total 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->prefix}woocommerce_order_itemmeta as order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id
        LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.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   posts.post_type     = 'shop_order'
        AND     posts.post_status   = 'publish'
        AND     tax.taxonomy        = 'shop_order_status'
        AND     term.slug           IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
        AND     order_items.order_item_type = 'line_item'
        AND     order_item_meta.meta_key = '_line_total'
        AND     order_item_meta_2.meta_key = '_product_id'
        GROUP BY order_item_meta_2.meta_value
    " )); ?>
     <?php while($my_query->have_posts()): $my_query->the_post(); ?>



    <div id="<?php $totalDonations = 0;
 $Charities = array($post->ID);
//Gather Total for Individual Items
foreach ($order_items as $item) {

    if (in_array($item->product_id, $Charities)) {

        $totalDonations = $item->line_total + $totalDonations;


    }

} echo $totalDonations; ?>" class="sort_by_total">
                        <div class="one_half">
                            <li><span><?php the_title(); ?></span></li>
                        </div>
                        <div class="one_quarter">
                            <p><?php echo get_post_meta($post->ID, 'total_sales', true); ?></p>
                        </div>
                        <div class="one_quarter last">
                            <p>$<?php echo $totalDonations; ?></p>
                        </div>
                        <div class="clear"></div>
                    <div class="divider"></div> 
                        </div>
                    <?php endwhile; ?>

                    <?php else : ?>

                    <?php endif; ?>
                    <?php wp_reset_query();?>

Once they are displayed, I'm using the total retail sales as an id for each div, and ordering the leaderboard with jquery based off of that. Thats why it's called twice in the code.

Any help is appreciated. Thanks

EDIT: I have been checking the error messages, and I was getting: PHP Fatal error: Using $this when not in object context on line 33 Also got the same error for line 66. Line 33 is the line that starts with <?php $order_items = apply_filters( and line 66 is the line that starts if (in_array($item->product_id, $Charities)) {

EDIT EDIT: Now Im not getting any error messages.

Answer 1

The WooCommerce 2.2 dashboard widget is using the following code to calculate the retail sales over the past month:

    // Sales
    $query            = array();
    $query['fields']  = "SELECT SUM( postmeta.meta_value ) FROM {$wpdb->posts} as posts";
    $query['join']    = "INNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id ";
    $query['where']   = "WHERE posts.post_type IN ( '" . implode( "','", wc_get_order_types( 'reports' ) ) . "' ) ";
    $query['where']  .= "AND posts.post_status IN ( 'wc-" . implode( "','wc-", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "' ) ";
    $query['where']  .= "AND postmeta.meta_key   = '_order_total' ";
    $query['where']  .= "AND posts.post_date >= '" . date( 'Y-m-01', current_time( 'timestamp' ) ) . "' ";
    $query['where']  .= "AND posts.post_date <= '" . date( 'Y-m-d H:i:s', current_time( 'timestamp' ) ) . "' ";

    $sales = $wpdb->get_var( implode( ' ', apply_filters( 'woocommerce_dashboard_status_widget_sales_query', $query ) ) );

then later on displaying it with :

wc_price( $sales );

Because the dates are being restricted in the last 2 lines of the $query['where'] string, I presume those could be deleted if you wanted to get the total sales of all time.

    // All-time Sales for entire store
    $query            = array();
    $query['fields']  = "SELECT SUM( postmeta.meta_value ) FROM {$wpdb->posts} as posts";
    $query['join']    = "INNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id ";
    $query['where']   = "WHERE posts.post_type IN ( '" . implode( "','", wc_get_order_types( 'reports' ) ) . "' ) ";
    $query['where']  .= "AND posts.post_status IN ( 'wc-" . implode( "','wc-", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "' ) ";
    $query['where']  .= "AND postmeta.meta_key   = '_order_total' ";

    $sales = $wpdb->get_var( implode( ' ', $query ) );

This strikes me as a fairly intensive query, so if it is going to be run all the time you might want to investigate Transients.

I'm less sure about modifying it for the individual product because I'm weak in SQL, but here's an attempt to get the "where" string to specify a product's ID.

    // All-time Sales for specific product, maybe
    $product_id = 10;
    $query            = array();
    $query['fields']  = "SELECT SUM( postmeta.meta_value ) FROM {$wpdb->posts} as posts";
    $query['join']    = "INNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id ";
    $query['where']   = sprintf( "WHERE posts.ID = %n", $product_id );
    $query['where']  .= "AND posts.post_status IN ( 'wc-" . implode( "','wc-", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "' ) ";
    $query['where']  .= "AND postmeta.meta_key   = '_order_total' ";

    $sales = $wpdb->get_var( implode( ' ', $query ) );