public IHttpActionResult Search(decimal?minPrice, decimal?maxPrice, decimal?minYear, decimal?maxYear, string makeName = null, string modelName = null, string year = null)
{
var repo = new VehicleRepositoryADO();
try
{
var parameters = new VehicleSearchParams()
{
MinPrice = minPrice,
MaxPrice = maxPrice,
MakeName = makeName,
ModelName = modelName,
Year = year,
MinYear = minYear,
MaxYear = maxYear
};
var result = repo.Search(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public Task <List <Vehicle> > List(VehicleSearchParams searchParams)
{
var taskResult = Task.Run(() =>
{
using (var context = new DbContext())
{
var ctx = (from msf in context.MapsSocietiesToFacilities
join f in context.Facilities on msf.FacilityId equals f.Id
join flr in context.Floors on f.Id equals flr.FacilityId
join fls in context.Flats on flr.Id equals fls.FloorId
join mffo in context.MapsFlatToFlatOwner on fls.Id equals mffo.FlatId
join fo in context.FlatOwners on mffo.FlatOwnerId equals fo.Id
join v in context.Vehicles on new { k1 = fo.Id, k2 = fls.Id } equals new { k1 = v.FlatOwnerId, k2 = v.FlatId } into result
from r in result.DefaultIfEmpty()
where msf.SocietyId == searchParams.SocietyId && r != null
select r);
if (searchParams.FlatId.HasValue)
{
ctx = ctx.Where(b => b.FlatId == searchParams.FlatId.Value);
}
if (searchParams.FlatOwnerId.HasValue)
{
ctx = ctx.Where(b => b.FlatOwnerId == searchParams.FlatOwnerId.Value);
}
return(ctx
.Include(s => s.FlatOwner)
.Include(f => f.FlatOwner.Gender)
.Include(s => s.Flat)
.Include(s => s.Approvals)
.Include(s => s.Comments)
.ToList());
}
});
return(taskResult);
}
public Task <List <Vehicle> > List(VehicleSearchParams searchParams)
{
return(_vehicleRepository.List(searchParams));
}
public IEnumerable <SearchDisplay> Search(VehicleSearchParams parameters)
{
List <SearchDisplay> vehicles = new List <SearchDisplay>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT VehicleID, VIN, Vehicle.ModelID, Model.MakeID, Make.MakeName, Model.ModelName, " +
"Vehicle.ConditionID, ConditionName, Year, Vehicle.VehicleTypeID, VehicleType.VehicleTypeName, " +
"Vehicle.TransmissionTypeID, TransmissionTypeName, Vehicle.CarColor, Color.ColorName AS CarColorName, " +
"Vehicle.Interior, (SELECT ColorName FROM Color WHERE Vehicle.Interior = Color.ColorID) AS InteriorColorName, " +
"Mileage, MSRP, SalesPrice, Picture, Featured " +
"FROM Vehicle " +
"INNER JOIN Model ON Vehicle.ModelID = Model.ModelID " +
"INNER JOIN Make ON Model.MakeID = Make.MakeID " +
"INNER JOIN Condition ON Vehicle.ConditionID = Condition.ConditionID " +
"INNER JOIN VehicleType ON Vehicle.VehicleTypeID = VehicleType.VehicleTypeID " +
"INNER JOIN TransmissionType ON Vehicle.TransmissionTypeID = TransmissionType.TransmissionTypeID " +
"INNER JOIN Color ON Vehicle.CarColor = Color.ColorID " +
"WHERE 1 = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.MinPrice.HasValue)
{
query += "AND SalesPrice >= @MinPrice ";
cmd.Parameters.AddWithValue("@MinPrice", parameters.MinPrice.Value);
}
if (parameters.MaxPrice.HasValue)
{
query += "AND SalesPrice <= @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.ModelName))
{
query += "AND ModelName LIKE @ModelName ";
cmd.Parameters.AddWithValue("@ModelName", '%' + parameters.ModelName + '%');
}
if (!string.IsNullOrEmpty(parameters.MakeName))
{
query += "AND MakeName LIKE @MakeName ";
cmd.Parameters.AddWithValue("@MakeName", '%' + parameters.MakeName + '%');
}
if (!string.IsNullOrEmpty(parameters.Year))
{
query += "AND Year LIKE @Year ";
cmd.Parameters.AddWithValue("@Year", '%' + parameters.Year + '%');
}
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
SearchDisplay row = new SearchDisplay();
row.VehicleID = (int)dr["VehicleID"];
row.VIN = dr["VIN"].ToString();
row.ModelID = (int)dr["ModelID"];
row.ModelName = dr["ModelName"].ToString();
row.MakeName = dr["MakeName"].ToString();
row.ConditionID = (int)dr["ConditionID"];
row.ConditionName = dr["ConditionName"].ToString();
row.VehicleTypeID = (int)dr["VehicleTypeID"];
row.VehicleTypeName = dr["VehicleTypeName"].ToString();
row.Year = dr["Year"].ToString();
row.TransmissionTypeID = (int)dr["TransmissionTypeID"];
row.TransmissionTypeName = dr["TransmissionTypeName"].ToString();
row.CarColor = (int)dr["CarColor"];
row.CarColorName = dr["CarColorName"].ToString();
row.Interior = (int)dr["Interior"];
row.InteriorColorName = dr["InteriorColorName"].ToString();
row.Mileage = dr["Mileage"].ToString();
row.MSRP = (decimal)dr["MSRP"];
row.SalesPrice = (decimal)dr["SalesPrice"];
//row.Description = dr["Description"].ToString();
row.Picture = dr["Picture"].ToString();
//row.Featured = (bool)dr["Featured"];
vehicles.Add(row);
}
}
}
return(vehicles);
}
