public IHttpActionResult SearchAdminInventory(decimal?minPrice, decimal?maxPrice, int?carYear, int?minYear,
int?maxYear, string carMakeName, string carModelName)
{
var repo = CarRepositoryFactory.GetRepository();
try
{
var parameters = new CarSearchParameters()
{
MinPrice = minPrice,
MaxPrice = maxPrice,
CarYear = carYear,
MinYear = minYear,
MaxYear = maxYear,
CarMakeName = carMakeName,
CarModelName = carModelName
};
var result = repo.SearchAll(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public void CanLoadSearchNoMinMax()
{
var repo = new SearchResultsRepositoryADO();
CarSearchParameters param = new CarSearchParameters();
List <SearchResult> searches = repo.Search(param).ToList();
Assert.AreEqual(20, searches.Count());
}
public void CanLoadMaxYear()
{
var repo = new SearchResultsRepositoryADO();
CarSearchParameters param = new CarSearchParameters();
param.MaxYear = 2015;
List <SearchResult> searches = repo.Search(param).ToList();
Assert.AreEqual(3, searches.Count());
}
public void CanLoadMaxNoMinPrice()
{
var repo = new SearchResultsRepositoryADO();
CarSearchParameters param = new CarSearchParameters();
param.MaxPrice = 10000M;
List <SearchResult> searches = repo.Search(param).ToList();
Assert.AreEqual(1, searches.Count());
Assert.AreEqual("Model2", searches[0].ModelName);
}
public void CanLoadMinNoMaxPrice()
{
var repo = new SearchResultsRepositoryADO();
CarSearchParameters param = new CarSearchParameters();
param.MinPrice = 10000M;
param.SearchTerm = "s";
List <SearchResult> searches = repo.Search(param).ToList();
Assert.AreEqual("Chrysler", searches[0].MakeName);
Assert.AreEqual(12, searches.Count());
}
public IEnumerable <SearchItem> Search(CarSearchParameters parameters)
{
throw new NotImplementedException();
/*
* List<SearchItem> cars = new List<SearchItem>();
*
* if (parameters.MagicTerm != null)
* {
*
* }
*
* //cars = _cars.Where(m => m.MfgYear == parameters.MagicTerm)
*/
}
public IHttpActionResult SearchAdmin(string magicTerm, decimal?maxPrice, decimal?minPrice, string minYear, string maxYear)
{
var repo = GuildCarsRepositoryFactory.GetRepository();
try
{
var parameters = new CarSearchParameters
{
MagicTerm = magicTerm,
MaxPrice = maxPrice,
MinPrice = minPrice,
MinMfgYear = minYear,
MaxMfgYear = maxYear
};
var result = repo.SearchSales(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IHttpActionResult SearchUsed(decimal?MinPrice, decimal?MaxPrice, int?MinYear, int?MaxYear, string SearchTerm)
{
var repo = SearchRepositoryFactory.GetRepository();
try
{
var param = new CarSearchParameters();
param.MinPrice = MinPrice;
param.MaxPrice = MaxPrice;
param.MinYear = MinYear;
param.MaxYear = MaxYear;
param.SearchTerm = SearchTerm;
var result = repo.Search(param).Where(c => c.CarTypeName == "Used");
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <SearchResult> Search(CarSearchParameters parameters)
{
List <SearchResult> cars = new List <SearchResult>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
string query = "SELECT TOP 20 CarId, ma.Make, mo.Model, ct.CarType, bs.BodyStyle, t.Transmission, " +
"cc.CarColor, i.InteriorColor, IsSold, Mileage, CarYear, VIN, MSRP, SalePrice, PicturePNG " +
"FROM Cars c" +
" INNER JOIN MakeModel mm ON c.MakeModelId = mm.MakeModelId"+
" INNER JOIN Makes ma ON mm.MakeId = ma.MakeId" +
" INNER JOIN Models mo ON mm.ModelId = mo.ModelId" +
" INNER JOIN BodyStyles bs ON c.BodyStyleId = bs.BodyStyleId" +
" INNER JOIN Transmissions t ON c.TransmissionId = t.TransmissionId" +
" INNER JOIN CarColors cc ON c.CarColorId = cc.CarColorId" +
" INNER JOIN InteriorColors i ON c.InteriorColorId = i.InteriorColorId" +
" INNER JOIN CarTypes ct ON c.CarTypeId = ct.CarTypeId " +
"WHERE 1 = 1";
if (parameters.MinPrice.HasValue)
{
query += " AND SalePrice >= @MinPrice";
cmd.Parameters.AddWithValue("@MinPrice", parameters.MinPrice.Value);
}
if (parameters.MaxPrice.HasValue)
{
query += " AND SalePrice <= @MaxPrice";
cmd.Parameters.AddWithValue("@MaxPrice", parameters.MaxPrice.Value);
}
if (parameters.MinYear.HasValue)
{
query += " AND CarYear >= @MinYear";
cmd.Parameters.AddWithValue("@MinYear", parameters.MinYear.Value);
}
if (parameters.MaxYear.HasValue)
{
query += " AND CarYear <= @MaxYear";
cmd.Parameters.AddWithValue("@MaxYear", parameters.MaxYear.Value);
}
if (!string.IsNullOrEmpty(parameters.SearchTerm))
{
query += " AND (CarYear LIKE '%' + @String + '%' OR Make LIKE '%' + @String + '%' OR Model LIKE '%' + @String + '%')";
cmd.Parameters.AddWithValue("@String", parameters.SearchTerm);
}
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
SearchResult car = new SearchResult();
car.CarId = (int)dr["CarId"];
car.MakeName = dr["Make"].ToString();
car.ModelName = dr["Model"].ToString();
car.CarTypeName = dr["CarType"].ToString();
car.Mileage = (int)dr["Mileage"];
car.IsSold = (bool)dr["IsSold"];
car.BodyStyleName = dr["BodyStyle"].ToString();
car.TransmissionName = dr["Transmission"].ToString();
car.CarColorName = dr["CarColor"].ToString();
car.CarYear = (int)dr["CarYear"];
car.VIN = (int)dr["VIN"];
car.MSRP = (decimal)dr["MSRP"];
car.SalePrice = (decimal)dr["SalePrice"];
car.InteriorColorName = dr["InteriorColor"].ToString();
if (dr["PicturePNG"] != DBNull.Value)
{
car.PicturePNG = dr["PicturePNG"].ToString();
}
cars.Add(car);
}
}
}
return(cars);
}
public IEnumerable <CarDetailsItem> SearchUsedInventory(CarSearchParameters parameters)
{
List <CarDetailsItem> cars = new List <CarDetailsItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT TOP 20 CarId, CarMakeName, CarModelName, BodyStyle, VinNumber, CarYear, Transmission, " +
"CarColor, InteriorColor, Mileage, CarPrice, CarSalePrice, ImageFileName " +
"FROM Car c INNER JOIN CarModel cd ON c.CarModelId = cd.CarModelId INNER JOIN CarMake cm ON cd.CarMakeId = cm.CarMakeId " +
"WHERE 1 = 1 AND CategoryId = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.MinPrice.HasValue)
{
query += "AND ((CarSalePrice >= @MinPrice) OR (CarPrice >= @MinPrice)) ";
cmd.Parameters.AddWithValue("@MinPrice", parameters.MinPrice.Value);
}
if (parameters.MaxPrice.HasValue)
{
query += "AND ((CarPrice <= @MaxPrice) OR (CarSalePrice <= @MaxPrice)) ";
cmd.Parameters.AddWithValue("@MaxPrice", parameters.MaxPrice.Value);
}
if (parameters.MinYear.HasValue)
{
query += "AND CarYear >= @MinYear ";
cmd.Parameters.AddWithValue("@MinYear", parameters.MinYear.Value);
}
if (parameters.MaxYear.HasValue)
{
query += "AND CarYear <= @MaxYear ";
cmd.Parameters.AddWithValue("@MaxYear", parameters.MaxYear.Value);
}
if (!string.IsNullOrEmpty(parameters.CarMakeName))
{
query += "AND CarMakeName LIKE @CarMakeName ";
cmd.Parameters.AddWithValue("@CarMakeName", parameters.CarMakeName + '%');
}
if (!string.IsNullOrEmpty(parameters.CarModelName))
{
query += "OR CarModelName LIKE @CarModelName ";
cmd.Parameters.AddWithValue("@CarModelName", parameters.CarModelName + '%');
}
if (!string.IsNullOrEmpty(parameters.CarYear.ToString()))
{
query += "OR CarYear LIKE @CarYear ";
cmd.Parameters.AddWithValue("@CarYear", parameters.CarYear + '%');
}
query += "ORDER BY CarPrice DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CarDetailsItem row = new CarDetailsItem();
row.CarId = (int)dr["CarId"];
row.CarYear = (int)dr["CarYear"];
row.CarMakeName = dr["CarMakeName"].ToString();
row.CarModelName = dr["CarModelName"].ToString();
row.BodyStyle = dr["BodyStyle"].ToString();
row.InteriorColor = dr["InteriorColor"].ToString();
row.VinNumber = dr["VinNumber"].ToString();
row.Transmission = dr["Transmission"].ToString();
row.CarColor = dr["CarColor"].ToString();
row.Mileage = (int)dr["Mileage"];
row.CarPrice = (decimal)dr["CarPrice"];
if (dr["CarSalePrice"] != DBNull.Value)
{
row.CarSalePrice = (decimal)dr["CarSalePrice"];
}
if (dr["ImageFileName"] != DBNull.Value)
{
row.ImageFileName = dr["ImageFileName"].ToString();
}
cars.Add(row);
}
}
}
return(cars);
}
public IEnumerable <SearchItem> SearchUsed(CarSearchParameters parameters)
{
throw new NotImplementedException();
}
