public IHttpActionResult Unsold(decimal?minPrice = null, decimal?maxPrice = null, int?minYear = null, int?maxYear = null, string makeModelOrYear = null)
{
IVehicleDetailRepository repo = VehicleDetailRepositoryFactory.GetRepository();
try
{
VehicleSearchParametersModel parameters = new VehicleSearchParametersModel()
{
MinPrice = minPrice,
MaxPrice = maxPrice,
MinYear = minYear,
MaxYear = maxYear,
MakeModelOrYear = makeModelOrYear
};
var result = repo.SearchUnsold(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <VehicleDetailModel> SearchNew(VehicleSearchParametersModel parameters)
{
IEnumerable <VehicleDetailModel> vehicles = _vehicles.FindAll(v => v.New.Equals(true) && v.Sold.Equals(false));
IEnumerable <VehicleDetailModel> minPriceVehicles;
IEnumerable <VehicleDetailModel> maxPriceVehicles;
IEnumerable <VehicleDetailModel> minYearVehicles;
IEnumerable <VehicleDetailModel> maxYearVehicles;
IEnumerable <VehicleDetailModel> makeModelOrYearVehicles;
IEnumerable <VehicleDetailModel> searchResults;
if (parameters.MinPrice.HasValue)
{
decimal minPrice = parameters.MinPrice ?? 0;
minPriceVehicles = from v in vehicles
where Decimal.Round(minPrice, 2) <= Decimal.Round(parameters.MinPrice.Value)
select v;
}
else
{
minPriceVehicles = vehicles;
};
if (parameters.MaxPrice.HasValue)
{
decimal maxPrice = parameters.MaxPrice ?? 0;
maxPriceVehicles = from v in minPriceVehicles
where Decimal.Round(maxPrice, 2) >= Decimal.Round(parameters.MaxPrice.Value)
select v;
}
else
{
maxPriceVehicles = minPriceVehicles;
};
if (parameters.MinYear.HasValue)
{
minYearVehicles = from v in maxPriceVehicles
where v.Year >= parameters.MinYear
select v;
}
else
{
minYearVehicles = maxPriceVehicles;
};
if (parameters.MaxYear.HasValue)
{
maxYearVehicles = from v in minYearVehicles
where v.Year <= parameters.MaxYear
select v;
}
else
{
maxYearVehicles = minYearVehicles;
};
if (!string.IsNullOrEmpty(parameters.MakeModelOrYear))
{
bool successfullyParsed = int.TryParse(parameters.MakeModelOrYear, out int year);
if (successfullyParsed)
{
makeModelOrYearVehicles = from v in maxYearVehicles
where v.Year == year
select v;
searchResults = makeModelOrYearVehicles;
}
else
{
searchResults = maxYearVehicles;
};
}
else
{
searchResults = maxYearVehicles;
};
List <VehicleDetailModel> sortedResults = searchResults.OrderBy(v => v.MSRP).Take(20).ToList();
return(sortedResults);
}
public IEnumerable <VehicleDetailModel> SearchUnsold(VehicleSearchParametersModel parameters)
{
List <VehicleDetailModel> _vehicles = new List <VehicleDetailModel>();
using (var conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
string query = "SELECT TOP 20 v.VehicleID, Make, Model, Year, Mileage, VIN, IntC.Color as IntColor, ExtC.Color as ExtColor, MSRP, SalePrice, Featured, Description, Sold, New, Type, BodyStyle, Image FROM Vehicle v " +
" JOIN Make mk on v.MakeID = mk.MakeID" +
" JOIN Model ml on v.ModelID = ml.ModelID" +
" JOIN Color intC on v.IntColorID = intC.ColorID JOIN Color extC on v.ExtColorID = extC.ColorID" +
" JOIN TransmissionType t on v.TransTypeID = t.TransTypeID" +
" JOIN BodyStyle b on v.BodyStyleID = b.BodyStyleID" +
" WHERE Sold = 0";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
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.MakeModelOrYear))
{
query += "AND (Year LIKE @MakeModelOrYear OR Make LIKE @MakeModelOrYear OR Model LIKE @MakeModelOrYear)";
cmd.Parameters.AddWithValue("@MakeModelOrYear", parameters.MakeModelOrYear);
}
query += "ORDER BY MSRP DESC";
cmd.CommandText = query;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
VehicleDetailModel row = new VehicleDetailModel();
row.VehicleID = (int)reader["VehicleID"];
row.MakeName = reader["Make"].ToString();
row.ModelName = reader["Model"].ToString();
row.Year = (int)reader["Year"];
row.Mileage = (int)reader["Mileage"];
row.VIN = reader["VIN"].ToString();
row.IntColor = reader["IntColor"].ToString();
row.ExtColor = reader["ExtColor"].ToString();
row.MSRP = (decimal)reader["MSRP"];
row.Price = (decimal)reader["SalePrice"];
row.Featured = (bool)reader["Featured"];
row.Sold = (bool)reader["Sold"];
row.New = (bool)reader["New"];
row.TransType = reader["Type"].ToString();
row.BodyStyle = reader["BodyStyle"].ToString();
row.Image = reader["Image"].ToString();
_vehicles.Add(row);
}
}
}
return(_vehicles);
}
