WebSphere Commerce Contract Data Model

IBM WebSphere Commerce Search Entitlement Expression Provider: SolrRestSearchProductEntitlementExpressionProvider Sequence Diagrams

The sequence diagrams below shows the details that how the entitlement expression is created.

Overview

checkEntitlementFlag

 getBuyerContractIds

getHostContractId

getExpression

addFilterQuery

Everything in one diagram

Debugging Catalog Filtering with Contracts

If the catalog filter does not work as expected, do the following checks:

Turn on the logs on the class SolrRESTSearchProductEntiltementExpressionProvider and check what the getExpression method returns:

Check if the expected expression has been created:

select EXPRESSION_ID, TRADING_ID, CATFILTER_ID, MEMBER_ID, cast(QUERY as varchar(1000)) from EXPRESSION

Check what contract ids are returned from the following queries:

Active reseller contracts belong to user_id, org_id or default
SELECT T.trading_id, T.account_id, T.starttime, T.endtime FROM PARTICIPNT P, TRADING T, STORECNTR S WHERE T.trading_id = P.trading_id and T.trading_id = S.contract_id 
and S.store_id =?store_id?  and P.partrole_id = 2 and P.termcond_id is null and (P.member_id = ?member_id? or P.member_id = ?org_id? or P.member_id is null) and T.state = 1 
and T.markfordelete = 0 

Active reseller contracts belong to all ancestor org of this org
SELECT T.trading_id, T.account_id, T.starttime,  T.endtime FROM PARTICIPNT P, TRADING T, STORECNTR S, mbrrel M WHERE T.trading_id = P.trading_id and T.trading_id = S.contract_id and S.store_id = ?store_id? and P.partrole_id = 2 and P.termcond_id is null and P.member_id = M.ancestor_id and M.descendant_id = ?org_id? and T.state = 1 and T.markfordelete = 0 

Active reseller contracts belong to the member group of this user
SELECT T.trading_id, T.account_id,  T.starttime, T.endtime FROM PARTICIPNT P, TRADING T, STORECNTR S, mbrgrpmbr M WHERE T.trading_id = P.trading_id and T.trading_id = S.contract_id and S.store_id = ?store_id? and P.partrole_id = 2 and P.termcond_id is null and P.member_id = M.mbrgrp_id and M.member_id = ?member_id? and M.exclude='0' and T.state = 1 and T.markfordelete = 0 

Check the following tables with more details:

select * from CONTRACT;
select * from TRADING;

select * from CATFILTER;
select * from PRODUCTSET;

select * from PRSETCEREL;

Check if the productset temporary table is correctly populated by the di-preprocess script:

SELECT * FROM TI_PRODUCTSET_0 where catentry_id in (?)

SELECT PRSETCEREL.CATENTRY_ID, PRODUCTSET.PRODUCTSET_ID  
FROM PRSETCEREL, PRODUCTSET, TI_CATENTRY_0  CE
WHERE PRODUCTSET.PRODUCTSET_ID = PRSETCEREL.PRODUCTSET_ID  
AND PRODUCTSET.MARKFORDELETE = 0  
AND PRSETCEREL.CATENTRY_ID=CE.CATENTRY_ID
ORDER BY CATENTRY_ID

Check if the product is properly indexed:

select * from TI_CATENTRY_0 where catentry_id in (1000005836, 1000005837)

SELECT DISTINCT CATENTRY.CATENTRY_ID, CATENTRY.CATENTTYPE_ID FROM CATENTRY, CATGPENREL WHERE CATENTRY.MARKFORDELETE=0 AND CATENTRY.CATENTRY_ID=CATGPENREL.CATENTRY_ID AND CATGPENREL.CATALOG_ID=11101 order by catentry.catentry_id;

Check if the query populated the PRODUCTSET with the expected productset id:

SELECT 
CATENTRY.CATENTRY_ID,CATENTRY.MEMBER_ID,CATENTRY.CATENTTYPE_ID,CATENTRY.PARTNUMBER,CATENTRY.MFPARTNUMBER,
CATENTRY.MFNAME, CATENTRY.BUYABLE, CATENTRY.STARTDATE, CATENTRY.ENDDATE,STORECENT.STOREENT_ID,
CATENTDESC.NAME,CATENTDESC.SHORTDESCRIPTION,CATENTDESC.LONGDESCRIPTION,CATENTDESC.THUMBNAIL,CATENTDESC.FULLIMAGE,
CATENTDESC.KEYWORD, CATENTDESC.PUBLISHED,CATENTSUBS.SUBSCPTYPE_ID,CATENTSUBS.DISALLOW_REC_ORDER,
TI_APGROUP.CATGROUPS APCATGROUP,
TI_APGROUP.CATPATHS APCATPATH,
TI_DPGROUP.CATGROUP DPCATGROUP,
TI_PRODUCTSET.PRODUCTSET,
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_TRY, TI_OFFERPRICE.PRICE_ILS,
TI_LISTPRICE.LISTPRICE_USD, TI_LISTPRICE.LISTPRICE_EUR, TI_LISTPRICE.LISTPRICE_CAD, TI_LISTPRICE.LISTPRICE_CNY,
TI_LISTPRICE.LISTPRICE_JPY, TI_LISTPRICE.LISTPRICE_KRW, TI_LISTPRICE.LISTPRICE_BRL, TI_LISTPRICE.LISTPRICE_TWD,
TI_LISTPRICE.LISTPRICE_PLN, TI_LISTPRICE.LISTPRICE_RUB, TI_LISTPRICE.LISTPRICE_RON, TI_LISTPRICE.LISTPRICE_EGP,
TI_LISTPRICE.LISTPRICE_GBP, TI_LISTPRICE.LISTPRICE_TRY, TI_LISTPRICE.LISTPRICE_ILS,
TI_ADATTR.ATTRIBUTES CATENTRY_ATTRIBUTES,
TI_DPCATENTRY.CATENTRY_PARENT,
TI_GROUPING.GROUPING,
TI_COMPONENTS.COMPONENTS CATENTRY_COMPONENTS,
TI_DCCATENTRY.CATENTRY_CHILD,
TI_CATALOG.CATALOG PARENT_CATALOG_ID,
TI_CATGPENREL.SEQUENCE CATENTRY_SEQUENCE,
TI_ENTGRPPATH.ENTGRPPATH,
TI_SEOURL.SEO_TOKEN,
TI_CATENTDESCOVR.NAME_OVR, TI_CATENTDESCOVR.SHORTDESC_OVR, TI_CATENTDESCOVR.KEYWORD_OVR, TI_CATENTDESCOVR.PUBLISHED_OVR,
TI_DPGRPNAME.CATGRPNAME CATGRPNAME,
TI_CASTB1.CAS_F1 CAS_F1ATTR, TI_CASTB1.CAS_F2 CAS_F2ATTR, TI_CASTB1.CAS_F3 CAS_F3ATTR,TI_CASTB1.CAS_F4 CAS_F4ATTR,TI_CASTB1.CAS_F5 CAS_F5ATTR,
TI_CASTB1.CAS_F6 CAS_F6ATTR,TI_CASTB1.CAS_F7 CAS_F7ATTR, TI_CASTB1.CAS_F8 CAS_F8ATTR,TI_CASTB1.CAS_F9 CAS_F9ATTR,TI_CASTB1.CAS_F10 CAS_F10ATTR,
TI_CASTB1.CAS_F11 CAS_F11ATTR,TI_CASTB1.CAS_F12 CAS_F12ATTR,TI_CASTB1.CAS_F13 CAS_F13ATTR,TI_CASTB1.CAS_F14 CAS_F14ATTR,TI_CASTB1.CAS_F15 CAS_F15ATTR,
TI_CASTB1.CAS_F16 CAS_F16ATTR,TI_CASTB1.CAS_F17 CAS_F17ATTR, TI_CASTB1.CAS_F18 CAS_F18ATTR,TI_CASTB1.CAS_F19 CAS_F19ATTR,TI_CASTB1.CAS_F20 CAS_F20ATTR,
TI_CASTB1.CAS_F21 CAS_F21ATTR,TI_CASTB1.CAS_F22 CAS_F22ATTR,TI_CASTB1.CAS_F23 CAS_F23ATTR,TI_CASTB1.CAS_F24 CAS_F24ATTR,TI_CASTB1.CAS_F25  CAS_F25ATTR,
TI_CASTB1.CAS_F26  CAS_F26ATTR,TI_CASTB1.CAS_F27  CAS_F27ATTR,TI_CASTB1.CAS_F28  CAS_F28ATTR,TI_CASTB1.CAS_F29  CAS_F29ATTR,TI_CASTB1.CAS_F30  CAS_F30ATTR,
TI_CAITB1.CAI_F1, TI_CAITB1.CAI_F2, TI_CAITB1.CAI_F3, TI_CAITB1.CAI_F4, TI_CAITB1.CAI_F5, TI_CAITB1.CAI_F6, TI_CAITB1.CAI_F7, TI_CAITB1.CAI_F8, TI_CAITB1.CAI_F9, TI_CAITB1.CAI_F10,
TI_CAFTB1.CAF_F1, TI_CAFTB1.CAF_F2, TI_CAFTB1.CAF_F3, TI_CAFTB1.CAF_F4, TI_CAFTB1.CAF_F5, TI_CAFTB1.CAF_F6, TI_CAFTB1.CAF_F7, TI_CAFTB1.CAF_F8, TI_CAFTB1.CAF_F9, TI_CAFTB1.CAF_F10,
TI_ATTR.ATTRS ATTRS,
TI_ATTR.ATTRI ATTRI,
TI_ATTR.ATTRF ATTRF,
TI_ATTR.SRCHS SRCHS,
TI_ATTR.SRCHI SRCHI,
TI_ATTR.SRCHF SRCHF,
XCATENTRYWC.DISPPRICEINCARTONLY,
XCATENTRYWC.SEARCHABLE,
XCATENTRYOMSDESC.BVRATING,
XCATENTRYOMSDESC.BVNUMBEROFRATING,
XSEARCHABLEPRODUCTS.IN_STOCK,
XSEARCHABLEPRODUCTS.FSA,
XSEARCHABLEPRODUCTS.PURCHASABLE,
CATENTDESC.AUXDESCRIPTION1,
CATENTDESC.AUXDESCRIPTION2
FROM CATENTRY
INNER JOIN TI_CATENTRY_0 TI_CATENTRY ON (CATENTRY.CATENTRY_ID=TI_CATENTRY.CATENTRY_ID)
LEFT OUTER JOIN STORECENT ON (CATENTRY.CATENTRY_ID=STORECENT.CATENTRY_ID)
LEFT OUTER JOIN CATENTDESC ON (CATENTDESC.CATENTRY_ID=CATENTRY.CATENTRY_ID AND CATENTDESC.LANGUAGE_ID=-1)
LEFT OUTER JOIN CATENTSUBS ON (CATENTSUBS.CATENTRY_ID=CATENTRY.CATENTRY_ID)
LEFT OUTER JOIN TI_DPGROUP_0 TI_DPGROUP ON (CATENTRY.CATENTRY_ID=TI_DPGROUP.CATENTRY_ID)
LEFT OUTER JOIN TI_DPGRPNAME_0_1 TI_DPGRPNAME ON (CATENTRY.CATENTRY_ID=TI_DPGRPNAME.CATENTRY_ID)
LEFT OUTER JOIN TI_APGROUP_0 TI_APGROUP ON (CATENTRY.CATENTRY_ID=TI_APGROUP.CATENTRY_ID)
LEFT OUTER JOIN TI_PRODUCTSET_0 TI_PRODUCTSET ON (CATENTRY.CATENTRY_ID=TI_PRODUCTSET.CATENTRY_ID)
LEFT OUTER JOIN TI_OFFERPRICE_0 TI_OFFERPRICE ON (CATENTRY.CATENTRY_ID=TI_OFFERPRICE.CATENTRY_ID)
LEFT OUTER JOIN TI_LISTPRICE_0 TI_LISTPRICE ON (CATENTRY.CATENTRY_ID=TI_LISTPRICE.CATENTRY_ID)
LEFT OUTER JOIN TI_DPCATENTRY_0 TI_DPCATENTRY ON (CATENTRY.CATENTRY_ID=TI_DPCATENTRY.CATENTRY_ID)
LEFT OUTER JOIN TI_GROUPING_0 TI_GROUPING ON (CATENTRY.CATENTRY_ID=TI_GROUPING.CATENTRY_ID)
LEFT OUTER JOIN TI_COMPONENTS_0 TI_COMPONENTS ON (CATENTRY.CATENTRY_ID=TI_COMPONENTS.CATENTRY_ID)
LEFT OUTER JOIN TI_DCCATENTRY_0 TI_DCCATENTRY ON (CATENTRY.CATENTRY_ID=TI_DCCATENTRY.CATENTRY_ID)
LEFT OUTER JOIN TI_CATALOG_0 TI_CATALOG ON (CATENTRY.CATENTRY_ID=TI_CATALOG.CATENTRY_ID)
LEFT OUTER JOIN TI_CATGPENREL_0 TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
LEFT OUTER JOIN TI_ENTGRPPATH_0 TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
LEFT OUTER JOIN TI_SEOURL_0_1 TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
LEFT OUTER JOIN TI_CEDSOVR_0_1 TI_CATENTDESCOVR ON (CATENTRY.CATENTRY_ID=TI_CATENTDESCOVR.CATENTRY_ID)
LEFT OUTER JOIN TI_CASTB1_0_1 TI_CASTB1 ON (CATENTRY.CATENTRY_ID=TI_CASTB1.CATENTRY_ID)
LEFT OUTER JOIN TI_CAITB1_0_1 TI_CAITB1 ON (CATENTRY.CATENTRY_ID=TI_CAITB1.CATENTRY_ID)
LEFT OUTER JOIN TI_CAFTB1_0_1 TI_CAFTB1 ON (CATENTRY.CATENTRY_ID=TI_CAFTB1.CATENTRY_ID)
LEFT OUTER JOIN TI_ATTR_0_1 TI_ATTR ON (CATENTRY.CATENTRY_ID=TI_ATTR.CATENTRY_ID)
LEFT OUTER JOIN TI_ADATTR_0_1 TI_ADATTR ON (CATENTRY.CATENTRY_ID=TI_ADATTR.CATENTRY_ID)
LEFT OUTER JOIN XCATENTRYWC ON (CATENTRY.CATENTRY_ID=XCATENTRYWC.CATENTRY_ID)
LEFT OUTER JOIN XCATENTRYOMSDESC ON (CATENTRY.CATENTRY_ID=XCATENTRYOMSDESC.CATENTRY_ID)
LEFT OUTER JOIN XSEARCHABLEPRODUCTS ON (CATENTRY.CATENTRY_ID=XSEARCHABLEPRODUCTS.CATENTRY_ID)
WHERE  CATENTRY.CATENTTYPE_ID in ('ProductBean', 'ItemBean', 'PackageBean')

Check the Search ProductView Service accepts contract id and checkEntitlement flag properly:

http://localhost:80/search/resources/store/10001/productview/bySearchTerm/american?catalogId=20001&langId=-1&currency=USD&responseFormat=json&pageSize=12&pageNumber=1&profileName=findProductsBySearchTerm&checkEntitlement=true&contractId=30001&priceMode=mixed

RAD 8 Ant not start error

RAD 8 Ant not start error

The error message: 
Could not find the main class: =. Program will exit.

Solution:
Right click build.xml file, follow
Run As -> 2 Ant Build... (not the first choice 1 Ant Build)
Choose the JRE tab
In Runtime JRE: section, choose Run in the same JRE as the workspace