public static void GetUserData(User user) { var id = (GetUserId(user.Username)); string sql = "SELECT * FROM [DaneUzytkownika] WHERE ([IdUzytkownika] = " + @id + ");"; SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); SqlCommand cmd = new SqlCommand(sql, sqlConnection); cmd.Parameters.AddWithValue("@IdUzytkownika", id); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { user.UserWeight = int.Parse((read["Waga"]).ToString()); user.UserHeight = int.Parse((read["Wzrost"]).ToString()); user.UserType = (read["Somatotyp"]).ToString(); user.UserdailyActivity = (read["DziennaAktywnosc"]).ToString(); user.UserTSWeekly = int.Parse((read["TStygodniowo"]).ToString()); user.UserTSDaily = int.Parse((read["TSdziennie"]).ToString()); user.UserTSIntensivity = (read["TSintensywnosc"]).ToString(); user.UserTAIntensivity = (read["TAintensywnosc"]).ToString(); user.UserTADaily = int.Parse((read["TAdziennie"]).ToString()); user.UserTAWeekly = int.Parse((read["TAtygodniowo"]).ToString()); user.UserFoodRestrictions = (read["Nietolerancje"]).ToString(); user.UserMealNumber = int.Parse((read["LiczbaPosilkow"]).ToString()); } } }
/* public static int GetProfileId(int id) * { * Int32 profileId=0; * string sql = "SELECT Id FROM [ProfileUzytkownikow] WHERE ([IdUzytkownika] = @id)"; * SqlConnection sqlConnection = new SqlConnection(); * sqlConnection = DataBaseSolution.Get_DB_Connection(); * using (sqlConnection) * { * SqlCommand cmd = new SqlCommand(sql, sqlConnection); * cmd.Parameters.Add("@IdUzytkownika", SqlDbType.Int); * cmd.Parameters["@IdUzytkownika"].Value = id; * try * { * sqlConnection.Open(); * profileId = (Int32)cmd.ExecuteScalar(); * } * catch (Exception ex) * { * Console.WriteLine(ex.Message); * } * } * return (int)profileId; * } */ public static void UpdateUserData(User user) { var Username = user.Username.Trim(); var UserSex = user.UserSex.Trim(); var UserType = user.UserType.Trim(); var UserdailyActivity = user.UserdailyActivity.Trim(); var UserTSIntensivity = user.UserTSIntensivity.Trim(); var UserTAIntensivity = user.UserTAIntensivity.Trim(); var UserFoodRestrictions = user.UserFoodRestrictions.Trim(); SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); string sql = "UPDATE DaneUzytkownika SET Plec = @plec,Wiek = @wiek, Waga=@waga, Wzrost=@wzrost,Somatotyp=@typ, DziennaAktywnosc=@da,TStygodniowo=@tst, TSdziennie=@tsd,TSintensywnosc=@tsi,TAtygodniowo=@tat, TAdziennie=@tad,TAintensywnosc=@tai, Nietolerancje=@nietolerancje, LiczbaPosilkow=@liczba WHERE IdUzytkownika=(Select Id from Uzytkownik Where ([Nazwa] = @nazwa));"; SqlCommand command = new SqlCommand(sql, sqlConnection); command.Parameters.AddWithValue("@plec", UserSex); command.Parameters.AddWithValue("@waga", user.UserWeight); command.Parameters.AddWithValue("@wiek", user.UserAge); command.Parameters.AddWithValue("@wzrost", user.UserHeight); command.Parameters.AddWithValue("@typ", UserType); command.Parameters.AddWithValue("@da", UserdailyActivity); command.Parameters.AddWithValue("@tst", user.UserTSWeekly); command.Parameters.AddWithValue("@tsd", user.UserTSDaily); command.Parameters.AddWithValue("@tsi", UserTSIntensivity); command.Parameters.AddWithValue("@tat", user.UserTAWeekly); command.Parameters.AddWithValue("@tad", user.UserTADaily); command.Parameters.AddWithValue("@tai", UserTAIntensivity); command.Parameters.AddWithValue("@nietolerancje", UserFoodRestrictions); command.Parameters.AddWithValue("@nazwa", Username); command.Parameters.AddWithValue("@liczba", user.UserMealNumber); command.ExecuteNonQuery(); }
public static Product SearchProduct(string name) { Product product1 = new Product(); var name1 = (name).Replace("'", "''"); SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); string sql = "SELECT COUNT(*) FROM [Produkty] WHERE ([Nazwa] = '" + name1 + "');"; SqlCommand check_Name = new SqlCommand(sql, sqlConnection); check_Name.Parameters.AddWithValue("@Nazwa", @name1); int UserExist = (int)check_Name.ExecuteScalar(); if (UserExist != 0) { string sql1 = "SELECT * FROM [Produkty] WHERE ([Nazwa] = '" + name1 + "');"; SqlCommand cmd = new SqlCommand(sql1, sqlConnection); cmd.Parameters.AddWithValue("@Nazwa", @name1); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { product1.Name = (read["Nazwa"]).ToString(); product1.Quantity = int.Parse((read["Ilosc"]).ToString()); product1.Unit = (read["Jednostka"]).ToString(); product1.Kcal = float.Parse((read["KCAL"]).ToString()); } } } return(product1); }
public static void AddUser(string nazwa, string haslo) { SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); SqlCommand check_User_Name = new SqlCommand("SELECT COUNT(*) FROM [Uzytkownik] WHERE ([Nazwa] = @nazwa)", sqlConnection); check_User_Name.Parameters.AddWithValue("@Nazwa", nazwa); int UserExist = (int)check_User_Name.ExecuteScalar(); if (UserExist == 0) { //< add > string sql_Add = "INSERT INTO Uzytkownik (Nazwa, Haslo) Values ('" + nazwa + "' , '" + haslo + "');"; DataBaseSolution.Execute_SQL(sql_Add); string sql_Add_Profile = "INSERT INTO ProfileUzytkownikow (IdUzytkownika) SELECT (Id) FROM Uzytkownik WHERE (Nazwa='" + nazwa + "') ;"; DataBaseSolution.Execute_SQL(sql_Add_Profile); string sql_Add_UserData = "INSERT INTO DaneUzytkownika (IdUzytkownika) SELECT (Id) FROM Uzytkownik WHERE (Nazwa='" + nazwa + "') ;"; DataBaseSolution.Execute_SQL(sql_Add_UserData); //</ add > } }
public static void AddProduct(Product product) { var name = (product.Name).Replace("'", "''"); int quan = (product.Quantity); var unit = (product.Unit).Replace("'", "''"); float kcal = product.Kcal; SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); string sql = "SELECT COUNT(*) FROM [Produkty] WHERE ([Nazwa] = '" + name + "');"; SqlCommand check_Name = new SqlCommand(sql, sqlConnection); check_Name.Parameters.AddWithValue("@Nazwa", @name); int ProductExist = (int)check_Name.ExecuteScalar(); if (ProductExist == 0) { string sql_Add = "INSERT INTO Produkty (Nazwa, Ilosc, Jednostka, KCAL) Values (@nazwa, @ilosc, @jednostka, @kcal);"; SqlCommand command = new SqlCommand(sql_Add, sqlConnection); command.Parameters.AddWithValue("@nazwa", name); command.Parameters.AddWithValue("@ilosc", quan); command.Parameters.AddWithValue("@jednostka", unit); command.Parameters.AddWithValue("@kcal", kcal); command.ExecuteNonQuery(); MessageBox.Show("Produkt został dodany"); } }
public static void ADDWeight(int id, float weight) { SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); string sql_Add = "INSERT INTO DziennikUzytkownika (IdUzytkownika, Waga, Data) Values ('" + id + "' , '" + weight + "', '" + DateTime.Now + "');"; DataBaseSolution.Execute_SQL(sql_Add); }
public static bool FoodPlanExist(User user) { SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); string sql = "SELECT Count(*) FROM [PlanZywieniowy] WHERE IdUzytkownika = @id;"; SqlCommand check_User_Name = new SqlCommand(sql, sqlConnection); check_User_Name.Parameters.AddWithValue("@id", user.UserId); int UserExist = (int)check_User_Name.ExecuteScalar(); if (UserExist == 0) { return(false); } return(true); }
//-------------------</ Class: DB >------------------- public static bool FindUser(string nazwa) { SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); SqlCommand check_User_Name = new SqlCommand("SELECT COUNT(*) FROM [Uzytkownik] WHERE ([Nazwa] = @nazwa)", sqlConnection); check_User_Name.Parameters.AddWithValue("@Nazwa", nazwa); int UserExist = (int)check_User_Name.ExecuteScalar(); if (UserExist > 0) { return(true); } else { return(false); } }
public static List <Tuple <DateTime, float> > GetWeightList(int id) { List <Tuple <DateTime, float> > WeightList = new List <Tuple <DateTime, float> >(); SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); SqlCommand command = new SqlCommand("SELECT [Data],[Waga] FROM [DziennikUzytkownika] WHERE ([IdUzytkownika] = " + @id + ");", sqlConnection); command.Parameters.AddWithValue("@IdUzytkownika", id); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { var date = DateTime.Parse((reader[0]).ToString()); var weight = Int32.Parse((reader[1]).ToString()); WeightList.Add(new Tuple <DateTime, float>(date, weight)); } return(WeightList); }
public static int GetUserId(string nazwa) { int userId = 0; nazwa = nazwa.Replace("'", "''"); string sql = "SELECT Id FROM [Uzytkownik] WHERE ([Nazwa] = @nazwa);"; SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); SqlCommand cmd = new SqlCommand(sql, sqlConnection); cmd.Parameters.AddWithValue("@Nazwa", nazwa); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { userId = int.Parse((reader["Id"]).ToString()); } } return((int)userId); }
public static void AddRecipe(Meal meal) { var name = (meal.Name).Replace("'", "''"); var daytime = (meal.Daytime).Trim(); var desc = (meal.Description).Replace("'", "''"); float kcal = meal.Caloricity; var skladniki = (meal.Ingredients).Replace("'", "''"); var ograniczenia = (meal.FoodRestrictions11).Replace("'", "''"); SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); string sql_Add = "INSERT INTO Jadlospisy (Nazwa, PoraDnia, KCAL, Opis, Skladniki,Ograniczenia) Values (@nazwa, @pd, @kcal, @opis, @skladniki, @ograniczenia);"; SqlCommand command = new SqlCommand(sql_Add, sqlConnection); command.Parameters.AddWithValue("@nazwa", name); command.Parameters.AddWithValue("@pd", daytime); command.Parameters.AddWithValue("@kcal", kcal); command.Parameters.AddWithValue("@opis", desc); command.Parameters.AddWithValue("@skladniki", skladniki); command.Parameters.AddWithValue("@ograniczenia", ograniczenia); command.ExecuteNonQuery(); MessageBox.Show("Przepis został dodany"); }
public static Meal GetMeal(int id) { Meal meal = new Meal(); SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); string sql = "SELECT* FROM [Jadlospisy] WHERE Id = @id;"; SqlCommand cmd = new SqlCommand(sql, sqlConnection); cmd.Parameters.AddWithValue("@id", id); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal.Name = (read["Nazwa"].ToString()); meal.Daytime = (read["PoraDnia"].ToString()); meal.Caloricity = float.Parse(read["KCAL"].ToString()); meal.Description = (read["Opis"].ToString()); meal.Ingredients = (read["Skladniki"].ToString()); meal.FoodRestrictions11 = (read["Ograniczenia"].ToString()); } } return(meal); }
public static void AddKCAL(int id, int kcal) { SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); SqlCommand check_User_Name = new SqlCommand("SELECT COUNT(*) FROM [PlanZywieniowy] WHERE ([IdUzytkownika] = " + @id + ");", sqlConnection); check_User_Name.Parameters.AddWithValue("@IdUzytkownika", id); int UserExist = (int)check_User_Name.ExecuteScalar(); if (UserExist == 0) { string sql_Add = "INSERT INTO PlanZywieniowy (IdUzytkownika, DzienneKCAL) Values ('" + id + "' , '" + kcal + "');"; DataBaseSolution.Execute_SQL(sql_Add); } else if (UserExist > 0) { string sql = "UPDATE PlanZywieniowy SET DzienneKCAL = @kcal WHERE ([IdUzytkownika] = " + @id + ");"; SqlCommand command = new SqlCommand(sql, sqlConnection); command.Parameters.AddWithValue("@kcal", kcal); command.Parameters.AddWithValue("@id", id); command.ExecuteNonQuery(); } }
public static void DeleteUser(User user) { var id = (GetUserId(user.Username)); string sql = "DELETE FROM [DaneUzytkownika] WHERE ([IdUzytkownika] = " + @id + ");"; string sql1 = "DELETE FROM [ProfileUzytkownikow] WHERE ([IdUzytkownika] = " + @id + ");"; string sql2 = "DELETE FROM [Uzytkownik] WHERE ([Id] = " + @id + ");"; SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); SqlCommand cmd = new SqlCommand(sql, sqlConnection); cmd.Parameters.AddWithValue("@IdUzytkownika", id); SqlCommand cmd1 = new SqlCommand(sql1, sqlConnection); cmd1.Parameters.AddWithValue("@IdUzytkownika", id); SqlCommand cmd2 = new SqlCommand(sql2, sqlConnection); cmd2.Parameters.AddWithValue("@Id", id); cmd.ExecuteNonQuery(); cmd1.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); }
public static FoodPlan GetFoodPlan(User user) { FoodPlan food = new FoodPlan(); SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); string sql = "SELECT * FROM [PlanZywieniowy] WHERE IdUzytkownika = @id;"; SqlCommand cmd1 = new SqlCommand(sql, sqlConnection); cmd1.Parameters.AddWithValue("@id", user.UserId); using (SqlDataReader read = cmd1.ExecuteReader()) { if (read.Read()) { food.meal1.MealId = Int32.Parse(read["IdJadlospisu"].ToString()); } } food.meal1 = GetMeal(food.meal1.MealId); string sql1 = "SELECT * FROM [PlanZywieniowy] WHERE(([PoraDnia] LIKE '%Sniadanie%') AND (IdUzytkownika = @id)); "; SqlCommand cmd2 = new SqlCommand(sql1, sqlConnection); cmd2.Parameters.AddWithValue("@id", user.UserId); using (SqlDataReader read = cmd2.ExecuteReader()) { if (read.Read()) { food.meal2.MealId = Int32.Parse(read["IdJadlospisu"].ToString()); } } food.meal2 = GetMeal(food.meal2.MealId); string sql2 = "SELECT * FROM [PlanZywieniowy] WHERE( ([PoraDnia] LIKE '%II sniadanie/podwieczorek%') AND (IdUzytkownika = @id)); "; SqlCommand cmd3 = new SqlCommand(sql2, sqlConnection); cmd3.Parameters.AddWithValue("@id", user.UserId); using (SqlDataReader read = cmd3.ExecuteReader()) { if (read.Read()) { food.meal3.MealId = Int32.Parse(read["IdJadlospisu"].ToString()); } } food.meal3 = GetMeal(food.meal3.MealId); if ((user.UserMealNumber == 4) || (user.UserMealNumber == 5)) { string sql3 = "SELECT * FROM [PlanZywieniowy] WHERE( ([PoraDnia] LIKE '%Obiad%') AND (IdUzytkownika = @id)); "; SqlCommand cmd4 = new SqlCommand(sql3, sqlConnection); cmd4.Parameters.AddWithValue("@id", user.UserId); using (SqlDataReader read = cmd4.ExecuteReader()) { if (read.Read()) { food.meal4.MealId = Int32.Parse(read["IdJadlospisu"].ToString()); } } food.meal4 = GetMeal(food.meal4.MealId); } if (user.UserMealNumber == 5) { string sql4 = "SELECT * FROM [PlanZywieniowy] WHERE( ([PoraDnia] LIKE '%Kolacja%') AND (IdUzytkownika = @id)); "; SqlCommand cmd5 = new SqlCommand(sql4, sqlConnection); cmd5.Parameters.AddWithValue("@id", user.UserId); using (SqlDataReader read = cmd5.ExecuteReader()) { if (read.Read()) { food.meal5.MealId = Int32.Parse(read["IdJadlospisu"].ToString()); } } food.meal5 = GetMeal(food.meal5.MealId); } return(food); }
public static void SaveFoodPlan(FoodPlan food) { Algorithm algorithm = new Algorithm(food.user1); float kcal = algorithm.FinalKCALResult(); SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); SqlCommand check_1 = new SqlCommand("SELECT COUNT(*) FROM [PlanZywieniowy] WHERE (([PoraDnia] LIKE '%Sniadanie%') AND [IdUzytkownika] = '" + food.user1.UserId + "');", sqlConnection); int Exist1 = (int)check_1.ExecuteScalar(); SqlCommand check_2 = new SqlCommand("SELECT COUNT(*) FROM [PlanZywieniowy] WHERE (([PoraDnia] LIKE '%II sniadanie/podwieczorek%') AND [IdUzytkownika] = '" + food.user1.UserId + "');", sqlConnection); int Exist2 = (int)check_1.ExecuteScalar(); SqlCommand check_3 = new SqlCommand("SELECT COUNT(*) FROM [PlanZywieniowy] WHERE ((([PoraDnia] LIKE '%Obiad%') AND [IdUzytkownika] = '" + food.user1.UserId + "');", sqlConnection); int Exist3 = (int)check_1.ExecuteScalar(); SqlCommand check_5 = new SqlCommand("SELECT COUNT(*) FROM [PlanZywieniowy] (([PoraDnia] LIKE '%Kolacja%') AND [IdUzytkownika] = '" + food.user1.UserId + "');", sqlConnection); int Exist4 = (int)check_1.ExecuteScalar(); MessageBox.Show((food.user1.UserMealCaloricty[0], food.user1.UserMealCaloricty[1], food.user1.UserMealCaloricty[2]).ToString()); if (food.user1.UserMealNumber == 3) { if ((Exist1 == 0) && (Exist3 == 0) && (Exist4 == 0)) { string sql1 = "INSERT INTO PlanZywieniowy (IdUzytkownika, DzienneKCAL, PoraDnia, IDJadlospisu) Values (@id, @kcal, @pd, @idj);"; SqlCommand command = new SqlCommand(sql1, sqlConnection); command.Parameters.AddWithValue("@id", food.user1.UserId); command.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command.Parameters.AddWithValue("@pd", food.meal1.Daytime); command.Parameters.AddWithValue("@idj", food.meal1.MealId); command.ExecuteNonQuery(); SqlCommand command1 = new SqlCommand(sql1, sqlConnection); command1.Parameters.AddWithValue("@id", food.user1.UserId); command1.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command1.Parameters.AddWithValue("@pd", food.meal2.Daytime); command1.Parameters.AddWithValue("@idj", food.meal2.MealId); command1.ExecuteNonQuery(); SqlCommand command2 = new SqlCommand(sql1, sqlConnection); command2.Parameters.AddWithValue("@id", food.user1.UserId); command2.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command2.Parameters.AddWithValue("@pd", food.meal3.Daytime); command2.Parameters.AddWithValue("@idj", food.meal3.MealId); command2.ExecuteNonQuery(); } else if ((Exist1 > 0)) { string sql1 = "UPDATE PlanZywieniowy SET DzienneKCAL = @kcal, PoraDnia = @pd , IDJadlospisu = @idj WHERE IdUzytkownika = @id;"; SqlCommand command = new SqlCommand(sql1, sqlConnection); command.Parameters.AddWithValue("@id", food.user1.UserId); command.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command.Parameters.AddWithValue("@pd", food.meal1.Daytime); command.Parameters.AddWithValue("@idj", food.meal1.MealId); command.ExecuteNonQuery(); } else if ((Exist3 > 0)) { string sql1 = "UPDATE PlanZywieniowy SET DzienneKCAL = @kcal, PoraDnia = @pd , IDJadlospisu = @idj WHERE IdUzytkownika = @id;"; SqlCommand command1 = new SqlCommand(sql1, sqlConnection); command1.Parameters.AddWithValue("@id", food.user1.UserId); command1.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command1.Parameters.AddWithValue("@pd", food.meal2.Daytime); command1.Parameters.AddWithValue("@idj", food.meal2.MealId); command1.ExecuteNonQuery(); } else if ((Exist4 > 0)) { string sql1 = "UPDATE PlanZywieniowy SET DzienneKCAL = @kcal, PoraDnia = @pd , IDJadlospisu = @idj WHERE IdUzytkownika = @id;"; SqlCommand command2 = new SqlCommand(sql1, sqlConnection); command2.Parameters.AddWithValue("@id", food.user1.UserId); command2.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command2.Parameters.AddWithValue("@pd", food.meal3.Daytime); command2.Parameters.AddWithValue("@idj", food.meal3.MealId); command2.ExecuteNonQuery(); } } if (food.user1.UserMealNumber > 3) { if ((Exist1 == 0) && ((Exist2 == 0 || Exist2 == 1)) && (Exist3 == 0) && (Exist4 == 0)) { string sql1 = "INSERT INTO PlanZywieniowy (IdUzytkownika, DzienneKCAL, PoraDnia, IDJadlospisu) Values (@id, @kcal, @pd, @idj);"; SqlCommand command = new SqlCommand(sql1, sqlConnection); command.Parameters.AddWithValue("@id", food.user1.UserId); command.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command.Parameters.AddWithValue("@pd", food.meal1.Daytime); command.Parameters.AddWithValue("@idj", food.meal1.MealId); command.ExecuteNonQuery(); SqlCommand command1 = new SqlCommand(sql1, sqlConnection); command1.Parameters.AddWithValue("@id", food.user1.UserId); command1.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command1.Parameters.AddWithValue("@pd", food.meal2.Daytime); command1.Parameters.AddWithValue("@idj", food.meal2.MealId); command1.ExecuteNonQuery(); SqlCommand command2 = new SqlCommand(sql1, sqlConnection); command2.Parameters.AddWithValue("@id", food.user1.UserId); command2.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command2.Parameters.AddWithValue("@pd", food.meal3.Daytime); command2.Parameters.AddWithValue("@idj", food.meal3.MealId); command2.ExecuteNonQuery(); if ((food.user1.UserMealNumber == 4) || (food.user1.UserMealNumber == 5)) { SqlCommand command3 = new SqlCommand(sql1, sqlConnection); command3.Parameters.AddWithValue("@id", food.user1.UserId); command3.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command3.Parameters.AddWithValue("@pd", food.meal4.Daytime); command3.Parameters.AddWithValue("@idj", food.meal4.MealId); command3.ExecuteNonQuery(); } if ((food.user1.UserMealNumber == 5)) { SqlCommand command4 = new SqlCommand(sql1, sqlConnection); command4.Parameters.AddWithValue("@id", food.user1.UserId); command4.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command4.Parameters.AddWithValue("@pd", food.meal5.Daytime); command4.Parameters.AddWithValue("@idj", food.meal5.MealId); command4.ExecuteNonQuery(); } } else if ((Exist1 > 0)) { string sql1 = "UPDATE PlanZywieniowy SET DzienneKCAL = @kcal, PoraDnia = @pd , IDJadlospisu = @idj WHERE IdUzytkownika = @id;"; SqlCommand command = new SqlCommand(sql1, sqlConnection); command.Parameters.AddWithValue("@id", food.user1.UserId); command.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command.Parameters.AddWithValue("@pd", food.meal1.Daytime); command.Parameters.AddWithValue("@idj", food.meal1.MealId); command.ExecuteNonQuery(); } else if ((Exist3 > 0)) { string sql1 = "UPDATE PlanZywieniowy SET DzienneKCAL = @kcal, PoraDnia = @pd , IDJadlospisu = @idj WHERE IdUzytkownika = @id;"; SqlCommand command1 = new SqlCommand(sql1, sqlConnection); command1.Parameters.AddWithValue("@id", food.user1.UserId); command1.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command1.Parameters.AddWithValue("@pd", food.meal2.Daytime); command1.Parameters.AddWithValue("@idj", food.meal2.MealId); command1.ExecuteNonQuery(); } else if ((Exist2 > 1)) { string sql1 = "UPDATE PlanZywieniowy SET DzienneKCAL = @kcal, PoraDnia = @pd , IDJadlospisu = @idj WHERE IdUzytkownika = @id;"; SqlCommand command2 = new SqlCommand(sql1, sqlConnection); command2.Parameters.AddWithValue("@id", food.user1.UserId); command2.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command2.Parameters.AddWithValue("@pd", food.meal3.Daytime); command2.Parameters.AddWithValue("@idj", food.meal3.MealId); command2.ExecuteNonQuery(); } else if ((Exist4 > 0)) { string sql1 = "UPDATE PlanZywieniowy SET DzienneKCAL = @kcal, PoraDnia = @pd , IDJadlospisu = @idj WHERE IdUzytkownika = @id;"; SqlCommand command2 = new SqlCommand(sql1, sqlConnection); command2.Parameters.AddWithValue("@id", food.user1.UserId); command2.Parameters.AddWithValue("@kcal", algorithm.FinalKCALResult()); command2.Parameters.AddWithValue("@pd", food.meal3.Daytime); command2.Parameters.AddWithValue("@idj", food.meal3.MealId); command2.ExecuteNonQuery(); } } }
public static FoodPlan GenerateFoodPlan(User user) { Algorithm algorithm = new Algorithm(user); algorithm.CountMealCaloricty(); FoodPlan foodPlan = new FoodPlan(); //float kcal = algorithm.FinalKCALResult(); SqlConnection sqlConnection = new SqlConnection(); sqlConnection = DataBaseSolution.Get_DB_Connection(); Meal meal1 = new Meal(); Meal meal2 = new Meal(); Meal meal3 = new Meal(); Meal meal4 = new Meal(); Meal meal5 = new Meal(); string userRestrictions = user.UserFoodRestrictions; string resultRestricitons = ""; String[] userRestrictions1 = userRestrictions.Split(','); /* foreach (string uFR in userRestrictions1) * { * if (uFR == "False") { resultRestricitons += "True,"; } * else { resultRestricitons += "False,"; } * } * resultRestricitons = resultRestricitons.Substring(0, resultRestricitons.Length - 1); * resultRestricitons = "%" + resultRestricitons + "%"; * resultRestricitons.Trim();*/ resultRestricitons = userRestrictions; if (user.UserMealNumber == 3) { //ograniczenia string sql1 = "SELECT TOP 1 * FROM Jadlospisy WHERE(([PoraDnia] LIKE '%Sniadanie%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[0] + "')) ORDER BY NEWID() ; ";// AND ([Ograniczenia] LIKE ('" + resultRestricitons + "')) string sql2 = "SELECT TOP 1 * FROM Jadlospisy WHERE( ([PoraDnia] LIKE '%Obiad%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[1] + "')) ORDER BY NEWID() ; "; string sql3 = "SELECT TOP 1 * FROM Jadlospisy WHERE( ([PoraDnia] LIKE '%Kolacja%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[2] + "')) ORDER BY NEWID() ; "; SqlCommand cmd = new SqlCommand(sql1, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal1.MealId = Int32.Parse(read["Id"].ToString()); meal1.Name = (read["Nazwa"]).ToString(); meal1.Daytime = (read["PoraDnia"]).ToString(); meal1.Caloricity = float.Parse((read["KCAL"]).ToString()); meal1.Description = (read["Opis"]).ToString(); meal1.Ingredients = (read["Skladniki"]).ToString(); meal1.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } cmd = new SqlCommand(sql2, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal2.MealId = Int32.Parse(read["Id"].ToString()); meal2.Name = (read["Nazwa"]).ToString(); meal2.Daytime = (read["PoraDnia"]).ToString(); meal2.Caloricity = float.Parse((read["KCAL"]).ToString()); meal2.Description = (read["Opis"]).ToString(); meal2.Ingredients = (read["Skladniki"]).ToString(); meal2.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } cmd = new SqlCommand(sql3, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal3.MealId = Int32.Parse(read["Id"].ToString()); meal3.Name = (read["Nazwa"]).ToString(); meal3.Daytime = (read["PoraDnia"]).ToString(); meal3.Caloricity = float.Parse((read["KCAL"]).ToString()); meal3.Description = (read["Opis"]).ToString(); meal3.Ingredients = (read["Skladniki"]).ToString(); meal3.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } foodPlan = new FoodPlan(user, meal1, meal2, meal3); } if (user.UserMealNumber == 4) { string sql1 = "SELECT TOP 1 * FROM Jadlospisy WHERE(([PoraDnia] LIKE '%Sniadanie%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[0] + "') AND ([Ograniczenia] LIKE ('" + resultRestricitons + "'))) ORDER BY NEWID() ; "; string sql2 = "SELECT TOP 1 * FROM Jadlospisy WHERE( ([PoraDnia] LIKE '%II sniadanie/podwieczorek%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[1] + "') AND ([Ograniczenia] LIKE ('" + resultRestricitons + "'))) ORDER BY NEWID() ; "; string sql3 = "SELECT TOP 1 * FROM Jadlospisy WHERE( ([PoraDnia] LIKE '%Obiad%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[2] + "') AND ([Ograniczenia] LIKE ('" + resultRestricitons + "'))) ORDER BY NEWID() ; "; string sql4 = "SELECT TOP 1 * FROM Jadlospisy WHERE( ([PoraDnia] LIKE '%Kolacja%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[3] + "') AND ([Ograniczenia] LIKE ('" + resultRestricitons + "'))) ORDER BY NEWID() ; "; SqlCommand cmd = new SqlCommand(sql1, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal1.MealId = Int32.Parse(read["Id"].ToString()); meal1.Name = (read["Nazwa"]).ToString(); meal1.Daytime = (read["PoraDnia"]).ToString(); meal1.Caloricity = float.Parse((read["KCAL"]).ToString()); meal1.Description = (read["Opis"]).ToString(); meal1.Ingredients = (read["Skladniki"]).ToString(); meal1.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } cmd = new SqlCommand(sql2, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal2.MealId = Int32.Parse(read["Id"].ToString()); meal2.Name = (read["Nazwa"]).ToString(); meal2.Daytime = (read["PoraDnia"]).ToString(); meal2.Caloricity = float.Parse((read["KCAL"]).ToString()); meal2.Description = (read["Opis"]).ToString(); meal2.Ingredients = (read["Skladniki"]).ToString(); meal2.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } cmd = new SqlCommand(sql3, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal3.MealId = Int32.Parse(read["Id"].ToString()); meal3.Name = (read["Nazwa"]).ToString(); meal3.Daytime = (read["PoraDnia"]).ToString(); meal3.Caloricity = float.Parse((read["KCAL"]).ToString()); meal3.Description = (read["Opis"]).ToString(); meal3.Ingredients = (read["Skladniki"]).ToString(); meal3.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } cmd = new SqlCommand(sql4, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal4.MealId = Int32.Parse(read["Id"].ToString()); meal4.Name = (read["Nazwa"]).ToString(); meal4.Daytime = (read["PoraDnia"]).ToString(); meal4.Caloricity = float.Parse((read["KCAL"]).ToString()); meal4.Description = (read["Opis"]).ToString(); meal4.Ingredients = (read["Skladniki"]).ToString(); meal4.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } foodPlan = new FoodPlan(user, meal1, meal2, meal3, meal4); } if (user.UserMealNumber == 5) { string sql1 = "SELECT TOP 1 * FROM Jadlospisy WHERE(([PoraDnia] LIKE '%Sniadanie%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[0] + "') AND ([Ograniczenia] LIKE ('" + resultRestricitons + "'))) ORDER BY NEWID() ; "; //AND ([Ograniczenia] LIKE ('" + resultRestricitons + "'))) string sql2 = "SELECT TOP 1 * FROM Jadlospisy WHERE( ([PoraDnia] LIKE '%II sniadanie/podwieczorek%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[1] + "') ) ORDER BY NEWID() ; "; //AND ([Ograniczenia] LIKE ('" + resultRestricitons + "')) string sql3 = "SELECT TOP 1 * FROM Jadlospisy WHERE( ([PoraDnia] LIKE '%Obiad%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[2] + "') ) ORDER BY NEWID() ; "; //AND ([Ograniczenia] LIKE ('" + resultRestricitons + "')) string sql4 = "SELECT TOP 1 * FROM Jadlospisy WHERE( ([PoraDnia] LIKE '%II sniadanie/podwieczorek%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[3] + "') AND ([Ograniczenia] LIKE ('" + resultRestricitons + "'))) ORDER BY NEWID() ; "; SqlCommand cmd = new SqlCommand(sql1, sqlConnection); string sql5 = "SELECT TOP 1 * FROM Jadlospisy WHERE( ([PoraDnia] LIKE '%Kolacja%') AND ([KCAL] LIKE '" + user.UserMealCaloricty[4] + "') AND ([Ograniczenia] LIKE ('" + resultRestricitons + "'))) ORDER BY NEWID() ; "; using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal1.MealId = Int32.Parse(read["Id"].ToString()); meal1.Name = (read["Nazwa"]).ToString(); meal1.Daytime = (read["PoraDnia"]).ToString(); meal1.Caloricity = float.Parse((read["KCAL"]).ToString()); meal1.Description = (read["Opis"]).ToString(); meal1.Ingredients = (read["Skladniki"]).ToString(); meal1.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } cmd = new SqlCommand(sql2, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal2.MealId = Int32.Parse(read["Id"].ToString()); meal2.Name = (read["Nazwa"]).ToString(); meal2.Daytime = (read["PoraDnia"]).ToString(); meal2.Caloricity = float.Parse((read["KCAL"]).ToString()); meal2.Description = (read["Opis"]).ToString(); meal2.Ingredients = (read["Skladniki"]).ToString(); meal2.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } cmd = new SqlCommand(sql3, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal3.MealId = Int32.Parse(read["Id"].ToString()); meal3.Name = (read["Nazwa"]).ToString(); meal3.Daytime = (read["PoraDnia"]).ToString(); meal3.Caloricity = float.Parse((read["KCAL"]).ToString()); meal3.Description = (read["Opis"]).ToString(); meal3.Ingredients = (read["Skladniki"]).ToString(); meal3.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } cmd = new SqlCommand(sql4, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal4.MealId = Int32.Parse(read["Id"].ToString()); meal4.Name = (read["Nazwa"]).ToString(); meal4.Daytime = (read["PoraDnia"]).ToString(); meal4.Caloricity = float.Parse((read["KCAL"]).ToString()); meal4.Description = (read["Opis"]).ToString(); meal4.Ingredients = (read["Skladniki"]).ToString(); meal4.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } cmd = new SqlCommand(sql5, sqlConnection); using (SqlDataReader read = cmd.ExecuteReader()) { if (read.Read()) { meal5.MealId = Int32.Parse(read["Id"].ToString()); meal5.Name = (read["Nazwa"]).ToString(); meal5.Daytime = (read["PoraDnia"]).ToString(); meal5.Caloricity = float.Parse((read["KCAL"]).ToString()); meal5.Description = (read["Opis"]).ToString(); meal5.Ingredients = (read["Skladniki"]).ToString(); meal5.FoodRestrictions11 = (read["Ograniczenia"]).ToString(); } } foodPlan = new FoodPlan(user, meal1, meal2, meal3, meal4, meal5); } return(foodPlan); }