Saturday, 22 August 2015

Solr settings and full indexing (Attribute task update)

SOLR is mainly used for :
  • Indexing
  • Querying
   SOLR is a product of apache and having the inbuilt server named jeety.
Mainly the SOLR has the data types(normal java)that has to be mapped in the solr manner where these are called as dynamics fields(SOLR specific).
       Data Types = dynamic fields
Example : <field name ="catentry_id" type="string" indexed="true" stored="true" required = "true"                         multiValued=£false"/>
Here in the above example :
  • type is the solr datatype.
  • name is the WCS datatype (Field name of table).
  • indexed must be true (used for indexing in solr search).
  • stored  mainly used to store the indexed field.
     If we want to customize by adding any table data that need to be indexed then we need to update the schema.xml.
      In the schema.xml we will find the file names like stopwords.txt and protwords.txt which files are responsible for the words that can be skipped during the search filtering process.
      Steps to create the SOLR setup:
  1.       setupSearchIndex.bat :
Syntax :
       D:\IBM\WCDE_ENT70\bin>setupSearchIndex.bat -masterCatalogId <mastercatalog_id>
     Actually the search folder gets created in the home directory which contains the following files mentioned below:
  •     solrconfig.xml (requestHandlers, search components).
  •      wc-data-config.xml ( Mainly for establishing the server connections, Solr Data Import Handler configuration for DB config).
  •     Schema.xml (mainly used for mapping the tables and fields that has to be indexed by solr)
          Make sure the server is in up and running when running this setupSearchIndex.bat
         2.di-preProcess.bat :
          <toolkit-home> \components\foundation\bin\di-preprocess.bat
       Syntax :
           D:\IBM\WCDE_ENT70\bin>di-preprocess.bat D:\IBM\WCDE_ENT70\search\pre-   processConfig\MC_<mastercat_id>\Cloudscape -localename en_US
       Actually this is used to push the data into the temporary table after the index has took place.
       3.di-buildIndex.bat :
        <toolkit-home> \components\foundation\bin\di-buildIndex.bat
          D:\IBM\WCDE_ENT70\bin>di-buildindex.bat -masterCatalogId <mastercat_id> -localename en_US
      Actually this step is the final batch that we run during the whole solr indexing process.    Especially this is mainly meant to get the indexing completed.
     The following url is used for testing the solr indexing is done properly or not is :
      Main files to be remembered for SOLR update are below mentioned:
   ·  D:\IBM\WCDE_ENT70\search\solr\home\MC_10351\en_US\CatalogEntry\conf\solrconfig.xml
   ·  D:\IBM\WCDE_ENT70\search\solr\home\MC_10351\en_US\CatalogEntry\conf\wc-data-   config.xml
   ·  D:\IBM\WCDE_ENT70\search\solr\home\MC_10351\en_US\CatalogEntry\conf\schema.xml
   ·  D:\IBM\WCDE_ENT70\search\pre-processConfig\MC_10351\Cloudscape\ALL XML files related to indexing of solr
   ·  The whole files (esply the xml) and folders (search present in toolkit home location) that are created during the process of setupSearchIndex.bat running are already present and in the  sample folder present in the below mentioned path
Task related to attribute dictionary model
    1.     Start the management center
    2.     Login to CMC and select the store(AURORA) from the drop down.
    3.     Select the catalog and select the attribute dictionary
Create a new attribute under this section and give the required values.
NOTE: Here keep in mind while creating the attribute dictionary attribute always select the attribute dictionary attribute with the predefined value.
         Under this step 3 we need fill the attribute values information with any value (for    EX: sequence can be n value and value section with any text value).
           Remaining fields can be left free.
          Once done select save and close.
       SQL select * from attr where identifier='<Name given in the step 3 for attribute >';
       SQL select * from attrval where attr_id= <select the attr_id which we got from the above sql>;
    4.     Now select a product to associate the create attribute ( step 3 ) for a specific product     for getting the catentryattr table updated with the associated attribute values.
       SQL : select * from catentryattr where attr_id=<select the attr_id which we got from the above sql>;
     Here from the above query we can see that the catentryattr table is being updated with  the associated attribute that we created in the step 3 above.
    5.     Now let’s check the solr part once the above steps are completed successfully, for   this we need to run the preprocess to get the temporary table updated with the above created table
     For the checking of above step lets verify the date that’s being updated in the attribute  related temporary date given in the OOB solr indexing process.( i.e. TI_ATTR_0_#lang_tag# where the lang id has to be mentioned appropriate and this table name we can get it from the preprocess xml files that are created during the setupSearchIndex.bat file running that is the search folder content (wc-dataimport-preprocess-attribute.xml) where we can see the temporary tables creation queries.)
     NOTE : while running this running this preprocess.bat keep the server in stop mode.
    6.     Now if the preprocess is completed with no errors then check the TI_ATTR_0_#lang_tag# table whether the date has been updated with the appropriate   value that was given for temporary table update.
       SQL : select * from TI_ATTR_0_1;
     You will find the updated data with field being updated with the name like
         'ads_f10501: <Value given in step3>||ads_f10501_ntk_cs:<Value given in step3>'
    7.     With this above correct value update in the temporary table we can conclude  preprocess has been done correctly.
    8.     Now the final step is buildindex in this we need to go for full build so that the data is    pushed to solr search engine so that we can retrieve when we search in the site. J J
    9.     Once the di-buildIndex.bat step is completed with no error codes. Then we need check the indexed data by running the below url in the browser.
    We can find the data indexed properly with details as expected with this the solr indexing for attribute dictionary task has been updated J J

Saturday, 20 June 2015

Creating new page layout for Product display page.

In this tutorial let’s discuss of how to create a custom page layout. For this we will take the sample of the product page layout which is already present and on using this existing layout we will create a new layout for the product page. For this we will just modify the existing code for getting familiar of creation of page layout. Later you guys can try trails with your own styles J
Lets start the tast prior to this lets have some basic information like the below :
Store_id, storeent_id, member_id, Max_pagelayout_id.
Lets gather the information by simple queries,
Open the database access JSP,
  • Execute : select * from STOREENT where identifier ='Aurora' ; (This query gives STOREENT_ID and MEMBER_ID of our store)
  • Execute : select max(PAGELAYOUT_ID) from PAGELAYOUT; (This query gives maximum Pagelayout_id used. So we can make Max+1 for our new page layout)

Creating the new product page layout JSP:
                We will create a new product page layout by just copying the default product page layout and modify it. In the default product page layout, we will find a double e-marketing spot container. Lets remove this code part for our newly creating page layout.
Open the toolkit and search(shft+ctrl+R) ProductDisplay.jsp
Copy and paste there in the same folder(/Stores/WebContent/Aurora/Layouts/).
Then rename the copied file to XProductDisplay.jsp.
Open the newly copied file and start modifying the code by removing the double e-spot container section. Just check-out the sample code part.

<!—Start Double E-Spot Container à
<div class=”widget_double_espot_container_position”>
<div class=”widget_double_espot_container”>
<c:when test=”${env_fetchMarketingDetailsOnLoad}”>
<div dojoType=”wc.widget.RefreshArea” id=”DoubleContentAreaESpot_Widget” controllerId=”DoubleContentAreaESpot_Controller”>
<c:import url=”${env_jspStoreDir}Widgets/Espot/ContentRecommendation/ContentRecommendation.jsp>
      <c:param name=”emsName” value=”CatalogBanner_Content” />
      <c:param name=”numberContentPerRow” value=”2” />
      <c:param name=”catalogId” value=”${catalogId}” />
<!—End Double E-Spot Container -> 
  • Lets add some CSS to this newly created page layout. Check-out the sample code part below,

<!-- Product Image and Product Information -->
<style type=”text/css”>
margin-top : 36px;
<!-- Content with Right Sidebar -->
  • Now finally modification part is done and now save the file J.

Now lets define the struts entries for the newly created pagelayout.
We will be updating the struts entries by following the below steps,
  • Open struts-config-ext.xml and paste the following code part before to </global-forwards> tag

<!—modified for new page layout -->
<forward className="" name="XProductPageLayoutView/11001" path="/Layouts/ XProductDisplay.jsp "/>
<!—modified for new page layout -->
@Note : the store_id may be different at your end.
  • Under the action-mappings update the following code part exactly before the </action-mappings> tag

<!—modified for new page layout -->
<action path=”/XProductPageLayoutView” type=””>
<set-property property=”credentialsAccepted” value=”10101:P”/>
<!—modified for new page layout -->
  • Save the struts file after updating all the modifications.

Update Struts configuration :
  • we can update the struts by restarting the server or else
  • we can update in the commerce administration console
  • both the steps are doing the same task finally.

Now lets add the new thumbnail images in the newly created product page layout:
Copy the images (i.e. product_thumb_large.png and product_thumb_small.png) from stores>web content > Aurora > images > layouts. Edit the images by removing double e-spot rename as XProduct_thumb_small.png and XProduct_thumb_large.png and paste it under the same path.
Register the new product page layout in the DB:
  • Restart the server after making all the above mentioned modifications
  • Open the DB access JSP from the hintsandtips.html guide.
  • Copy the below script and submit the query.

insert into pagelayout(PAGELAYOUT_ID, NAME, MEMBER_ID,
(100067, 'Layout used for dress products',7000000000000000101, 10101, 'ProductPage', null,
'Web', 1, 0, 'This layout contains the following widgets: 1. Header, 2. Product image, 3. Product
promotions, 4. Product name, price and ratings, 5. Product short description, 6.Product attributes
and actions, 7. Inventory details, 8. Merchandising associations, 9. E-Marketing Spot for Catalog
entries, 10. Footer.', '/images/layouts/XProduct_thumb_small.png',
'/images/layouts/XProduct_thumb_large.png', 'XProductPageLayoutView');
  • Do remember that the above query doesn’t throw any exceptions.

Apply the New layout and start testing :
  1. Open the CMC and select Page layout apply our product layout
  2. Select the Aurora store
Assign the new layout for the product
  • Store pages for Aurora > Apparel > Women > Dresses > Catalog Entry Page layout properties > Product PageLayout Assignments.
  • Drag & Drop the newly updated page layout.
  • Enter today date and time for the start date in the section
  • Select save and close option.

Test the new layout :
  1. Publish the server
  2. Reload the Aurora home page and navigate to Departments > Apparel > Women > dresses
  3. Select an item. Our custom page layout will come up.
  4. Now its possible to modify according to our own desired way for any product page with the layout of the same modifications.

@Let me know if any thing major obligation faced during the modifications.

Tuesday, 16 June 2015

Commerce composer related data..

Commerce composer important tables:


Some of the important queries:

Select * from PLWIDGET where PAGELAYOUT_ID in (select PAGELAYOUT_ID from PAGELAYOUT where name like 'Sample%');
Select * from PLWIDGETDEF where IDENTIFIER like 'Sample%';
Select * from PLSTOREWIDGET where PLWIDGETDEF_ID in (select PLWIDGETDEF_ID from PLWIDGETDEF where IDENTIFIER like 'Sample%');
Select * from PAGELAYOUT where name like 'Sample%';
 from PLWIDGET where PAGELAYOUT_ID in (select PAGELAYOUT_ID from PAGELAYOUT where name like 'Sample%'));

Select * from PLWIDGETSLOT where PAGELAYOUT_ID in (select PAGELAYOUT_ID from PAGELAYOUT where name like 'Sample%');

Do share the related data if anything found so that it can be helpful to others tooo...

Wednesday, 1 April 2015

Promotion Related DB queries.

--This table contains all the Promotion that is created and it can be identified by the name Under the column "NAME"
select * from PX_PROMOTION where PX_Promotion_id=10000301;

-- This table contains all the promotions that are created and the promotion name is present under "CODE" column
select * from calcode;

-- This table contains all the promotion codes available for the particular store.
select * from px_cdpool;

-- This table contains all the promotion code information for relationships with a promotion.
select * from PX_CDPROMO;

-- This table contains all the promotion code usage information.
select * from PX_CDUSAGE;

-- This table contains all of the coupons issued to individual customers.
select * from PX_COUPON;

-- This table contains promotion description information.
select * from PX_DESCRIPTION ;

-- This table Contains the promotion group information (i.e. under the grpname we can classify the type of promotion it belongs to).
select * from PX_GROUP;

-- This table contains promotion policies (based on the storeent_id we can classify the promotions and also we can get the status of the promotion).
select * from PX_POLICY;

-- This table contains details about how promotions are applied to an order.
select * from PX_PROMOARG;

-- This table contains statistics about promotion usage.
select * from PX_USAGE;

Promotion Data Model and its Life-Cycle

    Promotion Data Model


Promotions provide you the ability to offer customers incentives to purchase. WebSphere Commerce supports numerous types of promotions. For example, price promotions that include offering simple discounts; merchandise specials such as gifts with purchase and buy-one-get-one. Additional service promotions are available that include offering reduced shipping costs.

Business users create and manage promotions with the Promotions tool in the Management Center.
Promotion lifecycle

A business user creates a promotion in the inactive state. From that point forward, the promotion can be activated, modified, and deleted.

The following diagram depicts the life-cycle of a promotion.

  • When a promotion is first created, it is inactive.
  • When an inactive promotion is activated, a new version of that promotion is created and marked active. The original promotion is then marked as obsolete.
  • An active promotion can be withdrawn, marking it as inactive.
  • Promotions are static. When changes are made to a promotion, a new revision is made. The new revision inherits the state of the promotion before the change. The original promotion is then marked as obsolete.
  • Promotions in either active or inactive state can be modified or deleted. The changes take effect immediately after they are made.