Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Automatic Stock Notification


olsonsp4c

Recommended Posts

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!!

Link to comment
Share on other sites

  • 4 weeks later...
  • 1 month later...

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<[email protected]>\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<[email protected]>\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>

Link to comment
Share on other sites

  • 3 weeks later...

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

Link to comment
Share on other sites

  • 4 months later...

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
Link to comment
Share on other sites

  • 1 month later...
  • 4 years later...

@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'], '[email protected]', 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');
?>
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...