public int Update(PropertyCodeViewModel model)
{
try
{
// 3 relation tables to update: PropertyPayoutMethods, PropertyCodePropertyEntities, and PropertyAccountPayoutMethods
var payoutMethodProvider = new PayoutMethodProvider(_context);
int updateCount = payoutMethodProvider.UpdatePropertyCodeByName(model.PayoutMethod, model.PropertyCode);
var entityProvider = new PropertyEntityProvider(_context);
updateCount += entityProvider.UpdatePropertyCodeByName(model.PayoutEntity, model.PropertyCode);
var accountProvider = new PropertyAccountProvider(_context);
updateCount += accountProvider.UpdatePayoutMethodByOwner(model.PropertyOwner, model.PayoutMethod);
return(updateCount);
}
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
}
}
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
}
}
