public IEnumerable <CarViewModel> Get(CarSearchQueryViewModel queryViewModel)
{
var query = new CarSearchQuery()
{
FromEnginePower = queryViewModel.FromEnginePower,
FromLuggageCapacity = queryViewModel.FromLuggageCapacity,
FromProduction = queryViewModel.FromProduction,
Gearbox = queryViewModel.Gearbox,
MakeId = queryViewModel.MakeId,
MaxSeatsCount = queryViewModel.MaxSeatsCount,
MinSeatsCount = queryViewModel.MinSeatsCount,
ModelId = queryViewModel.ModelId,
ToEnginePower = queryViewModel.ToEnginePower,
ToLuggageCapacity = queryViewModel.ToLuggageCapacity,
ToProduction = queryViewModel.ToProduction
};
return(warehouseService.GetCars(query)
.Select(model => new CarViewModel()
{
Id = model.Id,
ModelId = model.ModelId,
MakeId = model.MakeId,
Description = model.Description,
EnginePower = model.EnginePower,
Gearbox = model.Gearbox,
LuggageCapacity = model.LuggageCapacity,
Photo = model.Photo,
Production = model.Production,
SeatsCount = model.SeatsCount
}));
}
public IEnumerable <Car> GetCars(CarSearchQuery searchQuery)
{
var query = new DBContract.CarSearchQuery()
{
FromEnginePower = searchQuery.FromEnginePower,
FromLuggageCapacity = searchQuery.FromLuggageCapacity,
FromProduction = searchQuery.FromProduction,
Gearbox = searchQuery.Gearbox,
MakeId = searchQuery.MakeId,
MaxSeatsCount = searchQuery.MaxSeatsCount,
MinSeatsCount = searchQuery.MinSeatsCount,
ModelId = searchQuery.ModelId,
ToEnginePower = searchQuery.ToEnginePower,
ToLuggageCapacity = searchQuery.ToLuggageCapacity,
ToProduction = searchQuery.ToProduction
};
return(carRepository.GetCarsByQuery(query)
.Select(model => new Car()
{
Id = model.Id,
ModelId = model.ModelId,
MakeId = model.MakeId,
Description = model.Description,
EnginePower = model.EnginePower,
Gearbox = model.Gearbox,
LuggageCapacity = model.LuggageCapacity,
Photo = model.Photo,
Production = model.Production,
SeatsCount = model.SeatsCount
}));
}
public QueryResult <CarModelWithDrivers> Where(CarSearchQuery searchQuery)
{
var result = new QueryResult <CarModelWithDrivers>(_carRep.All.Count());
bool hasModel = !string.IsNullOrWhiteSpace(searchQuery.Model),
hasColor = !string.IsNullOrWhiteSpace(searchQuery.Color),
hasDriverName = !string.IsNullOrWhiteSpace(searchQuery.DriverName),
hasGarageNumber = !string.IsNullOrWhiteSpace(searchQuery.GarageNumber),
hasGovermentNumber = !string.IsNullOrWhiteSpace(searchQuery.GovermentNumber);
var query = _carRep.Where(c => !searchQuery.YearFrom.HasValue || c.Year >= searchQuery.YearFrom,
c => !searchQuery.YearTo.HasValue || c.Year <= searchQuery.YearTo,
c => !searchQuery.Status.HasValue || c.Status == searchQuery.Status,
c => !hasModel || c.Model.ToLower().Contains(searchQuery.Model.ToLower()),
c => !hasGovermentNumber ||
c.GovermentNumber.ToLower().Contains(searchQuery.GovermentNumber.ToLower()),
c => !hasGarageNumber || c.GarageNumber.ToString().Contains(searchQuery.GarageNumber),
c => !hasDriverName ||
c.Drivers.Any(d => (d.FirstName + " " + d.LastName + " " + d.Patronymic).
ToLower().Contains(searchQuery.DriverName.ToLower())) ||
c.Drivers.Any(d => (d.LastName + " " + d.FirstName).ToLower().
Contains(searchQuery.DriverName.ToLower())),
c => !hasColor || c.Color.ToLower().Contains(searchQuery.Color.ToLower()));
result.TotalFiltered = query.Count();
query = query.OrderBy(searchQuery.OrderBy.ToString())
.Skip(searchQuery.Paging.Skip)
.Take(searchQuery.Paging.Length);
result.Data = query.ProjectTo <CarModelWithDrivers>().ToList();
result.Paging = searchQuery.Paging;
return(result);
}
public IEnumerable <Car> GetCarsByQuery(CarSearchQuery query)
{
var sqlQuery = searchCarQuery;
List <string> conditions = new List <string>
{
query.FromEnginePower != null ? $"[EnginePower] >= {query.FromEnginePower}" : string.Empty,
query.ToEnginePower != null ? $"[EnginePower] <= {query.ToEnginePower}" : string.Empty,
query.FromLuggageCapacity != null ? $"[LuggageCapacity] >= {query.FromLuggageCapacity}" : string.Empty,
query.ToLuggageCapacity != null ? $"[LuggageCapacity] <= {query.ToLuggageCapacity}" : string.Empty,
query.FromProduction != null ? $"[Production] >= {query.FromProduction}" : string.Empty,
query.ToProduction != null ? $"[Production] <= {query.ToProduction}" : string.Empty,
query.MinSeatsCount != null ? $"[SeatsCount] >= {query.MinSeatsCount}" : string.Empty,
query.MaxSeatsCount != null ? $"[SeatsCount] <= {query.MaxSeatsCount}" : string.Empty,
query.MakeId != null ? $"[MakeId] = '{query.MakeId}'" : string.Empty,
query.ModelId != null ? $"[ModelId] = '{query.ModelId}'" : string.Empty,
//query.Gearbox.Count() > 0 ? $"[Gearbox] IN ({String.Join(',', query.Gearbox.Select(x => x.ToString()))})" : string.Empty
};
conditions = conditions.Where(subquery => subquery != string.Empty).ToList();
if (conditions.Count > 0)
{
sqlQuery += " WHERE " + String.Join(" AND ", conditions);
}
using (var connection = new SqlConnection(connectionString.CommonDatabase))
{
return(connection.Query <Car>(sqlQuery));
}
}
