Aelia - Support

The reports generated by a "plain" WooCommerce installation will most likely show different figures from the ones generated when the Currency Switcher is active. This is because WooCommerce reports are not aware of orders currency, and simply calculate a grand total without any conversion. 

Example

Supposing that your shop's base currency is USD, and you had sales for 100 USD, 100 EUR and 100 GBP.

• With the Currency Switcher disabled, WooCommerce Sales Reports will show absolute totals, calculated as 100 + 100 + 100 = 300 USD. This figure is, of course, incorrect, as it's a mixup of amounts in different currencies.
• With the Currency Switcher enabled, the same report you activate the Currency Switcher, the same reports will show a total of 354.97 USD, i.e. 100 USD + 100 EUR converted to USD + 100 GBP converted to USD. This is the correct total.

1. What to do if the sales report show incorrect totals

If the totals don't seem correct, even after taking into account the conversion, the reason could vary depending on the specific issue.

1.2 The totals with the Currency Switcher active are lower than expected
If the figures are lower with the Currency Switcher active, then the processing of past orders might not have been completed successfully. You can trigger the processing again by following the instructions in the following article: Sales report show lower amounts when the Currency Switcher is active. This will re-process past orders all the way back to the beginning of last year, ensuring that the report data is in place.

-- Find all the order with duplicate meta data
-- IMPORTANT
-- Replace the table prefix (in the example, "wp_", with the appropriate one.
select
  post_id
  ,meta_key
  ,count(meta_key) as value_count
from 
  wp_postmeta
where
  (meta_key like '%_base_currency%')
group by
  post_id,
  meta_key
having
  value_count > 1

Step 2 -  Remove duplicate order meta

-- Delete the duplicate meta rows
-- IMPORTANT
-- Replace the table prefix (in the example, "wp_", with the appropriate one.
DELETE FROM wp_postmeta
WHERE meta_id IN
(
  SELECT meta_id
  FROM
  (
    -- This query will return a list of all the meta rows that are duplicates and
    -- that should be removed
    SELECT
      PM.meta_id
      ,PM.meta_key
      ,PM.post_id
      ,meta_duplicates.meta_id_to_keep
      ,meta_duplicates.duplicates_count
    FROM
      wp_postmeta PM
      JOIN
      (
        -- This query returns the meta keys with duplicates, as well as the ID of
        -- the first, or last meta that was added
        -- Use MIN() to keep the first meta that was added (i.e. when the
        -- duplicates, which were added later, are incorrect,
        -- Use MAX() to keep the last meta (i.e. when the duplicate was added
        -- deliberately, and the original one is now obsolete.
        SELECT
          post_id
          ,meta_key
          ,min(meta_id) as meta_id_to_keep
          ,count(meta_key) as duplicates_count
        FROM 
          wp_postmeta
        WHERE
          (meta_key like '%_base_currency%')
        GROUP BY
          post_id,
          meta_key
        HAVING
          duplicates_count > 1
      ) AS meta_duplicates ON
      (meta_duplicates.post_id = PM.post_id) AND
      (meta_duplicates.meta_key = PM.meta_key)
    -- This clause filters out the meta rows that we want to keep
    WHERE
      -- If you used MIN() above, you should check for "(PM.meta_id > meta_duplicates.meta_id_to_keep)"
      -- If you used MAX() above, you should check for "(PM.meta_id < meta_duplicates.meta_id_to_keep)"
      (PM.meta_id > meta_duplicates.meta_id_to_keep)
  ) AS duplicates_to_remove
)

Step 3 - Find duplicate order item meta

-- Find all the order with duplicate order item meta data
-- IMPORTANT
-- Replace the table prefix (in the example, "wp_", with the appropriate one.
SELECT
  order_item_id
  ,meta_key ,count(meta_key) as value_count
FROM
  wp_woocommerce_order_itemmeta 
WHERE
  (meta_key like '%_base_currency%') 
GROUP BY
  order_item_id
  ,meta_key
HAVING value_count > 1

-- Delete the duplicate meta rows
-- IMPORTANT
-- Replace the table prefix (in the example, "wp_", with the appropriate one.
DELETE FROM wp_woocommerce_order_itemmeta
WHERE meta_id IN
(
  SELECT meta_id
  FROM
  (
    -- This query will return a list of all the meta rows that are duplicates and
    -- that should be removed
    SELECT
      OIM.meta_id
      ,OIM.meta_key
      ,OIM.order_item_id
      ,meta_duplicates.meta_id_to_keep
      ,meta_duplicates.duplicates_count
    FROM
      wp_woocommerce_order_itemmeta OIM
      JOIN
      (
        -- This query returns the meta keys with duplicates, as well as the ID of
        -- the first, or last meta that was added
        -- Use MIN() to keep the first meta that was added (i.e. when the
        -- duplicates, which were added later, are incorrect,
        -- Use MAX() to keep the last meta (i.e. when the duplicate was added
        -- deliberately, and the original one is now obsolete.
        SELECT
          order_item_id
          ,meta_key
          ,min(meta_id) as meta_id_to_keep
          ,count(meta_key) as duplicates_count
        FROM 
          wp_woocommerce_order_itemmeta
        WHERE
          (meta_key like '%_base_currency%')
        GROUP BY
          order_item_id,
          meta_key
        HAVING
          duplicates_count > 1
      ) AS meta_duplicates ON
      (meta_duplicates.order_item_id = OIM.order_item_id) AND
      (meta_duplicates.meta_key = OIM.meta_key)
    -- This clause filters out the meta rows that we want to keep
    WHERE
      -- If you used MIN() above, you should check for "(OIM.meta_id > meta_duplicates.meta_id_to_keep)"
      -- If you used MAX() above, you should check for "(OIM.meta_id < meta_duplicates.meta_id_to_keep)"
      (OIM.meta_id > meta_duplicates.meta_id_to_keep)
  ) AS duplicates_to_remove
)

You can purchase the Currency Switcher from our online shop.