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