Help! It’s out of stock!

If we have a product that is out of stock, we need to make it possible for our customers to sign up to be alerted when they are back in stock. If we don’t do this, then they will be left with the option of either going elsewhere, or regularly returning to our store to check on the stock levels for that particular product. To try and discourage these customers from going elsewhere a “tell me when it is back in stock” option saves them the need to regularly check back, which would be off-putting. Of course, it is still likely that the customer may go elsewhere; however, if our store is niche, and the products are not available elsewhere, then if we give the customer this option they will feel more valued.

There are a few stages involved in extending our framework to support this:

1. Firstly, we need to take into account stock levels.
2. If a product has no stock, we need to insert a new template bit with an “alert me when it is back in stock” form.
3. We need a template to be inserted when this is the case.
4. We then need functionality to capture and store the customer’s e-mail address, and possibly their name, so that they can be informed when it is back in stock.
5. Next, we need to be able to inform all of the customers who expressed an interest in a particular product when it is back in stock.
6. Once our customers have been informed of the new stock level of the product, we need to remove their details from the database to prevent them from being informed at a later stage that there are more products in stock.
7. Finally, we will also require an e-mail template, which will be used when sending the e-mail alerts to our customers.

Detecting stock levels

With customizable products, stock levels won’t be completely accurate. Some products may not require stock levels, such as gift vouchers and other non-tangible products. To account for this, we could either add a new field to our database to indicate to the framework that a products stock level isn’t required for that particular product, or we could use an extreme or impossible value for the stock level, for example -1 to indicate this.

Changing our controller

We already have our model set to pull the product stock level from the database; we just need our controller to take this value and use different template bits where appropriate. We could also alter our model to detect stock levels, and if stock is required for a product.

if( $productData['stock'] == 0 )
{
$this->registry->getObject('template')->
addTemplateBit( 'stock', 'outofstock.tpl.php' );
}
elseif( $productData['stock'] > 0 )
{
$this->registry->getObject('template')->
addTemplateBit( 'stock', 'instock.tpl.php' );
}
else
{
$this->registry->getObject('template')->getPage()->
addTag( 'stock', '' );
}

This simple code addition imports a template file into our view, depending on the stock level.

Out of stock: a new template bit

When the product is out of stock, we need a template to contain a form for the user to complete, so that they can register their interest in that product.

<h2>Out of stock!</h2>
<p>
We are <strong>really</strong> sorry, but this product is currently
out of stock. If you let us know your name and email address, we
will let you know when it is back in stock.
</p>
<form action="products/stockalert/{product_path}" method="post">
<label for="stock_name">Your name</label>
<input type="text" id="stock_name" name="stock_name" />
<label for="stock_email">Your email address</label>
<input type="text" id="stock_email" name="stock_email" />
<input type="submit" id="stock_submit" name="stock_submit"
value="Let me know, when it is back in stock!" />
</form>

Here we have the form showing our product view, allowing the customer to enter their name and e-mail address:

Tell me when it is back in stock please!

Once a customer has entered their name, e-mail address, and clicked on the submit button, we need to store these details and associate them with the product. This is going to involve a new database table to maintain the relationship between products and customers who wish to be notified when they are back in stock.

Stock alerts database table

We need to store the following information in the database to manage a list of customers interested in being alerted when products are back in stock:

• Customer name
• Customer e-mail address
• Product

In terms of a database, the following table structure would represent this:

The following SQL represents this table:

CREATE TABLE `product_stock_notification_requests` (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`customer` VARCHAR( 100 ) NOT NULL ,
`email` VARCHAR( 255 ) NOT NULL ,
`product` INT NOT NULL ,
`processed` BOOL NOT NULL ,
INDEX ( `product` , `processed` )
) ENGINE = INNODB COMMENT = 'Customer notification requests for
new stock levels'
ALTER TABLE `product_stock_notification_requests`
 
ADD FOREIGN KEY ( `product` ) REFERENCES `book4`.`content` (`ID`)
ON DELETE CASCADE ON UPDATE CASCADE 

More controller changes

Some modifications are needed to our product’s controller to process the customer’s form submission and save it in the stock alerts database table.

In addition to the following code, we must also change our switch statement to detect that the customer is visiting the stockalert section, and that the relevant function should be called.

private function informCustomerWhenBackInStock()
{
$pathToRemove = 'products/stockalert/';
$productPath = str_replace( $pathToRemove, '',
                            $this->registry->getURLPath() );
require_once( FRAMEWORK_PATH . 'models/products/model.php');
$this->model = new Product( $this->registry, $productPath );

Once we have included the model and checked that the product is valid, all we need to do is build our insert array, containing the customer’s details and the product ID, and insert it into the notifications table.

if( $this->model->isValid() )
{
 $pdata = $this->product->getData();
 $alert = array();
 $alert['product'] = $pdata['ID'];
 $alert['customer'] = $this->registry->getObject('db')->
  sanitizeData( $_POST['stock_name'] );
 $alert['email'] = $this->registry->getObject('db')->
  sanitizeData( $_POST['stock_email'] );
 $alert['processed'] = 0;
 $this->registry->getObject('db')->
  insertRecords('product_stock_notification_requests', $alert );
 // We then inform the customer that we have saved their request.
 $this->registry->getObject('template')->getPage()->
  addTag('message_heading', 'Stock alert saved');
 $this->registry->getObject('template')->getPage()->
  addTag('message_heading', 'Thank you for your interest in
  this product, we will email you when it is back in stock.');
 $this->registry->getObject('template')->
  buildFromTemplates('header.tpl.php', 'message.tpl.php',
                     'footer.tpl.php');
}

If the product wasn’t valid, we tell them that, so they know the notification request was not saved.

else
{
 $this->registry->getObject('template')->getPage()->
  addTag('message_heading', 'Invalid product');
 $this->registry->getObject('template')->getPage()->
  addTag('message_heading', 'Unfortunately, we could not find
  the product you requested.');
 $this->registry->getObject('template')->
  buildFromTemplates('header.tpl.php', 'message.tpl.php',
                     'footer.tpl.php');
}
}

This code is very basic, and does not validate e-mail address formats, something which must be done before we try to send any e-mails out.

It is back!

Once the product is back in stock, we need to then alert those customers that the product which they were interested in is back in stock, and that they can proceed to make their purchase. This isn’t something we can implement now, as we don’t have an administrative interface in place yet. However, we can discuss what is involved in doing this:

1. The administrator alters the stock level.
2. Customers interested in that product are looked up.
3. E-mails for each of those customers are generated with relevant details, such as their name and the name of the product being automatically inserted.
4. E-mails are sent to the customers.

The database contains a processed field, so once an e-mail is sent, we can set the processed value to 1, and then once we have alerted all of our customers, we can delete those records. This covers us in the unlikely event that all the new stock sells out while we are e-mailing customers, and a new customer completes the notification form.