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