internal static List <string> GetModelDescription(MarsDBDataContext dataContext)
{
var returned = (from rem in dataContext.Vehicles
select rem.ModelDescription).Distinct().ToList();
return(returned);
}
internal static List <ListItem> GetPoolListItems(Dictionary <DictionaryParameter, string> parameters
, MarsDBDataContext dataContext
, bool checkOut = false)
{
List <ListItem> returned;
var locationCountry = parameters[checkOut ? DictionaryParameter.CheckOutCountry : DictionaryParameter.LocationCountry].ToLower();
if (string.IsNullOrEmpty(locationCountry))
{
return(new List <ListItem>());
}
if (locationCountry.Contains(Separator))
{
var splitCountries = locationCountry.Split(Separator.ToCharArray());
returned = (from p in dataContext.CMS_POOLs
where splitCountries.Contains(p.country.ToLower())
orderby p.country, p.cms_pool1
select new ListItem(p.country + "-" + p.cms_pool1, p.cms_pool_id.ToString())).ToList();
}
else
{
returned = (from p in dataContext.CMS_POOLs
where p.country.ToLower() == locationCountry
orderby p.cms_pool1
select new ListItem(p.cms_pool1, p.cms_pool_id.ToString())).ToList();
}
return(returned);
}
//ILog _logger = log4net.LogManager.GetLogger("VehiclesAbroad");
public IList <string> getList(params string[] dependants)
{
// returns, as full country name, a list of the return countries from the reservation_europe_actual table
using (MarsDBDataContext db = new MarsDBDataContext()) {
List <string> l = new List <string>();
try { // volatile db code (and just in case for the date strings but will not throw and exception)
l.AddRange((from p in db.Reservations
join startloc in db.LOCATIONs on p.RENT_LOC equals startloc.dim_Location_id
join startCmsLoc in db.CMS_LOCATION_GROUPs on startloc.cms_location_group_id equals
startCmsLoc.cms_location_group_id
join startCmsP in db.CMS_POOLs on startCmsLoc.cms_pool_id equals startCmsP.cms_pool_id
join startCtry in db.COUNTRies on startCmsP.country equals startCtry.country1
// Return Location
join returnloc in db.LOCATIONs on p.RTRN_LOC equals returnloc.dim_Location_id
join returnCmsLoc in db.CMS_LOCATION_GROUPs on returnloc.cms_location_group_id equals
returnCmsLoc.cms_location_group_id
join returnCmsP in db.CMS_POOLs on returnCmsLoc.cms_pool_id equals returnCmsP.cms_pool_id
join returnCtry in db.COUNTRies on returnCmsP.country equals returnCtry.country1
where (p.COUNTRY != returnCtry.country_dw) && // ensure that the return is not the start country
(startCtry.active)
orderby returnCtry.country_description
select returnCtry.country_description).Distinct());
}
catch (Exception ex) {
//if (_logger != null) _logger.Error("Exception thrown in ReservationReturnCountryRepository, message : " + ex.Message);
}
return(l);
}
}
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> 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));
}
}
private void PopulateCountryParameters(MarsDBDataContext dataContext, string activeOnly)
{
var d = AdminParameterDataAccess.GetAllCountryListItems(dataContext, activeOnly);
ddlCountry.Items.Clear();
ddlCountry.Items.AddRange(d.ToArray());
}
public IMainFilterEntity GetBreakdown(string x, bool b)
{
IMainFilterEntity mf = new MainFilterEntity();
char[] c = { ' ', '|' };
string[] s = x.Split(c);
using (MarsDBDataContext db = new MarsDBDataContext())
{
var countryMatch = db.COUNTRies.Where(p => p.country1 == s[0].Substring(0, 2));
var firstCountryMatch = countryMatch.FirstOrDefault();
if (firstCountryMatch == null)
{
throw new NullReferenceException();
}
mf.Country = firstCountryMatch.country_description;
mf.Branch = s[0];
if (b)
{
mf.PoolRegion = db.LOCATIONs.Where(p => p.location1 == s[0]).FirstOrDefault().CMS_LOCATION_GROUP.CMS_POOL.cms_pool1;
mf.LocationGrpArea = db.LOCATIONs.Where(p => p.location1 == s[0]).FirstOrDefault().CMS_LOCATION_GROUP.cms_location_group1;
}
else
{
mf.PoolRegion = db.LOCATIONs.Where(p => p.location1 == s[0]).FirstOrDefault().OPS_AREA.OPS_REGION.ops_region1;
mf.LocationGrpArea = db.LOCATIONs.Where(p => p.location1 == s[0]).FirstOrDefault().OPS_AREA.ops_area1;
}
mf.CarSegment = db.CAR_GROUPs.Where(p => p.car_group1 == s[1] && p.CAR_CLASS.CAR_SEGMENT.country == s[0].Substring(0, 2)).FirstOrDefault().CAR_CLASS.CAR_SEGMENT.car_segment1;
mf.CarClass = db.CAR_GROUPs.Where(p => p.car_group1 == s[1] && p.CAR_CLASS.CAR_SEGMENT.country == s[0].Substring(0, 2)).FirstOrDefault().CAR_CLASS.car_class1;
mf.CarGroup = s[1];
}
return(mf);
}
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));
}
}
private static IQueryable <ForecastExcelDataHolder> GetJoinedExcelData(MarsDBDataContext dataContext, IQueryable <ForecastRawDataHolder> rawData)
{
return(from rd in rawData
join lg in dataContext.CMS_LOCATION_GROUPs on rd.LocationGroupId equals lg.cms_location_group_id
join cs in dataContext.CAR_GROUPs on rd.CarClassId equals cs.car_group_id
select new ForecastExcelDataHolder
{
ReportDate = rd.ReportDate,
CountryId = lg.CMS_POOL.country,
CountryName = lg.CMS_POOL.COUNTRy1.country_description,
Pool = lg.CMS_POOL.cms_pool1,
PoolId = lg.cms_pool_id,
LocationGroupId = rd.LocationGroupId,
LocationGroup = lg.cms_location_group1,
CarSegment = cs.CAR_CLASS.CAR_SEGMENT.car_segment1,
CarSegmentId = cs.CAR_CLASS.car_segment_id,
CarClassGroup = cs.CAR_CLASS.car_class1,
CarClassGroupId = cs.car_class_id,
CarClass = cs.car_group1,
CarClassId = rd.CarClassId,
OnRent = rd.OnRent,
OnRentLy = rd.OnRentLy,
Constrained = rd.Constrained,
Unconstrained = rd.Unconstrained,
Fleet = rd.Fleet,
AlreadyBooked = rd.AlreadyBooked,
TopDown = rd.TopDown,
BottomUp1 = rd.BottomUp1,
BottomUp2 = rd.BottomUp2,
Reconciliation = rd.Reconciliation
});
}
internal static List <string> GetVehicleColours(MarsDBDataContext dataContext)
{
var returned = (from rem in dataContext.Vehicles
select rem.Colour).Distinct().ToList();
return(returned);
}
private static IEnumerable <BenchmarkExcelDataHolder> GetJoinedExcelData(MarsDBDataContext dataContext, IQueryable <MARS_CMS_FORECAST_HISTORY> rawData, bool constrainedForecast)
{
var returned = from rd in rawData
join lg in dataContext.CMS_LOCATION_GROUPs on rd.CMS_LOCATION_GROUP_ID equals lg.cms_location_group_id
join cs in dataContext.CAR_GROUPs on rd.CAR_CLASS_ID equals cs.car_group_id
select new BenchmarkExcelDataHolder
{
ReportDate = rd.REP_DATE,
CountryId = lg.CMS_POOL.country,
CountryName = lg.CMS_POOL.COUNTRy1.country_description,
Pool = lg.CMS_POOL.cms_pool1,
PoolId = lg.cms_pool_id,
LocationGroupId = rd.CMS_LOCATION_GROUP_ID,
LocationGroup = lg.cms_location_group1,
CarSegment = cs.CAR_CLASS.CAR_SEGMENT.car_segment1,
CarSegmentId = cs.CAR_CLASS.car_segment_id,
CarClassGroup = cs.CAR_CLASS.car_class1,
CarClassGroupId = cs.car_class_id,
CarClass = cs.car_group1,
CarClassId = rd.CAR_CLASS_ID,
CurrentOnRent = rd.CURRENT_ONRENT ?? 0,
OnRentLastYear = rd.ONRENT_LY ?? 0,
FrozenValue = constrainedForecast ? rd.CMS_CONSTRAINED ?? 0 : rd.CMS_UNCONSTRAINED ?? 0,
Week1 = constrainedForecast ? rd.CMS_CONSTRAINED_WK1 ?? 0 : rd.CMS_UNCONSTRAINED_WK1 ?? 0,
Week2 = constrainedForecast ? rd.CMS_CONSTRAINED_WK2 ?? 0 : rd.CMS_UNCONSTRAINED_WK2 ?? 0,
Week3 = constrainedForecast ? rd.CMS_CONSTRAINED_WK3 ?? 0 : rd.CMS_UNCONSTRAINED_WK3 ?? 0,
Week4 = constrainedForecast ? rd.CMS_CONSTRAINED_WK4 ?? 0 : rd.CMS_UNCONSTRAINED_WK4 ?? 0,
Week5 = constrainedForecast ? rd.CMS_CONSTRAINED_WK5 ?? 0 : rd.CMS_UNCONSTRAINED_WK5 ?? 0,
Week6 = constrainedForecast ? rd.CMS_CONSTRAINED_WK6 ?? 0 : rd.CMS_UNCONSTRAINED_WK6 ?? 0,
Week7 = constrainedForecast ? rd.CMS_CONSTRAINED_WK7 ?? 0 : rd.CMS_UNCONSTRAINED_WK7 ?? 0,
Week8 = constrainedForecast ? rd.CMS_CONSTRAINED_WK8 ?? 0 : rd.CMS_UNCONSTRAINED_WK8 ?? 0,
TopDown = rd.ADJUSTMENT_TD ?? 0
};
//var data = returned.ToList();
//foreach(var bh in data)
//{
// if(bh.CurrentOnRent == 0)
// {
// var localBh = bh;
// var previousDaysData = data.FirstOrDefault(d =>
// d.ReportDate == localBh.ReportDate.AddDays(-1)
// && d.CountryId == localBh.CountryId
// && d.LocationGroupId == localBh.LocationGroupId
// && d.CarClassId == localBh.CarClassId
// );
// if(previousDaysData.CountryId == null)
// {
// var ss = 0;
// }
// else
// {
// var ww = 0;
// }
// }
//}
return(returned);
}
internal static List <string> GetDriverNames(MarsDBDataContext dataContext)
{
var returned = (from rem in dataContext.Vehicles
select rem.LastDriverName).Distinct().ToList();
return(returned);
}
internal static List <string> GetUnitNumbers(MarsDBDataContext dataContext)
{
var returned = (from rem in dataContext.Vehicles
select rem.UnitNumber.ToString()).Distinct().ToList();
return(returned);
}
internal static List <string> GetLicecePlates(MarsDBDataContext dataContext)
{
var returned = (from rem in dataContext.Vehicles
select rem.LicensePlate).Distinct().ToList();
return(returned);
}
private static IQueryable <GenericSizingHolder> GetFleetSizeFutureTrendData(MarsDBDataContext dc, Dictionary <string, string> parameters, int fleetPlan = 1)
{
var fullFleetSizeDataSet = from fsft in dc.FleetSizeFutureTrends
select fsft;
var restrictedData = ForecastParameterRestriction.RestrictFutureTrendDataByParameters(parameters,
fullFleetSizeDataSet,
dc, fleetPlan);
var futureTrendData = from ftd in restrictedData
//join lg in dc.CMS_LOCATION_GROUPs on ftd.LocGrpId equals lg.cms_location_group_id //Comment to include differnt country Nessesary Fleet Calculations
//join pools in dc.CMS_POOLs on lg.cms_pool_id equals pools.cms_pool_id
//where pools.country == ftd.Country
select new GenericSizingHolder
{
Date = ftd.TargetDate,
Country = ftd.Country,
LocationGroupId = ftd.LocGrpId,
CarClassId = ftd.CarGrpId,
ExpectedFleet = ftd.ExpectedFleet,
};
return(futureTrendData);
}
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());
}
public IQueryable <DayActualEntity> GetQueryable(MarsDBDataContext db, IMainFilterEntity filter, Enums.DayActualTime tm)
{
return(from p in db.FLEET_EUROPE_ACTUALs
join loc in db.LOCATIONs on p.LSTWWD equals loc.location1
join cg in db.CAR_GROUPs on new { vc = p.VC, c = p.COUNTRY } equals new { vc = cg.car_group1, c = cg.CAR_CLASS.CAR_SEGMENT.country }
where ((p.FLEET_RAC_TTL ?? false) || (p.FLEET_CARSALES ?? false)) &&
(loc.COUNTRy1.active) &&
(loc.COUNTRy1.country_description == filter.Country || String.IsNullOrEmpty(filter.Country)) &&
(loc.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1 == filter.PoolRegion || loc.OPS_AREA.OPS_REGION.ops_region1 == filter.PoolRegion || String.IsNullOrEmpty(filter.PoolRegion)) &&
(loc.CMS_LOCATION_GROUP.cms_location_group1 == filter.LocationGrpArea || loc.OPS_AREA.ops_area1 == filter.LocationGrpArea || String.IsNullOrEmpty(filter.LocationGrpArea)) &&
(loc.location1 == filter.Branch || String.IsNullOrEmpty(filter.Branch)) &&
(cg.CAR_CLASS.CAR_SEGMENT.car_segment1 == filter.CarSegment || String.IsNullOrEmpty(filter.CarSegment)) &&
(cg.CAR_CLASS.car_class1 == filter.CarClass || String.IsNullOrEmpty(filter.CarClass)) &&
(cg.car_group1 == filter.CarGroup || String.IsNullOrEmpty(filter.CarGroup))
where (tm == Enums.DayActualTime.THREE ? (p.CI_HOURS >= 0 && p.CI_HOURS <= 71) : (p.CI_DAYS >= 0 && p.CI_DAYS <= 29))
group p by new
{
t1 = (String.IsNullOrEmpty(filter.Country) ? loc.COUNTRy1.country_description :
String.IsNullOrEmpty(filter.CarSegment) ? cg.CAR_CLASS.CAR_SEGMENT.car_segment1 :
String.IsNullOrEmpty(filter.CarClass) ? cg.CAR_CLASS.car_class1 :
cg.car_group1),
t2 = (tm == Enums.DayActualTime.THREE ? p.CI_HOURS : p.CI_DAYS)
}
into g
select new DayActualEntity
{
Tme = g.Key.t2 ?? 0,
Label = g.Key.t1,
Available = g.Sum(p => p.RT) + g.Sum(p => p.OVERDUE) ?? 0 + g.Sum(p => p.MOVETYPE.ToUpper() == "T-O" ? p.TOTAL_FLEET : 0) ?? 0,
Opentrips = g.Sum(p => p.MOVETYPE.ToUpper() == "T-O" ? p.TOTAL_FLEET : 0) ?? 0,
Checkin = g.Sum(p => p.ON_RENT) ?? 0,
OnewayCheckin = g.Sum(p => p.LSTWWD != p.DUEWWD ? p.ON_RENT : 0) ?? 0,
LocalCheckIn = g.Sum(p => p.LSTWWD == p.DUEWWD ? p.ON_RENT : 0) ?? 0
});
}
internal static List <ListItem> GetAdminAreaListItems(Dictionary <DictionaryParameter, string> parameters
, MarsDBDataContext dataContext, string activeOnly, bool includeAllListItem = true)
{
var areaData = from r in dataContext.OPS_AREAs
select r;
if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.Region))
{
int regionId = int.Parse(parameters[DictionaryParameter.Region]);
areaData = areaData.Where(d => d.ops_region_id == regionId);
}
else if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.LocationCountry))
{
int countryId = int.Parse(parameters[DictionaryParameter.LocationCountry]);
areaData = areaData.Where(d => d.OPS_REGION.COUNTRy1.CountryId == countryId);
}
else
{
return(GetEmptyParameterListItem());
}
var returnedData = from p in areaData
orderby p.ops_area1
select new ListItem(p.ops_area1, p.ops_area_id.ToString());
var returned = returnedData.ToList();
if (includeAllListItem)
{
returned.Insert(0, ParameterDataAccess.EmptyItem);
}
return(returned);
}
protected BaseDataAccess(Dictionary <DictionaryParameter, string> parameters, MarsDBDataContext dbc)
{
Parameters = parameters;
DataContext = dbc ?? new MarsDBDataContext();
//DataContext.Log = new DebugTextWriter();
DataContext.CommandTimeout = 60;
}
internal static List <ListItem> GetAdminCarSegmentListItems(Dictionary <DictionaryParameter, string> parameters
, MarsDBDataContext dataContext, string activeOnly, bool includeAllListItem = true)
{
var segmentData = from cs in dataContext.CAR_SEGMENTs
select cs;
if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.LocationCountry))
{
int countryId = int.Parse(parameters[DictionaryParameter.LocationCountry]);
segmentData = segmentData.Where(d => d.COUNTRy1.CountryId == countryId);
}
else
{
return(GetEmptyParameterListItem());
}
var returnedData = from cs in segmentData
orderby cs.car_segment1
select new ListItem(cs.car_segment1, cs.car_segment_id.ToString());
var returned = returnedData.ToList();
if (includeAllListItem)
{
returned.Insert(0, ParameterDataAccess.EmptyItem);
}
return(returned);
}
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 <ListItem> GetAllCountryListItems(MarsDBDataContext dataContext, string activeOnly = null, bool includeAllListItem = true)
{
var countryEntities = dataContext.COUNTRies.Select(d => d);
if (activeOnly == true.ToString())
{
countryEntities = countryEntities.Where(d => d.active);
}
var countries = from c in countryEntities
orderby c.country_description
select new ListItem
{
Text = c.country_description,
Value = c.CountryId.ToString()
};
var returned = countries.ToList();
if (includeAllListItem)
{
returned.Insert(0, ParameterDataAccess.EmptyItem);
}
return(returned);
}
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 <ListItem> GetAdminCarGroupsWithinSegmentListItems(Dictionary <DictionaryParameter, string> parameters
, MarsDBDataContext dataContext, string activeOnly, bool includeAllListItem = true)
{
var groupData = from cc in dataContext.CAR_GROUPs
select cc;
if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.CarSegment))
{
int segmentId = int.Parse(parameters[DictionaryParameter.CarSegment]);
groupData = groupData.Where(d => d.CAR_CLASS.car_segment_id == segmentId);
}
else
{
return(GetEmptyParameterListItem());
}
var returnedData = from cg in groupData
orderby cg.car_group1
select new ListItem(cg.car_group1, cg.car_group_id.ToString());
var returned = returnedData.ToList();
if (includeAllListItem)
{
returned.Insert(0, ParameterDataAccess.EmptyItem);
}
return(returned);
}
internal static bool DoesCountryDwAlreadyExist(MarsDBDataContext dataContext, string countryDw, int countryId = 0)
{
var existingCountryWithDw = dataContext.COUNTRies.FirstOrDefault(d => d.country_dw == countryDw);
if (countryId == 0)
{
if (existingCountryWithDw != null)
{
return(true); //Another Country alraedy has this DW code
}
}
else
{
var countryDbEntry = dataContext.COUNTRies.Single(d => d.CountryId == countryId);
if (countryDbEntry.country_dw != countryDw)
{
if (existingCountryWithDw != null)
{
return(true); //Another Country alraedy has this DW code
}
}
return(false); //No Update needed
}
return(false);
}
internal static List <ListItem> GetAdminPoolListItems(Dictionary <DictionaryParameter, string> parameters
, MarsDBDataContext dataContext, string activeOnly, bool includeAllListItem = true)
{
var poolData = from p in dataContext.CMS_POOLs
select p;
if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.LocationCountry))
{
int countryId = int.Parse(parameters[DictionaryParameter.LocationCountry]);
poolData = poolData.Where(d => d.COUNTRy1.CountryId == countryId);
}
else
{
return(GetEmptyParameterListItem());
}
var returnedData = from p in poolData
orderby p.cms_pool1
select new ListItem(p.cms_pool1, p.cms_pool_id.ToString());
var returned = returnedData.ToList();
if (includeAllListItem)
{
returned.Insert(0, ParameterDataAccess.EmptyItem);
}
return(returned);
}
internal static List <ListItem> GetLocationGroupListItems(Dictionary <DictionaryParameter, string> parameters
, MarsDBDataContext dataContext, bool checkOut = false)
{
List <ListItem> returned;
var cmsPoolId = parameters[checkOut ? DictionaryParameter.CheckOutPool : DictionaryParameter.Pool];
if (string.IsNullOrEmpty(cmsPoolId))
{
return(new List <ListItem>());
}
if (cmsPoolId.Contains(Separator))
{
var splitPools = cmsPoolId.Split(Separator.ToCharArray()).Select(int.Parse).ToList();
returned = (from lg in dataContext.CMS_LOCATION_GROUPs
where splitPools.Contains(lg.CMS_POOL.cms_pool_id)
orderby lg.CMS_POOL.cms_pool1, lg.cms_location_group1
select new ListItem(lg.CMS_POOL.country + "-" + lg.CMS_POOL.cms_pool1
+ "-" + lg.cms_location_group1
, lg.cms_location_group_id.ToString())).ToList();
}
else
{
returned = (from lg in dataContext.CMS_LOCATION_GROUPs
where lg.CMS_POOL.cms_pool_id == int.Parse(cmsPoolId)
orderby lg.cms_location_group1
select new ListItem(lg.cms_location_group1, lg.cms_location_group_id.ToString())).ToList();
}
return(returned);
}
internal static List <ListItem> GetAdminLocationGroupListItems(Dictionary <DictionaryParameter, string> parameters
, MarsDBDataContext dataContext, string activeOnly, bool includeAllListItem = true)
{
var locationGroupData = from lg in dataContext.CMS_LOCATION_GROUPs
select lg;
if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.Pool))
{
int poolId = int.Parse(parameters[DictionaryParameter.Pool]);
locationGroupData = locationGroupData.Where(d => d.cms_pool_id == poolId);
}
else if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.LocationCountry))
{
int countryId = int.Parse(parameters[DictionaryParameter.LocationCountry]);
locationGroupData = locationGroupData.Where(d => d.CMS_POOL.COUNTRy1.CountryId == countryId);
}
else
{
return(GetEmptyParameterListItem());
}
var returnedData = from p in locationGroupData
orderby p.cms_location_group1
select new ListItem(p.cms_location_group1, p.cms_location_group_id.ToString());
var returned = returnedData.ToList();
if (includeAllListItem)
{
returned.Insert(0, ParameterDataAccess.EmptyItem);
}
return(returned);
}
public void FeaDataAccess()
{
var feaQ = new FeaPoolingDataAccess();
var feaFilterQ = new FeaFilteredQueryable();
var mfe = GetMainFilterExample();
List <DayActualEntity> site;
List <DayActualEntity> fleet;
List <DayActualEntity> dayActuals;
using (var db = new MarsDBDataContext())
{
var checkOut = feaFilterQ.GetFeaCheckOut(db, mfe);
var checkInData = feaFilterQ.GetFeaCheckIn(db, mfe);
mfe.Branch = "";
site = feaQ.GetFeaDataWithLabels(checkOut, checkInData,
Enums.DayActualTime.THREE, true, mfe, db).ToList();
mfe.Branch = "GELEJ50";
fleet = feaQ.GetFeaDataWithLabels(checkOut, checkInData,
Enums.DayActualTime.THREE, false, mfe, db).ToList();
dayActuals = feaQ.GetFeaDataWithoutLabels(checkOut, checkInData, Enums.DayActualTime.THREE, mfe, db).ToList();
}
TestResultComparison.CheckAllPoolingValues(dayActuals, fleet, site, "GELEJ50");
}
public ICarSearchDataEntity getVehicleDetail(string License)
{
using (MarsDBDataContext db = new MarsDBDataContext()) {
var qDetails = new VehicleDetailsQueryable().getQueryable(db);
return((from p in qDetails where p.License == License select p).First());
}
}
