public async Task <ObterCipaQueryResult> Handle(ObterCipaQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
*
from
[{cipasQueryContext.DatabaseName}]..[Cipas] with(nolock)
where
[Id] = @cipaId";
var queryResult = (await cipasQueryContext.QueryAsync <ObterCipaQueryResult>(query, new
{
request.CipaId
})).FirstOrDefault();
if (queryResult != null)
{
query = $@"
select
[Id] MembroId, [UserName], [NomeCompleto], [Funcao]
from
[{cipasQueryContext.DatabaseName}]..[Membros] with(nolock)
where
[CipaId] = @cipaId
order by
[UserName]";
queryResult.Membros = await cipasQueryContext.QueryAsync <ObterCipaQueryResult.ObterCipaQueryResultInnerMembro>(query, new
{
request.CipaId
});
}
return(queryResult);
}
public async Task <ObterCipasAtivasDoUsuarioQueryResult> Handle(ObterCipasAtivasDoUsuarioQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
c.[Id], c.[Codigo], c.[Mandato_Inicio], c.[Mandato_Termino], c.[CodigoEmpresa]
from
[{cipasQueryContext.DatabaseName}]..[Cipas] c with(nolock)
inner join
[{cipasQueryContext.DatabaseName}]..[Membros] m with(nolock)
on c.[Id] = m.[CipaId]
where
c.[Status] = @status and m.[UserName] = @userName
order by
c.[Codigo]";
var queryResult = new ObterCipasAtivasDoUsuarioQueryResult()
{
Cipas = await cipasQueryContext.QueryAsync <ObterCipasAtivasDoUsuarioQueryResult.ObterCipasAtivasDoUsuarioQueryResultInnerCipa>(query, new
{
status = StatusAtividade.Ativo,
request.UserName
})
};
return(queryResult);
}
public async Task <ObterReunioesAgendadasDoUsuarioQueryResult> Handle(ObterReunioesAgendadasDoUsuarioQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
r.[Id], r.[CipaId], r.[MesDeReferencia], r.[DataPrevista], r.[Local], r.[Status], r.[Extraordinaria]
from
[{cipasQueryContext.DatabaseName}]..[Reunioes] r with(nolock)
inner join
[{cipasQueryContext.DatabaseName}]..[Cipas] c with(nolock)
on r.[CipaId] = c.[Id]
inner join
[{cipasQueryContext.DatabaseName}]..[Membros] m with(nolock)
on c.[Id] = m.[CipaId]
where
r.[Status] not in @notStatusReuniao and c.[Status] = @statusCipa and m.[UserName] = @userName
order by
r.[DataPrevista]" ;
var queryResult = new ObterReunioesAgendadasDoUsuarioQueryResult()
{
Reunioes = await cipasQueryContext.QueryAsync <ObterReunioesAgendadasDoUsuarioQueryResult.ObterReunioesAgendadasDoUsuarioQueryResultInnerReuniao>(query, new
{
notStatusReuniao = new[] { StatusAgendamento.Realizado, StatusAgendamento.Cancelado },
statusCipa = StatusAtividade.Ativo,
request.UserName
})
};
return(queryResult);
}
public async Task <ObterEstabelecimentosDaCipaQueryResult> Handle(ObterEstabelecimentosDaCipaQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
eo.*, ec.[Id] RelId, ec.[Tipo]
from
[{cipasQueryContext.DatabaseName}]..[Estabelecimentos] ec with(nolock)
inner join
[{cipasQueryContext.OrganizacoesDatabaseName}]..[Estabelecimentos] eo with(nolock)
on ec.[EstabelecimentoId] = eo.[Id]
where
ec.[CipaId] = @cipaId
order by
eo.[Codigo]" ;
var queryResult = new ObterEstabelecimentosDaCipaQueryResult()
{
Estabelecimentos = await cipasQueryContext.QueryAsync <ObterEstabelecimentosDaCipaQueryResult.ObterEstabelecimentosDaCipaQueryResultInnerEstabelecimento>(query, new
{
request.CipaId
})
};
return(queryResult);
}
public async Task <ObterConsentsDoAusenteQueryResult> Handle(ObterConsentsDoAusenteQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
*
from
[{cipasQueryContext.DatabaseName}]..[ConsentsDeAusentes] with(nolock)
where
[AusenteId] = @ausenteId
order by
[CreationDate] desc" ;
var queryResult = new ObterConsentsDoAusenteQueryResult()
{
Consents = await cipasQueryContext.QueryAsync <ObterConsentsDoAusenteQueryResult.ObterConsentsDoAusenteQueryResultInnerConsent>(query, new
{
request.AusenteId
})
};
return(queryResult);
}
public async Task <ObterAlteracoesDoAssuntoQueryResult> Handle(ObterAlteracoesDoAssuntoQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
*
from
[{cipasQueryContext.DatabaseName}]..[Alteracoes] with(nolock)
where
[AssuntoId] = @assuntoId
order by
[CreationDate] desc" ;
var queryResult = new ObterAlteracoesDoAssuntoQueryResult()
{
Alteracoes = await cipasQueryContext.QueryAsync <ObterAlteracoesDoAssuntoQueryResult.ObterAlteracoesDoAssuntoQueryResultInnerAlteracao>(query, new
{
request.AssuntoId
})
};
return(queryResult);
}
public async Task <ObterReunioesDaCipaQueryResult> Handle(ObterReunioesDaCipaQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
*
from
[{cipasQueryContext.DatabaseName}]..[Reunioes] with(nolock)
where
[CipaId] = @cipaId
order by
[DataPrevista]" ;
var queryResult = new ObterReunioesDaCipaQueryResult()
{
Reunioes = await cipasQueryContext.QueryAsync <ObterReunioesDaCipaQueryResult.ObterReunioesDaCipaQueryResultInnerReuniao>(query, new
{
request.CipaId
})
};
return(queryResult);
}
public async Task <ObterCipasQueryResult> Handle(ObterCipasQuery request, CancellationToken cancellationToken)
{
var query = $@"
select top (@quantidade)
*
from
[{cipasQueryContext.DatabaseName}]..[Cipas] with(nolock)
where
[Status] = @status";
if (!string.IsNullOrWhiteSpace(request.Codigo))
{
query += " and [Codigo] like @codigo";
}
if (request.CodigoEmpresa.HasValue && request.CodigoEmpresa.Value > 0)
{
query += " and [CodigoEmpresa] = @codigoEmpresa";
}
query += @"
order by
[CreationDate] desc";
var queryResult = new ObterCipasQueryResult()
{
Cipas = await cipasQueryContext.QueryAsync <ObterCipasQueryResult.ObterCipasQueryResultInnerCipa>(query, new
{
request.Status,
request.Quantidade,
codigo = $"%{request.Codigo}%",
request.CodigoEmpresa
})
};
return(queryResult);
}
public async Task <ObterReuniaoQueryResult> Handle(ObterReuniaoQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
r.*,
c.[Codigo] CipaCodigo,
a.[Id] AtaId, a.[CreationDate] AtaCreationDate, a.[CreationUser] AtaCreationUser, a.[Codigo], a.[CodigoCipa], a.[Numero], a.[Local], a.[Inicio], a.[Termino], a.[Status], a.[Finalizacao_Data], a.[Finalizacao_Ator], a.[Aprovacao_Data], a.[Aprovacao_Ator], a.[Fechamento_Data], a.[Fechamento_Ator],
p.[Id] ParticipanteId, p.[NomeCompleto] ParticipanteNomeCompleto, p.[Email] ParticipanteEmail, p.[PossuiConsentValido] ParticipantePossuiConsentValido, p.[EConvidado], p.[Organizacao], p.[Funcao],
au.[Id] AusenteId, au.[NomeCompleto] AusenteNomeCompleto, au.[Email] AusenteEmail, au.[Justificativa], au.[PossuiConsentValido] AusentePossuiConsentValido,
ass.[Id] AssuntoId, ass.[CreationDate] AssuntoCreationDate, ass.[CreationUser] AssuntoCreationUser, ass.[ClassificacaoDaInformacao], ass.[Tipo], ass.[Numero] AssuntoNumero, ass.[Descricao], ass.[Keywords], ass.[Versao]
from
[{cipasQueryContext.DatabaseName}]..[Reunioes] r with(nolock)
inner join
[{cipasQueryContext.DatabaseName}]..[Cipas] c with(nolock)
on r.[CipaId] = c.[Id]
left join
[{cipasQueryContext.DatabaseName}]..[Atas] a with(nolock)
on r.[Id] = a.[ReuniaoId]
left join
[{cipasQueryContext.DatabaseName}]..[Participantes] p with(nolock)
on a.[Id] = p.[AtaId]
left join
[{cipasQueryContext.DatabaseName}]..[Ausentes] au with(nolock)
on a.[Id] = au.[AtaId]
left join
[{cipasQueryContext.DatabaseName}]..[Assuntos] ass with(nolock)
on a.[Id] = ass.[AtaId]
where
r.[Id] = @reuniaoId
order by
p.[NomeCompleto], au.[NomeCompleto], ass.[Numero]" ;
var obterReuniaoQueryResultList = new List <ObterReuniaoQueryResult>();
await cipasQueryContext
.QueryAsync <ObterReuniaoQueryResult,
ObterReuniaoQueryResult.ObterReuniaoQueryResultInnerAta,
ObterReuniaoQueryResult.ObterReuniaoQueryResultInnerParticipante,
ObterReuniaoQueryResult.ObterReuniaoQueryResultInnerAusente,
ObterReuniaoQueryResult.ObterReuniaoQueryResultInnerAssunto>(query,
(reuniao, ata, participante, ausente, assunto) =>
{
var reuniaoOutput = obterReuniaoQueryResultList.FirstOrDefault(p => p.Id.Value == reuniao.Id.Value);
if (reuniaoOutput == null)
{
reuniaoOutput = reuniao;
obterReuniaoQueryResultList.Add(reuniaoOutput);
}
if (ata != null)
{
if (reuniaoOutput.Ata == null)
{
reuniaoOutput.Ata = ata;
reuniaoOutput.Ata.Participantes = new List <ObterReuniaoQueryResult.ObterReuniaoQueryResultInnerParticipante>();
reuniaoOutput.Ata.Ausentes = new List <ObterReuniaoQueryResult.ObterReuniaoQueryResultInnerAusente>();
reuniaoOutput.Ata.Assuntos = new List <ObterReuniaoQueryResult.ObterReuniaoQueryResultInnerAssunto>();
}
if (participante != null && !reuniaoOutput.Ata.Participantes.Any(p => p.ParticipanteId == participante.ParticipanteId))
{
reuniaoOutput.Ata.Participantes.Add(participante);
}
if (ausente != null && !reuniaoOutput.Ata.Ausentes.Any(a => a.AusenteId == ausente.AusenteId))
{
reuniaoOutput.Ata.Ausentes.Add(ausente);
}
if (assunto != null && !reuniaoOutput.Ata.Assuntos.Any(a => a.AssuntoId == assunto.AssuntoId))
{
reuniaoOutput.Ata.Assuntos.Add(assunto);
}
}
return(reuniaoOutput);
},
param : new
{
request.ReuniaoId
},
splitOn : "AtaId,ParticipanteId,AusenteId,AssuntoId");
return(obterReuniaoQueryResultList.FirstOrDefault());
}