public IList <ICarSearchDataEntity> getQueryable(IFilterEntity filters, ICarFilterEntity cf, string sortExpression, string sortDirection)
{
using (MarsDBDataContext db = new MarsDBDataContext()) {
var qDetails = new VehicleDetailsQueryable().getQueryable(db);
var qFilter = new VehiclesFilterQueryable().getQueryable(filters, db, qDetails);
var qCarFilter = new CarFilterQueryable().getQueryable(cf, db, qFilter);
IQueryable <ICarSearchDataEntity> qVDFilter;
switch (filters.VehiclePredicament)
{
case 0: qVDFilter = new VehicleDetailsAllQueryable().getQueryable(filters, db, qCarFilter); break;
case 1: qVDFilter = new VehicleDetailsOROwnToForeign().getQueryable(filters, db, qCarFilter); break;
case 2: qVDFilter = new VehicleDetailsTXToForeignQueryable().getQueryable(filters, db, qCarFilter); break;
case 3: qVDFilter = new VehicleDetailsIdleQueryable().getQueryable(filters, db, qCarFilter); break;
case 4: qVDFilter = new VehicleDetailsORInBetweenQueryable().getQueryable(filters, db, qCarFilter); break;
case 5: qVDFilter = new VehicleDetailsORReturningQueryable().getQueryable(filters, db, qCarFilter); break;
case 6: qVDFilter = new VehicleDetailsTXReturningQueryable().getQueryable(filters, db, qCarFilter); break;
case 7: qVDFilter = new VehicleDetailsNonRevQueryable().getQueryable(filters, db, qCarFilter); break;
default: qVDFilter = qCarFilter; break;
}
var l1 = qVDFilter.ToList();
var l2 = new VehicleDetailsSortQueryable().getQueryable(sortExpression, sortDirection, db, l1.AsQueryable()).ToList();
return(l2);
}
}
public List <FleetMatchEntity> getNewList(IFilterEntity f, string license, string sortExpression)
{
using (MarsDBDataContext db = new MarsDBDataContext())
{
var qDetails = new VehicleDetailsQueryable().getQueryable(db);
var qFilter = new VehiclesFilterQueryable().getQueryable(f, db, qDetails);
var q1 = new VehicleDetailsIdleQueryable().getQueryable(f, db, qFilter);
var tmp = from rea in db.Reservations
join rentLoc in db.LOCATIONs on rea.RENT_LOC equals rentLoc.dim_Location_id
join rentClg in db.CMS_LOCATION_GROUPs on rentLoc.cms_location_group_id equals
rentClg.cms_location_group_id
//Return location
join returnLoc in db.LOCATIONs on rea.RTRN_LOC equals returnLoc.dim_Location_id
where rea.RS_ARRIVAL_DATE >= f.ReservationStartDate && rea.RS_ARRIVAL_DATE <= f.ReservationEndDate
group rea by
new
{
rtrnLoc = returnLoc.served_by_locn.Substring(0, 2),
resLocGrp = rentClg.cms_location_group1
}
into grp
select new { grp.Key.rtrnLoc, grp.Key.resLocGrp, c = grp.Count() };
var q = from p in q1
join loc in db.LOCATIONs on p.Lstwwd equals loc.location1
join clg in db.CMS_LOCATION_GROUPs on loc.cms_location_group_id equals clg.cms_location_group_id
join t1 in tmp on new { x1 = p.OwnCountry, x2 = clg.cms_location_group1 } equals
new { x1 = t1.rtrnLoc, x2 = t1.resLocGrp } into tj
from t in tj.DefaultIfEmpty()
where p.License == license || string.IsNullOrEmpty(license) &&
p.Overdue != 1
select new { p, count = (t.c == null ? 0 : t.c) };
switch (sortExpression)
{
case "OwnCountry":
q = q.OrderBy(p => p.p.OwnCountry);
break;
case "OwnCountry DESC":
q = q.OrderByDescending(p => p.p.OwnCountry);
break;
case "Vc":
q = q.OrderBy(p => p.p.Vc);
break;
case "Vc DESC":
q = q.OrderByDescending(p => p.p.Vc);
break;
case "Unit":
q = q.OrderBy(p => p.p.Unit);
break;
case "Unit DESC":
q = q.OrderByDescending(p => p.p.Unit);
break;
case "License":
q = q.OrderBy(p => p.p.License);
break;
case "License DESC":
q = q.OrderByDescending(p => p.p.License);
break;
case "ModelDesc":
q = q.OrderBy(p => p.p.Moddesc);
break;
case "ModelDesc DESC":
q = q.OrderByDescending(p => p.p.Moddesc);
break;
case "Location":
q = q.OrderBy(p => p.p.Lstwwd);
break;
case "Location DESC":
q = q.OrderByDescending(p => p.p.Lstwwd);
break;
case "Operstat":
q = q.OrderBy(p => p.p.Op);
break;
case "Operstat DESC":
q = q.OrderByDescending(p => p.p.Op);
break;
case "Daysrev":
q = q.OrderBy(p => p.p.Nonrev);
break;
case "Daysrev DESC":
q = q.OrderByDescending(p => p.p.Nonrev);
break;
case "Matches":
q = q.OrderBy(p => p.count);
break;
case "Matches DESC":
q = q.OrderByDescending(p => p.count);
break;
default:
q = q.OrderBy(p => p.p.Nonrev).ThenBy(p => p.p.Duewwd);
break;
}
return((from item in q
select new FleetMatchEntity
{
OwnCountry = item.p.OwnCountry,
Unit = item.p.Unit,
License = item.p.License,
ModelDesc = item.p.Moddesc,
Vc = item.p.Vc,
Operstat = item.p.Op,
Location = item.p.Lstwwd,
Daysrev = item.p.Nonrev.ToString(),
CarVan = item.p.CarVan,
Matches = item.count.ToString()
}).ToList());
}
}
public IList <IDataTableEntity> getList(IFilterEntity filters)
{
using (MarsDBDataContext db = new MarsDBDataContext()) {
IList <IDataTableEntity> l = new List <IDataTableEntity>();
var qDetails = new VehicleDetailsQueryable().getQueryable(db);
var qFilter = new VehiclesFilterQueryable().getQueryable(filters, db, qDetails);
IQueryable <ICarSearchDataEntity> qVDFilter;
switch (filters.VehiclePredicament)
{
case 0: qVDFilter = new VehicleDetailsAllQueryable().getQueryable(filters, db, qFilter); break;
case 1: qVDFilter = new VehicleDetailsOROwnToForeign().getQueryable(filters, db, qFilter); break;
case 2: qVDFilter = new VehicleDetailsTXToForeignQueryable().getQueryable(filters, db, qFilter); break;
case 3: qVDFilter = new VehicleDetailsIdleQueryable().getQueryable(filters, db, qFilter); break;
case 4: qVDFilter = new VehicleDetailsORInBetweenQueryable().getQueryable(filters, db, qFilter); break;
case 5: qVDFilter = new VehicleDetailsORReturningQueryable().getQueryable(filters, db, qFilter); break;
case 6: qVDFilter = new VehicleDetailsTXReturningQueryable().getQueryable(filters, db, qFilter); break;
case 7: qVDFilter = new VehicleDetailsNonRevQueryable().getQueryable(filters, db, qFilter); break;
default: qVDFilter = qFilter; break;
}
IQueryable <IVehiclePredicamentEntity> q1 = from p in qVDFilter
join own in db.COUNTRies on p.OwnCountry equals own.country1
join due in db.COUNTRies on p.Duewwd.Substring(0, 2) equals due.country1 into dj
from what in dj.DefaultIfEmpty()
join lst in db.COUNTRies on p.Lstwwd.Substring(0, 2) equals lst.country1
select new VehiclePredicamentEntity
{
ownwwd = own.country_description, duewwd = what.country_description, lstwwd = lst.country_description
};
IQueryable <IVehiclePredicamentEntity> q2;
switch (filters.VehiclePredicament)
{
case 1:
case 2:
case 3:
case 4:
q2 = from p in q1
select new VehiclePredicamentEntity {
ownwwd = p.ownwwd, duewwd = (p.duewwd == null || p.duewwd == "") ? p.lstwwd : p.duewwd
};
break;
default:
q2 = from p in q1
select new VehiclePredicamentEntity {
ownwwd = p.ownwwd, duewwd = p.lstwwd
}; break;
}
var grp = (from p in q2
group p by new { o = p.ownwwd, d = p.duewwd } into g
select new { ownwwd = g.Key.o, duewwd = g.Key.d, sum = g.Key.o.Count() })
.OrderBy(p => p.duewwd).ThenBy(p => p.ownwwd);
foreach (var item in grp)
{
l.Add(new DataTableEntity {
header = item.ownwwd, rowDefinition = item.duewwd, theValue = item.sum.ToString()
});
}
return(l);
}
}
