Budget Entries Upload

//Creation of Budget header and Lines through Class in AX 2012

Excel Template
BudgetCode,
BudgetModel,
AccountStructure,
Date,
Amount,
AmountType,
MainAccount,
BusinessUnit,Department,
CostCenter

class San_ImportBudget extends RunBase
{
    // Packed variables
    Filename            importFileName;
    container           accountSegments;
    container           accountSegmentNames;
    BudgetCode          budgetCode;
    BudgetModelId       budgetModelId;
    Name                accountStructureName;
    TransDate           transDate;
    AmountCur           amount;
    BudgetType          budgetType;
    MainAccountNum      mainAccountNum;


    // Dialog fields
    DialogField     dlgImportFileName;

    #define.CurrentVersion(1)
    #define.Version1(1)
    #localmacro.CurrentList
        importFileName
    #endmacro

    BudgetTransactionService            budgetTransactionService;
    BudgetTransaction                   budgetTransaction;
    BudgetTransaction_BudgetTransHeader budgetTransaction_BudgetTransHeader;

    str fileName;
    str filePath;
    str fileExtension;
}

public str COMVariant2Str(COMVariant _cv,
                       int _decimals = 1,
                       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(),
                             213,
                             2,
                             1,
                             2,
                             1,
                             4);

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

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

    return "";
}

public Object dialog()
{
    DialogRunbase       dialog = super();
    #resAppl
    #AviFiles
    #Excel
    ;

    dlgImportFileName = dialog.addField(ExtendedTypeStr(FileNameOpen), "File name");
    dlgImportFileName.value(importFileName);
    dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS,"@SYS100852","*.csv","Text","*.txt"]);
    return dialog;
}


public boolean getFromDialog()
{
    importFileName = dlgImportFileName.value();
    return super();
}

public container pack()
{
    return [#CurrentVersion,#CurrentList];
}

public boolean unpack(container packedClass)
{
    Version version = RunBase::getVersion(packedClass);
;
    switch (version)
    {
        case #CurrentVersion:
            [version,#CurrentList] = packedClass;
            break;
        default:
            return false;
    }

    return true;
}

static void main(Args args)
{
    San_ImportBudget    budgetImport = new San_ImportBudget();

    if (budgetImport.prompt())
        budgetImport.run();
}

public void run()
{
    #OCCRetryCount
    if (! this.validate())
        throw error("");

    try
    {
        ttsbegin;

        [filePath, fileName, fileExtension] = fileNameSplit(importFileName);

        switch (fileExtension)
        {
            case ".xls", ".xlsx" :
                info("@SYS4010033");
                this.ProcessImportXLSFile();
                info("@SYS329919");
                break;
            default :
                info(strFmt("Unable to process file of type %1",fileExtension));
                info("File must be .xls or .xlsx");
                break;
        }

        ttscommit;
    }
    catch (Exception::Deadlock)
    {
        retry;
    }
    catch (Exception::UpdateConflict)
    {
        if (appl.ttsLevel() == 0)
        {
            if (xSession::currentRetryCount() >= #RetryNum)
            {
                throw Exception::UpdateConflictNotRecovered;
            }
            else
            {
                retry;
            }
        }
        else
        {
            throw Exception::UpdateConflict;
        }
    }
    catch (Exception::Error)
    {
        ttsAbort;
        info('Failed to post, reversing updates');
    }


}

private void ProcessImportXLSFile()
{
    SysExcelApplication excel;
    SysExcelWorkbooks   workbooks;
    SysExcelWorkbook    workbook;
    SysExcelWorksheets  worksheets;
    SysExcelWorksheet   worksheet;
    SysExcelCells       cells;
    SysExcelCell        cell;
    COMVariantType      type;
    COMVariantType      hdrtype;
    container           header;
    int                 row = 1;
    int                 totalColumnCount = 0;
    int                 currentColumn;

    excel = SysExcelApplication::construct();
    excel.visible(false);

    workbooks = excel.workbooks();

    try
    {
        workbooks.open(importFileName);
    }
    catch (Exception::Error)
    {
        throw error(strFmt('@GLS101746', importFileName));
    }

    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    type = cells.item(row+1,1).value().variantType();

    if (type == COMVariantType::VT_EMPTY || type == COMVariantType::VT_NULL || type == COMVariantType::VT_UNKNOWN)
    {
        error("@SYS102111");
    }
    else
    {
        // recover header column names
        for (currentColumn=1; currentColumn < 99; currentColumn++)
        {
            hdrtype = cells.item(row,currentColumn).value().variantType();
            if (hdrtype != COMVariantType::VT_EMPTY)
            {
                header += cells.item(row, currentColumn).value().bStr();
                totalColumnCount++;
            }
            else
            {
                break;
            }
        }

        type = cells.item(row+1, 1).value().variantType();
        while (type != COMVariantType::VT_EMPTY && type != COMVariantType::VT_NULL && type != COMVariantType::VT_UNKNOWN)
        {
            row++;
            accountSegments = conNull();
            accountSegmentNames = conNull();

            budgetCode = this.COMVariant2Str(cells.item(row, 1).value(), 0);
            budgetModelId = this.COMVariant2Str(cells.item(row, 2).value(), 0);
            accountStructureName = this.COMVariant2Str(cells.item(row, 3).value(), 0);
            transDate = str2Date(this.COMVariant2Str(cells.item(row, 4).value(), 0),213);
            amount = str2num(this.COMVariant2Str(cells.item(row, 5).value(), 2));
            budgetType = str2enum(budgetType, this.COMVariant2Str(cells.item(row, 6).value(), 0));
            mainAccountNum = this.COMVariant2Str(cells.item(row, 7).value(), 0);

            //The remaining columns are account segments and there can be any number of them
            for(currentColumn = 8; currentColumn <= totalColumnCount; currentColumn++)
            {
                //Read the remaining segment columns as strings and put into a container
                accountSegments += this.COMVariant2Str(cells.item(row, currentColumn).value(), 0);
                accountSegmentNames += conPeek(header, currentColumn);
            }

            this.createBudgetTransactionLine();

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

        BudgetTransactionService.create(budgetTransaction);
    }

    excel.quit();
}

public void createBudgetTransactionLine()
{
    BudgetTransaction_BudgetTransLine   budgetTransaction_BudgetTransLine;

    AifBudgetAccount                    aifBudgetAccount;
    AifDimensionAttributeValue          aifDimensionAttributeValue;
    int                                 dimensionColumn;

    if (!budgetTransactionService)
    {
        budgetTransactionService                = BudgetTransactionService::construct();
        budgetTransaction                       = new BudgetTransaction();
        budgetTransaction_BudgetTransHeader     = budgetTransaction.createBudgetTransHeader().addNew();
        budgetTransaction_BudgetTransHeader.parmBudgetTransactionCode(budgetCode);
        budgetTransaction_BudgetTransHeader.parmBudgetModelId(budgetModelId);
        budgetTransaction_BudgetTransLine       = budgetTransaction_BudgetTransHeader.createBudgetTransLine().addNew();
    }
    else
    {
        budgetTransaction_BudgetTransLine       = budgetTransaction_BudgetTransHeader.parmBudgetTransLine().addNew();
    }

    // Populate the budget line
    budgetTransaction_BudgetTransLine.parmDate(transDate);
    budgetTransaction_BudgetTransLine.parmTransactionCurrencyAmount(amount);
    budgetTransaction_BudgetTransLine.parmBudgetType(budgetType);

    aifBudgetAccount   = budgetTransaction_BudgetTransLine.createLedgerDimension();
    aifBudgetAccount.parmAccountStructure(accountStructureName);
    aifBudgetAccount.parmDisplayValue("1");    //Will be changed by system to write display value

    // Main Account - required
    aifDimensionAttributeValue   = aifBudgetAccount.createValues().addNew();
    aifDimensionAttributeValue.parmName('MainAccount');
    aifDimensionAttributeValue.parmValue(mainAccountNum);

    // variable dimension list - optional
    if (conLen(accountSegments) > 0)
    {
        for (dimensionColumn = 1; dimensionColumn <= conLen(accountSegments); dimensionColumn++)
        {
            if(conPeek(accountSegments, dimensionColumn) != "")
            {
                aifDimensionAttributeValue = aifBudgetAccount.parmValues().addNew();
                aifDimensionAttributeValue.parmName(conPeek(accountSegmentNames, dimensionColumn));
                aifDimensionAttributeValue.parmValue(conPeek(accountSegments, dimensionColumn));
            }
        }
    }
}


End

//Only budget transaction line upload - in within Budget Transaction entries Form

void clicked()
{
    BudgetTransactionLine transLine;
    super();
    transLine.BudgetTransactionHeader = BudgetTransactionHeader.RecId;
    transLine.Date = today();
    transLine.LineNumber =1;
    transLine.BudgetType = BudgetType::Expense;
    transLine.LedgerDimension = 5637155864;
    transLine.TransactionCurrency = "KWD";
    transLine.editTransactionCurrencyAmount(true,100);
    transLine.insert();
    BudgetTransactionLine_Ds.editAccountStructure(true,transLine,"FCCI");
    BudgetTransactionLine_Ds.research();
    transLine.clear();
    transLine.BudgetTransactionHeader = BudgetTransactionHeader.RecId;
    transLine.LineNumber =2;
    transLine.Date = today();
    transLine.BudgetType = BudgetType::Expense;
    transLine.LedgerDimension = 5637155859;
    transLine.TransactionCurrency = "USD";
    transLine.editTransactionCurrencyAmount(true,200);
    transLine.insert();
    BudgetTransactionLine_Ds.editAccountStructure(true,transLine,"FCCI");
    BudgetTransactionLine_Ds.research();
    transLine.clear();
    transLine.BudgetTransactionHeader = BudgetTransactionHeader.RecId;
    transLine.LineNumber =3;
    transLine.Date = today();
    transLine.BudgetType = BudgetType::Expense;
    transLine.LedgerDimension = 5637155862;
    transLine.TransactionCurrency = "USD";
    transLine.editTransactionCurrencyAmount(true,300);
    transLine.insert();
    BudgetTransactionLine_Ds.editAccountStructure(true,transLine,"FCCI");
    BudgetTransactionLine_Ds.research();
}