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 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 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);
}