You are here:  » Discount calculation and discount filter


Discount calculation and discount filter

Submitted by MiTU on Wed, 2020-12-16 00:16 in

Hi David, I am close to finishing my new project. I moved to bootstrap and using some bootstrap skin and site is looking very sweet now :)

Well need to ask this before I lose days on finding solution. Is it possible to make filter, something like most discounted product by calculating min and max price per product? Also want to use that calculation on my homepage instead featured products.

Lets say product name is Price Tapestry, Merchant1 price $5, Merchant2 price $10, Merchant3 price $20 etc.

Now I want to make search filter and featured product sorting based on discount, calculation between cheapest Merchant1 price $5 and most expensive Merchant3 price $20, that would be 75% discount if I am not mistaking :)

Hope thats possible, thanks for your great support David :)))

Submitted by support on Wed, 2020-12-16 08:47

Hi,

The discount would need be calculate as a post-import operation but that's no problem as there is the backfill review function that is called at the end of an individual feed or full import so the code can be inserted there.

First, add a new `discount` column to the products table by running the following dbmod.php script from the top level:

<?php
  set_time_limit
(0);
  require(
"includes/common.php");
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."products` ADD `discount` INT(11) NOT NULL default '0'";
  
database_queryModify($sql,$result);
  print 
"Done.";
?>

Next, edit includes/admin.php and look for the following code at line 750:

    $sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='0',reviews='0'";

...and REPLACE with:

    $sql = "SELECT DISTINCT(name),MIN(price) AS minPrice,MAX(price) AS maxPrice FROM `".$config_databaseTablePrefix."products` GROUP BY name";
    if (database_querySelect($sql,$rows))
    {
      foreach($rows as $row)
      {
        $discount = intval(100 - ((100 / $row["maxPrice"]) * $row["minPrice"]));
        $sql = "UPDATE `".$config_databaseTablePrefix."products` SET discount='".$discount."' WHERE name='".database_safe($row["name"])."'";
        database_queryModify($sql,$result);
      }
    }
    $sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='0',reviews='0'";

So after the next import you'll now have the discount field populated (the same value for all products of the same name representing the total discount between cheapest and most expensive merchant).

Now to select Featured Products by greatest discount, edit index.php and look for the default Featured Products SELECT code (line 12 in the distribution)

  $sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY sequence";

...and REPLACE with:

  $sql = "SELECT DISTINCT(name),1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY discount DESC LIMIT 6";

...and that will select the 6 (or as required, just change the LIMIT) most discounted products as Featured Products.

You could also add a sort by discount option to search results. To do this, edit search.php and look for the following code at line 81:

    $orderByDefault["rating"] = "rating DESC";

...and REPLACE with:

    $orderByDefault["rating"] = "rating DESC";
    $orderByDefault["discount"] = "discount DESC";

...and then the following code at line 91:

    $orderByFullText["rating"] = "rating DESC";

...and REPLACE with:

    $orderByFullText["rating"] = "rating DESC";
    $orderByFullText["discount"] = "discount DESC";

...and then the following code at line 463:

      $sortRelevance = ($sort=="relevance"?"<strong>".translate("Relevance")."</strong>":"<a href='".$sortHREF."relevance'>".translate("Relevance")."</a>");

...and REPLACE with:

      $sortRelevance = ($sort=="relevance"?"<strong>".translate("Relevance")."</strong>":"<a href='".$sortHREF."relevance'>".translate("Relevance")."</a>");
      $sortDiscount = ($sort=="discount"?"<strong>".translate("Discount")."</strong>":"<a href='".$sortHREF."discount'>".translate("Discount")."</a>");

...and finally the following code at line 478:

      $banner["h3"] = translate("Order by").": ".$sortRelevance.$sortRating." | ".translate("Price").": ".$sortPriceAsc.", ".$sortPriceDesc;

...and REPLACE with:

      $banner["h3"] = translate("Order by").": ".$sortRelevance.$sortDiscount.$sortRating." | ".translate("Price").": ".$sortPriceAsc.", ".$sortPriceDesc;

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by MiTU on Wed, 2020-12-16 15:16

You are great as always David :)

Btw I also needed to add discount here:

$titleSort = array("relevance" => translate("Relevance"),"rating" => translate("Rating"),"discount" => translate("Discount"),"cheap" => translate("Cheap First"),"costly" => translate("Costly First"));

Dont know whats happening, I have installed PT on localhost and using WAMP because dont want to go live before everything is ready. Import gets stuck, keep reloading and its slow, have just 2 merchants with around 7k products for testing purposes.

Any idea what could be problem?

I tried to restart all WAMP services, truncate products table, disable adblocker. I am on Firefox btw. Its very strange, didnt even toch admin.php till now and had fresh PT installation on my localhost, newest version. Didnt play with other files, just those inside html folder.

Submitted by support on Thu, 2020-12-17 08:22

Hi,

I'm not sure why it would reload (or be slow) but it's probably best to use the command line import on a WAMP installation, so if you open a command prompt, change directory to the /scripts/ folder of your Price Tapestry installation and then use

php cron.php

cron.php runs all Automation Tool jobs (if any configured) and then imports all feeds...

If that's still not working as expected let me know and I'll check it out further with you...

Cheers,
David.
--
PriceTapestry.com

Submitted by MiTU on Thu, 2020-12-17 15:30

Ah its slow because of discount, needs forever to calculate and update db :)

Used CMD to check what is the problem, also getting PHP Warning: Division by zero error but think thats not a big problem.

Changed code a bit so please review it and tell if its good, working much faster now (I am still learning) :))

function admin_importReviews()
  {
    global $config_databaseTablePrefix;
    $sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='0',reviews='0',discount='0'";
    database_queryModify($sql,$insertId);
    $sql = "SELECT product_name,AVG(rating) as rating,COUNT(id) as reviews FROM `".$config_databaseTablePrefix."reviews` WHERE approved <> '0' GROUP BY product_name";
    if (database_querySelect($sql,$rows))
    {
      foreach($rows as $review)
      {
        $sql = "UPDATE `".$config_databaseTablePrefix."products` SET rating='".$review["rating"]."',reviews='".$review["reviews"]."' WHERE normalised_name='".database_safe($review["product_name"])."'";
        database_queryModify($sql,$insertId);
      }
    }
    $sql = "SELECT DISTINCT(name),MIN(price) AS minPrice,MAX(price) AS maxPrice FROM `".$config_databaseTablePrefix."products` GROUP BY name";
    if (database_querySelect($sql,$rows))
    {
      foreach($rows as $row)
      {
        if ($row["minPrice"] != $row["maxPrice"])
        {
          $discount = intval(100 - ((100 / $row["maxPrice"]) * $row["minPrice"]));
          $sql = "UPDATE `".$config_databaseTablePrefix."products` SET discount='".$discount."' WHERE name='".database_safe($row["name"])."'";
          database_queryModify($sql,$result);
        }
      }
    }
  }

Do I need ELSE inside FOREACH or its good like this?

foreach($rows as $row)
{
  if ($row["minPrice"] != $row["maxPrice"])
    {
      $discount = intval(100 - ((100 / $row["maxPrice"]) * $row["minPrice"]));
      $sql = "UPDATE `".$config_databaseTablePrefix."products` SET discount='".$discount."' WHERE name='".database_safe($row["name"])."'";
      database_queryModify($sql,$result);
    }
}

Or you have some better solution then code above :))))

Thanks a lot David :)

Submitted by support on Fri, 2020-12-18 08:28

That looks perfect - no need for an ELSE condition as the dbmod.php set a default value of 0...

Cheers,
David.
--
PriceTapestry.com

Submitted by MiTU on Fri, 2020-12-18 23:40

What do you say about this, is it ok?

$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` ENABLE KEYS";
  database_queryModify($sql,$result);
  $sql = "UPDATE `".$config_databaseTablePrefix."products_import` SET discount='0'";
  database_queryModify($sql,$result);
  $sql = "SELECT DISTINCT(name),MIN(price) AS minPrice,MAX(price) AS maxPrice FROM `".$config_databaseTablePrefix."products_import` GROUP BY name";
    if (database_querySelect($sql,$rows))
    {
      foreach($rows as $row)
      {
        if ($row["minPrice"] != $row["maxPrice"])
        {
          $discount = intval(100 - ((100 / $row["maxPrice"]) * $row["minPrice"]));
          $sql = "UPDATE `".$config_databaseTablePrefix."products_import` SET discount='".$discount."' WHERE name='".database_safe($row["name"])."'";
          database_queryModify($sql,$result);
        }
      }
    }
  $sql = "DROP TABLE `".$config_databaseTablePrefix."products`";

I moved discount code from admin.php to cron.php and using temp products_import table for calculation. Is it ok to do it like this? Didnt liked how products jumping here and there till products import and calculation not finished :) Hope this working good lols :)

Tested via CMD and seems its working great but need your approval that is the right way :D

Submitted by support on Sat, 2020-12-19 08:29

That's fine - perfect place to do it and won't slow down admin / working with new feeds at all.

Cheers,
David.
--
PriceTapestry.com