/**
* Returns descriptive statistics for numeric date answers (minimum, maximum)
*
* @param question
* @return
*/
private List<QuestionStatistic> getDateDescriptiveStatistics(
Question question, final Long totalRecordCount) {
Long surveyDefinitionId = question.getPage().getSurveyDefinition().getId();
Short pageOrder = question.getPage().getOrder();
Short questionOrder = question.getOrder();
final String columnName = "p" + pageOrder + "q" + questionOrder;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append(
"select MIN(d." + columnName + ") as min ,MAX(d." + columnName + ") as max ");
stringBuilder.append(
" from survey_data_"
+ surveyDefinitionId
+ " d inner join survey s on (s.id=d.survey_id and s.status='S')");
String selectSQLStatement = stringBuilder.toString();
List<QuestionStatistic> questionStatistics =
this.jdbcTemplate.query(
selectSQLStatement,
new RowMapper<QuestionStatistic>() {
public QuestionStatistic mapRow(ResultSet rs, int rowNum) throws SQLException {
QuestionStatistic questionStatistic = new QuestionStatistic();
questionStatistic.setMinDate(rs.getDate("min"));
questionStatistic.setMaxDate(rs.getDate("max"));
questionStatistic.setTotalCount(totalRecordCount);
return questionStatistic;
}
});
return questionStatistics;
}
/**
* Returns frequency statistics for a choice question' answers (Value, Count)
*
* @param question
* @return
*/
private List<QuestionStatistic> getFrequencyStatistics(
Question question, final Long totalRecordCount) {
Long surveyDefinitionId = question.getPage().getSurveyDefinition().getId();
Short pageOrder = question.getPage().getOrder();
Short questionOrder = question.getOrder();
final String columnName = "p" + pageOrder + "q" + questionOrder;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("select d." + columnName + " as col, count(*) as total ");
stringBuilder.append(
" from survey_data_"
+ surveyDefinitionId
+ " d inner join survey s on (s.id=d.survey_id and s.status='S')");
stringBuilder.append(" group by d." + columnName);
String selectSQLStatement = stringBuilder.toString();
List<QuestionStatistic> questionStatistics =
this.jdbcTemplate.query(
selectSQLStatement,
new RowMapper<QuestionStatistic>() {
public QuestionStatistic mapRow(ResultSet rs, int rowNum) throws SQLException {
QuestionStatistic questionStatistic = new QuestionStatistic();
questionStatistic.setEntry(rs.getString("col"));
questionStatistic.setCount(rs.getLong("total"));
questionStatistic.setTotalCount(totalRecordCount);
return questionStatistic;
}
});
return questionStatistics;
}
/**
* Returns descriptive statistics for numeric matrix question' answers (minimum, maximum, average,
* standard deviation)
*
* @param question
* @return
*/
private List<QuestionStatistic> getMatrixDescriptiveStatistics(
Question question, final Long totalRecordCount) {
List<QuestionStatistic> questionStatistics = new ArrayList<QuestionStatistic>();
Long surveyDefinitionId = question.getPage().getSurveyDefinition().getId();
Short pageOrder = question.getPage().getOrder();
Short questionOrder = question.getOrder();
for (QuestionRowLabel row : question.getRowLabels()) {
for (QuestionColumnLabel column : question.getColumnLabels()) {
final Short columnOrder = column.getOrder();
final Short rowOrder = row.getOrder();
final String columnName =
"p" + pageOrder + "q" + questionOrder + "r" + rowOrder + "c" + columnOrder;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append(
"select MIN(d."
+ columnName
+ ") as min ,MAX(d."
+ columnName
+ ") as max ,AVG(d."
+ columnName
+ ") as avg ,STDDEV_SAMP(d."
+ columnName
+ ") as std ");
stringBuilder.append(
" from survey_data_"
+ surveyDefinitionId
+ " d inner join survey s on (s.id=d.survey_id and s.status='S')");
String selectSQLStatement = stringBuilder.toString();
List<QuestionStatistic> questionCellStatistics =
this.jdbcTemplate.query(
selectSQLStatement,
new RowMapper<QuestionStatistic>() {
public QuestionStatistic mapRow(ResultSet rs, int rowNum) throws SQLException {
QuestionStatistic questionStatistic = new QuestionStatistic();
questionStatistic.setColumnOrder(columnOrder);
questionStatistic.setRowOrder(rowOrder);
questionStatistic.setMin(rs.getDouble("min"));
questionStatistic.setMax(rs.getDouble("max"));
questionStatistic.setAverage(rs.getDouble("avg"));
questionStatistic.setSampleStandardDeviation(rs.getDouble("std"));
questionStatistic.setTotalCount(totalRecordCount);
return questionStatistic;
}
});
questionStatistics.addAll(questionCellStatistics);
} // loop on columns
} // loop on rows
return questionStatistics;
}