internal List <MealIngredientViewModel> FetchAll()
{
List <MealIngredientViewModel> FridgeContents = new List <MealIngredientViewModel>();
using (SqlConnection connection = new SqlConnection(singleConnectionString))
{
connection.Open();
string sqlFridgeIngredientQuery = @"
SELECT f.fridge_ingredient_id, f.ingredient_id, i.label, f.unit_id, u.label, f.quantity
FROM fridge_ingredient AS f
INNER JOIN ingredient AS i ON i.ingredient_id = f.ingredient_id
INNER JOIN unit AS u ON u.unit_id = f.unit_id";
SqlCommand getIngredient = new SqlCommand(sqlFridgeIngredientQuery, connection);
SqlDataReader ingredientReader = getIngredient.ExecuteReader();
if (ingredientReader.HasRows)
{
while (ingredientReader.Read())
{
MealIngredientViewModel IngredientToIndex = new MealIngredientViewModel();
IngredientToIndex.FridgeIngredientId = ingredientReader.GetInt32(0);
IngredientToIndex.IngredientId = ingredientReader.GetInt32(1);
IngredientToIndex.Label = ingredientReader.GetString(2);
IngredientToIndex.UnitId = ingredientReader.GetInt32(3);
IngredientToIndex.UnitLabel = ingredientReader.GetString(4);
IngredientToIndex.Quantity = ingredientReader.GetDecimal(5);
FridgeContents.Add(IngredientToIndex);
}
}
}
return(FridgeContents);
}
internal MealIngredientViewModel FetchMealIngredient(int mealIngredientID)
{
using (SqlConnection connection = new SqlConnection(multiConnectionString))
{
string sqlIngredientQuery = @"
SELECT m.meal_ingredient_id, m.meal_id, m.ingredient_id, i.label, m.unit_id, u.label, m.quantity
FROM meal_ingredient AS m
INNER JOIN ingredient AS i ON i.ingredient_id = m.ingredient_id
INNER JOIN unit AS u ON u.unit_id = m.unit_id
WHERE m.meal_id = @Id";
SqlCommand command = new SqlCommand(sqlIngredientQuery, connection);
command.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = mealIngredientID;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
var ingredientToEdit = new MealIngredientViewModel();
if (reader.HasRows)
{
while (reader.Read())
{
ingredientToEdit.MealIngredientId = reader.GetInt32(0);
ingredientToEdit.MealId = reader.GetInt32(1);
ingredientToEdit.IngredientId = reader.GetInt32(2);
ingredientToEdit.Label = reader.GetString(3);
ingredientToEdit.UnitId = reader.GetInt32(4);
ingredientToEdit.UnitLabel = reader.GetString(5);
ingredientToEdit.Quantity = reader.GetDecimal(6);
}
}
return(ingredientToEdit);
}
}
internal List <MealIngredientViewModel> FetchIngredientsOfMeal(int mealId)
{
var ingredientList = new List <MealIngredientViewModel>();
using (SqlConnection connection = new SqlConnection(multiConnectionString))
{
string sqlIngredientQuery = @"
SELECT m.meal_ingredient_id, m.meal_id, m.ingredient_id, i.label, m.unit_id, u.label, m.quantity
FROM meal_ingredient AS m
INNER JOIN ingredient AS i ON i.ingredient_id = m.ingredient_id
INNER JOIN unit AS u ON u.unit_id = m.unit_id
WHERE m.meal_id = @Id";
SqlCommand getIngredient = new SqlCommand(sqlIngredientQuery, connection);
getIngredient.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = mealId;
connection.Open();
SqlDataReader ingredientReader = getIngredient.ExecuteReader();
if (ingredientReader.HasRows)
{
while (ingredientReader.Read())
{
var newIngredient = new MealIngredientViewModel();
newIngredient.MealIngredientId = ingredientReader.GetInt32(0);
newIngredient.MealId = ingredientReader.GetInt32(1);
newIngredient.IngredientId = ingredientReader.GetInt32(2);
newIngredient.Label = ingredientReader.GetString(3);
newIngredient.UnitId = ingredientReader.GetInt32(4);
newIngredient.UnitLabel = ingredientReader.GetString(5);
newIngredient.Quantity = ingredientReader.GetDecimal(6);
ingredientList.Add(newIngredient);
}
}
}
return(ingredientList);
}
internal MealIngredientViewModel FetchOne(int id)
{
using (SqlConnection connection = new SqlConnection(singleConnectionString))
{
string sqlQuery = @"
SELECT f.fridge_ingredient_id, f.ingredient_id, i.label, f.unit_id, u.label, f.quantity
FROM fridge_ingredient AS f
INNER JOIN ingredient AS i ON i.ingredient_id = f.ingredient_id
INNER JOIN unit AS u ON u.unit_id = f.unit_id
WHERE fridge_ingredient_id = @Id";
SqlCommand command = new SqlCommand(sqlQuery, connection);
command.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id;
connection.Open();
SqlDataReader ingredientReader = command.ExecuteReader();
var ingredientToFetch = new MealIngredientViewModel();
if (ingredientReader.HasRows)
{
while (ingredientReader.Read())
{
ingredientToFetch.FridgeIngredientId = ingredientReader.GetInt32(0);
ingredientToFetch.IngredientId = ingredientReader.GetInt32(1);
ingredientToFetch.Label = ingredientReader.GetString(2);
ingredientToFetch.UnitId = ingredientReader.GetInt32(3);
ingredientToFetch.UnitLabel = ingredientReader.GetString(4);
ingredientToFetch.Quantity = ingredientReader.GetDecimal(5);
}
}
return(ingredientToFetch);
}
}
public async Task <IActionResult> Create(MealIngredientViewModel mealIngredientViewModel)
{
var validator = new MealIngredientValidator();
var result = validator.Validate(mealIngredientViewModel.MealIngredient);
List <MealIngredient> mealIngredientsCheck = new List <MealIngredient>(_context.MealIngredients
.Include(x => x.Ingredient)
.Where(x => x.MealId == mealIngredientViewModel.MealIngredient.MealId));
if (mealIngredientsCheck.Where(x => x.Ingredient.IngredientId == mealIngredientViewModel.MealIngredient.IngredientId).Count() > 0)
{
ModelState.AddModelError(string.Empty, "The meal already contains the meal ingredient");
}
if (result.IsValid && ModelState.IsValid)
{
_context.Add(mealIngredientViewModel.MealIngredient);
await _context.SaveChangesAsync();
return(RedirectToAction("Create", mealIngredientViewModel.MealId));
}
else
{
foreach (var item in result.Errors)
{
ModelState.AddModelError(string.Empty, item.ErrorMessage);
}
}
mealIngredientViewModel.MealIngredients = new List <MealIngredient>(_context.MealIngredients.Where(x => x.MealId == mealIngredientViewModel.MealIngredient.MealId).Include(x => x.Meal));
mealIngredientViewModel.Ingredients = new SelectList(_context.Ingredients, "IngredientId", "Name", mealIngredientViewModel.MealIngredient.IngredientId);
mealIngredientViewModel.Units = new SelectList(_context.Units, "UnitId", "Name", mealIngredientViewModel.MealIngredient.UnitId);
return(View(mealIngredientViewModel));
}
public ActionResult Create()
{
var ingredientDBInterface = new FridgeDAO();
var newIngredient = new MealIngredientViewModel();
var unitLabels = new List <Unit>();
unitLabels = ingredientDBInterface.FetchUnits();
newIngredient.AvailableUnitLabels = unitLabels;
return(View("FridgeIngredientCreateForm", newIngredient));
}
public IActionResult Add(int Id)
{
var meal = _mealRepository.GetMeal(Id);
var model = new MealIngredientViewModel()
{
MealId = Id,
MealName = meal.Name
};
return(View(model));
}
public ActionResult EditFridgeIngredient(MealIngredientViewModel updatedIngredient)
{
var updateFridge = new FridgeDAO();
var revisedIngredient = updatedIngredient;
updateFridge.UpdateIngredient(revisedIngredient);
var myFridge = new Fridge();
myFridge.Contents = updateFridge.FetchAll();
return(View("Index", myFridge));
}
public IActionResult Edit(MealIngredientViewModel model)
{
var ingredient = new Ingredient();
ingredient.ItemId = model.ItemId;
ingredient.Measure = model.Measure;
//ingredient.Unit = model.Unit;
_ingredientRepository.Edit(ingredient);
return(RedirectToAction("Index", "MealIngredient", new { id = model.MealId }));
}
//Add MealIngredient to Meal
public ActionResult ProcessMealIngredientToAdd(int id)
{
var MealDBInterface = new MealDAO();
var mealIngredientToCreate = new MealIngredientViewModel();
var unitList = new List <Unit>();
unitList = MealDBInterface.FetchUnits();
mealIngredientToCreate.MealId = id;
mealIngredientToCreate.AvailableUnitLabels = unitList;
return(View("MealIngredientCreateForm", mealIngredientToCreate));
}
internal void AddIngredientToIngredientDB(MealIngredientViewModel ingredientToAdd)
{
using (SqlConnection connection = new SqlConnection(singleConnectionString))
{
var sqlAddIngredientQuery = "INSERT INTO [dbo].[ingredient] VALUES (@label)";
SqlCommand addIngredient = new SqlCommand(sqlAddIngredientQuery, connection);
addIngredient.Parameters.Add("@label", System.Data.SqlDbType.VarChar).Value = ingredientToAdd.Label;
connection.Open();
addIngredient.ExecuteScalar();
}
}
//Edit MealIngredient
public ActionResult ProcessMealIngredientToEdit(MealIngredientViewModel ingredientToEdit)
{
var MealDBInterface = new MealDAO();
var mealId = ingredientToEdit.MealId;
MealDBInterface.UpdateMealIngredient(ingredientToEdit);
var mealToDisplay = new MealViewModel();
mealToDisplay = MealDBInterface.FetchMeal(mealId);
return(View("MealForm", mealToDisplay));
}
// GET: MealIngredient/Create
public IActionResult Create(int id)
{
MealIngredientViewModel mealIngredientViewModel = new MealIngredientViewModel();
mealIngredientViewModel.MealIngredient = new MealIngredient();
mealIngredientViewModel.MealIngredient.MealId = id;
mealIngredientViewModel.MealIngredients = new List <MealIngredient>(_context.MealIngredients
.Where(x => x.MealId == id).Include(x => x.Meal));
mealIngredientViewModel.Ingredients = new SelectList(_context.Ingredients, "IngredientId", "Name");
mealIngredientViewModel.Units = new SelectList(_context.Units, "UnitId", "Name");
return(View(mealIngredientViewModel));
}
//Edit MealIngredient
public ActionResult ProcessIngredientToEdit(int id)
{
var MealDBInterface = new MealDAO();
var mealIngredientToEdit = new MealIngredientViewModel();
mealIngredientToEdit = MealDBInterface.FetchMealIngredient(id);
var unitLabels = new List <Unit>();
unitLabels = MealDBInterface.FetchUnits();
mealIngredientToEdit.AvailableUnitLabels = unitLabels;
return(View("MealIngredientForm", mealIngredientToEdit));
}
public ActionResult EditIngredient(int id)
{
var ingredientDBInterface = new FridgeDAO();
var newIngredient = new MealIngredientViewModel();
newIngredient = ingredientDBInterface.FetchOne(id);
var unitLabels = new List <Unit>();
unitLabels = ingredientDBInterface.FetchUnits();
newIngredient.AvailableUnitLabels = unitLabels;
return(View("MealIngredientForm", newIngredient));
}
internal void UpdateMealIngredient(MealIngredientViewModel ingredientToEdit)
{
using (SqlConnection connection = new SqlConnection(singleConnectionString))
{
var sqlQuery = "UPDATE [dbo].[meal_ingredient] SET quantity = @quantity, unit_id = @unit WHERE meal_ingredient_id = @id";
SqlCommand editIngredient = new SqlCommand(sqlQuery, connection);
editIngredient.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = ingredientToEdit.MealIngredientId;
editIngredient.Parameters.Add("@quantity", System.Data.SqlDbType.Decimal).Value = ingredientToEdit.Quantity;
editIngredient.Parameters.Add("@unit", System.Data.SqlDbType.Int).Value = ingredientToEdit.SelectedUnitId;
connection.Open();
editIngredient.ExecuteNonQuery();
}
}
internal bool ingredientAlreadyInFridge(MealIngredientViewModel ingredientToCheck)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
//dbo.fridge_ingredient Query
var sqlFridgeIngredientDBOQuery = "SELECT ingredient_id FROM [dbo].[fridge_ingredient] WHERE ingredient_id = @id";
var ingredientID = ingredientToCheck.FridgeIngredientId;
SqlCommand fridgeDBCheck = new SqlCommand(sqlFridgeIngredientDBOQuery, connection);
fridgeDBCheck.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = ingredientID;
connection.Open();
var fridgeCheck = fridgeDBCheck.ExecuteScalar();
if (fridgeCheck != null)
{
return(true);
}
}
return(false);
}
public ActionResult AddFridgeIngredient(MealIngredientViewModel newIngredient)
{
FridgeDAO dbInterface = new FridgeDAO();
if (dbInterface.ingredientAlreadyInFridge(newIngredient))
{
ModelState.AddModelError("CustomError", "Ingredient already in Fridge");
return(View());
}
else
{
dbInterface.AddIngredientToFridge(newIngredient);
}
var myFridge = new Fridge();
myFridge.Contents = dbInterface.FetchAll();
return(View("Index", myFridge));
}
// GET: MealIngredient/Edit/5
public async Task <IActionResult> Edit(int?id, MealIngredientViewModel mealIngredientViewModel)
{
if (id == null)
{
return(RedirectToAction("Error", "Home"));
}
var mealIngredient = await _context.MealIngredients.FindAsync(id);
if (mealIngredient == null)
{
return(RedirectToAction("Error", "Home"));
}
mealIngredientViewModel.MealIngredient = mealIngredient;
mealIngredientViewModel.Ingredients = new SelectList(_context.Ingredients, "IngredientId", "Name", mealIngredientViewModel.MealIngredient.MealIngredientId);
mealIngredientViewModel.Units = new SelectList(_context.Units, "UnitId", "Name", mealIngredientViewModel.MealIngredient.UnitId);
return(View(mealIngredientViewModel));
}
public async Task <IActionResult> Edit(int id, MealIngredientViewModel mealIngredientViewModel)
{
if (id != mealIngredientViewModel.MealIngredient.MealIngredientId)
{
return(RedirectToAction("Error", "Home"));
}
if (ModelState.IsValid)
{
_context.Update(mealIngredientViewModel.MealIngredient);
await _context.SaveChangesAsync();
return(RedirectToAction("Create", new { id = mealIngredientViewModel.MealIngredient.MealId }));
}
mealIngredientViewModel.Ingredients = new SelectList(_context.Ingredients, "IngredientId", "Name", mealIngredientViewModel.MealIngredient.MealIngredientId);
mealIngredientViewModel.Units = new SelectList(_context.Units, "UnitId", "Name", mealIngredientViewModel.MealIngredient.UnitId);
return(View(mealIngredientViewModel));
}
public IActionResult Add(MealIngredientViewModel model)
{
var newModel = new Ingredient();
newModel.ItemId = model.ItemId;
newModel.Measure = model.Measure;
//newModel.Unit = model.Unit;
var newId = _ingredientRepository.Add(newModel);
var newIMModel = new MealIngredient();
newIMModel.IngredientId = newId;
newIMModel.MealId = model.MealId;
_mealingredientRepository.Add(newIMModel);
return(RedirectToAction("Index", "MealIngredient", new { id = newIMModel.MealId }));
}
public ActionResult AddMealIngredientToMeal(MealIngredientViewModel ingredientToAdd)
{
var MealDBInterface = new MealDAO();
var mealId = ingredientToAdd.MealId;
if (MealDBInterface.ingredientAlreadyInMeal(ingredientToAdd))
{
ModelState.AddModelError("CustomError", "Ingredient already in Fridge");
return(View());
}
else
{
MealDBInterface.AddIngredientToMeal(ingredientToAdd);
}
var mealToDisplay = new MealViewModel();
mealToDisplay = MealDBInterface.FetchMeal(mealId);
return(View("Mealform", mealToDisplay));
}
public IActionResult Index(int id)
{
var model = _mealingredientRepository.GetIngredientsForMeal(id);
var newModel = new List <MealIngredientViewModel>();
foreach (var item in model)
{
var temp = new MealIngredientViewModel();
temp.Id = item.Ingredient.Id;
temp.ItemName = item.Ingredient.Item.Name;
temp.Measure = item.Ingredient.Measure;
temp.Unit = item.Ingredient.Item.Unit;
temp.MealId = id;
newModel.Add(temp);
}
var meal = _mealRepository.GetMeal(id);
ViewBag.MealName = meal.Name;
ViewBag.MealId = meal.Id;
return(View(newModel));
}
internal void AddIngredientToMeal(MealIngredientViewModel ingredientToAdd)
{
if (!ingredientExistsInIngredientDb(ingredientToAdd.Label))
{
AddIngredientToIngredientDB(ingredientToAdd);
}
//add ingredient to dbo.meal_ingredient
using (SqlConnection connection = new SqlConnection(singleConnectionString))
{
var sqlAddIngredientQuery = @"
INSERT INTO [dbo].[meal_ingredient]
SELECT @meal_id, [dbo].[ingredient].ingredient_id, @unit_id, @quantity
FROM [dbo].[ingredient]
WHERE label = @label";
SqlCommand addToMeal = new SqlCommand(sqlAddIngredientQuery, connection);
addToMeal.Parameters.Add("@meal_id", System.Data.SqlDbType.Int).Value = ingredientToAdd.MealId;
addToMeal.Parameters.Add("@label", System.Data.SqlDbType.VarChar).Value = ingredientToAdd.Label;
addToMeal.Parameters.Add("@unit_id", System.Data.SqlDbType.Int).Value = ingredientToAdd.SelectedUnitId;
addToMeal.Parameters.Add("@quantity", System.Data.SqlDbType.Decimal).Value = ingredientToAdd.Quantity;
connection.Open();
addToMeal.ExecuteNonQuery();
}
}
internal void AddIngredientToFridge(MealIngredientViewModel ingredientToAdd)
{
//check if ingredient exists in dbo.ingredient
//var ingredientLabel = ingredientToAdd.Label;
if (ingredientExistsInIngredientDb(ingredientToAdd.Label) == false)
{
AddIngredientToIngredientDB(ingredientToAdd);
}
//add ingredient to dbo.fridge_ingredient
using (SqlConnection connection = new SqlConnection(singleConnectionString))
{
var sqlAddIngredientQuery = @"
INSERT INTO [dbo].[fridge_ingredient]
SELECT [dbo].[ingredient].ingredient_id, @unit_id, @quantity
FROM [dbo].[ingredient]
WHERE label = @label";
SqlCommand addToFridge = new SqlCommand(sqlAddIngredientQuery, connection);
addToFridge.Parameters.Add("@label", System.Data.SqlDbType.VarChar).Value = ingredientToAdd.Label;
addToFridge.Parameters.Add("@unit_id", System.Data.SqlDbType.Int).Value = ingredientToAdd.SelectedUnitId;
addToFridge.Parameters.Add("@quantity", System.Data.SqlDbType.Int).Value = ingredientToAdd.Quantity;
connection.Open();
addToFridge.ExecuteNonQuery();
}
}
public async Task <ActionResult <MealIngredientViewModel> > EditMealIngredient(int mealId, int mealIngredientId, MealIngredientViewModel model)
{
try
{
// Do we need to check if mealIngredientId == model.mealIngredientId?
// Do we need to check if mealId == model.mealId?
var existingMealIngredient = _repository.GetMealIngredientByMealId(mealId, mealIngredientId);
if (existingMealIngredient == null)
{
return(NotFound("Couldn't find meal ingredient"));
}
if (mealId != model.MealId || mealIngredientId != model.MealIngredientId)
{
return(BadRequest("Unable to edit Meals or Meal Ingredients during this stage."));
}
// Do we want to allow the user to change the ingredient type here? If so, we will need checks similar to Post ie: checking if ingredient exists in Db, and checking if ingredient is already part of this meal.
// Throwing error if no changes detected. Any additional items that can be changes should be added to this check.
if (model.Quantity == existingMealIngredient.Quantity)
{
return(Ok("No changes detected."));
}
existingMealIngredient.Quantity = model.Quantity;
var saveChanges = await _repository.SaveAll();
if (saveChanges)
{
return(_mapper.Map <MealIngredientViewModel>(existingMealIngredient));
}
}
catch (Exception exception)
{
_logger.LogError($"Could not add that meal ingredient: {exception}");
return(BadRequest("Could not add that ingredient."));
}
return(BadRequest("Could not update that ingredient. You are unable to edit Meals and Ingredients in this manner."));
}
public async Task <ActionResult <MealIngredientViewModel> > AddIngredientToMeal(int mealId, MealIngredientViewModel model)
{
try
{
var existingMeal = _repository.GetMealById(mealId);
var mealIngredient = _mapper.Map <MealIngredient>(model);
// Confirm meal exists
if (existingMeal == null)
{
return(BadRequest($"Meal with ID: {mealId} does not exist."));
}
if (model.Ingredient.Name == null)
{
return(BadRequest("Ingredient Name is required"));
}
// Check if ingredient exists in database
bool ingredientExistsInDb = await _repository.IngredientExists(mealIngredient.Ingredient);
if (ingredientExistsInDb)
{
mealIngredient.Ingredient = await _repository.GetIngredientByName(model.Ingredient.Name);
}
else
{
var newIngredient = mealIngredient.Ingredient;
_repository.AddEntity(newIngredient);
}
// Check if ingredient is already assigned to this meal
if (_repository.IngredientAlreadyAssignedToMeal(mealId, mealIngredient.Ingredient.IngredientId))
{
return(BadRequest("Ingredient is already assigned to this meal."));
}
mealIngredient.Meal = existingMeal;
_repository.AddEntity(mealIngredient);
var saveChanges = await _repository.SaveAll();
if (saveChanges)
{
var url = _linkGenerator.GetPathByAction("ShowMealIngredient", "MealIngredients",
values: new { mealId, mealIngredientId = mealIngredient.MealIngredientId });
if (string.IsNullOrWhiteSpace(url))
{
return(BadRequest("Could not use MealIngredientId."));
}
return(Created($"{url}", _mapper.Map <MealIngredientViewModel>(mealIngredient)));
}
else
{
return(BadRequest($"Failed to save new meal ingredient to meal with Meal Id: {mealId}."));
}
}
catch (Exception exception)
{
_logger.LogError($"Could not add that meal ingredient: {exception}");
return(BadRequest("Could not add that ingredient."));
}
}
