public List <DashboardModel> getListBook(string titleBook, int userSession) { List <DashboardModel> books = new List <DashboardModel>(); string sql = $"select b.idBook as idBook, namebook, author, PublishedDate, QuantityPage, googleID, " + $"idReading, DateRead, Rating, Review, CanPublishIt " + $"from books b left join UserBook us on us.idBook = b.idBook " + $"where b.namebook like '%{titleBook}%'"; ConnectionDB connection = new ConnectionDB(); DataTable dtBook = new DataTable(); dtBook = connection.executeSelect(sql); for (int i = 0; i < dtBook.Rows.Count; i++) { DashboardModel book = new DashboardModel(); book.Book.IdBook = int.Parse(dtBook.Rows[i]["idBook"].ToString()); book.Book.NameBook = dtBook.Rows[i]["namebook"].ToString(); book.Book.Author = dtBook.Rows[i]["author"].ToString(); book.Book.PublishedData = DateTime.Parse(dtBook.Rows[i]["PublishedDate"].ToString()); book.Book.QuantityPage = int.Parse(dtBook.Rows[i]["QuantityPage"].ToString()); book.Book.GoogleId = dtBook.Rows[i]["googleID"].ToString(); int idBookUser = book.UserBook.hasBookWriteByUserId(userSession, book.Book.IdBook); if (idBookUser == book.Book.IdBook) { if (dtBook.Rows[i]["idReading"].ToString() == "0") { book.UserBook.IsReading = false; } else { book.UserBook.IsReading = true; } book.UserBook.DateRead = DateTime.Parse(dtBook.Rows[i]["DateRead"].ToString()); book.UserBook.Rating = int.Parse(dtBook.Rows[i]["Rating"].ToString()); book.UserBook.Review = dtBook.Rows[i]["Review"].ToString(); if (dtBook.Rows[i]["CanPublishIt"].ToString() == "0") { book.UserBook.CanPublishIt = false; } else { book.UserBook.CanPublishIt = true; } } books.Add(book); } connection.closeConnection(); return(books); }
public DashboardModel getUserBook(int IdUserBook, int userId) { string sql = $"select b.idBook as idBook, namebook, author, PublishedDate, QuantityPage, googleID, " + $"idReading, DateRead, Rating, Review, CanPublishIt " + $"from books b left join UserBook us on us.idBook = b.idBook " + $"where us.idUserBook = {IdUserBook}"; ConnectionDB connection = new ConnectionDB(); DataTable dtBook = connection.executeSelect(sql); DashboardModel book = new DashboardModel(); book.Book.IdBook = int.Parse(dtBook.Rows[0]["idBook"].ToString()); book.Book.NameBook = dtBook.Rows[0]["namebook"].ToString(); book.Book.Author = dtBook.Rows[0]["author"].ToString(); book.Book.PublishedData = DateTime.Parse(dtBook.Rows[0]["PublishedDate"].ToString()); book.Book.QuantityPage = int.Parse(dtBook.Rows[0]["QuantityPage"].ToString()); book.Book.GoogleId = dtBook.Rows[0]["googleID"].ToString(); int idBookUser = book.UserBook.hasBookWriteByUserId(userId, book.Book.IdBook); if (idBookUser == book.Book.IdBook) { if (dtBook.Rows[0]["idReading"].ToString() == "0") { book.UserBook.IsReading = false; } else { book.UserBook.IsReading = true; } book.UserBook.DateRead = DateTime.Parse(dtBook.Rows[0]["DateRead"].ToString()); book.UserBook.Rating = int.Parse(dtBook.Rows[0]["Rating"].ToString()); book.UserBook.Review = dtBook.Rows[0]["Review"].ToString(); if (dtBook.Rows[0]["CanPublishIt"].ToString() == "0") { book.UserBook.CanPublishIt = false; } else { book.UserBook.CanPublishIt = true; } } return(book); }
public List <DashboardModel> getReviewsForIndex(int limit, string search) { List <DashboardModel> books = new List <DashboardModel>(); string sql = "select u.userName, u.picture, b.namebook, us.Review from UserBook us " + "inner join books b on b.idBook = us.idBook inner join user u on u.userId = us.idUser " + $"where us.canPublishIt = 1 and b.namebook like '%{search}%' " + "order by us.idUserBook desc " + $"LIMIT {limit}"; ConnectionDB connection = new ConnectionDB(); DataTable dtBook = new DataTable(); dtBook = connection.executeSelect(sql); for (int i = 0; i < dtBook.Rows.Count; i++) { DashboardModel book = new DashboardModel(); book.User.UserName = dtBook.Rows[i]["userName"].ToString(); book.User.UrlPicture = dtBook.Rows[i]["picture"].ToString(); book.Book.NameBook = dtBook.Rows[i]["namebook"].ToString(); book.UserBook.Review = dtBook.Rows[i]["Review"].ToString(); books.Add(book); } return(books); }
public List <DashboardModel> getListUserBook(int userId, int quantityPage, int currentPage, string search) { List <DashboardModel> books = new List <DashboardModel>(); string sql = $"select b.idBook as idBook, namebook, author, PublishedDate, QuantityPage, googleID, " + $"idReading, DateRead, Rating, Review, CanPublishIt " + $"from books b left join UserBook us on us.idBook = b.idBook " + $"where us.idUser = {userId} "; //if it has something at variable search if (search != "") { sql += $"and b.nameBook like '%{search}%' or b.Author like '%{search}%'"; } //ajust pagagination int offset = 0; if (currentPage > 1) { offset = (quantityPage * (currentPage - 1)); } sql += $" limit {quantityPage} offset {offset}"; ConnectionDB connection = new ConnectionDB(); DataTable dtBook = new DataTable(); dtBook = connection.executeSelect(sql); for (int i = 0; i < dtBook.Rows.Count; i++) { DashboardModel book = new DashboardModel(); book.Book.IdBook = int.Parse(dtBook.Rows[i]["idBook"].ToString()); book.Book.NameBook = dtBook.Rows[i]["namebook"].ToString(); book.Book.Author = dtBook.Rows[i]["author"].ToString(); book.Book.PublishedData = DateTime.Parse(dtBook.Rows[i]["PublishedDate"].ToString()); book.Book.QuantityPage = int.Parse(dtBook.Rows[i]["QuantityPage"].ToString()); book.Book.GoogleId = dtBook.Rows[i]["googleID"].ToString(); int idBookUser = book.UserBook.hasBookWriteByUserId(userId, book.Book.IdBook); if (idBookUser == book.Book.IdBook) { if (dtBook.Rows[i]["idReading"].ToString() == "0") { book.UserBook.IsReading = false; } else { book.UserBook.IsReading = true; } book.UserBook.DateRead = DateTime.Parse(dtBook.Rows[i]["DateRead"].ToString()); book.UserBook.Rating = int.Parse(dtBook.Rows[i]["Rating"].ToString()); book.UserBook.Review = dtBook.Rows[i]["Review"].ToString(); if (dtBook.Rows[i]["CanPublishIt"].ToString() == "0") { book.UserBook.CanPublishIt = false; } else { book.UserBook.CanPublishIt = true; } } books.Add(book); } return(books); }