static void InventoryClosingImportJob(Args _args)
{
InventJournalTable journalTable;
InventJournalTrans journalTrans;
InventJournalTableData journalTableData;
InventJournalTransData journalTransData;
InventTable inventTable;
InventDim locInventDim;
Counter cnt;
ItemIdInventoried Itemid;
InventSiteId inventSiteId;
InventLocationId inventLocationId;
InventBatchId inventBatchId;
InventQtyJournal qty;
ItemCostPrice costprice;
LedgerDimensionDefaultAccount mainacc;
str 20 mainaccStr;
container offSetEntryPattern;
LedgerJournalId JournalId;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog;
Integer row = 1;
int coun;
Mainaccount mainaccount;
int _mainaccountnum; //= "111200";//"202000";
Ledger ledger;
CompanyInfo companyinfo;
str COMVariant2Str(COMVariant _cv)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R8):
// return _cv.toString() ;
return _cv.bStr() ;
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()));
}
}
;
dialog = new Dialog('Import Vendor Details');
dialogFilename = dialog.addField(ExtendedTypeStr("FilenameOpen"));
dialog.filenameLookupTitle('Import from excel.');
dialog.caption('Import From Excel');
dialogFilename.value(filename);
if(dialog.run())
{
filename = dialogFilename.value();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error('File cannot be opened.');
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
try
{
ttsbegin;
// NOTE - JOURNAL ID MUST BE CHANGED BEFORE IMPORT
Select journalTable Where journalTable.JournalId =="TN-000052"; //CHANGE JOURNAL ID HERE
do
{
row++;
itemId = cells.item(row, 1).value().bStr();
inventSiteId = cells.item(row, 2).value().bStr();
inventLocationId = cells.item(row, 3).value().bStr();
inventBatchId = cells.item(row, 4).value().bStr();
qty = cells.item(row, 5).value().double();
costPrice = cells.item(row, 6).value().double();
_mainAccountNum = cells.item(row, 7).value().double();
mainAccstr = int2str(_mainaccountnum);
//department = cells.item(row, 8).value().bStr();
JournalId = cells.item(row, 9).value().bStr();
type = cells.item(row+1, 1).value().variantType();
select recId from MainAccount where mainAccount.MainAccountId ==mainAccstr;
locInventDim.inventBatchId = inventBatchId;
locInventDim.InventSiteId = inventSiteId;
locInventDim.InventLocationId = inventLocationId;
journalTrans.clear();
journalTrans.initFromInventJournalTable(journalTable);
journalTrans.TransDate = cells.item(row, 8).value().date();//systemDateGet();
journalTrans.ItemId = itemId;
journalTrans.JournalId = journalTable.JournalId;//JournalId;//
//Finding out the invent dim id from given value
journalTrans.InventDimId = InventDim::findOrCreate(locInventDim).inventDimId;
journalTrans.PriceUnit = 1;
journalTrans.Qty = qty;
journalTrans.CostPrice = costPrice;
journalTrans.CostAmount = journalTrans.Qty * journalTrans.CostPrice;
journalTrans.LedgerDimension = DimensionStorage::getDefaultAccountForMainAccountNum(mainAccstr);
//Except these below two lines above things are same as like in dynamics AX 2009
//If you want to insert the dimension value with account
//In below code I have no dimension with main account so I just put 0, Instead I have two
//dimension
//write a code like “[“MainAccount”, mainAcc, 2, Dinemsion1, dimVal1, Dimension2, dimVal2)
// offsetEntryPattern = [mainAccstr,0,0];
// journalTrans.LedgerDimension = AxdDimensionUtil::getLedgerAccountId(offsetEntryPattern);
//Default dimension Value either you can insert the dimension value here.
//defaultDim = [1,"Department",department];
//journalTrans.DefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(defaultDim);
journalTrans.insert();
coun++;
print(coun);
}
while (type != COMVariantType::VT_EMPTY);
info(int2str(coun));
ttscommit;
application.quit();
}
catch
{
throw error('Error in import.');
}
}
}
