public List <string> GetCategories()
 {
     try
     {
         StaticDB.Connect();
         List <string> categories = new List <string>();
         string        com        = "SELECT * FROM EP_Kategorien";
         StaticDB.Connection.Open();
         SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection);
         var        r          = sqlCommand.ExecuteReader();
         while (r.Read())
         {
             categories.Add(r.GetString(0));
         }
         StaticDB.Connection.Close();
         return(categories);
     }
     catch (Exception ex)
     {
         _ = ex.Message;
         if (StaticDB.Connection != null)
         {
             if (StaticDB.Connection.State != ConnectionState.Closed)
             {
                 StaticDB.Connection.Close();
             }
         }
         return(null);
     }
 }
        public bool AddBewertung(Bewertung bewertung, Ernährungsplan ernährungsplan)
        {
            try
            {
                StaticDB.Connect();
                string insertBew = $"INSERT INTO EP_Bewertung ([User], Bewertung) VALUES ('{bewertung.Bewerter.Nutzername}', '{bewertung.Rating}');" +
                                   "SELECT CAST(SCOPE_IDENTITY() AS INT)";
                StaticDB.Connection.Open();
                SqlCommand command = new SqlCommand(insertBew, StaticDB.Connection);
                int        lastID  = (int)command.ExecuteScalar();
                StaticDB.Connection.Close();
                string insertLink = $"INSERT INTO EP_Link_BaseBewertung (ID_EP_Base, ID_EP_Bewertung) VALUES ({ernährungsplan.ID}, {lastID})";
                StaticDB.RunSQL(insertLink);

                ernährungsplan.Bewertungen.Add(bewertung);
                return(true);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(false);
            }
        }
 public bool Delete(Ernährungsplan ernährungsplan)
 {
     try
     {
         foreach (var item in ernährungsplan.Bewertungen)
         {
             string command = $"DELETE FROM EP_Bewertung WHERE ID={item.ID}";
             StaticDB.RunSQL(command);
         }
         string com = $"DELETE FROM EP_Base WHERE ID={ernährungsplan.ID}";
         StaticDB.RunSQL(com);
         return(true);
     }
     catch (Exception ex)
     {
         _ = ex.Message;
         if (StaticDB.Connection != null)
         {
             if (StaticDB.Connection.State != ConnectionState.Closed)
             {
                 StaticDB.Connection.Close();
             }
         }
         return(false);
     }
 }
Exemple #4
0
        internal bool ChangePW(string nutzername, string hpw)
        {
            string com = $"UPDATE User_Password " +
                         $"SET Password = '******' " +
                         $"WHERE Nutzername = '{nutzername}';";

            return(StaticDB.RunSQL(com));
        }
Exemple #5
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public Trainingsplan GetByID(int ID)
        {
            try
            {
                StaticDB.Connect();
                Trainingsplan trainingsplan = new Trainingsplan();
                string        com           = "SELECT TP_Base.ID, TP_Base.Titel, TP_Info.ErstelltAM, TP_Info.ErstelltVon, TP_Info.GeaendertAm, TP_Info.Kategorie " +
                                              "FROM TP_Base " +
                                              "INNER JOIN TP_Info " +
                                              "ON TP_Base.ID = TP_Info.ID " +
                                              $"WHERE TP_Info.ID = '{ID}'";

                StaticDB.Connection.Open();
                SqlCommand  sqlCommand = new SqlCommand(com, StaticDB.Connection);
                IDataReader r          = sqlCommand.ExecuteReader();
                while (r.Read())
                {
                    trainingsplan = new Trainingsplan()
                    {
                        ID         = r.GetInt32(0),
                        Titel      = r.GetString(1),
                        ErstelltAm = r.GetDateTime(2),
                        Ersteller  = new User()
                        {
                            Nutzername = r.GetString(3)
                        },
                        Kategorie = r.GetString(5),
                    };
                    if (!r.IsDBNull(4))
                    {
                        trainingsplan.GeAendertAm = r.GetDateTime(4);
                    }
                }

                StaticDB.Connection.Close();

                trainingsplan.Ersteller      = AllVM.Datenbank.User.GetByName(trainingsplan.Ersteller.Nutzername);
                trainingsplan.UebungList     = AllVM.Datenbank.Trainingsplan.GetUebungen(trainingsplan.ID);
                trainingsplan.Bewertungen    = AllVM.Datenbank.Trainingsplan.GetBewertungen(trainingsplan.ID);
                trainingsplan.DurchBewertung = AllVM.Datenbank.Trainingsplan.GetAvgBewertung(trainingsplan.ID);
                return(trainingsplan);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(null);
            }
        }
        internal Ernährungsplan GetByID(int iD)
        {
            try
            {
                StaticDB.Connect();
                var    ePlan = new Ernährungsplan();
                string com   = "SELECT base.ID, base.Titel , info.ErstelltVon, info.ErstelltAm, info.GeaendertAm, info.Kategorie " +
                               "FROM EP_Base as base " +
                               "INNER JOIN EP_Info as info " +
                               "ON base.ID = info.ID " +
                               $"WHERE base.ID = '{iD}'";
                StaticDB.Connection.Open();
                SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection);
                var        r          = sqlCommand.ExecuteReader();
                while (r.Read())
                {
                    ePlan = new Ernährungsplan()
                    {
                        ID        = r.GetInt32(0),
                        Titel     = r.GetString(1),
                        Ersteller = new User()
                        {
                            Nutzername = r.GetString(2)
                        },
                        ErstelltAm = r.GetDateTime(3),
                        Kategorie  = r.GetString(5)
                    };

                    if (!r.IsDBNull(4))
                    {
                        ePlan.GeAendertAm = r.GetDateTime(4);
                    }
                }
                StaticDB.Connection.Close();

                ePlan.Ersteller      = AllVM.Datenbank.User.GetByName(ePlan.Ersteller.Nutzername);
                ePlan.Bewertungen    = AllVM.Datenbank.Ernährungsplan.GetBewertungen(ePlan.ID);
                ePlan.MahlzeitenList = AllVM.Datenbank.Ernährungsplan.GetMahlzeiten(ePlan.ID);
                ePlan.DurchBewertung = AllVM.Datenbank.Ernährungsplan.GetAvgBewertung(ePlan.ID);

                return(ePlan);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(null);
            }
        }
Exemple #7
0
        /// <summary>
        /// Löschen des Benutzers
        /// </summary>
        /// <param name="user">Benutzer</param>
        /// <returns></returns>
        internal bool Delete(User user)
        {
            try
            {
                string com = $"DELETE FROM User_Info WHERE Nutzername = '{user.Nutzername}';" +
                             $"DELETE FROM User_Bild WHERE Nutzername = '{user.Nutzername}';" +
                             $"DELETE FROM User_Favo WHERE Nutzername = '{user.Nutzername}';" +
                             $"DELETE FROM User_Password WHERE Nutzername = '{user.Nutzername}';" +
                             $"DELETE FROM User_Follows WHERE User_ID = '{user.Nutzername}' OR Follow_ID = '{user.Nutzername}';" +
                             $"DELETE FROM User_Base WHERE Nutzername = '{user.Nutzername}';";

                bool result = StaticDB.RunSQL(com);
                if (result == false)
                {
                    return(result);
                }


                List <News>           news  = AllVM.Datenbank.Feed.GetByUser(user);
                List <Trainingsplan>  tlist = AllVM.Datenbank.Trainingsplan.GetList(user.Nutzername);
                List <Ernährungsplan> elist = AllVM.Datenbank.Ernährungsplan.GetList(user.Nutzername);

                foreach (var item in news)
                {
                    AllVM.Datenbank.Feed.Delete(item);
                }

                foreach (var item in tlist)
                {
                    item.Ersteller = new User()
                    {
                        Nutzername = "fitness_bot"
                    };
                    item.GeAendertAm = DateTime.Now;
                    AllVM.Datenbank.Trainingsplan.Edit(item);
                }

                foreach (var item in elist)
                {
                    item.Ersteller = new User()
                    {
                        Nutzername = "fitness_bot"
                    };
                    item.GeAendertAm = DateTime.Now;
                    AllVM.Datenbank.Ernährungsplan.Edit(item);
                }
                return(true);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                return(false);
            }
        }
Exemple #8
0
        /// <summary>
        /// Zusätzliche Informationen zu einem Benutzer aufgrund eines Nutzernames erhalten
        /// </summary>
        /// <param name="nutzername">Nutzername des zu suchenden Benutzers</param>
        /// <returns></returns>
        public User GetByName(string nutzername)
        {
            try
            {
                User user = null;
                StaticDB.Connect();

                string com = "SELECT base.Nutzername, info.ErstelltAm, info.Infotext, info.OnlyCustomName, info.CustomName, bild.Bild " +
                             "FROM User_Base AS base " +
                             "INNER JOIN User_Info AS info " +
                             "ON info.Nutzername = base.Nutzername " +
                             "LEFT JOIN User_Bild AS bild " +
                             "ON bild.Nutzername = base.Nutzername " +
                             $"WHERE base.Nutzername = '{nutzername}';";
                SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                StaticDB.Connection.Open();
                var r = command.ExecuteReader();

                while (r.Read())
                {
                    user = new User()
                    {
                        Nutzername     = r.GetString(0),
                        ErstelltAm     = r.GetDateTime(1),
                        InfoText       = r.GetString(2),
                        OnlyCustomName = StaticDB.ConvertByteToBool(r.GetByte(3)),
                        CustomName     = r.GetString(4)
                    };

                    if (!r.IsDBNull(5))
                    {
                        user.ProfilBild = (byte[])r[5];
                    }
                }

                StaticDB.Connection.Close();
                user.AnzahlFollower = GetAnzahlFollower(user.Nutzername);

                return(user);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(null);
            }
        }
Exemple #9
0
        // in arbeit
        /// <summary>
        /// Ändert einen Trainingsplan in der Datenbank
        /// </summary>
        /// <param name="trainingsplan">Nimmt den bearbeiteten Trainingsplan entgegen (Typ Trainingsplan)</param>
        /// <returns>Gibt bei erfolgreichem Ausführen true zurück und bei einem Fehler false</returns>
        public bool Edit(Trainingsplan trainingsplan)
        {
            try
            {
                StaticDB.Connect();
                string editTP_Base = $"UPDATE TP_Base SET Titel = '{trainingsplan.Titel}' WHERE ID={trainingsplan.ID}";
                StaticDB.RunSQL(editTP_Base);
                string delLink = $"DELETE FROM TP_Link_BaseUebung WHERE ID_Base={trainingsplan.ID}";
                StaticDB.RunSQL(delLink);
                foreach (var item in trainingsplan.UebungList)
                {
                    string com = $"SELECT * FROM TP_Uebungen WHERE Name='{item.Name}' AND Gewicht='{item.Menge.ToString().Replace(",", ".")}' AND Repetition={item.Wiederholungen} AND Sets={item.Sätze}";
                    if (StaticDB.CheckExistenz(com) == true)
                    {
                        SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection);
                        StaticDB.Connection.Open();
                        int ID = (int)sqlCommand.ExecuteScalar();
                        StaticDB.Connection.Close();

                        com = $"INSERT INTO TP_Link_BaseUebung (ID_Base, ID_Uebung) VALUES('{trainingsplan.ID}', '{ID}')";
                        StaticDB.RunSQL(com);
                    }
                    else
                    {
                        com = $"INSERT INTO TP_Uebungen (Name, Gewicht, Repetition, Sets) VALUES ('{item.Name}', '{item.Menge.ToString().Replace(",", ".")}', '{item.Wiederholungen}', '{item.Sätze}'); " +
                              "SELECT CAST(SCOPE_IDENTITY() AS INT)";
                        StaticDB.Connection.Open();
                        SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                        int        ID      = (int)command.ExecuteScalar();
                        StaticDB.Connection.Close();
                        com = $"INSERT INTO TP_Link_BaseUebung (ID_Base, ID_Uebung) VALUES('{trainingsplan.ID}', '{ID}')";
                        StaticDB.RunSQL(com);
                    }
                }
                string editTP_Info = $"UPDATE TP_Info SET GeaendertAm='{DateTime.Now}' WHERE ID={trainingsplan.ID}";
                StaticDB.RunSQL(editTP_Info);

                return(true);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(false);
            }
        }
        public bool Edit(Ernährungsplan ernährungsplan)
        {
            try
            {
                StaticDB.Connect();
                string editEP_Base = $"UPDATE EP_Base SET Titel='{ernährungsplan.Titel}' WHERE ID={ernährungsplan.ID}";
                StaticDB.RunSQL(editEP_Base);
                string delLink = $"DELETE FROM EP_Link_BaseMahlzeiten WHERE ID_Base={ernährungsplan.ID}";
                StaticDB.RunSQL(delLink);
                foreach (var item in ernährungsplan.MahlzeitenList)
                {
                    string com = $"SELECT * FROM EP_Mahlzeiten WHERE Nahrungsmittel='{item.Nahrungsmittel}' AND Menge='{item.Menge.ToString().Replace(",", ".")}' AND Einheit='{item.Einheit}'";
                    if (StaticDB.CheckExistenz(com) == true)
                    {
                        SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection);
                        StaticDB.Connection.Open();
                        int ID = (int)sqlCommand.ExecuteScalar();
                        StaticDB.Connection.Close();

                        com = $"INSERT INTO EP_Link_BaseMahlzeiten (ID_Base, ID_Mahlzeit) VALUES('{ernährungsplan.ID}', '{ID}')";
                        StaticDB.RunSQL(com);
                    }
                    else
                    {
                        com = $"INSERT INTO EP_Mahlzeiten (Nahrungsmittel, Menge, Einheit) VALUES ('{item.Nahrungsmittel}', '{item.Menge.ToString().Replace(",", ".")}', '{item.Einheit}');" +
                              $"SELECT CAST(SCOPE_IDENTITY() AS INT)";
                        StaticDB.Connection.Open();
                        SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection);
                        int        ID         = (int)sqlCommand.ExecuteScalar();
                        StaticDB.Connection.Close();
                        com = $"INSERT INTO EP_Link_BaseMahlzeiten (ID_Base, ID_Mahlzeit) VALUES('{ernährungsplan.ID}', '{ID}')";
                        StaticDB.RunSQL(com);
                    }
                }
                string editEP_Info = $"UPDATE EP_Info SET GeaendertAm='{DateTime.Now}' WHERE ID={ernährungsplan.ID}";
                StaticDB.RunSQL(editEP_Info);

                return(true);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(false);
            }
        }
Exemple #11
0
        /// <summary>
        /// Lädt eine Liste der Bewertungen zu einem Trainingsplan aus der Datenbank.
        /// </summary>
        /// <param name="ID">Nimmt die ID eines Trainingsplans entgegen (Integer)</param>
        /// <returns>Gibt eine Liste von Bewertungen zurück</returns>
        public List <Bewertung> GetBewertungen(int ID)
        {
            try
            {
                StaticDB.Connect();
                List <Bewertung> bewertungsList = new List <Bewertung>();
                string           com            = "SELECT bew.ID, bew.[User], Bewertung " +
                                                  "FROM TP_Base as base " +
                                                  "INNER JOIN TP_Link_BaseBewertung as link " +
                                                  "ON base.ID = link.ID_TP_Base " +
                                                  "INNER JOIN TP_Bewertung as bew " +
                                                  "ON bew.ID = link.ID_TP_Bewertung " +
                                                  $"WHERE base.ID = {ID}";

                StaticDB.Connection.Open();
                SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection);

                var r = sqlCommand.ExecuteReader();
                while (r.Read())
                {
                    Bewertung bewertung = new Bewertung()
                    {
                        ID       = r.GetInt32(0),
                        Bewerter = new User()
                        {
                            Nutzername = r.GetString(1)
                        },
                        Rating = r.GetInt32(2)
                    };
                    bewertungsList.Add(bewertung);
                }
                StaticDB.Connection.Close();
                foreach (var item in bewertungsList)
                {
                    item.Bewerter = AllVM.Datenbank.User.GetByName(item.Bewerter.Nutzername);
                }
                return(bewertungsList);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(null);
            }
        }
Exemple #12
0
        /// <summary>
        /// Likes und Nutzernamen zu einem Beitrag erhalten
        /// </summary>
        /// <param name="id">ID des Beitrages</param>
        /// <returns></returns>
        public List <Like> GetLikesWithNames(string id)
        {
            try
            {
                List <User> subs = AllVM.Datenbank.User.GetSubs(AllVM.User.Nutzername);
                List <Like> list = new List <Like>();
                StaticDB.Connect();

                string com = "SELECT [User] " +
                             "FROM Feed_Likes " +
                             $"WHERE Feed_ID = '{id}'";
                SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                StaticDB.Connection.Open();
                var r = command.ExecuteReader();

                while (r.Read())
                {
                    Like like = new Like()
                    {
                        User = new User()
                        {
                            Nutzername = r.GetString(0)
                        }
                    };
                    list.Add(like);
                }

                StaticDB.Connection.Close();

                foreach (var item in list)
                {
                    item.User  = AllVM.Datenbank.User.GetByName(item.User.Nutzername);
                    item.IsSub = subs.Exists(s => s.Nutzername == item.User.Nutzername);
                }

                return(list);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(null);
            }
        }
Exemple #13
0
        internal bool CheckIfFavo(string key, User user)
        {
            string[] keys = key.Split(';');
            string   com  = $"SELECT * FROM User_Favo WHERE Nutzername = '{user.Nutzername}' AND Typ = '{keys[0]}' AND Plan_ID = '{keys[1]}'";
            bool?    x    = StaticDB.CheckExistenz(com);

            if (x == true)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Exemple #14
0
        /// <summary>
        /// Lädt die zu einem Trainingsplan zugehörigen Übungen aus der Datenbank.
        /// </summary>
        /// <param name="ID">ID des Trainingsplans (Integer)</param>
        /// <returns>Liste der Übungen</returns>
        public List <Uebung> GetUebungen(int ID)
        {
            try
            {
                StaticDB.Connect();
                List <Uebung> uebungen   = new List <Uebung>();
                string        sqlCommand = "SELECT ueb.ID, ueb.Name, ueb.Gewicht, ueb.Repetition, ueb.Sets, ueb.Einheit " +
                                           "FROM TP_Base as base " +
                                           "INNER JOIN TP_Link_BaseUebung as link " +
                                           "ON base.ID = link.ID_Base " +
                                           "INNER JOIN TP_Uebungen as ueb " +
                                           "ON ueb.ID = link.ID_Uebung " +
                                           $"WHERE base.ID = {ID}";
                StaticDB.Connection.Open();
                SqlCommand  command = new SqlCommand(sqlCommand, StaticDB.Connection);
                IDataReader r       = command.ExecuteReader();
                while (r.Read())
                {
                    Uebung uebung = new Uebung()
                    {
                        ID             = r.GetInt32(0),
                        Name           = r.GetString(1),
                        Menge          = r.GetDecimal(2),
                        Wiederholungen = r.GetInt32(3),
                        Sätze          = r.GetInt32(4),
                        Einheit        = r.GetString(5)
                    };
                    uebungen.Add(uebung);
                }

                StaticDB.Connection.Close();
                return(uebungen);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(null);
            }
        }
Exemple #15
0
        /// <summary>
        /// Liste der Follower zu einem Benutzer ausgeben
        /// </summary>
        /// <param name="profil">Benutzername</param>
        /// <returns></returns>
        internal List <Follower> GetFollows(string profil)
        {
            try
            {
                List <Follower> list = new List <Follower>();
                StaticDB.Connect();

                string com = "SELECT Follow_ID, GefolgtAm " +
                             "FROM User_Follows " +
                             $"WHERE User_ID = '{profil}';";
                SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                StaticDB.Connection.Open();
                var r = command.ExecuteReader();

                int counter = 1;
                while (r.Read())
                {
                    Follower follower = new Follower()
                    {
                        Index = counter,
                        User  = new User()
                        {
                            Nutzername = r.GetString(0)
                        },
                        GefolgtAm = r.GetDateTime(1)
                    };
                    list.Add(follower);
                    counter += 1;
                }

                StaticDB.Connection.Close();
                return(list);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(null);
            }
        }
Exemple #16
0
        /// <summary>
        /// Profilbild hochladen
        /// </summary>
        /// <param name="user">Benutzer</param>
        /// <param name="bild">Profilbild</param>
        /// <returns></returns>
        internal bool UploadProfilBild(User user, byte[] bild)
        {
            try
            {
                StaticDB.Connect();
                string com      = $"SELECT * FROM User_Bild WHERE Nutzername = '{user.Nutzername}'";
                bool?  existenz = StaticDB.CheckExistenz(com);

                if (existenz == null)
                {
                    return(false);
                }
                else
                {
                    if (existenz == true)
                    {
                        com = $"UPDATE User_Bild SET Bild = @bildBytes WHERE Nutzername = '{user.Nutzername}'";
                    }
                    else if (existenz == false)
                    {
                        com = $"INSERT INTO User_Bild VALUES('{user.Nutzername}', @bildBytes)";
                    }

                    SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                    StaticDB.Connection.Open();
                    command.Parameters.Add("@bildBytes", System.Data.SqlDbType.VarBinary).Value = bild;
                    command.ExecuteNonQuery();
                    StaticDB.Connection.Close();
                    return(true);
                }
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(false);
            }
        }
 public List <Mahlzeiten> GetMahlzeiten(int ID)
 {
     try
     {
         StaticDB.Connect();
         List <Mahlzeiten> mahlzeitenList = new List <Mahlzeiten>();
         string            com            = "select mahl.ID, mahl.Nahrungsmittel, mahl.Menge, mahl.Einheit " +
                                            "FROM EP_Base as base " +
                                            "INNER JOIN EP_Link_BaseMahlzeiten as link " +
                                            "ON base.ID = link.ID_Base " +
                                            "INNER JOIN EP_Mahlzeiten as mahl " +
                                            "ON link.ID_Mahlzeit = mahl.ID " +
                                            $"WHERE base.ID = {ID}";
         StaticDB.Connection.Open();
         SqlCommand sqlcommand = new SqlCommand(com, StaticDB.Connection);
         var        r          = sqlcommand.ExecuteReader();
         while (r.Read())
         {
             Mahlzeiten mahlzeit = new Mahlzeiten()
             {
                 ID             = r.GetInt32(0),
                 Nahrungsmittel = r.GetString(1),
                 Menge          = r.GetDecimal(2),
                 Einheit        = r.GetString(3)
             };
             mahlzeitenList.Add(mahlzeit);
         }
         StaticDB.Connection.Close();
         return(mahlzeitenList);
     }
     catch (Exception ex)
     {
         _ = ex.Message;
         if (StaticDB.Connection != null)
         {
             if (StaticDB.Connection.State != ConnectionState.Closed)
             {
                 StaticDB.Connection.Close();
             }
         }
         return(null);
     }
 }
Exemple #18
0
        /// <summary>
        /// Aktualisieren der neuen Werte für einen Benutzer
        /// </summary>
        /// <param name="user">zu aktualisierender Benutzer</param>
        /// <returns></returns>
        internal bool Update(User user)
        {
            string com = $"UPDATE User_Info SET " +
                         $"GeaendertAm = '{DateTime.Now:yyyy-MM-dd HH:mm:ss}', " +
                         $"Infotext = '{user.InfoText}'," +
                         $"OnlyCustomName = '{StaticDB.ConvertBoolToByte(user.OnlyCustomName)}'," +
                         $"CustomName = '{user.CustomName}' " +
                         $"WHERE Nutzername = '{user.Nutzername}';";

            bool result = StaticDB.RunSQL(com);

            if (result == false)
            {
                return(result);
            }

            try
            {
                com = $"UPDATE User_Bild SET " +
                      $"Bild = @bild " +
                      $"WHERE Nutzername = '{user.Nutzername}';";

                StaticDB.Connect();
                SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                StaticDB.Connection.Open();
                command.Parameters.Add("@bild", System.Data.SqlDbType.VarBinary).Value = user.ProfilBild;
                command.ExecuteNonQuery();
                StaticDB.Connection.Close();
                return(true);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(false);
            }
        }
Exemple #19
0
        /// <summary>
        /// Einfügen eines Status
        /// </summary>
        /// <param name="status">Der einzufügende Status</param>
        /// <returns></returns>
        public bool Insert(Status status)
        {
            string com    = $"INSERT INTO Feed_Base VALUES('{status.Beschreibung}');";
            bool   result = StaticDB.RunSQL(com);

            if (result == false)
            {
                return(false);
            }

            int id = StaticDB.GetID($"SELECT ID FROM Feed_Base WHERE Beschreibung = '{status.Beschreibung}'");

            com    = $"INSERT INTO Feed_Info VALUES('{id}', '{status.ErstelltAm:yyyy-MM-dd HH:mm:ss}', '{status.ErstelltVon.Nutzername}')";
            result = StaticDB.RunSQL(com);
            if (result == false)
            {
                return(false);
            }


            if (status.Foto != null)
            {
                com = $"INSERT INTO Feed_Fotos VALUES('{id}', @bild);";

                StaticDB.Connect();
                SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                command.Parameters.Add("@bild", System.Data.SqlDbType.VarBinary).Value = status.Foto;
                StaticDB.Connection.Open();
                int count = command.ExecuteNonQuery();
                StaticDB.Connection.Close();

                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            return(true);
        }
Exemple #20
0
        internal List <User> GetSubs(string nutzername)
        {
            try
            {
                List <User> list = new List <User>();
                StaticDB.Connect();

                string com = "SELECT User_ID " +
                             "FROM User_Follows " +
                             $"WHERE Follow_ID = '{nutzername}';";
                SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                StaticDB.Connection.Open();
                var r = command.ExecuteReader();

                int counter = 1;
                while (r.Read())
                {
                    User user = new User()
                    {
                        Nutzername = r.GetString(0)
                    };
                    list.Add(user);
                    counter += 1;
                }

                StaticDB.Connection.Close();
                return(list);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(null);
            }
        }
Exemple #21
0
        /// <summary>
        /// Alle Benutzer als Liste ausgeben
        /// </summary>
        /// <returns></returns>
        internal List <User> GetList()
        {
            try
            {
                List <User> list = new List <User>();
                StaticDB.Connect();

                string com = "SELECT base.Nutzername, info.CustomName " +
                             "FROM User_Base AS base " +
                             "INNER JOIN User_Info AS info " +
                             "ON info.Nutzername =  base.Nutzername;";
                SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                StaticDB.Connection.Open();
                var r = command.ExecuteReader();

                while (r.Read())
                {
                    User user = new User()
                    {
                        Nutzername = r.GetString(0),
                        CustomName = r.GetString(1)
                    };
                    list.Add(user);
                }

                StaticDB.Connection.Close();
                return(list);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(null);
            }
        }
Exemple #22
0
 /// <summary>
 /// Löscht eine Bewertung aus der Datenbank.
 /// </summary>
 /// <param name="bewertung">Zu löschende Bewertung (Typ BewertungTrainingsplan)</param>
 /// <returns>Gibt bei erfolgreichem Ausführen true zurück und bei einem Fehler false</returns>
 public bool DeleteBewertung(Bewertung bewertung)
 {
     try
     {
         string delBew = $"DELETE FROM TP_Bewertung WHERE ID={bewertung.ID}";
         StaticDB.RunSQL(delBew);
         return(true);
     }
     catch (Exception ex)
     {
         _ = ex.Message;
         if (StaticDB.Connection != null)
         {
             if (StaticDB.Connection.State != ConnectionState.Closed)
             {
                 StaticDB.Connection.Close();
             }
         }
         return(false);
     }
 }
Exemple #23
0
        /// <summary>
        /// Aktuellen Like-Anzahl zu einem Beitrag ausgeben
        /// </summary>
        /// <param name="id">ID des Beitrages</param>
        /// <returns></returns>
        public int GetLikes(int id)
        {
            try
            {
                int count = 0;
                StaticDB.Connect();

                string com = "SELECT COUNT(*)" +
                             "FROM Feed_Likes " +
                             $"WHERE Feed_ID = '{id}'";

                SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                StaticDB.Connection.Open();
                object r = command.ExecuteScalar();

                if (r != null)
                {
                    count = int.Parse(r.ToString());
                }
                else
                {
                    count = -1;
                }

                StaticDB.Connection.Close();
                return(count);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(-1);
            }
        }
Exemple #24
0
        public decimal GetAvgBewertung(int ID)
        {
            try
            {
                StaticDB.Connect();
                string durcchBew = "SELECT CONVERT(decimal(3,2),AVG(bew.Bewertung)) " +
                                   "FROM TP_Base as base " +
                                   "INNER JOIN TP_Link_BaseBewertung as link " +
                                   "ON base.ID = link.ID_TP_Base " +
                                   "INNER JOIN TP_Bewertung as bew " +
                                   "ON bew.ID = link.ID_TP_Bewertung " +
                                   $"WHERE base.ID = {ID}";
                SqlCommand command = new SqlCommand(durcchBew, StaticDB.Connection);
                StaticDB.Connection.Open();
                var x = command.ExecuteScalar();
                StaticDB.Connection.Close();

                if (x != null && x.GetType() != typeof(System.DBNull))
                {
                    return(decimal.Parse(x.ToString()));
                }
                else
                {
                    return(-1);
                }
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(-2);
            }
        }
Exemple #25
0
        /// <summary>
        /// Favorisierte Pläne (Training und Ernährung) aus der Datenbank ermitteln
        /// </summary>
        /// <returns></returns>
        internal List <FavoPlan> GetFavoPlans()
        {
            try
            {
                List <FavoPlan> list = new List <FavoPlan>();
                StaticDB.Connect();

                string com = $"SELECT Plan_ID, Typ FROM User_Favo WHERE Nutzername = '{AllVM.User.Nutzername}'";

                SqlCommand command = new SqlCommand(com, StaticDB.Connection);
                StaticDB.Connection.Open();
                var r = command.ExecuteReader();

                while (r.Read())
                {
                    FavoPlan favo = new FavoPlan()
                    {
                        ID  = r.GetInt32(0),
                        Typ = r.GetString(1)
                    };
                    list.Add(favo);
                }

                StaticDB.Connection.Close();
                return(list);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(null);
            }
        }
Exemple #26
0
        /// <summary>
        /// Einen Beitrag liken
        /// </summary>
        /// <param name="id">ID des Beitrages</param>
        /// <param name="user">Benutzer, der den Beitrag geliked hat</param>
        /// <returns></returns>
        public bool?Like(string id, User user)
        {
            bool   isdone = false;
            string com    = $"SELECT Feed_ID FROM Feed_Likes WHERE Feed_ID = '{id}' AND [User] = '{user.Nutzername}'";
            int    result = StaticDB.GetID(com);

            if (result == -1)
            {
                com    = $"INSERT INTO Feed_Likes VALUES('{id}', '{user.Nutzername}')";
                isdone = StaticDB.RunSQL(com);
                if (isdone == true)
                {
                    return(true);
                }
                else
                {
                    return(null);
                }
            }
            else if (result > 0)
            {
                com    = $"DELETE FROM Feed_Likes WHERE Feed_ID = '{id}' AND [User] = '{user.Nutzername}'";
                isdone = StaticDB.RunSQL(com);
                if (isdone == true)
                {
                    return(false);
                }
                else
                {
                    return(null);
                }
            }
            else
            {
                return(null);
            }
        }
Exemple #27
0
        internal bool?Exists(string user)
        {
            string com = $"SELECT Nutzername FROM User_Base WHERE Nutzername = '{user}'";

            return(StaticDB.CheckExistenz(com));
        }
Exemple #28
0
        /// <summary>
        /// Entfolgen
        /// </summary>
        /// <param name="profil">Benutzer, dem zu entfolgen ist</param>
        /// <param name="follower">Benutzer, der entfolgen möchte</param>
        /// <returns></returns>
        internal bool UnFollow(User profil, User follower)
        {
            string com = $"DELETE FROM User_Follows WHERE User_ID = '{profil.Nutzername}' AND Follow_ID = '{follower.Nutzername}'";

            return(StaticDB.RunSQL(com));
        }
Exemple #29
0
        /// <summary>
        /// Benutzer verfolgen
        /// </summary>
        /// <param name="profil">Benutzer, dem zu folgen ist</param>
        /// <param name="follower">Benutzer, der folgen möchte</param>
        /// <returns></returns>
        internal bool Follow(User profil, User follower)
        {
            string com = $"INSERT INTO User_Follows VALUES ('{profil.Nutzername}', '{follower.Nutzername}', '{DateTime.Now:yyyy-MM-dd HH:mm:ss}')";

            return(StaticDB.RunSQL(com));
        }
Exemple #30
0
        /// <summary>
        /// Fügt einen Trainingsplan zu der Datenbank hinzu.
        /// </summary>
        /// <param name="trainingsplan">Objekt vom Typ Trainingsplan</param>
        /// <returns>Gibt bei erfolgreichem Ausführen true zurück und bei einem Fehler false</returns>
        public bool AddTrainingsplan(Trainingsplan trainingsplan)
        {
            try
            {
                StaticDB.Connect();
                string com = $"INSERT INTO TP_Base (Titel) values ('{trainingsplan.Titel}'); " +
                             "SELECT CAST(SCOPE_IDENTITY() AS INT)";

                SqlCommand command = new SqlCommand(com, StaticDB.Connection);

                StaticDB.Connection.Open();

                int lastID = (int)command.ExecuteScalar();
                StaticDB.Connection.Close();

                if (trainingsplan.GeAendertAm != default)
                {
                    com = $"INSERT INTO TP_Info (ID, ErstelltAm, ErstelltVon, GeaendertAm, Kategorie) VALUES ({lastID}, '{trainingsplan.ErstelltAm:yyyy-dd-MM HH:mm:ss}', '{trainingsplan.Ersteller.Nutzername}', '{trainingsplan.GeAendertAm:yyyy-dd-MM HH:mm:ss}', '{trainingsplan.Kategorie}');";
                }
                else
                {
                    com = $"INSERT INTO TP_Info (ID, ErstelltAm, ErstelltVon, Kategorie) VALUES ({lastID}, '{trainingsplan.ErstelltAm:yyyy-dd-MM HH:mm:ss}', '{trainingsplan.Ersteller.Nutzername}', '{trainingsplan.Kategorie}');";
                }

                bool result = StaticDB.RunSQL(com);
                if (result == false)
                {
                    com = $"DELETE FROM TP_Base WHERE ID = '{lastID}'";
                    StaticDB.RunSQL(com);
                    return(false);
                }

                foreach (var uebung in trainingsplan.UebungList)
                {
                    string checkEx = $"SELECT * FROM TP_Uebungen WHERE Name='{uebung.Name}' AND Gewicht={uebung.Menge.ToString().Replace(",", ".")} AND Repetition={uebung.Wiederholungen} AND Sets={uebung.Sätze}";
                    if (StaticDB.CheckExistenz(checkEx) == true)
                    {
                        int    uebID     = StaticDB.GetID(checkEx);
                        string comTpLink = $"INSERT INTO TP_Link_BaseUebung (ID_Base, ID_Uebung) VALUES({lastID}, {uebID})";
                        StaticDB.RunSQL(comTpLink);
                    }
                    else
                    {
                        com = $"INSERT INTO TP_Uebungen (Name, Gewicht, Repetition, Sets, Einheit) VALUES ('{uebung.Name}', {uebung.Menge.ToString().Replace(",", ".")}, {uebung.Wiederholungen}, {uebung.Sätze}, '{uebung.Einheit}'); " +
                              "SELECT CAST(SCOPE_IDENTITY() AS INT)";
                        SqlCommand insertUeb = new SqlCommand(com, StaticDB.Connection);
                        StaticDB.Connection.Open();
                        int lastUebID = (int)insertUeb.ExecuteScalar();
                        StaticDB.Connection.Close();
                        string comTpLink = $"INSERT INTO TP_Link_BaseUebung (ID_Base, ID_Uebung) VALUES({lastID}, {lastUebID})";
                        StaticDB.RunSQL(comTpLink);
                    }
                }
                StaticDB.Connection.Close();
                return(true);
            }
            catch (Exception ex)
            {
                _ = ex.Message;
                if (StaticDB.Connection != null)
                {
                    if (StaticDB.Connection.State != ConnectionState.Closed)
                    {
                        StaticDB.Connection.Close();
                    }
                }
                return(false);
            }
        }