예제 #1
0
        //Vrakja lista na knigi
        public List<Book> SelectListBooks(string search,string language, string category)
        {
            List<Book> list = new List<Book>();

            using (MySqlConnection connection = new MySqlConnection())
            {
                connection.ConnectionString = connString;
                connection.Open();

                string query = "SELECT IDBook, Name, ImageSrc, Description, Date FROM Books, Categories, Tags, BelongsTo, Tagged";
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();

                Dictionary<string, string> dictionary = new Dictionary<string, string>();
                List<Dictionary<string, string>> books = new List<Dictionary<string, string>>();
                List<Author> authors;

                while (dataReader.Read())
                {
                    dictionary.Add("IDBook", dataReader["IDBook"].ToString());
                    dictionary.Add("Name", dataReader["Name"].ToString());
                    dictionary.Add("ImageSrc", dataReader["ImageSrc"].ToString());
                    dictionary.Add("Description", dataReader["Description"].ToString());
                    dictionary.Add("Date", dataReader["YearPublished"].ToString());
                    books.Add(dictionary);
                }
                dataReader.Close();

                for (int i = 0; i < books.Count; i++)
                {
                    //Lista na avtori za sekoja kniga
                    authors = new List<Author>();
                    query = "SELECT a.Name, a.Surname,a.Country FROM Authors as a, Books as b, Wrote as w WHERE w.IDAuthor = a.IDAuthor AND w.IDBook =" + books[i]["IDBook"];
                    command.CommandText = query;
                    dataReader = command.ExecuteReader();

                    while (dataReader.Read())
                    {
                        Author a = new Author(dataReader["Name"].ToString(), dataReader["Surname"].ToString(), dataReader["Countrey"].ToString());
                        authors.Add(a);
                    }
                    dataReader.Close();

                    //Dodavanje na knigata vo listata
                    Book b = new Book(books[i]["Name"], authors, books[i]["ImageSrc"], books[i]["Description"], books[i]["Date"]);
                    list.Add(b);
                    }

                    connection.Close();
                    return list;
            }
        }
예제 #2
0
        public void SetImgAndInfo(string bookID)
        {
            using (MySqlConnection connection = new MySqlConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["BooksConn"].ConnectionString;

                try
                {
                    connection.Open();

                    //kreiranje na kniga
                    string sql = "SELECT * FROM books WHERE IDBook = ?IDBook";

                    MySqlCommand command = new MySqlCommand(sql, connection);
                    command.Parameters.AddWithValue("?IDBook", bookID);

                    MySqlDataReader reader = command.ExecuteReader();

                    Book book = new Book();
                    if (reader.Read())
                    {
                        book.IDBook = Int32.Parse(reader["IDBook"].ToString());
                        book.ISBN = reader["ISBN"].ToString();
                        book.Name = reader["Name"].ToString();
                        book.Description = reader["Description"].ToString();
                        book.Language = reader["Language"].ToString();
                        book.ImageSrc = reader["CoverLink"].ToString();
                        book.ThumbnailSrc = reader["Thumbnail"].ToString();
                        book.YearPublished = reader["YearPublished"].ToString();
                        book.DateAdded = reader["DateAdded"].ToString();
                        book.SumRating = Int32.Parse(reader["SumRating"].ToString());
                        book.NumVotes = Int32.Parse(reader["NumVotes"].ToString());
                    }

                    reader.Close();

                    //dodavanje na avtorite vo kreiranata kniga
                    sql = "SELECT Name FROM authors, wrote WHERE authors.IDAuthor=wrote.IDAuthor AND wrote.IDBook=?IDBook";

                    command.CommandText = sql;
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("?IDBook", bookID);
                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        while (reader.Read())
                            book.Authors.Add(reader["Name"].ToString());

                    reader.Close();

                    //dodavanje na kategoriite vo kreiranata kniga
                    sql = "SELECT Name FROM categories, belongsto WHERE categories.IDCategory=belongsto.IDCategory AND belongsto.IDBook=?IDBook";

                    command.CommandText = sql;
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("?IDBook", bookID);
                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        while (reader.Read())
                            book.Categories.Add(reader["Name"].ToString());

                    reader.Close();

                    //dodavanje na tagovite vo kreiranata kniga
                    sql = "SELECT Name FROM tags, tagged WHERE tags.IDTag=tagged.IDTag AND tagged.IDBook=?IDBook";

                    command.CommandText = sql;
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("?IDBook", bookID);
                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        while (reader.Read())
                            book.Tags.Add(reader["Name"].ToString());

                    reader.Close();

                    //zemanje na site tagovi koi se koristat od strana na adminot
                    sql = "SELECT * FROM Tags WHERE IDTag NOT IN (SELECT IDTag FROM tagged WHERE IDBook=?IDBook)";

                    command.CommandText = sql;
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("?IDBook", bookID);
                    reader = command.ExecuteReader();

                    StringBuilder sb = new StringBuilder();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            sb.Append("<li><a href='' style='text-decoration: none; color: red; margin-right: 15px;'>" + "#" + reader["Name"] + "</a></li>");
                        }
                    }
                    allTags.InnerHtml = sb.ToString();
                    reader.Close();

                    //Jcarousel Recommendation Books
                    command.CommandText = "SELECT b.IDBook, b.Thumbnail, b.ISBN FROM Books as b, BelongsTo as bt, Categories as c WHERE b.IDBook = bt.IDBook AND bt.IDCategory = c.IDCategory AND c.Name = ?CategoryName ORDER BY rand() LIMIT 10";
                    command.Connection = connection;
                    foreach (string category in book.Categories)
                    {
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("?IDBook", IDBook);
                        command.Parameters.AddWithValue("?CategoryName", category);

                        reader = command.ExecuteReader();
                        if (reader.HasRows)
                        {
                            StringBuilder sb2 = new StringBuilder();
                            sb2.Append("<div class='jcarousel' data-jcarousel='true'><ul>");
                            while (reader.Read())
                            {
                                if(reader["thumbnail"].ToString().StartsWith(reader["ISBN"].ToString()))
                                    sb2.Append("<li><a href='book.aspx?id=" + reader["IDBook"] + "'><img src='/Images/BookPicture/" + reader["thumbnail"].ToString() + "' /></a></li>");
                                else
                                    sb2.Append("<li><a href='book.aspx?id=" + reader["IDBook"] + "'><img src='" + reader["thumbnail"].ToString() + "' /></a></li>");
                            }
                            sb2.Append("</ul></div>");
                            sb2.Append("<a href='#' class='jcarousel-control-prev'>&lsaquo;</a>");
                            sb2.Append("<a href='#' class='jcarousel-control-next'>&rsaquo;</a>");
                            sb2.Append("<p class='jcarousel-pagination'></p>");

                            jcarouselWrapper.InnerHtml = sb2.ToString();
                        }
                        reader.Close();
                    }

                    //setiranje na StarRating
                    if (book.NumVotes > 0)
                        StarRating = (float)book.SumRating / (book.NumVotes * 1.0F);
                    else
                        StarRating = 0.0F;

                    //popolnuvanje na komponentite
                    if (book.ImageSrc == "defaultImage.png")
                        imgBook.ImageUrl = "~/Images/defaultImage.png";
                    else if (book.ImageSrc.StartsWith(book.ISBN))
                        imgBook.ImageUrl = "~/Images/BookPicture/" + book.ImageSrc;
                    else
                        imgBook.ImageUrl = book.ImageSrc;

                    lblDescription.Text = book.Description;

                    sb.Clear();
                    sb.Append(book.Name + "<br/>" + "By" + "<br/>");
                    sb.Append(string.Join(", ", book.Authors.ToArray()));

                    lblAbout.Text = sb.ToString();

                    sb.Clear();
                    sb.Append("Year:" + "<label style='margin-left: 18%;'>" + book.YearPublished + "</label>" + "<br/>" + "Rating:" + "<label style='margin-left: 14%;'>" + StarRating + "</label>" + "<br/>" + "Language:" + "<label style='margin-left: 5%;'>" + book.Language + "</label>");
                    sb.Append("<br/>" + "Categories: ");
                    sb.Append(string.Join(", ", book.Categories.ToArray()));

                    lblInfo.Text = sb.ToString();

                    sb.Clear();
                    foreach (string tag in book.Tags)
                        sb.Append("<li><a href='' style='text-decoration: none; color: red; margin-right: 15px;'>" + "#" + tag + "</a></li>");

                    tags.InnerHtml = sb.ToString();

                    //Dodavanje buy/download linkovi
                    hlAmazon.NavigateUrl = "http://www.amazon.com/gp/product/" + book.ISBN;
                    hlEbooks.NavigateUrl = "http://www.ebooks.com/searchapp/searchresults.net?term=" + book.ISBN;

                    ViewState["Book"] = book;
                }
                catch (Exception e)
                {
                    lblError.Text = e.Message + " " + e.InnerException;
                    lblError.Visible = true;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
예제 #3
0
        protected void btnSearch_Click(object sender, EventArgs e)
        {
            using (WebClient webClient = new WebClient())
            {
                string url = "";
                string urlContents = "";
                url = "https://www.googleapis.com/books/v1/volumes?key=AIzaSyDQNdTLOCVjieDzeY9IZoyaMvpDy4ApRec&maxResults=40&q=";
                url += tbSearchBooks.Text;

                try
                {
                    urlContents = webClient.DownloadString(url);
                }
                catch (Exception exc)
                {
                    lblError.Text = exc.Message;
                    lblError.Visible = true;
                }

                if (urlContents.ToString().Trim() != "")
                {
                    Dictionary<string, object> jsonData = new Dictionary<string, object>();

                    jsonData = new JavaScriptSerializer().Deserialize<Dictionary<string, object>>(urlContents);

                    ArrayList items = jsonData["items"] as ArrayList;

                    List<string> idList = new List<string>();
                    for (int i = 0; i < items.Count; i++)
                    {
                        Dictionary<string, object> item = items[i] as Dictionary<string, object>;
                        idList.Add(item["id"].ToString());
                    }

                    //Prevzemanje lista na knigi od google.books
                    List<Book> bookList = new List<Book>();

                    foreach (string id in idList)
                    {
                        url = "https://www.googleapis.com/books/v1/volumes/";
                        url += (id + "?key=" + api_key);

                        try
                        {
                            urlContents = webClient.DownloadString(url);
                            jsonData = new JavaScriptSerializer().Deserialize<Dictionary<string, object>>(urlContents);

                            Dictionary<string, object> volumeInfo = jsonData["volumeInfo"] as Dictionary<string, object>;

                            //Kreiranje i dodavanje na kniga vo lista----------------------------------------

                            //ISBN id na kniga
                            if (volumeInfo.ContainsKey("industryIdentifiers"))
                            {
                                Book book = new Book();

                                ArrayList isbn = volumeInfo["industryIdentifiers"] as ArrayList;
                                Dictionary<string, object> isbn10 = isbn[0] as Dictionary<string, object>;
                                book.ISBN = isbn10["identifier"].ToString();

                                if (volumeInfo.ContainsKey("title"))
                                    book.Name = volumeInfo["title"].ToString();
                                else
                                    book.Name = "No title available";

                                if (volumeInfo.ContainsKey("description"))
                                    book.Description = volumeInfo["description"].ToString();
                                else
                                    book.Description = "No description available.";

                                if (volumeInfo.ContainsKey("publishedDate"))
                                    book.YearPublished = volumeInfo["publishedDate"].ToString();
                                else
                                    book.YearPublished = "-";
                                if (volumeInfo.ContainsKey("language"))
                                    book.Language = volumeInfo["language"].ToString();
                                else
                                    book.Language = "No language";

                                book.DateAdded = DateTime.Now.Year.ToString();
                                book.SumRating = 0;
                                book.NumVotes = 0;

                                //Avtori
                                if (volumeInfo.ContainsKey("authors"))
                                {
                                    ArrayList authors = volumeInfo["authors"] as ArrayList;
                                    List<string> authorList = new List<string>();
                                    for (int i = 0; i < authors.Count; i++)
                                    {
                                        authorList.Add(authors[i].ToString());
                                    }
                                    book.Authors = authorList;
                                }
                                else
                                {
                                    book.Authors = new List<string>();
                                    book.Authors.Add("-");
                                }

                                //Link kon sliki
                                Dictionary<string, object> imageLinks = volumeInfo["imageLinks"] as Dictionary<string, object>;
                                if (imageLinks.ContainsKey("small"))
                                    book.ImageSrc = imageLinks["small"].ToString();
                                else if (imageLinks.ContainsKey("medium"))
                                    book.ImageSrc = imageLinks["medium"].ToString();
                                else if (imageLinks.ContainsKey("large"))
                                    book.ImageSrc = imageLinks["large"].ToString();
                                else
                                    book.ImageSrc = "defaultImage.png";
                                if (imageLinks.ContainsKey("thumbnail"))
                                    book.ThumbnailSrc = imageLinks["thumbnail"].ToString();
                                else if (imageLinks.ContainsKey("smallThumbnail"))
                                    book.ThumbnailSrc = imageLinks["smallThumbnail"].ToString();
                                else
                                    book.ThumbnailSrc = "defaultThumb.png";

                                //-------------------------------------------------------------------------------
                                bookList.Add(book);
                            }

                        }
                        catch (Exception ex)
                        {
                            lblError.Text = ex.Message;
                            lblError.Visible = true;
                        }
                    }

                    if (bookList.Count > 0)
                    {
                        gvBooks.DataSource = bookList;
                        gvBooks.DataBind();

                        ViewState["BookList"] = bookList;

                        addAllBooks.Visible = true;
                        gvBooks.Visible = true;

                        bookField.Value = "";
                        bookIdsField.Value = "";
                    }

                }
            }
        }
예제 #4
0
        private void FillSearchContent(string search)
        {
            using (MySqlConnection connection = new MySqlConnection())
            {

                connection.ConnectionString = ConfigurationManager.ConnectionStrings["BooksConn"].ConnectionString;
                try
                {
                    connection.Open();

                    List<Book> books = new List<Book>();

                    string query = "";

                    MySqlCommand command = new MySqlCommand(query, connection);
                    MySqlDataReader reader;

                    bool categorySelected = false;
                    foreach (ListItem category in cblCategories.Items)
                    {
                        if (category.Selected)
                        {
                            query = "Select books.* from books, belongsto where "
                                + "books.idbook=belongsto.idbook and belongsto.idcategory=?IDCategory and "
                                + "(books.idbook in (select tagged.idbook from tagged,tags where tagged.idtag=tags.idtag and LOWER(tags.name) like ?Search) or"
                                + " books.idbook in (select wrote.idbook from authors, wrote where authors.idauthor=wrote.idauthor and LOWER(authors.name) like ?Search)"
                                + " or LOWER(books.name) like ?Search)";

                            categorySelected = true;
                            command.CommandText = query;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("?IDCategory", category.Value);
                            command.Parameters.AddWithValue("?Search",'%' + search.ToLower() + '%');
                            reader = command.ExecuteReader();

                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    Book book = new Book();
                                    book.IDBook = Int32.Parse(reader["IDBook"].ToString());
                                    book.ISBN = reader["ISBN"].ToString();
                                    book.Name = reader["Name"].ToString();
                                    book.Description = reader["Description"].ToString();
                                    book.Language = reader["Language"].ToString();
                                    book.ImageSrc = reader["CoverLink"].ToString();
                                    book.ThumbnailSrc = reader["Thumbnail"].ToString();
                                    book.YearPublished = reader["YearPublished"].ToString();
                                    book.DateAdded = reader["DateAdded"].ToString();
                                    book.SumRating = Int32.Parse(reader["SumRating"].ToString());
                                    book.NumVotes = Int32.Parse(reader["NumVotes"].ToString());

                                    bool flag = true;
                                    foreach (Book b in books)
                                    {
                                        if (b.IDBook == book.IDBook)
                                        {
                                            flag = false;
                                            break;
                                        }
                                    }
                                    if (flag)
                                        books.Add(book);
                                }
                                reader.Close();

                                foreach (Book book in books)
                                {
                                    query = "SELECT Name FROM Authors, Wrote WHERE Authors.IDAuthor=Wrote.IDAuthor AND Wrote.IDBook=?IDBook";

                                    command.CommandText = query;
                                    command.Parameters.Clear();
                                    command.Parameters.AddWithValue("?IDBook", book.IDBook);
                                    reader = command.ExecuteReader();

                                    if (reader.HasRows)
                                    {
                                        while (reader.Read())
                                        {
                                            if(!book.Authors.Contains(reader["Name"]))
                                                book.Authors.Add(reader["Name"].ToString());
                                        }
                                    }
                                    reader.Close();
                                }

                            }
                            reader.Close();
                        }
                    }

                    if ( ! categorySelected)
                    {
                        query = "Select distinct books.* from books where "
                        + "books.idbook in (select tagged.idbook from tagged,tags where tagged.idtag=tags.idtag and LOWER(tags.name) like ?Search) or"
                        + " books.idbook in (select wrote.idbook from authors, wrote where authors.idauthor=wrote.idauthor and LOWER(authors.name) like ?Search)"
                        + " or LOWER(books.name) like ?Search";

                        command.CommandText = query;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("?Search",'%' +  search.ToLower() + '%');
                        reader = command.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Book book = new Book();
                                book.IDBook = Int32.Parse(reader["IDBook"].ToString());
                                book.ISBN = reader["ISBN"].ToString();
                                book.Name = reader["Name"].ToString();
                                book.Description = reader["Description"].ToString();
                                book.Language = reader["Language"].ToString();
                                book.ImageSrc = reader["CoverLink"].ToString();
                                book.ThumbnailSrc = reader["Thumbnail"].ToString();
                                book.YearPublished = reader["YearPublished"].ToString();
                                book.DateAdded = reader["DateAdded"].ToString();
                                book.SumRating = Int32.Parse(reader["SumRating"].ToString());
                                book.NumVotes = Int32.Parse(reader["NumVotes"].ToString());

                                bool flag = true;
                                foreach (Book b in books)
                                {
                                    if (b.IDBook == book.IDBook)
                                    {
                                        flag = false;
                                        break;
                                    }
                                }
                                if (flag)
                                    books.Add(book);
                            }
                            reader.Close();

                            foreach (Book book  in books)
                            {
                                query = "SELECT Name FROM Authors, Wrote WHERE Authors.IDAuthor=Wrote.IDAuthor AND Wrote.IDBook=?IDBook";

                                command.CommandText = query;
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("?IDBook", book.IDBook);
                                reader = command.ExecuteReader();

                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        book.Authors.Add(reader["Name"].ToString());
                                    }
                                }
                                reader.Close();
                            }

                        }
                        reader.Close();
                    }

                    if (books.Count > 0)
                    {

                        StringBuilder innerHTML = new StringBuilder();

                        innerHTML.Append("<table class='tableSearch'>");
                        foreach (Book book in books)
                        {
                            innerHTML.Append("<tr>");
                            if (book.ThumbnailSrc.StartsWith(book.ISBN))
                                innerHTML.Append("<td><img src=/Images/BookPicture/" + book.ThumbnailSrc + " /></td>");
                            else
                                innerHTML.Append("<td><img src=" + book.ThumbnailSrc + " /></td>");
                            innerHTML.Append("<td>" + book.Name + "</td>");
                            innerHTML.Append("<td>" + book.YearPublished + "</td>");
                            if (book.NumVotes == 0)
                                innerHTML.Append("<td>0</td>");
                            else
                                innerHTML.Append("<td>" + (float)(book.SumRating / (book.NumVotes * 1.0)) + "</td>");
                            innerHTML.Append("<td style='display:none;' class='bookId'>" + book.IDBook + "</td>");

                            StringBuilder sb = new StringBuilder();
                            foreach (string author in book.Authors)
                            {
                                sb.Append(author.ToString() + "<br/>");
                            }
                            innerHTML.Append("<td>" + sb.ToString() + "</td>");
                            innerHTML.Append("</tr>");

                        }
                        innerHTML.Append("</table>");

                        searchList.InnerHtml = innerHTML.ToString();
                    }

                    else
                    {
                        searchList.InnerHtml = "<div class='searchItem'><span>Search result is empty. There are no matching books with '"+search.ToString()+"' in selected categories.</span></div>";
                    }

                }
                catch (Exception e)
                {
                    lblError.Text = e.Message + " " + e.InnerException;
                    lblError.Visible = true;
                }
                finally
                {
                    connection.Close();
                }
            }
        }