public async Task <TurnoPaginacaoViewtDto> PesquisarTurnosPorFiltrosPaginacaoAsync(TurnoFiltroDto filtroDto)
{
try
{
TurnoPaginacaoViewtDto paginacaoViewDto = new TurnoPaginacaoViewtDto();
filtroDto.RegistroInicial = filtroDto.RegistroInicial <= 1 ? 0 : filtroDto.RegistroInicial - 1;
string query = @"SELECT SQL_CALC_FOUND_ROWS DISTINCT ";
query = query + "tur.Id, " +
"tur.Inc_Turno, " +
"tur.Data_Hora_Inicio, " +
"tur.Data_Hora_Fim, " +
"tur.Status_Turno, " +
"tur.Usuario_Id, ";
query = query + "CONCAT(usu.UserName, ' - ', usu.NomeCompleto) as 'Descricao_Usuario' ";
query = query + "FROM Turno AS tur";
query = query + $" INNER JOIN usuario AS usu ON usu.Id = tur.Usuario_Id";
// WHERE
query = query + " WHERE 1 = 1 ";
if (!string.IsNullOrEmpty(filtroDto.DataHoraInicio) && !string.IsNullOrEmpty(filtroDto.DataHoraFim))
{
query = query + $"AND usu.DataHoraCadastro >= '{filtroDto.DataHoraInicio}' ";
query = query + $"AND usu.DataHoraCadastro <= '{filtroDto.DataHoraFim}' ";
}
if (!string.IsNullOrEmpty(filtroDto.UsuarioId))
{
query = query + $"AND tur.Usuario_Id = '{filtroDto.UsuarioId}' ";
}
if (!string.IsNullOrEmpty(filtroDto.StatusTurno))
{
query = query + $"AND tur.Status_Turno = {int.Parse(filtroDto.StatusTurno)} ";
}
query = query + "ORDER BY tur.Data_Hora_Inicio ";
// LIMIT RegistroInicial, QtdRegistroPorPagina;
query = query + $" LIMIT {filtroDto.RegistroInicial},{filtroDto.QtdRegistroPorPagina} ";
string queryTotalRegitrosEncontrados = "SELECT FOUND_ROWS() AS totalRegistros";
var connection = _context.Database.GetDbConnection();
using (var command = connection.CreateCommand())
{
await connection.OpenAsync();
command.CommandText = query;
using (var dataReader = await command.ExecuteReaderAsync())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
// Registros
TurnoTabelaViewDto viewDto = new TurnoTabelaViewDto();
viewDto.Id = Guid.Parse(dataReader["Id"].ToString());
viewDto.IncTurno = dataReader["Inc_Turno"].ToString();
viewDto.DataHoraInicio = dataReader["Data_Hora_Inicio"].ToString();
viewDto.DataHoraFim = dataReader["Data_Hora_Fim"].ToString();
viewDto.StatusTurno = dataReader["Status_Turno"].ToString();
viewDto.DescricaoStatusTurno = viewDto.descricaoStatusTurnoEnum(dataReader["Status_Turno"].ToString().ToUpper());
viewDto.UsuarioId = Guid.Parse(dataReader["Usuario_Id"].ToString());
viewDto.DescricaoUsuario = dataReader["Descricao_Usuario"].ToString();
paginacaoViewDto.ListaTurnoTabelaViewDto.Add(viewDto);
}
}
}
command.CommandText = queryTotalRegitrosEncontrados;
using (var dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
// Total Registros.: TotalRegistros
paginacaoViewDto.TotalRegistros = int.Parse(dataReader["totalRegistros"].ToString());;
}
}
}
}
paginacaoViewDto.preencherDadosPaginacao(filtroDto.QtdRegistroPorPagina, filtroDto.RegistroInicial);
return(paginacaoViewDto);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <TurnoPaginacaoViewtDto> PesquisarTurnosPorFiltrosPaginacaoAsync(TurnoFiltroDto filtroDto)
{
if (filtroDto == null)
{
Notificar("O filtro informado é inválido!");
return(null);
}
return(await _turnoRepository.PesquisarTurnosPorFiltrosPaginacaoAsync(filtroDto));
}