public async Task <SearchableCollection <Book> > GetBooks(Uri identifier, BookFilters filters, int page, int pageSize = 10)
{
return(await this.context.Books.GetCollectionPage(
identifier,
entity => entity.BookTitle,
this.FilterBooks(filters),
page,
pageSize,
this.factory.CreateBook));
}
public async Task <SearchableCollection <Book> > GetBooks(Uri identifier, BookFilters filters, int page, int pageSize = 10)
{
this.factory.OnlyCoverImage = true;
var books = this.context.Books.Include(b => b.Images);
return(await books.GetCollectionPage <Book, BookEntity, SearchableCollection <Book> >(
identifier,
entities => entities.OrderByDescending(b => b.BookTitle),
this.FilterBooks(filters),
page,
pageSize,
this.factory.CreateBook));
}
public Task <List <Book> > GetFilteredBooks(string userId, BookFilters filters)
{
var books = _context.Books.Where(x => x.UserId == userId);
if (filters.PublicationYearSince != null)
{
books = books.Where(x => x.PublicationYear >= filters.PublicationYearSince);
}
if (filters.PublicationYearTo != null)
{
books = books.Where(x => x.PublicationYear <= filters.PublicationYearTo);
}
if (filters.Title != null)
{
books = books.Where(x => x.Title.Contains(filters.Title));
}
if (filters.Read)
{
books = books.Where(x => x.ReadingEnd <= DateTime.UtcNow);
}
else if (filters.CurrentlyReading)
{
books = books.Where(x => x.ReadingStart <= DateTime.UtcNow && x.ReadingEnd == null);
}
switch (filters.order)
{
case OrderByFiled.OrderByTitle:
books = books.OrderBy(x => x.Title);
break;
case OrderByFiled.OrderByTitleDesc:
books = books.OrderByDescending(x => x.Title);
break;
case OrderByFiled.OrderByYear:
books = books.OrderBy(x => x.PublicationYear);
break;
case OrderByFiled.OrderByYearDesc:
books = books.OrderByDescending(x => x.PublicationYear);
break;
default:
throw new ArgumentOutOfRangeException();
}
return(books.ToListAsync());
}
public async Task <ICollection <Book> > SearchAsync(BookFilters bookFilters, PageInfo pageInfo)
{
IQueryable <Book> searchItems = _context.Books;
searchItems = setFilters(searchItems, bookFilters);
searchItems = setOrderBy(searchItems, pageInfo);
return(await searchItems
.Skip(pageInfo.Page *pageInfo.Size)
.Take(pageInfo.Size)
.Include(book => book.Genre)
.Include(book => book.Offers).ThenInclude(offer => offer.Format)
.Include(book => book.Offers).ThenInclude(offer => offer.Language)
.ToListAsync());
}
public async Task <IActionResult> Find(PageInfo pageInfo, BookFilters filters)
{
ICollection <Book> foundItems;
if (filters == null)
{
foundItems = await bookService.FindAllAsync(pageInfo);
}
else
{
foundItems = await bookService.SearchAsync(filters, pageInfo);
}
return(new ObjectResult(foundItems));
}
private Func <IQueryable <BookEntity>, IQueryable <BookEntity> > FilterBooks(BookFilters filters)
{
return(books =>
{
if (string.IsNullOrWhiteSpace(filters.Title) == false)
{
books = books.Where(e => e.BookTitle.Contains(filters.Title.Trim()));
}
if (string.IsNullOrWhiteSpace(filters.Author) == false)
{
books = books.Where(e => e.BookAuthor.Contains(filters.Author.Trim()));
}
if (string.IsNullOrWhiteSpace(filters.Language) == false)
{
books = books.Where(b => b.Languages.Contains(filters.Language));
}
return books;
});
}
public async Task <IActionResult> GetFilteredBooks(int yearSince, int yearTo, bool read, bool currentlyReading, int order, string title)
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
var filters = new BookFilters()
{
PublicationYearSince = yearSince,
PublicationYearTo = yearTo,
Read = read,
CurrentlyReading = currentlyReading,
Title = title,
order = (OrderByFiled)order
};
var user = await GetCurrentUser();
var books = await _booksService.GetFilteredBooks(user.Id, filters);
return(Ok(AutoMapper.Mapper.Map <List <BookResponse> >(books)));
}
private static IQueryable <Book> setFilters(IQueryable <Book> searchItems, BookFilters bookFilters)
{
if (bookFilters.searchString != null)
{
searchItems = searchItems.Where(book => book.Title.Contains(bookFilters.searchString));
}
if (bookFilters.Genres.Count > 0)
{
searchItems = searchItems.Where(book => bookFilters.Genres.Contains(book.Genre.Id));
}
if (bookFilters.Formats.Count > 0)
{
searchItems = searchItems.Where(book => book.Offers.Any(offer => bookFilters.Formats.Contains(offer.Format.Id)));
}
if (bookFilters.Languages.Count > 0)
{
searchItems = searchItems.Where(book => book.Offers.Any(offer => bookFilters.Languages.Contains(offer.Language.Id)));
}
return(searchItems);
}
public BookFiltersBuilder()
{
_bookFilters = new BookFilters();
}
public IActionResult Filter([FromBody] BookFilters filters)
{
IActionResult result = null;
try
{
using (MySqlConnection ctx = new MySqlConnection(ConnectionString.GetConnectionString()))
{
ctx.Open();
string filterBooksQuery = $@"
SELECT
{Book.ColumnNames.Id},
{Book.ColumnNames.WorkType},
{Book.ColumnNames.BookAuthors},
{Book.ColumnNames.ChapAuthors},
{Book.ColumnNames.Editors},
{Book.ColumnNames.BookTitle},
{Book.ColumnNames.ChapTitle},
{Book.ColumnNames.FirstPage},
{Book.ColumnNames.LastPage},
{Book.ColumnNames.Editorial},
{Book.ColumnNames.City},
{Book.ColumnNames.Country},
{Book.ColumnNames.Year},
{Book.ColumnNames.Isbn},
{Book.ColumnNames.Report}
FROM books
WHERE
MATCH(
{Book.ColumnNames.WorkType},
{Book.ColumnNames.BookAuthors},
{Book.ColumnNames.ChapAuthors},
{Book.ColumnNames.Editors},
{Book.ColumnNames.BookTitle},
{Book.ColumnNames.ChapTitle},
{Book.ColumnNames.Editorial},
{Book.ColumnNames.City},
{Book.ColumnNames.Country},
{Book.ColumnNames.Year},
{Book.ColumnNames.Isbn},
{Book.ColumnNames.Report}
)
AGAINST(""{filters.Text}*"" IN BOOLEAN MODE)
";
List <Book> books = FilterBooks(filterBooksQuery, ctx);
string filterAwardsQuery = $@"
SELECT
{Award.ColumnNames.Id},
{Award.ColumnNames.Name},
{Award.ColumnNames.Awardee},
{Award.ColumnNames.Contribution},
{Award.ColumnNames.Institution},
{Award.ColumnNames.Country},
{Award.ColumnNames.Year},
{Award.ColumnNames.Report}
FROM awards
WHERE
MATCH(
{Award.ColumnNames.Name},
{Award.ColumnNames.Awardee},
{Award.ColumnNames.Contribution},
{Award.ColumnNames.Institution},
{Award.ColumnNames.Country},
{Award.ColumnNames.Year},
{Award.ColumnNames.Report}
)
AGAINST(""{filters.Text}*"" IN BOOLEAN MODE)
";
List <Award> awards = FilterAwards(filterAwardsQuery, ctx);
string filterParticipationsQuery = $@"
SELECT
{Participation.ColumnNames.Id},
{Participation.ColumnNames.Type},
{Participation.ColumnNames.NameEvent},
{Participation.ColumnNames.Country},
{Participation.ColumnNames.City},
{Participation.ColumnNames.Modality},
{Participation.ColumnNames.Title},
{Participation.ColumnNames.CapesPersons},
{Participation.ColumnNames.Report}
FROM participation
WHERE
MATCH(
{Participation.ColumnNames.Type},
{Participation.ColumnNames.NameEvent},
{Participation.ColumnNames.Country},
{Participation.ColumnNames.City},
{Participation.ColumnNames.Modality},
{Participation.ColumnNames.Title},
{Participation.ColumnNames.CapesPersons},
{Participation.ColumnNames.Report}
)
AGAINST(""{filters.Text}*"" IN BOOLEAN MODE)
";
List <Participation> participations = FilterParticipations(filterParticipationsQuery, ctx);
string filterPublicationsQuery = $@"
SELECT
{Publication.ColumnNames.Id},
{Publication.ColumnNames.Doi},
{Publication.ColumnNames.Authors},
{Publication.ColumnNames.ArticleTitle},
{Publication.ColumnNames.JournalName},
{Publication.ColumnNames.Volume},
{Publication.ColumnNames.Year},
{Publication.ColumnNames.FirstPage},
{Publication.ColumnNames.LastPage},
{Publication.ColumnNames.Notes},
{Publication.ColumnNames.Report}
FROM public
WHERE
MATCH(
{Publication.ColumnNames.Doi},
{Publication.ColumnNames.Authors},
{Publication.ColumnNames.ArticleTitle},
{Publication.ColumnNames.JournalName},
{Publication.ColumnNames.Volume},
{Publication.ColumnNames.Year},
{Publication.ColumnNames.FirstPage},
{Publication.ColumnNames.LastPage},
{Publication.ColumnNames.Notes},
{Publication.ColumnNames.Report}
)
AGAINST(""{filters.Text}*"" IN BOOLEAN MODE)
";
List <Publication> publications = FilterPublications(filterPublicationsQuery, ctx);
FilterResult search = new FilterResult()
{
Books = books,
Awards = awards,
Participations = participations,
Publications = publications
};
result = Ok(search);
ctx.Close();
}
}
catch (Exception ex)
{
result = BadRequest(new { message = ex.Message });
}
return(result);
}
public async Task <IActionResult> GetBooksAsync([FromQuery] BookFilters filters)
{
var bookEntities = await _bookRepository.GetBooks(filters);
return(Ok(_mapper.Map <IEnumerable <BookDto> >(bookEntities)));
}
public async Task <IEnumerable <Book> > GetBooks(BookFilters filters)
{
var bookEntities = await _context.Books.Include(b => b.Category).ToListAsync();
var libraryEntities = await _context.Libraries.ToListAsync();
var libraryEntitieId = new List <int>();
var filteredBooks = new List <Book>();
if (filters.office != null)
{
libraryEntitieId = libraryEntities
.Where(lib => lib.OfficeId == filters.office)
.Select(lib => lib.BookId)
.ToList();
foreach (var bookId in libraryEntitieId)
{
filteredBooks.Add(await _context.Books.Where(book => book.Id == bookId).Include(book => book.Category).FirstOrDefaultAsync());
}
}
if (filters.category != null)
{
if (filteredBooks.Count > 0 && !filters.category.Contains(1))
{
var returnList = new List <Book>();
foreach (var category in filters.category)
{
var filteredByCategories = filteredBooks.Where(book => book.Category.Id == category).ToList();
foreach (var book in filteredByCategories)
{
if (!returnList.Contains(book))
{
returnList.Add(book);
}
}
}
filteredBooks = returnList;
}
else if (!filters.category.Contains(1))
{
var returnList = new List <Book>();
foreach (var category in filters.category)
{
var filteredByCategories = bookEntities.Where(book => book.Category.Id == category).ToList();
foreach (var book in filteredByCategories)
{
if (!returnList.Contains(book))
{
returnList.Add(book);
}
}
}
bookEntities = returnList;
}
}
if (filters.available != null)
{
if (filteredBooks.Count > 0)
{
filteredBooks = filteredBooks.Where(book => book.IsAvailable == filters.available).ToList();
}
else
{
bookEntities = bookEntities.Where(b => b.IsAvailable == filters.available).ToList();
}
}
return(filteredBooks.Count > 0 ? filteredBooks : bookEntities);
}
/// <inheritdoc />
public Task <SearchableCollection <Book> > GetBooks(Uri identifier, BookFilters filters, int page, int pageSize = 10)
{
return(this.GetAll <Book, SearchableCollection <Book> >(identifier, page, pageSize));
}
