using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class CustFDUploadExcel
{
str divisionFin, locationFin, projIdFin, clientFin, brandFin;
str businessSegmentFin, serviceSegmentFin, activityFin, CostcentreFin, vendorFin;
DimensionAttribute division, location, projIdF, clientF, brand;
DimensionAttribute businessSegment, serviceSegment, activity, Costcentre, vendorF;
public static void main(Args _args)
{
CustFDUploadExcel customerFinancialDimUpdate;
customerFinancialDimUpdate = new CustFDUploadExcel();
customerFinancialDimUpdate.run();
}
public void run()
{
System.IO.Stream stream;
ExcelSpreadsheetName sheeet;
FileUploadBuild fileUpload;
DialogGroup dlgUploadGroup;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
Dialog dialog = new Dialog("Import the Financial diemsnion for Customer data from Excel");
int cnt;
CustAccount custAccount;
TaxInformationCustTable_IN taxInformationCustTable;
CustTable custTable;
PANStatus_IN panStatus;
PANNumber_IN panNumber;
str panStatusStr;
DataAreaId DataAreaId;
str divisionFinValue, locationFinValue, projIdFinValue, clientFinValue, brandFinValue;
str businessSegmentFinValue, serviceSegmentFinValue, activityFinValue, CostcentreFinValue, vendorFinValue;
DimensionAttributeValue dimensionAttributeValue;
DimensionDefault result;
DimensionAttributeValueSetStorage valueSetStorage = new DimensionAttributeValueSetStorage();
dlgUploadGroup = dialog.addGroup("@SYS54759");
formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), "Upload");
fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
fileUploadBuild.fileTypesAccepted(".xlsx");
if (dialog.run() && dialog.closedOk())
{
FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId("Upload"));
FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
using (ExcelPackage Package = new ExcelPackage(stream))
{
int rowCount, i;
Package.Load(stream);
ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
OfficeOpenXml.ExcelRange range = worksheet.Cells;
rowCount = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;
ttsBegin;
for(i = 1; i<= rowCount; i++)
{
if (i == 1)
{
//this.financialDimensionNameGet(Cells, nRow);
DataAreaId = CompanyInfo::find().DataArea;
divisionFin = range.get_Item(i, 2).value;
division = dimensionAttribute::findByName(divisionFin);
locationFin = range.get_Item(i, 3).value;
location = dimensionAttribute::findByName(locationFin);
projIdFin = range.get_Item(i, 4).value;
projIdF = dimensionAttribute::findByName(projIdFin);
clientFin = range.get_Item(i, 5).value;
clientF = dimensionAttribute::findByName(clientFin);
}
else
{
valueSetStorage = new DimensionAttributeValueSetStorage();
custaccount = range.get_Item(i, 1).value;
custTable = CustTable::find(custAccount);
if (!custTable.RecId)
{
error(strFmt("The account number %1 is not available", custAccount));
}
divisionFinValue = range.get_Item(i, 2).value;
if (divisionFinValue && division)
{
dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(division, divisionFinValue, false, true);
valueSetStorage.addItem(dimensionAttributeValue);
}
locationFinValue = range.get_Item(i, 3).value;
if (locationFinValue && location)
{
dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(location, locationFinValue, false, true);
valueSetStorage.addItem(dimensionAttributeValue);
}
projIdFinValue = range.get_Item(i, 4).value;
if (projIdFinValue && projIdF)
{
dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(projIdF, projIdFinValue, false, true);
valueSetStorage.addItem(dimensionAttributeValue);
}
clientFinValue = range.get_Item(i, 5).value;
if (clientFinValue && clientF)
{
dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(clientF, clientFinValue, false, true);
valueSetStorage.addItem(dimensionAttributeValue);
}
ttsBegin;
custTable.selectForUpdate(true);
custTable.DefaultDimension = valueSetStorage.save();
custTable.update();
ttsCommit;
info(strFmt("%1 updated", custAccount));
}
}
}
}
}
}
}
