public IHttpActionResult Search(int?condition, decimal?minPrice, decimal?maxPrice,
int?minYear, int?maxYear, bool isAspNetUser, string searchTerm)
{
var repo = VehicleRepositoryFactory.GetRepository();
try
{
var parameters = new VehicleSearchParameters()
{
Condition = condition,
MinPrice = minPrice,
MaxPrice = maxPrice,
MinYear = minYear,
MaxYear = maxYear,
IsAspNetUser = isAspNetUser,
SearchTerm = searchTerm
};
var result = repo.Search(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
//public List<Vehicle> Search(string type, string SearchKey, int minYear, int maxYear, int minPrice, int maxPrice)
//{
// List<Vehicle> newVehicles = new List<Vehicle>();
// List<Vehicle> toReturn = new List<Vehicle>();
// if(type == "new")
// {
// newVehicles = (_vehicle.Where(v => v.IsNew == true)).ToList();
// foreach(var car in newVehicles)
// {
// if(car.Make.MakeName.Contains(SearchKey) || car.Model.ModelName.Contains(SearchKey) || car.Year.ToString() == SearchKey
// && car.Year >= minYear && car.Year <= maxYear && car.SalePrice >= minPrice && car.SalePrice <= maxPrice)
// {
// toReturn.Add(car);
// }
// }
// }
// return toReturn;
//}
public List <Vehicle> Search(VehicleSearchParameters pram)
{
//List<Vehicle> newVehicles = new List<Vehicle>();
//List<Vehicle> toReturn = new List<Vehicle>();
//var query = _vehicle.Where(i => i.IsNew == pram.IsNew);
//if (!String.IsNullOrWhiteSpace(pram.SearchKey))
//{
// query = query.Where(i => i.Make.MakeName.Contains(pram.SearchKey)
// || i.Model.ModelName.Contains(pram.SearchKey));
//}
//if (pram.MinPrice.HasValue)
//{
// query = query.Where(i => i.SalePrice > pram.MinPrice.Value);
//}
//if (pram.MaxPrice.HasValue)
//{
// query = query.Where(i => i.SalePrice < pram.MaxPrice.Value);
//}
//if (pram.MinYear.HasValue)
//{
// query = query.Where(i => i.Year > pram.MinYear);
//}
//if (pram.MaxYear.HasValue)
//{
// query = query.Where(i => i.Year < pram.MaxYear);
//}
//return query.ToList();
throw new NotImplementedException();
}
public IEnumerable <VehicleDetailsRequest> GetSearchResults(VehicleSearchParameters parameters)
{
List <Vehicle> results = _vehicles;
if (parameters.maxPrice.HasValue)
{
results = (from vehicle in results
where vehicle.SalesPrice <= parameters.maxPrice
select vehicle).ToList();
}
if (parameters.minPrice.HasValue)
{
results = (from vehicle in results
where vehicle.SalesPrice >= parameters.minPrice
select vehicle).ToList();
}
if (parameters.minYear != null)
{
results = (from vehicle in results
where vehicle.Year >= parameters.minYear
select vehicle).ToList();
}
if (parameters.maxYear != null)
{
results = (from vehicle in results
where vehicle.Year <= parameters.maxYear
select vehicle).ToList();
}
List <VehicleDetailsRequest> vehicleDetails = GetNamesFromId(results);
bool successfullyParsed = int.TryParse(parameters.search, out int year);
if (successfullyParsed == true)
{
vehicleDetails = (from vehicle in vehicleDetails
where vehicle.Year == year
select vehicle).ToList();
}
else
{
if (parameters.search != null)
{
vehicleDetails = (from vehicle in vehicleDetails
where vehicle.MakeName.Contains(parameters.search) || vehicle.ModelName.Contains(parameters.search)
select vehicle).ToList();
}
}
return(vehicleDetails);
}
public IEnumerable <VehicleSearchResult> Search(VehicleSearchParameters parameters)
{
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
var par = new DynamicParameters();
string query = "SELECT TOP 20 VehicleId, VIN, Interior, Transmission, Mileage, MSRP, SalePrice, Color, BodyStyle, mk.MakeName as Make, mo.ModelName as Model, [Year], ImageFileName FROM Vehicle v INNER JOIN Make mk ON v.MakeId = mk.MakeId INNER JOIN Model mo ON v.ModelId = mo.ModelId WHERE Sold = 'false' ";
if (!string.IsNullOrEmpty(parameters.Type))
{
query += "AND Type=@Type ";
par.Add("@Type", parameters.Type);
}
if (parameters.MinYear.HasValue)
{
query += "AND v.Year >= @MinYear ";
par.Add("@MinYear", parameters.MinYear);
}
if (parameters.MaxYear.HasValue)
{
query += "AND v.Year <= @MaxYear ";
par.Add("@MaxYear", parameters.MaxYear);
}
if (parameters.MinPrice.HasValue)
{
par.Add("@MinPrice", parameters.MinPrice);
query += "AND v.SalePrice >= @MinPrice ";
}
if (parameters.MaxPrice.HasValue)
{
query += "AND v.SalePrice <= @MaxPrice ";
par.Add("@MaxPrice", parameters.MaxPrice);
}
if (!string.IsNullOrEmpty(parameters.MakeModelYear))
{
query += "AND (mk.MakeName LIKE @MakeModelYear OR mo.ModelName LIKE @MakeModelYear OR Year LIKE @MakeModelYear) ";
par.Add("@MakeModelYear", parameters.MakeModelYear + '%');
}
query += "ORDER BY MSRP DESC";
return(cn.Query <VehicleSearchResult>(query, par, commandType: CommandType.Text).ToList());
}
}
public ActionResult AddVehicle(VehicleViewModel viewModel)
{
var vehicleRepo = VehicleFactory.GetVehicleRepository();
try
{
if (ModelState.IsValid)
{
viewModel.vehicle.UserId = GetUserId();
if (viewModel.ImageUpload != null && viewModel.ImageUpload.ContentLength > 0)
{
var savepath = Server.MapPath("~/Images");
string fileName = Path.GetFileNameWithoutExtension(viewModel.ImageUpload.FileName);
string extension = Path.GetExtension(viewModel.ImageUpload.FileName);
var filePath = Path.Combine(savepath, fileName + extension);
filePath = Path.Combine(savepath, "inventory-" + viewModel.vehicle.VehicleId + extension);
WebImage img = new WebImage(viewModel.ImageUpload.InputStream);
img.Resize(150, 100, preserveAspectRatio: false, preventEnlarge: true).Crop(1, 1);
img.Save(filePath);
viewModel.vehicle.PictureFileName = Path.GetFileName(filePath);
}
vehicleRepo.InsertVehicle(viewModel.vehicle);
var param = new VehicleSearchParameters();
viewModel.vehicle.VehicleId = vehicleRepo.GetSearchResults(param).Count();
}
else
{
viewModel = viewModel.GetLists();
viewModel.UserId = GetUserId();
viewModel.vehicle = new Vehicle();
return(View(viewModel));
}
}
catch (Exception ex)
{
throw ex;
}
return(RedirectToAction("EditVehicle", new { id = viewModel.vehicle.VehicleId }));
}
public List <Vehicle> QuickSearchUsed(VehicleSearchParameters parameters)
{
var result = GetAllUsed();
if (!String.IsNullOrWhiteSpace(parameters.QuickSearch))
{
result = result.Where(s => s.VehicleModel.VehicleMake.Make.Contains(parameters.QuickSearch) || s.VehicleModel.ModelType.Contains(parameters.QuickSearch)).ToList();
}
result = result.Where(r => r.SalePrice >= parameters.MinPrice).ToList();
result = result.Where(r => r.SalePrice <= parameters.MaxPrice).ToList();
result = result.Where(r => r.Year >= parameters.MinYear).ToList();
result = result.Where(r => r.Year <= parameters.MaxYear).ToList();
return(result);
}
public List <Vehicle> Search(VehicleSearchParameters pram)
{
var query = context.Vehicle.Where(i => i.IsNew == pram.IsNew && !context.Sales.Any(s => s.VehicleId == i.VehicleId));
if (!String.IsNullOrWhiteSpace(pram.SearchKey))
{
query = query.Where(i => i.MakeId.MakeName.Contains(pram.SearchKey) ||
i.ModelId.ModelName.Contains(pram.SearchKey) ||
i.Year.ToString() == pram.SearchKey);
}
if (pram.MinPrice.HasValue && pram.MaxPrice.HasValue)
{
query = query.Where(i => i.SalesPrice >= pram.MinPrice.Value && i.SalesPrice <= pram.MaxPrice.Value);
}
else
{
if (pram.MinPrice.HasValue)
{
query = query.Where(i => i.SalesPrice >= pram.MaxPrice.Value);
}
if (pram.MaxPrice.HasValue)
{
query = query.Where(i => i.SalesPrice <= pram.MaxPrice.Value);
}
}
if (pram.MinYear.HasValue && pram.MaxYear.HasValue)
{
query = query.Where(i => i.Year >= pram.MinYear.Value && i.Year <= pram.MaxYear.Value);
}
else
{
if (pram.MinYear.HasValue)
{
query = query.Where(i => i.Year > pram.MinYear);
}
if (pram.MaxYear.HasValue)
{
query = query.Where(i => i.Year > pram.MaxYear);
}
}
return(query.ToList());
}
public void SearchVehiclesTest()
{
var route4Me = new Route4MeManagerV5(c_ApiKey);
var searchParams = new VehicleSearchParameters()
{
VehicleIDs = new string[] { lsVehicles[0].VehicleId },
Latitude = 29.748868,
Longitude = -95.358473
};
var result = route4Me.SearchVehicles(searchParams, out ResultResponse resultResponse);
Assert.NotNull(result);
Assert.IsType <Vehicle[]>(result);
}
public IHttpActionResult SearchUsed(string quicksearch, decimal?minprice, decimal?maxprice, int?minyear, int?maxyear)
{
try
{
var parameters = new VehicleSearchParameters()
{
QuickSearch = quicksearch,
MinPrice = minprice,
MaxPrice = maxprice,
MinYear = minyear,
MaxYear = maxyear
};
return(Ok(_repo.QuickSearchUsed(parameters)));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public void CanDeleteVehicle()
{
var repoVehicle = VehicleFactory.GetVehicleRepository();
var param = new VehicleSearchParameters();
var vehicle = new Vehicle
{
MakeId = 2,
ModelId = 3,
ColorId = 3,
TransmitionId = 2,
InteriorId = 2,
Mileage = 50000,
VinNum = "56789",
Year = 2014,
Descrip = "One of the best prices on the lot!",
MSRP = 15000,
SalesPrice = 13000,
BodyStyleId = 2,
PictureFileName = "inventory-4.jpg",
IsSold = false,
IsFeatured = false,
IsNew = false
};
repoVehicle.InsertVehicle(vehicle);
var loadedVehicle = repoVehicle.GetVehicle(4);
loadedVehicle.IsDeleted = true;
repoVehicle.UpdateVehicle(loadedVehicle);
var found = repoVehicle.GetSearchResults(param);
found = found.Where(x => x.IsDeleted == false);
Assert.AreEqual(8, found.Count());
}
public IHttpActionResult GetSearch(decimal?minPrice, decimal?maxPrice, int?minYear, int?maxYear, string search)
{
var repo = VehicleFactory.GetVehicleRepository();
var parameters = new VehicleSearchParameters();
parameters.maxPrice = maxPrice;
parameters.minPrice = minPrice;
parameters.minYear = minYear;
parameters.maxYear = maxYear;
parameters.search = search;
try
{
var vehicles = repo.GetSearchResults(parameters);
vehicles = vehicles.Where(v => v.IsDeleted == false);
vehicles = vehicles.Where(v => v.IsSold == false);
return(Ok(vehicles));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IHttpActionResult SearchForInventory(string newUsed, string makeModelYear, decimal?minPrice, decimal?maxPrice, int?minYear, int?maxYear)
{
var repo = InventoryRepositoryFactory.GetRepository();
try
{
var parameters = new VehicleSearchParameters()
{
NewUsed = newUsed,
MakeModelYear = makeModelYear,
MinPrice = minPrice,
MaxPrice = maxPrice,
MinYear = minYear,
MaxYear = maxYear
};
var result = repo.SearchInventory(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IHttpActionResult Search(string makeModelYear, int?minYear, int?maxYear, decimal?minPrice, decimal?maxPrice, string type)
{
var repo = VehicleRepositoryFactory.GetVehicleRepository();
try
{
var parameters = new VehicleSearchParameters()
{
MakeModelYear = makeModelYear,
MinYear = minYear,
MaxYear = maxYear,
MinPrice = minPrice,
MaxPrice = maxPrice,
Type = type
};
var result = repo.Search(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IHttpActionResult Search(decimal?minPrice, decimal?maxPrice, string minYear, string maxYear, string makeName, string modelName, string year)
{
var repo = VehicleRepositoryFactory.GetRepository();
try
{
var parameters = new VehicleSearchParameters()
{
MinPrice = minPrice,
MaxPrice = maxPrice,
MinYear = minYear,
MaxYear = maxYear,
MakeName = makeName,
ModelName = modelName,
Year = year
};
var result = repo.Search(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <VehicleDetail> Search(VehicleSearchParameters parameters)
{
throw new NotImplementedException();
}
public IEnumerable <VehicleShortItem> Search(VehicleSearchParameters parameters)
{
List <VehicleShortItem> vehicles = new List <VehicleShortItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT TOP 20 VehicleId, BodyStyle, Year, Transmission, Color, Interior, Miles, Type, Vin, Price, Msrp, " +
"ImageFileName, MakeName, ModelName FROM Vehicle INNER JOIN Make ON Vehicle.MakeId = Make.MakeId INNER JOIN Model ON " +
"Vehicle.ModelId = Model.ModelId WHERE Type='NEW' AND 1 = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.MinPrice.HasValue)
{
query += "AND Price >= @MinPrice ";
cmd.Parameters.AddWithValue("@MinPrice", parameters.MinPrice.Value);
}
if (parameters.MaxPrice.HasValue)
{
query += "AND Price <= @MaxPrice ";
cmd.Parameters.AddWithValue("@MaxPrice", parameters.MaxPrice.Value);
}
if (!string.IsNullOrEmpty(parameters.MinYear))
{
query += "AND Year >= @MinYear ";
cmd.Parameters.AddWithValue("@MinYear", parameters.MinYear);
}
if (!string.IsNullOrEmpty(parameters.MaxYear))
{
query += "AND Year <= @MaxYear ";
cmd.Parameters.AddWithValue("@MaxYear", parameters.MaxYear);
}
if (!string.IsNullOrEmpty(parameters.MakeName))
{
query += "AND MakeName = @MakeName ";
cmd.Parameters.AddWithValue("@MakeName", parameters.MakeName);
}
if (!string.IsNullOrEmpty(parameters.ModelName))
{
query += "AND ModelName = @ModelName ";
cmd.Parameters.AddWithValue("@ModelName", parameters.ModelName);
}
if (!string.IsNullOrEmpty(parameters.Year))
{
query += "AND Year = @Year ";
cmd.Parameters.AddWithValue("@Year", parameters.Year);
}
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
VehicleShortItem row = new VehicleShortItem();
row.VehicleId = (int)dr["VehicleId"];
row.BodyStyle = dr["BodyStyle"].ToString();
row.Year = dr["Year"].ToString();
row.Transmission = dr["Transmission"].ToString();
row.Color = dr["Color"].ToString();
row.Interior = dr["Interior"].ToString();
row.Type = dr["Type"].ToString();
row.Vin = dr["Vin"].ToString();
row.Price = (decimal)dr["Price"];
row.Msrp = (decimal)dr["Msrp"];
row.MakeName = dr["MakeName"].ToString();
row.ModelName = dr["ModelName"].ToString();
if (dr["ImageFileName"] != DBNull.Value)
{
row.ImageFileName = dr["ImageFileName"].ToString();
}
vehicles.Add(row);
}
}
}
return(vehicles);
}
public IEnumerable <VehicleDetailsRequest> GetSearchResults(VehicleSearchParameters parameters)
{
VehicleSearchParameters parameter = new VehicleSearchParameters();
List <VehicleDetailsRequest> resultsList = new List <VehicleDetailsRequest>();
if (parameters.maxPrice == null)
{
parameter.maxPrice = 0;
}
else
{
parameter.maxPrice = parameters.maxPrice;
}
if (parameters.minPrice == null)
{
parameter.minPrice = 0;
}
else
{
parameter.minPrice = parameters.minPrice;
}
if (parameters.minYear == null)
{
parameter.minYear = 0;
}
else
{
parameter.minYear = parameters.minYear;
}
if (parameters.maxYear == null)
{
parameter.maxYear = 0;
}
else
{
parameter.maxYear = parameters.maxYear;
}
parameter.search = parameters.search;
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
string query = "SELECT TOP 20 BodyStyle, ColorType, InteriorType, MakeName, Mileage, MSRP, SalesPrice, TransmitionType, VehicleId, VinNum, [Year], ModelName, IsNew, IsSold, IsFeatured, PictureFileName, IsDeleted FROM Vehicle v INNER JOIN BodyStyle b ON v.BodyStyleId = b.BodyStyleId INNER JOIN Color c ON v.ColorId = c.ColorId INNER JOIN Interior i ON v.InteriorId = i.InteriorId INNER JOIN Make ma ON v.MakeId = ma.MakeId INNER JOIN Transmition t ON v.TransmitionId = t.TransmitionId INNER JOIN Model mo ON mo.ModelId = v.ModelId WHERE 1 = 1 ";
if (parameter.minPrice != 0 && parameter.minPrice != null)
{
query += "AND SalesPrice >= @MinPrice ";
cmd.Parameters.AddWithValue("@MinPrice", parameters.minPrice.Value);
}
if (parameter.maxPrice != 0 && parameter.maxPrice != null)
{
query += "AND SalesPrice <= @MaxPrice ";
cmd.Parameters.AddWithValue("@MaxPrice", parameters.maxPrice.Value);
}
if (parameter.minYear != 0 && parameter.minYear != null)
{
query += "AND [Year] >= @MinYear ";
cmd.Parameters.AddWithValue("@MinYear", parameters.minYear);
}
if (parameter.maxYear != 0 && parameter.maxYear != null)
{
query += "AND [Year] <= @MaxYear ";
cmd.Parameters.AddWithValue("@MaxYear", parameters.maxYear);
}
if (!string.IsNullOrEmpty(parameter.search))
{
query += "AND MakeName Like @Search OR ModelName LIKE @Search OR [Year] LIKE @Search ";
cmd.Parameters.AddWithValue("@Search", parameters.search + '%');
}
query += "ORDER BY MSRP DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
VehicleDetailsRequest currentRow = new VehicleDetailsRequest();
currentRow.BodyStyle = dr["BodyStyle"].ToString();
currentRow.ColorType = dr["ColorType"].ToString();
currentRow.InteriorType = dr["InteriorType"].ToString();
currentRow.MakeName = dr["MakeName"].ToString();
currentRow.ModelName = dr["ModelName"].ToString();
currentRow.Mileage = Convert.ToDecimal(dr["Mileage"]);
currentRow.MSRP = Convert.ToDecimal(dr["MSRP"]);
currentRow.SalesPrice = Convert.ToDecimal(dr["SalesPrice"]);
currentRow.TransmitionType = dr["TransmitionType"].ToString();
currentRow.PictureFileName = dr["PictureFileName"].ToString();
currentRow.VehicleId = (int)dr["VehicleId"];
currentRow.VinNum = dr["VinNum"].ToString();
currentRow.Year = Convert.ToInt32(dr["Year"]);
currentRow.IsNew = Convert.ToBoolean(dr["IsNew"]);
currentRow.IsDeleted = Convert.ToBoolean(dr["IsDeleted"]);
currentRow.IsSold = Convert.ToBoolean(dr["IsSold"]);
currentRow.IsFeatured = Convert.ToBoolean(dr["IsFeatured"]);
resultsList.Add(currentRow);
}
return(resultsList);
}
}
}
public VehicleSearchViewModel()
{
SearchParams = new VehicleSearchParameters();
SearchResults = null;
}
public Sales GetSales(VehicleSearchParameters pram)
{
throw new NotImplementedException();
}
public IEnumerable <VehicleDetail> Search(VehicleSearchParameters parameters)
{
var vehicles = new List <VehicleDetail>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = (
@"SELECT TOP 20 VehicleId
,V.UserId
,[Year]
,IsUsed
,IsAutomatic
,IsFeatured
,MK.[Name] AS Make
,MD.[Name] AS Model
,BS.[Name] AS BodyStyle
,IC.[Name] AS InteriorColor
,EC.[Name] AS ExteriorColor
,VIN
,V.[Description]
,[Image]
,SaleId
,SalePrice
,MSRP
,Mileage
FROM Vehicles V
INNER JOIN BodyStyles BS ON BS.BodyStyleId = V.BodyStyleId
INNER JOIN InteriorColors IC ON IC.InteriorColorId = V.InteriorColorId
INNER JOIN ExteriorColors EC ON EC.ExteriorColorId = V.ExteriorColorId
INNER JOIN Models MD ON MD.ModelId = V.ModelId
INNER JOIN Makes MK ON MK.MakeId = MD.MakeId
WHERE 1 = 1
");
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.IsAspNetUser)
{
query += "AND SaleId IS NULL ";
cmd.Parameters.AddWithValue("@IsAspNetUser", parameters.IsAspNetUser);
}
if (parameters.Condition == (int)Condition.New ||
parameters.Condition == (int)Condition.Used)
{
query += "AND IsUsed = @Condition ";
cmd.Parameters.AddWithValue("@Condition", parameters.Condition);
}
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 [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.SearchTerm))
{
query += @"AND MK.[Name] LIKE '%' + @SearchTerm + '%'
OR MD.[Name] 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())
{
var row = new VehicleDetail();
row.VehicleId = (int)dr["VehicleId"];
row.Year = (int)dr["Year"];
row.IsUsed = (bool)dr["IsUsed"];
row.IsAutomatic = (bool)dr["IsAutomatic"];
row.IsFeatured = (bool)dr["IsFeatured"];
row.SalePrice = (decimal)dr["SalePrice"];
row.MSRP = (decimal)dr["MSRP"];
row.Mileage = (decimal)dr["Mileage"];
row.Make = dr["Make"].ToString();
row.Model = dr["Model"].ToString();
row.BodyStyle = dr["BodyStyle"].ToString();
row.InteriorColor = dr["InteriorColor"].ToString();
row.ExteriorColor = dr["ExteriorColor"].ToString();
row.UserId = dr["UserId"].ToString();
row.VIN = dr["VIN"].ToString();
if (dr["SaleId"] != DBNull.Value)
{
row.SaleId = (int)dr["SaleId"];
}
if (dr["Description"] != DBNull.Value)
{
row.Description = dr["Description"].ToString();
}
if (dr["Image"] != DBNull.Value)
{
row.Image = dr["Image"].ToString();
}
vehicles.Add(row);
}
}
}
return(vehicles);
}
public List <Vehicle> QuickSearchUsed(VehicleSearchParameters parameters)
{
var result = GetAllUsed();
#region With Quick Search
if (!String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinPrice.HasValue)
{
result = result.Where(r => r.SalePrice >= parameters.MinPrice).ToList();
}
if (!String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MaxPrice.HasValue)
{
result = result.Where(r => r.SalePrice <= parameters.MaxPrice).ToList();
}
if (!String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinYear.HasValue)
{
result = result.Where(r => r.Year >= parameters.MinYear).ToList();
}
if (!String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MaxYear.HasValue)
{
result = result.Where(r => r.Year <= parameters.MaxYear).ToList();
}
if (!String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinPrice.HasValue && parameters.MaxPrice.HasValue &&
parameters.MinYear.HasValue && parameters.MaxYear.HasValue)
{
result = result.Where(r => r.SalePrice > +parameters.MinPrice && r.SalePrice <= parameters.MaxPrice &&
r.Year >= parameters.MinYear && r.Year <= parameters.MaxYear).ToList();
}
if (!String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinYear.HasValue && parameters.MaxYear.HasValue)
{
result = result.Where(r => r.Year >= parameters.MinYear && r.Year <= parameters.MaxYear).ToList();
}
if (!String.IsNullOrWhiteSpace(parameters.QuickSearch))
{
result = result.Where(s => s.VehicleModel.VehicleMake.Make.Contains(parameters.QuickSearch) || s.VehicleModel.ModelType.Contains(parameters.QuickSearch)).ToList();
}
#endregion
#region Without Quick Search
if (String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinPrice.HasValue && parameters.MaxPrice.HasValue &&
parameters.MinYear.HasValue && parameters.MaxYear.HasValue)
{
result = result.Where(r => r.SalePrice >= parameters.MinPrice && r.SalePrice <= parameters.MaxPrice && r.Year >= parameters.MinYear && r.Year <= parameters.MaxYear).ToList();
}
else if (String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinPrice.HasValue && parameters.MaxPrice.HasValue)
{
result = result.Where(r => r.SalePrice >= parameters.MinPrice && r.SalePrice <= parameters.MaxPrice).ToList();
}
else if (String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinYear.HasValue && parameters.MaxYear.HasValue)
{
result = result.Where(r => r.Year >= parameters.MinYear && r.Year <= parameters.MaxYear).ToList();
}
else if (String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinPrice.HasValue && parameters.MaxPrice.HasValue && parameters.MinYear.HasValue)
{
result = result.Where(r => r.Year >= parameters.MinYear && r.Year <= parameters.MaxYear && r.SalePrice >= parameters.MinPrice || r.SalePrice <= parameters.MaxPrice).ToList();
}
else if (String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinPrice.HasValue && parameters.MinYear.HasValue && parameters.MaxYear.HasValue)
{
result = result.Where(r => r.SalePrice >= parameters.MinPrice && r.SalePrice <= parameters.MaxPrice && r.Year <= parameters.MaxYear).ToList();
}
else if (String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MaxPrice.HasValue && parameters.MinYear.HasValue && parameters.MaxYear.HasValue)
{
result = result.Where(r => r.SalePrice <= parameters.MaxPrice && r.Year <= parameters.MinYear && r.Year <= parameters.MaxYear).ToList();
}
else if (String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinPrice.HasValue)
{
result = result.Where(r => r.SalePrice >= parameters.MinPrice).ToList();
}
else if (String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MaxPrice.HasValue)
{
result = result.Where(r => r.SalePrice <= parameters.MaxPrice).ToList();
}
else if (String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MinYear.HasValue)
{
result = result.Where(r => r.Year >= parameters.MinYear).ToList();
}
else if (String.IsNullOrWhiteSpace(parameters.QuickSearch) && parameters.MaxYear.HasValue)
{
result = result.Where(r => r.Year <= parameters.MaxYear).ToList();
}
return(result);
}
public List <Vehicle> SearchSales(VehicleSearchParameters pram)
{
throw new NotImplementedException();
}
public IEnumerable <VehicleSearchResult> SearchInventory(VehicleSearchParameters parameters)
{
{
List <VehicleSearchResult> list = new List <VehicleSearchResult>();
if (string.IsNullOrEmpty(parameters.MinPrice.ToString()))
{
parameters.MinPrice = 1;
}
if (string.IsNullOrEmpty(parameters.MaxPrice.ToString()))
{
parameters.MaxPrice = 150000;
}
if (string.IsNullOrEmpty(parameters.MinYear.ToString()))
{
parameters.MinYear = 1999;
}
if (string.IsNullOrEmpty(parameters.MaxYear.ToString()))
{
parameters.MaxYear = 3000;
}
if (!string.IsNullOrEmpty(parameters.MakeModelYear) && int.TryParse(parameters.MakeModelYear, out int year))
{
parameters.MakeModelYear = year.ToString();
}
else if (string.IsNullOrEmpty(parameters.MakeModelYear))
{
parameters.MakeModelYear = "";
}
if (string.IsNullOrEmpty(parameters.NewUsed))
{
var result = _Vehicles.
Where(v => (v.MakeDescription.StartsWith(parameters.MakeModelYear) || v.VehicleYear.ToString().StartsWith(parameters.MakeModelYear) || v.ModelDescription.StartsWith(parameters.MakeModelYear)) && v.VehicleYear >= parameters.MinYear && v.VehicleYear <= parameters.MaxYear && v.VehicleSalesPrice >= parameters.MinPrice && v.VehicleSalesPrice <= parameters.MaxPrice);
var newInventoryQuery = result.OrderByDescending(v => v.VehicleSalesPrice).Take(20);
foreach (VehicleSearchResult vehicle in newInventoryQuery)
{
vehicle.VehicleSalesPriceFormatted = vehicle.VehicleSalesPrice.ToString("c0");
vehicle.VehicleMSRPFormatted = vehicle.VehicleMSRP.ToString("c0");
if (vehicle.VehicleMileage < 1000)
{
vehicle.VehicleMileageFormatted = "New";
}
else
{
vehicle.VehicleMileageFormatted = Convert.ToDecimal(vehicle.VehicleMileage).ToString("#,##0");
}
}
return(newInventoryQuery);
}
else
{
var result = _Vehicles.
Where(v => v.VehicleTypeDescription == parameters.NewUsed && (v.MakeDescription.StartsWith(parameters.MakeModelYear) || v.VehicleYear.ToString().StartsWith(parameters.MakeModelYear) || v.ModelDescription.StartsWith(parameters.MakeModelYear)) && v.VehicleYear >= parameters.MinYear && v.VehicleYear <= parameters.MaxYear && v.VehicleSalesPrice >= parameters.MinPrice && v.VehicleSalesPrice <= parameters.MaxPrice);
var newInventoryQuery = result.OrderByDescending(v => v.VehicleSalesPrice).Take(20);
foreach (VehicleSearchResult vehicle in newInventoryQuery)
{
vehicle.VehicleSalesPriceFormatted = vehicle.VehicleSalesPrice.ToString("c0");
vehicle.VehicleMSRPFormatted = vehicle.VehicleMSRP.ToString("c0");
if (vehicle.VehicleMileage < 1000)
{
vehicle.VehicleMileageFormatted = "New";
}
else
{
vehicle.VehicleMileageFormatted = Convert.ToDecimal(vehicle.VehicleMileage).ToString("#,##0");
}
}
return(newInventoryQuery);
}
}
}
public IEnumerable <VehicleSearchResult> SearchInventory(VehicleSearchParameters parameters)
{
List <VehicleSearchResult> vehicles = new List <VehicleSearchResult>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT TOP 20 Vehicles.VehicleId, VehicleYear, MakeDescription, ModelDescription, VehicleTypeDescription, ";
query += "BodyStyleDescription, InteriorColorDescription, TransmissionDescription, VehicleMileage, BodyColorDescription, VehicleVIN, ";
query += "VehicleSalesPrice, VehicleMSRP, VehiclePicture, VehicleDescription, VehicleIsFeatured ";
query += "FROM Vehicles LEFT JOIN Sales ON Vehicles.VehicleId = Sales.VehicleId INNER JOIN BodyStyles ON Vehicles.BodyStyleId = BodyStyles.BodyStyleId ";
query += "INNER JOIN InteriorColors ON Vehicles.InteriorColorId = InteriorColors.InteriorColorId ";
query += "INNER JOIN Transmissions ON Vehicles.TransmissionId = Transmissions.TransmissionId ";
query += "INNER JOIN VehicleTypes ON Vehicles.VehicleTypeId = VehicleTypes.VehicleTypeId ";
query += "INNER JOIN BodyColors ON Vehicles.BodyColorId = BodyColors.BodyColorId INNER JOIN Models ON Vehicles.ModelId = Models.ModelId ";
query += "INNER JOIN Makes ON Makes.MakeId = Models.MakeId WHERE SaleId IS NULL AND 1 = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (!string.IsNullOrEmpty(parameters.NewUsed))
{
query += $"AND VehicleTypeDescription = @NewUsed ";
cmd.Parameters.AddWithValue("@NewUsed", parameters.NewUsed);
}
if (parameters.MinPrice.HasValue)
{
query += $"AND VehicleSalesPrice >= @MinPrice ";
cmd.Parameters.AddWithValue("@MinPrice", parameters.MinPrice.Value);
}
if (parameters.MaxPrice.HasValue)
{
query += $"AND VehicleSalesPrice <= @MaxPrice ";
cmd.Parameters.AddWithValue("@MaxPrice", parameters.MaxPrice.Value);
}
if (parameters.MinYear.HasValue)
{
query += $"AND VehicleYear >= @MinYear ";
cmd.Parameters.AddWithValue("@MinYear", parameters.MinYear.Value);
}
if (parameters.MaxYear.HasValue)
{
query += $"AND VehicleYear <= @MaxYear ";
cmd.Parameters.AddWithValue("@MaxYear", parameters.MaxYear.Value);
}
if (!string.IsNullOrEmpty(parameters.MakeModelYear) && int.TryParse(parameters.MakeModelYear, out int year))
{
query += $"AND VehicleYear = @Year ";
cmd.Parameters.AddWithValue("@Year", year);
}
else if (!string.IsNullOrEmpty(parameters.MakeModelYear))
{
query += $"AND (MakeDescription LIKE @MakeModelYear OR ModelDescription LIKE @MakeModelYear) ";
cmd.Parameters.AddWithValue("@MakeModelYear", parameters.MakeModelYear + '%');
}
query += "ORDER BY VehicleMSRP DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
VehicleSearchResult currentRow = new VehicleSearchResult();
currentRow.VehicleId = (int)dr["VehicleId"];
currentRow.VehicleYear = (int)dr["VehicleYear"];
currentRow.MakeDescription = dr["MakeDescription"].ToString();
currentRow.ModelDescription = dr["ModelDescription"].ToString();
currentRow.VehicleTypeDescription = dr["VehicleTypeDescription"].ToString();
currentRow.BodyStyleDescription = dr["BodyStyleDescription"].ToString();
currentRow.InteriorColorDescription = dr["InteriorColorDescription"].ToString();
currentRow.TransmissionDescription = dr["TransmissionDescription"].ToString();
currentRow.VehicleMileage = (int)dr["VehicleMileage"];
currentRow.BodyColorDescription = dr["BodyColorDescription"].ToString();
currentRow.VehicleVIN = dr["VehicleVIN"].ToString();
currentRow.VehicleSalesPrice = (decimal)dr["VehicleSalesPrice"];
currentRow.VehicleMSRP = (decimal)dr["VehicleMSRP"];
currentRow.VehiclePicture = dr["VehiclePicture"].ToString();
currentRow.VehicleDescription = dr["VehicleDescription"].ToString();
currentRow.VehicleIsFeatured = (bool)dr["VehicleIsFeatured"];
currentRow.VehicleSalesPriceFormatted = currentRow.VehicleSalesPrice.ToString("c0");
currentRow.VehicleMSRPFormatted = currentRow.VehicleMSRP.ToString("c0");
if (currentRow.VehicleMileage < 1000)
{
currentRow.VehicleMileageFormatted = "New";
}
else
{
currentRow.VehicleMileageFormatted = Convert.ToDecimal(currentRow.VehicleMileage).ToString("#,##0");
}
vehicles.Add(currentRow);
}
}
}
return(vehicles);
}
