//coded by fanddy
static void uploadProductionOrder(Args _args)
{
// Progress Bar Variables
#AviFiles
SysOperationProgress _ProgressBar = new SysOperationProgress();
// Excel Variables
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
str Filename;
COMVariantType type;
int row;
FilenameFilter filenameFilter = ['*.xlsx,*.xls'];
//variable
ProdId prodId;
ItemId itemid;
Qty qtySched;
TransDate StartDate,endDate,deliveryDate,bomDate;
InventSiteId inventSiteId;
InventLocationId inventLocationId;
ProdStatus prodStatus;
ProdBackStatus prodBackStatus;
ProdReservation prodReservation;
BOMId bomId;
//tables
ProdTable prodtable;
InventTable inventTable;
InventDim inventDim;
// convert into str from excel cell value
str COMVariant2Str(COMVariant _cv, int _decimals = 0, 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(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY): return '';
default:
throw error(strfmt('@SYS26908', _cv.variantType()));
}
return '';
}
filename = WinAPI::getOpenFileName(0, filenameFilter, '', '');
if (filename=='')
{
warning('Filename must be specified correctly.');
return;
}
//Filename = "C:\\prodorder.xls";
application = SysExcelApplication::construct();
workbooks = application.workbooks();
// gets the default ExchangeType RecId
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();
row = 1;
// Start Progress Bar
_ProgressBar.setCaption("Upload Production Order...");
_ProgressBar.setAnimation(#AviTransfer);
ttsBegin;
do
{
//Incrementing the row line to next Row
row++;
_ProgressBar.setText(strFmt('Processing %1...', row));
prodId = COMVariant2Str(cells.item(row,1).value());
itemid = COMVariant2Str(cells.item(row,2).value());
qtySched = cells.item(row,3).value().double();
startDate = cells.item(row,4).value().date();
endDate = cells.item(row,5).value().date();
inventSiteId = COMVariant2Str(cells.item(row,6).value());
inventLocationId = COMVariant2Str(cells.item(row,7).value());
prodStatus = str2enum(ProdStatus,COMVariant2Str(cells.item(row,8).value()));
prodBackStatus = str2enum(ProdBackStatus,COMVariant2Str(cells.item(row,9).value()));
deliveryDate = cells.item(row,10).value().date();
prodReservation = str2enum(prodReservation,COMVariant2Str(cells.item(row,11).value()));
bomDate = cells.item(row,12).value().date();
bomId = COMVariant2Str(cells.item(row,13).value());
// Initialize InventTable
inventTable.clear();
inventTable = inventTable::find(itemid);
// Initialize the base values
prodtable.clear();
prodtable.initValue();
prodtable.initFromInventTable(inventTable);
prodtable.ProdId = prodId;
prodtable.ItemId = inventTable.ItemId;
prodtable.QtySched = qtySched;
prodtable.SchedStart = StartDate;
prodtable.SchedEnd = endDate;
prodtable.DlvDate = deliveryDate;
prodtable.RemainInventPhysical = qtySched;
prodtable.BOMDate = today();
prodtable.ProdStatus = prodStatus;
prodtable.BackorderStatus = prodBackStatus;
prodtable.Reservation = prodReservation;
// Initialize InventDim (Obrigatory)
inventDim.clear();
inventDim.InventSiteId = inventSiteId;
inventDim.InventLocationId = inventLocationId;
inventDim = inventDim::findOrCreate(inventDim);
prodtable.InventDimId = inventDim.inventDimId;
// Set the active BOM and Route
if(!bomId)
{
prodtable.BOMId = BOMVersion::findActive(prodtable.ItemId,
prodtable.BOMDate,
prodtable.QtySched,
inventDim).BOMId;
}
if(!prodtable.RouteId)
{
prodtable.RouteId = RouteVersion::findActive(prodtable.ItemId,
prodtable.BOMDate,
prodtable.QtySched,
inventDim).RouteId;
}
// Initialize BOMVersion
prodtable.initBOMVersion();
// Initialize RouteVersion
prodtable.initRouteVersion();
//Use ProdTableType class to create the production order
prodtable.type().insert();
print (strFmt('Line number %1 - Production order %2 - Item ID %3', row,prodtable.ProdId, itemId));
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
ttsCommit;
// quits the application
application.quit();
info('Upload success');
}
Like this:
Like Loading...
Related