public OwnerStatement Retrieve(OwnerStatementViewModel model)
{
return(_context.OwnerStatements.Where(s => s.PropertyCode == model.PropertyName &&
s.Month == model.StatementMonth.Month &&
s.Year == model.StatementMonth.Year)
.FirstOrDefault());
}
private bool StatementExist(OwnerStatementViewModel model)
{
return(_context.OwnerStatements.Where(s => s.PropertyCode == model.PropertyName &&
s.Month == model.StatementMonth.Month &&
s.Year == model.StatementMonth.Year)
.Count() > 0);
}
public bool IsStatementModified(OwnerStatement finalizedStatement, OwnerStatementViewModel currentStatement)
{
return(!ConversionHelper.MoneyEqual(finalizedStatement.TotalRevenue, currentStatement.TotalRevenue) ||
!ConversionHelper.MoneyEqual(finalizedStatement.BeginBalance, currentStatement.BeginBalance) ||
!ConversionHelper.MoneyEqual(finalizedStatement.CleaningFees, currentStatement.CleaningFees) ||
!ConversionHelper.MoneyEqual(finalizedStatement.AdvancePayments, currentStatement.AdvancementPaymentsTotal) ||
!ConversionHelper.MoneyEqual(finalizedStatement.Balance, currentStatement.EndingBalance) ||
!ConversionHelper.MoneyEqual(finalizedStatement.ManagementFees, currentStatement.ManagementFees) ||
!ConversionHelper.MoneyEqual(finalizedStatement.TaxCollected, currentStatement.TaxCollected));
}
private OwnerStatementSummaryItem MapOwnerStatementToSummaryItem(OwnerStatementViewModel ownerStatement)
{
var summaryItem = new OwnerStatementSummaryItem();
summaryItem.BeginBalance = ownerStatement.BeginBalance;
summaryItem.CleaningFees = ownerStatement.CleaningFees;
summaryItem.ManagementFees = ownerStatement.ManagementFees;
summaryItem.UnitExpenseItems = ownerStatement.UnitExpensesTotal;
summaryItem.TaxCollected = ownerStatement.TaxCollected;
summaryItem.AdvancePayments = ownerStatement.AdvancementPaymentsTotal;
summaryItem.TotalRevenue = ownerStatement.TotalRevenue;
summaryItem.Payout = ownerStatement.Payout;
summaryItem.EndingBalance = ownerStatement.EndingBalance;
return(summaryItem);
}
public void MapData(OwnerStatementViewModel from, ref OwnerStatement to)
{
to.TotalRevenue = Math.Round(from.TotalRevenue, 2);
to.TaxCollected = Math.Round(from.TaxCollected, 2);
to.CleaningFees = Math.Round(from.CleaningFees, 2);
to.ManagementFees = Math.Round(from.ManagementFees, 2);
to.UnitExpenseItems = Math.Round(from.UnitExpensesTotal, 2);
to.AdvancePayments = Math.Round(from.AdvancementPaymentsTotal, 2);
to.BeginBalance = Math.Round(from.BeginBalance, 2);
to.Balance = Math.Round(from.EndingBalance, 2);
to.PropertyCode = from.PropertyName;
to.Month = from.StatementMonth.Month;
to.Year = from.StatementMonth.Year;
to.StatementStatus = from.IsFinalized ? StatementStatus.Finalized : StatementStatus.Approved;
to.FinalizedBy = HttpContext.Current.User.Identity.Name;
to.FinalizedDate = from.IsFinalized ? ConversionHelper.EnsureUtcDate(new DateTime(to.Year, to.Month, 15).AddMonths(1)) : (DateTime?)null;
}
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 OwnerStatementSummaryModel GetOwnerSummary(DateTime month, string payoutMethod, bool redo)
{
try
{
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter("@PayoutMethod", SqlDbType.NVarChar);
sqlParams[0].Value = payoutMethod;
var ownerProperties = _context.Database.SqlQuery <OwnerPropertyModel>("GetPropertiesForOwnerSummary @PayoutMethod", sqlParams).ToList();
// get paid payout amount
var paymentProvider = new OwnerPaymentProvider(_context);
double?paidPayout = paymentProvider.GetMonthlyPayout(month, payoutMethod);
var statementProvider = new OwnerStatementProvider(_context);
var ownerSummary = new OwnerStatementSummaryModel();
// banner data
ownerSummary.StatementMonth = month;
ownerSummary.OwnerName = payoutMethod;
ownerSummary.PaidPayout = paidPayout == null ? 0 : paidPayout.Value;
ownerSummary.TotalPayout = 0;
// sum up owner statement belonging to the summary if the statement has been finalized
int signFlag = 0;
var entityProvider = new PropertyEntityProvider(_context);
foreach (var property in ownerProperties)
{
var model = new OwnerStatementViewModel(_context)
{
PropertyName = property.PropertyCode,
StatementMonth = month
};
var ownerStatement = statementProvider.Retrieve(model);
if (ownerStatement != null && ownerStatement.StatementStatus == StatementStatus.Finalized)
{
var summaryItem = MapOwnerStatementToSummaryItem(ownerStatement);
summaryItem.Address = property.Address;
summaryItem.PropertyID = property.PropertyCode + '-' + property.Vertical;
summaryItem.EntityName = entityProvider.GetEntityName(property.PropertyCode, model.StatementMonth);
summaryItem.PayoutMethod = payoutMethod;
ownerSummary.SummaryItems.Add(summaryItem);
signFlag |= summaryItem.EndingBalance > 0 ? 0x0001 : (summaryItem.EndingBalance < 0 ? 0x0002 : 0);
}
}
// retrive statement summary row if it exist; otherwise compute it
var summaryRowModel = new OwnerStatementViewModel(_context)
{
PropertyName = payoutMethod,
StatementMonth = month
};
var summaryRow = statementProvider.Retrieve(summaryRowModel);
if (summaryRow != null)
{
ownerSummary.SummaryNotes = summaryRow.StatementNotes;
}
if (!redo && summaryRow != null) // get it from the record stored in ownerstatement table if exists
{
ownerSummary.SetSumTotal(summaryRow);
ownerSummary.IsFinalized = summaryRow.StatementStatus == StatementStatus.Finalized;
}
else // compute it
{
ownerSummary.SetSumTotal();
if (!redo)
{
ownerSummary.IsFinalized = false;
}
}
// if the summary has been finalized, we check if the total of all statements matches
if (ownerSummary.IsFinalized)
{
var itemTotal = ownerSummary.SumTotal(ownerSummary.SummaryItems); // the total of all the statements
ownerSummary.IsModified = IsSummaryModified(ownerSummary.ItemTotal, itemTotal);
}
ownerSummary.IsRebalanced = !(ownerSummary.TotalPayout < 0 && (signFlag & 0x0003) == 0x0003);
return(ownerSummary);
}
catch (Exception ex)
{
throw; // let caller handle the error
}
}