public async Task <PagedView <CustomerDto> > GetPagedCustomers
(string name, string cpf, string email, int page, int pageSize)
{
int totalItems = 0;
PagedView <CustomerDto> result = new PagedView <CustomerDto>();
var offset = QueryCommon.OffSet(page, pageSize);
string sql = @"SELECT FirstName + ' ' + LastName As Name,
Email,
Phone
FROM dbo.CUSTOMER
WHERE (Cpf = @Cpf OR ISNULL(@Cpf,'') = '')
AND
(Email = @Email OR ISNULL(@Email,'') = '')
AND
((FirstName like @FirstName OR ISNULL(@FirstName,'') = '')
OR
(LastName like @LastName OR ISNULL(@LastName,'') = ''))
";
string sqlPaged = QueryCommon.PagedQuery(orderBy: "Name", sql, offset, pageSize);
string sqlTotalCount = QueryCommon.TotalItemsQuery(sql);
var customers = await _database.GetConnection()
.QueryAsync <CustomerDto>(sqlPaged,
new
{
Cpf = cpf ?? string.Empty,
Email = email ?? string.Empty,
FirstName = $"%{name}%" ?? $"%{string.Empty}%",
LastName = $"%{name}%" ?? $"%{string.Empty}%"
});
totalItems = await _database.GetConnection()
.ExecuteScalarAsync <int>(sqlTotalCount,
new
{
Cpf = cpf ?? string.Empty,
Email = email ?? string.Empty,
FirstName = name ?? string.Empty,
LastName = name ?? string.Empty
});
result.Data = customers.ToList();
result.Page = page;
result.PageSize = pageSize;
result.TotalPages = totalItems / pageSize + (totalItems % pageSize == 0 ? 0 : 1);
result.TotalItems = totalItems;
return(result);
}