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