package pt.estgp.estgweb.services.questionarios.pedagogico.reportprocessors.queries;
import jomm.dao.impl.AbstractDao;
import jomm.utils.MyCalendar;
import org.hibernate.Query;
import pt.estgp.estgweb.domain.OlapStarFactQuestionario;
import pt.estgp.estgweb.services.questionarios.PedagogicoAlunosProcessor;
import pt.estgp.estgweb.services.questionarios.pedagogico.reportprocessors.reportdomains.EntityEvaluated;
import pt.estgp.estgweb.services.questionarios.pedagogico.reportprocessors.reportdomains.QuestionariosAlunosReport;
import pt.estgp.estgweb.utils.DatesUtils;
import java.util.Calendar;
import java.util.List;
/**
* Created by jorgemachado on 07/12/16.
*/
public class AlunosQueryDao
{
public static void countIdadesAlunosComRespostasAoDocente
(1.5.0/docs/api/java/lang/String.html">String teacherCode,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
countIdadesAlunosComRespostasGen
(new 1.5.0/docs/api/java/lang/String.html">String[]{teacherCode
},EntityEvaluated.
DOCENTE,anoLectivo,docenteReport,periodos,degrees
);
}
public static void countIdadesAlunosComRespostasAoCurso
(1.5.0/docs/api/java/lang/String.html">String courseCode,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
countIdadesAlunosComRespostasGen
(new 1.5.0/docs/api/java/lang/String.html">String[]{courseCode
},EntityEvaluated.
CURSO,anoLectivo,docenteReport,periodos,degrees
);
}
public static void countIdadesAlunosComRespostasAoCursos
(1.5.0/docs/api/java/lang/String.html">String[] courseCodes,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
countIdadesAlunosComRespostasGen
(courseCodes,EntityEvaluated.
CURSOS,anoLectivo,docenteReport,periodos,degrees
);
}
public static void countIdadesAlunosComRespostasAoCursos
(List
<String
> courseCodes,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
countIdadesAlunosComRespostasGen
((1.5.0/docs/api/java/lang/String.html">String[]) courseCodes.
toArray(),EntityEvaluated.
CURSOS,anoLectivo,docenteReport,periodos,degrees
);
}
public static void countIdadesAlunosComRespostasGen
(1.5.0/docs/api/java/lang/String.html">String[] codes,EntityEvaluated entityEvaluated,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport report,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
) {
1.5.0/docs/api/java/lang/String.html">String periodosQuery = QueryDaoUtils.
createPeriodosQuery(periodos,
"quest",
true);
1.5.0/docs/api/java/lang/String.html">String degreesQuery = QueryDaoUtils.
createDegreesQuery(degrees,
"curso",
true);
1.5.0/docs/api/javax/management/Query.html">Query query
;
1.5.0/docs/api/java/lang/String.html">String whereFragmentEntity = QuestionariosQueryDao.
getWhereEntityCodesFragment(codes, entityEvaluated
);
int secondYear = DatesUtils.
getSecondFromImportYear(anoLectivo
);
MyCalendar dataQuestionario17 =
new MyCalendar
(secondYear,9,1
);
MyCalendar dataQuestionario22 =
new MyCalendar
(secondYear,9,1
);
MyCalendar dataQuestionario25 =
new MyCalendar
(secondYear,9,1
);
MyCalendar dataQuestionario30 =
new MyCalendar
(secondYear,9,1
);
dataQuestionario17.
add(1.5.0/docs/api/java/util/Calendar.html">Calendar.
YEAR,-17
);
dataQuestionario22.
add(1.5.0/docs/api/java/util/Calendar.html">Calendar.
YEAR,-22
);
dataQuestionario25.
add(1.5.0/docs/api/java/util/Calendar.html">Calendar.
YEAR,-25
);
dataQuestionario30.
add(1.5.0/docs/api/java/util/Calendar.html">Calendar.
YEAR,-30
);
query = AbstractDao.
getCurrentSession().
createQuery(
"select " +
"count(distinct user.sigesCode) from " + OlapStarFactQuestionario.
class.
getName() +
" fc " +
//"count(distinct case when user.birthDate <= :dateStart and user.birthDate > :dateEnd then user.sigesCode else NULL end) from " + OlapStarFactQuestionario.class.getName() + " fc " +
"JOIN fc.olapQuestionario quest " +
"JOIN fc.olapTipologia tipo " +
"JOIN fc.olapPerfilUser user " +
"JOIN fc.olapCurso curso " +
"JOIN fc.olapTimeLine timeLine " +
"where " +
"user.birthDate <= :dateStart " +
"and user.birthDate > :dateEnd " +
"and quest.ano = :ano " +
whereFragmentEntity +
"and fc.respondido = true " +
"and timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery
);
query.
setDate("dateStart", dataQuestionario17.
getTime());
query.
setDate("dateEnd",dataQuestionario22.
getTime());
//query.setString("codigoSiges",teacherCode);
query.
setString("ano",anoLectivo
);
report.
idade17_22 =
((1.5.0/docs/api/java/lang/Long.html">Long) query.
uniqueResult()).
intValue();
report.
idade17_22Percent =
(int)(((float)report.
idade17_22) /
((float)report.
inquiridosComResposta) * 100.0
);
query = AbstractDao.
getCurrentSession().
createQuery(
"select count(distinct user.sigesCode) from " + OlapStarFactQuestionario.
class.
getName() +
" fc " +
"JOIN fc.olapQuestionario quest " +
"JOIN fc.olapTipologia tipo " +
"JOIN fc.olapPerfilUser user " +
"JOIN fc.olapCurso curso " +
"JOIN fc.olapTimeLine timeLine " +
"where " +
"user.birthDate <= :dateStart " +
"and user.birthDate > :dateEnd " +
"and quest.ano = :ano " +
whereFragmentEntity +
"and fc.respondido = true " +
"and timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery
);
query.
setDate("dateStart",dataQuestionario22.
getTime());
query.
setDate("dateEnd",dataQuestionario25.
getTime());
//query.setString("codigoSiges",teacherCode);
query.
setString("ano",anoLectivo
);
report.
idade23_25 =
((1.5.0/docs/api/java/lang/Long.html">Long) query.
uniqueResult()).
intValue();
report.
idade23_25Percent =
(int)(((float)report.
idade23_25) /
((float)report.
inquiridosComResposta) * 100.0
);
query = AbstractDao.
getCurrentSession().
createQuery(
"select count(distinct user.sigesCode) from " + OlapStarFactQuestionario.
class.
getName() +
" fc " +
"JOIN fc.olapQuestionario quest " +
"JOIN fc.olapTipologia tipo " +
"JOIN fc.olapPerfilUser user " +
"JOIN fc.olapCurso curso " +
"JOIN fc.olapTimeLine timeLine " +
"where " +
"user.birthDate <= :dateStart " +
"and user.birthDate > :dateEnd " +
"and quest.ano = :ano " +
whereFragmentEntity +
"and fc.respondido = true " +
"and timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery
);
query.
setDate("dateStart",dataQuestionario25.
getTime());
query.
setDate("dateEnd",dataQuestionario30.
getTime());
//query.setString("codigoSiges",teacherCode);
query.
setString("ano",anoLectivo
);
report.
idade26_30 =
((1.5.0/docs/api/java/lang/Long.html">Long) query.
uniqueResult()).
intValue();
report.
idade26_30Percent =
(int)(((float)report.
idade26_30) /
((float)report.
inquiridosComResposta) * 100.0
);
query = AbstractDao.
getCurrentSession().
createQuery(
"select count(distinct user.sigesCode) from " + OlapStarFactQuestionario.
class.
getName() +
" fc " +
"JOIN fc.olapQuestionario quest " +
"JOIN fc.olapTipologia tipo " +
"JOIN fc.olapPerfilUser user " +
"JOIN fc.olapCurso curso " +
"JOIN fc.olapTimeLine timeLine " +
"where " +
"user.birthDate <= :dateStart " +
"and quest.ano = :ano " +
whereFragmentEntity +
"and fc.respondido = true " +
"and timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery
);
query.
setDate("dateStart",dataQuestionario30.
getTime());
//query.setString("codigoSiges",teacherCode);
query.
setString("ano",anoLectivo
);
report.
idadeM30 =
((1.5.0/docs/api/java/lang/Long.html">Long) query.
uniqueResult()).
intValue();
report.
idadeM30Percent =
(int)(((float)report.
idadeM30) /
((float)report.
inquiridosComResposta) * 100.0
);
}
public static void countTiposAlunosComRespostasAoDocente
(1.5.0/docs/api/java/lang/String.html">String teacherCode,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
countTiposAlunosComRespostas
(new 1.5.0/docs/api/java/lang/String.html">String[]{teacherCode
},EntityEvaluated.
DOCENTE,anoLectivo,docenteReport,periodos,degrees
);
}
public static void countTiposAlunosComRespostasAoCurso
(1.5.0/docs/api/java/lang/String.html">String courseCode,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
countTiposAlunosComRespostas
(new 1.5.0/docs/api/java/lang/String.html">String[]{courseCode
},EntityEvaluated.
CURSO,anoLectivo,docenteReport,periodos,degrees
);
}
public static void countTiposAlunosComRespostasAoCursos
(1.5.0/docs/api/java/lang/String.html">String[] courseCodes,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
countTiposAlunosComRespostas
(courseCodes,EntityEvaluated.
CURSOS,anoLectivo,docenteReport,periodos,degrees
);
}
public static void countTiposAlunosComRespostasAoCursos
(List
<String
> courseCodes,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
countTiposAlunosComRespostas
((1.5.0/docs/api/java/lang/String.html">String[]) courseCodes.
toArray(),EntityEvaluated.
CURSOS,anoLectivo,docenteReport,periodos,degrees
);
}
private static void countTiposAlunosComRespostas
(1.5.0/docs/api/java/lang/String.html">String[] codes,EntityEvaluated entityEvaluated,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
) {
1.5.0/docs/api/java/lang/String.html">String periodosQuery = QueryDaoUtils.
createPeriodosQuery(periodos,
"quest",
true);
1.5.0/docs/api/java/lang/String.html">String degreesQuery = QueryDaoUtils.
createDegreesQuery(degrees,
"curso",
true);
1.5.0/docs/api/javax/management/Query.html">Query query
;
5+0%2Fdocs%2Fapi+Object">Object result
;
1.5.0/docs/api/java/lang/String.html">String whereFragmentEntity = QuestionariosQueryDao.
getWhereEntityCodesFragment(codes, entityEvaluated
);
query = AbstractDao.
getCurrentSession().
createQuery("" +
"SELECT " +
"count(distinct user.sigesCode) " +
"FROM " + OlapStarFactQuestionario.
class.
getName() +
" fc " +
"JOIN fc.olapQuestionario quest " +
"JOIN fc.olapTipologia tipo " +
"JOIN fc.olapPerfilUser user " +
"JOIN fc.olapCurso curso " +
"JOIN fc.olapTimeLine timeLine " +
"where quest.ano = :ano " +
whereFragmentEntity +
"and fc.respondido = true and user.tipoAlunoTrabalhadorEstudante = true " +
"and timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery
);
//query.setString("codigoSiges",teacherCode);
query.
setString("ano",anoLectivo
);
result = query.
uniqueResult();
docenteReport.
tipoAlunosTrabalhadorEstudanteComResposta =
((1.5.0/docs/api/java/lang/Long.html">Long) result
).
intValue();
query = AbstractDao.
getCurrentSession().
createQuery("" +
"SELECT " +
"count(distinct user.sigesCode) " +
"FROM " + OlapStarFactQuestionario.
class.
getName() +
" fc " +
"JOIN fc.olapQuestionario quest " +
"JOIN fc.olapTipologia tipo " +
"JOIN fc.olapPerfilUser user " +
"JOIN fc.olapCurso curso " +
"JOIN fc.olapTimeLine timeLine " +
"where quest.ano = :ano " +
whereFragmentEntity +
"and fc.respondido = true and user.tipoAlunoErasmus = true " +
"and timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery
);
//query.setString("codigoSiges",teacherCode);
query.
setString("ano",anoLectivo
);
result = query.
uniqueResult();
docenteReport.
tipoAlunosErasmusComResposta =
((1.5.0/docs/api/java/lang/Long.html">Long) result
).
intValue();
docenteReport.
tipoAlunosNormalComResposta = docenteReport.
inquiridosComResposta - docenteReport.
tipoAlunosErasmusComResposta - docenteReport.
tipoAlunosTrabalhadorEstudanteComResposta;
docenteReport.
tipoAlunosErasmusComRespostaPercent =
(int)((((float)docenteReport.
tipoAlunosErasmusComResposta) /
((float) docenteReport.
inquiridosComResposta)) * 100.0
);
docenteReport.
tipoAlunosTrabalhadorEstudanteComRespostaPercent =
(int)((((float)docenteReport.
tipoAlunosTrabalhadorEstudanteComResposta) /
((float) docenteReport.
inquiridosComResposta)) * 100.0
);
docenteReport.
tipoAlunosNormalComRespostaPercent =
(int)((((float)docenteReport.
tipoAlunosNormalComResposta) /
((float) docenteReport.
inquiridosComResposta)) * 100.0
);
}
public static int countAlunosQueResponderamAoDocente
(1.5.0/docs/api/java/lang/String.html">String teacherCode,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
return countAlunosQueResponderamGen
(new 1.5.0/docs/api/java/lang/String.html">String[]{teacherCode
},EntityEvaluated.
DOCENTE,anoLectivo,docenteReport,periodos,degrees
);
}
public static int countAlunosQueResponderamAoCurso
(1.5.0/docs/api/java/lang/String.html">String courseCode,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
return countAlunosQueResponderamGen
(new 1.5.0/docs/api/java/lang/String.html">String[]{courseCode
},EntityEvaluated.
CURSO,anoLectivo,docenteReport,periodos,degrees
);
}
public static int countAlunosQueResponderamAoCursos
(1.5.0/docs/api/java/lang/String.html">String[] courseCodes,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
return countAlunosQueResponderamGen
(courseCodes,EntityEvaluated.
CURSOS,anoLectivo,docenteReport,periodos,degrees
);
}
public static int countAlunosQueResponderamAoCursos
(List
<String
> courseCodes,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
)
{
return countAlunosQueResponderamGen
((1.5.0/docs/api/java/lang/String.html">String[]) courseCodes.
toArray(),EntityEvaluated.
CURSOS,anoLectivo,docenteReport,periodos,degrees
);
}
/**
* devolve respostas totais
* preenche os inquiridos com resposta incluindo os masculinos e femininos
* @param codes
* @param entityEvaluated
* @param anoLectivo
* @param report
* @param periodos
* @param degrees
* @return
*/
public static int countAlunosQueResponderamGen
(1.5.0/docs/api/java/lang/String.html">String[] codes,EntityEvaluated entityEvaluated,
1.5.0/docs/api/java/lang/String.html">String anoLectivo, QuestionariosAlunosReport report,
1.5.0/docs/api/java/lang/String.html">String[] periodos,
1.5.0/docs/api/java/lang/String.html">String[] degrees
) {
1.5.0/docs/api/java/lang/String.html">String periodosQuery = QueryDaoUtils.
createPeriodosQuery(periodos,
"quest",
true);
1.5.0/docs/api/java/lang/String.html">String degreesQuery = QueryDaoUtils.
createDegreesQuery(degrees,
"curso",
true);
1.5.0/docs/api/java/lang/String.html">String whereFragmentEntity = QuestionariosQueryDao.
getWhereEntityCodesFragment(codes, entityEvaluated
);
/***** HOMENS MULHERES QUE RESPONDERAM ********/
1.5.0/docs/api/javax/management/Query.html">Query query = AbstractDao.
getCurrentSession().
createQuery("" +
"SELECT " +
"count(distinct user.sigesCode) " +
"FROM " + OlapStarFactQuestionario.
class.
getName() +
" fc " +
"JOIN fc.olapQuestionario quest " +
"JOIN fc.olapTipologia tipo " +
"JOIN fc.olapPerfilUser user " +
"JOIN fc.olapCurso curso " +
"JOIN fc.olapTimeLine timeLine " +
"where " +
"quest.ano = :ano " +
whereFragmentEntity +
"and fc.respondido = true " +
"and user.sex='M' " +
"and timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery
);
//query.setString("codigoSiges",code);
query.
setString("ano",anoLectivo
);
5+0%2Fdocs%2Fapi+Object">Object result = query.
uniqueResult();
report.
masculinoComResposta =
((1.5.0/docs/api/java/lang/Long.html">Long) result
).
intValue();
query = AbstractDao.
getCurrentSession().
createQuery("" +
"SELECT " +
"count(distinct user.sigesCode) " +
"FROM " + OlapStarFactQuestionario.
class.
getName() +
" fc " +
"JOIN fc.olapQuestionario quest " +
"JOIN fc.olapTipologia tipo " +
"JOIN fc.olapPerfilUser user " +
"JOIN fc.olapCurso curso " +
"JOIN fc.olapTimeLine timeLine " +
"where quest.ano = :ano " +
whereFragmentEntity +
"and fc.respondido = true " +
"and user.sex='F' " +
"and timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery
);
//query.setString("codigoSiges",code);
query.
setString("ano",anoLectivo
);
result = query.
uniqueResult();
report.
femininoComResposta =
((1.5.0/docs/api/java/lang/Long.html">Long) result
).
intValue();
//BASTA TER UMA RESPOSTA CONSIDERA-SE QUE RESPONDEU AOS QUESTIONARIOS
report.
inquiridosComResposta = report.
masculinoComResposta + report.
femininoComResposta;
report.
masculinoComRespostaPercent =
(int)((((float)report.
masculinoComResposta) /
((float) report.
inquiridosComResposta)) * 100.0
);
report.
femininoComRespostaPercent =
(int)((((float)report.
femininoComResposta) /
((float) report.
inquiridosComResposta)) * 100.0
);
/*TAXA DE RESPOSTA NAO SE PODE RETIRAR OS ALUNOS SEM RESPOSTA PORQUE PODEM REPETIR NO CASO DE TEREM RESPONDIDO A UMAS E NAO A OUTRAS*/
query = AbstractDao.
getCurrentSession().
createQuery("" +
"SELECT " +
"count(distinct user.sigesCode) " +
"FROM " + OlapStarFactQuestionario.
class.
getName() +
" fc " +
"JOIN fc.olapQuestionario quest " +
"JOIN fc.olapTipologia tipo " +
"JOIN fc.olapPerfilUser user " +
"JOIN fc.olapCurso curso " +
"JOIN fc.olapTimeLine timeLine " +
"where quest.ano = :ano " +
whereFragmentEntity +
"and timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery
);
//query.setString("codigoSiges",code);
query.
setString("ano",anoLectivo
);
result = query.
uniqueResult();
report.
inquiridosTotal =
((1.5.0/docs/api/java/lang/Long.html">Long) result
).
intValue();
report.
inquiridosNaoResponderam = report.
inquiridosTotal - report.
inquiridosComResposta;
return report.
inquiridosComResposta;
}
}