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;
}
}
}