public IList <CampgroundModel> GetCampgrounds(int park_Id)
{
List <CampgroundModel> campgrounds = new List <CampgroundModel>();
try
{
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select * from campground where park_id = @park_id", conn);
cmd.Parameters.AddWithValue("@park_id", park_Id);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
CampgroundModel campground = ConvertReaderToCampground(reader);
campgrounds.Add(campground);
}
}
}
catch (SqlException ex)
{
Console.WriteLine("Error occurred reading campgrounds from database.");
Console.WriteLine(ex.Message);
throw;
}
return(campgrounds);
}
public void GetAvailableReservations_Returns_0_Sites()
{
CampsiteSqlDAO dao = new CampsiteSqlDAO(base.ConnectionString);
CampgroundModel campground = new CampgroundModel();
campground.Campground_Id = base.CampgroundId;
IList <CampsiteModel> availableSites = dao.GetAvailableReservations(campground, new DateTime(2019, 01, 01), new DateTime(2019, 02, 05));
Assert.AreEqual(Math.Min(base.CampsiteCount, 5), availableSites.Count);
}
private CampgroundModel ConvertReaderToCampground(SqlDataReader reader)
{
CampgroundModel campground = new CampgroundModel();
campground.Campground_Id = Convert.ToInt32(reader["campground_Id"]);
campground.Park_Id = Convert.ToInt32(reader["park_Id"]);
campground.Name = Convert.ToString(reader["name"]);
campground.Open_From_MM = Convert.ToInt32(reader["open_from_mm"]);
campground.Open_To_MM = Convert.ToInt32(reader["open_to_mm"]);
campground.Daily_Fee = Convert.ToDecimal(reader["daily_fee"]);
return(campground);
}
public void IsOpen_ShouldReturn_True_If_Campground_IsOpen(int startMonth, int endMonth, bool expected)
{
DateTime startDate = new DateTime(2019, startMonth, 1);
DateTime endDate = new DateTime(2019, endMonth, 1);
CampgroundSqlDAO dao = new CampgroundSqlDAO(base.ConnectionString);
CampgroundModel campground = new CampgroundModel();
campground.Open_From_MM = base.NewOpenFrom;
campground.Open_To_MM = base.NewOpenTo;
bool isOpen = dao.IsOpen(campground, startDate, endDate);
Assert.AreEqual(expected, isOpen);
}
public IList <CampsiteModel> GetAvailableReservations(CampgroundModel campground, DateTime fromDate, DateTime toDate)
{
// Start out by getting all reservations for the campsites at that campground
IList <CampsiteModel> availableReservations = GetCampsites(campground.Campground_Id);
try
{
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
//This SQL returns all reservations conflicting with requested dates
SqlCommand cmd = new SqlCommand("select * from site s join reservation r on s.site_id = r.site_id where from_date between @fromDate and @toDate or to_date between @fromDate and @toDate", conn);
cmd.Parameters.AddWithValue("@fromDate", fromDate);
cmd.Parameters.AddWithValue("@toDate", toDate);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
CampsiteModel campsite = ConvertReaderToCampsite(reader);
// So if the possible reservation conflicts, remove it.
if (availableReservations.Contains(campsite))
{
availableReservations.Remove(campsite);
}
}
// And only display the first 5 reservations, at most
while (availableReservations.Count > 5)
{
availableReservations.RemoveAt(5);
}
}
}
catch (SqlException ex)
{
Console.WriteLine("There was an error getting available reservations.");
Console.WriteLine(ex.Message);
throw;
}
return(availableReservations);
}
public List <CampgroundModel> CampgroundsInPark(string parkID)
{
List <CampgroundModel> campgroundsPerPark = new List <CampgroundModel>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(SQL_CampgroundsInPark, conn);
cmd.Parameters.AddWithValue("@PARK_ID", parkID);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
CampgroundModel nc = new CampgroundModel();
nc.CampgroundId = Convert.ToInt32(reader["campground_id"]);
nc.ParkId = Convert.ToInt32(reader["park_id"]);
nc.CampgroundName = Convert.ToString(reader["name"]);
nc.CampgroundOpenMonth = Convert.ToInt32(reader["open_from_mm"]);
nc.CampgroundCloseMonth = Convert.ToInt32(reader["open_to_mm"]);
nc.DailyCost = Convert.ToDecimal(reader["daily_fee"]);
campgroundsPerPark.Add(nc);
}
}
}
catch (SqlException ex)
{
throw;
}
return(campgroundsPerPark);
}
public bool IsOpen(CampgroundModel campground, DateTime startDate, DateTime endDate)
{
return(startDate.Month >= campground.Open_From_MM && startDate.Month <= campground.Open_To_MM && endDate.Month >= campground.Open_From_MM && endDate.Month <= campground.Open_To_MM);
}
