//[TODO] consultar equipe gestão sobre o segundo inner join desta query
//public IEnumerable<SchoolSuperior> Get(Guid userId, Guid groupId )
//{
// using (var context = new SqlConnection(stringConnection))
// {
// var query = context.Query<SchoolSuperior>(
// @"SELECT
// uadSuperior.ent_id,
// uadSuperior.uad_id,
// uadSuperior.uad_codigo,
// uadSuperior.uad_nome,
// uadSuperior.uad_sigla,
// uadSuperior.uad_codigoInep
// FROM
// ESC_Escola esc WITH(NOLOCK)
// INNER JOIN Synonym_SYS_UnidadeAdministrativa uad WITH(NOLOCK)
// ON uad.ent_id = esc.ent_id
// AND uad.uad_id = esc.uad_id
// AND uad.uad_situacao <> 3
// INNER JOIN Synonym_SYS_UnidadeAdministrativa uadSuperior WITH(NOLOCK)
// ON uadSuperior.ent_id = uad.ent_id
// AND uadSuperior.uad_id = ISNULL(esc.uad_idSuperiorGestao, uad.uad_idSuperior)
// AND uadSuperior.uad_situacao <> 3
// WHERE
// esc.esc_situacao <> 3
// AND uad.uad_id IN (SELECT uad_id FROM Synonym_FN_Select_UAs_By_PermissaoUsuario(@usu_idLogado, @gru_idLogado))",
// new { usu_idLogado = userId, gru_idLogado = groupId }
// );
// return query;
// }
//}
public IEnumerable <SchoolSuperior> Get(Guid userId, Guid groupId)
{
var groupRep = new GroupRepository();
var groupUser = groupRep.GetById(groupId);
SchoolRepository school = new SchoolRepository();
IEnumerable <Guid> ltAUPermission = null;
if (groupUser.VisionId > 1)
{
ltAUPermission = school.GetAUByPermission(userId, groupId);
}
using (var context = new SqlConnection(stringConnection))
{
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT DISTINCT
uadSuperior.uad_id 'Id'
, uadSuperior.uad_nome 'Name'
FROM
ESC_Escola esc WITH(NOLOCK)
INNER JOIN Synonym_SYS_UnidadeAdministrativa uad WITH(NOLOCK)
ON uad.ent_id = esc.ent_id
AND uad.uad_id = esc.uad_id
AND uad.uad_situacao <> 3
INNER JOIN Synonym_SYS_UnidadeAdministrativa uadSuperior WITH(NOLOCK)
ON uadSuperior.ent_id = uad.ent_id
AND uadSuperior.uad_id = ISNULL(esc.uad_idSuperiorGestao, uad.uad_idSuperior)
AND uadSuperior.uad_situacao <> 3
WHERE
esc.esc_situacao <> 3" );
//Se não for administrador, usa a lista filtrada de usuarioGrupoUA
if (groupUser.VisionId > 1)
{
sb.Append(" AND uad.uad_id IN @idsUADPermissao ");
}
sb.Append(" ORDER BY uadSuperior.uad_nome");
var query = context.Query <SchoolSuperior>(
sb.ToString(),
new
{
usu_idLogado = userId
,
gru_idLogado = groupId
,
idsUADPermissao = ltAUPermission
}
);
return(query);
}
}
/// <summary>
/// Retorna docentes baseados em filtros.
/// </summary>
/// <returns></returns>
public IEnumerable <Teacher> Get(Guid userId, Guid groupId, string calendarName, IEnumerable <Guid> ltSchoolSuperior, IEnumerable <int> listClassificationTypeSchool, IEnumerable <int> ltSchoolID, IEnumerable <int> listPosition, IEnumerable <int> listCourse, IEnumerable <int> listCoursePeriod, IEnumerable <int> listDiscipline, IEnumerable <int> listTeam)
{
var groupRep = new GroupRepository();
var groupUser = groupRep.GetById(groupId);
using (var context = new SqlConnection(stringConnection))
{
SchoolRepository school = new SchoolRepository();
IEnumerable <Guid> ltAUPermission = null;
if (groupUser.VisionId > 1)
{
ltAUPermission = school.GetAUByPermission(userId, groupId, ltSchoolSuperior, ltSchoolID);
}
StringBuilder sb = new StringBuilder();
sb.Append(
@"SELECT
DISTINCT
usu.usu_id 'Id'
FROM
TUR_Turma tur WITH(NOLOCK)
INNER JOIN ESC_Escola esc WITH(NOLOCK)
ON esc.esc_id = tur.esc_id
AND esc.esc_situacao <> 3
INNER JOIN Synonym_SYS_UnidadeAdministrativa uad WITH(NOLOCK)
ON uad.ent_id = esc.ent_id
AND uad.uad_id = esc.uad_id
AND uad.uad_situacao <> 3
INNER JOIN Synonym_SYS_UnidadeAdministrativa uadSuperior WITH(NOLOCK)
ON uadSuperior.ent_id = uad.ent_id
AND uadSuperior.uad_id = ISNULL(esc.uad_idSuperiorGestao, uad.uad_idSuperior)
AND uadSuperior.uad_situacao <> 3
INNER JOIN ESC_EscolaClassificacao ecl WITH(NOLOCK)
ON ecl.esc_id = esc.esc_id
INNER JOIN ESC_TipoClassificacaoEscola tce WITH(NOLOCK)
ON tce.tce_id = ecl.tce_id
AND tce.tce_situacao <> 3
INNER JOIN TUR_TurmaRelTurmaDisciplina relTud WITH(NOLOCK)
ON relTud.tur_id = tur.tur_id
INNER JOIN TUR_TurmaDisciplinaRelDisciplina relDis WITH(NOLOCK)
ON relDis.tud_id = relTud.tud_id
INNER JOIN ACA_Disciplina dis WITH(NOLOCK)
ON dis.dis_id = relDis.dis_id
AND dis.dis_situacao <> 3
INNER JOIN TUR_TurmaCurriculo tcr WITH(NOLOCK)
ON tcr.tur_id = tur.tur_id
AND tcr.tcr_situacao <> 3
INNER JOIN TUR_TurmaDocente tdt WITH(NOLOCK)
ON tdt.tud_id = relTud.tud_id
AND tdt.tdt_situacao <> 3
INNER JOIN RHU_Cargo crg WITH(NOLOCK)
ON crg.crg_id = tdt.crg_id
AND crg.crg_situacao <> 3
INNER JOIN RHU_Colaborador col WITH(NOLOCK)
ON col.col_id = tdt.col_id
AND col.col_situacao <> 3
INNER JOIN Synonym_SYS_Usuario usu WITH(NOLOCK)
ON usu.pes_id = col.pes_id
AND usu.usu_situacao <> 3
INNER JOIN ACA_CalendarioAnual as cal WITH(NOLOCK)
ON tur.cal_id=cal.cal_id
AND uad.ent_id=cal.ent_id
AND cal.cal_situacao<>3
WHERE
tur.tur_situacao <> 3" );
if (groupUser.VisionId > 1)
{
sb.Append(" AND uad.uad_id IN @idsUADPermissao ");
}
//(SELECT uad_id FROM Synonym_FN_Select_UAs_By_PermissaoUsuario(@usu_idLogado, @gru_idLogado))");
if (!String.IsNullOrEmpty(calendarName))
{
sb.Append(" AND cal.cal_ano = @calendarioNome");
}
if (ltSchoolSuperior != null && ltSchoolSuperior.Any())
{
sb.Append(" AND uadSuperior.uad_id IN @idsDRES");
}
if (listClassificationTypeSchool != null && listClassificationTypeSchool.Any())
{
sb.Append(" AND tce.tce_id IN @idsTipoClassificacaoEscola");
}
if (ltSchoolID != null && ltSchoolID.Any())
{
sb.Append(" AND esc.esc_id IN @idsEscola");
}
if (listPosition != null && listPosition.Any())
{
sb.Append(" AND crg.crg_id IN @idsCargo");
}
if (listCourse != null && listCourse.Any())
{
sb.Append(" AND tcr.cur_id IN @idsCurso");
}
if (listCoursePeriod != null && listCoursePeriod.Any())
{
sb.Append(" AND tcr.crp_id IN @idsPeriodo");
}
if (listDiscipline != null && listDiscipline.Any())
{
sb.Append(" AND dis.tds_id IN @idsDisciplina");
}
if (listTeam != null && listTeam.Any())
{
sb.Append(" AND tur.tur_id IN @idsTurma");
}
var query = context.Query <Teacher>(
sb.ToString(),
new
{
calendarioNome = calendarName
, usu_idLogado = userId
, gru_idLogado = groupId
, idsDRES = ltSchoolSuperior
, idsTipoClassificacaoEscola = listClassificationTypeSchool
, idsUADPermissao = ltAUPermission
, idsEscola = ltSchoolID
, idsCargo = listPosition
, idsCurso = listCourse
, idsPeriodo = listCoursePeriod
, idsDisciplina = listDiscipline
, idsTurma = listTeam
}
);
return(query);
}
}
/// <summary>
/// Retorna docentes baseados em filtros.
/// </summary>
/// <returns></returns>
public IEnumerable <Contributor> Get(Guid userId, Guid groupId, string calendarName, IEnumerable <Guid> ltSchoolSuperior, IEnumerable <int> listClassificationTypeSchool, IEnumerable <int> ltSchoolID, IEnumerable <int> listPosition)
{
var groupRep = new GroupRepository();
var groupUser = groupRep.GetById(groupId);
using (var context = new SqlConnection(stringConnection))
{
SchoolRepository school = new SchoolRepository();
IEnumerable <Guid> ltAUPermission = null;
if (groupUser.VisionId > 1)
{
ltAUPermission = school.GetAUByPermission(userId, groupId, ltSchoolSuperior, ltSchoolID);
}
StringBuilder sb = new StringBuilder();
sb.Append(
@"SELECT
DISTINCT
usu.usu_id 'Id'
FROM
RHU_Colaborador col WITH(NOLOCK)
INNER JOIN Synonym_SYS_Usuario usu WITH(NOLOCK)
ON usu.pes_id = col.pes_id
AND usu.usu_situacao <> 3
INNER JOIN RHU_ColaboradorCargo coc WITH(NOLOCK)
ON coc.col_id = col.col_id
AND coc.coc_situacao <> 3
INNER JOIN RHU_Cargo crg WITH(NOLOCK)
ON crg.crg_id = coc.crg_id
AND crg.crg_cargoDocente = 0
AND crg.crg_situacao <> 3
INNER JOIN ESC_Escola esc WITH(NOLOCK)
ON esc.ent_id = coc.ent_id
AND esc.uad_id = coc.uad_id
AND esc.esc_situacao <> 3
INNER JOIN Synonym_SYS_UnidadeAdministrativa uad WITH(NOLOCK)
ON uad.ent_id = esc.ent_id
AND uad.uad_id = esc.uad_id
AND uad.uad_situacao <> 3
INNER JOIN Synonym_SYS_UnidadeAdministrativa uadSuperior WITH(NOLOCK)
ON uadSuperior.ent_id = uad.ent_id
AND uadSuperior.uad_id = ISNULL(esc.uad_idSuperiorGestao, uad.uad_idSuperior)
AND uadSuperior.uad_situacao <> 3
INNER JOIN ESC_EscolaClassificacao ecl WITH(NOLOCK)
ON ecl.esc_id = esc.esc_id
INNER JOIN ESC_TipoClassificacaoEscola tce WITH(NOLOCK)
ON tce.tce_id = ecl.tce_id
AND tce.tce_situacao <> 3
INNER JOIN ACA_CalendarioAnual cal WITH(NOLOCK)
ON coc.coc_vigenciaInicio <= cal.cal_dataFim
AND
(
coc.coc_vigenciaFim IS NULL
OR coc.coc_vigenciaFim >= cal.cal_dataInicio
)
AND cal.cal_situacao <> 3
INNER JOIN Synonym_PES_Pessoa as pes WITH(NOLOCK)
ON pes.pes_id= usu.pes_id
WHERE
col.col_situacao <> 3");
if (groupUser.VisionId > 1)
{
sb.Append(" AND uad.uad_id IN @idsUADPermissao ");
}
//(SELECT uad_id FROM Synonym_FN_Select_UAs_By_PermissaoUsuario(@usu_idLogado, @gru_idLogado))");
if (!String.IsNullOrEmpty(calendarName))
{
sb.Append(" AND cal.cal_ano = @calendarioNome");
}
if (ltSchoolSuperior != null && ltSchoolSuperior.Any())
{
sb.Append(" AND uadSuperior.uad_id IN @idsDRES");
}
if (listClassificationTypeSchool != null && listClassificationTypeSchool.Any())
{
sb.Append(" AND tce.tce_id IN @idsTipoClassificacaoEscola");
}
if (ltSchoolID != null && ltSchoolID.Any())
{
sb.Append(" AND esc.esc_id IN @idsEscola");
}
if (listPosition != null && listPosition.Any())
{
sb.Append(" AND crg.crg_id IN @idsCargo");
}
sb.Append(@" UNION
SELECT
DISTINCT
usu.usu_id 'Id'
FROM
RHU_Colaborador col WITH(NOLOCK)
INNER JOIN Synonym_SYS_Usuario usu WITH(NOLOCK)
ON usu.pes_id = col.pes_id
AND usu.usu_situacao <> 3
INNER JOIN RHU_ColaboradorCargo coc WITH(NOLOCK)
ON coc.col_id = col.col_id
AND coc.coc_situacao <> 3
INNER JOIN RHU_Cargo crg WITH(NOLOCK)
ON crg.crg_id = coc.crg_id
AND crg.crg_cargoDocente = 0
AND crg.crg_situacao <> 3
INNER JOIN Synonym_SYS_UnidadeAdministrativa uadSuperior WITH(NOLOCK)
ON uadSuperior.ent_id = coc.ent_id
AND uadSuperior.uad_id = coc.uad_id
AND uadSuperior.uad_situacao <> 3
INNER JOIN ESC_Escola esc WITH(NOLOCK)
ON esc.ent_id = uadSuperior.ent_id
AND esc.uad_idSuperiorGestao = uadSuperior.uad_id
AND esc.esc_situacao <> 3
INNER JOIN ESC_EscolaClassificacao ecl WITH(NOLOCK)
ON ecl.esc_id = esc.esc_id
INNER JOIN ESC_TipoClassificacaoEscola tce WITH(NOLOCK)
ON tce.tce_id = ecl.tce_id
AND tce.tce_situacao <> 3
INNER JOIN ACA_CalendarioAnual cal WITH(NOLOCK)
ON coc.coc_vigenciaInicio <= cal.cal_dataFim
AND
(
coc.coc_vigenciaFim IS NULL
OR coc.coc_vigenciaFim >= cal.cal_dataInicio
)
AND cal.cal_situacao <> 3
INNER JOIN Synonym_PES_Pessoa as pes WITH(NOLOCK)
ON pes.pes_id= usu.pes_id
WHERE
col.col_situacao <> 3");
if (groupUser.VisionId > 1)
{
sb.Append(" AND esc.uad_id IN @idsUADPermissao ");
}
//(SELECT uad_id FROM Synonym_FN_Select_UAs_By_PermissaoUsuario(@usu_idLogado, @gru_idLogado))");
if (!String.IsNullOrEmpty(calendarName))
{
sb.Append(" AND cal.cal_ano = @calendarioNome");
}
if (ltSchoolSuperior != null && ltSchoolSuperior.Any())
{
sb.Append(" AND uadSuperior.uad_id IN @idsDRES");
}
if (listClassificationTypeSchool != null && listClassificationTypeSchool.Any())
{
sb.Append(" AND tce.tce_id IN @idsTipoClassificacaoEscola");
}
if (ltSchoolID != null && ltSchoolID.Any())
{
sb.Append(" AND esc.esc_id IN @idsEscola");
}
if (listPosition != null && listPosition.Any())
{
sb.Append(" AND crg.crg_id IN @idsCargo");
}
var query = context.Query <Contributor>(
sb.ToString(),
new
{
calendarioNome = calendarName
, usu_idLogado = userId
, gru_idLogado = groupId
, idsDRES = ltSchoolSuperior
, idsUADPermissao = ltAUPermission
, idsTipoClassificacaoEscola = listClassificationTypeSchool
, idsEscola = ltSchoolID
, idsCargo = listPosition
}
);
return(query);
}
}
public IEnumerable <Team> Get(
Guid userId,
Guid groupId,
string calendarYear,
IEnumerable <Guid> ltSchoolSuperior,
IEnumerable <int> schoolClassificationId,
IEnumerable <int> ltSchoolID,
IEnumerable <int> courseId,
IEnumerable <string> coursePeriodId,
IEnumerable <int> disciplineId)
{
var groupRep = new GroupRepository();
var groupUser = groupRep.GetById(groupId);
SchoolRepository school = new SchoolRepository();
IEnumerable <Guid> ltAUPermission = null;
if (groupUser.VisionId > 1)
{
ltAUPermission = school.GetAUByPermission(userId, groupId, ltSchoolSuperior, ltSchoolID);
}
var ltCoursePeriod = coursePeriodId.Select(c => new { curId = c.Split('|')[0], crrId = c.Split('|')[1], crpId = c.Split('|')[2] });
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT tur.tur_id as Id, tur.tur_codigo as Name
FROM TUR_Turma tur WITH(NOLOCK)
INNER JOIN ACA_CalendarioAnual cal WITH(NOLOCK) ON cal.cal_id = tur.cal_id AND cal.cal_situacao <> 3
INNER JOIN ESC_Escola esc WITH(NOLOCK) ON esc.esc_id = tur.esc_id AND esc.esc_situacao <> 3
INNER JOIN ESC_EscolaClassificacao ecl WITH(NOLOCK) ON ecl.esc_id = esc.esc_id
INNER JOIN ESC_TipoClassificacaoEscola tce WITH(NOLOCK) ON tce.tce_id = ecl.tce_id AND tce.tce_situacao <> 3
INNER JOIN Synonym_SYS_UnidadeAdministrativa uad WITH(NOLOCK) ON uad.ent_id = esc.ent_id AND uad.uad_id = esc.uad_id
INNER JOIN Synonym_SYS_UnidadeAdministrativa uadSuperior WITH(NOLOCK) ON uadSuperior.ent_id = uad.ent_id AND uadSuperior.uad_id = ISNULL(esc.uad_idSuperiorGestao, uad.uad_idSuperior) AND uadSuperior.uad_situacao <> 3
INNER JOIN TUR_TurmaCurriculo tcr WITH(NOLOCK) ON tcr.tur_id = tur.tur_id AND tcr.tcr_situacao <> 3
INNER JOIN TUR_TurmaRelTurmaDisciplina relTud WITH(NOLOCK) ON relTud.tur_id = tur.tur_id
INNER JOIN TUR_TurmaDisciplinaRelDisciplina relDis WITH(NOLOCK) ON relDis.tud_id = relTud.tud_id
INNER JOIN ACA_Disciplina dis WITH(NOLOCK) ON dis.dis_id = relDis.dis_id AND dis.dis_situacao <> 3");
if (ltCoursePeriod.Any())
{
sb.Append(" INNER JOIN (");
foreach (var item in ltCoursePeriod)
{
sb.Append(string.Format("SELECT {0} AS CUR_ID, {1} AS CRR_ID, {2} AS CRP_ID UNION ", item.curId, item.crrId, item.crpId));
}
sb.Remove(sb.Length - 7, 7);
sb.Append(") AS T1 ON T1.cur_id = tcr.cur_id AND T1.crr_id = tcr.crr_id AND T1.crp_id = tcr.crp_id");
}
sb.Append(@" WHERE tur.tur_situacao <> 3 AND cal.cal_ano = @calendarYear");
if (groupUser.VisionId > 1)
{
sb.Append(" AND uad.uad_id IN @idsUADPermissao ");
}
//(SELECT uad_id FROM Synonym_FN_Select_UAs_By_PermissaoUsuario(@userId, @groupId))");
if (ltSchoolSuperior != null && ltSchoolSuperior.Any())
{
sb.Append(" AND uadSuperior.uad_id IN @schoolSuperiorId");
}
if (schoolClassificationId != null && schoolClassificationId.Any())
{
sb.Append(" AND tce.tce_id IN @schoolClassificationId");
}
if (ltSchoolID != null && ltSchoolID.Any())
{
sb.Append(" AND esc.esc_id IN @schoolId");
}
if (courseId != null && courseId.Any())
{
sb.Append(" AND tcr.cur_id IN @courseId");
}
if (disciplineId != null && disciplineId.Any())
{
sb.Append(" AND dis.tds_id IN @disciplineId");
}
using (var context = new SqlConnection(stringConnection))
{
var query = context.Query <Team>(sb.ToString(),
new
{
userId = userId,
groupId = groupId,
calendarYear = calendarYear,
idsUADPermissao = ltAUPermission,
schoolSuperiorId = ltSchoolSuperior,
schoolClassificationId = schoolClassificationId,
schoolId = ltSchoolID,
courseId = courseId,
crp_id = coursePeriodId,
disciplineId = disciplineId
});
return(query);
}
}
public IEnumerable <SchoolClassification> Get(Guid userId, Guid groupId, IEnumerable <Guid> ltSchoolSuperior)
{
var groupRep = new GroupRepository();
var groupUser = groupRep.GetById(groupId);
using (var context = new SqlConnection(stringConnection))
{
SchoolRepository school = new SchoolRepository();
IEnumerable <Guid> ltAUPermission = null;
if (groupUser.VisionId > 1)
{
ltAUPermission = school.GetAUByPermission(userId, groupId, ltSchoolSuperior);
}
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT
DISTINCT
tce.tce_id 'Id',
tce.tce_nome 'Name'
FROM
ESC_Escola esc WITH(NOLOCK)
INNER JOIN ESC_EscolaClassificacao ecl WITH(NOLOCK)
ON ecl.esc_id = esc.esc_id
INNER JOIN ESC_TipoClassificacaoEscola tce WITH(NOLOCK)
ON tce.tce_id = ecl.tce_id
AND tce.tce_situacao <> 3
INNER JOIN Synonym_SYS_UnidadeAdministrativa uad WITH(NOLOCK)
ON uad.ent_id = esc.ent_id
AND uad.uad_id = esc.uad_id
AND uad.uad_situacao <> 3
INNER JOIN Synonym_SYS_UnidadeAdministrativa uadSuperior WITH(NOLOCK)
ON uadSuperior.ent_id = uad.ent_id
AND uadSuperior.uad_id = ISNULL(esc.uad_idSuperiorGestao, uad.uad_idSuperior)
AND uadSuperior.uad_situacao <> 3
WHERE
esc.esc_situacao <> 3" );
if (groupUser.VisionId > 1)
{
sb.Append(" AND uad.uad_id IN @idsUADPermissao ");
}
//(SELECT uad_id FROM Synonym_FN_Select_UAs_By_PermissaoUsuario(@usu_idLogado, @gru_idLogado))");
if (ltSchoolSuperior != null && ltSchoolSuperior.Any())
{
sb.Append(" AND uadSuperior.uad_id in @idsDRE");
}
sb.Append(" ORDER BY tce.tce_nome");
var query = context.Query <SchoolClassification>(
sb.ToString(),
new
{
usu_idLogado = userId
, gru_idLogado = groupId
, idsUADPermissao = ltAUPermission
, idsDRE = ltSchoolSuperior
});
return(query);
}
}