Work with a file
OpenFile
Function File::Exists : //ret = System.IO.File::Exists(fileNameComplete);
//fileNameComplete = @'\\erpapp\\interface\\test.txt'; //Ok -X "\\erpapp\\interface\\test.txt"
//fileNameComplete = "\\erpapp\\interface\\test.txt"; //Not Ok -X "\erpapp\interface\test.txt"
//fileNameComplete = "\\\\\erpapp\\interface\\test.txt";//Ok -X "\\erpapp\interface\test.txt"
// fileName = @"C:\Tmp\toto.txt" //OK
// fileName = "C:\\Tmp\\toto.txt" //To test
public TextIO openFile(FilePath _fileName, str _mode)
{
FileIOPermission permission;
TextIO file;
;
// Revert permissions
CodeAccessPermission::revertAssert();
// Assert permission
permission = new FileIOPermission(_fileName, _mode);
permission.assert();
file = new TextIo(_fileName, _mode,this.codepage());
//file = new TextIo(fileName, 'R'); //We don't put code
//This format is the default (UTF-8) that read also the hebrew.
if (!file)
{
throw error(strFmt("@SPL1021",_fileName));
}
file.inFieldDelimiter(#FieldDelimiter);
file.inRecordDelimiter(#RecordDelimiter);
file.outFieldDelimiter(#FieldDelimiter);
file.outRecordDelimiter(#RecordDelimiter);
return file;
}
private int codepage()
{
#Localcodepage
;
return #cp_1255;
}
class SPLPsMaintenanceImport extends RunBaseBatch
{
//Fields for file
TextIo file;
#define.fieldDelimiter(",")
#define.recordDelimiter("\r\n")
}
//remarque il vaut mieux que file soit une variable globale.
CheckDir
boolean checkDir()
{
FilePath fileNameComplete;
LPLInterfaceFilePath SPLRotlexFilePath;
FileIOPermission fileIOPerm;
boolean pathExists;
;
if (isRunningOnServer())
{
fileIOPerm = new FileIOPermission(RotlexDirIn,'R');
fileIOPerm.assert();
pathExists = WinAPIServer::pathExists(RotlexDirIn);
CodeAccessPermission::revertAssert();
}
else
{
pathExists = WinAPI::pathExists(RotlexDirIn);
}
if (!pathExists)
{
info(strfmt("@SPL1029",RotlexDirIn));
return false;
}
return true;
}
class SPLPsMaintenanceImport extends RunBaseBatch
{
Filename RotlexDirIn;
}
Move files
// in batch take this code, because the copy is Ok btu the delete need permission.
CodeAccessPermission::revertAssert();
// Assert permission
permissionSet = new Set(Types::Class);
permissionSet.add(new FileIOPermission(strFileName, 'R'));
permissionSet.add(new FileIOPermission(strPrintDevice, 'R'));
permissionSet.add(new FileIOPermission(strPrintDevice, 'W'));
CodeAccessPermission::assertMultiple(permissionSet);
WinAPIServer::copyFile(strFileName, strPrintDevice, true);
// Revert permissions
CodeAccessPermission::revertAssert();
} // end while
textio = null;
// 18/08/2014-STEPHANE-SPL-BNK-1101-CHECKTOPIMAGE-BEGIN
// DeleteFile
CodeAccessPermission::revertAssert();
// Assert permission
permissionSet = new Set(Types::Class);
permissionSet.add(new FileIOPermission(strFileName, 'R'));
permissionSet.add(new FileIOPermission(strFileName, 'W'));
CodeAccessPermission::assertMultiple(permissionSet);
WinAPIServer::deleteFile(strFileName);
// Revert permissions
CodeAccessPermission::revertAssert();
private void moveFiles(str _fileToCopy,FilePath _destDir)
{
//Filename fileToCopy;
FilePath sourcesDestDir; // this will have the path to the sources dir under the web references root Dir.
System.IO.FileInfo fileInfo;
str fileExtension;
FilePath fileDestToCopy;
InteropPermission interopPerm;
FileIOPermission fileIOPerm;
FileIOPermission sourceFileWritePerm;
FileIOPermission processedPathReadPerm;
Set permissionSet;
boolean bServer;
str 10 dateStr;
str 10 timestr;
timeOfDay now;
Filename filenameOnly,type,path;
;
#define.FilePathDelimiter('\\')
#define.read('R')
#define.write('W')
dateStr=strrem(date2str(systemdateget(), 321, 2, -1, 2, -1, 4),'/');
now = timenow();
timestr = strrem(time2str(now,1,1),':');
if(!_fileToCopy || !_destDir)
{
return;
}
if (isRunningOnServer())
bServer = true;
else
bServer = false;
_destDir = _destDir + #FilePathDelimiter;
[path, filenameOnly, type] = fileNameSplit(_fileToCopy);
fileDestToCopy = _destDir + '\\' + filenameOnly + '_' + dateStr + timestr + '.csv';
if (bServer)
{
// Revert permissions
CodeAccessPermission::revertAssert();
// Assert permission
permissionSet = new Set(Types::Class);
permissionSet.add(new FileIOPermission(_fileToCopy, #read));
permissionSet.add(new FileIOPermission(fileDestToCopy, #read));//dest
permissionSet.add(new FileIOPermission(fileDestToCopy, #write));//dest
CodeAccessPermission::assertMultiple(permissionSet);
// Move to processed folder
if (WinAPIServer::fileExists(fileDestToCopy))
{
info(strfmt("@SPL894",_fileToCopy,_destDir));
}
else
{
WinAPIServer::copyFile(_fileToCopy, fileDestToCopy, true);
// Revert permissions
CodeAccessPermission::revertAssert();
sourceFileWritePerm = new FileIOPermission(_fileToCopy, #write);
// Assert permission
sourceFileWritePerm.assert();
WinAPIServer::deleteFile(_fileToCopy);
}
// Revert permissions
CodeAccessPermission::revertAssert();
}
else
{
if (WinAPI::fileExistsClient(fileDestToCopy))
{
info(strfmt("@SPL894",_fileToCopy,_destDir));
}
else
WinAPI::moveFile(_fileToCopy,fileDestToCopy);
}
}
Close
// Finalize remove the locked : WinAPIServer::fileLocked("") ->Indicate if locked
public final void closeFileIN()
{
fileIN.finalize();
fileIn = null;
}
public final void closeFileIN()
{
fileIN = null;
}
Get Files of a directory
// It is also good in Batch !
private void getFilesInDirectory(FilePath dir, str filePattern)
{
int i;
int length;
str nextFile;
System.Array files;
container fileNameCon;
;
listFilesRotexWithDir = new List(Types::String);
listFilesRotex = new List(Types::String);
// get CLR interop permission
new InteropPermission(InteropKind::ClrInterop).assert();
files = System.IO.Directory::GetFiles(dir, filePattern);
if(files)
{
length =files.get_Length();
for(i=0;i "lesser than" Length;i++)
{
nextFile = files.GetValue(i);
listFilesRotexWithDir.addEnd(nextFile);
fileNameCon = fileNameSplit(nextFile);
listFilesRotex.addEnd(conpeek(fileNameCon, 2) + conpeek(fileNameCon, 3));
}
}
CodeAccessPermission::revertAssert();
}
Code Page
Code page for UTF-8 : 65001. : #define.cp_65001(65001) //UTF-8
With this code we can write a file csv and if we double click on it, we can see the hebrew on Excel.
If we use 1255 and we can write a file csv and if we double click on it, we cannot see the hebrew on Excel.
We need to do an import of the text file with the good code page.
file = new TextIo(fileName, 'R'); //We don't put code
This format is the default (UTF-8) that read also the hebrew.
Job that write a file
static void SPL_WriteFile(Args _args)
{
//Fields for file
TextIo file;
FilePath SPLBarTenderPath = "E:\\TMP\\SH";
FileIOPermission permission;
FilePath fileNameComplete;
container contFields;
Str f1,f2,f3;
int i;
#define.fieldDelimiter(";")
#define.recordDelimiter("\r\n")
#define.OpenMode("w")
#Localcodepage
void initF()
{
f1 = ""; f2 = ""; f3 = "";
}
i = 0;
while (i less than 3)
{
if (i == 0)
{
fileNameComplete = SPLBarTenderPath + "\\" + "test" + ".CSV";
// Revert permissions
CodeAccessPermission::revertAssert();
// Assert permission
permission = new FileIOPermission(fileNameComplete, "w");
permission.assert();
file = new TextIo(fileNameComplete, "w",#cp_1255);
if (!file)
{
throw error(strFmt("@SPL230",fileNameComplete));
}
file.outFieldDelimiter(#FieldDelimiter);
file.outRecordDelimiter(#RecordDelimiter);
}
initF();
f1 = "Y";
f2 = "A";
f3 = "A";
contFields = [f1,f2,f3];
file.writeExp(contFields);
i++;
}
if (i)
{
file = null;
}
info(strfmt("@SPL1061",int2str(i)));
}
WriteFile cont +
VOID writeIntoFile()
{
container contFields;
salesLine salesLine;
int i = 0;
queryRun qr;
;
qr = new QueryRun(this);
while (qr.next())
{
SalesLine = qr.get(tablenum(SalesLine));
i++;
if (i == 1)
this.openFile();
if (i == 1)
{
contFields = conNull();
contFields += [element.design().lookupCaption()]; //ReportName
this.write(contFields);
contFields = conNull();
contFields += [CompanyInfo::name()]; //Company name
this.write(contFields);
contFields = conNull();
contFields += [SysLabel::labelId2String2(literalstr("@SIL273"), lgStr)]; //Delivery date
contFields += [this.DeliveryDateDispl()];
this.write(contFields);
contFields = conNull();
contFields += [SysLabel::labelId2String2(literalstr("@SYS6437"), lgStr)]; //Warehouse
contFields += [this.InventlocationIdDispl()];
this.write(contFields);
contFields = conNull();
contFields += [SysLabel::labelId2String2(literalstr("@SYS54367"), lgStr)]; //Expiry date
contFields += [this.ExpiryDateDispl()];
this.write(contFields);
contFields = conNull();
contFields += [SysLabel::labelId2String2(literalstr("@SIL258"), lgStr)]; //Range item
contFields += [this.ItemIdDispl()];
this.write(contFields);
contFields = conNull();
contFields += [SysLabel::labelId2String2(literalstr("@SIL259"), lgStr)]; //Range customer
contFields += [this.CustAccountDispl()];
this.write(contFields);
contFields = conNull();
contFields += [SysLabel::labelId2String2(literalstr("@SYS24704"), lgStr)]; //Customer
contFields += [SysLabel::labelId2String2(literalstr("@SYS7399"), lgStr)]; //Customer name
contFields += [SysLabel::labelId2String2(literalstr("@SIL278"), lgStr)]; //Item
contFields += [SysLabel::labelId2String2(literalstr("@SYS5398"), lgStr)]; //Item name
contFields += [SysLabel::labelId2String2(literalstr("@SYS6437"), lgStr)]; //Warehouse
contFields += [SysLabel::labelId2String2(literalstr("@SIL279"), lgStr)]; //Qty for customer
contFields += [SysLabel::labelId2String2(literalstr("@SYS40864"), lgStr)]; //Unit
contFields += [SysLabel::labelId2String2(literalstr("@SIL252"), lgStr)]; //Qty Packed
contFields += [SysLabel::labelId2String2(literalstr("@SIL254"), lgStr)]; //Qty Sticker
this.write(contFields);
}
// We put the same value in the fields of the file that are in the fields of the report.
contFields = conNull();
contFields += [SalesLine.CustAccount];
contFields += [strReplace(SalesLine.SDXILCustName(), ',', ' ')];
contFields += [SalesLine.ItemId];
contFields += [strReplace(SalesLine.SDXILItemName(), ',', ' ')];
contFields += [SalesLine.SDXILInventLocationId()];
contFields += [SalesLine.salesQty];
contFields += [SalesLine.SalesUnit];
contFields += [SalesLine.SDXILQtyPacked()];
contFields += [SalesLine.SDXILQtySticker()];
this.write(contFields);
}
if (i)
{
this.closeFile();
info(strfmt("@SIL243",int2str(i - 1)));
}
}
Example of class
//- classDeclaration
//- checkDir
//- closeFile
//- codePage
//- file
//- openFile
//- run
//- write
//- writeIntoFile
//- construct
//- main
class SPLBarTender
{
SPLBarTenderEvent barTenderEvent;
TextIo file;
FilePath splBarTenderPath, splBarTenderFormatPath;
#define.fieldDelimiter(";")
#define.recordDelimiter("\r\n")
#define.OpenMode("w")
}
boolean checkDir()
{
FilePath fileNameComplete;
LPLInterfaceFilePath SPLRotlexFilePath;
FileIOPermission fileIOPerm;
boolean pathExists;
;
if (isRunningOnServer())
{
fileIOPerm = new FileIOPermission(SPLBarTenderPath,'R');
fileIOPerm.assert();
pathExists = WinAPIServer::pathExists(SPLBarTenderPath);
CodeAccessPermission::revertAssert();
}
else
{
pathExists = WinAPI::pathExists(SPLBarTenderPath);
}
if (!pathExists)
{
info(strfmt("@SPL1057",SPLBarTenderPath));
return false;
}
return true;
}
public final void closeFile()
{
file = null;
}
///
///Returns the code page for Windows Hebrew
///
///
/// code page 1255
///
private int codepage()
{
#Localcodepage
;
return #cp_1255;
}
protected final TextIo file()
{
return file;
}
public final void openFile(SPLTypeStickerId _splTypeStickerId = "")
{
FileIOPermission permission;
FilePath fileNameComplete;
date sysDate;
str 10 dateStr;
str 10 timestr;
timeOfDay now;
EndDateTime thisDate;
;
fileNameComplete = splBarTenderPath + "\\" + this.fileNameEvent()+'_'+ curuserid()+ '.txt';
// 08/05/2012-ADMIN-SPL-INV-014-BARTENDER-END
// Revert permissions
CodeAccessPermission::revertAssert();
// Assert permission
permission = new FileIOPermission(fileNameComplete, "w");
permission.assert();
file = new TextIo(fileNameComplete, "w",this.codepage());
if (!file)
{
throw error(strFmt("@SYS76826",fileNameComplete));
}
file.outFieldDelimiter(#FieldDelimiter);
file.outRecordDelimiter(#RecordDelimiter);
file.write(strfmt('%BTW% /AF="%1\\%2.btw" /D=%Trigger File Name% /R=2 /P /prn="%3" /%END%',
splBarTenderFormatPath, _splTypeStickerId ? _splTypeStickerId : this.fileNameEvent(),
this.printerName()));
}
public void run()
{
#OCCRetryCount
InventParameters inventParameters = InventParameters::find();
//if (! this.validate())
// throw error("");
try
{
SPLBarTenderPath = inventParameters.SPLBarTenderPath;
if (!SPLBarTenderPath)
throw error("@SPL1050");
SPLBarTenderFormatPath = inventParameters.SPLBarTenderFormatPath;
if (this.checkDir())
this.writeIntoFile();
}
catch (Exception::Deadlock)
{
retry;
}
catch (Exception::UpdateConflict)
{
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::UpdateConflictNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::UpdateConflict;
}
}
}
public void write(container _fields)
{
this.file().writeExp(_fields);
}
void writeIntoFile()
{
container contFields;
SPLTypeStickerId f1_1_StickerId;
ItemBarCode f1_2_ItemBarCode;
InventSizeId f1_3_InventSizeId;
InventSerialId f1_4_InventSerialId;
LPLInventBrandId f1_5_LPLInventBrandId;
str 10 f1_6_SPLCoDia;
str 10 f1_7_SPLInventBC;
SPLInventBC2 f1_8_SPLInventBC2;
str 10 f1_9_SPLSphere;
str 10 f1_10_SPLCly;
str 10 f1_11_SPLAxis;
SPLAdd f1_12_SPLAdd;
SPLAdd2 f1_13_SPLAdd2;
ItemName f1_14_SPLMaterial;
str f1_15_TexDefault;
SPLInventWaterPercent f1_16_SPLInventWaterPercent;
SPLInventRawMaterialType f1_17_SPLInventRawMaterialType;
SPLInventIndex f1_18_SPLInventIndex;
SalesId f1_19_SalesId;
PurchId f1_20_PurchId;
LineNum f1_21_SalesLineNum;
InventBatchId f1_22_inventBatchId;
str 7 f1_23_expDate; //format mm-yyyy
Qty f1_24_qty;
ProdId f1_25_ProdId;
CustAccount f1_26_CustAccount;
CustName f1_27_CustName;
WMSRouteId f1_28_WMSRouteId;
WMSLocationId f1_29_WMSLocationId;
Qty f1_30_PickedQty;
SPLInventColorId f1_31_SPLColor;
Str 1 f1_32_Test;
// 18/02/2013-ADMIN-SPL-YSA-20130218-BEGIN
SPLSkirt fl_33_Skirt;
str 10 fl_34_Edge;
// 18/02/2013-ADMIN-SPL-YSA-20130218-END
//fields for customer account, name + address
CustAccount f2_1_CustAccount;
CustName f2_2_CustName;
Name f2_3_Name;
str f2_4_type;
AddressStreet f2_5_Street;
LPLSettlementId f2_6_LPLSettlementId;
AddressZipCodeId f2_7_ZipCode;
AddressCity f2_8_City;
AddressCountyId f2_9_County;
AddressStateId f2_10_State;
AddressCountryRegionId f2_11_CountryRegionId;
InventTable inventTable, inventTableRawMaterial;
InventDim inventDim;
SalesLine salesLine;
SPLInventBarTender SPLInventBarTender;
InventBatch inventBatch;
Address address;
InventSerial inventSerial;
Common RecordLocal2;
InventTrans inventTrans; //used only for PurchLine.
Common RecordLocal;
custTable custTable;
Map mapRecord;
MapIterator mi;
int i = 0;
boolean ret;
ProdBom prodBOM;
void initF1()
{
f1_1_StickerId = stickerDefault; f1_2_ItemBarCode = ""; f1_3_InventSizeId = ""; f1_4_InventSerialId = "";
f1_5_LPLInventBrandId = ""; f1_6_SPLCoDia = ""; f1_7_SPLInventBC = ""; f1_8_SPLInventBC2 = 0;
f1_9_SPLSphere = ""; f1_10_SPLCly = ""; f1_11_SPLAxis = ""; f1_12_SPLAdd = 0;
f1_13_SPLAdd2 = 0; f1_14_SPLMaterial = ""; f1_15_TexDefault= ""; f1_16_SPLInventWaterPercent = 0;
f1_17_SPLInventRawMaterialType = "";f1_18_SPLInventIndex = ""; f1_19_SalesId = ""; f1_20_PurchId = "";
f1_21_SalesLineNum = 0; f1_22_inventBatchId = ""; f1_23_expDate = ""; f1_24_qty = 0;
f1_25_ProdId = ""; f1_26_CustAccount = ""; f1_27_CustName = ""; f1_28_WMSRouteId = "";
f1_29_WMSLocationId = ""; f1_30_PickedQty = 0; f1_31_SPLColor = ""; f1_32_Test = "";
fl_33_Skirt = ""; fl_34_Edge = "";
}
// 15-07-12-SBE-BEGIN
//mapRecord = new Map(typeId2Type(typeid(recId)), Types::Record);
mapRecord = new Map(typeId2Type(typeid(recId)), Types::Container);
// 15-07-12-SBE-END
this.insertMapRecord(mapRecord);
mi = new MapIterator(mapRecord);
mi.begin();
while (mi.more())
{
[RecordLocal,RecordLocal2] = mi.value();
this.parmCommon(RecordLocal);
inventTrans = RecordLocal2;
this.initVal();
//file structure 1 : ALL Cases except Customers
if (this.fileStruct() == 1)
{
initF1();
inventTable = this.inventTable();
inventDim = this.inventDim();
inventTableRawMaterial = InventTable::find(inventTable.SPLInventRawMaterialId);
ret = this.checkLine();
if (ret)
{
i++;
SPLInventBarTender = SPLInventBarTender::FindSticker(ModuleCustVendEvent,
BarTenderEvent,
InventTable.ItemId,
InventTable.LPLInventBrandId,
this.accountNum());
if (i == 1)
this.openFile(SPLInventBarTender.SPLTypeStickerId);
if (SPLInventBarTender)
f1_1_StickerId = SPLInventBarTender.SPLTypeStickerId;
if (this.clearInventTable())
inventTable.clear(); //case of RAFSimple.
if (inventTable)
{
if (!inventDim)
{
if (parmRecord.TableId == tablenum(inventItemBarCode))
f1_2_ItemBarCode = this.inventItemBarCodeBC();
else
f1_2_ItemBarCode = InventItemBarCode::findSalesBarcode(inventTable.ItemId).itemBarCode;
}
else
{
if (parmRecord.TableId == tablenum(inventItemBarCode))
f1_2_ItemBarCode = this.inventItemBarCodeBC();
else
f1_2_ItemBarCode = InventItemBarCode::findSalesBarcodeDimension(inventTable.ItemId,inventDim).itemBarCode;
f1_3_InventSizeId = inventDim.InventSizeId;
f1_4_InventSerialId = inventDim.inventSerialId;
}
f1_5_LPLInventBrandId = inventTable.LPLInventBrandId;
f1_6_SPLCoDia = this.SPLCoDia(inventTable);
f1_7_SPLInventBC = this.SPLInventBC(inventTable);
f1_8_SPLInventBC2 = this.SPLInventBC2(inventTable);
f1_9_SPLSphere = this.SPLSphere(inventTable);
f1_10_SPLCly = this.SPLCly(inventTable);
f1_11_SPLAxis = this.SPLAxis(inventTable);
f1_12_SPLAdd = this.SPLAdd(inventTable);
f1_13_SPLAdd2 = this.SPLAdd2(inventTable);
f1_14_SPLMaterial = inventTableRawMaterial.ItemName;
f1_15_TexDefault = Global::strRemoveCr(inventTable.SPLtxtDefault());
if (!f1_15_TexDefault)
f1_15_TexDefault = inventTable.ItemName;
f1_16_SPLInventWaterPercent = inventTableRawMaterial.SPLInventWaterPercent;
f1_17_SPLInventRawMaterialType = inventTableRawMaterial.SPLInventRawMaterialType;
f1_18_SPLInventIndex = inventTableRawMaterial.SPLInventIndex;
f1_31_SPLColor = inventTable.SPLColor;
// 18/02/2013-ADMIN-SPL-YSA-20130218-BEGIN
fl_33_Skirt = inventTable.SPLskirt;
fl_34_Edge = this.SPLCoEdge(inventTable);
// 18/02/2013-ADMIN-SPL-YSA-20130218-END
}
salesLine = this.salesLine();
if (salesLine)
{
f1_19_SalesId = salesLine.SalesId;
f1_21_SalesLineNum = salesLine.LineNum;
}
f1_20_PurchId = this.purchId();
f1_22_inventBatchId = this.BatchId(inventDim.inventBatchId,inventTable.SPLInventRawMaterialId,
inventTrans, parmInventBatchId);
inventBatch.clear();
inventSerial.clear();
if (f1_22_inventBatchId)
{
inventBatch = this.InventBatch(f1_22_inventBatchId,inventTable.ItemId,inventTable.SPLInventRawMaterialId);
inventSerial = this.inventSerial(inventBatch);
f1_23_expDate = this.ExpDate(inventBatch,inventSerial);
}
f1_24_qty = this.qty();
f1_25_ProdId = this.prodId();
f1_26_CustAccount = this.custAccount();
f1_27_CustName = this.custName(f1_26_CustAccount);
f1_28_WMSRouteId = this.WMSRouteId();
f1_29_WMSLocationId = this.WMSLocationId();
f1_30_PickedQty = this.PickedQty();
f1_32_Test = this.Test();
// Special Case of Bar Code.
//if (parmRecord.TableId == tablenum(inventItemBarCode))
// f1_2_ItemBarCode = this.inventItemBarCodeBC();
contFields = [f1_1_StickerId,f1_2_ItemBarCode,f1_3_InventSizeId,f1_4_InventSerialId,f1_5_LPLInventBrandId,
f1_6_SPLCoDia,f1_7_SPLInventBC,f1_8_SPLInventBC2,f1_9_SPLSphere,f1_10_SPLCly,
f1_11_SPLAxis,f1_12_SPLAdd,f1_13_SPLAdd2,f1_14_SPLMaterial,f1_15_TexDefault,
f1_16_SPLInventWaterPercent,f1_17_SPLInventRawMaterialType,f1_18_SPLInventIndex,f1_19_SalesId,
f1_20_PurchId,f1_21_SalesLineNum,f1_22_inventBatchId,f1_23_expDate,f1_24_qty,f1_25_ProdId,
f1_26_CustAccount,f1_27_CustName,f1_28_WMSRouteId,f1_29_WMSLocationId,f1_30_PickedQty,
f1_31_SPLColor,f1_32_Test,
fl_33_Skirt, fl_34_Edge];
this.write(contFields);
}
}
//file structure 2 : Customers
else
{
f2_1_CustAccount = "";f2_2_CustName = "";f2_3_Name = "";f2_4_type = "";
f2_5_Street = "";f2_6_LPLSettlementId = "";f2_7_ZipCode = "";f2_8_City = "";
f2_9_County = "";f2_10_State = "";f2_11_CountryRegionId = "";
address.clear();
i++;
SPLInventBarTender = SPLInventBarTender::FindSticker(ModuleCustVendEvent,
BarTenderEvent,
'',//InventTable.ItemId,
'',//InventTable.LPLInventBrandId,
this.accountNum());
if (i == 1)
this.openFile(SPLInventBarTender.SPLTypeStickerId);
f2_1_CustAccount = this.custAccount();
f2_2_CustName = this.custName(f2_1_CustAccount);
address = this.Address();
if (address)
{
f2_3_Name = address.Name;
f2_4_type = enum2str(address.type);
f2_5_Street = address.Street;
f2_6_LPLSettlementId = address.LPLSettlementId;
f2_7_ZipCode = address.ZipCode;
f2_8_City = address.City;
f2_9_County = address.County;
f2_10_State = address.State;
f2_11_CountryRegionId = address.CountryRegionId;
}
contFields = [f2_1_CustAccount,f2_2_CustName,f2_3_Name,f2_4_type,
f2_5_Street,f2_6_LPLSettlementId,f2_7_ZipCode,f2_8_City,
f2_9_County,f2_10_State,f2_11_CountryRegionId];
this.write(contFields);
}
mi.next();
}
if (i)
{
this.closeFile();
info(strfmt("@SPL1061",int2str(i)));
}
}
server static SPLBarTender construct(common _common,SPLBarTenderSpecial _splBarTenderSpecial)
{
switch(_common.TableId)
{
case tablenum(ProdParmReportFinished) :
if (_SPLBarTenderSpecial == SPLBarTenderSpecial::RAFSimple)
return new SPLBarTender_ReportFinished_Simple(_common); //RAF (Simple)
else
return new SPLBarTender_ReportFinished(_common); //RAF
case tablenum(BOMParmReportFinish) : return new SPLBarTender_BOMReportFinish(_common); //PACK
case tablenum(InventJournalTrans) :
if (_SPLBarTenderSpecial == SPLBarTenderSpecial::Trans)
return new SPLBarTender_InventJournalTrans_Trans(_common); //RCV
else
return new SPLBarTender_InventJournalTrans(_common, _splBarTenderSpecial); //ADJ, RPK, PACK
case tablenum(WMSOrderTrans) :
switch (_SPLBarTenderSpecial)
{
case SPLBarTenderSpecial::SHIPSum :
return new SPLBarTender_WMSOrderTrans_sum(_common); //SHIP (SUM)
case SPLBarTenderSpecial::SHIPProd :
return new SPLBarTender_WMSOrderTrans_Prod(_common); //RAF
// 15/10/2012-ADMIN-SPL-INV-041-VIRTUALLOCATION-BEGIN
case SPLBarTenderSpecial::MOVELOC :
return new SPLBarTender_WMSOrderTrans_MoveLoc(_common); // MOVE TO LOCATION
// 15/10/2012-ADMIN-SPL-INV-041-VIRTUALLOCATION-END
default :
return new SPLBarTender_WMSOrderTrans(_common); //SHIP
}
case tablenum(InventTable) : return new SPLBarTender_InventTable(_common); //BAR
case tablenum(InventItemBarCode) : return new SPLBarTender_InventItemBarCode(_common); //BAR
case tablenum(PurchParmTable) : return new SPLBarTender_PurchParmTable(_common); //RCV
case tablenum(PurchParmLine) : return new SPLBarTender_PurchParmTable(_common, true); //RCV (true for line)
case tablenum(VendPackingSlipTrans) : return new SPLBarTender_VendPackingSlipTrans(_common); //RCV
case tablenum(WMSJournalTrans) : return new SPLBarTender_WMSJournalTrans(_common); //RCV
case tablenum(CustTable) : return new SPLBarTender_CustTable(_common); //ADD
default : throw error(strfmt("@SYS19306",funcname()));
}
throw error(strfmt("@SYS19306",funcname()));
}
static void main(Args args)
{
Common record;
Object callerForm;
str callerFormName;
FormDataSource recordDS;
SPLBarTender splBarTender;
SPLBarTenderSpecial splBarTenderSpecial;
WMSLocationId localWMSLocationId;
;
if (! args)
throw error("@SYS25407");
record = args.record();
splBarTenderSpecial = args.parmEnum();
if (SPLBarTenderSpecial == SPLBarTenderSpecial::SHIPSum)
localWMSLocationId = args.parm();
callerForm = args.caller();
if (callerForm)
callerFormName = args.caller().name();
if (record)
recordDS = args.record().dataSource();
splBarTender = SPLBarTender::construct(record,SPLBarTenderSpecial);
splBarTender.parmSPLBarTenderSpecial(SPLBarTenderSpecial);
splBarTender.parmRecord(record);
splBarTender.parmRecordDataSource(recordDS);
splBarTender.parmCallerFormName(callerFormName);
splBarTender.parmWMSLocationId(localWMSLocationId);
// 21/06/2012-SSN-Print Sticker-BEGIN
splBarTender.parmAutoRun(args.parm());
// 21/06/2012-SSN-Print Sticker-BEGIN
splBarTender.run();
}
Example of read file with check before
//================================================//
//In This example we are doing 2 loop on the file.
//The first loop is for checking the file.
//The second loop is for update the Database
//================================================//
void insertSPLPsGasNominationDaily()
{
timeOfDay now;
boolean OK = true;
date dateDayDate,dateMonthDate,dateWeekDate;
SPLPsDate dateDay,dateMonth,dateWeek;
SPLVersion lastVersion;
SPLPsGasNominationDaily splPsGasNominationDaily;
int counterLine,counterLineInserted,counterLineInsertedInfo;
container fileRecord;
utcDateTime FromDateTimeUtc,ToDateTimeUtc;
#define.PosDate(1)
#define.PosOrderedQty(2)
#define.PosApprovedQty(3)
#define.PosReason(4)
void checkLine()
{
FromDateTimeUtc = utcDateTimeNull();
ToDateTimeUtc = utcDateTimeNull();
splPsGasNominationDaily.clear();
splPsGasNominationDaily.DateDayDate = dateNull();
splPsGasNominationDaily.OrderedQty = 0;
splPsGasNominationDaily.approvedQty = 0;
splPsGasNominationDaily.ReasonNotApproved = "";
if (Ok)
{
if (conpeek(fileRecord, #PosDate) > "")
{
OK = this.string2DateTime(conpeek(fileRecord, #PosDate));
if (OK)
{
splPsGasNominationDaily.DateTimeUtc = string2DateTimeUtc;
}
}
else
{
OK = false;
}
if (!Ok)
{
info(strfmt("@SPL874",int2str(counterLine),conpeek(fileRecord, #PosDate),
fieldpname(splPsGasNominationDaily,DateTimeUtc)));
}
}
if (Ok)
{
OK = this.string2Numeric(conpeek(fileRecord, #PosOrderedQty));
if (OK)
splPsGasNominationDaily.OrderedQty = string2Value;
else
{
info(strfmt("@SPL874",int2str(counterLine),conpeek(fileRecord, #PosOrderedQty),
fieldpname(splPsGasNominationDaily,OrderedQty)));
}
}
if (Ok)
{
OK = this.string2Numeric(conpeek(fileRecord, #PosApprovedQty));
if (OK)
splPsGasNominationDaily.ApprovedQty = string2Value;
else
{
info(strfmt("@SPL874",int2str(counterLine),conpeek(fileRecord, #PosApprovedQty),
fieldpname(splPsGasNominationDaily,ApprovedQty)));
}
}
if (Ok)
{
splPsGasNominationDaily.ReasonNotApproved = conpeek(fileRecord, #PosReason);
}
}
//dateStr=strrem(date2str(systemdateget(), 321, 2, -1, 2, -1, 4),'/');
//now = timenow();
//timestr = strrem(time2str(now,1,1),':');
counterLine = 0;
fileIn = this.openFile(fileNameOpen,'r');
if (fileIn)
{
fileRecord = fileIn.read();
while (fileRecord && fileIn.status() == IO_Status::Ok)
{
counterLine++;
if (counterLine == 1)
{
fileRecord = fileIn.read();
continue; //the first line is not read.
}
checkLine();
if (!Ok)
break;
fileRecord = fileIn.read();
}
}
this.closeFile();
if (!Ok)
return;
if (counterLine !=25)
{
info("@SPL1201");
return;
}
dateDayDate = systemDateGet();
dateDay = FcsDateTimeAPI::convertFromDateLocal(dateDayDate);
dateMonthDate = dateStartMth(dateDayDate);
dateMonth = FcsDateTimeAPI::convertFromDateLocal(dateMonthDate);
dateWeekDate = Global::dateStartWk(dateDayDate);
dateWeek = FcsDateTimeAPI::convertFromDateLocal(dateWeekDate);
lastVersion = SPLPsGasNominationDaily::findLastVersion(dateDay).Version;
ttsbegin;
counterLine = 0;
counterLineInserted = 0;
counterLineInsertedInfo = 0;
splPsGasNominationDaily.selectForUpdate(true);
fileIn = this.openFile(fileNameOpen,'r');
if (fileIn)
{
fileRecord = fileIn.read();
while (fileRecord && fileIn.status() == IO_Status::Ok)
{
counterLine++;
if (counterLine == 1)
{
fileRecord = fileIn.read();
continue; //the first line is not read.
}
checkLine();
if (Ok)
{
SPLPsGasNominationDaily.DateDayDate = dateDayDate;
SPLPsGasNominationDaily.DateDay = dateDay;
SPLPsGasNominationDaily.DateMonthDate = dateDayDate;
SPLPsGasNominationDaily.DateMonth = dateDay;
SPLPsGasNominationDaily.DateWeekDate = dateDayDate;
SPLPsGasNominationDaily.DateWeek = dateDay;
SPLPsGasNominationDaily.Version = lastVersion + 1;
SPLPsGasNominationDaily.DateTime = FcsDateTimeAPI::UtcDateTime2FCSDateTime(SPLPsGasNominationDaily.DateTimeUTC);
splPsGasNominationDaily.insert();
counterLineInserted++;
}
else
// If not OK we stop the program
break;
fileRecord = fileIn.read();
} //while on read file
if (Ok)
counterLineInsertedInfo = counterLineInserted;
else
counterLineInsertedInfo = 0;
info(strfmt("@SPL1204",int2str(counterLineInsertedInfo)));
this.closeFile();
}
if (!OK)
{
if (counterLineInserted)
throw Error("@SPL892");
else
info("@SPL892");
}
ttscommit;
//this.moveFiles(fileNameOpen,MaintenanceDirArch);
}
Example of read XLS file
class SPLPsImportGasNomination extends RunBase
{
SysExcelApplication application;
LineNum lineNum;
}
public void run()
{
#OCCRetryCount
if (! this.validate())
throw error("");
try
{
if (this.checkDir(fileNameOpen))
{
this.readfileXLS();
//this.insertSPLPsGasControlHour();
}
}
catch (Exception::Deadlock)
{
retry;
}
catch (Exception::UpdateConflict)
{
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::UpdateConflictNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::UpdateConflict;
}
}
}
void readfileXLS()
{
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
;
try
{
application = SysExcelApplication::construct();
workbooks = application.workbooks();
}
catch (Exception::Internal)
{
if (application == null)
{
throw error("@SYS98748");
}
}
//specify the file path that you want to read
try
{
workbooks.open(fileNameOpen);
}
catch (Exception::Error)
{
throw error(strFmt("@SYS85977", fileNameOpen));
this.finalize();
}
try
{
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(2); //the second sheet of the file
cells = worksheet.cells();
lineNum = 7; //we don't want the 6 lines of title.
ttsBegin;
do
{
lineNum++;
if (lineNum > 31)
break;
SPLPsGasControlHour.clear();
this.fillLinefromCells(cells);
SPLPsGasControlHour.insert();
//recordTotal++;
type = cells.item(lineNum+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
ttscommit;
application.quit();
}
catch (Exception::Error)
{
this.finalize();
}
}
void fillLinefromCells(SysExcelCells _cells)
{
str strtmp;
;
splPsGasControlHour.DateTimeUtc = str2datetime(this.convertVariant2Str(_cells.item(lineNum,#PosHourFrom).value()),123);
splPsGasControlHour.approvedQtyDP = this.amountFromStr(this.convertVariant2Str(_cells.item(lineNum,#PosApprovedQtyDP).value()));
splPsGasControlHour.ConsumedQtyDP = this.amountFromStr(this.convertVariant2Str(_cells.item(lineNum,#PosConsumedQtyDP).value()));
splPsGasControlHour.approvedQtyRP = this.amountFromStr(this.convertVariant2Str(_cells.item(lineNum,#PosApprovedQtyRP).value()));
splPsGasControlHour.ConsumedQtyRP = this.amountFromStr(this.convertVariant2Str(_cells.item(lineNum,#PosConsumedQtyRP).value()));
}
real amountFromStr(str _strAmount)
{
str strAmount = _strAmount;
;
strAmount = strReplace(strAmount, '"','');
strAmount = strReplace(strAmount, ',','');
return str2num(strAmount);
}
str convertVariant2Str(COMVariant _cv, boolean _FieldTargetString = false)
{
str retVal;
;
switch (true)
{
case (_cv.variantType() == COMVariantType::VT_BSTR) :
retVal = _cv.bStr();
break;
case (_cv.variantType() == COMVariantType::VT_R8) :
if (_FieldTargetString)
retVal = num2str(_cv.double(),-1,0,1,0);
else
retVal = num2str(_cv.double(),-1,2,1,2);
break;
case (_cv.variantType() == COMVariantType::VT_DATE) :
retval = date2str(_cv.date(), -1, -1, -1, -1, -1, 4, DateFlags::None); //Year as 4 digits
break;
case (_cv.variantType() == COMVariantType::VT_EMPTY):
retVal = '';
break;
default:
error("@SYS59922");
throw Exception::Error;
}
return retVal;
}
Example of read XLS file - example 2
// In this example, the dialog is interesting, because of the filters.
// There is also a refresh : formRun.task(#taskFormRefresh_F5);
// 17/08/2016-SBE_SPL_AR_014_Import_Excel
class SPLSalesImportXls extends RunBase
{
Dialog dialog;
DialogField dlgFileNameOpen;
FilenameOpen fileNameOpen;
TextIo fileIn;
SalesId salesIdCaller;
SysExcelApplication application;
Counter recordTotal;
LineNum lineNum;
Object objectForm;
#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
fileNameOpen
#endmacro
}
public Object dialog()
{
;
dialog = new DialogRunbase("@SPL3618", this);
dialog.addText(strfmt("@SPL3625",salesIdCaller));
dlgFileNameOpen = dialog.addFieldValue(typeid(FilenameOpen),FilenameOpen);
dialog.filenameLookupFilter(["Choose an excel file",'*.xls*']);
return dialog;
}
public boolean getFromDialog()
{
boolean ret;
ret = super();
fileNameOpen = dlgFileNameOpen.value();
if (!fileNameOpen)
{
ret = checkfailed("@SPL3619");
}
return ret;
}
public void run()
{
FormRun formRun;
#Task
#OCCRetryCount
if (! this.validate())
throw error("");
try
{
if (this.checkDir(fileNameOpen))
{
this.readfileXLS();
}
}
catch (Exception::Deadlock)
{
retry;
}
catch (Exception::UpdateConflict)
{
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::UpdateConflictNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::UpdateConflict;
}
}
formRun = objectForm;
formRun.task(#taskFormRefresh_F5);
}
static void main(Args args)
{
SPLSalesImportXls SPLSalesImportXls;
SalesTable salesTable;
;
SPLSalesImportXls = new SPLSalesImportXls();
if (!args || !args.record().TableId == tableNum(SalesTable))
{
throw error("@SYS22539");
}
salesTable = args.record();
if (salesTable.SalesStatus == SalesStatus::Canceled || salesTable.SalesStatus == SalesStatus::Invoiced)
{
info(strfmt("@SPL3624", enum2str(salesTable.SalesStatus)));
return;
}
SPLSalesImportXls.parmSalesId(salesTable.SalesId);
SPLSalesImportXls.setObject(args.caller());
if (SPLSalesImportXls.prompt())
SPLSalesImportXls.run();
}
public Object setObject(Object _object)
{
;
objectForm = _object;
return objectForm;
}
public boolean unpack(container packedClass)
{
Version version = RunBase::getVersion(packedClass);
;
switch (version)
{
case #CurrentVersion:
[version,#CurrentList] = packedClass;
break;
default:
return false;
}
return true;
}
// Here goes a description of the class
static ClassDescription description()
{
return "@SPL3618";
}
public boolean init()
{
boolean ret;
ret = super();
return ret;
}
public container pack()
{
return [#CurrentVersion,#CurrentList];
}
protected salesId parmSalesId(SalesId _salesId = salesIdCaller)
{
salesIdCaller = _salesId;
return salesIdCaller;
}
void readfileXLS()
{
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
SalesTable salesTable;
SalesLine salesLine;
int counterLineInserted, counterLineError;
str currTime;
Container contItemQty;
ItemId itemId;
Qty qty;
int i;
FormRun formRun;
#Task
#define.posItem(1)
#define.posQty(2)
;
try
{
application = SysExcelApplication::construct();
workbooks = application.workbooks();
}
catch (Exception::Internal)
{
if (application == null)
{
throw error("@SYS98748");
}
}
//specify the file path that you want to read
try
{
workbooks.open(fileNameOpen);
}
catch (Exception::Error)
{
this.finalize();
throw error(strFmt("@SYS85977", fileNameOpen));
}
try
{
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1); //first sheet.
cells = worksheet.cells();
lineNum = 0;
do
{
itemId = "";
qty = 0;
lineNum++;
itemId = this.convertVariant2Str(cells.item(lineNum,#posItem).value());
qty = this.amountFromStr(this.convertVariant2Str(cells.item(lineNum,#posQty).value()));
contItemQty += [[itemId,qty]];
type = cells.item(lineNum+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
Application.quit();
sleep(1000);
}
catch (Exception::Error)
{
this.finalize();
}
salesTable = SalesTable::find(salesIdCaller);
if (!salesTable)
return;
ttsbegin;
for (i = 1; i <= conLen(contItemQty); i++)
{
salesLine.clear();
[itemId, qty] = conpeek(contItemQty,i);
if (!inventTable::exist(itemId))
{
info(strfmt("@SPL3623",itemId,i));
counterLineError++;
continue;
}
salesLine.SalesId = salesTable.SalesId;
salesLine.initFromSalesTable(salesTable);
salesLine.ItemId = itemId;
salesLine.itemIdChanged();
salesLine.SalesQty = qty;
salesLine.modifiedField(fieldnum(SalesLine, SalesQty));
InventMovement::bufferSetRemainQty(salesLine);
salesLine.createLine(true, false, false,true,true,true,false,false,"",true);
counterLineInserted++;
}
info(strfmt("@SPL3621",int2str(counterLineInserted),salesIdCaller));
if (counterLineError)
info(strfmt("@SPL3622",int2str(counterLineError),salesIdCaller));
ttscommit;
}
public void finalize()
{
;
if (application)
{
application.quit();
}
}
real amountFromStr(str _strAmount)
{
str strAmount = _strAmount;
;
strAmount = strReplace(strAmount, '"','');
strAmount = strReplace(strAmount, ',','');
return str2num(strAmount);
}
boolean checkDir(FilePath _fileName)
{
FileIOPermission fileIOPerm;
boolean pathExists;
Filename filenameOnly,type,path;
Filename ArchDir;
[path, filenameOnly, type] = fileNameSplit(_fileName);
//test the directory of the file and the archive directory.
if (isRunningOnServer())
{
fileIOPerm = new FileIOPermission(path,'R');
fileIOPerm.assert();
pathExists = WinAPIServer::pathExists(path);
CodeAccessPermission::revertAssert();
}
else
{
pathExists = WinAPI::pathExists(path);
}
if (!pathExists)
{
info(strfmt("@SPL3620",path));
return false;
}
return true;
}
str convertVariant2Str(COMVariant _cv, boolean _FieldTargetString = false, boolean _dateTime = false)
{
str retVal;
;
switch (true)
{
case (_cv.variantType() == COMVariantType::VT_BSTR) :
retVal = _cv.bStr();
break;
case (_cv.variantType() == COMVariantType::VT_R8) :
if (_FieldTargetString)
retVal = num2str(_cv.double(),-1,0,1,0);
else
retVal = num2str(_cv.double(),-1,4,1,2);
break;
case (_cv.variantType() == COMVariantType::VT_DATE) :
if (_dateTime)
{
retval = date2str(_cv.date(), -1, -1, -1, -1, -1, 4, DateFlags::None) +
" " + time2str(_cv.time(),1,1);
}
else
retval = date2str(_cv.date(), -1, -1, -1, -1, -1, 4, DateFlags::None);
break;
case (_cv.variantType() == COMVariantType::VT_EMPTY):
retVal = '';
break;
default:
error(strFmt("@SPL1408",int2str(lineNum)));
throw Exception::Error;
}
return retVal;
}
Clean origin in XPO file
static void SPLPsm_ImportExport_Stream(Args _args)
{
TextIo fileIN,fileOUT;
FileIOPermission permission;
FilePath fileNameIN;
FilePath fileNameOUT;
int i;
container fileRecord;
int counterLine,posLast,Recordlen;
str fileRecord_1;
System.IO.StreamReader reader;
System.IO.StreamReader sr;
System.IO.StreamWriter sw;
System.String line;
InteropPermission interopPermission;
Dialog dialog = new Dialog ("Choose file");
DialogField dlgFileIN;
Filename filenameOnly,type,path;
interopPermission = new InteropPermission(InteropKind::ClrInterop);
interopPermission.assert();
dlgFileIN = dialog.addFieldValue(extendedTypeStr(FilenameOpen),fileNameIN);
dialog.filenameLookupFilter(["XPO Files",'*.xpo']);
if (dialog.run())
{
fileNameIN = dlgFileIN.value();
[path, filenameOnly, type] = fileNameSplit(fileNameIN);
fileNameOUT = path + '\\' + filenameOnly + '_2' + type;
reader = new System.IO.StreamReader(fileNameIN,System.Text.Encoding::get_UTF8());
line = reader.ReadLine();
sw = new System.IO.StreamWriter(fileNameOUT, false, System.Text.Encoding::get_UTF8());
while (line != null)
{
if (line.Contains("Origin #"))
{
i++;
}
else
{
sw.WriteLine(line);
}
line = reader.ReadLine();
}
reader.Close();
reader.Dispose();
sw.Flush();
sw.Close();
sw.Dispose();
info("number line cleaned " + int2str(i));
}
}
Some function for check
//Some fuction for check
static real amountFromStr(str _strAmount)
{
str strAmount = _strAmount;
strAmount = strReplace(strAmount, '"','');
strAmount = strReplace(strAmount, ',','');
return str2num(strAmount);
}
static container string2Int(str 20 _str)
{
boolean ret;
int string2Int;
;
ret = true;
string2Int = 0;
if (_str)
{
if (str2intOK(_str))
{
string2Int = str2Int(_str);
}
else
ret = false;
}
return [ret,string2Int];
}
static container string2Numeric(str 20 _str)
{
boolean ret;
real string2Value;
;
ret = true;
string2Value = 0;
if (_str)
{
if (str2numOK(_str))
{
string2Value = str2Num(_str);
}
else
ret = false;
}
return [ret,string2Value];
}
static container string2UtcDateTime(str _str)
{
boolean ret;
str 10 dateStr;
str 8 timeStr;
int iPos;
utcDateTime string2DateTimeUtc;
ret = true;
string2DateTimeUtc = utcDateTimeNull();
if (_str)
{
iPos = strFind(_str,"/",1,strLen(_str));
if (ipos > 2)
dateStr = subStr(_str,ipos - 2,10);
else
return [false,string2DateTimeUtc];
iPos = strFind(_str,":",1,strLen(_str));
if (ipos > 2)
timeStr = subStr(_str,ipos - 2,5) + ":00";
else
return [false,string2DateTimeUtc];
if (str2DateDMY(dateStr))
{
string2DateTimeUtc = DateTimeUtil::newDateTime(str2DateDMY(dateStr),str2time(timeStr));
}
else
return [false,string2DateTimeUtc];
}
return [ret,string2DateTimeUtc];
}
Tutorial and filepathLookupTitle : Lookup to choose a file
// There is a tutorial on File : tutorial_Form_File
// without this method, the lokup doesn't work
// the path directory has the EDT FileName
str filepathLookupTitle()
{
return "Choose";
}
Send to web service
//Test if a string is null :
if (System.String::IsNullOrEmpty(ret))
return false;
//Send a file to a Web service, URL = adresse of the web service
boolean submitXml(str url,str file)
{
System.Net.WebRequest webRequest;
System.Net.HttpWebResponse webResponse;
System.IO.Stream stream;
System.IO.StreamWriter streamWriter = null;
System.IO.StreamReader streamReader = null;
System.IO.StreamReader reader = null;
str result;
str strPost;
System.String ret;
System.IO.StreamReader sr;
System.Exception ex;
InteropPermission perm = new InteropPermission(InteropKind::ClrInterop);
;
perm.assert();
try
{
WebRequest = System.Net.WebRequest::Create(url);
WebRequest.set_Method('POST');
// WebRequest.set_ContentLength(strlen(strPost));
WebRequest.set_ContentType('text/xml');
streamWriter = new System.IO.StreamWriter(WebRequest.GetRequestStream());
// Write the XML text into the stream
reader = new System.IO.StreamReader(file);
ret = reader.ReadToEnd();
reader.Close();
if (System.String::IsNullOrEmpty(ret))
return false;
streamWriter.WriteLine(ret);
streamWriter.Close();
// Send the data to the webserver
webResponse = WebRequest.GetResponse();
sr = new System.IO.StreamReader(webResponse.GetResponseStream());
result = sr.ReadToEnd();
sr.Close();
if( strFind(result,"DONE",1,(strlen(result) - 1)) > 1)
{
return true;
}
else
{
return false;
}
}
catch (Exception::CLRError)
{
ex = ClrInterop::getLastException();
if (ex != null)
{
ex = ex.get_InnerException();
if (ex != null)
{
error(ex.ToString());
}
}
}
}
Example read from dir and write 2 files
// read a directory and write 2 files:
// Attention read = inFieldDelimiter !!!!
SPLSalesImportSplitFile
classDeclaration
caption
checkDir
CloseFileIn
CloseFileOut
CodePage
Date2MMSlashDDSlashYYYY
file
moveFilesIn
openFileIn
openFileOut
parmIline
ReadFileInDir
run
writeHeader
writeIntoFiles
writeLine
SPLSalesImportSplitFile
main
// 06/14/2016-SBE
class SPLSalesImportSplitFile extends RunBaseBatch
{
TextIo fileIn, fileArch, fileOutHeader, fileOutLine;
Filename fileNameOpenIn, fileNameOpenArch;
FilenameOpen dirPathOTR, dirPathOTRArch, fileNameOpenOutHeader, fileNameOpenOutLine;
filename fileNameOnlyIn,typeIn,pathIn;
filename fileNameOnlyHeader,typeHeader,pathHeader;
filename fileNameOnlyLine,typeLine,pathLine;
int iLine;
#File
#define.fieldDelimiter(#delimiterTab)
#define.recordDelimiter("\r\n")
#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
#endmacro
}
public ClassDescription caption()
{
ClassDescription ret;
ret = super();
ret = "@SPL226";
return ret;
}
boolean checkDir()
{
CustParameters custParameters;
DMFDefinitionGroup DMFDefinitionGroup;
DMFDefinitionGroupEntity DMFDefinitionGroupEntity;
FileIOPermission fileIOPerm;
boolean pathExists;
#SPLGlobalParam
;
custParameters = CustParameters::find();
// test the directory for import file
dirPathOTR = custParameters.SPLNUSAOTRDir;
if (!dirPathOTR)
{
info(strFmt("@SPL219",fieldPName(custParameters,SPLNUSAOTRDir)));
return false;
}
if (isRunningOnServer())
{
fileIOPerm = new FileIOPermission(dirPathOTR,'R');
fileIOPerm.assert();
pathExists = WinAPIServer::pathExists(dirPathOTR);
CodeAccessPermission::revertAssert();
}
else
{
pathExists = WinAPI::pathExists(dirPathOTR);
}
if (!pathExists)
{
info(strfmt("@SPL225",dirPathOTR,fieldPName(custParameters,SPLNUSAOTRDir)));
return false;
}
// test the directory for archive file
dirPathOTRArch = custParameters.SPLNUSAOTRDirArch;
if (!dirPathOTRArch)
{
info(strFmt("@SPL219",fieldPName(custParameters,SPLNUSAOTRDirArch)));
return false;
}
if (isRunningOnServer())
{
fileIOPerm = new FileIOPermission(dirPathOTRArch,'R');
fileIOPerm.assert();
pathExists = WinAPIServer::pathExists(dirPathOTRArch);
CodeAccessPermission::revertAssert();
}
else
{
pathExists = WinAPI::pathExists(dirPathOTRArch);
}
if (!pathExists)
{
info(strfmt("@SPL225",dirPathOTRArch,fieldPName(custParameters,SPLNUSAOTRDirArch)));
return false;
}
// test the directory for sales header
if (!custParameters.SPLDefGroupNameHeader)
{
info(strFmt("@SPL219",fieldPName(custParameters,SPLDefGroupNameHeader)));
return false;
}
DMFDefinitionGroup = DMFDefinitionGroup::find(custParameters.SPLDefGroupNameHeader);
if (!DMFDefinitionGroup)
{
info(strFmt("@SPL220",custParameters.SPLDefGroupNameHeader));
return false;
}
DMFDefinitionGroupEntity = DMFDefinitionGroupEntity::find(DMFDefinitionGroup.DefinationGroupName,#DMFEntitySalesHeader);
if (!DMFDefinitionGroupEntity)
{
info(strFmt("@SPL221",#DMFEntitySalesHeader,DMFDefinitionGroup.DefinationGroupName));
return false;
}
fileNameOpenOutHeader = DMFDefinitionGroupEntity.SampleFilePath;
if (!fileNameOpenOutHeader)
{
info(strFmt("@SPL222",fieldPName(DMFDefinitionGroupEntity,SampleFilePath),#DMFEntitySalesHeader,DMFDefinitionGroup.DefinationGroupName));
return false;
}
[pathHeader, fileNameOnlyHeader, typeHeader] = fileNameSplit(fileNameOpenOutHeader);
if (typeHeader != ".txt")
{
info(strfmt("@SPL223",fileNameOpenOutHeader,#DMFEntitySalesHeader,DMFDefinitionGroup.DefinationGroupName));
return false;
}
if (isRunningOnServer())
{
fileIOPerm = new FileIOPermission(pathHeader,'R');
fileIOPerm.assert();
pathExists = WinAPIServer::pathExists(pathHeader);
CodeAccessPermission::revertAssert();
}
else
{
pathExists = WinAPI::pathExists(pathHeader);
}
if (!pathExists)
{
info(strfmt("@SPL224",pathHeader,DMFDefinitionGroup.DefinationGroupName));
return false;
}
// test the directory for sales line
if (!custParameters.SPLDefGroupNameLine)
{
info(strFmt("@SPL219",fieldPName(custParameters,SPLDefGroupNameLine)));
return false;
}
DMFDefinitionGroup = DMFDefinitionGroup::find(custParameters.SPLDefGroupNameLine);
if (!DMFDefinitionGroup)
{
info(strFmt("@SPL220",custParameters.SPLDefGroupNameLine));
return false;
}
DMFDefinitionGroupEntity = DMFDefinitionGroupEntity::find(DMFDefinitionGroup.DefinationGroupName,#DMFEntitySalesLine);
if (!DMFDefinitionGroupEntity)
{
info(strFmt("@SPL221",#DMFEntitySalesLine,DMFDefinitionGroup.DefinationGroupName));
return false;
}
fileNameOpenOutLine = DMFDefinitionGroupEntity.SampleFilePath;
if (!fileNameOpenOutLine)
{
info(strFmt("@SPL222",fieldPName(DMFDefinitionGroupEntity,SampleFilePath),#DMFEntitySalesLine,DMFDefinitionGroup.DefinationGroupName));
return false;
}
[pathLine, fileNameOnlyLine, typeLine] = fileNameSplit(fileNameOpenOutLine);
if (typeLine != ".txt")
{
info(strfmt("@SPL223",fileNameOpenOutLine,#DMFEntitySalesLine,DMFDefinitionGroup.DefinationGroupName));
return false;
}
if (isRunningOnServer())
{
fileIOPerm = new FileIOPermission(pathLine,'R');
fileIOPerm.assert();
pathExists = WinAPIServer::pathExists(pathLine);
CodeAccessPermission::revertAssert();
}
else
{
pathExists = WinAPI::pathExists(pathLine);
}
if (!pathExists)
{
info(strfmt("@SPL224",pathLine,DMFDefinitionGroup.DefinationGroupName));
return false;
}
return true;
}
public void CloseFileIn()
{
fileIn = null;
}
public void CloseFileOut()
{
fileOutHeader = null;
fileOutLine = null;
}
private int CodePage()
{
#localcodepage
;
//return #cp_437;
return #cp_1252;
}
str Date2MMSlashDDSlashYYYY(str _YYYYMMDD)
{
str dateMMSlashDDSlashYYYY;
if (_YYYYMMDD)
{
dateMMSlashDDSlashYYYY = subStr(_YYYYMMDD,5,2) + "/" + subStr(_YYYYMMDD,7,2) + "/" + subStr(_YYYYMMDD,1,4);
}
//date2str(str2Date(_YYYYMMDD,321),213,
//date2str(prodJournalProd.TransDate,213,1,-1,1,-1,4)];
return dateMMSlashDDSlashYYYY;
}
private TextIo file()
{
return fileIn;
}
boolean moveFilesIn()
{
Filename fileToCopy;
FilePath sourcesDestDir;
System.IO.FileInfo fileInfo;
str fileExtension;
FilePath fileDestToCopy;
InteropPermission interopPerm;
FileIOPermission fileIOPerm;
FileIOPermission sourceFileWritePerm;
FileIOPermission processedPathReadPerm;
Set permissionSet;
boolean bServer;
str 10 dateStr;
str 10 timestr;
timeOfDay now;
Filename filenameOnly,type,path;
;
#define.FilePathDelimiter('\\')
#define.read('R')
#define.write('W')
dateStr=strrem(date2str(systemdateget(), 321, 2, -1, 2, -1, 4),'/');
now = timenow();
timestr = strrem(time2str(now,1,1),':');
fileToCopy = dirPathOTR + '\\' + fileNameOnlyIn + typeIn;
fileDestToCopy = dirPathOTRArch + '\\' + fileNameOnlyIn + '_' + dateStr + '_' + timestr + typeIn;
if (isRunningOnServer())
bServer = true;
else
bServer = false;
if (bServer)
{
// Revert permissions
CodeAccessPermission::revertAssert();
// Assert permission
permissionSet = new Set(Types::Class);
permissionSet.add(new FileIOPermission(fileToCopy, #read));
permissionSet.add(new FileIOPermission(fileDestToCopy, #read));//dest
permissionSet.add(new FileIOPermission(fileDestToCopy, #write));//dest
CodeAccessPermission::assertMultiple(permissionSet);
// Move to processed folder
if (WinAPIServer::fileExists(fileDestToCopy))
{
info(strfmt("@GIL903",fileToCopy,dirPathOTRArch));
}
else
{
WinAPIServer::copyFile(fileToCopy, fileDestToCopy, true);
// Revert permissions
CodeAccessPermission::revertAssert();
sourceFileWritePerm = new FileIOPermission(fileToCopy, #write);
// Assert permission
sourceFileWritePerm.assert();
WinAPIServer::deleteFile(fileToCopy);
}
// Revert permissions
CodeAccessPermission::revertAssert();
}
else
{
if (WinAPI::fileExistsClient(fileToCopy))
{
if (WinAPI::fileExistsClient(fileDestToCopy))
{
info(strfmt("@GIL903",fileToCopy,dirPathOTRArch));
}
else
WinAPI::moveFile(fileToCopy,fileDestToCopy);
}
}
return true;
}
public void openFileIn(filename _fileNameFull, str _mode)
{
TextIo file;
FileIOPermission permission;
str60 dateDayYYYYMMDD;
str 10 timeHHMMSS;
;
//dateDayYYYYMMDD = strRem(date2str(systemDateGet(),321,2,-1,2,-1,4),'/');
//timeHHMMSS = strRem(time2Str(timeNow(),TimeSeparator::Colon,1),":");
[pathIn, fileNameOnlyIn, typeIn] = fileNameSplit(_fileNameFull);
CodeAccessPermission::revertAssert();
permission = new FileIOPermission(_fileNameFull,_mode);
permission.assert();
fileIn = new TextIo(_fileNameFull, _mode, this.CodePage());
if (!fileIn)
{
throw error(strFmt("@SYS76826",_fileNameFull));
}
fileIn.inFieldDelimiter(#fieldDelimiter);
fileIn.inRecordDelimiter(#recordDelimiter);
}
public void openFileOut(filename _fileNameFullHeader, filename _fileNameFullLine, str _mode)
{
TextIo file;
FileIOPermission permission;
str60 dateDayYYYYMMDD;
str 10 timeHHMMSS;
;
//dateDayYYYYMMDD = strRem(date2str(systemDateGet(),321,2,-1,2,-1,4),'/');
//timeHHMMSS = strRem(time2Str(timeNow(),TimeSeparator::Colon,1),":");
//fileNameFull = dirPath + '\\' + #fileExportName + "_" + dateDayYYYYMMDD + timeHHMMSS + ".csv";
CodeAccessPermission::revertAssert();
permission = new FileIOPermission(_fileNameFullHeader,_mode);
permission.assert();
fileOutHeader = new TextIo(_fileNameFullHeader, _mode, this.CodePage());
if (!fileOutHeader)
{
throw error(strFmt("@SYS76826",_fileNameFullHeader));
}
fileOutHeader.outFieldDelimiter(#fieldDelimiter);
fileOutHeader.outRecordDelimiter(#recordDelimiter);
CodeAccessPermission::revertAssert();
permission = new FileIOPermission(_fileNameFullLine,_mode);
permission.assert();
fileOutLine = new TextIo(_fileNameFullLine, _mode, this.CodePage());
if (!fileOutLine)
{
throw error(strFmt("@SYS76826",_fileNameFullLine));
}
fileOutLine.outFieldDelimiter(#fieldDelimiter);
fileOutLine.outRecordDelimiter(#recordDelimiter);
}
public int parmIline(int _iLine = iLine)
{
return _iLine;
}
void ReadFileInDir()
{
CustAccount custAccountShip;
CustTable cusTableShip;
int iFiles;
int i;
int length;
System.Array files;
;
#SPLGlobalParam
if (!this.checkDir())
{
return;
}
custAccountShip = CustParameters::find().SPLCusNUSAShipping;
if (!custAccountShip)
{
info(strFmt("@SPL237",fieldPName(CustParameters,SPLCusNUSAShipping)));
return;
}
cusTableShip = CustTable::find(custAccountShip);
if (!cusTableShip)
{
info(strFmt("@SPL238",fieldPName(CustParameters,SPLCusNUSAShipping)));
return;
}
// get CLR interop permission // Also good for batch
// Read files sort by name
new InteropPermission(InteropKind::ClrInterop).assert();
files = System.IO.Directory::GetFiles(dirPathOTR, "*.txt");
if(files)
{
length = files.get_Length();
for(i=0;i Less Length;i++)
{
fileNameOpenIn = files.GetValue(i);
break;
}
}
CodeAccessPermission::revertAssert();
if (!fileNameOpenIn)
return;
iFiles = 0;
this.openFileIn(fileNameOpenIn, 'R');
if (fileIn)
{
iFiles++;
this.writeIntoFiles();
}
this.closeFileIn();
sleep(1000);
this.moveFilesIn();
}
public void run()
{
#OCCRetryCount
try
{
this.ReadFileInDir();
}
catch (Exception::Deadlock)
{
retry;
}
catch (Exception::UpdateConflict)
{
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::UpdateConflictNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::UpdateConflict;
}
}
catch
{
if (!fileIn)
this.CloseFileIn();
if (fileOutHeader || fileOutLine)
this.CloseFileOut();
throw error("Error on creating files");
}
}
public void writeHeader(container _fields)
{
fileOutHeader.writeExp(_fields);
}
void writeIntoFiles()
{
container fileRecord, contHeader, contLine;
Set salesIdSet;
CustVendExternalItem custVendExternalItem;
SalesId salesId;
str deliveryName, deliveryStreet, plannedDateStr, requestedDelDateStr;
LogisticsAddressCountryRegionId countryRegionId;
int iHeader;
boolean isNewSalesId;
CustAccount custAccountShip;
CustTable cusTableShip;
str 10 dateDayMMSlashDDSlashYYYY;
EanlogPartnerChannelTable eanlogPartnerChannelTable;
EanlogPartnerChannelHierarchies EanlogPartnerChannelHierarchies;
EanlogHierarchyId eanlogHierarchyId;
InventItemBarcode inventItemBarcode;
InventDim inventDim;
ItemBarCode itemBarCode;
BarcodeSetupId barcodeSetupId;
#SPLGlobalParam
//01 Order Type
//02 PO number //(01) sales (PurchOrderFormNum)
//03 Sales Doc. //(02) sales + line (SalesId) (01) line
//04 Delivery number //(03) sales (SPLDeliveryNum)
//05 Load //(04) sales (SPLLoadNum)
//06 Carrier name //(05) sales
//07 Sold to // sales (DeliveryName)
//08 Sold to name //(06) sales 08 + " " + 07 -> (DeliveryName)
//09 Ship to name
//10 House No. // sales (DeliveryStreet)
//11 Ship to street //(07) sales 10 + " " + 11 -> (DeliveryStreet)
//12 Ship to City //(08) sales (DeliveryCity)
//13 Ship to Postal code //(09) sales (DeliveryZipCode)
//14 Ship to Country //(10) sales (DeliverCountry)
//15 Ship to Region //(11) sales (DeliverCountryRegionId) -> State
//16 Ord Planned GI date //(12) sales (ShippingDateConfirmed)
//17 Requested delivery d //(13) sales (ShippingDateRequeste)
//18 Item // Line (LineNum) (02) line -> Line number
//19 Material // Line (ItemId) - Nesle external Item Id (03) line -> ItemId
//20 Description (04) line -> Name
//21 Order Quantity // Line (QtyOrdered)
//22 Order Confirmed Quan // Line (SalesQty) (05) line -> SalesQty
//23 Actual GI date
//24 Document date
//25 Credit status
//26 Credit block
//27 Order found on Excep
#define.PosHeader1('PurchOrderFormNum') // PO number
#define.PosHeader2('SalesId')
#define.PosHeader3('SPLDeliveryNum')
#define.PosHeader4('SPLLoadNum')
#define.PosHeader5('ShipCarrierName')
#define.PosHeader6('DeliveryName')
#define.PosHeader7('DeliveryStreet')
#define.PosHeader8('DeliveryCity')
#define.PosHeader9('DeliveryZipCode')
#define.PosHeader10('DeliveryCountryRegionId')
#define.PosHeader11('DeliveryState')
#define.PosHeader12('ShippingDateConfirmed') //Ord Planned GI date
#define.PosHeader13('ShippingDateRequested') //Requested delivery date
#define.PosHeader14('CustAccount')
#define.PosHeader15('InvoiceAccount')
#define.PosHeader16('CurrencyCode')
#define.PosHeader17('CustGroup')
#define.PosHeader18('LanguageId')
#define.PosHeader19('SalesType')
#define.PosHeader20('DefaultDimension')
#define.PosHeader21('DlvMode')
#define.PosHeader22('DlvTerm')
#define.PosHeader23('InventLocationId')
#define.PosHeader24('InventSiteId')
#define.PosHeader25('PostingProfile')
#define.PosHeader26('PriceGroupId')
#define.PosHeader27('SalesName')
#define.PosHeader28('SalesOriginId')
#define.PosHeader29('SalesPoolid')
#define.PosHeader30('TaxGroup')
#define.PosHeader31('EanlogCalcMethodId')
#define.PosHeader32('EanLogCalculateDate')
#define.PosHeader33('EanLogCloseRemainQty')
#define.PosHeader34('EanlogCustInvoicePAyby')
#define.PosHeader35('EanlogHierarchyId')
#define.PosHeader36('EanLogOrderDate')
#define.PosHeader37('EanlogPartnerChannelId')
#define.PosHeader38('EanlogPriceApplicationDate')
#define.PosHeader39('EanlogSalesOverDelivery')
#define.PosHeader40('EanlogSalesunderDelivery')
#define.PosLine1('SalesId')
#define.PosLine2('LineNum')
#define.PosLine3('ItemId')
#define.PosLine4('Name')
#define.PosLine5('SalesQty')
#define.PosLine6('CurrencyCode')
#define.PosLine7('CustAccount')
#define.PosLine8('CustGroup')
#define.PosLine9('ShippingDateRequested')
#define.PosLine10('CustomerRef')
#define.PosLine11('ExternalItemId')
#define.PosLine12('BarCode')
#define.PosLine13('BarCodeType')
#define.PosLine14('DlvMode')
#define.PosLine15('InventSiteId')
#define.PosLine16('InventLocationId')
#define.PosLine17('PriceUnit')
#define.PosLine18('SalesGroup')
#define.PosLine19('SalesType')
#define.PosLine20('TaxGroup')
#define.PosLine21('TaxItemGroup')
#define.PosPoNumber(2)
#define.PosSalesId(3)
#define.PosDeliveryNum(4)
#define.PosLoad(5)
#define.PosCarrierName(6)
#define.PosSoldTo(7)
#define.PosSoldToName(8)
#define.PosHouseNo(10)
#define.PosShipToStreet(11)
#define.PosShipToCity(12)
#define.PosShipToPostalCode(13)
#define.PosShipToCountry(14)
#define.PosShipToRegion(15)
#define.PosOrdPlannedDate(16)
#define.PosRequestedDelDate(17)
#define.PosItemLine(18)
#define.PosMaterial(19)
#define.PosDescription(20)
#define.PosOrderQty(21)
#define.PosOrderConfirmedQty(22)
salesIdSet = new Set(Types::String);
dateDayMMSlashDDSlashYYYY = date2str(systemDateGet(),321,2,-1,2,-1,4);
custAccountShip = CustParameters::find().SPLCusNUSAShipping;
cusTableShip = CustTable::find(custAccountShip);
fileRecord = fileIn.read();
while (fileRecord && fileIn.status() == IO_Status::Ok)
{
salesId = "";
deliveryName = "";
deliveryStreet = "";
countryRegionId = "";
plannedDateStr = "";
requestedDelDateStr = "";
isNewSalesId = false;
iLine++;
if (iLine == 1)
{
iHeader++;
this.openFileOut(fileNameOpenOutHeader,fileNameOpenOutLine,"W");
contHeader = conNull();
contLine = conNull();
contHeader +=[#PosHeader1]; contHeader +=[#PosHeader2]; contHeader +=[#PosHeader3]; contHeader +=[#PosHeader4]; contHeader +=[#PosHeader5];
contHeader +=[#PosHeader6]; contHeader +=[#PosHeader7]; contHeader +=[#PosHeader8]; contHeader +=[#PosHeader9]; contHeader +=[#PosHeader10];
contHeader +=[#PosHeader11];contHeader +=[#PosHeader12];contHeader +=[#PosHeader13];contHeader +=[#PosHeader14];contHeader +=[#PosHeader15];
contHeader +=[#PosHeader16];contHeader +=[#PosHeader17];contHeader +=[#PosHeader18];contHeader +=[#PosHeader19];contHeader +=[#PosHeader20];
contHeader +=[#PosHeader21];contHeader +=[#PosHeader22];contHeader +=[#PosHeader23];contHeader +=[#PosHeader24];contHeader +=[#PosHeader25];
contHeader +=[#PosHeader26];contHeader +=[#PosHeader27];contHeader +=[#PosHeader28];contHeader +=[#PosHeader29];contHeader +=[#PosHeader30];
contHeader +=[#PosHeader31];contHeader +=[#PosHeader32];contHeader +=[#PosHeader33];contHeader +=[#PosHeader34];contHeader +=[#PosHeader35];
contHeader +=[#PosHeader36];contHeader +=[#PosHeader37];contHeader +=[#PosHeader38];contHeader +=[#PosHeader39];contHeader +=[#PosHeader40];
this.writeHeader(contHeader);
contLine +=[#PosLine1]; contLine +=[#PosLine2]; contLine +=[#PosLine3]; contLine +=[#PosLine4]; contLine +=[#PosLine5];
contLine +=[#PosLine6]; contLine +=[#PosLine7]; contLine +=[#PosLine8]; contLine +=[#PosLine9]; contLine +=[#PosLine10];
contLine +=[#PosLine11];contLine +=[#PosLine12];contLine +=[#PosLine13];contLine +=[#PosLine14];contLine +=[#PosLine15];
contLine +=[#PosLine16];contLine +=[#PosLine17];contLine +=[#PosLine18];contLine +=[#PosLine19];contLine +=[#PosLine20];
contLine +=[#PosLine21];
this.writeLine(contLine);
//the first line of the read file is a line of comment.
fileRecord = fileIn.read();
continue;
}
contHeader = conNull();
contLine = conNull();
salesId = conPeek(fileRecord,#PosSalesId);
if (!salesIdSet.in(salesId))
{
iHeader++;
salesIdSet.add(salesId);
isNewSalesId = true;
}
if (isNewSalesId)
{
//(01-05)
contHeader += [conPeek(fileRecord,#PosPoNumber)];
contHeader += [salesId];
contHeader += [conPeek(fileRecord,#PosDeliveryNum)];
contHeader += [conPeek(fileRecord,#PosLoad)];
contHeader += [conPeek(fileRecord,#PosCarrierName)];
//(06) : DeliveryName
deliveryName = conPeek(fileRecord,#PosSoldToName);
if (deliveryName)
deliveryName += " ";
deliveryName += conPeek(fileRecord,#PosSoldTo);
contHeader += [deliveryName];
//(07) : DeliveryStreet
deliveryStreet = conPeek(fileRecord,#PosHouseNo);
if (deliveryStreet)
deliveryStreet += " ";
deliveryStreet += conPeek(fileRecord,#PosShipToStreet);
contHeader += [deliveryStreet];
//(08) : Ship to City
contHeader += [conPeek(fileRecord,#PosShipToCity)];
//(09) : Ship to Postal code
contHeader += [conPeek(fileRecord,#PosShipToPostalCode)];
//(10) : Ship to Country
countryRegionId = LogisticsAddressCountryRegion::findByISOCode(conPeek(fileRecord,#PosShipToCountry)).CountryRegionId;
if (!countryRegionId)
countryRegionId = conPeek(fileRecord,#PosShipToCountry);
contHeader += [countryRegionId];
//(11) : Ship to Region
contHeader += [conPeek(fileRecord,#PosShipToRegion)];
//(12) : Ord Planned GI date
plannedDateStr = this.Date2MMSlashDDSlashYYYY(conPeek(fileRecord,#PosOrdPlannedDate));
contHeader += [plannedDateStr];
//(13) : Requested delivery date
requestedDelDateStr = this.Date2MMSlashDDSlashYYYY(conPeek(fileRecord,#PosRequestedDelDate));
contHeader += [requestedDelDateStr];
// Fields that are not in the read file
//(14) : CustAccount
contHeader += [cusTableShip.AccountNum];
//(15) : InvoiceAccount
contHeader += [cusTableShip.AccountNum];
//(16) : CurrencyCode
contHeader += ["USD"];
//(17) : CustGroup
contHeader += [cusTableShip.CustGroup];
//(18) : LanguageId
contHeader += ["en-us"];
//(19) : SalesType
contHeader += ["Sales order"];
//(20) : DefaultDimension
contHeader += [""];
//(21) : DlvMode
contHeader += [cusTableShip.DlvMode];
//(22) : DlvTerm
contHeader += [cusTableShip.DlvTerm];
//(23) : InventLocationId
contHeader += [cusTableShip.InventLocation];
//(24) : InventSiteId
contHeader += [cusTableShip.InventSiteId];
//(25) : PostingProfile
contHeader += [custParameters::find().PostingProfile];
//(26) : PriceGroupId
contHeader += [cusTableShip.PriceGroup];
//(27) : SalesName
contHeader += [cusTableShip.name()];
//(28) : SalesOriginId
contHeader += [""];
//(29) : SalesPoolid
contHeader += [cusTableShip.SalesPoolId];
//(30) : TaxGroup
contHeader += [cusTableShip.TaxGroup];
select firstOnly eanlogPartnerChannelTable
where eanlogPartnerChannelTable.PartyDeliverTo == cusTableShip.Party
|| eanlogPartnerChannelTable.PartyOrderBy == cusTableShip.Party;
//(31) : EanlogCalcMethodId
contHeader += ["Sales"];
//(32) : EanLogCalculateDate
contHeader += [dateDayMMSlashDDSlashYYYY];
//(33) : EanLogCloseRemainQty
contHeader += [cusTableShip.EanlogCloseRemainQty];
//(34) : EanlogCustInvoicePAyby
contHeader += [cusTableShip.AccountNum];
//(35) : EanlogHierarchyId
eanlogHierarchyId = "";
if (eanlogPartnerChannelTable.PartnerChannelId)
{
eanlogHierarchyId = (select firstonly HierarchyId from EanlogPartnerChannelHierarchies
index IdIdx
where EanlogPartnerChannelHierarchies.PartnerChannelId == eanlogPartnerChannelTable.PartnerChannelId
&& EanlogPartnerChannelHierarchies.FromDate <= systemDateGet()
&& (!EanlogPartnerChannelHierarchies.ToDate || EanlogPartnerChannelHierarchies.ToDate >= systemDateGet())
).HierarchyId;
}
contHeader += [eanlogHierarchyId];
//(36) : EanLogOrderDate
contHeader += [dateDayMMSlashDDSlashYYYY];
//(37) : EanlogPartnerChannelId
contHeader += [eanlogPartnerChannelTable.PartnerChannelId];
//(38) : EanlogPriceApplicationDate
contHeader += [""];
//(39) : EanlogSalesOverDelivery
contHeader += [1];
//(40) : EanlogSalesunderDelivery
contHeader += [1];
this.writeHeader(contHeader);
}
//(01-05)
contLine += [salesId];
contLine += [conPeek(fileRecord,#PosItemLine)];
//Find ItemId from external Item.
custVendExternalItem = CustVendExternalItem::findExternalItemId(ModuleInventPurchSalesVendCustGroup::Cust,custAccountShip,conPeek(fileRecord,#PosMaterial));
//
if (!custVendExternalItem)
{
throw error(strFmt("No item for the external item %1 and the customer %2", conPeek(fileRecord,#PosMaterial),cusTableShip.AccountNum));
}
//
contLine += [custVendExternalItem.itemId];
contLine += [conPeek(fileRecord,#PosDescription)];
contLine += [conPeek(fileRecord,#PosOrderConfirmedQty)];
//(06) : CurrencyCode
contLine += ["USD"];
//(07) : CustAccount
contLine += [cusTableShip.AccountNum];
//(08) : CustGroup
contLine += [cusTableShip.CustGroup];
//(09) : ShippingDateRequested
requestedDelDateStr = this.Date2MMSlashDDSlashYYYY(conPeek(fileRecord,#PosRequestedDelDate));
contLine += [requestedDelDateStr];
//(10) : CustomerRef
contLine += [""];
//(11) : ExternalItemId
contLine += [conPeek(fileRecord,#PosMaterial)];
//(12) : BarCode
inventDim.clear();
itemBarCode = "";
barcodeSetupId = "";
inventItemBarcode = InventItemBarcode::findSalesBarcodeDimension(custVendExternalItem.itemId, inventDim);
if (inventItemBarcode)
{
itemBarCode = inventItemBarcode.ItemBarCode;
barcodeSetupId = inventItemBarcode.BarcodeSetupId;
}
contLine += [itemBarCode];
//(13) : BarCodeType
contLine += [barcodeSetupId];
//(14) : DlvMode
contLine += [cusTableShip.DlvMode];
//(15) : InventSiteId
contLine += [cusTableShip.InventSiteId];
//(16) : InventLocationId
contLine += [cusTableShip.InventLocation];
//(17) : PriceUnit
contLine += [1];
//(18) : SalesGroup
contLine += [""];
//(19) : SalesType
contLine += ["Sales order"];
//(20) : TaxGroup
contLine += [cusTableShip.TaxGroup];
//(21) : TaxItemGroup
contLine += [InventTableModule::find(custVendExternalItem.itemId,ModuleInventPurchSales::Sales).TaxItemGroupId];
this.writeLine(contLine);
fileRecord = fileIn.read();
}
info(strFmt("@SPL228", fileNameOpenIn));
if (iLine)
{
this.closeFileOut();
info(strFmt("@SPL229",fileNameOpenOutHeader,int2str(iHeader - 1)));
info(strFmt("@SPL230",fileNameOpenOutLine,int2str(iLine - 1)));
}
else
{
info(strFmt("@SPL180"));
}
}
public void writeLine(container _fields)
{
fileOutLine.writeExp(_fields);
}
server static SPLSalesImportSplitFile construct()
{
return new SPLSalesImportSplitFile();
}
static void main(Args args)
{
SPLSalesImportSplitFile SPLSalesImportSplitFile;
;
SPLSalesImportSplitFile = SPLSalesImportSplitFile::construct();
SPLSalesImportSplitFile.getLast();
if (SPLSalesImportSplitFile.prompt())
SPLSalesImportSplitFile.run();
}
Print a file on the client : txt, doc, pdf
// Print on the client (open the notepad or word or the acrobat reader ...)
// Print on the default printer of the user.
static void SPL_SBE_PrintFileKit(Args _args)
{
InventTable inventTable;
SPLManufacturerInst SPLManufacturerInst;
FileName fileName;
str coreLabelExe;
#macrolib.WinAPI
;
inventTable = InventTable::find("ItemKit");
SPLManufacturerInst = SPLManufacturerInst::find(inventTable.ItemId);
if (!SPLManufacturerInst)
{
info(strfmt("@SPL3653",inventTable.ItemId));
return;
}
fileName = SPLManufacturerInst.FileInstruction;
CoreLabelExe = WinAPI::findExecutable(fileName);
if (CoreLabelExe)
WinAPI::shellExecute(fileName, '', '', #ShellExePrint);
}
Export file format excel XML
// To known the good format we need to save on excel in format XML to know what to put in file.
// (All the header of the file)
public class SPLSalesLineDailySalesExport extends RunBaseBatch
{
SPLSalesLine SplSalesLineTmp;
FormDataSource SPLSalesLineDS;
TextIo file;
FilePath filePathDir;
FilePath fileNameFull;
int iTab;
#File
#define.tabItem(1)
#define.tabOrder(2)
#define.tabSalesCat(3)
#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
#endmacro
}
public ClassDescription caption()
{
ClassDescription ret;
ret = super();
ret = "@SPL39";
return ret;
}
boolean checkDir()
{
FileIOPermission fileIOPerm;
boolean pathExists;
filePathDir = InventParameters::find().SPLSalesDailyExportDir;
if (!filePathDir)
{
return checkFailed("@SPL38");
}
if (isRunningOnServer())
{
fileIOPerm = new FileIOPermission(filePathDir,'R');
fileIOPerm.assert();
pathExists = WinAPIServer::pathExists(filePathDir);
CodeAccessPermission::revertAssert();
}
else
{
pathExists = WinAPI::pathExists(filePathDir);
}
if (!pathExists)
{
info(strFmt("@SPL37",filePathDir));
return false;
}
return true;
}
public void CloseFile()
{
file = null;
}
private int CodePage()
{
#localcodepage
;
return #cp_1255;
}
private void CreateAndSaveXLS_Cat()
{
int iLine,iLineFile;
WorkerSalesResponsible WorkerSalesResponsibleSave;
;
try
{
while select WorkerSalesResponsible, SalesCategory,
sum(LineAmount), sum(EanLogQuantity), sum(Qty)
from SplSalesLineTmp
group by WorkerSalesResponsible, SalesCategory
{
iLine++;
if (SplSalesLineTmp.WorkerSalesResponsible != WorkerSalesResponsibleSave || iLine == 1)
{
if (iLine !=1)
{
this.WriteFooter();
this.closeFile();
info(strFmt("@SPL32",fileNameFull,iLineFile));
}
iLineFile = 0;
this.openFile();
this.WriteHeader();
}
this.WriteLine();
iLineFile++;
WorkerSalesResponsibleSave = SplSalesLineTmp.WorkerSalesResponsible;
}
if (iLine > 0)
{
this.WriteFooter();
this.CloseFile();
info(strFmt("@SPL32",fileNameFull,iLineFile));
}
}
catch
{
this.CloseFile();
throw error("");
}
}
private TextIo file()
{
return file;
}
public str fileName()
{
str fileNameItab;
switch (iTab)
{
case #tabItem :
fileNameItab = this.FileName_Item();
break;
case #tabOrder :
fileNameItab = this.FileName_order();
break;
case #tabSalesCat :
fileNameItab = this.FileName_Cat();
break;
}
return fileNameItab;
}
public str fileName_Cat()
{
return "DailySales_Category";
}
public boolean FillSalesLineTmp()
{
int i;
SPLSalesLine SPLSalesLine;
date dateDlv;
boolean ret = false;
;
dateDlv = systemDateGet() - 1;
SplSalesLineTmp.setTmp();
ttsBegin;
while select SPLSalesLine
//where SPLSalesLine.dlvdate == dateDlv
{
SplSalesLineTmp.data(SPLSalesLine);
SplSalesLineTmp.insert();
ret = true;
}
ttsCommit;
return ret;
}
public void openFile()
{
FileIOPermission permission;
str 10 dateStr;
str 10 timeStr;
;
dateStr = strRem(date2str(systemDateGet(),321,2,-1,2,-1,4),'/');
timeStr = strRem(time2str(timeNow(),1,1),':');
//fileNameFull = filePathDir + "\\" + this.fileName() + "_" + SplSalesLineTmp.WorkerSalesResponsibleName() + "_" + dateStr + "_" + timeStr + ".XML";
fileNameFull = filePathDir + "\\" + dateStr + "_" + SplSalesLineTmp.WorkerSalesResponsibleName() + "_" + this.fileName() + ".XML";
CodeAccessPermission::revertAssert();
permission = new FileIOPermission(fileNameFull,"w");
permission.assert();
file = new TextIo(fileNameFull, "w", this.CodePage());
if (!file)
{
throw error(strFmt("@SYS76826",fileNameFull));
}
}
public void run()
{
if (this.checkDir())
{
if (this.FillSalesLineTmp())
{
iTab = #tabItem;
this.createAndSaveXLS_Item();
iTab = #tabOrder;
this.createAndSaveXLS_Order();
iTab = #tabSalesCat;
this.createAndSaveXLS_Cat();
}
}
}
public void WriteFooter()
{
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' False');
file.write(' False');
file.write(' ');
file.write(' ');
file.write('');
}
public void WriteHeader()
{
this.WriteHeaderGeneral();
switch (iTab)
{
case #tabItem :
this.WriteHeader_Item();
break;
case #tabOrder :
this.WriteHeader_order();
break;
case #tabSalesCat :
this.WriteHeader_Cat();
break;
}
}
public void WriteHeader_cat()
{
file.write('');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' | Name | ');
file.write(' Sales amount | ');
file.write(' SLU Qty | ');
file.write(' Quantity | ');
file.write(' Month sales amount | ');
file.write(' Month SLU qty | ');
file.write(' Month quantity | ');
file.write(' Year sales amount | ');
file.write(' Year SLU qty | ');
file.write(' Year quantity | ');
file.write('
');
}
public void WriteHeaderGeneral()
{
file.write('');
file.write('');
file.write('');
file.write(' ');
file.write(' Tali MaronDotan');
file.write(' Tali MaronDotan');
file.write(' 2015-06-01T11:36:37Z');
file.write(' 15.00');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' 7515');
file.write(' 17160');
file.write(' 0');
file.write(' 0');
file.write(' False');
file.write(' False');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
file.write(' ');
}
public void WriteLine()
{
switch (iTab)
{
case #tabItem :
this.WriteLine_Item();
break;
case #tabOrder :
this.WriteLine_order();
break;
case #tabSalesCat :
this.WriteLine_Cat();
break;
}
}
public void WriteLine_cat()
{
Amount startMonthLineAmount;
Amount startMonthEanLogQty;
Amount startMonthQty;
Amount startYearLineAmount;
Amount startYearEanLogQty;
Amount startYearQty;
;
[startMonthLineAmount, startMonthEanLogQty, startMonthQty] = SplSalesLineTmp.FromStartMonthAllQty();
[startYearLineAmount, startYearEanLogQty, startYearQty] = SplSalesLineTmp.FromStartYearAllQty();
file.write(' ');
file.write(' | ' + SplSalesLineTmp.SalesCategoryName() + ' | ');
file.write(' ' + num2str(SplSalesLineTmp.LineAmount,0,4,1,2) + ' | ');
file.write(' ' + num2str(SplSalesLineTmp.EanlogQuantity,0,4,1,2) + ' | ');
file.write(' ' + num2str(SplSalesLineTmp.qty,0,4,1,2) + ' | ');
file.write(' ' + num2str(startMonthLineAmount,0,4,1,2) + ' | ');
file.write(' ' + num2str(startMonthEanLogQty,0,4,1,2) + ' | ');
file.write(' ' + num2str(startMonthQty,0,4,1,2) + ' | ');
file.write(' ' + num2str(startYearLineAmount,0,4,1,2) + ' | ');
file.write(' ' + num2str(startYearEanLogQty,0,4,1,2) + ' | ');
file.write(' ' + num2str(startYearQty,0,4,1,2) + ' | ');
file.write('
');
}
static void main(Args args)
{
SPLSalesLineDailySalesExport SPLSalesLineDailySalesExport;
;
SPLSalesLineDailySalesExport = new SPLSalesLineDailySalesExport();
if (SPLSalesLineDailySalesExport.prompt())
SPLSalesLineDailySalesExport.run();
}
Export file XLS
//Column A in text format : cells.range('A:A').numberFormat('@');
// Export format XLS
#Excel
SysExcelApplication sysExcelApplication;
SysExcelRange sysExcelRange;
SysExcelWorkbook sysExcelWorkbook;
SysExcelWorkbooks sysExcelWorkbooks;
SysExcelWorksheet sysExcelWorksheet;
SysExcelWorksheets sysExcelWorksheets;
SysExcelCell sysExcelCell;
SysExcelCells sysExcelCells;
protected void writeFileXLS()
{
str ledgerAccount;
SPLDimDepartment dimDepartment;
SPLDimBusinessStream dimBusiness;
LedgerTable ledgerTable;
ReferenceDate SplReferenceDatePeriod;
int refDateYear, refDateMonth, refDateDay;
int rowsWritten;
str accountNumber;
AmountMST amount;
AmountMST openingBalance, debit, credit, net, closingBalance;
int i, iMax;
Filename filenameOnly, type, path;
FileName exportFileName;
;
[path, filenameOnly, type] = fileNameSplit(fileName);
exportFileName = path + filenameOnly + ".xls";
this.writeHeader();
rowsWritten += 1;
iMax = conLen(contLedger);
for (i = 1; i less then iMax; i++)
{
rowsWritten += 1;
accountNumber = "";
openingBalance = 0;
closingBalance = 0;
[accountNumber,openingBalance,closingBalance] = conpeek(contLedger,i);
[ledgerAccount, dimDepartment, dimBusiness] = str2con(accountNumber,"-");
ledgerTable = LedgerTable::find(ledgerAccount);
SplReferenceDatePeriod = dateMthFwd(referenceDate,2);
refDateYear = year(SplReferenceDatePeriod);
refDateMonth = MthOfYr(SplReferenceDatePeriod);
refDateDay = dayOfMth(SplReferenceDatePeriod);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posLedgerName);
sysExcelCell.value(ledgerTable.AccountName);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posCodeCombinationId);
sysExcelCell.value("");
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posPeriodName);
sysExcelCell.value(date2str(referenceDate,123,2,-1,2,-1,4));
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posPeriodYear);
sysExcelCell.value(refDateYear);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posPeriodNum);
sysExcelCell.value(refDateMonth);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posCompany);
sysExcelCell.value("375");
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posBusinessStream);
if (dimBusiness == "021" || dimBusiness == "21")
sysExcelCell.value("B374");
else if (dimBusiness == "375")
sysExcelCell.value("B375");
else
sysExcelCell.value(dimBusiness);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posDepartment);
sysExcelCell.value(dimDepartment);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posAccount);
if (ledgerTable.SPLIsIntercompany)
sysExcelCell.value("");
else
sysExcelCell.value(ledgerAccount);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posLocalAccount);
sysExcelCell.value(0);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posInterCompany);
if (ledgerTable.SPLIsIntercompany)
sysExcelCell.value(ledgerAccount);
else
sysExcelCell.value("");
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posLocation);
sysExcelCell.value(0);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posProduct);
sysExcelCell.value(0);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posFuture);
sysExcelCell.value(0);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posCurrency);
sysExcelCell.value("ILS");
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posPtdBalance);
sysExcelCell.value(openingBalance);
sysExcelCell = sysExcelWorksheet.cells().item(rowsWritten, #posYtdBalance);
sysExcelCell.value(closingBalance);
}//For
//sysExcelRange.autoFit();
sysExcelWorksheet.columns().autoFit();
sysExcelApplication.displayAlerts(false);
sysExcelWorkbook.saveAs(exportFileName);
sysExcelWorkBook.comObject().save();
sysExcelWorkBook.saved(true);
//sysExcelApplication.quit();
//Sh
//sysExcelApplication.save();
// Close Excel
sysExcelApplication.quit();
sysExcelWorkbooks = null;
sysExcelWorkbook = null;
sysExcelWorksheet = null;
sysExcelCell = null;
sysExcelApplication = null;
}
//===================Other code=======================//
public void run()
{
SysExcelApplication sysExcelApplication;
SysExcelWorkbooks sysExcelWorkBooks;
SysExcelWorkbook sysExcelWorkBook;
SysExcelWorkSheets sysExcelWorkSheets;
SysExcelWorkSheet sysExcelWorkSheet;
int row;
int col;
SPLOperatorReport operatorReport;
SysDictEnum dictEnumEnergy = new SysDictEnum(enumNum(SplEnergyType));
Counter valuesEnergy = dictEnumEnergy.values();
//create excel file
fileName = SPLPsParameters::find().WorkPlanFilePath +
"\\WorkPlan_" +
date2str(sdate,321,2,DateSeparator::None,2,DateSeparator::None,4) +
"_" +
date2str(edate,321,2,DateSeparator::None,2,DateSeparator::None,4) +
"_" +
enum2str(energy) +
"_" +
enum2str(Block) +
".xlsx";
sysExcelApplication = SysExcelApplication::construct();
sysExcelWorkBooks = sysExcelApplication.workbooks();
sysExcelWorkBook = sysExcelWorkBooks.add();
sysExcelWorkSheets = sysExcelWorkbook.worksheets();
sysExcelWorkSheet = sysExcelWorkSheets.itemFromNum(1);
sysExcelApplication.visible(false);
row = 1 ;
col = 1;
sysExcelWorkSheet.cells().item(row,col).value(Syslabel::labelId2String(literalstr("@MCS1334"), companyinfo::languageId()));
sysExcelWorkSheet.cells().item(row,col).font().bold(true);
col++;
if(Block != SplTubinBlockIncAll::Station)
{
sysExcelWorkSheet.cells().item(row,col).value(Syslabel::labelId2String(literalstr("@SPL425"), companyinfo::languageId()));
sysExcelWorkSheet.cells().item(row,col).font().bold(true);
col++;
sysExcelWorkSheet.cells().item(row,col).value(Syslabel::labelId2String(literalstr("@MCS1397"), companyinfo::languageId()));
sysExcelWorkSheet.cells().item(row,col).font().bold(true);
col++;
}
sysExcelWorksheet.cells().item(row,col).value(Syslabel::labelId2String(literalstr("@SPL527"), companyinfo::languageId()));
sysExcelWorkSheet.cells().item(row,col).font().bold(true);
col++;
sysExcelWorksheet.cells().item(row,col).value(Syslabel::labelId2String(literalstr("@SPL645"), companyinfo::languageId()));
sysExcelWorkSheet.cells().item(row,col).font().bold(true);
col++;
sysExcelWorksheet.cells().item(row,col).value(Syslabel::labelId2String(literalstr("@SPL2888"), companyinfo::languageId()));
sysExcelWorkSheet.cells().item(row,col).font().bold(true);
col = 1;
row++;
if(block == SplTubinBlockIncAll::Block1 || block == SplTubinBlockIncAll::Block2)
{
while select * from operatorReport
where operatorReport.DateDate More= sDate
&& operatorReport.DateDate Less= edate
&& operatorReport.EnergyType == energy
&& ((block == SplTubinBlockIncAll::Block1 && operatorReport.TurbinBlock == SPLTurbinBlock::Element1) ||
(block == SplTubinBlockIncAll::Block2 && operatorReport.TurbinBlock == SPLTurbinBlock::Element2))
{
sysExcelWorksheet.cells().item(row,1).value(FcsDateTimeAPI::value2display(operatorReport.DateTime));
sysExcelWorksheet.cells().item(row,2).value(enum2str(operatorReport.TurbinBlock));
sysExcelWorksheet.cells().item(row,3).value(enum2str(operatorReport.EnergyType));
sysExcelWorksheet.cells().item(row,5).value(operatorReport.ProductionLoadPlanStr());
sysExcelWorksheet.cells().item(row,4).value(operatorReport.getTotIECInsProductionStr());
sysExcelWorksheet.cells().item(row,6).value(operatorReport.totProduction());
row++;
}
}
else
{
while select DateTime,sum(TotalProduction),sum(ProductionLoadPlan),sum(ActualProduction),sum(IECInsProduction),
sum(IECDeviation),sum(NotExplainedChange) from operatorReport
group by DateTime,energytype
where operatorReport.DateDate More= sDate
&& operatorReport.DateDate Less= edate
&& operatorReport.EnergyType == energy
{
sysExcelWorksheet.cells().item(row,1).value(FcsDateTimeAPI::value2display(operatorReport.DateTime));
sysExcelWorksheet.cells().item(row,2).value(operatorReport.ProductionLoadPlanStr());
sysExcelWorksheet.cells().item(row,3).value(operatorReport.getTotIECInsProductionStr());
sysExcelWorksheet.cells().item(row,4).value(operatorReport.totProduction());
row++;
}
}
sysExcelWorksheet.columns().autoFit();
sysExcelApplication.displayAlerts(false);
sysExcelWorkbook.saveAs(fileName);
sysExcelWorkBook.comObject().save();
sysExcelWorkBook.saved(true);
sysExcelApplication.quit();
//send mail
this.sendMail();
}