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); }