2636 lines
148 KiB
C#
2636 lines
148 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Collections.ObjectModel;
|
|
using System.Data.Entity;
|
|
using System.Data.Entity.Core.Objects;
|
|
using System.Linq;
|
|
using System.Web;
|
|
using EnVisage.Models;
|
|
using Microsoft.AspNet.Identity;
|
|
using System.Data.Entity.Infrastructure;
|
|
using NLog;
|
|
|
|
namespace EnVisage.Code.BLL
|
|
{
|
|
public class ScenarioManager : ManagerBase<Scenario, ScenarioModel>
|
|
{
|
|
public Guid[] materialsECs;
|
|
#region sub classes
|
|
public class AdditionalExpCatItem
|
|
{
|
|
public Guid ExpenditureCategoryId { get; set; }
|
|
public DateTime LastUpdate { get; set; }
|
|
public ExpenditureCategoryModel.CategoryTypes CategoryType { get; set; }
|
|
public decimal Quantity { get; set; }
|
|
public decimal DetailCost { get; set; }
|
|
public int? SortOrder { get; set; }
|
|
public ExpenditureCategoryModel.UseTypes UseType { get; set; }
|
|
public int WeekOrdinal { get; set; }
|
|
public ExpenditureCategoryModel.CgEfx CG_EFX { get; set; }
|
|
public string CategoryName { get; set; }
|
|
public Guid? SysField1 { get; set; }
|
|
public Guid SysField2 { get; set; }
|
|
public Guid? CreditId { get; set; }
|
|
public Guid? GlId { get; set; }
|
|
public Guid? UOMId { get; set; }
|
|
}
|
|
public class ScenarioDetailsListItem
|
|
{
|
|
public Guid Id { get; set; }
|
|
public Guid ParentId { get; set; }
|
|
public Guid ExpenditureCategoryId { get; set; }
|
|
public DateTime? WeekEndingDate { get; set; }
|
|
public decimal Quantity { get; set; }
|
|
public DateTime LastUpdate { get; set; }
|
|
public int WeekOrdinal { get; set; }
|
|
public decimal DetailCost { get; set; }
|
|
public string ExpenditureCategoryName { get; set; }
|
|
public Guid? GlId { get; set; }
|
|
public Guid? UOMId { get; set; }
|
|
public Guid? CreditId { get; set; }
|
|
public ExpenditureCategoryModel.CategoryTypes CategoryType { get; set; }
|
|
public ExpenditureCategoryModel.UseTypes UseType { get; set; }
|
|
public ExpenditureCategoryModel.CgEfx CG_EFX { get; set; }
|
|
public Guid? SysField1 { get; set; }
|
|
public Guid SysField2 { get; set; }
|
|
public int? SortOrder { get; set; }
|
|
}
|
|
public class TemporaryCalculationResults
|
|
{
|
|
public decimal mvActualCostCG_Fee { get; set; }
|
|
public decimal mvActualCostEFX_Fee { get; set; }
|
|
public decimal mvActualCostCG_LM { get; set; }
|
|
public decimal mvActualCostEFX_LM { get; set; }
|
|
public decimal mvActualCostCG { get; set; }
|
|
public decimal mvActualCostEFX { get; set; }
|
|
public decimal mvUsedCostCG { get; set; }
|
|
public decimal mvUsedCostEFX { get; set; }
|
|
public decimal mvUsedCostCG_LM { get; set; }
|
|
public decimal mvUsedCostEFX_LM { get; set; }
|
|
public decimal lvCGFactor { get; set; }
|
|
public decimal lvEFXFactor { get; set; }
|
|
}
|
|
public class CalculatedExpenditureCategoryItem
|
|
{
|
|
public Guid? ExpenditureId { get; set; }
|
|
public int ProcessOrder { get; set; }
|
|
public decimal Factor { get; set; }
|
|
public int FactorType { get; set; }
|
|
|
|
public static Dictionary<Guid, List<CalculatedExpenditureCategoryItem>> LoadAllCalculatedCategories(EnVisageEntities dbContext)
|
|
{
|
|
var query = dbContext.VW_Expenditure2Calculation.OrderBy(t => t.ParentId).ThenBy(t => t.ProcessOrder);
|
|
var parentId = Guid.Empty;
|
|
var firstTime = false;
|
|
var calcCategories = new List<CalculatedExpenditureCategoryItem>();
|
|
var result = new Dictionary<Guid, List<CalculatedExpenditureCategoryItem>>();
|
|
foreach (var expenditure2Calculation in query)
|
|
{
|
|
if (expenditure2Calculation.ParentId != parentId)
|
|
{
|
|
if (firstTime)
|
|
{
|
|
firstTime = false;
|
|
calcCategories = new List<CalculatedExpenditureCategoryItem>();
|
|
}
|
|
else
|
|
{
|
|
result.Add(parentId, calcCategories);
|
|
calcCategories = new List<CalculatedExpenditureCategoryItem>();
|
|
}
|
|
parentId = expenditure2Calculation.ParentId;
|
|
}
|
|
calcCategories.Add(new CalculatedExpenditureCategoryItem
|
|
{
|
|
ExpenditureId = expenditure2Calculation.ExpenditureCategoryID,
|
|
ProcessOrder = expenditure2Calculation.ProcessOrder ?? 0,
|
|
Factor = expenditure2Calculation.FactorInt ?? 0,
|
|
FactorType = expenditure2Calculation.FactorType ?? 0
|
|
});
|
|
}
|
|
result.Add(parentId, calcCategories);
|
|
return result;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
public ScenarioManager(EnVisageEntities dbContext)
|
|
: base(dbContext)
|
|
{
|
|
}
|
|
protected override Scenario InitInstance()
|
|
{
|
|
return new Scenario {
|
|
Id = Guid.NewGuid(),
|
|
LastUpdate = DateTime.Now
|
|
};
|
|
}
|
|
|
|
protected override Scenario RetrieveReadOnlyById(Guid key)
|
|
{
|
|
return DataTable.AsNoTracking().FirstOrDefault(t => t.Id == key);
|
|
}
|
|
|
|
public override DbSet<Scenario> DataTable
|
|
{
|
|
get
|
|
{
|
|
return DbContext.Scenarios;
|
|
}
|
|
}
|
|
|
|
public IList<ScenarioModel.ExpenditureItem> GetExpenditureCategories(Guid? templateId)
|
|
{
|
|
if (templateId == null || templateId == Guid.Empty)
|
|
return new ScenarioModel.ExpenditureItem[0];
|
|
|
|
var list = new List<ScenarioModel.ExpenditureItem>();
|
|
var categories = DbContext.VW_ExpCategoriesInScenario.Where(t => t.ScenarioID == templateId).ToArray();
|
|
var lstIds = categories.Select(t => t.Id).ToList();
|
|
|
|
// add checked expenditure categories from template
|
|
list.AddRange(DbContext.VW_Expenditure2Category.Where(t => lstIds.Contains(t.Id))
|
|
.OrderBy(t => t.ExpenditureName)
|
|
.Select(t => new ScenarioModel.ExpenditureItem
|
|
{
|
|
Id = t.Id,
|
|
Group = Constants.EXPENDITURE_INTEMPLATE_TITLE,
|
|
Name = t.ExpenditureName,
|
|
Checked = true
|
|
}).ToArray());
|
|
|
|
var arrCalculatedIds = categories.Where(t => t.UseType == (int) ExpenditureCategoryModel.UseTypes.Calculated).Select(t => t.Id).ToArray();
|
|
//var additionalExpCatsP = new List<AdditionalExpCatItem>();
|
|
|
|
if (arrCalculatedIds.Length > 0)
|
|
{
|
|
foreach (var item in DbContext.VW_Expenditure2Calculation.Where(t=> arrCalculatedIds.Contains(t.ParentId)))
|
|
{
|
|
if (!item.ExpenditureCategoryID.HasValue)
|
|
continue;
|
|
if (!lstIds.Contains(item.ExpenditureCategoryID.Value))
|
|
lstIds.Add(item.ExpenditureCategoryID.Value);
|
|
//additionalExpCatsP.Add(new AdditionalExpCatItem
|
|
// {
|
|
// ExpCatId = item.ExpenditureCategoryID.Value,
|
|
// LastUpdateDate = DateTime.Now,
|
|
// CategoryType = (ExpenditureCategoryModel.CategoryTypes)(item.Type ?? 0),
|
|
// Quantity = 0,
|
|
// DetailCost = 0,
|
|
// SortOrder = item.SortOrder ?? 0,
|
|
// UseType = (ExpenditureCategoryModel.UseTypes)(item.UseType ?? 1),
|
|
// WeekOrdinal = 0,
|
|
// CG_EFX = item.CGEFX,
|
|
// CategoryName = item.ExpenditureName,
|
|
// CreditId = item.CreditId,
|
|
// GlId = item.GLId,
|
|
// SysField1 = item.SystemAttributeOne,
|
|
// SysField2 = Guid.Empty,
|
|
// UOMId = item.UOMId
|
|
// });
|
|
}
|
|
}
|
|
// add not checked expenditure categories for Labor/Materials/Usage types
|
|
list.AddRange(DbContext.VW_Expenditure2Category.Where(t => !lstIds.Contains(t.Id))
|
|
.OrderBy(t => t.Type)
|
|
.ThenBy(t => t.ExpenditureName)
|
|
.Select(t => new
|
|
{
|
|
t.Id,
|
|
t.ExpenditureName,
|
|
t.Type
|
|
}).ToArray()
|
|
.Select(t => new ScenarioModel.ExpenditureItem
|
|
{
|
|
Id = t.Id,
|
|
Group = ((ExpenditureCategoryModel.CategoryTypes) (t.Type ?? 0)).ToDisplayValue(),
|
|
Name = t.ExpenditureName,
|
|
Checked = false
|
|
}));
|
|
|
|
return list;
|
|
}
|
|
|
|
public void SaveExpenditureCategories(Guid? scenarioId, IList<ExpenditureModel> expenditures)
|
|
{
|
|
if (scenarioId == null)
|
|
throw new ArgumentNullException("scenarioId");
|
|
|
|
#region prepare scenario object
|
|
Scenario dbObj = null;
|
|
if (scenarioId.HasValue && scenarioId.Value != Guid.Empty)
|
|
{
|
|
dbObj = DataTable.Find(scenarioId);
|
|
//model.TemplateId = dbObj.TemplateId ?? Guid.Empty;
|
|
//model.PriorWeekCutOff = model.AllowAdjustment ? new DateTime(1753, 1, 1) : model.PriorWeekCutOff;
|
|
//model.StartDate = model.AllowAdjustment ? model.StartDate : dbObj.StartDate;
|
|
}
|
|
//if (dbObj == null)
|
|
//{
|
|
// dbObj = InitInstance();
|
|
//}
|
|
//model.CopyTo(dbObj);
|
|
#endregion
|
|
|
|
#region Load referenced objects from DB
|
|
|
|
var project = DbContext.Projects.Find(dbObj.ParentId);
|
|
if (project == null)
|
|
throw new BLLException("Unable to find selected project. Please, reload the page or try again later.");
|
|
|
|
var FiscalCalendars = DbContext.FiscalCalendars.Where(
|
|
t =>
|
|
t.Type == (int)FiscalCalendarModel.FiscalYearType.Week && t.EndDate >= dbObj.StartDate &&
|
|
t.EndDate <= dbObj.EndDate && t.NonWorking == 0)
|
|
.OrderBy(t => t.EndDate).ToArray();
|
|
var rates = new List<Rate>();
|
|
var gRates = new List<Rate>();
|
|
var lstDctEcAddOn = new List<AdditionalExpCatItem>();
|
|
var lstExpCatsToExclude = new List<Guid>(); //this variable will be used to keep track of expenditure categories defined in the template that are not selected...
|
|
|
|
#region load referenced data for existing scenario
|
|
|
|
rates = DbContext.Rates.Where(
|
|
t => t.Type == (short)RateModel.RateType.Derived && t.ParentId == dbObj.ParentId)
|
|
.OrderBy(t => t.ExpenditureCategoryId)
|
|
.ThenBy(t => t.EndDate).ToList();
|
|
var lsExpCatsForLRates = rates.Select(t => t.ExpenditureCategoryId).ToArray();
|
|
|
|
gRates = lsExpCatsForLRates.Length > 0
|
|
? DbContext.Rates.Where(t => t.Type == (short)RateModel.RateType.Global &&
|
|
!lsExpCatsForLRates.Contains(t.ExpenditureCategoryId))
|
|
.OrderBy(t => t.ExpenditureCategoryId)
|
|
.ThenBy(t => t.EndDate)
|
|
.ToList()
|
|
: DbContext.Rates.Where(t => t.Type == (short)RateModel.RateType.Global)
|
|
.OrderBy(t => t.ExpenditureCategoryId)
|
|
.ThenBy(t => t.EndDate)
|
|
.ToList();
|
|
var list = new List<AdditionalExpCatItem>();
|
|
var categories = DbContext.VW_ExpCategoriesInScenario.Where(t => t.ScenarioID == dbObj.Id).ToArray();
|
|
var lstIds = categories.Select(t => t.Id).ToList();
|
|
var arrCalculatedIds = categories.Where(t => t.UseType == (int)ExpenditureCategoryModel.UseTypes.Calculated).Select(t => t.Id).ToArray();
|
|
if (arrCalculatedIds.Length > 0)
|
|
{
|
|
foreach (var item in DbContext.VW_Expenditure2Calculation.Where(t => arrCalculatedIds.Contains(t.ParentId)))
|
|
{
|
|
if (!item.ExpenditureCategoryID.HasValue)
|
|
continue;
|
|
if (!lstIds.Contains(item.ExpenditureCategoryID.Value))
|
|
lstIds.Add(item.ExpenditureCategoryID.Value);
|
|
}
|
|
}
|
|
// add not checked expenditure categories for Labor/Materials/Usage types
|
|
list.AddRange(DbContext.VW_Expenditure2Category.Where(t => !lstIds.Contains(t.Id))
|
|
.OrderBy(t => t.Type)
|
|
.ThenBy(t => t.ExpenditureName)
|
|
.Select(t => new
|
|
{
|
|
t.Id,
|
|
t.ExpenditureName,
|
|
t.Type,
|
|
t.GLId,
|
|
t.CGEFX,
|
|
t.CreditId,
|
|
t.SortOrder,
|
|
t.SystemAttributeOne,
|
|
t.SystemAttributeTwo,
|
|
t.UOMId,
|
|
t.UseType
|
|
}).ToArray()
|
|
.Select(t => new AdditionalExpCatItem
|
|
{
|
|
ExpenditureCategoryId = t.Id,
|
|
CategoryName = t.ExpenditureName,
|
|
CG_EFX = (t.CGEFX ?? string.Empty).Trim().Equals(ExpenditureCategoryModel.CgEfx.CG.ToString()) ? ExpenditureCategoryModel.CgEfx.CG : ExpenditureCategoryModel.CgEfx.EFX,
|
|
CategoryType = (ExpenditureCategoryModel.CategoryTypes)(t.Type ?? 0),
|
|
CreditId = t.CreditId,
|
|
DetailCost = 0,
|
|
GlId = t.GLId,
|
|
LastUpdate = DateTime.Today,
|
|
Quantity = 0,
|
|
SortOrder = t.SortOrder,
|
|
SysField1 = t.SystemAttributeOne,
|
|
SysField2 = Guid.Empty,
|
|
UOMId = t.UOMId,
|
|
UseType = (ExpenditureCategoryModel.UseTypes)(t.UseType ?? 1)
|
|
}));
|
|
#endregion
|
|
|
|
var allCheckedItemIds = expenditures.Select(t => t.Id).ToArray();
|
|
// get added categories (for those who were unchecked and become checked)
|
|
lstDctEcAddOn = list.Where(t => allCheckedItemIds.Contains(t.ExpenditureCategoryId)).ToList();
|
|
|
|
|
|
#endregion
|
|
|
|
#region Save Scenario
|
|
// setup fields for new scenario
|
|
|
|
////if (!model.UseLMMargin)
|
|
//// dbObj.TDDirectCosts = dbObj.ProjectedRevenue - (dbObj.ProjectedRevenue * dbObj.ExpectedGrossMargin);
|
|
////else
|
|
//// dbObj.TDDirectCosts_LM = dbObj.ProjectedRevenue - (dbObj.ProjectedRevenue * dbObj.ExpectedGrossMargin_LM);
|
|
|
|
//if (dbObj.Shots > 0)
|
|
// dbObj.TDRevenueShot = dbObj.ProjectedRevenue / dbObj.Shots;
|
|
//else
|
|
// dbObj.TDRevenueShot = 0;
|
|
//dbObj.Duration = FiscalCalendars.Length;
|
|
|
|
////if (model.Id == Guid.Empty)
|
|
//// DataTable.Add(dbObj);
|
|
////else
|
|
// DbContext.Entry(dbObj).State = EntityState.Modified;
|
|
|
|
#endregion
|
|
|
|
#region Create Scenario Details
|
|
|
|
Dictionary<Guid, List<ScenarioDetailsListItem>> lvaCurrScenario;
|
|
lvaCurrScenario = GetTemplateScenarioDetailsPerExpCat(dbObj.Id, lstDctEcAddOn.ToList(), Guid.Empty,
|
|
new Collection<Guid>());
|
|
if (lvaCurrScenario.Count == 0 || lvaCurrScenario.FirstOrDefault().Value.Count == 0)
|
|
throw new BLLException("Unable to calculate periods for provided information.");
|
|
var adjustHandler = new AdjustScenarioDetailsDistribution(DbContext)
|
|
{
|
|
CurrentPeriods = lvaCurrScenario.FirstOrDefault().Value.Count,
|
|
NewPeriods = dbObj.Duration ?? 0,
|
|
CurrentScenarioDetails = lvaCurrScenario,
|
|
FiscalCalendarWeeks = FiscalCalendars,
|
|
IsUpdate = true,
|
|
NewScenario = dbObj,
|
|
//CutOffDate = model.PriorWeekCutOff ?? new DateTime(1753, 1, 1)
|
|
};
|
|
var calculatedNewScenario = adjustHandler.CalculateDistribution();
|
|
|
|
var temporaryCalcResult = new TemporaryCalculationResults();
|
|
var mdtActualsEndWeek = new DateTime(1753, 1, 1);
|
|
var actualScenarioDetails = new VW_ScenarioAndProxyDetails[] { };
|
|
if (dbObj.GrowthScenario) // || !model.UseActuals)
|
|
mdtActualsEndWeek = new DateTime(1753, 1, 1);
|
|
else
|
|
{
|
|
//retrieve actuals scenario end date...
|
|
var actualScenarios =
|
|
DbContext.VW_Scenario2Project.Where(
|
|
t => t.ParentId == dbObj.ParentId && t.Type == (int)ScenarioType.Actuals).ToArray();
|
|
|
|
if (actualScenarios.Length > 0)
|
|
{
|
|
var actualScenario = actualScenarios[0];
|
|
if (actualScenario.EndDate != null)
|
|
mdtActualsEndWeek = actualScenario.EndDate.Value;
|
|
|
|
actualScenarioDetails = DbContext.VW_ScenarioAndProxyDetails.Where(t => t.ParentID == actualScenario.Id).ToArray();
|
|
|
|
if (actualScenarioDetails.Length > 0)
|
|
{
|
|
foreach (var actualScenarioDetailItem in actualScenarioDetails)
|
|
{
|
|
if (ExpenditureCategoryModel.CgEfx.CG.ToString().Equals(actualScenarioDetailItem.CGEFX))
|
|
{
|
|
temporaryCalcResult.mvUsedCostCG += actualScenarioDetailItem.Cost ?? 0;
|
|
if ((int)ExpenditureCategoryModel.CategoryTypes.Labor == actualScenarioDetailItem.Type ||
|
|
(int)ExpenditureCategoryModel.CategoryTypes.Materials == actualScenarioDetailItem.Type)
|
|
temporaryCalcResult.mvUsedCostCG_LM += actualScenarioDetailItem.Cost ?? 0;
|
|
}
|
|
else if (ExpenditureCategoryModel.CgEfx.EFX.ToString().Equals(actualScenarioDetailItem.CGEFX))
|
|
{
|
|
temporaryCalcResult.mvUsedCostEFX += actualScenarioDetailItem.Cost ?? 0;
|
|
if ((int)ExpenditureCategoryModel.CategoryTypes.Labor == actualScenarioDetailItem.Type ||
|
|
(int)ExpenditureCategoryModel.CategoryTypes.Materials == actualScenarioDetailItem.Type)
|
|
temporaryCalcResult.mvUsedCostEFX_LM += actualScenarioDetailItem.Cost ?? 0;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
#region Creating and Indexing derived Rate table
|
|
|
|
var loERates = new Dictionary<DateTime, decimal>();
|
|
var moRateTranslate = new Dictionary<Guid, Dictionary<DateTime, decimal>>();
|
|
var lsLastExpCatOID = Guid.Empty;
|
|
if (rates.Count > 0)
|
|
{
|
|
foreach (var rate in rates.Where(t => t.EndDate != null))
|
|
{
|
|
if (rate.ExpenditureCategoryId != lsLastExpCatOID)
|
|
{
|
|
if (lsLastExpCatOID != Guid.Empty)
|
|
moRateTranslate.Add(lsLastExpCatOID, loERates);
|
|
lsLastExpCatOID = rate.ExpenditureCategoryId;
|
|
loERates = new Dictionary<DateTime, decimal>();
|
|
}
|
|
if (!loERates.ContainsKey(rate.EndDate))
|
|
loERates.Add(rate.EndDate, rate.Rate1);
|
|
}
|
|
moRateTranslate.Add(lsLastExpCatOID, loERates);
|
|
}
|
|
#endregion
|
|
|
|
#region Indexing Global Rate table
|
|
if (gRates.Count > 0)
|
|
{
|
|
lsLastExpCatOID = Guid.Empty;
|
|
foreach (var gRate in gRates.Where(t => t.EndDate != null))
|
|
{
|
|
if (!lsLastExpCatOID.Equals(gRate.ExpenditureCategoryId))
|
|
{
|
|
if (lsLastExpCatOID != Guid.Empty && !moRateTranslate.ContainsKey(lsLastExpCatOID))
|
|
moRateTranslate.Add(lsLastExpCatOID, loERates);
|
|
lsLastExpCatOID = gRate.ExpenditureCategoryId;
|
|
loERates = new Dictionary<DateTime, decimal>();
|
|
}
|
|
loERates.Add(gRate.EndDate, gRate.Rate1);
|
|
}
|
|
moRateTranslate.Add(lsLastExpCatOID, loERates);
|
|
}
|
|
rates.Clear();
|
|
gRates.Clear();
|
|
#endregion
|
|
|
|
#region Calculate the CG/EFX Split
|
|
// store row of each calculated and fee expenditure category
|
|
var moCatRowTranslate =
|
|
calculatedNewScenario.Where(
|
|
item =>
|
|
ExpenditureCategoryModel.UseTypes.Calculated != item.Value[0].UseType &&
|
|
ExpenditureCategoryModel.UseTypes.Fee != item.Value[0].UseType)
|
|
.ToDictionary(item => item.Key, item => item.Value);
|
|
if (dbObj.UseLMMargin == 1)
|
|
{
|
|
temporaryCalcResult = ComputeNONCalculatedCategories(calculatedNewScenario, mdtActualsEndWeek,
|
|
moRateTranslate, temporaryCalcResult);
|
|
|
|
{
|
|
if (temporaryCalcResult.mvActualCostCG_LM == 0)
|
|
temporaryCalcResult.lvCGFactor = 0;
|
|
else
|
|
temporaryCalcResult.lvCGFactor = (((((dbObj.TDDirectCosts_LM ?? 0) * (dbObj.CGSplit ?? 0)) -
|
|
temporaryCalcResult.mvUsedCostCG_LM) -
|
|
temporaryCalcResult.mvActualCostCG_LM) /
|
|
temporaryCalcResult.mvActualCostCG_LM);
|
|
|
|
if (temporaryCalcResult.mvActualCostEFX_LM == 0)
|
|
temporaryCalcResult.lvEFXFactor = 0;
|
|
else
|
|
temporaryCalcResult.lvEFXFactor = (((((dbObj.TDDirectCosts_LM ?? 0) * (dbObj.EFXSplit ?? 0)) -
|
|
temporaryCalcResult.mvUsedCostEFX_LM) -
|
|
temporaryCalcResult.mvActualCostEFX_LM) /
|
|
temporaryCalcResult.mvActualCostEFX_LM);
|
|
}
|
|
//TODO: Put mdtActualsEndWeek to temporaryCalcResult object in entire class
|
|
ApplyCGEFXFactor(calculatedNewScenario, temporaryCalcResult, mdtActualsEndWeek, dbObj.FreezeRevenue);
|
|
//TODO: need to closely review this functions and get rid of excessive data/parameters
|
|
ComputeCalculatedCategories(calculatedNewScenario, temporaryCalcResult, mdtActualsEndWeek, dbObj.Shots ?? 0,
|
|
dbObj.Duration, moCatRowTranslate, moRateTranslate);
|
|
}
|
|
else
|
|
{
|
|
ComputeNONCalculatedCategories(calculatedNewScenario, mdtActualsEndWeek, moRateTranslate,
|
|
temporaryCalcResult);
|
|
//TODO: need to closely review this functions and get rid of excessive data/parameters
|
|
ComputeCalculatedCategories(calculatedNewScenario, temporaryCalcResult, mdtActualsEndWeek, dbObj.Shots ?? 0,
|
|
dbObj.Duration, moCatRowTranslate, moRateTranslate);
|
|
|
|
{
|
|
if (temporaryCalcResult.mvActualCostCG == 0)
|
|
temporaryCalcResult.lvCGFactor = 0;
|
|
else
|
|
temporaryCalcResult.lvCGFactor = (((dbObj.TDDirectCosts ?? 0 * dbObj.CGSplit ?? 0) -
|
|
temporaryCalcResult.mvUsedCostCG) -
|
|
temporaryCalcResult.mvActualCostCG -
|
|
temporaryCalcResult.mvActualCostCG_Fee) /
|
|
temporaryCalcResult.mvActualCostCG;
|
|
|
|
if (temporaryCalcResult.mvActualCostEFX == 0)
|
|
temporaryCalcResult.lvEFXFactor = 0;
|
|
else
|
|
temporaryCalcResult.lvEFXFactor = (((dbObj.TDDirectCosts ?? 0 * dbObj.EFXSplit ?? 0) -
|
|
temporaryCalcResult.mvUsedCostEFX) -
|
|
temporaryCalcResult.mvActualCostEFX -
|
|
temporaryCalcResult.mvActualCostEFX_Fee) /
|
|
temporaryCalcResult.mvActualCostEFX;
|
|
|
|
}
|
|
ApplyCGEFXFactor(calculatedNewScenario, temporaryCalcResult, mdtActualsEndWeek, dbObj.FreezeRevenue);
|
|
}
|
|
#endregion
|
|
|
|
#region Committing scenario details to the data store
|
|
|
|
var newId = dbObj.Id;
|
|
var scenarioDetailItems = DbContext.ScenarioDetail.Where(t => t.ParentID == newId).ToDictionary(t => t.Id);
|
|
foreach (var dbDetailsObj in from expCatGroup in calculatedNewScenario from scenarioDetailsItem in expCatGroup.Value select scenarioDetailsItem)
|
|
{
|
|
|
|
ScenarioDetail currentDbItem = null;
|
|
if (dbDetailsObj.Id != Guid.Empty) // && model.Id != Guid.Empty)
|
|
{
|
|
if (scenarioDetailItems.ContainsKey(dbDetailsObj.Id))
|
|
currentDbItem = scenarioDetailItems[dbDetailsObj.Id];
|
|
}
|
|
if (currentDbItem == null)
|
|
{
|
|
currentDbItem = new ScenarioDetail
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
ParentID = newId,
|
|
ExpenditureCategoryId = dbDetailsObj.ExpenditureCategoryId,
|
|
WeekEndingDate = dbDetailsObj.WeekEndingDate,
|
|
Quantity = dbDetailsObj.Quantity,
|
|
WeekOrdinal = dbDetailsObj.WeekOrdinal,
|
|
Cost = dbDetailsObj.DetailCost,
|
|
};
|
|
DbContext.ScenarioDetail.Add(currentDbItem);
|
|
}
|
|
else
|
|
{
|
|
currentDbItem.WeekEndingDate = dbDetailsObj.WeekEndingDate;
|
|
currentDbItem.WeekOrdinal = dbDetailsObj.WeekOrdinal;
|
|
currentDbItem.Quantity = dbDetailsObj.Quantity;
|
|
currentDbItem.Cost = dbDetailsObj.DetailCost;
|
|
DbContext.Entry(currentDbItem).State = EntityState.Modified;
|
|
}
|
|
}
|
|
|
|
// delete details where weekending date > end date
|
|
//if (model.Id != Guid.Empty)
|
|
{
|
|
var newEndDate = dbObj.EndDate;
|
|
var newDuration = dbObj.Duration;
|
|
var items2Delete = DbContext.ScenarioDetail.Where(
|
|
t => t.ParentID == dbObj.Id &&
|
|
(t.WeekEndingDate > newEndDate || t.WeekOrdinal > newDuration || t.WeekOrdinal < 1));
|
|
DbContext.ScenarioDetail.RemoveRange(items2Delete);
|
|
}
|
|
#endregion
|
|
|
|
// we need to commit scenario changes so SP could process data correctly
|
|
DbContext.SaveChanges();
|
|
|
|
#region Recalculated scenrio details via Stored Procedure
|
|
// recalculate scenario via Stored Procedure
|
|
//try
|
|
//{
|
|
// SetBottomUpCosts();
|
|
// //(DbContext as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(string.Format("exec sp_SetScenarioBottomUpCosts '{0}'", dbObj.Id));
|
|
//}
|
|
//catch (Exception exception) // handle any unexpected error
|
|
//{
|
|
// LogManager.GetCurrentClassLogger().Fatal(exception.Message);
|
|
//}
|
|
//// we need to get recalculated values
|
|
//((IObjectContextAdapter)DbContext).ObjectContext.Refresh(RefreshMode.StoreWins, dbObj);
|
|
//// reload scenario from db
|
|
//dbObj = DataTable.Find(dbObj.Id);
|
|
|
|
//{
|
|
// dbObj.TDDirectCosts = dbObj.ProjectedRevenue - (dbObj.ProjectedRevenue * dbObj.ExpectedGrossMargin);
|
|
// dbObj.TDDirectCosts_LM = dbObj.ProjectedRevenue - (dbObj.ProjectedRevenue * dbObj.ExpectedGrossMargin_LM);
|
|
//}
|
|
//DbContext.Entry(dbObj).State = EntityState.Modified;
|
|
//if (dbObj.Status == (int?)StatusType.Active)
|
|
//{
|
|
// var active_scenarios = (from c in DbContext.Scenarios where c.ParentId == dbObj.ParentId && dbObj.Type == c.Type && c.Status == (int?)StatusType.Active select c).ToList();
|
|
// active_scenarios.ForEach(scen => scen.Status = (int?)StatusType.Inactive);
|
|
// dbObj.Status = (int?)StatusType.Active;
|
|
//}
|
|
//DbContext.SaveChanges();
|
|
|
|
#endregion
|
|
|
|
#endregion
|
|
}
|
|
|
|
/// <summary>
|
|
/// Saves scenario and scenario details.
|
|
/// </summary>
|
|
/// <param name="model"></param>
|
|
public override Scenario Save(ScenarioModel model)
|
|
{
|
|
if (model == null)
|
|
throw new ArgumentNullException("model");
|
|
|
|
#region prepare scenario object
|
|
Scenario dbObj = null;
|
|
if (model.Id != Guid.Empty)
|
|
dbObj = DataTable.Find(model.Id);
|
|
if (model.Id != Guid.Empty && !ScenarioStatus.Draft.Equals(model.Status))
|
|
{
|
|
model.TemplateId = dbObj.TemplateId ?? Guid.Empty;
|
|
model.PriorWeekCutOff = model.AllowAdjustment ? new DateTime(1753, 1, 1) : model.PriorWeekCutOff;
|
|
model.StartDate = model.AllowAdjustment ? model.StartDate : dbObj.StartDate;
|
|
if (model.UseLMMargin)
|
|
model.GrossMargin = dbObj.ExpectedGrossMargin * 100.0M;
|
|
else
|
|
model.LMMargin = dbObj.ExpectedGrossMargin_LM * 100.0M;
|
|
}
|
|
if (dbObj == null)
|
|
{
|
|
dbObj = InitInstance();
|
|
}
|
|
model.CopyTo(dbObj);
|
|
|
|
#endregion
|
|
|
|
#region Load referenced objects from DB
|
|
decimal totalBtUpCostsMaterials = 0;
|
|
var project = DbContext.Projects.Find(model.ParentId);
|
|
if (project == null)
|
|
throw new BLLException("Unable to find selected project. Please, reload the page or try again later.");
|
|
|
|
var FiscalCalendars = DbContext.FiscalCalendars.Where(
|
|
t =>
|
|
t.Type == (int) FiscalCalendarModel.FiscalYearType.Week && t.EndDate >= model.StartDate &&
|
|
t.EndDate <= model.EndDate && t.NonWorking == 0).OrderBy(t=>t.EndDate).ToArray();
|
|
var rates = new List<Rate>();
|
|
var gRates = new List<Rate>();
|
|
var lstDctEcAddOn = new List<AdditionalExpCatItem>();
|
|
var lstExpCatsToExclude = new List<Guid>(); //this variable will be used to keep track of expenditure categories defined in the template that are not selected...
|
|
if (!model.AsDraft)
|
|
if (model.Id == Guid.Empty || ScenarioStatus.Draft.Equals(model.Status)) // if new scenario
|
|
{
|
|
#region load referenced data for new scenario
|
|
// Expenditure Categories related to template scenario (GetExpenditureCategoriesUnique method in old code)
|
|
var templateExpCats = DbContext.VW_ExpCategoriesInScenario.Where(t => t.ScenarioID == model.TemplateId).Select(t => new
|
|
{
|
|
t.Id,
|
|
t.UseType,
|
|
t.Name
|
|
}).ToArray();
|
|
// list of expenditure category Ids related to the template scenario
|
|
var lstCatFromTemplate = templateExpCats.Select(t => new { t.Id, t.Name }).ToList();
|
|
var lsUnqCatList = templateExpCats.Select(t => t.Id).ToList();
|
|
// list of template scenario's expenditure category Ids with "Calculated" UseType
|
|
var arrCalculatedIds = templateExpCats.Where(t => t.UseType == (int)ExpenditureCategoryModel.UseTypes.Calculated).Select(t => t.Id).ToArray();
|
|
var calculatedChildren = new VW_Expenditure2Calculation[0];
|
|
if (arrCalculatedIds.Length > 0)
|
|
{
|
|
calculatedChildren = DbContext.VW_Expenditure2Calculation.Where(t => arrCalculatedIds.Contains(t.ParentId)).ToArray();
|
|
}
|
|
// add children of calculated expenditures to the list of unique expenditure category Ids
|
|
foreach (var item in calculatedChildren.Where(t => t.ExpenditureCategoryID != null && !lsUnqCatList.Contains(t.ExpenditureCategoryID.Value)))
|
|
{
|
|
if (!lsUnqCatList.Contains(item.ExpenditureCategoryID.Value))
|
|
{
|
|
lsUnqCatList.Add(item.ExpenditureCategoryID.Value);
|
|
lstCatFromTemplate.Add(new {Id = item.ExpenditureCategoryID.Value, Name = item.ExpenditureName});
|
|
lstDctEcAddOn.Add(new AdditionalExpCatItem
|
|
{
|
|
ExpenditureCategoryId = item.ExpenditureCategoryID.Value,
|
|
LastUpdate = DateTime.Today,
|
|
CategoryType = (ExpenditureCategoryModel.CategoryTypes)(item.Type ?? 0),
|
|
Quantity = 0,
|
|
DetailCost = 0,
|
|
SortOrder = item.SortOrder,
|
|
UseType = (ExpenditureCategoryModel.UseTypes)(item.UseType ?? 1),
|
|
WeekOrdinal = 0,
|
|
CG_EFX = (item.CGEFX ?? string.Empty).Trim().Equals(ExpenditureCategoryModel.CgEfx.CG.ToString()) ? ExpenditureCategoryModel.CgEfx.CG : ExpenditureCategoryModel.CgEfx.EFX,
|
|
CategoryName = item.ExpenditureName,
|
|
SysField1 = item.SystemAttributeOne,
|
|
SysField2 = Guid.Empty,
|
|
CreditId = item.CreditId,
|
|
GlId = item.GLId,
|
|
UOMId = item.UOMId,
|
|
//IsCalculated = true // stored as "P" + ExpCatId as key field in old code
|
|
});
|
|
}
|
|
}
|
|
lstDctEcAddOn.AddRange(DbContext.VW_Expenditure2Category.Where(t => !lsUnqCatList.Contains(t.Id)).
|
|
Select(t => new AdditionalExpCatItem
|
|
{
|
|
ExpenditureCategoryId = t.Id,
|
|
LastUpdate = DateTime.Today,
|
|
CategoryType = (ExpenditureCategoryModel.CategoryTypes)(t.Type ?? 0),
|
|
Quantity = 0,
|
|
DetailCost = 0,
|
|
SortOrder = t.SortOrder,
|
|
UseType = (ExpenditureCategoryModel.UseTypes)(t.UseType ?? 1),
|
|
WeekOrdinal = 0,
|
|
CG_EFX = (t.CGEFX ?? string.Empty).Trim().Equals(ExpenditureCategoryModel.CgEfx.CG.ToString()) ? ExpenditureCategoryModel.CgEfx.CG : ExpenditureCategoryModel.CgEfx.EFX,
|
|
CategoryName = t.ExpenditureName,
|
|
SysField1 = t.SystemAttributeOne,
|
|
SysField2 = Guid.Empty,
|
|
CreditId = t.CreditId,
|
|
GlId = t.GLId,
|
|
UOMId = t.UOMId,
|
|
//IsCalculated = true // stored as "P" + ExpCatId as key field in old code
|
|
}).ToList());
|
|
|
|
var lstExpCatsForRates = lstCatFromTemplate.Select(t => t.Id).ToList(); //list of template expenditures and children of calculated expenditures
|
|
var lstExpCatsForGRates = new List<Guid>(); //list of unchecked non-template expenditure categories
|
|
|
|
foreach (var expenditureItem in model.Expenditures)
|
|
{
|
|
if (!Constants.EXPENDITURE_INTEMPLATE_TITLE.Equals(expenditureItem.Group))
|
|
{
|
|
if (expenditureItem.Checked)
|
|
lstExpCatsForRates.Add(expenditureItem.Id);
|
|
else
|
|
{
|
|
lstExpCatsForGRates.Add(expenditureItem.Id);
|
|
var id2Exclude = expenditureItem.Id;
|
|
lstDctEcAddOn.RemoveAll(t => t.ExpenditureCategoryId == id2Exclude);
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (!expenditureItem.Checked)
|
|
{
|
|
lstExpCatsToExclude.Add(expenditureItem.Id);
|
|
var id2Exclude = expenditureItem.Id;
|
|
lstDctEcAddOn.RemoveAll(t => t.ExpenditureCategoryId == id2Exclude);
|
|
}
|
|
}
|
|
}
|
|
if (lstExpCatsForRates.Count > 0)
|
|
{
|
|
rates = DbContext.Rates.Where(
|
|
t =>
|
|
t.Type == (short)RateModel.RateType.Global &&
|
|
lstExpCatsForRates.Contains(t.ExpenditureCategoryId))
|
|
.OrderBy(t => t.ExpenditureCategoryId)
|
|
.ThenBy(t => t.EndDate)
|
|
.ToList();
|
|
}
|
|
if (lstExpCatsForGRates.Count > 0)
|
|
{
|
|
gRates =
|
|
DbContext.Rates.Where(
|
|
t =>
|
|
t.Type == (short)RateModel.RateType.Global &&
|
|
lstExpCatsForGRates.Contains(t.ExpenditureCategoryId))
|
|
.OrderBy(t => t.ExpenditureCategoryId)
|
|
.ThenBy(t => t.EndDate)
|
|
.ToList();
|
|
}
|
|
#endregion
|
|
}
|
|
else // if edit scenario
|
|
{
|
|
#region load referenced data for existing scenario
|
|
|
|
rates = DbContext.Rates.Where(
|
|
t => t.Type == (short) RateModel.RateType.Derived && t.ParentId == model.Id)
|
|
.OrderBy(t => t.ExpenditureCategoryId)
|
|
.ThenBy(t => t.EndDate).ToList();
|
|
var lsExpCatsForLRates = rates.Select(t => t.ExpenditureCategoryId).ToArray();
|
|
|
|
gRates = lsExpCatsForLRates.Length > 0
|
|
? DbContext.Rates.Where(t => t.Type == (short) RateModel.RateType.Global &&
|
|
!lsExpCatsForLRates.Contains(t.ExpenditureCategoryId))
|
|
.OrderBy(t => t.ExpenditureCategoryId)
|
|
.ThenBy(t => t.EndDate)
|
|
.ToList()
|
|
: DbContext.Rates.Where(t => t.Type == (short) RateModel.RateType.Global)
|
|
.OrderBy(t => t.ExpenditureCategoryId)
|
|
.ThenBy(t => t.EndDate)
|
|
.ToList();
|
|
var list = new List<AdditionalExpCatItem>();
|
|
var categories = DbContext.VW_ExpCategoriesInScenario.Where(t => t.ScenarioID == model.Id).ToArray();
|
|
materialsECs = categories.Where(x => x.ECType.Value == (int)ExpenditureCategoryModel.CategoryTypes.Materials).Select(x => x.Id).ToArray();
|
|
var data = (from sd in DbContext.ScenarioDetail
|
|
join vw in DbContext.VW_Expenditure2Category on sd.ExpenditureCategoryId equals vw.Id
|
|
where sd.ParentID == model.Id
|
|
select new
|
|
{
|
|
sd.Id,
|
|
sd.Cost,
|
|
ExpenditureType = vw.Type,
|
|
ScenarioId = sd.ParentID,
|
|
}).Distinct().ToArray();
|
|
//var totalBtUpCosts = data.Sum(t => t.Cost);
|
|
totalBtUpCostsMaterials = data.Where(t =>
|
|
t.ExpenditureType == (int)ExpenditureCategoryModel.CategoryTypes.Materials).Sum(x => (decimal)x.Cost);
|
|
|
|
var lstIds = categories.Select(t => t.Id).ToList();
|
|
var arrCalculatedIds = categories.Where(t => t.UseType == (int) ExpenditureCategoryModel.UseTypes.Calculated).Select(t => t.Id).ToArray();
|
|
if (arrCalculatedIds.Length > 0)
|
|
{
|
|
foreach (var item in DbContext.VW_Expenditure2Calculation.Where(t=> arrCalculatedIds.Contains(t.ParentId)))
|
|
{
|
|
if (!item.ExpenditureCategoryID.HasValue)
|
|
continue;
|
|
if (!lstIds.Contains(item.ExpenditureCategoryID.Value))
|
|
lstIds.Add(item.ExpenditureCategoryID.Value);
|
|
}
|
|
}
|
|
// add not checked expenditure categories for Labor/Materials/Usage types
|
|
list.AddRange(DbContext.VW_Expenditure2Category.Where(t => !lstIds.Contains(t.Id))
|
|
.OrderBy(t => t.Type)
|
|
.ThenBy(t => t.ExpenditureName)
|
|
.Select(t => new
|
|
{
|
|
t.Id,
|
|
t.ExpenditureName,
|
|
t.Type,
|
|
t.GLId,
|
|
t.CGEFX,
|
|
t.CreditId,
|
|
t.SortOrder,
|
|
t.SystemAttributeOne,
|
|
t.SystemAttributeTwo,
|
|
t.UOMId,
|
|
t.UseType
|
|
}).ToArray()
|
|
.Select(t => new AdditionalExpCatItem
|
|
{
|
|
ExpenditureCategoryId = t.Id,
|
|
CategoryName = t.ExpenditureName,
|
|
CG_EFX = (t.CGEFX ?? string.Empty).Trim().Equals(ExpenditureCategoryModel.CgEfx.CG.ToString()) ? ExpenditureCategoryModel.CgEfx.CG : ExpenditureCategoryModel.CgEfx.EFX,
|
|
CategoryType = (ExpenditureCategoryModel.CategoryTypes)(t.Type ?? 0),
|
|
CreditId = t.CreditId,
|
|
DetailCost = 0,
|
|
GlId = t.GLId,
|
|
LastUpdate = DateTime.Today,
|
|
Quantity = 0,
|
|
SortOrder = t.SortOrder,
|
|
SysField1 = t.SystemAttributeOne,
|
|
SysField2 = Guid.Empty,
|
|
UOMId = t.UOMId,
|
|
UseType = (ExpenditureCategoryModel.UseTypes) (t.UseType ?? 1)
|
|
}));
|
|
#endregion
|
|
|
|
var allCheckedItemIds = model.Expenditures.Where(t => t.Checked).Select(t => t.Id).ToArray();
|
|
// get added categories (for those who were unchecked and become checked)
|
|
lstDctEcAddOn = list.Where(t => allCheckedItemIds.Contains(t.ExpenditureCategoryId)).ToList();
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Save Scenario
|
|
// setup fields for new scenario
|
|
if (model.Id == Guid.Empty || ScenarioStatus.Draft.Equals(model.Status))
|
|
{
|
|
if (string.IsNullOrEmpty(model.Color))
|
|
dbObj.Color = project.Color;
|
|
dbObj.FreezeRevenue = true;
|
|
if (!model.UseLMMargin)
|
|
{
|
|
dbObj.ExpectedGrossMargin_LM = 0;
|
|
dbObj.TDDirectCosts_LM = 0;
|
|
}
|
|
else
|
|
{
|
|
dbObj.ExpectedGrossMargin = 0;
|
|
dbObj.TDDirectCosts = 0;
|
|
}
|
|
|
|
if (!model.IsRevenueGenerating)
|
|
{
|
|
dbObj.ProjectedRevenue = 0;
|
|
dbObj.ExpectedGrossMargin = 0;
|
|
dbObj.ExpectedGrossMargin_LM = 0;
|
|
}
|
|
}
|
|
|
|
if (model.IsRevenueGenerating)
|
|
{
|
|
if (!model.UseLMMargin)
|
|
{
|
|
dbObj.TDDirectCosts = dbObj.ProjectedRevenue - (dbObj.ProjectedRevenue * dbObj.ExpectedGrossMargin);
|
|
}
|
|
else
|
|
{
|
|
dbObj.TDDirectCosts_LM = dbObj.ProjectedRevenue - (dbObj.ProjectedRevenue * dbObj.ExpectedGrossMargin_LM);
|
|
}
|
|
|
|
}
|
|
else {
|
|
dbObj.TDDirectCosts = model.TDDirectCosts;
|
|
dbObj.TDDirectCosts_LM = 0;
|
|
}
|
|
|
|
if (dbObj.Shots > 0)
|
|
dbObj.TDRevenueShot = dbObj.ProjectedRevenue/dbObj.Shots;
|
|
else
|
|
dbObj.TDRevenueShot = 0;
|
|
dbObj.Duration = FiscalCalendars.Length;
|
|
|
|
if (model.Id == Guid.Empty)
|
|
DataTable.Add(dbObj);
|
|
else
|
|
DbContext.Entry(dbObj).State = EntityState.Modified;
|
|
|
|
#endregion
|
|
|
|
#region Create Scenario Details
|
|
|
|
if (!model.AsDraft)
|
|
{
|
|
var lvaCurrScenario = model.Id == Guid.Empty || ScenarioStatus.Draft.Equals(model.Status)
|
|
? GetTemplateScenarioDetailsPerExpCat(model.TemplateId ?? Guid.Empty, lstDctEcAddOn.ToList(),
|
|
dbObj.Id, lstExpCatsToExclude)
|
|
: GetTemplateScenarioDetailsPerExpCat(model.Id, lstDctEcAddOn.ToList(),
|
|
Guid.Empty,
|
|
new Collection<Guid>());
|
|
if (lvaCurrScenario.Count == 0 || lvaCurrScenario.FirstOrDefault().Value.Count == 0)
|
|
throw new BLLException("Unable to calculate periods for provided information.");
|
|
var adjustHandler = new AdjustScenarioDetailsDistribution(DbContext)
|
|
{
|
|
CurrentPeriods = lvaCurrScenario.FirstOrDefault().Value.Count,
|
|
NewPeriods = dbObj.Duration ?? 0,
|
|
CurrentScenarioDetails = lvaCurrScenario,
|
|
FiscalCalendarWeeks = FiscalCalendars,
|
|
IsUpdate = true,
|
|
NewScenario = dbObj,
|
|
CutOffDate = model.PriorWeekCutOff ?? new DateTime(1753, 1, 1),
|
|
MaterialsEcs = materialsECs
|
|
};
|
|
var calculatedNewScenario = adjustHandler.CalculateDistribution();
|
|
|
|
var temporaryCalcResult = new TemporaryCalculationResults();
|
|
var mdtActualsEndWeek = new DateTime(1753, 1, 1);
|
|
var actualScenarioDetails = new VW_ScenarioAndProxyDetails[] {};
|
|
if (model.GrowthScenario || !model.UseActuals)
|
|
mdtActualsEndWeek = new DateTime(1753, 1, 1);
|
|
else
|
|
{
|
|
//retrieve actuals scenario end date...
|
|
var actualScenarios =
|
|
DbContext.VW_Scenario2Project.Where(
|
|
t => t.ParentId == dbObj.ParentId && t.Type == (int) ScenarioType.Actuals).ToArray();
|
|
|
|
if (actualScenarios.Length > 0)
|
|
{
|
|
var actualScenario = actualScenarios[0];
|
|
if (actualScenario.EndDate != null)
|
|
mdtActualsEndWeek = actualScenario.EndDate.Value;
|
|
if (model.Id == Guid.Empty || ScenarioStatus.Draft.Equals(model.Status))
|
|
actualScenarioDetails =
|
|
DbContext.VW_ScenarioAndProxyDetails.Where(t => t.ParentID == actualScenario.Id)
|
|
.OrderBy(t => t.ExpenditureCategoryId)
|
|
.ThenBy(t => t.WeekEndingDate).ToArray();
|
|
else
|
|
actualScenarioDetails =
|
|
DbContext.VW_ScenarioAndProxyDetails.Where(t => t.ParentID == actualScenario.Id)
|
|
.ToArray();
|
|
|
|
if (actualScenarioDetails.Length > 0)
|
|
{
|
|
foreach (var actualScenarioDetailItem in actualScenarioDetails)
|
|
{
|
|
if (ExpenditureCategoryModel.CgEfx.CG.ToString().Equals(actualScenarioDetailItem.CGEFX))
|
|
{
|
|
temporaryCalcResult.mvUsedCostCG += actualScenarioDetailItem.Cost ?? 0;
|
|
if ((int) ExpenditureCategoryModel.CategoryTypes.Labor ==
|
|
actualScenarioDetailItem.Type ||
|
|
(int) ExpenditureCategoryModel.CategoryTypes.Materials ==
|
|
actualScenarioDetailItem.Type)
|
|
temporaryCalcResult.mvUsedCostCG_LM += actualScenarioDetailItem.Cost ?? 0;
|
|
}
|
|
else if (
|
|
ExpenditureCategoryModel.CgEfx.EFX.ToString()
|
|
.Equals(actualScenarioDetailItem.CGEFX))
|
|
{
|
|
temporaryCalcResult.mvUsedCostEFX += actualScenarioDetailItem.Cost ?? 0;
|
|
if ((int) ExpenditureCategoryModel.CategoryTypes.Labor ==
|
|
actualScenarioDetailItem.Type ||
|
|
(int) ExpenditureCategoryModel.CategoryTypes.Materials ==
|
|
actualScenarioDetailItem.Type)
|
|
temporaryCalcResult.mvUsedCostEFX_LM += actualScenarioDetailItem.Cost ?? 0;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
mdtActualsEndWeek = new DateTime(1753, 1, 1);
|
|
}
|
|
}
|
|
|
|
#region Creating and Indexing derived Rate table
|
|
|
|
var loERates = new Dictionary<DateTime, decimal>();
|
|
var moRateTranslate = new Dictionary<Guid, Dictionary<DateTime, decimal>>();
|
|
var lsLastExpCatOID = Guid.Empty;
|
|
if (rates.Count > 0)
|
|
{
|
|
foreach (var rate in rates.Where(t => t.EndDate != null))
|
|
{
|
|
if (rate.ExpenditureCategoryId != lsLastExpCatOID)
|
|
{
|
|
if (lsLastExpCatOID != Guid.Empty)
|
|
moRateTranslate.Add(lsLastExpCatOID, loERates);
|
|
lsLastExpCatOID = rate.ExpenditureCategoryId;
|
|
loERates = new Dictionary<DateTime, decimal>();
|
|
}
|
|
if (!loERates.ContainsKey(rate.EndDate))
|
|
loERates.Add(rate.EndDate, rate.Rate1);
|
|
}
|
|
moRateTranslate.Add(lsLastExpCatOID, loERates);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Indexing Global Rate table
|
|
|
|
if (gRates.Count > 0)
|
|
{
|
|
lsLastExpCatOID = Guid.Empty;
|
|
foreach (var gRate in gRates.Where(t => t.EndDate != null))
|
|
{
|
|
if (!lsLastExpCatOID.Equals(gRate.ExpenditureCategoryId))
|
|
{
|
|
if (lsLastExpCatOID != Guid.Empty && !moRateTranslate.ContainsKey(lsLastExpCatOID))
|
|
moRateTranslate.Add(lsLastExpCatOID, loERates);
|
|
lsLastExpCatOID = gRate.ExpenditureCategoryId;
|
|
loERates = new Dictionary<DateTime, decimal>();
|
|
}
|
|
if (loERates.ContainsKey(gRate.EndDate))
|
|
loERates[gRate.EndDate] = gRate.Rate1;
|
|
else
|
|
loERates.Add(gRate.EndDate, gRate.Rate1);
|
|
}
|
|
if (moRateTranslate.ContainsKey(lsLastExpCatOID))
|
|
moRateTranslate[lsLastExpCatOID] = loERates;
|
|
else
|
|
moRateTranslate.Add(lsLastExpCatOID, loERates);
|
|
}
|
|
rates.Clear();
|
|
gRates.Clear();
|
|
|
|
#endregion
|
|
|
|
#region Calculate the CG/EFX Split
|
|
|
|
// store row of each calculated and fee expenditure category
|
|
var moCatRowTranslate =
|
|
calculatedNewScenario.Where(
|
|
item =>
|
|
item.Value.Count > 0 &&
|
|
ExpenditureCategoryModel.UseTypes.Calculated != item.Value[0].UseType &&
|
|
ExpenditureCategoryModel.UseTypes.Fee != item.Value[0].UseType)
|
|
.ToDictionary(item => item.Key, item => item.Value);
|
|
if (dbObj.UseLMMargin == 1)
|
|
{
|
|
temporaryCalcResult = ComputeNONCalculatedCategories(calculatedNewScenario, mdtActualsEndWeek,
|
|
moRateTranslate, temporaryCalcResult);
|
|
if (model.Id == Guid.Empty || ScenarioStatus.Draft.Equals(model.Status))
|
|
{
|
|
if (temporaryCalcResult.mvActualCostCG_LM > 0)
|
|
temporaryCalcResult.lvCGFactor = (((((dbObj.TDDirectCosts_LM ?? 0)*(dbObj.CGSplit ?? 0)) -
|
|
temporaryCalcResult.mvUsedCostCG_LM) -
|
|
temporaryCalcResult.mvActualCostCG_LM)/
|
|
temporaryCalcResult.mvActualCostCG_LM);
|
|
else
|
|
temporaryCalcResult.lvCGFactor = 0;
|
|
|
|
if (temporaryCalcResult.mvActualCostEFX_LM > 0)
|
|
temporaryCalcResult.lvEFXFactor = (((((dbObj.TDDirectCosts_LM ?? 0)*(dbObj.EFXSplit ?? 0)) -
|
|
temporaryCalcResult.mvUsedCostEFX_LM) -
|
|
temporaryCalcResult.mvActualCostEFX_LM)/
|
|
temporaryCalcResult.mvActualCostEFX_LM);
|
|
else
|
|
temporaryCalcResult.lvEFXFactor = 0;
|
|
}
|
|
else
|
|
{
|
|
if (temporaryCalcResult.mvActualCostCG_LM == 0)
|
|
temporaryCalcResult.lvCGFactor = 0;
|
|
else
|
|
{
|
|
var modifier = 1 - totalBtUpCostsMaterials / (decimal)(dbObj.BUDirectCosts_LM);
|
|
temporaryCalcResult.lvCGFactor = (((((dbObj.TDDirectCosts_LM ?? 0) * (dbObj.CGSplit ?? 0)) -
|
|
temporaryCalcResult.mvUsedCostCG_LM) -
|
|
temporaryCalcResult.mvActualCostCG_LM) /
|
|
temporaryCalcResult.mvActualCostCG_LM);
|
|
temporaryCalcResult.lvCGFactor = temporaryCalcResult.lvCGFactor / modifier;
|
|
}
|
|
if (temporaryCalcResult.mvActualCostEFX_LM == 0)
|
|
temporaryCalcResult.lvEFXFactor = 0;
|
|
else
|
|
temporaryCalcResult.lvEFXFactor = (((((dbObj.TDDirectCosts_LM ?? 0)*(dbObj.EFXSplit ?? 0)) -
|
|
temporaryCalcResult.mvUsedCostEFX_LM) -
|
|
temporaryCalcResult.mvActualCostEFX_LM)/
|
|
temporaryCalcResult.mvActualCostEFX_LM);
|
|
}
|
|
//TODO: Put mdtActualsEndWeek to temporaryCalcResult object in entire class
|
|
ApplyCGEFXFactor(calculatedNewScenario, temporaryCalcResult, mdtActualsEndWeek, dbObj.FreezeRevenue, model.UseLMMargin);
|
|
//TODO: need to closely review this functions and get rid of excessive data/parameters
|
|
ComputeCalculatedCategories(calculatedNewScenario, temporaryCalcResult, mdtActualsEndWeek,
|
|
model.TotalMilestones,
|
|
dbObj.Duration, moCatRowTranslate, moRateTranslate);
|
|
}
|
|
else
|
|
{
|
|
ComputeNONCalculatedCategories(calculatedNewScenario, mdtActualsEndWeek, moRateTranslate,
|
|
temporaryCalcResult);
|
|
//TODO: need to closely review this functions and get rid of excessive data/parameters
|
|
ComputeCalculatedCategories(calculatedNewScenario, temporaryCalcResult, mdtActualsEndWeek,
|
|
model.TotalMilestones, dbObj.Duration, moCatRowTranslate,
|
|
moRateTranslate);
|
|
if (model.Id == Guid.Empty || ScenarioStatus.Draft.Equals(model.Status))
|
|
{
|
|
if (temporaryCalcResult.mvActualCostCG > 0)
|
|
temporaryCalcResult.lvCGFactor = (((dbObj.TDDirectCosts ?? 0*dbObj.CGSplit ?? 0) -
|
|
temporaryCalcResult.mvUsedCostCG) -
|
|
temporaryCalcResult.mvActualCostCG -
|
|
temporaryCalcResult.mvActualCostCG_Fee)/
|
|
temporaryCalcResult.mvActualCostCG;
|
|
else
|
|
temporaryCalcResult.lvCGFactor = 0;
|
|
if (temporaryCalcResult.mvActualCostEFX > 0)
|
|
temporaryCalcResult.lvEFXFactor = (((dbObj.TDDirectCosts ?? 0*dbObj.EFXSplit ?? 0) -
|
|
temporaryCalcResult.mvUsedCostEFX) -
|
|
temporaryCalcResult.mvActualCostEFX -
|
|
temporaryCalcResult.mvActualCostEFX_Fee)/
|
|
temporaryCalcResult.mvActualCostEFX;
|
|
else
|
|
temporaryCalcResult.lvEFXFactor = 0;
|
|
}
|
|
else
|
|
{
|
|
if (temporaryCalcResult.mvActualCostCG == 0)
|
|
temporaryCalcResult.lvCGFactor = 0;
|
|
else
|
|
{
|
|
if (model.IsRevenueGenerating)
|
|
temporaryCalcResult.lvCGFactor = (((dbObj.TDDirectCosts*dbObj.CGSplit ?? 0) -
|
|
temporaryCalcResult.mvUsedCostCG) -
|
|
temporaryCalcResult.mvActualCostCG -
|
|
temporaryCalcResult.mvActualCostCG_Fee)/
|
|
temporaryCalcResult.mvActualCostCG;
|
|
else
|
|
{
|
|
temporaryCalcResult.lvCGFactor = (((dbObj.TDDirectCosts ?? 0*dbObj.CGSplit ?? 0) -
|
|
temporaryCalcResult.mvUsedCostCG) -
|
|
temporaryCalcResult.mvActualCostCG -
|
|
temporaryCalcResult.mvActualCostCG_Fee)/
|
|
temporaryCalcResult.mvActualCostCG;
|
|
}
|
|
}
|
|
|
|
if (temporaryCalcResult.mvActualCostEFX == 0)
|
|
temporaryCalcResult.lvEFXFactor = 0;
|
|
else
|
|
temporaryCalcResult.lvEFXFactor = (((dbObj.TDDirectCosts ?? 0*dbObj.EFXSplit ?? 0) -
|
|
temporaryCalcResult.mvUsedCostEFX) -
|
|
temporaryCalcResult.mvActualCostEFX -
|
|
temporaryCalcResult.mvActualCostEFX_Fee)/
|
|
temporaryCalcResult.mvActualCostEFX;
|
|
|
|
}
|
|
ApplyCGEFXFactor(calculatedNewScenario, temporaryCalcResult, mdtActualsEndWeek, dbObj.FreezeRevenue, model.UseLMMargin);
|
|
}
|
|
if (model.Id == Guid.Empty || ScenarioStatus.Draft.Equals(model.Status))
|
|
CopyActuals(calculatedNewScenario, actualScenarioDetails, mdtActualsEndWeek);
|
|
|
|
#endregion
|
|
|
|
#region Committing scenario details to the data store
|
|
|
|
var newId = dbObj.Id;
|
|
var scenarioDetailItems =
|
|
DbContext.ScenarioDetail.Where(t => t.ParentID == newId).ToDictionary(t => t.Id);
|
|
foreach (var dbDetailsObj in
|
|
from expCatGroup in calculatedNewScenario
|
|
from scenarioDetailsItem in expCatGroup.Value
|
|
select scenarioDetailsItem)
|
|
{
|
|
ScenarioDetail currentDbItem = null;
|
|
if (dbDetailsObj.Id != Guid.Empty && model.Id != Guid.Empty)
|
|
{
|
|
if (scenarioDetailItems.ContainsKey(dbDetailsObj.Id))
|
|
currentDbItem = scenarioDetailItems[dbDetailsObj.Id];
|
|
}
|
|
if (currentDbItem == null)
|
|
{
|
|
currentDbItem = new ScenarioDetail
|
|
{
|
|
Id = Guid.NewGuid(),
|
|
ParentID = newId,
|
|
ExpenditureCategoryId = dbDetailsObj.ExpenditureCategoryId,
|
|
WeekEndingDate = dbDetailsObj.WeekEndingDate,
|
|
Quantity = dbDetailsObj.Quantity,
|
|
WeekOrdinal = dbDetailsObj.WeekOrdinal,
|
|
Cost = dbDetailsObj.DetailCost,
|
|
};
|
|
DbContext.ScenarioDetail.Add(currentDbItem);
|
|
}
|
|
else
|
|
{
|
|
currentDbItem.WeekEndingDate = dbDetailsObj.WeekEndingDate;
|
|
currentDbItem.WeekOrdinal = dbDetailsObj.WeekOrdinal;
|
|
currentDbItem.Quantity = dbDetailsObj.Quantity;
|
|
currentDbItem.Cost = dbDetailsObj.DetailCost;
|
|
DbContext.Entry(currentDbItem).State = EntityState.Modified;
|
|
}
|
|
}
|
|
|
|
// delete details where weekending date > end date
|
|
if (model.Id != Guid.Empty)
|
|
{
|
|
var newEndDate = dbObj.EndDate;
|
|
var newDuration = dbObj.Duration;
|
|
var items2Delete = DbContext.ScenarioDetail.Where(
|
|
t => t.ParentID == model.Id &&
|
|
(t.WeekEndingDate > newEndDate || t.WeekOrdinal > newDuration || t.WeekOrdinal < 1));
|
|
DbContext.ScenarioDetail.RemoveRange(items2Delete);
|
|
}
|
|
|
|
#endregion
|
|
|
|
DbContext.SaveChanges();
|
|
|
|
#region Recalculate bottom-up costs
|
|
|
|
// recalculate scenario
|
|
try
|
|
{
|
|
SetBottomUpCosts(dbObj, model.IsRevenueGenerating);
|
|
}
|
|
catch (Exception exception) // handle any unexpected error
|
|
{
|
|
LogManager.GetCurrentClassLogger().Fatal(exception.Message);
|
|
}
|
|
if (model.Id == Guid.Empty || ScenarioStatus.Draft.Equals(model.Status))
|
|
{
|
|
if (model.UseLMMargin)
|
|
{
|
|
if (model.IsRevenueGenerating)
|
|
{
|
|
dbObj.ExpectedGrossMargin = dbObj.CalculatedGrossMargin;
|
|
dbObj.TDDirectCosts = dbObj.ProjectedRevenue -
|
|
(dbObj.ProjectedRevenue*dbObj.ExpectedGrossMargin);
|
|
}
|
|
else
|
|
dbObj.TDDirectCosts = model.TDDirectCosts;
|
|
}
|
|
else
|
|
{
|
|
if (model.IsRevenueGenerating)
|
|
{
|
|
dbObj.ExpectedGrossMargin_LM = dbObj.CalculatedGrossMargin_LM;
|
|
dbObj.TDDirectCosts_LM = dbObj.ProjectedRevenue -
|
|
(dbObj.ProjectedRevenue*dbObj.ExpectedGrossMargin_LM);
|
|
}
|
|
else
|
|
dbObj.TDDirectCosts = model.TDDirectCosts;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (model.IsRevenueGenerating)
|
|
{
|
|
dbObj.TDDirectCosts = dbObj.ProjectedRevenue -
|
|
(dbObj.ProjectedRevenue*dbObj.ExpectedGrossMargin);
|
|
dbObj.TDDirectCosts_LM = dbObj.ProjectedRevenue -
|
|
(dbObj.ProjectedRevenue*dbObj.ExpectedGrossMargin_LM);
|
|
}
|
|
else
|
|
dbObj.TDDirectCosts = model.TDDirectCosts;
|
|
|
|
}
|
|
DbContext.Entry(dbObj).State = EntityState.Modified;
|
|
if (dbObj.Status == (int?) ScenarioStatus.Active)
|
|
{
|
|
var activeScenarios = (from c in DbContext.Scenarios
|
|
where
|
|
c.ParentId == dbObj.ParentId && dbObj.Type == c.Type &&
|
|
c.Status == (int?) ScenarioStatus.Active
|
|
select c).ToList();
|
|
activeScenarios.ForEach(scen =>
|
|
{
|
|
if (scen.Id != dbObj.Id)
|
|
scen.Status = (int?) ScenarioStatus.Inactive;
|
|
});
|
|
dbObj.Status = (int?) ScenarioStatus.Active;
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
|
|
DbContext.SaveChanges();
|
|
#endregion
|
|
|
|
return dbObj;
|
|
}
|
|
|
|
public ScenarioDetail LoadScenarioDetail(Guid? value, bool isReadOnly = true)
|
|
{
|
|
if (value == null || value == Guid.Empty)
|
|
return new ScenarioDetail();
|
|
|
|
return isReadOnly
|
|
? DbContext.ScenarioDetail.AsNoTracking().FirstOrDefault(t=>t.Id == value.Value)
|
|
: DbContext.ScenarioDetail.Find(value);
|
|
}
|
|
|
|
public void Save(ScenarioDetailModel model)
|
|
{
|
|
if (model == null)
|
|
throw new ArgumentNullException("model");
|
|
|
|
Scenario dbObj = null;
|
|
if (model.Id != Guid.Empty)
|
|
dbObj = DataTable.Find(model.Id);
|
|
if (dbObj == null)
|
|
{
|
|
throw new ArgumentNullException("model");
|
|
}
|
|
model.CopyTo(dbObj);
|
|
DbContext.Entry(dbObj).State = EntityState.Modified;
|
|
DbContext.SaveChanges();
|
|
}
|
|
|
|
/// <summary>
|
|
/// Copying actuals values to scenario details
|
|
/// </summary>
|
|
private void CopyActuals(Dictionary<Guid, List<ScenarioDetailsListItem>> calculatedNewScenario, VW_ScenarioAndProxyDetails[] actualScenarioDetails, DateTime mdtActualsEndWeek)
|
|
{
|
|
//zero out scenario details that have actuals...,
|
|
//prep before copying actuals...
|
|
foreach (var expCategory in calculatedNewScenario)
|
|
{
|
|
foreach (var scenarioDetailsListItem in expCategory.Value)
|
|
{
|
|
if (!(scenarioDetailsListItem.WeekEndingDate <= mdtActualsEndWeek))
|
|
break;
|
|
|
|
scenarioDetailsListItem.Quantity = 0;
|
|
scenarioDetailsListItem.DetailCost = 0;
|
|
}
|
|
}
|
|
foreach (var vwScenarioAndProxyDetailse in actualScenarioDetails)
|
|
{
|
|
if (calculatedNewScenario.ContainsKey(vwScenarioAndProxyDetailse.ExpenditureCategoryId.Value))
|
|
{
|
|
var row = calculatedNewScenario[vwScenarioAndProxyDetailse.ExpenditureCategoryId.Value];
|
|
var item = row.FirstOrDefault(t => t.WeekEndingDate == vwScenarioAndProxyDetailse.WeekEndingDate.Value);
|
|
if (item != null)
|
|
{
|
|
item.Quantity = vwScenarioAndProxyDetailse.Quantity ?? 0;
|
|
item.DetailCost = vwScenarioAndProxyDetailse.Cost ?? 0;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Calculates costs for calculated expenditure
|
|
/// </summary>
|
|
private void ComputeCalculatedCategories(Dictionary<Guid, List<ScenarioDetailsListItem>> calculatedNewScenario, TemporaryCalculationResults temporaryCalcResult,
|
|
DateTime mdtActualsEndWeek, int totalMilestones, int? duration, Dictionary<Guid, List<ScenarioDetailsListItem>> moCatRowTranslate, Dictionary<Guid, Dictionary<DateTime, decimal>> moRateTranslate)
|
|
{
|
|
temporaryCalcResult.mvActualCostCG_Fee = 0;
|
|
temporaryCalcResult.mvActualCostEFX_Fee = 0;
|
|
var feeCalculations =
|
|
DbContext.VW_Expenditure2FeeCalculation.Where(
|
|
t => t.MinShot <= totalMilestones && t.MaxShot >= totalMilestones);
|
|
var allCalculatedCategories = CalculatedExpenditureCategoryItem.LoadAllCalculatedCategories(DbContext);
|
|
decimal liWksSubject2Fee = 0;
|
|
decimal feeFactor = 0;
|
|
decimal week2StartFee = 0;
|
|
foreach (var expCatItem in calculatedNewScenario)
|
|
{
|
|
if (expCatItem.Value.Count > 0 && expCatItem.Value[0].UseType == ExpenditureCategoryModel.UseTypes.Fee)
|
|
{
|
|
foreach (var feeCalculation in feeCalculations)
|
|
{
|
|
if (feeCalculation.ExpenditureCategoryId == expCatItem.Key)
|
|
{
|
|
liWksSubject2Fee = feeCalculation.WksSubjectToFee ?? 0;
|
|
feeFactor = feeCalculation.Quantity;
|
|
week2StartFee = ((100 - liWksSubject2Fee)/100)*(duration ?? 0);
|
|
if (week2StartFee > (int) week2StartFee)
|
|
week2StartFee++;
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
for (var colIndex = 0; colIndex < expCatItem.Value.Count; colIndex++)
|
|
{
|
|
var scenarioDetailsListItem = expCatItem.Value[colIndex];
|
|
if (mdtActualsEndWeek < scenarioDetailsListItem.WeekEndingDate)
|
|
{
|
|
switch (expCatItem.Value[0].UseType)
|
|
{
|
|
case ExpenditureCategoryModel.UseTypes.Calculated:
|
|
var lstCalcCats = allCalculatedCategories[scenarioDetailsListItem.ExpenditureCategoryId];
|
|
scenarioDetailsListItem.Quantity = 0;
|
|
foreach (var calculatedExpenditureCategoryItem in lstCalcCats)
|
|
{
|
|
if (!calculatedExpenditureCategoryItem.ExpenditureId.HasValue ||
|
|
!calculatedNewScenario.ContainsKey(calculatedExpenditureCategoryItem.ExpenditureId.Value))
|
|
continue;
|
|
|
|
//var row = moCatRowTranslate[calculatedExpenditureCategoryItem.ExpenditureId.Value];
|
|
switch ((CalculatesCategoryModel.FactorTypes)calculatedExpenditureCategoryItem.FactorType)
|
|
{
|
|
case CalculatesCategoryModel.FactorTypes.Multipiy:
|
|
scenarioDetailsListItem.Quantity +=
|
|
calculatedNewScenario[calculatedExpenditureCategoryItem.ExpenditureId.Value][colIndex]
|
|
.Quantity*calculatedExpenditureCategoryItem.Factor;
|
|
break;
|
|
case CalculatesCategoryModel.FactorTypes.Divide:
|
|
scenarioDetailsListItem.Quantity +=
|
|
calculatedNewScenario[calculatedExpenditureCategoryItem.ExpenditureId.Value][colIndex]
|
|
.Quantity/calculatedExpenditureCategoryItem.Factor;
|
|
break;
|
|
}
|
|
}
|
|
scenarioDetailsListItem.DetailCost =
|
|
GetRate(scenarioDetailsListItem.ExpenditureCategoryId, mdtActualsEndWeek,
|
|
moRateTranslate)*scenarioDetailsListItem.Quantity;
|
|
if (ExpenditureCategoryModel.CgEfx.CG == scenarioDetailsListItem.CG_EFX)
|
|
temporaryCalcResult.mvActualCostCG += scenarioDetailsListItem.DetailCost;
|
|
else if (ExpenditureCategoryModel.CgEfx.EFX == scenarioDetailsListItem.CG_EFX)
|
|
temporaryCalcResult.mvActualCostEFX += scenarioDetailsListItem.DetailCost;
|
|
break;
|
|
case ExpenditureCategoryModel.UseTypes.Fee:
|
|
if ((colIndex + 1) > week2StartFee)
|
|
{
|
|
scenarioDetailsListItem.Quantity = feeFactor;
|
|
scenarioDetailsListItem.DetailCost =
|
|
GetRate(scenarioDetailsListItem.ExpenditureCategoryId,
|
|
scenarioDetailsListItem.WeekEndingDate.Value, moRateTranslate)*
|
|
scenarioDetailsListItem.Quantity;
|
|
}
|
|
else
|
|
{
|
|
if (week2StartFee > (int) week2StartFee && (colIndex + 1) == (int) week2StartFee)
|
|
{
|
|
scenarioDetailsListItem.Quantity = (1 - (week2StartFee - (int) week2StartFee)) * feeFactor;
|
|
scenarioDetailsListItem.DetailCost =
|
|
GetRate(scenarioDetailsListItem.ExpenditureCategoryId,
|
|
scenarioDetailsListItem.WeekEndingDate.Value, moRateTranslate)*
|
|
scenarioDetailsListItem.Quantity;
|
|
}
|
|
else
|
|
{
|
|
scenarioDetailsListItem.DetailCost = 0;
|
|
scenarioDetailsListItem.Quantity = 0;
|
|
}
|
|
}
|
|
if (ExpenditureCategoryModel.CgEfx.CG == scenarioDetailsListItem.CG_EFX)
|
|
temporaryCalcResult.mvActualCostCG_Fee += scenarioDetailsListItem.DetailCost;
|
|
else
|
|
temporaryCalcResult.mvActualCostEFX_Fee += scenarioDetailsListItem.DetailCost;
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
private void ComputeNONCalculatedCategories(Dictionary<Guid, List<ScenarioDetailsListItem>> calculatedNewScenario, TemporaryCalculationResults temporaryCalcResult, DateTime mdtActualsEndWeek, ScenarioModel model, int? duration, Dictionary<Guid, List<ScenarioDetailsListItem>> moCatRowTranslate, Dictionary<Guid, Dictionary<DateTime, decimal>> moRateTranslate)
|
|
{
|
|
temporaryCalcResult.mvActualCostCG = 0;
|
|
temporaryCalcResult.mvActualCostEFX = 0;
|
|
temporaryCalcResult.mvActualCostCG_LM = 0;
|
|
temporaryCalcResult.mvActualCostEFX_LM = 0;
|
|
|
|
foreach (var expCatItem in calculatedNewScenario)
|
|
{
|
|
if (expCatItem.Value[0].UseType == ExpenditureCategoryModel.UseTypes.Calculated ||
|
|
expCatItem.Value[0].UseType == ExpenditureCategoryModel.UseTypes.Fee)
|
|
{
|
|
foreach (var scenarioDetailsListItem in expCatItem.Value)
|
|
{
|
|
if (mdtActualsEndWeek < scenarioDetailsListItem.WeekEndingDate)
|
|
{
|
|
scenarioDetailsListItem.DetailCost =
|
|
GetRate(scenarioDetailsListItem.ExpenditureCategoryId, mdtActualsEndWeek,
|
|
moRateTranslate) * scenarioDetailsListItem.Quantity;
|
|
if (expCatItem.Value[0].CategoryType == ExpenditureCategoryModel.CategoryTypes.Labor ||
|
|
expCatItem.Value[0].CategoryType == ExpenditureCategoryModel.CategoryTypes.Materials)
|
|
{
|
|
if (ExpenditureCategoryModel.CgEfx.CG == scenarioDetailsListItem.CG_EFX)
|
|
{
|
|
temporaryCalcResult.mvActualCostCG_LM += scenarioDetailsListItem.DetailCost;
|
|
temporaryCalcResult.mvActualCostCG += scenarioDetailsListItem.DetailCost;
|
|
}
|
|
else
|
|
{
|
|
temporaryCalcResult.mvActualCostEFX_LM += scenarioDetailsListItem.DetailCost;
|
|
temporaryCalcResult.mvActualCostEFX += scenarioDetailsListItem.DetailCost;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (ExpenditureCategoryModel.CgEfx.CG == scenarioDetailsListItem.CG_EFX)
|
|
temporaryCalcResult.mvActualCostCG += scenarioDetailsListItem.DetailCost;
|
|
else
|
|
temporaryCalcResult.mvActualCostEFX += scenarioDetailsListItem.DetailCost;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Applies labor/materials split factor
|
|
/// </summary>
|
|
private void ApplyCGEFXFactor(Dictionary<Guid, List<ScenarioDetailsListItem>> calculatedNewScenario, TemporaryCalculationResults temporaryCalcResult, DateTime mdtActualsEndWeek, bool freezeResources, bool UseLMMargin = false)
|
|
{
|
|
foreach (var expCatItem in calculatedNewScenario)
|
|
{
|
|
if (UseLMMargin && materialsECs != null && materialsECs.Contains(expCatItem.Key)) continue;
|
|
foreach (var scenarioDetailsListItem in expCatItem.Value)
|
|
{
|
|
if (scenarioDetailsListItem.UseType != ExpenditureCategoryModel.UseTypes.Fee &&
|
|
mdtActualsEndWeek < scenarioDetailsListItem.WeekEndingDate &&
|
|
!freezeResources)
|
|
{
|
|
if (ExpenditureCategoryModel.CgEfx.CG == scenarioDetailsListItem.CG_EFX)
|
|
{
|
|
scenarioDetailsListItem.DetailCost = (scenarioDetailsListItem.DetailCost * temporaryCalcResult.lvCGFactor) +
|
|
scenarioDetailsListItem.DetailCost;
|
|
scenarioDetailsListItem.Quantity = (scenarioDetailsListItem.Quantity * temporaryCalcResult.lvCGFactor) +
|
|
scenarioDetailsListItem.Quantity;
|
|
}
|
|
else if (ExpenditureCategoryModel.CgEfx.EFX == scenarioDetailsListItem.CG_EFX)
|
|
{
|
|
scenarioDetailsListItem.DetailCost = (scenarioDetailsListItem.DetailCost * temporaryCalcResult.lvEFXFactor) +
|
|
scenarioDetailsListItem.DetailCost;
|
|
scenarioDetailsListItem.Quantity = (scenarioDetailsListItem.Quantity * temporaryCalcResult.lvEFXFactor) +
|
|
scenarioDetailsListItem.Quantity;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
private TemporaryCalculationResults ComputeNONCalculatedCategories(Dictionary<Guid, List<ScenarioDetailsListItem>> calculatedNewScenario, DateTime mdtActualsEndWeek, Dictionary<Guid, Dictionary<DateTime, decimal>> moRateTranslate, TemporaryCalculationResults temporaryCalcResult)
|
|
{
|
|
temporaryCalcResult.mvActualCostCG = 0M;
|
|
temporaryCalcResult.mvActualCostEFX = 0M;
|
|
temporaryCalcResult.mvActualCostCG_LM = 0M;
|
|
temporaryCalcResult.mvActualCostEFX_LM = 0M;
|
|
foreach (var item in calculatedNewScenario)
|
|
{
|
|
if (item.Value.Count > 0 && ExpenditureCategoryModel.UseTypes.Calculated != item.Value[0].UseType &&
|
|
ExpenditureCategoryModel.UseTypes.Fee != item.Value[0].UseType)
|
|
{
|
|
foreach (var scenarioDetailsListItem in item.Value)
|
|
{
|
|
if (mdtActualsEndWeek < scenarioDetailsListItem.WeekEndingDate.Value)
|
|
{
|
|
scenarioDetailsListItem.DetailCost =
|
|
GetRate(scenarioDetailsListItem.ExpenditureCategoryId,
|
|
scenarioDetailsListItem.WeekEndingDate.Value, moRateTranslate)*scenarioDetailsListItem.Quantity;
|
|
if (ExpenditureCategoryModel.CategoryTypes.Labor == scenarioDetailsListItem.CategoryType ||
|
|
ExpenditureCategoryModel.CategoryTypes.Materials == scenarioDetailsListItem.CategoryType)
|
|
{
|
|
if (ExpenditureCategoryModel.CgEfx.CG == scenarioDetailsListItem.CG_EFX)
|
|
{
|
|
temporaryCalcResult.mvActualCostCG_LM += scenarioDetailsListItem.DetailCost;
|
|
temporaryCalcResult.mvActualCostCG += scenarioDetailsListItem.DetailCost;
|
|
}
|
|
else if (
|
|
ExpenditureCategoryModel.CgEfx.EFX == scenarioDetailsListItem.CG_EFX)
|
|
{
|
|
temporaryCalcResult.mvActualCostEFX_LM += scenarioDetailsListItem.DetailCost;
|
|
temporaryCalcResult.mvActualCostEFX += scenarioDetailsListItem.DetailCost;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (ExpenditureCategoryModel.CgEfx.CG == scenarioDetailsListItem.CG_EFX)
|
|
temporaryCalcResult.mvActualCostCG += scenarioDetailsListItem.DetailCost;
|
|
else if (ExpenditureCategoryModel.CgEfx.EFX == scenarioDetailsListItem.CG_EFX)
|
|
temporaryCalcResult.mvActualCostEFX += scenarioDetailsListItem.DetailCost;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return temporaryCalcResult;
|
|
}
|
|
|
|
public Dictionary<Guid, Dictionary<DateTime, decimal>> LoadRates(Guid scenarioId)
|
|
{
|
|
var result = new Dictionary<Guid, Dictionary<DateTime, decimal>>();
|
|
if (scenarioId == Guid.Empty)
|
|
return result;
|
|
|
|
#region loading rates
|
|
// retrieve all local rates for the specified scenario
|
|
var localRates = DbContext.Rates.AsNoTracking().Where(t =>
|
|
t.ParentId == scenarioId && t.Type == (int)RateModel.RateType.Derived &&
|
|
t.ExpenditureCategoryId != null)
|
|
.Select(t => new
|
|
{
|
|
t.ExpenditureCategoryId,
|
|
t.EndDate,
|
|
t.Rate1
|
|
}).OrderBy(t => t.ExpenditureCategoryId)
|
|
.ThenBy(t => t.EndDate).ToArray();
|
|
// group rates by expenditure category id and store them as disctionary by expenditure category id
|
|
var localRatesLU = localRates.GroupBy(t => t.ExpenditureCategoryId)
|
|
.ToDictionary(key => key.Key, group => group.ToList());
|
|
// retrieve similar dictionary for global rates that does not referenced to already retrieved expenditures
|
|
var globalRatesQuery = DbContext.Rates.AsNoTracking().Where(t => t.Type == (int)RateModel.RateType.Global);
|
|
//TODO: we should validate SQL query to make it is correct and optimal
|
|
if (localRates.Length > 0)
|
|
globalRatesQuery = globalRatesQuery.Where(t => !localRatesLU.Keys.ToList().Contains(t.ExpenditureCategoryId));
|
|
var globalRates = globalRatesQuery
|
|
.Select(t => new
|
|
{
|
|
t.ExpenditureCategoryId,
|
|
t.EndDate,
|
|
t.Rate1
|
|
})
|
|
.OrderBy(t => t.ExpenditureCategoryId).ThenBy(t => t.EndDate)
|
|
.ToArray()
|
|
.GroupBy(t => t.ExpenditureCategoryId)
|
|
.ToDictionary(key => key.Key, group => group.ToList());
|
|
// append global rates to local rates
|
|
foreach (var globalRate in globalRates)
|
|
{
|
|
if (localRatesLU.ContainsKey(globalRate.Key))
|
|
throw new BLLException("Unable to add global rate to the list because it already contains a local rate with the same key");
|
|
localRatesLU.Add(globalRate.Key, globalRate.Value);
|
|
}
|
|
#endregion
|
|
|
|
result = localRatesLU.ToDictionary(t => t.Key, pair => pair.Value.ToDictionary(tt => tt.EndDate, arg => arg.Rate1));
|
|
|
|
return result;
|
|
}
|
|
public static decimal GetRate(Guid expenditureCategoryId, DateTime weekEndingDate, IReadOnlyDictionary<Guid, Dictionary<DateTime, decimal>> moRateTranslate)
|
|
{
|
|
if (moRateTranslate.Count > 0)
|
|
{
|
|
if (moRateTranslate.ContainsKey(expenditureCategoryId))
|
|
{
|
|
if (moRateTranslate[expenditureCategoryId].Values.Count > 0)
|
|
{
|
|
foreach (var rate in moRateTranslate[expenditureCategoryId].Where(rate => weekEndingDate <= rate.Key))
|
|
{
|
|
return rate.Value;
|
|
}
|
|
// if not found, use last rate in collection
|
|
return moRateTranslate[expenditureCategoryId].LastOrDefault().Value;
|
|
}
|
|
}
|
|
}
|
|
return 0;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Prepare a list of scenario details templates for each provided expenditure category.
|
|
/// </summary>
|
|
/// <param name="templateScenarioId">Id of the template scenario.</param>
|
|
/// <param name="plstExpCatsToAdd"></param>
|
|
/// <param name="newScenarioId"></param>
|
|
/// <param name="plstExpCatsToExclude"></param>
|
|
/// <returns></returns>
|
|
private Dictionary<Guid, List<ScenarioDetailsListItem>> GetTemplateScenarioDetailsPerExpCat(Guid templateScenarioId, IReadOnlyCollection<AdditionalExpCatItem> plstExpCatsToAdd, Guid newScenarioId,
|
|
ICollection<Guid> plstExpCatsToExclude)
|
|
{
|
|
var result = new Dictionary<Guid, List<ScenarioDetailsListItem>>();
|
|
if (templateScenarioId == Guid.Empty)
|
|
return result;
|
|
|
|
var query = DbContext.VW_ScenarioAndProxyDetails.Where(t => t.ParentID == templateScenarioId);
|
|
if (plstExpCatsToExclude.Count > 0)
|
|
query =
|
|
query.Where(
|
|
t =>
|
|
t.ExpenditureCategoryId != null && !plstExpCatsToExclude.Contains(t.ExpenditureCategoryId.Value));
|
|
query = query.OrderBy(t => t.UseType).ThenBy(t => t.ExpenditureCategoryId).ThenBy(t => t.WeekOrdinal);
|
|
var lvaRtnArray = query.ToArray();
|
|
if (lvaRtnArray.Length + plstExpCatsToAdd.Count == 0)
|
|
return result;
|
|
var scenarioDetailsNumber = 0;
|
|
var scenarioDetailExpCatNumber = lvaRtnArray.Select(t=>t.ExpenditureCategoryId).Distinct().Count();
|
|
if (lvaRtnArray.Length > 0)
|
|
{
|
|
scenarioDetailsNumber = lvaRtnArray.GroupBy(detail => detail.ExpenditureCategoryId)
|
|
.Select(grouping => new
|
|
{
|
|
ExpenditureCategoryId = grouping.Key,
|
|
ScenarioDetailsCount = grouping.Count()
|
|
}).Max(item => item.ScenarioDetailsCount);
|
|
}
|
|
if (scenarioDetailsNumber == 0)
|
|
scenarioDetailsNumber = 52; // 52 weeks by default (~1 year)
|
|
result = new Dictionary<Guid, List<ScenarioDetailsListItem>>(scenarioDetailExpCatNumber + plstExpCatsToAdd.Count);
|
|
|
|
// first of all fill the result list with not calculated expenditure categories
|
|
foreach (var item in plstExpCatsToAdd.Where(t=>t.UseType != ExpenditureCategoryModel.UseTypes.Calculated))
|
|
{
|
|
var expCatGroup = new List<ScenarioDetailsListItem>(scenarioDetailsNumber);
|
|
for (var i = 0; i < scenarioDetailsNumber; i++)
|
|
{
|
|
expCatGroup.Add(new ScenarioDetailsListItem
|
|
{
|
|
Id = Guid.Empty,
|
|
ParentId = newScenarioId == Guid.Empty ? templateScenarioId : newScenarioId,
|
|
ExpenditureCategoryId = item.ExpenditureCategoryId,
|
|
WeekEndingDate = null,
|
|
Quantity = item.Quantity,
|
|
LastUpdate = item.LastUpdate,
|
|
WeekOrdinal = item.WeekOrdinal,
|
|
DetailCost = item.DetailCost,
|
|
ExpenditureCategoryName = item.CategoryName,
|
|
GlId = item.GlId,
|
|
UOMId = item.UOMId,
|
|
CreditId = item.CreditId,
|
|
CategoryType = item.CategoryType,
|
|
UseType = item.UseType,
|
|
CG_EFX = item.CG_EFX,
|
|
SysField1 = item.SysField1,
|
|
SysField2 = item.SysField2,
|
|
SortOrder = item.SortOrder
|
|
});
|
|
}
|
|
result.Add(item.ExpenditureCategoryId, expCatGroup);
|
|
}
|
|
// then fill the result list with scenario details from template
|
|
foreach (var item in lvaRtnArray)
|
|
{
|
|
var details = new ScenarioDetailsListItem
|
|
{
|
|
Id = item.Id,
|
|
ParentId = item.ParentID ?? Guid.Empty,
|
|
ExpenditureCategoryId = item.ExpenditureCategoryId.Value,
|
|
WeekEndingDate = item.WeekEndingDate,
|
|
Quantity = item.Quantity ?? 0,
|
|
LastUpdate = item.LastUpdate ?? DateTime.Today,
|
|
WeekOrdinal = item.WeekOrdinal ?? 0,
|
|
DetailCost = item.Cost ?? 0,
|
|
ExpenditureCategoryName = item.ExpenditureCategoryName,
|
|
GlId = item.GLId,
|
|
UOMId = item.UOMId,
|
|
CreditId = item.CreditId,
|
|
CategoryType = (ExpenditureCategoryModel.CategoryTypes)(item.Type ?? 0),
|
|
UseType = (ExpenditureCategoryModel.UseTypes)(item.UseType ?? 1),
|
|
CG_EFX = (item.CGEFX ?? string.Empty).Trim().Equals(ExpenditureCategoryModel.CgEfx.CG.ToString()) ? ExpenditureCategoryModel.CgEfx.CG : ExpenditureCategoryModel.CgEfx.EFX,
|
|
SysField1 = item.SystemAttributeOne,
|
|
SysField2 = item.SystemAttributeTwo ?? Guid.Empty,
|
|
SortOrder = item.SortOrder
|
|
};
|
|
if (!result.ContainsKey(details.ExpenditureCategoryId))
|
|
result.Add(details.ExpenditureCategoryId, new List<ScenarioDetailsListItem>(scenarioDetailsNumber));
|
|
result[details.ExpenditureCategoryId].Add(details);
|
|
}
|
|
// then fill the result list with scenario details of calculated template
|
|
foreach (var item in plstExpCatsToAdd.Where(t => t.UseType == ExpenditureCategoryModel.UseTypes.Calculated))
|
|
{
|
|
var expCatGroup = new List<ScenarioDetailsListItem>(scenarioDetailsNumber);
|
|
for (var i = 0; i < scenarioDetailsNumber; i++)
|
|
{
|
|
expCatGroup.Add(new ScenarioDetailsListItem
|
|
{
|
|
Id = Guid.Empty,
|
|
ParentId = newScenarioId == Guid.Empty ? templateScenarioId : newScenarioId,
|
|
ExpenditureCategoryId = item.ExpenditureCategoryId,
|
|
WeekEndingDate = null,
|
|
Quantity = item.Quantity,
|
|
LastUpdate = item.LastUpdate,
|
|
WeekOrdinal = item.WeekOrdinal,
|
|
DetailCost = item.DetailCost,
|
|
ExpenditureCategoryName = item.CategoryName,
|
|
GlId = item.GlId,
|
|
UOMId = item.UOMId,
|
|
CreditId = item.CreditId,
|
|
CategoryType = item.CategoryType,
|
|
UseType = item.UseType,
|
|
CG_EFX = item.CG_EFX,
|
|
SysField1 = item.SysField1,
|
|
SysField2 = item.SysField2,
|
|
SortOrder = item.SortOrder
|
|
});
|
|
}
|
|
result.Add(item.ExpenditureCategoryId, expCatGroup);
|
|
}
|
|
return result;
|
|
}
|
|
/// <summary>
|
|
/// Returns a dictionary where Key = ExpenditureCategoryId, Value = List<ParentId>
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public Dictionary<Guid, List<Guid>> GetExpWhereUsedForCalculation()
|
|
{
|
|
var items = DbContext.VW_Expenditure2Calculation.AsNoTracking().Select(t=> new {t.ExpenditureCategoryID, t.ParentId})
|
|
.OrderBy(t => t.ExpenditureCategoryID).ThenBy(t => t.ParentId)
|
|
.GroupBy(t => t.ExpenditureCategoryID)
|
|
.ToDictionary(t => t.Key, group => group.ToList())
|
|
.ToDictionary(item => item.Key.Value, item => item.Value.Select(t => t.ParentId).ToList());
|
|
return items;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Sets Bottom Up costs upon scenario creation or editing.
|
|
/// </summary>
|
|
|
|
public void SetBottomUpCosts(ScenarioModel model)
|
|
{
|
|
if (model == null)
|
|
throw new ArgumentNullException("model");
|
|
if (model.Id == Guid.Empty)
|
|
throw new ArgumentException("Unable to recalculate bottom-up costs because model does not have Id specified.");
|
|
|
|
var scenario = (from s in DbContext.Scenarios where s.Id == model.Id select s).FirstOrDefault();
|
|
SetBottomUpCosts(scenario, model.IsRevenueGenerating);
|
|
}
|
|
|
|
public void SetBottomUpCosts(Guid id)
|
|
{
|
|
if (id == Guid.Empty)
|
|
throw new ArgumentException("Unable to recalculate bottom-up costs because Id is Guid.Empty.");
|
|
|
|
var scenario = (from s in DbContext.Scenarios where s.Id == id select s).FirstOrDefault();
|
|
var model = (ScenarioModel)scenario;
|
|
SetBottomUpCosts(scenario, model.IsRevenueGenerating);
|
|
}
|
|
|
|
public void SetBottomUpCosts(Scenario scenario)
|
|
{
|
|
if (scenario == null)
|
|
throw new ArgumentException("Unable to recalculate bottom-up costs because scenario is null.");
|
|
|
|
var model = (ScenarioModel)scenario;
|
|
SetBottomUpCosts(scenario, model.IsRevenueGenerating);
|
|
}
|
|
|
|
/// <summary>
|
|
/// Recalculate scenario bottom-up costs for the specified scenario. DbContext submission required.
|
|
/// </summary>
|
|
/// <param name="scenario">The scenario that should be recalculated.</param>
|
|
/// <param name="isRevenueGeneratingScenario">Indicates whether scenario is revenue generating or not.</param>
|
|
public void SetBottomUpCosts(Scenario scenario, bool isRevenueGeneratingScenario)
|
|
{
|
|
if (scenario == null)
|
|
throw new ArgumentNullException("scenario");
|
|
if (scenario.Id == Guid.Empty)
|
|
throw new ArgumentException("Unable to recalculate bottom-up costs because scenario does not have Id specified.");
|
|
|
|
if (scenario.Type == (int)ScenarioType.Portfolio || scenario.Type == (int)ScenarioType.Scheduling)
|
|
{
|
|
var data = (from sd in DbContext.ScenarioDetail
|
|
join vw in DbContext.VW_Expenditure2Category on sd.ExpenditureCategoryId equals vw.Id
|
|
where sd.ParentID == scenario.Id
|
|
select new
|
|
{
|
|
sd.Id,
|
|
sd.Cost,
|
|
ExpenditureType = vw.Type,
|
|
ScenarioId = sd.ParentID,
|
|
}).Distinct().ToArray();
|
|
var totalBtUpCosts = data.Sum(t => t.Cost);
|
|
var totalBtUpCostsLM = data.Where(t =>
|
|
t.ExpenditureType == (int) ExpenditureCategoryModel.CategoryTypes.Labor ||
|
|
t.ExpenditureType == (int) ExpenditureCategoryModel.CategoryTypes.Materials).Sum(x => x.Cost);
|
|
|
|
scenario.BUDirectCosts = totalBtUpCosts;
|
|
scenario.BURevenueShot = scenario.BUDirectCosts / scenario.Shots;
|
|
|
|
//ENV-458 Suggest Margin When GrossMargin is set only
|
|
if (isRevenueGeneratingScenario && !scenario.ProjectedRevenue.HasValue && scenario.ExpectedGrossMargin > 0)
|
|
{
|
|
scenario.ProjectedRevenue = (scenario.ExpectedGrossMargin * scenario.BUDirectCosts / (1 - scenario.ExpectedGrossMargin)) + scenario.BUDirectCosts;
|
|
}
|
|
else if (isRevenueGeneratingScenario && scenario.ProjectedRevenue.HasValue && scenario.ExpectedGrossMargin == 0)
|
|
{
|
|
scenario.ExpectedGrossMargin = 1 - scenario.BUDirectCosts / scenario.ProjectedRevenue;
|
|
}
|
|
|
|
scenario.CalculatedGrossMargin = isRevenueGeneratingScenario ? (scenario.ProjectedRevenue - scenario.BUDirectCosts) / scenario.ProjectedRevenue : 0;
|
|
|
|
scenario.BUDirectCosts_LM = totalBtUpCostsLM;
|
|
scenario.BURevenueShot_LM = scenario.BUDirectCosts_LM / scenario.Shots;
|
|
scenario.CalculatedGrossMargin_LM = isRevenueGeneratingScenario ? (scenario.ProjectedRevenue - scenario.BUDirectCosts_LM) / scenario.ProjectedRevenue : 0;
|
|
|
|
//get actuals scenario
|
|
var actualsscen = (from p in DbContext.Scenarios
|
|
where p.ParentId == scenario.ParentId && p.Type == (int)ScenarioType.Actuals
|
|
select new { ActualsId = p.Id, ActualsSatrtDate = p.StartDate, ActualsEndDate = p.EndDate }).FirstOrDefault();
|
|
if (!scenario.GrowthScenario && actualsscen != null)
|
|
{
|
|
var actualsId = actualsscen.ActualsId;
|
|
var actualsSatrtDate = actualsscen.ActualsSatrtDate;
|
|
var actualsEndDate = actualsscen.ActualsEndDate;
|
|
|
|
var actualsData = (from p in DbContext.ScenarioDetail
|
|
join vw in DbContext.VW_Expenditure2Category on p.ExpenditureCategoryId equals vw.Id
|
|
where (p.ParentID == actualsId && p.WeekEndingDate <= actualsSatrtDate && p.WeekEndingDate <= actualsEndDate) ||
|
|
(p.ParentID == scenario.Id && p.WeekEndingDate > actualsEndDate)
|
|
select new {p.Cost, vw.Type});
|
|
|
|
scenario.Actuals_BUDirectCosts = actualsData.Sum(t => t.Cost);
|
|
scenario.Actuals_BUDirectCosts_LM = actualsData.Where(t =>
|
|
t.Type == (int) ExpenditureCategoryModel.CategoryTypes.Labor ||
|
|
t.Type == (int) ExpenditureCategoryModel.CategoryTypes.Materials).Sum(x => x.Cost);
|
|
}
|
|
DbContext.Entry(scenario).State = EntityState.Modified;
|
|
}
|
|
else if (scenario.Type == (int)ScenarioType.Actuals)
|
|
{
|
|
var data = (from sd in DbContext.ScenarioDetail
|
|
join vw in DbContext.VW_Expenditure2Category on sd.ExpenditureCategoryId equals vw.Id
|
|
where sd.ParentID == scenario.Id && sd.WeekEndingDate <= scenario.EndDate
|
|
select new {sd.Cost, ExpenditureType = vw.Type});
|
|
var actualTotalBtUpCosts = data.Sum(t => t.Cost);
|
|
var actualTotalBtUpCostsLM = data.Where(t =>
|
|
t.ExpenditureType == (int)ExpenditureCategoryModel.CategoryTypes.Labor ||
|
|
t.ExpenditureType == (int)ExpenditureCategoryModel.CategoryTypes.Materials).Sum(x => x.Cost);
|
|
|
|
scenario.BUDirectCosts = actualTotalBtUpCosts;
|
|
scenario.BUDirectCosts_LM = actualTotalBtUpCostsLM;
|
|
scenario.Actuals_BUDirectCosts = actualTotalBtUpCosts;
|
|
scenario.Actuals_BUDirectCosts_LM = actualTotalBtUpCostsLM;
|
|
DbContext.Entry(scenario).State = EntityState.Modified;
|
|
|
|
//recalculate buttom up costs of all portfolio, efc, cg scenarios under the same show as the actuals scenario.
|
|
var scenarios = (from s in DbContext.Scenarios where s.ParentId == scenario.ParentId && (s.Type == (int)ScenarioType.Portfolio || s.Type == (int)ScenarioType.Scheduling) && s.Status != (int)ScenarioStatus.Draft && !s.GrowthScenario select s).ToList();
|
|
foreach (var scen in scenarios)
|
|
{
|
|
var itemScenario = scen;
|
|
var actualsData = (from sd in DbContext.ScenarioDetail
|
|
join vw in DbContext.VW_Expenditure2Category on sd.ExpenditureCategoryId equals vw.Id
|
|
where sd.ParentID == scenario.Id && sd.WeekEndingDate >= scenario.StartDate
|
|
select new { sd.Cost, ExpenditureType = vw.Type, });
|
|
var subTotal = actualsData.Sum(t => t.Cost);
|
|
var subTotalLM = actualsData.Where(t =>
|
|
t.ExpenditureType == (int)ExpenditureCategoryModel.CategoryTypes.Labor ||
|
|
t.ExpenditureType == (int)ExpenditureCategoryModel.CategoryTypes.Materials).Sum(t => t.Cost);
|
|
|
|
itemScenario.Actuals_BUDirectCosts = subTotal;// actualTotalBtUpCosts +
|
|
itemScenario.Actuals_BUDirectCosts_LM = subTotalLM; //actualTotalBtUpCostsLM +
|
|
DbContext.Entry(itemScenario).State = EntityState.Modified;
|
|
}
|
|
}
|
|
}
|
|
|
|
public class BottomUpCostsDataItem
|
|
{
|
|
public decimal Cost { get; set; }
|
|
public ExpenditureCategoryModel.CategoryTypes Type { get; set; }
|
|
public Guid ScenarioId { get; set; }
|
|
}
|
|
|
|
/// <summary>
|
|
/// Recalculate scenario bottom-up costs for the specified scenario. DbContext submission required.
|
|
/// </summary>
|
|
/// <param name="scenario">The scenario which bottom-up costs should be recalculated.</param>
|
|
/// <param name="isRevenueGeneratingScenario">Indicates whether scenario is revenue generating or not.</param>
|
|
/// <param name="scenarioDetailItems">A list of all scenario detail records of <paramref name="scenario"/>.</param>
|
|
/// <param name="actualScenarioDetailItems">A li st of all scenario detail records of actuals scenario referenced to the same project as <paramref name="scenario"/>.</param>
|
|
private void UpdateScenarioBottomUpCosts(Scenario scenario, bool isRevenueGeneratingScenario, List<BottomUpCostsDataItem> scenarioDetailItems, List<BottomUpCostsDataItem> actualScenarioDetailItems = null)
|
|
{
|
|
if (scenario == null)
|
|
throw new ArgumentNullException("scenario");
|
|
if (scenario.Id == Guid.Empty)
|
|
throw new ArgumentException("Unable to recalculate bottom-up costs because scenario does not have Id specified.");
|
|
|
|
var totalBtUpCosts = scenarioDetailItems.Sum(t => t.Cost);
|
|
var totalBtUpCostsLM = scenarioDetailItems.Where(t =>
|
|
t.Type == ExpenditureCategoryModel.CategoryTypes.Labor ||
|
|
t.Type == ExpenditureCategoryModel.CategoryTypes.Materials).Sum(x => x.Cost);
|
|
|
|
scenario.BUDirectCosts = totalBtUpCosts;
|
|
scenario.BURevenueShot = scenario.BUDirectCosts / scenario.Shots;
|
|
scenario.CalculatedGrossMargin = isRevenueGeneratingScenario ? (scenario.ProjectedRevenue - scenario.BUDirectCosts) / scenario.ProjectedRevenue : 0;
|
|
scenario.BUDirectCosts_LM = totalBtUpCostsLM;
|
|
scenario.BURevenueShot_LM = scenario.BUDirectCosts_LM / scenario.Shots;
|
|
scenario.CalculatedGrossMargin_LM = isRevenueGeneratingScenario ? (scenario.ProjectedRevenue - scenario.BUDirectCosts_LM) / scenario.ProjectedRevenue : 0;
|
|
|
|
if (actualScenarioDetailItems != null)
|
|
{
|
|
var actualTotalBtUpCosts = actualScenarioDetailItems.Sum(t => t.Cost);
|
|
var actualTotalBtUpCostsLM = actualScenarioDetailItems.Where(t =>
|
|
t.Type == ExpenditureCategoryModel.CategoryTypes.Labor ||
|
|
t.Type == ExpenditureCategoryModel.CategoryTypes.Materials).Sum(x => x.Cost);
|
|
scenario.Actuals_BUDirectCosts = actualTotalBtUpCosts;
|
|
scenario.Actuals_BUDirectCosts_LM = actualTotalBtUpCostsLM;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Copy scenario
|
|
/// </summary>
|
|
/// <param name="id">Scenario Id</param>
|
|
/// <param name="checkedProject">Project Id</param>
|
|
/// <param name="copyStatus">Status of the copyed scenario</param>
|
|
/// <returns>Id of the new scenario</returns>
|
|
public Guid CopyTo(Guid id, Guid? checkedProject, ScenarioStatus copyStatus)
|
|
{
|
|
var newId = Guid.Empty;
|
|
var scenario = DbContext.Scenarios.FirstOrDefault(x => x.Id == id);
|
|
if (scenario != null)
|
|
{
|
|
var copiedScenario = DbContext.Scenarios.Create();
|
|
newId = copiedScenario.Id = Guid.NewGuid();
|
|
if (scenario.ParentId == checkedProject)
|
|
{
|
|
copiedScenario.Name = "Copy " + scenario.Name;
|
|
}
|
|
else
|
|
{
|
|
copiedScenario.Name = scenario.Name;
|
|
}
|
|
copiedScenario.ParentId = checkedProject;
|
|
copiedScenario.TemplateId = scenario.TemplateId;
|
|
copiedScenario.Type = scenario.Type;
|
|
|
|
copiedScenario.ProjectedRevenue = scenario.ProjectedRevenue;
|
|
copiedScenario.ExpectedGrossMargin = scenario.ExpectedGrossMargin;
|
|
copiedScenario.CalculatedGrossMargin = scenario.CalculatedGrossMargin;
|
|
copiedScenario.CGSplit = scenario.CGSplit;
|
|
copiedScenario.EFXSplit = scenario.EFXSplit;
|
|
copiedScenario.StartDate = scenario.StartDate;
|
|
copiedScenario.EndDate = scenario.EndDate;
|
|
copiedScenario.Duration = scenario.Duration;
|
|
copiedScenario.TDDirectCosts = scenario.TDDirectCosts;
|
|
copiedScenario.BUDirectCosts = scenario.BUDirectCosts;
|
|
copiedScenario.Shots = scenario.Shots;
|
|
copiedScenario.TDRevenueShot = scenario.TDRevenueShot;
|
|
copiedScenario.BURevenueShot = scenario.BURevenueShot;
|
|
copiedScenario.FreezeRevenue = scenario.FreezeRevenue;
|
|
copiedScenario.Color = scenario.Color;
|
|
if (scenario.Status == (int) ScenarioStatus.Draft) // we cannot copy draft scenario as not draft
|
|
copiedScenario.Status = (int) ScenarioStatus.Draft;
|
|
else
|
|
copiedScenario.Status = (int?)copyStatus;
|
|
copiedScenario.UseLMMargin = scenario.UseLMMargin;
|
|
copiedScenario.ExpectedGrossMargin_LM = scenario.ExpectedGrossMargin_LM;
|
|
copiedScenario.CalculatedGrossMargin_LM = scenario.CalculatedGrossMargin_LM;
|
|
copiedScenario.TDDirectCosts_LM = scenario.TDDirectCosts_LM;
|
|
copiedScenario.BUDirectCosts_LM = scenario.BUDirectCosts_LM;
|
|
copiedScenario.BURevenueShot_LM = scenario.BURevenueShot_LM;
|
|
copiedScenario.ShotStartDate = scenario.ShotStartDate;
|
|
copiedScenario.GrowthScenario = scenario.GrowthScenario;
|
|
copiedScenario.Actuals_BUDirectCosts = scenario.Actuals_BUDirectCosts;
|
|
copiedScenario.Actuals_BUDirectCosts_LM = scenario.Actuals_BUDirectCosts_LM;
|
|
copiedScenario.SystemAttributeObjectID = scenario.SystemAttributeObjectID;
|
|
|
|
DbContext.Entry(copiedScenario).State = EntityState.Added;
|
|
|
|
if (copiedScenario.Status == (int)ScenarioStatus.Active)
|
|
{
|
|
var activeScenarios = (from c in DbContext.Scenarios
|
|
where c.ParentId == copiedScenario.ParentId &&
|
|
copiedScenario.Type == c.Type && c.Status == (int?)ScenarioStatus.Active
|
|
select c).ToList();
|
|
|
|
foreach (var activeScenario in activeScenarios)
|
|
{
|
|
activeScenario.Status = (int?)ScenarioStatus.Inactive;
|
|
DbContext.Entry(activeScenario).State = EntityState.Modified;
|
|
}
|
|
}
|
|
|
|
DbContext.SaveChanges();
|
|
|
|
foreach (var scenarioDetails in DbContext.ScenarioDetail.Where(s => s.ParentID == id).ToList())
|
|
{
|
|
|
|
var copiedScenarioDetails = DbContext.ScenarioDetail.Create();
|
|
copiedScenarioDetails.Cost = scenarioDetails.Cost;
|
|
copiedScenarioDetails.Id = Guid.NewGuid();
|
|
copiedScenarioDetails.ParentID = copiedScenario.Id;
|
|
copiedScenarioDetails.ExpenditureCategoryId = scenarioDetails.ExpenditureCategoryId;
|
|
copiedScenarioDetails.WeekEndingDate = scenarioDetails.WeekEndingDate;
|
|
copiedScenarioDetails.Quantity = scenarioDetails.Quantity;
|
|
copiedScenarioDetails.WeekOrdinal = scenarioDetails.WeekOrdinal;
|
|
|
|
DbContext.Entry(copiedScenarioDetails).State = EntityState.Added;
|
|
}
|
|
|
|
foreach (var scenarioRate in DbContext.Rates.Where(s => s.ParentId == id).ToList())
|
|
{
|
|
var copiedScenarioRate = DbContext.Rates.Create();
|
|
copiedScenarioRate.Id = Guid.NewGuid();
|
|
copiedScenarioRate.ParentId = copiedScenario.Id;
|
|
copiedScenarioRate.ExpenditureCategoryId = scenarioRate.ExpenditureCategoryId;
|
|
copiedScenarioRate.Rate1 = scenarioRate.Rate1;
|
|
copiedScenarioRate.StartDate = scenarioRate.StartDate;
|
|
copiedScenarioRate.EndDate = scenarioRate.EndDate;
|
|
copiedScenarioRate.FreezeRate = scenarioRate.FreezeRate;
|
|
copiedScenarioRate.Type = scenarioRate.Type;
|
|
copiedScenarioRate.DerivedId = scenarioRate.DerivedId;
|
|
|
|
DbContext.Entry(copiedScenarioRate).State = EntityState.Added;
|
|
}
|
|
|
|
DbContext.SaveChanges();
|
|
}
|
|
|
|
//delete from History where EntityId = @aScenarioOID
|
|
//delete from Note where ParentId = @aScenarioOID
|
|
//delete from Scenario_Snapshot where ParentID = @aScenarioOID
|
|
//delete from Scenario_Wide where ScenarioId = @aScenarioOID
|
|
//delete from ScenarioAccess where ScenarioAccess.ParentId = @aScenarioOID
|
|
|
|
//delete from Scenario where id = @aScenarioOID
|
|
//delete from Rate where ParentId = @aScenarioOID
|
|
//delete from ScenarioDetail where ScenarioDetail.ParentID = @aScenarioOID
|
|
|
|
return newId;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Updates costs of referenced scenario details records and recalculate bottom-up costs of related scenarios.
|
|
/// </summary>
|
|
/// <param name="rate">A <see cref="Rate"/> object that has been created or updated.</param>
|
|
public void ApplyRateAndRecalculateScenarios(Rate rate)
|
|
{
|
|
if (rate.ExpenditureCategoryId == Guid.Empty)
|
|
throw new ArgumentException("Rate's expenditure category is not set.", "rate");
|
|
if (!rate.ParentId.HasValue)
|
|
throw new ArgumentException("Rate's parent Id is not set.", "rate");
|
|
|
|
var expCatId = rate.ExpenditureCategoryId;
|
|
var parentId = rate.ParentId.Value;
|
|
var start = rate.StartDate;
|
|
var end = rate.EndDate;
|
|
|
|
#region update scenario details and gather scenarios to recalculate bottom-up costs
|
|
|
|
// if it's a local rate
|
|
if (rate.Type == (short)RateModel.RateType.Derived)
|
|
{
|
|
var scenario = Load(parentId, false);
|
|
var isRevenueGenerating = (scenario.Project == null || scenario.Project.Type == null ||
|
|
scenario.Project.IsRevenueGenerating);
|
|
DateTime? lastRateEndDate = DbContext.Rates.Where(t => t.ExpenditureCategoryId == expCatId &&
|
|
t.Type == (short)RateModel.RateType.Derived &&
|
|
t.ParentId == parentId)
|
|
.DefaultIfEmpty().Max(t => null == t ? DateTime.MinValue : t.EndDate);
|
|
var detailItems = (from sd in DbContext.ScenarioDetail
|
|
join vw in DbContext.VW_Expenditure2Category on sd.ExpenditureCategoryId equals vw.Id
|
|
where sd.ParentID == parentId & sd.ExpenditureCategoryId == expCatId
|
|
select new
|
|
{
|
|
ScenarioDetail = sd,
|
|
ExpenditureType = vw.Type
|
|
});
|
|
var totalCostDelta = 0M;
|
|
var totalCostLMDelta = 0M;
|
|
foreach (var item in detailItems)
|
|
{
|
|
if (
|
|
(item.ScenarioDetail.WeekEndingDate >= start && item.ScenarioDetail.WeekEndingDate <= end) || // if week is in rate interval
|
|
(lastRateEndDate == end && item.ScenarioDetail.WeekEndingDate > end) // if this is a latest rate and week is later than rate's end date
|
|
)
|
|
{
|
|
var newCost = (item.ScenarioDetail.Quantity ?? 0)*rate.Rate1;
|
|
if (newCost != item.ScenarioDetail.Cost)
|
|
{
|
|
totalCostDelta += newCost - (item.ScenarioDetail.Cost ?? 0);
|
|
if (item.ExpenditureType == (int)ExpenditureCategoryModel.CategoryTypes.Labor ||
|
|
item.ExpenditureType == (int)ExpenditureCategoryModel.CategoryTypes.Materials)
|
|
totalCostLMDelta += newCost - (item.ScenarioDetail.Cost ?? 0);
|
|
item.ScenarioDetail.Cost = newCost;
|
|
item.ScenarioDetail.LastUpdate = DateTime.Now;
|
|
DbContext.Entry(item.ScenarioDetail).State = EntityState.Modified;
|
|
}
|
|
}
|
|
}
|
|
scenario.BUDirectCosts += totalCostDelta;
|
|
scenario.BURevenueShot = scenario.BUDirectCosts / scenario.Shots;
|
|
scenario.CalculatedGrossMargin = isRevenueGenerating ? (scenario.ProjectedRevenue - scenario.BUDirectCosts) / scenario.ProjectedRevenue : 0;
|
|
scenario.BUDirectCosts_LM += totalCostLMDelta;
|
|
scenario.BURevenueShot_LM = scenario.BUDirectCosts_LM / scenario.Shots;
|
|
scenario.CalculatedGrossMargin_LM = isRevenueGenerating ? (scenario.ProjectedRevenue - scenario.BUDirectCosts_LM) / scenario.ProjectedRevenue : 0;
|
|
DbContext.Entry(scenario).State = EntityState.Modified;
|
|
}
|
|
else if (rate.Type == (short)RateModel.RateType.Global)
|
|
{
|
|
DateTime? lastRateEndDate = DbContext.Rates.Where(t =>
|
|
t.ExpenditureCategoryId == expCatId &&
|
|
t.Type == (short)RateModel.RateType.Global)
|
|
.DefaultIfEmpty().Max(t => null == t ? DateTime.MinValue : t.EndDate);
|
|
var scenarioIds = (from sd in DbContext.ScenarioDetail
|
|
where sd.ExpenditureCategoryId == expCatId
|
|
select sd.ParentID).Distinct().ToArray();
|
|
//TODO: consider ability to update data only for active scenarios
|
|
var scenarios = (from s in DbContext.Scenarios
|
|
where s.Status != (int)ScenarioStatus.Draft &
|
|
(s.Type == (int)ScenarioType.Portfolio || s.Type == (int)ScenarioType.Scheduling)
|
|
select new
|
|
{
|
|
Scenario = s,
|
|
IsRevenueGenerating = (s.Project == null || s.Project.Type == null || s.Project.IsRevenueGenerating)
|
|
}).ToDictionary(key=>key.Scenario.Id, element => element);
|
|
|
|
var detailItems = (from sd in DbContext.ScenarioDetail
|
|
join vw in DbContext.VW_Expenditure2Category on sd.ExpenditureCategoryId equals vw.Id
|
|
where sd.ExpenditureCategoryId == expCatId & scenarioIds.Contains(sd.ParentID)
|
|
select new
|
|
{
|
|
ScenarioDetail = sd,
|
|
ExpenditureType = vw.Type
|
|
}).ToArray();
|
|
var localRates = (from localRate in DbContext.Rates
|
|
where localRate.Type == (int)RateModel.RateType.Derived &
|
|
localRate.ParentId.HasValue &
|
|
scenarioIds.Contains(localRate.ParentId.Value) &
|
|
localRate.ExpenditureCategoryId == expCatId
|
|
select new {localRate.ParentId, localRate.Rate1, localRate.StartDate, localRate.EndDate}).ToArray();
|
|
foreach (var scenarioItem in scenarios)
|
|
{
|
|
var scenarioId = scenarioItem.Key;
|
|
var scenario = scenarioItem.Value.Scenario;
|
|
var scenarioLocalRates = localRates.Where(t => t.ParentId == scenarioId).ToArray();
|
|
var scenarioDetailItems = detailItems.Where(t =>
|
|
t.ScenarioDetail.ParentID == scenarioId &&
|
|
!scenarioLocalRates.Any(x =>
|
|
x.StartDate <= t.ScenarioDetail.WeekEndingDate &&
|
|
x.EndDate >= t.ScenarioDetail.WeekEndingDate));
|
|
var totalCostDelta = 0M;
|
|
var totalCostLMDelta = 0M;
|
|
foreach (var item in scenarioDetailItems)
|
|
{
|
|
if (
|
|
(item.ScenarioDetail.WeekEndingDate >= start && item.ScenarioDetail.WeekEndingDate <= end) || // if week is in rate interval
|
|
(lastRateEndDate == end && item.ScenarioDetail.WeekEndingDate > end) // if this is a latest rate and week is later than rate's end date
|
|
)
|
|
{
|
|
var newCost = (item.ScenarioDetail.Quantity ?? 0) * rate.Rate1;
|
|
if (newCost != item.ScenarioDetail.Cost)
|
|
{
|
|
totalCostDelta += newCost - (item.ScenarioDetail.Cost ?? 0);
|
|
if (item.ExpenditureType == (int)ExpenditureCategoryModel.CategoryTypes.Labor ||
|
|
item.ExpenditureType == (int)ExpenditureCategoryModel.CategoryTypes.Materials)
|
|
totalCostLMDelta += newCost - (item.ScenarioDetail.Cost ?? 0);
|
|
item.ScenarioDetail.Cost = newCost;
|
|
item.ScenarioDetail.LastUpdate = DateTime.Now;
|
|
DbContext.Entry(item.ScenarioDetail).State = EntityState.Modified;
|
|
}
|
|
}
|
|
}
|
|
scenario.BUDirectCosts += totalCostDelta;
|
|
scenario.BURevenueShot = 0 != scenario.Shots ? scenario.BUDirectCosts / scenario.Shots : 0;
|
|
scenario.CalculatedGrossMargin = scenarioItem.Value.IsRevenueGenerating && 0 != scenario.ProjectedRevenue ? (scenario.ProjectedRevenue - scenario.BUDirectCosts) / scenario.ProjectedRevenue : 0;
|
|
scenario.BUDirectCosts_LM += totalCostLMDelta;
|
|
scenario.BURevenueShot_LM = 0 != scenario.Shots ? scenario.BUDirectCosts_LM / scenario.Shots : 0;
|
|
scenario.CalculatedGrossMargin_LM = scenarioItem.Value.IsRevenueGenerating && 0 != scenario.ProjectedRevenue ? (scenario.ProjectedRevenue - scenario.BUDirectCosts_LM) / scenario.ProjectedRevenue : 0;
|
|
DbContext.Entry(scenario).State = EntityState.Modified;
|
|
}
|
|
}
|
|
#endregion
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Interpolates or extrapolates scenario details distribution.
|
|
/// </summary>
|
|
|
|
public class AdjustScenarioDetailsDistribution
|
|
{
|
|
#region private variables
|
|
private decimal periodScale;
|
|
private readonly EnVisageEntities dbContext;
|
|
#endregion
|
|
|
|
#region Public properties
|
|
public DateTime CutOffDate { get; set; }
|
|
public bool IsUpdate { get; set; }
|
|
public FiscalCalendar[] FiscalCalendarWeeks { set; private get; }
|
|
public Scenario NewScenario { set; private get; }
|
|
public Dictionary<Guid, List<ScenarioManager.ScenarioDetailsListItem>> CurrentScenarioDetails { set; private get; }
|
|
public int CurrentPeriods { get; set; }
|
|
public int NewPeriods { get; set; }
|
|
public Guid[] MaterialsEcs { get; set; }
|
|
#endregion
|
|
|
|
#region Constructors
|
|
public AdjustScenarioDetailsDistribution(EnVisageEntities dbcontext)
|
|
{
|
|
dbContext = dbcontext;
|
|
}
|
|
#endregion
|
|
|
|
#region Methods
|
|
|
|
/// <summary>
|
|
/// Distribution recalculation.
|
|
/// </summary>
|
|
|
|
public Dictionary<Guid, List<ScenarioManager.ScenarioDetailsListItem>> CalculateDistribution()
|
|
{
|
|
if (CurrentScenarioDetails == null)
|
|
throw new NullReferenceException("Unable to calculate distribution because CurrentScenarioDetails is null;");
|
|
if (NewScenario == null)
|
|
throw new NullReferenceException("Unable to calculate distribution because NewScenario is null;");
|
|
|
|
var result = new Dictionary<Guid, List<ScenarioManager.ScenarioDetailsListItem>>(CurrentScenarioDetails.Count); //Creating result set of details, same ExpCat number
|
|
|
|
// load fiscal years if empty collection
|
|
if (FiscalCalendarWeeks == null || FiscalCalendarWeeks.Length == 0)
|
|
{
|
|
FiscalCalendarWeeks = dbContext.FiscalCalendars.Where(
|
|
t => t.Type == (int) FiscalCalendarModel.FiscalYearType.Week && t.StartDate >= NewScenario.StartDate &&
|
|
t.EndDate <= NewScenario.EndDate).OrderBy(t => t.EndDate).ToArray();
|
|
}
|
|
|
|
//transfer all columns > than cut-off date (system date for now)
|
|
var PreviousWeeks = 0; // number of weeks for CutOff
|
|
if (IsUpdate)
|
|
{
|
|
for (var i = FiscalCalendarWeeks.Length - 1; i >= 0; i--)
|
|
{
|
|
if (CutOffDate >= FiscalCalendarWeeks[i].EndDate)
|
|
{
|
|
PreviousWeeks = i;
|
|
break;
|
|
}
|
|
}
|
|
|
|
foreach (var currentItem in CurrentScenarioDetails)
|
|
{
|
|
//if (MaterialsEcs.Contains(currentItem.Key) && NewScenario.UseLMMargin.Value > 0) continue;
|
|
var newExpCatItem = new List<ScenarioManager.ScenarioDetailsListItem>(NewPeriods);
|
|
for (var weekIndex = 0; weekIndex < PreviousWeeks; weekIndex++)
|
|
{
|
|
newExpCatItem.Add(new ScenarioManager.ScenarioDetailsListItem
|
|
{
|
|
ExpenditureCategoryId = currentItem.Value[0].ExpenditureCategoryId,
|
|
ParentId = currentItem.Value[0].ParentId,
|
|
WeekOrdinal = weekIndex + 1,
|
|
DetailCost = 0,
|
|
WeekEndingDate = FiscalCalendarWeeks[weekIndex].EndDate,
|
|
LastUpdate = DateTime.Today,
|
|
ExpenditureCategoryName = currentItem.Value[0].ExpenditureCategoryName,
|
|
CG_EFX = currentItem.Value[0].CG_EFX,
|
|
UseType = currentItem.Value[0].UseType,
|
|
CategoryType = currentItem.Value[0].CategoryType,
|
|
});
|
|
if (weekIndex >= CurrentPeriods)
|
|
{
|
|
newExpCatItem[weekIndex].Quantity = 0;
|
|
newExpCatItem[weekIndex].Id = Guid.Empty;
|
|
}
|
|
else
|
|
{
|
|
newExpCatItem[weekIndex].Quantity = currentItem.Value[weekIndex].Quantity;
|
|
newExpCatItem[weekIndex].Id = currentItem.Value[weekIndex].Id;
|
|
}
|
|
}
|
|
result.Add(currentItem.Key, newExpCatItem);
|
|
}
|
|
if (NewPeriods == PreviousWeeks)
|
|
periodScale = 0;
|
|
else
|
|
periodScale = (decimal) (CurrentPeriods - PreviousWeeks)/(decimal) (NewPeriods - PreviousWeeks);
|
|
}
|
|
else
|
|
periodScale = (NewPeriods == 0) ? 0 : (decimal) CurrentPeriods/(decimal) NewPeriods;
|
|
foreach (var currentItem in CurrentScenarioDetails)
|
|
{
|
|
var dbFact = 0M;
|
|
var dbNewVal = 0M;
|
|
var distributionStep = 1M;
|
|
var processingWeek = PreviousWeeks;
|
|
|
|
var parentId = IsUpdate ? currentItem.Value[0].ParentId : NewScenario.Id;
|
|
var expCatId = currentItem.Key;
|
|
var expCatName = currentItem.Value[0].ExpenditureCategoryName;
|
|
var ecSplit = currentItem.Value[0].CG_EFX;
|
|
var cost = 0;
|
|
var useType = currentItem.Value[0].UseType;
|
|
var expCatType = currentItem.Value[0].CategoryType;
|
|
if (periodScale == 1.0M) //number of weeks in old an new periods match - no need to rescale
|
|
{
|
|
for (var weekIndex = PreviousWeeks; weekIndex < NewPeriods; weekIndex++)
|
|
{
|
|
if (!result.ContainsKey(currentItem.Key))
|
|
result.Add(currentItem.Key, new List<ScenarioManager.ScenarioDetailsListItem>());
|
|
if (result[currentItem.Key].Count <= weekIndex)
|
|
result[currentItem.Key].Add(new ScenarioManager.ScenarioDetailsListItem());
|
|
var newListItem = result[currentItem.Key][weekIndex];
|
|
if (weekIndex < currentItem.Value.Count)
|
|
{
|
|
newListItem.Quantity = currentItem.Value[weekIndex].Quantity;
|
|
newListItem.Id = IsUpdate && weekIndex <= CurrentPeriods ? currentItem.Value[weekIndex].Id : Guid.Empty;
|
|
}
|
|
else
|
|
{
|
|
newListItem.Quantity = 0;
|
|
newListItem.Id = Guid.Empty;
|
|
}
|
|
|
|
|
|
newListItem.ParentId = parentId;
|
|
newListItem.ExpenditureCategoryId = expCatId;
|
|
newListItem.WeekOrdinal = weekIndex + 1;
|
|
newListItem.DetailCost = cost;
|
|
newListItem.WeekEndingDate = FiscalCalendarWeeks[weekIndex].EndDate;
|
|
newListItem.LastUpdate = DateTime.Now;
|
|
newListItem.ExpenditureCategoryName = expCatName;
|
|
newListItem.CG_EFX = ecSplit;
|
|
newListItem.UseType = useType;
|
|
newListItem.CategoryType = expCatType;
|
|
}
|
|
}
|
|
else if (periodScale < 1) // If we stretch
|
|
{
|
|
for (var weekIndex = PreviousWeeks; weekIndex < NewPeriods; weekIndex++)
|
|
{
|
|
if (!result.ContainsKey(currentItem.Key))
|
|
result.Add(currentItem.Key, new List<ScenarioManager.ScenarioDetailsListItem>());
|
|
if (result[currentItem.Key].Count <= weekIndex)
|
|
result[currentItem.Key].Add(new ScenarioManager.ScenarioDetailsListItem());
|
|
var newListItem = result[currentItem.Key][weekIndex];
|
|
if ((dbFact + periodScale) < 1)
|
|
{
|
|
newListItem.Quantity = currentItem.Value[processingWeek].Quantity*periodScale;
|
|
dbFact = dbFact + periodScale;
|
|
}
|
|
else
|
|
{
|
|
if (processingWeek < (CurrentPeriods - 1))
|
|
newListItem.Quantity = (currentItem.Value[processingWeek].Quantity * (1 - dbFact)) +
|
|
(currentItem.Value[processingWeek + 1].Quantity * ((dbFact + periodScale) - 1));
|
|
else
|
|
newListItem.Quantity = (currentItem.Value[processingWeek].Quantity * (1 - dbFact));
|
|
processingWeek++;
|
|
if (processingWeek > CurrentPeriods)
|
|
{
|
|
if (IsUpdate && weekIndex < CurrentPeriods)
|
|
newListItem.Id = currentItem.Value[weekIndex].Id;
|
|
else
|
|
newListItem.Id = Guid.Empty;
|
|
|
|
newListItem.ParentId = parentId;
|
|
newListItem.ExpenditureCategoryId = expCatId;
|
|
newListItem.WeekOrdinal = weekIndex + 1;
|
|
newListItem.DetailCost = cost;
|
|
newListItem.WeekEndingDate = FiscalCalendarWeeks[weekIndex].EndDate;
|
|
newListItem.LastUpdate = DateTime.Now;
|
|
newListItem.ExpenditureCategoryName = expCatName;
|
|
newListItem.CG_EFX = ecSplit;
|
|
newListItem.UseType = useType;
|
|
newListItem.CategoryType = expCatType;
|
|
break;
|
|
}
|
|
dbFact = ((dbFact + periodScale) - 1);
|
|
}
|
|
if (IsUpdate && weekIndex < CurrentPeriods)
|
|
newListItem.Id = currentItem.Value[weekIndex].Id;
|
|
else
|
|
newListItem.Id = Guid.Empty;
|
|
newListItem.Quantity = newListItem.Quantity / periodScale;
|
|
newListItem.ParentId = parentId;
|
|
newListItem.ExpenditureCategoryId = expCatId;
|
|
newListItem.WeekOrdinal = weekIndex + 1;
|
|
newListItem.DetailCost = cost;
|
|
newListItem.WeekEndingDate = FiscalCalendarWeeks[weekIndex].EndDate;
|
|
newListItem.LastUpdate = DateTime.Now;
|
|
newListItem.ExpenditureCategoryName = expCatName;
|
|
newListItem.CG_EFX = ecSplit;
|
|
newListItem.UseType = useType;
|
|
newListItem.CategoryType = expCatType;
|
|
}
|
|
}
|
|
else if (periodScale > 1) // If we shrink
|
|
{
|
|
for (var weekIndex = PreviousWeeks; weekIndex < CurrentPeriods; weekIndex++)
|
|
{
|
|
if (!result.ContainsKey(currentItem.Key))
|
|
result.Add(currentItem.Key, new List<ScenarioManager.ScenarioDetailsListItem>());
|
|
if (result[currentItem.Key].Count <= processingWeek)
|
|
result[currentItem.Key].Add(new ScenarioManager.ScenarioDetailsListItem());
|
|
var newListItem = result[currentItem.Key][processingWeek];
|
|
if (distributionStep < periodScale)
|
|
{
|
|
if (distributionStep < 1)
|
|
dbNewVal += currentItem.Value[weekIndex - 1].Quantity * distributionStep;
|
|
if (distributionStep == 1)
|
|
{
|
|
dbNewVal += currentItem.Value[weekIndex].Quantity;
|
|
distributionStep++;
|
|
}
|
|
else
|
|
{
|
|
if (periodScale - distributionStep >= 1)
|
|
{
|
|
dbNewVal += currentItem.Value[weekIndex].Quantity;
|
|
|
|
if (distributionStep == periodScale)
|
|
{
|
|
newListItem.Quantity = dbNewVal / periodScale;
|
|
newListItem.Id = (IsUpdate && processingWeek <= CurrentPeriods)
|
|
? currentItem.Value[processingWeek].Id
|
|
: Guid.Empty;
|
|
newListItem.ParentId = parentId;
|
|
newListItem.ExpenditureCategoryId = expCatId;
|
|
newListItem.WeekOrdinal = processingWeek + 1;
|
|
newListItem.DetailCost = cost;
|
|
newListItem.WeekEndingDate = FiscalCalendarWeeks[processingWeek].EndDate;
|
|
newListItem.LastUpdate = DateTime.Now;
|
|
newListItem.ExpenditureCategoryName = expCatName;
|
|
newListItem.CG_EFX = ecSplit;
|
|
newListItem.UseType = useType;
|
|
newListItem.CategoryType = expCatType;
|
|
distributionStep = 1;
|
|
if (processingWeek < NewPeriods)
|
|
{
|
|
processingWeek++;
|
|
dbNewVal = 0;
|
|
}
|
|
}
|
|
distributionStep++;
|
|
}
|
|
else
|
|
{
|
|
dbNewVal += ((periodScale - distributionStep) * currentItem.Value[weekIndex].Quantity);
|
|
newListItem.Quantity = dbNewVal / periodScale;
|
|
newListItem.Id = (IsUpdate && processingWeek <= CurrentPeriods)
|
|
? currentItem.Value[processingWeek].Id
|
|
: Guid.Empty;
|
|
|
|
newListItem.ParentId = parentId;
|
|
newListItem.ExpenditureCategoryId = expCatId;
|
|
newListItem.WeekOrdinal = processingWeek + 1;
|
|
newListItem.DetailCost = cost;
|
|
newListItem.WeekEndingDate = FiscalCalendarWeeks[processingWeek].EndDate;
|
|
newListItem.LastUpdate = DateTime.Now;
|
|
newListItem.ExpenditureCategoryName = expCatName;
|
|
newListItem.CG_EFX = ecSplit;
|
|
newListItem.UseType = useType;
|
|
newListItem.CategoryType = expCatType;
|
|
if (processingWeek < NewPeriods)
|
|
{
|
|
processingWeek++;
|
|
dbNewVal = 0;
|
|
}
|
|
distributionStep = 1 - (periodScale - distributionStep);
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
dbNewVal += ((periodScale - (distributionStep - 1)) * currentItem.Value[weekIndex].Quantity);
|
|
newListItem.Quantity = dbNewVal / periodScale;
|
|
newListItem.Id = (IsUpdate && processingWeek <= CurrentPeriods) ? currentItem.Value[processingWeek].Id : Guid.Empty;
|
|
|
|
newListItem.ParentId = parentId;
|
|
newListItem.ExpenditureCategoryId = expCatId;
|
|
newListItem.WeekOrdinal = processingWeek + 1;
|
|
newListItem.DetailCost = cost;
|
|
newListItem.WeekEndingDate = FiscalCalendarWeeks[processingWeek].EndDate;
|
|
newListItem.LastUpdate = DateTime.Now;
|
|
newListItem.ExpenditureCategoryName = expCatName;
|
|
newListItem.CG_EFX = ecSplit;
|
|
newListItem.UseType = useType;
|
|
newListItem.CategoryType = expCatType;
|
|
distributionStep = distributionStep - periodScale;
|
|
if (distributionStep == 0)
|
|
distributionStep = 1;
|
|
if (processingWeek < NewPeriods)
|
|
{
|
|
processingWeek++;
|
|
dbNewVal = 0;
|
|
}
|
|
}
|
|
}
|
|
if (!result.ContainsKey(currentItem.Key))
|
|
result.Add(currentItem.Key, new List<ScenarioManager.ScenarioDetailsListItem>());
|
|
if ((dbNewVal > 0 || result[currentItem.Key][result[currentItem.Key].Count -1].ExpenditureCategoryId == Guid.Empty) && result[currentItem.Key].Count <= (processingWeek + 1))
|
|
{
|
|
//result[currentItem.Key].Add(new ScenarioManager.ScenarioDetailsListItem());
|
|
if (result[currentItem.Key].Count > processingWeek)
|
|
{
|
|
result[currentItem.Key][processingWeek].Quantity = dbNewVal / periodScale;
|
|
result[currentItem.Key][processingWeek].Id = (IsUpdate && processingWeek <= CurrentPeriods)
|
|
? currentItem.Value[processingWeek].Id
|
|
: Guid.Empty;
|
|
result[currentItem.Key][processingWeek].ParentId = parentId;
|
|
result[currentItem.Key][processingWeek].ExpenditureCategoryId = expCatId;
|
|
result[currentItem.Key][processingWeek].WeekOrdinal = processingWeek + 1;
|
|
result[currentItem.Key][processingWeek].DetailCost = cost;
|
|
result[currentItem.Key][processingWeek].WeekEndingDate = FiscalCalendarWeeks[processingWeek - 1].EndDate;
|
|
result[currentItem.Key][processingWeek].LastUpdate = DateTime.Now;
|
|
result[currentItem.Key][processingWeek].ExpenditureCategoryName = expCatName;
|
|
result[currentItem.Key][processingWeek].CG_EFX = ecSplit;
|
|
result[currentItem.Key][processingWeek].UseType = useType;
|
|
result[currentItem.Key][processingWeek].CategoryType = expCatType;
|
|
}
|
|
else
|
|
{
|
|
result[currentItem.Key][processingWeek - 1].Quantity = dbNewVal / periodScale;
|
|
result[currentItem.Key][processingWeek - 1].Id = (IsUpdate && processingWeek <= CurrentPeriods)
|
|
? currentItem.Value[processingWeek].Id
|
|
: Guid.Empty;
|
|
result[currentItem.Key][processingWeek - 1].ParentId = parentId;
|
|
result[currentItem.Key][processingWeek - 1].ExpenditureCategoryId = expCatId;
|
|
result[currentItem.Key][processingWeek - 1].WeekOrdinal = processingWeek + 1;
|
|
result[currentItem.Key][processingWeek - 1].DetailCost = cost;
|
|
result[currentItem.Key][processingWeek - 1].WeekEndingDate = FiscalCalendarWeeks[processingWeek - 1].EndDate;
|
|
result[currentItem.Key][processingWeek - 1].LastUpdate = DateTime.Now;
|
|
result[currentItem.Key][processingWeek - 1].ExpenditureCategoryName = expCatName;
|
|
result[currentItem.Key][processingWeek - 1].CG_EFX = ecSplit;
|
|
result[currentItem.Key][processingWeek - 1].UseType = useType;
|
|
result[currentItem.Key][processingWeek - 1].CategoryType = expCatType;
|
|
}
|
|
//if (result[currentItem.Key].Count > processingWeek)
|
|
// result[currentItem.Key].RemoveAt(processingWeek);
|
|
}
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
#endregion
|
|
}
|
|
} |