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