public List <CampgroundSite> GetTop5SitesByParkId(int parkId)
{
List <CampgroundSite> campgroundSites = new List <CampgroundSite>();
const string sql = "Select top 5 campground.*, site.* From park join campground " +
"on park.park_id = campground.park_id " +
"join site " +
"on campground.campground_id = site.campground_id " +
"where park.park_id = @parkId;";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@parkId", parkId);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
CampgroundSite campgroundSite = new CampgroundSite();
campgroundSite.site = GetSiteFromReader(reader);
campgroundSite.campground = GetCampgroundFromReader(reader);
campgroundSites.Add(campgroundSite);
}
}
return(campgroundSites);
}
public List <CampgroundSite> GetSitesByParkIdAndResevationAvailability(int parkId, DateTime startDate, DateTime endDate)
{
List <CampgroundSite> campgroundSites = new List <CampgroundSite>();
const string sql = "select top 5 campground.*, site.* from site " +
"join campground on site.campground_id = campground.campground_id " +
"join park on park.park_id = campground.park_id " +
"where park.park_id = @parkId and " +
"site_id not in " +
"(select site.site_id " +
"from site join reservation " +
"on site.site_id = reservation.site_id " +
"where " +
"((reservation.from_date >= @startDate and " +
"reservation.from_date <= @endDate) or " +
"(reservation.to_date >= @startDate and reservation.to_date <= @endDate))) " +
"and campground.open_from_mm <= month(@startDate) and " +
"campground.open_to_mm >= month(@endDate);";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@parkId", parkId);
cmd.Parameters.AddWithValue("@startDate", startDate);
cmd.Parameters.AddWithValue("@endDate", endDate);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
CampgroundSite campgroundSite = new CampgroundSite();
campgroundSite.site = GetSiteFromReader(reader);
campgroundSite.campground = GetCampgroundFromReader(reader);
campgroundSites.Add(campgroundSite);
}
}
return(campgroundSites);
}
public List <CampgroundSite> GetSitesByParkIdAndResevationAvailability(int parkId, DateTime startDate, DateTime endDate, int maxOccupancy, AccessibleOptions accessible, int maxRVLength, UtilitiesAvailble utilities)
{
List <CampgroundSite> campgroundSites = new List <CampgroundSite>();
bool accessibleClause1 = true;
bool accessibleClause2 = true;
bool utilityClause1 = true;
bool utilityClause2 = true;
if (accessible == AccessibleOptions.yes)
{
accessibleClause1 = true;
accessibleClause2 = true;
}
else if (accessible == AccessibleOptions.no)
{
accessibleClause1 = false;
accessibleClause2 = false;
}
else if (accessible == AccessibleOptions.either)
{
accessibleClause1 = true;
accessibleClause2 = false;
}
if (utilities == UtilitiesAvailble.yes)
{
utilityClause1 = true;
utilityClause2 = true;
}
else if (utilities == UtilitiesAvailble.no)
{
utilityClause1 = false;
utilityClause2 = false;
}
else if (utilities == UtilitiesAvailble.either)
{
utilityClause1 = true;
utilityClause2 = false;
}
string sql = "select top 5 campground.*, site.* from site " +
"join campground on site.campground_id = campground.campground_id " +
"join park on park.park_id = campground.park_id " +
"where park.park_id = @parkId and " +
"site_id not in " +
"(select site.site_id " +
"from site join reservation " +
"on site.site_id = reservation.site_id " +
"where " +
"((reservation.from_date >= @startDate and " +
"reservation.from_date <= @endDate) or " +
"(reservation.to_date >= @startDate and reservation.to_date <= @endDate))) " +
"and campground.open_from_mm <= month(@startDate) and " +
"campground.open_to_mm >= month(@endDate)" +
"and max_occupancy >= @maxOccupancy " +
"and (accessible = @accessibleClause1 or accessible = @accessibleClause2) " +
"and max_rv_length >= @maxRVLength " +
"and (utilities = @accessibleClause1 or utilities = @accessibleClause2);";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@parkId", parkId);
cmd.Parameters.AddWithValue("@startDate", startDate);
cmd.Parameters.AddWithValue("@endDate", endDate);
cmd.Parameters.AddWithValue("@maxOccupancy", maxOccupancy);
cmd.Parameters.AddWithValue("@maxRVLength", maxRVLength);
cmd.Parameters.AddWithValue("@utilityClause1", utilityClause1);
cmd.Parameters.AddWithValue("@utilityClause2", utilityClause2);
cmd.Parameters.AddWithValue("@accessibleClause1", accessibleClause1);
cmd.Parameters.AddWithValue("@accessibleClause2", accessibleClause2);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
CampgroundSite campgroundSite = new CampgroundSite();
campgroundSite.site = GetSiteFromReader(reader);
campgroundSite.campground = GetCampgroundFromReader(reader);
campgroundSites.Add(campgroundSite);
}
}
return(campgroundSites);
}
