public override IQueryable <ReservationMatchEntity> GetQueryable(MarsDBDataContext db, IFilterEntity Filters, ICarFilterEntity CarFilters, string SortExpression)
{
try {
var tmp = new FleetEuropeActualQueryable().GetQueryable(db);
IQueryable <Reservations> q1 = 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
// Car details
join carGp in db.CAR_GROUPs on p.GR_INCL_GOLDUPGR equals carGp.car_group_id
where (p.COUNTRY != returnloc.country) &&
(Filters.OwnCountry == p.COUNTRY || Filters.OwnCountry == "" || Filters.OwnCountry == null)
&&
(p.ReservedCarGroup == Filters.CarGroup || Filters.CarGroup == "" || Filters.CarGroup == null) &&
(startCtry.active) && // only corporate countries
(Filters.CarGroup == carGp.car_group1 || string.IsNullOrEmpty(Filters.CarGroup))
&&
(p.RS_ARRIVAL_DATE >= Filters.ReservationStartDate &&
p.RS_ARRIVAL_DATE <= Filters.ReservationEndDate)
// reservation start dates
select p;
q1 = new ReservationMatchCarSegmentQueryable().GetQueryable(db, q1, Filters.CarSegment);
q1 = new ReservationMatchCarClassQueryable().GetQueryable(db, q1, Filters.CarClass);
q1 = new ReservationMatchPoolQueryable().GetQueryable(db, q1, Filters.Pool);
q1 = new ReservationMatchReturnQueryable().GetQueryable(db, q1, Filters.DueCountry, Filters.DuePool, Filters.DueLocationGroup);
IQueryable <ReservationMatchEntity> q = from p in q1
// Start Location
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
// 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
join t1 in tmp on
new { x0 = returnloc.served_by_locn.Substring(0, 2), x1 = startCmsLoc.cms_location_group1 } equals
new { x0 = t1.OwningCountry, x1 = t1.CurrentLocation } into tj
from t in tj.DefaultIfEmpty()
where
(returnCmsLoc.cms_location_group1 == Filters.Location || Filters.Location == "" ||
Filters.Location == null)
select new ReservationMatchEntity
{
ResLocation = startloc.served_by_locn,
ResGroup = carGp.car_group1,
ResCheckoutDate =
new DateTime(p.RS_ARRIVAL_DATE.Value.Year, p.RS_ARRIVAL_DATE.Value.Month,
p.RS_ARRIVAL_DATE.Value.Day, p.RS_ARRIVAL_TIME.Value.Hour,
p.RS_ARRIVAL_TIME.Value.Minute, 0),
ResCheckoutLoc = startloc.served_by_locn,
ResCheckinLoc = returnloc.served_by_locn,
ResId = p.RES_ID_NBR,
ResNoDaysUntilCheckout =
SqlMethods.DateDiffDay(DateTime.Now, p.RS_ARRIVAL_DATE.Value).ToString(),
ResNoDaysReserved = ((int)p.RES_DAYS).ToString(),
ResDriverName = p.CUST_NAME,
Matches = t.Count == null ? "0" : t.Count.ToString()
};
switch (SortExpression)
{
case "ResLocation": q = q.OrderBy(p => p.ResLocation); break;
case "ResLocation DESC": q = q.OrderByDescending(p => p.ResLocation); break;
case "ResGroup": q = q.OrderBy(p => p.ResGroup); break;
case "ResGroup DESC": q = q.OrderByDescending(p => p.ResGroup); break;
case "ResCheckoutDate": q = q.OrderBy(p => p.ResCheckoutDate); break;
case "ResCheckoutDate DESC": q = q.OrderByDescending(p => p.ResCheckoutDate); break;
case "ResCheckinLoc": q = q.OrderBy(p => p.ResCheckinLoc); break;
case "ResCheckinLoc DESC": q = q.OrderByDescending(p => p.ResCheckinLoc); break;
case "ResNoDaysUntilCheckout": q = q.OrderBy(p => p.ResNoDaysUntilCheckout); break;
case "ResNoDaysUntilCheckout DESC": q = q.OrderByDescending(p => p.ResNoDaysUntilCheckout); break;
case "ResNoDaysReserved": q = q.OrderBy(p => p.ResNoDaysReserved); break;
case "ResNoDaysReserved DESC": q = q.OrderByDescending(p => p.ResNoDaysReserved); break;
case "ResDriverName": q = q.OrderBy(p => p.ResDriverName); break;
case "ResDriverName DESC": q = q.OrderByDescending(p => p.ResDriverName); break;
case "ResId": q = q.OrderBy(p => p.ResId); break;
case "ResId DESC": q = q.OrderByDescending(p => p.ResId); break;
case "Matches": q = q.OrderBy(p => p.Matches); break;
case "Matches DESC": q = q.OrderByDescending(p => p.Matches); break;
default: q = q.OrderBy(p => p.ResNoDaysUntilCheckout).ThenBy(p => p.ResLocation); break;
}
return(q);
} catch (Exception ex) {
//.Error("Exception thrown in ReservationDetails Model, exception = " + ex);
throw new Exception("Exception thrown in ReservationDetails Model, exception = " + ex);
}
}
public IQueryable <ReservationMatchEntity> GetOldDataQueryable(MarsDBDataContext db, IFilterEntity Filters, ICarFilterEntity CarFilters, string SortExpression)
{
try
{
var tmp = new FleetEuropeActualQueryable().GetQueryable(db);
IQueryable <RESERVATIONS_EUROPE_ACTUAL> q1 = from p in db.RESERVATIONS_EUROPE_ACTUALs
join tc1 in db.COUNTRies on p.COUNTRY equals tc1.country1
where (p.COUNTRY != p.RTRN_LOC.Substring(0, 2)) &&
(Filters.OwnCountry == p.COUNTRY || Filters.OwnCountry == "" || Filters.OwnCountry == null) &&
(p.RES_VEH_CLASS == Filters.CarGroup || Filters.CarGroup == "" || Filters.CarGroup == null) &&
(tc1.active) && // only corporate countries
(Filters.CarGroup == p.GR_INCL_GOLDUPGR || string.IsNullOrEmpty(Filters.CarGroup)) &&
(p.RS_ARRIVAL_DATE >= Filters.ReservationStartDate && p.RS_ARRIVAL_DATE <= Filters.ReservationEndDate) // reservation start dates
select p;
q1 = new OldReservationMatchCarSegmentQueryable().GetQueryable(db, q1, Filters.CarSegment);
q1 = new OldReservationMatchCarClassQueryable().GetQueryable(db, q1, Filters.CarClass);
q1 = new OldReservationMatchPoolQueryable().GetQueryable(db, q1, Filters.Pool);
q1 = new OldReservationMatchReturnQueryable().GetQueryable(db, q1, Filters.DueCountry, Filters.DuePool, Filters.DueLocationGroup);
IQueryable <ReservationMatchEntity> q = from p in q1
join tLoc in db.CMS_LOCATION_GROUPs on p.CMS_LOC_GRP equals tLoc.cms_location_group_id.ToString()
join t1 in tmp on new { x0 = p.RTRN_LOC.Substring(0, 2), x1 = tLoc.cms_location_group1 } equals new { x0 = t1.OwningCountry, x1 = t1.CurrentLocation } into tj
from t in tj.DefaultIfEmpty()
where (tLoc.cms_location_group1 == Filters.Location || Filters.Location == "" || Filters.Location == null)
select new ReservationMatchEntity
{
ResLocation = p.RES_LOC,
ResGroup = p.GR_INCL_GOLDUPGR,
ResCheckoutDate = new DateTime(p.RS_ARRIVAL_DATE.Value.Year, p.RS_ARRIVAL_DATE.Value.Month, p.RS_ARRIVAL_DATE.Value.Day, p.RS_ARRIVAL_TIME.Value.Hour, p.RS_ARRIVAL_TIME.Value.Minute, 0),
ResCheckoutLoc = p.RENT_LOC,
ResCheckinLoc = p.RTRN_LOC,
ResId = p.RES_ID_NBR,
ResNoDaysUntilCheckout = SqlMethods.DateDiffDay(DateTime.Now, p.RS_ARRIVAL_DATE.Value).ToString(),
ResNoDaysReserved = ((int)p.RES_DAYS).ToString(),
ResDriverName = p.CUST_NAME,
Matches = t.Count == null ? "0" : t.Count.ToString()
};
switch (SortExpression)
{
case "ResLocation": q = q.OrderBy(p => p.ResLocation); break;
case "ResLocation DESC": q = q.OrderByDescending(p => p.ResLocation); break;
case "ResGroup": q = q.OrderBy(p => p.ResGroup); break;
case "ResGroup DESC": q = q.OrderByDescending(p => p.ResGroup); break;
case "ResCheckoutDate": q = q.OrderBy(p => p.ResCheckoutDate); break;
case "ResCheckoutDate DESC": q = q.OrderByDescending(p => p.ResCheckoutDate); break;
case "ResCheckinLoc": q = q.OrderBy(p => p.ResCheckinLoc); break;
case "ResCheckinLoc DESC": q = q.OrderByDescending(p => p.ResCheckinLoc); break;
case "ResNoDaysUntilCheckout": q = q.OrderBy(p => p.ResNoDaysUntilCheckout); break;
case "ResNoDaysUntilCheckout DESC": q = q.OrderByDescending(p => p.ResNoDaysUntilCheckout); break;
case "ResNoDaysReserved": q = q.OrderBy(p => p.ResNoDaysReserved); break;
case "ResNoDaysReserved DESC": q = q.OrderByDescending(p => p.ResNoDaysReserved); break;
case "ResDriverName": q = q.OrderBy(p => p.ResDriverName); break;
case "ResDriverName DESC": q = q.OrderByDescending(p => p.ResDriverName); break;
case "ResId": q = q.OrderBy(p => p.ResId); break;
case "ResId DESC": q = q.OrderByDescending(p => p.ResId); break;
case "Matches": q = q.OrderBy(p => p.Matches); break;
case "Matches DESC": q = q.OrderByDescending(p => p.Matches); break;
default: q = q.OrderBy(p => p.ResNoDaysUntilCheckout).ThenBy(p => p.ResLocation); break;
}
return(q);
}
catch (Exception ex)
{
// _logger.Error("Exception thrown in ReservationDetails Model, exception = " + ex);
throw new Exception("Exception thrown in ReservationDetails Model, exception = " + ex);
}
}
