310 lines
17 KiB
C#
310 lines
17 KiB
C#
using EnVisage.Code;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Web;
|
|
|
|
namespace EnVisage.Models
|
|
{
|
|
public class ShowRollUpModel
|
|
{
|
|
#region Classes and enums
|
|
class ScenarioTooProject
|
|
{
|
|
public string Name { get; set; }
|
|
public Nullable<System.Guid> ParentId { get; set; }
|
|
public Nullable<decimal> ProjectedRevenue { get; set; }
|
|
public Nullable<decimal> ExpectedGrossMargin { get; set; }
|
|
public Nullable<decimal> ExpectedGrossMargin_LM { get; set; }
|
|
public Nullable<decimal> BUDirectCosts { get; set; }
|
|
public Nullable<decimal> BUDirectCosts_LM { get; set; }
|
|
public bool GrowthScenario { get; set; }
|
|
public Nullable<decimal> Actuals_BUDirectCosts { get; set; }
|
|
public Nullable<decimal> Actuals_BUDirectCosts_LM { get; set; }
|
|
public Nullable<decimal> CGSplit { get; set; }
|
|
public Nullable<decimal> EFXSplit { get; set; }
|
|
public Nullable<int> Duration { get; set; }
|
|
public decimal Probability { get; set; }
|
|
public Nullable<int> Shots { get; set; }
|
|
public Nullable<decimal> TDDirectCosts_LM { get; set; }
|
|
public Nullable<decimal> UseLMMargin { get; set; }
|
|
public Nullable<decimal> TDDirectCosts { get; set; }
|
|
public Nullable<decimal> TDRevenueShot { get; set; }
|
|
public Nullable<decimal> BURevenueShot { get; set; }
|
|
public Nullable<decimal> BURevenueShot_LM { get; set; }
|
|
public Nullable<decimal> CostSavings { get; set; }
|
|
}
|
|
|
|
public class ReportElement
|
|
{
|
|
public System.Guid Id { get; set; }
|
|
public string Name { get; set; }
|
|
public Nullable<decimal> ProjectedRevenue { get; set; }
|
|
public Nullable<decimal> ExpectedGrossMargin { get; set; }
|
|
public Nullable<decimal> ExpectedGrossMargin_LM { get; set; }
|
|
public Nullable<decimal> CalculatedGrossMargin { get; set; }
|
|
public Nullable<decimal> CalculatedGrossMarginLM { get; set; }
|
|
public Nullable<decimal> CalculatedGrossMarginAct { get; set; }
|
|
public Nullable<decimal> CalculatedGrossMarginLMAct { get; set; }
|
|
public Nullable<decimal> CGSplit { get; set; }
|
|
public Nullable<decimal> EFXSplit { get; set; }
|
|
public Nullable<int> Duration { get; set; }
|
|
public Nullable<decimal> Priority { get; set; }
|
|
public decimal Probability { get; set; }
|
|
public Nullable<decimal> TDDirectCosts { get; set; }
|
|
public Nullable<decimal> BUDirectCosts { get; set; }
|
|
public Nullable<decimal> BUDirectCosts_LM { get; set; }
|
|
public Nullable<decimal> Actuals_BUDirectCosts { get; set; }
|
|
public Nullable<decimal> Actuals_BUDirectCosts_LM { get; set; }
|
|
public Nullable<int> Shots { get; set; }
|
|
public Nullable<decimal> TDRevenueShot { get; set; }
|
|
public Nullable<decimal> BURevenueShot { get; set; }
|
|
public Nullable<decimal> BURevenueShot_LM { get; set; }
|
|
public bool IsTotalRow { get; set; }
|
|
public int ScenariosCount { get; set; }
|
|
public decimal CostSavings { get; set; }
|
|
|
|
public ReportElement()
|
|
{
|
|
Id = Guid.Empty;
|
|
Name = string.Empty;
|
|
ProjectedRevenue = 0;
|
|
ExpectedGrossMargin = 0;
|
|
ExpectedGrossMargin_LM = 0;
|
|
CalculatedGrossMargin = 0;
|
|
CalculatedGrossMarginLM = 0;
|
|
CalculatedGrossMarginAct = 0;
|
|
CalculatedGrossMarginLMAct = 0;
|
|
CGSplit = 0;
|
|
EFXSplit = 0;
|
|
Duration = 0;
|
|
Priority = 0;
|
|
Probability = 0;
|
|
TDDirectCosts = 0;
|
|
BUDirectCosts = 0;
|
|
BUDirectCosts_LM = 0;
|
|
Actuals_BUDirectCosts = 0;
|
|
Actuals_BUDirectCosts_LM = 0;
|
|
Shots = 0;
|
|
TDRevenueShot = 0;
|
|
BURevenueShot = 0;
|
|
BURevenueShot_LM = 0;
|
|
IsTotalRow = false;
|
|
ScenariosCount = 0;
|
|
CostSavings = 0;
|
|
}
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region Properties
|
|
public List<ReportElement> scenarioCalculationList { get; set; }
|
|
public ReportElement totalScenarioCalculation { get; set; }
|
|
public Guid? GroupId { get; set; }
|
|
public ScenarioType SelectedScenarioType { get; set; }
|
|
#endregion
|
|
|
|
public ShowRollUpModel()
|
|
{
|
|
GroupId = Guid.Empty;
|
|
SelectedScenarioType = ScenarioType.Portfolio;
|
|
}
|
|
|
|
#region Methods
|
|
public void FormReport(EnVisageEntities db)
|
|
{
|
|
List<ScenarioTooProject> scenarioTooProject;
|
|
System.Guid? lastId = Guid.Empty;
|
|
int showCenariosCount = 1;
|
|
scenarioCalculationList = new List<ReportElement>();
|
|
ReportElement reportListElement = new ReportElement();
|
|
totalScenarioCalculation = new ReportElement
|
|
{
|
|
IsTotalRow = true
|
|
};
|
|
scenarioTooProject = GetData(db);
|
|
|
|
foreach (var scenario in scenarioTooProject)
|
|
{
|
|
if (lastId != scenario.ParentId)
|
|
{
|
|
if (lastId != Guid.Empty)
|
|
{ scenarioCalculationList.Add(CalculationMeanValues(reportListElement, showCenariosCount, 100)); }
|
|
|
|
reportListElement = new ReportElement()
|
|
{
|
|
Name = scenario.Name
|
|
};
|
|
|
|
lastId = scenario.ParentId;
|
|
showCenariosCount = 0;
|
|
}
|
|
reportListElement = CalculateScenario(reportListElement, scenario);
|
|
totalScenarioCalculation = CalculateScenario(totalScenarioCalculation, scenario);
|
|
totalScenarioCalculation.ScenariosCount++;
|
|
showCenariosCount++;
|
|
}
|
|
|
|
if (lastId != Guid.Empty)
|
|
{
|
|
//scenarioCalculationList.Add(reportListElement);
|
|
scenarioCalculationList.Add(CalculationMeanValues(reportListElement, showCenariosCount, 100));
|
|
}
|
|
|
|
scenarioCalculationList.Add(CalculationMeanValues(totalScenarioCalculation, showCenariosCount, 100));
|
|
}
|
|
|
|
ReportElement CalculateScenario(ReportElement reportListElement, ScenarioTooProject scenario)
|
|
{
|
|
reportListElement.ProjectedRevenue += scenario.ProjectedRevenue??0;
|
|
reportListElement.ExpectedGrossMargin += scenario.ExpectedGrossMargin??0;
|
|
reportListElement.ExpectedGrossMargin_LM += scenario.ExpectedGrossMargin_LM??0;
|
|
reportListElement.CalculatedGrossMargin += !scenario.ProjectedRevenue.HasValue || scenario.ProjectedRevenue == 0
|
|
? 0
|
|
: ((scenario.ProjectedRevenue - scenario.BUDirectCosts??0)/ scenario.ProjectedRevenue);
|
|
reportListElement.CalculatedGrossMarginLM += !scenario.ProjectedRevenue.HasValue || scenario.ProjectedRevenue == 0
|
|
? 0
|
|
: ((scenario.ProjectedRevenue - scenario.BUDirectCosts_LM??0) / scenario.ProjectedRevenue);
|
|
reportListElement.BUDirectCosts += scenario.BUDirectCosts??0;
|
|
reportListElement.BUDirectCosts_LM += scenario.BUDirectCosts_LM??0;
|
|
|
|
if (!scenario.GrowthScenario)
|
|
{
|
|
reportListElement.CalculatedGrossMarginAct += !scenario.ProjectedRevenue.HasValue || scenario.ProjectedRevenue == 0
|
|
? 0
|
|
: ((scenario.ProjectedRevenue - scenario.Actuals_BUDirectCosts??0) / scenario.ProjectedRevenue);
|
|
reportListElement.CalculatedGrossMarginLMAct += !scenario.ProjectedRevenue.HasValue || scenario.ProjectedRevenue == 0
|
|
? 0
|
|
: ((scenario.ProjectedRevenue - scenario.Actuals_BUDirectCosts_LM??0) / scenario.ProjectedRevenue);
|
|
reportListElement.Actuals_BUDirectCosts += scenario.Actuals_BUDirectCosts??0;
|
|
reportListElement.Actuals_BUDirectCosts_LM += scenario.Actuals_BUDirectCosts_LM??0;
|
|
}
|
|
else
|
|
{
|
|
reportListElement.CalculatedGrossMarginAct += !scenario.ProjectedRevenue.HasValue || scenario.ProjectedRevenue == 0
|
|
? 0
|
|
: ((scenario.ProjectedRevenue - scenario.BUDirectCosts??0) / scenario.ProjectedRevenue);
|
|
reportListElement.CalculatedGrossMarginLMAct += !scenario.ProjectedRevenue.HasValue || scenario.ProjectedRevenue == 0
|
|
? 0
|
|
: ((scenario.ProjectedRevenue - scenario.BUDirectCosts_LM??0) / scenario.ProjectedRevenue);
|
|
reportListElement.Actuals_BUDirectCosts += scenario.BUDirectCosts??0;
|
|
reportListElement.Actuals_BUDirectCosts_LM += scenario.BUDirectCosts??0;
|
|
}
|
|
|
|
reportListElement.CGSplit += scenario.CGSplit??0;
|
|
reportListElement.EFXSplit += scenario.EFXSplit??0;
|
|
reportListElement.Duration += scenario.Duration??0;
|
|
reportListElement.Probability += scenario.Probability;
|
|
reportListElement.Shots += scenario.Shots??0;
|
|
reportListElement.CostSavings += scenario.CostSavings ?? 0;
|
|
if (scenario.UseLMMargin == 1)
|
|
{
|
|
reportListElement.TDDirectCosts += scenario.TDDirectCosts_LM??0;
|
|
}
|
|
else
|
|
{
|
|
reportListElement.TDDirectCosts += scenario.TDDirectCosts??0;
|
|
}
|
|
|
|
reportListElement.TDRevenueShot += scenario.TDRevenueShot??0;
|
|
reportListElement.BURevenueShot += scenario.BURevenueShot??0;
|
|
reportListElement.BURevenueShot_LM += scenario.BURevenueShot_LM??0;
|
|
|
|
return reportListElement;
|
|
}
|
|
|
|
ReportElement CalculationMeanValues(ReportElement reportListElement, int count, int multiplier)
|
|
{
|
|
reportListElement.ProjectedRevenue = Math.Round((decimal)(reportListElement.ProjectedRevenue ?? 0), 2);
|
|
reportListElement.ExpectedGrossMargin = count == 0 ? 0 : Math.Round((decimal)(reportListElement.ExpectedGrossMargin??0 / count) * multiplier, 2);
|
|
reportListElement.ExpectedGrossMargin_LM = count == 0 ? 0 : Math.Round((decimal)(reportListElement.ExpectedGrossMargin_LM ?? 0 / count) * multiplier, 2);
|
|
reportListElement.CalculatedGrossMargin = count == 0 ? 0 : Math.Round((decimal)(reportListElement.CalculatedGrossMargin ?? 0 / count) * multiplier, 2);
|
|
reportListElement.CalculatedGrossMarginLM = count == 0 ? 0 : Math.Round((decimal)(reportListElement.CalculatedGrossMarginLM ?? 0 / count) * multiplier, 2);
|
|
reportListElement.CalculatedGrossMarginAct = count == 0 ? 0 : Math.Round((decimal)(reportListElement.CalculatedGrossMarginAct ?? 0 / count) * multiplier, 2);
|
|
reportListElement.CalculatedGrossMarginLMAct = count == 0 ? 0 : Math.Round((decimal)(reportListElement.CalculatedGrossMarginLMAct ?? 0 / count) * multiplier, 2);
|
|
reportListElement.CGSplit = count == 0 ? 0 : Math.Round((decimal)(reportListElement.CGSplit ?? 0 / count) * 100, 2);
|
|
reportListElement.EFXSplit = count == 0 ? 0 : Math.Round((decimal)(reportListElement.EFXSplit ?? 0 / count) * 100, 2);
|
|
reportListElement.Duration = count == 0 ? 0 : (int?)Math.Round((decimal)(reportListElement.Duration ?? 0 / count), 0);
|
|
reportListElement.Probability = count == 0 ? 0 : Math.Round((decimal)(reportListElement.Probability / count) * multiplier, 2);
|
|
reportListElement.TDDirectCosts = Math.Round((decimal)(reportListElement.TDDirectCosts ?? 0), 0);
|
|
reportListElement.BUDirectCosts = Math.Round((decimal)(reportListElement.BUDirectCosts ?? 0), 0);
|
|
|
|
reportListElement.BUDirectCosts_LM = Math.Round((decimal)(reportListElement.BUDirectCosts_LM ?? 0), 0);
|
|
reportListElement.Actuals_BUDirectCosts = Math.Round((decimal)(reportListElement.Actuals_BUDirectCosts ?? 0), 0);
|
|
reportListElement.Actuals_BUDirectCosts_LM = Math.Round((decimal)(reportListElement.Actuals_BUDirectCosts_LM ?? 0), 0);
|
|
reportListElement.Shots = (int?)Math.Round((decimal)(reportListElement.Shots ?? 0), 0);
|
|
reportListElement.TDRevenueShot = Math.Round((decimal)(reportListElement.TDRevenueShot ?? 0), 0);
|
|
reportListElement.BURevenueShot = Math.Round((decimal)(reportListElement.BURevenueShot ?? 0), 0);
|
|
reportListElement.BURevenueShot_LM = Math.Round((decimal)(reportListElement.BURevenueShot_LM ?? 0), 0);
|
|
|
|
return reportListElement;
|
|
}
|
|
|
|
private List<ScenarioTooProject> GetData(EnVisageEntities db)
|
|
{
|
|
if (GroupId == Guid.Empty)
|
|
{
|
|
return
|
|
db.VW_Scenario2Project.Where(t => t.Type == (int?) SelectedScenarioType && t.Status == (int)ScenarioStatus.Active)
|
|
.Select(x =>
|
|
new ScenarioTooProject()
|
|
{
|
|
Name = x.ShowName + (x.ParentProjectId != null ? ": " + x.ParentProjectName: ""),
|
|
ParentId = x.ParentId,
|
|
ProjectedRevenue = x.ProjectedRevenue,
|
|
ExpectedGrossMargin = x.ExpectedGrossMargin,
|
|
ExpectedGrossMargin_LM = x.ExpectedGrossMargin_LM,
|
|
BUDirectCosts = x.BUDirectCosts,
|
|
BUDirectCosts_LM = x.BUDirectCosts_LM,
|
|
GrowthScenario = x.GrowthScenario,
|
|
Actuals_BUDirectCosts = x.Actuals_BUDirectCosts,
|
|
Actuals_BUDirectCosts_LM = x.Actuals_BUDirectCosts_LM,
|
|
CGSplit = x.CGSplit,
|
|
EFXSplit = x.EFXSplit,
|
|
Duration = x.Duration,
|
|
Probability = x.Probability ?? 0,
|
|
Shots = x.Shots,
|
|
TDDirectCosts_LM = x.TDDirectCosts_LM,
|
|
UseLMMargin = x.UseLMMargin,
|
|
TDDirectCosts = x.TDDirectCosts,
|
|
TDRevenueShot = x.TDRevenueShot,
|
|
BURevenueShot = x.BURevenueShot,
|
|
BURevenueShot_LM = x.BURevenueShot_LM,
|
|
CostSavings = x.CostSavings
|
|
|
|
}).ToList();
|
|
}
|
|
return
|
|
db.VW_Scenario2Project.Where(
|
|
t => t.Type == (int?) SelectedScenarioType && t.Status == (int)ScenarioStatus.Active && t.GroupId == GroupId)
|
|
.Select(x =>
|
|
new ScenarioTooProject()
|
|
{
|
|
Name = x.ShowName + (x.ParentProjectId != null ? ": " + x.ParentProjectName : ""),
|
|
ParentId = x.ParentId,
|
|
ProjectedRevenue = x.ProjectedRevenue,
|
|
ExpectedGrossMargin = x.ExpectedGrossMargin,
|
|
ExpectedGrossMargin_LM = x.ExpectedGrossMargin_LM,
|
|
BUDirectCosts = x.BUDirectCosts,
|
|
BUDirectCosts_LM = x.BUDirectCosts_LM,
|
|
GrowthScenario = x.GrowthScenario,
|
|
Actuals_BUDirectCosts = x.Actuals_BUDirectCosts,
|
|
Actuals_BUDirectCosts_LM = x.Actuals_BUDirectCosts_LM,
|
|
CGSplit = x.CGSplit,
|
|
EFXSplit = x.EFXSplit,
|
|
Duration = x.Duration,
|
|
Probability = x.Probability ?? 0,
|
|
Shots = x.Shots,
|
|
TDDirectCosts_LM = x.TDDirectCosts_LM,
|
|
UseLMMargin = x.UseLMMargin,
|
|
TDDirectCosts = x.TDDirectCosts,
|
|
TDRevenueShot = x.TDRevenueShot,
|
|
BURevenueShot = x.BURevenueShot,
|
|
BURevenueShot_LM = x.BURevenueShot_LM,
|
|
CostSavings = x.CostSavings
|
|
}).ToList();
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
} |