Hi David,
Hope all is going well.
I added the code below to my featured items page to display a GBP saving value:
<?php if ($product["discountgbp"]!="0.00"): ?>
<div class="price item_price"><span style='color:#F00; font-weight:bold '><?php print translate("SAVE "); ?><?php print $config_currencyHTML.$product["discountgbp"]; ?></span></div>
<?php endif; ?>I already calculate and store an actual GBP saving at load time. The code works fine except in a few instances where no saving is displayed even though I can see there is one. After some investigation, I have noticed some suppliers don't always quote an RRP so in some instances if no saving is available for the main product nothing is displayed. Since RRP should be the same for all like products, I wondered if you could possibly suggest if there is any way to modify my code so that if the main product does not have a saving calculated the other matching products are checked for an RRP value and this is then used (on the fly) to display the saving on the featured page? The saving being RRP - lowest priced product in comparison= saving in GBP...does this sound possible? I'm wondering if the speed may be an issue?
Thanks in advance.
Regards
Chris
Hi David,
Thanks for your quick response. The backfilling option sounds like a better solution. The fields from my dB are:
$config_fieldSet["price"] = "Price";
$config_fieldSet["rrpprice"] = "RRP Price";
Thanks in advance.
Have a good weekend.
Best regards
Chris
Thanks Chris,
The easiest thing to do would be to plumb the code into the admin_importReviews() function, that way, it will be applied at the end of any import process however invoked.
To give it a go, edit includes/admin.php and look for the following code around line 566:
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='0',reviews='0'";...and REPLACE with:
$noRRP = "0.00";
$sql = "SELECT id,name,price,rrpprice,merchant FROM `".$config_databaseTablePrefix."products` WHERE rrpprice = '".$noRRP."'";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$sql2 = "SELECT rrpprice FROM `".$config_databaseTablePrefix."products` WHERE rrpprice = '".$noRRP."' AND name='".database_safe($row["name"])."' AND merchant <> '".database_safe($row["merchant"])."' LIMIT 1";
if (database_querySelect($sql2,$rows2))
{
$discountgbp = tapestry_decimalise($rows2[0]["rrpprice"]-$row["price"]);
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET rrpprice='".$rows2[0]["rrpprice"]."',discountgbp='".$discountgbp."'";
database_queryModify($sql,$result);
}
}
}
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='0',reviews='0'";I wasn't sure whether a product with no RRP price would have an rrpprice field value of 0.00 or just be empty, so I've included a variable in the first line of the modification so that you can use either case. If the field is actually empty rather than 0.00 to imply no RRP, replace with:
$noRRP = "";Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David,
Thanks for the mod above which I have implemented, something seems to be not quite right though, as I can see rrp's(which have matching products) but which have not been populated after a full (slow) import. Also, I think having the rrp update at the end of the process could be a problem as the GBPdiscount and the percentage discount calculations I make are done earlier in the admin.php file using the code below:
/* decimalise price */
$importRecord["price"] = tapestry_decimalise($importRecord["price"]);
$importRecord["normalprice"] = tapestry_decimalise($importRecord["normalprice"]);
$importRecord["deliverycost"] = tapestry_decimalise($importRecord["deliverycost"]);
/* $importRecord["discount"] = tapestry_decimalise($importRecord["discount"]); temp stop replaced by 5 lines below*/
/*calculate percentage discount and populate discount field */
if (($importRecord["normalprice"] != "0.00") && ($importRecord["normalprice"] != $importRecord["price"]))
{
$importRecord["discount"] = (($importRecord["normalprice"]-($importRecord["price"])) / ($importRecord["normalprice"])*100);
$importRecord["discount"] = tapestry_decimalise($importRecord["discount"]);
}
/*calculate actual discount and populate discountGBP field */
if (($importRecord["normalprice"] != "0.00") && ($importRecord["normalprice"] != $importRecord["price"]))
{
$importRecord["discountgbp"] = (($importRecord["normalprice"]-($importRecord["price"])));
$importRecord["discountgbp"] = tapestry_decimalise($importRecord["discountgbp"]);
}Would be grateful for any suggestions you might have on how to resolve the rrp issue and if you think the discount field calculations will need to be moved or modified to work.
Thanks in advance.
Regards
Chris
hi Chris,
I did spot an error in the original replacement (superfluous review code) so i've corrected that above however I'm a little confused since the section of code in your post refers to a 'normalprice' rather than 'rrpprince' field, so I wondered if the calculation needs to be made against this field instead?
If that's the case, have a go with the following as an alternative REPLACEment:
$sql = "SELECT id,name,price,normalprice,merchant FROM `".$config_databaseTablePrefix."products`
WHERE normalprice = '0.00'";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$sql2 = "SELECT normalprice FROM `".$config_databaseTablePrefix."products`
WHERE normalprice <> '0.00' AND name='".database_safe($row["name"])."'
AND merchant <> '".database_safe($row["merchant"])."' LIMIT 1";
if (database_querySelect($sql2,$rows2))
{
$discount = (($rows[2][0]["normalprice"]-($row["price"])) / ($rows[2][0]["normalprice"])*100);
$discount = tapestry_decimalise($discount);
$discountgbp = (($rows[2][0]["normalprice"]-($row["price"])));
$discountgbp = tapestry_decimalise($discountgbp);
$sql = "UPDATE `".$config_databaseTablePrefix."products`
SET discount='".$discount."',discountgbp='".$discountgbp."'
WHERE id='".$row["id"]."'";
database_queryModify($sql,$result);
}
}
}
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='0',reviews='0'";If still not working as expected after the next import (the above will be applied at the end after the discount / discountgbp fields have been populated for products for which the data is available), if you could email me your modified includes/admin.php and corresponding config.advanced.php I'll check it out further for you...
Cheers,
David.
--
PriceTapestry.com
Hi Chris,
I did spot an error in the original replacement (superfluous review code) so i've corrected that above however I'm a little confused since the section of code in your post refers to a 'normalprice' rather than 'rrpprice' field, so I wondered if the calculation needs to be made against this field instead?
If that's the case, have a go with the following as an alternative REPLACEment:
$sql = "SELECT id,name,price,normalprice,merchant FROM `".$config_databaseTablePrefix."products`
WHERE normalprice = '0.00'";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$sql2 = "SELECT normalprice FROM `".$config_databaseTablePrefix."products`
WHERE normalprice <> '0.00' AND name='".database_safe($row["name"])."'
AND merchant <> '".database_safe($row["merchant"])."' LIMIT 1";
if (database_querySelect($sql2,$rows2))
{
$discount = (($rows[2][0]["normalprice"]-($row["price"])) / ($rows[2][0]["normalprice"])*100);
$discount = tapestry_decimalise($discount);
$discountgbp = (($rows[2][0]["normalprice"]-($row["price"])));
$discountgbp = tapestry_decimalise($discountgbp);
$sql = "UPDATE `".$config_databaseTablePrefix."products`
SET
normalprice='".$rows[2][0]["normalprice"]."',
discount='".$discount."',
discountgbp='".$discountgbp."' WHERE id='".$row["id"]."'";
database_queryModify($sql,$result);
}
}
}
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='0',reviews='0'";If still not working as expected after the next import (the above will be applied at the end after the discount / discountgbp fields have been populated for products for which the data is available), if you could email me your modified includes/admin.php and corresponding config.advanced.php I'll check it out further for you...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Thanks for the above solution which works perfectly. Apologies for the delay in responding and for the rrp/normalprice field confusion.
Thanks again.
Best regards
Chris
Hi David,
In most instances retailers do not provide the RRP as used in this thread, and if they do they are often subjective ({link saved}).
I implemented node 5965 for sorting savings on the search page which made me think a hybrid would be useful.
I would appreciate knowing how to modify the above code, if the best solution, to take the highest price from other retailers and then populate new field "highPrice", and then calculate/save the field "saving"
Secondly creating a list of the top 15 savings in descending order that can be included in the sidebar or home page
Best regards,
Richard
Hi Richard,
It would be no problem to create a new "saving" field populated at import time as the percentage difference between price and the highest price for the same product.
Firstly, to add the new field, apply the following dbmod.php script:
<?php
require("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
ADD `saving` DECIMAL(10,2) NOT NULL
";
database_queryModify($sql,$result);
print "Done.";
?>
And then to apply the calculation at the end of the import process as above, edit includes/admin.php and look for the following code around line 751:
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='0',reviews='0'";...and REPLACE with:
$sql1 = "SELECT name,MAX(price) AS maxPrice FROM `".$config_databaseTablePrefix."products` GROUP BY name";
if (database_querySelect($sql1,$products1))
{
foreach($products1 as $product1)
{
$sql2 = "SELECT id,price FROM `".$config_databaseTablePrefix."products` WHERE name='".database_safe($product1["name"])."'";
database_querySelect($sql2,$products2);
foreach($products2 as $product2)
{
$saving = (($product1["maxPrice"]-($product2["price"])) / ($product1["maxPrice"]*100));
$saving = tapestry_decimalise($saving);
$sql3 = "UPDATE `".$config_databaseTablePrefix."products` SET saving = '".$saving."' WHERE id='".$product2["id"]."'";
database_queryModify($sql3,$result3);
}
}
}
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='0',reviews='0'";To display top 15 products by saving, you could do this with an alternative Featured Products query, just like random Featured Products but using:
$sql = "SELECT DISTINCT(name),1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY saving DESC LIMIT 15";Cheers,
David.
--
PriceTapestry.com
Hi David,
Thanks for quick reply.
On import I get the following message
{code saved}
Any thoughts?
BR
Richard
Sorry about that Richard i'd got the brackets out of sync in the calculation - corrected above...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Just comparing output in featured table with database and although code is pulling out products with savings, the code
$sql = "SELECT DISTINCT(name),1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY saving DESC LIMIT 8";does not appear to be pulling the biggest savings first.
Please note that I amended admin.php to import absolute saving as opposed to percentage.
Any thoughts on what might be going wrong?
BR
Richard
The featured section within index.php is
<?php
$sql = "SELECT DISTINCT(name),1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY saving DESC LIMIT 4";
if (database_querySelect($sql,$rows))
{
$sqlNames = array();
$sqlCase = "CASE normalised_name";
foreach($rows as $featured)
{
$featured["name"] = tapestry_normalise($featured["name"]);
$sqlNames[] = "'".$featured["name"]."'";
$sqlCase .= " WHEN '".database_safe($featured["name"])."' THEN ".$featured["sequence"];
}
$sqlCase .= " END AS sequence";
$sqlIn = implode(",",$sqlNames);
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, ".$sqlCase." FROM `".$config_databaseTablePrefix."products` WHERE normalised_name IN (".$sqlIn.") GROUP BY normalised_name ORDER BY sequence";
database_querySelect($sql,$rows);
$featured["products"] = $rows;
foreach($featured["products"] as $k => $product)
{
$featured["products"][$k]["productHREF"] = tapestry_productHREF($product);
$featured["products"][$k]["reviewHREF"] = tapestry_reviewHREF($product);
}
}
if (isset($featured)) require("html/featured_savings.php");
?>I have tried various amendments to get the biggest savings listed in descending order but without success.
BR
Richard
Hi Richard,
Ah - that would require that the saving value is actually the same for all product records - in other words, not the saving between each each merchant and the highest price merchant, but instead, the saving between the highest and lowest price merchant for each product.
In this context, and as an absolute rather than percentage value, the REPLACEment to includes/admin.php would be as follows;
$sql = "SELECT name,MIN(price) AS minPrice,MAX(price) AS maxPrice
FROM `".$config_databaseTablePrefix."products` GROUP BY name";
if (database_querySelect($sql,$products))
{
foreach($products as $product)
{
$saving = ($product["maxPrice"] - $product["minPrice"]);
$sql = "UPDATE `".$config_databaseTablePrefix."products`
SET saving = '".$saving."' WHERE name='".database_safe($product["name"])."'";
database_queryModify($sql,$result);
}
}
}
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='0',reviews='0'";(which is far more efficient of course, as only requires one UPDATE query per product...)
Cheers,
David.
--
PriceTapestry.com
Hi David,
I'm justing sanity checking xml feed data for Argos and I have noticed that sometimes they have a saleprice field:
<saleprice>19.99</saleprice>
<price>39.99</price>When registering the feed I would map price to the price field. However, in instances where there is a sale price I would like that field to be imported to the price field.
Is that possible?
Best regards,
Richard
Hi Richard,
It could be hard coded in easily enough - in includes/admin.php look for the following code at line 466:
$importRecord["price"] = tapestry_decimalise($importRecord["price"]);...and REPLACE with:
if (($importRecord["merchant"]=="Argos") && isset($record["SALEPRICE"]))
{
$importRecord["price"] = $record["SALEPRICE"];
}
$importRecord["price"] = tapestry_decimalise($importRecord["price"]);Cheers,
David.
--
PriceTapestry.com
Hi David,
I am so pleased that I asked the question. I wasn't sure it was going to be possible.
Many thanks
Best regards,
Richard
Hi Chris,
What is the retail price field name on your products table? (if you could post the line from $config_fieldSet in config.advanced.php that will help)
Whilst it can be done "on the fly", and I don't think would be a significant overhead (although of course there would be one) it would need to be done slightly differently in each scenario (Featured Products, Search Results, Product Page etc.)
A tidier approach would be a post-import process, just like the backfill reviews process, that backfills un-populated rrp fields from populated values for the same product from another merchant, and re-calculates discountgpb, so if you'd like to give that a go, I'll work out the code as soon as I am back online properly as I am on the road this weekend, otherwise if you'd prefer an on-the-fly version, I can work that out instead..!
Cheers,
David.
--
PriceTapestry.com