// Insert a new review for a restaurant
public void AddReview(Review review, int restID)
{
try
{
using (db = new RestaurantDBEntities())
{
// Add the Review to the Review table
db.Reviews.Add(review);
// Save the changes to the database
db.SaveChanges();
var revs = db.Reviews.ToList();
var id = revs.Last().ReviewID;
// Add the reference to the Reviews table
var r = new Review1()
{
RestaurantID = restID,
ReviewID = id
};
db.Reviews1.Add(r);
// Save the changes to the database
db.SaveChanges();
// Update the Restaurant's average rating
UpdateRestaurantAverageRating(restID);
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
}
// Update a given Restaurant's average rating
public void UpdateRestaurantAverageRating(int restID)
{
try
{
using (db = new RestaurantDBEntities())
{
// All Reviews for the restaurant
var rev = db.Reviews1.Where(x => x.RestaurantID == restID).ToList();
double rating = 0.0;
double averageRating = 0.0;
foreach (var r in rev)
{
rating += db.Reviews.Find(r.ReviewID).Rating;
}
averageRating = rating / rev.Count();
// Update the Restaurant's Average Rating
var rest = db.Restaurants.Find(restID);
rest.Rating = averageRating;
// Save the changes to the database
db.SaveChanges();
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
}
// Delete a selected restaurant review
public void DeleteRestaurantReview(int revID, int restID)
{
try
{
using (db = new RestaurantDBEntities())
{
// Delete the restaurant review
db.Reviews.Remove(db.Reviews.Find(revID));
// Delete the review from the Reviews table
var rev = db.Reviews1.Where(x => x.RestaurantID == restID && x.ReviewID == revID).FirstOrDefault();
if (rev != null)
{
db.Reviews1.Remove(db.Reviews1.Find(rev.ID));
}
// Save the changes to the database
db.SaveChanges();
// Update the restaurant's average rating
UpdateRestaurantAverageRating(restID);
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
}
// Update a selected Review
public void UpdateReview(Review review, int restID)
{
try
{
using (db = new RestaurantDBEntities())
{
// Find the Review within the database and update it
var r = db.Reviews.Where(x => x.ReviewID == review.ReviewID).FirstOrDefault();
if (r != null)
{
r.Author = review.Author;
r.Rating = review.Rating;
}
// Save the changes to the database
db.SaveChanges();
// Update the Restaurant's average rating
UpdateRestaurantAverageRating(restID);
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
}
// Add a Restaurant to the database
public void AddRestaurant(PLC.Restaurant restaurant)
{
using (db = new RestaurantDBEntities())
{
db.Restaurants.Add(LibraryToData(restaurant));
db.SaveChanges();
}
}
// Delete a selected Restaurant and all of it's corresponding reviews
public void DeleteRestaurant(Restaurant restaurant)
{
try
{
using (db = new RestaurantDBEntities())
{
if (restaurant != null)
{
// Remove the restaurant from the database
db.Restaurants.Remove(db.Restaurants.Find(restaurant.RestaurantID));
// Remove all of the it's reviews from the database
var reviewsToDelete = db.Reviews1.Where(x => x.RestaurantID == restaurant.RestaurantID).ToList();
if (reviewsToDelete != null)
{
foreach (var review in reviewsToDelete)
{
var r = db.Reviews.Where(x => x.ReviewID == review.ReviewID).FirstOrDefault();
db.Reviews.Remove(r); // Remove from the Review table
db.Reviews1.Remove(review); // Remove from the Reviews table
}
}
}
else
{
throw new NotImplementedException();
}
// Save the changes to the database
db.SaveChanges();
}
}
catch (NotImplementedException ne)
{
logger.Error(ne.Message);
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
}
// Update all Restaurant's records Rating to contain its average rating from the reviews
public void UpdateAverageRating()
{
using (db = new RestaurantDBEntities())
{
foreach (var rest in db.Restaurants)
{
double averageRating = 0.0;
double reviewCount = 0;
foreach (var rev in db.Reviews1)
{
if (rev.RestaurantID == rest.RestaurantID)
{
var reviewstable = db.Reviews.ToList();
averageRating += reviewstable.ElementAt((int)rev.ReviewID - 1).Rating;
reviewCount++;
}
}
averageRating = averageRating / reviewCount; // average rating
rest.Rating = averageRating;
}
db.SaveChanges(); // save the changes made
}
}
// Update an existing restaurant in the database
public void UpdateRestaurant(Restaurant restaurant)
{
try
{
// Search for the restaurant using it's ID
using (db = new RestaurantDBEntities())
{
var rest = db.Restaurants.Where(x => x.RestaurantID == restaurant.RestaurantID).FirstOrDefault();
// Update the restaurant
if (rest != null)
{
rest.Name = restaurant.Name;
rest.Address = restaurant.Address;
rest.City = restaurant.City;
rest.State = restaurant.State;
rest.ZipCode = restaurant.ZipCode;
}
else
{
throw new NotImplementedException();
}
// Save the changes
db.SaveChanges();
}
}
catch (NotImplementedException ne)
{
logger.Error(ne.Message);
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
}