Coding Commerce: Showing the shopper which items they already purchased

Do you shop on a specific site and would like to see what products you have previously purchased in the shopping experience? I have seen some implementations of a “My Closet” where its a specific page on the site that shows what products you have already purchased. What I think would be really cool is if you could get that information on the product page or product listings or even both.

So let’s look at the WebSphere Commerce Aurora site, here is what the men’s shirts category entry page looks like:

mens-shirts

 

 

Ideally, when a customer searches or lands on this category they would immediately see what items they have in their “closet” or even items that were “already ordered”. The status could also link to a “My Closet” page.

I started looking into this to figure out what you would have to do in order to get this accomplished. Basically there are three tables at play:

orders – The primary order table that contains all orders in the system.

orderitems – The order items table contains all product level SKU’s in all orders. It can grow to be a very large table.

catentrel – This is the catalog entry relationship table. This is where we can get the parent product number as it stores the relationship between SKU’s and Products

The key to making the query useful is to get the current shoppers member Id and look up the product Id in the orderitems and catentryrel tables for the current shopper. The basic SQL statement to achieve these joins is here:

select CATENTREL.CATENTRY_ID_PARENT,ORDERITEMS.MEMBER_ID,ORDERITEMS.CATENTRY_ID,ORDERS.STATUS from ORDERITEMS 
JOIN CATENTREL 
ON ORDERITEMS.CATENTRY_ID=CATENTREL.CATENTRY_ID_CHILD
JOIN ORDERS
ON ORDERITEMS.ORDERS_ID=ORDERS.ORDERS_ID

If you issue this SQL on your WebSphere Commerce server you should see a similar result to this:

query

Now we have all of the critical pieces of information we need to see if a particular product has been purchased by the current shopper – Parent Catalog Entry Id, the Member Id, and at last, the Status. To get a list of all status codes you should check out the InfoCenter here. For my proof of concept work I am just going to be looking for a status code of “S” – which means “Shipped“, any other code I will just print out “Ordered“. (Lot’s of holes in that logic but it’s a start).

So let’s see some code!

Since I want this to be on search results, category listings, and also the product page I am just going to modify the PriceDisplay.jsp in a similar way as I did in the last post (Coding Commerce: Extending the Price display widget for consumable products).

The first thing I did was create a new tag with the actual database look-up and the code to print out the status. So in the PriceDisplay_UI.jspf I will add this line of code right after the price is printed:

<alreadypurchased:check productID=”${catalogEntryID}”/>

The custom tag is the alreadypurchased:check part and I just pass in the catalog entry Id for the current product. In my tag code I will actually get the shopper’s member_id to pull off the query. But first we have to fix the query to only return items for the given shopper and product we are working on, here is the modified query:

select CATENTREL.CATENTRY_ID_PARENT,ORDERITEMS.MEMBER_ID,ORDERITEMS.CATENTRY_ID,ORDERS.STATUS from ORDERITEMS 
JOIN CATENTREL 
ON ORDERITEMS.CATENTRY_ID=CATENTREL.CATENTRY_ID_CHILD
JOIN ORDERS
ON ORDERITEMS.ORDERS_ID=ORDERS.ORDERS_ID
where ORDERITEMS.MEMBER_ID= ? AND ORDERITEMS.CATENTRY_ID= ?

Notice I added a where clause at the end. This should have a result set much more granular and specific to what we are looking for. It should contain all order entries for this specific shopper and product (more than one may be returned):

query-single

I then take the result set and check for the status “S” and print out Ordered  or In your Closet directly under the price.  Here are the results:

Category List or Search Results:

closet-1

Product Page:

closet-2

Pretty cool huh?

Now for the disclaimer – There is no way I would ever implement it like this in production. Ideally you would want a query or table indexed on the server and just hit against that index versus doing joins live during a session – especially on category and search results – it will do that query for N results returned which will have serious performance implications. Remember, the larger the orderitems table gets the slower this query will be.

I would be really interested in hearing your thoughts on this and different ways you could make a solution like this perform well.

Here is the full code for the custom tag (use at your own risk as I did this in about 30 minutes):

[codesyntax lang=”java”]

package com.ibm.custom.tags;

import java.io.IOException;
import java.rmi.RemoteException;
import java.sql.SQLException;
import java.util.Vector;

import javax.ejb.CreateException;
import javax.naming.NamingException;
import javax.servlet.jsp.JspException;
import javax.servlet.jsp.JspWriter;
import javax.servlet.jsp.tagext.BodyTagSupport;

import com.ibm.commerce.base.objects.ServerJDBCHelperAccessBean;
import com.ibm.commerce.command.CommandContext;
import com.ibm.commerce.server.ECConstants;

public classAlreadyPurchasedTag extends BodyTagSupport {

	private StringproductID = null;
	private StringcustomerID= null;

	public StringgetProductID() {
		returnproductID;
	}

	public voidsetProductID(StringproductID) {
		this.productID = productID;
	}

	public intdoStartTag() throwsJspException{

		return EVAL_PAGE;
	}
	
	public intdoEndTag() throws JspException{JspWriter out = pageContext.getOut();

		try {customerID =getSessionId();
			
			if (customerID != null){
				Vector results =getResults();
				
				if (results == null || results.size() < 1)
					return EVAL_PAGE;
				
				
				String status = ((Vector)results.get(0)).get(3).toString();
				
				if (status.equals("S"))
					out.println("
In your closet!
");
				else
					out.println("
Ordered
");
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return EVAL_PAGE;
	}
	
	private Vector getResults() {
		
		Object [] queryParams = new Object [] { customerID, productID };
		StringBuffer queryString = new StringBuffer("select CATENTREL.CATENTRY_ID_PARENT, ORDERITEMS.MEMBER_ID,ORDERITEMS.CATENTRY_ID,ORDERS.STATUS from ORDERITEMS " + 
				"JOIN CATENTREL ON ORDERITEMS.CATENTRY_ID=CATENTREL.CATENTRY_ID_CHILD JOIN ORDERS ON ORDERITEMS.ORDERS_ID=ORDERS.ORDERS_ID where ORDERITEMS.MEMBER_ID= ? AND CATENTREL.CATENTRY_ID_PARENT= ? ");
		ServerJDBCHelperAccessBean jdbcHelper = new ServerJDBCHelperAccessBean();
		Vector results = null;
		
		try {
			results = jdbcHelper.executeParameterizedQuery(queryString.toString(), queryParams);
		} catch (RemoteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (CreateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return results;
	}

	private String getSessionId(){
		String id = null;
		JspWriter out = pageContext.getOut();
		
		try{
			boolean isGuest = true;
			CommandContext context = (CommandContext) pageContext.getRequest().getAttribute(ECConstants.EC_COMMANDCONTEXT);
			if (context != null) {
				if (context.getUser() == null || !"R".equals(context.getUser().getRegisterType())) {
					isGuest = true;
				}else {
					id = context.getUser().getMemberId();
				}
			}
			
		}catch(Exception e){
			e.printStackTrace();
			
			id = Long.toString(System.currentTimeMillis());
		}
		return id;
		
	}
}

[/codesyntax]

 

 

 

 

4 thoughts on “Coding Commerce: Showing the shopper which items they already purchased

  1. Hi Bob
    Is there a enhancement from IBM in the near future to provide this as a search rune in Management center. We have a requirement to apply a search rule to the search to boost results based on previously ordered items. There is a a search rule to do this but the limitation is to define the products to boost explicitly. We are looking to customize the MC to accomplish this, at the very least , add a expression provider to boost the products from the order history to the top.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s