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