Beispiel #1
0
        public List <Campsite> GetTop5CampsitesInPark(int parkId, DateTime fromDate, DateTime toDate)
        {
            List <Campsite> output = new List <Campsite>();

            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();

                    CampgroundSqlDAL  dal = new CampgroundSqlDAL(connectionString);
                    List <Campground> listOfCampgrounds = dal.GetCampground(parkId);

                    SqlCommand cmd = new SqlCommand();

                    foreach (Campground cg in listOfCampgrounds)
                    {
                        cmd = new SqlCommand(@"SELECT TOP 5*, campground.daily_fee FROM site 
INNER JOIN campground ON campground.campground_id = site.campground_id 
WHERE site.site_id not in 
	(SELECT site_id 
		FROM reservation 
		WHERE (@toDate >  reservation.from_date AND @toDate < reservation.to_date)
		OR (@fromDate > reservation.from_date AND @fromDate < reservation.to_date)
		OR (@fromDate < reservation.from_date AND @toDate > reservation.to_date)
		) 
AND site.campground_id = @campground ;", conn);
                        cmd.Parameters.AddWithValue("@campground", cg.campgroundID);
                        cmd.Parameters.AddWithValue("@fromDate", fromDate);
                        cmd.Parameters.AddWithValue("@toDate", toDate);

                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Campsite c = new Campsite();
                                c.siteID       = Convert.ToInt32(reader["site_id"]);
                                c.campgroundID = Convert.ToInt32(reader["campground_id"]);
                                c.siteNumber   = Convert.ToInt32(reader["site_number"]);
                                c.maxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
                                c.accessible   = Convert.ToBoolean(reader["accessible"]);
                                c.maxRVLength  = Convert.ToInt32(reader["max_rv_length"]);
                                c.utilities    = Convert.ToBoolean(reader["utilities"]);
                                c.dailyFee     = Convert.ToDouble(reader["daily_fee"]);
                                c.totalPrice   = c.CalcTotalPrice(fromDate, toDate, c.dailyFee);

                                output.Add(c);
                            }
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                throw;
            }

            return(output);
        }
Beispiel #2
0
        public List <Campsite> GetCampsite(int campgroundId, DateTime fromDate, DateTime toDate)
        {
            List <Campsite> output = new List <Campsite>();

            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();

                    SqlCommand cmd = new SqlCommand(@"SELECT TOP 5*, campground.daily_fee FROM site LEFT JOIN reservation ON site.site_id=reservation.site_id LEFT JOIN campground ON site.campground_id = campground.campground_id WHERE site.campground_id = @campgroundId AND (@fromDate > to_date OR @toDate < from_date);", conn);
                    cmd.Parameters.AddWithValue("@campgroundId", campgroundId);
                    cmd.Parameters.AddWithValue("@fromDate", fromDate);
                    cmd.Parameters.AddWithValue("@toDate", toDate);

                    SqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        Campsite c = new Campsite();
                        c.siteID       = Convert.ToInt32(reader["site_id"]);
                        c.campgroundID = Convert.ToInt32(reader["campground_id"]);
                        c.siteNumber   = Convert.ToInt32(reader["site_number"]);
                        c.maxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
                        c.accessible   = Convert.ToBoolean(reader["accessible"]);
                        c.maxRVLength  = Convert.ToInt32(reader["max_rv_length"]);
                        c.utilities    = Convert.ToBoolean(reader["utilities"]);
                        c.dailyFee     = Convert.ToDouble(reader["daily_fee"]);
                        c.totalPrice   = c.CalcTotalPrice(fromDate, toDate, c.dailyFee);


                        output.Add(c);
                    }
                }
            }
            catch (SqlException e)
            {
                throw;
            }

            return(output);
        }