public void insertIngredientIntoAllTables(Ingredient i, Recipe r)
{
var dbRecipes = new DatabaseAccessRecipe();
var dbIngredients = new DatabaseAccessIngredient();
var dbConsumptionOuncesConsumed = new DatabaseAccessConsumptionOuncesConsumed();
var dbConsumption = new DatabaseAccessConsumption();
var dbDensities = new DatabaseAccessDensities();
var dbDensitiesInformation = new DatabaseAccessDensityInformation();
var dbCosts = new DatabaseAccessCosts();
var myRecipes = dbRecipes.queryRecipes();
//var myIngredientBox = dbIngredients.queryAllIngredientsFromIngredientTable();
var myIngredients = queryAllRelevantTablesSQLByIngredientName(i);
var myRecipe = dbRecipes.queryRecipeFromRecipesTableByName(r);
if (string.IsNullOrEmpty(myRecipe.name))
{
dbRecipes.InsertRecipe(r);
}
dbIngredients.insertIngredient(i, r);
var myIng = queryAllRelevantTablesSQLByIngredientName(i);
dbDensitiesInformation.insertIngredientIntoDensityInfoDatabase(i);
dbDensities.insertIngredientDensityData(i);
dbConsumption.insertIngredientConsumtionData(i);
var myIngUpdated = queryAllRelevantTablesSQLByIngredientName(i);
dbCosts.insertIngredientCostDataCostTable(i);
var myConsumptionIngredient = dbConsumption.queryConsumptionTableRowByName(i);
dbIngredients.UpdateIngredient(i);
var myUpdatedIngredient = queryAllRelevantTablesSQLByIngredientName(i);
}
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 void updateAllTables(Ingredient i, Recipe r)
{
var dbRecipes = new DatabaseAccessRecipe();
var dbIngredients = new DatabaseAccessIngredient();
var dbConsumptionOuncesConsumed = new DatabaseAccessConsumptionOuncesConsumed();
var dbConsumption = new DatabaseAccessConsumption();
var dbDensities = new DatabaseAccessDensities();
var dbDensityInformation = new DatabaseAccessDensityInformation();
var dbCosts = new DatabaseAccessCosts();
var myCostTable = dbCosts.queryCostTable();
foreach (var ingredient in myCostTable)
{
if (ingredient.ingredientId == i.ingredientId)
{
if (ingredient.sellingPrice == 0m && i.sellingPrice != 0m)
{
dbCosts.updateCostDataTable(i);
break;
}
}
}
dbRecipes.UpdateRecipe(r);
dbIngredients.UpdateIngredient(i);
var updatedIngredient = queryAllRelevantTablesSQLByIngredientName(i);
dbDensityInformation.updateDensityInfoTable(i);
dbDensities.updateDensityTable(i);
dbCosts.updateCostDataTable(i);
}
public void GetFullRecipePrice(Recipe r)
{
var db = new DatabaseAccess();
var dbIngredients = new DatabaseAccessIngredient();
var myIngredients = GetFullRecipeAndFullIngredientsForRecipe(r).ingredients;
foreach (var ing in myIngredients)
{
if (ing.recipeId == r.id)
{
dbIngredients.getIngredientMeasuredPrice(ing, r);
r.ingredients.Add(ing);
db.updateAllTables(ing, r);
var currentIngredient = db.queryAllRelevantTablesSQLByIngredientName(ing);
}
}
var aggregatedPrice = 0m;
foreach (var ing in r.ingredients)
{
aggregatedPrice += ing.priceOfMeasuredConsumption;
}
r.aggregatedPrice = aggregatedPrice;
UpdateRecipe(r);
}
public void insertIngredientConsumtionData(Ingredient i)
{
var db = new DatabaseAccess();
var dbI = new DatabaseAccessIngredient();
var dbConsumptionOuncesConsumed = new DatabaseAccessConsumptionOuncesConsumed();
var convertWeight = new ConvertWeight();
var convert = new ConvertDensity();
var myIngredientIngredientTable = dbI.queryIngredientFromIngredientsTableByName(i);
var myConsumptionTable = queryConsumptionTable();
//i need to make sure i have all my information from my sql tables... that's why im getting null reference exceptions...
var temp = new Ingredient();
bool alreadyContainsIngredient = new bool();
if (myIngredientIngredientTable.classification.ToLower().Contains("egg"))
{
temp.name = "Egg";
//i would prefer this to be eggs, but i am matching the ingredient_classification if the ingredient.name doesn't match for querying the ingredients table and the consumption table, and the classifications are singular...
//i'm going to have to put a warning or something in the READ ME asking the user not to change the name of the consumption table ignredients... i'm not a big fan of that. I want there to be flexibility for what the user needs
i.ouncesConsumed = convertWeight.EggsConsumedFromIngredientMeasurement(myIngredientIngredientTable.measurement);
}
else
{
i.ouncesConsumed = dbConsumptionOuncesConsumed.CalculateOuncesConsumedFromMeasurement(i);
}
foreach (var ingredient in myConsumptionTable)
{
if (ingredient.name.ToLower() == i.name.ToLower() && (ingredient.name.ToLower().Contains(i.classification.ToLower()) && i.classification != " ") ||
ingredient.name == temp.name)
{
//if the name is the same && the classification is the same || the ingredient.name is the temp.name, noting the eggs already being present
alreadyContainsIngredient = true;
break;
}
}
if (string.IsNullOrEmpty(temp.name))
{
temp.name = i.name;
}
if (alreadyContainsIngredient == false)
{
var commandText = @"Insert into consumption (name, density, ounces_consumed, ounces_remaining, measurement) values (@name, @density, @ounces_consumed, @ounces_remaining, @measurement);";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@name", temp.name);
cmd.Parameters.AddWithValue("@density", i.density);
cmd.Parameters.AddWithValue("@ounces_consumed", i.ouncesConsumed);
cmd.Parameters.AddWithValue("@ounces_remaining", i.ouncesRemaining);
cmd.Parameters.AddWithValue("@measurement", i.measurement);
return(cmd);
});
updateConsumptionTable(i);
}
else
{
updateConsumptionTable(i);
}
var myUpdatedIngredient = queryConsumptionTable();
var myConsumptionOuncesConsumedTable = dbConsumptionOuncesConsumed.queryConsumptionOuncesConsumed();
}
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 List <Ingredient> queryAllRelevantTablesSQLForListOfIngredients(List <Ingredient> listOfIngredients)
{
var dbI = new DatabaseAccessIngredient();
var queriedIngredientList = new List <Ingredient>();
foreach (var ingredient in listOfIngredients)
{
var queriedIngredient = new Ingredient();
queriedIngredient = queryAllRelevantTablesSQLByIngredientName(ingredient);
queriedIngredientList.Add(queriedIngredient);
}
return(queriedIngredientList);
}
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;
}
}
}
public void DeleteRecipeAndRecipeIngredients(Recipe r)
{
var db = new DatabaseAccess();
var dbIngredients = new DatabaseAccessIngredient();
r.name = r.name.Trim();
var myRecipe = GetFullRecipeAndFullIngredientsForRecipe(r);
var myIngredients = dbIngredients.queryAllIngredientsFromIngredientTable();
foreach (var ingredient in myRecipe.ingredients)
{
dbIngredients.DeleteIngredientFromIngredientTable(ingredient);
}
//this will change the ingredient ids... i may have to go through and make sure all my logic for comparing ids will still be compatible when i start deleting stuff... lots of integrative testing needs to be done with that
var delete = "DELETE FROM recipes WHERE name=@name";
db.executeVoidQuery(delete, cmd => {
cmd.Parameters.AddWithValue("@name", r.name);
return(cmd);
});
}
public decimal CalculateOuncesConsumedFromMeasurement(Ingredient i)
{
var dbIngredients = new DatabaseAccessIngredient();
var convertMeasurement = new ConvertMeasurement();
var convertWeight = new ConvertWeight();
var convert = new ConvertDensity();
var myIngredientIngredientsTableData = dbIngredients.queryIngredientFromIngredientsTableByName(i);
var myConsumedOunces = 0m;
var temp = new Ingredient();
if (myIngredientIngredientsTableData.classification.ToLower().Contains("egg"))
{
var accumulatedOunces = convertMeasurement.AccumulatedTeaspoonMeasurement(i.measurement);
if (i.classification.ToLower().Contains("egg"))
{
var splitEggMeasurement = convertWeight.SplitWeightMeasurement(i.sellingWeight);
i.sellingWeightInOunces = decimal.Parse(splitEggMeasurement[0]);
}
}
myConsumedOunces = convert.CalculateOuncesUsed(i);
return(myConsumedOunces);
}
public void subtractOuncesRemainingIfExpirationDateIsPast(Ingredient i)
{
var db = new DatabaseAccess();
var dbIngredients = new DatabaseAccessIngredient();
var convert = new ConvertWeight();
var myIngredient = db.queryAllRelevantTablesSQLByIngredientName(i);
if (i.expirationDate < DateTime.Today && (dbIngredients.convertDateToStringMMDDYYYY(i.expirationDate) != "01/01/0001"))
{
myIngredient.ouncesRemaining = myIngredient.ouncesRemaining - i.sellingWeightInOunces;
if (myIngredient.ouncesRemaining < 0m)
{
myIngredient.ouncesRemaining = 0m;
}
var commandText = @"update consumption set ounces_remaining=@ounces_remaining where name=@name";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@name", myIngredient.name);
cmd.Parameters.AddWithValue("@ounces_remaining", i.ouncesRemaining);
return(cmd);
});
}
var myUpdatedIngredient = queryConsumptionTable();
}
public void insertIngredientIntoConsumptionOuncesConsumed(Ingredient i)
{
var db = new DatabaseAccess();
var dbi = new DatabaseAccessIngredient();
var myConsumptionOuncesConsumedIngredient = new Ingredient();
var dbc = new DatabaseAccessConsumption();
var ingredientTableRow = dbi.queryIngredientFromIngredientsTableByName(i);
var consumptiontablerow = dbc.queryConsumptionTableRowByName(i);
if (i.classification.ToLower().Contains("egg"))
{
i.classification = char.ToUpper(i.classification[0]) + i.classification.Substring(1, i.classification.Length - 1);
}
var commandTextQueryMultipleRows = string.Format(@"SELECT ingredients.name, ingredients.measurement, consumption.ounces_consumed, consumption.ounces_remaining
FROM ingredients
JOIN consumption
ON (ingredients.name=consumption.name AND ingredients.measurement=consumption.measurement)
OR (ingredients.ingredient_classification=consumption.name AND ingredients.measurement=consumption.measurement)
WHERE ingredients.name='{0}' AND ingredients.measurement='{1}' AND ingredients.ingredient_classification='{2}';", i.name, i.measurement, i.classification);
var myListOfQueriedIngredients = db.queryItems(commandTextQueryMultipleRows, reader => {
myConsumptionOuncesConsumedIngredient.name = (string)reader["name"];
myConsumptionOuncesConsumedIngredient.measurement = (string)reader["measurement"];
myConsumptionOuncesConsumedIngredient.ouncesConsumed = (decimal)reader["ounces_consumed"];
myConsumptionOuncesConsumedIngredient.ouncesRemaining = (decimal)reader["ounces_remaining"];
return(myConsumptionOuncesConsumedIngredient);
});
var commandTextVarsFilled = string.Format(@"INSERT INTO consumption_ounces_consumed
(name, ounces_consumed, ounces_remaining, measurement)
VALUES ('{0}', {1}, {2}, '{3}');", myListOfQueriedIngredients[0].name, myListOfQueriedIngredients[0].ouncesConsumed, myListOfQueriedIngredients[0].ouncesRemaining, myListOfQueriedIngredients[0].measurement);
db.executeVoidQuery(commandTextVarsFilled, cmd => { return(cmd); });
//as a note to self, i was using the querySingleItem from DatabaseAccess, and that's the difference between my working query and my query that reutrned null...
//something is off w that method.
//check:
var myConsumptionOuncesConsumedTable = queryConsumptionOuncesConsumed();
}
public void updateConsumptionTable(Ingredient i)
{
var db = new DatabaseAccess();
var dbI = new DatabaseAccessIngredient();
var dbConsumptionOuncesConsumed = new DatabaseAccessConsumptionOuncesConsumed();
var convert = new ConvertWeight();
var dbD = new DatabaseAccessDensities();
var myIngredient = dbI.queryIngredientFromIngredientsTableByName(i);
var myConsumptionTableIngredient = queryConsumptionTableRowByName(i);
var myDensityTableIngredient = dbD.queryIngredientFromDensityTableByName(i);
//var myDensityTableIngredient = dbD.queryIngredientFromDensityTableByName(i);
var temp = new Ingredient();
//this handles egg classifications, calculates ounces consumed and ounces remaining
if (myIngredient.classification.ToLower().Contains("egg"))
{
var currentOuncesConsumed = convert.EggsConsumedFromIngredientMeasurement(i.measurement);
if (myConsumptionTableIngredient.ouncesConsumed != currentOuncesConsumed)
{
i.ouncesConsumed = convert.EggsConsumedFromIngredientMeasurement(i.measurement);
}
if (myConsumptionTableIngredient.ouncesRemaining == 0m)
{
i.ouncesRemaining = i.sellingWeightInOunces - i.ouncesConsumed;
}
else
{
i.ouncesRemaining = myConsumptionTableIngredient.ouncesRemaining - i.ouncesConsumed;
}
}
//this handles other ingredients; eggs have to be calculated by usage of egg, not by an actual measurement
else
{
//if (i.ouncesConsumed == 0m)
myConsumptionTableIngredient.ouncesConsumed = dbConsumptionOuncesConsumed.CalculateOuncesConsumedFromMeasurement(i);
i.ouncesConsumed = myConsumptionTableIngredient.ouncesConsumed;
if (myConsumptionTableIngredient.ouncesRemaining == 0m)
{
myConsumptionTableIngredient.ouncesRemaining = myDensityTableIngredient.sellingWeightInOunces - myConsumptionTableIngredient.ouncesConsumed;
}
else
{
myConsumptionTableIngredient.ouncesRemaining = myConsumptionTableIngredient.ouncesRemaining - myConsumptionTableIngredient.ouncesConsumed;
}
i.ouncesRemaining = myConsumptionTableIngredient.ouncesRemaining;
}
//if (string.IsNullOrEmpty(temp.name) && !(i.classification.ToLower().Contains("egg")))
if (i.classification.ToLower().Contains("egg"))
{
temp.name = "Egg";
}
if (string.IsNullOrEmpty(temp.name))
{
temp.name = i.name;
}
//temp.name = i.name;
//subtractOuncesRemainingIfExpirationDateIsPast(i);
// this needs to be fixed, maybe for hte moment having a condition for ig it is eggs or dairy... flour and sugar, etc. should be totally fine
var commandText = "update consumption set ounces_consumed=@ounces_consumed, ounces_remaining=@ounces_remaining, refill=@refill where name=@name;";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@name", temp.name);
cmd.Parameters.AddWithValue("@ounces_consumed", i.ouncesConsumed);
cmd.Parameters.AddWithValue("@ounces_remaining", i.ouncesRemaining);
cmd.Parameters.AddWithValue("@refill", i.restock);
return(cmd);
});
doesIngredientNeedRestocking(i);
//this is after the consumption insertion and update... so it should work fine...
var myUpdatedIngredient = queryConsumptionTableRowByName(i);
dbConsumptionOuncesConsumed.insertIngredientIntoConsumptionOuncesConsumed(i);
//still not getting the ouncesRemaining... need to change this
var consumptionOuncesConsumed = dbConsumptionOuncesConsumed.queryConsumptionOuncesConsumed();
var myUpdatedIngredient2 = queryConsumptionTableRowByName(i);
//why am i not inserting this into the database?
var myUpdatedConsumptionOuncesConsumedTable = dbConsumptionOuncesConsumed.queryConsumptionOuncesConsumed();
}
