//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 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;
}
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;
}
}
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 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 void sendShoppingEmail(int userID, string reminder)
{
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();
string message = "Hi, " + userObj.Name + " this is a reminder for you when go shop for food" + System.Environment.NewLine + System.Environment.NewLine + reminder;
Email.SendEmail("*****@*****.**", "FoodnStuff@support", userObj.Email, userObj.Name, "Shopping food reminder", message);
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;
}
//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 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 bool CheckForUname(string uname)
{
bool contain = false;
Database myDatabase = new Database();
myDatabase.ReturnConnection();
string command = "SELECT * FROM UserTable Where UserName='******'";
var reader = myDatabase.ExcuteQuery(command);
reader.Read();
if (reader.HasRows == true)
{
contain = true;
}
return contain;
}
public static Ingredient GetIngredient(int ID)
{
Ingredient A = new Ingredient { };
Database myDatabase = new Database();
myDatabase.ReturnConnection();
string command = "SELECT * FROM Ingredient WHERE ID ='" + ID + "';";
myDatabase.ExcuteQuery(command);
OleDbDataReader reader = myDatabase.ExcuteQuery(command);
A.SetName(reader["Name"].ToString());
command = "SELECT * FROM RecipeIngredientAmount WHERE IngredientID ='" + ID + "';";
reader = myDatabase.ExcuteQuery(command);
A.SetAmount(Convert.ToDouble(reader["Amount"].ToString()));
return A;
}
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 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 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 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 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())
{
return true;
}
else return false;
}
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();
}
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 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 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 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 string getData(string field, string id)
{
string data = "";
Database myDatabase = new Database();
myDatabase.ReturnConnection();
string command = "SELECT * FROM UserTable Where ID=" + id + "";
var reader = myDatabase.ExcuteQuery(command);
reader.Read();
if (reader.HasRows == true)
{
data = reader[field].ToString();
}
return data;
}
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 string Login(string inputUserName, string inputPassWord)
{
Database myDatabase = new Database();
string id = "0";
myDatabase.ReturnConnection();
string command = "SELECT * FROM UserTable Where UserName='******'";
var reader = myDatabase.ExcuteQuery(command);
reader.Read();
if (reader.HasRows == true)
{
string name = reader["Name"].ToString();
string hashedPass = reader["Pass"].ToString();
if (PasswordHash.ValidatePassword(inputPassWord, hashedPass) == true)
{
id = reader["ID"].ToString();
}
}
return id;
}
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;
}