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 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(); }
//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 void Register(string UserName, string Name, string Email, string PassWord) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string Alreadypassword = PasswordHash.CreateHash(PassWord); string command = "INSERT INTO UserTable (Name,UserName,Email,Pass) VALUES ('" + Name + "','" + UserName + "','" + Email + "','" + Alreadypassword + "');"; myDatabase.ExcuteNonQuery(command); }
public void EditProfile(string id,string Name, string UserName, string Email, string PassWord) { Database myDatabase = new Database(); myDatabase.ReturnConnection(); string command; if (PassWord == "") { command = "UPDATE UserTable SET Name='" + Name + "',UserName='******',Email='" + Email + "' WHERE ID =" + id + ";"; } else { string Alreadypassword = PasswordHash.CreateHash(PassWord); command = "UPDATE UserTable SET Name='" + Name + "',UserName='******',Email='" + Email + "',Pass='******' WHERE ID =" + id + ";"; } myDatabase.ExcuteNonQuery(command); }
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 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 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 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 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 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 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(); }