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()); } }