Example: Indexing new currency offer prices

In this example, WebSphere Commerce search is customized to index new currency offer prices.

Before you begin

Ensure that you identify the currency and currency code that you want to add to WebSphere Commerce and index. For more information about the currencies and currency codes that WebSphere Commerce supports by default, see Supported currencies.

Procedure

Configure the search preprocessor:

Go to the following directory:
- WCDE_installdir\search\pre-processConfig\MC_masterCatalogId\development_db
  Where MC_masterCatalogId is the directory name for your master catalog files, and development_db is the name of the directory for your development database files.
- WC_installdir/instances/instance_name/search/pre-processConfig/MC_masterCatalogId/target_db
  Where instance_name is the directory name for WebSphere Commerce instance files, and target_db is the directory name for your database.
Open the offer price preprocess configuration file, wc-dataimport-preprocess-offerprice.xml for editing to update this file to include offer prices in your new currency.
Note: If the WebSphere Commerce instance is previously configured to support other currencies than the default supported currencies, your file can contain other currencies than the currencies identified in the following code snippet examples.

Locate the following code that defines your TI_OFFERPRICE_X table, where X is the numeric value for the offer price table for your store.

For example, TI_OFFERPRICE_0. This table includes the currency offer prices.

<_config:table definition="CREATE TABLE TI_OFFERPRICE_X (CATENTRY_ID BIGINT NOT NULL, PRICE_USD DECIMAL(20,5) , PRICE_EUR DECIMAL(20,5) , 
PRICE_CAD DECIMAL(20,5) , PRICE_CNY DECIMAL(20,5) , PRICE_JPY DECIMAL(20,5) , PRICE_KRW DECIMAL(20,5) , PRICE_BRL DECIMAL(20,5) ,
PRICE_TWD DECIMAL(20,5) , PRICE_PLN DECIMAL(20,5) , PRICE_RON DECIMAL(20,5) , PRICE_RUB DECIMAL(20,5) , PRICE_EGP DECIMAL(20,5) ,
PRICE_GBP DECIMAL(20,5) , PRIMARY KEY (CATENTRY_ID))" name="TI_OFFERPRICE_X"/>

Update this code to add a column for your new currency. Adding the following the code to define the column,

PRICE_XXX DECIMAL(20,5)

Replace XXX with the currency code for your new currency, such as SEK for Swedish Krona. Your updated table definition can resemble the following code snippet:

<_config:table definition="CREATE TABLE TI_OFFERPRICE_0 (CATENTRY_ID BIGINT NOT NULL, PRICE_USD DECIMAL(20,5) , PRICE_EUR DECIMAL(20,5) , 
PRICE_CAD DECIMAL(20,5) , PRICE_CNY DECIMAL(20,5) , PRICE_JPY DECIMAL(20,5) , PRICE_KRW DECIMAL(20,5) , PRICE_BRL DECIMAL(20,5) ,
PRICE_TWD DECIMAL(20,5) , PRICE_PLN DECIMAL(20,5) , PRICE_RON DECIMAL(20,5) , PRICE_RUB DECIMAL(20,5) , PRICE_EGP DECIMAL(20,5) ,
PRICE_GBP DECIMAL(20,5) , PRICE_XXX DECIMAL(20,5) , PRIMARY KEY (CATENTRY_ID))" name="TI_OFFERPRICE_0"/>

Repeat step 1.c to update the table definitions for the TI_BUNDLEPRICE_X and TI_DKITPRICE_X tables, where X is the numeric value for the tables for your store.
For example, TI_BUNDLEPRICE_0 and TI_DKITPRICE_0.
Locate the following code that defines the configuration property that identifies the supported currency codes:
```
<_config:property name="currencyCode" value="USD, EUR, CAD, CNY, JPY, KRW, BRL, TWD, PLN, RON, RUB, EGP, GBP"/>
```
Update the values for this configuration property to add your new currency code. For example, your updated configuration property and values can resemble the following code snippet:
```
<_config:property name="currencyCode" value="USD, EUR, CAD, CNY, JPY, KRW, BRL, TWD, PLN, RON, RUB, EGP, GBP, XXX"/>
```
Where XXX is your new currency code, such as SEK.

Locate the following code that defines the select SQL statement for calculating the sum of offer prices for bundles in each supported currency.

<_config:query sql="SELECT CATENTREL.CATENTRY_ID_PARENT, SUM(OP.PRICE_USD) PRICE_USD, SUM(OP.PRICE_EUR) PRICE_EUR, 
  SUM(OP.PRICE_CAD) PRICE_CAD, SUM(OP.PRICE_CNY) PRICE_CNY, SUM(OP.PRICE_JPY) PRICE_JPY, 
  SUM(OP.PRICE_KRW) PRICE_KRW, SUM(OP.PRICE_BRL) PRICE_BRL, SUM(OP.PRICE_TWD) PRICE_TWD,
  SUM(OP.PRICE_PLN) PRICE_PLN, SUM(OP.PRICE_RON) PRICE_RON, SUM(OP.PRICE_RUB) PRICE_RUB,
  SUM(OP.PRICE_EGP) PRICE_EGP, SUM(OP.PRICE_GBP) PRICE_GBP

Update the SQL statement to include your new currency. Add the code:

SUM(OP.PRICE_XXX) PRICE_XXX

Replace XXX with your new currency code, such as SEK. Your updated select statement can resemble the following code snippet:

<_config:query sql="SELECT CATENTREL.CATENTRY_ID_PARENT, SUM(OP.PRICE_USD) PRICE_USD, SUM(OP.PRICE_EUR) PRICE_EUR, 
  SUM(OP.PRICE_CAD) PRICE_CAD, SUM(OP.PRICE_CNY) PRICE_CNY, SUM(OP.PRICE_JPY) PRICE_JPY, 
  SUM(OP.PRICE_KRW) PRICE_KRW, SUM(OP.PRICE_BRL) PRICE_BRL, SUM(OP.PRICE_TWD) PRICE_TWD,
  SUM(OP.PRICE_PLN) PRICE_PLN, SUM(OP.PRICE_RON) PRICE_RON, SUM(OP.PRICE_RUB) PRICE_RUB,
  SUM(OP.PRICE_EGP) PRICE_EGP, SUM(OP.PRICE_GBP) PRICE_GBP, SUM(OP.PRICE_XXX) PRICE_XXX

Repeat step 1.f to update the following select SQL statement for calculating the sum of offer prices for dynamic kits:

<_config:query sql="SELECT DKPDCCATENTREL.CATENTRY_ID, SUM(OP.PRICE_USD) PRICE_USD, SUM(OP.PRICE_EUR) PRICE_EUR, 
  SUM(OP.PRICE_CAD) PRICE_CAD, SUM(OP.PRICE_CNY) PRICE_CNY, SUM(OP.PRICE_JPY) PRICE_JPY,
  SUM(OP.PRICE_KRW) PRICE_KRW, SUM(OP.PRICE_BRL) PRICE_BRL, SUM(OP.PRICE_TWD) PRICE_TWD,
  SUM(OP.PRICE_PLN) PRICE_PLN, SUM(OP.PRICE_RON) PRICE_RON, SUM(OP.PRICE_RUB) PRICE_RUB,
  SUM(OP.PRICE_EGP) PRICE_EGP, SUM(OP.PRICE_GBP) PRICE_GBP

Locate the following code that configures the column mappings for the CATENTRY_ID_PARENT column for the supported currencies in your WebSphere Commerce instance:

<_config:mapping>
<_config:key queryColumn="CATENTRY_ID_PARENT" tableColumn="CATENTRY_ID"/>
<_config:column-mapping>
<_config:column-column-mapping>
  <_config:column-column queryColumn="PRICE_USD" tableColumn="PRICE_USD" />
  <_config:column-column queryColumn="PRICE_EUR" tableColumn="PRICE_EUR" />
  <_config:column-column queryColumn="PRICE_CAD" tableColumn="PRICE_CAD" />
  <_config:column-column queryColumn="PRICE_CNY" tableColumn="PRICE_CNY" />
  <_config:column-column queryColumn="PRICE_JPY" tableColumn="PRICE_JPY" />
  <_config:column-column queryColumn="PRICE_KRW" tableColumn="PRICE_KRW" />
  <_config:column-column queryColumn="PRICE_BRL" tableColumn="PRICE_BRL" />
  <_config:column-column queryColumn="PRICE_TWD" tableColumn="PRICE_TWD" />
  <_config:column-column queryColumn="PRICE_PLN" tableColumn="PRICE_PLN" />
  <_config:column-column queryColumn="PRICE_RON" tableColumn="PRICE_RON" />
  <_config:column-column queryColumn="PRICE_RUB" tableColumn="PRICE_RUB" />
  <_config:column-column queryColumn="PRICE_EGP" tableColumn="PRICE_EGP" />
  <_config:column-column queryColumn="PRICE_GBP" tableColumn="PRICE_GBP" />
</_config:column-column-mapping>
</_config:column-mapping>
</_config:mapping>

Add a column mapping for your new currency column by adding the code

<_config:column-column queryColumn="PRICE_XXX" tableColumn="PRICE_XXX" />

Replace XXX with your new currency code, such as SEK. Your updated code, can resemble the following code snippet:

<_config:mapping>
<_config:key queryColumn="CATENTRY_ID_PARENT" tableColumn="CATENTRY_ID"/>
<_config:column-mapping>
<_config:column-column-mapping>
  <_config:column-column queryColumn="PRICE_USD" tableColumn="PRICE_USD" />
  <_config:column-column queryColumn="PRICE_EUR" tableColumn="PRICE_EUR" />
  <_config:column-column queryColumn="PRICE_CAD" tableColumn="PRICE_CAD" />
  <_config:column-column queryColumn="PRICE_CNY" tableColumn="PRICE_CNY" />
  <_config:column-column queryColumn="PRICE_JPY" tableColumn="PRICE_JPY" />
  <_config:column-column queryColumn="PRICE_KRW" tableColumn="PRICE_KRW" />
  <_config:column-column queryColumn="PRICE_BRL" tableColumn="PRICE_BRL" />
  <_config:column-column queryColumn="PRICE_TWD" tableColumn="PRICE_TWD" />
  <_config:column-column queryColumn="PRICE_PLN" tableColumn="PRICE_PLN" />
  <_config:column-column queryColumn="PRICE_RON" tableColumn="PRICE_RON" />
  <_config:column-column queryColumn="PRICE_RUB" tableColumn="PRICE_RUB" />
  <_config:column-column queryColumn="PRICE_EGP" tableColumn="PRICE_EGP" />
  <_config:column-column queryColumn="PRICE_GBP" tableColumn="PRICE_GBP" />
  <_config:column-column queryColumn="PRICE_XXX" tableColumn="PRICE_XXX" />
</_config:column-column-mapping>
</_config:column-mapping>
</_config:mapping>

Repeat step 1.h to update the column mapping configuration for the CATENTRY_ID column.
Save your changes and close the file.

Preprocess the search index data.
1. Query the TI_OFFERPRICE_X, TI_BUNDLEPRICE_X, and TI_DKITPRICE_X tables to confirm that the offer prices for your currencies are populated. Replace X with the numeric value for the tables for your store.
  For example, TI_OFFERPRICE_0, TI_BUNDLEPRICE_0 and TI_DKITPRICE_0.

Configure the Data Import Handler mapping.

Go to the following directory:
- WCDE_installdir\search\solr\home
- WC_installdir/instances/instance_name/search/solr/home
This directory contains the master catalog folder, which contains configurations files for each supported language. If you have more than one language folder, complete this step for every language.
Open the MC_masterCatalogId/en_US/CatalogEntry/conf/wc-data-config.xml file for editing.
Locate the code entity name="Product" within the element <document name="CatalogEntry"> in the file.
Update the query and deltaImportQuery definitions to include your new currency. Add the following code to the list of currency offer prices within the query:
```
TI_OFFERPRICE.PRICE_XXX
```
Replace XXX with the currency code for your new currency, such as SEK for Swedish Krona. Your updated code, can resemble the following code snippet:
```
TI_OFFERPRICE.PRICE_USD, TI_OFFERPRICE.PRICE_EUR, TI_OFFERPRICE.PRICE_CAD, TI_OFFERPRICE.PRICE_CNY,
TI_OFFERPRICE.PRICE_JPY, TI_OFFERPRICE.PRICE_KRW, TI_OFFERPRICE.PRICE_BRL, TI_OFFERPRICE.PRICE_TWD,
TI_OFFERPRICE.PRICE_PLN, TI_OFFERPRICE.PRICE_RUB, TI_OFFERPRICE.PRICE_RON, TI_OFFERPRICE.PRICE_EGP,
TI_OFFERPRICE.PRICE_GBP, TI_OFFERPRICE.PRICE_XXX,
```
Repeat step 3.c to update the query and deltaImportQuery definitions for bundles and dynamic kits.
Update the definitions for entity name="Bundle" by adding the following code:
```
TI_BUNDLEPRICE.PRICE_XXX
```
Update the definitions for entity name="DynamicKit" by adding the following code:
```
TI_DKITPRICE.PRICE_XXX
```

Locate the field column definitions for the supported currencies for products:

<field column="PRICE_USD" name="price_USD"/>
<field column="PRICE_EUR" name="price_EUR"/>
<field column="PRICE_CAD" name="price_CAD"/>
<field column="PRICE_CNY" name="price_CNY"/>
<field column="PRICE_JPY" name="price_JPY"/>
<field column="PRICE_KRW" name="price_KRW"/>
<field column="PRICE_BRL" name="price_BRL"/>
<field column="PRICE_TWD" name="price_TWD"/>
<field column="PRICE_PLN" name="price_PLN"/>
<field column="PRICE_RON" name="price_RON"/>
<field column="PRICE_RUB" name="price_RUB"/>
<field column="PRICE_EGP" name="price_EGP"/>
<field column="PRICE_GBP" name="price_GBP"/>

Add a field column definition for your new currency by adding the code:

<field column="PRICE_XXX" name="price_XXX"/>

Replace XXX with the currency code for your new currency, such as SEK for Swedish Krona. Your updated code, can resemble the following code snippet:

<field column="PRICE_USD" name="price_USD"/>
<field column="PRICE_EUR" name="price_EUR"/>
<field column="PRICE_CAD" name="price_CAD"/>
<field column="PRICE_CNY" name="price_CNY"/>
<field column="PRICE_JPY" name="price_JPY"/>
<field column="PRICE_KRW" name="price_KRW"/>
<field column="PRICE_BRL" name="price_BRL"/>
<field column="PRICE_TWD" name="price_TWD"/>
<field column="PRICE_PLN" name="price_PLN"/>
<field column="PRICE_RON" name="price_RON"/>
<field column="PRICE_RUB" name="price_RUB"/>
<field column="PRICE_EGP" name="price_EGP"/>
<field column="PRICE_GBP" name="price_GBP"/>
<field column="PRICE_XXX" name="price_XXX"/>

Repeat step 3.e to add a field column definition for your new currency for bundles and dynamic kits.
Save your changes and close the file.

Enable the price range facet:

Insert a new attribute into the SRCHATTR table:

INSERT INTO srchattr (SRCHATTR_ID, INDEXSCOPE, INDEXTYPE, IDENTIFIER) values (primary_key, '0', 'CatalogEntry', '_cat.OfferPrice_XXX');

Where:

primary_key is an unused key in the SRCHATTR_ID column.
XXX is the new currency code, such as SEK.

For example:

INSERT INTO srchattr (SRCHATTR_ID, INDEXSCOPE, INDEXTYPE, IDENTIFIER) VALUES (10000, '0', 'CatalogEntry', '_cat.OfferPrice_SEK');

Insert a new record into the SRCHATTRPROP table for price ranges

INSERT INTO srchattrprop (SRCHATTR_ID, PROPERTYNAME, PROPERTYVALUE) VALUES (srchattr_id, 'facet', 'price_XXX:{* Number1} Number1;{Number1 Number2} Number2;{Number2 Number3} Number3;{Number3 Number4} Number4;{Number4 Number5} Number5;{Number5 *}');

Where:

srchattr_id is the primary key that is used in the previous query.
XXX is the new currency code, such as SEK.
Number1 through Number5 are the values for price ranges in the facet

For example:

INSERT INTO srchattrprop (SRCHATTR_ID, PROPERTYNAME, PROPERTYVALUE) VALUES (10000, 'facet', 'price_SEK:{* 100} 100;{100 200} 200;{200 300} 300;{300 400} 400;{400 500} 500;{500 *}');

Insert a new record into the FACET table.

For example:

INSERT INTO facet (FACET_ID, SRCHATTR_ID, SELECTION, SORT_ORDER, KEYWORD_SEARCH, ZERO_DISPLAY, STOREENT_ID, MAX_DISPLAY, SEQUENCE ) values (10000,10000,0,0,1,0,0,20,0.0);

Insert a new record into the FACETDESC table.

For example:

insert into facetdesc (FACET_ID, LANGUAGE_ID, NAME, DESCRIPTION) values (10000,-1,'Price-SEK','price facet in SEK');

Restart the WebSphere Commerce search server.
Run the di-buildindex utility.
Verify that your new currency data is indexed by entering the following URL in your web browser:
- http://localhost/solr/MC_10101_CatalogEntry_en_US/select?q=*:*
- http://yoursolrserverhost:3737/solr/MC_10101_CatalogEntry_en_US/select?q=*:*
A sample snippet should display that contains the following snippet:
```
<float name="price_XXX">offer_price</float>
```
Where XXX is the new currency code, such as SEK, and offer_price is the price for a product in the new currency, such as 100.0.