How to import free text invoice in ax 2012
static void importFreeTextInvoice(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Dialog dialog;
Filename filename;
DialogField dialogFilename;
container conFilter = ["Microsoft Excel Worksheets (.xlsx)" ,"*.xlsx"];
int row = 1;
LineNum lineNum;
CustTable custTable;
CustInvoiceTable custInvoiceTable;
CustInvoiceLine custInvoiceLine;
str description = "";
//int i;
CustPostInvoice custPostInvoice;
int iPos;
int dimCount;
container offsetDimensions;
TransDate transDate;
CustAccount custAccount;
Amount amount;
MainAccountNum offsetAccount;
DimensionValue costCenter;
DimensionValue customer;
TaxItemGroup tax;
DimensionDefault line_DimensionDefault;
DimensionDefault updateItemDim(Name _dimName,DimensionDefault _defaultDimension,str _value)
{
DimensionAttributeValueSetStorage dimStorage;
DimensionAttribute dimAttr;
DimensionAttributeValue dimAttributeValue;
DimensionDefault defaultDimension;
dimAttr =DimensionAttribute::findByName(_dimName);
//update dim
dimStorage = DimensionAttributeValueSetStorage::find(_defaultDimension);
dimAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimAttr, _value , true, true);
dimStorage.addItem(dimAttributeValue);
defaultDimension = dimStorage.save();
return defaultDimension;
}
application = SysExcelApplication::construct();
workbooks = application.workbooks();
dialog = new dialog();
dialog.caption("Import Free text invoice");
dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
dialog.filenameLookupFilter(conFilter);
dialog.run();
if(dialog.closedOk())
{
filename = dialogFileName.value();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File not found");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
try
{
//Iterate through cells and get the values
do
{
//Incrementing the row line to next Row
row++;
transDate = cells.item(row,1).value().date();
custAccount = cells.item(row,2).value().bStr();
amount = cells.item(row,3).value().double();
offsetAccount = cells.item(row,4).value().bStr();
costCenter = cells.item(row,5).value().bStr();
customer = cells.item(row,6).value().bStr();
tax = cells.item(row,7).value().bStr();
description = cells.item(row,8).value().bStr();
ttsBegin;
if(costCenter)
{
line_DimensionDefault = updateItemDim("CostCenter",line_DimensionDefault,costCenter);
}
if(customer)
{
line_DimensionDefault = updateItemDim("Customer",line_DimensionDefault,customer);
}
custTable = CustTable::find(custAccount);
custInvoiceTable.initFromCustTable(custTable);
custInvoiceTable.InvoiceDate = transDate;
custInvoiceTable.DefaultDimension = line_DimensionDefault;
custInvoiceTable.insert();
custInvoiceLine.clear();
custInvoiceLine.initValue();
offsetDimensions = [offsetAccount,offsetAccount, 0];
custInvoiceLine.LedgerDimension = AxdDimensionUtil::getLedgerAccountId(offsetDimensions);
custInvoiceLine.initFromCustInvoiceTable(custInvoiceTable);
custInvoiceLine.Quantity = 1;
custInvoiceLine.UnitPrice = amount;
custInvoiceLine.AmountCur = custInvoiceLine.Quantity * custInvoiceLine.UnitPrice;
custInvoiceLine.Description = description;
custInvoiceLine.TaxItemGroup = tax;
custInvoiceLine.ParentRecId = custInvoiceTable.RecId;
lineNum++;
custInvoiceLine.LineNum = lineNum;
custInvoiceLine.insert();
ttsCommit;
// Loads the next row into the variant type and validating that its is empty or not
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
}
catch
{
ttsAbort;
}
info(strFmt("DONE. %1 Records.", (row - 1)));
}
// quits the application
application.quit();
}
Comments
Post a Comment