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