public IHttpActionResult Search(decimal?minPrice, decimal?maxPrice, decimal?minYear, decimal?maxYear, string makeName = null, string modelName = null, string year = null) { var repo = new VehicleRepositoryADO(); try { var parameters = new VehicleSearchParams() { MinPrice = minPrice, MaxPrice = maxPrice, MakeName = makeName, ModelName = modelName, Year = year, MinYear = minYear, MaxYear = maxYear }; var result = repo.Search(parameters); return(Ok(result)); } catch (Exception ex) { return(BadRequest(ex.Message)); } }
public Task <List <Vehicle> > List(VehicleSearchParams searchParams) { var taskResult = Task.Run(() => { using (var context = new DbContext()) { var ctx = (from msf in context.MapsSocietiesToFacilities join f in context.Facilities on msf.FacilityId equals f.Id join flr in context.Floors on f.Id equals flr.FacilityId join fls in context.Flats on flr.Id equals fls.FloorId join mffo in context.MapsFlatToFlatOwner on fls.Id equals mffo.FlatId join fo in context.FlatOwners on mffo.FlatOwnerId equals fo.Id join v in context.Vehicles on new { k1 = fo.Id, k2 = fls.Id } equals new { k1 = v.FlatOwnerId, k2 = v.FlatId } into result from r in result.DefaultIfEmpty() where msf.SocietyId == searchParams.SocietyId && r != null select r); if (searchParams.FlatId.HasValue) { ctx = ctx.Where(b => b.FlatId == searchParams.FlatId.Value); } if (searchParams.FlatOwnerId.HasValue) { ctx = ctx.Where(b => b.FlatOwnerId == searchParams.FlatOwnerId.Value); } return(ctx .Include(s => s.FlatOwner) .Include(f => f.FlatOwner.Gender) .Include(s => s.Flat) .Include(s => s.Approvals) .Include(s => s.Comments) .ToList()); } }); return(taskResult); }
public Task <List <Vehicle> > List(VehicleSearchParams searchParams) { return(_vehicleRepository.List(searchParams)); }
public IEnumerable <SearchDisplay> Search(VehicleSearchParams parameters) { List <SearchDisplay> vehicles = new List <SearchDisplay>(); using (var cn = new SqlConnection(Settings.GetConnectionString())) { string query = "SELECT VehicleID, VIN, Vehicle.ModelID, Model.MakeID, Make.MakeName, Model.ModelName, " + "Vehicle.ConditionID, ConditionName, Year, Vehicle.VehicleTypeID, VehicleType.VehicleTypeName, " + "Vehicle.TransmissionTypeID, TransmissionTypeName, Vehicle.CarColor, Color.ColorName AS CarColorName, " + "Vehicle.Interior, (SELECT ColorName FROM Color WHERE Vehicle.Interior = Color.ColorID) AS InteriorColorName, " + "Mileage, MSRP, SalesPrice, Picture, Featured " + "FROM Vehicle " + "INNER JOIN Model ON Vehicle.ModelID = Model.ModelID " + "INNER JOIN Make ON Model.MakeID = Make.MakeID " + "INNER JOIN Condition ON Vehicle.ConditionID = Condition.ConditionID " + "INNER JOIN VehicleType ON Vehicle.VehicleTypeID = VehicleType.VehicleTypeID " + "INNER JOIN TransmissionType ON Vehicle.TransmissionTypeID = TransmissionType.TransmissionTypeID " + "INNER JOIN Color ON Vehicle.CarColor = Color.ColorID " + "WHERE 1 = 1 "; SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; if (parameters.MinPrice.HasValue) { query += "AND SalesPrice >= @MinPrice "; cmd.Parameters.AddWithValue("@MinPrice", parameters.MinPrice.Value); } if (parameters.MaxPrice.HasValue) { query += "AND SalesPrice <= @MaxPrice "; cmd.Parameters.AddWithValue("@MaxPrice", parameters.MaxPrice.Value); } if (parameters.MinYear.HasValue) { query += "AND Year >= @MinYear "; cmd.Parameters.AddWithValue("@MinYear", parameters.MinYear.Value); } if (parameters.MaxYear.HasValue) { query += "AND Year <= @MaxYear "; cmd.Parameters.AddWithValue("@MaxYear", parameters.MaxYear.Value); } if (!string.IsNullOrEmpty(parameters.ModelName)) { query += "AND ModelName LIKE @ModelName "; cmd.Parameters.AddWithValue("@ModelName", '%' + parameters.ModelName + '%'); } if (!string.IsNullOrEmpty(parameters.MakeName)) { query += "AND MakeName LIKE @MakeName "; cmd.Parameters.AddWithValue("@MakeName", '%' + parameters.MakeName + '%'); } if (!string.IsNullOrEmpty(parameters.Year)) { query += "AND Year LIKE @Year "; cmd.Parameters.AddWithValue("@Year", '%' + parameters.Year + '%'); } cmd.CommandText = query; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { SearchDisplay row = new SearchDisplay(); row.VehicleID = (int)dr["VehicleID"]; row.VIN = dr["VIN"].ToString(); row.ModelID = (int)dr["ModelID"]; row.ModelName = dr["ModelName"].ToString(); row.MakeName = dr["MakeName"].ToString(); row.ConditionID = (int)dr["ConditionID"]; row.ConditionName = dr["ConditionName"].ToString(); row.VehicleTypeID = (int)dr["VehicleTypeID"]; row.VehicleTypeName = dr["VehicleTypeName"].ToString(); row.Year = dr["Year"].ToString(); row.TransmissionTypeID = (int)dr["TransmissionTypeID"]; row.TransmissionTypeName = dr["TransmissionTypeName"].ToString(); row.CarColor = (int)dr["CarColor"]; row.CarColorName = dr["CarColorName"].ToString(); row.Interior = (int)dr["Interior"]; row.InteriorColorName = dr["InteriorColorName"].ToString(); row.Mileage = dr["Mileage"].ToString(); row.MSRP = (decimal)dr["MSRP"]; row.SalesPrice = (decimal)dr["SalesPrice"]; //row.Description = dr["Description"].ToString(); row.Picture = dr["Picture"].ToString(); //row.Featured = (bool)dr["Featured"]; vehicles.Add(row); } } } return(vehicles); }