public void LoadFoodstuff(FoodStuff inFS)
{
//fill out controls based on what inFS contains
fstoUpdate = inFS;
txtName.Text = inFS.Name;
txtServingSize.Text = inFS.Servings.ToString();
txtPriceSold.Text = inFS.Cost.ToString();
txtPrepTime.Text = inFS.PrepTime.ToString();
txtCookTime.Text = inFS.CookTime.ToString();
txtDirections.Text = inFS.Directions;
foreach (string s in inFS.ReturnTagList())
{
lbxTags.Items.Add(s);
}
//get recipematerials for it...
List <Recipe> inFSmats = DataConnection.ListOfIngredients(inFS.ID);
foreach (Recipe r in inFSmats)
{
ListViewItem lvi = new ListViewItem();
lvi.Text = r.FractionAmount();
lvi.SubItems.Add(r.Unit);
lvi.SubItems.Add(DataConnection.GetFoodstuffWithID(r.MadeOf).Name);
lsvIngredients.Items.Add(lvi);
}
}
public static List <FoodStuff> ListAllIngredients()
{
//displays all foodstuffs that ARE atomic items (that is, base ingredients)
//this is determined by the RecipeMaterials entries: items where the Makes and MadeOf fields are different
//are the ones we DO want this function to return.
List <FoodStuff> lstFoods = new List <FoodStuff>();
try
{
string query = "Select * from Foodstuff where FoodstuffID in (select Makes from RecipeMaterials where Makes = MadeOf)"; //Not equals for Access is <> not !=
OleDbCommand cmd = new OleDbCommand(query, conn);
DataConnection.OpenConnection();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
FoodStuff fs = new FoodStuff(reader.GetString(0),
reader.GetString(1));
lstFoods.Add(fs);
}
reader.Close();
}
catch (Exception ex)
{
//don't do anything, apparently can't post messageboxes out of this thing
}
finally
{
DataConnection.CloseConnection();
}
return(lstFoods);
}
public void LoadFoodstuff(FoodStuff inFS)
{
//fill out controls based on what inFS contains
fstoUpdate = inFS;
txtName.Text = inFS.Name;
txtServingSize.Text = inFS.Servings.ToString();
txtPriceSold.Text = inFS.Cost.ToString();
txtPrepTime.Text = inFS.PrepTime.ToString();
txtCookTime.Text = inFS.CookTime.ToString();
txtDirections.Text = inFS.Directions;
foreach (string s in inFS.ReturnTagList())
{
lbxTags.Items.Add(s);
}
//get recipematerials for it...
List<Recipe> inFSmats = DataConnection.ListOfIngredients(inFS.ID);
foreach (Recipe r in inFSmats)
{
ListViewItem lvi = new ListViewItem();
lvi.Text = r.FractionAmount();
lvi.SubItems.Add(r.Unit);
lvi.SubItems.Add(DataConnection.GetFoodstuffWithID(r.MadeOf).Name);
lsvIngredients.Items.Add(lvi);
}
}
public static List <FoodStuff> ListAllFoodstuffs()
{
//displays all foodstuffs that are not atomic items (that is, base ingredients)
//this is determined by the RecipeMaterials entries: items where the Makes and MadeOf fields are identical
//are atomic items. These are the ones we DO NOT want this function to return.
List <FoodStuff> lstFoods = new List <FoodStuff>();
try
{
string query = "Select * from Foodstuff where FoodstuffID in (select Makes from RecipeMaterials where Makes <> MadeOf)"; //Not equals for Access is <> not !=
OleDbCommand cmd = new OleDbCommand(query, conn);
DataConnection.OpenConnection();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
List <Recipe> lstMats = DataConnection.ListOfIngredients(reader.GetString(0));
FoodStuff fs = new FoodStuff(reader.GetString(0),
reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1.0 : reader.GetDouble(5),
reader.IsDBNull(6) ? -1 : reader.GetInt32(6),
null,
lstMats);
//tokenize the tags from their long string stored in the database.
if (!(reader[7] is System.DBNull))
{
string[] strTagList = reader.GetString(7).Split(',');
if (strTagList.Length > 0)
{
foreach (string t in strTagList)
{
fs.AddTag(t);
}
}
}
lstFoods.Add(fs);
}
reader.Close();
}
catch (Exception ex)
{
//don't do anything, apparently can't post messageboxes out of this thing
ex.ToString();
}
finally
{
DataConnection.CloseConnection();
}
return(lstFoods);
}
public static List <FoodStuff> FindFoodstuffsNamed(string inName)
{
//return a list of foodstuffs whose names contain the passed in value
//does not distinguish between base ingredients and final items.
List <FoodStuff> lstFoods = new List <FoodStuff>();
try
{
string query = "Select * from Foodstuff where Name like ?";
OleDbCommand cmd = new OleDbCommand(query, conn);
cmd.Parameters.Add("?", OleDbType.VarChar).Value = inName;
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
List <Recipe> lstMats = DataConnection.ListOfIngredients(reader.GetString(0));
FoodStuff fs = new FoodStuff(reader.GetString(0),
reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1.0 : reader.GetDouble(5),
reader.IsDBNull(6) ? -1 : reader.GetInt32(6),
null,
lstMats);
//tokenize the tags from their long string stored in the database.
string[] strTagList = reader.IsDBNull(7) ? new string[0] : reader.GetString(7).Split(',');
foreach (string t in strTagList)
{
fs.AddTag(t);
}
lstFoods.Add(fs);
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
DataConnection.CloseConnection();
}
return(lstFoods);
}
public static FoodStuff GetFoodstuffWithID(string fsID)
{
//really simple, match the foodstuff ID passed in with the ID in the table and return it.
FoodStuff fs = new FoodStuff();
try
{
string query = "Select * from Foodstuff where FoodstuffID like ?";
OleDbCommand cmd = new OleDbCommand(query, conn);
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fsID;
OleDbDataReader reader = cmd.ExecuteReader();
reader.Read();
string id = reader.IsDBNull(0) ? "" : reader.GetString(0);
List <Recipe> lstMats = DataConnection.ListOfIngredients(reader.GetString(0));
fs = new FoodStuff(reader.GetString(0),
reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1.0 : reader.GetDouble(5),
reader.IsDBNull(6) ? -1 : reader.GetInt32(6),
null,
lstMats);
//tokenize the tags from their long string stored in the database.
string[] strTagList = reader.IsDBNull(7) ? new string[0] : reader.GetString(7).Split(',');
if (strTagList.Length != 0)
{
foreach (string t in strTagList)
{
fs.AddTag(t);
}
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
DataConnection.CloseConnection();
}
return(fs);
}
private void Form1_Load(object sender, EventArgs e)
{
fsTestItem = new FoodStuff();
lblTestItem.Text = fsTestItem.ToString();
string[] strTagList = fsTestItem.GetTags().Split(',');
foreach (string element in strTagList)
{
cboTags.Items.Add(element);
}
lstRecipes = DataConnection.ListOfIngredients();
foreach (Recipe r in lstRecipes)
{
ListViewItem lvi = new ListViewItem(r.Makes);
lvi.SubItems.Add(r.MadeOf);
lvi.SubItems.Add(r.FractionAmount());
lvi.SubItems.Add(r.Unit);
lbxRecipeList.Items.Add(lvi);
}
}
private void btnSaveRecipe_Click(object sender, EventArgs e)
{
/*
* Here's what this is needing to do:
* Validate the various fields on the form:
* Name must exist
* Rest are optional but if they do exist:
* PriceSold is a double
* ServingSize, PrepTime, CookTime are integers
* Check if entries in the ingredients list (which are matched to foodstuffs) exist
* in the foodstuff master list.
* If they do exist, get their IDs and generate Recipe items to link this new item to them.
* If they do NOT exist, prompt user whether to make generic entries for them
* (i.e. ID and Name, rest being null)
* Then make Recipe entries from these new foodstuffs we just made.
* Once that's done, we can push to database...I think...
* -Dustin
*/
if (blnValidate()) //really this is the level you'd do validation at
{
DialogResult button = MessageBox.Show("Are you sure you want to save this data?", "Save Recipe", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (button == DialogResult.Yes)
{
//they clicked yes, go about adding it
//generate the id
string newID = txtName.Text.Substring(0, Math.Min(txtName.Text.Split(' ')[0].Length,4));//grab the first up to 4 characters of the name
//get the item count and add one.
newID += (DataConnection.NumFoodstuffs() + 1).ToString("0000#");
List<string> newTags = new List<String>();
if (lbxTags.Items.Count > 0)
{
for (int i = 0; i < lbxTags.Items.Count; i++)
{
newTags.Add(lbxTags.Items[i].ToString());
}
}
//temp fix for blank textboxes so the foodstuff constructor doesn't error out with a blank string
if (txtPrepTime.Text == "")
//convert to a double
txtPrepTime.Text = "-1";
if (txtCookTime.Text == "")
txtCookTime.Text = "-1";
if (txtServingSize.Text == "")
txtServingSize.Text = "-1";
if (txtPriceSold.Text == "")
txtPriceSold.Text = "-1.0";
List<Recipe> newItemIngredients = new List<Recipe>();
//if lsvIngredients is empty...atomic ingredient? Don't hand the list to the constructor.
if (lsvIngredients.Items.Count > 0)
{
foreach (ListViewItem lvi in lsvIngredients.Items)
{
//check to see whether the item exists or not by matching name, which is the third column of the listview.
if (DataConnection.FindFoodstuffsNamed(lvi.SubItems[2].Text).Count > 0)
{
//this is supposed to return true if a name match is found
//and add it to the new food's ingredient list
newItemIngredients.Add(new Recipe(newID, (DataConnection.FindFoodstuffsNamed(lvi.SubItems[2].Text)[0].ID),
lvi.SubItems[0].Text, lvi.SubItems[1].Text));
}
else
{
//Did not find a match...
//making dummy entries
//grab the first up to 4 characters of the name
string newPHID = lvi.SubItems[2].Text.Substring(0, Math.Min(lvi.SubItems[2].Text.Split(' ')[0].Length, 4));
newPHID += (DataConnection.NumFoodstuffs() + 1).ToString("0000#");
//new placeholder ID, name from listview's name column
FoodStuff fsPlaceholder = new FoodStuff(newPHID, lvi.SubItems[2].Text);
//should automatically generate the self-referencing RecipeMaterial stub...
DataConnection.AddFoodStuff(fsPlaceholder);
//then put this in the list for the actual food we're going to add
newItemIngredients.Add(new Recipe(newID, newPHID, lvi.SubItems[0].Text, lvi.SubItems[1].Text));
}
}
}
//build the new foodstuff we're adding
FoodStuff newFS = new FoodStuff(newID, txtName.Text, txtDirections.Text,
Convert.ToInt32(txtPrepTime.Text), Convert.ToInt32(txtCookTime.Text), Convert.ToDouble(txtPriceSold.Text),
Convert.ToInt32(txtServingSize.Text), newTags, newItemIngredients);
//send to database
DataConnection.AddFoodStuff(newFS);
}
//Then Clear the form and show a message stating the info was saved
lblSaved.Text = "Recipe was saved. Please enter new recipe";
var cntrlCollections = GetAll(this, typeof(TextBox));
foreach (Control ctrl in cntrlCollections)
{
if (ctrl is TextBox)
{
ctrl.Text = string.Empty;
}
}
lsvIngredients.Items.Clear();
lbxTags.Items.Clear();
timer1.Start();
}
else
{
MessageBox.Show(ErrMessage);
}
// FoodStuff fs = new FoodStuff(txtName.Text,
// if (blnValid = true)
//{
// Add stuff into food stuff class and into database
//}
{
}
}
public static FoodStuff GetFoodstuffWithID(string fsID)
{
//really simple, match the foodstuff ID passed in with the ID in the table and return it.
FoodStuff fs = new FoodStuff();
try
{
string query = "Select * from Foodstuff where FoodstuffID like ?";
OleDbCommand cmd = new OleDbCommand(query, conn);
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fsID;
OleDbDataReader reader = cmd.ExecuteReader();
reader.Read();
string id = reader.IsDBNull(0) ? "" : reader.GetString(0);
List<Recipe> lstMats = DataConnection.ListOfIngredients(reader.GetString(0));
fs = new FoodStuff(reader.GetString(0),
reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1.0 : reader.GetDouble(5),
reader.IsDBNull(6) ? -1 : reader.GetInt32(6),
null,
lstMats);
//tokenize the tags from their long string stored in the database.
string[] strTagList = reader.IsDBNull(7) ? new string[0] : reader.GetString(7).Split(',');
if (strTagList.Length != 0)
{
foreach (string t in strTagList)
fs.AddTag(t);
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
DataConnection.CloseConnection();
}
return fs;
}
private void btnSaveRecipe_Click(object sender, EventArgs e)
{
/*
* Here's what this is needing to do:
* Validate the various fields on the form:
* Name must exist
* Rest are optional but if they do exist:
* PriceSold is a double
* ServingSize, PrepTime, CookTime are integers
* Check if entries in the ingredients list (which are matched to foodstuffs) exist
* in the foodstuff master list.
* If they do exist, get their IDs and generate Recipe items to link this new item to them.
* If they do NOT exist, prompt user whether to make generic entries for them
* (i.e. ID and Name, rest being null)
* Then make Recipe entries from these new foodstuffs we just made.
* Once that's done, we can push to database...I think...
* -Dustin
*/
if (blnValidate()) //really this is the level you'd do validation at
{
DialogResult button = MessageBox.Show("Are you sure you want to save this data?", "Save Recipe", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (button == DialogResult.Yes)
{
//they clicked yes, go about adding it
//generate the id
string newID = txtName.Text.Substring(0, Math.Min(txtName.Text.Split(' ')[0].Length, 4));//grab the first up to 4 characters of the name
//get the item count and add one.
newID += (DataConnection.NumFoodstuffs() + 1).ToString("0000#");
List <string> newTags = new List <String>();
if (lbxTags.Items.Count > 0)
{
for (int i = 0; i < lbxTags.Items.Count; i++)
{
newTags.Add(lbxTags.Items[i].ToString());
}
}
//temp fix for blank textboxes so the foodstuff constructor doesn't error out with a blank string
if (txtPrepTime.Text == "")
{
//convert to a double
txtPrepTime.Text = "-1";
}
if (txtCookTime.Text == "")
{
txtCookTime.Text = "-1";
}
if (txtServingSize.Text == "")
{
txtServingSize.Text = "-1";
}
if (txtPriceSold.Text == "")
{
txtPriceSold.Text = "-1.0";
}
List <Recipe> newItemIngredients = new List <Recipe>();
//if lsvIngredients is empty...atomic ingredient? Don't hand the list to the constructor.
if (lsvIngredients.Items.Count > 0)
{
foreach (ListViewItem lvi in lsvIngredients.Items)
{
//check to see whether the item exists or not by matching name, which is the third column of the listview.
if (DataConnection.FindFoodstuffsNamed(lvi.SubItems[2].Text).Count > 0)
{
//this is supposed to return true if a name match is found
//and add it to the new food's ingredient list
newItemIngredients.Add(new Recipe(newID, (DataConnection.FindFoodstuffsNamed(lvi.SubItems[2].Text)[0].ID),
lvi.SubItems[0].Text, lvi.SubItems[1].Text));
}
else
{
//Did not find a match...
//making dummy entries
//grab the first up to 4 characters of the name
string newPHID = lvi.SubItems[2].Text.Substring(0, Math.Min(lvi.SubItems[2].Text.Split(' ')[0].Length, 4));
newPHID += (DataConnection.NumFoodstuffs() + 1).ToString("0000#");
//new placeholder ID, name from listview's name column
FoodStuff fsPlaceholder = new FoodStuff(newPHID, lvi.SubItems[2].Text);
//should automatically generate the self-referencing RecipeMaterial stub...
DataConnection.AddFoodStuff(fsPlaceholder);
//then put this in the list for the actual food we're going to add
newItemIngredients.Add(new Recipe(newID, newPHID, lvi.SubItems[0].Text, lvi.SubItems[1].Text));
}
}
}
//build the new foodstuff we're adding
FoodStuff newFS = new FoodStuff(newID, txtName.Text, txtDirections.Text,
Convert.ToInt32(txtPrepTime.Text), Convert.ToInt32(txtCookTime.Text), Convert.ToDouble(txtPriceSold.Text),
Convert.ToInt32(txtServingSize.Text), newTags, newItemIngredients);
//send to database
DataConnection.AddFoodStuff(newFS);
}
//Then Clear the form and show a message stating the info was saved
lblSaved.Text = "Recipe was saved. Please enter new recipe";
var cntrlCollections = GetAll(this, typeof(TextBox));
foreach (Control ctrl in cntrlCollections)
{
if (ctrl is TextBox)
{
ctrl.Text = string.Empty;
}
}
lsvIngredients.Items.Clear();
lbxTags.Items.Clear();
timer1.Start();
}
else
{
MessageBox.Show(ErrMessage);
}
// FoodStuff fs = new FoodStuff(txtName.Text,
// if (blnValid = true)
//{
// Add stuff into food stuff class and into database
//}
{
}
}
private void Form1_Load(object sender, EventArgs e)
{
fsTestItem = new FoodStuff();
lblTestItem.Text = fsTestItem.ToString();
string[] strTagList = fsTestItem.GetTags().Split(',');
foreach(string element in strTagList)
cboTags.Items.Add(element);
lstRecipes = DataConnection.ListOfIngredients();
foreach (Recipe r in lstRecipes)
{
ListViewItem lvi = new ListViewItem(r.Makes);
lvi.SubItems.Add(r.MadeOf);
lvi.SubItems.Add(r.FractionAmount());
lvi.SubItems.Add(r.Unit);
lbxRecipeList.Items.Add(lvi);
}
}
public static List<FoodStuff> ListAllIngredients()
{
//displays all foodstuffs that ARE atomic items (that is, base ingredients)
//this is determined by the RecipeMaterials entries: items where the Makes and MadeOf fields are different
//are the ones we DO want this function to return.
List<FoodStuff> lstFoods = new List<FoodStuff>();
try
{
string query = "Select * from Foodstuff where FoodstuffID in (select Makes from RecipeMaterials where Makes = MadeOf)"; //Not equals for Access is <> not !=
OleDbCommand cmd = new OleDbCommand(query, conn);
DataConnection.OpenConnection();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
FoodStuff fs = new FoodStuff(reader.GetString(0),
reader.GetString(1));
lstFoods.Add(fs);
}
reader.Close();
}
catch (Exception ex)
{
//don't do anything, apparently can't post messageboxes out of this thing
}
finally
{
DataConnection.CloseConnection();
}
return lstFoods;
}
//========================================================
//
//public static string AddFoodStuff(FoodStuff fs, List<Recipe> ingredients)
//{
// //insert a foodstuff entry in the Foodstuff table
// //will use comma-delineated string for the tags
// try
// {
// //this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
// //database will auto-default to NULL for the rest.
// string query = "insert into Foodstuff(FoodstuffID, Name"; //these two must always be there
// DataConnection.OpenConnection();
// OleDbDataReader rdItemCount = new OleDbCommand("Select Count(Name) from Foodstuff", conn).ExecuteReader();
// rdItemCount.Read();
// DataConnection.CloseConnection();
// OleDbCommand cmd = new OleDbCommand();
// cmd.Connection = conn;
// //if statements for each value to add, and its associated parameter
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.ID;// +Convert.ToInt32(rdItemCount[0]).ToString("0000#");
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Name;
// int intParameterCount = 2;
// if (fs.Directions != "")
// {
// query += ", Directions";
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Directions;
// intParameterCount++;
// }
// if (fs.PrepTime > -1) //use -1, items may still have 0 prep time
// {
// query += ", PrepTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.PrepTime;
// intParameterCount++;
// }
// if (fs.CookTime > -1)
// {
// query += ", CookTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.CookTime;
// intParameterCount++;
// }
// if (fs.Cost > 0.0) //unlikely anything is going to be free
// {
// query += ", Cost";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Cost;
// intParameterCount++;
// }
// if (fs.Servings > 0)
// {
// query += ", Servings";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Servings;
// intParameterCount++;
// }
// if (fs.GetTags() != "")
// {
// query += ", Tags";
// //Comma-and-space delineated list of the tags.
// //When tokenizing or splitting tags, use ", " for the demarcation.
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.GetTags();
// intParameterCount++;
// }
// query += ") Values(?";
// for (int i = 0; i < intParameterCount-1; i++)
// {
// query += ",?";
// }
// query += ")";
// cmd.CommandText = query;
// DataConnection.OpenConnection();
// cmd.ExecuteNonQuery();
// foreach (Recipe r in ingredients)
// AddRecipeItem(r);
// return "Item plus ingredients added.";
// }
// catch (Exception ex)
// {
// return ex.ToString();
// }
// finally
// {
// DataConnection.CloseConnection();
// }
//}
//public static string AddFoodStuff(FoodStuff fs, Recipe r)
//{
// //same as other but for just adding single Recipe elements instead of from a list.
// //insert a foodstuff entry in the Foodstuff table
// //will use comma-delineated string for the tags
// try
// {
// //this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
// //database will auto-default to NULL for the rest.
// string query = "insert into Foodstuff(FoodstuffID, Name"; //these two must always be there
// OleDbDataReader rdItemCount = new OleDbCommand("Select Count(Name) from Foodstuff", conn).ExecuteReader();
// rdItemCount.Read();
// OleDbCommand cmd = new OleDbCommand();
// cmd.Connection = conn;
// //fs.ID += Convert.ToInt32(rdItemCount[0]).ToString("0000#");
// //if statements for each value to add, and its associated parameter
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.ID;// +Convert.ToInt32(rdItemCount[0]).ToString("0000#");
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Name;
// int intParameterCount = 2;
// if (fs.Directions != "")
// {
// query += ", Directions";
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Directions;
// intParameterCount++;
// }
// if (fs.PrepTime > -1) //use -1, items may still have 0 prep time
// {
// query += ", PrepTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.PrepTime;
// intParameterCount++;
// }
// if (fs.CookTime > -1)
// {
// query += ", CookTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.CookTime;
// intParameterCount++;
// }
// if (fs.Cost > 0.0) //unlikely anything is going to be free
// {
// query += ", Cost";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Cost;
// intParameterCount++;
// }
// if (fs.Servings > 0)
// {
// query += ", Servings";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Servings;
// intParameterCount++;
// }
// if (fs.GetTags() != "")
// {
// query += ", Tags";
// //Comma-and-space delineated list of the tags.
// //When tokenizing or splitting tags, use ", " for the demarcation.
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.GetTags();
// intParameterCount++;
// }
// query += ") Values(?";
// for (int i = 1; i < intParameterCount; i++) //counter is one-based
// {
// query += ",?";
// }
// query += ")";
// cmd.CommandText = query;
// AddRecipeItem(r);
// DataConnection.OpenConnection();
// cmd.ExecuteNonQuery();
// return "Item plus ingredients added.";
// }
// catch (Exception ex)
// {
// return ex.ToString();
// }
// finally
// {
// DataConnection.CloseConnection();
// }
//}
public static void UpdateFoodstuff(FoodStuff updatedFS)
{
//need some way of identifying the column without passing in the name itself which might change.
//an enum won't work since that's just a bit of code associated with an integer.
//TODO:
//verify the record exists, if it does then update the designated column with the new value.
//Potentially ugly hack to make this work is to just update every field regardless of whether they were
//altered or not, overwriting with the same values for unaltered fields.
//it might be slower but would save the headache of trying to pick and choose the fields we need to write to.
try
{
//this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
//database will auto-default to NULL for the rest.
string query = "update foodstuff set "; //these two must always be there
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
//if statements for each value to add, and its associated parameter
query += "Name = ? ";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.Name;
int intParameterCount = 1;
if (updatedFS.Directions != "")
{
query += ", Directions = ?";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.Directions;
intParameterCount++;
}
if (updatedFS.PrepTime > -1) //use -1, items may still have 0 prep time
{
query += ", PrepTime = ?";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.PrepTime;
intParameterCount++;
}
if (updatedFS.CookTime > -1)
{
query += ", CookTime = ? ";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.CookTime;
intParameterCount++;
}
if (updatedFS.Cost > 0.0) //unlikely anything is going to be free
{
query += ", Cost = ? ";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.Cost;
intParameterCount++;
}
if (updatedFS.Servings > 0)
{
query += ", Servings = ?";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.Servings;
intParameterCount++;
}
if (updatedFS.GetTags() != "")
{
query += ", Tags = ? ";
//Comma-and-space delineated list of the tags.
//When tokenizing or splitting tags, use ", " for the demarcation.
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.GetTags();
intParameterCount++;
}
query += "where FoodstuffID = ?";
cmd.Parameters.Add("?",OleDbType.VarChar).Value = updatedFS.ID;
cmd.CommandText = query;
DataConnection.OpenConnection();
cmd.ExecuteNonQuery();
//item in fs's list isn't in ingredient list, so make (another) list of things we need to remove
//and do the exact opposite for those ingredients we need to add new entries for.
List<Recipe> IngredientsToDelete = new List<Recipe>();
List<Recipe> IngredientsToAdd = new List<Recipe>();
foreach (Recipe old_r in GetFoodstuffWithID(updatedFS.ID).ReturnIngredientsList())
{
//needs to match at least one item
bool match = false;
foreach (Recipe new_r in updatedFS.ReturnIngredientsList())
if (old_r == new_r)
match = true;
if (!match)
IngredientsToDelete.Add(old_r);
}
foreach (Recipe new_r in updatedFS.ReturnIngredientsList())
{
bool match = false;
foreach (Recipe old_r in GetFoodstuffWithID(updatedFS.ID).ReturnIngredientsList())
if (new_r == old_r)
match = true;
if(!match)
IngredientsToAdd.Add(new_r);
}
if (IngredientsToDelete.Count > 0)
foreach (Recipe r in IngredientsToDelete)
DeleteRecipeMaterial(r);
if (IngredientsToAdd.Count > 0)
foreach (Recipe r in IngredientsToAdd)
{
AddRecipeItem(r);
}
}
catch (Exception ex)
{
//return ex.ToString();
}
finally
{
DataConnection.CloseConnection();
}
}
//functions to read from, update in, and add to the database
//
//========================================================
public static string AddFoodStuff(FoodStuff fs)
{
//insert a foodstuff entry in the Foodstuff table
//will use comma-delineated string for the tags
//now contains the ingredients itself, don't need to handle them separately
try
{
//this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
//database will auto-default to NULL for the rest.
string query = "insert into Foodstuff(FoodstuffID, Name"; //these two must always be there
DataConnection.OpenConnection();
OleDbDataReader rdItemCount = new OleDbCommand("Select Count(Name) from Foodstuff", conn).ExecuteReader();
rdItemCount.Read();
DataConnection.CloseConnection();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
//if statements for each value to add, and its associated parameter
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.ID;// +Convert.ToInt32(rdItemCount[0]).ToString("0000#");
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Name;
int intParameterCount = 2;
if (fs.Directions != "")
{
query += ", Directions";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Directions;
intParameterCount++;
}
if (fs.PrepTime > -1) //use -1, items may still have 0 prep time
{
query += ", PrepTime";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.PrepTime;
intParameterCount++;
}
if (fs.CookTime > -1)
{
query += ", CookTime";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.CookTime;
intParameterCount++;
}
if (fs.Cost > 0.0) //unlikely anything is going to be free
{
query += ", Cost";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Cost;
intParameterCount++;
}
if (fs.Servings > 0)
{
query += ", Servings";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Servings;
intParameterCount++;
}
if (fs.GetTags() != "")
{
query += ", Tags";
//Comma-and-space delineated list of the tags.
//When tokenizing or splitting tags, use ", " for the demarcation.
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.GetTags();
intParameterCount++;
}
query += ") Values(?";
for (int i = 0; i < intParameterCount - 1; i++)
{
query += ",?";
}
query += ")";
cmd.CommandText = query;
DataConnection.OpenConnection();
cmd.ExecuteNonQuery();
foreach (Recipe r in fs.ReturnIngredientsList())
{
AddRecipeItem(r);
}
return("Item plus ingredients added.");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
DataConnection.CloseConnection();
}
}
public static List <FoodStuff> FindFoodstuffsTagged(List <string> TagsToMatch)
{
//find a match for the tags in the passed-in list of tags in the foodstuff's tag string
List <FoodStuff> FoodstuffsFound = new List <FoodStuff>();
try
{
if (TagsToMatch.Count > 0)
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
string query = "select * from foodstuffs where tags like ?";
cmd.Parameters.Add(TagsToMatch[0]);
if (TagsToMatch.Count > 1)
{
for (int i = 1; i < TagsToMatch.Count - 1; i++)
{
query += " or tags like ?";
cmd.Parameters.Add(TagsToMatch[i]);
}
}
cmd.CommandText = query;
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
List <Recipe> lstMats = DataConnection.ListOfIngredients(reader.GetString(0));
FoodStuff fs = new FoodStuff(reader.GetString(0),
reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1.0 : reader.GetDouble(5),
reader.IsDBNull(6) ? -1 : reader.GetInt32(6),
null,
lstMats);
//tokenize the tags from their long string stored in the database.
if (!(reader[7] is System.DBNull))
{
string[] strTagList = reader.GetString(7).Split(',');
if (strTagList.Length > 0)
{
foreach (string t in strTagList)
{
fs.AddTag(t);
}
}
}
FoodstuffsFound.Add(fs);
}
reader.Close();
}
}
catch (Exception ex)
{
// throw;
}
finally
{
DataConnection.CloseConnection();
}
return(FoodstuffsFound);
}
private void btnEditRecipe_Click(object sender, EventArgs e)
{
if (btnEditRecipe.Text == "Edit Recipe")
{
//Switches the form into a editable state
txtName.Enabled = true;
txtPriceSold.Enabled = true;
txtPrepTime.Enabled = true;
txtCookTime.Enabled = true;
txtDirections.Enabled = true;
txtDirections.ReadOnly = false;
txtTags.Enabled = true;
txtServingSize.Enabled = true;
cboIng.Enabled = true;
cboUnit.Enabled = true;
txtQty.Enabled = true;
btnAddIng.Enabled = true;
btnEditIng.Enabled = true;
btnDeleteIng.Enabled = true;
btnAddToTagList.Enabled = true;
btnRemoveSelectedTag.Enabled = true;
btnEditRecipe.Text = "Save Updates";
lsvIngredients.Enabled = true;
lbxTags.Enabled = true;
btnPrint.Visible = true;
}
else
{
//Save Data...Hunter's version of what dustin did on the Entry page
DialogResult button = MessageBox.Show("Are you sure you want to overwrite the previous data?", "Save Edited Recipe", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (button == DialogResult.Yes)
{
//Basically, create brand new foodstuff from all the parameters here, getting the old FSID
//then remove the old foodstuff's recipematerial entries that don't match the new one's
string strID = fstoUpdate.ID;
string strName = fstoUpdate.Name;
if (txtName.Text != strName)
strName = txtName.Text;
int intPrepTime = fstoUpdate.PrepTime;
if (txtPrepTime.Text == "")
txtPrepTime.Text = "-1";
else if (txtPrepTime.Text != intPrepTime.ToString())
if (!int.TryParse(txtPrepTime.Text, out intPrepTime))
{
MessageBox.Show("Error converting new prep time value. Did you enter it correctly?");
return;
}
int intCookTime = fstoUpdate.CookTime;
if (txtCookTime.Text == "")
txtCookTime.Text = "-1";
else if (txtCookTime.Text != intCookTime.ToString())
if (!int.TryParse(txtCookTime.Text, out intCookTime))
{
MessageBox.Show("Error converting new cook time value. Did you enter it correctly?");
return;
}
int intServings = fstoUpdate.Servings;
if (txtServingSize.Text == "")
txtServingSize.Text = "-1";
else if (txtServingSize.Text != intServings.ToString())
if (!int.TryParse(txtServingSize.Text, out intServings))
{
MessageBox.Show("Error converting new serving size value. Did you enter it correctly?");
return;
}
double dblCost = fstoUpdate.Cost;
if (txtPriceSold.Text == "")
txtPriceSold.Text = "-1.0";
else if (txtPriceSold.Text != dblCost.ToString())
if (!Double.TryParse(txtPriceSold.Text, out dblCost))
{
MessageBox.Show("Error converting new price value. Did you enter it correctly?");
return;
}
string strDirections = fstoUpdate.Directions;
if (txtDirections.Text == "")
strDirections = txtDirections.Text;
else if (txtDirections.Text != strDirections)
strDirections = txtDirections.Text;
List<string> newTags = new List<string>();
if (lbxTags.Items.Count > 0)
{
for (int i = 0; i < lbxTags.Items.Count; i++)
{
newTags.Add(lbxTags.Items[i].ToString());
}
}
//
//if item in list but not fs's list, add it, then add as its own foodstuff
//if item matches, check unit and quantity
//if different, update from ingredient list
//if item in fs's list is not in ingredient list, remove it
List<Recipe> newIngrList = new List<Recipe>();
if (lsvIngredients.Items.Count > 0)
{
foreach (ListViewItem lvi in lsvIngredients.Items)
{
//check to see whether the item exists or not by matching name, which is the third column of the listview.
if (DataConnection.FindFoodstuffsNamed(lvi.SubItems[2].Text).Count > 0)
{
//this is supposed to return true if a name match is found
//and add it to the new food's ingredient list
newIngrList.Add(new Recipe(strID, (DataConnection.FindFoodstuffsNamed(lvi.SubItems[2].Text)[0].ID),
lvi.SubItems[0].Text, lvi.SubItems[1].Text));
}
else
{
//Did not find a match...
//making dummy entries
//grab the first up to 4 characters of the name
string newPHID = lvi.SubItems[2].Text.Substring(0, Math.Min(lvi.SubItems[2].Text.Split(' ')[0].Length, 4));
newPHID += (DataConnection.NumFoodstuffs() + 1).ToString("0000#");
//new placeholder ID, name from listview's name column
FoodStuff fsPlaceholder = new FoodStuff(newPHID, lvi.SubItems[2].Text);
//should automatically generate the self-referencing RecipeMaterial stub...
DataConnection.AddFoodStuff(fsPlaceholder);
//then put this in the list for the actual food we're going to add
newIngrList.Add(new Recipe(strID, newPHID, lvi.SubItems[0].Text, lvi.SubItems[1].Text));
}
}
}
//now update it!
DataConnection.UpdateFoodstuff(new FoodStuff(strID, strName, strDirections, intPrepTime, intCookTime, dblCost, intServings, newTags, newIngrList));
}
//List<Recipe> newItemIngredients = new List<Recipe>();
//if (lsvIngredients.Items.Count == 0)
//{
// //Not sure if this will need to change for updating...We only need the new ingredients
// newItemIngredients.Add(new Recipe(newFS.ID, fstoUpdate.ID));
//}
//else
//{
// foreach(ListViewItem lvi in lsvIngredients.Items)
// if(DataConnection.FindFoodstuffsNamed(lvi.SubItems[0].Text).Count > 0 )
// {
// newItemIngredients.Add(new Recipe(fstoUpdate.ID, (DataConnection.FindFoodstuffsNamed(lvi.SubItems[0].Text)[0].ID),
// lvi.SubItems[1].Text, lvi.SubItems[2].Text));
// }
// else
// {
// //Did not find a match...Dustin Dummy entries
// string newPHID = lvi.SubItems[0].Text.Substring(0, Math.Min(lvi.SubItems[0].Text.Split(' ')[0].Length, 4));//grab the first up to 4 characters of the name
// newPHID += (DataConnection.NumFoodstuffs()+1).ToString("0000#");
// FoodStuff fsPlaceholder = new FoodStuff(newPHID, lvi.SubItems[0].Text);
// DataConnection.AddFoodStuff(fsPlaceholder, new Recipe(newPHID, newPHID));
// newItemIngredients.Add(new Recipe(newFS.ID, newPHID, lvi.SubItems[1].Text, lvi.SubItems[2].Text));
// }
//}
//then reset the form back into a readonly state
btnEditRecipe.Text = "Edit Recipe";
txtName.Enabled = false;
txtPriceSold.Enabled = false;
txtPrepTime.Enabled = false;
txtCookTime.Enabled = false;
txtDirections.Enabled = false;
txtTags.Enabled = false;
txtServingSize.Enabled = false;
cboIng.Enabled = false;
cboUnit.Enabled = false;
txtQty.Enabled = false;
btnAddIng.Enabled = false;
btnEditIng.Enabled = false;
btnDeleteIng.Enabled = false;
btnAddToTagList.Enabled = false;
btnRemoveSelectedTag.Enabled = false;
txtDirections.ReadOnly = true;
lblMessage.Text = "Data edit saved";
timer1.Start();
lsvIngredients.Enabled = false;
lbxTags.Enabled = false;
}
}
public static List<FoodStuff> FindFoodstuffsTagged(List<string> TagsToMatch)
{
//find a match for the tags in the passed-in list of tags in the foodstuff's tag string
List<FoodStuff> FoodstuffsFound = new List<FoodStuff>();
try
{
if (TagsToMatch.Count > 0)
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
string query = "select * from foodstuffs where tags like ?";
cmd.Parameters.Add(TagsToMatch[0]);
if (TagsToMatch.Count > 1)
{
for (int i = 1; i < TagsToMatch.Count - 1; i++)
{
query += " or tags like ?";
cmd.Parameters.Add(TagsToMatch[i]);
}
}
cmd.CommandText = query;
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
List<Recipe> lstMats = DataConnection.ListOfIngredients(reader.GetString(0));
FoodStuff fs = new FoodStuff(reader.GetString(0),
reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1.0 : reader.GetDouble(5),
reader.IsDBNull(6) ? -1 : reader.GetInt32(6),
null,
lstMats);
//tokenize the tags from their long string stored in the database.
if (!(reader[7] is System.DBNull))
{
string[] strTagList = reader.GetString(7).Split(',');
if (strTagList.Length > 0)
{
foreach (string t in strTagList)
fs.AddTag(t);
}
}
FoodstuffsFound.Add(fs);
}
reader.Close();
}
}
catch (Exception ex)
{
// throw;
}
finally
{
DataConnection.CloseConnection();
}
return FoodstuffsFound;
}
public static List<FoodStuff> FindFoodstuffsNamed(string inName)
{
//return a list of foodstuffs whose names contain the passed in value
//does not distinguish between base ingredients and final items.
List<FoodStuff> lstFoods = new List<FoodStuff>();
try
{
string query = "Select * from Foodstuff where Name like ?";
OleDbCommand cmd = new OleDbCommand(query, conn);
cmd.Parameters.Add("?", OleDbType.VarChar).Value = inName;
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
List<Recipe> lstMats = DataConnection.ListOfIngredients(reader.GetString(0));
FoodStuff fs = new FoodStuff(reader.GetString(0),
reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1.0 : reader.GetDouble(5),
reader.IsDBNull(6) ? -1 : reader.GetInt32(6),
null,
lstMats);
//tokenize the tags from their long string stored in the database.
string[] strTagList = reader.IsDBNull(7) ? new string[0] : reader.GetString(7).Split(',');
foreach (string t in strTagList)
fs.AddTag(t);
lstFoods.Add(fs);
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
DataConnection.CloseConnection();
}
return lstFoods;
}
private static int intMaintenanceCounter; //load this from the settings file? utility table? registry? (ack!)
#endregion Fields
#region Methods
//functions to read from, update in, and add to the database
//
//========================================================
public static string AddFoodStuff(FoodStuff fs)
{
//insert a foodstuff entry in the Foodstuff table
//will use comma-delineated string for the tags
//now contains the ingredients itself, don't need to handle them separately
try
{
//this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
//database will auto-default to NULL for the rest.
string query = "insert into Foodstuff(FoodstuffID, Name"; //these two must always be there
DataConnection.OpenConnection();
OleDbDataReader rdItemCount = new OleDbCommand("Select Count(Name) from Foodstuff", conn).ExecuteReader();
rdItemCount.Read();
DataConnection.CloseConnection();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
//if statements for each value to add, and its associated parameter
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.ID;// +Convert.ToInt32(rdItemCount[0]).ToString("0000#");
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Name;
int intParameterCount = 2;
if (fs.Directions != "")
{
query += ", Directions";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Directions;
intParameterCount++;
}
if (fs.PrepTime > -1) //use -1, items may still have 0 prep time
{
query += ", PrepTime";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.PrepTime;
intParameterCount++;
}
if (fs.CookTime > -1)
{
query += ", CookTime";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.CookTime;
intParameterCount++;
}
if (fs.Cost > 0.0) //unlikely anything is going to be free
{
query += ", Cost";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Cost;
intParameterCount++;
}
if (fs.Servings > 0)
{
query += ", Servings";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Servings;
intParameterCount++;
}
if (fs.GetTags() != "")
{
query += ", Tags";
//Comma-and-space delineated list of the tags.
//When tokenizing or splitting tags, use ", " for the demarcation.
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.GetTags();
intParameterCount++;
}
query += ") Values(?";
for (int i = 0; i < intParameterCount - 1; i++)
{
query += ",?";
}
query += ")";
cmd.CommandText = query;
DataConnection.OpenConnection();
cmd.ExecuteNonQuery();
foreach (Recipe r in fs.ReturnIngredientsList())
AddRecipeItem(r);
return "Item plus ingredients added.";
}
catch (Exception ex)
{
return ex.ToString();
}
finally
{
DataConnection.CloseConnection();
}
}
public static List<FoodStuff> ListAllFoodstuffs()
{
//displays all foodstuffs that are not atomic items (that is, base ingredients)
//this is determined by the RecipeMaterials entries: items where the Makes and MadeOf fields are identical
//are atomic items. These are the ones we DO NOT want this function to return.
List<FoodStuff> lstFoods = new List<FoodStuff>();
try
{
string query = "Select * from Foodstuff where FoodstuffID in (select Makes from RecipeMaterials where Makes <> MadeOf)"; //Not equals for Access is <> not !=
OleDbCommand cmd = new OleDbCommand(query, conn);
DataConnection.OpenConnection();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
List<Recipe> lstMats = DataConnection.ListOfIngredients(reader.GetString(0));
FoodStuff fs = new FoodStuff(reader.GetString(0),
reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1.0 : reader.GetDouble(5),
reader.IsDBNull(6) ? -1 : reader.GetInt32(6),
null,
lstMats);
//tokenize the tags from their long string stored in the database.
if (!(reader[7] is System.DBNull))
{
string[] strTagList = reader.GetString(7).Split(',');
if (strTagList.Length > 0)
{
foreach (string t in strTagList)
fs.AddTag(t);
}
}
lstFoods.Add(fs);
}
reader.Close();
}
catch (Exception ex)
{
//don't do anything, apparently can't post messageboxes out of this thing
ex.ToString();
}
finally
{
DataConnection.CloseConnection();
}
return lstFoods;
}
private void btnEditRecipe_Click(object sender, EventArgs e)
{
if (btnEditRecipe.Text == "Edit Recipe")
{
//Switches the form into a editable state
txtName.Enabled = true;
txtPriceSold.Enabled = true;
txtPrepTime.Enabled = true;
txtCookTime.Enabled = true;
txtDirections.Enabled = true;
txtDirections.ReadOnly = false;
txtTags.Enabled = true;
txtServingSize.Enabled = true;
cboIng.Enabled = true;
cboUnit.Enabled = true;
txtQty.Enabled = true;
btnAddIng.Enabled = true;
btnEditIng.Enabled = true;
btnDeleteIng.Enabled = true;
btnAddToTagList.Enabled = true;
btnRemoveSelectedTag.Enabled = true;
btnEditRecipe.Text = "Save Updates";
lsvIngredients.Enabled = true;
lbxTags.Enabled = true;
btnPrint.Visible = true;
}
else
{
//Save Data...Hunter's version of what dustin did on the Entry page
DialogResult button = MessageBox.Show("Are you sure you want to overwrite the previous data?", "Save Edited Recipe", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (button == DialogResult.Yes)
{
//Basically, create brand new foodstuff from all the parameters here, getting the old FSID
//then remove the old foodstuff's recipematerial entries that don't match the new one's
string strID = fstoUpdate.ID;
string strName = fstoUpdate.Name;
if (txtName.Text != strName)
{
strName = txtName.Text;
}
int intPrepTime = fstoUpdate.PrepTime;
if (txtPrepTime.Text == "")
{
txtPrepTime.Text = "-1";
}
else if (txtPrepTime.Text != intPrepTime.ToString())
{
if (!int.TryParse(txtPrepTime.Text, out intPrepTime))
{
MessageBox.Show("Error converting new prep time value. Did you enter it correctly?");
return;
}
}
int intCookTime = fstoUpdate.CookTime;
if (txtCookTime.Text == "")
{
txtCookTime.Text = "-1";
}
else if (txtCookTime.Text != intCookTime.ToString())
{
if (!int.TryParse(txtCookTime.Text, out intCookTime))
{
MessageBox.Show("Error converting new cook time value. Did you enter it correctly?");
return;
}
}
int intServings = fstoUpdate.Servings;
if (txtServingSize.Text == "")
{
txtServingSize.Text = "-1";
}
else if (txtServingSize.Text != intServings.ToString())
{
if (!int.TryParse(txtServingSize.Text, out intServings))
{
MessageBox.Show("Error converting new serving size value. Did you enter it correctly?");
return;
}
}
double dblCost = fstoUpdate.Cost;
if (txtPriceSold.Text == "")
{
txtPriceSold.Text = "-1.0";
}
else if (txtPriceSold.Text != dblCost.ToString())
{
if (!Double.TryParse(txtPriceSold.Text, out dblCost))
{
MessageBox.Show("Error converting new price value. Did you enter it correctly?");
return;
}
}
string strDirections = fstoUpdate.Directions;
if (txtDirections.Text == "")
{
strDirections = txtDirections.Text;
}
else if (txtDirections.Text != strDirections)
{
strDirections = txtDirections.Text;
}
List <string> newTags = new List <string>();
if (lbxTags.Items.Count > 0)
{
for (int i = 0; i < lbxTags.Items.Count; i++)
{
newTags.Add(lbxTags.Items[i].ToString());
}
}
//
//if item in list but not fs's list, add it, then add as its own foodstuff
//if item matches, check unit and quantity
//if different, update from ingredient list
//if item in fs's list is not in ingredient list, remove it
List <Recipe> newIngrList = new List <Recipe>();
if (lsvIngredients.Items.Count > 0)
{
foreach (ListViewItem lvi in lsvIngredients.Items)
{
//check to see whether the item exists or not by matching name, which is the third column of the listview.
if (DataConnection.FindFoodstuffsNamed(lvi.SubItems[2].Text).Count > 0)
{
//this is supposed to return true if a name match is found
//and add it to the new food's ingredient list
newIngrList.Add(new Recipe(strID, (DataConnection.FindFoodstuffsNamed(lvi.SubItems[2].Text)[0].ID),
lvi.SubItems[0].Text, lvi.SubItems[1].Text));
}
else
{
//Did not find a match...
//making dummy entries
//grab the first up to 4 characters of the name
string newPHID = lvi.SubItems[2].Text.Substring(0, Math.Min(lvi.SubItems[2].Text.Split(' ')[0].Length, 4));
newPHID += (DataConnection.NumFoodstuffs() + 1).ToString("0000#");
//new placeholder ID, name from listview's name column
FoodStuff fsPlaceholder = new FoodStuff(newPHID, lvi.SubItems[2].Text);
//should automatically generate the self-referencing RecipeMaterial stub...
DataConnection.AddFoodStuff(fsPlaceholder);
//then put this in the list for the actual food we're going to add
newIngrList.Add(new Recipe(strID, newPHID, lvi.SubItems[0].Text, lvi.SubItems[1].Text));
}
}
}
//now update it!
DataConnection.UpdateFoodstuff(new FoodStuff(strID, strName, strDirections, intPrepTime, intCookTime, dblCost, intServings, newTags, newIngrList));
}
//List<Recipe> newItemIngredients = new List<Recipe>();
//if (lsvIngredients.Items.Count == 0)
//{
// //Not sure if this will need to change for updating...We only need the new ingredients
// newItemIngredients.Add(new Recipe(newFS.ID, fstoUpdate.ID));
//}
//else
//{
// foreach(ListViewItem lvi in lsvIngredients.Items)
// if(DataConnection.FindFoodstuffsNamed(lvi.SubItems[0].Text).Count > 0 )
// {
// newItemIngredients.Add(new Recipe(fstoUpdate.ID, (DataConnection.FindFoodstuffsNamed(lvi.SubItems[0].Text)[0].ID),
// lvi.SubItems[1].Text, lvi.SubItems[2].Text));
// }
// else
// {
// //Did not find a match...Dustin Dummy entries
// string newPHID = lvi.SubItems[0].Text.Substring(0, Math.Min(lvi.SubItems[0].Text.Split(' ')[0].Length, 4));//grab the first up to 4 characters of the name
// newPHID += (DataConnection.NumFoodstuffs()+1).ToString("0000#");
// FoodStuff fsPlaceholder = new FoodStuff(newPHID, lvi.SubItems[0].Text);
// DataConnection.AddFoodStuff(fsPlaceholder, new Recipe(newPHID, newPHID));
// newItemIngredients.Add(new Recipe(newFS.ID, newPHID, lvi.SubItems[1].Text, lvi.SubItems[2].Text));
// }
//}
//then reset the form back into a readonly state
btnEditRecipe.Text = "Edit Recipe";
txtName.Enabled = false;
txtPriceSold.Enabled = false;
txtPrepTime.Enabled = false;
txtCookTime.Enabled = false;
txtDirections.Enabled = false;
txtTags.Enabled = false;
txtServingSize.Enabled = false;
cboIng.Enabled = false;
cboUnit.Enabled = false;
txtQty.Enabled = false;
btnAddIng.Enabled = false;
btnEditIng.Enabled = false;
btnDeleteIng.Enabled = false;
btnAddToTagList.Enabled = false;
btnRemoveSelectedTag.Enabled = false;
txtDirections.ReadOnly = true;
lblMessage.Text = "Data edit saved";
timer1.Start();
lsvIngredients.Enabled = false;
lbxTags.Enabled = false;
}
}
//========================================================
//
//public static string AddFoodStuff(FoodStuff fs, List<Recipe> ingredients)
//{
// //insert a foodstuff entry in the Foodstuff table
// //will use comma-delineated string for the tags
// try
// {
// //this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
// //database will auto-default to NULL for the rest.
// string query = "insert into Foodstuff(FoodstuffID, Name"; //these two must always be there
// DataConnection.OpenConnection();
// OleDbDataReader rdItemCount = new OleDbCommand("Select Count(Name) from Foodstuff", conn).ExecuteReader();
// rdItemCount.Read();
// DataConnection.CloseConnection();
// OleDbCommand cmd = new OleDbCommand();
// cmd.Connection = conn;
// //if statements for each value to add, and its associated parameter
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.ID;// +Convert.ToInt32(rdItemCount[0]).ToString("0000#");
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Name;
// int intParameterCount = 2;
// if (fs.Directions != "")
// {
// query += ", Directions";
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Directions;
// intParameterCount++;
// }
// if (fs.PrepTime > -1) //use -1, items may still have 0 prep time
// {
// query += ", PrepTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.PrepTime;
// intParameterCount++;
// }
// if (fs.CookTime > -1)
// {
// query += ", CookTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.CookTime;
// intParameterCount++;
// }
// if (fs.Cost > 0.0) //unlikely anything is going to be free
// {
// query += ", Cost";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Cost;
// intParameterCount++;
// }
// if (fs.Servings > 0)
// {
// query += ", Servings";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Servings;
// intParameterCount++;
// }
// if (fs.GetTags() != "")
// {
// query += ", Tags";
// //Comma-and-space delineated list of the tags.
// //When tokenizing or splitting tags, use ", " for the demarcation.
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.GetTags();
// intParameterCount++;
// }
// query += ") Values(?";
// for (int i = 0; i < intParameterCount-1; i++)
// {
// query += ",?";
// }
// query += ")";
// cmd.CommandText = query;
// DataConnection.OpenConnection();
// cmd.ExecuteNonQuery();
// foreach (Recipe r in ingredients)
// AddRecipeItem(r);
// return "Item plus ingredients added.";
// }
// catch (Exception ex)
// {
// return ex.ToString();
// }
// finally
// {
// DataConnection.CloseConnection();
// }
//}
//public static string AddFoodStuff(FoodStuff fs, Recipe r)
//{
// //same as other but for just adding single Recipe elements instead of from a list.
// //insert a foodstuff entry in the Foodstuff table
// //will use comma-delineated string for the tags
// try
// {
// //this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
// //database will auto-default to NULL for the rest.
// string query = "insert into Foodstuff(FoodstuffID, Name"; //these two must always be there
// OleDbDataReader rdItemCount = new OleDbCommand("Select Count(Name) from Foodstuff", conn).ExecuteReader();
// rdItemCount.Read();
// OleDbCommand cmd = new OleDbCommand();
// cmd.Connection = conn;
// //fs.ID += Convert.ToInt32(rdItemCount[0]).ToString("0000#");
// //if statements for each value to add, and its associated parameter
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.ID;// +Convert.ToInt32(rdItemCount[0]).ToString("0000#");
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Name;
// int intParameterCount = 2;
// if (fs.Directions != "")
// {
// query += ", Directions";
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Directions;
// intParameterCount++;
// }
// if (fs.PrepTime > -1) //use -1, items may still have 0 prep time
// {
// query += ", PrepTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.PrepTime;
// intParameterCount++;
// }
// if (fs.CookTime > -1)
// {
// query += ", CookTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.CookTime;
// intParameterCount++;
// }
// if (fs.Cost > 0.0) //unlikely anything is going to be free
// {
// query += ", Cost";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Cost;
// intParameterCount++;
// }
// if (fs.Servings > 0)
// {
// query += ", Servings";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Servings;
// intParameterCount++;
// }
// if (fs.GetTags() != "")
// {
// query += ", Tags";
// //Comma-and-space delineated list of the tags.
// //When tokenizing or splitting tags, use ", " for the demarcation.
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.GetTags();
// intParameterCount++;
// }
// query += ") Values(?";
// for (int i = 1; i < intParameterCount; i++) //counter is one-based
// {
// query += ",?";
// }
// query += ")";
// cmd.CommandText = query;
// AddRecipeItem(r);
// DataConnection.OpenConnection();
// cmd.ExecuteNonQuery();
// return "Item plus ingredients added.";
// }
// catch (Exception ex)
// {
// return ex.ToString();
// }
// finally
// {
// DataConnection.CloseConnection();
// }
//}
public static void UpdateFoodstuff(FoodStuff updatedFS)
{
//need some way of identifying the column without passing in the name itself which might change.
//an enum won't work since that's just a bit of code associated with an integer.
//TODO:
//verify the record exists, if it does then update the designated column with the new value.
//Potentially ugly hack to make this work is to just update every field regardless of whether they were
//altered or not, overwriting with the same values for unaltered fields.
//it might be slower but would save the headache of trying to pick and choose the fields we need to write to.
try
{
//this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
//database will auto-default to NULL for the rest.
string query = "update foodstuff set "; //these two must always be there
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
//if statements for each value to add, and its associated parameter
query += "Name = ? ";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.Name;
int intParameterCount = 1;
if (updatedFS.Directions != "")
{
query += ", Directions = ?";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.Directions;
intParameterCount++;
}
if (updatedFS.PrepTime > -1) //use -1, items may still have 0 prep time
{
query += ", PrepTime = ?";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.PrepTime;
intParameterCount++;
}
if (updatedFS.CookTime > -1)
{
query += ", CookTime = ? ";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.CookTime;
intParameterCount++;
}
if (updatedFS.Cost > 0.0) //unlikely anything is going to be free
{
query += ", Cost = ? ";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.Cost;
intParameterCount++;
}
if (updatedFS.Servings > 0)
{
query += ", Servings = ?";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.Servings;
intParameterCount++;
}
if (updatedFS.GetTags() != "")
{
query += ", Tags = ? ";
//Comma-and-space delineated list of the tags.
//When tokenizing or splitting tags, use ", " for the demarcation.
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.GetTags();
intParameterCount++;
}
query += "where FoodstuffID = ?";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.ID;
cmd.CommandText = query;
DataConnection.OpenConnection();
cmd.ExecuteNonQuery();
//item in fs's list isn't in ingredient list, so make (another) list of things we need to remove
//and do the exact opposite for those ingredients we need to add new entries for.
List <Recipe> IngredientsToDelete = new List <Recipe>();
List <Recipe> IngredientsToAdd = new List <Recipe>();
foreach (Recipe old_r in GetFoodstuffWithID(updatedFS.ID).ReturnIngredientsList())
{
//needs to match at least one item
bool match = false;
foreach (Recipe new_r in updatedFS.ReturnIngredientsList())
{
if (old_r == new_r)
{
match = true;
}
}
if (!match)
{
IngredientsToDelete.Add(old_r);
}
}
foreach (Recipe new_r in updatedFS.ReturnIngredientsList())
{
bool match = false;
foreach (Recipe old_r in GetFoodstuffWithID(updatedFS.ID).ReturnIngredientsList())
{
if (new_r == old_r)
{
match = true;
}
}
if (!match)
{
IngredientsToAdd.Add(new_r);
}
}
if (IngredientsToDelete.Count > 0)
{
foreach (Recipe r in IngredientsToDelete)
{
DeleteRecipeMaterial(r);
}
}
if (IngredientsToAdd.Count > 0)
{
foreach (Recipe r in IngredientsToAdd)
{
AddRecipeItem(r);
}
}
}
catch (Exception ex)
{
//return ex.ToString();
}
finally
{
DataConnection.CloseConnection();
}
}
