public DayActualRepository(IJavaScriptRepository j, Enums.DayActualTime Time)
{
JavascriptRepository = j;
_time = Time;
_feq = new FeaPoolingDataAccess();
_resCarFilterQ = new ReservationsFilterCar();
_resFilterQ = new ReservationsSiteFilter();
_reqCi = new ResActualCIQueryable();
_reqCo = new PoolingCheckOutReservations();
_feaFilterQ = new FeaFilteredQueryable();
getFeaData = GetFeaData;
getPoolingCIData = GetPoolingCIData;
getPoolingCOData = GetPoolingCOData;
}
IList <DayActualEntity> GetFeaData(Enums.DayActualTime tme)
{
using (var db = new MarsDBDataContext())
{
IList <DayActualEntity> l = new List <DayActualEntity>();
try
{
l = _feaFleetQ.GetQueryable(db, Filter, tme).OrderBy(p => p.Label).ToList();
}
catch (SqlException ex)
{
// ILog _logger = log4net.LogManager.GetLogger("Pooling");
// if (_logger != null) _logger.Error(" SQL Exception thrown in FleetComparisonRepository accessing FLEET_EUROPE_ACTUAL table, message : " + ex.Message);
}
return(l);
}
}
IList <DayActualEntity> GetPoolingCOData(Enums.DayActualTime tme)
{
using (PoolingDataClassesDataContext db = new PoolingDataClassesDataContext())
{
IList <DayActualEntity> l = new List <DayActualEntity>();
try
{
IQueryable <App.Classes.DAL.Pooling.PoolingDataContext.Reservation> q2And3 = _resCarFilterQ.FilterByCarParameters(db, Filter, false);
q2And3 = _resFilterQ.FilterByRentalLocation(q2And3, Filter);
l = _resFleetCOQ.GetQueryable(q2And3, Filter, tme).ToList();
}
catch (SqlException ex)
{
// ILog _logger = log4net.LogManager.GetLogger("Pooling");
// if (_logger != null) _logger.Error(" SQL Exception thrown in SiteComparisonRepository accessing Reservations table, message : " + ex.Message);
}
return(l);
}
}
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 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> GetQueryableCO(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.PoolRegion) ? filter.CmsLogic ? p.LOCATION1.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1 : p.LOCATION1.OPS_AREA.OPS_REGION.ops_region1 :
String.IsNullOrEmpty(filter.LocationGrpArea) ? filter.CmsLogic ? p.LOCATION1.CMS_LOCATION_GROUP.cms_location_group1 : p.LOCATION1.OPS_AREA.ops_area1 :
p.LOCATION1.served_by_locn),
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 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 IQueryable <DayActualEntity> GetFeaDataWithLabels(IQueryable <FLEET_EUROPE_ACTUAL> availableData
, IQueryable <FLEET_EUROPE_ACTUAL> checkInData
, Enums.DayActualTime time, bool siteQ
, IMainFilterEntity filter, MarsDBDataContext db)
{
IQueryable <DayActualEntity> availableEntities;
IQueryable <DayActualEntity> checkInEntities;
IQueryable <DayActualEntity> offsetEntities;
IQueryable <DayActualEntity> openTripsEntities;
var now = DateTime.Now.GetDateAndHourOnlyByCountry(filter.Country);
if (siteQ)
{
availableEntities = from p in availableData
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.RT == 1
group p by(String.IsNullOrEmpty(filter.Country)
?loc.COUNTRy1.country_description
: String.IsNullOrEmpty(filter.PoolRegion)
?filter.CmsLogic
?loc.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1
: loc.OPS_AREA.OPS_REGION.ops_region1
: String.IsNullOrEmpty(filter.LocationGrpArea)
?filter.CmsLogic
?loc.CMS_LOCATION_GROUP.cms_location_group1
: loc.OPS_AREA.ops_area1
: loc.served_by_locn)
into g
select new DayActualEntity
{
Tme = 0,
Label = g.Key,
Available = g.Sum(d => d.TOTAL_FLEET.HasValue ? d.TOTAL_FLEET.Value : 0),
Checkin = 0,
OnewayCheckin = 0,
LocalCheckIn = 0,
Opentrips = 0,
Offset = 0
};
openTripsEntities = from p in availableData
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
(time == Enums.DayActualTime.THREE ?
(SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.DUEDATE.Value) <= 29)) &&
p.RT == 1
group p by new
{
t1 = (String.IsNullOrEmpty(filter.Country)
? loc.COUNTRy1.country_description
: String.IsNullOrEmpty(filter.PoolRegion)
? filter.CmsLogic
? loc.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1
: loc.OPS_AREA.OPS_REGION.ops_region1
: String.IsNullOrEmpty(filter.LocationGrpArea)
? filter.CmsLogic
? loc.CMS_LOCATION_GROUP.cms_location_group1
: loc.OPS_AREA.ops_area1
: loc.served_by_locn),
t2 = (time == Enums.DayActualTime.THREE
? SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour))
: SqlMethods.DateDiffDay(now, p.DUEDATE.Value))
}
into g
select new DayActualEntity
{
Tme = g.Key.t2,
Label = g.Key.t1,
Available = 0,
Checkin = 0,
OnewayCheckin = 0,
LocalCheckIn = 0,
Opentrips = g.Sum(p => (p.MOVETYPE.ToUpper() == "T-O" ||
p.MOVETYPE.ToUpper() == "L-O") &&
p.TOTAL_FLEET.HasValue ? p.TOTAL_FLEET.Value : 0),
Offset = 0
};
checkInEntities = from p in checkInData
join loc in db.LOCATIONs on p.DUEWWD 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
(time == Enums.DayActualTime.THREE ?
(SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.DUEDATE.Value) <= 29))
group p by new
{
t1 = (String.IsNullOrEmpty(filter.Country)
? loc.COUNTRy1.country_description
: String.IsNullOrEmpty(filter.PoolRegion)
? filter.CmsLogic
? loc.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1
: loc.OPS_AREA.OPS_REGION.ops_region1
: String.IsNullOrEmpty(filter.LocationGrpArea)
? filter.CmsLogic
? loc.CMS_LOCATION_GROUP.cms_location_group1
: loc.OPS_AREA.ops_area1
: loc.served_by_locn),
t2 = (time == Enums.DayActualTime.THREE
? SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour))
: SqlMethods.DateDiffDay(now, p.DUEDATE.Value))
}
into g
select new DayActualEntity
{
Tme = g.Key.t2,
Label = g.Key.t1,
Available = 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,
Opentrips = 0,
Offset = 0
};
offsetEntities = from p in checkInData
join loc in db.LOCATIONs on p.DUEWWD equals loc.served_by_locn
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
(time == Enums.DayActualTime.THREE ?
(SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.DUEDATE.Value) <= 29)) &&
loc.location1 == loc.served_by_locn
group p by new
{
t1 = (String.IsNullOrEmpty(filter.Country)
? loc.COUNTRy1.country_description
: String.IsNullOrEmpty(filter.PoolRegion)
? filter.CmsLogic
? loc.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1
: loc.OPS_AREA.OPS_REGION.ops_region1
: String.IsNullOrEmpty(filter.LocationGrpArea)
? filter.CmsLogic
? loc.CMS_LOCATION_GROUP.cms_location_group1
: loc.OPS_AREA.ops_area1
: loc.served_by_locn),
t2 = (time == Enums.DayActualTime.THREE
? SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)
.AddHours(loc.turnaround_hours.HasValue ? loc.turnaround_hours.Value : 0))
: SqlMethods.DateDiffDay(now, p.DUEDATE.Value.AddHours(loc.turnaround_hours.HasValue ? loc.turnaround_hours.Value : 0))
)
}
into g
select new DayActualEntity
{
Tme = g.Key.t2,
Label = g.Key.t1,
Available = 0,
Checkin = 0,
OnewayCheckin = 0,
LocalCheckIn = 0,
Opentrips = 0,
Offset = g.Sum(p => p.TOTAL_FLEET) ?? 0
};
}
else
{ // Fleet Comparison Query
availableEntities = from p in availableData
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.RT == 1
group p by(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)
into g
select new DayActualEntity
{
Tme = 0,
Label = g.Key,
Available = g.Sum(d => d.TOTAL_FLEET.HasValue ? d.TOTAL_FLEET.Value : 0),
Checkin = 0,
OnewayCheckin = 0,
LocalCheckIn = 0,
Opentrips = 0,
Offset = 0
};
openTripsEntities = from p in availableData
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
(time == Enums.DayActualTime.THREE ?
(SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.DUEDATE.Value) <= 29)) &&
p.RT == 1
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 = (time == Enums.DayActualTime.THREE
? SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour))
: SqlMethods.DateDiffDay(now, p.DUEDATE.Value))
}
into g
select new DayActualEntity
{
Tme = g.Key.t2,
Label = g.Key.t1,
Available = 0,
Checkin = 0,
OnewayCheckin = 0,
LocalCheckIn = 0,
Opentrips = g.Sum(p => (p.MOVETYPE.ToUpper() == "T-O" ||
p.MOVETYPE.ToUpper() == "L-O") &&
p.TOTAL_FLEET.HasValue ? p.TOTAL_FLEET.Value : 0),
Offset = 0
};
checkInEntities = from p in checkInData
join loc in db.LOCATIONs on p.DUEWWD 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
(time == Enums.DayActualTime.THREE ?
(SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.DUEDATE.Value) <= 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 = (time == Enums.DayActualTime.THREE
? SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour))
: SqlMethods.DateDiffDay(now, p.DUEDATE.Value))
}
into g
select new DayActualEntity
{
Tme = g.Key.t2,
Label = g.Key.t1,
Available = 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,
Opentrips = 0,
Offset = 0
};
offsetEntities = from p in checkInData
//join c in db.COUNTRies on p.COUNTRY equals c.country1
join loc in db.LOCATIONs on p.DUEWWD equals loc.served_by_locn
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
(time == Enums.DayActualTime.THREE ?
(SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.DUEDATE.Value) <= 29)) &&
loc.location1 == loc.served_by_locn
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 = (time == Enums.DayActualTime.THREE
? SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)
.AddHours(loc.turnaround_hours.HasValue ? loc.turnaround_hours.Value : 0))
: SqlMethods.DateDiffDay(now, p.DUEDATE.Value.AddHours(loc.turnaround_hours.HasValue ? loc.turnaround_hours.Value : 0))
)
}
into g
select new DayActualEntity
{
Tme = g.Key.t2,
Label = g.Key.t1,
Available = 0,
Checkin = 0,
OnewayCheckin = 0,
LocalCheckIn = 0,
Opentrips = 0,
Offset = g.Sum(p => p.TOTAL_FLEET) ?? 0
};
}
var combinedData = from cd in availableEntities.Union(checkInEntities).Union(openTripsEntities).Union(offsetEntities)
group cd by new { cd.Tme, cd.Label }
into gd
select new DayActualEntity
{
Tme = gd.Key.Tme,
Label = gd.Key.Label,
Available = gd.Sum(d => d.Available),
Opentrips = gd.Sum(d => d.Opentrips),
Checkin = gd.Sum(d => d.Checkin),
OnewayCheckin = gd.Sum(d => d.OnewayCheckin),
LocalCheckIn = gd.Sum(d => d.LocalCheckIn),
Offset = gd.Sum(d => d.Offset)
};
return(combinedData);
}
public IQueryable <DayActualEntity> GetFeaDataWithoutLabels(IQueryable <FLEET_EUROPE_ACTUAL> availableData
, IQueryable <FLEET_EUROPE_ACTUAL> checkInData
, Enums.DayActualTime time
, IMainFilterEntity mfe
, MarsDBDataContext db)
{
var availableEntity = from p in availableData
where p.RT == 1
group p by 0
into g
select new DayActualEntity
{
Tme = 0,
Available = g.Sum(d => d.TOTAL_FLEET.HasValue ? d.TOTAL_FLEET.Value : 0),
Checkin = 0,
OnewayCheckin = 0,
LocalCheckIn = 0,
Opentrips = 0,
Offset = 0
};
var now = DateTime.Now.GetDateAndHourOnlyByCountry(mfe.Country);
var openTripsEntity = from p in availableData
where (time == Enums.DayActualTime.THREE ?
(SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.DUEDATE.Value) <= 29)) &&
p.RT == 1
group p by(time == Enums.DayActualTime.THREE
?SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour))
: SqlMethods.DateDiffDay(now, p.DUEDATE.Value)
)
into g
select new DayActualEntity
{
Tme = g.Key,
Available = 0,
Checkin = 0,
OnewayCheckin = 0,
LocalCheckIn = 0,
Opentrips = g.Sum(d => (d.MOVETYPE.ToUpper() == "T-O" ||
d.MOVETYPE.ToUpper() == "L-O"
) &&
d.TOTAL_FLEET.HasValue ? d.TOTAL_FLEET.Value : 0),
Offset = 0
};
var checkInEntities = from p in checkInData
join loc in db.LOCATIONs on p.DUEWWD 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 (time == Enums.DayActualTime.THREE?
(SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.DUEDATE.Value) <= 29))
group p by(time == Enums.DayActualTime.THREE
?SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour))
: SqlMethods.DateDiffDay(now, p.DUEDATE.Value)
)
into g
select new DayActualEntity
{
Tme = g.Key,
Available = 0,
Checkin = g.Sum(p => p.TOTAL_FLEET) ?? 0,
OnewayCheckin = g.Sum(p => p.LSTWWD != p.DUEWWD ? p.TOTAL_FLEET : 0) ?? 0,
LocalCheckIn = g.Sum(p => p.LSTWWD == p.DUEWWD ? p.TOTAL_FLEET : 0) ?? 0,
Opentrips = 0,
Offset = 0
};
//var yy = checkInEntities.ToList();
var offsetEntities = from p in checkInData
join loc in db.LOCATIONs on p.DUEWWD equals loc.served_by_locn
where (time == Enums.DayActualTime.THREE ?
(SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) <= 71)
: (SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)) >= 0 &&
SqlMethods.DateDiffDay(now, p.DUEDATE.Value) <= 29)) &&
loc.location1 == loc.served_by_locn
group p by(time == Enums.DayActualTime.THREE
?SqlMethods.DateDiffHour(now, p.DUEDATE.Value.AddHours(p.DUETIME.Value.Hour)
.AddHours(loc.turnaround_hours.HasValue ? loc.turnaround_hours.Value : 0))
: SqlMethods.DateDiffDay(now, p.DUEDATE.Value.AddHours(loc.turnaround_hours.HasValue ? loc.turnaround_hours.Value : 0))
)
into g
select new DayActualEntity
{
Tme = g.Key,
Available = 0,
Checkin = 0,
OnewayCheckin = 0,
LocalCheckIn = 0,
Opentrips = 0,
Offset = g.Sum(p => p.TOTAL_FLEET) ?? 0
};
// var xx = offsetEntities.ToList();
var combinedData = from cd in availableEntity.Union(openTripsEntity).Union(checkInEntities).Union(offsetEntities)
group cd by cd.Tme
into gd
select new DayActualEntity
{
Tme = gd.Key,
Available = gd.Sum(d => d.Available),
Opentrips = gd.Sum(d => d.Opentrips),
Checkin = gd.Sum(d => d.Checkin),
OnewayCheckin = gd.Sum(d => d.OnewayCheckin),
LocalCheckIn = gd.Sum(d => d.LocalCheckIn),
Offset = gd.Sum(d => d.Offset)
};
return(combinedData);
}
private void SetStartAndEndDate(DateTime lastRequestDate, int timeSlotSelected, Enums.DayActualTime dayActualTime)
{
lastRequestDate = dayActualTime == Enums.DayActualTime.THIRTY
? lastRequestDate.AddDays(timeSlotSelected)
: lastRequestDate.AddHours(timeSlotSelected);
DateRangeModel.StartDateModel.Text = lastRequestDate.ToShortDateString();
DateRangeModel.EndDateModel.Text = lastRequestDate.ToShortDateString();
}
public List <DayActualEntity> GetFleetReservationsWithLabels(IQueryable <Reservation> q, IMainFilterEntity filter,
Enums.DayActualTime time, PoolingDataClassesDataContext db)
{
var now = DateTime.Now.GetDateAndHourOnlyByCountry(filter.Country);
var actualData = from p in q
//join c in db.COUNTRies on p.COUNTRY equals c.country1
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))
//&& (String.IsNullOrEmpty(filter.Country)
// || c.country_description == filter.Country)
group p by new
{
t1 = (String.IsNullOrEmpty(filter.Country) ? p.ReturnLocation.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.RTRN_DATE.Value.AddHours(p.RTRN_TIME.Value.Hour))
: SqlMethods.DateDiffDay(now, p.RTRN_DATE.Value))
}
into g
select new DayActualEntity
{
Label = g.Key.t1,
Tme = g.Key.t2,
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
//join c in db.COUNTRies on p.COUNTRY equals c.country1
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).AddHours(p.CI_HOURS_OFFSET.Value)) >= 0 &&
SqlMethods.DateDiffDay(now, p.RTRN_DATE.Value) <= 29))
//&& (String.IsNullOrEmpty(filter.Country)
// || c.country_description == filter.Country)
group p by new
{
t1 = (String.IsNullOrEmpty(filter.Country) ? p.ReturnLocation.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.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.t2,
Label = g.Key.t1,
Offset = g.Sum(p => p.RES_ID_NBR != null ? 1 : 0),
};
var combinedData = from cd in actualData.ToList().Union(offsetData.ToList())
group cd by new { cd.Tme, cd.Label }
into gd
select new DayActualEntity
{
Tme = gd.Key.Tme,
Label = gd.Key.Label,
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 IList <String[]> GetList(Enums.DayActualTime tme)
{
var hourlyColumns = tme == Enums.DayActualTime.THREE;
var returned = new List <String[]>();
var numberOfColumns = tme == Enums.DayActualTime.THIRTY ? 30 : (Int32)Enums.ThreeDayActuals.MAXNOOFCOLUMNS;
var topicData = ReservationsDataAccess.CalculateTopics(hourlyColumns, numberOfColumns, Filter, true, false);
int topicId = (from topic in _resTopics where topic.Name.Equals(Filter.Topic) select topic.Id).Single();
foreach (string lbl in topicData.Select(p => p.Label).Distinct())
{
var s = new String[NoInArray];
s[0] = lbl;
for (int i = 0; i < NoInArray - 1; i++)
{
var data = topicData.FirstOrDefault(d => d.Tme == i && d.Label == lbl);
int val = 0;
if (data != null)
{
switch (topicId)
{
case 1: val = data.Available; break;
case 2: val = data.Opentrips; break;
case 3: val = data.Reservations; break;
case 4: val = data.OnewayRes; break;
case 5: val = data.GoldServiceReservations; break;
case 6: val = data.PrepaidReservations; break;
//case 7: val = e3 == null ? 0 : e3.Predelivery; break;
case 8: val = data.Checkin; break;
case 9: val = data.OnewayCheckin; break;
case 10: val = data.LocalCheckIn; break;
case 11: val = data.Balance; break;
case 12: val = data.JustAdditions; break;
case 13: val = data.JustDeletions; break;
}
}
s[i + 1] = val.ToString(CultureInfo.InvariantCulture);
}
returned.Add(s);
}
var totals = new string[NoInArray];
for (int i = 1; i < NoInArray; i++)
{
totals[i] = returned.Sum(p => Convert.ToDecimal(p.ElementAt(i))).ToString();
}
totals[0] = "Totals";
returned.Add(totals);
return(returned);
}
public List <DayActualEntity> GetAdditionDeletionData(Enums.DayActualTime tme)
{
List <DayActualEntity> returned;
using (var db = new MarsDBDataContext())
{
var additions = from ad in db.ResAdditions
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)) &&
ad.RepDate > DateTime.Now
group ad by new
{
k1 = (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),
k2 = (tme == Enums.DayActualTime.THREE ? SqlMethods.DateDiffHour(DateTime.Now, ad.RepDate)
: SqlMethods.DateDiffDay(DateTime.Now, ad.RepDate))
}
into g
select new DayActualEntity
{
Tme = g.Key.k2,
Label = g.Key.k1,
AddditionDeletion = g.Sum(d => d.Value),
JustAdditions = g.Sum(d => d.Value),
JustDeletions = 0
};
var deletions = from del in db.ResDeletions
where (del.LOCATION.COUNTRy1.active) &&
(del.LOCATION.COUNTRy1.country_description == Filter.Country || String.IsNullOrEmpty(Filter.Country)) &&
(del.LOCATION.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1 == Filter.PoolRegion ||
del.LOCATION.OPS_AREA.OPS_REGION.ops_region1 == Filter.PoolRegion || String.IsNullOrEmpty(Filter.PoolRegion)) &&
(del.LOCATION.CMS_LOCATION_GROUP.cms_location_group1 == Filter.LocationGrpArea ||
del.LOCATION.OPS_AREA.ops_area1 == Filter.LocationGrpArea || String.IsNullOrEmpty(Filter.LocationGrpArea)) &&
(del.LOCATION.location1 == Filter.Branch || String.IsNullOrEmpty(Filter.Branch)) &&
(del.CAR_GROUP.CAR_CLASS.CAR_SEGMENT.car_segment1 == Filter.CarSegment || String.IsNullOrEmpty(Filter.CarSegment)) &&
(del.CAR_GROUP.CAR_CLASS.car_class1 == Filter.CarClass || String.IsNullOrEmpty(Filter.CarClass)) &&
(del.CAR_GROUP.car_group1 == Filter.CarGroup || String.IsNullOrEmpty(Filter.CarGroup)) &&
del.RepDate > DateTime.Now
group del by new
{
k1 = (String.IsNullOrEmpty(Filter.Country) ? del.LOCATION.COUNTRy1.country_description :
String.IsNullOrEmpty(Filter.CarSegment) ? del.CAR_GROUP.CAR_CLASS.CAR_SEGMENT.car_segment1 :
String.IsNullOrEmpty(Filter.CarClass) ? del.CAR_GROUP.CAR_CLASS.car_class1 :
del.CAR_GROUP.car_group1),
k2 = (tme == Enums.DayActualTime.THREE ? SqlMethods.DateDiffHour(DateTime.Now, del.RepDate)
: SqlMethods.DateDiffDay(DateTime.Now, del.RepDate))
}
into g
select new DayActualEntity
{
Tme = g.Key.k2,
Label = g.Key.k1,
AddditionDeletion = g.Sum(d => d.Value * -1),
JustAdditions = 0,
JustDeletions = g.Sum(d => d.Value * -1)
};
var additionsAndDeletions = additions.Union(deletions).ToList();
returned = (from ad in additionsAndDeletions
group ad by new
{
k1 = ad.Tme,
k2 = ad.Label
}
into g
select new DayActualEntity
{
Tme = g.Key.k1,
Label = g.Key.k2,
AddditionDeletion = g.Sum(d => d.AddditionDeletion),
JustAdditions = g.Sum(d => d.JustAdditions),
JustDeletions = g.Sum(d => d.JustDeletions)
}).ToList();
}
return(returned);
}
public List <DayActualEntity> GetQueryableCI(IQueryable <Reservation> q, IMainFilterEntity filter
, Enums.DayActualTime time, PoolingDataClassesDataContext db)
{
var now = DateTime.Now.GetDateAndHourOnlyByCountry(filter.Country);
var actualData = from p in q
//join c in db.COUNTRies on p.COUNTRY equals c.country1
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))
//&& (String.IsNullOrEmpty(filter.Country)
// || c.country_description == filter.Country)
group p by new
{
t1 = (String.IsNullOrEmpty(filter.Country) ? p.ReturnLocation.COUNTRy1.country_description :
String.IsNullOrEmpty(filter.PoolRegion) ? filter.CmsLogic ? p.ReturnLocation.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1 : p.LOCATION.OPS_AREA.OPS_REGION.ops_region1 :
String.IsNullOrEmpty(filter.LocationGrpArea) ? filter.CmsLogic ? p.ReturnLocation.CMS_LOCATION_GROUP.cms_location_group1 : p.LOCATION.OPS_AREA.ops_area1 :
p.LOCATION.served_by_locn),
t2 = (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
{
Label = g.Key.t1,
Tme = g.Key.t2,
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
//join c in db.COUNTRies on p.COUNTRY equals c.country1
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))
//&& (String.IsNullOrEmpty(filter.Country)
// || c.country_description == filter.Country)
group p by new
{
t1 = (String.IsNullOrEmpty(filter.Country) ? p.ReturnLocation.COUNTRy1.country_description :
String.IsNullOrEmpty(filter.PoolRegion) ? filter.CmsLogic ? p.ReturnLocation.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1 : p.LOCATION.OPS_AREA.OPS_REGION.ops_region1 :
String.IsNullOrEmpty(filter.LocationGrpArea) ? filter.CmsLogic ? p.ReturnLocation.CMS_LOCATION_GROUP.cms_location_group1 : p.LOCATION.OPS_AREA.ops_area1 :
p.LOCATION.served_by_locn),
t2 = (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.t2,
Label = g.Key.t1,
Offset = g.Sum(p => p.RES_ID_NBR != null ? 1 : 0),
};
var combinedData = from cd in actualData.ToList().Union(offsetData.ToList())
group cd by new { cd.Tme, cd.Label }
into gd
select new DayActualEntity
{
Tme = gd.Key.Tme,
Label = gd.Key.Label,
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 IQueryable <DayActualEntity> GetReservationsWithoutLabels(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(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
{
Tme = g.Key,
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),
});
}
