UPDATING THE PRODUCT ATTRIBUTE FOR PARTICULAR PRODUCT FROM EXCEL

By using job we update the product attributes for particular product in product information management module.

static void excelattribute(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    FilenameOpen  filename;
    EcoResAttributeGroupAttribute ecoResAttributeGroupAttribute;
    EcoResProduct  ecoResProduct ;
    EcoResProductCategory ecoResProductCategory;
    EcoResCategory ecoResCategory;
    EcoResCategoryAttributeLookup ecoResCategoryAttributeLookup;
    EcoResAttribute ecoResAttribute;
    EcoResAttributeType ecoResAttributeType;


    int row ;                   // if the excel has the header
    str attribute ,attribute1;
    str attribute2;
       
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();

    filename  = "C:\\Users\\charlie\\Desktop\\RamaProductAttribute.xlsx"; // file path
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();

    //Iterate through cells and get the values
    do
    {
 
    //Incrementing the row line to next Row
      row++;

        attribute              = cells.item(row, 1).value().bStr();
        attribute1              = cells.item(row, 2).value().bStr();
        attribute2              = cells.item(row, 3).value().bStr();          
           
     
/*Insert ProductAttribute values*/

        select ecoResAttributeType;
        ecoResAttributeType.Name=attribute1;
       
        ecoResAttributeType.DataType=any2int(str2enum(AttributeDataType::Decimal,attribute2));
        ecoResAttributeType.insert();
        select ecoResAttribute;
        ecoResAttribute.Name=attribute;
        if(ecoResAttribute.AttributeType)
        ecoResAttribute.AttributeType=ecoResAttributeType.RecId;
        
        ecoResAttribute.insert();

/* assign above productAttributes to "0117" product*/

    select ecoResProduct join ecoResProductCategory join ecoResCategory join                                 

ecoResCategoryAttributeLookup join ecoResAttribute  join ecoResAttributeType  where 

ecoResProduct.DisplayProductNumber=="0117" && 

ecoResProductCategory.Product==ecoResProduct.RecId     && 

ecoResCategory.RecId==ecoResProductCategory.Category;

    if(ecoResCategoryAttributeLookup)

    ecoResCategoryAttributeLookup.Category=ecoResCategory.RecId;

    while   select Name from ecoResAttribute

     if(ecoResAttribute.Name==attribute)
       
    {

     ecoResCategoryAttributeLookup.Attribute=ecoResAttribute.RecId;
           
     while select Attribute from ecoResAttributeGroupAttribute
                 
      if(ecoResAttributeGroupAttribute.Attribute== ecoResCategoryAttributeLookup.Attribute)          
               
            ecoResCategoryAttributeLookup.AttributeGroupAttribute=ecoResAttributeGroupAttribute.RecId;
     
      ecoResCategoryAttributeLookup.insert();

       }

       while select ecoResAttribute where

       ecoResAttribute.RecId==ecoResCategoryAttributeLookup.Attribute

     //  if(ecoResAttribute.RecId==ecoResCategoryAttributeLookup.Attribute)
 
         {
   
         info(ecoResAttribute.Name);
     
         }


     // Loads the next row into the variant type and validating that its is empty or not
 
       type = cells.item(row+1, 1).value().variantType();
     
       }
   
       while (type != COMVariantType::VT_EMPTY);

     // quits the application
 
       application.quit();    


}

Here we should understand the table realtions.

1.EcoResProduct              

    // Here select the product to update product attribute

2.EcoResProductCategory    

 // EcoResProductCategory.product==EcoresProduct.RecId

3.EcoResCategory                

  // EcoResCategory .RecId==EcoResProductCategory.Category

4.EcoResCategoryAttributeLookup                                          

 // EcoResCategoryAttributeLookup.Category==EcoResCategory.RecId

5.EcoResAttribute            

  // EcoResAttribute.RecId==EcoResCategoryAttributeLookup.Attribute