[DEV] SagePay Multiple currency gateway

[Martin]

After upgrading to 6.1.1 I've found that the orders table has a few columns that may well allow me to hack something into shape sooner rather than later.

ordcurrencyid
orddefaultcurrencyid
ordcurrencyexchangerate

It seems these were there before but until now it's never occurred to me that I could hack things slightly differently to allow the use of my foreign currency gateway accounts while using the information in those fields to record it all...

What I'm thinking of doing is re-writing the Protx checkout module so that it reads the currency preferred by the customer, the current exchange rate and then adjusts the figures, appropriate account used, etc... to use the amount the customer has been seeing as they've shopped, pass it on to the appropriate foreign account gateway, ensure it gets that same amount back and then stores it all as if it was a normal transaction.

The only way this could be any better is if SagePay were actually providing their own exchange rates as I suspect there's going to be some minor discrepancies between the ex' rate from the currency module compared to what the gateway will be using. That's about the only fly in the ointment that I can think of as it's going to cause some minor annoyance when it comes to balancing the books..


Notes:
- Obv' check the currency is one we have an account for...
- $targetCurrency = GetCurrency() required to get the currency customer has been browsing in.
- check if the /lib/currency.php is included
- then ConvertPriceToCurrency( $total, $targetCurrency )
- adjust VerifyOrder() to ensure expected total returned is the ex' rated total
- Put a flag in the database (custom field maybe) that indicates the order was paid in currency X so you can easily parse any records if discrepancies show up in your accounts later.

EDIT: Turns out that the way my SagePay and Merchant accounts are setup, I don't need multiple logins because SagePay have all the merchant ID's required under my single vendor account. So, all I need to do is send the adjusted ForEx total and appropriate Currency Code and they then tackle it themselves.

I've also found that RBS provide the ForEx data for my merchant account via this link... so a new currency module might help there.

[Martin]

Ok... stage #1 done... I now have a new currency module that grabs the currency exchange data from the RBS Business Forex page.


I need to query Worldpay/Streamline to find out how the 2.5% surcharge is applied so I can allow for that but other than that, the initial data requirement is pretty much ready to go..

EDIT: ... and done... The 2.5% surcharge is only for terminals and on reflection was a bit daft of me to consider. The customer will pay whatever I send through to SagePay as the chargeable amount. The difference in charging will be what my merchant account provider charge me (as in percentage) which is slightly higher for foreign currency payments.

[Martin]

Stage #2 now done...

The Protx (SagePay) module will now take the payment in the appropriate currency and validate successfully on the store as well.

TODO: Locate the email invoice function and force that to send the foreign currency information as well, as this is still sending default currency values.

[Martin]

TODO: Locate the email invoice function and force that to send the foreign currency information as well, as this is still sending default currency values.

Found it in the lib/orders.php file under function EmailInvoiceToCustomer()

Rather strange that this function completely ignores anything other than the default currency thanks to these lines:

Code: Select all

	// All prices in the emailed invoices will be shown in the default currency of the store
	$defaultCurrency = GetDefaultCurrency();
.....
	$GLOBALS['TotalCost'] = FormatPrice($order_row['total_inc_tax'], false, true, false, $defaultCurrency, true);
.....
		$GLOBALS['ProductPrice'] = FormatPrice($product_row[$itemPriceColumn], false, true, false, $defaultCurrency, true);
		$GLOBALS['ProductTotal'] = FormatPrice($product_row[$itemTotalColumn], false, true, false, $defaultCurrency, true);
.....
		$emailTemplate->assign('value', formatPrice($row['value'], false, true, false, $defaultCurrency, true));

Given that the orders are quoted in the chosen currency when displayed in the orders screen for a customer, not entirely sure why the emailed versions would be different. Inconsistencies like this confuse customers and make for more support issues.


EDIT:
Seems most of the code I need to be looking to use is provided in the includes/classes/class.account.php file although it's pretty vanilla given the work to date.

TODO: Thinking I need to add a DB Table field to indicate that payment has been actually taken in the target currency rather than just converted to the default and then taken... Should make it easier to create a report for when someone wants to consolidate any such payments and/or check for discrepancies in exchange rate, total, etc...


Right job for Tuesday I'm thinking and then this can all go live..

[Martin]

Hmm... Found a small problem in the way the code totals things up that might (well, probably would) cause confusion..

The invoice display system uses a function called getGrandTotal() which totals up an array of sub-totals (shipping, tax, etc..) and then displays the result.

The only problem is that the getGatewayAmount() value (which is the same as $order['total_inc_tax']) is calculated by totalling up the individual products, shipping, tax, etc... and then applying the number/conversion formatting...

This can result in minor discrepancies between what's shown on the actual invoice and what you end up charging the customer by a small amount.

Knowing accounts are a pain in the patookis for this kind of thing, you can bet this means that they'll not like an invoice that doesn't match the actual payment...

So, not quite as "there" as I thought I was but doubtless I'll find a way to tweak it...

[Martin]

Beginning to understand why this is happening now...

It seems there's still a fair amount of duplication going on in this cart code... Stuff for displaying invoices appears to pull in the values one way while emailing invoices is using a different approach... Still some way to go to normalise everything it seems but could be a whole lot worse I guess.

[Martin]

I've found that the discrepancy is pretty much limited to "rare" status so I think I'll probably find a way to code in a pop up that explains the potential before someone heads through to the SagePay gateway so they won't be scared..

Pretty sure too that the discrepancy is usually in the customers favour but so small as to no bother so we'll leave for now.


Ok, in terms of actual coding, it's now done..

Features are as follow:

• Currency module reads Streamline (RBS) Exchange rates used by the merchant account to convert foreign currency payments
• Protx (Sagepay) checkout module heavily rewritten to:
  • Read currency checkout is being viewed in
  • Convert base total to the target currency total and submit to SagePay
  • Convert expected validation value so that sanity checks succeed when submitted foreign currency total has been paid
  • Apply an Order note (staff note) to the order record to indicate foreign currency payment and what total was
  • Flag (added) orderchargedincurrencyid field with the target currencyid
• Email notification module mod'd to allow custom order value (with target currency data/code) in the email body and subject line
• Customers accounts/orders will display invoice in the foreign currency paid
• For currencies that have to be paid in default currency (ie: old method), it will now display the default currency value (grey/italic) as well to indicate actual payment amount
• Printable invoice (as above)
• Email invoices now show proper currency information instead of default currency (ie: brought in line with printable/viewed invoices).

The bottom line is that I now have a system that will allow customers to pay in US Dollars, Euros, Pounds Sterling (default) and any other currencies for which I have a merchant account.

Not too bad for 2 days work!

[Martin]

Unf***ing believable..

Today has been accounts day and this has meant going through all the transactions taken via the ISC store and settlements from Streamline. It was all going well until we reached the end and noticed that every single foreign currency account had lost money on every single transaction (between 5% and 8% on each!)

The circus that has followed just beggars belief as Streamline have been incapable of providing the information on the exchange rate table used, it's source and at what point the exchange rate is applied (ie: is it on day of transaction or day of settlement).

As you all know, from previous posts, Streamline originally told me that it was my business account provider, then they said it was the RBS commercial rate and today it was "Bank of England" rate...

When I pushed for a definitive answer Streamline have been completely incapable of telling me who knows because they don't know who that person/department is.. Absolute ruddy shambles...

[Tony Barnes]

lol - that's pretty damn weak!

Big errors there too, surprised you hadn't picked up on it by now? Many orders affected?

[Martin]

lol - that's pretty damn weak!

You're not wrong... I think the general concensus if that if you're big enough to go multi-currency then you're going to have multiple currency accounts with your business bank and avoid the whole mess completely...

Big errors there too, surprised you hadn't picked up on it by now? Many orders affected?

I think it's karma for not keeping my accounts up to date so a lesson learned on that score but in terms of cost... We're out by over £100...

Ironically, if SagePay hadn't been screwing up various transactions and losing orders to Paypal (or just losing them!) I'd be out even further so ironic save there...