this source below is x++ source code to upload tradeagreement di ax 2012
can be modified for other use,
static void ron_TradeAgreement_ver2(Args _args)
{
//created by ronny febri
//date created 12-12-2013
//revision 2017-03-08 17:42
//upload data trade agreement and autoclosed old trade agreement
//versi ini mirip dengan versi sebelumnya,
//perbedaannya, adanya perbaikan pada saat closed tradeagreementnya (yang benar)
//dan bisa digunakan untuk upload tradeagreement secara umum,
//perhatikan pada baris paling bawah, harus setting transaction models nya
//nama file xlsx nya
Filename FileNamePath = 'F:\\MTL_-06938.xlsx';
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
#WinApi
#File
SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
str itemId;
int LengthRows = 2440;
int ron_UploadTradeAgreement(PriceType TradeAgreementType)
{
int i=0, j=0;
Filename Filename;
InventTable invtable;
InventDim _inventDim;
InventTable _inventtable;
PriceDiscAdmTable _pricediscadmtable;
PriceDiscAdmTrans _pricediscadmtrans;
EnumId AccCode,ItemCode;
PriceDiscTable _pricedisctable;
//date tgl;
int TType;
;
startLengthyOperation();
switch(TradeAgreementType)
{
case PriceType::PricePurch:
case PriceType::LineDiscPurch:
case PriceType::MultiLineDiscPurch:
case PriceType::EndDiscPurch:
TType = ModuleInventCustVend::Vend;
break;
case PriceType::PriceSales:
case PriceType::LineDiscSales:
case PriceType::MultiLineDiscSales:
case PriceType::EndDiscSales:
TType = ModuleInventCustVend::Cust;
break;
}
if (!WinAPI::fileExists(FileNamePath))
throw error(strfmt("@SYS109820", FileNamePath));
if (workbooks.open(FileNamePath, 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();
i = 3;
while (true)
{
progress.setCaption('Progress');
progress.setText(strfmt("Row %1", i-2));
progress.setTotal(LengthRows);
progress.setCount(i-2, 1);
if (cells.item(i, 2).value().bStr() == "" || i > 65536)
break;
else
{
ttsBegin;
try
{
itemId = cells.item(i, 6).value().bStr();
select firstonly _pricediscadmtable where _pricediscadmtable.JournalNum == cells.item(i, 17).value().bStr();
if (_pricediscadmtable)
{
_inventDim.clear();
_inventDim.configId = cells.item(i, 7).value().bStr();
_inventDim.InventSizeId = cells.item(i, 8).value().bStr();
_inventDim.InventColorId = cells.item(i, 9).value().bStr();
_inventDim = InventDim::findOrCreate(_inventDim);
if (cells.item(i, 3).value().bStr() == "Table")
AccCode = TableGroupAll::Table;
else if (cells.item(i, 3).value().bStr() == "Group")
AccCode = TableGroupAll::GroupId;
else
AccCode = TableGroupAll::All;
if (cells.item(i, 5).value().bStr() == "Table")
ItemCode = TableGroupAll::Table;
else if (cells.item(i, 5).value().bStr() == "Group")
ItemCode = TableGroupAll::GroupId;
else
ItemCode = TableGroupAll::All;
_pricediscadmtrans.clear();
_pricediscadmtrans.AccountCode = AccCode;
_pricediscadmtrans.AccountRelation = cells.item(i, 4).value().bStr();
_pricediscadmtrans.Percent1 = cells.item(i, 11).value().double();
_pricediscadmtrans.Amount = cells.item(i, 13).value().double();
_pricediscadmtrans.Currency = cells.item(i, 14).value().bStr();
_pricediscadmtrans.FromDate = cells.item(i, 15).value().date();
_pricediscadmtrans.ToDate = cells.item(i, 16).value().date();
_pricediscadmtrans.InventDimId = _inventDim.inventDimId;
_pricediscadmtrans.ItemCode = ItemCode;
_pricediscadmtrans.ItemRelation = cells.item(i, 6).value().bStr();
_pricediscadmtrans.JournalNum = _pricediscadmtable.JournalNum;
_pricediscadmtrans.Module = TType;
_pricediscadmtrans.PriceUnit = 1;
_pricediscadmtrans.QuantityAmountFrom = 1;
_pricediscadmtrans.relation = TradeAgreementType;
_pricediscadmtrans.UnitId = cells.item(i, 12).value().bStr();
_pricediscadmtrans.UnitId = cells.item(i, 12).value().bStr();
_pricediscadmtrans.insert();
}
i++;
j++;
//print(strfmt("Line %1 is succeeded", i));
}
catch
{
info(strfmt("Line %1 is error for item %2", i, itemid));
i++;
}
ttsCommit;
}
}
}
application.quit();
return j;
}
int ron_ClosedTradeAgreement(PriceType TradeAgreementType)
{
/*
#WinApi
#File
SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
*/
int i=0, j=0;
Filename Filename;
InventTable invtable;
InventDim _inventDim;
InventTable _inventtable;
PriceDiscAdmTable _pricediscadmtable;
PriceDiscAdmTrans _pricediscadmtrans;
PriceDiscTable _pricedisctable;
//date tgl;
int TType;
//str FileNamePath;
;
startLengthyOperation();
switch(TradeAgreementType)
{
case PriceType::PricePurch:
case PriceType::LineDiscPurch:
case PriceType::MultiLineDiscPurch:
case PriceType::EndDiscPurch:
TType = ModuleInventCustVend::Vend;
break;
case PriceType::PriceSales:
case PriceType::LineDiscSales:
case PriceType::MultiLineDiscSales:
case PriceType::EndDiscSales:
TType = ModuleInventCustVend::Cust;
break;
}
if (!WinAPI::fileExists(FileNamePath))
throw error(strfmt("@SYS109820", FileNamePath));
if (workbooks.open(FileNamePath, 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();
i = 3;
while (true)
{
progress.setCaption('Progress');
progress.setText(strfmt("Row %1", i-2));
progress.setTotal(LengthRows);
progress.setCount(i-2, 1);
if (cells.item(i, 2).value().bStr() == "" || i > 65536)
break;
else
{
ttsBegin;
try
{
//difference starts here - ron
itemId = cells.item(i, 6).value().bStr();
while select forUpdate _pricedisctable
where _pricedisctable.Module == TType
&& _pricedisctable.relation == TradeAgreementType
&& _pricedisctable.itemrelation==cells.item(i, 6).value().bStr()
{
if(_pricedisctable.ToDate == str2Date("1900-01-01",321))
{
if(_pricedisctable.AccountCode == TableGroupAll::All)
{
_pricedisctable.ToDate = cells.item(i, 18).value().date();
_pricedisctable.SearchAgain = 1;
_pricedisctable.update();
}
if(_pricedisctable.AccountCode == TableGroupAll::Table)
{
_pricedisctable.ToDate = cells.item(i, 18).value().date();
_pricedisctable.SearchAgain = 1;
_pricedisctable.AccountRelation = cells.item(i, 4).value().bStr();
_pricedisctable.update();
}
}
}
//difference ends here
i++;
j++;
//print(strfmt("Line %1 is succeeded", i));
}
catch
{
info(strfmt("Line %1 is error for item %2", i, itemid));
i++;
}
ttsCommit;
}
}
}
application.quit();
return j;
}
//rubah di sini
//jika mau gunakan utk kepentingan lain, gunakan pricetype::
info(strfmt("%1 data closed", ron_ClosedTradeAgreement(PriceType::LineDiscSales)));
info(strfmt("%1 data uploaded", ron_UploadTradeAgreement(PriceType::LineDiscSales)));
}