class ASAPG_Update_ProductMasterOGS_Fields
{
#define.firstColumnTitle("ItemId")
#define.progressCaption("Importing Product Master Fields ")
#define.dialogCaption("Import To ProductMaster ")
#define.ITEMID(1)
#define.STOCKNUMBER(2)
#define.PARTNUMBER(3)
#define.CARTONQTY(4)
#define.BOXQTY(5)
#define.CARTYPE(6)
#define.BRAND(7)
#define.ITEMTYPE(8)
#define.HSCODE(9)
#define.VEHICLETYPE(10)
#define.ORIGIN(11)
#define.REFERENCE(12)
#define.MODELNUMBER(13)
#define.NETWEIGHT(14)
#define.TARAWEIGHT(15)
}
------------
public void readFromExcel(Filename _fileName, ecoResProductMaster _ecoResProductMaster)
{
#AviFiles
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBooks;
SysExcelWorkBook xlsWorkBook;
SysExcelWorkSheets xlsWorkSheets;
SysExcelWorkSheet xlsWorkSheet;
SysExcelRange xlsRange;
SysExcelCells xlsCells;
SysExcelCell xlsCell;
int row = 2; //nonExistitemRow;
int TotalLineNum, importedLines = 0;
SysOperationProgress progressBar;
OGSCTNNumber ctnNumber;
OGSReference refNumber;
ItemNetWeight netWeight;
ItemTaraWeight taraWeight;
OGSBrand brand;
OGSCAR car;
OGSHSCode hsCode;
OGSItemType itemType;
OGSModelNumber modelNumber;
OGSOEMNumber partNumber;
OGSOrigin origin;
OGSVehicleType vehicleType;
OGSBOXNumber boxQty;
OGSSetCode stockNumber;
ItemId itemId;
InventTable inventTable;
EcoResProductMaster ecoResProductMaster;
str _excelColumnName1,_excelColumnName2,_excelColumnName3,_excelColumnName4,_excelColumnName5;
str _excelColumnName6,_excelColumnName7,_excelColumnName8,_excelColumnName9,_excelColumnName10;
str _excelColumnName11,_excelColumnName12,_excelColumnName13,_excelColumnName14,_excelColumnName15;
int LineNum;
str excelRowNum;
SysOperationProgress sysprogressBar = new SysOperationProgress();
if(WinApi::fileExists(_fileName))
{
xlsApplication = SysExcelApplication::construct();
xlsWorkBooks = xlsApplication.workbooks();
try
{
xlsWorkBooks.open(_fileName,0,true);
}
catch (Exception::Error)
{
throw error("file cannot be opened");
}
xlsWorkBook = xlsWorkBooks.item(1);
xlsWorkSheets = xlsWorkBook.worksheets();
xlsWorkSheet = xlsWorkSheets.itemFromNum(1);
xlsCells = xlsWorkSheet.cells();
try
{
do
{
sysprogressBar.setCaption(strfmt("OGS Fields "));
sysprogressBar.setAnimation(#aviUpdate);
sysprogressBar.setText(strfmt("Processing %1 out of %2",row,20));
_excelColumnName1 = xlsCells.item(1,1).value().bStr();
_excelColumnName2 = xlsCells.item(1,2).value().bStr();
_excelColumnName3 = xlsCells.item(1,3).value().bStr();
_excelColumnName4 = xlsCells.item(1,4).value().bStr();
_excelColumnName5 = xlsCells.item(1,5).value().bStr();
_excelColumnName6 = xlsCells.item(1,6).value().bStr();
_excelColumnName7 = xlsCells.item(1,7).value().bStr();
_excelColumnName8 = xlsCells.item(1,8).value().bStr();
_excelColumnName9 = xlsCells.item(1,9).value().bStr();
_excelColumnName10 = xlsCells.item(1,10).value().bStr();
_excelColumnName11 = xlsCells.item(1,11).value().bStr();
_excelColumnName12 = xlsCells.item(1,12).value().bStr();
_excelColumnName13 = xlsCells.item(1,13).value().bStr();
_excelColumnName14 = xlsCells.item(1,14).value().bStr();
_excelColumnName15 = xlsCells.item(1,15).value().bStr();
LineNum++;
ItemId = xlsCells.item(row,1).value().bStr();
stockNumber = xlsCells.item(row,2).value().bStr();
//if(!stockNumber)
//{
//stockNumber = int2str(real2int(xlsCells.item(row,2).value().double()));
//}
partNumber = xlsCells.item(row,3).value().bStr();
//if(!partNumber)
//{
//partNumber = int2str(real2int(xlsCells.item(row,3).value().double()));
//}
ctnNumber = xlsCells.item(row,4).value().bStr();
//if(!ctnNumber)
//{
//ctnNumber = int2str(real2int(xlsCells.item(row,4).value().double()));
//}
boxQty = xlsCells.item(row,5).value().bStr();
//if(!boxQty)
//{
//boxQty = int2str(real2int(xlsCells.item(row,5).value().double()));
//}
car = xlsCells.item(row,6).value().bStr();
//if(!car)
//{
//car = int2str(real2int(xlsCells.item(row,6).value().double()));
//}
brand = xlsCells.item(row,7).value().bStr();
//if(!brand)
//{
//brand = int2str(real2int(xlsCells.item(row,7).value().double()));
//}
itemType = xlsCells.item(row,8).value().bStr();
//if(!itemType)
//{
//itemType = int2str(real2int(xlsCells.item(row,8).value().double()));
//}
hsCode = xlsCells.item(row,9).value().bStr();
//if(!hsCode)
//{
//hsCode = int2str(real2int(xlsCells.item(row,9).value().double()));
//}
vehicleType = xlsCells.item(row,10).value().bStr();
//if(!vehicleType)
//{
//vehicleType = int2str(real2int(xlsCells.item(row,10).value().double()));
//}
origin = xlsCells.item(row,11).value().bStr();
//if(!origin)
//{
//origin = int2str(real2int(xlsCells.item(row,11).value().double()));
//}
refNumber = xlsCells.item(row,12).value().bStr();
//if(!refNumber)
//{
//refNumber = int2str(real2int(xlsCells.item(row,12).value().double()));
//}
modelNumber = xlsCells.item(row,13).value().bStr();
//if(!modelNumber)
//{
//modelNumber = int2str(real2int(xlsCells.item(row,13).value().double()));
//}
netWeight = xlsCells.item(row,14).value().double();
taraWeight = xlsCells.item(row,15).value().double();
ttsbegin;
while select forUpdate ecoResProductMaster
where ecoResProductMaster.DisplayProductNumber == itemId
if(ecoResProductMaster)
{
importedLines++;
if(_excelColumnName2 == "STOCKNUMBER" ) //----- STOCKNUMBER
{
if(stockNumber != '')
{
ecoResProductMaster.OGSSetCode = stockNumber;
ecoResProductMaster.update();
}
}
if(_excelColumnName3 == "PARTNUMBER" ) //----- PARTNUMBER
{
if(partNumber != '')
{
ecoResProductMaster.OGSOEMNumber = partNumber;
ecoResProductMaster.update();
}
}
if(_excelColumnName4 == "CARTONQTY" ) //----- CARTONQTY
{
if(ctnNumber != '')
{
ecoResProductMaster.OGSCTNNumber = ctnNumber;
ecoResProductMaster.update();
}
}
if(_excelColumnName5 == "BOXQTY" ) //----- BOXQTY
{
if(boxQty != '')
{
ecoResProductMaster.OGSBOXQTY = boxQty;
ecoResProductMaster.update();
}
}
if(_excelColumnName6 == "CARTYPE" ) //----- CARTYPE
{
if(ctnNumber != '')
{
ecoResProductMaster.OGSCTNNumber = ctnNumber;
ecoResProductMaster.update();
}
}
if(_excelColumnName7 == "BRAND" ) //----- BRAND
{
if(brand != '')
{
ecoResProductMaster.OGSBrand = brand;
ecoResProductMaster.update();
}
}
if(_excelColumnName8 == "ITEMTYPE" ) //----- ITEMTYPE
{
if(itemType != '')
{
ecoResProductMaster.OGSItemType = itemType;
ecoResProductMaster.update();
}
}
if(_excelColumnName9 == "HSCODE" ) //----- HSCODE
{
if(hsCode != '')
{
ecoResProductMaster.OGSHSCode = hsCode;
ecoResProductMaster.update();
}
}
if(_excelColumnName10 == "VEHICLETYPE" ) //----- VEHICLETYPE
{
if(vehicleType != '')
{
ecoResProductMaster.OGSVehicleType = vehicleType;
ecoResProductMaster.update();
}
}
if(_excelColumnName11 == "ORIGIN" ) //----- ORIGIN
{
if(origin != '')
{
ecoResProductMaster.OGSOrigin = origin;
ecoResProductMaster.update();
}
}
if(_excelColumnName12 == "REFERENCE" ) //----- REFERENCE
{
if(refNumber != '')
{
ecoResProductMaster.OGSReference = refNumber;
ecoResProductMaster.update();
}
}
if(_excelColumnName13 == "MODELNUMBER" ) //----- MODELNUMBER
{
if(modelNumber != '')
{
ecoResProductMaster.OGSModelNumber = modelNumber;
ecoResProductMaster.update();
}
}
if(_excelColumnName14 == "NETWEIGHT" ) //----- NETWEIGHT
{
if(netWeight != 0.00)
{
ecoResProductMaster.OGSNetWeight = netWeight;
ecoResProductMaster.update();
}
}
if(_excelColumnName15 == "TARAWEIGHT" ) //----- TARAWEIGHT
{
if(taraWeight != 0.00)
{
ecoResProductMaster.OGSTaraWeight = taraWeight;
ecoResProductMaster.update();
}
}
else if( _excelColumnName2 != "STOCKNUMBER" || _excelColumnName3 != "PARTNUMBER" || _excelColumnName4 != "CARTONQTY" || _excelColumnName5 != "BOXQTY" || _excelColumnName6 != "CARTYPE" || _excelColumnName7 != "BRAND" ||
_excelColumnName8 != "ITEMTYPE" || _excelColumnName9 != "HSCODE" || _excelColumnName10 != "VEHICLETYPE" || _excelColumnName11 != "ORIGIN" || _excelColumnName12 != "REFERENCE" || _excelColumnName13 != "MODELNUMBER" ||
_excelColumnName14 != "NETWEIGHT" || _excelColumnName15 != "TARAWEIGHT" || _excelColumnName1 != "ITEMID")
{
checkFailed(strFmt("Header Name : %1 Not Matching1 -- %2 Not Matching2 -- %3 Not Matching3 -- %4 Not Matching4 -- %5 Not Matching5 -- %6 Not Matching6 -- %7 Not Matching7 -- %8 Not Matching8 -- %9 Not Matching9 -- %10 Not Matching10 -- %11 Not Matching11 -- %12 Not Matching12 -- %13 Not Matching13 -- %14 Not Matching14 -- %15 Not Matching15",_excelColumnName1,_excelColumnName2,_excelColumnName3,_excelColumnName4,_excelColumnName5,_excelColumnName6,_excelColumnName7,_excelColumnName8,_excelColumnName9,_excelColumnName10,_excelColumnName11,_excelColumnName12,_excelColumnName13,_excelColumnName14,_excelColumnName15));
// checkFailed(strFmt("Header Name : %1 Not Matching6 -- %2 Not Matching7 -- %3 Not Matching8 -- %4 Not Matching9 -- %5 Not Matching10",_excelColumnName6,_excelColumnName7,_excelColumnName8,_excelColumnName9,_excelColumnName10));
//checkFailed(strFmt("Header Name : %1 Not Matching11 -- %2 Not Matching12 -- %3 Not Matching13 -- %4 Not Matching14 -- %5 Not Matching15",_excelColumnName11,_excelColumnName12,_excelColumnName13,_excelColumnName14,_excelColumnName15));
break;
}
}
else
{
info(strFmt("Item : %1 Not available",ItemId));
}
ttscommit;
row++;
}
while(xlsCells.item(row,#ITEMID).value().bStr());
}
catch(Exception::Error)
{
error("error");
//checkFailed(strFmt("Item %1", itemId));
}
}
info(strFmt("%1 rows imported from file",importedLines));
}
------------\
public void run(ecoResProductMaster _ecoResProductMaster)
{
Dialog dialog;
DialogField dialogFileName;
FileName errorFile;
str fileName;
fileName = WinAPI::getOpenFileName(0,['Excel','*.xlsx'],'',' OGS Fields ','xlsx',fileName);
if(fileName)
{
this.readFromExcel(fileName,_ecoResProductMaster);
}
}
----
public static void main(Args _args)
{
EcoResProductMaster ecoResProductMaster;
if (_args)
{
ecoResProductMaster = _args.record();
}
new ASAPG_Update_ProductMasterOGS_Fields().run(ecoResProductMaster);
}
No comments:
Post a Comment