Woocommerce反映了每种产品的零售总额 [英] Woocommerce echo total retail sales per product

问题描述

我一直在搜索，似乎找不到任何东西.现在，我有一个查询要显示我商店中的产品.我还需要能够在查询中显示每个产品的总销售额和每个产品的总零售额.我用它来创建排行榜.

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.

总销售额很容易，这就是woocommerce为您提供的自定义字段.我无法找到零售总额的答案.

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.

我在此页面上有代码正常运行，但随后插件进行了更新，并且停止运行.它只是显示0，而现在却什么也不显示.

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();?>

一旦显示它们，我就将零售总额用作每个div的ID，并以此为基础通过jquery订购排行榜.这就是为什么它在代码中被两次调用的原因.

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.

感谢您的帮助.谢谢

我一直在检查错误消息，并且得到:PHP Fatal error: Using $this when not in object context on line 33第66行也遇到了相同的错误.第33行是以<?php $order_items = apply_filters(开头的行，第66行是以<if (in_array($item->product_id, $Charities)) {

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 Now Im not getting any error messages.

推荐答案

WooCommerce 2.2仪表板小部件正在使用以下代码来计算过去一个月的零售额:

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

然后再用:

wc_price( $sales );

由于日期被限制在$query['where']字符串的最后两行中，因此，如果您想获得所有时间的总销售额，我认为可以删除这些日期.

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

这让我感到震惊，因为它是一个相当密集的查询，因此，如果要一直运行该查询，则可能需要调查Transients.

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.

由于对SQL的了解较弱，因此我不太确定是否要针对单个产品进行修改，但这是尝试获取"where"字符串以指定产品ID的尝试.

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

这篇关于Woocommerce反映了每种产品的零售总额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！