get refund transaction data with product metadata woocommerce

3 comments

1. First of all +1 for your question. From WooCommerce 2.2 onwards, WooCommerce directly allows refunds if your payment gateway allows. Refunding can be done in 2 ways: Automatically via Payment Gateway or Manually. Please refer the page: http://docs.woothemes.com/document/woocommerce-refunds/ for more information. If you want to get the refund details please use the methods listed in this page: http://docs.woothemes.com/wc-apidocs/class-WC_Order.html.

   Log in to Reply

2. I haven’t found a proper way to do this using built in WooCommerce classes (reports by day is…close). I dug through queries used by WooCommerce to pull report data and came up with the following to get refunded order items:

   <?php
       global $wpdb;
   
       // TODO: time zones, better ranges, etc.
   
       $date_start = new DateTime('-7 days');
       $date_end   = new DateTime('now');
   
       $sql_reports_refunded = <<<SQL
   
             SELECT
               product_id.meta_value        AS product_id,
               variation_id.meta_value      AS variation_id,
               order_items.order_item_name  AS product_name,
               product_sku.meta_value       AS product_SKU,
               variation_sku.meta_value     AS variation_SKU,
               SUM(quantity.meta_value)     AS quantity
   
             FROM {$wpdb->prefix}posts AS posts
   
             LEFT JOIN {$wpdb->prefix}woocommerce_order_items     AS order_items    ON posts.ID = order_items.order_id
             LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta  AS quantity       ON order_items.order_item_id = quantity.order_item_id      AND quantity.meta_key      = '_qty'
             LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta  AS variation_id   ON order_items.order_item_id = variation_id.order_item_id  AND variation_id.meta_key  = '_variation_id'
             LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta  AS product_id     ON order_items.order_item_id = product_id.order_item_id    AND product_id.meta_key    = '_product_id'
             LEFT JOIN {$wpdb->prefix}postmeta                    AS product_sku    ON product_id.meta_value     = product_sku.post_id         AND product_sku.meta_key   = '_sku'
             LEFT JOIN {$wpdb->prefix}postmeta                    AS variation_sku  ON variation_id.meta_value   = variation_sku.post_id       AND variation_sku.meta_key = '_sku'
   
             WHERE posts.post_status IN ('wc-completed', 'wc-refunded')
               AND posts.post_type IN ('shop_order_refund')
               AND posts.post_date >= '{$date_start->format('Y-m-d')}'
               AND posts.post_date <  '{$date_end->format('Y-m-d')}'
               AND order_items.order_item_type = 'line_item'
               AND order_items.order_id IS NOT NULL
   
             GROUP BY product_id, variation_id
             ORDER BY quantity DESC, product_name ASC
   
       SQL;
   
       return $wpdb->get_results($sql_reports_refunded);
   ?>
   

   It’s very close to what WC is using to grab data for various reports. As I write this I would suggest that if you need custom reporting from WooCommerce you will likely have to do things yourself via custom queries or get_order_report_data in WC_Admin_Report.

   Log in to Reply

3. I know this is almost a couple years old, but it’s the only discussion I found regarding obtaining refund data. Anoop’s answer is correct, but to better clarify, the refund methods from WC_Order should return what’s neededL https://docs.woothemes.com/wc-apidocs/class-WC_Order.html

   Example: I’m building a list of “attendees” to an event-style product. The number of attendees looks at purchased orders’ quantities of that product/event. Refunding by way of quantity doesn’t actually adjust the order’s quantity, but rather the quantity of items refunded are stored here:

   WC_Order()->get_item_count_refunded();
   

   General refund details can be obtained with:

   WC_Order()->get_refunds();
   

   Log in to Reply