private void AddBookLoad(object sender, EventArgs e)
        {
            using (var context = new LibraryModel())
            {
                var publishers = from p in context.publishers
                                 select p;

                publishersList = new List <string>();

                foreach (var p in publishers)
                {
                    publishersList.Add(p.publisher_name);
                }

                publishersList.Sort();

                publisherComboBox.DataSource = publishersList;

                var genres = (from item in context.items
                              select item.genre).Distinct();

                genreList = new List <string>();

                foreach (var g in genres)
                {
                    genreList.Add(g);
                }

                genreComboBox.DataSource = genreList;
            }
        }
Exemple #2
0
        private void button1_Click(object sender, EventArgs e)
        {
            int  id;
            bool result = int.TryParse(userIdTextBox.Text, out id);

            if (userIdTextBox.Text != "" && result)
            {
                using (var context = new LibraryModel())
                {
                    var userId = (from user in context.adminusers
                                  where user.admin_id == id
                                  select user).FirstOrDefault();

                    if (userId != null)
                    {
                        showMainForm(true);
                    }
                    else
                    {
                        MessageBox.Show("No User Found with given User ID", "Library Database",
                                        MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                    }
                }
            }
            else
            {
                MessageBox.Show("No User Found with given User ID", "Library Database",
                                MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
            }
        }
Exemple #3
0
        public Form1()
        {
            selectedItem = SelectedItem.Book;
            InitializeComponent();

            switchToBookView();

            genres        = new List <string>();
            durations     = new List <string>();
            mpaaRatings   = new List <string>();
            numTracks     = new List <string>();
            ratingsById   = new Dictionary <int, double>();
            minimumRating = 3;

            if (!isAdmin)
            {
                this.MainMenuStrip.Hide();
            }

            // Get ratings
            using (var context = new LibraryModel())
            {
                //GROUP BY AGGREGATE QUERY
                //========================

                //Select avg(r.rating) , i.item_title
                //from Rating r
                //join item i on i.item_id = r.item_id
                //group by r.item_id
                //HAVING avg(r.rating) > UserAverage

                var ratings = from r in context.ratings
                              //join r in context.ratings on i.item_id equals r.item_id
                              group r by new
                {
                    r.item_id
                }
                into g
                    select new
                {
                    Average = g.Average(p => p.rating1),
                    g.Key.item_id
                };

                foreach (var rating in ratings)
                {
                    ratingsById.Add(rating.item_id, rating.Average.Value);
                }
            }
        }
        private void onLoad(object sender, EventArgs e)
        {
            using (var context = new LibraryModel())
            {
                var genres = (from item in context.items
                              select item.genre).Distinct();

                genreList = new List <string>();

                foreach (var g in genres)
                {
                    genreList.Add(g);
                }

                genreComboBox.DataSource = genreList;
            }
        }
        private void insertMovie(object sender, EventArgs e)
        {
            string director    = directorTextBox.Text;
            string title       = titleTextBox.Text;
            string durationStr = durationTextBox.Text;

            int rIndex = ratingComboBox.SelectedIndex;
            int gIndex = genreComboBox.SelectedIndex;

            string   genre      = genreList.ElementAt(gIndex);
            string   mpaaRating = ratings[rIndex];
            DateTime date       = datePicker.Value;

            //validate input

            int  duration;
            bool result = int.TryParse(durationStr, out duration);

            if (director == "" || title == "" || durationStr == "" || !result)
            {
                //validation error
                MessageBox.Show("Please fix invalid input", "Library Database",
                                MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
            }
            else
            {
                //validation successful
                //insert new movie

                using (var context = new LibraryModel())
                {
                    //INSERT INTO item(Item_title, genre, release_date)
                    //VALUES([title], [genre], [date])
                    item i = new item
                    {
                        genre        = genre,
                        Item_title   = title,
                        release_date = date
                    };

                    context.items.Add(i);
                    context.SaveChanges();

                    int id = (from item in context.items
                              orderby item.item_id descending
                              select item).FirstOrDefault().item_id;

                    //INSERT INTO movie(item_id, director, mpaa_rating, duration)
                    //VALUES((SELECT item_id FROM item ORDER BY item_id DESC LIMIT 1), [director], [mpaa_rating], [duration])

                    movie m = new movie
                    {
                        item_id     = id,
                        director    = director,
                        mpaa_rating = mpaaRating,
                        duration    = duration
                    };

                    context.movies.Add(m);
                    context.SaveChanges();

                    var moviesWithGenre = from item in context.items
                                          join mv in context.movies on item.item_id equals mv.item_id
                                          group item.item_id by item.genre into group1
                                          select new
                    {
                        count = group1.Count(),
                        genre = group1.Key
                    };

                    string message = "____________________________\n\n";
                    foreach (var selectedGenre in moviesWithGenre)
                    {
                        message = message + selectedGenre.genre + "(" + selectedGenre.count + ")\n";
                    }


                    MessageBox.Show("You've added '" + title + "'.\nThis movie has been added into the genre '" + genre + "'.\n" + message, "Library Database",
                                    MessageBoxButtons.OKCancel, MessageBoxIcon.Information);

                    context.Dispose();
                }

                this.Close();
            }
        }
Exemple #6
0
        private void searchButton_Click(object sender, EventArgs e)
        {
            searchResultsGrid.Rows.Clear();

            var title = searchTextBox.Text;

            using (var context = new LibraryModel())
            {
                switch (selectedItem)
                {
                case SelectedItem.Book:
                    // Basic query
                    var books = context.items.Include("book").Where(i => i.Item_title.ToUpper().Contains(title.ToUpper())).Where(i => i.book != null);
                    //books.Join(b in book_published)
                    //books.Include("book_publishedby");
                    // Apply filters
                    books = addGenreFilter(books);

                    // Execute query
                    foreach (var book in books.ToList())
                    {
                        if ((ratingsById.ContainsKey(book.item_id) && ratingsById[book.item_id] >= minimumRating) ||
                            (!ratingsById.ContainsKey(book.item_id)))
                        {
                            searchResultsGrid.Rows.Add(createBookRow(book));
                        }
                    }
                    break;

                case SelectedItem.Movie:
                    // Basic query
                    var movies = context.items.Include("movie").Where(i => i.Item_title.ToUpper().Contains(title.ToUpper())).Where(i => i.movie != null);

                    // Apply filters
                    movies = addGenreFilter(movies);
                    movies = addDurationFilter(movies);
                    movies = addMpaaRatingsFilter(movies);

                    // Execute query
                    foreach (var movie in movies.ToList())
                    {
                        if ((ratingsById.ContainsKey(movie.item_id) && ratingsById[movie.item_id] >= minimumRating) ||
                            (!ratingsById.ContainsKey(movie.item_id)))
                        {
                            searchResultsGrid.Rows.Add(createMovieRow(movie));
                        }
                    }
                    break;

                case SelectedItem.Music:
                    // Basic query
                    var music = context.items.Include("musicalbum").Where(i => i.Item_title.ToUpper().Contains(title.ToUpper())).Where(i => i.musicalbum != null);

                    // Apply filters
                    music = addGenreFilter(music);
                    music = addNumTracksFilter(music);

                    // Execute query
                    foreach (var album in music.ToList())
                    {
                        if ((ratingsById.ContainsKey(album.item_id) && ratingsById[album.item_id] >= minimumRating) ||
                            (!ratingsById.ContainsKey(album.item_id)))
                        {
                            searchResultsGrid.Rows.Add(createMusicRow(album));
                        }
                    }
                    break;

                default:
                    break;
                }
            }

            searchResultsGrid.Update();
        }
Exemple #7
0
        private void getTopRatedButton_Click(object sender, EventArgs e)
        {
            using (var context = new LibraryModel())
            {
                searchResultsGrid.Rows.Clear();
                var title = searchTextBox.Text;

                switch (selectedItem)
                {
                //CORRELATED NESTED QUERY
                //==========================

                //select i.Item_title , avg(r.rating) as aRating, i.genre
                //from item i, rating r
                //where i.item_id = r.item_id
                //group by i.item_id
                //having
                //(aRating >
                //              (select avg(rating.rating)
                //                from rating, item i2
                //                where rating.item_id = i2.item_id
                //                &&
                //                i2.genre = i.genre
                //                group by i2.genre)
                //                );

                case SelectedItem.Book:

                    var topRatedBooks = from i in context.items.ToList()
                                        join b in context.books on i.item_id equals b.item_id
                                        where i.ratings.Average(r => r.rating1) > (

                        from item in context.items
                        where item.genre == i.genre
                        select item.ratings.Average(r => r.rating1)

                        ).Average()
                                        select i;

                    foreach (var book in topRatedBooks)
                    {
                        searchResultsGrid.Rows.Add(createBookRow(book));
                    }

                    break;

                case SelectedItem.Movie:

                    var topRatedMovies = from i in context.items.ToList()
                                         join m in context.movies on i.item_id equals m.item_id
                                         where i.Item_title.Contains(title) && i.ratings.Average(r => r.rating1) > (

                        from item in context.items
                        where item.genre == i.genre
                        select item.ratings.Average(r => r.rating1)

                        ).Average()
                                         select i;

                    foreach (var movie in topRatedMovies)
                    {
                        searchResultsGrid.Rows.Add(createMovieRow(movie));
                    }

                    break;

                case SelectedItem.Music:

                    var topRatedMusic = from i in context.items.ToList()
                                        join m in context.musicalbums on i.item_id equals m.item_id
                                        where i.Item_title.Contains(title) && i.ratings.Average(r => r.rating1) > (

                        from item in context.items
                        where item.genre == i.genre
                        select item.ratings.Average(r => r.rating1)

                        ).Average()
                                        select i;

                    foreach (var album in topRatedMusic)
                    {
                        searchResultsGrid.Rows.Add(createMusicRow(album));
                    }

                    break;

                default:
                    break;
                }
            }
        }
        private void insertBookButton_Click(object sender, EventArgs e)
        {
            //validate fields
            //insert new book

            int genreIndex = genreComboBox.SelectedIndex;
            int pubIndex   = publisherComboBox.SelectedIndex;

            string p = publishersList.ElementAt(pubIndex);
            string g = genreList.ElementAt(genreIndex);

            string   title  = titleTextBox.Text;
            string   author = authorTextBox.Text;
            DateTime date   = datePicker.Value;

            if (author == "" || title == "")
            {
                //validation error
                MessageBox.Show("Please fix invalid input", "Library Database",
                                MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
            }
            else
            {
                using (var context = new LibraryModel())
                {
                    //INSERT INTO item(item_title, genre)
                    //VALUES([title], [genre]);

                    item i = new item
                    {
                        genre        = g,
                        Item_title   = title,
                        release_date = date
                    };

                    context.items.Add(i);
                    context.SaveChanges();

                    int id = (from item in context.items
                              orderby item.item_id descending
                              select item).FirstOrDefault().item_id;

                    //INSERT INTO book(author, item_id)
                    //VALUES([author], (SELECT item_id FROM item ORDER BY item_id DESC LIMIT 1))

                    book b = new book
                    {
                        item_id = id,
                        author  = author
                    };

                    context.books.Add(b);

                    int pid = (from pub in context.publishers
                               where pub.publisher_name == p
                               select pub).First().publisher_id;

                    //INSERT INTO book_publishedby(item_id, publisher_id)
                    //VALUES((SELECT item_id FROM item ORDER BY item_id DESC LIMIT 1), [publisherid])

                    book_publishedby published = new book_publishedby
                    {
                        item_id      = id,
                        publisher_id = pid
                    };

                    context.book_publishedby.Add(published);
                    context.SaveChanges();

                    var booksWithGenre = from item in context.items
                                         join bk in context.books on item.item_id equals bk.item_id
                                         group item.item_id by item.genre into group1
                                         select new {
                        count = group1.Count(),
                        genre = group1.Key
                    };

                    string message = "____________________________\n\n";
                    foreach (var selectedGenre in booksWithGenre)
                    {
                        message = message + selectedGenre.genre + "(" + selectedGenre.count + ")\n";
                    }


                    MessageBox.Show("You've added '" + title + "'.\nThis book has been added into the genre '" + g + "'.\n" + message, "Library Database",
                                    MessageBoxButtons.OKCancel, MessageBoxIcon.Information);

                    context.Dispose();
                }

                this.Close();
            }
        }
        private void insertMusicAlbum(object sender, EventArgs e)
        {
            string artist          = artistTextBox.Text;
            string title           = titleTextBox.Text;
            string numTracksString = numTracksTextBox.Text;
            int    gIndex          = genreComboBox.SelectedIndex;

            string   genre = genreList.ElementAt(gIndex);
            DateTime date  = datePicker.Value;

            //validate input

            int  numTracks;
            bool result = int.TryParse(numTracksString, out numTracks);

            if (artist == "" || title == "" || numTracksString == "" || !result)
            {
                //validation error
                MessageBox.Show("Please fix invalid input", "Library Database",
                                MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
            }
            else
            {
                //validation successful
                //insert new music album

                using (var context = new LibraryModel())
                {
                    //INSERT INTO item(Item_title, genre, release_date)
                    //VALUES([title], [genre], [date])
                    item i = new item
                    {
                        genre        = genre,
                        Item_title   = title,
                        release_date = date
                    };

                    context.items.Add(i);
                    context.SaveChanges();

                    int id = (from item in context.items
                              orderby item.item_id descending
                              select item).FirstOrDefault().item_id;

                    //INSERT INTO musicalbum(item_id, artist, num_of_tracks)
                    //VALUES((SELECT item_id FROM item ORDER BY item_id DESC LIMIT 1), [artist], [numTracks])

                    musicalbum m = new musicalbum
                    {
                        item_id       = id,
                        artist        = artist,
                        num_of_tracks = numTracks
                    };

                    context.musicalbums.Add(m);
                    context.SaveChanges();

                    var musicWithGenre = from item in context.items
                                         join music in context.musicalbums on item.item_id equals music.item_id
                                         group item.item_id by item.genre into group1
                                         select new
                    {
                        count = group1.Count(),
                        genre = group1.Key
                    };

                    string message = "____________________________\n\n";
                    foreach (var selectedGenre in musicWithGenre)
                    {
                        message = message + selectedGenre.genre + "(" + selectedGenre.count + ")\n";
                    }


                    MessageBox.Show("You've added '" + title + "'.\nThis music album has been added into the genre '" + genre + "'.\n" + message, "Library Database",
                                    MessageBoxButtons.OKCancel, MessageBoxIcon.Information);

                    context.Dispose();
                }

                this.Close();
            }
        }