/**
* Add recipe to the database
* */
public static int AddRecipeAsync(Recipe r, int index = -1)
{
int useIndex = RecipeManager.instance.lastRecipeID;
if (index > -1)
{
useIndex = index;
}
//form query
int usedId = -1;
String query = @"INSERT INTO recipes VALUES (" + useIndex + ", '" + MySqlHelper.EscapeString(r.title) + "', '" + MySqlHelper.EscapeString(r.description) + "' , " + RecipeManager.instance.GetCurrentUser().id + ");";
DBConnector.initAsync();
MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
cmd.ExecuteNonQuery();
//add ingredients
IngredientDAO.AddIngredient((int)cmd.LastInsertedId, r);
DBConnector.conn.Close();
usedId = RecipeManager.instance.lastRecipeID;
RecipeManager.instance.lastRecipeID++;
return(usedId);
}
/**
* Add new user
* @Param username - username
* @Param lastname - lastname of user
* @Param password - password of user
* */
public static bool addUser(string username, string lastname, string password)
{
string query = @"INSERT INTO users VALUES (NULL, '" + username + "', '" + password + "', '" + lastname + "');";
DBConnector.initAsync();
MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
cmd.ExecuteNonQuery();
DBConnector.conn.Close();
return(true);
}
/**
* Delete shopping list
* */
public static void DeleteShoppingList(ShoppingList shoppingList)
{
String query = @"DELETE FROM shoppinglist WHERE idshoppinglist=" + shoppingList.id;
DBConnector.initAsync();
MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
cmd.ExecuteNonQuery();
DBConnector.conn.Close();
}
/**
* Add recipe to the database
* */
public static bool AddIngredient(int id, Recipe r)
{
foreach (Ingredient i in r.ingredients)
{
//form query
String query = @"INSERT INTO ingredients VALUES (NULL, '" + MySqlHelper.EscapeString(i.Name) + "', " + i.Amount + " , '" + i.Unit.Name + "' , " + id + ");";
DBConnector.initAsync();
MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
cmd.ExecuteNonQuery();
DBConnector.conn.Close();
}
return(true);
}
/**
* get user by username and pass
* */
public static User getUser(string username, string password)
{
User user = null;
string query = @"SELECT * FROM users WHERE username = '******' AND password = '******'";
DBConnector.initAsync();
MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
user = new User(int.Parse(reader["id"] + ""), reader["username"] + "");
}
return(user);
}
/**
* Delete given recipe from the database
* */
public static void DeleteRecipe(Recipe recipe)
{
//form query
String queryIngredients = @"DELETE FROM ingredients WHERE Recipes_idRecipes=" + recipe.id;
String query = @"DELETE FROM recipes WHERE idRecipes=" + recipe.id;
DBConnector.initAsync();
MySqlCommand cmdIngredients = new MySqlCommand(queryIngredients, DBConnector.conn);
cmdIngredients.ExecuteNonQuery();
MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
cmd.ExecuteNonQuery();
DBConnector.conn.Close();
}
/**
* Get a list of all recipes by given user id
* */
public static List <Recipe> GetAll(int userid)
{
List <Recipe> r = new List <Recipe>();
//form query
String query = @"SELECT * FROM recipes WHERE users_id = " + userid;
DBConnector.initAsync();
MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
MySqlDataReader reader = cmd.ExecuteReader();
//add results to the collection
while (reader.Read())
{
r.Add(new Recipe(reader["description"] + "", reader["title"] + ""));
r.ElementAt(r.Count - 1).id = int.Parse(reader["idrecipes"] + "");
r.ElementAt(r.Count - 1).ingredients = IngredientDAO.GetAll((r.ElementAt(r.Count - 1).id));
}
DBConnector.conn.Close();
String queryMax = @"SELECT MAX(idRecipes) AS RINDEX FROM recipes";
DBConnector.initAsync();
MySqlCommand cmdMax = new MySqlCommand(queryMax, DBConnector.conn);
MySqlDataReader readerMax = cmdMax.ExecuteReader();
while (readerMax.Read())
{
RecipeManager.instance.lastRecipeID = (int)readerMax["RINDEX"] + 1;
}
DBConnector.conn.Close();
return(r);
}
/**
* Get all ingredients of a recipe with given id
* */
public static ObservableCollection <Ingredient> GetAll(int recipeid)
{
//form query
ObservableCollection <Ingredient> r = new ObservableCollection <Ingredient>();
String query = @"SELECT * FROM ingredients WHERE recipes_idrecipes = " + recipeid;
DBConnector.initAsync();
MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
MySqlDataReader reader = cmd.ExecuteReader();
//fix ingredients units and add them to the collection
while (reader.Read())
{
Ingredient i = new Ingredient();
i.Name = reader["name"] + "";
i.Amount = int.Parse(reader["amount"] + "");
String units = reader["unit"] + "";
switch (units)
{
case "Liter":
i.Unit = new Units()
{
Name = "Liter", Short = "L", Unit = EUnit.Liter
};
r.Add(i);
break;
case "Pieces":
i.Unit = new Units()
{
Name = "Pieces", Short = "P.", Unit = EUnit.Pieces
};
r.Add(i);
break;
case "Spoons":
i.Unit = new Units()
{
Name = "Spoons", Short = "Spns.", Unit = EUnit.Spoons
};
r.Add(i);
break;
case "Mililiter":
i.Unit = new Units()
{
Name = "Mililiter", Short = "ml", Unit = EUnit.Spoons
};
r.Add(i);
break;
case "Kilogramm":
i.Unit = new Units()
{
Name = "Kilogramm", Short = "kg", Unit = EUnit.Kilogramm
};
r.Add(i);
break;
case "Gram":
i.Unit = new Units()
{
Name = "Gramm", Short = "G", Unit = EUnit.Gramm
};
r.Add(i);
break;
case "Miligram":
i.Unit = new Units()
{
Name = "Miligram", Short = "mg", Unit = EUnit.Milligramm
};
r.Add(i);
break;
default:
break;
}
}
return(r);
}
/**
* Get all shopping lists by user id
* */
public static List <ShoppingList> GetAll(int userid)
{
List <ShoppingList> sl = new List <ShoppingList>();
//form query
String query = @"SELECT * FROM shoppinglist WHERE user_id = '" + userid + "'";
DBConnector.initAsync();
MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
MySqlDataReader reader = cmd.ExecuteReader();
//sl.Add(new ShoppingList(((int)reader["idshoppinglist"])))
//add shopping lists to collection
while (reader.Read())
{
//assign shopping list id and recipes id
int sId = (int)reader["idshoppinglist"];
int rId = (int)reader["recipe_id"];
int amount = (int)reader["amount"];
bool addRecipe = true;
bool addShoppingList = true;
for (int i = 0; i < sl.Count; i++)
{
if (sl[i].id == sId)
{
addShoppingList = false;
for (int j = 0; j < sl[i].recipes.Count; j++)
{
if (sl[i].recipes[j].id == rId)
{
addRecipe = false;
}
}
if (addRecipe)
{
foreach (Recipe r in RecipeManager.instance.GetCurrentUser().recipes)
{
if (r.id == rId)
{
for (int k = 0; k < amount; k++)
{
sl[sl.Count - 1].AddRecipe(r);
}
}
}
}
}
}
if (addShoppingList)
{
sl.Add(new ShoppingList((sId)));
foreach (Recipe r in RecipeManager.instance.GetCurrentUser().recipes)
{
if (r.id == rId)
{
for (int k = 0; k < amount; k++)
{
sl[sl.Count - 1].AddRecipe(r);
}
sl[sl.Count - 1].date = DateTime.Now; //TODO: NEEDS TO BE THE DB DATE!
}
}
}
}
DBConnector.conn.Close();
//get last shopping list index and assign it
String queryMax = @"SELECT MAX(idshoppinglist) AS SINDEX FROM shoppinglist";
DBConnector.initAsync();
MySqlCommand cmdMax = new MySqlCommand(queryMax, DBConnector.conn);
MySqlDataReader readerMax = cmdMax.ExecuteReader();
while (readerMax.Read())
{
RecipeManager.instance.lastShoppingID = (int)readerMax["SINDEX"] + 1;
}
DBConnector.conn.Close();
return(sl);
}
/**
* Add shopping list to given user id
* */
public static int AddShoppinglistAsync(ShoppingList sl, int id = -1)
{
int usedId = 0;
bool added = false;
Dictionary <int, int> recipeList = new Dictionary <int, int>();
//get last shopping id
int useIndex = RecipeManager.instance.lastShoppingID;
if (id > -1)
{
useIndex = id;
}
for (int i = 0; i < sl.recipes.Count; i++)
{
int amountOne = 0;
for (int y = 0; y < sl.recipes.Count; y++)
{
if (sl.recipes[i].id == sl.recipes[y].id)
{
amountOne++;
}
}
if (!recipeList.ContainsKey(sl.recipes[i].id))
{
recipeList.Add(sl.recipes[i].id, amountOne);
}
}
foreach (KeyValuePair <int, int> item in recipeList)
{
if (item.Value > 0)
{
//form query
string query = @"INSERT INTO shoppinglist VALUES (" + useIndex + ", 'NOW()', " + item.Key + ", " + RecipeManager.instance.GetCurrentUser().id + ", " + item.Value + ")";
DBConnector.initAsync();
MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
cmd.ExecuteNonQuery();
added = true;
DBConnector.conn.Close();
}
}
if (added)
{
usedId = RecipeManager.instance.lastShoppingID;
RecipeManager.instance.lastShoppingID++;
return(usedId);
}
else
{
return(-1);
}
/*
* String query = @"INSERT INTO shoppinglist VALUES ('NULL', 'NOW()', ''";
* DBConnector.initAsync();
* MySqlCommand cmd = new MySqlCommand(query, DBConnector.conn);
* cmd.ExecuteNonQuery();
*
*
* foreach(Recipe r in sl.recipes)
* {
* IngredientDAO.AddIngredient((int)cmd.LastInsertedId, r);
* }
*/
}