Diego

For the benefit of anyone who reads this conversation, we are currently distributing an update of our Aelia Currency Switcher that includes a safeguard to prevent the data duplication. The update server rolls out new versions in batches, if the update is not visible it will become available shortly.

Just a quick update for the readers to inform that we found the quirk that causes the data duplication. It’s actually linked to the WooCommerce core. We have a piece of logic that changed slightly. Here’s an example of what changed, simplified for easier understanding:

add_action('woocommerce_after_order_object_save', function(WC_Order $order) {
  $exchange_rate = get_exchange_rate();
  $order->update_meta_data('_order_total_base_currency', $order->get_total() * $exchange_rate);
  $order->update_meta_data('_order_total_tax_base_currency', $order->get_total_tax() * $exchange_rate);
  // Save more meta, converting order properties

   // Save the exchange rate last
  $order->update_meta_data('_base_currency_exchange_rate', $exchange_rate);
  $order->save_meta_data();
});

To this:

add_action('woocommerce_after_order_object_save', function(WC_Order $order) {
  $exchange_rate = get_exchange_rate();
   // Save the exchange rate first
  $order->update_meta_data('_base_currency_exchange_rate', $exchange_rate);

  $order->update_meta_data('_order_total_base_currency', $order->get_total() * $exchange_rate);
  $order->update_meta_data('_order_total_tax_base_currency', $order->get_total_tax() * $exchange_rate);
  // Save more meta, converting order properties

  $order->save_meta_data();
});

For reasons that I can’t explain, if action woocommerce_after_order_object_save is triggered more than once, the behaviour will be different in the two cases:

1. In the first case, the call to $order->update_meta_data('_base_currency_exchange_rate', $exchange_rate); adds the meta the first time and updates it the second time.
2. In the second case, the call to $order->update_meta_data('_base_currency_exchange_rate', $exchange_rate); adds the meta twice, resulting in data duplication.

We couldn’t figure out the reason behind this inconsistent behaviour. The only speculation, which hasn’t been proven, is that calling the WC_Order::get_total(), WC_Order::get_total_tax() and so on causes the custom meta set by WC_Order::update_meta_data() to be “forgotten”, so to speak, despite the call to WC_Order::save_meta_data(). I’m aware that it doesn’t make much sense, but the test results are consistent. Saving the exchange rate last doesn’t cause any duplication, for some reason.

We’re now implementing a safeguard, so that the meta won’t be duplicated despite this unexpected behaviour.

Additional note
So far, we’ve only noticed this behaviour on a site with the HPOS feature disabled, where the orders are stored in the wp_posts table. With the HPOS feature enabled, this doesn’t seem to happen. However, we will run more tests to check if that actually makes a difference.

• This reply was modified 1 year, 7 months ago by Diego. Reason: Added note about HPOS

For the benefit of anyone else who’s reading, the Aelia Currency Switcher doesn’t change how the Analytics works. It just adds a JOIN to the query, to multiply the order amounts by an exchange rate, preventing order totals in different currencies from being added together. Out of the box, the Analytics could calculate a grand total as in “100 USD + 100 EUR + 100 GBP = 300 USD”. By adding the exchange rate, the calculation becomes “100 USD + 100 EUR x <USD exchange rate> + 100 GBP x <USD exchange rate>”. That’s the only difference between the “vanilla” calculation and the multi-currency one.

There haven’t been any changes in recent updates of the Currency Switcher, and, based on our tests, the recent changes in the Analytics don’t seem to affect the existing integration logic, either. The most likely cause of the issue is the presence of duplicate data. There must be only one exchange rate per order, but if more are present, the JOIN would match and return multiple rows. That would explain the discrepancy:

1. With the Currency Switcher enabled, the JOIN to fetch the exchange rate for each order matches all the duplicate rows. The affected orders are processed multiple times.
2. With the Currency Switcher disabled, the JOIN to fetch the exchange rate is no longer there, and the duplicate rows don’t have any effect.

We’re now assisting the customer to verify this hypothesis, find the duplicate data and clean it up. That should be sufficient to fix the calculations.

I’m the author of the Aelia plugin mentioned, I’ve been asked to look into this. Based on what we have seen on the site, that’s not an issue on our side. However, the root cause does seem to be the one described:

It would seem there is a disconnect between the currency provided when the JS script is loaded, and what’s being used server side when API requests are made

This is a slightly imprecise statement, but it’s in the right direction. In a multi-currency environment, the functions get_woocommerce_currency() and WC_Order::get_currency() may or may not return the same value. The former returns the active currency at a given time, which may change depending on the context, whereas WC_Order::get_currency() always returns the currency in which an order is placed. The two are not the same thing, and can’t be used interchangeably.

In the specific case reported by @henrybaum , the site is using multiple currencies, and it’s configured so that when a customer chooses a country at checkout, the currency in which the order is being placed changes as well. For example, a customers choosing US will see USD, a customer choosing UK will see GBP, and so on. Due to that, the following can happen:

1. When the checkout page is loaded, the currency is USD. Calling get_woocommerce_currency() returns USD.
2. The customer changes the country to France. WooCommerce triggers a “refresh order” event, which is a fetch/Ajax call (i.e. it doesn’t refresh the page). The call passes the newly selected country (Spain) and that changes the currency to EUR.
3. The customer places the order. The order is placed in EUR, as it should be.

The “disconnect” between the currency on page load and the one after the checkout refresh is not due to an error, it’s due how the system works. Quite simply, at the time of a “page load” event it’s not possible to determine with certainty in which currency an order will be placed. Fetching the currency at that time could lead to a discrepancy when the checkout is actually started.

Suggested solution

To address the issue during the payment process, the currency to be used for payments should only be fetched from the order when it’s placed. That value never changes (our Currency Switcher doesn’t even take part into the payment process, and never alter existing orders). If you need to build a URL for the PayPal SDK, then the currency should be stored into a variable and added to the URL as the second to last step, just before calling the PayPal service.

For the sake of clarity, this behaviour is nothing new. The currency selection has been working this way for at least a decade. The fix I described is something we recommended to other developers, and it should be sufficient to prevent the error.