public Ingredient queryAllRelevantTablesSQLByIngredientName(Ingredient i) { var dbI = new DatabaseAccessIngredient(); var queriedIngredient = new Ingredient(); var commandText = string.Format(@"SELECT * FROM ingredients JOIN consumption_ounces_consumed ON ingredients.name=consumption_ounces_consumed.name AND ingredients.ing_id=consumption_ounces_consumed.ing_id JOIN costs ON ingredients.name=costs.name AND ingredients.ing_id=costs.ing_id JOIN densities ON ingredients.name=densities.name AND ingredients.ing_id=densities.ing_id WHERE ingredients.name='{0}' AND ingredients.ing_id={1};", i.name, i.ingredientId); queryItems(commandText, reader => { queriedIngredient.name = (string)(reader["name"]); queriedIngredient.ingredientId = (int)(reader["ing_id"]); queriedIngredient.recipeId = (int)(reader["recipe_id"]); queriedIngredient.measurement = (string)(reader["measurement"]); queriedIngredient.ouncesConsumed = (decimal)(reader["ounces_consumed"]); queriedIngredient.ouncesRemaining = (decimal)(reader["ounces_remaining"]); queriedIngredient.classification = (string)(reader["ingredient_classification"]); queriedIngredient.typeOfIngredient = (string)(reader["ingredient_type"]); queriedIngredient.pricePerOunce = (decimal)(reader["price_per_ounce"]); queriedIngredient.priceOfMeasuredConsumption = (decimal)(reader["price_measured_ingredient"]); queriedIngredient.sellingPrice = (decimal)(reader["selling_price"]); queriedIngredient.sellingWeight = (string)(reader["selling_weight"]); queriedIngredient.sellingWeightInOunces = (decimal)(reader["selling_weight_ounces"]); queriedIngredient.density = (decimal)reader["density"]; var expirationDate = (string)(reader["expiration_date"]); queriedIngredient.expirationDate = dbI.convertStringMMDDYYYYToDateYYYYMMDD(expirationDate); return(queriedIngredient); }); return(queriedIngredient); }
public Recipe GetFullRecipeAndFullIngredientsForRecipe(Recipe r) { var db = new DatabaseAccess(); var dbI = new DatabaseAccessIngredient(); var dbCOC = new DatabaseAccessConsumptionOuncesConsumed(); var myRecipe = new Recipe(); //var myIngredient = new Ingredient(); var myListOfIngredients = new List <Ingredient>(); var myIngredientTable = dbI.queryAllIngredientsFromIngredientTable(); var myConsumptionTable = dbCOC.queryConsumptionOuncesConsumed(); var myRecipeTableName = queryRecipeFromRecipesTableByName(r); //maybe this is going off of order... the ingredients is the first table, the consumption_ounces_consumed, then the name var commandText = string.Format(@"SELECT * FROM ingredients JOIN consumption_ounces_consumed ON ingredients.name=consumption_ounces_consumed.name AND ingredients.ing_id=consumption_ounces_consumed.ing_id JOIN recipes ON ingredients.recipe_id=recipes.recipe_id WHERE recipes.recipe_id={0};", r.id); myListOfIngredients = db.queryItems(commandText, reader => { var myIngredient = new Ingredient((string)(reader["name"])); myIngredient.ingredientId = (int)reader["ing_id"]; myIngredient.measurement = (string)reader["measurement"]; myIngredient.classification = (string)reader["ingredient_classification"]; myIngredient.typeOfIngredient = (string)reader["ingredient_type"]; myIngredient.priceOfMeasuredConsumption = (decimal)reader["price_measured_ingredient"]; myIngredient.recipeId = (int)reader["recipe_id"]; myIngredient.ouncesConsumed = (decimal)reader["ounces_consumed"]; myIngredient.ouncesRemaining = (decimal)reader["ounces_remaining"]; myIngredient.itemId = (int)reader["item_id"]; myIngredient.itemResponseName = (string)reader["item_response_name"]; var expirationDate = dbI.convertStringMMDDYYYYToDateYYYYMMDD((string)reader["expiration_date"]); myIngredient.expirationDate = expirationDate; return(myIngredient); }); db.queryItems(commandText, reader => { myRecipe.id = (int)reader["recipe_id"]; myRecipe.name = (string)reader["recipe_name"]; myRecipe.yield = (int)reader["yield"]; myRecipe.aggregatedPrice = (decimal)reader["aggregated_price"]; myRecipe.pricePerServing = (decimal)reader["price_per_serving"]; return(myRecipe); }); myRecipe.ingredients = myListOfIngredients; if (myRecipe.aggregatedPrice == 0) { foreach (var ingredient in myRecipe.ingredients) { myRecipe.aggregatedPrice += ingredient.priceOfMeasuredConsumption; } } myRecipe.pricePerServing = ReturnRecipePricePerServing(myRecipe); //UpdateRecipe(myRecipe); var myUpdatedRecipe = queryRecipeFromRecipesTableByName(myRecipe); return(myRecipe); }
public void refillIngredientInConsumptionDatabase(Ingredient i, string sellingWeightToRefill, string newExpirationDate) { var db = new DatabaseAccess(); var dbIngredients = new DatabaseAccessIngredient(); var convert = new ConvertWeight(); var myConsumptionTable = queryConsumptionTable(); var myIngredientTable = dbIngredients.queryAllIngredientsFromIngredientTable(); var sellingWeightToRefillOunces = convert.ConvertWeightToOunces(sellingWeightToRefill); foreach (var ingredient in myConsumptionTable) { if (ingredient.name.ToLower() == i.name.ToLower()) { if (i.ouncesRemaining < 0m) { i.ouncesRemaining = 0m; } i.ouncesRemaining = ingredient.ouncesRemaining + sellingWeightToRefillOunces; var commandText = "update consumption set ounces_remaining=@ounces_remaining where name=@name;"; db.executeVoidQuery(commandText, cmd => { cmd.Parameters.AddWithValue("@name", i.name); cmd.Parameters.AddWithValue("@ounces_remaining", i.ouncesRemaining); return(cmd); }); break; } } foreach (var ingredient in myIngredientTable) { if (ingredient.ingredientId == i.ingredientId && ingredient.name.ToLower() == i.name.ToLower()) { ingredient.expirationDate = dbIngredients.convertStringMMDDYYYYToDateYYYYMMDD(newExpirationDate); var commandText = "update ingredients set expiration_date=@expiration_date where ing_id=@ing_id"; db.executeVoidQuery(commandText, cmd => { cmd.Parameters.AddWithValue("@expiration_date", dbIngredients.convertDateToStringMMDDYYYY(ingredient.expirationDate)); cmd.Parameters.AddWithValue("@ing_id", ingredient.ingredientId); return(cmd); }); break; } } }