/**
 * Extracts and compares the order data used by WooCommerce and the Aelia Currency Switcher to generate 
 * the sales reports on the traditional reports and the analytics.
 * 
 * DISCLAIMER
 * THE USE OF THIS CODE IS AT YOUR OWN RISK. You remain fully liable for compliance with tax laws.
 * This code is offered free of charge and there is no warranty for it, to the extent permitted by applicable law.
 * Except when otherwise stated in writing the copyright holders and/or other parties provide the program "as is"
 * without warranty of any kind, either expressed or implied, including, but not limited to, the implied warranties of
 * merchantability and fitness for a particular purpose. The entire risk as to the quality and performance of the program
 * is with you. Should the program prove defective, you assume the cost of all necessary servicing, repair or correction.
 *
 * The code is provided as an example and it's not covered by our support service. We won't be able to offer free support
 * in relation to it. Should you need a consultation, or assistance to customise this code, you can contact us to avail
 * of our paid consultation services: https://aelia.co/hire_us 
 */

SELECT
	ORDERS_META1.post_id AS order_id
	,ORDERS_META1.meta_value AS order_total
	,ORDERS_META2.meta_value AS order_total_base_currency
	,ORDERS_META3.meta_value AS exchange_rate
	,(ORDERS_META2.meta_value / ORDERS_META1.meta_value) AS exchange_rate_calculated
	,(ORDERS_META1.meta_value * ORDERS_META3.meta_value) AS order_total_base_currency_calculated
	-- Calculates the difference between the exchange rate stored in the database and the one calculated from
	-- the source data
	,(ORDERS_META2.meta_value / ORDERS_META1.meta_value) - ORDERS_META3.meta_value as exchange_rate_difference
	-- Calculates the difference between the order totals in base currency stored in the database and the one 
	-- calculated using the exchange rate
	,(ORDERS_META1.meta_value * ORDERS_META3.meta_value) - ORDERS_META2.meta_value as order_total_calculated_difference
FROM
	wp_posts ORDERS
LEFT JOIN
	wp_postmeta ORDERS_META1 ON
		(ORDERS_META1.meta_key = '_order_total') AND
		(ORDERS_META1.post_id = ORDERS.ID)
LEFT JOIN
	wp_postmeta ORDERS_META2 ON
		(ORDERS_META2.post_id = ORDERS_META1.post_id) AND
		(ORDERS_META2.meta_key = '_order_total_base_currency')
LEFT JOIN
	wp_postmeta ORDERS_META3 ON
		(ORDERS_META3.post_id = ORDERS_META1.post_id) AND
		(ORDERS_META3.meta_key = '_base_currency_exchange_rate')
WHERE
  ORDERS.post_type = 'shop_order' AND
  ORDERS.post_status IN ('wc-completed', 'wc-processing')
ORDER BY
  ORDERS.ID