How are partial refunds stored in the WooCommerce Database?

Question

I have a simple backend management tool written in PHP that pulls orders from the WooCommerce database and presents them to our kitchen for processing deliveries.

I have always been able to highlight a table row that had a status other than 'wc-completed' to handle any additional processing that might be needed. However, it was brought to my attention today that one particular order had been refunded for one line item. A partial refund does not change the order status, so I need to pull the refund info from another table in the database.

I checked all the tables I know of related to order processing - wp_posts, wp_postmeta, wp_woocommerce_order_items and wp_woocommerce_order_itemmeta, but no sign of any refund information. In fact, the itemmeta contained in the last table doesn't even adjust the quantity, making my reports incorrect for partial orders like this.

When you view the order summary in the woocommerce backend, it shows a quantity of 1 for this particular item, and then a -1 right below it. I assume based on this, that somewhere in the database there are refund records associated with the orders, but I can't seem to find it. Even the order total in wp_postmeta doesn't reflect the partial refund.

Does anyone know where this refund data is stored in the wordpress database?

Thanks.

Answer 1

Partial refund orders are stored in database like normal ones: In wp_posts table with a 'post_type' = 'shop_order_refund' and a 'post_parent' = order_ID (number), where the order_ID is the reference to the original 'shop_order'.

To find out which one of this refunds are partial, you need the _refund_amount amount value that you can find under wp_post_meta for this 'refund_order' and his corresponding 'shop_order' with the _order_total value too:

if ( 'refund_order' => _refund_amount ) != ( 'shop_order' => _order_total ) : then it's partial.

if ( 'refund_order' => _refund_amount ) == ( 'shop_order' => _order_total ) : then it's normal (not partial).

Notes:

• You can have one or many partial refunds for one order.
• There is for refund orders an _order_total item in wp_postmeta table with a negative value that is always reflecting the positive value of _refund_amount item. This _order_total value has nothing to do with the related parent 'shop_order' => '_order_total' value.

  It's the opposite (negative) value of the related parent 'shop_order' => '_order_total' value only when refund order IS NOT PARTIAL.

• In wp_woocommerce_order_items and wp_woocommerce_order_itemmeta tables, there is related detailed data concerning products in orders and refunded orders...