Solr Search Customization to display promotional prices in WebSphere Commerce

In my last couple of projects, we had a requirement to display promotional prices in product page, search and category pages.  Therefore, I think in recent times, this has become a very common requirement as most of the retailers want to display discounted price upfront in browse pages and reap its benefits by –

1. Attracting More Buyers
2. Providing better Shopping Experience
3. Lower IT support costs
4. Reusability enabling rapid integration & deployment

I have done this customization in both Endeca & Solr. This blog will cover the customization needed in Solr and will talk about Endeca customization in a later blog.

Here is just an overview of the files modified to display promotional details:

Customization Steps:

First step required is to create a scheduler job which will extract promotional prices for every product applicable and load in a custom table – XPROMOPRICE. This will be run before Solr pre-process and build index.

Logic to load data in XPROMOPRICE table

• Create A new table XPROMOPRICE as described below.

• Create a new controller command “XPromoPriceCmd” in package com.mycompany.commerce.product.price.commands
• This command will follow IBM WCS standard controller command framework.
• Get the store id using request properties in command
• Use CalculationCodeAccessBean.findByCalculationUsageAndStoreEntity function to get the list of calcodes applicable for the store. CalculationUsageId = ‘-1’ and store id will be passed as the arguments to this function
• If status of the promotion is inactive or promotion is already expired, then delete all the records for that promotion id from the table
• Check if a record for that catalog entry in the XPROMOPRICE table already exists
• If no, apply the promotion to the Offer Price of the catalog entry if the current date is between start date and end date of that promotion and promotion is published.
• If yes, update the record in XPROMOPRICE with the updated promotional price
• Create Constants files to store Promotion types. Based on the promotion type, store category id or product id in the respective fields.

Create an entity bean XPromoPriceAccessBean which will represent the new table. Add a finder method findByCatEntryId which will accept the catalog entry id as input and will return Enumeration of XPromoPriceAccessBean.

Solr Customization:

Creating custom preprocess xml

This is required to update Solr index with the latest promotion details. Let’s create custom preprocess file to achieve this:

• wc-dataimport-preprocess-custom.xml @ search\pre-processConfig\MC_catalogId\Oracle

This file will contain the query to create a temporary table TI_XPROMOPRICE_0 to store all the promotion information from XPROMOPRICE.

<_config:table definition="CREATE TABLE TI_XPROMOPRICE_0 (CATENTRY_ID NUMBER(18) NOT NULL, promoAttributes CLOB, PRIMARY KEY (CATENTRY_ID))" name="TI_XPROMOPRICE_0"/> 
<_config:query sql1="SELECT CATENTRY_ID, LISTAGG(concat(concat(concat(concat(PROMOID,'||'),PROMOPRICE),'||'), PROMONAME),';') WITHIN GROUP (ORDER BY PRECEDENCE DESC) as PROMOATTRIBUTES FROM XPROMOPRICE GROUP BY CATENTRY_ID"/> 
<_config:mapping> 
    <_config:key queryColumn="CATENTRY_ID" tableColumn="CATENTRY_ID"/>
    <_config:column-mapping> 
        <_config:column-column-mapping> 
            <_config:column-column queryColumn="PROMOATTRIBUTES" tableColumn="PROMOATTRIBUTES" /> 
        </_config:column-column-mapping> 
    </_config:column-mapping> 
</_config:mapping>

Note: query can be modified to include more columns such as catentry_id, start & end time, precedence (to handle multiple promotions).  

 Modify xmls to populate search index

Let’s make changes to existing files:

• xml at search\solr\home\MC_catalogId\locale\CatalogEntry\Config
• wc-data-config.xml at search\solr\home\MC_catalogId\locale\CatalogEntry\Config

The wc-data-config.xml file is used to configure the Data-Import-Handler (DIH) queries and any field transformation that is needed between database data and index data. It contains at least one dataSource configuration, and at least one document configuration. Different queries, fields mapping, and transformers are defined for each entity included in a document. The query and deltaImportQuery define the SQL queries that are responsible for fetching the data from the database, where field mapping is responsible for matching a database column to an index field.

Let’s make changes to the following:

1. Query: Modify the query to fetch data from TI_XPROMOPRICE_0 in the following way:

<entity name="Product" dataSource="WC database" transformer="DateFormatTransformer, ClobTransformer, RegexTransformer, com.ibm.commerce.solr.handler.NameValuePairTransformer" 
query="SELECT CATENTRY.CATENTRY_ID,(…)
TI_XPROMOPRICE_0.PROMOATTRIBUTES PROMOATTRIBUTE FROM CATENTRY (…) 
LEFT OUTER JOIN TI_XPROMOPRICE_0 ON (CATENTRY.CATENTRY_ID=TI_XPROMOPRICE_0.CATENTRY_ID) 
>

2. Field mappings: Field mapping declarations map database column name into index field name.

<field column="PROMOATTRIBUTES" clob="true"/> 
<field column="CATENTRYPROMO" splitBy=";"sourceColName="PROMOATTRIBUTES"/> 

The Solr search engine uses schema.xml file to describe the structure of each data index. This XML file determines how Solr will build indices from input documents, and how to perform index and query time processing. The next step is to declare a unique name and associate it with one of the defined field types in the following manner:

<field name="CATENTRYPROMO" type="wc_keywordText" indexed="true" stored="true" multiValued="true" />
Note: Multivalued attribute is made true to handle multiple promotions  

Run pre-process and build-index so that the data indexation is done successfully.

Steps to verify:

• Check table TI_XPROMOPRICE_0 to verify all promotion details are extracted
• To check if the promotion details are indexed hit the following URL and pass appropriate catentry_id: http://localhost/solr/MC_catalogId_CatalogEntry_locale/select?q=<catentry_id>:

Creating custom Search Profile

IBM WebSphere Commerce (WCS) provides predefined default search profiles to control the fields that are used in the search query. The common search profile uses the product name and short description fields in the search query. These search profiles have a hierarchical structure and can be extended. For this module, extend IBM_findProductByIds_Details to include promotion fields by extending and adding the new profile X<MyCompany>_findProductByIds_Details in wc-search.xml @ Search\xml\config\com.ibm.commerce.catalog-ext

Note: To change the value of an existing property in the WebSphere Commerce search component configuration file, you must create a customized version of the file in this location. The customized version of the file must contain only the changed properties 

The following snippet demonstrates how this can be achieved –

<_config:profile name=" X<MyCompany>_findProductByIds_Details" extends="IBM_findProductByIds_Details"> 
    <_config:query inherits="true"> 
        <_config:postprocessor classname="com.ibm.commerce.foundation.server.services.rest.search.postprocessor.solr.SolrRESTSearchCatalogEntryViewUserDataQueryPostprocessor" /> 
    </_config:query> 
    <_config:result inherits="true"> 
        <_config:field name="CATENTRYPROMO" /> 
    </_config:result> 
</_config:profile>

The component configuration file, wc-component.xml, contains properties to configure various WebSphere Commerce search application features. In the component configuration files, the search properties are grouped into sections. The component configuration file is stored @Search\xml\config\com.ibm.commerce.catalog-ext

Note: To change the value of an existing property in the WebSphere Commerce search component configuration file, you must create a customized version of the file in this location. The customized version of the file must contain only the changed properties 

Modify wc-component.xml to include the custom data:

<_config:valuemappingservice> 
    <_config:valuemapping externalName="CatalogEntryUserDataFieldNameMapping" internalName="CatalogEntryUserDataFieldNameMapping"> 
        <_config:valuemap externalValue="CATENTRYPROMO" internalValue="CATENTRYPROMO" /> 
    </_config:valuemapping> 
</_config:valuemappingservice> 

 Note: CatalogEntryUserDataFieldNameMapping mapping is for defining the mapping from a custom index field name used in the CatalogEntry search index to the field name used in the UserData area in the REST response.

After creating the custom profile, update wc-rest-resourceconfig.xml to include the custom profile located @Search-Rest\WebContent\WEB-INF\config\com.ibm.commerce.rest-ext

Note: To change the value of an existing property in the WebSphere Commerce configuration file, you must create a customized version of the file in this location. The customized version of the file must contain only the changed properties.  

<Resource name="productview"> 
    <GetUri uri="store/{storeId}/productview/byId/{productId}" 
description="Get product by unique ID" searchProfile="IBM_findProductByIds_Details,IBM_findProductByIdsWithAttributesAndAttachments,IBM_findProductByIds_Summary,IBM_findProductByIds_Summary_WithNoEntitlementCheck,IBM_Admin_findProductByIds,X<MyCompany>_findProductByIds_Details"/> 
</Resource> 

Similarly extend IBM_findProductsByCategory and IBM_findProductsBySearchTerm to display promotional prices on Search and PLP pages.

The last and final step would be to make changes in the jsp to pass the custom profile name and display data. For this purpose, let’s modify ProductDisplay.jsp and pass the custom profile in the rest call:

<wcf:rest var="catalogNavigationView" url="${searchHostNamePath}${searchContextPath}/store/${WCParam.storeId}/productview/byId/${productId}" cached="true" > 
    <wcf:param name="langId" value="${langId}"/> 
    <wcf:param name="currency" value="${env_currencyCode}"/> 
    <wcf:param name="responseFormat" value="json"/> 
    <wcf:param name="catalogId" value="${WCParam.catalogId}"/> 
    <wcf:param name="profileName" value="X<MyCompany>_findProductByIds_Details"/> 
</wcf:rest> 

The result of the rest call will contain promotion details- promoid, promoprice & promodescription:

This displays promotional prices in the storefront along with offer badges, promotional texts, and promotional price on PDP, PLP and Search pages in the following manner: