/* * * Copyright 2013 Entando S.r.l. (http://www.entando.com) All rights reserved. * * This file is part of Entando software. * Entando is a free software; * You can redistribute it and/or modify it * under the terms of the GNU General Public License (GPL) as published by the Free Software Foundation; version 2. * * See the file License for the specific language governing permissions * and limitations under the License * * * * Copyright 2013 Entando S.r.l. (http://www.entando.com) All rights reserved. * */ package com.agiletec.plugins.jpsurvey.aps.system.services.survey; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.Enumeration; import java.util.Iterator; import java.util.List; import java.util.Set; import com.agiletec.aps.system.ApsSystemUtils; import com.agiletec.aps.util.ApsProperties; import com.agiletec.plugins.jpsurvey.aps.system.services.AbstractSurveyDAO; import com.agiletec.plugins.jpsurvey.aps.system.services.survey.model.Choice; import com.agiletec.plugins.jpsurvey.aps.system.services.survey.model.Question; import com.agiletec.plugins.jpsurvey.aps.system.services.survey.model.Survey; /** * This is the DAO class for the survey objects * @author M.E. Minnai */ public class SurveyDAO extends AbstractSurveyDAO implements ISurveyDAO { /** * This method loads a complete survey. 'Complete' here means all the elements found in the database * for the survey with the given ID, so check your assumptions because 'complete' is neither 'correct' * nor 'logically complete'. The survey is build sorting the position of the question and then the position * of the choices. * @param id the ID of the survey to load * @return the complete survey, null otherwise */ @Override public Survey loadSurvey(int id) { Connection conn = null; Survey survey = null; try { conn = this.getConnection(); survey = this.loadSurvey(conn, id); } catch (Throwable t) { processDaoException(t, "Error while loading the survey", "loadSurvey"); } finally { closeConnection(conn); } return survey; } private Survey loadSurvey(Connection conn, int id) { PreparedStatement stat = null; ResultSet res = null; Survey survey = null; Integer currentQuestionId = null; try { stat = conn.prepareStatement(GET_COMPLETE_SURVEY_BY_ID); stat.setInt(1, id); res = stat.executeQuery(); while (res.next()) { // FIXME usare le costanti per lo shift? if (null == survey) { survey = this.buildSurveyRecordFromResultSet(res); } Integer questionId = res.getInt(18); Question question = null; if (null == currentQuestionId || !questionId.equals(currentQuestionId)) { question = this.buildQuestionRecordFromResultSet(res, 18); if (null == question) continue; survey.getQuestions().add(question); currentQuestionId = questionId; } else { question = survey.getQuestion(currentQuestionId); } Choice choice = this.buildChoiceRecordFromResultSet(res, 25); if (null == choice) { continue; } if (null == question.getChoice(choice.getId())) { question.getChoices().add(choice); } } } catch (Throwable t) { processDaoException(t, "Error while loading the survey ID "+id, "loadSurvey"); } finally { closeDaoResources(res, stat); } return survey; } /** * build a survey object from the record returned by the database. * @param res the result set containing to process * @param survey the resulting survey object or null if errors occur */ private Survey buildSurveyRecordFromResultSet(ResultSet res) { Survey survey = null; if (null == res) { return null; } try { int id = res.getInt(1); if (id > 0) { survey = new Survey(); survey.setQuestions(new ArrayList<Question>()); survey.setId(id); ApsProperties prop = new ApsProperties(); prop.loadFromXml(res.getString(2)); survey.setDescriptions(prop); survey.setGroupName(res.getString(3).trim()); survey.setStartDate(res.getDate(4)); survey.setEndDate(res.getDate(5)); survey.setActive(res.getBoolean(6)); survey.setPublicPartialResult(res.getBoolean(7)); survey.setPublicResult(res.getBoolean(8)); survey.setQuestionnaire(res.getBoolean(9)); survey.setGatherUserInfo(res.getBoolean(10)); prop = new ApsProperties(); prop.loadFromXml(res.getString(11)); survey.setTitles(prop); survey.setRestricted(res.getBoolean(12)); survey.setCheckCookie(res.getBoolean(13)); survey.setCheckIpAddress(res.getBoolean(14)); survey.setCheckUsername(res.getBoolean(15)); survey.setImageId(res.getString(16)); prop = new ApsProperties(); prop.loadFromXml(res.getString(17)); survey.setImageDescriptions(prop); } } catch (Throwable t) { ApsSystemUtils.logThrowable(t, this, "buildSurveyRecordFromResultSet", "Error while building a 'survey' object from the result set"); } return survey; } @Override public void saveSurvey(Survey survey) { Connection conn = null; PreparedStatement stat = null; String NEXT_ID = "SELECT MAX(id) FROM jpsurvey"; try { conn = this.getConnection(); conn.setAutoCommit(false); // SAVE SURVEY int selfGeneratedId = this.getUniqueId( NEXT_ID, conn); stat = conn.prepareStatement(SAVE_SURVEY); survey.setId(selfGeneratedId); int index = 1; stat.setInt(index++, survey.getId()); //1 stat.setString(index++, survey.getDescriptions().toXml()); //2 stat.setString(index++, survey.getGroupName()); //3 stat.setDate(index++, new java.sql.Date(survey.getStartDate().getTime())); //4 Date date = survey.getEndDate(); if (null != date) { stat.setDate(index++, new java.sql.Date(survey.getEndDate().getTime())); //5 } else { stat.setNull(index++, java.sql.Types.DATE); //5 } if (survey.isActive()) { stat.setInt(index++, 1); //6 } else { stat.setInt(index++, 0); //6 } if (survey.isPublicPartialResult()) { stat.setInt(index++, 1); //7 } else { stat.setInt(index++, 0); //7 } if (survey.isPublicResult()) { stat.setInt(index++, 1); //8 } else { stat.setInt(index++, 0); //8 } if (survey.isQuestionnaire()) { stat.setInt(index++, 1); //9 } else { stat.setInt(index++, 0); //9 } if (survey.isGatherUserInfo()) { stat.setInt(index++, 1); //10 } else { stat.setInt(index++, 0); //10 } stat.setString(index++, survey.getTitles().toXml()); //11 if (survey.isRestricted()) { stat.setInt(index++, 1); //12 } else { stat.setInt(index++, 0); //12 } if (survey.isCheckCookie()) { stat.setInt(index++, 1); //13 } else { stat.setInt(index++, 0); //13 } if (survey.isCheckIpAddress()) { stat.setInt(index++, 1); //14 } else { stat.setInt(index++, 0); //14 } if (survey.isCheckUsername()) { stat.setInt(index++, 1); //14 } else { stat.setInt(index++, 0); //14 } if (null != survey.getImageId() && survey.getImageId().length() > 0) { stat.setString(index++, survey.getImageId()); // 15 } else { stat.setNull(index++, java.sql.Types.VARCHAR); // 15 } if (null != survey.getImageDescriptions() && !survey.getImageDescriptions().isEmpty()) { stat.setString(index++, survey.getImageDescriptions().toXml()); //16 } else { stat.setNull(index++, java.sql.Types.VARCHAR); // 16 } stat.executeUpdate(); // SAVE QUESTIONS if (null != survey.getQuestions() && !survey.getQuestions().isEmpty()) { Iterator<Question> itr = survey.getQuestions().iterator(); while (itr.hasNext()) { Question currentQuestion = itr.next(); currentQuestion.setSurveyId(survey.getId()); this.saveQuestion(conn, currentQuestion); } } conn.commit(); } catch (Throwable t) { this.executeRollback(conn); processDaoException(t, "Error while saving the survey loadSurvey", "saveSurvey"); } finally { this.refreshExtraInfo(survey); closeDaoResources(null, stat, conn); } } @Override public void deleteSurvey(int id) { PreparedStatement stat = null; Connection conn = null; try { conn = this.getConnection(); conn.setAutoCommit(false); Survey survey = this.loadSurvey(id); if (null != survey) { // CANCELLA LE DOMANDE for (Question question: survey.getQuestions()) { this.deleteQuestion(conn, question.getId()); } // CANCELLA IL SONDAGGIO stat = conn.prepareStatement(DELETE_SURVEY_BY_ID); stat.setInt(1, id); stat.execute(); } conn.commit(); } catch (Throwable t) { this.executeRollback(conn); processDaoException(t, "Error while deleting the survey", "deleteSurvey"); } finally { closeDaoResources(null, stat, conn); } } @Override public void updateSurvey(Survey survey) { Connection conn = null; PreparedStatement stat = null; try { conn = this.getConnection(); conn.setAutoCommit(false); stat = conn.prepareStatement(UPDATE_SURVEY_BY_ID); int index = 1; stat.setString(index++, survey.getDescriptions().toXml()); // 1 stat.setString(index++, survey.getGroupName()); // 2 stat.setDate(index++, new java.sql.Date(survey.getStartDate().getTime())); // 3 if (null == survey.getEndDate()) { stat.setNull(index++, java.sql.Types.DATE); // 4 } else { stat.setDate(index++, new java.sql.Date(survey.getEndDate().getTime())); // 4 } if (survey.isActive()) { stat.setInt(index++, 1); // 5 } else { stat.setInt(index++, 0); // 5 } if (survey.isPublicPartialResult()) { stat.setInt(index++, 1); // 6 } else { stat.setInt(index++, 0); // 6 } if (survey.isPublicResult()) { stat.setInt(index++, 1); // 7 } else { stat.setInt(index++, 0); // 7 } if (survey.isQuestionnaire()) { stat.setInt(index++, 1); // 8 } else { stat.setInt(index++, 0); // 8 } if (survey.isGatherUserInfo()) { stat.setInt(index++, 1); // 9 } else { stat.setInt(index++, 0); // 9 } stat.setString(index++, survey.getTitles().toXml()); // 10 if (survey.isRestricted()) { stat.setInt(index++, 1); // 11 } else { stat.setInt(index++, 0); // 11 } if (survey.isCheckCookie()) { stat.setInt(index++, 1); //12 } else { stat.setInt(index++, 0); //12 } if (survey.isCheckIpAddress()) { stat.setInt(index++, 1); //13 } else { stat.setInt(index++, 0); //13 } if (survey.isCheckUsername()) { stat.setInt(index++, 1); //13 } else { stat.setInt(index++, 0); //13 } if (null != survey.getImageId() && survey.getImageId().length() > 0) { stat.setString(index++, survey.getImageId()); //14 } else { stat.setNull(index++, java.sql.Types.VARCHAR); //14 } if (null != survey.getImageDescriptions() && !survey.getImageDescriptions().isEmpty()) { stat.setString(index++, survey.getImageDescriptions().toXml()); //15 } else { stat.setNull(index++, java.sql.Types.VARCHAR); // 15 } stat.setInt(index++, survey.getId()); stat.executeUpdate(); // UPDATE QUESTIONS Set<Integer> kept = this.deleteQuestionsInExcess(conn, survey); for (Question currentQuestion: survey.getQuestions()) { if (kept.contains(currentQuestion.getId())) { this.updateQuestion(conn, currentQuestion); } else { currentQuestion.setSurveyId(survey.getId()); // for sake of safety this.saveQuestion(conn, currentQuestion); } } conn.commit(); } catch (Throwable t) { this.executeRollback(conn); this.processDaoException(t, "Error while updating the survey", "updateSurvey"); } finally { this.refreshExtraInfo(survey); closeDaoResources(null, stat, conn); } } @Override public List<Integer> searchSurvey(Integer id, String description, Collection<String> groups, Boolean isActive, Boolean isQuestionnaire, String title, Boolean isPublic) { List<Integer> list = new ArrayList<Integer>(); Connection conn = null; PreparedStatement stat = null; ResultSet res = null; try { int idx=1; conn = this.getConnection(); String query = this.createSearchIdsQueryString(id, description, groups, isActive,isQuestionnaire, title, isPublic); stat = conn.prepareStatement(query); if (null != id) { stat.setInt(idx++, id); } if (null != description && description.length() > 0) { stat.setString(idx++, "%"+description+"%"); } if (null != groups && groups.size() > 0) { Iterator<String> iter = groups.iterator(); while (iter.hasNext()) { String code = iter.next(); stat.setString(idx++, code); } } if (null != title && title.length () > 0) { stat.setString(idx++, "%"+title+"%"); } res = stat.executeQuery(); while (res.next()) { Integer currentId= new Integer(res.getInt(1)); // if the search includes part of the title and / or the description we must perform extra checks if ((null != description && description.trim().length () > 0) || (null != title && title.trim().length () > 0)) { Survey survey = this.loadSurvey(conn, currentId); if (null != description && description.trim().length() > 0) { if (this.searchInProperties(description, survey.getDescriptions())) { list.add(currentId); continue; } } if ((null != title && title.trim().length() > 0)) { if (this.searchInProperties(title, survey.getTitles())) { list.add(currentId); } } } else { // always add if no description or title description are given list.add(currentId); } } } catch (Throwable t) { this.processDaoException(t, "Error while searching for surveys", "searchSurvey"); } finally { closeDaoResources(res, stat, conn); } return list; } @Override public List<Question> getSurveyQuestions(int id) { Survey survey = null; List<Question> list = new ArrayList<Question>(); try { survey = this.loadSurvey(id); if (null != survey) { list = survey.getQuestions(); } } catch (Throwable t) { this.processDaoException(t, "Error getting the questions of a survey", "searchSurveyByIds"); } return list; } private String createSearchIdsQueryString(Integer id, String description, Collection<String> groups, Boolean isActive, Boolean isQuestionnaire, String title, Boolean isRestricted) { StringBuilder query = new StringBuilder("SELECT id FROM jpsurvey "); boolean isWherePresent = false; if (null != id) { if (!isWherePresent) { isWherePresent=true; query.append(" WHERE "); } query.append(" id = ? "); } if (null != description && description.length () > 0) { if (!isWherePresent) { isWherePresent=true; query.append(" WHERE "); } else { query.append(" AND "); } query.append(" LOWER(description) LIKE LOWER(?) "); } if (null != groups && groups.size() > 0) { for (int i = 0; i < groups.size(); i++) { if (i == 0) { if (!isWherePresent) { isWherePresent = true; query.append(" WHERE ( "); } else { query.append(" AND ( "); } } else { query.append(" OR "); } query.append(" LOWER(maingroup) = LOWER(?) "); if (i == (groups.size()-1)) { query.append(" ) "); } } } if (null != isActive) { if (!isWherePresent) { isWherePresent=true; query.append(" WHERE "); } else { query.append(" AND "); } if (isActive) { query.append(" active > 0 "); } else { query.append(" active = 0 "); } } if (null != isQuestionnaire) { if (!isWherePresent) { isWherePresent=true; query.append(" WHERE "); } else { query.append(" AND "); } if (isQuestionnaire) { query.append(" questionnaire > 0 "); } else { query.append(" questionnaire = 0 "); } } if (null != title && title.length () > 0) { if (!isWherePresent) { isWherePresent = true; query.append(" WHERE "); } else { query.append(" AND "); } query.append(" LOWER(title) LIKE LOWER(?) "); } if (null != isRestricted) { if (!isWherePresent) { isWherePresent=true; query.append(" WHERE "); } else { query.append(" AND "); } if (isRestricted) { query.append(" restrictedaccess > 0 "); } else { query.append(" restrictedaccess = 0 "); } } query.append(" ORDER BY startdate DESC"); return query.toString(); } private boolean searchInProperties(String string, ApsProperties props) { for (Enumeration enu = props.keys(); enu.hasMoreElements(); ) { String key = (String) enu.nextElement(); String value = props.getProperty(key); if (value.toLowerCase().contains(string.toLowerCase())) return true; } return false; } public List<Integer> loadResourceUtilizers(String resourceId) { PreparedStatement stat = null; ResultSet res = null; Connection conn = null; List<Integer> utilizers = new ArrayList<Integer>(); try { conn = this.getConnection(); stat = conn.prepareStatement(SELECT_RESOURCE_UTILIZERS); stat.setString(1, resourceId); res = stat.executeQuery(); while (res.next()) { utilizers.add(res.getInt(1)); } } catch (Throwable t) { processDaoException(t, "Error while loading resource utilizers", "loadResourceUtilizers"); } finally { closeConnection(conn); } return utilizers; } private static final String GET_COMPLETE_SURVEY_BY_ID = "SELECT " + // survey: 1 - 17 "jpsurvey.id,description, maingroup, startdate, enddate, active, publicpartialresult, publicresult, questionnaire, gatheruserinfo, title, restrictedaccess, checkcookie, checkipaddress, checkusername, imageid, imagedescr, "+ // questions: 18 - 24 "jpsurvey_questions.id, surveyid, question, jpsurvey_questions.pos, singlechoice, minresponsenumber, maxresponsenumber, " + // choices: 25 - 29 "jpsurvey_choices.id, questionid, choice, jpsurvey_choices.pos, freetext " + "FROM jpsurvey "+ "LEFT JOIN jpsurvey_questions ON jpsurvey.id = jpsurvey_questions.surveyid "+ "LEFT JOIN jpsurvey_choices ON jpsurvey_questions.id = jpsurvey_choices.questionid "+ "WHERE jpsurvey.id= ? ORDER BY jpsurvey_questions.pos, jpsurvey_choices.pos"; private static final String SAVE_SURVEY = "INSERT INTO " + "jpsurvey " + " (id, description, maingroup, startdate, enddate, active, publicpartialresult, publicresult, " + " questionnaire, gatheruserinfo, title, restrictedaccess, " + " checkcookie, checkipaddress, checkusername, imageid, imagedescr) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; private static final String UPDATE_SURVEY_BY_ID = "UPDATE jpsurvey SET description = ? , maingroup = ? , startdate = ? , enddate = ? , active = ? , " + "publicpartialresult = ?, publicresult = ?, questionnaire = ?, gatheruserinfo = ? , title = ? , restrictedaccess = ? , checkcookie = ? , checkipaddress = ?, checkusername = ?, imageid = ?, imagedescr = ? WHERE id = ? "; private static final String DELETE_SURVEY_BY_ID = "DELETE FROM jpsurvey WHERE id = ? "; private static final String SELECT_RESOURCE_UTILIZERS = "SELECT jpsurvey.id FROM jpsurvey WHERE imageid = ? "; }