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();
}