private JobCostRow ParseJobCostExcelRow(ExcelRange cells, int row, string inputSource)
{
var costRow = new JobCostRow();
costRow.JobCostTotal = GetSafeCellString(cells[row, _costTotalCol].Value);
costRow.JobCostPayee = GetSafeCellString(cells[row, _costPayeeCol].Value);
costRow.PropertyCode = GetSafeCellString(cells[row, _costPropertyCol].Value);
costRow.JobCostProperty2 = GetSafeCellString(cells[row, _costProperty2Col].Value);
costRow.JobCostType = GetSafeCellString(cells[row, _costTypeCol].Value);
costRow.JobCostDate = GetSafeDate(cells[row, _costDateCol].Text);
costRow.JobCostNumber = GetSafeCellString(cells[row, _costNumberCol].Value);
costRow.JobCostSource = GetSafeCellString(cells[row, _costSourceCol].Value);
costRow.JobCostMemo = GetSafeCellString(cells[row, _costMemoCol].Value);
costRow.JobCostAccount = GetSafeCellString(cells[row, _costAccountCol].Value);
costRow.JobCostClass = GetSafeCellString(cells[row, _costClassCol].Value);
costRow.JobCostBillable = string.Compare(GetSafeCellString(cells[row, _costBillingCol].Value), "Unbilled", true) == 0 ? true : false;
costRow.JobCostAmount = GetSafeNumber(cells[row, _costAmountCol].Text);
costRow.JobCostBalance = GetSafeNumber(cells[row, _costBalanceCol].Text);
// temporary testing for billable and unbillable setting
//if (costRow.JobCostDate != null)
//{
// if (costRow.JobCostDate.Value.Day == 31) costRow.JobCostDate = costRow.JobCostDate.Value.AddDays(-1);
// costRow.JobCostDate = costRow.JobCostDate.Value.AddMonths(1);
//}
// house keeping fields
costRow.JobCostInputSource = inputSource;
return(costRow);
}
private JobCost MapJobCost(JobCostRow costRow)
{
return(new JobCost
{
PropertyCode = costRow.PropertyCode,
JobCostPayoutTo = costRow.JobCostPayoutTo,
JobCostType = costRow.JobCostType,
JobCostDate = ConversionHelper.EnsureUtcDate(costRow.JobCostDate),
JobCostNumber = costRow.JobCostNumber,
JobCostSource = costRow.JobCostSource,
JobCostMemo = costRow.JobCostMemo,
JobCostAccount = costRow.JobCostAccount,
JobCostClass = costRow.JobCostClass,
JobCostBillable = costRow.JobCostBillable,
JobCostAmount = costRow.JobCostAmount,
JobCostBalance = costRow.JobCostBalance,
OriginalPropertyCode = costRow.OriginalPropertyCode
});
}
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);
}
private bool IsOwnerRow(JobCostRow row)
{
return(!string.IsNullOrEmpty(row.JobCostPayee) && !IsCostRow(row) && row.JobCostBalance == 0 && row.JobCostAmount == 0);
}
private bool IsOwnerTotalRow(JobCostRow row)
{
return(!string.IsNullOrEmpty(row.JobCostPayee) && !IsCostRow(row));
}
private bool IsSubTotalRow(JobCostRow row)
{
return(!string.IsNullOrEmpty(row.PropertyCode) && !IsCostRow(row));
}
private bool IsPropertyRow(JobCostRow row)
{
return(!string.IsNullOrEmpty(row.PropertyCode) || !string.IsNullOrEmpty(row.JobCostProperty2));
}
private bool IsCostRow(JobCostRow row)
{
return(row.JobCostDate != null && row.JobCostAccount != string.Empty);
}
private bool IsLastRow(JobCostRow row)
{
return(string.Compare(row.JobCostTotal, "total", true) == 0);
}