public async Task <(IEnumerable <Order>, int)> GetOrdersByUserIdAsync(string userId, PaginationFilterModel pagination)
{
var result = await _dbSet
.Include(orderItem => orderItem.OrderItems)
.ThenInclude(printingEdition => printingEdition.PrintingEdition)
.Where(order => order.UserId == userId)
.OrderByDescending(orderBy => orderBy.CreateDate)
.ToListAsync();
int countElement = result.Count();
result = result.Skip((pagination.PageNumber - Constants.Page.PAGE_NUMBER) * pagination.PageSize).Take(pagination.PageSize).ToList();
(IEnumerable <Order>, int)tupleResult = (result, countElement);
return(tupleResult);
}
public async Task <(IEnumerable <PrintingEdition>, int)> GetFilteredPrintingEditionsAsync(PrintingEditionFilterModel model, PaginationFilterModel pagination)
{
var result = await _dbSet
.Include(authorInPe => authorInPe.AuthorInPrintingEdition)
.ThenInclude(author => author.Author)
.Where(printingEdition => model.Category == null || model.Category.Contains(printingEdition.Type))
.Where(printingEdition => model.PrintingEditionName == null || EF.Functions.Like(printingEdition.Title, $"%{model.PrintingEditionName}%"))
.Where(printingEdition => (model.MinPrice == null || model.MaxPrice == null) || (printingEdition.Price >= model.MinPrice && printingEdition.Price <= model.MaxPrice))
.Where(printingEdition => model.AuthorName == null || printingEdition.AuthorInPrintingEdition
.Any(author => EF.Functions.Like(author.Author.Name, $"%{model.AuthorName}%")))
.OrderBy($"{model.SortBy} {model.TypeSort}")
.ToListAsync();
int countElement = result.Count();
result = result.Skip((pagination.PageNumber - Constants.Page.PAGE_NUMBER) * pagination.PageSize).Take(pagination.PageSize).ToList();
(IEnumerable <PrintingEdition>, int)tupleResult = (result, countElement);
return(tupleResult);
}
public async Task <(IEnumerable <Order>, int)> GetOrdersByUserIdAsync(string userId, PaginationFilterModel pagination)
{
using (var connection = CreateConnection())
{
var sql = new StringBuilder();
sql.Append($"SELECT o.*, pe.*, orderItem.Id, orderItem.Count FROM Orders AS o");
sql.Append($" INNER JOIN OrderItems AS orderItem on orderItem.OrderId = o.Id");
sql.Append($" INNER JOIN PrintingEditions AS pe on orderItem.PrintingEditionId = pe.Id");
sql.Append($" ORDER BY o.Id DESC");
var orders = await connection.QueryAsync <Order, PrintingEdition, OrderItem, Order>(sql.ToString(),
(order, printingEdition, orderItem) =>
{
orderItem.PrintingEdition = printingEdition;
order.OrderItems.Add(orderItem);
return(order);
},
splitOn : "Id");
var groupedOrders = orders.GroupBy(order => order.Id).Select(groupOrder =>
{
var groupedOrder = groupOrder.First();
groupedOrder.OrderItems = groupOrder.Select(order => order.OrderItems.FirstOrDefault()).ToList();;
return(groupedOrder);
});
int countElement = orders.Count();
groupedOrders = groupedOrders.Skip((pagination.PageNumber - Constants.Page.PAGE_NUMBER) * pagination.PageSize).Take(pagination.PageSize).ToList();
(IEnumerable <Order>, int)tupleResult = (groupedOrders, countElement);
return(tupleResult);
}
}
public async Task <(IEnumerable <Order>, int)> GetFilteredOrdersAsync(OrderFilterModel model, PaginationFilterModel pagination)
{
var result = await _dbSet.Include(user => user.User)
.Include(orderItem => orderItem.OrderItems)
.ThenInclude(printingEdition => printingEdition.PrintingEdition)
.Where(order => model.Status == null || order.Status == model.Status)
.OrderBy($"{model.SortBy} {model.TypeSort}")
.ToListAsync();
int countElement = result.Count();
result = result.Skip((pagination.PageNumber - Constants.Page.PAGE_NUMBER) * pagination.PageSize).Take(pagination.PageSize).ToList();
(IEnumerable <Order>, int)tupleResult = (result, countElement);
return(tupleResult);
}
public static int GetItemsToTake(this PaginationFilterModel paginationFilter)
{
return(paginationFilter.PageSize);
}
public async Task <(IEnumerable <Order>, int)> GetFilteredOrdersAsync(OrderFilterModel model, PaginationFilterModel pagination)
{
using (var connection = CreateConnection())
{
var status = new StringBuilder();
if (model.Status is not null)
{
status.Append((int)model.Status);
}
var sql = new StringBuilder();
sql.Append($"SELECT o.* , us.* , pe.*, orderItem.Id, orderItem.Count FROM Orders AS o");
sql.Append($" INNER JOIN AspNetUsers AS us on us.Id = o.UserId");
sql.Append($" INNER JOIN OrderItems AS orderItem on orderItem.OrderId = o.Id");
sql.Append($" INNER JOIN PrintingEditions AS pe on orderItem.PrintingEditionId = pe.Id");
sql.Append($" WHERE ((@status = '' OR @status = null) OR o.Status = @status)");
var orders = await connection.QueryAsync <Order, User, PrintingEdition, OrderItem, Order>(sql.ToString(),
(order, user, printingEdition, orderItem) =>
{
order.User = user;
orderItem.PrintingEdition = printingEdition;
order.OrderItems.Add(orderItem);
return(order);
},
new { status = status.ToString() },
splitOn : "Id");
var groupedOrders = orders.GroupBy(order => order.Id).Select(groupOrder =>
{
var groupedOrder = groupOrder.First();
groupedOrder.OrderItems = groupOrder.Select(order => order.OrderItems.Single()).ToList();
return(groupedOrder);
});
int countElement = orders.Count();
groupedOrders = groupedOrders.Skip((pagination.PageNumber - Constants.Page.PAGE_NUMBER) * pagination.PageSize).Take(pagination.PageSize).ToList();
(IEnumerable <Order>, int)tupleResult = (groupedOrders, countElement);
return(tupleResult);
}
}
public static int GetItemsToSkip(this PaginationFilterModel paginationFilter)
{
return((paginationFilter.PageNumber - 1) * paginationFilter.PageSize);
}
public async Task <(IEnumerable <User>, int)> GetFilteredUsersAsync(UserFilterModel model, PaginationFilterModel pagination)
{
var result = await _dbSet
.Where(user => model.UserName == null || EF.Functions.Like(user.FirstName, $"%{model.UserName}%") || EF.Functions.Like(user.LastName, $"%{model.UserName}%"))
.Where(user => model.IsBlocked == null || user.IsBlocked == model.IsBlocked)
.ToListAsync();
int countElement = result.Count();
result = result.Skip((pagination.PageNumber - Constants.Page.PAGE_NUMBER) * pagination.PageSize).Take(pagination.PageSize).ToList();
(IEnumerable <User>, int)tupleResult = (result, countElement);
return(tupleResult);
}
public async Task <PagedResponse <AuthorModel> > GetFilteredAuthorsAsync(AuthorFilterModel model, PaginationFilterModel pagination)
{
if (string.IsNullOrWhiteSpace(model.SortBy) && string.IsNullOrWhiteSpace(model.TypeSort))
{
model.SortBy = Constants.Sort.DefaultSortById;
model.TypeSort = Constants.Sort.DefaultSortByAsc;
}
(IEnumerable <Author> authors, int count)tupleAuthors = await _authorRepository.GetFilteredAuthorsAsync(model, pagination);
var authorModel = _autoMapper.Map <IEnumerable <AuthorModel> >(tupleAuthors.authors);
var pagedResponse = new PagedResponse <AuthorModel>
{
Data = authorModel,
PageNumber = pagination.PageNumber,
PageSize = pagination.PageSize,
TotalItems = tupleAuthors.count
};
return(pagedResponse);
}
public async Task <(IEnumerable <Author>, int)> GetFilteredAuthorsAsync(AuthorFilterModel model, PaginationFilterModel pagination)
{
var result = await _dbSet
.Include(pe => pe.AuthorInPrintingEdition)
.ThenInclude(u => u.PrintingEdition)
.OrderBy($"{model.SortBy} {model.TypeSort}")
.Where(author => model.Name == null || EF.Functions.Like(author.Name, $"%{model.Name}%"))
.ToListAsync();
int countElement = result.Count();
result = result.Skip((pagination.PageNumber - Constants.Page.PAGE_NUMBER) * pagination.PageSize).Take(pagination.PageSize).ToList();
var tupleResult = (result, countElement);
return(tupleResult);
}
public async Task <(IEnumerable <Author>, int)> GetFilteredAuthorsAsync(AuthorFilterModel model, PaginationFilterModel pagination)
{
using (var connection = CreateConnection())
{
var sql = new StringBuilder();
sql.Append("SELECT author.Id, author.Name, pe.*");
sql.Append($" FROM Authors as author");
sql.Append($" INNER JOIN AuthorInPrintingEditions as auInpe on author.Id = auInpe.AuthorId");
sql.Append($" INNER JOIN PrintingEditions as pe on pe.Id = auInpe.PrintingEditionId");
sql.Append($" WHERE ((@Name = '' OR @Name = null) OR Name LIKE @Name )");
var authors = await connection.QueryAsync <Author, PrintingEdition, Author>(sql.ToString(),
(author, printingEdition) =>
{
author.AuthorInPrintingEdition.Add(new AuthorInPrintingEdition {
PrintingEdition = printingEdition
});
return(author);
},
new { Name = $"%{model.Name}%" },
splitOn : "Id");
var groupedAuthors = authors.GroupBy(author => author.Id).Select(groupAuthor =>
{
var groupedAuthor = groupAuthor.First();
groupedAuthor.AuthorInPrintingEdition = groupAuthor.Select(author => author.AuthorInPrintingEdition.FirstOrDefault()).ToList();
return(groupedAuthor);
});
int countElement = authors.Count();
groupedAuthors = groupedAuthors.Skip((pagination.PageNumber - Constants.Page.PAGE_NUMBER) * pagination.PageSize).Take(pagination.PageSize).ToList();
(IEnumerable <Author>, int)tupleResult = (groupedAuthors, countElement);
return(tupleResult);
}
}
