// -----------------------------------------------------------------------------
public Chart(int chartId, string name, Profile profile, IList<User> users, DateTime created)
{
this.ChartID = chartId;
this.name = name;
this.Profile = profile;
this.Users = new ReadOnlyCollection<User>(users);
this.Created = created;
}
public static void SaveProfile(Profile profile)
{
try
{
string query = "UPDATE [Profile] SET ";
query += "Name = '" + profile.Name + "',";
query += "IsPrimary = '" + profile.IsPrimary.ToString() + "',";
query += "IsDeleted = '" + profile.IsDeleted.ToString() + "' WHERE ProfileID = " + profile.ProfileID;
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
catch (SqlException ex)
{
foreach (SqlError err in ex.Errors)
{
switch (err.Number)
{
case SqllErrorNumbers.DupKey: throw new Exception("Diese Daten werden bereits verwendet.");
}
}
}
catch (Exception ex)
{
string errmsg = "Fehler beim Speichern des Profils.\n\n";
errmsg += "DatabaseHandler.SaveProfile(profile): " + ex.ToString();
throw new Exception(errmsg);
}
}
public static Profile GetProfile(string profilename)
{
Profile p = null;
try
{
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM Profile WHERE Name = '" + profilename + "'", connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
p = new Profile(
Convert.ToInt32(reader["ProfileID"]),
reader["Name"].ToString(),
Convert.ToBoolean(reader["IsPrimary"]),
Convert.ToBoolean(reader["IsDeleted"]),
Convert.ToDateTime(reader["Created"]),
GetArticles(Convert.ToInt32(reader["ProfileID"])),
GetDeposites(),
GetCancellations(Convert.ToInt32(reader["ProfileID"]))
);
}
connection.Close();
}
catch (Exception ex)
{
string errmsg = "Fehler beim Abrufen des Profils.\n\n";
errmsg += "DatabaseHandler.GetProfile(profilename): " + ex.ToString();
throw new Exception(errmsg);
}
if (p == null)
throw new Exception("Es existiert kein Profil mit der angegebenen ProfilID (" + profilename + ")\n\nDatabaseHandler.GetProfile(profilename)");
return p;
}
public static ObservableCollection<Profile> GetProfiles(bool includeDeleted = false)
{
ObservableCollection<Profile> profiles = new ObservableCollection<Profile>();
try
{
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM Profile" + (includeDeleted ? "" : " WHERE IsDeleted = 0"), connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Profile p = new Profile(
Convert.ToInt32(reader["ProfileID"]),
reader["Name"].ToString(),
Convert.ToBoolean(reader["IsPrimary"]),
Convert.ToBoolean(reader["IsDeleted"]),
Convert.ToDateTime(reader["Created"]),
GetArticles(Convert.ToInt32(reader["ProfileID"])),
GetDeposites(),
GetCancellations(Convert.ToInt32(reader["ProfileID"]))
);
profiles.Add(p);
}
connection.Close();
}
catch (Exception ex)
{
string errmsg = "Fehler beim Abrufen der Profile.\n\n";
errmsg += "DatabaseHandler.GetProfiles(): " + ex.ToString();
throw new Exception(errmsg);
}
return profiles;
}
public static ObservableCollection<DepositReturn> GetUserDepositReturnsDetailed(IList<User> users, Profile profile)
{
List<DepositReturn> returns = new List<DepositReturn>();
foreach (User u in users)
{
var userDepRet = DatabaseHandler.GetUserDepositReturnsDetailed(u, profile);
returns.AddRange(userDepRet);
}
return new ObservableCollection<DepositReturn>((from r in returns orderby r.Created select r).ToList());
}
public static ObservableCollection<DepositReturn> GetUserDepositReturnsDetailed(User user, Profile profile)
{
ObservableCollection<DepositReturn> deps = new ObservableCollection<DepositReturn>();
string command = @"
SELECT *
FROM DepositReturn
WHERE userId = " + user.UserID + " AND ProfileID = " + profile.ProfileID;
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(command, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Deposit d = GetDeposit(Convert.ToInt32(reader["DepositID"]));
deps.Add(new DepositReturn(
Convert.ToInt32(reader["ReturnID"]),
d,
profile,
user,
Convert.ToInt32(reader["Count"]),
Convert.ToDateTime(reader["Created"])
));
}
connection.Close();
return deps;
}
public static Cart GetCanceledArticles(Profile profile, IList<User> users)
{
Cart c = new Cart();
string query = "SELECT UserID,a.ArticleID,ArticleCount " +
"FROM Cancellation AS c " +
"INNER JOIN Article AS a " +
"ON a.ArticleID = c.ArticleID " +
"WHERE a.ProfileID = " + profile.ProfileID + " AND (";
for (int i = 0; i < users.Count; i++)
query += i == users.Count - 1 ? "c.UserID = "+users[i].UserID + ")" : "c.UserID = "+users[i].UserID + " OR ";
try
{
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
c.Add(
GetArticle(Convert.ToInt32(reader["ArticleID"])), // article id to article
Convert.ToInt32(reader["ArticleCount"]) // count
);
}
connection.Close();
}
catch (Exception ex)
{
string errmsg = "Fehler beim Abrufen der stornierten Artikel.\n\n";
errmsg += "DatabaseHandler.GetCanceledArticles(profile, users): " + ex.ToString();
throw new Exception(errmsg);
}
return c;
}
public static Cart GetPurchaseCart(Profile profile, IList<User> users, bool includeUnsold = false)
{
Cart cart = new Cart();
List<Article> articleCache = new List<Article>();
if (includeUnsold)
foreach (Article a in GetArticles(profile.ProfileID))
cart.Add(a, 0);
string query = "SELECT pa.ArticleID, pa.ArticleCount FROM Purchase AS p " +
"INNER JOIN PurchasedArticle AS pa " +
"ON p.PurchaseID = pa.PurchaseID " +
"INNER JOIN Article AS ar " +
"ON ar.ArticleID = pa.ArticleID " +
"WHERE ar.ProfileID = " + profile.ProfileID + " AND (";
for (int i = 0; i < users.Count; i++)
query += i == users.Count - 1 ? "UserID = " + users[i].UserID + ")" : "UserID = " + users[i].UserID + " OR ";
try
{
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int artId = Convert.ToInt32(reader["ArticleID"]);
Article a = articleCache.Count > 0 ? (from art in articleCache where art.ArticleID == artId select art as Article).FirstOrDefault() : null;
if (a == null)
{
a = GetArticle(artId);
articleCache.Add(a);
}
cart.Add(
a, // article id to article
Convert.ToInt32(reader["ArticleCount"]) // count
);
}
connection.Close();
}
catch (Exception ex)
{
string errmsg = "Fehler beim Abrufen der verkauften Artikel.\n\n";
errmsg += "DatabaseHandler.GetPurchases(profile, users): " + ex.ToString();
throw new Exception(errmsg);
}
return cart;
}
public static ObservableCollection<Purchase> GetPurchases(Profile profile)
{
_loadPurchasesUserCache = new List<User>();
_loadPurchasesArticleCache = new List<Article>();
ObservableCollection<Purchase> purchases = new ObservableCollection<Purchase>();
GetPurchaseArticleCache = new List<Article>();
GetPurchaseUserCache = new List<User>();
int currentPurchaseId = -1;
Purchase p = null;
Cart c = null;
User u = null;
Article a = null;
try
{
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand("SELECT PurchaseID, UserID, ArticleID, ArticleCount, [Date] FROM vPurchaseProfile WHERE ProfileID = " + profile.ProfileID + " ORDER BY [Date] DESC", connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int pid = Convert.ToInt32(reader["PurchaseID"]);
int artId = Convert.ToInt32(reader["ArticleID"]);
// article cache
a = (from art in _loadPurchasesArticleCache where art.ArticleID == artId select art as Article).FirstOrDefault();
if (a == null) { a = GetArticle(artId); _loadPurchasesArticleCache.Add(a); }
if (currentPurchaseId == -1 || pid != currentPurchaseId) // create new purchase object
{
int userId = Convert.ToInt32(reader["UserID"]);
if (p != null) purchases.Add(p);
// user cache
u = (from usr in _loadPurchasesUserCache where usr.UserID == userId select usr as User).FirstOrDefault();
if (u == null) { u = GetUser(userId); _loadPurchasesUserCache.Add(u); }
currentPurchaseId = pid;
c = new Cart();
p = new Purchase(pid, c, u, Convert.ToDateTime(reader["Date"]));
c.Add(a, Convert.ToInt32(reader["ArticleCount"]));
}
else // add article to purchase object
{
c.Add(a, Convert.ToInt32(reader["ArticleCount"]));
}
}
if (!purchases.Contains(p) && p != null) purchases.Add(p);
connection.Close();
}
catch (Exception ex)
{
string errmsg = "Fehler beim Abrufen der Verkäufe.\n\n";
errmsg += "DatabaseHandler.GetPurchases(profile): " + ex.ToString();
throw new Exception(errmsg);
}
return purchases;
}