How to sync bulk products based on product code to online catalog?

[Former Member]

Hi All,

We have many products coming datahub and loaded into hybris system(under staged catalog) but only half of them has values to all the product related attributes.However, we were able to find out the product codes for such products using flexiable search queries, but the real challenge over here is how to sync them all at once.

Thanks in advance

G Gopinath.

[arvind-kumar_avinash]

Some of the options are:

a. Using groovy script (switch on the Commit Mode and execute the following code in hAC):

   flexibleSearchService.search(/SELECT {pk} FROM {Product} WHERE {code} IN ({{your-flexible-search-query-to-select-the-product-codes}})/).result.each {
     it.setApprovalStatus(de.hybris.platform.catalog.enums.ArticleApprovalStatus.APPROVED)
        modelService.save(it)
  }

e.g. I was able to set the approval status of two products (code, 1101690 and 1101695) to approved using the following code:

 flexibleSearchService.search(/SELECT {pk} FROM {Product} WHERE {code} IN ('1101690','1101695')/).result.each {
     it.setApprovalStatus(de.hybris.platform.catalog.enums.ArticleApprovalStatus.APPROVED)
     modelService.save(it)
 }

b. Using ImpEx with FS Query (check the checkbox, Enable code execution and import the following ImpEx in hAC):

 $productCatalog=electronicsProductCatalog
 $catalogVersion=catalogVersion(catalog(id[default=$productCatalog]),version[default='Staged'])[unique=true,default=$productCatalog:Staged]
 
 "#% import de.hybris.platform.servicelayer.search.FlexibleSearchQuery; 
 flexibleSearchService = Registry.getApplicationContext().getBean(""flexibleSearchService"");"
 
 UPDATE Product;code[unique=true];$catalogVersion;approvalStatus(code)[default='approved']
 "#% query = ""SELECT {pk} FROM {Product} WHERE {code} IN ({{your-flexible-search-query-to-select-the-product-codes}})"";
 flexibleSearchQuery = new FlexibleSearchQuery(query);
 resultIterator = flexibleSearchService.search(flexibleSearchQuery).getResult().iterator();
 while (resultIterator.hasNext()) {
     currentLine = new HashMap();
     currentLine.put(Integer.valueOf(1), resultIterator.next().getCode());
     impex.insertLine(currentLine);
 } "

e.g. I was able to set the approval status of two products (code, 1101690 and 1101695) to approved by importing the following ImpEx:

 $productCatalog=powertoolsProductCatalog
 $catalogVersion=catalogVersion(catalog(id[default=$productCatalog]),version[default='Staged'])[unique=true,default=$productCatalog:Staged]
 
 "#% import de.hybris.platform.servicelayer.search.FlexibleSearchQuery; 
 flexibleSearchService = Registry.getApplicationContext().getBean(""flexibleSearchService"");"
 
 UPDATE Product;code[unique=true];$catalogVersion;approvalStatus(code)[default='approved']
 "#% query = ""SELECT {pk} FROM {Product} WHERE {code} IN ('1101690','1101695')"";
 flexibleSearchQuery = new FlexibleSearchQuery(query);
 resultIterator = flexibleSearchService.search(flexibleSearchQuery).getResult().iterator();
 while (resultIterator.hasNext()) {
     currentLine = new HashMap();
     currentLine.put(Integer.valueOf(1), resultIterator.next().getCode());
     impex.insertLine(currentLine);
 } "

c. Using ImpEx with SQL (check the checkbox, Enable code execution and import the following ImpEx in hAC):

 $dburl=jdbc:mysql://localhost/hybris67
 $dbusername=hybris67
 $dbpassword=hybris67
 $dbdriver=com.mysql.jdbc.Driver
 
 UPDATE Product[batchmode=true];pk[unique=true];approvalStatus(code)[default='approved']
 #% impex.initDatabase("$dburl", "$dbusername","$dbpassword","$dbdriver");
 "#%
 impex.includeSQLData( 
     ""YOUR-SQL-QUERY-TO-SELECT-PRODUCT-CODEs""
 );
 "

e.g. I was able to set the approval status of two products (code, 300608306 and 300608308) to approved by importing the following ImpEx: $dburl=jdbc:mysql://localhost/hybris67 $dbusername=hybris67 $dbpassword=hybris67 $dbdriver=com.mysql.jdbc.Driver

 UPDATE Product[batchmode=true];pk[unique=true];approvalStatus(code)[default='approved']
 #% impex.initDatabase("$dburl", "$dbusername","$dbpassword","$dbdriver");
 "#%
 impex.includeSQLData( 
     ""SELECT PK FROM Products WHERE p_code IN ('300608306','300608308')""
 );
 "

Note:

1. Change the productCatalog and catalogVersion as per your requirement. If your product codes are unique across product catalogs, you can even remove $catalogVersion completely from the ImpExes given above.

2. I have used MySQL database as an example. Change the code as per your database.

Update [25-Jan-2019]: The following ImpEx will create a new sync job with restriction to synchronize only approved products.

 $productCatalog=electronicsProductCatalog
 $sourceVersion=sourceVersion(catalog(id[default=$productCatalog]),version[default='Staged'])[unique=true,default=$productCatalog:Staged]
 $targetVersion=targetVersion(catalog(id[default=$productCatalog]),version[default='Online'])[unique=true,default=$productCatalog:Online]
 $languages=en

 INSERT_UPDATE SyncItemJob;code[unique=true];syncPrincipals(uid);$sourceVersion;$targetVersion
 ;syncOnlyApprovedElectronicsProducts;testproductmgr;;;

 INSERT_UPDATE CatalogVersion;catalog(id)[unique=true];version[unique=true];active;languages(isoCode);readPrincipals(uid);writePrincipals(uid);synchronizations(code)
 ;$productCatalog;Staged;false;$languages;testproductmgr;testproductmgr;syncOnlyApprovedElectronicsProducts
 ;$productCatalog;Online;true;$languages;testproductmgr

 INSERT_UPDATE JobSearchRestriction;code[unique=true];job(code);query;type(code)
 ;mySyncRestriction;syncOnlyApprovedElectronicsProducts;{pk} IN ({{ SELECT {p:pk} FROM {Product as p},{ArticleApprovalStatus as s} WHERE {s.code}='approved' AND {p.approvalStatus}={s.pk}}});Product

Note: please chage the values for $productCatalog and syncPrincipals as per your setup.

[Former Member]

Hi ,

Thanks for the solution provided.

But in the above mentioned solution, your not syncing the products from staged catalog to online catalog.

[arvind-kumar_avinash]

Oh, no! Either you have updated your question or I read it wrong. I thought you wanted to bulk update the approvalStatus of products selected on the basis of a flexible search query. It's too late in the night here and I do not have any energy left to think about a solution for your requirement now. If you do not get any answer from someone, I will try to answer it tomorrow.

[Former Member]

I have not updated the question,but thanks for your help.I will try how to sync only approved products to online.Please help me if you have time tommorrow, how to sync approved products to online catalog.As anyhow i need to change the approval status to approved after syncing the products,this solution is useful for me in my requirement.

[arvind-kumar_avinash]

Hi - I have updated my answer as per your requirement. Please let me know if it fulfils your requirement.

I would also like to share an informative link https://www.sap.com/cxworks/article/432582396/Catalog_Synchronization with you.