Job upload General Journal example code 2

static void TIDF_UploadJournal(Args _args)
{
    container   accEntryPattern;
    container   offSetEntryPattern;
    container   financialDimensionFromExcel;
    container   conDimensionName;
    int         countCon;
    int         countLedger;
    RecId       recIdDefaultDimension;

    DimensionDynamicAccount parmLedgerDimension;
    DimensionDynamicAccount parmOffsetLedgerDimension;

    LedgerJournalTable   ledgerJournalTable;
    LedgerJournalACType  LedgerJournalACType;
    ledgerJournalName    ledgerJournalName;
    journalID            JournalID;
    axLedgerJournalTable axLedgerJournalTable;
    axledgerJournalTrans axLedgerJournalTrans;
    LedgerJournalType JournalType = ledgerJournalType::Daily;


    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename, numSeq;

    boolean         _return = true;
    ;

    startLengthyOperation();
    filename = "C:\\test.xlsx";
    JournalID = "G0615-0040";// <<< JOURNALNUM
    //filename = _filePath;

    try
    {
        if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
        {
            workbook   = workbooks.item(1);
            workSheets = workbook.worksheets();
            workSheet  = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
            cells      = workSheet.cells();

            row = 5;

            ledgerJournalTable = ledgerJournalTable::find(JournalID);
            conDimensionName = Global::TIDgetDimensionName();

            ttsBegin;
            while (cells.item(row,2).value().bStr() != "")
            {
                //empty container
                financialDimensionFromExcel = conNull();
                countLedger = 0;

                //Create Trans ...
                axLedgerJournalTrans = new axLedgerJournalTrans();
                axLedgerJournalTrans.parmJournalNum(JournalID);
                //start from A
                axLedgerJournalTrans.parmTransDate(cells.item(row,1).value().date());
                axLedgerJournalTrans.parmAccountType(str2Enum(LedgerJournalACType, cells.item(row,2).value().bStr()));
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,17).value().bStr();
                if(cells.item(row,17).value().bStr() != "") {countLedger++;}
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,18).value().bStr();
                if(cells.item(row,18).value().bStr() != "") {countLedger++;}
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,19).value().bStr();
                if(cells.item(row,19).value().bStr() != "") {countLedger++;}
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,20).value().bStr();
                if(cells.item(row,20).value().bStr() != "") {countLedger++;}
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,21).value().bStr();
                if(cells.item(row,21).value().bStr() != "") {countLedger++;}
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,22).value().bStr();
                if(cells.item(row,22).value().bStr() != "")  {countLedger++;}

                //ledger Dimension
                if(axLedgerJournalTrans.parmAccountType() == LedgerJournalACType::Ledger)
                {
                    accEntryPattern =
                    [   cells.item(row, 3).value().bStr(),
                        cells.item(row, 3).value().bStr(),
                        countLedger
                    ];

                    countCon = 1;
                    while(countCon <= countLedger)
                    {
                        accEntryPattern += conPeek(conDimensionName,countCon);
                        accEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                        countCon++;
                    }
                    parmLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accEntryPattern));
                }
                else
                {
                    parmLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 3).value().bStr(),axLedgerJournalTrans.parmAccountType());
                }
                AxLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, LedgerDimension), false);
                axLedgerJournalTrans.parmLedgerDimension(parmLedgerDimension);

                //offset dimension
                axLedgerJournalTrans.parmOffsetAccountType(str2Enum(LedgerJournalACType, cells.item(row,8).value().bStr()));
                if(axLedgerJournalTrans.parmOffsetAccountType() == LedgerJournalACType::Ledger)
                {
                    offSetEntryPattern =
                    [   cells.item(row, 9).value().bStr(),
                        cells.item(row, 9).value().bStr(),
                        countLedger
                    ];

                    countCon = 1;
                    while(countCon <= countLedger)
                    {
                        offSetEntryPattern += conPeek(conDimensionName,countCon);
                        offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                        countCon++;
                    }
                    parmOffsetLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern));
                }
                else
                {
                    parmOffsetLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 9).value().bStr(),axLedgerJournalTrans.parmAccountType());
                }
                
                axLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, offsetLedgerDimension), false);
                axLedgerJournalTrans.parmOffsetLedgerDimension(parmOffsetLedgerDimension);
                if(cells.item(row,5).value().double() != 0)
                {
                    axLedgerJournalTrans.parmAmountCurDebit(cells.item(row,5).value().double());
                }

                if(cells.item(row,6).value().double() != 0)
                {
                    axLedgerJournalTrans.parmAmountCurCredit(cells.item(row,6).value().double());
                }

                axLedgerJournalTrans.parmtxt(cells.item(row,7).value().bStr());
                axLedgerJournalTrans.parmCurrencyCode(cells.item(row,10).value().bStr());
                axLedgerJournalTrans.parmExchRate(cells.item(row,11).value().double());
                axLedgerJournalTrans.parmInvoice(cells.item(row,12).value().bStr());
                axLedgerJournalTrans.parmPaymReference(cells.item(row,13).value().bStr());
                axLedgerJournalTrans.parmBankTransType(cells.item(row,14).value().bStr());
                axLedgerJournalTrans.parmDocumentNum(cells.item(row,15).value().bStr());
                axLedgerJournalTrans.parmDocumentDate(cells.item(row,16).value().date());
                //
                axLedgerJournalTrans.parmPostingProfile(cells.item(row,23).value().bStr());

                recIdDefaultDimension = Global::TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
                axLedgerJournalTrans.parmDefaultDimension(recIdDefaultDimension);
                axLedgerJournalTrans.parmOffsetDefaultDimension(recIdDefaultDimension);

                axLedgerJournalTrans.save();
                row++;
            }
               ttsCommit;

            info('Success !!');
            application.quit();
        }
    }
    catch(Exception::Error)
    {
        info("Journal Upload Error");
        application.quit();
    }

}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s