Wednesday, January 18, 2017

X++ code for Importing general journals through excel in ax 2012

static void Journal(Args _args)
{

    str         mBusinessUnit,mLOB, mCLOB, mDept, mLocation, mOrigin,mProject, mEmployee,mIntercompany;
    str         oBusinessUnit,oLOB, oCLOB, oDept, oLocation, oOrigin, oProject, oEmployee,oIntercompany;
    str         invoice;
    str         TDSgroup,salesTaxGroup,itemSalesTax;
    date        documentdate;
    Voucher     voucher;
    TaxOnItem                       TaxOnItem;
    TaxGroupData                    TaxGroupData;
    real                            TaxAmount = 0, TaxAmount_1 = 0;
    TaxValue                        TaxValue = 0, TaxValue_1 = 0;
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;

    COMVariantType type;
    Name name;
    FileName filename;

    str path;
    str filetype;
    int i;
    real Debit,Credit;
    NoYes   NoYes;
    FileIoPermission perm;
    DocuValue docuvalue;
    #define.FileMode('W')
    TransDate transdate;
    str Currency,accountType,Accountnumb,text,OffsetAccounttype,offsetAccountnum;
    ledgerJournalTrans ledgerJournalTrans;
    ledgerJournalTable ledgerJournalTable;
    Numbersequencetable numSeqTable;
    NumberSeq numberseq;
    LedgerJournalACType LedgerJournalACType;
    container cont1,cont2,ledgerDimension,offSetAcctPattern;
    DimensionDefault        DimensionDefault;   //EDT
DimensionDynamicAccount ledgerDim, offsetledgerDim;


    int                 row = 0,cnt;

     filename = "D:\\jourimport1sample-APINV-DEV(1).xlsx";

         perm = new FileIOPermission(filename, #FileMode);
        perm.assert();
        application = SysExcelApplication::construct();
        workbooks = application.workbooks();
    try
    {
        workbooks.open(filename);
    }

    catch (Exception::Error)
    {
        throw error("@SYS19358");
    }
    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();
    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();

    row = 1;
    startLengthyOperation();
    //progress.setCaption("@KPM1054");
    //progress.setAnimation(#AviUpdate);
    do
    {
        row++;

        //progress.setText(strfmt("@KPM1055", row));
        transdate       = cells.item(row, 1).value().date();
        Currency        = cells.item(row, 2).value().bStr();
        accountType     = cells.item(row, 3).value().bStr();
        AccountNumb = strFmt("%1", cells.item(row, 4).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("@KPM1056", cells.item(row+1, 1).value().variantType()));
        }
        mbusinessUnit       = cells.item(row, 5).value().bStr();
        mLOB                = cells.item(row, 6).value().bStr();
        mCLOB               = cells.item(row, 7).value().bStr();
        mDept               = cells.item(row, 8).value().bStr();
        mLocation           = cells.item(row, 9).value().bStr();
        mOrigin             = cells.item(row, 10).value().bStr();
        mProject            = cells.item(row, 11).value().bStr();
        mEmployee           = cells.item(row, 12).value().bStr();
        mIntercompany       = cells.item(row, 13).value().bStr();

        //invoice         = cells.item(row, 14).value().bStr();
        Text            = cells.item(row, 15).value().bStr();
        Debit           = any2real(cells.item(row, 16).value().double());
        Credit          = any2real(cells.item(row, 17).value().double());
        OffsetAccounttype = cells.item(row, 18).value().bStr();

        switch(cells.item(row, 19).value().variantType())
        {
            case COMVariantType::VT_BSTR:
                offsetAccountnum = strFmt("%1", cells.item(row, 19).value().bStr());
                break;
            case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
                offsetAccountnum = strFmt("%1", any2int(cells.item(row, 19).value().double()));
                break;
            case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
                offsetAccountnum = strFmt("%1", cells.item(row, 19).value().int());
                break;
            case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
                offsetAccountnum = strFmt("%1", cells.item(row, 19).value().uLong());
                break;
            case COMVariantType::VT_EMPTY:
                offsetAccountnum = '';
                break;
            default:
                throw error(strfmt("@KPM1056", cells.item(row, 19).value().variantType()));
        }
        obusinessUnit       = cells.item(row, 20).value().bStr();
        oLOB                = cells.item(row, 21).value().bStr();
       oCLOB               = cells.item(row, 22).value().bStr();
        oDept               = cells.item(row, 23).value().bStr();
        oLocation           = cells.item(row, 24).value().bStr();
        oOrigin             = cells.item(row, 25).value().bStr();
        oProject            = cells.item(row, 26).value().bStr();
        oEmployee       = cells.item(row, 27).value().bStr();
        oIntercompany   = cells.item(row, 28).value().bStr();
        TDSgroup        = cells.item(row, 29).value().bStr();
        salesTaxGroup   = cells.item(row, 30).value().bStr();
        itemSalesTax    = cells.item(row, 31).value().bStr();
        documentdate    = cells.item(row, 32).value().date();

        //In Excel cell should be in Text format
        try
        {
            ttsbegin;
            voucher = '';
            ledgerJournalTrans.clear();
            ledgerJournalTrans.initValue();
            ledgerJournalTrans.JournalNum   = "INMF-00485";//ledgerJournalTable.JournalNum;
            ledgerJournalTrans.TransDate    = transdate;
            ledgerJournalTrans.Approved          = NoYes::Yes;
            ledgerJournalTrans.Approver          = HcmWorker::userId2Worker(curuserid());

            select firstOnly numSeqTable
                where numSeqTable.RecId  == LedgerJournalName::find("GenJN").NumberSequenceTable;
            if (numSeqTable && !voucher)
            {
                numberseq = numberseq::newGetVoucherFromCode(numSeqTable.NumberSequence);
                voucher = numberseq.voucher();
            }

            ledgerJournalTrans.Voucher              = voucher;
            ledgerJournalTrans.AccountType          = str2enum(LedgerJournalACType, accountType);
            // Main account dimensions
            cont1=conNull();
            cont2=conNull();
            ledgerDimension =conNull();
            cnt=0;
            //Account type
            if(mBusinessUnit != '')
            {
                cnt++;
                cont2+=['BU',mBusinessUnit];
            }
            if(mLOB != '')
            {
                cnt++;
                cont2+=['LOB',mLOB];
            }
            if(mCLOB != '')
            {
                cnt++;
                cont2+=['CLOB',mCLOB];
            }
            if(mDept != '')
            {
                cnt++;
                cont2+=['Department',mDept];
            }
            if(mLocation != '')
            {
                cnt++;
                cont2+=['Location',mLocation];
            }
            if(mOrigin != '')
            {
                cnt++;
                cont2+=['Origin',mOrigin];
            }
            if(mProject != '')
            {
                cnt++;
                cont2+=['Projects',mProject];
            }
            if(mEmployee != '')
            {
                cnt++;
                cont2+=['Worker',mEmployee]; //Employee
            }
            if(mIntercompany != '')
            {
                cnt++;
                cont2+=['Intercompany',mIntercompany];
            }

            if(ledgerJournalTrans.AccountType  == LedgerJournalACType::Ledger)
            {
                cont1+=['MainAccount',AccountNumb,cnt];
                cont1+=cont2;
                ledgerDim = AxdDimensionUtil::getLedgerAccountId(cont1);
                if(ledgerDim==0)
                {
                        offSetAcctPattern = [AccountNumb,AccountNumb];
                        ledgerDim = AxdDimensionUtil::getLedgerAccountId( offSetAcctPattern);
                }

                ledgerJournalTrans.LedgerDimension  = ledgerDim;
            }
            else
            {
                ledgerDim = DimensionStorage::getDynamicAccount( AccountNumb,ledgerJournalTrans.AccountType);
                ledgerDimension +=cnt;
                ledgerDimension +=cont2;
                DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);
                ledgerJournalTrans.LedgerDimension  = ledgerDim;
                LedgerJournalTrans.modifiedField(fieldNum(LedgerJournalTrans,LedgerDimension));
                ledgerJournalTrans.DefaultDimension = DimensionDefault;
            }
            ledgerJournalTrans.Txt                  = Text;
            ledgerJournalTrans.CurrencyCode         = Currency;
            ledgerJournalTrans.AmountCurDebit       = Debit;
            ledgerJournalTrans.AmountCurCredit      = Credit;
            if(offsetaccountType)
            {
                ledgerJournalTrans.OffsetAccountType    = str2enum(LedgerJournalACType, offsetaccountType);
            }
            else
            {
                   ledgerJournalTrans.OffsetAccountType    = LedgerJournalACType::Ledger;
            }
            cont1=conNull();
            cont2=conNull();
            ledgerDimension =conNull();
            cnt=0;
            //Offset Account Type

            if(oBusinessUnit != '')
            {
                cnt++;
                cont2+=['BU',oBusinessUnit];
            }
            if(oLOB != '')
            {
                cnt++;
                cont2+=['LOB',oLOB];
            }
            if(oCLOB != '')
            {
                cnt++;
                cont2+=['CLOB',oCLOB];
            }
            if(oDept != '')
            {
                cnt++;
                cont2+=['Department',oDept];
            }
            if(oLocation != '')
            {
                cnt++;
                cont2+=['Location',oLocation];
            }
            if(oOrigin != '')
            {
                cnt++;
                cont2+=['Origin',oOrigin];
            }
            if(oProject != '')
            {
                cnt++;
                cont2+=['Projects',oProject];
            }
            if(oEmployee != '')
            {
                cnt++;
                cont2+=['Worker',oEmployee];
            }
            if(oIntercompany != '')
            {
                cnt++;
                cont2+=['Intercompany',oIntercompany];
            }

            if (ledgerJournalTrans.OffsetAccountType  == LedgerJournalACType::Ledger && offsetAccountnum)
            {
                cont1+=['MainAccount',offsetAccountnum,cnt];
                cont1+=cont2;
                offsetledgerDim =AxdDimensionUtil::getLedgerAccountId(cont1);

                if(offsetledgerDim == 0)
                {
                    offSetAcctPattern = [offsetAccountnum,offsetAccountnum];
                    offsetledgerDim = AxdDimensionUtil::getLedgerAccountId( offSetAcctPattern);
                }
                ledgerJournalTrans.OffsetLedgerDimension = offsetledgerDim;
            }
            else
            {
                if(offsetAccountnum)
                {
                    offsetledgerDim = DimensionStorage::getDynamicAccount(offsetAccountnum,ledgerJournalTrans.OffsetAccountType);
                    ledgerDimension +=cnt;
                    ledgerDimension +=cont2;
                    DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);
                    ledgerJournalTrans.OffsetLedgerDimension = offsetledgerDim;
                    LedgerJournalTrans.modifiedField(fieldNum(LedgerJournalTrans,OffsetLedgerDimension));
                    ledgerJournalTrans.OffsetDefaultDimension = DimensionDefault;
                }
            }
            ledgerJournalTrans.TaxGroup     = salesTaxGroup;
            ledgerJournalTrans.TaxItemGroup = itemSalesTax;
             //tax1 = Tax::calcTaxAmount(ledgerJournalTrans.TaxGroup, ledgerJournalTrans.TaxItemGroup, Systemdateget(), ledgerJournalTrans.CurrencyCode, ledgerJournalTrans.AmountCurCredit, TaxModuleType::);
       // TaxLedgerJournalCalculate::totalTaxAmountSingleLine(TaxJournalCall::Journal, _ledgerJournalTrans, 0, 0);
            //TaxAmount_1 = 0;
            //if(ledgerJournalTrans.TaxItemGroup && ledgerJournalTrans.TaxGroup) //&& ledgerJournalTrans.AmountCurCredit != 0)
            //{
                //while select TaxOnItem
                    //where TaxOnItem.TaxItemGroup == ledgerJournalTrans.TaxItemGroup
                //{
                    //if(TaxOnItem)
                    //{
                        //while select TaxGroupData
                            //where TaxGroupData.TaxGroup == ledgerJournalTrans.TaxGroup
                                //&& TaxGroupData.TaxCode  == TaxOnItem.TaxCode
                        //{
                            //if(TaxGroupData)
                            //{
                                //TaxAmount = 0;
                                //TaxValue  =  TaxData::find(TaxOnItem.TaxCode, Systemdateget(), 0).TaxValue;
                                //TaxValue_1  += TaxValue;
                                //if(ledgerJournalTrans.AmountCurCredit != 0)
                                    //TaxAmount = (ledgerJournalTrans.AmountCurCredit* TaxValue)/100;
                                //else if(ledgerJournalTrans.AmountCurDebit != 0)
                                    //TaxAmount = (ledgerJournalTrans.AmountCurCredit* TaxValue)/100;
                                //TaxAmount_1 += TaxAmount;
                            //}
                        //}
                    //}
                //}
            //}
            ledgerJournalTrans.DocumentDate = documentdate;
            ledgerJournalTrans.TDSGroup_IN  = TDSgroup;


            if (ledgerJournalTrans.validateWrite())
            {
                ledgerJournalTrans.insert();
                 info(strFmt('journal inserted %1 - %2',ledgerJournalTable.JournalNum,row));
            }
            ttscommit;




            type = cells.item(row+1, 1).value().variantType();
        }
        catch(Exception::Error)
        {
            info(strFmt('Catched an error in row: %1',row));
        }

    }

    while (type != COMVariantType::VT_EMPTY);
    application.quit();
    endLengthyOperation();

}


excel format :




No comments:

Post a Comment

Export a copy of the standard user acceptance testing (UAT) database

 Reference link: Export a copy of the standard user acceptance testing (UAT) database - Finance & Operations | Dynamics 365 | Microsoft ...