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