public static void AddIngredientToStorage(int StorageOwnerID, string Name, double amount, int UnitID, string exdate) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); //If ingredient available, take ingredient ID if (Model.RecipeManagement.CheckIngredientAvailability(Name)) { string command = "SELECT ID FROM Ingredient Where Name ='" + Name + "';"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); reader.Read(); int IngredientID = Convert.ToInt32(reader["ID"].ToString()); command = "INSERT INTO StorageIngredientAmount (Amount, IngredientID, OwnerID, UnitID, ExpiredDate) VALUES ('" + amount + "','" + IngredientID + "','" + StorageOwnerID + "','" + UnitID + "','" + exdate + "');"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); } // If not available, create a new Ingredient else { //Create a new Ingredient here Model.RecipeManagement.CreateIngredient(Name, ""); myDatabase.ReturnConnection(); string command = "SELECT ID FROM Ingredient Where Name ='" + Name + "';"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); reader.Read(); int IngredientID = Convert.ToInt32(reader["ID"].ToString()); command = "INSERT INTO StorageIngredientAmount (Amount, IngredientID, OwnerID, UnitID, ExpiredDate) VALUES ('" + amount + "','" + IngredientID + "','" + StorageOwnerID + "','" + UnitID + "','" + exdate + "');"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); } }
public static List<HistoryRecipe> GetRecList(int uid) { List<HistoryRecipe> historyList = new List<HistoryRecipe>(); Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM CookingRecipeHistory Where UserID=" + uid + ""; var reader = myDatabase.ExcuteQuery(command); bool notEOF = false; notEOF = reader.Read(); while (notEOF) { HistoryRecipe recipeObj = new HistoryRecipe(); recipeObj.RecipeID = Convert.ToInt16(reader["RecipeID"]); recipeObj.CookingDate = Convert.ToDateTime(reader["CookingDate"]); historyList.Add(recipeObj); notEOF = reader.Read(); } if (historyList.Count > 0) { foreach (HistoryRecipe recipe in historyList) { string command2 = "SELECT Name FROM Recipe WHERE ID=" + recipe.RecipeID + ";"; var reader2 = myDatabase.ExcuteQuery(command2); bool EOF2; EOF2 = reader2.Read(); recipe.Name = reader2["Name"].ToString(); } } myDatabase.CloseConnection(); return historyList; }
public static int GetRecipeVote(int RecipeID) { int averageVote = 0; int count = 0; double sum = 0; Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM Vote Where RecipeID=" + RecipeID + ""; var reader = myDatabase.ExcuteQuery(command); bool notEOF = false; notEOF = reader.Read(); while (notEOF) { count++; sum = sum + Convert.ToInt32(reader["Vote"]); notEOF = reader.Read(); } myDatabase.CloseConnection(); averageVote = (int)Math.Round((sum / count),0, MidpointRounding.AwayFromZero); if (averageVote >= 0) { return averageVote; } else return 0; }
//Return a list of planRecipe Object belong to this specific UserID public static List<PlanRecipe> GetPlanRecipe(int UserID) { List<PlanRecipe> planRecipeList = new List<PlanRecipe>(); Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM Plan Where UserID=" + UserID + ""; var reader = myDatabase.ExcuteQuery(command); bool notEOF = false; notEOF = reader.Read(); while (notEOF) { planRecipeList.Add(new PlanRecipe(Convert.ToInt32(reader["RecipeID"]), "", Convert.ToDateTime(reader["CookingDate"]), Convert.ToInt32(reader["ID"]))); notEOF = reader.Read(); } foreach (PlanRecipe wlr in planRecipeList) { command = "SELECT Name FROM Recipe Where ID=" + wlr.RecipeId + ""; reader = myDatabase.ExcuteQuery(command); notEOF = false; notEOF = reader.Read(); while (notEOF) { wlr.RecipeName = reader["Name"].ToString(); notEOF = reader.Read(); } } myDatabase.CloseConnection(); return planRecipeList; }
public static double IngredientAmountSum(int IngredientID, int OwnerID) { double sum = 0; Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM StorageIngredientAmount WHERE (IngredientID =" + IngredientID + ") AND (OwnerID =" + OwnerID + ");"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); bool EOF = reader.Read(); List<double> amountList = new List<double>(); List<double> unitIDList = new List<double>(); while (EOF) { amountList.Add(Convert.ToDouble(reader["Amount"])); unitIDList.Add(Convert.ToInt32(reader["UnitID"])); EOF = reader.Read(); } for (int i = 0; i < amountList.Count; i++) { command = "SELECT * FROM Unit WHERE ID =" + unitIDList[i] + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); reader.Read(); sum += amountList[i] * Convert.ToDouble(reader["RateToKilogram"]); } myDatabase.CloseConnection(); return sum; }
public static void AddNewHR(int uid,int rid) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "INSERT INTO CookingRecipeHistory (UserID,RecipeID,CookingDate) VALUES ('" + uid + "','" + rid + "','" + DateTime.Now.Date.ToString() + "');"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); }
//Remove from the planID with ID of a row as an parameter public static void RemoveFromPlan(int PlanID) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "DELETE FROM Plan WHERE ID=" + PlanID + ";"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); }
//Add new row to Plan table public static void AddPlan(int RecipeID, int UserID, DateTime CookingDate) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "INSERT INTO Plan (RecipeID,UserID,CookingDate) VALUES ('" + RecipeID + "','" + UserID + "','" + CookingDate + "');"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); }
public static bool CheckIngredientInStorage(int IngredientID, int OwnerID) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM StorageIngredientAmount WHERE (IngredientID =" + IngredientID + ") AND (OwnerID =" + OwnerID + ");"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); if (reader.Read()) { myDatabase.CloseConnection(); return true; } else { myDatabase.CloseConnection(); return false; } }
//Create new row to vote table, public static void CreateVote(int UserID, int RecipeID, int Vote) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string id = ""; string command = "SELECT * FROM Vote Where UserID=" + UserID + " AND RecipeID=" + RecipeID; var reader = myDatabase.ExcuteQuery(command); reader.Read(); if (reader.HasRows == true) { id = reader["ID"].ToString(); command = "UPDATE Vote SET Vote='" + Vote + "' WHERE ID =" + id + ";"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); } else { command = "INSERT INTO Vote (RecipeID,UserID,Vote) VALUES ('" + RecipeID + "','" + UserID + "','" + Vote + "');"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); } }
public static List<Unit> ListAllUnit() { Database myDatbase = new Database(); myDatbase.ReturnConnection(); string command = "SELECT * FROM Unit"; var reader = myDatbase.ExcuteQuery(command); List<Unit> returnUnitList = new List<Unit>(); bool EOF = reader.Read(); while (EOF) { Unit unitObj = new Unit(); unitObj.Name = reader["Name"].ToString(); unitObj.ID = int.Parse(reader["ID"].ToString()); returnUnitList.Add(unitObj); EOF = reader.Read(); } myDatbase.CloseConnection(); return returnUnitList; }
public static double CalculatePrice(int IngredientID, double amount, int UnitID) { double result = 0; Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM Ingredient WHERE ID =" + IngredientID + ";"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); reader.Read(); double PricePerKilo = Convert.ToDouble(reader["PricePerKilo"]); command = "SELECT * FROM Unit WHERE ID =" + UnitID + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); reader.Read(); result = amount * Convert.ToDouble(reader["RateToKilogram"]); result = result * PricePerKilo; myDatabase.CloseConnection(); return result; }
public static void sendRemindEmail(int userID) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM UserTable Where ID=" + userID + ";"; var reader = myDatabase.ExcuteQuery(command); reader.Read(); User userObj = new User(); userObj.Name = reader["Name"].ToString(); userObj.Email = reader["Email"].ToString(); List<Ingredient> expiredIngredientList = Model.StorageManagement.GetExpiredIngredient(userID); string message = "Hi, " + userObj.Name + " you have some food will expired today. Take a look" + System.Environment.NewLine + System.Environment.NewLine; foreach (Ingredient ing in expiredIngredientList) { message += ing.Amount + " " + ing.Unit + " " + ing.Name + " Expired day " + ing.ExpiredDay + System.Environment.NewLine; } Email.SendEmail("*****@*****.**", "FoodnStuff@support", userObj.Email, userObj.Name, "Expired food reminder", message); myDatabase.CloseConnection(); }
public static List<Ingredient> GetAllStorageIngredient(int OwnerID) { List<Ingredient> IngredientList = new List<Ingredient>(); string command = "SELECT * FROM StorageIngredientAmount WHERE OwnerID =" + OwnerID + ";"; Database myDatabase = new Database(); myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); bool EOF1 = reader.Read(); List<int> idList = new List<int>(); while (EOF1) { idList.Add(Convert.ToInt32(reader["ID"])); EOF1 = reader.Read(); } for (int i = 0; i < idList.Count; i++) { command = "SELECT * FROM StorageIngredientAmount WHERE ID =" + idList[i] + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); reader.Read(); Ingredient ingredientObj = new Ingredient(); ingredientObj.Amount = Convert.ToDouble(reader["Amount"]); ingredientObj.ExpiredDay = reader["ExpiredDate"].ToString(); int IngredientID = Convert.ToInt32(reader["IngredientID"]); int UnitID = Convert.ToInt32(reader["UnitID"]); myDatabase.ReturnConnection(); command = "SELECT * FROM Unit WHERE ID =" + UnitID + ";"; myDatabase.ExcuteQuery(command); OleDbDataReader reader1 = myDatabase.ExcuteQuery(command); reader1.Read(); string Unit = reader1["Name"].ToString(); ingredientObj.Unit = Unit; IngredientList.Add(ingredientObj); command = "SELECT * FROM Ingredient WHERE ID =" + IngredientID + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); reader.Read(); IngredientList[i].Name = reader["Name"].ToString(); } myDatabase.CloseConnection(); return IngredientList; }
public static List<Category> GetAllCategory() { List<Category> CategoryList = new List<Category>(); Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM Category;"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); bool EOF = reader.Read(); List<int> idList = new List<int>(); while (EOF) { idList.Add(Convert.ToInt32(reader["ID"])); EOF = reader.Read(); } CategoryList = GetCategory(idList); myDatabase.CloseConnection(); return CategoryList; }
public static int IngredientCompare(int IngredientID, int RecipeID, int OwnerID) { if (CheckIngredientInStorage(IngredientID, OwnerID)) { double StorageSum = IngredientAmountSum(IngredientID, OwnerID); Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM RecipeIngredientAmount WHERE (IngredientID =" + IngredientID + ") AND (RecipeID =" + RecipeID + ");"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); reader.Read(); double RecipeAmount = Convert.ToDouble(reader["Amount"]); command = "SELECT * FROM Unit WHERE ID =" + Convert.ToInt32(reader["UnitID"]) + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); reader.Read(); RecipeAmount = RecipeAmount * Convert.ToDouble(reader["RateToKilogram"]); myDatabase.CloseConnection(); //Value = 1 => Equal amount of ingredient in storage if (StorageSum == RecipeAmount) { return 1; } else { //Value = 2 => Storage has more amount than recipe needed if (StorageSum > RecipeAmount) { return 2; } else //Value = 3 => Storage has less amount than recipe needed { return 3; } } } //Value = 0 => No Ingredient in storage else { return 0; } }
public static double RecipeTotalPrice(int RecipeID) { double total = 0; double amount = 0; Recipe myRecipe = new Recipe(); myRecipe = RecipeManagement.getRecipe(RecipeID)[0]; for (int i = 0; i < myRecipe.IngredientList.Count; i++) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); //Get ID for PricePerKilo string command = "SELECT * FROM Ingredient WHERE Name ='" + myRecipe.IngredientList[i].Name + "';"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); reader.Read(); double PricePerKilo = Convert.ToDouble(reader["PricePerKilo"]); //Get Unit for RatetoKilo command = "SELECT * FROM Unit WHERE Name ='" + myRecipe.IngredientList[i].Unit + "';"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); reader.Read(); amount = myRecipe.IngredientList[i].Amount * Convert.ToDouble(reader["RateToKilogram"]); total += amount * PricePerKilo; myDatabase.CloseConnection(); } return total; }
public static void AddRecipePicture(string path) { int maxRecipeID = 0; Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT ID FROM Recipe"; myDatabase.ExcuteQuery(command); OleDbDataReader reader1 = myDatabase.ExcuteQuery(command); bool EOF1 = reader1.Read(); List<int> idList1 = new List<int>(); while (EOF1) { idList1.Add(Convert.ToInt32(reader1["ID"])); EOF1 = reader1.Read(); } if (idList1.Count() > 0) { maxRecipeID = idList1.Max(); } command = "INSERT INTO RecipeImage (Path, RecipeID) VALUES ('" + path + "','" + maxRecipeID + "');"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); }
public static List<Ingredient> GetAvailableIngredient(List<int> IngredientIDList, int OwnerID) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); List<Ingredient> IngredientList = new List<Ingredient>(); List<int> ResultList = new List<int>(); //Get Ingredient for (int i = 0; i < IngredientIDList.Count; i++) { if (Calculation.CheckIngredientInStorage(IngredientIDList[i], OwnerID)) { ResultList.Add(IngredientIDList[i]); } } for (int i = 0; i < ResultList.Count; i++) { string IngredientName; string command = "SELECT * FROM Ingredient WHERE ID =" + ResultList[i] + ";"; myDatabase.ExcuteQuery(command); OleDbDataReader reader1 = myDatabase.ExcuteQuery(command); reader1.Read(); IngredientName = reader1["Name"].ToString(); command = "SELECT * FROM StorageIngredientAmount WHERE (IngredientID =" + ResultList[i] + ") AND (OwnerID =" + OwnerID + ");"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); bool EOF = reader.Read(); while (EOF) { Ingredient ingredientObj = new Ingredient(); ingredientObj.Amount = Convert.ToDouble(reader["Amount"]); int UnitID = Convert.ToInt32(reader["UnitID"]); myDatabase.ReturnConnection(); command = "SELECT * FROM Unit WHERE ID =" + UnitID + ";"; myDatabase.ExcuteQuery(command); OleDbDataReader reader2 = myDatabase.ExcuteQuery(command); reader2.Read(); string Unit = reader2["Name"].ToString(); ingredientObj.Unit = Unit; ingredientObj.ExpiredDay = (reader["ExpiredDate"].ToString()).Substring(0, 10); ingredientObj.Name = IngredientName; IngredientList.Add(ingredientObj); EOF = reader.Read(); } } myDatabase.CloseConnection(); return IngredientList; }
public static void UpdatePrice(int IngredientID,double Price) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "UPDATE Ingredient SET PricePerKilo = " + Price.ToString() + " WHERE ID = " + IngredientID.ToString() + ";"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); }
public static bool CheckIngredientAvailability(string Name) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM Ingredient Where Name ='" + Name + "';"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); if (reader.Read()) { myDatabase.CloseConnection(); return true; } else { myDatabase.CloseConnection(); return false; } }
public static List<int> GetRecipeIDBelongToCategory(int categoryID) { List<int> idList = new List<int>(); Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM RecipeCategory WHERE CategoryID = "+ categoryID + ";"; OleDbDataReader reader = myDatabase.ExcuteQuery(command); bool EOF = reader.Read(); while (EOF) { idList.Add(Convert.ToInt32(reader["RecipeID"])); EOF = reader.Read(); } myDatabase.CloseConnection(); return idList; }
public static List<Recipe> getRecipe(int? recipeID=null ,string searchParam = null) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = ""; //Create List of Recipe to be returned List<Recipe> recipeList = new List<Recipe>(); //Get by ID if (recipeID != null && recipeID >0) { command = "SELECT * FROM Recipe WHERE ID =" + recipeID.ToString() + ";"; } else if (searchParam != null) { command = "SELECT * FROM Recipe WHERE NAME LIKE '%" + searchParam + "%'"; } //If enter nothing return all the recipe else if (searchParam == null) { command = "SELECT * FROM Recipe"; } OleDbDataReader mainReader = myDatabase.ExcuteQuery(command); bool EOF = mainReader.Read(); //Loop over list of Response while (EOF) { Recipe myRecipe = new Recipe(); //Get Name, Instruction and AuthorID myRecipe.Name = mainReader["Name"].ToString(); myRecipe.Instruction = mainReader["Instruction"].ToString(); myRecipe.AuthorID = Convert.ToInt32(mainReader["CreatedID"]); myRecipe.ID = Convert.ToInt32(mainReader["ID"]); myRecipe.Duration = Convert.ToInt32(mainReader["Duration"]); //Get AuthorName command = "SELECT * FROM UserTable WHERE ID =" + myRecipe.AuthorID.ToString() + ";"; myDatabase.ExcuteQuery(command); var reader = myDatabase.ExcuteQuery(command); reader.Read(); myRecipe.AuthorName = reader["Name"].ToString(); //Get Ingredient List<Ingredient> IngredientList = new List<Ingredient>(); command = "SELECT * FROM RecipeIngredientAmount WHERE RecipeID =" + myRecipe.ID.ToString() + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); bool EOF1 = reader.Read(); List<int> idList = new List<int>(); while (EOF1) { idList.Add(Convert.ToInt32(reader["IngredientID"])); EOF1 = reader.Read(); } for (int i = 0; i < idList.Count; i++) { command = "SELECT * FROM RecipeIngredientAmount WHERE IngredientID =" + idList[i] + " AND RecipeID =" + myRecipe.ID.ToString() + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); reader.Read(); Ingredient ingredientObj = new Ingredient(); ingredientObj.Amount = Convert.ToDouble(reader["Amount"]); int UnitID = Convert.ToInt32(reader["UnitID"]); myDatabase.ReturnConnection(); command = "SELECT * FROM Unit WHERE ID =" + UnitID + ";"; myDatabase.ExcuteQuery(command); OleDbDataReader reader1 = myDatabase.ExcuteQuery(command); reader1.Read(); string Unit = reader1["Name"].ToString(); ingredientObj.Unit = Unit; IngredientList.Add(ingredientObj); command = "SELECT * FROM Ingredient WHERE ID =" + idList[i] + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); reader.Read(); IngredientList[i].Name = reader["Name"].ToString(); } myRecipe.IngredientList = IngredientList; //Get picture path command = "SELECT * FROM RecipeImage WHERE RecipeID =" + myRecipe.ID.ToString() + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); reader.Read(); myRecipe.PicturePath = reader["Path"].ToString(); //Get category list command = "SELECT * FROM RecipeCategory WHERE RecipeID =" + myRecipe.ID.ToString() + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); bool EOF2 = reader.Read(); List<int> idList1 = new List<int>(); while (EOF2) { idList1.Add(Convert.ToInt32(reader["CategoryID"])); EOF2 = reader.Read(); } myRecipe.CategoryList = GetCategory(idList1); //Get recipe vote int Vote = VoteManagement.GetRecipeVote(Convert.ToInt32(myRecipe.ID)); myRecipe.Vote = Vote; recipeList.Add(myRecipe); EOF = mainReader.Read(); } myDatabase.CloseConnection(); return recipeList; }
public static int CreateRecipe(string Name, string Instruction, int creatorID, int Duration, List<int> CategoryIDList) { int maxRecipeID = 0; Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT ID FROM Recipe"; myDatabase.ExcuteQuery(command); OleDbDataReader reader1 = myDatabase.ExcuteQuery(command); bool EOF1 = reader1.Read(); List<int> recipeIDList = new List<int>(); while (EOF1) { recipeIDList.Add(Convert.ToInt32(reader1["ID"])); EOF1 = reader1.Read(); } if (recipeIDList.Count() > 0) { maxRecipeID = recipeIDList.Max(); } command = "INSERT INTO Recipe (ID, Name, Instruction, CreatedID, Duration) VALUES ('"+ (maxRecipeID + 1) + "','" + Name + "','" + Instruction + "','" + creatorID + "','" + Duration + "');"; myDatabase.ExcuteNonQuery(command); //Add Category Recipe for (int i = 0; i < CategoryIDList.Count; i++) { command = "INSERT INTO RecipeCategory (RecipeID, CategoryID) VALUES ('" + (maxRecipeID + 1) + "','" + CategoryIDList[i] + "');"; myDatabase.ExcuteNonQuery(command); } myDatabase.CloseConnection(); maxRecipeID++; return maxRecipeID; }
public static void CreateRecipe(string Name, string Instruction, int creatorID) { int maxrecipeID = 0; Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT ID FROM Recipe"; myDatabase.ExcuteQuery(command); OleDbDataReader reader1 = myDatabase.ExcuteQuery(command); bool EOF1 = reader1.Read(); List<int> idList1 = new List<int>(); if (EOF1) { idList1.Add(Convert.ToInt32(reader1["ID"])); reader1.Read(); } if (idList1.Count() > 0) { maxrecipeID = idList1.Max(); } command = "INSERT INTO Recipe (ID, Name, Instruction, CreatedID) VALUES ('"+ (maxrecipeID+1) + "','" + Name + "','" + Instruction + "','" + creatorID +"');"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); }
public static void IngredientCase2Calculation(int IngredientID, double amount, int OwnerID) { while (amount > 0) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT * FROM StorageIngredientAmount WHERE ExpiredDate = (SELECT MIN(ExpiredDate) FROM StorageIngredientAmount WHERE (IngredientID =" + IngredientID + ") AND (OwnerID =" + OwnerID + "));"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); reader.Read(); double AvailableAmount = Convert.ToDouble(reader["Amount"]); command = "SELECT * FROM Unit WHERE ID =" + Convert.ToInt32(reader["UnitID"]) + ";"; myDatabase.ExcuteQuery(command); reader = myDatabase.ExcuteQuery(command); reader.Read(); double rate = Convert.ToDouble(reader["RateToKilogram"]); AvailableAmount = AvailableAmount * rate; if (AvailableAmount <= amount) { myDatabase.ReturnConnection(); command = "DELETE * FROM StorageIngredientAmount WHERE ExpiredDate = (SELECT MIN(ExpiredDate) FROM StorageIngredientAmount WHERE (IngredientID =" + IngredientID + ") AND (OwnerID =" + OwnerID + "));"; myDatabase.ExcuteNonQuery(command); amount -= AvailableAmount; myDatabase.CloseConnection(); } else { myDatabase.ReturnConnection(); AvailableAmount -= amount; AvailableAmount = AvailableAmount / rate; command = "UPDATE StorageIngredientAmount SET Amount =" + AvailableAmount + " WHERE ExpiredDate = (SELECT MIN(ExpiredDate) FROM StorageIngredientAmount WHERE (IngredientID =" + IngredientID + ") AND (OwnerID =" + OwnerID + "));"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); amount = 0; } } }
public static void CreateIngredient(string Name, string Description) { int maxingredientID = 0; Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT ID FROM Ingredient"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); bool EOF = reader.Read(); List<int> idList = new List<int>(); if (EOF) { idList.Add(Convert.ToInt32(reader["ID"])); reader.Read(); } if (idList.Count>0) { maxingredientID = idList.Max(); } command = "INSERT INTO Ingredient (ID, Name, Description) VALUES ('" + (maxingredientID + 1) + "','" + Name + "','" + Description + "');"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); }
public static List<string> GetAvailableIngredientByName(string hint) { List<string> returnString = new List<string>(); Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT Name FROM Ingredient WHERE NAME LIKE '%" + hint + "%'"; var reader = myDatabase.ExcuteQuery(command); bool EOF = reader.Read(); while (EOF) { returnString.Add(reader["Name"].ToString()); EOF = reader.Read(); } myDatabase.CloseConnection(); return returnString; }
public static void AddIngredientToRecipe(string Name, double amount, int UnitID) { int maxrecipeID = 0; Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = "SELECT ID FROM Recipe"; myDatabase.ExcuteQuery(command); OleDbDataReader reader1 = myDatabase.ExcuteQuery(command); bool EOF1 = reader1.Read(); List<int> idList1 = new List<int>(); if (EOF1) { idList1.Add(Convert.ToInt32(reader1["ID"])); reader1.Read(); } if (idList1.Count()>0) { maxrecipeID = idList1.Max(); } //If ingredient available, take ingredient ID if (CheckIngredientAvailability(Name)) { command = "SELECT ID FROM Ingredient Where Name ='" + Name + "';"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); reader.Read(); int IngredientID = Convert.ToInt32(reader["ID"].ToString()); command = "INSERT INTO RecipeIngredientAmount (Amount, IngredientID, RecipeID, UnitID) VALUES ('" + amount + "','" + IngredientID + "','" + (maxrecipeID+1) + "','" + UnitID + "');"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); } // If not available, create a new Ingredient else { //Create a new Ingredient here CreateIngredient(Name, ""); myDatabase.ReturnConnection(); command = "SELECT ID FROM Ingredient Where Name ='" + Name + "';"; myDatabase.ExcuteQuery(command); OleDbDataReader reader = myDatabase.ExcuteQuery(command); reader.Read(); int IngredientID = Convert.ToInt32(reader["ID"].ToString()); command = "INSERT INTO RecipeIngredientAmount (Amount, IngredientID, RecipeID, UnitID) VALUES ('" + amount + "','" + IngredientID + "','" + maxrecipeID + "','" + UnitID + "');"; myDatabase.ExcuteNonQuery(command); myDatabase.CloseConnection(); } }
public static List<Category> GetCategory(List<int> IDList) { List<Category> CategoryList = new List<Category>(); Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command = ""; for (int i = 0; i < IDList.Count; i++) { Category myCategory = new Category(); command = "SELECT * FROM Category WHERE ID =" + IDList[i] + ";"; OleDbDataReader reader = myDatabase.ExcuteQuery(command); reader.Read(); myCategory.ID = Convert.ToInt32(reader["ID"]); myCategory.Name = reader["Name"].ToString(); myCategory.ImagePath = reader["ImagePath"].ToString(); CategoryList.Add(myCategory); } myDatabase.CloseConnection(); return CategoryList; }