/// <summary>
/// Fügt eine Bewertung zu einem Trainingsplan in der Datenbank hinzu
/// </summary>
/// <param name="bewertung">Hinzuzufügende Bewertung (Typ BewertungTrainingsplan)</param>
/// <param name="trainingsplan">Zur Bewertung zugehöriger Trainingsplan (Typ Trainingsplan)</param>
/// <returns>Gibt bei erfolgreichem Ausführen true zurück und bei einem Fehler false</returns>
public bool AddBewertung(Bewertung bewertung, Trainingsplan trainingsplan)
{
try
{
StaticDB.Connect();
string insertBew = $"INSERT INTO TP_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 TP_Link_BaseBewertung (ID_TP_Base, ID_TP_Bewertung) VALUES ({trainingsplan.ID}, {lastID})";
StaticDB.RunSQL(insertLink);
trainingsplan.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 List <string> GetCategories()
{
try
{
StaticDB.Connect();
List <string> categories = new List <string>();
string com = "SELECT * FROM TP_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);
}
}
/// <summary>
/// Anzahl der Follower ermitteln
/// </summary>
/// <param name="nutzername">Zu suchender Benutzer</param>
/// <returns></returns>
private int GetAnzahlFollower(string nutzername)
{
try
{
string com = $"SELECT COUNT(*) FROM User_Follows WHERE User_ID = '{nutzername}'";
StaticDB.Connect();
SqlCommand command = new SqlCommand(com, StaticDB.Connection);
StaticDB.Connection.Open();
object result = command.ExecuteScalar();
StaticDB.Connection.Close();
if (result != null)
{
return((int)result);
}
else
{
return(-1);
}
}
catch (Exception ex)
{
_ = ex.Message;
if (StaticDB.Connection != null)
{
if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
{
StaticDB.Connection.Close();
}
}
return(-1);
}
}
public List <string> GetUnits()
{
try
{
List <string> units = new List <string>();
string com = "SELECT * FROM TP_Einheiten";
StaticDB.Connect();
StaticDB.Connection.Open();
SqlCommand sql = new SqlCommand(com, StaticDB.Connection);
var r = sql.ExecuteReader();
while (r.Read())
{
units.Add(r.GetString(0));
}
StaticDB.Connection.Close();
return(units);
}
catch (Exception ex)
{
_ = ex.Message;
if (StaticDB.Connection != null)
{
if (StaticDB.Connection.State != ConnectionState.Closed)
{
StaticDB.Connection.Close();
}
}
return(null);
}
}
internal string GetPasswort(string nutzername)
{
try
{
StaticDB.Connect();
string com = $"SELECT Password FROM User_Password WHERE Nutzername = '{nutzername}'";
SqlCommand command = new SqlCommand(com, StaticDB.Connection);
StaticDB.Connection.Open();
object r = command.ExecuteScalar();
StaticDB.Connection.Close();
if (r != null)
{
return(r.ToString());
}
else
{
return(null);
}
}
catch (Exception ex)
{
_ = ex.Message;
if (StaticDB.Connection != null)
{
if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
{
StaticDB.Connection.Close();
}
}
return(null);
}
}
/// <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);
}
}
/// <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);
}
}
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);
}
}
// 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);
}
}
/// <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);
}
}
/// <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);
}
}
/// <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);
}
}
/// <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);
}
}
/// <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);
}
}
/// <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);
}
}
/// <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);
}
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);
}
}
/// <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);
}
}
/// <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);
}
}
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);
}
}
/// <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);
}
}
/// <summary>
/// Liest die Trainingspläne für einen Nutzer aus der Datenbank aus.
/// </summary>
/// <param name="Nutzername">Nutzername von dem Trainingspläne geladen werden sollen (string)</param>
/// <returns>Liste von Trainingsplänen</returns>
public List <Trainingsplan> GetList(string Nutzername = null)
{
try
{
string com = null;
Trainingsplan trainingsplan = null;
List <Trainingsplan> trainingsplaene = new List <Trainingsplan>();
StaticDB.Connect();
if (string.IsNullOrWhiteSpace(Nutzername))
{
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 ";
}
else
{
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.ErstelltVon = '{Nutzername}'";
}
SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection);
StaticDB.Connection.Open();
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);
}
trainingsplaene.Add(trainingsplan);
}
StaticDB.Connection.Close();
foreach (var item in trainingsplaene)
{
item.Ersteller = AllVM.Datenbank.User.GetByName(item.Ersteller.Nutzername);
item.UebungList = AllVM.Datenbank.Trainingsplan.GetUebungen(item.ID);
item.Bewertungen = AllVM.Datenbank.Trainingsplan.GetBewertungen(item.ID);
item.DurchBewertung = AllVM.Datenbank.Trainingsplan.GetAvgBewertung(item.ID);
}
return(trainingsplaene);
}
catch (Exception ex)
{
_ = ex.Message;
if (StaticDB.Connection != null)
{
if (StaticDB.Connection.State != ConnectionState.Closed)
{
StaticDB.Connection.Close();
}
}
return(null);
}
}
/// <summary>
/// Löschen eines Feed-Beitrages
/// </summary>
/// <param name="news">Der zulöschende Beitrag</param>
/// <returns></returns>
internal bool Delete(News news)
{
string com = null;
bool result = false;
List <Like> listLikes = GetLikesWithNames(news.ID.ToString());
if (news.IsFoto)
{
com = $"DELETE FROM Feed_Fotos WHERE ID = '{news.ID}'";
result = StaticDB.RunSQL(com);
if (!result)
{
return(false);
}
}
com = $"DELETE FROM Feed_Likes WHERE Feed_ID = '{news.ID}'";
result = StaticDB.RunSQL(com);
if (!result)
{
if (news.IsFoto)
{
com = $"INSERT INTO Feed_Fotos VALUES('{news.ID}', @bild);";
StaticDB.Connect();
SqlCommand command = new SqlCommand(com, StaticDB.Connection);
command.Parameters.Add("@bild", System.Data.SqlDbType.VarBinary).Value = news.Foto;
StaticDB.Connection.Open();
int count = command.ExecuteNonQuery();
StaticDB.Connection.Close();
}
return(false);
}
com = $"DELETE FROM Feed_Info WHERE ID = '{news.ID}'";
result = StaticDB.RunSQL(com);
if (!result)
{
foreach (var item in listLikes)
{
com = $"INSERT INTO Feed_Likes VALUES('{news.ID}', '{item.User.Nutzername}')";
StaticDB.RunSQL(com);
}
if (news.IsFoto)
{
com = $"INSERT INTO Feed_Fotos VALUES('{news.ID}', @bild);";
StaticDB.Connect();
SqlCommand command = new SqlCommand(com, StaticDB.Connection);
command.Parameters.Add("@bild", System.Data.SqlDbType.VarBinary).Value = news.Foto;
StaticDB.Connection.Open();
int count = command.ExecuteNonQuery();
StaticDB.Connection.Close();
}
return(false);
}
com = $"DELETE FROM Feed_Base WHERE ID = '{news.ID}'";
result = StaticDB.RunSQL(com);
if (!result)
{
com = $"INSERT INTO Feed_Info VALUES('{news.ID}', '{news.ErstelltAm:yyyy-MM-dd HH:mm:ss}', '{news.Ersteller.Nutzername}')";
result = StaticDB.RunSQL(com);
if (news.IsFoto)
{
com = $"INSERT INTO Feed_Fotos VALUES('{news.ID}', @bild);";
StaticDB.Connect();
SqlCommand command = new SqlCommand(com, StaticDB.Connection);
command.Parameters.Add("@bild", System.Data.SqlDbType.VarBinary).Value = news.Foto;
StaticDB.Connection.Open();
int count = command.ExecuteNonQuery();
StaticDB.Connection.Close();
}
return(false);
}
return(true);
}
public bool AddErnährungsplan(Ernährungsplan ernährungsplan)
{
try
{
StaticDB.Connect();
StaticDB.Connection.Open();
string com = $"INSERT INTO EP_Base (Titel) VALUES ('{ernährungsplan.Titel}'); SELECT CAST(SCOPE_IDENTITY() AS INT)";
SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection);
int ID = (int)sqlCommand.ExecuteScalar();
StaticDB.Connection.Close();
if (ernährungsplan.GeAendertAm != default)
{
com = $"INSERT INTO EP_Info (ID, ErstelltAm, ErstelltVon, GeaendertAm, Kategorie) VALUES ({ID}, '{ernährungsplan.ErstelltAm:yyyy-dd-MM HH:mm:ss}', '{ernährungsplan.Ersteller.Nutzername}', '{ernährungsplan.GeAendertAm:yyyy-dd-MM HH:mm:ss}', '{ernährungsplan.Kategorie}');";
}
else
{
com = $"INSERT INTO EP_Info (ID, ErstelltAm, ErstelltVon, Kategorie) VALUES ({ID}, '{ernährungsplan.ErstelltAm:yyyy-dd-MM HH:mm:ss}', '{ernährungsplan.Ersteller.Nutzername}', '{ernährungsplan.Kategorie}');";
}
bool result = StaticDB.RunSQL(com);
if (result == false)
{
com = $"DELETE FROM EP_Base WHERE ID = '{ID}'";
StaticDB.RunSQL(com);
return(false);
}
foreach (var mahlzeit in ernährungsplan.MahlzeitenList)
{
string checkEx = $"SELECT * FROM EP_Mahlzeiten WHERE Nahrungsmittel='{mahlzeit.Nahrungsmittel}' AND Menge={mahlzeit.Menge.ToString().Replace(",", ".")} AND Einheit='{mahlzeit.Einheit}'";
if (StaticDB.CheckExistenz(checkEx) == true)
{
int mahlID = StaticDB.GetID(checkEx);
string comEpLink = $"INSERT INTO EP_Link_BaseMahlzeiten (ID_Base, ID_Mahlzeit) VALUES({ID}, {mahlID})";
StaticDB.RunSQL(comEpLink);
}
else
{
com = $"INSERT INTO EP_Mahlzeiten (Nahrungsmittel, Menge, Einheit) VALUES ('{mahlzeit.Nahrungsmittel}', {mahlzeit.Menge.ToString().Replace(",", ".")}, '{mahlzeit.Einheit}'); " +
"SELECT CAST(SCOPE_IDENTITY() AS INT)";
SqlCommand insertMahl = new SqlCommand(com, StaticDB.Connection);
StaticDB.Connection.Open();
int lastMahlID = (int)insertMahl.ExecuteScalar();
StaticDB.Connection.Close();
string comTpLink = $"INSERT INTO EP_Link_BaseMahlzeiten (ID_Base, ID_Mahlzeit) VALUES({ID}, {lastMahlID})";
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);
}
}
public List <Ernährungsplan> GetList(string Nutzername = null)
{
try
{
StaticDB.Connect();
string com = null;
List <Ernährungsplan> ernährungsplaene = new List <Ernährungsplan>();
if (string.IsNullOrWhiteSpace(Nutzername))
{
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 ";
}
else
{
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 info.ErstelltVon = '{Nutzername}'";
}
StaticDB.Connection.Open();
SqlCommand sqlCommand = new SqlCommand(com, StaticDB.Connection);
var r = sqlCommand.ExecuteReader();
while (r.Read())
{
Ernährungsplan ernährungsplan = 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),
};
ernährungsplaene.Add(ernährungsplan);
if (!r.IsDBNull(4))
{
ernährungsplan.GeAendertAm = r.GetDateTime(4);
}
}
StaticDB.Connection.Close();
foreach (var item in ernährungsplaene)
{
item.Ersteller = AllVM.Datenbank.User.GetByName(item.Ersteller.Nutzername);
item.Bewertungen = AllVM.Datenbank.Ernährungsplan.GetBewertungen(item.ID);
item.MahlzeitenList = AllVM.Datenbank.Ernährungsplan.GetMahlzeiten(item.ID);
item.DurchBewertung = AllVM.Datenbank.Ernährungsplan.GetAvgBewertung(item.ID);
}
return(ernährungsplaene);
}
catch (Exception ex)
{
_ = ex.Message;
if (StaticDB.Connection != null)
{
if (StaticDB.Connection.State != ConnectionState.Closed)
{
StaticDB.Connection.Close();
}
}
return(null);
}
}
/// <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);
}
}
/// <summary>
/// Feed-Beiträge zu einem bestimmten Nutzer ausgeben
/// </summary>
/// <param name="user">Zu suchender Benutzer</param>
/// <returns></returns>
public List <News> GetByUser(User user)
{
try
{
List <News> list = new List <News>();
StaticDB.Connect();
string com = "SELECT base.ID, base.Beschreibung, info.ErstelltAm, info.ErstelltVon, fotos.Bild, COUNT(likes.[User]) " +
"FROM Feed_Base AS base " +
"INNER JOIN Feed_Info AS info " +
"ON info.ID = base.ID " +
"LEFT JOIN Feed_Fotos AS fotos " +
"ON fotos.ID = info.ID " +
"LEFT JOIN Feed_Likes as likes " +
"ON likes.Feed_ID = base.ID " +
$"WHERE info.ErstelltVon = '{user.Nutzername}' " +
"GROUP BY base.ID, base.Beschreibung, info.ErstelltAm, info.ErstelltVon, fotos.Bild";
SqlCommand command = new SqlCommand(com, StaticDB.Connection);
StaticDB.Connection.Open();
var r = command.ExecuteReader();
while (r.Read())
{
News news = new News()
{
ID = r.GetInt32(0),
Beschreibung = r.GetString(1),
ErstelltAm = r.GetDateTime(2),
Ersteller = new User()
{
Nutzername = r.GetString(3)
},
Likes = r.GetInt32(5)
};
if (!r.IsDBNull(4))
{
news.Foto = (byte[])r[4];
}
list.Add(news);
}
StaticDB.Connection.Close();
foreach (var item in list)
{
item.Liked = CheckIfLiked(item.ID);
}
return(list);
}
catch (Exception ex)
{
_ = ex.Message;
if (StaticDB.Connection != null)
{
if (StaticDB.Connection.State != System.Data.ConnectionState.Closed)
{
StaticDB.Connection.Close();
}
}
return(null);
}
}
/// <summary>
/// Ausgabe der Feed-Einträge nach einem bestimmten Nutzer und optional in einem bestimmten Zeitraum
/// </summary>
/// <param name="user">Benutzer</param>
/// <param name="vonDatum">Ab wann soll selektiert werden</param>
/// <param name="bisDatum">Bis wann soll selektiert werden</param>
/// <returns></returns>
public List <News> Get(User user, DateTime vonDatum = default, DateTime bisDatum = default)
{
try
{
if (bisDatum == default)
{
bisDatum = DateTime.Now;
}
List <News> list = new List <News>();
StaticDB.Connect();
string com = $"SELECT DISTINCT base.ID, base.Beschreibung, info.ErstelltAm, info.ErstelltVon, fotos.Bild " +
$"FROM Feed_Base AS base " +
$"INNER JOIN Feed_Info AS info " +
$"ON info.ID = base.ID " +
$"INNER JOIN User_Follows AS follows " +
$"ON follows.Follow_ID = '{user.Nutzername}' " +
$"LEFT JOIN Feed_Fotos AS fotos " +
$"ON fotos.ID = info.ID " +
$"WHERE(info.ErstelltVon = follows.User_ID OR info.ErstelltVon = '{user.Nutzername}') " +
$"AND info.ErstelltAm >= '{vonDatum:yyyy-MM-dd}' " +
$"AND info.ErstelltAm <= '{bisDatum:yyyy-MM-dd HH:mm:ss}';";
SqlCommand command = new SqlCommand(com, StaticDB.Connection);
StaticDB.Connection.Open();
var r = command.ExecuteReader();
while (r.Read())
{
News news = new News()
{
ID = r.GetInt32(0),
Beschreibung = r.GetString(1),
ErstelltAm = r.GetDateTime(2),
Ersteller = new User()
{
Nutzername = r.GetString(3)
},
};
if (!r.IsDBNull(4))
{
news.Foto = (byte[])r[4];
}
list.Add(news);
}
StaticDB.Connection.Close();
foreach (var item in list)
{
item.Liked = CheckIfLiked(item.ID);
item.Likes = GetLikes(item.ID);
item.Ersteller = AllVM.Datenbank.User.GetByName(item.Ersteller.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);
}
}
