public async Task <AlunoQuery> ObterAlunoCompletoAsync(Guid id)
{
try
{
var query = @"SELECT Distinct
a.id as AlunoId,
a.nome as AlunoNome,
a.email as AlunoEmail,
a.cpf as AlunoCpf,
a.foto as AlunoFoto,
a.data_nascimento as AlunoDataNascimento,
a.uif_id as AlunoGuid,
a.telefone as AlunoTelefone,
a.celular as AlunoCelular,
l.id as AlunoLogradouro_LogradouroId,
l.rua as AlunoLogradouro_LogradouroRua,
l.numero as AlunoLogradouro_LogradouroNumenro,
l.bairro as AlunoLogradouro_LogradouroBairro,
l.cep as AlunoLogradouro_LogradouroCep,
l.complemento as AlunoLogradouro_LogradouroComplemento,
l.cidade as AlunoLogradouro_LogradouroCidade,
l.estado as AlunoLogradouro_LogradouroEstado,
f.id as AlunoFiliacoes_FiliacaoId,
f.nome as AlunoFiliacoes_FiliacaoNome,
f.telefone as AlunoFiliacoes_FiliacaoTelefone,
f.documento as AlunoFiliacoes_FiliacaoDocumento,
f.email as AlunoFiliacoes_FiliacaoEmail,
t.id as AlunoTurmas_TurmaId,
t.des_turma as AlunoTurmas_TurmaDescricao,
t.cod_turma as AlunoTurmas_TurmaCodigo,
t.turma_professor as AlunoTurmas_TurmaProfessor,
t.turma_tipo as AlunoTurmas_TurmaTipo,
ma.id as AlunoMatricula_MatriculaId,
ma.valor_contrato as AlunoMatricula_MatriculaValorContrato,
ma.percentual_desconto as AlunoMatricula_MatriculaPercentualDesconto,
ma.valor_desconto as AlunoMatricula_MatriculaValorDesconto,
ma.dia_venciamento as AlunoMatricula_MatriculaDiaVencimento,
ma.data_inicial_pagamento as AlunoMatricula_MatriculaDataInicialPagamento,
ma.uif_id as AlunoMatricula_MatriculaGuid,
ma.valor_matricula as AlunoMatricula_MatriculaValorMatricula,
ma.total_parcelas as AlunoMatricula_MatriculaTotalParcelas,
ma.ano as AlunoMatricula_MatriculaAno,
case when ma.status = 1 then 'Ativo' else 'Inativo' end as AlunoMatricula_MatriculaStatus,
me.id as AlunoMensalidades_MensalidadeId,
me.data_vencimento as AlunoMensalidades_MensalidadeDataVencimento,
me.valor as AlunoMensalidades_MensalidadeValor,
me.desconto as AlunoMensalidades_MensalidadeDesconto,
me.pago as AlunoMensalidades_MensalidadePago,
me.data_pagamento as AlunoMensalidades_MensalidadeDataPagamento,
me.juros as AlunoMensalidades_MensalidadeJuros,
me.parcela as AlunoMensalidades_MensalidadeParcela
FROM academia.aluno as a
left join academia.logradouro_aluno as la on a.id = la.id_aluno
left join academia.logradouro as l on la.id_logradouro = l.id
left join academia.aluno_filiacao as af on a.id = af.id_aluno
left join academia.filiacao as f on af.id_filiacao = f.id
left join academia.turma_aluno as ta on a.id = ta.id_aluno
left join academia.view_turma_professor as t on ta.id_turma = t.id
left join academia.matricula as ma on a.id = ma.id_aluno
left outer join academia.mensalidade as me on a.id = me.id_aluno and ma.id = me.id_matricula
where a.uif_id=@id
Order by a.id,l.id,f.id,t.id;";
var parametros = new DynamicParameters();
parametros.Add("id", id);
var aluno = new AlunoQuery();
var alunoRetorno = (await _contexto.Connection.QueryAsync <dynamic>(query,
parametros, commandType: CommandType.Text));
AutoMapper.Configuration.AddIdentifier(typeof(AlunoQuery), "AlunoId");
AutoMapper.Configuration.AddIdentifier(typeof(AlunoEnderecoQuery), "LogradouroId");
AutoMapper.Configuration.AddIdentifier(typeof(AlunoFiliacaoQuery), "FiliacaoId");
AutoMapper.Configuration.AddIdentifier(typeof(AlunoTurmaQuery), "TurmaId");
AutoMapper.Configuration.AddIdentifier(typeof(AlunoMatriculaQuery), "MatriculaId");
AutoMapper.Configuration.AddIdentifier(typeof(AlunoMensalidadeQuery), "MensalidadeId");
aluno = (AutoMapper.MapDynamic <AlunoQuery>(alunoRetorno)).FirstOrDefault();
return(aluno);
}
finally
{
_contexto.Dispose();
}
}
public async Task <AlunoQuery> ObterAlunoCompletotesteAsync(Guid id)
{
try
{
var query = @"SELECT Distinct
a.id as AlunoId,
a.nome as AlunoNome,
a.email as AlunoEmail,
a.cpf as AlunoCpf,
a.foto as AlunoFoto,
a.data_nascimento as AlunoDataNascimento,
a.uif_id as AlunoGuid,
a.telefone as AlunoTelefone,
a.celular as AlunoCelular,
l.id as LogradouroId,
l.rua as LogradouroRua,
l.numero as LogradouroNumenro,
l.bairro as LogradouroBairro,
l.cep as LogradouroCep,
l.complemento as LogradouroComplemento,
l.cidade as LogradouroCidade,
l.estado as LogradouroEstado,
f.id as FiliacaoId,
f.nome as FiliacaoNome,
f.telefone as FiliacaoTelefone,
f.documento as FiliacaoDocumento,
f.email as FiliacaoEmail,
t.id as TurmaId,
t.des_turma as TurmaDescricao,
t.cod_turma as TurmaCodigo,
t.turma_professor as TurmaProfessor
FROM academia.aluno as a
left join academia.logradouro_aluno as la on la.id_aluno = a.id
left join academia.logradouro as l on l.id = la.id_logradouro
left join academia.aluno_filiacao as af on a.id = af.id_aluno
left join academia.filiacao as f on af.id_filiacao = f.id
left join academia.turma_aluno as ta on a.id = ta.id_aluno
left join academia.view_turma_professor as t on ta.id_turma = t.id
left join academia.matricula as ma on a.id = ma.id_aluno
where a.uif_id=@id
Order by a.id,l.id,f.id,t.id;";
var parametros = new DynamicParameters();
parametros.Add("id", id);
AlunoQuery aluno = new AlunoQuery();
var alunoRetorno = (await _contexto.Connection.QueryAsync <AlunoQuery, AlunoEnderecoQuery, AlunoFiliacaoQuery, AlunoTurmaQuery, AlunoQuery>(query,
(a, l, f, t) =>
{
aluno = a;
aluno.AlunoTurmas.Add(t);
aluno.AlunoLogradouro = l;
//aluno.Alun.Add(f);
return(aluno);
}, parametros, splitOn: "AlunoId,LogradouroId,FiliacaoId,TurmaId", commandType: CommandType.Text));
return(alunoRetorno.FirstOrDefault());
}
finally
{
_contexto.Dispose();
}
}
