ECORESCATEGORY Table in Dynamics-SSC -Vikas Mehta MULTISELECT

ECORESCATEGORY Table in Dynamics-SSC

Query to get the ecorescategory branches and nodes in ax 2012 

  this.parmQuery().dataSourceTable(tablenum(EcoResCategory), 1).clearRanges();

        categoryBuildRange = this.parmQuery().dataSourceTable(tablenum(EcoResCategory), 1).addRange(fieldnum(EcoResCategory, RecId));

        categoryEnum = category.getEnumerator();

        while (categoryEnum.moveNext())

        {

            categoryRecid = categoryEnum.current();

            select ecoResCategory where ecoResCategory.RecId == categoryRecid;

            currentLevel = ecoResCategory.Level;

            lowestlevel = ecoResCategory.getLowestLevel();

            categoryHierarchy = ecoResCategory.CategoryHierarchy;

            while(currentLevel <= lowestlevel)

            {

            while select ecoResCategoryChild where ecoResCategoryChild.CategoryHierarchy == categoryHierarchy

                                                && ecoResCategoryChild.Level ==  currentLevel

            {

                categoryRecid = ecoResCategoryChild.RecId;

                while select * from ecoResCat

                    order by ecoResCat.NestedSetLeft

                    where   ecoResCat.CategoryHierarchy == ecoResCategoryChild.CategoryHierarchy

                            && ecoResCat.NestedSetLeft  <= ecoResCategoryChild.NestedSetLeft

                            && ecoResCat.NestedSetRight >= ecoResCategoryChild.NestedSetRight

                {

                    if(ecoResCategory.RecId == ecoResCat.RecId)

                    {

                        select * from ecoResCategoryisLeaf

                            order by ecoResCategoryisLeaf.Name

                            where   ecoResCategoryisLeaf.CategoryHierarchy == ecoResCategoryChild.CategoryHierarchy

                             && ecoResCategoryisLeaf.ParentCategory  == ecoResCategoryChild.RecId

                                && ecoResCategoryisLeaf.IsActive == true;

                        if(!ecoResCategoryisLeaf)

                        {

                            allCategory.addEnd(categoryRecid);

                        }

                        break;

                    }

                }

            }

            currentLevel++;

        }

        }

 

Item lookup of ecorescategory in ui builder class

private void itemLookup(FormStringControl _control)

{

    Query query;

    QueryBuildDataSource qbdscategory,qbdsProductCategory,qbdsInvent,qbdsEcoProduct;

    container brandCon;

    int counter;

    RecId brandCategoryRecid,categoryRecid,segmentRecid;

    EcoResCategory category,segmentCategory;

    ;

    query = new query();

    qbdsInvent = query.addDataSource(tableNum(InventTable));

    qbdsEcoProduct = qbdsInvent.addDataSource(tableNum(EcoResProduct));

    qbdsEcoProduct.relations(false);

    qbdsEcoProduct.addLink(fieldNum(EcoResProduct,RecId),fieldNum(InventTable,Product));

    qbdsProductCategory = qbdsEcoProduct.addDataSource(tableNum(EcoResProductCategory));

    qbdsProductCategory.relations(false);

    qbdsProductCategory.addLink(fieldNum(EcoResProduct,RecId),fieldNum(EcoResProductCategory,Product));

    qbdscategory = qbdsProductCategory.addDataSource(tableNum(EcoResCategory));

    qbdscategory.relations(false);

    qbdscategory.addLink(fieldNum(EcoResProductCategory,Category),fieldNum(EcoResCategory,RecId));

 ECORESCATEGORY LOOKUP MULTISELECT

public void categoryLookup()

{

    Query           query = new Query(querystr(ProcCategoryEcoResCategory));

    int             mutiSelectTableNum = tablenum(EcoResCategory);

    container selectedFields = [mutiSelectTableNum, fieldname2id(mutiSelectTableNum, fieldstr(EcoResCategory, Name))];

    SysLookupMultiSelectCtrl::constructWithQuery(this.dialog().dialogForm().formRun(), categoryField.control(), query, false, selectedFields);

}

INVENTTABLE AND ECORESPRODUCT RELATION JOIN QUERY LINK ECORESCATEGORY

INVENTTABLE AND ECORESPRODUCT RELATION 

JOIN QUERY LINK ECORESCATEGORY

InventTable and ECORESPRODUCT

select ecorescategorygroc where ecorescategorygroc.Name == catgnames;

    while select * from inventTable join product where inventTable.Product == product.RecId

                    join    productCat  where productCat.Product  == product.RecId

                    join    category    where   category.RecId      == productCat.Category && category.Name == catgnames//category.ParentCategory == ecorescategorygroc.RecId// && category.Name == catgnames

                    join catHierarchy   where   catHierarchy.RecId  == category.CategoryHierarchy//

                             && inventTable.itemid like

AX 2012 EXCEL UPLOAD CODE EXCELIMPORT :VIKAS :DYNAMICS AX 2012 : ITEM CATEGORY UPDATE FOR ITEMS

ITEM CATEGORY UPDATE FOR ITEMS

   VIKAS ITEM EXCEL IMPORT TESTED

    METHODS

       classDeclaration

        class Vik_ItemCategory_ExcelImport exts RunBaseBatch

        {

             DialogField                         dialogfile;

             FilenameOpen                        fileName;

             localmacro.CurrentList

              fileName

            macro

        }

     

       createcategory

        Public void createcategory(Itemid _itemno,EcoResCategoryCommodityCode _CategoryName,EcoResCategoryHierarchyName _name)

        {

       

                 //vikas

            EcoResDistinctProduct  ecoResDistinctProduct;

            EcoResProduct           ecoResProduct;

            EcoResProductTranslation ecoResProductTranslation;

            EcoResProductCategory   ecoResProductCategory;

            EcoResCategory          ecoResCategory;

            EcoResCategoryHierarchy ecoResCategoryHierarchy;

            ecoResCategoryTranslation   ecoResCategoryTranslation;

            //vikas

             //Create Producr Category (Hierarchy)

            select ecoResDistinctProduct where ecoResDistinctProduct.DisplayProductNumber == _itemno;

            Select firstOnly ecoResCategoryHierarchy where ecoResCategoryHierarchy.Name == _name;

            Select firstonly ecoResProductCategory where ecoResProductCategory.Product == ecoResDistinctProduct.RecId;

            if (!ecoResProductCategory)

            {

                ttsBegin;

                ecoResProductCategory.clear();

                ecoResProductCategory.initValue();

                ecoResProductCategory.CategoryHierarchy         = ecoResCategoryHierarchy.RecId;

                select * from ecoResCategory where ecoResCategory.Name == _CategoryName;

                ecoResProductCategory.Category                  = ecoResCategory.RecId;

                //ecoResProductCategory.category = ecoResCategoryTranslation.RecId;

                ecoResProductCategory.Product                   = ecoResDistinctProduct.RecId;

                //ecoResProductCategory.VIK_EcoResProductType     = VIK_EcoResProductType::findByProductType(_productType).RecId;

                //ecoResProductCategory.VIK_EcoResSubCategory     = VIK_EcoResSubCategory::findBySubCategory(_subCategory).RecId;

                ecoResProductCategory.insert();

                ttsCommit;

                info(strFmt("category for item %1 is created",_itemno));

            }

       

        }

     

       dialog

        protected Object dialog()

        {

            DialogRunbase  _dialog = super();

       

            _dialog.caption("Product Upload");

            dialogfile = _dialog.addField("FilenameOpen","Enter file path : ");

            dialogfile.value(fileName);

       

            return _dialog;

        }

     

       getFromDialog

        public boolean getFromDialog()

        {

            ;

            fileName = dialogfile.value();

            return true;

        }

     

       run

        public void run()

        {

            SysExcelApplication excel;

            SysExcelWorkbooks workbooks;

            SysExcelWorkbook workbook;

            SysExcelWorksheets worksheets;

            SysExcelWorksheet worksheet;

            SysExcelCells cells;

            COMVariantType type;

            int row =1;

            int _noofprints;

            EcoResCategoryCommodityCode _CatName;

            EcoResCategoryHierarchyName _Hiername;

            CustName name;

            int i,j;

            //sales price upload

            str queryPricingMaster,itemId,existingItemIdsList,tempValue;

            ResultSet resultSet;

            InventTable inventTable;

       

            //vikas

            EcoResProduct           ecoResProduct;

            EcoResProductTranslation ecoResProductTranslation;

            EcoResProductCategory   ecoResProductCategory;

            EcoResCategory          ecoResCategory;

            EcoResCategoryHierarchy checkEcoResCatH;

            EcoResCategory          checkEcoResCat;

               //checkEcoResCatH                     = EcoResCategoryHierarchy::findByName(_Hiername);

            //checkEcoResCat                      = EcoResCategory::findByName(_CatName,checkEcoResCatH.RecId);

            //vikas

       

            //vikas

            //sales price upload//

            str _relation,_accountcode,_accountselection,_itemcode,_temrelation,_unit,_curcode;

            real _from,_amtincur;

            date _todate,_fromdate;

            PriceType _pricetype;

       

            itemId tempItemId;

            str stritemid;

            AviFiles

            SysOperationProgress progress1 = new SysOperationProgress();

            ;

        

        //excel

        define.filename(fileName)

        excel = SysExcelApplication::construct();

        workbooks = excel.workbooks();

        try

        {

        workbooks.open(fileName);

        }

        catch (Exception::Error)

        {

        throw error("File cannot be opened");

        }

       

       

                workbook = workbooks.item(1);

                worksheets = workbook.worksheets();

                worksheet = worksheets.itemFromNum(1);

                cells = worksheet.cells();

                type = cells.item(row+1, 1).value().variantType();

       

         ttsBegin;

       

           while (type != COMVariantType::VT_EMPTY)

        {

            row++;

            tempItemId  = cells.item(Row,1).value().bStr();

           // stritemid = cells.item(Row,1).value().bStr();

            select inventTable where inventTable.ItemId == tempItemId;

            if(inventTable)

            {

            select ecoResProduct where ecoResProduct.DisplayProductNumber == inventTable.ItemId;

            select ecoResProductCategory    where ecoResProductCategory.product == ecoResProduct.RecId;

       

            _CatName  = cells.item(Row,2).value().bStr();

            _Hiername = cells.item(Row,3).value().bStr();

       

            checkEcoResCatH                     = EcoResCategoryHierarchy::findByName(_Hiername);

            checkEcoResCat                      = EcoResCategory::findByName(_CatName,checkEcoResCatH.RecId);

       

            if(!ecoResProductCategory && ecoResProduct && checkEcoResCatH && checkEcoResCat)

                {

                    this.createcategory(tempItemId,_CatName,_Hiername);

                }

            else if (ecoResProductCategory && ecoResProduct && checkEcoResCatH && checkEcoResCat)

                {

                    this.updatecategory(tempItemId,_CatName,_Hiername);

                }

            }

                 type = cells.item(row+1, 1).value().variantType();

        }

            ttsCommit;

        excel.quit();

        }

     

       updatecategory

        Public void updatecategory(Itemid _itemno,EcoResCategoryCommodityCode _CategoryName,EcoResCategoryHierarchyName _name)

        {

       

                 //vikas

            EcoResDistinctProduct  ecoResDistinctProduct;

            EcoResProduct           ecoResProduct;

            EcoResProductTranslation ecoResProductTranslation;

            EcoResProductCategory   ecoResProductCategory;

            EcoResCategory          ecoResCategory;

            EcoResCategoryHierarchy ecoResCategoryHierarchy;

            ecoResCategoryTranslation   ecoResCategoryTranslation;

            //vikas

             //Create Producr Category (Hierarchy)

            select ecoResDistinctProduct where ecoResDistinctProduct.DisplayProductNumber == _itemno;

            Select firstOnly ecoResCategoryHierarchy where ecoResCategoryHierarchy.Name == _name;

       

            while select forupdate ecoResProductCategory where ecoResProductCategory.Product == ecoResDistinctProduct.RecId

            if(ecoResProductCategory)

            {

                ttsBegin;

                //ecoResProductCategory.clear();

                //ecoResProductCategory.initValue();

                ecoResProductCategory.CategoryHierarchy         = ecoResCategoryHierarchy.RecId;

                select * from ecoResCategory where ecoResCategory.Name == _CategoryName;

                ecoResProductCategory.Category                  = ecoResCategory.RecId;

                //ecoResProductCategory.category = ecoResCategoryTranslation.RecId;

                ecoResProductCategory.Product                   = ecoResDistinctProduct.RecId;

                //ecoResProductCategory.VIK_EcoResProductType     = VIK_EcoResProductType::findByProductType(_productType).RecId;

                //ecoResProductCategory.VIK_EcoResSubCategory     = VIK_EcoResSubCategory::findBySubCategory(_subCategory).RecId;

                ecoResProductCategory.update();

                ttsCommit;

                info(strFmt("category for item %1 is updated",_itemno));

            }

       

        }

     

       main

        public static void main(Args _args)

        {

            Vik_ItemCategory_ExcelImport    Vik_ItemCategory_ExcelImport = new Vik_ItemCategory_ExcelImport();

       

            if(curext() == "COMP")

            {

             if(Vik_ItemCategory_ExcelImport.prompt())

                Vik_ItemCategory_ExcelImport.run();

            }

        }

     

    METHODS

  CLASS

***Element: