Jump to content
olsonsp4c

Automatic Stock Notification

Recommended Posts

Hey there,

 

thanks for this great contirb. It´s working fine. But how can I get it to use german Product Names ? Whenever it sends an Mail it uses the english product names. Even German is Standardlanguage. How can I change the Code to work with german Product Names ?

 

Greetings,

Moelle78

Share this post


Link to post
Share on other sites

Great contribution!!!,

 

Does anyone have installed bundle products?

 

The bundle products stock is different and I haven't got idea to solve this issue.

 

This contribucion add to TABLE_PRODUCTS 'products_bundle', this may be 'yes' or 'empty' and add TABLE_PRODUCTS_BUNDLE with this 'bundle_id' (like product_id), 'subproduct_id' (product_id that forms this bundle) and 'subproduct_qty' (quantity of subproduct_id that forms this bundle).

 

Sorry for my poor english.

 

Regards!!

Share this post


Link to post
Share on other sites

Is it possible to see how many customers subscribe to a particular product? Or something like that? It will be a great way to know wich product to re-order, or just to know what to buy for your shop.

Share this post


Link to post
Share on other sites

Thank you so much for sharing this. I have taken it and reworked it a bit to work as a standalone script that can be run by cron. I think this would have worked fine in older OSCommerce with htaccess only on the admin area, but in the newer versions and CRE Loaded, there is an additional admin login, so I was trying to use lynx to pass both htaccess credentials and then also get it to login to the admin area. I gave up on that and stripped all the oscommerce functions out of it so it has no includes, placed it in an admin subdirectory with htaccess only on it and was able to set up a cron job using lynx to get this to run daily (which syncs up nicely with the daily xml quantity updates from the warehouse). Anyways - i wanted to try to share this, but haven't submitted such things before, so I am just going to drop it here and hope someone finds it useful. I did include some help at the top of the file regarding the variables to fill out and the cron command. Hope someone finds this useful! I have it running on a CRE Loaded 6.4.0 B2B site - Remember, your server must support the lynx text browser...

 

<?php
/*
 $Id: cron_product_customer_notifications.php

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2010 osCommerce

 Released under the GNU General Public License

 ///////////////////////////////////////////////////

 2010-07-20 - jeemdesign.com - stripped out all the oscommerce functions and includes to make this standalone and easier to set with a cron job.  I put mine in my admin dir (which has an additional .htaccess password on it, so it is not open to public).  


 Here are the lines to edit below:

 	$dbhost_osc='localhost';   // your db host
$dbuser_osc='user_name';   // your db username
$dbpass_osc='aBc1234#';    // your db password
$dbase_osc='my_cre_db1';   // your db name

	define('NOTIFICATION_HTTP_SERVER', 'http://www.1234-website-abc.com');  // your http website URL
	define('DIR_WS_CATALOG', '/');  // your catalog subdirectory if any
	define('HTTPS_SERVER', 'https://www.1234-website-abc.com');  // your https website URL

 	define('STORE_NAME', 'My Store Name');   // Your Store Name

	$from = "From: Customer Service<customerservice@1234-website-abc.com>\r\n";  //  Change to your store's preferred FROM email address


Place this (cron_product_customer_notifications.php) in an .htaccess password protected directory, then you can schedule the following command in cron (provided your server has lynx available to you).

Command:

lynx -auth=adminusername:PaSsw0rd123 -dump http://www.1234-website-abc.com/admin/protectedcron/cron_product_customer_notifications.php

* In the command above:
*  the htaccess credentials are passed via "-auth="  followed by adminusername:PaSsw0rd123
*  -dump sends the results to standard output, which, if you have the results of the cron job emailed to you, you will get a daily report of product notifications.
* The last part is the URL to the file.  "protectedcron" is a subfolder in the admin directory.  The admin directory has the .htaccess username and password on it.


This is working for me - let me know if you have any trouble with it.  Hope it makes sense and is helpful.  I've taken a lot of stuff from the community and want to give back a bit when possible.  Thanks!  Viva la Open Source!


*/

 $max_mails = 50;  // throttle the # of emails to avoid looking like a spammer

// make db connection
$dbhost_osc='localhost';   // your db host
$dbuser_osc='user_name';   // your db username
$dbpass_osc='aBc1234#';    // your db password
$dbase_osc='my_cre_db1';   // your db name

$link = mysql_connect($dbhost_osc,$dbuser_osc,$dbpass_osc);

if (!$link) {
	die('Not Connected to Server: ' . mysql_error());
}	

//select db
if(!mysql_select_db($dbase_osc,$link)) {
	print "no such dbname";
}

// Automatic Stock Notification
define('NOTIFICATION_HTTP_SERVER', 'http://www.1234-website-abc.com');  // your http website URL
define('DIR_WS_CATALOG', '/');  // your catalog subdirectory if any
define('HTTPS_SERVER', 'https://www.1234-website-abc.com');  // your https website URL

define('TEXT_NOTIFICATION1', 'Dear ');
define('TEXT_NOTIFICATION2', ',' . "\n\n" . 'According to your ');
define('TEXT_NOTIFICATION3', ' Product Notifications request, we wish to inform you that ');
define('TEXT_NOTIFICATION4', ' is now in stock:  ' . NOTIFICATION_HTTP_SERVER . DIR_WS_CATALOG . 'product_info.php?products_id=');
define('TEXT_NOTIFICATION4b', "\n\n" . 'Please visit ' . NOTIFICATION_HTTP_SERVER . ' if you would like to make a purchase.' . "\n\n" . 'Your product notification for '); 
define('TEXT_NOTIFICATION5', ' has been removed.  If you would like to receive future stock notifications for ');
define('TEXT_NOTIFICATION6', ', you must login to your ');
define('TEXT_NOTIFICATION7', ' account, navigate to the ');
define('TEXT_NOTIFICATION8', ' page, and click the notifications button.' . "\n\n" . 'Warm greetings from your friends at ');

define('EMAIL_SUBJECT', 'Now In Stock: ');

define('HEADING_TITLE','Generating customer emails for products back in stock');
define('TABLE_HEADING_STATUS','Status');
define('TABLE_HEADING_FIRSTNAME','First Name');
define('TABLE_HEADING_LASTNAME','Last Name');
define('TABLE_HEADING_CUSTOMERS_ID','Customer Id');
define('TABLE_HEADING_PRODUCTS_NAME','Products Name');
define('TABLE_HEADING_PRODUCTS_ID','Products Id');
define('TABLE_HEADING_EMAIL_ADDRESS','Email Address');
define('TEXT_NUMBER_OF_EMAILS',' %s out of %s emails sent');
define('TABLE_PRODUCTS_NOTIFICATIONS', 'products_notifications');
define('TABLE_CUSTOMERS', 'customers');
define('TABLE_PRODUCTS', 'products');
define('TABLE_PRODUCTS_DESCRIPTION', 'products_description');

define('STORE_NAME', 'My Store Name');   // Your Store Name


?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Automated Stock Notifications</title>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2" align="center">
 <tr>
<!-- body_text //-->
   <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
     <tr>
       <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
           <td><h3>Automated Stock Notifications</h3><br></td>
           <td align="right"></td>
         </tr>
       </table></td>
     </tr>
     <tr>
       <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
           <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
             <tr>
               <td><?php echo TABLE_HEADING_STATUS; ?></td>
               <td><?php echo TABLE_HEADING_FIRSTNAME; ?></td>
               <td><?php echo TABLE_HEADING_LASTNAME; ?></td>
               <td align="center"><?php echo TABLE_HEADING_CUSTOMERS_ID; ?></td>
               <td align="center"><?php echo TABLE_HEADING_PRODUCTS_NAME; ?></td>
               <td><?php echo TABLE_HEADING_PRODUCTS_ID; ?></td>
               <td align="center"><?php echo TABLE_HEADING_EMAIL_ADDRESS; ?></td>
             </tr>
		<?php
	// BEGIN Automatic Stock Notification

                               // jeemdesign.com - remove all product notifications for product_id = 0 if they exist
                              $products_zero_ct="select * from " . TABLE_PRODUCTS_NOTIFICATIONS . " where products_id = 0;";
                              $resultset_zeroct = mysql_query($products_zero_ct,$link);
                              $num77 = mysql_num_rows($resultset_zeroct);

                              if ($num77 !== 0) {
                              		 $products_zero_qry="delete from " . TABLE_PRODUCTS_NOTIFICATIONS . " where products_id = '0' ";
                             		 $resultset_pz = mysql_query($products_zero_qry,$link);
								 print "CLEANUP:  Blanked " . $num77 . " records where Product_ID = 0<br><br>";
							}  else {
								print "CLEANUP:  No records with Product ID = 0 to clean up<br><br>";
							}

                               //look for notifications to process                                
                               $audience = array();

                               $prod_notify_qry="select distinct pd.products_name, p.products_id, pn.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address from " . TABLE_CUSTOMERS . " c, " . TABLE_PRODUCTS_NOTIFICATIONS . " pn , " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.customers_id = pn.customers_id and pn.products_id = p.products_id and p.products_quantity > 0 and p.products_id = pd.products_id and pn.products_id != '0'";

                               $resultset_notify = mysql_query($prod_notify_qry,$link);

                               $num1 = mysql_num_rows($resultset_notify);

                               if ($num1 !== 0) {
								print $num1 . " Product Notifications to Process<br><br>";
							}  else {
								print "No Product Notifications to Process<br><br>";
							}

                               // process each notification, create message, send email, remove notification
                               while ($products = mysql_fetch_array($resultset_notify)) {
                                 $audience[$products['customers_id']] = array(
                                 		   'firstname' => $products['customers_firstname'],
									   'lastname' => $products['customers_lastname'],
									   'customers_id' => $products['customers_id'],
									   'products_name' => $products['products_name'],
									   'products_id' => $products['products_id'],
									   'email_address' => $products['customers_email_address']);
						  	}

                               reset($audience);
                               $mailed = 0;
                               while (list($key, $value) = each ($audience)) {
                                 if ($mailed < $max_mails) {


                                       $message2 = TEXT_NOTIFICATION1 . $value['firstname'] . ' ' . $value['lastname'] . TEXT_NOTIFICATION2 . STORE_NAME . TEXT_NOTIFICATION3 . $value['products_name'] . TEXT_NOTIFICATION4 . $value['products_id'] . TEXT_NOTIFICATION4b .  $value['products_name'] . TEXT_NOTIFICATION5 . $value['products_name'] . TEXT_NOTIFICATION6 . STORE_NAME . TEXT_NOTIFICATION7 .  $value['products_name'] . TEXT_NOTIFICATION8 . STORE_NAME; 

									$to = $value['firstname'] . ' ' . $value['lastname'] . '<' . $value['email_address'] . '>'; 
									$subject = EMAIL_SUBJECT . $value['products_name']; 

									$from = "From: Customer Service<customerservice@1234-website-abc.com>\r\n";  //  Change to your store's preferred FROM address

									mail($to, $subject, $message2, $from); 

									// increase counter of # mailed
									$mailed++;

									// delete the notification now that mail is sent	
								   $delete_notification_qry="delete from " . TABLE_PRODUCTS_NOTIFICATIONS . " where products_id = '" . $value['products_id'] . "' and customers_id = '" . $value['customers_id'] . "' ";

								   $resultset_del = mysql_query($delete_notification_qry,$link);

								   //echo $delete_notification_qry . "<br>";

								   if ($resultset_del) {
										print "Removed Product Notifications from table that have been emailed.<br><br>";
									}  else {
										print "None Removed from Product Notification Table<br><br>";
									}
                                      ?>

                   					<td>DONE: </td>

									<?php

                                 		} else {

									?>

                   					<td>TODO: </td>

									<?php
                                 	}
							?>
								<td><?php echo $value['firstname']; ?></td>
								<td><?php echo $value['lastname']; ?></td>
								<td><?php echo $value['customers_id']; ?></td>
								<td><?php echo $value['products_name']; ?></td>
								<td><?php echo $value['products_id']; ?></td>
								<td><?php echo $value['email_address']; ?></td>
					 		</tr>
							<?php
                     			}
							// END Automatic Stock Notification
							?>
                         <tr>
               <td colspan=7><br><?php echo sprintf(TEXT_NUMBER_OF_EMAILS, $mailed, mysql_num_rows($resultset_notify)); ?></td>
             </tr>
           </table></td>
         </tr>
       </table></td>
     </tr>
   </table></td>
<!-- body_text_eof //-->
 </tr>
</table>
<!-- body_eof //-->
</body>
<?php mysql_close($link); ?>
</html>

Share this post


Link to post
Share on other sites

Can someone describe to me how this add-on handles notifications for customers who have selected to be notified for every product update (Global Product Notifications)? Are they included in the automatic notification for each product as it's level is increased above zero?

 

thanks,

Ron

Share this post


Link to post
Share on other sites

Bruyndoncs, awesome code. You save me hours. Thanks!

 

In

define('HTML_NOTIFICATION8', ' .... NOTIFICATION_HTTP_SERVER . DIR_WS_CATALOG

 

should be

 

HTTP_SERVER . DIR_WS_CATALOG

 

to take the correct link.

Share this post


Link to post
Share on other sites

Other question:

 

I need to send email notifications all days, and need the cron not to delete the product from table PRODUCTS_NOTIFICATIONS.

I commented this line:

// tep_db_query("delete from " . TABLE_PRODUCTS_NOTIFICATIONS . " where products_id = '" . $value['products_id'] . "' and customers_id = '" . $value['customers_id'] . "' ");

 

Here the problem is a customer with diferent products,.. the cron always send the same product, not all products customer has in his list.

Edited by bhbilbao

Share this post


Link to post
Share on other sites

@egutierrez

 

Here is Carines file for the latest osC version. Now that you mentioned the stock osC products notification I know how it works.

You could put the language definitions into a separate language file and name it same as the file.

Example:

admin/product_customer_notifications.php

admin/includes/languages/*yourlanguage*/product_customer_notifications.php

<?php
/*
  $Id: product_customer_notifications.php

  osCommerce, Open Source E-Commerce Solutions
  http://www.oscommerce.com

  Copyright (c) 2015 osCommerce

  Released under the GNU General Public License
*/

  $max_mails = 10;
  
  require('includes/application_top.php');

// Automatic Stock Notification
 define('HTML_NOTIFICATION1', 'Dear ');
 define('HTML_NOTIFICATION2', ',' . "\n\n" . 'According to your ');
 define('HTML_NOTIFICATION3', ' Product Notifications request, we wish to inform you that ' . '<a href="' . NOTIFICATION_HTTP_SERVER . DIR_WS_CATALOG . 'product_info.php' . '?products_id=');
 define('HTML_NOTIFICATION4', '</a>' . ' is now in stock.  Please visit ' . '<a href="' . NOTIFICATION_HTTP_SERVER . DIR_WS_CATALOG);
 define('HTML_NOTIFICATION5', '</a>' . ' if you would like to make a purchase.' . "\n\n" . 'Your product notification for ');
 define('HTML_NOTIFICATION6', ' has been removed.  If you would like to receive future stock notifications for ');
 define('HTML_NOTIFICATION7', ', you must ' . '<a href="' . HTTPS_SERVER . DIR_WS_CATALOG . 'login.php' . '">' . 'login' . '</a>' . ' to your ');
 define('HTML_NOTIFICATION8', ' account, navigate to the ' . '<a href="' . NOTIFICATION_HTTP_SERVER . DIR_WS_CATALOG . 'product_info.php' . '?products_id=');
 define('HTML_NOTIFICATION9', '</a>' . ' page, and click the ' . '<i>' . 'notifications' . '</i>' . ' button.' . "\n\n" . 'Warm greetings from your friends at ');
 define('TEXT_NOTIFICATION1', 'Dear ');
 define('TEXT_NOTIFICATION2', ',' . "\n\n" . 'According to your ');
 define('TEXT_NOTIFICATION3', ' Product Notifications request, we wish to inform you that ');
 define('TEXT_NOTIFICATION4', ' is now in stock.  Please visit ' . NOTIFICATION_HTTP_SERVER . DIR_WS_CATALOG . ' if you would like to make a purchase.' . "\n\n" . 'Your product notification for ');
 define('TEXT_NOTIFICATION5', ' has been removed.  If you would like to receive future stock notifications for ');
 define('TEXT_NOTIFICATION6', ', you must login to your ');
 define('TEXT_NOTIFICATION7', ' account, navigate to the ');
 define('TEXT_NOTIFICATION8', ' page, and click the notifications button.' . "\n\n" . 'Warm greetings from your friends at ');
 define('EMAIL_SUBJECT', 'Now In Stock: ');
 
 define('HEADING_TITLE','Generating customer emails for products back in stock');
 define('TABLE_HEADING_STATUS','Status');
 define('TABLE_HEADING_FIRSTNAME','First Name');
 define('TABLE_HEADING_LASTNAME','Last Name');
 define('TABLE_HEADING_CUSTOMERS_ID','Customer Id');
 define('TABLE_HEADING_PRODUCTS_NAME','Products Name');
 define('TABLE_HEADING_PRODUCTS_ID','Products Id');
 define('TABLE_HEADING_EMAIL_ADDRESS','Email Address');
 define('TEXT_NUMBER_OF_EMAILS',' %s out of %s emails sent');
 
   require(DIR_WS_INCLUDES . 'template_top.php');
?>

   <table border="0" width="100%" cellspacing="0" cellpadding="2">
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
            <td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr class="dataTableHeadingRow">
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_STATUS; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_FIRSTNAME; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_LASTNAME; ?></td>
                <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_CUSTOMERS_ID; ?></td>
                <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_PRODUCTS_NAME; ?></td>
                <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_PRODUCTS_ID; ?></td>
                <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_EMAIL_ADDRESS; ?></td>
              </tr>
<?php
// BEGIN Automatic Stock Notification
				$audience = array();
				$products_query = tep_db_query("select distinct pd.products_name, p.products_id, pn.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address from " . TABLE_CUSTOMERS . " c, " . TABLE_PRODUCTS_NOTIFICATIONS . " pn , " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.customers_id = pn.customers_id and pn.products_id = p.products_id and p.products_quantity > 0 and p.products_id = pd.products_id ");

				while ($products = tep_db_fetch_array($products_query)) {
				  $audience[$products['customers_id']] = array('firstname' => $products['customers_firstname'],
															   'lastname' => $products['customers_lastname'],
															   'customers_id' => $products['customers_id'],
															   'products_name' => $products['products_name'],
															   'products_id' => $products['products_id'],
															   'email_address' => $products['customers_email_address']);
				}
				reset($audience);
				$mailed = 0;
				while (list($key, $value) = each ($audience)) {
				  if ($mailed < $max_mails) {
					$mimemessage = new email(array('X-Mailer: osCommerce System Mailer'));
					// add the message to the object
					if (EMAIL_USE_HTML == 'true') {
					  $mimemessage->add_html(HTML_NOTIFICATION1 . $value['firstname'] . ' ' . $value['lastname'] . HTML_NOTIFICATION2 . STORE_NAME . HTML_NOTIFICATION3 . $value['products_id'] . '">' . $value['products_name'] . HTML_NOTIFICATION4 . '">' . STORE_NAME . HTML_NOTIFICATION5 . $value['products_name'] . HTML_NOTIFICATION6 . $value['products_name'] . HTML_NOTIFICATION7 . STORE_NAME . HTML_NOTIFICATION8 . $value['products_id'] . '">' . $value['products_name'] . HTML_NOTIFICATION9 . STORE_NAME);
					} else {
					  $mimemessage->add_text(TEXT_NOTIFICATION1 . $value['firstname'] . ' ' . $value['lastname'] . TEXT_NOTIFICATION2 . STORE_NAME . TEXT_NOTIFICATION3 . $value['products_name'] . TEXT_NOTIFICATION4 . $value['products_name'] . TEXT_NOTIFICATION5 . $value['products_name'] . TEXT_NOTIFICATION6 . STORE_NAME . TEXT_NOTIFICATION7 . $value['products_name'] . TEXT_NOTIFICATION8 . STORE_NAME);
					}
					$mimemessage->build_message();
//To test use something like below
//					$mimemessage->send($value['firstname'] . ' ' . $value['lastname'], 'emailtest@yourdomain.com', STORE_NAME, STORE_OWNER_EMAIL_ADDRESS, EMAIL_SUBJECT . $value['products_name'], $email_order);
					$mimemessage->send($value['firstname'] . ' ' . $value['lastname'], $value['email_address'], STORE_NAME, STORE_OWNER_EMAIL_ADDRESS, EMAIL_SUBJECT . $value['products_name'], $email_order);

					$mailed++;

//To test comment out the delete query so you can re-test with the same data till everything is fine
// the following line deletes the customer notification so that that after the email is sent, the customer will have to visit the product page and select the notification button in order to receive future email stock notifications
					tep_db_query("delete from " . TABLE_PRODUCTS_NOTIFICATIONS . " where products_id = '" . $value['products_id'] . "' and customers_id = '" . $value['customers_id'] . "' ");
?>
                    <td class="dataTableContent">DONE: </td>
<?php
				  } else {
?>
                    <td class="dataTableContent">TODO: </td>
<?php
				  }
?>
                <td class="dataTableContent"><?php echo $value['firstname']; ?></td>
                <td class="dataTableContent"><?php echo $value['lastname']; ?></td>
                <td class="dataTableContent" align="center"><?php echo $value['customers_id']; ?></td>
                <td class="dataTableContent" align="center"><?php echo $value['products_name']; ?></td>
                <td class="dataTableContent" align="center"><?php echo $value['products_id']; ?></td>
                <td class="dataTableContent" align="center"><?php echo $value['email_address']; ?></td>
              </tr>
<?php
				}
// END Automatic Stock Notification
?>
			  <tr>
                <td class="smallText" colspan="7"><?php echo sprintf(TEXT_NUMBER_OF_EMAILS, $mailed, tep_db_num_rows($products_query)); ?></td>
              </tr>
            </table></td>
          </tr>
        </table></td>
      </tr>
    </table>

<?php
  require(DIR_WS_INCLUDES . 'template_bottom.php');
  require(DIR_WS_INCLUDES . 'application_bottom.php');
?>

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×