public int ImportExcel(Stream excelData, bool newVersion)
{
int startRow = 2; // starting row for reservation data
int errorCount = 0;
string currentProperty = string.Empty;
string currentPayee = string.Empty;
string inputSource = "Job Cost Excel";
int totalCols = newVersion ? 25 : 23;
int billingStatusOffset = newVersion ? 0 : -2;
_costSkip10Col += billingStatusOffset;
_costAmountCol += billingStatusOffset;
_costSkip11Col += billingStatusOffset;
_costBalanceCol += billingStatusOffset;
List <JobCost> jobCosts = new List <JobCost>();
var propertyProvider = new PropertyProvider(_context);
using (var package = new ExcelPackage(excelData))
{
// storage for parsed data
List <InputError> errorRows = new List <InputError>();
ExcelWorkbook workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
ExcelWorksheet currentWorksheet = workBook.Worksheets[1];
for (int row = startRow; row <= currentWorksheet.Dimension.End.Row; row++)
{
if (currentWorksheet.Dimension.End.Column != totalCols)
{
var message = string.Format("The total number of columns {0:d} does not match {1:d}", currentWorksheet.Dimension.End.Column, totalCols);
var inputError = CreateInputError(inputSource, row, "Parse", message, "Excel row");
errorRows.Add(inputError);
errorCount++;
}
try
{
JobCostRow costRow = ParseJobCostExcelRow(currentWorksheet.Cells, row, inputSource);
// the last row has 'Total' on the first column
if (IsLastRow(costRow))
{
break;
}
if (IsPropertyRow(costRow))
{
currentProperty = costRow.PropertyCode != string.Empty ? costRow.PropertyCode : costRow.JobCostProperty2;
}
else if (IsOwnerRow(costRow))
{
currentPayee = costRow.JobCostPayee;
}
else if (IsCostRow(costRow))
{
costRow.OriginalPropertyCode = currentProperty;
if (propertyProvider.PropertyExist(currentProperty))
{
costRow.PropertyCode = currentProperty;
}
else
{
costRow.PropertyCode = AppConstants.DEFAULT_PROPERTY_CODE;
}
costRow.JobCostPayoutTo = currentPayee;
jobCosts.Add(MapJobCost(costRow));
}
else if (IsOwnerTotalRow(costRow) || IsSubTotalRow(costRow))
{
continue;
}
}
catch (Exception ex)
{
var message = "Data parse exception: " + ex.Message;
var inputError = CreateInputError(inputSource, row, "Exception", message, "Job Cost Excel row");
errorRows.Add(inputError);
errorCount++;
}
}
try
{
// save job cost if there is no error
if (errorCount == 0 && jobCosts.Count > 0)
{
_context.JobCosts.AddRange(jobCosts);
_context.SaveChanges(); // save job costs
}
}
catch (Exception ex)
{
var message = "Job Cost saving error: " + ex.Message;
var inputError = CreateInputError(inputSource, 0, "Exception", message, "Database saving");
_context.InputErrors.Add(inputError);
_context.SaveChanges(); // save errors
errorCount = 100000; // a large number
}
}
else
{
var message = "Input file error: Cannot detect workbook in the import file.";
var inputError = CreateInputError(inputSource, 0, "Input File", message, "Job Cost Excel file");
errorRows.Add(inputError);
errorCount++;
}
}
else
{
var message = "Input file error: Cannot detect worksheet in the import file.";
var inputError = CreateInputError(inputSource, 0, "Input File", message, "Job Cost Excel file");
errorRows.Add(inputError);
errorCount++;
}
}
return(errorCount == 0 ? jobCosts.Count * 10000 : -errorCount);
}
