package pt.estgp.estgweb.services.questionarios.pedagogico.reportprocessors.queries;
import jomm.dao.impl.AbstractDao;
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.reportsdomain.EntityEvaluated;
import pt.estgp.estgweb.services.questionarios.pedagogico.reportprocessors.reportsdomain.QuestionariosAlunosReport;
import pt.estgp.estgweb.services.questionarios.pedagogico.reportprocessors.reportsdomain.QuestionariosReport;
import pt.estgp.estgweb.services.questionarios.pedagogico.reportprocessors.utils.DataTable;
import java.util.List;
/**
* Created by jorgemachado on 07/12/16.
*/
public class QuestionariosQueryDao
{
public static 1.5.0/docs/api/java/lang/String.html">String getWhereEntityCodesFragment
(1.5.0/docs/api/java/lang/String.html">String[] codes, EntityEvaluated entityEvaluated
) {
1.5.0/docs/api/java/lang/String.html">String whereFragmentEntity =
"" ;
if(entityEvaluated == EntityEvaluated.
DOCENTE)
{
whereFragmentEntity =
" and tipo.codigoSiges = '" + codes
[0] +
"' ";
}
else if(entityEvaluated == EntityEvaluated.
CURSO)
{
whereFragmentEntity =
" and curso.codigoCurso = '" + codes
[0] +
"' ";
}
else if(entityEvaluated == EntityEvaluated.
ESCOLA)
{
whereFragmentEntity =
" and curso.codigoInstituicao = '" + codes
[0] +
"' ";
}
else if(entityEvaluated == EntityEvaluated.
CURSOS)
{
1.5.0/docs/api/java/lang/String.html">String union =
" ";
for(1.5.0/docs/api/java/lang/String.html">String code:codes
)
{
whereFragmentEntity += union +
" curso.codigoCurso = '" + code +
"' ";
union =
" or ";
}
whereFragmentEntity =
" and (" + whereFragmentEntity +
") ";
}
else
{
throw new 1.5.0/docs/api/java/lang/RuntimeException.html">RuntimeException("Nao esta a entidade em avaliacao implementada");
}
return whereFragmentEntity
;
}
public static void countQuestionariosRespostasAoDocente
(1.5.0/docs/api/java/lang/String.html">String teacherCode,
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
)
{
countQuestionariosRespostasGen
(new 1.5.0/docs/api/java/lang/String.html">String[]{teacherCode
},EntityEvaluated.
DOCENTE,anoLectivo,report,periodos,degrees
);
}
public static void countQuestionariosRespostasAoCurso
(1.5.0/docs/api/java/lang/String.html">String courseCode,
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
)
{
countQuestionariosRespostasGen
(new 1.5.0/docs/api/java/lang/String.html">String[]{courseCode
},EntityEvaluated.
CURSO,anoLectivo,report,periodos,degrees
);
}
public static void countQuestionariosRespostasAoCursos
(1.5.0/docs/api/java/lang/String.html">String[] courseCodes,
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
)
{
countQuestionariosRespostasGen
(courseCodes,EntityEvaluated.
CURSOS,anoLectivo,report,periodos,degrees
);
}
public static void countQuestionariosRespostasAoCursos
(List
<String
> courseCodes,
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
)
{
countQuestionariosRespostasGen
((1.5.0/docs/api/java/lang/String.html">String[]) courseCodes.
toArray(),EntityEvaluated.
CURSOS,anoLectivo,report,periodos,degrees
);
}
public static void countQuestionariosRespostasGen
(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/java/lang/String.html">String whereFragmentEntity = getWhereEntityCodesFragment
(codes, entityEvaluated
);
1.5.0/docs/api/javax/management/Query.html">Query query
;
5+0%2Fdocs%2Fapi+Object">Object result
;
//Questionários Respondidos
query = AbstractDao.
getCurrentSession().
createQuery("" +
"SELECT " +
"sum(case when fc.respondido = true then 1 else 0 end) as respondido," +
"sum(case when fc.respondido = false then 1 else 0 end) as naoRespondido " +
"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
);
5+0%2Fdocs%2Fapi+Object">Object[] result2 =
(5+0%2Fdocs%2Fapi+Object">Object[]) query.
uniqueResult();
docenteReport.
questionariosReqRespondidos =
((1.5.0/docs/api/java/lang/Long.html">Long) result2
[0
]).
intValue();
docenteReport.
questionariosReqNaoRespondidos =
((1.5.0/docs/api/java/lang/Long.html">Long) result2
[1
]).
intValue();
docenteReport.
questionariosReqTotal = docenteReport.
questionariosReqRespondidos + docenteReport.
questionariosReqNaoRespondidos;
docenteReport.
questionariosReqRespondidosPercentagem =
(int)(((float)docenteReport.
questionariosReqRespondidos/
(float)docenteReport.
questionariosReqTotal)*100.
f);
docenteReport.
questionariosReqRespondidosPercentagemColor = DataTable.
getColorGradientForPercentage(((double)docenteReport.
questionariosReqRespondidosPercentagem)/100.0
);
//docenteReport.questionariosReqRespondidosPercentagem =53;
}
public static void createDataTableLocalidadesAoDocente
(QuestionariosReport report,
1.5.0/docs/api/java/lang/String.html">String teacherCode,
1.5.0/docs/api/java/lang/String.html">String anoLectivo,
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 targetPopulationTitle
) {
createDataTableLocalidadesGen
(report,
new 1.5.0/docs/api/java/lang/String.html">String[]{teacherCode
}, EntityEvaluated.
DOCENTE, anoLectivo, periodos, degrees,targetPopulationTitle
);
}
public static void createDataTableLocalidadesAoCurso
(QuestionariosReport report,
1.5.0/docs/api/java/lang/String.html">String courseCode,
1.5.0/docs/api/java/lang/String.html">String anoLectivo,
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 targetPopulationTitle
) {
createDataTableLocalidadesGen
(report,
new 1.5.0/docs/api/java/lang/String.html">String[]{courseCode
}, EntityEvaluated.
CURSO, anoLectivo, periodos, degrees,targetPopulationTitle
);
}
public static void createDataTableLocalidadesAoCursos
(QuestionariosReport report,
1.5.0/docs/api/java/lang/String.html">String[] courseCodes,
1.5.0/docs/api/java/lang/String.html">String anoLectivo,
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 targetPopulationTitle
) {
createDataTableLocalidadesGen
(report, courseCodes, EntityEvaluated.
CURSOS, anoLectivo, periodos, degrees,targetPopulationTitle
);
}
public static void createDataTableLocalidadesAoCursos
(QuestionariosReport report, List
<String
> courseCodes,
1.5.0/docs/api/java/lang/String.html">String anoLectivo,
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 targetPopulationTitle
) {
createDataTableLocalidadesGen
(report,
(1.5.0/docs/api/java/lang/String.html">String[]) courseCodes.
toArray(), EntityEvaluated.
CURSOS, anoLectivo, periodos, degrees,targetPopulationTitle
);
}
public static void createDataTableLocalidadesGen
(QuestionariosReport docenteReport,
1.5.0/docs/api/java/lang/String.html">String[] codes, EntityEvaluated entityEvaluated,
1.5.0/docs/api/java/lang/String.html">String anoLectivo,
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 targetPopulationTitle
) {
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 = getWhereEntityCodesFragment
(codes, entityEvaluated
);
1.5.0/docs/api/javax/management/Query.html">Query query = AbstractDao.
getCurrentSession().
createQuery(
"select count(distinct user.sigesCode), user.distrito 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 timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery +
" group by user.distrito " +
"order by count(distinct user.sigesCode) desc");
query.
setMaxResults(4
);
//query.setString("codigoSiges",teacherCode);
query.
setString("ano",anoLectivo
);
List
<Object
[]> localidades = query.
list();
DataTable tableLocalidades =
new DataTable
();
docenteReport.
setTabelaDistritos(tableLocalidades
);
DataTable.
Row header = tableLocalidades.
addRowHeader();
header.
addColTextCenter("Distrito");
header.
addColTextCenter(targetPopulationTitle
);
header.
addColTextCenter("%");
int contados =
0;
for(int i =
0; i
< 3
&& i
< localidades.
size(); i++
)
{
DataTable.
Row rowDistrito = tableLocalidades.
addRowNormal();
1.5.0/docs/api/java/lang/Long.html">Long num =
(1.5.0/docs/api/java/lang/Long.html">Long)localidades.
get(i
)[0
];
contados += num
;
1.5.0/docs/api/java/lang/String.html">String distrit =
(1.5.0/docs/api/java/lang/String.html">String)localidades.
get(i
)[1
];
if(distrit
!=
null)
rowDistrito.
addColTextCenter(distrit
);
if(num
!=
null)
rowDistrito.
addColTextCenter("" + num
);
int percentage =
(int)(((float)(num
) /
((float)docenteReport.
getInquiridosComResposta()) * 100.0
));
rowDistrito.
addColPercentageCenter("" + percentage
);
}
//int contagemOutros = 0;
/*for(int i = 3; i < localidades.size(); i++)
{
Long outro = (Long)localidades.get(i)[0];
if(outro != null)
contagemOutros += outro;
}*/
int contagemOutros = docenteReport.
getInquiridosComResposta() - contados
;
DataTable.
Row rowOutros = tableLocalidades.
addRowNormal();
rowOutros.
addColTextCenter("Outros");
rowOutros.
addColTextCenter(""+contagemOutros
);
int percentage =
(int)(((float)(contagemOutros
) /
((float)docenteReport.
getInquiridosComResposta()) * 100.0
));
rowOutros.
addColPercentageCenter("" + percentage
);
DataTable.
Row footer = tableLocalidades.
addRowFooter();
footer.
addColTextCenter("Total");
footer.
addColTextCenter("" + docenteReport.
getInquiridosComResposta());
footer.
addColPercentageCenter("100");
}
public static void createDataTableIdades
(QuestionariosReport questionariosReport,
1.5.0/docs/api/java/lang/String.html">String targetPopulationTitle
)
{
DataTable tableIdades =
new DataTable
();
questionariosReport.
setTabelaIdades(tableIdades
);
DataTable.
Row header = tableIdades.
addRowHeader();
header.
addColTextCenter("Idade");
header.
addColTextCenter(targetPopulationTitle
);
header.
addColTextCenter("%");
DataTable.
Row row17_22 = tableIdades.
addRowNormal();
row17_22.
addColTextCenter("17 aos 22");
row17_22.
addColTextCenter("" + questionariosReport.
idade17_22);
row17_22.
addColPercentageCenter("" + questionariosReport.
idade17_22Percent);
DataTable.
Row row23_25 = tableIdades.
addRowNormal();
row23_25.
addColTextCenter("23 aos 25");
row23_25.
addColTextCenter("" + questionariosReport.
idade23_25);
row23_25.
addColPercentageCenter("" + questionariosReport.
idade23_25Percent);
DataTable.
Row row26_30 = tableIdades.
addRowNormal();
row26_30.
addColTextCenter("26 aos 30");
row26_30.
addColTextCenter("" + questionariosReport.
idade26_30);
row26_30.
addColPercentageCenter("" + questionariosReport.
idade26_30Percent);
DataTable.
Row rowM30 = tableIdades.
addRowNormal();
rowM30.
addColTextCenter("> 30");
rowM30.
addColTextCenter("" + questionariosReport.
idadeM30);
rowM30.
addColPercentageCenter("" + questionariosReport.
idadeM30Percent);
DataTable.
Row footer = tableIdades.
addRowFooter();
footer.
addColTextCenter("Total");
footer.
addColTextCenter("" + questionariosReport.
getInquiridosComResposta());
footer.
addColPercentageCenter("100");
//return tableIdades;
}
public static List
<Object
[]> obtainEvolucaoRespostas
(1.5.0/docs/api/java/lang/String.html">String[] codes, EntityEvaluated entityEvaluated,
1.5.0/docs/api/java/lang/String.html">String anoLectivo,
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 = getWhereEntityCodesFragment
(codes, entityEvaluated
);
1.5.0/docs/api/javax/management/Query.html">Query query = AbstractDao.
getCurrentSession().
createQuery(
"select count(distinct fc.id), " +
"week(fc.dataFim), " +
"year(fc.dataFim) " +
"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 timeLine.nome = '" + PedagogicoAlunosProcessor.
getHistoryDriveConstraintStatic() +
"' " +
periodosQuery +
degreesQuery +
" group by week(fc.dataFim) " +
"order by week(fc.dataFim) asc");
//query.setString("codigoSiges",teacherCode);
query.
setString("ano",anoLectivo
);
return query.
list();
}
}