private static MealEntry SelectMealEntry(int MealID, out int affected) //select MealEntry from DB based on MealID given
{
affected = 0;
SqlParameter[] sparams = new SqlParameter[] { new SqlParameter("@MealID", MealID) };
DataRowCollection rows = DBHelper.selectStatement("SELECT m.MealName, m.Portion, n.Score, n.NoteText " +
"FROM Meals m LEFT JOIN Notes n ON m.NoteID=n.NoteID WHERE m.MealID=@MealID", sparams); //select Meal data for this Meal(based on ID) and its associated Note data
if (rows.Count == 0) //If No meal is found Stop/do Nothing
{
return(null);
}
//create MealEntry object and populate some of its data
MealEntry meal = new MealEntry();
meal._name = rows[0]["MealName"].ToString();
meal._portion = (double)rows[0]["Portion"];
meal._note = new Note(rows[0]["Score"] == DBNull.Value ? "" : rows[0]["Score"].ToString(),
rows[0]["NoteText"] == DBNull.Value ? "" : rows[0]["NoteText"].ToString());
affected++;
rows = DBHelper.selectStatement("SELECT f.FoodID, f.IsDish, f.FoodName, f.Brand, f.Fat, f.Carbs, f.Protein, f.SizeInfo, md.Amount " +
"FROM FoodItems f JOIN MealData md ON f.FoodID=md.FoodID WHERE md.MealID=@MealID;", sparams); //select FoodItems associated with this MealID from MealData
foreach (DataRow row in rows) //add the rows information as FoodEntry object to this newly made MealEntry
{
if ((bool)row["IsDish"]) //if the food found is a Dish create a Dish and populate its data
{
Dish foodDish = new Dish(row["FoodName"].ToString(), row["Brand"].ToString(),
(double)row["Fat"], (double)row["Carbs"], (double)row["Protein"],
row["SizeInfo"] == DBNull.Value ? Measure.Grams : Measure.Pieces);
SqlParameter[] sparamsD = new SqlParameter[] { new SqlParameter("@DishID", row["FoodID"]) };
DataRowCollection rowsD = DBHelper.selectStatement("SELECT f.FoodName, f.Brand, f.Fat, f.Carbs, f.Protein, f.SizeInfo, dd.Amount " +
"FROM FoodItems f JOIN DishData dd ON f.FoodID=dd.IngredientID WHERE dd.DishID=@DishID;", sparamsD); //select FoodItems associated with this FoodIF(DishID in DishData) from DishData
foreach (DataRow rowD in rowsD) //add the rows information as FoodEntry object to this newly made Dish
{
foodDish.addFoodEntry(new FoodEntry(new FoodItem(rowD["FoodName"].ToString(), rowD["Brand"].ToString(),
(double)rowD["Fat"], (double)rowD["Carbs"], (double)rowD["Protein"],
rowD["SizeInfo"] == DBNull.Value ? Measure.Grams : Measure.Pieces), (double)rowD["Amount"]));
affected++;
}
meal.addFoodEntry(new FoodEntry(foodDish, (double)row["Amount"]));
}
else
{ //if the food found is not a Dish create a basic FoodItem
FoodItem food = new FoodItem(row["FoodName"].ToString(), row["Brand"].ToString(),
(double)row["Fat"], (double)row["Carbs"], (double)row["Protein"],
row["SizeInfo"] == DBNull.Value ? Measure.Grams : Measure.Pieces);
meal.addFoodEntry(new FoodEntry(food, (double)row["Amount"]));
}
affected++;
}
meal.setCalculatedValues(); //calculate the final food values
return(meal);
}
public DayEntry Clone() //return a New copy of this object with the same values
{
List <MealEntry> ml = new List <MealEntry>();
foreach (var meal in this._mealEntries)
{
List <FoodEntry> fl = new List <FoodEntry>();
foreach (var fentry in meal._foodEntries)
{
fl.Add(new FoodEntry(fentry._food, fentry._amount));
}
MealEntry newm = new MealEntry(fl, new Note(meal._note._grade, meal._note._text), meal._fatEntry, meal._carbsEntry, meal._proteinEntry, meal._portion);
newm._name = meal._name;
ml.Add(newm);
}
DayEntry clone = new DayEntry(this._date, ml, new Note(this._note._grade, this._note._text), this._fatEntry, this._carbsEntry, this._proteinEntry);
clone.setActiv(this._activ);
return(clone);
}
public void addMealEntry(MealEntry mealEntry) //adds a meal entry
{
_mealEntries.Add(mealEntry);
}
private static int InsertMeal(MealEntry meal, DateTime date) //insert one MealEntry into DB
{
int affected = 0;
SqlParameter[] sparams = new SqlParameter[] {
new SqlParameter("@MealName", meal._name),
new SqlParameter("@TimeEaten", date)
};
DataRowCollection rows = DBHelper.selectStatement("SELECT * FROM Meals WHERE MealName=@MealName AND TimeEaten=@TimeEaten;", sparams); //attempts to select This Meal(the one tasked to be inserted) from Meals table
if (rows.Count != 0) //If Meal Already Exists Stop/do Nothing!
{
return(0);
}
int noteID = 0;
if (meal._note.ToString() != "") //if the Meal has a Note
{
sparams = new SqlParameter[] {
new SqlParameter("@UserID", 1),
new SqlParameter("@Title", "N"),
new SqlParameter("@Score", meal._note.gradeAverage()),
new SqlParameter("@NoteText", meal._note._text),
new SqlParameter("@OfDay", DBNull.Value)
};
affected += DBHelper.insertStatement("INSERT INTO Notes VALUES(@UserID, @Title, @Score, @NoteText, @OfDay);", sparams); //inserts Note for this Meal
rows = DBHelper.selectStatement("SELECT IDENT_CURRENT ('Notes');", null); //select last identity(PK) added for table Notes
noteID = Convert.ToInt32(rows[0][0]);
}
sparams = new SqlParameter[] {
new SqlParameter("@MealName", meal._name),
new SqlParameter("@TimeEaten", date.ToString("yyyy/MM/dd")),
new SqlParameter("@Portion", meal._portion),
new SqlParameter("@UserID", 1),
new SqlParameter("@NoteID", (noteID == 0 ? (object)DBNull.Value : noteID))
};
affected += DBHelper.insertStatement("INSERT INTO Meals VALUES (@MealName, @TimeEaten, @Portion, @UserID, @NoteID);", sparams); //inserts entry for table Meals
if (meal._foodEntries.Count != 0)
{
rows = DBHelper.selectStatement("SELECT IDENT_CURRENT ('Meals');", null); //select last identity(PK) added for table Meals
int mealID = Convert.ToInt32(rows[0][0]);
int foodID;
String comText = "INSERT INTO MealData VALUES"; //multirow Insert into MealData for Food Entries of this Meal
List <SqlParameter> sparamsMultiline = new List <SqlParameter>(); //multirow Parameter list for Food Entries of this Meal
sparamsMultiline.Add(new SqlParameter("@MealID", mealID));
for (int i = 0; i < meal._foodEntries.Count; i++)
{
sparams = new SqlParameter[] {
new SqlParameter("@FoodName", meal._foodEntries[i]._food._name),
new SqlParameter("@Brand", meal._foodEntries[i]._food._brand),
};
rows = DBHelper.selectStatement("SELECT FoodID FROM FoodItems WHERE FoodName=@FoodName AND Brand=@Brand;", sparams); //select from FoodItems FoodID for currect FoodEntry that need to be inserted
foodID = Convert.ToInt32(rows[0][0]);
comText += $" (@MealID, @FoodID{i}, @Amount{i}, @Measure{i}),"; //addidng current Food Entry values/parameters to insert statement
sparamsMultiline.AddRange(new SqlParameter[] {
new SqlParameter($"@FoodID{i}", foodID),
new SqlParameter($"@Amount{i}", meal._foodEntries[i]._amount),
new SqlParameter($"@Measure{i}", meal._foodEntries[i]._food._measure.ToString())
});
}
comText = comText.Remove(comText.Length - 1) + ";"; //replacing last comma with a ; for ending statement
affected += DBHelper.insertStatement(comText, sparamsMultiline.ToArray()); //executing the insert into MealData for all Food Entries of this Meal
}
return(affected);
}
private void button_addFood_Click(object sender, EventArgs e) //adds a new food(or references from Foods through the process of FoodEntry init) to selected meal
{
MealEntry meal = comboBox_meals.SelectedItem as MealEntry;
if (meal == null)
{
MessageBox.Show("No Meal Selected!");
return;
}
if (textBox_amount.Text == "") //checks amount
{
MessageBox.Show("Needs Amount!");
return;
}
FoodItem food = null;
if (radio_list.Checked) //adds food from the list of all foods
{
food = (FoodItem)listBox_foodItems.SelectedItem;
if (food != null)
{
meal.addFoodEntry(new FoodEntry(food, Convert.ToDouble(textBox_amount.Text)));
}
else
{
MessageBox.Show("Invalid Selection for Food!");
}
textBox_amount.Clear();
textBox_searchFood.Clear();
textBox_searchFood.Focus();
}
else if (radio_pattern.Checked) //adds food by matching a string(similar to one written file format)
{
if (MainForm.regexEntry.Match(textBox_pattern.Text).Success)
{
meal.addFoodEntry(new FoodEntry(textBox_pattern.Text));
food = _day._mealEntries[comboBox_meals.SelectedIndex]._foodEntries.Last()._food;
}
else
{
MessageBox.Show("Invalid Pattern for Food!");
}
textBox_pattern.Clear();
textBox_pattern.Focus();
}
else
{ //adds new food by creating it with values given by user through custom textboxes
try
{
food = new FoodItem(textBox_foodName.Text, textBox_brand.Text,
Convert.ToDouble(textBox_fat.Text), Convert.ToDouble(textBox_carbs.Text), Convert.ToDouble(textBox_protein.Text),
(Measure)comboBox_measure.SelectedIndex);
meal.addFoodEntry(new FoodEntry(food, Convert.ToDouble(textBox_amount.Text)));
}
catch (Exception ex)
{
MessageBox.Show($"Invalid Entry Values! {ex.Message}");
}
textBox_foodName.Clear();
textBox_brand.Clear();
textBox_fat.Clear();
textBox_carbs.Clear();
textBox_protein.Clear();
textBox_foodName.Focus();
}
if (food != null) //updates MealEntry and FoodEntry calculated values(does the math for macros)
{
((MealEntry)comboBox_meals.SelectedItem).setCalculatedValues();
_day.setCalculatedValues();
}
updateDay();
}