private static string GetSortByQuery(BookSortByType sortBy)
{
switch (sortBy)
{
case BookSortByType.DateCreated:
return("DateAdded");
case BookSortByType.seriesIndex:
return("seriesIndex");
default:
return("Title");
}
}
public async Task <IActionResult> GetBooks(int libraryId,
string query,
int pageNumber = 1,
int pageSize = 10,
[FromQuery] int?authorId = null,
[FromQuery] int?categoryId = null,
[FromQuery] int?seriesId = null,
[FromQuery] bool?favorite = null,
[FromQuery] bool?read = null,
[FromQuery] BookStatuses status = BookStatuses.Published,
[FromQuery] BookSortByType sortBy = BookSortByType.Title,
[FromQuery] SortDirection sortDirection = SortDirection.Ascending,
CancellationToken token = default(CancellationToken))
{
var filter = new BookFilter
{
AuthorId = authorId,
CategoryId = categoryId,
SeriesId = seriesId,
Favorite = favorite,
Read = read,
Status = status
};
var request = new GetBooksQuery(libraryId, pageNumber, pageSize, _userHelper.Account?.Id)
{
Query = query,
Filter = filter,
SortBy = sortBy,
SortDirection = sortDirection
};
var books = await _queryProcessor.ExecuteAsync(request, cancellationToken : token);
var args = new PageRendererArgs <BookModel, BookFilter>
{
Page = books,
RouteArguments = new PagedRouteArgs
{
PageNumber = pageNumber,
PageSize = pageSize,
Query = query,
SortBy = sortBy,
SortDirection = sortDirection
},
Filters = filter,
};
return(new OkObjectResult(_bookRenderer.Render(args, libraryId)));
}
public async Task <Page <BookModel> > GetBooksByUser(int libraryId, int accountId, int pageNumber, int pageSize, BookStatuses status, BookSortByType sortBy, SortDirection direction, CancellationToken cancellationToken)
{
using (var connection = _connectionProvider.GetConnection())
{
var sortByQuery = $"b.{GetSortByQuery(sortBy)}";
var sortDirection = direction == SortDirection.Descending ? "DESC" : "ASC";
var assignmentfilter = string.Empty;
if (status == BookStatuses.BeingTyped)
{
assignmentfilter = "AND bp.WriterAccountId = @AccountId";
}
else if (status == BookStatuses.ProofRead)
{
assignmentfilter = "AND bp.ReviewerAccountId = @AccountId";
}
var param = new
{
LibraryId = libraryId,
PageSize = pageSize,
PageNumber = pageNumber,
AccountId = accountId,
StatusFilter = status
};
var sql = @"Select b.Id
From Book b
INNER Join BookPage bp on bp.BookId = b.Id
Where b.LibraryId = @LibraryId
AND b.Status = @StatusFilter " +
assignmentfilter +
$" ORDER BY {sortByQuery} {sortDirection} " +
@"OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY";
var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
var bookIds = await connection.QueryAsync(command);
var sqlCount = @"Select COUNT(b.Id)
From Book b
INNER Join BookPage bp on bp.BookId = b.Id
Where b.LibraryId = @LibraryId
AND b.Status = @StatusFilter " +
assignmentfilter;
var bookCount = await connection.QuerySingleAsync <int>(new CommandDefinition(sqlCount, param, cancellationToken : cancellationToken));
var books = await GetBooks(connection, libraryId, bookIds.Select(b => (int)b.Id).ToList(), cancellationToken);
return(new Page <BookModel>
{
PageNumber = pageNumber,
PageSize = pageSize,
TotalCount = bookCount,
Data = books
});
}
}
public async Task <Page <BookModel> > SearchBooks(int libraryId, string searchText, int pageNumber, int pageSize, int?AccountId, BookFilter filter, BookSortByType sortBy, SortDirection direction, CancellationToken cancellationToken)
{
using (var connection = _connectionProvider.GetConnection())
{
var sortByQuery = $"b.{GetSortByQuery(sortBy)}";
var sortDirection = direction == SortDirection.Descending ? "DESC" : "ASC";
var param = new
{
LibraryId = libraryId,
Query = $"%{searchText}%",
PageSize = pageSize,
PageNumber = pageNumber,
AccountId = AccountId,
AuthorFilter = filter.AuthorId,
SeriesFilter = filter.SeriesId,
CategoryFilter = filter.CategoryId,
FavoriteFilter = filter.Favorite,
RecentFilter = filter.Read,
StatusFilter = filter.Status
};
var sql = @"Select b.Id, b.Title, b.seriesIndex, b.DateAdded
From Book b
LEFT JOIN Series s On b.SeriesId = s.id
LEFT JOIN FavoriteBooks f On b.Id = f.BookId
INNER JOIN BookAuthor ba ON b.Id = ba.BookId
INNER JOIN Author a On ba.AuthorId = a.Id
LEFT JOIN BookCategory bc ON b.Id = bc.BookId
LEFT JOIN Category c ON bc.CategoryId = c.Id
LEFT JOIN FavoriteBooks fb On fb.BookId = b.Id
LEFT JOIN RecentBooks r On b.Id = r.BookId
Where b.LibraryId = @LibraryId
AND b.Title Like @Query
AND (@AccountId IS NOT NULL OR b.IsPublic = 1)
AND (b.Status = @StatusFilter OR @StatusFilter IS NULL)
AND (ba.AuthorId = @AuthorFilter OR @AuthorFilter IS NULL)
AND (s.Id = @SeriesFilter OR @SeriesFilter IS NULL)
AND (f.AccountId = @AccountId OR @FavoriteFilter IS NULL)
AND (r.AccountId = @AccountId OR @RecentFilter IS NULL)
AND (bc.CategoryId = @CategoryFilter OR @CategoryFilter IS NULL)
GROUP BY b.Id, b.Title, b.seriesIndex, b.DateAdded " +
$" ORDER BY {sortByQuery} {sortDirection} " +
@"OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY";
var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
var bookIds = await connection.QueryAsync(command);
var sqlCount = @"SELECT Count(*) FROM (Select b.Id
From Book b
INNER JOIN BookAuthor ba ON b.Id = ba.BookId
INNER JOIN Author a On ba.AuthorId = a.Id
LEFT OUTER JOIN Series s On b.SeriesId = s.id
LEFT OUTER JOIN FavoriteBooks f On b.Id = f.BookId
LEFT OUTER JOIN BookCategory bc ON b.Id = bc.BookId
LEFT OUTER JOIN Category c ON bc.CategoryId = c.Id
LEFT OUTER JOIN FavoriteBooks fb On fb.BookId = b.Id
LEFT OUTER JOIN RecentBooks r On b.Id = r.BookId
Where b.LibraryId = @LibraryId
AND b.Title Like @Query
AND (@AccountId IS NOT NULL OR b.IsPublic = 1)
AND (b.Status = @StatusFilter OR @StatusFilter IS NULL)
AND (ba.AuthorId = @AuthorFilter OR @AuthorFilter IS NULL)
AND (s.Id = @SeriesFilter OR @SeriesFilter IS NULL)
AND (f.AccountId = @AccountId OR @FavoriteFilter IS NULL)
AND (r.AccountId = @AccountId OR @RecentFilter IS NULL)
AND (bc.CategoryId = @CategoryFilter OR @CategoryFilter IS NULL)
GROUP BY b.Id) AS bkcnt";
var bookCount = await connection.QuerySingleAsync <int>(new CommandDefinition(sqlCount, param, cancellationToken : cancellationToken));
var books = await GetBooks(connection, libraryId, bookIds.Select(b => (int)b.Id).ToList(), cancellationToken);
return(new Page <BookModel>
{
PageNumber = pageNumber,
PageSize = pageSize,
TotalCount = bookCount,
Data = books
});
}
}
