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 { 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> 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(); var result = new Dictionary>(); foreach (var expenditure2Calculation in query) { if (expenditure2Calculation.ParentId != parentId) { if (firstTime) { firstTime = false; calcCategories = new List(); } else { result.Add(parentId, calcCategories); calcCategories = new List(); } 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 DataTable { get { return DbContext.Scenarios; } } public IList GetExpenditureCategories(Guid? templateId) { if (templateId == null || templateId == Guid.Empty) return new ScenarioModel.ExpenditureItem[0]; var list = new List(); 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(); 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 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(); var gRates = new List(); var lstDctEcAddOn = new List(); var lstExpCatsToExclude = new List(); //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(); 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> lvaCurrScenario; lvaCurrScenario = GetTemplateScenarioDetailsPerExpCat(dbObj.Id, lstDctEcAddOn.ToList(), Guid.Empty, new Collection()); 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(); var moRateTranslate = new Dictionary>(); 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(); } 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(); } 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 } /// /// Saves scenario and scenario details. /// /// 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(); var gRates = new List(); var lstDctEcAddOn = new List(); var lstExpCatsToExclude = new List(); //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(); //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(); 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()); 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(); var moRateTranslate = new Dictionary>(); 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(); } 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(); } 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(); } /// /// Copying actuals values to scenario details /// private void CopyActuals(Dictionary> 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; } } } } /// /// Calculates costs for calculated expenditure /// private void ComputeCalculatedCategories(Dictionary> calculatedNewScenario, TemporaryCalculationResults temporaryCalcResult, DateTime mdtActualsEndWeek, int totalMilestones, int? duration, Dictionary> moCatRowTranslate, Dictionary> 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> calculatedNewScenario, TemporaryCalculationResults temporaryCalcResult, DateTime mdtActualsEndWeek, ScenarioModel model, int? duration, Dictionary> moCatRowTranslate, Dictionary> 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; } } } } } } /// /// Applies labor/materials split factor /// private void ApplyCGEFXFactor(Dictionary> 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> calculatedNewScenario, DateTime mdtActualsEndWeek, Dictionary> 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> LoadRates(Guid scenarioId) { var result = new Dictionary>(); 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> 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; } /// /// Prepare a list of scenario details templates for each provided expenditure category. /// /// Id of the template scenario. /// /// /// /// private Dictionary> GetTemplateScenarioDetailsPerExpCat(Guid templateScenarioId, IReadOnlyCollection plstExpCatsToAdd, Guid newScenarioId, ICollection plstExpCatsToExclude) { var result = new Dictionary>(); 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>(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(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(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(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; } /// /// Returns a dictionary where Key = ExpenditureCategoryId, Value = List<ParentId> /// /// public Dictionary> 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; } /// /// Sets Bottom Up costs upon scenario creation or editing. /// 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); } /// /// Recalculate scenario bottom-up costs for the specified scenario. DbContext submission required. /// /// The scenario that should be recalculated. /// Indicates whether scenario is revenue generating or not. 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; } } /// /// Recalculate scenario bottom-up costs for the specified scenario. DbContext submission required. /// /// The scenario which bottom-up costs should be recalculated. /// Indicates whether scenario is revenue generating or not. /// A list of all scenario detail records of . /// A li st of all scenario detail records of actuals scenario referenced to the same project as . private void UpdateScenarioBottomUpCosts(Scenario scenario, bool isRevenueGeneratingScenario, List scenarioDetailItems, List 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; } } /// /// Copy scenario /// /// Scenario Id /// Project Id /// Status of the copyed scenario /// Id of the new scenario 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; } /// /// Updates costs of referenced scenario details records and recalculate bottom-up costs of related scenarios. /// /// A object that has been created or updated. 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 } } /// /// Interpolates or extrapolates scenario details distribution. /// 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> 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 /// /// Distribution recalculation. /// public Dictionary> 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>(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(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()); 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()); 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()); 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()); 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 } }