internal static string GetKpiExcelData(Dictionary <string, string> parameters, KpiCalculationType kpiType, FutureTrendDataType futureTrendDataType)
{
var csvData = new StringBuilder();
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var additionalColumns = GenerateAdditionalColumns(parameters, dataContext);
//var rawData = GetRawKpiDataNew(dataContext, parameters, futureTrendDataType, true);
var rawData = GenericSizingDataAccess.GetKpiRawData(dataContext, parameters, futureTrendDataType);
var groupedData = from rw in rawData
select new KpiExcelDataHolder
{
ReportDate = rw.ReportDate,
Country = rw.Country,
Kpi = kpiType == KpiCalculationType.OperationalUtilization ? rw.ExpectedFleet == 0 ? 0 :
(rw.Forecast / rw.ExpectedFleet) * 100 :
kpiType == KpiCalculationType.IdleFleetPercentage ? rw.ExpectedFleet == 0 ? 0 :
(rw.ExpectedFleet - rw.Forecast) * 100 / rw.ExpectedFleet :
kpiType == KpiCalculationType.IdleFleet ?
rw.ExpectedFleet - rw.Forecast : 0
};
var excelData = from gd in groupedData.OrderBy(d => d.ReportDate).ThenBy(d => d.Country)
select string.Format("{0},{1},{2}{3}\n", gd.ReportDate.Value.ToShortDateString(), gd.Country, additionalColumns,
Math.Round(gd.Kpi, 0, MidpointRounding.AwayFromZero));
excelData.ToList().ForEach(d => csvData.Append(d.ToString()));
return(csvData.ToString());
}
}
internal static List <GraphSeries> GetKpiDataNew(Dictionary <string, string> parameters, KpiCalculationType kpiType, FutureTrendDataType futureTrendDataType)
{
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
//var rawData = GetRawKpiDataNew(dataContext, parameters, futureTrendDataType);
var rawData = GenericSizingDataAccess.GetKpiRawData(dataContext, parameters, futureTrendDataType);
var kpiGraphingData = from gd in rawData
orderby gd.ReportDate ascending
select new KpiGraphDataHolder
{
ReportDate = gd.ReportDate,
Kpi = kpiType == KpiCalculationType.OperationalUtilization ? gd.ExpectedFleet == 0 ? 0 :
gd.Forecast / gd.ExpectedFleet :
kpiType == KpiCalculationType.IdleFleetPercentage ? gd.ExpectedFleet == 0 ? 0 :
(gd.ExpectedFleet - gd.Forecast) / gd.ExpectedFleet :
kpiType == KpiCalculationType.IdleFleet ?
gd.ExpectedFleet - gd.Forecast : 0
};
return(GraphKpiData(kpiGraphingData));
}
}
internal static List <GraphSeries> GetForecastGraphingData(Dictionary <string, string> parameters, DataType timeZone)
{
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var rawData = timeZone == DataType.DailyChanging
? GenericSizingDataAccess.GetForecastRawData(parameters, dataContext)
: GetFrozenZoneForecastRawData(parameters, dataContext);
var groupedData = from rd in rawData
group rd by new { RepDate = rd.ReportDate } into g
orderby g.Key.RepDate ascending
select new ForecastGraphDataHolder
{
ReportDate = g.Key.RepDate,
CurrentOnRent = g.Sum(d => d.OnRent),
OnRentLastYear = g.Sum(d => d.OnRentLy),
ConstrainedForecast = g.Sum(d => d.Constrained),
UnconstrainedForecast = g.Sum(d => d.Unconstrained),
Fleet = g.Sum(d => d.Fleet),
AlreadyBooked = g.Sum(d => d.AlreadyBooked),
OnRentTopDown = g.Sum(d => d.TopDown),
OnRentBottomUpOne = g.Sum(d => d.BottomUp1),
OnRentBottomUpTwo = g.Sum(d => d.BottomUp2),
OnRentReconciliation = g.Sum(d => d.Reconciliation)
};
dataContext.Log = new DebugTextWriter();
return(GetForecastSeriesData(groupedData, timeZone));
}
}
internal static string GetSupplyAnalysisExcelData(Dictionary <string, string> parameters, FutureTrendDataType dataType,
int scenarioId, int siteGroup, int fleetGroup, DataType timeZone, bool weeklyGrouping)
{
var toDate = timeZone == DataType.FrozenZone
? FutureTrendDataAccess.SetFrozenZoneToDateLimit(DateTime.Parse(parameters[ParameterNames.ToDate]))
: parameters[ParameterNames.ToDate];
var newParameters = new Dictionary <string, string>
{
{ ParameterNames.Country, parameters[ParameterNames.Country] },
{ ParameterNames.ToDate, toDate },
{ ParameterNames.FromDate, parameters[ParameterNames.FromDate] }
};
if (timeZone == DataType.DailyChanging)
{
//return new FutureTrendRepository().GetSupplyAnalysisCsvData(newParameters, scenarioId, siteGroup, fleetGroup
// , dataType, weeklyGrouping);
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var joinedData = timeZone == DataType.DailyChanging
? GenericSizingDataAccess.GetFutureTrendRawData(dataContext, parameters,
scenarioId)
: FutureTrendDataAccess.GetRawFrozenZoneFutureTrendData(dataContext, newParameters,
(byte)scenarioId);
return(GenerateExcelDataFromRaw(dataContext, joinedData, siteGroup, fleetGroup, dataType, weeklyGrouping));
}
}
else
{
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var joinedData = FutureTrendDataAccess.GetRawFrozenZoneFutureTrendData(dataContext,
newParameters,
(byte)scenarioId);
return(GenerateExcelDataFromRaw(dataContext, joinedData, siteGroup, fleetGroup, dataType,
weeklyGrouping));
}
}
return(null);
}
internal static string GetBenchmarkCsvData(Dictionary <string, string> parameters, bool constrainedForecast, int siteGroup, int fleetGroup)
{
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var restrictedData = GenericSizingDataAccess.GetBenchmarkRawData(parameters, dataContext);
var fullDataSet = GetJoinedExcelData(dataContext, restrictedData, constrainedForecast);
var csvData = new StringBuilder();
csvData.Append(GeForecastCsvHeader(siteGroup, fleetGroup, constrainedForecast));
var orderedData = fullDataSet.GroupByMany(CsvExportMethods.GetGroupingColumns(siteGroup, fleetGroup)).OrderBy(d => d.Key);
var keyList = new List <string>();
foreach (var gr in orderedData)
{
CheckSubGroup(gr, keyList, csvData);
}
return(csvData.ToString());
}
}
internal static string GetForecastExcelData(Dictionary <string, string> parameters, FutureTrendDataType dataType, int siteGroup, int fleetGroup, DataType timeZone)
{
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var csvData = new StringBuilder();
var rawData = timeZone == DataType.DailyChanging
? GenericSizingDataAccess.GetForecastRawData(parameters, dataContext)
: GetFrozenZoneForecastRawData(parameters, dataContext);
var fullDataSet = GetJoinedExcelData(dataContext, rawData);
var orderedData = fullDataSet.GroupByMany(CsvExportMethods.GetGroupingColumns(siteGroup, fleetGroup)).OrderBy(d => d.Key);
var keyList = new List <string>();
foreach (var gr in orderedData)
{
CheckSubGroup(gr, keyList, csvData);
}
return(csvData.ToString());
}
}
internal static string GetFutureTrendExcelData(Dictionary <string, string> parameters, FutureTrendDataType dataType, int scenarioId, int siteGroup, int fleetGroup, DataType timeZone)
{
var toDate = timeZone == DataType.FrozenZone
? SetFrozenZoneToDateLimit(DateTime.Parse(parameters[ParameterNames.ToDate]))
: parameters[ParameterNames.ToDate];
var newParameters = new Dictionary <string, string>
{
{ ParameterNames.Country, parameters[ParameterNames.Country] },
{ ParameterNames.ToDate, toDate },
{ ParameterNames.FromDate, parameters[ParameterNames.FromDate] }
};
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var joinedData = timeZone == DataType.DailyChanging
? GenericSizingDataAccess.GetFutureTrendRawData(dataContext, parameters, scenarioId)
: GetRawFrozenZoneFutureTrendData(dataContext, newParameters, (byte)scenarioId);
return(GenerateExcelDataFromRaw(dataContext, joinedData, siteGroup, fleetGroup, dataType));
}
}
internal static List <GraphSeries> GetFutureTrendGraphingData(Dictionary <string, string> parameters, FutureTrendDataType dataType, int scenarioId, DataType timeZone)
{
_timeZone = timeZone;
if (_timeZone == DataType.FrozenZone)
{
parameters[ParameterNames.ToDate] = SetFrozenZoneToDateLimit(DateTime.Parse(parameters[ParameterNames.ToDate]));
}
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
dataContext.CommandTimeout = TIMEOUT;
IQueryable <FutureTrendOrSupplyAnalysisRawDataHolder> groupedData;
if (_timeZone == DataType.DailyChanging)
{
//groupedData = new FutureTrendRepository().GetData(parameters, scenarioId);
groupedData = GenericSizingDataAccess.GetFutureTrendGroupyByDayData(dataContext, parameters, scenarioId).OrderBy(d => d.ReportDate);
}
else
{
var joinedData = GetRawFrozenZoneFutureTrendData(dataContext, parameters, (byte)scenarioId);
groupedData = from fc in joinedData
group fc by new { fc.ReportDate }
into g
orderby g.Key.ReportDate ascending
select new FutureTrendOrSupplyAnalysisRawDataHolder
{
ReportDate = g.Key.ReportDate,
Constrained = g.Sum(c => c.Constrained),
Unconstrained = g.Sum(c => c.Unconstrained),
Booked = g.Sum(c => c.Booked),
NessesaryConstrained = g.Sum(c => c.NessesaryConstrained),
NessesaryUnconstrained = g.Sum(c => c.NessesaryUnconstrained),
NessesaryBooked = g.Sum(c => c.NessesaryBooked),
Expected = g.Sum(c => c.Expected)
};
}
string foreCastSeriesName;
IQueryable <FutureTrendGraphDataHolder> data;
switch (dataType)
{
case FutureTrendDataType.Constrained:
data = from gd in groupedData
select new FutureTrendGraphDataHolder
{
ReportDate = gd.ReportDate,
Forecast = gd.Constrained,
Nessesary = gd.NessesaryConstrained,
Expected = gd.Expected,
};
foreCastSeriesName = "Constrained Forecast";
break;
case FutureTrendDataType.Unconstrained:
data = from gd in groupedData
select new FutureTrendGraphDataHolder
{
ReportDate = gd.ReportDate,
Forecast = gd.Unconstrained,
Nessesary = gd.NessesaryUnconstrained,
Expected = gd.Expected
};
foreCastSeriesName = "Unconstrained Forecast";
break;
case FutureTrendDataType.AlreadyBooked:
data = from gd in groupedData
select new FutureTrendGraphDataHolder
{
ReportDate = gd.ReportDate,
Forecast = gd.Booked,
Nessesary = gd.NessesaryBooked,
Expected = gd.Expected
};
foreCastSeriesName = "Already Booked Forecast";
break;
default:
throw new ArgumentException("Invalid Future Trend Datatype selected");
}
return(GetFutureTrendSeriesData(data, foreCastSeriesName, dataType));
}
}
internal static List <GraphSeries> GetSupplyAnalysisData(Dictionary <string, string> parameters, FutureTrendDataType dataType, int scenarioId, DataType timeZone)
{
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
if (timeZone == DataType.FrozenZone)
{
parameters[ParameterNames.ToDate] = FutureTrendDataAccess.SetFrozenZoneToDateLimit(DateTime.Parse(parameters[ParameterNames.ToDate]));
}
IQueryable <FutureTrendOrSupplyAnalysisRawDataHolder> groupedData;
if (timeZone == DataType.DailyChanging)
{
//groupedData = new FutureTrendRepository().GetData(parameters, scenarioId);
groupedData = GenericSizingDataAccess.GetFutureTrendGroupyByDayData(dataContext, parameters, scenarioId);
}
else
{
var joinedData = FutureTrendDataAccess.GetRawFrozenZoneFutureTrendData(dataContext, parameters, (byte)scenarioId);
groupedData = from fc in joinedData
group fc by new { fc.ReportDate }
into g
orderby g.Key.ReportDate ascending
select new FutureTrendOrSupplyAnalysisRawDataHolder {
ReportDate = g.Key.ReportDate,
Constrained = g.Sum(c => c.Constrained),
Unconstrained = g.Sum(c => c.Unconstrained),
Booked = g.Sum(c => c.Booked),
NessesaryConstrained = g.Sum(c => c.NessesaryConstrained),
NessesaryUnconstrained = g.Sum(c => c.NessesaryUnconstrained),
NessesaryBooked = g.Sum(c => c.NessesaryBooked),
Expected = g.Sum(c => c.Expected)
};
}
IQueryable <SupplyAnalysisGraphDataHolder> data;
switch (dataType)
{
case FutureTrendDataType.Constrained:
data = from gd in groupedData
select new SupplyAnalysisGraphDataHolder
{
ReportDate = gd.ReportDate,
Expected = gd.Expected - gd.NessesaryConstrained,
};
break;
case FutureTrendDataType.Unconstrained:
data = from gd in groupedData
select new SupplyAnalysisGraphDataHolder
{
ReportDate = gd.ReportDate,
Expected = gd.Expected - gd.NessesaryUnconstrained,
};
break;
case FutureTrendDataType.AlreadyBooked:
data = from gd in groupedData
select new SupplyAnalysisGraphDataHolder
{
ReportDate = gd.ReportDate,
Expected = gd.Expected - gd.NessesaryBooked,
};
break;
default:
throw new ArgumentException("Invalid Future Trend Datatype selected");
}
var sw = new Stopwatch();
sw.Start();
var finalGraphingData = data.ToList();
sw.Stop();
var ell = sw.Elapsed;
var graphData = GetSupplyAnalysisSeriesData(finalGraphingData);
graphData.Add(GetSupplyAnalysisWeeklyData(finalGraphingData));
return(graphData);
}
}
internal static List <GraphSeries> GetSiteComparisonData(Dictionary <string, string> parameters, int topicId, int scenarioId)
{
var country = parameters.ContainsKey(ParameterNames.Country) ? parameters[ParameterNames.Country] : null;
var cmsPoolId = parameters.ContainsKey(ParameterNames.Pool) ? parameters[ParameterNames.Pool] : null;
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var joinedData = GenericSizingDataAccess.GetRawSiteAndFleetComparisonData(dataContext, parameters, scenarioId);
//var joinedData = GetRawSiteAndFleetComparisonData(dataContext, parameters, scenarioId);
var groupedByLocationGroupAndCarClass = from jd in joinedData
group jd by new { jd.CarClassId, jd.LocationGroupId, jd.Country, jd.ReportDate }
into g
select new
{
g.Key.ReportDate,
g.Key.Country,
Constrained = g.Sum(d => d.Constrained),
Unconstrained = g.Sum(d => d.Unconstrained),
Booked = g.Sum(d => d.Booked),
OperationalFleet = g.Sum(d => d.ExpectedFleet),
CarClass = g.Key.CarClassId,
LocationGroup = g.Key.LocationGroupId,
ExpectedFleet = g.Sum(d => d.ExpectedFleet),
};
groupedByLocationGroupAndCarClass = from jd in groupedByLocationGroupAndCarClass
group jd by new { jd.CarClass, jd.LocationGroup, jd.Country }
into g
select new
{
ReportDate = DateTime.Now,
Country = g.Key.Country,
Constrained = g.Average(d => d.Constrained),
Unconstrained = g.Average(d => d.Unconstrained),
Booked = g.Average(d => d.Booked),
OperationalFleet = g.Average(d => d.OperationalFleet),
CarClass = g.Key.CarClass,
LocationGroup = g.Key.LocationGroup,
ExpectedFleet = g.Average(d => d.ExpectedFleet),
};
IQueryable <SiteAndFleetComparisonDataHolder> returnedData;
if (!string.IsNullOrEmpty(cmsPoolId))
{
var groupedByLocationGroup = from gd in groupedByLocationGroupAndCarClass
group gd by gd.LocationGroup
into g
select new
{
LocationGroup = g.Key,
ExpectedFleet = g.Sum(d => d.ExpectedFleet),
Constrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Constrained) / g.Sum(d => d.ExpectedFleet),
Unconstrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Unconstrained) / g.Sum(d => d.ExpectedFleet),
Booked = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Booked) / g.Sum(d => d.ExpectedFleet)
};
returnedData = from rd in groupedByLocationGroup
join lg in dataContext.CMS_LOCATION_GROUPs on rd.LocationGroup equals lg.cms_location_group_id
select new SiteAndFleetComparisonDataHolder()
{
ColumnName = lg.cms_location_group1,
Value =
topicId == 1 ? rd.ExpectedFleet :
topicId == 3 ? rd.Constrained :
topicId == 4 ? rd.Unconstrained :
topicId == 5 ? rd.Booked : 0
};
}
else if (!string.IsNullOrEmpty(country))
{
var groupedByPool = from gd in groupedByLocationGroupAndCarClass
join lg in dataContext.CMS_LOCATION_GROUPs on gd.LocationGroup equals lg.cms_location_group_id
group gd by lg.cms_pool_id
into g
select new
{
Pool = g.Key,
ExpectedFleet = g.Sum(d => d.ExpectedFleet),
Constrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Constrained) / g.Sum(d => d.ExpectedFleet),
Unconstrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Unconstrained) / g.Sum(d => d.ExpectedFleet),
Booked = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Booked) / g.Sum(d => d.ExpectedFleet)
};
returnedData = from rd in groupedByPool
join p in dataContext.CMS_POOLs on rd.Pool equals p.cms_pool_id
select new SiteAndFleetComparisonDataHolder()
{
ColumnName = p.cms_pool1,
Value =
topicId == 1 ? rd.ExpectedFleet :
topicId == 3 ? rd.Constrained :
topicId == 4 ? rd.Unconstrained :
topicId == 5 ? rd.Booked : 0
};
}
else
{
var groupedByCountry = from gd in groupedByLocationGroupAndCarClass
join lg in dataContext.CMS_LOCATION_GROUPs on gd.LocationGroup equals lg.cms_location_group_id
group gd by gd.Country
into g
select new
{
Country = g.Key,
ExpectedFleet = g.Sum(d => d.ExpectedFleet),
Constrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Constrained) / g.Sum(d => d.ExpectedFleet),
Unconstrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Unconstrained) / g.Sum(d => d.ExpectedFleet),
Booked = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Booked) / g.Sum(d => d.ExpectedFleet)
};
returnedData = from rd in groupedByCountry
join lg in dataContext.COUNTRies on rd.Country equals lg.country1
select new SiteAndFleetComparisonDataHolder()
{
ColumnName = lg.country_description,
Value =
topicId == 1 ? rd.ExpectedFleet :
topicId == 3 ? rd.Constrained :
topicId == 4 ? rd.Unconstrained :
topicId == 5 ? rd.Booked : 0
};
}
var columnNames = new List <object>();
var columnValues = new List <double>();
foreach (var fcd in returnedData.OrderByDescending(d => d.Value))
{
columnNames.Add(fcd.ColumnName);
columnValues.Add((double)fcd.Value);
}
var seriesInformation = new List <GraphSeries>
{
new GraphSeries("Fleet")
{
GraphColour = Color.LawnGreen,
Xvalue = columnNames,
Yvalue = columnValues
},
};
return(seriesInformation);
}
}
internal static string GetSiteComparisonExcelData(Dictionary <string, string> parameters, int siteGroup, int topicId, int scenarioId)
{
var csvData = new StringBuilder();
var csvHeader = new StringBuilder();
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var joinedData = GenericSizingDataAccess.GetRawSiteAndFleetComparisonData(dataContext, parameters, scenarioId);
var carDetails = new StringBuilder();
csvHeader.Append(CsvExportMethods.GetExportHeaders(siteGroup, 0));
if (!string.IsNullOrEmpty(parameters[ParameterNames.CarSegment]))
{
carDetails.Append((from cc in dataContext.CAR_SEGMENTs
where cc.car_segment_id == int.Parse(parameters[ParameterNames.CarSegment])
select cc.car_segment1).First() + ",");
}
if (!string.IsNullOrEmpty(parameters[ParameterNames.CarClassGroup]))
{
carDetails.Append((from cc in dataContext.CAR_CLASSes
where cc.car_class_id == int.Parse(parameters[ParameterNames.CarClassGroup])
select cc.car_class1).First() + ",");
}
if (!string.IsNullOrEmpty(parameters[ParameterNames.CarClass]))
{
carDetails.Append((from cc in dataContext.CAR_GROUPs
where cc.car_group_id == int.Parse(parameters[ParameterNames.CarClass])
select cc.car_group1).First() + ",");
}
var fullDataSet = from gd in joinedData
join lg in dataContext.CMS_LOCATION_GROUPs on gd.LocationGroupId equals lg.cms_location_group_id
select new SiteAndFleetComparisonExcelDataHolder
{
ReportDate = gd.ReportDate,
CountryId = gd.Country,
CountryName = lg.CMS_POOL.COUNTRy1.country_description,
Pool = lg.CMS_POOL.cms_pool1,
PoolId = lg.cms_pool_id,
LocationGroupId = gd.LocationGroupId,
LocationGroup = lg.cms_location_group1,
ExpectedFleet = gd.ExpectedFleet,
Constrained = gd.Constrained,
Unconstrained = gd.Unconstrained,
Booked = gd.Booked,
AdditionalColumns = carDetails.ToString()
};
IQueryable <string> data;
switch (siteGroup)
{
case 0:
data = from av in fullDataSet
group av by new { Country = av.CountryId, av.CountryName, av.ReportDate } into gd
orderby gd.Key.ReportDate, gd.Key.Country
select string.Format("{0},{1},{2}{3}\n", gd.Key.ReportDate.ToShortDateString(),
gd.Key.CountryName, gd.First().AdditionalColumns,
topicId == 1?Math.Round(gd.Sum(d => d.ExpectedFleet), 0, MidpointRounding.AwayFromZero).ToString() :
topicId == 3?gd.Sum(d => d.ExpectedFleet) == 0? "0" :
Math.Round(gd.Sum(d => d.Constrained) / gd.Sum(d => d.ExpectedFleet) * 100, 0, MidpointRounding.AwayFromZero).ToString() :
topicId == 4?gd.Sum(d => d.ExpectedFleet) == 0? "0" :
Math.Round(gd.Sum(d => d.Unconstrained) / gd.Sum(d => d.ExpectedFleet) * 100, 0, MidpointRounding.AwayFromZero).ToString() :
topicId == 5?gd.Sum(d => d.ExpectedFleet) == 0? "0" :
Math.Round(gd.Sum(d => d.Booked) / gd.Sum(d => d.ExpectedFleet) * 100, 0, MidpointRounding.AwayFromZero).ToString() : "0");
break;
case 1:
data = from av in fullDataSet
group av by new { Country = av.CountryId, av.CountryName, av.ReportDate, av.PoolId, av.Pool } into gd
orderby gd.Key.ReportDate, gd.Key.Country, gd.Key.PoolId
select string.Format("{0},{1},{2},{3}{4}\n", gd.Key.ReportDate.ToShortDateString(),
gd.Key.CountryName, gd.Key.Pool, gd.First().AdditionalColumns,
topicId == 1?Math.Round(gd.Sum(d => d.ExpectedFleet), 0, MidpointRounding.AwayFromZero).ToString() :
topicId == 3?gd.Sum(d => d.ExpectedFleet) == 0? "0" :
Math.Round(gd.Sum(d => d.Constrained) / gd.Sum(d => d.ExpectedFleet) * 100, 0, MidpointRounding.AwayFromZero).ToString() :
topicId == 4?gd.Sum(d => d.ExpectedFleet) == 0? "0" :
Math.Round(gd.Sum(d => d.Unconstrained) / gd.Sum(d => d.ExpectedFleet) * 100, 0, MidpointRounding.AwayFromZero).ToString() :
topicId == 5?gd.Sum(d => d.ExpectedFleet) == 0? "0" :
Math.Round(gd.Sum(d => d.Booked) / gd.Sum(d => d.ExpectedFleet) * 100, 0, MidpointRounding.AwayFromZero).ToString() : "0");
break;
case 2:
data = from av in fullDataSet
group av by new { Country = av.CountryId, av.CountryName, av.ReportDate, av.PoolId, av.Pool, av.LocationGroupId, av.LocationGroup } into gd
orderby gd.Key.ReportDate, gd.Key.Country, gd.Key.PoolId, gd.Key.LocationGroupId
select string.Format("{0},{1},{2},{3},{4}{5}\n", gd.Key.ReportDate.ToShortDateString(),
gd.Key.CountryName, gd.Key.Pool, gd.Key.LocationGroup, gd.First().AdditionalColumns,
topicId == 1?Math.Round(gd.Sum(d => d.ExpectedFleet), 0, MidpointRounding.AwayFromZero).ToString() :
topicId == 3?gd.Sum(d => d.ExpectedFleet) == 0? "0" :
Math.Round(gd.Sum(d => d.Constrained) / gd.Sum(d => d.ExpectedFleet) * 100, 0, MidpointRounding.AwayFromZero).ToString() :
topicId == 4?gd.Sum(d => d.ExpectedFleet) == 0? "0" :
Math.Round(gd.Sum(d => d.Unconstrained) / gd.Sum(d => d.ExpectedFleet) * 100, 0, MidpointRounding.AwayFromZero).ToString() :
topicId == 5?gd.Sum(d => d.ExpectedFleet) == 0? "0" :
Math.Round(gd.Sum(d => d.Booked) / gd.Sum(d => d.ExpectedFleet) * 100, 0, MidpointRounding.AwayFromZero).ToString() : "0");
break;
default:
throw new NotImplementedException("Invalid Site Group passed to GetSiteComparisonExcelData()");
}
if (!string.IsNullOrEmpty(parameters[ParameterNames.CarSegment]))
{
csvHeader.Append(CsvExportHeaders.CarSegment + ",");
}
if (!string.IsNullOrEmpty(parameters[ParameterNames.CarClassGroup]))
{
csvHeader.Append(CsvExportHeaders.CarClassGroup + ",");
}
if (!string.IsNullOrEmpty(parameters[ParameterNames.CarClass]))
{
csvHeader.Append(CsvExportHeaders.CarClass + ",");
}
switch (topicId)
{
case 1:
csvHeader.Append(CsvExportHeaders.OperationalFleet);
break;
case 2:
csvHeader.Append(CsvExportHeaders.AvailableFleet);
break;
case 3:
csvHeader.Append(CsvExportHeaders.UltilizationConstrained);
break;
case 4:
csvHeader.Append(CsvExportHeaders.UltilizationUnonstrained);
break;
case 5:
csvHeader.Append(CsvExportHeaders.UltilizationAlreadyBooked);
break;
default:
throw new NotImplementedException("Invalid topicId passed to GetSiteComparisonDataAccess");
}
csvData.AppendLine(csvHeader.ToString());
data.ToList().ForEach(d => csvData.Append(d.ToString()));
return(csvData.ToString());
}
}
internal static List <GraphSeries> GetBenchMarkGraphingData(Dictionary <string, string> parameters, bool constrainedForecast)
{
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var restrictedData = GenericSizingDataAccess.GetBenchmarkRawData(parameters, dataContext);
var groupedData = from rd in restrictedData
group rd by new { RepDate = rd.REP_DATE }
into g
orderby g.Key.RepDate ascending
select new BenchmarkGraphDataHolder
{
ReportDate = g.Key.RepDate,
CurrentOnRent = g.Sum(d => d.CURRENT_ONRENT) ?? 0,
OnRentLastYear = g.Sum(d => d.ONRENT_LY) ?? 0,
FrozenValue = constrainedForecast ? g.Sum(d => d.CMS_CONSTRAINED) ?? 0 : g.Sum(d => d.CMS_UNCONSTRAINED) ?? 0, // altered by Gavin
Week1 = constrainedForecast ? g.Sum(d => d.CMS_CONSTRAINED_WK1) ?? 0 : g.Sum(d => d.CMS_UNCONSTRAINED_WK1) ?? 0,
Week2 = constrainedForecast ? g.Sum(d => d.CMS_CONSTRAINED_WK2) ?? 0 : g.Sum(d => d.CMS_UNCONSTRAINED_WK2) ?? 0,
Week3 = constrainedForecast ? g.Sum(d => d.CMS_CONSTRAINED_WK3) ?? 0 : g.Sum(d => d.CMS_UNCONSTRAINED_WK3) ?? 0,
Week4 = constrainedForecast ? g.Sum(d => d.CMS_CONSTRAINED_WK4) ?? 0 : g.Sum(d => d.CMS_UNCONSTRAINED_WK4) ?? 0,
Week5 = constrainedForecast ? g.Sum(d => d.CMS_CONSTRAINED_WK5) ?? 0 : g.Sum(d => d.CMS_UNCONSTRAINED_WK5) ?? 0,
Week6 = constrainedForecast ? g.Sum(d => d.CMS_CONSTRAINED_WK6) ?? 0 : g.Sum(d => d.CMS_UNCONSTRAINED_WK6) ?? 0,
Week7 = constrainedForecast ? g.Sum(d => d.CMS_CONSTRAINED_WK7) ?? 0 : g.Sum(d => d.CMS_UNCONSTRAINED_WK7) ?? 0,
Week8 = constrainedForecast ? g.Sum(d => d.CMS_CONSTRAINED_WK8) ?? 0 : g.Sum(d => d.CMS_UNCONSTRAINED_WK8) ?? 0,
TopDown = constrainedForecast ? g.Sum(p => p.ADJUSTMENT_TD) ?? 0 : g.Sum(p => p.ADJUSTMENT_TD) ?? 0
};
var dateTimes = new List <object>();
var currentOnRent = new List <double>();
var onRentLastYear = new List <double>();
var frozenValue = new List <double>();
var week1 = new List <double>();
var week2 = new List <double>();
var week3 = new List <double>();
var week4 = new List <double>();
var week5 = new List <double>();
var week6 = new List <double>();
var week7 = new List <double>();
var week8 = new List <double>();
var topDown = new List <double>();
foreach (var ft in groupedData)
{
dateTimes.Add(ft.ReportDate);
currentOnRent.AddPreviousIfZero((double)ft.CurrentOnRent);
onRentLastYear.AddPreviousIfZero((double)ft.OnRentLastYear);
frozenValue.AddPreviousIfZero((double)ft.FrozenValue);
week1.AddPreviousIfZero((double)ft.Week1);
week2.AddPreviousIfZero((double)ft.Week2);
week3.AddPreviousIfZero((double)ft.Week3);
week4.AddPreviousIfZero((double)ft.Week4);
week5.AddPreviousIfZero((double)ft.Week5);
week6.AddPreviousIfZero((double)ft.Week6);
week7.AddPreviousIfZero((double)ft.Week7);
week8.AddPreviousIfZero((double)ft.Week8);
topDown.AddPreviousIfZero((double)ft.TopDown);
}
var colourList = new List <Color>();
var startingColour = Color.Orange;
colourList.Add(startingColour);
for (var i = 0; i < 8; i++)
{
colourList.Add(Color.FromArgb(startingColour.R - i * 20, startingColour.G - i * 20, startingColour.B));
}
var seriesInformation = new List <GraphSeries>
{
new GraphSeries("On Rent")
{
GraphColour = Color.Green, Xvalue = dateTimes, Yvalue = currentOnRent
},
new GraphSeries("On Rent LY")
{
GraphColour = Color.DimGray, Xvalue = dateTimes, Yvalue = onRentLastYear
},
new GraphSeries(constrainedForecast ? "Constrained" : "Unconstrained")
{
GraphColour = constrainedForecast ? Color.MidnightBlue : Color.CornflowerBlue,
Xvalue = dateTimes, Yvalue = frozenValue
},
new GraphSeries("Week 1")
{
GraphColour = colourList[7], Xvalue = dateTimes, Yvalue = week1
},
new GraphSeries("Week 2")
{
GraphColour = colourList[6], Xvalue = dateTimes, Yvalue = week2
},
new GraphSeries("Week 3")
{
GraphColour = colourList[5], Xvalue = dateTimes, Yvalue = week3
},
new GraphSeries("Week 4")
{
GraphColour = colourList[4], Xvalue = dateTimes, Yvalue = week4
},
new GraphSeries("Week 5")
{
GraphColour = colourList[3], Xvalue = dateTimes, Yvalue = week5
},
new GraphSeries("Week 6")
{
GraphColour = colourList[2], Xvalue = dateTimes, Yvalue = week6
},
new GraphSeries("Week 7")
{
GraphColour = colourList[1], Xvalue = dateTimes, Yvalue = week7
},
new GraphSeries("Week 8")
{
GraphColour = colourList[0], Xvalue = dateTimes, Yvalue = week8
},
new GraphSeries("Top Down")
{
GraphColour = Color.SandyBrown, Xvalue = dateTimes, Yvalue = topDown
}
};
return(seriesInformation);
}
}
internal static List <GraphSeries> GetFleetComarisonData(Dictionary <string, string> parameters, int topicId, int scenarioId)
{
var country = parameters.ContainsKey(ParameterNames.Country) ? parameters[ParameterNames.Country] : null;
var carSegmentId = parameters.ContainsKey(ParameterNames.CarSegment) ? parameters[ParameterNames.CarSegment] : null;
var carClassGroupId = parameters.ContainsKey(ParameterNames.CarClassGroup) ? parameters[ParameterNames.CarClassGroup] : null;
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var joinedData = GenericSizingDataAccess.GetRawSiteAndFleetComparisonData(dataContext, parameters, scenarioId);
var groupedByLocationGroupAndCarClass = from jd in joinedData
group jd by new { jd.CarClassId, jd.LocationGroupId, jd.Country, jd.ReportDate }
into g
select new
{
g.Key.ReportDate,
g.Key.Country,
Constrained = g.Sum(d => d.Constrained),
Unconstrained = g.Sum(d => d.Unconstrained),
Booked = g.Sum(d => d.Booked),
OperationalFleet = g.Sum(d => d.ExpectedFleet),
CarClass = g.Key.CarClassId,
LocationGroup = g.Key.LocationGroupId,
ExpectedFleet = g.Sum(d => d.ExpectedFleet),
};
groupedByLocationGroupAndCarClass = from jd in groupedByLocationGroupAndCarClass
group jd by new { jd.CarClass, jd.LocationGroup, jd.Country }
into g
select new
{
ReportDate = DateTime.Now,
Country = g.Key.Country,
Constrained = g.Average(d => d.Constrained),
Unconstrained = g.Average(d => d.Unconstrained),
Booked = g.Average(d => d.Booked),
OperationalFleet = g.Average(d => d.OperationalFleet),
CarClass = g.Key.CarClass,
LocationGroup = g.Key.LocationGroup,
ExpectedFleet = g.Average(d => d.ExpectedFleet),
};
IQueryable <SiteAndFleetComparisonDataHolder> returnedData;
if (!string.IsNullOrEmpty(carClassGroupId))
{
var groupedByCarClass = from gd in groupedByLocationGroupAndCarClass
group gd by gd.CarClass
into g
select new
{
CarClass = g.Key,
ExpectedFleet = g.Sum(d => d.ExpectedFleet),
Constrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Constrained) / g.Sum(d => d.ExpectedFleet),
Unconstrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Unconstrained) / g.Sum(d => d.ExpectedFleet),
Booked = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Booked) / g.Sum(d => d.ExpectedFleet)
};
returnedData = from rd in groupedByCarClass
join cg in dataContext.CAR_GROUPs on rd.CarClass equals cg.car_group_id
select new SiteAndFleetComparisonDataHolder
{
ColumnName = cg.car_group1,
Value = (
topicId == 1 ? rd.ExpectedFleet :
topicId == 3 ? rd.Constrained :
topicId == 4 ? rd.Unconstrained :
topicId == 5 ? rd.Booked : 0)
};
}
else if (!string.IsNullOrEmpty(carSegmentId))
{
var groupedByCarClassGroup = from gd in groupedByLocationGroupAndCarClass
join cg in dataContext.CAR_GROUPs on gd.CarClass equals cg.car_group_id
group gd by cg.car_class_id
into g
select new
{
CarClassGroup = g.Key,
ExpectedFleet = g.Sum(d => d.ExpectedFleet),
Constrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Constrained) / g.Sum(d => d.ExpectedFleet),
Unconstrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Unconstrained) / g.Sum(d => d.ExpectedFleet),
Booked = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Booked) / g.Sum(d => d.ExpectedFleet)
};
returnedData = from rd in groupedByCarClassGroup
join ccg in dataContext.CAR_CLASSes on rd.CarClassGroup equals ccg.car_class_id
select new SiteAndFleetComparisonDataHolder
{
ColumnName = ccg.car_class1,
Value = (
topicId == 1 ? rd.ExpectedFleet :
topicId == 3 ? rd.Constrained :
topicId == 4 ? rd.Unconstrained :
topicId == 5 ? rd.Booked : 0)
};
}
else if (!string.IsNullOrEmpty(country))
{
var groupedByCarSegment = from gd in groupedByLocationGroupAndCarClass
join lg in dataContext.CAR_GROUPs on gd.CarClass equals lg.car_group_id
group gd by lg.CAR_CLASS.car_segment_id
into g
select new
{
CarSegment = g.Key,
ExpectedFleet = g.Sum(d => d.ExpectedFleet),
Constrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Constrained) / g.Sum(d => d.ExpectedFleet),
Unconstrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Unconstrained) / g.Sum(d => d.ExpectedFleet),
Booked = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Booked) / g.Sum(d => d.ExpectedFleet)
};
returnedData = from rd in groupedByCarSegment
join cs in dataContext.CAR_SEGMENTs on rd.CarSegment equals cs.car_segment_id
select new SiteAndFleetComparisonDataHolder
{
ColumnName = cs.car_segment1,
Value = (
topicId == 1 ? rd.ExpectedFleet :
topicId == 3 ? rd.Constrained :
topicId == 4 ? rd.Unconstrained :
topicId == 5 ? rd.Booked : 0)
};
}
else
{
var groupedByCountry = from gd in groupedByLocationGroupAndCarClass
group gd by gd.Country
into g
select new
{
Country = g.Key,
ExpectedFleet = g.Sum(d => d.ExpectedFleet),
Constrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Constrained) / g.Sum(d => d.ExpectedFleet),
Unconstrained = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Unconstrained) / g.Sum(d => d.ExpectedFleet),
Booked = g.Sum(d => d.ExpectedFleet) == 0 ? 0 : g.Sum(d => d.Booked) / g.Sum(d => d.ExpectedFleet)
};
returnedData = from rd in groupedByCountry
join lg in dataContext.COUNTRies on rd.Country equals lg.country1
select new SiteAndFleetComparisonDataHolder
{
ColumnName = lg.country_description,
Value = (
topicId == 1 ? rd.ExpectedFleet :
topicId == 3 ? rd.Constrained :
topicId == 4 ? rd.Unconstrained :
topicId == 5 ? rd.Booked : 0)
};
}
var columnNames = new List <object>();
var columnValues = new List <double>();
foreach (var fcd in returnedData.OrderByDescending(d => d.Value))
{
columnNames.Add(fcd.ColumnName);
columnValues.Add((double)fcd.Value);
}
var seriesInformation = new List <GraphSeries>
{
new GraphSeries("Fleet")
{
GraphColour = Color.PowderBlue,
Xvalue = columnNames,
Yvalue = columnValues
},
};
return(seriesInformation);
}
}