/**
   * 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;
  }
  @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));
    }
  }
  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(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));
    }
  }