public ActionResult Details(int id)
{
var repo = InventoryRepositoryFactory.GetRepository();
VehicleSearchResult model = repo.GetVehicleDetails(id);
TempData["VIN"] = model.VehicleVIN;
return(View(model));
}
public VehicleSearchResult GetVehicleDetails(int vehicleId)
{
VehicleSearchResult vehicle = null;
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetVehicleDetails", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@VehicleId", vehicleId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
vehicle = new VehicleSearchResult();
vehicle.VehicleId = (int)vehicleId;
vehicle.VehicleYear = (int)dr["VehicleYear"];
vehicle.MakeDescription = dr["MakeDescription"].ToString();
vehicle.ModelDescription = dr["ModelDescription"].ToString();
vehicle.BodyStyleDescription = dr["BodyStyleDescription"].ToString();
vehicle.InteriorColorDescription = dr["InteriorColorDescription"].ToString();
vehicle.TransmissionDescription = dr["TransmissionDescription"].ToString();
vehicle.VehicleMileage = (int)dr["VehicleMileage"];
vehicle.BodyColorDescription = dr["BodyColorDescription"].ToString();
vehicle.VehicleVIN = dr["VehicleVIN"].ToString();
vehicle.VehicleSalesPrice = (decimal)dr["VehicleSalesPrice"];
vehicle.VehicleMSRP = (decimal)dr["VehicleMSRP"];
vehicle.VehiclePicture = dr["VehiclePicture"].ToString();
vehicle.VehicleDescription = dr["VehicleDescription"].ToString();
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");
}
vehicle.VehicleIsSold = IsSold(vehicle.VehicleId);
}
}
}
return(vehicle);
}
public async Task <VehicleSearchResult> SearchAsync(string searchText, string filter = null, SearchOptions searchOptions = null)
{
//NOTE: auto cast from SearchResult<VehicleDocument> to VehicleSearchResult did not work
//return await _vehicleSearchRepositoryService.SearchAsync(searchText, filter, searchOptions) as VehicleSearchResult;
var result = await _vehicleSearchRepositoryService.SearchAsync(searchText, filter, searchOptions);
VehicleSearchResult vehicleSearchResult = new VehicleSearchResult()
{
Documents = result.Documents,
Facets = result.Facets,
TotalCount = result.TotalCount
};
return(vehicleSearchResult);
}
public VehicleSearchResult GetVehicleDetails(int vehicleId)
{
VehicleSearchResult vehicle = _Vehicles.FirstOrDefault(v => v.VehicleId == vehicleId);
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");
}
vehicle.VehicleIsSold = IsSold(vehicle.VehicleId);
return(vehicle);
}
public async Task <ActionResult> Index(string brand, string model, string plate, VehiclesServiceInterfaces.VehicleState?state, int pageIndex = 0, int pageSize = 10)
{
var vehicles =
await this.vehiclesServiceProxy.SearchVehiclesAsync(plate, model, brand, state, default(CancellationToken));
var count = vehicles.Count();
vehicles = vehicles.OrderBy(v => v.Plate);
if (pageIndex >= 0 && pageSize > 0)
{
vehicles = vehicles.Skip(pageIndex * pageSize).Take(pageSize);
}
var result = new VehicleSearchResult(vehicles, count, pageIndex, pageSize)
{
BrandFilter = brand,
ModelFilter = model,
PlateFilter = plate,
StateFilter = state
};
return(View(result));
}
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);
}
public VehicleSearchViewModel Map(VehicleSearchResult source)
{
Guid guid;
var vehicleSearchViewModel = new VehicleSearchViewModel()
{
Facets = new VehicleSearchFacets()
{
Regions = source.Facets.Any(f => f.Name == "regionName") ?
source.Facets.First(f => f.Name == "regionName")
.Value.Select(item => ((SimpleValue)item).Value.ToString()).OrderBy(item => item)
.ToArray() : default(string[]),
VehicleTypeGroups = source.Facets.Any(f => f.Name == "vehicleTypeGroupName") ?
source.Facets.First(f => f.Name == "vehicleTypeGroupName")
.Value.Select(item => ((SimpleValue)item).Value.ToString()).OrderBy(item => item)
.ToArray() : default(string[]),
VehicleTypes = source.Facets.Any(f => f.Name == "vehicleTypeName") ?
source.Facets.First(f => f.Name == "vehicleTypeName")
.Value.Select(item => ((SimpleValue)item).Value.ToString()).OrderBy(item => item)
.ToArray() : default(string[]),
Years = source.Facets.Any(f => f.Name == "yearId") ?
source.Facets.First(f => f.Name == "yearId")
.Value.Select(item => ((SimpleValue)item).Value.ToString()).OrderBy(item => item)
.ToArray() : default(string[]),
Makes = source.Facets.Any(f => f.Name == "makeName") ?
source.Facets.First(f => f.Name == "makeName")
.Value.Select(item => ((SimpleValue)item).Value.ToString()).OrderBy(item => item)
.ToArray() : default(string[]),
Models = source.Facets.Any(f => f.Name == "modelName") ?
source.Facets.First(f => f.Name == "modelName")
.Value.Select(item => ((SimpleValue)item).Value.ToString()).OrderBy(item => item)
.ToArray() : default(string[]),
SubModels = source.Facets.Any(f => f.Name == "subModelName") ?
source.Facets.First(f => f.Name == "subModelName")
.Value.Select(item => ((SimpleValue)item).Value.ToString()).OrderBy(item => item)
.ToArray() : default(string[]),
},
Result = new VehicleSearchResultViewModel
{
//NOTE: VehicleId of Guid means new base vehicle without vehicle information
Vehicles = source.Documents.Where(item => !Guid.TryParse(item.VehicleId, out guid)).Select(item => new VehicleViewModel
{
BaseVehicleId = item.BaseVehicleId ?? 0,
Id = Convert.ToInt32(item.VehicleId),
MakeId = item.MakeId ?? 0,
MakeName = item.MakeName,
ModelId = item.ModelId ?? 0,
ModelName = item.ModelName,
RegionId = item.RegionId ?? 0,
RegionName = item.RegionName,
SourceName = item.Source,
SubModelId = item.SubModelId ?? 0,
SubModelName = item.SubModelName,
YearId = item.YearId ?? 0,
ChangeRequestId = item.VehicleChangeRequestId
}).ToList(),
BaseVehicles = source.Documents.Distinct(new DistinctBaseVehicleIdComparer()).Select(item => new BaseVehicleViewModel
{
Id = item.BaseVehicleId ?? 0,
MakeId = item.MakeId ?? 0,
MakeName = item.MakeName,
ModelId = item.ModelId ?? 0,
ModelName = item.ModelName,
YearId = item.YearId ?? 0,
ChangeRequestId = item.BaseVehicleChangeRequestId
}).ToList(),
},
TotalCount = source.TotalCount != null && source.TotalCount > 1000 ? source.TotalCount : null,
};
return(vehicleSearchViewModel);
}
