public List <Ride> GetRidesBasedOnCategory(string categoryName)
{
string sql = $"SELECT * FROM Rides JOIN RideCategories ON RideCategories.RideCategoryId = Rides.CategoryId WHERE RideCategories.Name = '{categoryName}'";
DataTable ridesTable = ExecuteQuery(sql);
List <Ride> rides = new List <Ride>();
CategoryRepository categoryRepo = new CategoryRepository();
foreach (DataRow row in ridesTable.Rows)
{
int id = (int)row["RideId"];
string name = (string)row["Name"];
string imgUrl = (string)row["ImgUrl"];
string description = (string)row["Description"];
int categoryId = (int)row["CategoryId"];
Ride ride = new Ride()
{
Id = id,
Name = name,
ImgUrl = imgUrl,
Description = description,
Category = categoryRepo.GetCategory(categoryId)
};
rides.Add(ride);
}
ReportRepository reportRepository = new ReportRepository();
foreach (Ride ride in rides)
{
ride.Reports = reportRepository.GetReportsFor(ride.Id);
}
return(rides);
}
public Ride GetById(int id)
{
CategoryRepository categoryRepo = new CategoryRepository();
ReportRepository reportRepository = new ReportRepository();
string sql = $"SELECT * FROM Rides WHERE RideId = {id}";
DataTable rideTable = ExecuteQuery(sql);
DataRow row = rideTable.Rows[0];
Ride ride = new Ride()
{
Id = (int)row["RideId"],
Name = (string)row["Name"],
ImgUrl = (string)row["ImgUrl"],
Description = (string)row["Description"],
Category = categoryRepo.GetCategory((int)row["CategoryId"]),
Reports = reportRepository.GetReportsFor((int)row["RideId"])
};
return(ride);
}
public Ride GetLatestBrokenRide()
{
CategoryRepository categoryRepo = new CategoryRepository();
ReportRepository reportRepository = new ReportRepository();
string sql = "SELECT TOP(1) * FROM Reports JOIN Rides ON Reports.RideId = Rides.RideId WHERE Status = 2 ORDER BY ReportTime DESC";
DataTable rideTable = ExecuteQuery(sql);
DataRow row = rideTable.Rows[0];
Ride ride = new Ride()
{
Id = (int)row["RideId"],
Name = (string)row["Name"],
ImgUrl = (string)row["ImgUrl"],
Description = (string)row["Description"],
Category = categoryRepo.GetCategory((int)row["CategoryId"]),
Reports = reportRepository.GetReportsFor((int)row["RideId"])
};
return(ride);
}
public (Ride ride, int timesBroken) GetMostBrokenRide()
{
CategoryRepository categoryRepo = new CategoryRepository();
ReportRepository reportRepository = new ReportRepository();
string sql = $"SELECT TOP(1) COUNT(status) AS TimesBroken, Rides.Name, Rides.RideId, ImgUrl, Description, CategoryId FROM Rides JOIN Reports ON Reports.RideId = Rides.RideId WHERE Status = 2 GROUP BY Rides.Name, Rides.RideId, ImgUrl, Description, CategoryId ";
DataTable rideTable = ExecuteQuery(sql);
DataRow row = rideTable.Rows[0];
int timesBroken = (int)row["TimesBroken"];
Ride ride = new Ride()
{
Id = (int)row["RideId"],
Name = (string)row["Name"],
ImgUrl = (string)row["ImgUrl"],
Description = (string)row["Description"],
Category = categoryRepo.GetCategory((int)row["CategoryId"]),
Reports = reportRepository.GetReportsFor((int)row["RideId"])
};
return(ride, timesBroken);
}
