public IEnumerable <ListingShortItem> ListingSearch(ListingSearchParameters parameters)
{
var listings = new List <ListingShortItem>();
using (var cxn = new SqlConnection(cxnStr))
{
string query = "select top 12 ListingId, UserId, StateId, City, Rate, ImageFileName from Listings where 1 = 1 ";
var dymprms = new DynamicParameters();
if (parameters.MinRate.HasValue)
{
query += $"and Rate >= @MinRate ";
dymprms.Add("@MinRate", parameters.MinRate.Value);
}
if (parameters.MaxRate.HasValue)
{
query += $"and Rate <= @MaxRate ";
dymprms.Add("@MaxRate", parameters.MaxRate.Value);
}
if (!string.IsNullOrEmpty(parameters.City))
{
query += $"and City like @City ";
dymprms.Add("@City", parameters.City + "%");
}
if (!string.IsNullOrEmpty(parameters.StateId))
{
query += $"and StateId like @StateId ";
dymprms.Add("@StateId", parameters.StateId);
}
query += "order by CreatedDate desc";
listings = cxn.Query <ListingShortItem>(query, dymprms, commandType: CommandType.Text).ToList();
}
return(listings);
}
public TResponse <List <Listing> > Search(ListingSearchParameters paramters)
{
var response = new TResponse <List <Listing> >();
if (paramters == null)
{
response.Message = "No search parameters were provided";
response.Success = false;
}
else
{
response.Payload = Repo.Search(paramters).ToList();
if (!response.Payload.Any())
{
response.Success = false;
response.Message = "Query could not find any results";
}
else
{
response.Success = true;
}
}
return(response);
}
public IHttpActionResult Search(string view, string quickSearch, int?minPrice, int?maxPrice, int?minYear, int?maxYear)
{
//check model state befor try
_listingManager = ListingManagerFactory.Create();
try
{
var parameters = new ListingSearchParameters()
{
View = view,
QuickSearch = quickSearch,
MinPrice = minPrice,
MaxPrice = maxPrice,
MinYear = minYear,
MaxYear = maxYear
};
var result = _listingManager.Search(parameters);
return(Ok(result.Payload));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <ListingShortItem> ListingSearch(ListingSearchParameters parameters)
{
try {
return(listingsRepo.ListingSearch(parameters));
}
catch (Exception ex)
{
//log exception
throw ex;
}
}
public void CanGetSearchResults()
{
var listingRepo = new VehicleListingRepoQA();
var parameters = new ListingSearchParameters()
{
VehicleTypeId = 1
};
var searchResults = listingRepo.GetSearchResults(parameters);
Assert.AreEqual(1, searchResults.Count());
}
public IHttpActionResult Search([FromUri] ListingSearchParameters parameters)
{
var repo = GuildRepositoryFactory.GetRepository();
try
{
var result = repo.Search(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <ListingShortItem> ListingSearch(ListingSearchParameters parameters)
{
var listings = new List <ListingShortItem>();
using (var cxn = new SqlConnection(cxnStr))
using (var cmd = new SqlCommand())
{
cmd.Connection = (SqlConnection)cxn;
string query = "select top 12 ListingId, UserId, StateId, City, Rate, ImageFileName from Listings where 1 = 1";
if (parameters.MinRate.HasValue)
{
query += $"and Rate >= @MinRate ";
cmd.Parameters.AddWithValue("@MinRate", parameters.MinRate.Value);
}
if (parameters.MaxRate.HasValue)
{
query += $"and Rate <= @MaxRate ";
cmd.Parameters.AddWithValue("@MaxRate", parameters.MaxRate.Value);
}
if (!string.IsNullOrEmpty(parameters.City))
{
query += $"and City like @City ";
cmd.Parameters.AddWithValue("@City", parameters.City + '%');
}
if (!string.IsNullOrEmpty(parameters.StateId))
{
query += $"and StateId like @StateId ";
cmd.Parameters.AddWithValue("@StateId", parameters.StateId);
}
query += "order by CreatedDate desc";
cmd.CommandText = query;
cxn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
listings.Add(PopulateListingShortItemFromDataReader(dr));
}
}
}
return(listings);
}
public IHttpActionResult Search(decimal?minRate, decimal?maxRate, string city, string stateId)
{
try {
var parameters = new ListingSearchParameters()
{
MinRate = minRate,
MaxRate = maxRate,
City = city,
StateId = stateId
};
var result = _svc.ListingSearch(parameters);
return(Ok(result));
}
catch (Exception ex) {
return(BadRequest(ex.Message));
}
}
public IHttpActionResult SalesSearch(string searchTerm, decimal?minMSRP, decimal?maxMSRP, int?minYear, int?maxYear, int?vehicleTypeId)
{
var repo = VehicleListingsFactory.GetRepository();
try
{
var parameters = new ListingSearchParameters()
{
SearchTerm = searchTerm,
MinMSRP = minMSRP,
MaxMSRP = maxMSRP,
MinYear = minYear,
MaxYear = maxYear,
VehicleTypeId = vehicleTypeId
};
var result = repo.GetSearchResults(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <ListingSearchItem> GetSearchResults(ListingSearchParameters parameters)
{
var listings = GetAll();
var makesRepo = new VehicleMakeRepoQA();
var modelsRepo = new VehicleModelRepoQA();
var bodyRepo = new BodyStyleRepoQA();
var transRepo = new TransmissionTypeRepoQA();
var colorRepo = new ColorRepoQA();
var makes = makesRepo.GetVehicleMakes();
var models = modelsRepo.GetVehicleModels();
var styles = bodyRepo.GetAll();
var trans = transRepo.GetAll();
var colors = colorRepo.GetAll();
var query = from l in listings
join ma in makes on l.VehicleMakeId equals ma.VehicleMakeId
join mo in models on l.VehicleModelId equals mo.VehicleModelId
join s in styles on l.BodyStyleId equals s.BodyStyleId
join t in trans on l.TransmissionTypeId equals t.TransmissionTypeId
join c in colors on l.ColorId equals c.ColorId
join ic in colors on l.InteriorColorId equals ic.ColorId
where l.Sold == false
orderby l.MSRP descending
select new ListingSearchItem
{
VehicleListingId = l.VehicleListingId,
Year = l.Year,
VehicleMakeId = l.VehicleMakeId,
VehicleMakeName = ma.VehicleMakeName,
VehicleModelId = l.VehicleModelId,
VehicleModelName = mo.VehicleModelName,
BodyStyleId = l.BodyStyleId,
BodyStyleName = s.BodyStyleName,
TransmissionTypeId = l.TransmissionTypeId,
TransmissionTypeName = t.TransmissionTypeName,
SalePrice = l.SalePrice,
Mileage = l.Mileage,
MSRP = l.MSRP,
InteriorColorId = l.InteriorColorId,
InteriorColor = ic.ColorName,
ColorId = l.ColorId,
Color = c.ColorName,
VIN = l.VIN,
ImageFileName = l.ImageFileName,
VehicleTypeId = l.VehicleTypeId
};
if (parameters.MinMSRP.HasValue)
{
query = query.Where(q => q.MSRP >= parameters.MinMSRP);
}
if (parameters.MaxMSRP.HasValue)
{
query = query.Where(q => q.MSRP <= parameters.MaxMSRP);
}
if (parameters.MinYear.HasValue)
{
query = query.Where(q => q.Year >= parameters.MinYear);
}
if (parameters.MaxYear.HasValue)
{
query = query.Where(q => q.Year <= parameters.MaxYear);
}
if (parameters.VehicleTypeId.HasValue)
{
query = query.Where(q => q.VehicleTypeId == parameters.VehicleTypeId);
}
if (!string.IsNullOrEmpty(parameters.SearchTerm))
{
query = query.Where(q => q.VehicleMakeName.Contains(parameters.SearchTerm) || q.VehicleModelName.Contains(parameters.SearchTerm) || q.Year.ToString().Contains(parameters.SearchTerm));
}
return(query);
}
public IEnumerable <Listing> Search(ListingSearchParameters parameters)
{
return(null);
}
public IEnumerable <ListingShortItem> Search(ListingSearchParameters parameters)
{
List <ListingShortItem> listings = new List <ListingShortItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT TOP 12 ListingId, UserId, StateId, City, Rate, ImageFileName FROM Listings WHERE 1 = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.MinRate.HasValue)
{
query += "AND Rate >= @MinRate ";
cmd.Parameters.AddWithValue("@MinRate", parameters.MinRate.Value);
}
if (parameters.MaxRate.HasValue)
{
query += "AND Rate <= @MaxRate ";
cmd.Parameters.AddWithValue("@MaxRate", parameters.MaxRate.Value);
}
if (!string.IsNullOrEmpty(parameters.City))
{
query += "AND City LIKE @City ";
cmd.Parameters.AddWithValue("@City", parameters.City + '%');
}
if (!string.IsNullOrEmpty(parameters.StateId))
{
query += "AND StateId = @StateId ";
cmd.Parameters.AddWithValue("@StateId", parameters.StateId);
}
query += "ORDER BY CreatedDate DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ListingShortItem row = new ListingShortItem();
row.ListingId = (int)dr["ListingId"];
row.UserId = dr["UserId"].ToString();
row.StateId = dr["StateId"].ToString();
row.City = dr["City"].ToString();
row.Rate = (decimal)dr["Rate"];
if (dr["ImageFileName"] != DBNull.Value)
{
row.ImageFileName = dr["ImageFileName"].ToString();
}
listings.Add(row);
}
}
}
return(listings);
}
public IEnumerable <Car> Search(ListingSearchParameters parameters)
{
List <Car> searchList = new List <Car>();
List <Car> searchList2 = new List <Car>();
var makesRepo = MakeFactory.GetRepository();
var modelRepo = ModelFactory.GetRepository();
var typesRepo = ConditionFactory.GetRepository();
var bodyStylesRepo = BodyStyleFactory.GetRepository();
var transmissionsRepo = TransmissionFactory.GetRepository();
var extColorsRepo = ExteriorColorFactory.GetRepository();
var intColorsRepo = InteriorColorFactory.GetRepository();
searchList = cars;
if (!string.IsNullOrEmpty(parameters.Make)) //FYI: we do not have a make null
{
if (parameters.Mileage == "used")
{
searchList = cars.Where(x => x.ConditionID == 2 && makesRepo.GetMakeById(x.MakeID).MakeName.Contains(parameters.Make.ToLower())).ToList();
}
else if (parameters.Mileage == "new")
{
searchList = cars.Where(x => x.ConditionID == 1 && makesRepo.GetMakeById(x.MakeID).MakeName.ToLower().Contains(parameters.Make.ToLower())).ToList();
}
else if (parameters.OnSale == "true")
{
searchList = cars.Where(x => x.OnSale == true && makesRepo.GetMakeById(x.MakeID).MakeName.ToLower().Contains(parameters.Make.ToLower())).ToList();
}
else
{
searchList = cars.Where(x => makesRepo.GetMakeById(x.MakeID).MakeName.ToLower().Contains(parameters.Make.ToLower())).ToList();
}
}
if (!string.IsNullOrEmpty(parameters.Model)) //FYI: we do not have a model null
{
if (parameters.Mileage == "used")
{
searchList = cars.Where(x => x.ConditionID == 2 && modelRepo.GetModelById(x.ModelID).ModelName.ToLower().Contains(parameters.Model.ToLower())).ToList();
}
else if (parameters.Mileage == "new")
{
searchList = cars.Where(x => x.ConditionID == 1 && modelRepo.GetModelById(x.ModelID).ModelName.ToLower().Contains(parameters.Model.ToLower())).ToList();
}
else if (parameters.OnSale == "true")
{
searchList = cars.Where(x => x.OnSale == true && modelRepo.GetModelById(x.ModelID).ModelName.ToLower().Contains(parameters.Model.ToLower())).ToList();
}
else
{
searchList = cars.Where(x => modelRepo.GetModelById(x.ModelID).ModelName.ToLower().Contains(parameters.Model.ToLower())).ToList();
}
}
searchList2 = searchList;
if (!string.IsNullOrEmpty(parameters.MinPrice) || !string.IsNullOrEmpty(parameters.MaxPrice))
{
int resultMinPrice;
int.TryParse(parameters.MinPrice, out resultMinPrice);
int resultMaxPrice;
int.TryParse(parameters.MaxPrice, out resultMaxPrice);
searchList = searchList2.Where(x => x.SalePrice >= resultMinPrice && x.SalePrice <= resultMaxPrice).ToList();
}
if (parameters.MinYear != "Any" && parameters.MaxYear != "Any")
{
int resultMinYear;
int.TryParse(parameters.MinYear, out resultMinYear);
int resultMaxYear;
int.TryParse(parameters.MaxYear, out resultMaxYear);
searchList = searchList2.Where(x => x.Year >= resultMinYear && x.Year <= resultMaxYear).ToList();
}
if (parameters.MinYear != "Any" && parameters.MaxYear == "Any")
{
int resultMinYear;
int.TryParse(parameters.MinYear, out resultMinYear);
searchList = searchList2.Where(x => x.Year >= resultMinYear).ToList();
}
if (parameters.MinYear == "Any" && parameters.MaxYear != "Any")
{
int resultMaxYear;
int.TryParse(parameters.MaxYear, out resultMaxYear);
searchList = searchList2.Where(x => x.Year <= resultMaxYear).ToList();
}
List <Car> carsSearched = new List <Car>();
foreach (var car in searchList)
{
car.Model = new Model();
car.Model.ModelID = car.ModelID;
car.Model.ModelName = modelRepo.GetModelById(car.ModelID).ModelName;
car.Make = new Make();
car.Make.MakeID = car.MakeID;
car.Make.MakeName = makesRepo.GetMakeById(car.MakeID).MakeName;
car.BodyStyle = new BodyStyle();
car.BodyStyle.BodyStyleID = car.BodyStyleID;
car.BodyStyle.BodyStyleName = bodyStylesRepo.GetBodyStyleById(car.BodyStyleID).BodyStyleName;
car.Transmission = new Transmission();
car.Transmission.TransmissionID = car.TransmissionID;
car.Transmission.TransmissionType = transmissionsRepo.GetTransmissionById(car.TransmissionID).TransmissionType;
car.ExteriorColor = new ExteriorColor();
car.ExteriorColor.ExteriorColorID = car.ExteriorColorID;
car.ExteriorColor.Color = extColorsRepo.GetExteriorColorById(car.ExteriorColorID).Color;
car.InteriorColor = new InteriorColor();
car.InteriorColor.InteriorColorID = car.InteriorColorID;
car.InteriorColor.Color = intColorsRepo.GetInteriorColorById(car.InteriorColorID).Color;
carsSearched.Add(car);
}
return(carsSearched);
}
public IEnumerable <Car> Search(ListingSearchParameters parameters)
{
List <Car> cars = new List <Car>();
using (var conn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT TOP 10 * FROM Car JOIN Make on Make.MakeID = Car.MakeID " +
"JOIN Model on Model.ModelID = Car.ModelID " +
"JOIN Condition on Condition.ConditionID = Car.ConditionID " +
"JOIN BodyStyle on BodyStyle.BodyStyleID = Car.BodyStyleID " +
"JOIN Transmission on Transmission.TransmissionID = Car.TransmissionID " +
"JOIN ExteriorColor on ExteriorColor.ExteriorColorID = Car.ExteriorColorID " +
"JOIN InteriorColor on InteriorColor.InteriorColorID = Car.InteriorColorID ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
if (parameters.Mileage == "used")
{
query += "AND Car.ConditionID = '2' ";
}
if (parameters.Mileage == "new")
{
query += "AND Car.ConditionID = '1' ";
}
if (parameters.OnSale == "true")
{
query += "AND Car.OnSale = '1' ";
}
if (!string.IsNullOrEmpty(parameters.Make))
{
query += "AND Make.MakeName LIKE @makeName ";
cmd.Parameters.AddWithValue("@makeName", parameters.Make + '%');
}
if (!string.IsNullOrEmpty(parameters.Model))
{
query += "AND Model.ModelName LIKE @modelName ";
cmd.Parameters.AddWithValue("@modelName", parameters.Model + '%');
}
if (!string.IsNullOrEmpty(parameters.MinPrice) || !string.IsNullOrEmpty(parameters.MaxPrice))
{
query += "AND Car.SalePrice BETWEEN @minPrice AND @maxPrice ";
cmd.Parameters.AddWithValue("@minPrice", parameters.MinPrice);
cmd.Parameters.AddWithValue("@maxPrice", parameters.MaxPrice);
}
if (parameters.MinYear != "Any" && parameters.MaxYear != "Any")
{
query += "AND [Car].[Year] BETWEEN @minYear AND @maxYear ";
cmd.Parameters.AddWithValue("@minYear", parameters.MinYear);
cmd.Parameters.AddWithValue("@maxYear", parameters.MaxYear);
}
if (parameters.MinYear != "Any" && parameters.MaxYear == "Any")
{
query += "AND [Car].[Year] >= @minYear ";
cmd.Parameters.AddWithValue("@minYear", parameters.MinYear);
}
if (parameters.MinYear == "Any" && parameters.MaxYear != "Any")
{
query += "AND [Car].[Year] <= @maxYear ";
cmd.Parameters.AddWithValue("@maxYear", parameters.MaxYear);
}
cmd.CommandText = query;
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Car row = new Car();
row.CarID = (int)dr["CarID"];
row.OnSale = (bool)dr["OnSale"];
row.IsInStock = (bool)dr["IsInStock"];
row.Make = new Make();
row.Make.MakeID = (int)dr["MakeID"];
row.Make.MakeName = dr["MakeName"].ToString();
row.Model = new Model();
row.Model.ModelID = (int)dr["ModelID"];
row.Model.ModelName = dr["ModelName"].ToString();
row.Condition = new Condition();
row.Condition.ConditionID = (int)dr["ConditionID"];
row.Condition.ConditionType = dr["ConditionType"].ToString();
row.Year = (int)dr["Year"];
row.BodyStyle = new BodyStyle();
row.BodyStyle.BodyStyleID = (int)dr["BodyStyleID"];
row.BodyStyle.BodyStyleName = dr["BodyStyleName"].ToString();
row.Transmission = new Transmission();
row.Transmission.TransmissionID = (int)dr["TransmissionID"];
row.Transmission.TransmissionType = dr["TransmissionType"].ToString();
row.ExteriorColor = new ExteriorColor();
row.ExteriorColor.ExteriorColorID = (int)dr["ExteriorColorID"];
row.ExteriorColor.Color = dr["Color"].ToString();
row.InteriorColor = new InteriorColor();
row.InteriorColor.InteriorColorID = (int)dr["InteriorColorID"];
row.InteriorColor.Color = dr["Color"].ToString();
row.Mileage = dr["Mileage"].ToString();
row.VIN = dr["VIN"].ToString();
row.SalePrice = (decimal)dr["SalePrice"];
row.MSRP = (decimal)dr["MSRP"];
row.Description = dr["Description"].ToString();
row.DateAdded = dr["DateAdded"].ToString();
row.Photo = dr["Photo"].ToString();
cars.Add(row);
}
}
}
return(cars);
}
public IEnumerable <Listing> Search(ListingSearchParameters parameters)
{
List <Listing> listings = new List <Listing>();
using (var cn = new SqlConnection(ConnectionStrings.GetConnectionString()))
{
string query =
"SELECT TOP 20 ListingId, l.ModelId, mo.ModelName, l.ModelYear, " +
"ma.MakeId, ma.MakeName, l.BodyStyleId, bs.BodyStyleName, l.InteriorColorId, " +
"ic.InteriorColorName, l.ExteriorColorId, ec.ExteriorColorName, " +
"Condition, Transmission, Mileage, VIN, MSRP, SalePrice, VehicleDescription, " +
"ImageFileUrl, IsFeatured, IsSold, l.DateAdded " +
"FROM Listings l " +
"inner join Models mo on mo.ModelId = l.ModelId " +
"inner join Makes ma on ma.MakeId = mo.MakeId " +
"inner join InteriorColors ic on ic.InteriorColorId = l.InteriorColorId " +
"inner join ExteriorColors ec on ec.ExteriorColorId = l.ExteriorColorId " +
"inner join BodyStyles bs on bs.BodyStyleId = l.BodyStyleId " +
"WHERE 1 = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
switch (parameters.View)
{
case "New":
query += "AND l.Condition = 1 ";
break;
case "Used":
query += "AND l.Condition = 2 ";
break;
case "Admin":
query += "AND (l.Condition = 1 or l.Condition = 2) ";
break;
case "Sales":
query += "AND l.IsSold = 0 ";
break;
default:
break;
}
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 l.ModelYear >= @MinYear ";
cmd.Parameters.AddWithValue("@MinYear", parameters.MinYear.Value);
}
if (parameters.MaxYear.HasValue)
{
query += "AND l.ModelYear <= @MaxYear ";
cmd.Parameters.AddWithValue("@MaxYear", parameters.MaxYear.Value);
}
if (!string.IsNullOrEmpty(parameters.QuickSearch))
{
query += "AND (ma.MakeName LIKE @QuickSearch OR mo.ModelName LIKE @QuickSearch OR l.ModelYear LIKE @QuickSearch) ";
cmd.Parameters.AddWithValue("@QuickSearch", parameters.QuickSearch + '%');
}
query += "ORDER BY DateAdded DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Listing row = new Listing();
row.ListingId = (int)dr["ListingId"];
row.ModelId = (int)dr["ModelId"];
row.ModelName = dr["ModelName"].ToString();
row.ModelYear = (int)dr["ModelYear"];
row.MakeId = (int)dr["MakeId"];
row.MakeName = dr["MakeName"].ToString();
row.BodyStyleId = (int)dr["BodyStyleId"];
row.BodyStyleName = dr["BodyStyleName"].ToString();
row.InteriorColorId = (int)dr["InteriorColorId"];
row.InteriorColorName = dr["InteriorColorName"].ToString();
row.ExteriorColorId = (int)dr["ExteriorColorId"];
row.ExteriorColorName = dr["ExteriorColorName"].ToString();
row.Condition = (Condition)dr["Condition"];
row.Transmission = (Transmission)dr["Transmission"];
row.Mileage = (int)dr["Mileage"];
row.VIN = dr["VIN"].ToString();
row.MSRP = (decimal)dr["MSRP"];
row.SalePrice = (decimal)dr["SalePrice"];
row.VehicleDescription = dr["VehicleDescription"].ToString();
row.IsFeatured = (bool)dr["IsFeatured"];
row.IsSold = (bool)dr["IsSold"];
if (dr["ImageFileUrl"] != DBNull.Value)
{
row.ImageFileUrl = dr["ImageFileUrl"].ToString();
}
listings.Add(row);
}
}
}
return(listings);
}
public IEnumerable <Listing> Search(ListingSearchParameters parameters)
{
//search testing to implement later... this would be hard
throw new NotImplementedException();
}
public IEnumerable <ListingSearchItem> GetSearchResults(ListingSearchParameters parameters)
{
List <ListingSearchItem> listings = new List <ListingSearchItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT TOP 20 VehicleListingId, [Year], vma.VehicleMakeId, vma.VehicleMakeName, VehicleTypeId, " +
"vmo.VehicleModelId, vmo.VehicleModelName, b.BodyStyleId, b.BodyStyleName, t.TransmissionTypeId, t.TransmissionTypeName, " +
"SalePrice, Mileage, MSRP, vl.InteriorColorId, ic.ColorName as InteriorColor, c.ColorId, c.ColorName, VIN, ImageFileName, Sold " +
"FROM VehicleListings vl " +
"INNER JOIN VehicleMakes vma ON vl.VehicleMakeId = vma.VehicleMakeId " +
"INNER JOIN VehicleModels vmo ON vl.VehicleModelId = vmo.VehicleModelId " +
"INNER JOIN BodyStyles b ON vl.BodyStyleId = b.BodyStyleId " +
"INNER JOIN TransmissionTypes t ON vl.TransmissionTypeId = t.TransmissionTypeId " +
"INNER JOIN Colors ic ON vl.InteriorColorId = ic.ColorId " +
"INNER JOIN Colors c ON vl.ColorId = c.ColorId " +
"WHERE Sold = 0 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.MinMSRP.HasValue)
{
query += $"AND MSRP >= @MinMSRP ";
cmd.Parameters.AddWithValue("@MinMSRP", parameters.MinMSRP.Value);
}
if (parameters.MaxMSRP.HasValue)
{
query += $"AND MSRP <= @MaxMSRP ";
cmd.Parameters.AddWithValue("@MaxMSRP ", parameters.MaxMSRP.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 (parameters.VehicleTypeId.HasValue)
{
query += $"AND VehicleTypeId = @VehicleTypeId ";
cmd.Parameters.AddWithValue("@VehicleTypeId", parameters.VehicleTypeId.Value);
}
if (!string.IsNullOrEmpty(parameters.SearchTerm))
{
query += $"AND (vma.VehicleMakeName LIKE @SearchTerm OR vmo.VehicleModelName LIKE @SearchTerm OR [Year] LIKE @SearchTerm) ";
cmd.Parameters.AddWithValue("@SearchTerm", parameters.SearchTerm + '%');
}
query += "ORDER BY MSRP DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ListingSearchItem row = new ListingSearchItem();
row.VehicleListingId = (int)dr["VehicleListingId"];
row.Year = (int)dr["Year"];
row.VehicleMakeId = (int)dr["VehicleMakeId"];
row.VehicleMakeName = dr["VehicleMakeName"].ToString();
row.VehicleModelId = (int)dr["VehicleModelId"];
row.VehicleModelName = dr["VehicleModelName"].ToString();
row.BodyStyleId = (int)dr["BodyStyleId"];
row.BodyStyleName = dr["BodyStyleName"].ToString();
row.TransmissionTypeId = (int)dr["TransmissionTypeId"];
row.TransmissionTypeName = dr["TransmissionTypeName"].ToString();
row.SalePrice = (decimal)dr["SalePrice"];
row.Mileage = (int)dr["Mileage"];
row.MSRP = (decimal)dr["MSRP"];
row.InteriorColorId = (int)dr["InteriorColorId"];
row.InteriorColor = dr["InteriorColor"].ToString();
row.ColorId = (int)dr["ColorId"];
row.Color = dr["ColorName"].ToString();
row.VIN = dr["VIN"].ToString();
if (dr["ImageFileName"] != DBNull.Value)
{
row.ImageFileName = dr["ImageFileName"].ToString();
}
listings.Add(row);
}
}
}
return(listings);
}
