예제 #1
0
파일: Chart.cs 프로젝트: fhoner/Kasse
        // -----------------------------------------------------------------------------

        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;
        }
예제 #2
0
        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);
            }
        }
예제 #3
0
        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;
        }
예제 #4
0
        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;
        }
예제 #5
0
        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;
        }
예제 #6
0
        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;
        }
예제 #7
0
        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;
        }