Notify me when the product's information, price and stock level is changed

ShishaPipeUK · #1 11-23-2006, 01:52 PM

Notify me when the product's information, price and stock level is changed

You can see this in action at http://www.theshisha.com/shopcart/product.php?productid=16139&cat=0&page=1

This modification is for x-cart 4.1.3 version
I have posted this modification so that x-cart forum members can contribute to the code and hopefully make any improvements to the modification and post them in this thread.

Now about the modification:

CUSTOMER:

1. When customer is on "Product Details" page, s/he will see a new form:
------
Notify my when the product's information is changed: < "Enter the email here..." text field >
<"Submit" button>
------

The form will look similar for both logged and not logged customer. The only exception will be that the email from the user' profile will be pre-filled in the text-field, however, can be changed to another address, if needed.

2. After clicking "Submit", the script will create a special record in the database, containing the productID, current description, price, quantity in stock and specified email address (a new table will be created for this purpose).

ADMIN:

When changing the product details (submitting "Product Modify" form, or using "Products import" facility), the script will look if there are any email addresses, associated with the current productID.

If yes, it will look for the differences in one of the following fields:
- Short/Long description;
- Price;
- Quantity in stock (from 0 to a bigger value).

I.e., it will compare the date, was stored when customer submitted a new form and the currently submitted by the admin data.

If any of described changes have taken place, a special "Product information has changed" email will be sent to all the email addresses, associated with current product.

The email will contain the text, saying that the information about the product has changed (language variable, which the admin can change in "Languages" section) and the link to "Product Details" page. updated product's information.

Also, the information, saved about the product initially (in new table) will be updated with the new product's information (will allow to catch the next changes for the product).

Also with the email your customer receives, you have the option to unsubscribe to the product notify me.

This modification requires five original xcart 4.1.3 files to be changed:
init.php
product.php
include/import_products.php
include/product_modify.php
skin1/customer/main/product.tpl

Four new x-cart files
changed_product_notify.php
skin1/mail/productinfo_has_changed.tpl
skin1/mail/productinfo_has_changed_subj.tpl
skin1/mail/html/productinfo_has_changed.tpl

And a change to the SQL file which is below.

Apply the SQL changes (Open the Patch/Upgrade page in admin area and select the patch.sql from the archive for the "Apply SQL patch" section).

Code:

 
UPDATE xcart_config SET value='1164025634' WHERE name='data_cache_expiration';
UPDATE xcart_config SET value='19' WHERE name='survey_sending_remainder';
CREATE TABLE xcart_email_for_changed_products (productid int(11) NOT NULL default '0',email varchar(128) NOT NULL default '',descr text NOT NULL default '',fulldescr text NOT NULL default '',price decimal(12,2) NOT NULL default '0.00',avail int(11) NOT NULL default '0',PRIMARY KEY (productid,email)) TYPE=MyISAM;
INSERT INTO xcart_languages SET code='US', name='eml_productinfo_has_changed', value='The product description, price, or quantity has changed with regard to this product.<br><br>Please go to the product for more details.', topic='E-Mail';
INSERT INTO xcart_languages SET code='US', name='eml_productinfo_has_changed_subj', value='Product information has changed', topic='E-Mail';
INSERT INTO xcart_languages SET code='US', name='lbl_notify_about_changed_product', value='Notify me when the product's information is changed', topic='Labels';
INSERT INTO xcart_languages SET code='US', name='lbl_notify_me', value='Notify me if any changes are made to the above product', topic='Labels';
INSERT INTO xcart_languages SET code='US', name='productinfo_has_changed_subj', value='Product information has changed', topic='Labels';
INSERT INTO xcart_languages SET code='US', name='txt_unsubscribe_message', value='You have unsubscribed to the email notification about product changes.', topic='Text';

Upload the Four new x-cart files

changed_product_notify.php - This new file goes in the root of your x-cart, same area as where the home.php file is.

Code:

 
<?php
if($mode == "unsubscribe"){
 require "./auth.php" ;
 db_query("DELETE FROM $sql_tbl[email_for_changed_products] WHERE productid='$productid' AND email='$email'");
 echo($message);
 exit;
}
if ($mode == "changed_product_notify" && !empty($notify_email)){
 $is_notified = func_query_first("SELECT productid FROM $sql_tbl[email_for_changed_products] WHERE productid='$productid' AND email='$notify_email'");
 if ($is_notified)
  db_query("DELETE FROM $sql_tbl[email_for_changed_products] WHERE productid='$productid' AND email='$notify_email'");
 
 db_query("INSERT INTO $sql_tbl[email_for_changed_products] VALUES ('$productid', '$notify_email', '$desc', '$fulldesc', '$price', '$avail')");
}
if ($login) {
 $notify_email = func_query_first_cell("SELECT email FROM $sql_tbl[customers] WHERE login='$login'");
 $smarty->assign("notify_email", $notify_email);
}
?>

skin1/mail/productinfo_has_changed.tpl

Code:

 
{* $Id: productinfo_has_changed.tpl,v 1.0 2006/11/23 Shishapipe UK $ *}
{config_load file="$skin_config"}
{include file="mail/mail_header.tpl"}
{$lng.eml_productinfo_has_changed}
{include file="mail/signature.tpl"}

skin1/mail/productinfo_has_changed_subj.tpl

Code:

{config_load file="$skin_config"}{ $config.Company.company_name }: {$lng.eml_productinfo_has_changed_subj}

skin1/mail/html/productinfo_has_changed.tpl

Code:

 
{* $Id: productinfo_has_changed.tpl,v 1.0 2006/11/23 Shishapipe UK *}
{config_load file="$skin_config"}
{include file="mail/html/mail_header.tpl"}
<P>{$lng.eml_productinfo_has_changed}
<P>{$lng.product_details}
<P><A href="{$http_location}/product.php?productid={$productid}" target=_new>{$http_location}/product.php?productid={$productid}</A>
<P><A href="{$http_location}/changed_product_notify.php?mode=unsubscribe&email={$email}&productid={$productid}&message={$lng.txt_unsubscribe_message}" target=_new>Click HERE to Unsubscribe from this product change notify me</A>
{include file="mail/html/signature.tpl"}

ShishaPipeUK · #2 11-23-2006, 01:54 PM

Now to the five original xcart 4.1.3 files to be changed:

include/import_products.php

Look for this code:

Code:

  # Direct import
  $is_new = ($_productid === false);
  if (!empty($data)) {
   $data = func_addslashes($data);
   if ($_productid === false) {
    $data['provider'] = $import_data_provider;
    $_productid = func_array2insert("products", $data);
   } else {
    func_array2update("products", $data, "productid = '$_productid'");
   }
  }

replace it with:

Code:

 
# Direct import
  $is_new = ($_productid === false);
  if (!empty($data)) {
   $data = func_addslashes($data);
   if ($_productid === false) {
    $data['provider'] = $import_data_provider;
    $_productid = func_array2insert("products", $data);
   } else {
# START: SHISHAPIPE UK MOD - theshisha.com
 
    # Check email for changed product
    $notify_emails = func_query("SELECT email FROM $sql_tbl[email_for_changed_products] WHERE productid='$_productid'");
    if ($notify_emails){
     foreach($notify_emails as $v){
      $old_product_data = func_query_first("SELECT * FROM $sql_tbl[email_for_changed_products] WHERE productid='$_productid' AND email='$v[email]'");
      if ($old_product_data["descr"] != $query_data["descr"] || $old_product_data["fulldescr"] != $query_data["fulldescr"] || $old_product_data["avail"] != $query_data["avail"]){
       global $mail_smarty;
       $mail_smarty->assign("productid", $_productid);
                      $mail_smarty->assign("email", $v["email"]);
                      func_send_mail($v["email"], "mail/productinfo_has_changed_subj.tpl", "mail/productinfo_has_changed.tpl", $config["Company"]["orders_department"], false);
       db_query("UPDATE $sql_tbl[email_for_changed_products] SET descr='".addslashes($data["descr"])."', fulldescr='".addslashes($data["fulldescr"])."', avail='$data[avail]' WHERE productid='$_productid' AND email='$v[email]'");
      }
     }
    }
    # /Check email for changed product
# END: SHISHAPIPE UK MOD - theshisha.com
    func_array2update("products", $data, "productid = '$_productid'");
   }
  }

Then look for this code in the same file include/import_products.php :

Code:

  # Import price
  if (isset($product['price']) || $is_new) {
   $product['price'] = doubleval($product['price']);
   $priceid = func_query_first_cell("SELECT priceid FROM $sql_tbl[pricing] WHERE productid = '$_productid' AND quantity = 1 AND membershipid = 0 AND variantid = 0");
   if ($priceid) {
    func_array2update("pricing", array("price" => $product['price']), "priceid = '$priceid'");

And replace it with:

Code:

 
  # Import price
  if (isset($product['price']) || $is_new) {
   $product['price'] = doubleval($product['price']);
   $priceid = func_query_first_cell("SELECT priceid FROM $sql_tbl[pricing] WHERE productid = '$_productid' AND quantity = 1 AND membershipid = 0 AND variantid = 0");
   if ($priceid) {
# START: SHISHAPIPE UK MOD - theshisha.com
    # Check email for changed product
    $notify_emails = func_query("SELECT email FROM $sql_tbl[email_for_changed_products] WHERE productid='$_productid'"); 
    if ($notify_emails){
     foreach($notify_emails as $v){
      $old_product_price = func_query_first_cell("SELECT price FROM $sql_tbl[email_for_changed_products] WHERE productid='$_productid' AND email='$v[email]'");
      if ($old_product_price != $product['price']){
       global $mail_smarty;
       $mail_smarty->assign("productid", $_productid);
       $mail_smarty->assign("email", $v["email"]);
       func_send_mail($v["email"], "mail/productinfo_has_changed_subj.tpl", "mail/productinfo_has_changed.tpl", $config["Company"]["orders_department"], false);
       db_query("UPDATE $sql_tbl[email_for_changed_products] SET price='$product[price]' WHERE productid='$_productid' AND email='$v[email]'");
      }
     }
    } 
 
    # /Check email for changed product
# END: SHISHAPIPE UK MOD - theshisha.com
    func_array2update("pricing", array("price" => $product['price']), "priceid = '$priceid'");

ShishaPipeUK · #3 11-23-2006, 02:10 PM

include/product_modify.php

Find this code:

Code:

 
   # Update the default price
   if (!$is_variant)
    func_array2update("pricing", array("price" => $price), "productid='$productid' AND quantity='1' AND membershipid = 0 AND variantid = 0");
   if ($fields['price'] == 'Y' && $geid && !$is_variant) {
    while ($pid = func_ge_each($geid, 1, $productid)) {
     $is_variant_sub = (func_query_first_cell("SELECT COUNT(*) FROM $sql_tbl[variants] WHERE productid = '$pid'") > 0);
     if (!$is_variant_sub)
      func_array2update("pricing", array("price" => $price), "productid='$pid' AND quantity='1' AND membershipid = 0 AND variantid = 0");
    }
   }

and replace it with:

Code:

 
# Update the default price
   if (!$is_variant)
    func_array2update("pricing", array("price" => $price), "productid='$productid' AND quantity='1' AND membershipid = 0 AND variantid = 0");
   if ($fields['price'] == 'Y' && $geid && !$is_variant) {
    while ($pid = func_ge_each($geid, 1, $productid)) {
     $is_variant_sub = (func_query_first_cell("SELECT COUNT(*) FROM $sql_tbl[variants] WHERE productid = '$pid'") > 0);
     if (!$is_variant_sub)
      func_array2update("pricing", array("price" => $price), "productid='$pid' AND quantity='1' AND membershipid = 0 AND variantid = 0");
# START: SHISHAPIPE UK MOD - theshisha.com 
     $notify_emails = func_query("SELECT email FROM $sql_tbl[email_for_changed_products] WHERE productid='$pid'");
     if ($notify_emails){
      foreach($notify_emails as $v){
       global $mail_smarty;
       $mail_smarty->assign("productid", $pid);
       $mail_smarty->assign("email", $v["email"]);
       func_send_mail($v["email"], "mail/productinfo_has_changed_subj.tpl", "mail/productinfo_has_changed.tpl", $config["Company"]["orders_department"], false);
       db_query("UPDATE $sql_tbl[email_for_changed_products] SET price='".abs($price)."' WHERE productid='$pid' AND email='$v[email]'");
      }
     }
# END: SHISHAPIPE UK MOD - theshisha.com 
    }
   }

In the same file include/product_modify.php look for:

Code:

 
# Update taxes
db_query("DELETE FROM $sql_tbl[product_taxes] WHERE productid='$productid'");
if($geid && $fields['taxes']) {
while ($pid = func_ge_each($geid, 100, $productid)) {
db_query("DELETE FROM $sql_tbl[product_taxes] WHERE productid IN ('".implode("','", $pid)."')");
}
}
if (!empty($taxes) && is_array($taxes)) {
foreach ($taxes as $k=>$v) {
if (intval($v) > 0) {
$query_data = array(
"productid" => $productid,
"taxid" => intval($v)
);
func_array2insert("product_taxes", $query_data, true);
if($geid && $fields['taxes']) {
while ($pid = func_ge_each($geid, 1, $productid)) {
$query_data['productid'] = $pid;
func_array2insert("product_taxes", $query_data, true);
}
}
}
}
}

and replace it with:

Code:

 
# Update taxes
db_query("DELETE FROM $sql_tbl[product_taxes] WHERE productid='$productid'");
if($geid && $fields['taxes']) {
while ($pid = func_ge_each($geid, 100, $productid)) {
db_query("DELETE FROM $sql_tbl[product_taxes] WHERE productid IN ('".implode("','", $pid)."')");
}
}
 
# START: SHISHAPIPE UK MOD - theshisha.com  
# Check email for changed product
$notify_emails = func_query("SELECT email FROM $sql_tbl[email_for_changed_products] WHERE productid='$productid'");
if ($notify_emails){
foreach($notify_emails as $v){
$old_product_data = func_query_first("SELECT * FROM $sql_tbl[email_for_changed_products] WHERE productid='$productid' AND email='$v[email]'");
if ($old_product_data["descr"] != $query_data["descr"] || $old_product_data["fulldescr"] != $query_data["fulldescr"] || $old_product_data["price"] != abs($price) || $old_product_data["avail"] != $query_data["avail"]){
 
global $mail_smarty;
$mail_smarty->assign("productid", $productid);
$mail_smarty->assign("email", $v["email"]);
func_send_mail($v["email"], "mail/productinfo_has_changed_subj.tpl", "mail/productinfo_has_changed.tpl", $config["Company"]["orders_department"], false);
db_query("UPDATE $sql_tbl[email_for_changed_products] SET descr='".addslashes($query_data["descr"])."', fulldescr='".addslashes($query_data["fulldescr"])."', avail='$query_data[avail]', price='".abs($price)."' WHERE productid='$productid' AND email='$v[email]'");
}
}
} 
 
# END: SHISHAPIPE UK MOD - theshisha.com  
if (!empty($taxes) && is_array($taxes)) {
foreach ($taxes as $k=>$v) {
if (intval($v) > 0) {
$query_data = array(
"productid" => $productid,
"taxid" => intval($v)
);
func_array2insert("product_taxes", $query_data, true);
if($geid && $fields['taxes']) {
while ($pid = func_ge_each($geid, 1, $productid)) {
$query_data['productid'] = $pid;
func_array2insert("product_taxes", $query_data, true);
}
}
}
}
}

In the same file include/product_modify.php look for:

Code:

 
FROM $sql_tbl[variants] WHERE productid = '$pid'") > 0);
      if ($is_variant_sub) {
       func_unset($query_data_sub, "avail", "weight");
      }
      func_array2update("products", $query_data_sub, "productid = '$pid'");
     } else {
      func_array2update("products", $query_data, "productid IN ('".implode("','", $pid)."')");
     }
    }
   }
  }

and replace it with:

Code:

 
FROM $sql_tbl[variants] WHERE productid = '$pid'") > 0);
      if ($is_variant_sub) {
       func_unset($query_data_sub, "avail", "weight");
      }
      func_array2update("products", $query_data_sub, "productid = '$pid'");
     } else {
      func_array2update("products", $query_data, "productid IN ('".implode("','", $pid)."')");
     }
# START: SHISHAPIPE UK MOD - theshisha.com   
 
     $notify_emails = func_query("SELECT email FROM $sql_tbl[email_for_changed_products] WHERE productid='$pid'");
     if ($notify_emails){
      foreach($notify_emails as $v){
       $old_product_data = func_query_first("SELECT * FROM $sql_tbl[email_for_changed_products] WHERE productid='$pid' AND email='$v[email]'");
       if ($old_product_data["descr"] != $query_data_sub["descr"] || $old_product_data["fulldescr"] != $query_data_sub["fulldescr"] || $old_product_data["avail"] != $query_data_sub["avail"]){
 
       global $mail_smarty;
       $mail_smarty->assign("prodictid", $pid);
       $mail_smarty->assign("email", $v["email"]);
       func_send_mail($v["email"], "mail/productinfo_has_changed_subj.tpl", "mail/productinfo_has_changed.tpl", $config["Company"]["orders_department"], false);
       db_query("UPDATE $sql_tbl[email_for_changed_products] SET descr='".addslashes($query_data_sub["descr"])."', fulldescr='".addslashes($query_data_sub["fulldescr"])."', avail='$query_data_sub[avail]' WHERE productid='$id' AND email='$v[email]'");
       }
      }
     } 
# END: SHISHAPIPE UK MOD - theshisha.com  
    }
   }
  }

ShishaPipeUK · #4 11-23-2006, 02:32 PM

You need to change the init.php file

Look for this code:

Code:

"download_keys" => "xcart_download_keys",
"export_ranges" => "xcart_export_ranges",

and replace it with:

Code:

 
"download_keys" => "xcart_download_keys",
# START: SHISHAPIPE UK MOD - theshisha.com 
 "email_for_changed_products" => "xcart_email_for_changed_products",
# END: SHISHAPIPE UK MOD - theshisha.com 
 "export_ranges" => "xcart_export_ranges",

Now open up your product.php and look for:

Code:

 
include "./vote.php";
require $xcart_dir."/include/categories.php";

and replace it with:

Code:

 
include "./vote.php";
# START: SHISHAPIPE UK MOD - theshisha.com  
include "./changed_product_notify.php";
# END: SHISHAPIPE UK MOD - theshisha.com  
require $xcart_dir."/include/categories.php";

OK, last file now, you need to edit skin1/customer/main/product.tpl and place this code in the file.

Code:

{capture name=dialog}
<table width="100%" cellpadding="0" cellspacing="0">
<form action="product.php" method="post" name="notifyform">
<input type="hidden" name="mode" value="changed_product_notify" />
<input type="hidden" name="productid" value="{$product.productid}">
<input type="hidden" name="desc" value="{$product.descr}">
<input type="hidden" name="fulldesc" value="{$product.fulldescr}">
<input type="hidden" name="price" value="{$product.price}">
<input type="hidden" name="avail" value="{$product.avail}">
<tr>
    <td colspan="3">{include file="main/subheader.tpl" title=$lng.lbl_notify_me}</td>
</tr>
<tr>
    <td><br />{$lng.lbl_notify_about_changed_product}:</td>
    <td><br />
  <input type="text" size="25" name="notify_email" value="{$notify_email}"/>
 </td>
 <td><br />
  {include file="buttons/button.tpl" button_title=$lng.lbl_submit style="button" href="javascript: document.notifyform.submit();" type="input"}
 </td> 
</tr>
</form>
</table>
{/capture}
{include file="dialog.tpl" content=$smarty.capture.dialog title=$lng.lbl_notify_me extra='width="100%"'}

I would recommend you place this just under your product description, i placed it after the below code:

Code:

{if $active_modules.Magnifier ne "" && ($config.Magnifier.magnifier_image_popup ne 'Y' || $js_enabled ne 'Y')}
<p />
{include file="modules/Magnifier/product_magnifier.tpl" productid=$product.productid}
{/if}

Thats it the full modification for x-cart 4.1.3, enjoy.

Corto_Maltese · #5 11-24-2006, 12:03 PM

Hi Shisha,

Do you think that this mod can work on a 4.0.18 version ? I saw that the original mod by Mallromania. And one day, it seemed to me that I saw it on your old site (4.0.1

.

Thanks in advance

Erick

pcparts · #6 11-24-2006, 05:20 PM

Just cart get this working for some reason any ideas ?
CREATE TABLE x cart_email_for_changed_products
Comes up with some errors

ShishaPipeUK · #7 11-26-2006, 10:58 PM

What errors ?

Here is the code in my daily CRON mysql backup:

Code:

 
--
-- Table structure for table `xcart_email_for_changed_products`
--
DROP TABLE IF EXISTS xcart_email_for_changed_products;
CREATE TABLE xcart_email_for_changed_products (
productid int(11) NOT NULL default '0',
email varchar(128) NOT NULL default '',
descr text NOT NULL,
fulldescr text NOT NULL,
price decimal(12,2) NOT NULL default '0.00',
avail int(11) NOT NULL default '0',
PRIMARY KEY (productid,email)
) TYPE=MyISAM;
--
-- Dumping data for table `xcart_email_for_changed_products`
--
INSERT INTO xcart_email_for_changed_products VALUES (16582,'paul@shishapipe.net','Dubai Tobacco molasses is made in United Arab Emirates, package in a 115 mm square, 75mm depth box with a plastic 250 gram container inside. Dubai Tobacco uses the best quality essences and has a real flavor and is being produced with more than ten different flavors. \r\n\r\n','Dubai Tobacco molasses is made in United Arab Emirates, package in a 115 mm square, 75mm depth box with a plastic 250 gram container inside. Dubai Tobacco uses the best quality essences and has a real flavor and is being produced with more than ten different flavors. ',"25.00",0);

Just add this into your mysql patch in admin area:

Code:

 
DROP TABLE IF EXISTS xcart_email_for_changed_products;
CREATE TABLE xcart_email_for_changed_products (
productid int(11) NOT NULL default '0',
email varchar(128) NOT NULL default '',
descr text NOT NULL,
fulldescr text NOT NULL,
price decimal(12,2) NOT NULL default '0.00',
avail int(11) NOT NULL default '0',
PRIMARY KEY (productid,email)
) TYPE=MyISAM;

ShishaPipeUK · #8 11-26-2006, 11:04 PM

Quote:

Originally Posted by Corto_Maltese

Hi Shisha,

Do you think that this mod can work on a 4.0.18 version ? I saw that the original mod by Mallromania. And one day, it seemed to me that I saw it on your old site (4.0.1

.

Thanks in advance

Erick

No this is for 4.1.3, i really don't think it will work on 4.0.18 and i have not tried it on this version either, and i would imagine you will have to make some code changes for 4.0.18.
This mod has nothing to do with Mallromania ( 4.0.18 Price change thread - http://forum.x-cart.com/showthread.php?t=22781 ), if anything it was born via the Funkydunks code (Notify Me - http://forum.x-cart.com/showthread.php?t=2471&page=5&highlight=notify ) which i adapted for 4.0.?? and then changed it for xcart 4.1.? to also include description and price changes as well as zero stock change.

paryaei · #9 02-23-2007, 12:31 AM

Thank you very much for this nice mod
I installed it in my shop and everything went OK, but there is one big problem
When a customer types their email in the box and clicks "submit" they get this error message

Access denied !
You are not allowed to access that resource!

Error ID: 33

any idea what is wrong?
thank you in advance for your help

LoveHurts · #10 03-10-2007, 07:36 AM

Hi All;

i use X-cart PRO 4.1.6.
& i'm a newbie

Is there no easyer way??
just that costumers can send a mail when its out of stock ??

like this

<Out of stock>
notify me when it's back in stock

Thx in advance