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