Action Menu Item: SAN_UploadExcelData
Object type: Class
Object: <Controller class name>
Label: <>
Class:
Controller class
class SAN_UploadExcelDataController extends SysOperationServiceController
{
/// <summary>
/// Sets the class caption.
/// </summary>
public ClassDescription caption()
{
return "Upload excel data";
}
/// <summary>
/// Provides entry point for the instance of <c>SAN_UploadExcelDataController</c>.
/// </summary>
/// <param name = "_args">
/// The arguments passed to the class <c>SAN_UploadExcelDataController</c>.
/// </param>
public static void main(Args _args)
{
SAN_UploadExcelDataController controller;
controller = SAN_UploadExcelDataController::construct();
controller.showBatchTab(false);
controller.parmArgs(_args);
controller.startOperation();
}
/// <summary>
/// Initializes new instance of <c>SAN_UploadExcelDataController</c>.
/// </summary>
/// <returns>
/// Return object of <c>SAN_UploadExcelDataController</c>.
/// </returns>
public static SAN_UploadExcelDataController construct()
{
SAN_UploadExcelDataController controller;
controller = new SAN_UploadExcelDataController(classStr(SAN_UploadExcelDataService),
methodStr(SAN_UploadExcelDataService, Run),
SysOperationExecutionMode::Synchronous);
return controller;
}
}
Contract class:
[DataContractAttribute,
SysOperationContractProcessingAttribute(classStr(SAN_UploadExcelDataUIBuilder))]
public class SAN_UploadExcelDataContract extends SysOperationDataContractBase implements SysOperationValidatable
{
Str1260 layout;
NoYes hasColumnNames;
FileName fileName;
private System.IO.Stream inputDataStream;
public boolean validate()
{
boolean isValid = true;
return isValid;
}
/// <summary>
/// Gets or sets the value of the data contract parameter hasColumnNames.
/// </summary>
/// <param name="_hasColumnNames">
/// The new value of the data contract parameter hasColumnNames.
/// </param>
/// <returns>
/// Returns the current value of data contract parameter hasColumnNames.
/// </returns>
[DataMemberAttribute,
SysOperationLabelAttribute("File has column name in first row") ]
public NoYes parmHasColumnNames(NoYes _hasColumnNames = hasColumnNames)
{
hasColumnNames = _hasColumnNames;
return hasColumnNames;
}
/// <summary>
/// Gets or sets the value of the data contract parameter fileName.
/// </summary>
/// <param name="_fileName">
/// The new value of the data contract parameter fileName.
/// </param>
/// <returns>
/// Returns the current value of data contract parameter fileName.
/// </returns>
public Filename parmFileName(FileName _fileName = fileName)
{
fileName = _fileName;
return fileName;
}
/// <summary>
/// Gets or sets the value of inputDataStream.
/// </summary>
/// <param name="_value">
/// The new value of the parameter inputDataStream.
/// </param>
/// <returns>
/// Returns the current value of data contract parameter inputDataStream.
/// </returns>
[Hookable(false)]
public System.IO.Stream parmInputDataStream(System.IO.Stream _value = inputDataStream)
{
inputDataStream = _value;
return inputDataStream;
}
/// <summary>
/// Gets or sets the value of the data contract parameter fileLayout.
/// </summary>
/// <param name="_layout">
/// The new value of the data contract parameter fileLayout.
/// </param>
/// <returns>
/// Returns the current value of data contract parameter fileLayout.
/// </returns>
[DataMemberAttribute,
SysOperationLabelAttribute("File layout") ]
public Str1260 parmLayout(Str1260 _layout = layout)
{
layout = '';
layout += strfmt("%1 | %2 - %3\n", '1', 'A', "@SYS12128"); //Invoice
layout += strfmt("%1 | %2 - %3\n", '2', 'B', "Number"); //Item number
layout += strfmt("%1 | %2 - %3\n", '3', 'C', "Date");//Date
layout += strfmt("%1 | %2 - %3\n", '4', 'D', "Status"); //Status ENUM
layout += strfmt("%1 | %2 - %3\n", '5', 'E', "@SYS14578"); //Quantity
return layout;
}
}
UI builder class:
class SAN_UploadExcelDataUIBuilder extends SysOperationAutomaticUIBuilder
{
#define.commandButton('CommandButton')
#define.fileUpload('FileUpload')
#define.fileTypesAccepted('.xlsx,.xls')
SAN_UploadExcelDataContract contract;
/// <summary>
/// Adds a button to the dialog to import a file.
/// </summary>
[Hookable(false)]
public void build()
{
DialogGroup dialogGroup;
DialogTabPage dialogTabPage;
Dialog dlg;
FormBuildControl formBuildControl;
FileUploadBuild dialogFileUpload;
DialogField dialogLayout;
FormBuildTabPageControl parametersTabPage;
FormBuildTabPageControl layoutTabPage;
dlg = this.dialog();
contract = this.dataContractObject() as SAN_UploadExcelDataContract;
dialogTabPage = dlg.addTabPage("@SYS7764");
dialogTabPage.columns(2);
parametersTabPage = dlg.formBuildDesign().control(dialogTabPage.name());
parametersTabPage.fastTabExpanded(FastTabExpanded::Yes);
dialogGroup = dlg.addGroup('Upload file');
formBuildControl = dlg.formBuildDesign().control(dialogGroup.name());
dialogFileUpload = formBuildControl.addControlEx(classstr(FileUpload), #fileUpload);
dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);
dialogFileUpload.baseFileUploadStrategyClassName(classstr(FileUploadTemporaryStorageStrategy));
dialogFileUpload.fileTypesAccepted(#fileTypesAccepted);
dialogFileUpload.fileNameLabel("@SYS308842");
dlg.addGroup("@SYS339526");
this.addDialogField(methodStr(SAN_UploadExcelDataContract, parmHasColumnNames), contract);
dialogTabPage = dlg.addTabPage("@Polaris:FileLayout");
layoutTabPage = dlg.formBuildDesign().control(dialogTabPage.name());
layoutTabPage.fastTabExpanded(FastTabExpanded::Yes);
dialogLayout = this.addDialogField(methodStr(SAN_UploadExcelDataContract, parmLayout), contract);
dialogLayout.showLabel(false);
dialogLayout.enabled(false);
dialogLayout.widthMode(1);
dialogLayout.displayHeight(15);
}
/// <summary>
/// Handles dialog closing events.
/// </summary>
/// <param name = "sender">
/// xFormRun class.
/// </param>
/// <param name = "e">
/// FormEventArgs class.
/// </param>
[SuppressBPWarningAttribute('BPParametersNotUsed', 'This event parameter is not used')]
private void dialogClosing(xFormRun sender, FormEventArgs e)
{
FormEventArgs formEventArgs;
formEventArgs = e;
this.dialogEventsUnsubscribe(sender as FormRun);
}
/// <summary>
/// Handles dialog events.
/// </summary>
/// <param name = "_formRun">
/// FormRun class type.
/// </param>
private void dialogEventsSubscribe(FormRun _formRun)
{
FileUpload fileUpload = _formRun.control(_formRun.controlId(#fileUpload));
fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);
fileUpload.notifyUploadAttemptStarted += eventhandler(this.uploadStarted);
_formRun.onClosing += eventhandler(this.dialogClosing);
}
/// <summary>
/// Handles dialog event.
/// </summary>
/// <param name = "_formRun">
/// FormRun class type.
/// </param>
private void dialogEventsUnsubscribe(FormRun _formRun)
{
FileUpload fileUpload = _formRun.control(_formRun.controlId(#fileUpload));
fileUpload.notifyUploadCompleted -= eventhandler(this.uploadCompleted);
fileUpload.notifyUploadAttemptStarted -= eventhandler(this.uploadStarted);
_formRun.onClosing -= eventhandler(this.dialogClosing);
}
/// <summary>
/// Handles dialog event post run.
/// </summary>
[Hookable(false)]
public void postRun()
{
super();
FormRun formRun = this.dialog().dialogForm().formRun();
this.dialogEventsSubscribe(formRun);
this.setDialogOkButtonEnabled(formRun, false);
}
/// <summary>
/// Enables or disables the dialog Ok button.
/// </summary>
/// <param name = "_formRun">
/// The <c>FormRun</c> object.
/// </param>
/// <param name = "_isEnabled">
/// Indicates to enable or disable the Ok button.
/// </param>
protected void setDialogOkButtonEnabled(FormRun _formRun, boolean _isEnabled)
{
FormControl okButtonControl = _formRun.control(_formRun.controlId(#commandButton));
if (okButtonControl)
{
okButtonControl.enabled(_isEnabled);
}
}
/// <summary>
/// After the file has been uploaded, the Ok button is enabled.
/// </summary>
protected void uploadCompleted()
{
contract = this.dataContractObject() as SAN_UploadExcelDataContract;
var formRun = this.dialog().dialogForm().formRun();
FileUpload fileUpload = formRun.control(formRun.controlId(#fileUpload));
contract.parmFileName(fileUpload.fileName());
using (System.IO.Stream stream = fileUpload.getUploadedFile(true))
{
if (stream)
{
System.IO.MemoryStream copiedStream = new System.IO.MemoryStream();
stream.CopyTo(copiedStream);
contract.parmInputDataStream(copiedStream);
}
}
this.setDialogOkButtonEnabled(formRun, contract.parmInputDataStream() != null);
}
/// <summary>
/// During file upload, the Ok button is disabled.
/// </summary>
private void uploadStarted()
{
var formRun = this.dialog().dialogForm().formRun();
this.setDialogOkButtonEnabled(formRun, false);
}
}
Service class:
class SAN_UploadExcelDataService extends SysOperationServiceBase
{
OfficeOpenXml.ExcelRange cells;
System.IO.Stream stream;
NoYes hasColumnNames;
boolean validated = false;
container rowData;
int rowCounter;
int rowsWithError;
boolean error = false;
int totalRows;
int rowStarter;
int lineNumber;
InventSiteId lineInventSiteId;
FileName fileNameWithExt;
FileName fileName;
TextStreamIo streamIO;
boolean exceptionOccurred;
str lineNumberText;
boolean emptyRow;
TransDate today = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone());
container orderUnique;
str invoiceId, itemId, number;
PurchStatus statusValue;
TransDate dateValue;
Qty quantity;
PurchStatus purchStatus;
#file
#define.totalCells(5) // TODO need to be changed as per layout column nos
/// <summary>
/// Creates and initializes the file object.
/// </summary>
public void initFile()
{
#define.validationFileSuffix('-data-validation-errors.txt')
InteropPermission perm = new InteropPermission(InteropKind::ClrInterop);
perm.assert();
streamIO = TextStreamIO::constructForWrite(#utf8format);
streamIO.outRecordDelimiter(#delimiterCRLF);
fileName += #validationFileSuffix;
}
/// <summary>
/// The excel file objects are initialized here.
/// </summary>
public void initFileRead()
{
OfficeOpenXml.ExcelWorksheet worksheet;
OfficeOpenXml.ExcelPackage package;
int totalCells;
rowStarter = hasColumnNames ? 2 : 1;
try
{
package = new OfficeOpenXml.ExcelPackage(stream);
worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
cells = worksheet.Cells;
totalRows = worksheet.Dimension.End.Row;
totalCells = worksheet.Dimension.End.Column;
if (totalCells != #totalCells)
{
error = true;
error(strFmt("File should contain %1 columns but contains %2 columns.", #totalCells, totalCells));
}
}
catch
{
error = true;
error("@SYS79574" + ' ' + "@SYS25338"+' '+"An unexpected error occured while importing order data from excel file");
}
}
/// <summary>
/// Called after data has been processed and file has been generated. Closes the file created.
/// </summary>
public void downloadFile()
{
if (streamIo != null)
{
File::SendFileToUser(streamIo.getStream(), fileName);
}
CodeAccessPermission::revertAssert();
}
/// <summary>
/// Display upload summary after operation is executed.
/// </summary>
public void displaySummary()
{
if (error)
{
if (!streamIO)
{
return;
}
this.downloadFile();
setPrefix("Data validation summary");
info(strFmt("File name: %1",fileNamewithExt));
info(strFmt("Number of lines in the file: %1", totalRows));
info(strFmt("Number of valid lines: %1", totalRows-rowsWithError));
info(strfmt("Number of lines with errors: %1",rowsWithError));
error("Import data from Excel stopped because of the data validation errors in the Excel file.");
error("Fix the data validation errors and then upload the Excel file to run the data validation again.");
error("Data validation error file has automatically downloaded to your machine. Please save the file and review the errors to make the appropriate corrections and then upload the updated file.");
error(fileName);
}
else
{
setPrefix("Import data summary");
info(strFmt("File name: %1", fileNameWithExt));
info(strfmt("Number of lines in the file: %1t", totalRows));
}
}
/// <summary>
/// Process purchase order creation after file is validated.
/// </summary>
public void importFile()
{
if (error)
{
return;
}
try
{
orderUnique = conNull();
ttsbegin;
for (rowCounter = rowStarter; rowCounter<= totalRows; rowCounter++)
{
this.setRowData();
this.uploadData();
}
ttscommit;
}
catch
{
error = true;
error("An unexpected error occured while importing order data from excel file");
this.displaySummary();
}
}
public void uploadData()
{
//TODO
//Logic to be written on Table
}
/// <summary>
/// Process the purchase order import functionality.
/// </summary>
/// <param name = "_contract">
/// Contract class.
/// </param>
public void run(SAN_UploadExcelDataContract _contract)
{
SAN_UploadExcelDataContract contract;
int position;
try
{
contract = _contract;
stream = contract.parmInputDataStream();
hasColumnNames = contract.parmHasColumnNames();
fileNameWithExt = contract.parmFileName();
position = strFind(fileNameWithExt, '.', 0, strlen(fileNameWithExt));
FileName = strDel(fileNameWithExt, position, strlen(fileNameWithExt));
FileName = strReplace(FileName, ' ', '-');
this.initFileRead();
this.validateFile();
this.importFile();
this.displaySummary();
}
catch
{
error("An unexpected error occured while importing order data from excel file");
}
}
private void resetValues()
{
rowData = conNull();
exceptionOccurred = false;
emptyRow = true;
invoiceId = '';
itemId = '';
statusValue = PurchStatus::Backorder;
dateValue = dateNull();
quantity = 0;
}
/// <summary>
/// Set values by reading excel row data.
/// </summary>
public void setRowData()
{
boolean ret = true;
NoYes noYes;
try
{
int i;
this.resetValues();
lineNumberText = strFmt("Line number %1 >>", rowCounter);
for (i = 1; i <= #totalCells; i++)
{
rowData = conIns(rowData, i, cells.get_Item(rowCounter, i).Value);
if (emptyRow && conPeek(rowData, i))
{
emptyRow = false;
}
}
if (emptyRow)
{
ret = this.writeToLogFile(strFmt(lineNumberText + "The row contains no data."));
return;
}
if (typeOf(conPeek(rowData, 1)) == Types::String ||
typeOf(conPeek(rowData, 1)) == Types::Real) //InvoiceId
{
invoiceId = strLRTrim(conPeek(rowData, 1).ToString());
}
else if (conPeek(rowData, 1) != null)
{
ret = this.writeToLogFile(strFmt(lineNumberText + "InvalidDataTypeString: %1 must be a string but the value does not meet the expected format.", "Invoice number"));
}
if (typeOf(conPeek(rowData, 2)) == Types::String ||
typeOf(conPeek(rowData, 2)) == Types::Real) //Item number
{
itemId = strLRTrim(conPeek(rowData, 2).ToString());
}
else if (conPeek(rowData, 2) != null)
{
ret = this.writeToLogFile(strFmt(lineNumberText + "InvalidDataTypeString: %1 must be a string but the value does not meet the expected format.", "Item number"));
}
if (typeOf(conPeek(rowData, 3)) == Types::Date ||
typeOf(conPeek(rowData, 3)) == Types::Class) //Date
{
dateValue = conPeek(rowData, 3);
}
else if (typeOf(conPeek(rowData, 3)) == Types::String)
{
dateValue = str2Date(conPeek(rowData, 3), -1);
if (!dateValue)
{
ret = this.writeToLogFile(strFmt(lineNumberText + "InvalidDataTypeDate: %1 must be a date but the value does not meet the expected format. Sample date format is %2.", "Pedimento date", DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone())));
}
}
if (typeOf(conPeek(rowData, 4)) == Types::String ||
typeOf(conPeek(rowData, 4)) == Types::Real) // Status
{
statusValue = str2Enum(purchStatus, strLRTrim(conPeek(rowData, 4).ToString()));
}
else if (conPeek(rowData, 4) != null)
{
ret = this.writeToLogFile(strFmt(lineNumberText + "InvalidDataTypeString: %1 must be a string but the value does not meet the expected format.", "Status"));
}
if (typeOf(conPeek(rowData, 5)) == Types::Integer ||
typeOf(conPeek(rowData, 5)) == Types::Real) //Quantity
{
quantity = conPeek(rowData, 5);
}
else if (conPeek(rowData, 5) != null)
{
ret = this.writeToLogFile(strFmt(lineNumberText + "InvalidDataTypeNumber: %1 must be a number but the value does not meet the expected format.", "Quantity"));
}
//error = !ret;
}
catch
{
error = true;
ret = this.writeToLogFile(strFmt(lineNumberText+"@Polaris:ImportSalesOrderIllegalValue"));
}
}
/// <summary>
/// Validates Purchase order import file by looping through rows.
/// </summary>
/// <returns>
/// Returns true or false based on validation.
/// </returns>
public boolean validateFile()
{
if (error)
{
return error;
}
orderUnique = conNull();
for (rowCounter = rowStarter; rowCounter<= totalRows; rowCounter++)
{
this.setRowData();
if(!this.validateRowData())
{
error = true;
rowsWithError++;
}
}
return error;
}
/// <summary>
/// Validates purchase order line from excel
/// </summary>
/// <returns>
/// Returns true or false based on validation.
/// </returns>
boolean validateRowData()
{
boolean ret;
ret = true;
if (emptyRow)
{
return false;
}
return ret;
}
private boolean writeToLogFile(FreeTxt _errorTxt)
{
if(!streamIO)
{
this.initFile();
}
streamIO.write(_errorTxt);
return false;
}
}