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