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