public void CanReturnUsedCarsWithoutParameters() { CarsRepositoryADO repo = new CarsRepositoryADO(); CarsSearchParameters searchParameters = new CarsSearchParameters { IsNew = false, MinYear = null, MaxYear = null, MaxPrice = null, MinPrice = null, SearchTerm = null }; List <SearchResultItem> searchCarResults = repo.SearchCars(searchParameters).ToList(); Assert.AreEqual(5, searchCarResults.Count); Assert.AreEqual(3, searchCarResults[1].CarId); Assert.AreEqual(new DateTime(2019, 1, 1).Year.ToString(), searchCarResults[1].Year); Assert.AreEqual("Ford", searchCarResults[1].Make); Assert.AreEqual("Escape", searchCarResults[1].Model); Assert.AreEqual("White", searchCarResults[1].BodyColor); Assert.AreEqual("White", searchCarResults[1].InteriorColor); Assert.AreEqual("Automatic", searchCarResults[1].Transmission); Assert.AreEqual("/Images/2017FordEscape.png", searchCarResults[1].IMGURL); Assert.AreEqual("3ABC3ABC3ABC3ABC3", searchCarResults[1].VIN); Assert.AreEqual("1200", searchCarResults[1].Mileage); Assert.AreEqual(22669m, searchCarResults[1].SalePrice); Assert.AreEqual(24500m, searchCarResults[1].MSRP); }
public void CanReturnNewCarsWithoutParameters() { CarsRepositoryADO repo = new CarsRepositoryADO(); CarsSearchParameters searchParameters = new CarsSearchParameters { IsNew = true, MinYear = null, MaxYear = null, MaxPrice = null, MinPrice = null, SearchTerm = null }; List <SearchResultItem> searchCarResults = repo.SearchCars(searchParameters).ToList(); Assert.AreEqual(5, searchCarResults.Count); Assert.AreEqual(1, searchCarResults[1].CarId); Assert.AreEqual(new DateTime(2021, 1, 1).Year.ToString(), searchCarResults[1].Year); Assert.AreEqual("Toyota", searchCarResults[1].Make); Assert.AreEqual("Tundra LX", searchCarResults[1].Model); Assert.AreEqual("Black", searchCarResults[1].BodyColor); Assert.AreEqual("Black", searchCarResults[1].InteriorColor); Assert.AreEqual("Automatic", searchCarResults[1].Transmission); Assert.AreEqual("/Images/2017ToyotaTundra1794.jpg", searchCarResults[1].IMGURL); Assert.AreEqual("1ABC1ABC1ABC1ABC1", searchCarResults[1].VIN); Assert.AreEqual("0", searchCarResults[1].Mileage); Assert.AreEqual(50315m, searchCarResults[1].SalePrice); Assert.AreEqual(51815m, searchCarResults[1].MSRP); }
public IEnumerable <SearchResultItem> SearchCars(CarsSearchParameters parameters) { List <SearchResultItem> results = new List <SearchResultItem>(); var query = _cars.Where(c => c.IsNew == parameters.IsNew); if (parameters.MaxYear.HasValue) { query = query.Where(c => c.ModelYear <= parameters.MaxYear); } if (parameters.MinYear.HasValue) { query = query.Where(c => c.ModelYear >= parameters.MinYear); } if (parameters.MaxPrice > 0) { query = query.Where(c => c.MSRP <= parameters.MaxPrice); } if (parameters.MinPrice > 0) { query = query.Where(c => c.MSRP >= parameters.MinPrice); } foreach (var car in query) { SearchResultItem searchResultItem = new SearchResultItem() { InteriorColor = _colorRepo.GetColorById(car.InteriorColorId).ColorName, BodyColor = _colorRepo.GetColorById(car.BodyColorId).ColorName, BodyStyle = _bodyStyleRepo.GetBodyStyleById(car.BodyStyleId).BodyStyleType, Transmission = _transmissionRepo.GetTransmissionById(car.TransmissionId).TransmissionType, CarId = car.CarId, IMGURL = car.IMGFilePath, MSRP = car.MSRP, SalePrice = car.SalePrice, Make = _makeRepo.GetMakeById(car.MakeId.ToString()).MakeName, Model = _modelRepo.GetModelById(car.ModelId).ModelName, Mileage = car.Mileage, VIN = car.VIN, Year = car.ModelYear.Year.ToString() }; results.Add(searchResultItem); } if (!String.IsNullOrEmpty(parameters.SearchTerm)) { results = results.Where(c => (c.Make).ToLower().Contains(parameters.SearchTerm.ToLower()) || c.Model.ToLower().Contains(parameters.SearchTerm.ToLower()) || c.Year.Contains(parameters.SearchTerm)).Take(20).ToList(); } return(results); }
public static CarsSearchParameters CreateVehicleParameter(string searchTerm, string minYear, string maxYear, decimal minPrice, decimal maxPrice) { CarsSearchParameters parameters = new CarsSearchParameters(); if (searchTerm == "null") { parameters.SearchTerm = ""; } else { parameters.SearchTerm = searchTerm; } if (minYear == "null") { parameters.MinYear = null; } else { parameters.MinYear = DateTime.Parse(minYear + "/1/1"); } if (maxYear == "null") { parameters.MaxYear = null; } else { parameters.MaxYear = DateTime.Parse(maxYear + "/1/1"); } parameters.MinPrice = minPrice; parameters.MaxPrice = maxPrice; return(parameters); }
public IEnumerable <SearchResultItem> SearchCars(CarsSearchParameters Parameters) { List <SearchResultItem> carsSearchResults = new List <SearchResultItem>(); bool parametersChosen = false; using (var dbConnection = new SqlConnection(Settings.GetConnectionString())) { string query = "SELECT TOP 20 c.CarId AS \"CarId\", c.ModelYear AS \"ModelYear\", mk.MakeName AS \"Make\", md.ModelName AS \"Model\", c.IMGFilePath AS \"IMGURL\"," + "ic.ColorName AS \"InteriorColor\", bc.ColorName AS \"BodyColor\", t.TransmissionType AS \"Transmission\"," + "c.Mileage AS \"Mileage\", c.VIN AS \"VIN\", c.SalePrice AS \"SalePrice\", c.MSRP AS \"MSRP\"" + "FROM Cars c INNER JOIN Make mk ON mk.MakeId = c.MakeId INNER JOIN Model md ON md.MakeId = mk.MakeId INNER JOIN " + "Color bc ON c.BodyColorId = bc.ColorId INNER JOIN Color ic ON ic.ColorId = c.InteriorColorId INNER JOIN " + "Transmission t ON t.TransmissionId = c.TransmissionId WHERE 1 = 1 "; SqlCommand cmd = new SqlCommand(); cmd.Connection = dbConnection; cmd.Parameters.AddWithValue("@IsNew", Parameters.IsNew); if (Parameters.MinYear.HasValue) { query += "AND ModelYear >= @MinYear AND IsNew = @IsNew "; cmd.Parameters.AddWithValue("@MinYear", Parameters.MinYear.Value); parametersChosen = true; } if (Parameters.MaxYear.HasValue) { query += "AND ModelYear <= @MaxYear AND IsNew = @IsNew "; cmd.Parameters.AddWithValue("@MaxYear", Parameters.MaxYear.Value); parametersChosen = true; } if (Parameters.MinPrice.HasValue) { query += "AND SalePrice >= @MinPrice AND IsNew = @IsNew "; cmd.Parameters.AddWithValue("@MinPrice", Parameters.MinPrice.Value); parametersChosen = true; } if (Parameters.MaxPrice.HasValue) { query += "AND SalePrice <= @MaxPrice AND IsNew = @IsNew "; cmd.Parameters.AddWithValue("@MaxPrice", Parameters.MaxPrice.Value); parametersChosen = true; } if (!string.IsNullOrEmpty(Parameters.SearchTerm)) { query += "AND (MakeName LIKE @SearchTerm AND IsNew = @IsNew) OR (ModelName LIKE @SearchTerm AND IsNew = @IsNew) OR (ModelYear LIKE @SearchTerm AND IsNew = @IsNew) "; cmd.Parameters.AddWithValue("@SearchTerm", Parameters.SearchTerm + '%'); parametersChosen = true; } if (parametersChosen) { query += "GROUP BY CarId, MakeName, ModelName, ModelYear, IMGFilePath, bc.ColorName, ic.ColorName, TransmissionType, " + "Mileage, VIN, SalePrice, MSRP Order by ModelYear "; cmd.CommandText = query; } else { query += "AND IsNew = @IsNew GROUP BY CarId, MakeName, ModelName, ModelYear, IMGFilePath, bc.ColorName, ic.ColorName, TransmissionType, " + "Mileage, VIN, SalePrice, MSRP ORDER BY MSRP DESC "; cmd.CommandText = query; } dbConnection.Open(); try { using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { SearchResultItem carSearchResult = new SearchResultItem(); carSearchResult.CarId = (int)dr["CarId"]; carSearchResult.Year = (dr["ModelYear"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["ModelYear"])); carSearchResult.Make = dr["Make"].ToString(); carSearchResult.Model = dr["Model"].ToString(); carSearchResult.IMGURL = dr["IMGURL"].ToString(); carSearchResult.InteriorColor = dr["InteriorColor"].ToString(); carSearchResult.BodyColor = dr["BodyColor"].ToString(); carSearchResult.Transmission = dr["Transmission"].ToString(); carSearchResult.Mileage = dr["Mileage"].ToString(); carSearchResult.VIN = dr["VIN"].ToString(); carSearchResult.SalePrice = (decimal)dr["SalePrice"]; carSearchResult.MSRP = (decimal)dr["MSRP"]; carsSearchResults.Add(carSearchResult); } } } catch (Exception ex) { string errorMessage = String.Format(CultureInfo.CurrentCulture, "Exception Type: {0}, Message: {1}{2}", ex.GetType(), ex.Message, ex.InnerException == null ? String.Empty : String.Format(CultureInfo.CurrentCulture, " InnerException Type: {0}, Message: {1}", ex.InnerException.GetType(), ex.InnerException.Message)); System.Diagnostics.Debug.WriteLine(errorMessage); dbConnection.Close(); } } return(carsSearchResults); }
public void CanSearchForUsedCarSearchTerm() { CarsRepositoryADO repo = new CarsRepositoryADO(); CarsSearchParameters searchParametersMake = new CarsSearchParameters { IsNew = false, MinYear = null, MaxYear = null, MaxPrice = null, MinPrice = null, SearchTerm = "For" }; CarsSearchParameters searchParametersModel = new CarsSearchParameters { IsNew = false, MinYear = null, MaxYear = null, MaxPrice = null, MinPrice = null, SearchTerm = "Esc" }; CarsSearchParameters searchParametersYear = new CarsSearchParameters { IsNew = false, MinYear = null, MaxYear = null, MaxPrice = null, MinPrice = null, SearchTerm = "2017" }; List <SearchResultItem> searchCarResults = repo.SearchCars(searchParametersMake).ToList(); Assert.AreEqual(1, searchCarResults.Count); Assert.AreEqual(3, searchCarResults[0].CarId); Assert.AreEqual(new DateTime(2019, 1, 1).Year.ToString(), searchCarResults[0].Year); Assert.AreEqual("Ford", searchCarResults[0].Make); Assert.AreEqual("Escape", searchCarResults[0].Model); Assert.AreEqual("White", searchCarResults[0].BodyColor); Assert.AreEqual("White", searchCarResults[0].InteriorColor); Assert.AreEqual("Automatic", searchCarResults[0].Transmission); Assert.AreEqual("/Images/2017FordEscape.png", searchCarResults[0].IMGURL); Assert.AreEqual("3ABC3ABC3ABC3ABC3", searchCarResults[0].VIN); Assert.AreEqual("1200", searchCarResults[0].Mileage); Assert.AreEqual(22669m, searchCarResults[0].SalePrice); Assert.AreEqual(24500m, searchCarResults[0].MSRP); searchCarResults.Clear(); searchCarResults = repo.SearchCars(searchParametersModel).ToList(); Assert.AreEqual(1, searchCarResults.Count); Assert.AreEqual(3, searchCarResults[0].CarId); Assert.AreEqual(new DateTime(2019, 1, 1).Year.ToString(), searchCarResults[0].Year); Assert.AreEqual("Ford", searchCarResults[0].Make); Assert.AreEqual("Escape", searchCarResults[0].Model); Assert.AreEqual("White", searchCarResults[0].BodyColor); Assert.AreEqual("White", searchCarResults[0].InteriorColor); Assert.AreEqual("Automatic", searchCarResults[0].Transmission); Assert.AreEqual("/Images/2017FordEscape.png", searchCarResults[0].IMGURL); Assert.AreEqual("3ABC3ABC3ABC3ABC3", searchCarResults[0].VIN); Assert.AreEqual("1200", searchCarResults[0].Mileage); Assert.AreEqual(22669m, searchCarResults[0].SalePrice); Assert.AreEqual(24500m, searchCarResults[0].MSRP); searchCarResults.Clear(); searchCarResults = repo.SearchCars(searchParametersYear).ToList(); Assert.AreEqual(3, searchCarResults.Count); Assert.AreEqual(6, searchCarResults[1].CarId); Assert.AreEqual(new DateTime(2017, 1, 1).Year.ToString(), searchCarResults[1].Year); Assert.AreEqual("Mock", searchCarResults[1].Make); Assert.AreEqual("Vehicle", searchCarResults[1].Model); Assert.AreEqual("White", searchCarResults[1].BodyColor); Assert.AreEqual("White", searchCarResults[1].InteriorColor); Assert.AreEqual("Automatic", searchCarResults[1].Transmission); Assert.AreEqual("/Images/MockNewCarPhoto.png", searchCarResults[1].IMGURL); Assert.AreEqual("6ABC6ABC6ABC6ABC6", searchCarResults[1].VIN); Assert.AreEqual("0", searchCarResults[1].Mileage); Assert.AreEqual(20000m, searchCarResults[1].SalePrice); Assert.AreEqual(20000m, searchCarResults[1].MSRP); }