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); } }