public void UpdateNextMonthBalances(DateTime month)
{
try
{
DateTime comingMonth = month.AddMonths(1);
int nextMonth = comingMonth.Month;
int nextYear = comingMonth.Year;
// get the balance carryover from owner statements
var balanceProvider = new PropertyBalanceProvider(_context);
var carryovers = RetrieveCarryOvers(month.Month, month.Year);
// insert or update property balances
foreach (var carryover in carryovers)
{
var balance = _context.PropertyBalances.Where(b => b.PropertyCode == carryover.PropertyCode && b.Month == nextMonth && b.Year == nextYear)
.FirstOrDefault();
if (balance == null)
{
balance = new PropertyBalance();
MapData(carryover, ref balance, nextMonth, nextYear);
balanceProvider.Create(balance);
}
else
{
MapData(carryover, ref balance, nextMonth, nextYear);
balanceProvider.Update(balance.PropertyBalanceId, balance);
}
}
// we carry over those properties that are not active for next month from current month
var nextMonthBalances = _context.PropertyBalances.Where(b => b.Month == nextMonth && b.Year == nextYear).ToList();
if (nextMonthBalances != null && nextMonthBalances.Count > 0)
{
var lastMonthBalances = _context.PropertyBalances.Where(b => b.Month == month.Month && b.Year == month.Year).ToList();
foreach (var balance in lastMonthBalances)
{
var matchBalance = nextMonthBalances.Where(x => x.PropertyCode == balance.PropertyCode).FirstOrDefault();
if (matchBalance == null)
{
var missingBalance = new PropertyBalance();
missingBalance.BeginningBalance = balance.AdjustedBalance;
missingBalance.AdjustedBalance = balance.AdjustedBalance;
missingBalance.PropertyCode = balance.PropertyCode;
missingBalance.Month = nextMonth;
missingBalance.Year = nextYear;
balanceProvider.Create(missingBalance);
}
}
}
balanceProvider.Commit();
}
catch
{
throw;
}
}
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
}
}
