private static string GenerateExcelDataFromRaw(MarsDBDataContext dataContext, IQueryable <FutureTrendOrSupplyAnalysisRawDataHolder> joinedRawData, int siteGroup, int fleetGroup, FutureTrendDataType dataType, bool weeklyGrouping) { var csvData = new StringBuilder(); var fullDataSet = GetJoinedExcelRawData(dataContext, joinedRawData, dataType); csvData.Append(CsvExportMethods.GetExportHeaders(siteGroup, fleetGroup, weeklyGrouping)); csvData.Append(weeklyGrouping ? string.Format("{0}\n", CsvExportHeaders.MinWeeklyDifference) : string.Format("{0},{1},{2}\n", CsvExportHeaders.NessesaryFleet, CsvExportHeaders.ExpectedFleet, CsvExportHeaders.Difference)); if (weeklyGrouping) { fullDataSet = fullDataSet.OrderBy(d => d.Week); } var groupedData = fullDataSet.GroupByMany(CsvExportMethods.GetGroupingColumns(siteGroup, fleetGroup, weeklyGrouping)); var orderedData = groupedData.OrderBy(d => d.Key); var keyList = new List <string>(); foreach (var gr in orderedData) { CheckSubGroup(gr, keyList, csvData, weeklyGrouping); } return(csvData.ToString()); }
internal static string GeForecastCsvHeader(int siteGroup, int fleetGroup) { var header = new StringBuilder(); header.Append(CsvExportMethods.GetExportHeaders(siteGroup, fleetGroup)); header.Append(string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}\n", CsvExportHeaders.OnRent, CsvExportHeaders.OnRentLy, CsvExportHeaders.Constrained, CsvExportHeaders.Unconstrained, CsvExportHeaders.Fleet, CsvExportHeaders.AlreadyBooked, CsvExportHeaders.TopDown, CsvExportHeaders.BottomUp1, CsvExportHeaders.BottomUp2, CsvExportHeaders.Reconciliation)); return(header.ToString()); }
private static string GenerateExcelDataFromRaw(MarsDBDataContext dataContext, IQueryable <AdditionDeletionRawDataHolder> joinedRawData, int siteGroup, int fleetGroup, bool isAddition) { var csvData = new StringBuilder(); var fullDataSet = GetJoinedExcelRawData(dataContext, joinedRawData); csvData.Append(CsvExportMethods.GetExportHeaders(siteGroup, fleetGroup, false, true)); csvData.Append(string.Format("{0}\n", isAddition ? CsvExportHeaders.Addition : CsvExportHeaders.Deletion)); var orderedData = fullDataSet.GroupByMany(CsvExportMethods.GetGroupingColumns(siteGroup, fleetGroup, false, true)).OrderBy(d => d.Key); var keyList = new List <string>(); foreach (var gr in orderedData) { CheckSubGroup(gr, keyList, csvData); } return(csvData.ToString()); }
private static string GenerateExcelDataFromRaw(MarsDBDataContext dataContext, IQueryable <FutureTrendOrSupplyAnalysisRawDataHolder> joinedRawData, int siteGroup, int fleetGroup, FutureTrendDataType dataType) { var csvData = new StringBuilder(); var fullDataSet = GetJoinedExcelRawData(dataContext, joinedRawData, dataType); var forecastHeader = string.Empty; switch (dataType) { case FutureTrendDataType.Constrained: forecastHeader = CsvExportHeaders.Constrained; break; case FutureTrendDataType.Unconstrained: forecastHeader = CsvExportHeaders.Unconstrained; break; case FutureTrendDataType.AlreadyBooked: forecastHeader = CsvExportHeaders.AlreadyBooked; break; } csvData.Append(CsvExportMethods.GetExportHeaders(siteGroup, fleetGroup)); csvData.Append(string.Format("{0},{1},{2}\n", forecastHeader, CsvExportHeaders.NessesaryFleet, CsvExportHeaders.ExpectedFleet)); 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()); }
private static string GeForecastCsvHeader(int siteGroup, int fleetGroup, bool constrainedForecast) { var header = new StringBuilder(); header.Append(CsvExportMethods.GetExportHeaders(siteGroup, fleetGroup)); if (constrainedForecast) { header.Append(string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11}\n", CsvExportHeaders.OnRent, CsvExportHeaders.OnRentLy, CsvExportHeaders.Constrained, CsvExportHeaders.ConstrainedWeek1, CsvExportHeaders.ConstrainedWeek2, CsvExportHeaders.ConstrainedWeek3, CsvExportHeaders.ConstrainedWeek4, CsvExportHeaders.ConstrainedWeek5, CsvExportHeaders.ConstrainedWeek6, CsvExportHeaders.ConstrainedWeek7, CsvExportHeaders.ConstrainedWeek8 , CsvExportHeaders.TopDown)); } else { header.Append(string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11}\n", CsvExportHeaders.OnRent, CsvExportHeaders.OnRentLy, CsvExportHeaders.Unconstrained, CsvExportHeaders.UnconstrainedWeek1, CsvExportHeaders.UnconstrainedWeek2, CsvExportHeaders.UnconstrainedWeek3, CsvExportHeaders.UnconstrainedWeek4, CsvExportHeaders.UnconstrainedWeek5, CsvExportHeaders.UnconstrainedWeek6, CsvExportHeaders.UnconstrainedWeek7, CsvExportHeaders.UnconstrainedWeek8 , CsvExportHeaders.TopDown)); } return(header.ToString()); }
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()); } }