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> queryDensityInfoTable() { var db = new DatabaseAccess(); var DensityInfo = db.queryItems("select * from densityInfo order by ingredient desc", reader => { var densityIngredientInformation = new Ingredient(reader["ingredient"].ToString()); densityIngredientInformation.density = (decimal)reader["density"]; return(densityIngredientInformation); }); return(DensityInfo); }
public List <Ingredient> queryConsumptionOuncesConsumed() { var db = new DatabaseAccess(); var ingredientConsumptionInformation = db.queryItems("select * from consumption_ounces_consumed", reader => { var ingredient = new Ingredient(reader["name"].ToString()); ingredient.ouncesConsumed = (decimal)reader["ounces_consumed"]; ingredient.measurement = (string)reader["measurement"]; ingredient.ouncesRemaining = (decimal)reader["ounces_remaining"]; return(ingredient); }); return(ingredientConsumptionInformation); }
public List <Ingredient> queryIngredientIdsAndNamesFromIngredientTable() { var db = new DatabaseAccess(); //var dict = new Dictionary<int, string>(); var listOfIngredients = new List <Ingredient>(); var commandText = @"SELECT ing_id, name FROM ingredients;"; listOfIngredients = db.queryItems(commandText, reader => { var ingredient = new Ingredient((string)reader["name"]); ingredient.ingredientId = (int)reader["ing_id"]; return(ingredient); }); return(listOfIngredients); }
public decimal queryDensityTableRowDensityValueByName(Ingredient i) { var rest = new MakeRESTCalls(); var db = new DatabaseAccess(); var myIngredient = new Ingredient(); var commandTextQueryTableRowByName = string.Format(@"SELECT * FROM densityInfo WHERE ingredient='{0}';", i.typeOfIngredient); db.queryItems(commandTextQueryTableRowByName, reader => { myIngredient.name = (string)reader["ingredient"]; myIngredient.density = (decimal)reader["density"]; return(myIngredient); }); return(myIngredient.density); }
public List <Ingredient> queryConsumptionTableSorted() { var db = new DatabaseAccess(); var commandText = @"SELECT * FROM consumption ORDER BY name ASC"; var myConsumptionTable = db.queryItems(commandText, reader => { var myConsumptionIngredient = new Ingredient((string)reader["name"]); myConsumptionIngredient.ouncesConsumed = (decimal)reader["ounces_consumed"]; myConsumptionIngredient.ouncesRemaining = (decimal)reader["ounces_remaining"]; myConsumptionIngredient.restock = (int)reader["refill"]; return(myConsumptionIngredient); }); return(myConsumptionTable); }
public Ingredient queryConsumptionOuncesConsumedTableByName(Ingredient i) { var db = new DatabaseAccess(); var consumptionOucnesConsumedIngredient = new Ingredient(); var commandTextConsumptionRow = string.Format(@"SELECT * FROM consumption_ounces_consumed WHERE name='{0}'", i.name); db.queryItems(commandTextConsumptionRow, reader => { consumptionOucnesConsumedIngredient.name = (string)reader["name"]; consumptionOucnesConsumedIngredient.measurement = (string)reader["measurement"]; consumptionOucnesConsumedIngredient.ouncesConsumed = (decimal)reader["ounces_consumed"]; consumptionOucnesConsumedIngredient.ouncesRemaining = (decimal)reader["ounces_remaining"]; return(consumptionOucnesConsumedIngredient); }); return(consumptionOucnesConsumedIngredient); }
public List <Ingredient> queryCostTable() { var db = new DatabaseAccess(); var ingredientInformation = db.queryItems("select * from costs", reader => { var ingredient = new Ingredient(reader["name"].ToString()); ingredient.ingredientId = (int)reader["ing_id"]; ingredient.sellingWeight = (string)reader["selling_weight"]; ingredient.sellingPrice = (decimal)reader["selling_price"]; ingredient.pricePerOunce = (decimal)reader["price_per_ounce"]; ingredient.itemId = (int)reader["item_id"]; return(ingredient); }); return(ingredientInformation); }
public List <string> myDistinctIngredientTypesSorted() { var db = new DatabaseAccess(); var distinctTypes = new List <string>(); var commandTextDistinctTypes = @"SELECT DISTINCT ingredient_type FROM ingredients ORDER BY ingredient_type ASC;"; db.queryItems(commandTextDistinctTypes, reader => { string type = (string)(reader["ingredient_type"]); //if (!commandTextDistinctTypes.Contains(type)) distinctTypes.Add(type); return(type); }); return(distinctTypes); }
public List <string> myDistinctIngredientClassificationsSorted() { var db = new DatabaseAccess(); var distinctClassifications = new List <string>(); var commandTextDistinctClassifications = @"SELECT DISTINCT ingredient_classification FROM ingredients ORDER BY ingredient_classification ASC"; db.queryItems(commandTextDistinctClassifications, reader => { string classification = (string)(reader["ingredient_classification"]); //if (!distinctClassifications.Contains(classification)) distinctClassifications.Add(classification); return(classification); }); return(distinctClassifications); }
public Recipe queryRecipeFromRecipesTableByName(Recipe r) { var db = new DatabaseAccess(); var myRecipe = new Recipe(); var commandTextQueryRecipeByName = string.Format(@"SELECT * FROM recipes where recipe_name='{0}';", r.name); db.queryItems(commandTextQueryRecipeByName, reader => { myRecipe.name = (string)(reader["recipe_name"]); myRecipe.id = (int)(reader["recipe_id"]); myRecipe.yield = (int)(reader["yield"]); myRecipe.aggregatedPrice = (decimal)(reader["aggregated_price"]); myRecipe.pricePerServing = (decimal)(reader["price_per_serving"]); return(myRecipe); }); return(myRecipe); }
public List <Ingredient> queryCostTableSortedByPricePerOunceDESC() { var db = new DatabaseAccess(); var commandText = @"SELECT * FROM costs ORDER BY price_per_ounce DESC;"; var mySortedListOfIngredients = db.queryItems(commandText, reader => { var ingredient = new Ingredient((string)reader["name"]); ingredient.ingredientId = (int)reader["ing_id"]; ingredient.sellingWeight = (string)reader["selling_weight"]; ingredient.pricePerOunce = (decimal)reader["price_per_ounce"]; ingredient.itemId = (int)reader["item_id"]; ingredient.sellingPrice = (decimal)reader["selling_price"]; return(ingredient); }); return(mySortedListOfIngredients); }
public List <Ingredient> orderIngredientsByPricePerOunce() { var db = new DatabaseAccess(); var orderedIngredients = new List <Ingredient>(); var commandTextOrderIngredientsPricePerOunce = @"SELECT DISTINCT ingredients.name, price_per_ounce, ingredients.recipe_id FROM ingredients JOIN costs ON ingredients.name=costs.name ORDER BY costs.price_per_ounce DESC;"; db.queryItems(commandTextOrderIngredientsPricePerOunce, reader => { var ingredient = new Ingredient((string)(reader["name"])); ingredient.pricePerOunce = (decimal)(reader["price_per_ounce"]); orderedIngredients.Add(ingredient); return(ingredient); }); return(orderedIngredients); }
public Ingredient queryCostsTableByName(Ingredient i) { var db = new DatabaseAccess(); var myIngredient = new Ingredient(); var commandTextQueryCostTableIngredient = string.Format(@"SELECT * FROM costs WHERE name='{0}';", i.name); db.queryItems(commandTextQueryCostTableIngredient, reader => { myIngredient.name = (string)reader["name"]; myIngredient.ingredientId = (int)reader["ing_id"]; myIngredient.sellingWeight = (string)reader["selling_weight"]; //myIngredient.sellingWeightInOunces = (decimal)reader["selling_weight_ounces"]; myIngredient.pricePerOunce = (decimal)reader["price_per_ounce"]; myIngredient.itemId = (int)reader["item_id"]; myIngredient.sellingPrice = (decimal)reader["selling_price"]; return(myIngredient); }); return(myIngredient); }
public List <Ingredient> orderIngredientsByExpirationDateAsc() { var db = new DatabaseAccess(); var ExpiringIngredients = new List <Ingredient>(); var commandTextOrderIngredientsByExpirationDate = @"SELECT name, expiration_date FROM ingredients ORDER BY expiration_date ASC"; db.queryItems(commandTextOrderIngredientsByExpirationDate, reader => { var ingredient = new Ingredient((string)(reader["name"])); var expirationDate = (string)(reader["expiration_date"]); ingredient.expirationDate = convertStringMMDDYYYYToDateYYYYMMDD(expirationDate); //ingredient.expirationDate = (DateTime)(reader["expiration_date"]); ExpiringIngredients.Add(ingredient); return(ingredient); }); return(ExpiringIngredients); }
public List <string> myDistinctIngredientNamesSorted() { var db = new DatabaseAccess(); var uniqueIngredientNames = new List <string>(); var orderIngredientsByName = @"SELECT DISTINCT name FROM ingredients ORDER BY name ASC;"; db.queryItems(orderIngredientsByName, reader => { var ingredient = new Ingredient(reader["name"].ToString()); if (!uniqueIngredientNames.Contains(ingredient.name)) { uniqueIngredientNames.Add(ingredient.name); } return(ingredient); }); return(uniqueIngredientNames); }
public Ingredient queryIngredientFromDensityTableByName(Ingredient i) { var db = new DatabaseAccess(); var ingredientDensityTableInformation = new Ingredient(); var commandTextQueryDensityByName = string.Format(@"SELECT * FROM densities WHERE name='{0}';", i.name); db.queryItems(commandTextQueryDensityByName, reader => { ingredientDensityTableInformation.name = (string)reader["name"]; ingredientDensityTableInformation.ingredientId = (int)reader["ing_id"]; ingredientDensityTableInformation.density = (decimal)reader["density"]; ingredientDensityTableInformation.sellingWeight = (string)reader["selling_weight"]; ingredientDensityTableInformation.sellingWeightInOunces = (decimal)reader["selling_weight_ounces"]; ingredientDensityTableInformation.sellingPrice = (decimal)reader["selling_price"]; ingredientDensityTableInformation.pricePerOunce = (decimal)reader["price_per_ounce"]; return(ingredientDensityTableInformation); }); return(ingredientDensityTableInformation); }
public Ingredient queryConsumptionTableRowByName(Ingredient i) { var db = new DatabaseAccess(); var consumptionTableRow = new Ingredient(); var commandTextGetConsumptionRow = string.Format(@"SELECT * FROM consumption WHERE name='{0}';", i.name); db.queryItems(commandTextGetConsumptionRow, reader => { consumptionTableRow.name = (string)reader["name"]; consumptionTableRow.ingredientId = (int)reader["id"]; consumptionTableRow.density = (decimal)reader["density"]; consumptionTableRow.ouncesConsumed = (decimal)reader["ounces_consumed"]; consumptionTableRow.ouncesRemaining = (decimal)reader["ounces_remaining"]; consumptionTableRow.restock = (int)reader["refill"]; consumptionTableRow.measurement = (string)reader["measurement"]; return(consumptionTableRow); }); return(consumptionTableRow); }
public List <Ingredient> queryConsumptionTable() { var db = new DatabaseAccess(); //var ingredientInformation = db.queryItems("select * from consumption order by name asc", reader => { var commandText = @"SELECT * FROM consumption;"; //ORDER BY consumption.name ASC;"; var ingredientInformation = db.queryItems(commandText, reader => { var ingredient = new Ingredient(reader["name"].ToString()); ingredient.ingredientId = (int)reader["id"]; ingredient.density = (decimal)reader["density"]; ingredient.ouncesConsumed = (decimal)reader["ounces_consumed"]; ingredient.ouncesRemaining = (decimal)reader["ounces_remaining"]; ingredient.restock = (int)reader["refill"]; ingredient.measurement = (string)reader["measurement"]; return(ingredient); }); return(ingredientInformation); }
public List <Ingredient> GetRecipeIngredients(Recipe r) { var db = new DatabaseAccess(); var joinCommand = @"SELECT ingredients.name, ingredients.measurement, ingredients.price_measured_ingredient FROM recipes JOIN ingredients ON recipes.recipe_id=ingredients.recipe_id;"; var myRecipeIngredients = db.queryItems(joinCommand, reader => { //var recipe = new Recipe(reader["recipes.name"].ToString()); var ingredient = new Ingredient(reader["name"].ToString()); ingredient.measurement = (string)reader["measurement"].ToString(); ingredient.priceOfMeasuredConsumption = (decimal)reader["price_measured_ingredient"]; return(ingredient); }); return(myRecipeIngredients); }
public List <Recipe> queryRecipes() { var db = new DatabaseAccess(); var count = 1; var MyRecipeBox = db.queryItems("select * from recipes", reader => { var recipe = new Recipe(reader["recipe_name"].ToString()); recipe.yield = (int)reader["yield"]; recipe.id = (int)reader["recipe_id"]; recipe.aggregatedPrice = (decimal)reader["aggregated_price"]; recipe.pricePerServing = (decimal)reader["price_per_serving"]; return(recipe); }); foreach (var recipe in MyRecipeBox) { recipe.id = count++; } return(MyRecipeBox); }
public List <Ingredient> queryDensitiesTableAllRows() { var db = new DatabaseAccess(); //var ingredientInformation = db.queryItems("select * from densities ", reader => { var commandText = @"SELECT * FROM densities ORDER BY ingredient ASC;"; var ingredientInformation = db.queryItems(commandText, reader => { var ingredient = new Ingredient(reader["name"].ToString()); ingredient.ingredientId = (int)reader["ing_id"]; ingredient.density = (decimal)reader["density"]; ingredient.sellingWeight = (string)reader["selling_weight"]; ingredient.sellingWeightInOunces = (decimal)reader["selling_weight_ounces"]; ingredient.sellingPrice = (decimal)reader["selling_price"]; ingredient.pricePerOunce = (decimal)reader["price_per_ounce"]; return(ingredient); }); return(ingredientInformation); }
public Ingredient queryIngredientFromIngredientsTableByName(Ingredient i) { var db = new DatabaseAccess(); var queriedIngredient = new Ingredient(); var commandTextQueryIngredient = string.Format(@"SELECT * FROM ingredients where name='{0}' AND ing_id={1};", i.name, i.ingredientId); db.queryItems(commandTextQueryIngredient, reader => { queriedIngredient.name = (string)(reader["name"]); queriedIngredient.measurement = (string)(reader["measurement"]); queriedIngredient.recipeId = (int)(reader["recipe_id"]); queriedIngredient.ingredientId = (int)(reader["ing_id"]); queriedIngredient.classification = (string)(reader["ingredient_classification"]); queriedIngredient.typeOfIngredient = (string)(reader["ingredient_type"]); queriedIngredient.priceOfMeasuredConsumption = (decimal)(reader["price_measured_ingredient"]); queriedIngredient.itemResponseName = (string)(reader["item_response_name"]); var expirationDate = (string)(reader["expiration_date"]); queriedIngredient.expirationDate = convertStringMMDDYYYYToDateYYYYMMDD(expirationDate); return(queriedIngredient); }); return(queriedIngredient); }
public List <Ingredient> queryAllIngredientsFromIngredientTable() { var db = new DatabaseAccess(); var count = 1; var myIngredientBox = db.queryItems("select * from ingredients", reader => { var ingredient = new Ingredient(reader["name"].ToString()); ingredient.measurement = (string)reader["measurement"]; ingredient.recipeId = (int)reader["recipe_id"]; ingredient.priceOfMeasuredConsumption = (decimal)reader["price_measured_ingredient"]; ingredient.itemId = (int)reader["item_id"]; ingredient.typeOfIngredient = (string)reader["ingredient_type"]; ingredient.classification = (string)reader["ingredient_classification"]; ingredient.expirationDate = convertStringMMDDYYYYToDateYYYYMMDD((string)reader["expiration_date"]); return(ingredient); }); foreach (var ingredient in myIngredientBox) { ingredient.ingredientId = count++; } return(myIngredientBox); }
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(); }