/*
*
* 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.collect;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.agiletec.plugins.jpsurvey.aps.system.services.AbstractSurveyDAO;
import com.agiletec.plugins.jpsurvey.aps.system.services.collect.model.SingleQuestionResponse;
public class ResponseDAO extends AbstractSurveyDAO implements IResponseDAO {
@Override
public void submitResponses(List<SingleQuestionResponse> responses) {
Connection conn = null;
PreparedStatement stat= null;
try {
conn = this.getConnection();
conn.setAutoCommit(false);
stat = conn.prepareStatement(SAVE_RESPONSE);
for (int i=0; i<responses.size(); i++) {
SingleQuestionResponse result = responses.get(i);
stat.setInt(1, result.getVoterId()); // 1
stat.setInt(2, result.getQuestionId()); // 2
stat.setInt(3, result.getChoiceId()); // 3
if (null!=result.getFreeText()) {
stat.setString(4, result.getFreeText()); // 4
} else {
stat.setNull(4, java.sql.Types.VARCHAR); // 4
}
stat.addBatch();
stat.clearParameters();
}
stat.executeBatch();
conn.commit();
} catch (Throwable t) {
this.processDaoException(t, "Error saving answers", "submitResponses");
} finally {
this.closeDaoResources(null, stat, conn);
}
}
@Override
public Map<Integer, Integer> loadQuestionStatistics(Integer questionId) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
Map<Integer, Integer> map = new HashMap<Integer, Integer>();
try {
conn = this.getConnection();
stat = conn.prepareStatement(LOAD_QUESTION_STATS);
stat.setInt(1, questionId);
res = stat.executeQuery();
while (res.next()) {
int choiceId = res.getInt(1);
int occurrences = res.getInt(2);
map.put(choiceId, occurrences);
}
} catch (Throwable t) {
processDaoException(t, "loadQuestionStatistics id " + questionId, "loadQuestionStatistics");
} finally {
closeDaoResources(res, stat, conn);
}
return map;
}
private static final String LOAD_QUESTION_STATS =
"SELECT jpsurvey_responses.choiceid, COUNT(jpsurvey_responses.choiceid) " +
"FROM jpsurvey_responses WHERE jpsurvey_responses.questionid = ? GROUP BY jpsurvey_responses.choiceid";
/* (non-Javadoc)
* @see com.agiletec.plugins.jpsurvey.aps.system.services.collect.IResponseDAO#saveResponse(com.agiletec.plugins.jpsurvey.aps.system.services.collect.Response)
*/
public void submitResponse(SingleQuestionResponse result) {
Connection conn = null;
PreparedStatement stat=null;
try {
conn = this.getConnection();
stat=conn.prepareStatement(SAVE_RESPONSE);
stat.setInt(1, result.getVoterId()); // 1
stat.setInt(2, result.getQuestionId()); // 2
stat.setInt(3, result.getChoiceId()); // 3
if (null!=result.getFreeText()) {
stat.setString(4, result.getFreeText()); // 4
}
stat.executeUpdate();
} catch (Throwable t) {
this.processDaoException(t, "Error saving vote", "saveResponse");
} finally {
closeDaoResources(null, stat, conn);
}
}
/* (non-Javadoc)
* @see com.agiletec.plugins.jpsurvey.aps.system.services.collect.IResponseDAO#aggregateResponseByIds(java.lang.Integer, java.lang.Integer, java.lang.Integer, java.lang.String)
*/
public List<SingleQuestionResponse> aggregateResponseByIds(Integer voterId, Integer questionId, Integer choiceId, String freetext) {
List<SingleQuestionResponse> list = null;
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
int idx = 1;
try {
conn = this.getConnection();
stat = conn.prepareStatement(prepareAggregationStatment(voterId,questionId,choiceId,freetext));
if (null!=voterId) {
stat.setInt(idx++, voterId);
}
if (null!=questionId) {
stat.setInt(idx++, questionId);
}
if (null!=choiceId) {
stat.setInt(idx++, choiceId);
}
if (null!=freetext) {
stat.setString(idx++, "%"+freetext+"%");
}
res = stat.executeQuery();
list = createListFromResultSet(res);
} catch (Throwable t) {
this.processDaoException(t, "Errore searching responseId", "loadResponseByIds");
} finally {
closeDaoResources(res, stat, conn);
}
return list;
}
private String prepareAggregationStatment(Integer voterId,
Integer questionId, Integer choiceId, String freetext) {
StringBuffer query=new StringBuffer(AGGREGATE_RESPONSE);
boolean isWhereInserted=false;
if (null!=voterId) {
if (!isWhereInserted) {
query.append(" WHERE ");
isWhereInserted=true;
}
query.append(" voterid = ? ");
}
if (null!=questionId) {
if (!isWhereInserted) {
query.append(" WHERE ");
isWhereInserted=true;
} else {
query.append(" AND ");
}
query.append(" questionid = ? ");
}
if (null!=choiceId) {
if (!isWhereInserted) {
query.append(" WHERE ");
isWhereInserted=true;
} else {
query.append(" AND ");
}
query.append(" choiceid = ? ");
}
if (null!=freetext) {
if (!isWhereInserted) {
query.append(" WHERE ");
isWhereInserted=true;
} else {
query.append(" AND ");
}
query.append(" LOWER(freetext ) LIKE LOWER(?) ");
}
query.append("ORDER BY voterid ");
return query.toString();
}
private List<SingleQuestionResponse> createListFromResultSet(ResultSet res) {
List<SingleQuestionResponse> list=new ArrayList<SingleQuestionResponse>();
try {
while (res.next()) {
SingleQuestionResponse response=this.createRecordFromResultSet(res);
list.add(response);
}
}
catch (Throwable t) {
this.processDaoException(t, "Error building the vote result list", "createListFromResultSet");
}
if (list.isEmpty()) return null;
return list;
}
private SingleQuestionResponse createRecordFromResultSet(ResultSet res) {
SingleQuestionResponse response = new SingleQuestionResponse();
try {
response.setVoterId(res.getInt(1)); // 1
response.setQuestionId(res.getInt(2)); // 2
response.setChoiceId(res.getInt(3)); // 3
response.setFreeText(res.getString(4)); // 4
} catch (Throwable t) {
this.processDaoException(t, "Error creating the 'response' onject form resulset", "createRecordFromResultSet");
}
return response;
}
public void deleteResponse(SingleQuestionResponse response) {
Connection conn=null;
PreparedStatement stat = null;
try {
conn=this.getConnection();
if (null!=response.getFreeText() && response.getFreeText().length()>0) {
stat=conn.prepareStatement(DELETE_RESPONSE_FREETEXT);
} else {
stat=conn.prepareStatement(DELETE_RESPONSE_NO_FREETEXT);
}
stat.setInt(1, response.getVoterId()); // 1
stat.setInt(2, response.getQuestionId()); // 2
stat.setInt(3, response.getChoiceId()); // 3
if (null!=response.getFreeText() && response.getFreeText().length()>0) {
stat.setString(4, response.getFreeText()); // 4
}
stat.execute();
} catch (Throwable t) {
this.processDaoException(t, "Error deleting the object response", "deleteResponse");
} finally {
closeDaoResources(null, stat, conn);
}
}
public void deleteResponseByQuestionId(int questionId) {
Connection conn=null;
PreparedStatement stat = null;
try {
conn=this.getConnection();
stat=conn.prepareStatement(DELETE_RESPONSE_BY_QUESTION_ID);
stat.setInt(1, questionId); // 1
stat.execute();
} catch (Throwable t) {
this.processDaoException(t, "Error deleting responses by question ID "+questionId, "deleteResponseByQuestionId");
} finally {
closeDaoResources(null, stat, conn);
}
}
public void deleteResponseByChoiceId(int choiceId) {
Connection conn=null;
PreparedStatement stat = null;
try {
conn=this.getConnection();
stat=conn.prepareStatement(DELETE_RESPONSE_BY_CHOICE_ID);
stat.setInt(1, choiceId); // 1
stat.execute();
} catch (Throwable t) {
this.processDaoException(t, "Error deleting responses by choice ID "+choiceId, "deleteResponseByQuestionId");
} finally {
closeDaoResources(null, stat, conn);
}
}
final String SAVE_RESPONSE =
"INSERT INTO jpsurvey_responses (voterid,questionid,choiceid,freetext) VALUES (?,?,?,?)";
final String AGGREGATE_RESPONSE =
"SELECT voterid,questionid,choiceid,freetext FROM jpsurvey_responses ";
final String DELETE_RESPONSE_FREETEXT =
"DELETE FROM jpsurvey_responses WHERE voterid = ? AND questionid = ? AND choiceid = ? AND freetext = ? ";
final String DELETE_RESPONSE_NO_FREETEXT =
"DELETE FROM jpsurvey_responses WHERE voterid = ? AND questionid = ? AND choiceid = ? AND freetext IS NULL ";
final String DELETE_RESPONSE_BY_QUESTION_ID = "DELETE FROM jpsurvey_responses WHERE questionid = ? ";
final String DELETE_RESPONSE_BY_CHOICE_ID = "DELETE FROM jpsurvey_responses WHERE choiceid = ? ";
}