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);
}
