Beispiel #1
0
        public IEnumerable <Restaurant> GetRestaurants()
        {
            var restaurants = new List <Restaurant>();
            var meals       = new List <Meal>();
            var comments    = new List <Comment>();

            using (var conn = GetConnection())
            {
                _logger.AddInfoLog("Establishing connection to dabatase");
                try
                {
                    _logger.AddInfoLog("Getting restaurant data from database");
                    conn.Open();
                    var cmd = new MySqlCommand("select * from restaurants", conn);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var temp = reader;
                            restaurants.Add(new Restaurant()
                            {
                                Id       = Convert.ToInt32(reader["id"]),
                                Name     = reader["restaurant_name"].ToString(),
                                Lat      = Convert.ToDouble(reader["restaurant_lat"]),
                                Lng      = Convert.ToDouble(reader["restaurant_lng"]),
                                IsActive = Convert.ToBoolean(reader["is_active"])
                            });
                        }
                    }
                    cmd = new MySqlCommand("SELECT meals.meal_id, meals.meal_name, meals.meal_price, meals.restaurant_id, avg(comments.comment_rating) " +
                                           "FROM meals left join comments on meals.meal_id=comments.meal_id " +
                                           "group by meal_id", conn);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var meal = new Meal()
                            {
                                Id           = Convert.ToInt32(reader["meal_id"]),
                                Name         = reader["meal_name"].ToString(),
                                Price        = Convert.ToDouble(reader["meal_price"]),
                                RestaurantId = Convert.ToInt32(reader["restaurant_id"])
                            };
                            try
                            {
                                meal.AverageRate = Convert.ToDouble(reader["avg(comments.comment_rating)"]);
                            }
                            catch
                            {
                                meal.AverageRate = 0;
                            }
                            meals.Add(meal);
                        }
                    }

                    cmd = new MySqlCommand("select * from comments", conn);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            comments.Add(new Comment()
                            {
                                MealId = Convert.ToInt32(reader["meal_id"]),
                                Text   = reader["comment_text"].ToString(),
                                Rate   = Convert.ToInt32(reader["comment_rating"])
                            });
                        }
                    }

                    foreach (var restaurant in restaurants)
                    {
                        restaurant.Menu = meals.Where(x => x.RestaurantId == restaurant.Id);
                        foreach (var meal in restaurant.Menu)
                        {
                            meal.Comments = comments.Where(x => x.MealId == meal.Id).Select(x => x.Text);
                            meal.Rates    = comments.Where(x => x.MealId == meal.Id).Select(x => x.Rate);
                        }
                    }
                }
                catch (Exception e)
                {
                    _logger.AddErrorLog("Error while getting restaurants data from database" + e.Message);
                }

                return(restaurants);
            }
        }
Beispiel #2
0
        public Restaurant UpdateRestaurant(Restaurant restaurant)
        {
            using (var conn = GetConnection())
            {
                _logger.AddInfoLog("Establishing connection to dabatase");
                try
                {
                    _logger.AddInfoLog($"Trying to update restaurant: {restaurant.Name} data");
                    conn.Open();
                    var cmd = new MySqlCommand($"update restaurants " +
                                               $"set restaurant_lat={restaurant.Lat.ToString(System.Globalization.CultureInfo.InvariantCulture)}, " +
                                               $"restaurant_lng={restaurant.Lng.ToString(System.Globalization.CultureInfo.InvariantCulture)} " +
                                               $"where id={restaurant.Id} and restaurant_name like \"{restaurant.Name}\";", conn);
                    //cmd.ExecuteNonQuery();

                    cmd = new MySqlCommand($"select * from meals where restaurant_id={restaurant.Id}", conn);
                    var oldMenu = new List <Meal>();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var meal = new Meal()
                            {
                                Id           = Convert.ToInt32(reader["meal_id"]),
                                Name         = reader["meal_name"].ToString(),
                                Price        = Convert.ToDouble(reader["meal_price"]),
                                RestaurantId = Convert.ToInt32(reader["restaurant_id"])
                            };

                            oldMenu.Add(meal);
                        }
                    }
                    if (oldMenu.Count() > restaurant.Menu.Count())
                    {
                        for (int i = 0; i < oldMenu.Count(); i++)
                        {
                            if (i < restaurant.Menu.Count())
                            {
                                var meal = restaurant.Menu.ToList()[i];
                                cmd = new MySqlCommand($"update meals set " +
                                                       $"meal_name=\"{meal.Name}\", " +
                                                       $"meal_price={meal.Price.ToString(System.Globalization.CultureInfo.InvariantCulture)} " +
                                                       $"where meal_id={meal.Id} and restaurant_id={meal.RestaurantId};", conn);
                                cmd.ExecuteNonQuery();
                            }
                            else
                            {
                                var meal = oldMenu.ToList()[i];
                                cmd = new MySqlCommand($"delete from meals where meal_id={meal.Id}", conn);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    else if (oldMenu.Count() < restaurant.Menu.Count())
                    {
                        for (int i = 0; i < restaurant.Menu.Count(); i++)
                        {
                            var meal = restaurant.Menu.ToList()[i];
                            if (i < oldMenu.Count())
                            {
                                cmd = new MySqlCommand($"update meals set " +
                                                       $"meal_name=\"{meal.Name}\", " +
                                                       $"meal_price={meal.Price.ToString(System.Globalization.CultureInfo.InvariantCulture)} " +
                                                       $"where meal_id={meal.Id} and restaurant_id={meal.RestaurantId};", conn);
                                cmd.ExecuteNonQuery();
                            }
                            else
                            {
                                cmd = new MySqlCommand($"insert into meals values" +
                                                       $"(0,\"{meal.Name}\",{meal.Price.ToString(System.Globalization.CultureInfo.InvariantCulture)},{meal.RestaurantId});", conn);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    else
                    {
                        //just update
                        foreach (var meal in restaurant.Menu)
                        {
                            cmd = new MySqlCommand($"update meals set " +
                                                   $"meal_name=\"{meal.Name}\", " +
                                                   $"meal_price={meal.Price.ToString(System.Globalization.CultureInfo.InvariantCulture)} " +
                                                   $"where meal_id={meal.Id} and restaurant_id={meal.RestaurantId};", conn);
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception e)
                {
                    _logger.AddErrorLog($"Error while updating restaurant: {restaurant.Name} data" + e.Message);
                }
            }
            return(restaurant);
        }