public IHttpActionResult SalesSearch(decimal?salesMinPrice, decimal?salesMaxPrice, string salesMinYear, string salesMaxYear, string salesMakeName, string salesModelName, string salesYear)
{
var repo = VehicleRepositoryFactory.GetRepository();
try
{
var parameters = new SalesVehicleSearchParameters()
{
SalesMinPrice = salesMinPrice,
SalesMaxPrice = salesMaxPrice,
SalesMinYear = salesMinYear,
SalesMaxYear = salesMaxYear,
SalesMakeName = salesMakeName,
SalesModelName = salesModelName,
SalesYear = salesYear
};
var result = repo.SalesSearch(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <VehicleShortItem> SalesSearch(SalesVehicleSearchParameters 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, " +
"IsSold, ImageFileName, MakeName, ModelName FROM Vehicle INNER JOIN Make ON Vehicle.MakeId = Make.MakeId INNER JOIN Model ON " +
"Vehicle.ModelId = Model.ModelId WHERE IsSold=0 AND 1 = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.SalesMinPrice.HasValue)
{
query += "AND Price >= @SalesMinPrice ";
cmd.Parameters.AddWithValue("@SalesMinPrice", parameters.SalesMinPrice.Value);
}
if (parameters.SalesMaxPrice.HasValue)
{
query += "AND Price <= @SalesMaxPrice ";
cmd.Parameters.AddWithValue("@SalesMaxPrice", parameters.SalesMaxPrice.Value);
}
if (!string.IsNullOrEmpty(parameters.SalesMinYear))
{
query += "AND Year >= @SalesMinYear ";
cmd.Parameters.AddWithValue("@SalesMinYear", parameters.SalesMinYear);
}
if (!string.IsNullOrEmpty(parameters.SalesMaxYear))
{
query += "AND Year <= @SalesMaxYear ";
cmd.Parameters.AddWithValue("@SalesMaxYear", parameters.SalesMaxYear);
}
if (!string.IsNullOrEmpty(parameters.SalesMakeName))
{
query += "AND MakeName = @SalesMakeName ";
cmd.Parameters.AddWithValue("@SalesMakeName", parameters.SalesMakeName);
}
if (!string.IsNullOrEmpty(parameters.SalesModelName))
{
query += "AND ModelName = @SalesModelName ";
cmd.Parameters.AddWithValue("@SalesModelName", parameters.SalesModelName);
}
if (!string.IsNullOrEmpty(parameters.SalesYear))
{
query += "AND Year = @SalesYear ";
cmd.Parameters.AddWithValue("@SalesYear", parameters.SalesYear);
}
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.IsSold = (bool)dr["IsSold"];
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);
}