Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * 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
Add Comment
Please, Sign In to add comment