/// <summary>
/// Updates the specified recipe in the database.
/// </summary>
/// <param name="recipe">The recipe to update in the database.</param>
/// <returns>Returns whether or not the required queries were successful.</returns>
public static async Task <QueryResult[]> UpdateRecipe(FullRecipe recipe)
{
List <QueryResult> allResults = null;
try
{
//Updates the basic info of the recipe & deletes all recipe links temporarily
allResults = new List <QueryResult>
{
await App.ExecuteQuery(
"update Rezepte " +
$"set Gerichtname = '{recipe.Name}', Zubereitung = '{recipe.Preparation}' " +
$"where ID = {recipe.ID}"),
await App.ExecuteQuery($"delete from Rezeptzutatenliste where IDRezepte = {recipe.ID}")
};
//Updates the ingredient links of the recipe
for (int i = 0; i < recipe.LinkedIngredients.Count; i++)
{
allResults.Add(await App.ExecuteQuery(
"insert into Rezeptzutatenliste(IDRezepte, IDZutaten, Menge, Notiz) " +
$"values({recipe.ID}, " +
$"{recipe.LinkedIngredients[i].ID}, " +
$"'{recipe.LinkedIngredients[i].Amount}', " +
$"'{recipe.LinkedIngredients[i].Note}')"));
}
}
catch (Exception)
{
//Ignore
}
return(allResults?.ToArray());
}
public ActionResult <FullRecipe> PostFullRecipe(FullRecipe fullRecipe)
{
_logger.LogInformation("Post recipe called");
FullRecipe r = this._recipeService.PostRecipe(fullRecipe);
return(CreatedAtAction(nameof(GetRecipe), new { id = r.RecipeID }, r));
}
internal void UpdateRecipe(FullRecipe fullRecipe)
{
using (SqlConnection conn = new SqlConnection(_dBConn.getDevString()))
{
conn.Open();
{
SqlCommand command = new SqlCommand(queries.updateRecipeQuery(fullRecipe), conn);
command.ExecuteNonQuery();
}
foreach (RecipeToIngredient recipeToIngredient in fullRecipe.recipeList)
{
//TODO only insert if ingredient name is not in db
/* SqlCommand command = new SqlCommand(queries.updateIngredientQuery(recipeToIngredient), conn);
* command.ExecuteNonQuery();*/
SqlCommand command = new SqlCommand(queries.updateRecipeComponent(fullRecipe, recipeToIngredient), conn);
command.ExecuteNonQuery();
}
foreach (Instruction instruction in fullRecipe.instructionList)
{
SqlCommand command = new SqlCommand(queries.updateIstructionQuery(instruction, fullRecipe), conn);
command.ExecuteNonQuery();
}
}
}
public FullRecipe PostRecipe(FullRecipe recipe)
{
Recipe r = new Recipe();
try
{
r = this.SetRecipe(recipe);
this._context.recipe.Add(r);
this._context.SaveChanges();
AddIngredients(recipe.Ingredients, r.ID);
AddSteps(recipe.Steps, r.ID);
AddSubSteps(recipe.SubSteps, r.ID);
AddTips(recipe.Tips, r.ID);
AddSubTips(recipe.SubTips, r.ID);
this._context.SaveChanges();
} catch (Exception ex)
{
_logger.LogInformation("Error: " + ex);
}
return(GetSingleRecipe(r.ID).Value);
}
private Recipe SetRecipe(FullRecipe recipe)
{
Recipe r = new Recipe();
try
{
r = new Recipe()
{
Name = recipe.Name,
ImagePath = recipe.ImagePath,
Description = recipe.Description,
Category = recipe.Category,
PrepTime = recipe.PrepTime,
CookTime = recipe.CookTime,
Created = recipe.Created,
LastModified = recipe.LastModified
};
}
catch (Exception ex)
{
_logger.LogInformation("Error: " + ex);
}
return(r);
}
public FullRecipe UpdateRecipe(FullRecipe recipe)
{
Recipe smallRecipe = new Recipe();
try
{
smallRecipe = this.SetRecipe(recipe);
smallRecipe.ID = recipe.RecipeID;
//_context.recipe.Add(this.SetRecipe(recipe));
_context.recipe.Update(smallRecipe);
this.UpdateSteps(recipe.Steps, recipe.RecipeID);
this.UpdateSubSteps(recipe.SubSteps, recipe.RecipeID);
this.UpdateTips(recipe.Tips, recipe.RecipeID);
this.UpdateSubTips(recipe.SubTips, recipe.RecipeID);
this.UpdateIngredients(recipe.Ingredients, recipe.RecipeID);
_context.SaveChanges();
} catch (Exception ex)
{
_logger.LogInformation("ERROR: " + ex);
}
return(recipe);
}
public async Task GetRecipeAsync_Success(int recipeId, FullRecipe recipe)
{
A.CallTo(() => recipeService.GetRecipeAsync(recipeId)).Returns(recipe);
var result = await sut.GetRecipeAsync(recipeId).ConfigureAwait(false);
result.Should().Be(recipe);
}
/* internal static string updateIngredientQuery(RecipeToIngredient recipeToIngredient)
* {
* return $"update ingredients set IngredientName values ('{recipeToIngredient.IngredientName}')";
* }*/
internal static string updateRecipeComponent(FullRecipe fullRecipe, RecipeToIngredient recipeToIngredient)
{
return($"update recipeComponents (" +
$" set RecipeId = (select recipeid from recipes where recipes.recipename='{fullRecipe.recipe.RecipeName}'), " +
$" set IngredientId = (select ingredientId from Ingredients where ingredients.ingredientName='{recipeToIngredient.IngredientName}'), " +
$" set Quantity = {recipeToIngredient.Quantity}" +
$" set Unit = '{recipeToIngredient.Unit}') ) " +
$" where recipes.recipeId = {fullRecipe.recipe.RecipeId};");
}
public ActionResult <FullRecipe> UpdateRecipe(FullRecipe recipe)
{
FullRecipe r;
_logger.LogInformation("request made");
r = _recipeService.UpdateRecipe(recipe);
return(CreatedAtAction(nameof(GetRecipe), new { id = recipe.RecipeID }, r));
}
public static string insertRecipeComponent(FullRecipe fullRecipe, RecipeToIngredient recipeToIngredient) => $"insert into recipeComponents(" +
$"RecipeId, " +
$"IngredientId, " +
$"Quantity, " +
$"Unit) " +
$"values" +
$"((select recipeid from recipes where recipes.recipename='{fullRecipe.recipe.RecipeName}')," +
$"(select ingredientId from Ingredients where ingredients.ingredientName='{recipeToIngredient.IngredientName}'), " +
$"{recipeToIngredient.Quantity}, " +
$"'{recipeToIngredient.Unit}')";
public async Task CreateRecipeAsync_Success(NewRecipe newRecipe, FullRecipe recipe, int recipeId)
{
A.CallTo(() => recipeService.CreateRecipeAsync(newRecipe)).Returns(recipeId);
A.CallTo(() => recipeService.GetRecipeAsync(recipeId)).Returns(recipe);
var result = await sut.CreateRecipeAsync(newRecipe).ConfigureAwait(false);
A.CallTo(() => recipeService.CreateRecipeAsync(newRecipe)).MustHaveHappenedOnceExactly();
result.Should().Be(recipe);
}
/**
* A method for getting a full recipe from the database,
* the main purpose of this is to display the full recipe on the seerecipe site
*/
public static FullRecipe GetFullRecipe(String recipeName)
{
using (var db = new RecipeDBConnection())
{
FullRecipe result = new FullRecipe();
DbSet <RECIPE> recipes = db.RECIPEs;
//getting the recipe data
RECIPE recipe = null;
foreach (var item in recipes)
{
if (item.NAME == recipeName)
{
recipe = item;
break;
}
}
if (recipe == null)
{
return(null);
}
result.SetName(recipe.NAME);
result.Description = recipe.DESCRIPTION;
//Getting the comments for the recipe
List <comment> commentsForRecipe = new List <comment>();
foreach (var item in db.comments)
{
if (item.ID_RECIPE == recipe.ID_RECIPE)
{
commentsForRecipe.Add(item);
}
}
result.Comments = commentsForRecipe;
//getting the ingredients for the recipe
var iNGREDIENT_IN_RECIPEs = from item in db.INGREDIENT_IN_RECIPE
join measurement in db.MEASUREMENT_UNIT on item.ID_MEASUREMENT equals measurement.ID_MEASUREMENT
join ingredient in db.INGREDIENTs on item.ID_INGREDIENT equals ingredient.ID_INGREDIENT
where item.ID_RECIPE == recipe.ID_RECIPE select item;
List <JsonIngredient> displayIngredients = new List <JsonIngredient>();
foreach (var item in iNGREDIENT_IN_RECIPEs)
{
displayIngredients.Add(new JsonIngredient(item.INGREDIENT.NAME, 999, item.MEASUREMENT_UNIT.TEXT));
}
result.Ingredients = displayIngredients;
return(result);
}
}
/**
* A method for uploading a Recipe object to the database.
*/
public static bool CreateRecipe(FullRecipe fullRecipe)
{
using (var db = new RecipeDBConnection())
{
RECIPE tmpRECIPE = new RECIPE(fullRecipe.GetName(), fullRecipe.Description);
db.RECIPEs.Add(tmpRECIPE);
foreach (var ingredient_in_recipe in fullRecipe.Ingredients)
{
db.INGREDIENT_IN_RECIPE.Add(new INGREDIENT_IN_RECIPE(ingredient_in_recipe.amount, ingredient_in_recipe.unit, (int)tmpRECIPE.ID_RECIPE));
}
db.SaveChanges();
}
return(false);
}
/**
* A full recipe is retirved from the database using the id from the path as the name of the recipe
*
*/
public ActionResult SeeRecipe(String id)
{
FullRecipe objectToPass;
if (id == "" || id == null)
{
objectToPass = new FullRecipe();
}
else
{
objectToPass = RecipeDAO.GetFullRecipe(id);
}
// send ther recipe with image object to the view
return(View(objectToPass));
}
public ActionResult <FullRecipe> GetSingleRecipe(int id)
{
Recipe r = new Recipe();
var x = new FullRecipe();
try
{
r = _context.recipe.Where(y => y.ID == id).ToList()[0];
}
catch (Exception ex)
{
_logger.LogError($"No recipe with provided index ({id}) found.");
}
x = InitializeRecipe(r);
return(x);
}
/// <summary>
/// Fetches all recipes and linked ingredients and enables this page.
/// </summary>
private async void RecipesPage_OnLoaded(object sender, RoutedEventArgs e)
{
//Returns if the page is already filled with information
if (IsEnabled)
{
return;
}
//Wait for the window to display this page
await Task.Delay(100);
//Gets all recipes
_recipes = await App.ExecuteQuery(
"select ID, Gerichtname, Zubereitung from Rezepte");
//Gets all linked ingredients
_ingredients = await App.ExecuteQuery(
"select Rezepte_ID, Gerichtname, Zutat, Menge, Notiz from ZusammenfassungRezeptzutatenliste");
//Adds all fetched recipes to an array as new objects
ListRecipes.Items.Clear();
var rawArray = new FullRecipe[_recipes.ReturnedRows.Count];
for (int i = 0; i < _recipes.ReturnedRows.Count; i++)
{
rawArray[i] = new FullRecipe(
_recipes.ReturnedRows[i][0] as int?,
_recipes.ReturnedRows[i][1].ToString(),
_recipes.ReturnedRows[i][2].ToString(),
new IngredientInfo[0]);
}
//Orders the recipes and adds them to the displayed list
FullRecipe[] orderedArray = rawArray.OrderBy(a => a.Name).ToArray();
for (int i = 0; i < orderedArray.Length; i++)
{
ListRecipes.Items.Add(orderedArray[i]);
}
//Enables the page
IsEnabled = true;
}
public IActionResult SubmitForm(FullRecipe fullrecipe)
{
//submit new recipe
// 1) submit new recipe id, name to recipes
// 2) where recipe id is the above submit ingredients using select list, adding quantities with form
// 3) add instructions based on recipe id with a form
// submit recipe with submit form button
foreach (RecipeToIngredient recipeToIngredient in fullrecipe.recipeList)
{
string insertIngredientQuery = $"insert into ingredients(IngredientName) values ('{recipeToIngredient.IngredientName}')";
}
/*string insertRecipeNameQuery = $"insert into recipes(RecipeName) values('{fullrecipe.recipe.RecipeName}')";
* string insertComponentQuery = $"insert into RecipeComponents(RecipeId, IngredientId, Quantity, Unit) VALUES" +
* $"(SELECT RecipeId from Recipes where Recipes.RecipeName = '{fullrecipe.recipe.RecipeName}')," +
* $"(SELECT IngredientId FROM Ingredients where Ingredients.IngredientName = '{fullrecipe.recipeList[0].IngredientName}'),3,'cups');";
* string insertIinstructionQuery = $"insert into instructions(RecipeId, InstructionContent) values" +
* $"(select recipes.recipeId from Recipes where Recipes.RecipeName = '{fullrecipe.recipe.RecipeName}')," +
* $"'{fullrecipe.instructionList[0].InstructionContent}')";*/
return(RedirectToAction("Index"));
}
/// <summary>
/// Inserts the specified recipe into the database.
/// </summary>
/// <param name="recipe">The recipe to insert into the database.</param>
/// <returns>Returns whether or not the required queries were successful.</returns>
public static async Task <QueryResult[]> InsertNewRecipe(FullRecipe recipe)
{
List <QueryResult> allResults = null;
try
{
//Inserts basic info
allResults = new List <QueryResult>
{
await App.ExecuteQuery(
"insert into Rezepte(Gerichtname, Zubereitung) " +
$"values('{recipe.Name}', '{recipe.Preparation}')"),
await App.ExecuteQuery("select ID from Rezepte " +
$"where Gerichtname = '{recipe.Name}' " +
$"and Zubereitung = '{recipe.Preparation}'")
};
//Inserts the ingredient links
if (allResults[1].ReturnedRows?[0][0] is int newRecipeID)
{
for (int i = 0; i < recipe.LinkedIngredients.Count; i++)
{
allResults.Add(await App.ExecuteQuery(
"insert into Rezeptzutatenliste(IDRezepte, IDZutaten, Menge, Notiz) " +
$"values({newRecipeID}, " +
$"{recipe.LinkedIngredients[i].ID}, " +
$"'{recipe.LinkedIngredients[i].Amount}', " +
$"'{recipe.LinkedIngredients[i].Note}')"));
}
}
}
catch (Exception)
{
//Ignore
}
return(allResults?.ToArray());
}
/// <summary>
/// Close the window and save the recipe into <see cref="Recipe"/> for external use.
/// </summary>
private void ConfirmRecipe(object sender, RoutedEventArgs e)
{
//Creates a new regex expression to check against illegal SQL query characters
var matcher = new Regex(".*(\\\"|').*");
//Checks if the recipe info contains illegal characters
if (matcher.IsMatch(((FullRecipe)DataContext).Name) ||
matcher.IsMatch(((FullRecipe)DataContext).Preparation) ||
((FullRecipe)DataContext).LinkedIngredients.Any(a => matcher.IsMatch(a.Name ?? string.Empty) ||
matcher.IsMatch(a.Amount ?? string.Empty) ||
matcher.IsMatch(a.Note ?? string.Empty)))
{
//Warns the user of illegal characters
MessageBox.Show(Languages.Resources.MsgInvalidRecipeContent, Languages.Resources.ErrorSimple,
MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
//Saves the recipe and closes the window successfully
DialogResult = true;
Recipe = (FullRecipe)DataContext;
Close();
}
private FullRecipe InitializeRecipe(Recipe originalRecipe)
{
FullRecipe recipe = new FullRecipe()
{
RecipeID = originalRecipe.ID,
Name = originalRecipe.Name,
ImagePath = originalRecipe.ImagePath,
Description = originalRecipe.Description,
Category = originalRecipe.Category,
PrepTime = originalRecipe.PrepTime,
CookTime = originalRecipe.CookTime,
Created = originalRecipe.Created,
LastModified = originalRecipe.LastModified
};
try
{
String[] prepTimeSplit = recipe.PrepTime.Split(":");
String[] cookTimeSplit = recipe.CookTime.Split(":");
recipe.PrepTimeH = float.Parse(prepTimeSplit[0]);
recipe.PrepTimeM = float.Parse(prepTimeSplit[1]);
recipe.CookTimeH = float.Parse(cookTimeSplit[0]);
recipe.CookTimeM = float.Parse(cookTimeSplit[1]);
} catch (Exception ex)
{
_logger.LogError(ex.ToString());
}
recipe.Ingredients = GetIngredients(recipe.RecipeID);
recipe.Steps = GetSteps(recipe.RecipeID);
recipe.SubSteps = GetSubSteps(recipe.RecipeID);
recipe.Tips = GetTips(recipe.RecipeID);
recipe.SubTips = GetSubTips(recipe.RecipeID);
return(recipe);
}
//insertRecipeQuery
//insertIngredientQuery
//insertRecipeComponent
//insertInstructionQuery
internal static string updateRecipeQuery(FullRecipe fullRecipe)
{
return($"update Recipes set RecipeName = {fullRecipe.recipe.RecipeName} where Recipes.RecipeId = {fullRecipe.recipe.RecipeId};");
}
public static void Main()
{
FullRecipe recipe = GetFullRecipe("spagettieRecipe");
}
internal static string insertRecipeQuery(FullRecipe fullRecipe) => $"insert into Recipes(RecipeName) values ('{fullRecipe.recipe.RecipeName}')";
public static string insertIstructionQuery(Instruction instruction, FullRecipe fullRecipe) =>
$"insert into instructions(recipeid, instructionContent) values " +
$"((select recipes.recipeId from recipes where recipes.recipeName = '{fullRecipe.recipe.RecipeName}')," +
$"'{instruction.InstructionContent}')";
internal static string updateIstructionQuery(Instruction instruction, FullRecipe fullRecipe) =>
$"update instructions " +
$"set instructionContent = '{instruction.InstructionContent}' " +
$" where instructions.instructionId = {instruction.InstructionId};";
public bool Post(FullRecipe fullRecipe)
{
new RecipeAPI_DAO(_dBConn).PostRecipe(fullRecipe);
return(true);
}
public bool Put(FullRecipe fullRecipe)
{
new RecipeAPI_DAO(_dBConn).UpdateRecipe(fullRecipe);
return(true);
}
