public IHttpActionResult AllCarSearch(decimal?minPrice, decimal?maxPrice, int?minYear, int?maxYear, string searchTerm)
{
var repo = SalesFactory.GetRepo();
try
{
var parameters = new InventorySearchParameters()
{
SearchTerm = searchTerm,
MinPrice = minPrice,
MaxPrice = maxPrice,
MinYear = minYear,
MaxYear = maxYear
};
var result = repo.AllCarSearch(parameters);
if (result.Count == 0)
{
return(NotFound());
}
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public void GetUsedInventoryTest()
{
InventoryRepoEF repo = new InventoryRepoEF();
int total = 2;
var parameters = new InventorySearchParameters()
{
SearchTerm = "2012 Toyota Camry",
MinPrice = null,
MaxPrice = null,
MinYear = null,
MaxYear = null
};
var search = repo.NewCarSearch(parameters);
var list = new List <CarView>();
var dbList = _ctx.Cars.ToList();
foreach (var car in dbList)
{
list.Add(VehicleView.Car(car));
}
Assert.AreEqual(total, list.Count);
}
public List <Models.Tables.CarView> UsedCarSearch(InventorySearchParameters parameters)
{
var list = new List <Models.Tables.CarView>();
var newCars = _testVehicleList.Where(c => c.Mileage > 10000).ToList();
newCars = newCars.Take(20).ToList();
foreach (var car in newCars)
{
list.Add(car);
}
return(list);
}
public IHttpActionResult InventorySearch(string userId, DateTime?fromDate, DateTime?toDate)
{
var repo = SaleLogRepositoryFactory.GetSaleLogRepository();
try
{
var parameters = new InventorySearchParameters()
{
UserId = userId,
FromDate = fromDate,
ToDate = toDate
};
var result = repo.Search(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public List <UserSaleObject> Search(InventorySearchParameters parameters)
{
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
var par = new DynamicParameters();
string query = "SELECT FirstName + ' ' + LastName as [User], SUM(PurchasePrice) AS 'TotalSales', COUNT(SalesUserId) AS 'TotalVehicles' FROM SaleLog s INNER JOIN AspNetUsers u ON s.SalesUserId = u.Id WHERE 1=1 ";
if (!string.IsNullOrEmpty(parameters.UserId))
{
query += "AND u.Id=@UserId ";
par.Add("@UserId", parameters.UserId);
}
if (parameters.FromDate.HasValue && !parameters.ToDate.HasValue)
{
query += "AND PurchaseDate >= @FromDate ";
par.Add("@FromDate", parameters.FromDate);
}
if (!parameters.FromDate.HasValue && parameters.ToDate.HasValue)
{
query += "AND PurchaseDate <= @ToDate ";
par.Add("@ToDate", parameters.ToDate);
}
if (parameters.FromDate.HasValue && parameters.ToDate.HasValue)
{
query += "AND PurchaseDate BETWEEN @FromDate AND @ToDate ";
par.Add("@FromDate", parameters.FromDate);
par.Add("@ToDate", parameters.ToDate);
}
query += "GROUP BY FirstName, LastName";
return(cn.Query <UserSaleObject>(query, par, commandType: CommandType.Text).ToList());
}
}
public IHttpActionResult Search(decimal?minPrice, decimal?maxPrice, int?minYear, int?maxYear, string searchTextBox)
{
var repo = VehicleRepositoryFactory.GetRepository();
try
{
var parameters = new InventorySearchParameters()
{
MinPrice = minPrice,
MaxPrice = maxPrice,
MinYear = minYear,
MaxYear = maxYear,
SearchTextBox = searchTextBox
};
var result = repo.SearchSaleable(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IHttpActionResult Search(string quickSearch, decimal?minPrice, decimal?maxPrice, int?minYear, int?maxYear, int?newOrUsed)
{
var repo = VehicleRepositoryFactory.GetRepository();
try
{
var parameters = new InventorySearchParameters()
{
QuickSearch = quickSearch,
MinPrice = minPrice,
MaxPrice = maxPrice,
MinYear = minYear,
MaxYear = maxYear,
NewOrUsed = newOrUsed
};
var result = repo.Search(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public List <UserSaleObject> Search(InventorySearchParameters parameters)
{
var context = new ApplicationDbContext();
var allUsers = context.Users.ToList();
var list = new List <UserSaleObject>();
var query = from s in saleLogList
join u in allUsers on s.SalesUserId equals u.Id
group s by new
{
u.FirstName,
u.LastName,
} into grp
select new
{
User = grp.Key.FirstName + " " + grp.Key.LastName,
TotalVehicles = grp.Count(),
TotalSales = grp.Sum(s => s.PurchasePrice)
};
if (!string.IsNullOrEmpty(parameters.UserId) && !parameters.FromDate.HasValue && !parameters.ToDate.HasValue)
{
query = from s in saleLogList
join u in allUsers on s.SalesUserId equals u.Id
where u.Id == parameters.UserId
group s by new
{
u.FirstName,
u.LastName,
} into grp
select new
{
User = grp.Key.FirstName + " " + grp.Key.LastName,
TotalVehicles = grp.Count(),
TotalSales = grp.Sum(s => s.PurchasePrice)
};
}
else if (parameters.FromDate.HasValue && parameters.ToDate.HasValue && !string.IsNullOrEmpty(parameters.UserId))
{
query = from s in saleLogList
join u in allUsers on s.SalesUserId equals u.Id
where u.Id == parameters.UserId && s.PurchaseDate >= parameters.FromDate && s.PurchaseDate <= parameters.ToDate
group s by new
{
u.FirstName,
u.LastName,
} into grp
select new
{
User = grp.Key.FirstName + " " + grp.Key.LastName,
TotalVehicles = grp.Count(),
TotalSales = grp.Sum(s => s.PurchasePrice)
};
}
else if (!parameters.FromDate.HasValue && parameters.ToDate.HasValue && !string.IsNullOrEmpty(parameters.UserId))
{
query = from s in saleLogList
join u in allUsers on s.SalesUserId equals u.Id
where u.Id == parameters.UserId && s.PurchaseDate <= parameters.ToDate
group s by new
{
u.FirstName,
u.LastName,
} into grp
select new
{
User = grp.Key.FirstName + " " + grp.Key.LastName,
TotalVehicles = grp.Count(),
TotalSales = grp.Sum(s => s.PurchasePrice)
};
}
else if (parameters.FromDate.HasValue && !parameters.ToDate.HasValue && !string.IsNullOrEmpty(parameters.UserId))
{
query = from s in saleLogList
join u in allUsers on s.SalesUserId equals u.Id
where u.Id == parameters.UserId && s.PurchaseDate >= parameters.FromDate
group s by new
{
u.FirstName,
u.LastName,
} into grp
select new
{
User = grp.Key.FirstName + " " + grp.Key.LastName,
TotalVehicles = grp.Count(),
TotalSales = grp.Sum(s => s.PurchasePrice)
};
}
else if (parameters.FromDate.HasValue && !parameters.ToDate.HasValue && string.IsNullOrEmpty(parameters.UserId))
{
query = from s in saleLogList
join u in allUsers on s.SalesUserId equals u.Id
where s.PurchaseDate >= parameters.FromDate
group s by new
{
u.FirstName,
u.LastName,
} into grp
select new
{
User = grp.Key.FirstName + " " + grp.Key.LastName,
TotalVehicles = grp.Count(),
TotalSales = grp.Sum(s => s.PurchasePrice)
};
}
else if (!parameters.FromDate.HasValue && parameters.ToDate.HasValue && string.IsNullOrEmpty(parameters.UserId))
{
query = from s in saleLogList
join u in allUsers on s.SalesUserId equals u.Id
where s.PurchaseDate <= parameters.ToDate
group s by new
{
u.FirstName,
u.LastName,
} into grp
select new
{
User = grp.Key.FirstName + " " + grp.Key.LastName,
TotalVehicles = grp.Count(),
TotalSales = grp.Sum(s => s.PurchasePrice)
};
}
else if (parameters.FromDate.HasValue && parameters.ToDate.HasValue && string.IsNullOrEmpty(parameters.UserId))
{
query = from s in saleLogList
join u in allUsers on s.SalesUserId equals u.Id
where s.PurchaseDate >= parameters.FromDate && s.PurchaseDate <= parameters.ToDate
group s by new
{
u.FirstName,
u.LastName,
} into grp
select new
{
User = grp.Key.FirstName + " " + grp.Key.LastName,
TotalVehicles = grp.Count(),
TotalSales = grp.Sum(s => s.PurchasePrice)
};
}
foreach (var item in query)
{
var record = new UserSaleObject();
record.TotalSales = item.TotalSales;
record.TotalVehicles = item.TotalVehicles;
record.User = item.User;
list.Add(record);
}
return(list);
}
public List <CarView> AllCarSearch(InventorySearchParameters parameters)
{
return(_testVehicleList);
}
public List <CarView> AllCarSearch(InventorySearchParameters parameters)
{
var list = new List <CarView>();
var dbList = _ctx.Cars.ToList();
dbList = dbList.Where(c => c.IsPurchased == false).ToList();
if (string.IsNullOrEmpty(parameters.SearchTerm) && parameters.MaxPrice == null && parameters.MinPrice == null && parameters.MaxYear == null && parameters.MinYear == null && parameters.SearchTerm == null)
{
dbList = dbList.OrderByDescending(c => c.MSRP).ToList();
}
else
{
if (!string.IsNullOrEmpty(parameters.SearchTerm) && parameters.SearchTerm.Length > 2)
{
if (parameters.SearchTerm.Contains(" "))
{
var secondTerm = "";
var thirdTerm = "";
var splitTerm = parameters.SearchTerm.Split(' ');
var firstTerm = splitTerm[0];
if (splitTerm[1].Length > 0)
{
secondTerm = splitTerm[1];
if (splitTerm.Length > 2)
{
thirdTerm = splitTerm[2];
}
}
if (firstTerm != null)
{
dbList = dbList.Where(c =>
c.Year.ToString().Contains(firstTerm) ||
c.CarMake.MakeName.ToUpper().Contains(firstTerm.ToUpper()) ||
c.CarModel.ModelName.ToUpper().Contains(firstTerm.ToUpper())).ToList();
}
if (secondTerm != null)
{
dbList = dbList.Where(c =>
c.Year.ToString().Contains(secondTerm) ||
c.CarMake.MakeName.ToUpper().Contains(secondTerm.ToUpper()) ||
c.CarModel.ModelName.ToUpper().Contains(secondTerm.ToUpper())).ToList();
}
if (thirdTerm != null)
{
dbList = dbList.Where(c =>
c.Year.ToString().Contains(secondTerm) ||
c.CarMake.MakeName.ToUpper().Contains(secondTerm.ToUpper()) ||
c.CarModel.ModelName.ToUpper().Contains(secondTerm.ToUpper())).ToList();
}
}
}
if (!string.IsNullOrEmpty(parameters.SearchTerm) && !parameters.SearchTerm.Contains(" "))
{
dbList = dbList.Where(c =>
c.Year.ToString().Contains(parameters.SearchTerm.ToUpper()) ||
c.CarMake.MakeName.ToUpper().Contains(parameters.SearchTerm.ToUpper()) ||
c.CarModel.ModelName.ToUpper().Contains(parameters.SearchTerm.ToUpper())).ToList();
if (dbList.Count() == 0)
{
return(list);
}
else
{
}
}
if (parameters.MaxPrice != null)
{
dbList = dbList.Where(c => c.SalePrice < parameters.MaxPrice).ToList();
}
if (parameters.MinPrice != null)
{
dbList = dbList.Where(c => c.SalePrice > parameters.MinPrice).ToList();
}
if (parameters.MaxYear != null)
{
dbList = dbList.Where(c => c.Year <= parameters.MaxYear).ToList();
}
if (parameters.MinYear != null)
{
dbList = dbList.Where(c => c.Year >= parameters.MinYear).ToList();
}
}
dbList = dbList.Take(20).ToList();
foreach (var car in dbList)
{
list.Add(VehicleView.Car(car));
}
return(list);
}
public List <VehicleListItem> SearchSaleable(InventorySearchParameters parameters)
{
List <VehicleListItem> vehicles = new List <VehicleListItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT VehicleID, VehicleYear, mk.MakeName, md.ModelName, bs.BodyDescription, Price, Mileage, Vin, MSRP, VehicleType, ImageFileName, bs.BodyDescription, t.Gears, BodyColorName, InteriorColorName FROM Vehicle v ";
query += "INNER JOIN Make mk ON v.MakeID = mk.MakeID ";
query += "INNER JOIN Model md ON V.ModelID = md.ModelID ";
query += "INNER JOIN BodyStyle bs ON v.BodyStyleID = bs.BodyStyleID ";
query += "INNER JOIN Transmission t ON v.TransmissionID = t.TransmissionID ";
query += "INNER JOIN BodyColor bc ON v.BodyColorID = bc.BodyColorID ";
query += "INNER JOIN InteriorColor ic ON v.InteriorColorID = ic.InteriorColorID ";
query += "WHERE SaleStatus = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.MinPrice.HasValue)
{
query += "AND Price >= @MinPrice ";
cmd.Parameters.AddWithValue("@MinPrice", parameters.MinPrice.Value);
}
if (parameters.MaxPrice.HasValue)
{
query += "AND Price <= @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.SearchTextBox))
{
query += "AND (MakeName LIKE @MakeName ";
cmd.Parameters.AddWithValue("@MakeName", parameters.SearchTextBox + '%');
query += "OR ModelName LIKE @ModelName ";
cmd.Parameters.AddWithValue("@ModelName", parameters.SearchTextBox + '%');
query += "OR VehicleYear LIKE @VehicleYear) ";
cmd.Parameters.AddWithValue("@VehicleYear", parameters.SearchTextBox + '%');
}
query += "ORDER BY MSRP DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
VehicleListItem row = new VehicleListItem();
row.VehicleID = (int)dr["VehicleID"];
row.VehicleYear = (int)dr["VehicleYear"];
row.MakeName = dr["MakeName"].ToString();
row.ModelName = dr["ModelName"].ToString();
row.Price = (decimal)dr["Price"];
row.Mileage = (int)dr["Mileage"];
row.Vin = dr["Vin"].ToString();
row.MSRP = (decimal)dr["MSRP"];
row.ImageFileName = dr["ImageFileName"].ToString();
row.BodyDescription = dr["BodyDescription"].ToString();
row.Gears = dr["Gears"].ToString();
row.BodyColorName = dr["BodyColorName"].ToString();
row.InteriorColorName = dr["InteriorColorName"].ToString();
vehicles.Add(row);
}
}
}
return(vehicles);
}
public IEnumerable <VehicleDetailsItem> Search(InventorySearchParameters parameters)
{
List <VehicleDetailsItem> results = new List <VehicleDetailsItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT TOP 20 ID,Make,Model,[Year],BodyStyle,Transmission,Color,Interior,Mileage,VIN#,SalePrice,MSRP,ImageFileName,[Description]" +
" FROM Inventory i INNER JOIN Makes ma ON i.MakeID = ma.MakeID" +
" INNER JOIN Models mo ON i.ModelID = mo.ModelID" +
" INNER JOIN BodyStyles b ON i.BodyStyleID = b.BodyStyleID" +
" INNER JOIN Transmissions t ON i.TransmissionID = t.TransmissionID" +
" INNER JOIN Colors c ON i.ColorID = c.ColorID" +
" INNER JOIN Interiors ir ON i.InteriorID = ir.InteriorID" +
" WHERE i.PurchaseID IS NULL ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (!string.IsNullOrEmpty(parameters.QuickSearch))
{
query += $"AND (Make LIKE '%' + @QuickSearch + '%' OR Model LIKE '%' + @QuickSearch + '%' OR [Year] LIKE @QuickSearch + '%') ";
cmd.Parameters.AddWithValue("@QuickSearch", parameters.QuickSearch);
}
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 (parameters.NewOrUsed.HasValue)
{
query += $"AND TypeID = @NewOrUsed ";
cmd.Parameters.AddWithValue("@NewOrUsed", parameters.NewOrUsed.Value);
}
query += "ORDER BY MSRP DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
VehicleDetailsItem vehicle = new VehicleDetailsItem();
vehicle.ID = (int)dr["ID"];
vehicle.Make = dr["Make"].ToString();
vehicle.Model = dr["Model"].ToString();
vehicle.Year = (int)dr["Year"];
vehicle.BodyStyle = dr["BodyStyle"].ToString();
vehicle.Transmission = dr["Transmission"].ToString();
vehicle.Color = dr["Color"].ToString();
vehicle.Interior = dr["Interior"].ToString();
vehicle.Mileage = (int)dr["Mileage"];
vehicle.VIN = dr["VIN#"].ToString();
vehicle.SalePrice = (decimal)dr["SalePrice"];
vehicle.MSRP = (decimal)dr["MSRP"];
vehicle.Description = dr["Description"].ToString();
if (dr["ImageFileName"] != DBNull.Value)
{
vehicle.ImageFileName = dr["ImageFileName"].ToString();
}
results.Add(vehicle);
}
}
}
return(results);
}
