Magento Product Catalog and price extract

Magento Product Catalog and price extract:

1. Create a table KGSPMIProdMasterPriceTracker fields like
  ItemId,DisplayProductNumber,salesprice,productgroupid,taxitemgroupid,itemdataareaid,searchname,
productsearchname,salesunit,productname,productdescription,inventoryunit,isprocessed,multilinedisc,
linedisc.

2. create a class which data is inserted in above created table

 class KGSPMIProdMasterPriceTrackerStartUpJob
{       
    /// <summary>
    /// Runs this class to inisialize the KGSPMIProdMasterPriceTracker for the first time only.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {  
        EcoResProduct   Ecoresproduct;
        KGSPMIProdMasterPriceTracker    prodMasterPriceTracker,prodMasterPriceTrackerUpdate;
        InventTableModule   inventTableModule;
        InventItemGroupItem inventItemGroupItem;
        InventTable inventTable;
        int countRec = 0;
        KGSPMIPriceDiscAdmTransDataCreation::main(_args);
        while select Ecoresproduct
        {
            inventTable = InventTable::findByProduct(Ecoresproduct.RecId);
            prodMasterPriceTracker = KGSPMIProdMasterPriceTracker::find(inventTable.ItemId);
           
            if(inventTable && !prodMasterPriceTracker)
            {
                inventTableModule = InventTableModule::find(inventTable.ItemId, ModuleInventPurchSales::Sales);
                inventItemGroupItem = inventItemGroupItem::findByItemIdLegalEntity(inventTableModule.ItemId);
                ttsbegin;
                prodMasterPriceTracker.ItemId = inventTable.ItemId; //ecoResProduct.releasedItemId();
                prodMasterPriceTracker.SearchName = inventTable.NameAlias;
                prodMasterPriceTracker.DisplayProductNumber = ecoResProduct.DisplayProductNumber;
                prodMasterPriceTracker.ProductSearchName = ecoResProduct.SearchName;
                prodMasterPriceTracker.ItemDataAreaId = curExt();
                prodMasterPriceTracker.SalesPrice = inventTableModule.Price;
                prodMasterPriceTracker.SalesUnit = inventTableModule.PriceUnit;
                prodMasterPriceTracker.TaxItemGroupId = inventTableModule.TaxItemGroupId;
                prodMasterPriceTracker.ProductGroupId = inventItemGroupItem.ItemGroupId;
                prodMasterPriceTracker.IsProcessed    = NoYes::No; //Added bY Naren on 11/15/2019 for Project: KGSPMIProdMasterToMagento
                prodMasterPriceTracker.ProductName          = ecoResProduct.productName();
                prodMasterPriceTracker.ProductDescription   = ecoResProduct.productDescription();
                prodMasterPriceTracker.InventoryUnit        = inventTableModule.UnitId;
                prodMasterPriceTracker.LineDisc             = inventTableModule.LineDisc;//Added bY vinod on 01/13/2019 for Project: KGSPMIProdMasterToMagento
                prodMasterPriceTracker.MultiLineDisc        = inventTableModule.MultiLineDisc;//Added bY vinod on 01/13/2019 for Project: KGSPMIProdMasterToMagento
                prodMasterPriceTracker.insert();
                ttscommit;
                countRec++;
            }
            else
            {
                ttsbegin;
                select forupdate prodMasterPriceTrackerUpdate
                    where prodMasterPriceTrackerUpdate.ItemId ==  prodMasterPriceTracker.ItemId;
                inventTableModule = InventTableModule::find(prodMasterPriceTrackerUpdate.ItemId, ModuleInventPurchSales::Sales);
                prodMasterPriceTrackerUpdate.LineDisc         =   inventTableModule.LineDisc;
                prodMasterPriceTrackerUpdate.MultiLineDisc    =   inventTableModule.MultiLineDisc;
                prodMasterPriceTrackerUpdate.doUpdate();
                ttscommit;
                countRec++;
            }
        }
        Info(strFmt("@KGSPMI:KGSPMIJobCreated", countRec));
                  
    }
}
----------------------------------------------------------------
3. update in the above table of few fields which ecoresproduct update, by taking extension of ecoresproduct table for update method

[ExtensionOf(tableStr(EcoResProduct))]
final class KGSPMIEcoResProduct_Extension
{
    public void update()
    {
        next update();
        InventTable                     inventTable = InventTable::findByProduct(this.RecId);
        KGSPMIProdMasterPriceTracker    prodMasterPriceTracker = KGSPMIProdMasterPriceTracker::find(inventTable.ItemId, true);
        if(prodMasterPriceTracker && inventTable)
        {
            ttsbegin;
            prodMasterPriceTracker.DisplayProductNumber = this.DisplayProductNumber;
            prodMasterPriceTracker.ProductSearchName = this.SearchName;
            prodMasterPriceTracker.ItemDataAreaId = curExt();
            prodMasterPriceTracker.IsProcessed  = NoYes::No;
            prodMasterPriceTracker.update();
            ttscommit;
        }
    }
}
----------------------------
4. create a extension class of inventtable for insert and update for the above table fields

[ExtensionOf(tableStr(InventTable))]
final class KGSPMIInventTable_Extension
{
    public void insert(boolean _doInsert)
    {
        next insert(_doInsert);
        KGSPMIProdMasterPriceTracker    prodMasterPriceTracker;
        EcoResProduct                   ecoResProduct = EcoResProduct::find(this.Product);
        ttsbegin;
        prodMasterPriceTracker.ItemId               = this.ItemId;
        prodMasterPriceTracker.SearchName           =  this.NameAlias;
        prodMasterPriceTracker.DisplayProductNumber = ecoResProduct.DisplayProductNumber;
        prodMasterPriceTracker.ProductSearchName    = ecoResProduct.SearchName;
        prodMasterPriceTracker.ItemDataAreaId       = curExt();
        //START Added bY Naren on 11/14/2019 for Project: KGSPMIProdMasterToMagento
        prodMasterPriceTracker.ProductName          = ecoResProduct.productName();
        prodMasterPriceTracker.ProductDescription   = ecoResProduct.productDescription();
        prodMasterPriceTracker.IsProcessed          = NoYes::No;
        //END Added bY Naren on 11/14/2019 for Project: KGSPMIProdMasterToMagento
        prodMasterPriceTracker.insert();
        ttscommit;
    }
    public void update()
    {
        next update();
        KGSPMIProdMasterPriceTracker    prodMasterPriceTracker = KGSPMIProdMasterPriceTracker::find(this.ItemId, true);
        EcoResProduct                   ecoResProduct = EcoResProduct::find(this.Product);
        if(prodMasterPriceTracker)
        {
            ttsbegin;
            prodMasterPriceTracker.ItemId               = this.ItemId;
            prodMasterPriceTracker.SearchName           =  this.NameAlias;
            prodMasterPriceTracker.DisplayProductNumber = ecoResProduct.DisplayProductNumber;
            prodMasterPriceTracker.ProductSearchName    = ecoResProduct.SearchName;
            prodMasterPriceTracker.ItemDataAreaId       = curExt();
            prodMasterPriceTracker.ProductName          = ecoResProduct.productName();
            prodMasterPriceTracker.ProductDescription   = ecoResProduct.productDescription();
            prodMasterPriceTracker.IsProcessed          = NoYes::No;
            prodMasterPriceTracker.update();
            ttscommit;
        }
    }
}
--------------------------------
5.  create a extension class for inventitemgroup table for insert and update above table

 [ExtensionOf(tableStr(InventItemGroupItem))]
final class KGSPMIInventItemGroupItem_Extension
{
    public void update()
    {
        next update();
        this.initializeKGSPMIProdMasterPriceTracker();
    }
    public void insert()
    {
        next insert();
        this.initializeKGSPMIProdMasterPriceTracker();
    }
    protected void initializeKGSPMIProdMasterPriceTracker()
    {
        KGSPMIProdMasterPriceTracker    prodMasterPriceTracker = KGSPMIProdMasterPriceTracker::find(this.ItemId, true, this.ItemDataAreaId);
       
        if(this.itemid && prodMasterPriceTracker)
        {
            ttsbegin;
            prodMasterPriceTracker.ProductGroupId   = this.ItemGroupId;
            //START Added bY Naren on 11/14/2019 for Project: KGSPMIProdMasterToMagento
            prodMasterPriceTracker.IsProcessed      = NoYes::No;
            //END Added bY Naren on 11/14/2019 for Project: KGSPMIProdMasterToMagento
            prodMasterPriceTracker.update();
            ttscommit;
        }
    }
}
------------------------------
6. Create a extension class for inventtablemodule table for insert and update fields for above table

[ExtensionOf(tableStr(InventTableModule))]
final class KGSPMIInventTableModule_Extension
{
    public void insert()
    {
        next insert();
        this.inisializeProdMasterPriceTracker();
    }
    public void update(boolean _updatePriceDate, boolean _updatePrice)
    {
        next update( _updatePriceDate,  _updatePrice);
        this.inisializeProdMasterPriceTracker();
    }
    protected void inisializeProdMasterPriceTracker()
    {
        KGSPMIProdMasterPriceTracker    prodMasterPriceTracker = KGSPMIProdMasterPriceTracker::find(this.ItemId, true);
       
        if(this.itemid && this.ModuleType == ModuleInventPurchSales::Sales && prodMasterPriceTracker)
        {
            ttsbegin;
            prodMasterPriceTracker.SalesPrice       = this.Price;
            prodMasterPriceTracker.TaxItemGroupId   = this.TaxItemGroupId;
            //START Added bY Naren on 11/14/2019 for Project: KGSPMIProdMasterToMagento
            prodMasterPriceTracker.InventoryUnit    = this.UnitId;
            prodMasterPriceTracker.IsProcessed      = NoYes::No;
            //END Added bY Naren on 11/14/2019 for Project: KGSPMIProdMasterToMagento
            //START Added bY vinod on 01/13/2019 for Project: KGSPMIProdMasterToMagento
            prodMasterPriceTracker.LineDisc         = this.LineDisc;
            prodMasterPriceTracker.MultiLineDisc    = this.MultiLineDisc;
            //END Added bY vinod on 01/13/2019 for Project: KGSPMIProdMasterToMagento
            prodMasterPriceTracker.update();
            ttscommit;
        }
    }
}
-------------------------------
7. Create a class for updateing the fields in above table

class KGSPMIProdMasterPriceTrackerProcessAllItems
{       
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {       
        KGSPMIProdMasterPriceTracker    kgsPMIProdMasterPriceTracker;
        int countRec = 0;
        select count(RecId) from kgsPMIProdMasterPriceTracker;
        countRec = kgsPMIProdMasterPriceTracker.RecId;
        ttsbegin;
        while select forupdate kgsPMIProdMasterPriceTracker
        {
            kgsPMIProdMasterPriceTracker.IsProcessed    = NoYes::No;
            kgsPMIProdMasterPriceTracker.doUpdate();
            countRec++;
        }
        //delete_from kgsPMIProdMasterPriceTracker;
        ttscommit;
        Info(strFmt("@KGSPMI:KGSPMIJobUpdate", countRec));
    }
}
------------------------
8. Create a service class for extracting the Magento product catalog

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
class KGSPMIProdMasterPriceTrackerExportService
{
    void process()
    {
        EcoResCategoryName              ecoResCategoryName;
        KGSPMIBlobFileShareDataWriter   integrationDataWriter;
        EcoResCategory                  category;
        EcoResProductCategory           prodCategory;
        //EcoResProductParameters         ecoResProdParm;
        InventTable                     inventTable;
        KGSPMIProdMasterPriceTracker    kgsPMIProdMasterPriceTracker;
        ItemId                          itemId;
        const str                       fileName = "ProdMasterPrice.xlsx";
        KGSPMIIntegrationParameters     integrationParameters = KGSPMIIntegrationParameters::find();
        //itemId  = _contract.parmItemId();
        //ecoResProdParm = EcoResProductParameters::find();
        //integrationDataWriter = KGSPMIBlobFileShareDataWriter::construct(KGSPMIIntegrationType::MagentoProduct);
        MemoryStream memoryStream = new MemoryStream();
        ttsbegin;
        using (var package = new ExcelPackage(memoryStream))
        {
            var currentRow = 1;
            var worksheets = package.get_Workbook().get_Worksheets();
            var CustTableWorksheet = worksheets.Add("@KGSPMI:KGSPMIExport");
            var cells = CustTableWorksheet.get_Cells();
           
            OfficeOpenXml.ExcelRange cell = cells.get_Item(currentRow, 1);
            System.String value = "@SYS330532";
            cell.set_Value(value);
           
            cell = null;
            value = "@SYS59620";
            cell = cells.get_Item(currentRow, 2);
            cell.set_Value(value);
            cell = null;
            value = "@KGSPMI:KGSPMIInventoryUnit";
            cell = cells.get_Item(currentRow, 3);
            cell.set_Value(value);
            cell = null;
            value = "@KGSPMI:KGSPMIErpItemGroup";
            cell = cells.get_Item(currentRow, 4);
            cell.set_Value(value);
            cell = null;
            value = "@KGSPMI:KGSPMIEcommerecProductGroup";
            cell = cells.get_Item(currentRow, 5);
            cell.set_Value(value);
            cell = null;
            value = "@KGSPMI:KGSPMISalesPrice";
            cell = cells.get_Item(currentRow, 6);
            cell.set_Value(value);
            cell = null;
            value = "@KGSPMI:KGSPMIItemSalesTaxGroup";
            cell = cells.get_Item(currentRow, 7);
            cell.set_Value(value);
            cell = null;
            value = "@SYS3392";
            cell = cells.get_Item(currentRow, 8);
            cell.set_Value(value);
            cell = null;
            value = "Multiline discount group";
            cell = cells.get_Item(currentRow, 9);
            cell.set_Value(value);
            cell = null;
            value = "Line discount group";
            cell = cells.get_Item(currentRow, 10);
            cell.set_Value(value);
                          
            while select forupdate kgsPMIProdMasterPriceTracker
                where kgsPMIProdMasterPriceTracker.IsProcessed == NoYes::No
                    join inventTable
                where inventTable.ItemId    == kgsPMIProdMasterPriceTracker.ItemId                            
                    //notexists  join prodCategory
                    exists  join prodCategory
                where prodCategory.Product == inventTable.Product
                    && prodCategory.Category != integrationParameters.MagentoCategory
                      && prodCategory.CategoryHierarchy == integrationParameters.MagentoCategoryHierarchy
            {
                currentRow ++;
                    cell = null;
                    cell = cells.get_Item(currentRow, 1);
                    cell.set_Value(kgsPMIProdMasterPriceTracker.ItemId);
               
                    /*cell = null;
                cell = cells.get_Item(currentRow, 2);
                cell.set_Value(kgsPMIProdMasterPriceTracker.DisplayProductNumber);*/
                    cell = null;
                    cell = cells.get_Item(currentRow, 2);
                    cell.set_Value(kgsPMIProdMasterPriceTracker.ProductName);
                    /*cell = null;
                cell = cells.get_Item(currentRow, 3);
                cell.set_Value(kgsPMIProdMasterPriceTracker.ProductDescription);*/
                    cell = null;
                    cell = cells.get_Item(currentRow, 3);
                    cell.set_Value(kgsPMIProdMasterPriceTracker.InventoryUnit);
                    cell = null;
                    cell = cells.get_Item(currentRow, 4);
                    cell.set_Value(kgsPMIProdMasterPriceTracker.ProductGroupId);
                    cell = null;
                    cell = cells.get_Item(currentRow, 5);
                    EcoResProductCategory locProdCategory;
                    select locProdCategory
                        where locProdCategory.Product == inventTable.Product;
                    cell.set_Value(EcoResCategory::find(locProdCategory.Category).Name);
                    cell = null;
                    cell = cells.get_Item(currentRow, 6);
                    cell.set_Value(kgsPMIProdMasterPriceTracker.SalesPrice);
                    cell = null;
                    cell = cells.get_Item(currentRow, 7);
                    cell.set_Value(kgsPMIProdMasterPriceTracker.TaxItemGroupId);
                   
                    cell = null;
                    cell = cells.get_Item(currentRow, 8);
                    cell.set_Value(Ledger::accountingCurrency(CompanyInfo::current()));
                    cell = null;
                    cell = cells.get_Item(currentRow, 9);
                    cell.set_Value(kgsPMIProdMasterPriceTracker.MultiLineDisc);
                    cell = null;
                    cell = cells.get_Item(currentRow, 10);
                    cell.set_Value(kgsPMIProdMasterPriceTracker.LineDisc);
                    if(!itemId)
                    {
                        kgsPMIProdMasterPriceTracker.IsProcessed = NoYes::yes;
                        kgsPMIProdMasterPriceTracker.doUpdate();
                    }               
            }
            package.Save();
            ttscommit;
            if(isRunningOnBatch())
            {
                integrationDataWriter.exportToBlob(memoryStream, fileName);
            }
            else
            {
                file::SendFileToUser(memoryStream, fileName);
            }
        }
    }
}