Example: Indexing new currency offer prices
In this example, WebSphere Commerce search is customized to index new currency
offer prices.
Before you begin
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, anddevelopment_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, andtarget_db
is the directory name for your database.
- WCDE_installdir\search\pre-processConfig\MC_masterCatalogId\development_db
-
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.
Update this code to add a column for your new currency. Adding the following the code to define the column,<_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"/>
ReplacePRICE_XXX DECIMAL(20,5)
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
andTI_DKITPRICE_X
tables, where X is the numeric value for the tables for your store.For example,TI_BUNDLEPRICE_0
andTI_DKITPRICE_0
. -
Locate the following code that defines the configuration property that identifies the supported
currency codes:
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"/>
Where<_config:property name="currencyCode" value="USD, EUR, CAD, CNY, JPY, KRW, BRL, TWD, PLN, RON, RUB, EGP, GBP, XXX"/>
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.
Update the SQL statement to include your new currency. Add the code:<_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
ReplaceSUM(OP.PRICE_XXX) PRICE_XXX
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:
Add a column mapping for your new currency column by adding the code<_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>
Replace<_config:column-column queryColumn="PRICE_XXX" tableColumn="PRICE_XXX" />
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.
-
Go to the following directory:
-
Preprocess the search index data.
-
Query the
TI_OFFERPRICE_X
,TI_BUNDLEPRICE_X
, andTI_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
andTI_DKITPRICE_0
.
-
Query the
-
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 thequery
anddeltaImportQuery
definitions to include your new currency. Add the following code to the list of currency offer prices within the query:
ReplaceTI_OFFERPRICE.PRICE_XXX
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
anddeltaImportQuery
definitions for bundles and dynamic kits.Update the definitions forentity name="Bundle"
by adding the following code:
Update the definitions forTI_BUNDLEPRICE.PRICE_XXX
entity name="DynamicKit"
by adding the following code:TI_DKITPRICE.PRICE_XXX
-
Locate the field column definitions for the supported currencies for products:
Add a field column definition for your new currency by adding the code:<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"/>
Replace<field column="PRICE_XXX" name="price_XXX"/>
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.
-
Go to the following directory:
-
Enable the price range facet:
- Insert a new attribute into the SRCHATTR table:
Where:INSERT INTO srchattr (SRCHATTR_ID, INDEXSCOPE, INDEXTYPE, IDENTIFIER) values (primary_key, '0', 'CatalogEntry', '_cat.OfferPrice_XXX');
- primary_key is an unused key in the SRCHATTR_ID column.
- XXX is the new currency code, such as SEK.
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
Where: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 *}');
- 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
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');
- Insert a new attribute into the SRCHATTR table:
- 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:
Where<float name="price_XXX">offer_price</float>
XXX
is the new currency code, such as SEK, andoffer_price
is the price for a product in the new currency, such as 100.0.