php - Woocommerce 回显每种产品的总零售额

Question

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

总销售额很简单，这只是 woocommerce 为您添加的自定义字段。我很难找到零售总额的答案。

我还需要显示整个商店的零售总额，但似乎也没有任何效果。

我的代码是 this page工作，但随后插件更新并停止运行。之前只显示 0，现在什么也没有显示。

这是我现在的查询，使用上面提到的页面中的代码。

    <?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 对排行榜进行排序。这就是为什么它在代码中被调用两次的原因。

感谢任何帮助。谢谢

编辑:我一直在检查错误消息，我得到: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)) { 开始的行

编辑编辑:现在我没有收到任何错误消息。

Answer 1

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

    // 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'] 字符串的最后两行中，因此我认为如果您想获得所有时间的总销售额，可以删除这些日期。

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

这对我来说是一个相当密集的查询，因此如果它要一直运行，您可能需要研究 transient 。

我不太确定是否要针对单个产品修改它，因为我的 SQL 能力较弱，但这里尝试获取“where”字符串来指定产品的 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 ) );