public async Task <ObterCipaQueryResult> Handle(ObterCipaQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
*
from
[{coreBusinessQueryContext.DatabaseName}]..[Cipas] with(nolock)
where
[Id] = @cipaId";
var queryResult = (await coreBusinessQueryContext.QueryAsync <ObterCipaQueryResult>(query, new
{
request.CipaId
})).FirstOrDefault();
if (queryResult != null)
{
query = $@"
select
[Id] MembroId, [UserName], [NomeCompleto], [Funcao]
from
[{coreBusinessQueryContext.DatabaseName}]..[Membros] with(nolock)
where
[CipaId] = @cipaId
order by
[UserName]";
queryResult.Membros = await coreBusinessQueryContext.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.[Nome], c.[Descricao], c.[Mandato_Inicio], c.[Mandato_Termino], c.[Fornecedor]
from
[{coreBusinessQueryContext.DatabaseName}]..[Cipas] c with(nolock)
inner join
[{coreBusinessQueryContext.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 coreBusinessQueryContext.QueryAsync <ObterCipasAtivasDoUsuarioQueryResult.ObterCipasAtivasDoUsuarioQueryResultInnerCipa>(query, new
{
status = StatusAtividade.Ativo,
request.UserName
})
};
return(queryResult);
}
public async Task <ObterItensDePlanoDeAcaoPendentesAtribuidosAoUsuarioQueryResult> Handle(ObterItensDePlanoDeAcaoPendentesAtribuidosAoUsuarioQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
i.[Id], i.[Codigo], i.[Descricao], i.[Acao], i.[Prazo], i.[Status], i.[PlanoDeAcaoId]
from
[{coreBusinessQueryContext.DatabaseName}]..[Itens] i with(nolock)
inner join
[{coreBusinessQueryContext.DatabaseName}]..[Responsaveis] r with(nolock)
on i.[Id] = r.[ItemId]
where
i.[Status] not in @statusItemPlanoDeAcao and r.[Email] = @email
order by
i.[Prazo]" ;
var queryResult = new ObterItensDePlanoDeAcaoPendentesAtribuidosAoUsuarioQueryResult()
{
Itens = await coreBusinessQueryContext.QueryAsync <ObterItensDePlanoDeAcaoPendentesAtribuidosAoUsuarioQueryResult.ObterItensDePlanoDeAcaoPendentesAtribuidosAoUsuarioQueryResultInnerItem>(query, new
{
statusItemPlanoDeAcao = new[] { StatusAgendamento.Realizado, StatusAgendamento.Cancelado },
request.Email
})
};
return(queryResult);
}
public async Task <ObterEstabelecimentoQueryResult> Handle(ObterEstabelecimentoQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
e.*, c.[Codigo] CipaCodigo, c.[Nome] CipaNome
from
[{coreBusinessQueryContext.DatabaseName}]..[Estabelecimentos] e with(nolock)
left join
[{coreBusinessQueryContext.DatabaseName}]..[Cipas] c with(nolock)
on e.[CipaId] = c.[Id]
where
e.[Id] = @estabelecimentoId";
var queryResult = (await coreBusinessQueryContext.QueryAsync <ObterEstabelecimentoQueryResult>(query, new
{
request.EstabelecimentoId
})).FirstOrDefault();
if (queryResult != null)
{
query = $@"
select
ca.[CipaId], c.[Codigo], c.[Nome], ca.[CreationDate]
from
[{coreBusinessQueryContext.DatabaseName}]..[CipasAnteriores] ca with(nolock)
inner join
[{coreBusinessQueryContext.DatabaseName}]..[Cipas] c with(nolock)
on ca.[CipaId] = c.[Id]
where
ca.[EstabelecimentoId] = @estabelecimentoId
order by
ca.[CreationDate] desc";
queryResult.CipasAnteriores = await coreBusinessQueryContext.QueryAsync <ObterEstabelecimentoQueryResult.ObterEstabelecimentoQueryResultInnerCipaAnterior>(query, new
{
request.EstabelecimentoId
});
}
return(queryResult);
}
public async Task <ObterEstabelecimentosQueryResult> Handle(ObterEstabelecimentosQuery request, CancellationToken cancellationToken)
{
var query = $@"
select top (@quantidade)
e.*, c.[Codigo] CipaCodigo
from
[{coreBusinessQueryContext.DatabaseName}]..[Estabelecimentos] e with(nolock)
left join
[{coreBusinessQueryContext.DatabaseName}]..[Cipas] c with(nolock)
on e.[CipaId] = c.[Id]
where
e.[Status] = @status";
if (request.CipaId.HasValue)
{
query += " and e.[CipaId] = @cipaId";
}
if (!string.IsNullOrWhiteSpace(request.Codigo))
{
query += " and e.[Codigo] like @codigo";
}
if (!string.IsNullOrWhiteSpace(request.Nome))
{
query += " and e.[Nome] like @nome";
}
query += @"
order by
e.[CreationDate] desc";
var queryResult = new ObterEstabelecimentosQueryResult()
{
Estabelecimentos = await coreBusinessQueryContext.QueryAsync <ObterEstabelecimentosQueryResult.ObterEstabelecimentosQueryResultInnerEstabelecimento>(query, new
{
request.Quantidade,
request.Status,
request.CipaId,
codigo = $"%{request.Codigo}%",
nome = $"%{request.Nome}%"
})
};
return(queryResult);
}
public async Task <ObterConsentsDoParticipanteQueryResult> Handle(ObterConsentsDoParticipanteQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
*
from
[{coreBusinessQueryContext.DatabaseName}]..[ConsentsDeParticipantes] with(nolock)
where
[ParticipanteId] = @participanteId
order by
[CreationDate] desc" ;
var queryResult = new ObterConsentsDoParticipanteQueryResult()
{
Consents = await coreBusinessQueryContext.QueryAsync <ObterConsentsDoParticipanteQueryResult.ObterConsentsDoParticipanteQueryResultInnerConsent>(query, new
{
request.ParticipanteId
})
};
return(queryResult);
}
public async Task <ObterEstabelecimentosDaCipaQueryResult> Handle(ObterEstabelecimentosDaCipaQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
*
from
[{coreBusinessQueryContext.DatabaseName}]..[Estabelecimentos] with(nolock)
where
[CipaId] = @cipaId
order by
[Codigo]" ;
var queryResult = new ObterEstabelecimentosDaCipaQueryResult()
{
Estabelecimentos = await coreBusinessQueryContext.QueryAsync <ObterEstabelecimentosDaCipaQueryResult.ObterEstabelecimentosDaCipaQueryResultInnerEstabelecimento>(query, new
{
request.CipaId
})
};
return(queryResult);
}
public async Task <ObterAlteracoesDoAssuntoQueryResult> Handle(ObterAlteracoesDoAssuntoQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
*
from
[{coreBusinessQueryContext.DatabaseName}]..[Alteracoes] with(nolock)
where
[AssuntoId] = @assuntoId
order by
[CreationDate] desc" ;
var queryResult = new ObterAlteracoesDoAssuntoQueryResult()
{
Alteracoes = await coreBusinessQueryContext.QueryAsync <ObterAlteracoesDoAssuntoQueryResult.ObterAlteracoesDoAssuntoQueryResultInnerAlteracao>(query, new
{
request.AssuntoId
})
};
return(queryResult);
}
public async Task <ObterCipasQueryResult> Handle(ObterCipasQuery request, CancellationToken cancellationToken)
{
var query = $@"
select top (@quantidade)
*
from
[{coreBusinessQueryContext.DatabaseName}]..[Cipas] with(nolock)
where
[Status] = @status";
if (!string.IsNullOrWhiteSpace(request.Codigo))
{
query += " and [Codigo] like @codigo";
}
if (!string.IsNullOrWhiteSpace(request.Nome))
{
query += " and [Nome] like @nome";
}
query += @"
order by
[CreationDate] desc";
var queryResult = new ObterCipasQueryResult()
{
Cipas = await coreBusinessQueryContext.QueryAsync <ObterCipasQueryResult.ObterCipasQueryResultInnerCipa>(query, new
{
request.Status,
request.Quantidade,
codigo = $"%{request.Codigo}%",
nome = $"%{request.Nome}%"
})
};
return(queryResult);
}
public async Task <ObterPlanoDeAcaoQueryResult> Handle(ObterPlanoDeAcaoQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
pa.*,
i.[Id] ItemId, i.[Codigo], i.[Descricao], i.[Acao], i.[Prazo], i.[Status], i.[DataRealizacao],
r.[Id] ResponsavelId, r.[NomeCompleto], r.[Email]
from
[{coreBusinessQueryContext.DatabaseName}]..[PlanosDeAcao] pa with(nolock)
left join
[{coreBusinessQueryContext.DatabaseName}]..[Itens] i with(nolock)
on pa.[Id] = i.[PlanoDeAcaoId]
left join
[{coreBusinessQueryContext.DatabaseName}]..[Responsaveis] r with(nolock)
on i.[Id] = r.[ItemId]
where
pa.[Id] = @planoDeAcaoId
order by
i.[Codigo], r.[NomeCompleto]" ;
var obterPlanoDeAcaoQueryResultList = new List <ObterPlanoDeAcaoQueryResult>();
await coreBusinessQueryContext
.QueryAsync <ObterPlanoDeAcaoQueryResult,
ObterPlanoDeAcaoQueryResult.ObterPlanoDeAcaoQueryResultInnerItem,
ObterPlanoDeAcaoQueryResult.ObterPlanoDeAcaoQueryResultInnerResponsavel>(query,
(planoDeAcao, item, responsavel) =>
{
var planoDeAcaoOutput = obterPlanoDeAcaoQueryResultList.FirstOrDefault(p => p.Id.Value == planoDeAcao.Id.Value);
if (planoDeAcaoOutput == null)
{
planoDeAcaoOutput = planoDeAcao;
planoDeAcaoOutput.Itens = new List <ObterPlanoDeAcaoQueryResult.ObterPlanoDeAcaoQueryResultInnerItem>();
obterPlanoDeAcaoQueryResultList.Add(planoDeAcaoOutput);
}
if (item != null)
{
var itemOutput = planoDeAcaoOutput.Itens.FirstOrDefault(i => i.ItemId.Value == item.ItemId.Value);
if (itemOutput == null)
{
itemOutput = item;
itemOutput.Responsaveis = new List <ObterPlanoDeAcaoQueryResult.ObterPlanoDeAcaoQueryResultInnerResponsavel>();
planoDeAcaoOutput.Itens.Add(item);
}
if (responsavel != null)
{
itemOutput.Responsaveis.Add(responsavel);
}
}
return(planoDeAcaoOutput);
},
param : new
{
request.PlanoDeAcaoId
},
splitOn : "ItemId,ResponsavelId");
return(obterPlanoDeAcaoQueryResultList.FirstOrDefault());
}
public async Task <ObterReuniaoQueryResult> Handle(ObterReuniaoQuery request, CancellationToken cancellationToken)
{
var query = $@"
select
r.*,
c.[Codigo] CipaCodigo,
pa.[Codigo] PlanoDeAcaoCodigo,
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],
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
[{coreBusinessQueryContext.DatabaseName}]..[Reunioes] r with(nolock)
inner join
[{coreBusinessQueryContext.DatabaseName}]..[Cipas] c with(nolock)
on r.[CipaId] = c.[Id]
left join
[{coreBusinessQueryContext.DatabaseName}]..[PlanosDeAcao] pa with(nolock)
on r.[PlanoDeAcaoId] = pa.[Id]
left join
[{coreBusinessQueryContext.DatabaseName}]..[Atas] a with(nolock)
on r.[Id] = a.[ReuniaoId]
left join
[{coreBusinessQueryContext.DatabaseName}]..[Participantes] p with(nolock)
on a.[Id] = p.[AtaId]
left join
[{coreBusinessQueryContext.DatabaseName}]..[Ausentes] au with(nolock)
on a.[Id] = au.[AtaId]
left join
[{coreBusinessQueryContext.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 coreBusinessQueryContext
.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());
}