internal static string GetKpiCsvHeader(Dictionary <string, string> parameters, KpiCalculationType kpiType, FutureTrendDataType futureTrendDataType)
{
var csvHeader = new StringBuilder();
csvHeader.Append(string.Format("{0}, {1},", CsvExportHeaders.ReportDate, CsvExportHeaders.Country));
if (!string.IsNullOrEmpty(parameters[ParameterNames.Pool]))
{
csvHeader.Append(CsvExportHeaders.Pool + ",");
}
if (!string.IsNullOrEmpty(parameters[ParameterNames.LocationGroup]))
{
csvHeader.Append(CsvExportHeaders.LocationGroup + ",");
}
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 (kpiType)
{
case KpiCalculationType.OperationalUtilization:
switch (futureTrendDataType)
{
case FutureTrendDataType.Constrained:
csvHeader.Append(CsvExportHeaders.UltilizationConstrained);
break;
case FutureTrendDataType.Unconstrained:
csvHeader.Append(CsvExportHeaders.UltilizationUnonstrained);
break;
case FutureTrendDataType.AlreadyBooked:
csvHeader.Append(CsvExportHeaders.UltilizationAlreadyBooked);
break;
}
break;
case KpiCalculationType.IdleFleet:
csvHeader.Append(CsvExportHeaders.IdleFleet);
break;
case KpiCalculationType.IdleFleetPercentage:
csvHeader.Append(CsvExportHeaders.IdleFleetPercentage);
break;
}
return(csvHeader.ToString());
}
internal static List <GraphSeries> GetHistoricalKpiData(Dictionary <string, string> parameters, KpiCalculationType kpiType)
{
using (var dataContext = new MarsDBDataContext(MarsConnection.ConnectionString))
{
var rawData = GetFrozenZoneRawKpiData(dataContext, parameters);
var groupedData = from fc in rawData
group fc by fc.ReportDate
into g
orderby g.Key ascending
select g;
var kpiGraphingData = from gd in groupedData
select new KpiGraphDataHolder
{
ReportDate = gd.Key,
Kpi = kpiType == KpiCalculationType.OperationalUtilization ? gd.Sum(d => d.OperationalFleet) == 0 ? 0 :
gd.Sum(d => d.OnRent) / gd.Sum(d => d.OperationalFleet) :
kpiType == KpiCalculationType.IdleFleetPercentage ? gd.Sum(d => d.TotalFleet) == 0 ? 0 :
gd.Sum(d => d.IdleFleet) / gd.Sum(d => d.TotalFleet) :
kpiType == KpiCalculationType.IdleFleet ? gd.Sum(d => d.IdleFleet) : 0
};
return(GraphKpiData(kpiGraphingData));
}
}
internal static string GetFrozenZoneKpiExcelData(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 = GetFrozenZoneRawKpiData(dataContext, parameters);
var groupedData = from rw in rawData
group rw by new { rw.ReportDate, rw.Country } into gd
join c in dataContext.COUNTRies on gd.Key.Country equals c.country1
select new KpiExcelDataHolder
{
ReportDate = gd.Key.ReportDate,
Country = c.country_description,
Kpi = kpiType == KpiCalculationType.OperationalUtilization ? gd.Sum(d => d.OperationalFleet) == 0 ? 0 :
gd.Sum(d => d.OnRent) * 100 / gd.Sum(d => d.OperationalFleet) :
kpiType == KpiCalculationType.IdleFleetPercentage ? gd.Sum(d => d.TotalFleet) == 0 ? 0 :
gd.Sum(d => d.IdleFleet) * 100 / gd.Sum(d => d.TotalFleet) :
kpiType == KpiCalculationType.IdleFleet ? gd.Sum(d => d.IdleFleet) : 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 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());
}
}
