The European Central Bank maintains an publicly available online database of exchange rates for the Euro against major currencies. See http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml for the feed.
Here's a bit of code which will fetch that data, look up your main and alternate currency and if both have ECB rates available the Actinic database value will be set to the current rate.
The code only runs on the store top page and only updates the rate if the current rate is more than 5% dfferent from the prior. This saves you from big uploads every day as minor exchange rate changes won't affect the displayed page.
Some diagnostic info is displayed when on the store top page (only on Preview - not on the live site).
Note that Actinic caches the exchange rate value so the rate used on your pages will be the one from the last time Actinic started. If you want to force an update - preview the store top page then close and restart Actinic.
Installation:
Put the following code into your overall layout (either just after the <body ...> tag or just before the </body> tag is best)
Here's a bit of code which will fetch that data, look up your main and alternate currency and if both have ECB rates available the Actinic database value will be set to the current rate.
The code only runs on the store top page and only updates the rate if the current rate is more than 5% dfferent from the prior. This saves you from big uploads every day as minor exchange rate changes won't affect the displayed page.
Some diagnostic info is displayed when on the store top page (only on Preview - not on the live site).
Note that Actinic caches the exchange rate value so the rate used on your pages will be the one from the last time Actinic started. If you want to force an update - preview the store top page then close and restart Actinic.
Installation:
Put the following code into your overall layout (either just after the <body ...> tag or just before the </body> tag is best)
Code:
<actinic:block if="%3cactinic%3avariable%20name%3d%22PageType%22%20%2f%3e%20%3d%3d%20%22Section%22" >
<actinic:block if="%3cactinic%3avariable%20name%3d%22SectionLevel%22%20%2f%3e%20%3d%3d%200">
<actinic:block php="true" >
$percentdifference = 5; // don't change rate unless current outside this range
$showdiags = true; // whether to show diagnostic info (Preview only)
$isPreview = <actinic:variable name="IsPreviewMode" encoding="perl" selectable="false" />;
$connect = odbc_pconnect("ActinicCatalog<actinic:variable name="ECMajorVersion" encoding="perl" selectable="false" />","","", SQL_CUR_USE_ODBC);
// see if we're using an alternate currency - if so, get the existing rate from the DB
$query = "SELECT bDisplayEuroPrice, sAltPriceCurrency FROM Setup2 WHERE bDisplayEuroPrice";
$result = odbc_exec($connect, $query);
odbc_fetch_row($result);
$bDisplayEuroPrice = odbc_result($result, 1);
$sAltPriceCurrency = odbc_result($result, 2);
// proceed if we're using an alternate currency
if ( $bDisplayEuroPrice )
{
// first lookup our base currency and symbol from the Actinic setup
$query = "SELECT [Currency of purchase], SINTLSYMBOLS FROM Catalog WHERE SINTLSYMBOLS";
$result = odbc_exec($connect, $query);
odbc_fetch_row($result);
$sBaseCurrencyLong = odbc_result($result, 1);
$sBaseCurrencySymbol = odbc_result($result, 2);
// get stored iso code and rate for second currency from the database
$query = "SELECT sExchangeRate, SINTLSYMBOL FROM currencies WHERE SINTLSYMBOL='$sAltPriceCurrency'";
$result = odbc_exec($connect, $query);
odbc_fetch_row($result);
$sExchangeRate = odbc_result($result, 1);
// Now get ECB rates
$exchrate['EUR'] = 1.00; // rates returned against Euro so we need a dummy entry
$rates = file("http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml"); // the ECB XML file
if ( ! $rates )
{
if ( $isPreview )
{
echo "Unable to get exchange rate data from http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml";
}
}
else
{
# Extract data from page - conversion rates between each currency and the Euro
foreach ($rates as $line)
{
if ( preg_match("/currency='(.*?)' rate='(.*?)'/" , $line, $matches) )
{
$exchrate[$matches[1]] = $matches[2];
}
}
// now lookup the todays rate
if ( array_key_exists($sAltPriceCurrency, $exchrate) && array_key_exists($sBaseCurrencySymbol, $exchrate) )
{
$todaysbasecurrencyeurorate = 1 / $exchrate[$sBaseCurrencySymbol]; // ECB supplies Euro rates
$todayaltcurrencyrate = sprintf('%.4f', $todaysbasecurrencyeurorate * $exchrate[$sAltPriceCurrency]);
// see what the percentage change is from prior value
$rateratio = $sExchangeRate > $todayaltcurrencyrate ? $sExchangeRate / $todayaltcurrencyrate : $todayaltcurrencyrate / $sExchangeRate;
$ratepercentdiff = sprintf('%.1f',($rateratio - 1 ) * 100);
// some diagnostic info
if ( $showdiags && $isPreview )
{
echo "N.B. Only shows on Preview. Default: $sBaseCurrencyLong $sBaseCurrencySymbol ";
echo " Alternate: $sAltPriceCurrency (Old $sExchangeRate)";
echo " Today: EUR $todaysbasecurrencyeurorate $sAltPriceCurrency $todayaltcurrencyrate";
echo " Difference: $ratepercentdiff%";
}
// now update the exchange rate - but only if significant change from previous value
if ( $ratepercentdiff >= $percentdifference )
{
$query = "UPDATE currencies SET sExchangeRate = '$todayaltcurrencyrate' WHERE SINTLSYMBOL='$sAltPriceCurrency'";
$result = odbc_exec($connect, $query);
}
}
else // one or both rates unavailable
{
$errmsg = '';
if ( ! array_key_exists($sAltPriceCurrency, $exchrate) )
{
$errmsg .= "No rate available for $sAltPriceCurrency ";
}
if ( ! array_key_exists($sBaseCurrencySymbol, $exchrate) )
{
$errmsg .= "No rate available for $sBaseCurrencySymbol ";
}
if ( $isPreview )
{
echo " Error: $errmsg ";
}
}
}
// whew - done with database
odbc_close_all();
}
</actinic:block>
</actinic:block>
</actinic:block>
Comment