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: 

AX 2012 SALES TRADE AGGREMENTS UPLOAD FROM EXCEL X++ CODE

AX 2012 SALES TRADE AGGREMENTS UPLOAD FROM EXCEL X++ CODE

AX 2012 SALES TRADE AGGREMENTS UPLOAD FROM EXCEL X++ CODE

        class VIK_SalesMaster_ExcelImport extends RunBaseBatch

        {

             DialogField                         dialogfile;

             FilenameOpen                        fileName;

             localmacro.CurrentList

              fileName

            endmacro

        }

     

       dialog

        protected Object dialog()

        {

            DialogRunbase  _dialog = super();

       

            _dialog.caption("Sales : Trade Aggrement 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;

            CustName name;

            int i,j;

            //sales price upload

            str queryPricingMaster,itemId,existingItemIdsList,tempValue;

            ResultSet resultSet;

            InventTable inventTable;

            AifEntityKeyList                        keys;

            DD_HI_RRP_Staging                       RRPStaging;

            PricePriceDiscJournalService            PriceDiscSvc;

            PricePriceDiscJournal                   PriceDiscJour;

            PricePriceDiscJournal_PriceDiscAdmTrans PriceDiscJourAdmTrans;

            PricePriceDiscJournal_InventDim         PriceDiscJourDim;

            PriceDiscAdmTrans   _PriceDiscAdmTrans;

            //vikas

            PriceDiscAdmTable                   priceDiscAdmTable;

            PriceDiscAdmTrans                   PriceDiscAdmTrans;

            PriceDiscAdmName                    PriceDiscAdmName;

            PriceDiscJournalName                pricediscjournalName;

            NumberSeq journalNum;

            //sales price upload//

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

            real _from,_amtincur;

            date _todate,_fromdate;

            PriceType _pricetype;

            itemId tempItemId;

            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();

       

            //progress1.setCaption("Updation of pricing master is in progress…");

            //progress1.setAnimation(AviUpdate);

       

       

       

         ttsBegin;

       

            Select firstOnly  PriceDiscAdmName;

            priceDiscAdmTable.clear();

            priceDiscAdmTable.initFromPriceDiscAdmName(PriceDiscAdmName);

            journalNum      = NumberSeq::newGetNum(SalesParameters::numRefPriceDiscJournalNum());

            pricediscjournalName        =   journalNum.num();

            priceDiscAdmTable.JournalNum = pricediscjournalName;

            priceDiscAdmTable.insert();

       

           while (type != COMVariantType::VT_EMPTY)

        {

            row++;

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

                // Set PriceDiscJourAdmTrans

                  while select forUpdate * from inventTable where inventTable.ItemId == tempItemId

                {

       

                   // progress1.setText(strfmt("Item ID  %1", tempItemId));

                    PriceDiscAdmTrans.clear();

                    PriceDiscAdmTrans.JournalNum        = pricediscjournalName;

                    PriceDiscAdmTrans.relation          = real2int(cells.item(Row,1).value().double()) ;//relation

                    PriceDiscAdmTrans.InventDimId       = "Allblank";

                    PriceDiscAdmTrans.DisregardLeadTime = NoYes::Yes;

                    PriceDiscAdmTrans.AccountCode       = real2int(cells.item(Row,2).value().double()) ; //account code

                    PriceDiscAdmTrans.AccountRelation   = cells.item(Row,3).value().bStr();     //account relation

                    PriceDiscAdmTrans.ItemCode          = real2int(cells.item(Row,4).value().double());

                    PriceDiscAdmTrans.ItemRelation      = cells.item(Row,5).value().bStr();         //item realtaion item id

                    PriceDiscAdmTrans.QuantityAmountFrom= cells.item(Row,6).value().double();        //from quantity

                    PriceDiscAdmTrans.UnitId            = cells.item(Row,7).value().bStr();            //unit id

                    PriceDiscAdmTrans.Amount            = cells.item(Row,8).value().double();         //Amount in Curr

                    PriceDiscAdmTrans.Currency          = cells.item(Row,9).value().bStr();        //currency code

                    PriceDiscAdmTrans.FromDate          = cells.item(Row,10).value().date();        //from date

                    PriceDiscAdmTrans.ToDate            = cells.item(Row,11).value().date();         //todate

                    PriceDiscAdmTrans.insert();

                    itemId=inventTable.ItemId;

       

                }

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

        }

            ttsCommit;

        excel.quit();

        }

     

       main

        public static void main(Args _args)

        {

            VIK_SalesMaster_ExcelImport    VIK_SalesMaster_ExcelImport = new VIK_SalesMaster_ExcelImport();

       

       

             if(VIK_SalesMaster_ExcelImport.prompt())

                VIK_SalesMaster_ExcelImport.run();

        }

IMPORT JOURNALS FROM EXCEL USING X++ IN AX 2009 ledgerJournalTable VOUCHER SYSEXCEL

Importing Journals from Excel using X++ in AX 2009

class VIKAS_ImportVendInvoiceJournalLines
{
    Dialog                         dialog;
    DialogField                    dialogfield;
    Filename                       filename;

    #AviFiles
    SysExcelApplication            application;
    SysExcelWorkbooks              workbooks;
    SysExcelWorkbook               workbook;
    SysExcelWorksheets             worksheets;
    SysExcelWorksheet              worksheet;
    SysExcelCells                  cells;
    COMVariantType                 type;
    COMVariantType                 typeModule;
    NumberSeq                      numberSeq;
    NumberSequenceTable            numSeqTable;

    str                            Name, num,text, currency,businessunit,account,accountType, department,offsetaccount,OffsetAccounttype;
    int64                          costcenter;
    real                           debit, credit;
    container                      Account1, offsetaccount1;
    str                            acc;
    date                           transdate;

    LedgerJournalName              ledgerJournalName;
    LedgerJournalTable             ledgerJournalTable;
    LedgerJournalTrans             ledgerJournalTrans;



    container                      cont1,cont2,offSetAcctPattern;
    int                            cnt;

    LedgerJournalAC                AccountNumb, offsetAccountnum;

    container                      ledgerDimension;

    LedgerJournalACType            LedgerJournalACType;
    boolean                        ret;

}

--------------------------------------------------------------------------------------------------------------------------

public void DataImport()

{

    str         mSBU,mDepartment,mCostCenter,mPurpose;

    str         oSBU,oDepartment,oCostCenter,oPurpose;

    str         invoice;

    str         TDSgroup,salesTaxGroup,itemSalesTax;

    date        documentdate;

    Voucher     voucher;

    SysOperationProgress progress = new SysOperationProgress();

    int                 row = 0;

    workbook    = workbooks.item(1);

    worksheets  = workbook.worksheets();

    worksheet   = worksheets.itemFromNum(1);

    cells       = worksheet.cells();

    ledgerJournalName = ledgerjournalname::find(ledgerjournaltable.JournalName);

    row = 1;

    do

    {

        row++;

        transdate       = cells.item(row, 1).value().date();

        Currency        = cells.item(row, 2).value().bStr();

        accountType     = cells.item(row, 3).value().bStr();

        switch(cells.item(row, 4).value().variantType())

        {

            case COMVariantType::VT_BSTR:

                AccountNumb = strFmt("%1", cells.item(row, 4).value().bStr());

                break;

            case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:

                AccountNumb = strFmt("%1", any2int(cells.item(row, 4).value().double()));

                break;

            case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:

                AccountNumb = strFmt("%1", cells.item(row, 4).value().int());

                break;

            case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:

                AccountNumb = strFmt("%1", cells.item(row, 4).value().uLong());

                break;

            case COMVariantType::VT_EMPTY:

                AccountNumb = '';

                break;

            default:

                throw error(strfmt('Unhandled variant type (%1).', cells.item(row+1, 1).value().variantType()));

        }

        mSBU            = cells.item(row, 5).value().bStr();

        mDepartment     = cells.item(row, 6).value().bStr();

        mCostCenter     = cells.item(row, 7).value().bStr();

        mPurpose        = cells.item(row, 8).value().bStr();

        invoice         = cells.item(row, 9).value().bStr();

        Text            = cells.item(row, 10).value().bStr();

        Debit           = any2real(cells.item(row, 11).value().double());

        Credit          = any2real(cells.item(row, 12).value().double());

        OffsetAccounttype = cells.item(row, 13).value().bStr();

        switch(cells.item(row, 14).value().variantType())

        {

            case COMVariantType::VT_BSTR:

                offsetAccountnum = strFmt("%1", cells.item(row, 14).value().bStr());

                break;

            case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:

                offsetAccountnum = strFmt("%1", any2int(cells.item(row, 14).value().double()));

                break;

            case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:

                offsetAccountnum = strFmt("%1", cells.item(row, 14).value().int());

                break;

            case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:

                offsetAccountnum = strFmt("%1", cells.item(row, 14).value().uLong());

                break;

            case COMVariantType::VT_EMPTY:

                offsetAccountnum = '';

                break;

            default:

                throw error(strfmt('Unhandled variant type (%1).', cells.item(row, 1).value().variantType()));

        }

        oSBU            = cells.item(row, 15).value().bStr();

        oDepartment     = cells.item(row, 16).value().bStr();

        oCostCenter     = cells.item(row, 17).value().bStr();

        oPurpose        = cells.item(row, 18).value().bStr();

        TDSgroup        = cells.item(row, 19).value().bStr();

        salesTaxGroup   = cells.item(row, 20).value().bStr();

        itemSalesTax    = cells.item(row, 21).value().bStr();

        documentdate    = cells.item(row, 22).value().date();

        //In Excel cell should be in Text format

        try

        {

            ttsbegin;

            ledgerJournalTrans.clear();

            ledgerJournalTrans.initValue();

            ledgerJournalTrans.JournalNum   = ledgerJournalTable.JournalNum;

            ledgerJournalTrans.TransDate    = transdate;

            ledgerJournalTrans.Dimension[1] = mSBU;

            ledgerJournalTrans.Dimension[2] = mDepartment;

            ledgerJournalTrans.Dimension[3] = mCostCenter;

            ledgerJournalTrans.Dimension[4] = mPurpose;

            //select firstOnly numSeqTable

            //    where numSeqTable.RecId  == ledgerJournalName.NumberSequenceTable;

            if (!voucher)

            {

                numberseq = numberseq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);

                voucher = numberseq.voucher();

            }

            ledgerJournalTrans.Voucher              = voucher;

            ledgerJournalTrans.AccountType          = str2enum(LedgerJournalACType, accountType);

            ledgerJournalTrans.AccountNum           = AccountNumb;

            ledgerJournalTrans.Txt                  = Text;

            ledgerJournalTrans.CurrencyCode         = Currency;

            ledgerJournalTrans.AmountCurDebit       = Debit;

            ledgerJournalTrans.AmountCurCredit      = Credit;

            ledgerJournalTrans.OffsetAccountType    = str2enum(LedgerJournalACType, offsetaccountType);

            ledgerJournalTrans.OffsetAccount        = offsetAccountnum;

            ledgerJournalTrans.Invoice              = invoice;

            ledgerJournalTrans.TaxGroup             = salesTaxGroup;

            ledgerJournalTrans.TaxItemGroup         = itemSalesTax;

            ledgerJournalTrans.DocumentDate         = documentdate;

            ledgerJournalTrans.TDSGroup_IN          = TDSgroup;

            if (ledgerJournalTrans.validateWrite())

            {

                ledgerJournalTrans.insert();

            }

            ttscommit;

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

        }

        catch(Exception::Error)

        {

            info(strFmt('Catched an error in row: %1',row));

        }

        info(strFmt('journal inserted %1',ledgerJournalTable.JournalNum));

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

}

-------------------------------------------------------------------------------------------------------------------------

public void run(Args _args)

{

    ;

    LedgerJournalTable = _args.record();

    dialog = new dialog('Excel Import');

    dialogfield = dialog.addField(TypeId(FilenameOpen), 'File Name');

    dialog.run();

    if(dialog.run())

    {

        filename =(dialogfield.value());

    }

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    if(filename)

    {

        try

        {

            workbooks.open(filename);

        }

        catch (Exception::Error)

        {

            throw error('File cannot be opened.');

        }

        this.DataImport();

    }

}

-------------------------------------------------------------------------------------------------------------------------

public static void main(Args args)

{

    VIKAS_ImportVendInvoiceJournalLines import = new VIKAS_ImportVendInvoiceJournalLines();

    import.run(args);

}

--------------------------------------------------------------------------------------------------------------------------

void clicked()

{

    FormRun callerForm;

    Args args = new Args();

    VIKAS_ImportVendInvoiceJournalLines VIKAS_ImportVendInvoiceJournalLines = new         VIKAS_ImportVendInvoiceJournalLines();

    ;

    args.record(ledgerjournaltable);

    VIKAS_ImportVendInvoiceJournalLines.run(args);

    callerForm          = element.args().caller();

    callerForm.dataSource().refresh();

    callerForm.dataSource().reread();

}