public async Task CalculateExpenses(decimal contractTotal, BillingExpenseData data, IList <BillingExpense> billingExpenses)
{
var summaryRows = data.Summary.Rows;
var headerRows = data.Header.Rows;
data.Sections.SelectMany(s => s.Rows).ForEach(r => r.Total = GetTotal(r.Cells));
var financialYears = data.FinancialYears;
foreach (var section in data.Sections)
{
for (var y = 0; y < financialYears.Count; y++)
{
section.Totals?.Cells.Add(GetColumnTotal(section, y));
}
section.Totals.Total = section.Totals.Cells.Sum(c => c.Value);
}
var noOfMonthsInFy = data.Header.Rows.First(r => r.Key == Constants.BillingExpenseItem.NumberOfMonthsFY);
var totalMonths = (int)noOfMonthsInFy.Cells.Sum(fy => fy.Value);
for (var y = 0; y < financialYears.Count; y++)
{
foreach (var row in summaryRows)
{
CalculateSummaryValue(row, y, (int)noOfMonthsInFy.Cells[y].Value, totalMonths, data, contractTotal);
}
}
foreach (var row in summaryRows)
{
row.Total = GetTotal(row.Cells);
}
var balancePrepaid = headerRows.First(row => row.Key == Constants.BillingExpenseItem.BalancePrepaid);
var balanceToBeMoved = summaryRows.First(row => row.Key == Constants.BillingExpenseSummaryItem.BalanceToBeMoved);
var currentBalance = 0M;
for (var y = 0; y < financialYears.Count; y++)
{
//Use the previous balance with the current balancePrepaid.
var year = financialYears[y];
balancePrepaid.Cells.Add(new BillingExpenseItem
{
Value = currentBalance,
Year = year,
ReadOnly = true,
Key = Constants.BillingExpenseItem.BalancePrepaid,
SectionKey = Constants.BillingExpenseSection.Header
});
currentBalance = balanceToBeMoved.Cells[y].Value;
}
foreach (var row in headerRows)
{
row.Total = GetTotal(row.Cells);
}
}
public async Task <ICollection <CostLineItem> > InterpolateCostLineItems(
Guid costId,
Guid costStageRevisionId,
BillingExpenseData billingExpense)
{
/*
* Base Compensation Total feeds into "Base Compensation" line of Cost Section
* Pension & Health Total feeds into "Pension & Health" line of Cost Section
* Bonus Total feeds into “Bonus" line of Cost Section
* Agency Fee Total feeds into "Negotiation/broker agency fee
* Other incurred Costs Total feeds into "Other services and fees"
*/
var interpolatedItems = new List <CostLineItem>();
var stageForm = await _costStageRevisionService.GetStageDetails <PgStageDetailsForm>(costStageRevisionId);
string contentType = stageForm.GetContentType();
string production = stageForm.GetProductionType();
var cost = await _efContext.Cost
.Include(c => c.CostTemplateVersion)
.ThenInclude(ctv => ctv.CostTemplate)
.ThenInclude(ct => ct.FieldDefinitions)
.FirstOrDefaultAsync(c => c.Id == costId);
var costStageRevision = await _efContext.CostStageRevision
.Include(c => c.CostLineItems)
.FirstOrDefaultAsync(c => c.Id == costStageRevisionId);
var paymentCurrency = await _efContext.Currency.FirstOrDefaultAsync(c => c.Code == stageForm.AgencyCurrency);
var templateModel = await _costTemplateVersionService.GetCostTemplateVersionModel(cost.CostTemplateVersionId);
var form = _costSectionFinder.GetCostSection(templateModel, contentType, production).Result;
var costLineItems = costStageRevision.CostLineItems;
var baseCompensation = GetOrCreateCostLineItem(form, costLineItems, "baseCompensation", paymentCurrency);
var pensionAndHealth = GetOrCreateCostLineItem(form, costLineItems, "pensionAndHealth", paymentCurrency);
var bonusCelebrityOnly = GetOrCreateCostLineItem(form, costLineItems, "bonusCelebrityOnly", paymentCurrency);
var negotiationBrokerAgencyFee = GetOrCreateCostLineItem(form, costLineItems, "negotiationBrokerAgencyFee", paymentCurrency);
var otherServicesAndFees = GetOrCreateCostLineItem(form, costLineItems, "otherServicesAndFees", paymentCurrency);
interpolatedItems.AddRange(new[] { baseCompensation, pensionAndHealth, bonusCelebrityOnly, negotiationBrokerAgencyFee, otherServicesAndFees });
baseCompensation.ValueInLocalCurrency = GetBillingExpenseItemValue(billingExpense, Constants.BillingExpenseItem.UsageBuyoutFee);
pensionAndHealth.ValueInLocalCurrency = GetBillingExpenseItemValue(billingExpense, Constants.BillingExpenseItem.PensionAndHealth);
bonusCelebrityOnly.ValueInLocalCurrency = GetBillingExpenseItemValue(billingExpense, Constants.BillingExpenseItem.Bonus);
negotiationBrokerAgencyFee.ValueInLocalCurrency = GetBillingExpenseItemValue(billingExpense, Constants.BillingExpenseItem.AgencyFee);
otherServicesAndFees.ValueInLocalCurrency = GetBillingExpenseItemValue(billingExpense, Constants.BillingExpenseItem.OtherCosts);
var exchangeRates = await _costExchangeRateService.GetExchangeRatesByDefaultCurrency(costStageRevision.CostStage.CostId);
UpdateDefaultCurrency(interpolatedItems, exchangeRates);
return(interpolatedItems);
}
private static decimal GetBillingExpenseItemValue(BillingExpenseData billingExpense, string itemKey)
{
var row = billingExpense.Sections.SelectMany(s => s.Rows).FirstOrDefault(r => r.Key == itemKey);
return(row.Total);
}
public BillingExpenseData BuildExpenses(CostStage costStage, IList <BillingExpense> billingExpenses, IList <FinancialYear> financialYears)
{
var billingExpensesLookup = CreateLookup(billingExpenses);
var data = new BillingExpenseData();
financialYears.ForEach(fy => data.FinancialYears.Add(fy.ShortName));
data.Sections.Add(new BillingExpenseDataSection
{
Key = Constants.BillingExpenseSection.ContractTerms,
Label = "Billing to P&G for contract terms"
});
data.Sections.Add(new BillingExpenseDataSection
{
Key = Constants.BillingExpenseSection.IncurredCosts,
Label = "Incurred costs, pre, bonus payments, etc."
});
data.Header.Rows.Add(new BillingExpenseRow
{
Key = Constants.BillingExpenseItem.BalancePrepaid,
Label = "BALANCE FROM PRIOR FY/PREPAID",
Type = "currency"
});
var noOfMonthsRow = new BillingExpenseRow
{
Key = Constants.BillingExpenseItem.NumberOfMonthsFY,
Label = "No. of months in contract term per FY",
Type = "number"
};
data.Header.Rows.Add(noOfMonthsRow);
data.Sections[0].Rows.Add(new BillingExpenseRow
{
Key = Constants.BillingExpenseItem.UsageBuyoutFee,
Label = "Base Compensation",
Type = "currency"
});
data.Sections[0].Rows.Add(new BillingExpenseRow
{
Key = Constants.BillingExpenseItem.PensionAndHealth,
Label = "Pension & Health",
Type = "currency"
});
data.Sections[1].Rows.Add(new BillingExpenseRow
{
Key = Constants.BillingExpenseItem.Bonus,
Label = "Bonus",
Type = "currency"
});
data.Sections[1].Rows.Add(new BillingExpenseRow
{
Key = Constants.BillingExpenseItem.AgencyFee,
Label = "Agency fee (PRE or other)",
Type = "currency"
});
data.Sections[1].Rows.Add(new BillingExpenseRow
{
Key = Constants.BillingExpenseItem.OtherCosts,
Label = "Other incurred costs (including non-reclaimable taxes)",
Type = "currency"
});
data.Sections[0].Totals = new BillingExpenseTotalRow
{
SectionKey = Constants.BillingExpenseSection.ContractTerms,
Key = Constants.BillingExpenseSectionTotal.ContractTerms,
Label = "Total billing for contract terms"
};
data.Sections[1].Totals = new BillingExpenseTotalRow
{
SectionKey = Constants.BillingExpenseSection.IncurredCosts,
Key = Constants.BillingExpenseSectionTotal.IncurredCosts,
Label = "Total billing for incurred costs, bonuses, etc."
};
financialYears.ForEach(year =>
{
data.Sections.ForEach(section =>
{
section.Rows.ForEach(row =>
{
if (row.Key != Constants.BillingExpenseItem.NumberOfMonthsFY)
{
row.Cells.Add(
GetOrCreateItem(billingExpensesLookup, section.Key, row.Key, year.ShortName)
);
}
});
});
});
var totalMonths = 0;
financialYears.ForEach(year =>
{
noOfMonthsRow.Cells.Add(GetNumberOfMonthsItem(billingExpensesLookup, costStage, year));
totalMonths += year.Months;
});
noOfMonthsRow.Total = totalMonths;
data.Summary.Rows.Add(new BillingExpenseCalculatedRow
{
Key = Constants.BillingExpenseSummaryItem.TotalContractTermsAndIncurredCosts,
Label = "Total billing for contract terms & bonuses, etc.",
Type = "currency"
});
data.Summary.Rows.Add(new BillingExpenseCalculatedRow
{
Key = Constants.BillingExpenseSummaryItem.ExpensePerFY,
Label = "Expense per FY",
Type = "currency"
});
data.Summary.Rows.Add(new BillingExpenseCalculatedRow
{
Key = Constants.BillingExpenseSummaryItem.BalanceToBeMoved,
Label = "Balance to be moved to prepaid",
Type = "currency"
});
return(data);
}
private void CalculateSummaryValue(BillingExpenseCalculatedRow row, int yearIndex, int monthsInYear, int totalMonths, BillingExpenseData data, decimal targetBudgetAmount)
{
var total = 0M;
switch (row.Key)
{
case Constants.BillingExpenseSummaryItem.TotalContractTermsAndIncurredCosts:
total = CalculateContractTermsAndIncurredCosts(yearIndex, data.Sections);
break;
case Constants.BillingExpenseSummaryItem.ExpensePerFY:
total = CalculateExpensePerFinancialYear(totalMonths, monthsInYear, targetBudgetAmount, yearIndex, data.Sections);
break;
case Constants.BillingExpenseSummaryItem.BalanceToBeMoved:
total = CalculateBalanceToBeMoved(yearIndex, data.Summary);
break;
}
row.Cells.Add(new BillingExpenseCalculatedItem(total));
}
