Monday, 4 September 2017

uploadcustomertaxinfo

static void uploadcustomertaxinfo(Args _args)
{
    #AviFiles
    FilenameOpen filename;
    dialogField dialogFilename,DialogJournalType;
    LedgerDimensionAccount  mainAccDimension;
    int jounaltype;
    Dialog dialog= new Dialog("Excel Upoad");
    Container excelCont[], financialDimensions;
    RecId           offsetledger;

    int rowIdx;
    Counter linesImported;
    int lastRow,countr;
    boolean ok = true;
    LedgerDimensionAccount              ledgerDimension;
    InventTable                         inventTable;
    InventJournalName                   inventJournalName;
    InventJournalTable                  inventJournalTable;
    InventJournalTrans                  inventJournalTrans;
    NumberSeq                           numberSeq;
    Container                           con,defDimension;
    FileIOPermission                    permission;
    TextIO                              textIO, textIO1;
    //Dialog                              dialog;
    DialogField                         dialogField;
    //Filename                            Filename;
    LineNum                             lineNumber = 1;
    CustAccount                         custAccNum;
    InventJournalId                     journalNum;
    int                                 dimCount,dimCount2;

    LedgerJournalACType                 AccountType;
    InventDim                           inventDim;
    InventDimId                         dimId;
    InventJournalTrans_IN               InventJournalTrans_IN;
    str                                 custac;

    CustTable                   custTable;
    LogisticsLocation      logisticsLocation;
    DirPartyLocation       dirPartyLocation;
    TaxInformation_IN    taxInformation_IN;

    TaxRegistrationNumbers_IN  TaxRegistrationNumbers;


    //#File
    Description c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23;

    str input;

    SysExcelApplication application;
    SysExcelWorkBooks workBooks;
    SysExcelWorkSheets workSheets;
    SysExcelWorkSheet workSheet;
    SysExcelCells cells;
    //dhiBusinessType dhiBusinessType;
    PurchaseType    purchaseType;
    DocumentStatus  documentStatus;
    VersioningDocumentState   documentState;
    SysOperationProgress progress;
    struct      struct= new Struct();

    #define.CurrentVersion(1)
    #localmacro.CurrentList
    filename
    #endmacro

    #Excel
    #define.Star('*')
    #define.Space(' ')


    // convert into str from excel cell value
    str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
    {
        switch (_cv.variantType())
        {
            case (COMVariantType::VT_BSTR):
            return _cv.bStr();
            case (COMVariantType::VT_R4):
            return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

            case (COMVariantType::VT_R8):
            return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

            case (COMVariantType::VT_DECIMAL):
            return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

            case (COMVariantType::VT_DATE):
            return date2str(_cv.date(),123,2,1,2,1,4);

            case (COMVariantType::VT_EMPTY):
            return "";

            default:
            throw error(strfmt("@SYS26908", _cv.variantType()));
        }
        return "";
    }

    // Find last row from excel

    int findLastRow(SysExcelWorkSheet _workSheet)
    {
        SysExcelRange range;
        ;

        range = _workSheet.cells().range(#ExcelTotalRange);

        try
        {
            // Finds the row where the first contents is found.
            range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
        }
        catch (Exception::Error)
        {
            error("@SYS59926");
            return 0;
        }

        if (range)
        {
            return range.row();
        }
        else
        {
            return 0;
        }
    }


    ;


    dialogFilename = dialog.addField(extendedtypestr(FilenameOpen));
   // DialogJournalType = dialog.addField(enumstr(God_JournalToUpload),"Journal Type");
    dialog.filenameLookupFilter(["@SYS28576",#XLS,#Xlsx]);
    dialog.filenameLookupTitle("Upload from Excel");
    dialogFilename.value(filename);

    if(!dialog.run())
    return;

    filename = dialogFilename.value();

   // ttsbegin;

    application = SysExcelApplication::construct();
    workBooks = application.workbooks();
    workBooks.open(filename,0,true);

    workSheets = workBooks.item(1).worksheets();
    // this.importExcel("Sheet1");

    input = "Sheet1";
    workSheet = workSheets.itemFromNum(1);//.itemFromName(input);
    cells = workSheet.cells();
    lastRow = findLastRow(workSheet);
    rowIdx = 2;

    progress = new SysOperationProgress();
    progress.setCaption("Excel Importing");
    progress.setTotal(lastRow);
    progress.setAnimation(#AviTransfer);
    setprefix("Excel Import");
    try{

            while (rowIdx <= lastRow)
            {
                setPrefix(strfmt("Excel Row: %1", rowIdx));

                    c1              = COMVariant2Str(cells.item(rowIdx,1).value());
                    c2            = COMVariant2Str(cells.item(rowIdx,2).value());

                    custac = c1;
                linesImported++;

                InventJournaltrans.clear();

                    ttsBegin;
                select custTable where custTable.AccountNum== c1;

               select dirPartyLocation where dirPartyLocation.Party == custTable.Party;
                //join logisticsLocation where logisticsLocation.RecId == dirPartyLocation.Location
               select taxInformation_IN where taxInformation_IN.RegistrationLocation ==                       dirPartyLocation.Location ;
                if(!taxInformation_IN)
                {


                //select taxInformation_IN where taxInformation_IN.RegistrationLocation ==logisticsLocation.RecId;

                taxInformation_IN.RegistrationLocation  = dirPartyLocation.Location;
                taxInformation_IN.Name                  = c2;
                taxInformation_IN.IsPrimary             = NoYes::Yes;

                taxInformation_IN.insert();

                }




                ttsCommit;
                lineNumber++;
                rowIdx++;
                progress.setText("Importing " + c1);
                progress.setCount(linesImported);
               }
           }
           catch
       {
           error(strFmt("error in line %1",rowIdx));
       }
    info(strFmt("done! Imported %1 records",rowIdx-2));
}

No comments:

Post a Comment

Labels

#veryusefulcode (1) AIF (8) AOT Maps (1) Args (1) Ax 2009 Reports (2) AX 2012 navigation (1) Ax 2012 Interview Questions (1) AX 7 (2) AX Architecture (1) Ax Backup (1) AX Workflow (2) AX2012 (1) AX2012 R2 (1) Ax2012R3 (1) AX2012R3 Dynamics Connector Step by Step Installation and Configuration (1) AX2012R3 EP Step by Step Installation and Configuration EP R3 (1) AX2012R3 HelpServer Step by Step Installation and Configuration (1) AX2012R3 Rapid Start Connector Step by Step Installation and Configuration (1) AX2012R3 Report Server and Analysis Server Step by Step Installation and Configuration (1) AX7 (1) Best practices (1) Blocking user to enter (1) Collection Classes (1) Container (1) D365FO (3) Data Migration Frame Work ax 2012R3 (1) Deleting duplicate Record from Table – Ax2012 (1) Delivery due date notification workflow in Ax 2012 (1) Development Steps EP (1) Dimensions (1) DIXF (1) DMF in Ax 2012 R3 (1) Dynamics Ax 2012 Interview Questions (1) DYNAMICS AX 2012 INTERVIEW QUESTIONS PART 2 (1) DYNAMICS AX 7 (1) EDT relation Migration Tool (1) EP AX 2012 (1) Ep Lookup (1) Error (1) Event Handler (1) F5 (1) File Handling (4) Filter on AX2012 Listpage (1) filtering (2) financial dimensions in AX 2012 (3) form (1) images (1) Installation and Configration (4) Installation and Configuration (11) Installation of Management Reporter 2012 for AX 2012 (1) Interaction class in ax 2012 (1) Interview Question (1) Interview Questions For Ax 2012 (1) Invent DIm (1) Jobs (2) license (1) List page and form menuitem enable code (1) Methods (1) microsoft Dynamics AX 365FO (1) Number Sequence Generation – AX 2012 (5) Number Sequence2012 (1) OLTP-OLAP (1) Passing Args (1) Passing form caller and menu item caller in ax 2012 (1) Passing Multiple Records Args (1) Posting in Ax 2012 (1) POSTING PURCHASE ORDER (1) Query (1) Query Filter Form (2) Query Ranges in X++ (1) Question and Answer (1) Report (1) Reports Controller class (1) RLS in ax 2009 (1) SALES ORDER IMPORT/EXPORT FRAMEWORK BY DMF (1) Security (1) security roles (1) Security Sysqueryrangeutil (1) Sharepoint 2016 (1) SQL SERVER (1) SSRS (2) SSRS Reports Controller class (2) Table collections & Virtual company (1) Time (1) TIPS AND TRICKS (1) Web service AIF (3) Web Services on IIS (AIF) Step by Step Installation and Configuration (1) workflow ax2012 (1) Workflow installation (1) Workflow Method (3) X++ (1)