//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;
}
}
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'>‹</a>");
sb2.Append("<a href='#' class='jcarousel-control-next'>›</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();
}
}
}
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 = "";
}
}
}
}
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();
}
}
}