/**
* 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 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 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;
}
/** Computes question answer statistics */
@Override
public List<QuestionStatistic> getStatistics(Question question, Long totalRecordCount) {
switch (question.getType()) {
case YES_NO_DROPDOWN:
return getFrequencyStatistics(question, totalRecordCount);
case SHORT_TEXT_INPUT:
return null;
case LONG_TEXT_INPUT:
return null;
case HUGE_TEXT_INPUT:
return null;
case INTEGER_INPUT:
return getDescriptiveStatistics(question, totalRecordCount);
case CURRENCY_INPUT:
return getDescriptiveStatistics(question, totalRecordCount);
case DECIMAL_INPUT:
return getDescriptiveStatistics(question, totalRecordCount);
case DATE_INPUT:
return getDateDescriptiveStatistics(question, totalRecordCount);
case SINGLE_CHOICE_DROP_DOWN:
return getFrequencyStatistics(question, totalRecordCount);
case MULTIPLE_CHOICE_CHECKBOXES:
return getArrayFrequencyStatistics(question, totalRecordCount);
case DATASET_DROP_DOWN:
return getFrequencyStatistics(question, totalRecordCount);
case SINGLE_CHOICE_RADIO_BUTTONS:
return getFrequencyStatistics(question, totalRecordCount);
case YES_NO_DROPDOWN_MATRIX:
return getMatrixFrequencyStatistics(question, totalRecordCount);
case SHORT_TEXT_INPUT_MATRIX:
return null;
case INTEGER_INPUT_MATRIX:
return getMatrixDescriptiveStatistics(question, totalRecordCount);
case CURRENCY_INPUT_MATRIX:
return getMatrixDescriptiveStatistics(question, totalRecordCount);
case DECIMAL_INPUT_MATRIX:
return getMatrixDescriptiveStatistics(question, totalRecordCount);
case DATE_INPUT_MATRIX:
return getDateMatrixDescriptiveStatistics(question, totalRecordCount);
case IMAGE_DISPLAY:
return null;
case VIDEO_DISPLAY:
return null;
case FILE_UPLOAD:
return null;
case STAR_RATING:
return getFrequencyStatistics(question, totalRecordCount);
case SMILEY_FACES_RATING:
return getFrequencyStatistics(question, totalRecordCount);
default:
return null;
}
}
void populateEditForm(Model uiModel, Question question, User user) {
log.info("populateEditForm()");
try {
short i = (short) question.getPage().getQuestions().size();
uiModel.addAttribute("question", question);
uiModel.addAttribute("regularExpressions", surveySettingsService.regularExpression_findAll());
uiModel.addAttribute("questionOptions", question.getType());
uiModel.addAttribute("datasets", surveySettingsService.dataSet_findAll());
uiModel.addAttribute(
"surveyDefinitionPages", surveySettingsService.surveyDefinitionPage_findAll());
if (i != 0) {
uiModel.addAttribute("size", i);
} else {
uiModel.addAttribute("size", size);
}
} catch (Exception e) {
log.error(e.getMessage(), e);
throw (new RuntimeException(e));
}
}
@Secured({"ROLE_ADMIN", "ROLE_SURVEY_ADMIN"})
@RequestMapping(value = "/{id}", params = "create", produces = "text/html")
public String createQuestion(
@PathVariable("id") Long surveyDefinitionPageId,
Principal principal,
Model uiModel,
HttpServletRequest httpServletRequest) {
log.info("createForm(): handles param form");
try {
String login = principal.getName();
User user = userService.user_findByLogin(login);
SurveyDefinitionPage surveyDefinitionPage =
surveySettingsService.surveyDefinitionPage_findById(surveyDefinitionPageId);
// Check if the user is authorized
if (!securityService.userIsAuthorizedToManageSurvey(
surveyDefinitionPage.getSurveyDefinition().getId(), user)
&& !securityService.userBelongsToDepartment(
surveyDefinitionPage.getSurveyDefinition().getDepartment().getId(), user)) {
log.warn(
"Unauthorized access to url path "
+ httpServletRequest.getPathInfo()
+ " attempted by user login:"******"from IP:"
+ httpServletRequest.getLocalAddr());
return "accessDenied";
}
// User user = userService.user_findByLogin(principal.getName());
// SurveyDefinitionPage surveyDefinitionPage =
// surveySettingsService.surveyDefinitionPage_findById(surveyDefinitionPageId);
Question question = new Question(surveyDefinitionPage);
size = (short) question.getPage().getQuestions().size();
populateEditForm(uiModel, question, user);
return "settings/questions/create";
} catch (Exception e) {
log.error(e.getMessage(), e);
throw (new RuntimeException(e));
}
}
@Secured({"ROLE_ADMIN", "ROLE_SURVEY_ADMIN"})
@RequestMapping(value = "/{id}", method = RequestMethod.DELETE, produces = "text/html")
public String delete(
@PathVariable("id") Long id,
Principal principal,
Model uiModel,
HttpServletRequest httpServletRequest) {
log.info("delete(): id=" + id);
try {
Question question = surveySettingsService.question_findById(id);
String login = principal.getName();
User user = userService.user_findByLogin(login);
// SurveyDefinitionPage surveyDefinitionPage =
// surveySettingsService.surveyDefinitionPage_findById(surveyDefinitionPageId);
// Check if the user is authorized
if (!securityService.userIsAuthorizedToManageSurvey(
question.getPage().getSurveyDefinition().getId(), user)
&& !securityService.userBelongsToDepartment(
question.getPage().getSurveyDefinition().getDepartment().getId(), user)) {
log.warn(
"Unauthorized access to url path "
+ httpServletRequest.getPathInfo()
+ " attempted by user login:"******"from IP:"
+ httpServletRequest.getLocalAddr());
return "accessDenied";
}
surveySettingsService.question_remove(id);
uiModel.asMap().clear();
return "redirect:/settings/surveyDefinitions/"
+ encodeUrlPathSegment(
question.getPage().getSurveyDefinition().getId().toString(), httpServletRequest);
} catch (Exception e) {
log.error(e.getMessage(), e);
throw (new RuntimeException(e));
}
}
@Secured({"ROLE_ADMIN", "ROLE_SURVEY_ADMIN"})
@RequestMapping(method = RequestMethod.PUT, produces = "text/html")
public String update(
@RequestParam(value = "_proceed", required = false) String proceed,
@Valid Question question,
BindingResult bindingResult,
Principal principal,
Model uiModel,
HttpServletRequest httpServletRequest) {
log.info("update(): handles PUT");
try {
// User user = userService.user_findByLogin(principal.getName());
String login = principal.getName();
User user = userService.user_findByLogin(login);
// SurveyDefinitionPage surveyDefinitionPage =
// surveySettingsService.surveyDefinitionPage_findById(surveyDefinitionPageId);
// surveySettingsService.question_findById(question.getId()).getPage().getSurveyDefinition().getId()
// Check if the user is authorized
if (!securityService.userIsAuthorizedToManageSurvey(
question.getPage().getSurveyDefinition().getId(), user)
&& !securityService.userBelongsToDepartment(
question.getPage().getSurveyDefinition().getDepartment().getId(), user)) {
log.warn(
"Unauthorized access to url path "
+ httpServletRequest.getPathInfo()
+ " attempted by user login:"******"from IP:"
+ httpServletRequest.getLocalAddr());
return "accessDenied";
}
if (proceed != null) {
if (bindingResult.hasErrors()) {
populateEditForm(uiModel, question, user);
log.info(
"-------------------------------------------"
+ bindingResult.getFieldErrors().toString());
return "settings/questions/update";
}
if (!surveySettingsService.question_ValidateDateRange(question)) {
populateEditForm(uiModel, question, user);
bindingResult.rejectValue("dateMinimum", "date_format_validation_range");
return "settings/questions/update";
}
if (!surveySettingsService.question_ValidateMinMaxDoubleValues(question)) {
populateEditForm(uiModel, question, user);
bindingResult.rejectValue("decimalMinimum", "field_min_invalid");
return "settings/questions/update";
}
if (!surveySettingsService.question_ValidateMinMaxValues(question)) {
populateEditForm(uiModel, question, user);
bindingResult.rejectValue("integerMinimum", "field_min_invalid");
return "settings/questions/update";
}
if (question.getSuportsOptions()) {
// If user wants to modify and existent question without
// options to Rating type, then use the default values
int NumberOfQuestionOptions = 0;
Set<QuestionOption> qOpts =
surveySettingsService.questionOption_findByQuestionId(question.getId());
for (QuestionOption q : qOpts) {
NumberOfQuestionOptions++;
}
if ((question.getType().toString() == "SMILEY_FACES_RATING"
|| question.getType().toString() == "STAR_RATING")
&& NumberOfQuestionOptions != 5) {
log.info(
"Removing Question Options since the amount of Questions Options for Rating Type cannot be longer than 5 Qoptions");
surveySettingsService.questionOption_removeQuestionOptionsByQuestionId(
question.getId());
SortedSet<QuestionOption> options = new TreeSet<QuestionOption>();
options.add(
new QuestionOption(
question,
(short) 1,
"1",
messageSource.getMessage(
EXTREMELY_UNSATISFIED_LABEL, null, LocaleContextHolder.getLocale())));
options.add(
new QuestionOption(
question,
(short) 2,
"2",
messageSource.getMessage(
UNSATISFIED_LABEL, null, LocaleContextHolder.getLocale())));
options.add(
new QuestionOption(
question,
(short) 3,
"3",
messageSource.getMessage(
NEUTRAL_LABEL, null, LocaleContextHolder.getLocale())));
options.add(
new QuestionOption(
question,
(short) 4,
"4",
messageSource.getMessage(
SATISFIED_LABEL, null, LocaleContextHolder.getLocale())));
options.add(
new QuestionOption(
question,
(short) 5,
"5",
messageSource.getMessage(
EXTREMELY_SATISFIED_LABEL, null, LocaleContextHolder.getLocale())));
// Adding default values to Rating Type Question
log.info("Adding default values to Rating Type Question");
question = surveySettingsService.question_merge(question, options);
uiModel.asMap().clear();
return "settings/questions/saved";
} else {
Policy questionTextPolicy =
Policy.getInstance(this.getClass().getResource(POLICY_FILE_LOCATION));
AntiSamy emailAs = new AntiSamy();
CleanResults crQuestionText =
emailAs.scan(question.getQuestionText(), questionTextPolicy);
question.setQuestionText(crQuestionText.getCleanHTML());
Policy questionTipPolicy =
Policy.getInstance(this.getClass().getResource(POLICY_FILE_LOCATION));
AntiSamy completedSurveyAs = new AntiSamy();
CleanResults crQuestionTip =
completedSurveyAs.scan(question.getTip(), questionTipPolicy);
question.setTip(crQuestionTip.getCleanHTML());
question = surveySettingsService.question_merge(question);
uiModel.asMap().clear();
return "settings/questions/saved";
}
}
question = surveySettingsService.question_merge(question);
uiModel.asMap().clear();
return "settings/questions/saved";
} else {
return "redirect:/settings/surveyDefinitions/"
+ encodeUrlPathSegment(
question.getPage().getSurveyDefinition().getId().toString(), httpServletRequest);
}
} catch (Exception e) {
log.error(e.getMessage(), e);
throw (new RuntimeException(e));
}
}
@Secured({"ROLE_ADMIN", "ROLE_SURVEY_ADMIN"})
@RequestMapping(method = RequestMethod.POST, produces = "text/html")
public String create(
@RequestParam(value = "_proceed", required = false) String proceed,
@Valid Question question,
BindingResult bindingResult,
Principal principal,
Model uiModel,
HttpServletRequest httpServletRequest) {
log.info("create(): handles " + RequestMethod.POST.toString());
try {
String login = principal.getName();
User user = userService.user_findByLogin(login);
// SurveyDefinitionPage surveyDefinitionPage =
// surveySettingsService.surveyDefinitionPage_findById(surveyDefinitionPageId);
// Check if the user is authorized
if (!securityService.userIsAuthorizedToManageSurvey(
question.getPage().getSurveyDefinition().getId(), user)
&& !securityService.userBelongsToDepartment(
question.getPage().getSurveyDefinition().getDepartment().getId(), user)) {
log.warn(
"Unauthorized access to url path "
+ httpServletRequest.getPathInfo()
+ " attempted by user login:"******"from IP:"
+ httpServletRequest.getLocalAddr());
return "accessDenied";
}
// User user = userService.user_findByLogin(principal.getName());
if (proceed != null) {
if (bindingResult.hasErrors()) {
populateEditForm(uiModel, question, user);
return "settings/questions/create";
}
if (!surveySettingsService.question_ValidateDateRange(question)) {
populateEditForm(uiModel, question, user);
bindingResult.rejectValue("dateMinimum", "date_format_validation_range");
return "settings/questions/create";
}
// validate Double min max
if (!surveySettingsService.question_ValidateMinMaxDoubleValues(question)) {
populateEditForm(uiModel, question, user);
bindingResult.rejectValue("decimalMinimum", "field_min_invalid");
return "settings/questions/create";
}
// validate Integer min max
if (!surveySettingsService.question_ValidateMinMaxValues(question)) {
populateEditForm(uiModel, question, user);
bindingResult.rejectValue("integerMinimum", "field_min_invalid");
return "settings/questions/create";
}
if (question.getType().getIsRating()) {
SortedSet<QuestionOption> options = new TreeSet<QuestionOption>();
options.add(
new QuestionOption(
question,
(short) 1,
"1",
messageSource.getMessage(
EXTREMELY_UNSATISFIED_LABEL, null, LocaleContextHolder.getLocale())));
options.add(
new QuestionOption(
question,
(short) 2,
"2",
messageSource.getMessage(
UNSATISFIED_LABEL, null, LocaleContextHolder.getLocale())));
options.add(
new QuestionOption(
question,
(short) 3,
"3",
messageSource.getMessage(NEUTRAL_LABEL, null, LocaleContextHolder.getLocale())));
options.add(
new QuestionOption(
question,
(short) 4,
"4",
messageSource.getMessage(
SATISFIED_LABEL, null, LocaleContextHolder.getLocale())));
options.add(
new QuestionOption(
question,
(short) 5,
"5",
messageSource.getMessage(
EXTREMELY_SATISFIED_LABEL, null, LocaleContextHolder.getLocale())));
question = surveySettingsService.question_merge(question, options);
}
// if (question.getPublishToSocrata().equals(true)){
// bindingResult.rejectValue("socrataColumnName",
// "field_min_invalid");
// return "settings/questions/create";
// }
else {
Policy questionTextPolicy =
Policy.getInstance(this.getClass().getResource(POLICY_FILE_LOCATION));
AntiSamy emailAs = new AntiSamy();
CleanResults crQuestionText =
emailAs.scan(question.getQuestionText(), questionTextPolicy);
question.setQuestionText(crQuestionText.getCleanHTML());
Policy questionTipPolicy =
Policy.getInstance(this.getClass().getResource(POLICY_FILE_LOCATION));
AntiSamy completedSurveyAs = new AntiSamy();
CleanResults crQuestionTip = completedSurveyAs.scan(question.getTip(), questionTipPolicy);
question.setTip(crQuestionTip.getCleanHTML());
question = surveySettingsService.question_merge(question);
}
uiModel.asMap().clear();
return "settings/questions/saved";
} else {
return "redirect:/settings/surveyDefinitions/"
+ encodeUrlPathSegment(
question.getPage().getSurveyDefinition().getId().toString(), httpServletRequest);
}
} catch (Exception e) {
log.error(e.getMessage(), e);
throw (new RuntimeException(e));
}
}