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); }
public int ImportFromCsv(string csvFile, DateTime reportDate, bool isCompleted, DateTime?payoutStartDate) { int errorCount = 0; int payoutCount = 0; int reservationCount = 0; int resolutionCount = 0; string channel = "Airbnb"; string account = GetAccountFromFilename(csvFile); string inputSource = string.Format("{0} {1}", reportDate.ToString("yyyy-MM-dd"), GetInputSourceFromFilePath(csvFile)); if (DuplicateInputSource(inputSource)) { DeleteFileIfAllowed(csvFile); return(0); } if (!File.Exists(csvFile)) { var inputError = new InputError { InputSource = inputSource, Row = 0, Section = "Input File Check", Message = string.Format("Input file '{0}' does not exist.", csvFile), OriginalText = csvFile, CreatedTime = DateTime.UtcNow }; _context.InputErrors.Add(inputError); _context.SaveChanges(); // save errors return(-1000000); // large number of errors } using (StreamReader sr = new StreamReader(csvFile)) { int totalCols = 14; int startRow = 2; // starting row for reservation data int payoutDateCol = 0; int typeCol = 1; int confirmationCodeCol = 2; int checkinDateCol = 3; int nightCol = 4; int guestCol = 5; int listingCol = 6; int detailCol = 7; int referenceCol = 8; int currencyCol = 9; int amountCol = 10; int payoutCol = 11; bool isOwnerRow = false; bool isRowError = false; List <InputError> errorRows = new List <InputError>(); char delimiter = (char)0; OwnerPayout payout = null; List <Reservation> reservations = null; List <Resolution> resolutions = null; DateTime today = DateTime.UtcNow; var propertyProvider = new PropertyProvider(_context); int row = 0; while (!sr.EndOfStream) { string line = sr.ReadLine(); row++; if (row < startRow) { continue; // first row is header } if (delimiter == (char)0) { delimiter = (line.Count(x => x == '\t') > 2 ? '\t' : ','); } string[] columns = ParseLine(line, delimiter, totalCols); if (columns.Count() != totalCols) { errorRows.Add(new InputError { InputSource = inputSource, Row = row, Section = "Parse", Message = string.Format("The total number of columns {0:d} does not match {1:d}", columns.Count(), totalCols), OriginalText = line, CreatedTime = DateTime.UtcNow }); } try { // exit loop if there is no transaction date on a row if (isCompleted && columns[payoutDateCol] == null) { if (payout != null && isOwnerRow && !isRowError) { QueuePayout(payout, reservations, resolutions); payoutCount++; payout = null; // start a new input section cycle } break; } string type = columns[typeCol]; string confirmationCode = columns[confirmationCodeCol]; var payoutType = type.StartsWith("Payout") ? PayoutType.Payout : (type.StartsWith("Reservation") ? PayoutType.Reservation : (type.StartsWith("Resolution") ? PayoutType.Resolution : PayoutType.Other)); if (payoutType == PayoutType.Payout) { // payout row following a reservation or resolution row start an input cycle, we queue up the payout if (isCompleted && payout != null && !isOwnerRow && !isRowError) { if (payoutStartDate == null || payout.PayoutDate.Value.Date > payoutStartDate.Value.Date) { QueuePayout(payout, reservations, resolutions); payoutCount++; } else { sr.Close(); break; // payout date comes in in ascending order; so we can skip dates before payoutStartDate } payout = null; // start a new input section cycle } // owner row following another owner row is a payout split, we add the amount up else if (isCompleted && payout != null && isOwnerRow && !isRowError) { payout.PayoutAmount += GetSafeNumber(columns[payoutCol]); continue; } isOwnerRow = true; isRowError = false; payout = new OwnerPayout(); reservations = new List <Reservation>(); resolutions = new List <Resolution>(); payout.PayoutDate = ConversionHelper.EnsureUtcDate(GetSafeDate(columns[payoutDateCol])); payout.AccountNumber = GetAccountNumber(columns[detailCol]); payout.Source = account; payout.PayoutAmount = GetSafeNumber(columns[payoutCol]); payout.CreatedDate = today; payout.ModifiedDate = today; payout.InputSource = inputSource; } else if (payoutType == PayoutType.Resolution || payoutType == PayoutType.Other) { if (!isCompleted && resolutions == null) { resolutions = new List <Resolution>(); } resolutionCount++; isOwnerRow = false; Resolution resolution = new Resolution(); resolution.ResolutionDate = (payout != null && payout.PayoutDate != null) ? payout.PayoutDate : ConversionHelper.EnsureUtcDate(GetSafeDate(columns[payoutDateCol])); resolution.ResolutionType = columns[typeCol]; resolution.ResolutionDescription = columns[detailCol]; resolution.ResolutionAmount = GetSafeNumber(columns[amountCol]); resolution.ConfirmationCode = columns[confirmationCodeCol]; resolution.Impact = string.Empty; // fill in later resolution.CreatedDate = today; resolution.ModifiedDate = today; resolution.InputSource = inputSource; resolution.ApprovalStatus = RevenueApprovalStatus.NotStarted; // resolution has confirmation code associated with if (!string.IsNullOrEmpty(resolution.ConfirmationCode)) { string listingTitle = columns[listingCol]; string propertyCode = propertyProvider.GetPropertyCodeByListing(account, Unquote(listingTitle)); resolution.PropertyCode = propertyCode; if (string.IsNullOrEmpty(resolution.ResolutionDescription)) { resolution.ResolutionDescription = listingTitle; } } // for future payout, link to if (!isCompleted) { resolution.OwnerPayoutId = 0; } if (resolutions != null) { resolutions.Add(resolution); } } else if (payoutType == PayoutType.Reservation) { if (!isCompleted && reservations == null) { reservations = new List <Reservation>(); } reservationCount++; isOwnerRow = false; Reservation r = new Reservation(); r.ListingTitle = columns[listingCol]; string propertyCode = propertyProvider.GetPropertyCodeByListing(account, Unquote(r.ListingTitle)); r.TransactionDate = (payout != null && payout.PayoutDate != null) ? payout.PayoutDate : ConversionHelper.EnsureUtcDate(GetSafeDate(columns[payoutDateCol])); r.PropertyCode = propertyCode; r.ConfirmationCode = columns[confirmationCodeCol]; r.CheckinDate = ConversionHelper.EnsureUtcDate(GetSafeDate(columns[checkinDateCol])); r.Nights = (int)GetSafeNumber(columns[nightCol]); r.CheckoutDate = r.CheckinDate.Value.AddDays(r.Nights); r.TotalRevenue = GetSafeNumber(columns[amountCol]); r.GuestName = columns[guestCol]; r.Reference = columns[referenceCol]; CurrencyType currency; if (Enum.TryParse(columns[currencyCol], true, out currency) == true) { r.Currency = currency; } else { r.Currency = CurrencyType.USD; } // non-input fields r.Source = account; r.Channel = channel; r.LocalTax = 0; r.DamageWaiver = 0; r.AdminFee = 0; r.PlatformFee = 0; r.TaxRate = 0; r.IsFutureBooking = isCompleted ? false : true; r.IncludeOnStatement = true; r.ApprovalStatus = RevenueApprovalStatus.NotStarted; // house keeping fields r.CreatedDate = today; r.ModifiedDate = today; r.InputSource = inputSource; if (!isCompleted) { r.OwnerPayoutId = 0; // link to Owner Payout placeholder record for future payout } // if property is not fund, we use a placehoder property so it can be fixed later if (string.IsNullOrEmpty(propertyCode)) { errorRows.Add(new InputError { InputSource = inputSource, Row = row, Section = "Reservation", Message = string.Format("Property from listing title '{0}' and account '{1}' does not exist", r.ListingTitle, account), OriginalText = line, CreatedTime = DateTime.UtcNow }); r.PropertyCode = AppConstants.DEFAULT_PROPERTY_CODE; //isRowError = true; //errorCount++; } if (r.TotalRevenue < 0) { errorRows.Add(new InputError { InputSource = inputSource, Row = row, Section = "Reservation", Message = string.Format("[PropertyCode] = '{0}' and [ConfirmationCode] = '{1}' [TotalRevenue] = {2}", r.PropertyCode, r.ConfirmationCode, r.TotalRevenue.ToString("C2")), OriginalText = "Excel row", CreatedTime = DateTime.UtcNow }); } if (reservations != null && !string.IsNullOrEmpty(r.PropertyCode)) { reservations.Add(r); } } } catch (Exception ex) { errorRows.Add(new InputError { InputSource = inputSource, Row = row, Section = "Exception", Message = "Data parse exception: " + ex.Message, OriginalText = line, CreatedTime = DateTime.UtcNow }); isRowError = true; errorCount++; } } try { // last one if (isCompleted & payout != null && !isRowError) { if (payoutStartDate == null || payout.PayoutDate.Value.Date > payoutStartDate.Value.Date) { QueuePayout(payout, reservations, resolutions); payoutCount++; } } // save reservations and resoutions for future payout if there is no error if (errorCount == 0 && !isCompleted) { if (resolutions != null && resolutions.Count() > 0) { _context.Resolutions.AddRange(resolutions); } if (reservations != null && reservations.Count() > 0) { _context.Reservations.AddRange(reservations); } _context.SaveChanges(); // Save Reservations } else if (errorCount == 0 && isCompleted) { _context.SaveChanges(); // save OwnerPayouts, Reservations, and Resolutions } // save process errors for resolution if (errorRows.Count > 0) { _context.InputErrors.AddRange(errorRows); _context.SaveChanges(); // save errors } } catch (Exception ex) { var inputError = new InputError { InputSource = inputSource, Row = 0, Section = "Exception", Message = "Data saving error: " + ex.Message, OriginalText = "Database saving", CreatedTime = DateTime.UtcNow }; _context.InputErrors.Add(inputError); _context.SaveChanges(); // save errors errorCount = 100000; // a large number } sr.Close(); } DeleteFileIfAllowed(csvFile); if (errorCount > 0) { return(-errorCount); } else if (isCompleted) { return(payoutCount); } else { return(reservationCount); } }
public OwnerStatementViewModel GetOwnerStatement(DateTime month, string propertyCode) { try { var ownerStatement = new OwnerStatementViewModel(); var utcMonth = ConversionHelper.EnsureUtcDate(month.AddDays(1)); var endMonth = ConversionHelper.MonthWithLastDay(month); var lastMonth = ConversionHelper.MonthWithLastDay(month.AddMonths(-1)); var propertyProvider = new PropertyProvider(_context); var property = propertyProvider.Retrieve(propertyCode); // remove these words from statement display if (property.Address != null) { property.Address = property.Address.Replace("COMBO:", "").Replace("ROOM:", "").Replace("MULTI:", ""); } else { property.Address = string.Empty; } var feeProvider = new PropertyFeeProvider(_context); var propertyFee = feeProvider.Retrieve(propertyCode, utcMonth); var isFixedCostModel = IsFixedCostModel(propertyFee) && FixedCostEffectiveDate(month); var balanceProvider = new PropertyBalanceProvider(_context); double carryOver = 0; if (lastMonth.Month < 8 && lastMonth.Year <= 2017) { var propertyBalance = balanceProvider.Retrieve(propertyCode, month.Month, month.Year); carryOver = propertyBalance == null ? 0 : propertyBalance.AdjustedBalance.Value; } else { var propertyBalance = balanceProvider.RetrieveCarryOvers(lastMonth.Month, lastMonth.Year, propertyCode).FirstOrDefault(); carryOver = propertyBalance == null ? 0 : propertyBalance.CarryOver; } var entityProvider = new PropertyEntityProvider(_context); var entityName = entityProvider.GetEntityName(propertyCode, endMonth); // banner data ownerStatement.StatementMonth = month; ownerStatement.OwnerName = entityName; ownerStatement.PropertyName = string.Format("{0} | {1}", property.PropertyCode, property.Address); ownerStatement.PropertyNameWithProduct = string.Format("{0}-{1} | {2}", property.PropertyCode, property.Vertical, property.Address); ownerStatement.IsFinalized = IsFinalized(month, propertyCode); ownerStatement.ApprovalSummary = GetApprovalStateText(month, propertyCode); // resolution data var resolutions = GetResolutionStatement(month, propertyCode); ownerStatement.ResolutionDetails = resolutions; ownerStatement.ResolutionsTotal = resolutions.Sum(x => x.TotalRevenue); // reservation data var reservations = GetReservationStatement(month, propertyCode); ownerStatement.ReservationDetails = reservations; ownerStatement.ReservationsTotal = reservations.Sum(x => x.TotalRevenue) + ownerStatement.ResolutionsTotal; var ExcludedTaxRevenue = GetExcludedTaxRevenue(reservations); // off-airbnb reservations for tax calculation ownerStatement.TaxCollected = reservations.Where(x => x.Channel != "Airbnb") .Sum(x => (x.TotalRevenue - ExcludedTaxRevenue) * (x.TaxRate == null ? 0 : x.TaxRate.Value)); // advance payment section ownerStatement.AdvancePaymentDetails = GetAdvancePayments(month, propertyCode); // owner & unit expenses section // special rule: maintenace expenses are rolled up to be with fixed cost if applicable var fixedUnitExpenses = new List <UnitExpenseStatement>(); ownerStatement.UnitExpenseDetails = GetUnitExpenses(month, propertyCode, isFixedCostModel); if (isFixedCostModel) { fixedUnitExpenses = GetUnitExpenses(month, GetFixedCostModelCount(reservations, month), propertyFee); if (fixedUnitExpenses.Count > 0) { if (UseGroundKeepingRule(month)) { if (UseCleaningCountRule(month)) { MergeExpenses(ownerStatement.UnitExpenseDetails, fixedUnitExpenses); } else { MergeGroundskeeping(ownerStatement.UnitExpenseDetails, fixedUnitExpenses); } } } } if (fixedUnitExpenses.Count > 0) { ownerStatement.UnitExpenseDetails.AddRange(fixedUnitExpenses); } // footer section ownerStatement.IsProductRS = (property.Vertical == "RS"); // statement section ownerStatement.NightsBooked = GetNightCount(reservations); ownerStatement.ReservationCount = reservations.Distinct().Count(); ownerStatement.TotalRevenue = ownerStatement.ReservationsTotal; ownerStatement.BeginBalance = carryOver; ownerStatement.CityTaxRate = propertyFee != null && propertyFee.CityTax != null ? propertyFee.CityTax.Value : 0; double managementFeeRate = (propertyFee == null || propertyFee.ManagementFee == null) ? 0.0 : propertyFee.ManagementFee.Value; ownerStatement.ManagementFeePercentage = managementFeeRate.ToString("P1", new NumberFormatInfo { PercentPositivePattern = 1, PercentNegativePattern = 1 }); if (ownerStatement.IsProductRS) { ownerStatement.CleaningFees = 0; ownerStatement.ManagementFees = -ownerStatement.TotalRevenue * managementFeeRate; } else { // special rule: cleaning fee for fixed cost also include special cleaning fees from expense table ownerStatement.CleaningFees = -GetCleanFees(month, propertyCode); // cleaning fee for one-off cleaning cost including 10% surcharge int fixedCostCount = GetFixedCostModelCount(reservations, month); // filter reservations that do not need cleaning if (isFixedCostModel && propertyFee.Cleanings != null && fixedCostCount > 0) { ownerStatement.CleaningFees += -Math.Round(fixedCostCount * propertyFee.Cleanings.Value * 1.1, 2); // mark up 10% on fixed clean fee } // all cleaning fees are accounted for by fixed cleaning rule above, so we remove it from unit expenses RemoveCleaningExpenses(ownerStatement.UnitExpenseDetails); // special rule: management fee = 0 if there is no revenue but has cleaning fee if ((ConversionHelper.ZeroMoneyValue(ownerStatement.TotalRevenue) && ownerStatement.CleaningFees < 0) || (ownerStatement.TotalRevenue + ownerStatement.CleaningFees) < 0.01) { ownerStatement.ManagementFees = 0; } else { ownerStatement.ManagementFees = -(ownerStatement.TotalRevenue + ownerStatement.CleaningFees) * managementFeeRate; } } ownerStatement.AdvancementPaymentsTotal = -ownerStatement.AdvancePaymentDetails.Sum(x => x.Amount); ownerStatement.UnitExpensesTotal = -ownerStatement.UnitExpenseDetails.Sum(x => x.Amount); ownerStatement.EndingBalance = Math.Round(ownerStatement.TotalRevenue, 2) + Math.Round(ownerStatement.BeginBalance, 2) + Math.Round(ownerStatement.TaxCollected, 2) + Math.Round(ownerStatement.CleaningFees, 2) + Math.Round(ownerStatement.ManagementFees, 2) + Math.Round(ownerStatement.UnitExpensesTotal, 2) + Math.Round(ownerStatement.AdvancementPaymentsTotal, 2); var note = _context.OwnerStatements.Where(x => x.Month == month.Month && x.Year == month.Year && x.PropertyCode == property.PropertyCode) .Select(x => x.StatementNotes) .FirstOrDefault(); var finalizedStatement = GetOwnerStatement(property.PropertyCode, month.Month, month.Year); if (finalizedStatement != null) { ownerStatement.StatementNotes = finalizedStatement.StatementNotes; ownerStatement.IsModified = IsStatementModified(finalizedStatement, ownerStatement); } return(ownerStatement); } catch { throw; // let caller handle the error } }
public int ImportFromExcel(string excelFile, DateTime reportDate, bool isCompleted) { int errorCount = 0; int payoutCount = 0; int reservationCount = 0; int resolutionCount = 0; string channel = "Airbnb"; string account = GetAccountFromFilename(excelFile); string inputSource = string.Format("{0} {1}", reportDate.ToString("yyyy-MM-dd"), GetInputSourceFromFilePath(excelFile)); string excelPath = Path.Combine(UrlHelper.DataRootUrl(), excelFile); if (!File.Exists(excelPath)) { var inputError = new InputError { InputSource = inputSource, Row = 0, Section = "Input File Check", Message = string.Format("Input file '{0}' does not exist.", excelPath), OriginalText = excelPath, CreatedTime = DateTime.UtcNow }; _context.InputErrors.Add(inputError); _context.SaveChanges(); // save errors return(-1000000); // large number of error } FileInfo excelFileInfo = new FileInfo(excelPath); using (var package = new ExcelPackage(excelFileInfo)) { ExcelWorkbook workBook = package.Workbook; if (workBook != null) { if (workBook.Worksheets.Count > 0) { int totalCols = 14; int startRow = 2; // starting row for reservation data int payoutDateCol = 1; int typeCol = 2; int confirmationCodeCol = 3; int checkinDateCol = 4; int nightCol = 5; int guestCol = 6; int listingCol = 7; int detailCol = 8; int referenceCol = 9; int currencyCol = 10; int amountCol = 11; int payoutCol = 12; //int hostFeeCol = 13; //int cleanFeeCol = 14; ExcelWorksheet currentWorksheet = workBook.Worksheets[1]; bool isOwnerRow = false; bool isRowError = false; List <InputError> errorRows = new List <InputError>(); OwnerPayout payout = null; List <Reservation> reservations = null; List <Resolution> resolutions = null; DateTime today = DateTime.UtcNow; PropertyProvider propertyProvider = new PropertyProvider(_context); for (int row = startRow; row <= currentWorksheet.Dimension.End.Row; row++) { if (currentWorksheet.Dimension.End.Column != totalCols) { errorRows.Add(new InputError { InputSource = inputSource, Row = row, Section = "Parse", Message = string.Format("The total number of columns {0:d} does not match {1:d}", currentWorksheet.Dimension.End.Column, totalCols), OriginalText = "Excel row", CreatedTime = DateTime.UtcNow }); } try { if (isCompleted && currentWorksheet.Cells[row, payoutDateCol].Value == null) { if (payout != null && isOwnerRow && !isRowError) { QueuePayout(payout, reservations, resolutions); payoutCount++; } break; // exit loop at the end of row } string type = GetSafeCellString(currentWorksheet.Cells[row, typeCol].Value); var payoutType = type.StartsWith("Payout") ? PayoutType.Payout : (type.StartsWith("Reservation") ? PayoutType.Reservation : (type.StartsWith("Resolution") ? PayoutType.Resolution : PayoutType.Other)); if (payoutType == PayoutType.Payout) { if (isCompleted && payout != null && !isOwnerRow && !isRowError) { QueuePayout(payout, reservations, resolutions); payoutCount++; } isOwnerRow = true; isRowError = false; payout = new OwnerPayout(); reservations = new List <Reservation>(); resolutions = new List <Resolution>(); payout.PayoutDate = GetSafeDate(currentWorksheet.Cells[row, payoutDateCol].Value); payout.PayoutDate = ConversionHelper.EnsureUtcDate(payout.PayoutDate); payout.AccountNumber = GetAccountNumber(currentWorksheet.Cells[row, detailCol].Value); payout.Source = account; payout.PayoutAmount = GetSafeNumber(currentWorksheet.Cells[row, payoutCol].Value); payout.CreatedDate = today; payout.ModifiedDate = today; payout.InputSource = inputSource; } else if (payoutType == PayoutType.Resolution) { // business rule: one resolution per reservastion resolutionCount++; isOwnerRow = false; Resolution resolution = new Resolution(); resolution.ResolutionDate = GetSafeDate(currentWorksheet.Cells[row, payoutDateCol].Value); resolution.ResolutionDate = ConversionHelper.EnsureUtcDate(resolution.ResolutionDate); resolution.ResolutionType = GetSafeCellString(currentWorksheet.Cells[row, typeCol].Value); resolution.ResolutionDescription = GetSafeCellString(currentWorksheet.Cells[row, detailCol].Value); resolution.ResolutionAmount = GetSafeNumber(currentWorksheet.Cells[row, amountCol].Value); resolution.ConfirmationCode = string.Empty; resolution.Impact = string.Empty; // fill in later resolution.CreatedDate = today; resolution.ModifiedDate = today; resolution.InputSource = inputSource; if (!isCompleted) { resolution.OwnerPayoutId = 0; // link to Owner Payout placeholder record for future payout } if (resolutions != null) { resolutions.Add(resolution); } } else if (payoutType == PayoutType.Reservation) { if (!isCompleted && reservations == null) { reservations = new List <Reservation>(); } reservationCount++; isOwnerRow = false; Reservation r = new Reservation(); r.ListingTitle = GetSafeCellString(currentWorksheet.Cells[row, listingCol].Value); string propertyCode = propertyProvider.GetPropertyCodeByListing(account, Unquote(r.ListingTitle)); r.TransactionDate = GetSafeDate(currentWorksheet.Cells[row, payoutDateCol].Value); r.TransactionDate = ConversionHelper.EnsureUtcDate(r.TransactionDate); r.PropertyCode = propertyCode; r.ConfirmationCode = GetSafeCellString(currentWorksheet.Cells[row, confirmationCodeCol].Value); r.CheckinDate = GetSafeDate(currentWorksheet.Cells[row, checkinDateCol].Value); r.CheckinDate = ConversionHelper.EnsureUtcDate(r.CheckinDate); r.Nights = (int)GetSafeNumber(currentWorksheet.Cells[row, nightCol].Value); r.CheckoutDate = r.CheckinDate.Value.AddDays(r.Nights); r.TotalRevenue = GetSafeNumber(currentWorksheet.Cells[row, amountCol].Value); //r.HostFee = GetSafeNumber(currentWorksheet.Cells[row, hostFeeCol].Value); // not needed per Jason //r.CleanFee = GetSafeNumber(currentWorksheet.Cells[row, cleanFeeCol].Value); // not needed per Jason r.GuestName = GetSafeCellString(currentWorksheet.Cells[row, guestCol].Value); r.Reference = GetSafeCellString(currentWorksheet.Cells[row, referenceCol].Value); CurrencyType currency; if (Enum.TryParse(GetSafeCellString(currentWorksheet.Cells[row, currencyCol].Value), true, out currency) == true) { r.Currency = currency; } else { r.Currency = CurrencyType.USD; } // non-input fields r.Source = account; r.Channel = channel; r.LocalTax = 0; r.DamageWaiver = 0; r.AdminFee = 0; r.PlatformFee = 0; r.TaxRate = 0; r.IsFutureBooking = isCompleted ? false : true; r.IncludeOnStatement = false; r.ApprovalStatus = RevenueApprovalStatus.NotStarted; // house keeping fields r.CreatedDate = today; r.ModifiedDate = today; r.InputSource = inputSource; if (!isCompleted) { r.OwnerPayoutId = 0; // link to Owner Payout placeholder record for future payout } // if property is not fund, we use a placehoder property so it can be fixed later if (string.IsNullOrEmpty(propertyCode)) { errorRows.Add(new InputError { InputSource = inputSource, Row = row, Section = "Reservation", Message = string.Format("Property from listing title '{0}' and account '{1}' does not exist", Unquote(r.ListingTitle), account), OriginalText = "Excel row", CreatedTime = DateTime.UtcNow }); r.PropertyCode = AppConstants.DEFAULT_PROPERTY_CODE; //isRowError = true; //errorCount++; } if (reservations != null && !string.IsNullOrEmpty(r.PropertyCode)) { reservations.Add(r); } } } catch (Exception ex) { errorRows.Add(new InputError { InputSource = inputSource, Row = row, Section = "Exception", Message = "Data parse exception: " + ex.Message, OriginalText = "Excel row", CreatedTime = DateTime.UtcNow }); isRowError = true; errorCount++; } } try { // save reservations and resoutions for future payout if there is no error if (errorCount == 0 && !isCompleted) { if (resolutions != null && resolutions.Count() > 0) { _context.Resolutions.AddRange(resolutions); } if (reservations != null && reservations.Count() > 0) { _context.Reservations.AddRange(reservations); } _context.SaveChanges(); // Save Reservations } else if (errorCount == 0 && isCompleted) { _context.SaveChanges(); // save OwnerPayouts, Reservations, and Resolutions } if (errorRows.Count > 0) { _context.InputErrors.AddRange(errorRows); _context.SaveChanges(); // Save errors } } catch (Exception ex) { var inputError = new InputError { InputSource = inputSource, Row = 0, Section = "Exception", Message = "Data saving error: " + ex.Message, OriginalText = "Database saving", CreatedTime = DateTime.UtcNow }; _context.InputErrors.Add(inputError); _context.SaveChanges(); // save errors errorCount = 100000; // a large number } } } } try { excelFileInfo.Delete(); } catch { // ignore if cannot delete } if (errorCount > 0) { return(-errorCount); } else if (isCompleted) { return(payoutCount); } else { return(reservationCount); } }