public IList <IReservationDetailsEntity> getList(IMainFilterEntity filter, IReservationDetailsFilterEntity rdfe, string sortExpression, string sortDirection)
{
using (var db = new PoolingDataClassesDataContext())
{
//db.Log = new DebugTextWriter();
IQueryable <Mars.App.Classes.DAL.Pooling.PoolingDataContext.Reservation> q = _resCarFilterQ.FilterByCarParameters(db, filter);
if (rdfe.CheckInOut == CHECKIN)
{
q = _resFilterQ.FilterByReturnLocation(q, filter);
q = _resCIQueryable.GetQueryable(q, rdfe, filter);
}
else
{
q = _resFilterQ.FilterByRentalLocation(q, filter);
q = _resCOQueryable.GetQueryable(q, rdfe, filter);
}
q = _resBFQ.GetQueryable(q, rdfe);
q = _resSortQ.getQueryable(sortExpression, sortDirection, q);
_list = _resDetQ.getQueryable(db, q).ToList();
var ss = _list.Count();
return(_list);
}
}
private void fromAlerts(string p)
{
if (string.IsNullOrEmpty(p))
{
return;
}
IMainFilterEntity Filter = _alertBreakdownModel.GetBreakdown(p, CmsOpsModel.isCMS);
CmsOpsModel.CountryFilterModel.FilterDropDownList.SelectedValue = Filter.Country;
CmsOpsModel.GeneralThreeFilterModel.TopModel.bind(Filter.Country);
CmsOpsModel.GeneralThreeFilterModel.TopModel.FilterDropDownList.SelectedValue = Filter.PoolRegion;
CmsOpsModel.GeneralThreeFilterModel.MiddleModel.bind(Filter.PoolRegion, Filter.Country);
CmsOpsModel.GeneralThreeFilterModel.MiddleModel.FilterDropDownList.SelectedValue = Filter.LocationGrpArea;
CmsOpsModel.GeneralThreeFilterModel.BottomModel.bind(Filter.LocationGrpArea, Filter.PoolRegion,
Filter.Country);
CmsOpsModel.CountryFilterModel.FeedbackLabel.Text = Filter.Country;
CmsOpsModel.GeneralThreeFilterModel.TopModel.FeedbackLabel.Text = Filter.PoolRegion;
CmsOpsModel.GeneralThreeFilterModel.MiddleModel.FeedbackLabel.Text = Filter.LocationGrpArea;
CmsOpsModel.GeneralThreeFilterModel.BottomModel.SelectedValue = Filter.Branch;
CmsOpsModel.GeneralThreeFilterModel.BottomModel.FeedbackLabel.Text = Filter.Branch;
CarCascadeModel.TopModel.bind(Filter.Country);
CarCascadeModel.TopModel.FilterDropDownList.SelectedValue = Filter.CarSegment;
CarCascadeModel.TopModel.FeedbackLabel.Text = Filter.CarSegment;
CarCascadeModel.MiddleModel.bind(Filter.Country, Filter.CarSegment);
CarCascadeModel.MiddleModel.FilterDropDownList.SelectedValue = Filter.CarClass;
CarCascadeModel.MiddleModel.FeedbackLabel.Text = Filter.CarClass;
CarCascadeModel.BottomModel.bind(Filter.Country, Filter.CarSegment, Filter.CarClass);
CarCascadeModel.BottomModel.FilterDropDownList.SelectedValue = Filter.CarGroup;
CarCascadeModel.BottomModel.FeedbackLabel.Text = Filter.CarGroup;
SetSession();
}
public static List <DayActualEntity> GetLocationCarGroupsWithNegativeBalance(IMainFilterEntity mfe, DateTime toDate)
{
var allgroups = GetLocationCarGroups(mfe, toDate);
allgroups.RemoveAll(d => d.Balance >= 0);
var onlyLowestGroups = new List <DayActualEntity>();
onlyLowestGroups.AddRange(allgroups.Where(d => d.Tme == 0));
var next4Hours = allgroups.Where(d => d.Tme > 0 && d.Tme < 5).ToList();
var labels = next4Hours.Select(d => d.Label).Distinct();
onlyLowestGroups.AddRange(labels.Select(lbl => next4Hours.Where(d => d.Label == lbl).MinBy(d => d.Balance)));
var now = DateTime.Now.GetDateAndHourOnlyByCountry(mfe.Country);
//entityToAdd.Tme >= 5 && now.AddHours(entityToAdd.Tme).Day == now.Day
var restOfDay = allgroups.Where(d => d.Tme >= 5 && now.AddHours(d.Tme).Day == now.Day).ToList();
labels = restOfDay.Select(d => d.Label).Distinct();
onlyLowestGroups.AddRange(labels.Select(lbl => restOfDay.Where(d => d.Label == lbl).MinBy(d => d.Balance)));
var futureDays = allgroups.Where(d => now.AddHours(d.Tme).Day != now.Day).ToList();
labels = futureDays.Select(d => d.Label).Distinct();
onlyLowestGroups.AddRange(labels.Select(lbl => futureDays.Where(d => d.Label == lbl).MinBy(d => d.Balance)));
return(onlyLowestGroups);
}
public DataTable GetTable(IMainFilterEntity mfe)
{
var hourlyColumns = _time == Enums.DayActualTime.THREE;
var numberOfColumns = _time == Enums.DayActualTime.THIRTY ? 30 : MAXNOOFCOLUMNS;
var topics = ReservationsDataAccess.CalculateTopics(hourlyColumns, numberOfColumns, mfe, false).ToDictionary(d => d.Tme);
var dataTable = new DataTable();
var now = DateTime.Now.GetDateAndHourOnlyByCountry(mfe.Country);
for (int i = 0; i < MAXNOOFCOLUMNS; i++)
{
dataTable.Columns.Add(hourlyColumns ?
now.AddHours(i).ToString(CultureInfo.CurrentCulture)
: now.AddDays(i).ToString(CultureInfo.CurrentCulture), typeof(string)
);
}
dataTable.Rows.Add(topics.Values.Select(d => d.Balance.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.Available.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.Opentrips.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.Reservations.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.OnewayRes.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.GoldServiceReservations.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.PrepaidReservations.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.Checkin.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.OnewayCheckin.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.Offset.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.LocalCheckIn.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.Buffer.ToString()).ToArray());
dataTable.Rows.Add(topics.Values.Select(d => d.AddditionDeletion.ToString()).ToArray());
_DataTable = dataTable;
return(dataTable);
}
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
});
}
public IQueryable <DayActualEntity> GetQueryable(IQueryable <Reservation> q, IMainFilterEntity filter, Enums.DayActualTime time)
{
var now = DateTime.Now.GetDateAndHourOnlyByCountry(filter.Country);
return(from p in q
where (time == Enums.DayActualTime.THREE ? (
SqlMethods.DateDiffHour(now, p.RS_ARRIVAL_DATE.Value.AddHours(p.RS_ARRIVAL_TIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.RS_ARRIVAL_DATE.Value.AddHours(p.RS_ARRIVAL_TIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.RS_ARRIVAL_DATE.Value.AddHours(p.RS_ARRIVAL_TIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.RS_ARRIVAL_DATE.Value) <= 29))
group p by new
{
t1 = (String.IsNullOrEmpty(filter.Country) ? p.LOCATION1.COUNTRy1.country_description :
String.IsNullOrEmpty(filter.CarSegment) ? p.CAR_GROUP.CAR_CLASS.CAR_SEGMENT.car_segment1 :
String.IsNullOrEmpty(filter.CarClass) ? p.CAR_GROUP.CAR_CLASS.car_class1 :
p.CAR_GROUP.car_group1),
t2 = (time == Enums.DayActualTime.THREE ?
SqlMethods.DateDiffHour(now, p.RS_ARRIVAL_DATE.Value.AddHours(p.RS_ARRIVAL_TIME.Value.Hour)
.AddMinutes(p.RS_ARRIVAL_TIME.Value.Minute))
: SqlMethods.DateDiffDay(now, p.RS_ARRIVAL_DATE.Value))
}
into g
select new DayActualEntity
{
Label = g.Key.t1,
Tme = g.Key.t2,
Reservations = g.Sum(p => p.RES_ID_NBR != null ? 1 : 0),
OnewayRes = g.Sum(p => p.ONEWAY == "Y" ? 1 : 0),
GoldServiceReservations = g.Sum(p => p.GS == "Y" ? 1 : 0),
PrepaidReservations = g.Sum(p => p.PREPAID == "Y" ? 1 : 0),
Predelivery = g.Sum(p => p.PREDELIVERY == "Y" ? 1 : 0)
});
}
public static List <DayActualEntity> GetBuffersForAlerts(IMainFilterEntity filter, MarsDBDataContext db)
{
var bufferData = from p in db.ResBuffers
join loc in db.LOCATIONs on p.LocId equals loc.dim_Location_id
where (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)) &&
(p.CAR_GROUP.CAR_CLASS.CAR_SEGMENT.car_segment1 == filter.CarSegment || String.IsNullOrEmpty(filter.CarSegment)) &&
(p.CAR_GROUP.CAR_CLASS.car_class1 == filter.CarClass || String.IsNullOrEmpty(filter.CarClass)) &&
(p.CAR_GROUP.car_group1 == filter.CarGroup || String.IsNullOrEmpty(filter.CarGroup))
select new DayActualEntity
{
Buffer = p.Value,
Label = p.LOCATION.location1 + " " + p.CAR_GROUP.car_group1,
Tme = 0
};
if (bufferData.Any())
{
return(bufferData.ToList());
}
return(new List <DayActualEntity> {
new DayActualEntity()
});
}
public IQueryable <AlertTempEntity> GetQueryable(MarsDBDataContext db, IMainFilterEntity filter, DateTime selectedDate)
{
int endOfDay = 24 - DateTime.Now.Hour;
double maxHours = (selectedDate - DateTime.Now).TotalHours;
var returned = from p in db.FLEET_EUROPE_ACTUALs
join loc in db.LOCATIONs on new { l1 = p.LSTWWD, l2 = p.COUNTRY } equals new { l1 = loc.location1, l2 = loc.country }
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)) &&
(p.CI_HOURS >= 0 &&
p.CI_HOURS <= maxHours
)
group p by new { p.CI_HOURS, cg.car_group1, loc.location1 } into g
select new AlertTempEntity
{
rsTime = g.Key.CI_HOURS == 0 ? 1
: g.Key.CI_HOURS >= 1 && g.Key.CI_HOURS < 5 ? 2
: g.Key.CI_HOURS <= endOfDay ? 3
: 4,
rentLoc = g.Key.location1,
crGrp = g.Key.car_group1,
Amnt = g.Sum(p => p.MOVETYPE == "T-O" && p.TOTAL_FLEET == 1 ? 1 : 0) + g.Sum(p => p.RT) + g.Sum(p => p.ON_RENT ?? 0)
};
return(returned);
}
public List <DayActualEntity> GetQueryable(IQueryable <Reservation> q, IMainFilterEntity filter, Enums.DayActualTime time)
{
var now = DateTime.Now.GetDateAndHourOnlyByCountry(filter.Country);
var actualData = from p in q
where (time == Enums.DayActualTime.THREE?
(SqlMethods.DateDiffHour(now, p.RTRN_DATE.Value.AddHours(p.RTRN_TIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.ArrivalDateTime) >= 0 &&
SqlMethods.DateDiffHour(now, p.RTRN_DATE.Value.AddHours(p.RTRN_TIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.RTRN_DATE.Value.AddHours(p.RTRN_TIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.RTRN_DATE.Value) <= 29))
group p by(time == Enums.DayActualTime.THREE
?SqlMethods.DateDiffHour(now, p.RTRN_DATE.Value.AddHours(p.RTRN_TIME.Value.Hour))
: SqlMethods.DateDiffDay(now, p.RTRN_DATE.Value))
into g
select new DayActualEntity
{
Tme = g.Key,
Checkin = g.Sum(p => p.RES_ID_NBR != null ? 1 : 0),
OnewayCheckin = g.Sum(p => p.ONEWAY == "Y" ? 1 : 0),
LocalCheckIn = g.Sum(p => p.ONEWAY != "Y" ? 1 : 0)
};
var offsetData = from p in q
where (time == Enums.DayActualTime.THREE
? (SqlMethods.DateDiffHour(now, p.RTRN_DATE.Value.AddHours(p.RTRN_TIME.Value.Hour).AddHours(p.CI_HOURS_OFFSET.Value)) >= 0 &&
SqlMethods.DateDiffHour(now, p.ArrivalDateTime) >= 0 &&
SqlMethods.DateDiffHour(now, p.RTRN_DATE.Value.AddHours(p.RTRN_TIME.Value.Hour).AddHours(p.CI_HOURS_OFFSET.Value)) <= 71)
: (SqlMethods.DateDiffHour(now, p.RTRN_DATE.Value.AddHours(p.RTRN_TIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.RTRN_DATE.Value) <= 29))
group p by(time == Enums.DayActualTime.THREE
?SqlMethods.DateDiffHour(now, p.RTRN_DATE.Value.AddHours(p.RTRN_TIME.Value.Hour).AddHours(p.CI_HOURS_OFFSET.Value))
: SqlMethods.DateDiffDay(now, p.RTRN_DATE.Value))
into g
select new DayActualEntity
{
Tme = g.Key,
Offset = g.Sum(p => p.RES_ID_NBR != null ? 1 : 0),
};
var combinedData = from cd in actualData.ToList().Union(offsetData.ToList())
group cd by cd.Tme
into gd
select new DayActualEntity
{
Tme = gd.Key,
Checkin = gd.Sum(d => d.Checkin),
OnewayCheckin = gd.Sum(d => d.OnewayCheckin),
LocalCheckIn = gd.Sum(d => d.LocalCheckIn),
Offset = gd.Sum(d => d.Offset)
};
var returned = combinedData.ToList();
return(returned);
}
public static IQueryable <DayActualEntity> GetFeaFullForAlerts(IMainFilterEntity mfe, MarsDBDataContext db)
{
var checkOutData = FeaFilter.GetFeaCheckOut(db, mfe);
var checkInData = FeaFilter.GetFeaCheckIn(db, mfe);
var feaData = _feq.GetFullFeaDataForAlerts(checkOutData, checkInData, mfe, db);
return(feaData);
}
public static List <DayActualEntity> GetFeaData(IMainFilterEntity mfe, bool hourlyTimeSlots, MarsDBDataContext db)
{
var checkOutData = FeaFilter.GetFeaCheckOut(db, mfe);
var checkInData = FeaFilter.GetFeaCheckIn(db, mfe);
var feaData = _feq.GetFeaDataWithoutLabels(checkOutData, checkInData,
hourlyTimeSlots ? Enums.DayActualTime.THREE : Enums.DayActualTime.THIRTY, mfe, db).ToList();
var returned = feaData;
return(returned);
}
public static List <DayActualEntity> GetPoolingCheckOutData(IMainFilterEntity mfe, bool hourlyTimeSlots
, PoolingDataClassesDataContext db)
{
var q2And3 = CarParametersFilter.FilterByCarParameters(db, mfe, false);
q2And3 = SiteParametersFilter.FilterByRentalLocation(q2And3, mfe);
var l = _reqCo.GetReservationsWithoutLabels(q2And3, mfe,
hourlyTimeSlots ? Enums.DayActualTime.THREE : Enums.DayActualTime.THIRTY).ToList();
var returned = l;
return(returned);
}
public static List <DayActualEntity> GetCurrentAdditionDeletion(IMainFilterEntity filter
, bool hourly, MarsDBDataContext db, bool alertsRequest = false)
{
var startingTimePeriod = DateTime.SpecifyKind(DateTime.Now, DateTimeKind.Unspecified);
var endingTimePeriod = DateTime.SpecifyKind(DateTime.Now, DateTimeKind.Unspecified);
startingTimePeriod = startingTimePeriod.Date.AddHours(startingTimePeriod.Hour);
if (hourly)
{
endingTimePeriod = endingTimePeriod.Date.AddHours(endingTimePeriod.Hour);
endingTimePeriod = endingTimePeriod.AddHours(1).AddSeconds(-1);
}
else
{
endingTimePeriod = endingTimePeriod.Date.AddDays(1).AddSeconds(-1);
}
var additions = (from p in db.ResAdditions
join loc in db.LOCATIONs on p.LocId equals loc.dim_Location_id
where (loc.COUNTRy1.active) &&
p.RepDate >= startingTimePeriod &&
(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)) &&
(p.CAR_GROUP.CAR_CLASS.CAR_SEGMENT.car_segment1 == filter.CarSegment || String.IsNullOrEmpty(filter.CarSegment)) &&
(p.CAR_GROUP.CAR_CLASS.car_class1 == filter.CarClass || String.IsNullOrEmpty(filter.CarClass)) &&
(p.CAR_GROUP.car_group1 == filter.CarGroup || String.IsNullOrEmpty(filter.CarGroup))
select p).ToList();
var deletions = (from p in db.ResDeletions
join loc in db.LOCATIONs on p.LocId equals loc.dim_Location_id
where (loc.COUNTRy1.active) &&
p.RepDate >= startingTimePeriod &&
(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)) &&
(p.CAR_GROUP.CAR_CLASS.CAR_SEGMENT.car_segment1 == filter.CarSegment || String.IsNullOrEmpty(filter.CarSegment)) &&
(p.CAR_GROUP.CAR_CLASS.car_class1 == filter.CarClass || String.IsNullOrEmpty(filter.CarClass)) &&
(p.CAR_GROUP.car_group1 == filter.CarGroup || String.IsNullOrEmpty(filter.CarGroup))
select p).ToList();
List <DayActualEntity> returned = alertsRequest
? GetAdjustmentsWithLabel(startingTimePeriod, endingTimePeriod, additions, deletions)
: GetAdjustmentsWithoutLabel(hourly, startingTimePeriod, endingTimePeriod, additions, deletions);
return(returned);
}
public static List <DayActualEntity> GetPoolingCheckInDataWithLabels(IMainFilterEntity mfe, bool hourlyTimeSlots
, PoolingDataClassesDataContext db, bool siteQ)
{
var q2And3 = CarParametersFilter.FilterByCarParameters(db, mfe, true);
q2And3 = SiteParametersFilter.FilterByReturnLocation(q2And3, mfe);
var checkinData = siteQ
? _resCISiteQ.GetQueryableCI(q2And3, mfe, hourlyTimeSlots ? Enums.DayActualTime.THREE : Enums.DayActualTime.THIRTY, db)
: _resCIFleetQ.GetFleetReservationsWithLabels(q2And3, mfe, hourlyTimeSlots ? Enums.DayActualTime.THREE : Enums.DayActualTime.THIRTY, db);
var returned = checkinData.ToList();
return(returned);
}
public static List <DayActualEntity> GetPoolingCheckInData(IMainFilterEntity mfe, bool hourlyTimeSlots
, PoolingDataClassesDataContext db)
{
var q2And3 = CarParametersFilter.FilterByCarParameters(db, mfe, true);
q2And3 = SiteParametersFilter.FilterByReturnLocation(q2And3, mfe);
var checkinData = _reqCi.GetQueryable(q2And3, mfe,
hourlyTimeSlots ? Enums.DayActualTime.THREE : Enums.DayActualTime.THIRTY);
var returned = checkinData.ToList();
return(returned);
}
public static List <DayActualEntity> GetPoolingCheckOutDataWithLabels(IMainFilterEntity mfe, bool hourlyTimeSlots
, PoolingDataClassesDataContext db, bool siteQ)
{
var q2And3 = CarParametersFilter.FilterByCarParameters(db, mfe, false);
q2And3 = SiteParametersFilter.FilterByRentalLocation(q2And3, mfe);
var l = siteQ
? _resCOSiteQ.GetQueryableCO(q2And3, mfe,
hourlyTimeSlots ? Enums.DayActualTime.THREE : Enums.DayActualTime.THIRTY).ToList()
: _resCOFleetQ.GetQueryable(q2And3, mfe,
hourlyTimeSlots ? Enums.DayActualTime.THREE : Enums.DayActualTime.THIRTY).ToList();
var returned = l;
return(returned);
}
public static List <DayActualEntity> GetBuffersWithLabels(IMainFilterEntity filter, MarsDBDataContext db, bool siteQ)
{
var buffers = from ad in db.ResBuffers
where (ad.LOCATION.COUNTRy1.active) &&
(ad.LOCATION.COUNTRy1.country_description == filter.Country || String.IsNullOrEmpty(filter.Country)) &&
(ad.LOCATION.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1 == filter.PoolRegion ||
ad.LOCATION.OPS_AREA.OPS_REGION.ops_region1 == filter.PoolRegion || String.IsNullOrEmpty(filter.PoolRegion)) &&
(ad.LOCATION.CMS_LOCATION_GROUP.cms_location_group1 == filter.LocationGrpArea ||
ad.LOCATION.OPS_AREA.ops_area1 == filter.LocationGrpArea || String.IsNullOrEmpty(filter.LocationGrpArea)) &&
(ad.LOCATION.location1 == filter.Branch || String.IsNullOrEmpty(filter.Branch)) &&
(ad.CAR_GROUP.CAR_CLASS.CAR_SEGMENT.car_segment1 == filter.CarSegment || String.IsNullOrEmpty(filter.CarSegment)) &&
(ad.CAR_GROUP.CAR_CLASS.car_class1 == filter.CarClass || String.IsNullOrEmpty(filter.CarClass)) &&
(ad.CAR_GROUP.car_group1 == filter.CarGroup || String.IsNullOrEmpty(filter.CarGroup))
group ad by new
{
k1 = siteQ ? (String.IsNullOrEmpty(filter.Country) ? ad.LOCATION.COUNTRy1.country_description :
String.IsNullOrEmpty(filter.PoolRegion) ? filter.CmsLogic
? ad.LOCATION.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1
: ad.LOCATION.OPS_AREA.OPS_REGION.ops_region1 :
String.IsNullOrEmpty(filter.LocationGrpArea) ? filter.CmsLogic
? ad.LOCATION.CMS_LOCATION_GROUP.cms_location_group1
: ad.LOCATION.OPS_AREA.ops_area1 :
ad.LOCATION.location1)
: (String.IsNullOrEmpty(filter.Country) ? ad.LOCATION.COUNTRy1.country_description :
String.IsNullOrEmpty(filter.CarSegment) ? ad.CAR_GROUP.CAR_CLASS.CAR_SEGMENT.car_segment1 :
String.IsNullOrEmpty(filter.CarClass) ? ad.CAR_GROUP.CAR_CLASS.car_class1 :
ad.CAR_GROUP.car_group1)
}
into g
select new DayActualEntity
{
Label = g.Key.k1,
Buffer = g.Sum(d => d.Value)
};
if (buffers.Any())
{
return(buffers.ToList());
}
return(new List <DayActualEntity> {
new DayActualEntity()
});
}
IList <DayActualEntity> GetPoolingCOData(IMainFilterEntity mfe)
{
using (var db = new PoolingDataClassesDataContext())
{
IList <DayActualEntity> l = new List <DayActualEntity>();
try
{
IQueryable <App.Classes.DAL.Pooling.PoolingDataContext.Reservation> q2And3 = _resCarFilterQ.FilterByCarParameters(db, mfe, false);
q2And3 = _resFilterQ.FilterByRentalLocation(q2And3, mfe);
l = _reqCo.GetReservationsWithoutLabels(q2And3, mfe, _time).ToList();
}
catch (SqlException ex)
{
// ILog _logger = LogManager.GetLogger("Pooling");
// if (_logger != null) _logger.Error(" SQL Exception thrown in DayActualRepository accessing Reservations table, message : " + ex.Message);
}
return(l);
}
}
public string GetItem(IMainFilterEntity filter, int NumberOfDays)
{
using (var db = new MarsDBDataContext())
{
try
{
var q = _feaFilterQ.GetFeaCheckOut(db, filter);;
return((from p in q
where p.CI_HOURS < 0 && p.MOVETYPE == "R-O"
select p).Sum(p => p.TOTAL_FLEET).ToString());
}
catch (SqlException ex)
{
//ILog _logger = LogManager.GetLogger("Pooling");
//if (_logger != null) _logger.Error(" SQL Exception thrown in OverdueCollectionsRepository, message : " + ex.Message);
}
return("0");
}
}
public string GetItem(IMainFilterEntity filter, Int32 NumberOfDays)
{
using (var db = new MarsDBDataContext())
{
try
{
IQueryable <FLEET_EUROPE_ACTUAL> q = _feaFilterQ.GetFeaCheckOut(db, filter);
return((from p in q
where p.CI_DAYS < 0 &&
p.MOVETYPE == "T-O" || p.MOVETYPE == "L-O"
select p).Sum(p => p.TOTAL_FLEET).ToString());
}
catch (SqlException ex)
{
//ILog _logger = log4net.LogManager.GetLogger("Pooling");
// if (_logger != null) _logger.Error("SQL Exception thrown in OverdueOpenTripsRepository, message : " + ex.Message);
}
return("0");
}
}
IList <DayActualEntity> GetFeaData(IMainFilterEntity mfe)
{
using (var db = new MarsDBDataContext())
{
IList <DayActualEntity> feaData = new List <DayActualEntity>();
try
{
//db.Log = new DebugTextWriter();
var checkInData = _feaFilterQ.GetFeaCheckOut(db, mfe);
var checkoutData = _feaFilterQ.GetFeaCheckIn(db, mfe);
feaData = _feq.GetFeaDataWithoutLabels(checkInData, checkoutData, _time, mfe, db).ToList();
}
catch (SqlException ex)
{
//ILog _logger = LogManager.GetLogger("Pooling");
//if (_logger != null) _logger.Error(" SQL Exception thrown in DayActualRepository accessing FLEET_EUROPE_ACTUAL table, message : " + ex.Message);
}
return(feaData);
}
}
public static int GetBuffers(IMainFilterEntity filter, MarsDBDataContext db)
{
var bufferData = from p in db.ResBuffers
join loc in db.LOCATIONs on p.LocId equals loc.dim_Location_id
where (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)) &&
(p.CAR_GROUP.CAR_CLASS.CAR_SEGMENT.car_segment1 == filter.CarSegment || String.IsNullOrEmpty(filter.CarSegment)) &&
(p.CAR_GROUP.CAR_CLASS.car_class1 == filter.CarClass || String.IsNullOrEmpty(filter.CarClass)) &&
(p.CAR_GROUP.car_group1 == filter.CarGroup || String.IsNullOrEmpty(filter.CarGroup))
select p;
if (bufferData.Any())
{
var returned = bufferData.Sum(d => d.Value);
return(returned);
}
return(0);
}
public IQueryable <FLEET_EUROPE_ACTUAL> GetFeaCheckOut(MarsDBDataContext db, IMainFilterEntity filter)
{
//db.Log = new DebugTextWriter();
var returned = from p in db.FLEET_EUROPE_ACTUALs
join locA in db.LOCATIONs on p.LSTWWD equals locA.location1
join loc in db.LOCATIONs on locA.served_by_locn 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_OPS.HasValue && p.FLEET_RAC_OPS.Value && p.TOTAL_FLEET > 0
//&& (loc.COUNTRy1.active)
//&& loc.active
&& (loc.COUNTRy1.country_description == filter.Country || String.IsNullOrEmpty(filter.Country)) &&
p.COUNTRY == loc.country
//&& (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.served_by_locn == 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))
select p;
if (filter.ExcludeLongterm)
{
returned = returned.Except(returned.Where(d => d.LSTWWD.Substring(5, 1) != "5" &&
SqlMethods.DateDiffDay(d.LSTDATE, d.DUEDATE) > 27));
//returned = returned.Where(d => !(d.LSTWWD.Substring(5, 1) != "5" &&
// SqlMethods.DateDiffDay(d.LSTDATE, d.DUEDATE) > 27));
}
//var ss = returned.ToList();
return(returned);
}
{ // the return location
//public IQueryable<RESERVATIONS_EUROPE_ACTUAL> getQueryable(MarsDBDataContext db, IMainFilterEntity f)
//{
// return from p in db.RESERVATIONS_EUROPE_ACTUALs
// join c in db.COUNTRies on p.RTRN_LOC.Substring(0, 2) equals c.country1
// join loc in db.LOCATIONs on p.RTRN_LOC equals loc.location1
// join clg in db.CMS_LOCATION_GROUPs on loc.cms_location_group_id equals clg.cms_location_group_id
// join pl in db.CMS_POOLs on clg.cms_pool_id equals pl.cms_pool_id
// join oa in db.OPS_AREAs on loc.ops_area_id equals oa.ops_area_id
// join ors in db.OPS_REGIONs on oa.ops_region_id equals ors.ops_region_id
// where (f.Country == c.country_description || string.IsNullOrEmpty(f.Country))
// &&
// ((f.CmsLogic ? f.PoolRegion == pl.cms_pool1 : f.PoolRegion == ors.ops_region1) ||
// string.IsNullOrEmpty(f.PoolRegion))
// &&
// ((f.CmsLogic ? f.LocationGrpArea == clg.cms_location_group1 : f.LocationGrpArea == oa.ops_area1) ||
// string.IsNullOrEmpty(f.LocationGrpArea))
// && (f.Branch == p.RTRN_LOC || string.IsNullOrEmpty(f.Branch))
// select p;
//}
public IQueryable <Mars.App.Classes.DAL.MarsDBContext.Reservations> getQueryable(MarsDBDataContext db, IMainFilterEntity f)
{
return(from p in db.Reservations
// 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
join opR in db.OPS_REGIONs on returnCtry.country1 equals opR.country
join opA in db.OPS_AREAs on opR.ops_region_id equals opA.ops_region_id
where (f.Country == returnCtry.country_description || string.IsNullOrEmpty(f.Country))
&&
((f.CmsLogic ? f.PoolRegion == returnCmsP.cms_pool1 : f.PoolRegion == opR.ops_region1) ||
string.IsNullOrEmpty(f.PoolRegion))
&&
((f.CmsLogic
? f.LocationGrpArea == returnCmsLoc.cms_location_group1
: f.LocationGrpArea == opA.ops_area1) ||
string.IsNullOrEmpty(f.LocationGrpArea)) &&
(f.Branch == returnloc.served_by_locn || string.IsNullOrEmpty(f.Branch))
select p);
}
public IQueryable <PoolingDataContext.vw_Pooling_FEA> FilterData(IQueryable <PoolingDataContext.vw_Pooling_FEA> q, IMainFilterEntity filter, int numberOfDays)
{
return(from p in q
where (filter.Country == p.country_description || String.IsNullOrEmpty(filter.Country)) &&
((filter.Logic == true &&
(filter.PoolRegion == p.cms_pool || String.IsNullOrEmpty(filter.PoolRegion)) &&
(filter.LocationGrpArea == p.cms_location_group || String.IsNullOrEmpty(filter.LocationGrpArea)) &&
(filter.Branch == p.location || String.IsNullOrEmpty(filter.Branch))) ||
(filter.Logic == false &&
(filter.PoolRegion == p.ops_region || String.IsNullOrEmpty(filter.PoolRegion)) &&
(filter.LocationGrpArea == p.ops_area || String.IsNullOrEmpty(filter.LocationGrpArea)) &&
(filter.Branch == p.location || String.IsNullOrEmpty(filter.Branch)))) &&
(filter.CarSegment == p.car_segment || String.IsNullOrEmpty(filter.CarSegment)) &&
(filter.CarClass == p.car_class || String.IsNullOrEmpty(filter.CarClass)) &&
(filter.CarGroup == p.car_group || String.IsNullOrEmpty(filter.CarGroup))
select p);
}
public IQueryable <Mars.App.Classes.DAL.MarsDBContext.Reservations> getQueryable(MarsDBDataContext db, IMainFilterEntity f, IQueryable <Mars.App.Classes.DAL.MarsDBContext.Reservations> q)
{
return(from p in q
// Car details
join carGp in db.CAR_GROUPs on p.GR_INCL_GOLDUPGR equals carGp.car_group_id
join carCs in db.CAR_CLASSes on carGp.car_class_id equals carCs.car_class_id
join carS in db.CAR_SEGMENTs on carCs.car_segment_id equals carS.car_segment_id
where (f.CarSegment == carS.car_segment1 || string.IsNullOrEmpty(f.CarSegment)) &&
(f.CarClass == carCs.car_class1 || string.IsNullOrEmpty(f.CarClass)) &&
(f.CarGroup == carGp.car_group1 || string.IsNullOrEmpty(f.CarGroup))
select p);
}
public List <DayActualEntity> GetCheckOutReservationsForAlerts(IQueryable <Reservation> q, PoolingDataClassesDataContext db, IMainFilterEntity filter)
{
var now = DateTime.Now.GetDateAndHourOnlyByCountry(filter.Country);
var reservationData = from p in q
where (SqlMethods.DateDiffHour(now, p.RS_ARRIVAL_DATE.Value.AddHours(p.RS_ARRIVAL_TIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.RS_ARRIVAL_DATE.Value.AddHours(p.RS_ARRIVAL_TIME.Value.Hour)) <= 71)
group p by new
{
Time = SqlMethods.DateDiffHour(now, p.RS_ARRIVAL_DATE.Value.AddHours(p.RS_ARRIVAL_TIME.Value.Hour)),
CarGroup = p.CAR_GROUP.car_group1,
Location = p.RentalLocation.location1
}
into g
select new DayActualEntity
{
Label = g.Key.Location + " " + g.Key.CarGroup,
Tme = g.Key.Time,
Reservations = g.Sum(p => p.RES_ID_NBR != null ? 1 : 0)
};
var returned = reservationData.ToList();
return(returned);
}
public IQueryable <Reservation> GetQueryable(IQueryable <Reservation> q, IReservationDetailsFilterEntity rdfe, IMainFilterEntity filter)
{
var now = DateTime.Now.GetDateAndHourOnlyByCountry(filter.Country);
var returned = from p in q
where (p.RTRN_DATE >= rdfe.StartDate && p.RTRN_DATE <= rdfe.EndDate) &&
SqlMethods.DateDiffHour(now, p.RS_ARRIVAL_DATE.Value.AddHours(p.RS_ARRIVAL_TIME.Value.Hour))
>= 0
select p;
return(returned);
}
public static List <DayActualEntity> CalculateTopics(bool hourlyTimeSlots, int timeSlots, IMainFilterEntity mfe
, bool withLabels, bool siteQ = false)
{
var returned = new List <DayActualEntity>();
List <DayActualEntity> feaData, adjustments, checkInData, checkOutData;
var bufferData = new List <DayActualEntity>();
int buffer = 0;
using (var db = new MarsDBDataContext())
{
db.Log = new DebugTextWriter();
feaData = withLabels
? DayActualEntityRetrievers.GetFeaDataWithLabel(mfe, hourlyTimeSlots, db, siteQ)
: DayActualEntityRetrievers.GetFeaData(mfe, hourlyTimeSlots, db);
adjustments = withLabels
? DayActualEntityRetrievers.GetAdditionDeletionDataWithLabels(mfe, hourlyTimeSlots, db, siteQ)
: DayActualEntityRetrievers.GetCurrentAdditionDeletion(mfe, hourlyTimeSlots, db);
if (withLabels)
{
bufferData = DayActualEntityRetrievers.GetBuffersWithLabels(mfe, db, siteQ);
}
else
{
buffer = DayActualEntityRetrievers.GetBuffers(mfe, db);
}
}
using (var db = new PoolingDataClassesDataContext())
{
checkInData = withLabels
? DayActualEntityRetrievers.GetPoolingCheckInDataWithLabels(mfe, hourlyTimeSlots, db, siteQ)
: DayActualEntityRetrievers.GetPoolingCheckInData(mfe, hourlyTimeSlots, db);
checkOutData = withLabels
? DayActualEntityRetrievers.GetPoolingCheckOutDataWithLabels(mfe, hourlyTimeSlots, db, siteQ)
: DayActualEntityRetrievers.GetPoolingCheckOutData(mfe, hourlyTimeSlots, db);
}
var emptyHolder = new DayActualEntity();
var checkInLocations = checkInData.Select(d => d.Label).Distinct().ToList();
var checkOutLocations = checkInData.Select(d => d.Label).Distinct().ToList();
var labels = withLabels ? GetLabels(mfe, siteQ) : new List <string> {
null
};
if (withLabels)
{
labels.AddRange(checkInLocations);
labels.AddRange(checkOutLocations);
labels = labels.Distinct().ToList();
}
for (var i = 0; i < timeSlots; i++)
{
foreach (var lbl in labels)
{
var fea = feaData.FirstOrDefault(d => d.Tme == i && (d.Label == lbl)) ?? emptyHolder;
var adj = adjustments.FirstOrDefault(d => d.Tme == i && (d.Label == lbl)) ?? emptyHolder;
var ci = checkInData.FirstOrDefault(d => d.Tme == i && (d.Label == lbl)) ?? emptyHolder;
var co = checkOutData.FirstOrDefault(d => d.Tme == i && (d.Label == lbl)) ?? emptyHolder;
var bufferHolder = bufferData.FirstOrDefault(d => d.Label == lbl) ?? emptyHolder;
if (withLabels)
{
buffer = bufferHolder.Buffer;
}
var previousBalance = returned.FirstOrDefault(d => d.Tme == i - 1 && (d.Label == lbl));
var previousBalanceInt = previousBalance == null ? 0 : previousBalance.Balance;
var available = i == 0
? fea.Available + adj.AddditionDeletion
: previousBalanceInt + buffer;
var balance = available
//+ fea.Opentrips
- co.Reservations + (fea.Offset + ci.Offset)
- buffer + adj.AddditionDeletion;
var dae = new DayActualEntity
{
Tme = i,
Label = lbl,
Available = available,
Opentrips = fea.Opentrips,
Reservations = co.Reservations,
OnewayRes = co.OnewayRes,
GoldServiceReservations = co.GoldServiceReservations,
PrepaidReservations = 0, // co.PrepaidReservations,
Checkin = fea.Checkin + ci.Checkin,
OnewayCheckin = fea.OnewayCheckin + ci.OnewayCheckin,
Offset = fea.Offset + ci.Offset,
LocalCheckIn = fea.LocalCheckIn,// + ci.LocalCheckIn,
Balance = balance,
Buffer = buffer,
AddditionDeletion = adj.AddditionDeletion,
JustAdditions = adj.JustAdditions,
JustDeletions = adj.JustDeletions,
};
returned.Add(dae);
if (string.IsNullOrEmpty(lbl))
{
break;
}
}
}
return(returned);
}
private static List <string> GetLabels(IMainFilterEntity mfe, bool siteQ = false)
{
using (var db = new MarsDBDataContext())
{
if (string.IsNullOrEmpty(mfe.Country))
{
var countries = from c in db.COUNTRies
where c.active
select c.country_description;
return(countries.ToList());
}
if (siteQ)
{
if (string.IsNullOrEmpty(mfe.PoolRegion))
{
if (mfe.CmsLogic)
{
var pools = from p in db.CMS_POOLs
where p.COUNTRy1.country_description == mfe.Country
select p.cms_pool1;
return(pools.ToList());
}
var regions = from r in db.OPS_REGIONs
where r.COUNTRy1.country_description == mfe.Country
select r.ops_region1;
return(regions.ToList());
}
if (string.IsNullOrEmpty(mfe.LocationGrpArea))
{
if (mfe.CmsLogic)
{
var lg = from l in db.CMS_LOCATION_GROUPs
where l.CMS_POOL.cms_pool1 == mfe.PoolRegion &&
l.CMS_POOL.COUNTRy1.country_description == mfe.Country
select l.cms_location_group1;
return(lg.ToList());
}
var area = from a in db.OPS_AREAs
where a.OPS_REGION.ops_region1 == mfe.PoolRegion &&
a.OPS_REGION.COUNTRy1.country_description == mfe.Country
select a.ops_area1;
return(area.ToList());
}
if (string.IsNullOrEmpty(mfe.Branch))
{
if (mfe.CmsLogic)
{
var branches = from b in db.LOCATIONs
where b.CMS_LOCATION_GROUP.cms_location_group1 == mfe.LocationGrpArea &&
b.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1 == mfe.PoolRegion &&
b.COUNTRy1.country_description == mfe.Country &&
b.active
select b.location1;
return(branches.ToList());
}
var branchesOps = from b in db.LOCATIONs
where b.OPS_AREA.ops_area1 == mfe.LocationGrpArea &&
b.OPS_AREA.OPS_REGION.ops_region1 == mfe.PoolRegion &&
b.COUNTRy1.country_description == mfe.Country &&
b.active
select b.location1;
return(branchesOps.ToList());
}
}
if (string.IsNullOrEmpty(mfe.CarSegment))
{
var segment = from cs in db.CAR_SEGMENTs
where cs.COUNTRy1.country_description == mfe.Country
select cs.car_segment1;
return(segment.ToList());
}
if (string.IsNullOrEmpty(mfe.CarClass))
{
var carClasses = from cc in db.CAR_CLASSes
where cc.CAR_SEGMENT.car_segment1 == mfe.CarSegment &&
cc.CAR_SEGMENT.COUNTRy1.country_description == mfe.Country
orderby cc.sort_car_class
select cc.car_class1;
return(carClasses.ToList());
}
if (string.IsNullOrEmpty(mfe.CarGroup))
{
var carGroups = from cg in db.CAR_GROUPs
where cg.CAR_CLASS.car_class1 == mfe.CarClass &&
cg.CAR_CLASS.CAR_SEGMENT.car_segment1 == mfe.CarSegment &&
cg.CAR_CLASS.CAR_SEGMENT.COUNTRy1.country_description == mfe.Country
orderby cg.sort_car_group
select cg.car_group1;
return(carGroups.ToList());
}
}
return(null);
}
