public void insertListIntoDensityInfoDatabase(List <Ingredient> MyIngredients)
{
var read = new Reader(); //the filename below for the moment is hardcoded... but i would prefer to not keep it that way... bad business
var db = new DatabaseAccess();
var myDensityTable = queryDensityInfoTable();
var myDensityInfoTableIngredients = new List <string>();
foreach (var ingredient in myDensityTable)
{
myDensityInfoTableIngredients.Add(ingredient.typeOfIngredient);
}
for (int i = 0; i < MyIngredients.Count(); i++)
{
if (!myDensityInfoTableIngredients.Contains(MyIngredients[i].typeOfIngredient))
{
var commandText = @"Insert into densityInfo (ingredient, density) values (@ingredient, @density);";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@ingredient", MyIngredients[i].name);
cmd.Parameters.AddWithValue("@density", MyIngredients[i].density);
return(cmd);
});
}
}
var myDensityInfoTable = queryDensityInfoTable();
}
public void dropConsumptionTableIfExists(string table)
{
var db = new DatabaseAccess();
var drop = @"IF OBJECT_ID('dbo." + table + " ', 'U') IS NOT NULL DROP TABLE dbo." + table + ";";
db.executeVoidQuery(drop, a => a);
}
public void insertDensityTextFileIntoDensityInfoDatabase()
{
var read = new Reader(); //the filename below for the moment is hardcoded...
var db = new DatabaseAccess();
var DensityTextDatabaseDictionary = read.ReadDensityTextFile(@"C: \Users\Rachel\Documents\Visual Studio 2015\Projects\RachelsRosesWebPages\RachelsRosesWebPages\densityTxtDatabase.txt");
var myDensityTable = queryDensityInfoTable();
var myDensityTableNames = new List <string>();
foreach (var ingredient in myDensityTable)
{
myDensityTableNames.Add(ingredient.name);
}
foreach (var ingredient in DensityTextDatabaseDictionary)
{
if (!myDensityTableNames.Contains(ingredient.Key))
{
var commandText = @"Insert into densityInfo (ingredient, density) values (@ingredient, @density);";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@ingredient", ingredient.Key);
cmd.Parameters.AddWithValue("@density", ingredient.Value);
return(cmd);
});
}
}
var myDensityTableAfter = queryDensityInfoTable();
}
public void refillIngredientInConsumptionDatabase(Ingredient i, string sellingWeightToRefill)
{
var db = new DatabaseAccess();
var convert = new ConvertWeight();
var myConsumptionTable = queryConsumptionTable();
var sellingWeightToRefillInOunces = 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 + sellingWeightToRefillInOunces;
break;
}
}
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);
});
}
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 void insertIngredient(Ingredient i, Recipe r)
{
var db = new DatabaseAccess();
if ((i.sellingPrice == 0m && !i.classification.ToLower().Contains("dairy")) || (i.sellingPrice == 0m && !i.classification.ToLower().Contains("eggs")))
{
// if (i.sellingPrice == 0m && (!i.classification.ToLower().Contains("dairy")) || (!i.classification.ToLower().Contains("egg"))) {
myItemResponse = returnItemResponse(i);
if (i.itemId == 0)
{
i.itemId = myItemResponse.itemId;
}
if (string.IsNullOrEmpty(i.itemResponseName))
{
i.itemResponseName = myItemResponse.name;
}
if (i.sellingPrice == 0m)
{
i.sellingPrice = myItemResponse.salePrice;
}
}
if ((i.classification.ToLower().Contains("dairy")) || (i.classification.ToLower().Contains("egg")))
{
i.itemResponseName = " ";
}
if (string.IsNullOrEmpty(i.classification))
{
i.classification = " ";
}
if (i.expirationDate == null)
{
i.expirationDate = new DateTime();
}
var expirationDateString = convertDateToStringMMDDYYYY(i.expirationDate);
if ((i.classification.ToLower() == "dairy" || i.classification.ToLower() == "egg" || i.classification.ToLower() == "eggs") && expirationDateString == "01/01/0000")
{
throw new Exception("Please enter an expiration date for dairy and egg items");
}
var commandText = @"Insert into ingredients(recipe_id, name, measurement, price_measured_ingredient, item_id, ingredient_type, ingredient_classification, item_response_name, expiration_date)
values (@rid, @name, @measurement, @price_measured_ingredient, @item_id, @ingredient_type, @ingredient_classification, @item_response_name, @expiration_date);";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@rid", r.id);
cmd.Parameters.AddWithValue("@name", i.name);
cmd.Parameters.AddWithValue("@measurement", i.measurement);
cmd.Parameters.AddWithValue("@price_measured_ingredient", i.priceOfMeasuredConsumption);
cmd.Parameters.AddWithValue("@item_id", i.itemId);
cmd.Parameters.AddWithValue("@ingredient_type", i.typeOfIngredient);
cmd.Parameters.AddWithValue("@ingredient_classification", i.classification);
cmd.Parameters.AddWithValue("@item_response_name", i.itemResponseName);
cmd.Parameters.AddWithValue("@expiration_date", convertDateToStringMMDDYYYY(i.expirationDate));
return(cmd);
});
//var myIngredients = queryAllIngredientsFromIngredientTable();
//var myIngredientFull = db.queryAllRelevantTablesSQL(i);
}
public void InitializeDensityInformationTable()
{
var db = new DatabaseAccess();
dropDensityInformationTableIfExists("densityInfo");
db.executeVoidQuery(@"create table densityInfo (
ingredient nvarchar(max),
density decimal(4,2)
);", a => a);
}
public void initializeCostTable()
{
var db = new DatabaseAccess();
dropCostTableIfExists("costs");
db.executeVoidQuery(@"create table costs (
ingredient nvarchar(max),
density decimal(4,2)
);", a => a);
}
public void DeleteIngredientFromDensitiesTable(Ingredient i)
{
var db = new DatabaseAccess();
var deleteCommand = "delete from densities where ing_id=@ing_id";
db.executeVoidQuery(deleteCommand, cmd => {
cmd.Parameters.AddWithValue("@ing_id", i.ingredientId);
return(cmd);
});
}
public void DeleteIngredientFromCostTable(Ingredient i)
{
var db = new DatabaseAccess();
var deleteCommand = "delete from costs where ing_id=@ing_id"; //if needed, : AND name=@name;
db.executeVoidQuery(deleteCommand, cmd => {
cmd.Parameters.AddWithValue("@ing_id", i.ingredientId);
return(cmd);
});
}
public void DeleteIngredientFromConsumptionTable(Ingredient i)
{
var db = new DatabaseAccess();
var deleteCommand = "delete from consumption where name=@name";
db.executeVoidQuery(deleteCommand, cmd => {
cmd.Parameters.AddWithValue("@name", i.name);
return(cmd);
});
}
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 initializeConsumptionOuncesConsumedTable()
{
var db = new DatabaseAccess();
dropIfConsumptionOuncesConsumptionTableExists("consumption_ounces_consumed");
db.executeVoidQuery(@"create table consumption_ounces_consumed (
ingredient nvarchar(max),
ounces_consumed decimal(4,2),
ounces_remaining decimal(5,2),
measurement nvarchar(250)
);", a => a);
}
public void insertIngredientIntoDensityInfoDatabase(Ingredient i)
{
var rest = new MakeRESTCalls();
var db = new DatabaseAccess();
insertDensityTextFileIntoDensityInfoDatabase();
var myUpdatedDensityInfoTable = queryDensityInfoTable();
var myMilkAndEggDensityInfoIngredients = new List <Ingredient>();
foreach (var ingredient in myUpdatedDensityInfoTable)
{
if (ingredient.name.ToLower().Contains("milk") || ingredient.name.ToLower().Contains("egg"))
{
myMilkAndEggDensityInfoIngredients.Add(ingredient);
}
}
var countSimilarIngredients = 0;
foreach (var ingredient in myUpdatedDensityInfoTable)
{
if (i.typeOfIngredient.ToLower().Contains("milk") || i.typeOfIngredient.ToLower().Contains("egg"))
{
foreach (var dairyOrEggIngredient in myMilkAndEggDensityInfoIngredients)
{
if (i.typeOfIngredient == dairyOrEggIngredient.name)
{
countSimilarIngredients++;
break;
}
}
break;
}
else
{
if (rest.SimilaritesInStrings(i.typeOfIngredient, ingredient.name))
{
countSimilarIngredients++;
break;
}
}
}
if (countSimilarIngredients == 0)
{
var commandText = @"Insert into densityInfo (ingredient, density) values (@ingredient, @density);";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@ingredient", i.typeOfIngredient);
cmd.Parameters.AddWithValue("@density", i.density);
return(cmd);
});
}
//all this is doing is determining if the density table already has an ingredient with said name, if so, then it won't add it, if the table doesn't have that name, it will insert it with the density
var myDensityInfoDatabase = queryDensityInfoTable();
}
public void InsertRecipe(Recipe r)
{
var db = new DatabaseAccess();
var commandText = "Insert into recipes (recipe_name, yield, aggregated_price, price_per_serving) values (@recipe_name, @yield, @aggregated_price, @price_per_serving);";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@recipe_name", r.name);
cmd.Parameters.AddWithValue("@yield", r.yield);
cmd.Parameters.AddWithValue("@aggregated_price", r.aggregatedPrice);
cmd.Parameters.AddWithValue("@price_per_serving", r.pricePerServing);
return(cmd);
});
}
public void initializeRecipeTable()
{
var db = new DatabaseAccess();
dropIfRecipeTableExists("recipes");
db.executeVoidQuery(@"create table recipes (
recipe_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
recipe_name nvarchar(max),
yield int,
aggregated_price decimal(5, 2),
price_per_serving decimal (5,2)
);", a => a);
}
//will this be enough for records? Should I include the ingredientId?
public void updateIngredientInConsumptionouncesConsumed(Ingredient i)
{
var db = new DatabaseAccess();
var commandText = @"Update consumption_ounces_consumed set ounces_consumed=@ounces_consumed, ounces_remaining=@ounces_remaining where name=@name and measurement=@measurement;";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@measurement", i.measurement);
cmd.Parameters.AddWithValue("@ounces_consumed", i.ouncesConsumed);
cmd.Parameters.AddWithValue("@name", i.name);
cmd.Parameters.AddWithValue("@ounces_remaining", i.ouncesRemaining);
return(cmd);
});
}
public void DeleteIngredientFromIngredientTableIngIds(Ingredient i)
{
var db = new DatabaseAccess();
i.name = i.name.Trim();
i.measurement = i.measurement.Trim();
var delete = "delete from ingredients where name=@name AND ing_id=@ing_id";
db.executeVoidQuery(delete, cmd => {
cmd.Parameters.AddWithValue("@name", i.name);
cmd.Parameters.AddWithValue("@ing_id", i.ingredientId);
return(cmd);
});
}
public void initializeRecipeTable()
{
var db = new DatabaseAccess();
dropConsumptionTableIfExists("consumption");
db.executeVoidQuery(@"create table consumption (
ingredient nvarchar(max),
density decimal(4,2),
ounces_consumed decimal(5,2),
ounces_remaining decimal(5,2),
measurement nvarchar(250),
refill int default 0
);", a => a);
}
public void updateDensityTable(Ingredient i)
{
var db = new DatabaseAccess();
var commandText = "update densities set name=@name, density=@density, selling_weight=@selling_weight, selling_weight_ounces=@selling_weight_ounces, selling_price=@selling_price, price_per_ounce=@price_per_ounce where ing_id=@ing_id";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@ing_id", i.ingredientId);
cmd.Parameters.AddWithValue("@name", i.name);
cmd.Parameters.AddWithValue("@density", i.density);
cmd.Parameters.AddWithValue("@selling_weight", i.sellingWeight);
cmd.Parameters.AddWithValue("@selling_weight_ounces", i.sellingWeightInOunces);
cmd.Parameters.AddWithValue("@selling_price", i.sellingPrice);
cmd.Parameters.AddWithValue("@price_per_ounce", i.pricePerOunce);
return(cmd);
});
}
public void updateCostDataTable(Ingredient i)
{
var db = new DatabaseAccess();
var myCostTable = queryCostTable();
var commandText = @"Update costs set name=@name, selling_weight=@selling_weight, selling_price=@selling_price, price_per_ounce=@price_per_ounce where ing_id=@ing_id;";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@ing_id", i.ingredientId);
cmd.Parameters.AddWithValue("@name", i.name);
cmd.Parameters.AddWithValue("@selling_weight", i.sellingWeight);
cmd.Parameters.AddWithValue("@selling_price", i.sellingPrice);
cmd.Parameters.AddWithValue("@price_per_ounce", getPricePerOunce(i));
cmd.Parameters.AddWithValue("@item_id", i.itemId);
return(cmd);
});
var myUpdatedCostTable = queryCostTable();
}
public void insertIngredientDensityData(Ingredient i)
{
var convert = new ConvertWeight();
var db = new DatabaseAccess();
var dbDensityInformation = new DatabaseAccessDensityInformation();
if (i.sellingPrice == 0m)
{
myItemResponse = returnItemResponse(i);
}
i.density = dbDensityInformation.queryDensityTableRowDensityValueByName(i);
if (i.sellingPrice == 0m)
{
i.sellingPrice = myItemResponse.salePrice;
}
if (i.classification.ToLower() == "egg" || i.classification.ToLower() == "eggs")
{
i.sellingWeightInOunces = convert.NumberOfEggsFromSellingQuantity(i.sellingWeight);
}
else
{
i.sellingWeightInOunces = convert.ConvertWeightToOunces(i.sellingWeight);
}
if (i.sellingWeightInOunces == 0m)
{
throw new Exception("Selling Weight In Ounces is 0; please check that your Selling Weight is an appopriate weight.");
}
i.pricePerOunce = Math.Round((i.sellingPrice / i.sellingWeightInOunces), 4);
if (string.IsNullOrEmpty(i.classification))
{
i.classification = " ";
}
var commandText = @"Insert into densities (name, density, selling_weight, selling_weight_ounces, selling_price, price_per_ounce)
values (@name, @density, @selling_weight, @selling_weight_ounces, @selling_price, @price_per_ounce);";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@name", i.name);
cmd.Parameters.AddWithValue("@density", i.density);
cmd.Parameters.AddWithValue("@selling_weight", i.sellingWeight);
cmd.Parameters.AddWithValue("@selling_price", i.sellingPrice);
cmd.Parameters.AddWithValue("@selling_weight_ounces", i.sellingWeightInOunces);
cmd.Parameters.AddWithValue("@price_per_ounce", i.pricePerOunce);
return(cmd);
});
}
//the reason why this is void and access the database apart from the method that calls this (UpdateConsumptionTable) is the ouncesRemaining aren't set in the consumption table yet, i would get incorrect data
public void doesIngredientNeedRestocking(Ingredient i)
{
var db = new DatabaseAccess();
var consumptionTableIngredientRow = queryConsumptionTableRowByName(i);
//var ingredientOuncesRemaining = getOuncesRemainingFromConsumptionTableFromIngredient(i);
var doubleOunces = doubleAverageOuncesConsumed(i);
var intBool = consumptionTableIngredientRow.ouncesRemaining <= doubleOunces ? 1 : 0;
if (consumptionTableIngredientRow.restock != intBool)
{
var commandTextEnterRestockValue = @"UPDATE consumption set refill=@refill where name=@name";
db.executeVoidQuery(commandTextEnterRestockValue, cmd => {
cmd.Parameters.AddWithValue("@refill", intBool);
cmd.Parameters.AddWithValue("@name", i.name);
return(cmd);
});
}
}
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 void UpdateRecipe(Recipe r)
{
var db = new DatabaseAccess();
var myRecipe = queryRecipeFromRecipesTableByName(r);
if (myRecipe.yield != r.yield)
{
myRecipe.yield = r.yield;
}
var updatedRecipe = GetFullRecipeAndFullIngredientsForRecipe(r);
var commandText = "update recipes set recipe_name=@recipe_name, yield=@yield, aggregated_price=@aggregated_price where recipe_id=@rid;";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@recipe_name", r.name);
cmd.Parameters.AddWithValue("@rid", r.id);
cmd.Parameters.AddWithValue("@yield", r.yield);
cmd.Parameters.AddWithValue("@aggregated_price", updatedRecipe.aggregatedPrice);
cmd.Parameters.AddWithValue("@price_per_serving", updatedRecipe.pricePerServing);
return(cmd);
});
}
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 updateDensityInfoTable(Ingredient myIngredient)
{
var db = new DatabaseAccess();
var myDensityTableInfo = queryDensityInfoTable();
var myDensityTableInfoNames = new List <string>();
foreach (var ingredient in myDensityTableInfo)
{
myDensityTableInfoNames.Add(ingredient.name);
}
if (!myDensityTableInfoNames.Contains(myIngredient.typeOfIngredient))
{
insertIngredientIntoDensityInfoDatabase(myIngredient);
}
else
{
var commandText = @"Update densityInfo set density=@density where ingredient=@ingredient;";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@ingredient", myIngredient.typeOfIngredient);
cmd.Parameters.AddWithValue("@density", myIngredient.density);
return(cmd);
});
}
}
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 insertIngredientCostDataCostTable(Ingredient i)
{
var db = new DatabaseAccess();
var dbD = new DatabaseAccessDensities();
var convert = new ConvertWeight();
var myIngredientDensityInformation = dbD.queryIngredientFromDensityTableByName(i);
if (i.classification.ToLower().Contains("egg"))
{
i.sellingWeightInOunces = convert.NumberOfEggsFromSellingQuantity(i.sellingWeight);
i.pricePerOunce = i.sellingPrice / i.sellingWeightInOunces;
}
var commandText = @"Insert into costs (name, selling_weight, selling_price, price_per_ounce, item_id) values (@name, @selling_weight, @selling_price, @price_per_ounce, @item_id);";
db.executeVoidQuery(commandText, cmd => {
cmd.Parameters.AddWithValue("@ing_id", i.ingredientId);
cmd.Parameters.AddWithValue("@name", i.name);
cmd.Parameters.AddWithValue("@selling_weight", i.sellingWeight);
cmd.Parameters.AddWithValue("@selling_price", i.sellingPrice);
cmd.Parameters.AddWithValue("@price_per_ounce", i.pricePerOunce);
cmd.Parameters.AddWithValue("@item_id", i.itemId);
return(cmd);
});
}
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();
}