如何检索使用特定优惠券的 WooCommerce 订单列表? [英] How to retrieve a list of WooCommerce orders which use a particular coupon?

问题描述

我正在尝试编写一个函数来检索使用指定优惠券代码并在指定日期范围内的 WooCommerce 订单列表，然后对应用于这些订单的总折扣求和.

经过一些谷歌搜索后，我觉得我应该使用类似的东西

$customer_orders = get_posts( array('数字帖子' =>-1,'meta_key' =>???，'元值' =>$CouponToSearchFor,'post_type' =>wc_get_order_types(),'post_status' =>array_keys( wc_get_order_statuses() ),) );

我试过了:

'meta_key' =>'优惠券''meta_key' =>'shop_coupon''meta_key' =>'_优惠券'

但这些都不起作用.我如何才能找出哪些 meta_key/meta_value 术语可以满足我的需求?

此外，我认为 meta_query 可用于执行日期过滤，作为此 get_posts() 查询的一部分，对吗?

解决方案

您的代码不起作用，因为默认情况下 WooCommerce 不存储wp_postmeta 表中使用的优惠券代码.它存储在wp_woocommerce_order_items 表，在 order_item_type => 下优惠券和order_item_name =>你的代码.

您必须首先获得所有订单 ID，然后您必须循环它以获得所需的总数、税金或折扣.

代码如下:

function wh_getOrderbyCouponCode($coupon_code, $start_date, $end_date) {全球 $wpdb;$return_array = [];$total_discount = 0;$query = "选择p.ID AS order_id从{$wpdb->prefix}posts AS pINNER JOIN {$wpdb->prefix}woocommerce_order_items AS woi ON p.ID = woi.order_id在哪里p.post_type = 'shop_order' ANDp.post_status IN ('" . implode("','", array_keys(wc_get_order_statuses())) ."') ANDwoi.order_item_type = '优惠券' ANDwoi.order_item_name = '" . $coupon_code . "' ANDDATE(p.post_date) BETWEEN '" . $start_date . "' AND '" . $end_date . "';";$orders = $wpdb->get_results($query);如果(！空($订单)){$dp = ( isset($filter['dp']) ? intval($filter['dp']) : 2 );//遍历所有order_idforeach ($orders as $key => $order) {$order_id = $order->order_id;//获取订单对象$objOrder = wc_get_order($order_id);$return_array[$key]['order_id'] = $order_id;$return_array[$key]['total'] = wc_format_decimal($objOrder->get_total(), $dp);$return_array[$key]['total_discount'] = wc_format_decimal($objOrder->get_total_discount(), $dp);$total_discount += $return_array[$key]['total_discount'];}//echo '
';//print_r($return_array);}$return_array['full_discount'] = $total_discount;返回 $return_array;}
代码位于活动子主题(或主题)的 function.php 文件中.或者也可以在任何插件 php 文件中. 
用法
$orders = wh_getOrderbyCouponCode('my_code', '2016-09-17', '2016-10-07');echo '总折扣:'.$orders['full_discount'];//print_r($orders);
请注意:
所有日期均采用 YYYY-MM-DD 格式.
print_r(array_keys(wc_get_order_statuses())); 将输出如下内容:
数组([0] =>wc-待定[1] =>wc-处理[4] =>wc-on-hold[5] =>厕所完成[6] =>厕所取消[7] =>wc-refunded[8] =>厕所失败)
代码已经过测试并且可以正常工作.
希望这有帮助！
I'm trying to write a function that retrieves a list of WooCommerce orders which use a specified coupon code and are in a specified date range, then sums the total discounts applied to those orders. 

After a little googling I feel like I should be using something like
$customer_orders = get_posts( array(
    'numberposts' => -1,
    'meta_key'    => ???,
    'meta_value'  => $CouponToSearchFor,
    'post_type'   => wc_get_order_types(),
    'post_status' => array_keys( wc_get_order_statuses() ),
) ); 
I've tried:
'meta_key' => 'coupon'
'meta_key' => 'shop_coupon'
'meta_key' => '_coupon'
But none of those work. How can I find out which meta_key/meta_value terms will give me what I need?

Additionally I think meta_query can be used to perform the date filtering as part of this get_posts() query, is that correct? 
解决方案

  
Your code is not working because by default WooCommerce does't store
  used coupon code in wp_postmeta table. It stores in
  wp_woocommerce_order_items table, under order_item_type =>
  coupon and order_item_name => YOUR_CODE.
You have to first get all the Order ID(s), then you have to loop it to get the desired total, or tax or discount.

Here is the code:
function wh_getOrderbyCouponCode($coupon_code, $start_date, $end_date) {
    global $wpdb;
    $return_array = [];
    $total_discount = 0;

    $query = "SELECT
        p.ID AS order_id
        FROM
        {$wpdb->prefix}posts AS p
        INNER JOIN {$wpdb->prefix}woocommerce_order_items AS woi ON p.ID = woi.order_id
        WHERE
        p.post_type = 'shop_order' AND
        p.post_status IN ('" . implode("','", array_keys(wc_get_order_statuses())) . "') AND
        woi.order_item_type = 'coupon' AND
        woi.order_item_name = '" . $coupon_code . "' AND
        DATE(p.post_date) BETWEEN '" . $start_date . "' AND '" . $end_date . "';";

    $orders = $wpdb->get_results($query);

    if (!empty($orders)) {
        $dp = ( isset($filter['dp']) ? intval($filter['dp']) : 2 );
        //looping throught all the order_id
        foreach ($orders as $key => $order) {
            $order_id = $order->order_id;
            //getting order object
            $objOrder = wc_get_order($order_id);

            $return_array[$key]['order_id'] = $order_id;
            $return_array[$key]['total'] = wc_format_decimal($objOrder->get_total(), $dp);
            $return_array[$key]['total_discount'] = wc_format_decimal($objOrder->get_total_discount(), $dp);
            $total_discount += $return_array[$key]['total_discount'];
        }
//        echo '<pre>';
//        print_r($return_array);
    }
    $return_array['full_discount'] = $total_discount;
    return $return_array;
}
Code goes in function.php file of your active child theme (or theme). Or also in any plugin php files. 

USAGE
$orders = wh_getOrderbyCouponCode('my_code', '2016-09-17', '2016-10-07');
echo 'Total Discount : ' . $orders['full_discount'];
//print_r($orders);
Please Note: 

All dates are in YYYY-MM-DD format.

print_r(array_keys(wc_get_order_statuses())); will output something like this:
Array
(
    [0] => wc-pending
    [1] => wc-processing
    [4] => wc-on-hold
    [5] => wc-completed
    [6] => wc-cancelled
    [7] => wc-refunded
    [8] => wc-failed
)
Code is tested and works.

Hope this helps!

                        
这篇关于如何检索使用特定优惠券的 WooCommerce 订单列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！