Sale price not converting back into base currency

Submitted by teezone on Fri, 2008-02-29 06:20 in Price Tapestry

Oops, I had everything working perfectly, until a non-base currency item went on sale!

For example, regular price of 100 GBP, converts correctly to 196.84 USD, but sale price of 50 GBP shows 50 USD.

It seems I haven't converted the (new) sale price field into the base currency, and would be very grateful for some assistance. I've tried to figure this out myself, with limited (ie. no) success!

These are Currency settings:

In products.php:
$sql = "SELECT *,IF(currency='GBP',(price * 1.9684),price) AS sortPrice FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY sortPrice LIMIT ".$config_resultsPerPage;

In search.php:
$sql = "SELECT * , IF(currency='GBP',(MIN(price) * 1.9684),MIN(price)) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name";

And the following Sale Price:

In html/product.php:

<?php
 if (floatval($mainProduct["saleprice"]) == 0.0) {
     print " ";
}  if ($mainProduct["saleprice"] == $mainProduct["sortPrice"]) {
    print "<div><font color='#0006eb'>Regular Price</font></div>";
}  elseif (floatval($mainProduct["saleprice"]) > 0.0) {
   print "<div>";
   print $config_currencyHTML.number_format($mainProduct["saleprice"],2);
   print "<a href='";
   print tapestry_buyURL($mainProduct);
   print "' target='_blank'> Sale Price</a></div>";
}
?>

In html/searchresults.php:

<?php
 if (floatval($product["saleprice"]) == 0.0) {
     print " ";
}  if ($product["saleprice"] == $product["price"]) {
    print " ";
}  elseif (floatval($product["saleprice"]) > 0.0) {
   print "<div>";
   print $config_currencyHTML.number_format($product["saleprice"],2);
   print "<a href='";
   print tapestry_buyURL($product);
   print "' target='_blank'> Sale Price</a></div>";
}
?>

I'm trying to convert both the search results page, as well as the product page.

Thanks!
T.

Hi, You will need to make 2

Submitted by support on Fri, 2008-02-29 11:28

Hi,

You will need to make 2 changes to fix this. One to select the converted (or not) sale price as a different field name in the result set (because "saleprice" already exists), and then secondly to use the new field name in the HTML section. For the SQL, try this:

In products.php:

$sql = "SELECT *,IF(currency='GBP',(price * 1.9684),price) AS sortPrice,IF(currency='GBP',(saleprice * 1.9684),saleprice) AS display_saleprice FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY sortPrice LIMIT ".$config_resultsPerPage;

In search.php:

$sql = "SELECT * , IF(currency='GBP',(MIN(price) * 1.9684),MIN(price)) AS minPrice, MAX( price ) AS maxPrice,IF(currency='GBP',(saleprice * 1.9684),saleprice) AS display_saleprice COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name";

And then in your HTML sections, change "saleprice" to "display_saleprice"...

Hope this helps!
Cheers,
David.

You make it look so simple,

Submitted by teezone on Fri, 2008-02-29 19:21

You make it look so simple, it worked like a charm! I was definitely over-thinking...

Thanks so much for the help!

Hi, I just noticed another

Submitted by teezone on Tue, 2008-03-04 07:07

Hi, I just noticed another bug in my code related to currency conversion - I had tried to add a 3rd currency, but it seems to skip the USD conversion (only performing the second IF statement for currency=EUR).

Should the following code work? Any suggestions much appreciated!

search.php:

$sql = "SELECT * , IF(currency='USD',(MIN(price) * .51),MIN(price)) AS minPrice, IF(currency='EUR',(MIN(price) * .7407),MIN(price)) AS minPrice, MAX( price ) AS maxPrice, IF(currency='USD',(saleprice / 1.9862),saleprice) AS display_saleprice, IF(currency='EUR',(saleprice * 1.35),saleprice) AS display_saleprice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name";

products.php:

$sql = "SELECT *,IF(currency='USD',(price * .51),price) AS sortPrice, IF(currency='EUR',(price * .7407),price) AS sortPrice FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY sortPrice LIMIT ".$config_resultsPerPage;

Thanks
T.

Hi, What's happening there

Submitted by support on Tue, 2008-03-04 08:34

Hi,

What's happening there is that only the last expression in the list is being evaluated into the actual result (each one overwriting the previous!). What you need to do is use the CASE statement instead to return a different conversion factor for each currency. The basic expression would be:

CASE currency WHEN 'USD' THEN 0.51 WHEN 'EUR' THEN 0.7407 ELSE 1 END

So replacing this into both SQL statements above gives:

search.php

$sql = "SELECT * , (MIN(price) * CASE currency WHEN 'USD' THEN 0.51 WHEN 'EUR' THEN 0.7407 ELSE 1 END) AS minPrice, MAX( price ) AS maxPrice, (saleprice * CASE currency WHEN 'USD' THEN 0.51 WHEN 'EUR' THEN 0.7407 ELSE 1 END) AS display_saleprice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name";

products.php

$sql = "SELECT *,(price * CASE currency WHEN 'USD' THEN 0.51 WHEN 'EUR' THEN 0.7407 ELSE 1 END) AS sortPrice FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY sortPrice LIMIT ".$config_resultsPerPage;

Hope this helps!
Cheers,
David.