public async Task <IReadOnlyList <ClienteVO> > Get(ClienteFilterVO filter)
{
try
{
var parameters = new { COD_EMPRESA = filter.COD_EMPRESA, NOME = filter.NOME, CPF = filter.CPF, CIDADE = filter.CIDADE, ESTADO = filter.ESTADO };
var query = @"SELECT DISTINCT C.ID, C.COD_EMPRESA, C.NOME, C.CPF, C.RG, C.DATA_NASCIMENTO, C.TELEFONE, C.EMAIL_, CI.NOME as CIDADE, CI.ESTADO as ESTADO FROM TB_CLIENTE C
LEFT JOIN TB_ENDERECO E ON(E.TB_CLIENTE_ID = C.ID)
LEFT JOIN TB_CIDADE CI ON(E.TB_CIDADE_ID = CI.ID)
where
C.COD_EMPRESA = @COD_EMPRESA and (@NOME IS NULL OR C.NOME LIKE CONCAT('%',@NOME,'%')) and (@CPF IS NULL OR C.CPF = @CPF) and (@CIDADE IS NULL OR CI.NOME LIKE CONCAT('%',@CIDADE,'%')) and (@ESTADO IS NULL OR CI.ESTADO = @ESTADO) ";
return(await _readDbConnection.QueryAsync <ClienteVO>(query, parameters));
}
catch (Exception ex)
{
_logger.LogInformation(string.Format("Erro ao tentar buscar clientes por filtro. Mensagem de Erro = {0}", ex.Message));
return(null);
}
}
public async Task <List <ContactDto> > Handle(GetContactsQuery request, CancellationToken cancellationToken)
{
List <Contact> results = new List <Contact>();
if (request.TagId != default)
{
results = await _context.Contacts
.Include(contact => contact.Tags)
.Where(contact => contact.Tags.Any(tag => tag.Id == request.TagId))
.ToListAsync();
}
else if (!string.IsNullOrWhiteSpace(request.SearchQuery))
{
var contacts = new Dictionary <Guid, Contact>();
await _readDbConnection
.QueryAsync <Contact, Tag, Contact>("EXECUTE dbo.GetContacts @searchQuery", (contact, tag) =>
{
Contact contactEntity = contact;
if (!contacts.TryGetValue(contact.Id, out contactEntity))
{
contacts.Add(contact.Id, contact);
contactEntity = contact;
}
if (tag != null)
{
contactEntity.Tags.Add(tag);
}
return(contactEntity);
}, new { searchQuery = request.SearchQuery });
results = contacts.Values.ToList();
}
return(results.AsQueryable().ProjectTo <ContactDto>(_mapper.ConfigurationProvider)
.OrderBy(contact => contact.CompanyName)
.ThenBy(contact => contact.Surname)
.ToList());
}
public async Task <List <ContactAutoCompleteDto> > Handle(GetContactAutoCompleteQuery request, CancellationToken cancellationToken)
{
return((await _readDbConnection.QueryAsync <ContactAutoCompleteDto>("EXECUTE dbo.GetContactAutoComplete @searchQuery", new { request.SearchQuery }))
.OrderBy(autocomplete => autocomplete.Suggestion)
.ToList());
}