VoterDAO.java

/*
*
* 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.Date;
import java.util.List;

import com.agiletec.aps.system.common.FieldSearchFilter;
import com.agiletec.plugins.jpsurvey.aps.system.services.AbstractSurveyDAO;
import com.agiletec.plugins.jpsurvey.aps.system.services.collect.model.Voter;

public class VoterDAO extends AbstractSurveyDAO implements IVoterDAO {

	@Override
	public List<Integer> searchVotersId(FieldSearchFilter[] filters) {
		List recordsId = null;
		try {
			recordsId  = super.searchId(filters);
		} catch (Throwable t) {
			throw new RuntimeException("error in searchRecordsId", t);
		}
		return recordsId;
	}
	
	public Voter getVoter(String username, String ipAddress, int surveyId) {
		Voter voter = null;
		Connection conn = null;
		PreparedStatement stat=null;
		ResultSet res = null;
		try {
			conn = this.getConnection();
			stat = conn.prepareStatement(GET_VOTER_BY_SURVEY);
			stat.setString(1, username);
			stat.setString(2, ipAddress);
			stat.setInt(3, surveyId);
			res = stat.executeQuery();
			voter = this.buildVoterByResultSet(res);
		} catch (Throwable t) {
			processDaoException(t, "Error extracting the voting user by IP "+ ipAddress + " and idSurvey " + surveyId, "getVoter");
		} finally {
			closeDaoResources(res, stat, conn);
		}
		return voter;
	}
	
	/* (non-Javadoc)
	 * @see com.agiletec.plugins.jpsurvey.aps.system.services.collect.IVoterDAO#getVoterbyId(int)
	 */
	public Voter getVoterById(int id) {
		Voter voter = null;
		Connection conn = null;
		try {
			conn=this.getConnection();
			voter=this.getVoterById(conn, id);
		} catch (Throwable t) {
			processDaoException(t, "Error getting the user ID "+id, "getVoterbyId");
		} finally {
			closeConnection(conn);
		}
		return voter;
	}
	
	/* (non-Javadoc)
	 * @see com.agiletec.plugins.jpsurvey.aps.system.services.collect.IVoterDAO#getVoterbyId(java.sql.Connection, int)
	 */
	public Voter getVoterById(Connection conn, int id) {
		Voter voter = null;
		PreparedStatement stat = null;
		ResultSet res = null;
		try {
			stat = conn.prepareStatement(GET_VOTER_BY_ID);
			stat.setInt(1, id);
			res = stat.executeQuery();
			voter = this.buildVoterByResultSet(res);
		} catch (Throwable t) {
			processDaoException(t, "Error loading user voter from id " + id, "getVoterbyId");
		} finally {
			closeDaoResources(res, stat);
		}
		return voter;
	}
	
	private Voter buildVoterByResultSet(ResultSet res) {
		Voter voter = null;
		try {
			if (res.next()) {
				voter = new Voter();
				voter.setId(res.getInt(1)); // 1
				voter.setAge(res.getShort(2)); // 2
				voter.setCountry(res.getString(3)); // 3
				String sex = res.getString(4);
				if (null != sex && sex.length()>0) {
					voter.setSex(sex.charAt(0)); // 4
				}
				voter.setDate(res.getDate(5)); // 5
				voter.setSurveyid(res.getInt(6)); // 6
				voter.setUsername(res.getString(7)); // 7
				voter.setIpaddress(res.getString(8)); // 8	
			}
		} catch (Throwable t) {
			processDaoException(t, "Error loading voting user", "buildVoterByResultSet");
		}
		return voter;
	}


	/* (non-Javadoc)
	 * @see com.agiletec.plugins.jpsurvey.aps.system.services.collect.IVoterDAO#saveVoter(com.agiletec.plugins.jpsurvey.aps.system.services.collect.Voter)
	 */
	public void saveVoter(Voter voter) {
		Connection conn = null;
		try {
			conn = this.getConnection();
			conn.setAutoCommit(false);
			this.saveVoter(conn, voter);
			conn.commit();
		} catch (Throwable t) {
			this.processDaoException(t, "Errore saving voting user", "saveVoter");
		} finally {
			closeConnection(conn);
		}
	}
	
	/* (non-Javadoc)
	 * @see com.agiletec.plugins.jpsurvey.aps.system.services.collect.IVoterDAO#saveVoter(java.sql.Connection, com.agiletec.plugins.jpsurvey.aps.system.services.collect.Voter)
	 */
	public void saveVoter(Connection conn, Voter voter) {
		PreparedStatement stat= null;
		try {
			String NEXT_ID = "SELECT MAX(id) FROM jpsurvey_voters";
			int selfGeneratedId = this.getUniqueId(NEXT_ID, conn);//this._keyGeneratorManager.getUniqueKeyCurrentValue();
			stat = conn.prepareStatement(SAVE_VOTER);
			voter.setId(selfGeneratedId); 
			stat.setInt(1, voter.getId()); // 1
			if (voter.getAge() > -1) {
				stat.setShort(2, voter.getAge()); // 2
			} else {
				stat.setNull(2, java.sql.Types.SMALLINT); // 2
			}
			if (null != voter.getCountry() && voter.getCountry().length() > 0) {
				stat.setString(3, voter.getCountry().substring(0,2)); // 3
			} else {
				stat.setNull(3, java.sql.Types.VARCHAR); // 3
			}
			
			if (null != voter.getSex()) {
				stat.setString(4, voter.getSex().toString()); // 4
			} else {
				stat.setNull(4, java.sql.Types.VARCHAR); // 4
			}
			stat.setDate(5, new java.sql.Date(voter.getDate().getTime())); // 5
			stat.setInt(6, voter.getSurveyid()); // 6
			stat.setString(7, voter.getUsername()); // 7
			stat.setString(8, voter.getIpaddress()); // 8
			stat.executeUpdate();
		} catch (Throwable t) {
			this.processDaoException(t, "Errore nel salvataggio dell'utente votante", "saveVoter");
		} finally  {
			closeDaoResources(null, stat);
		}
	}

	/* (non-Javadoc)
	 * @see com.agiletec.plugins.jpsurvey.aps.system.services.collect.IVoterDAO#deleteVoterById(int)
	 */
	public void deleteVoterById(int id) {
		Connection conn = null;
		PreparedStatement stat=null;
		try {
			conn=this.getConnection();
			stat=conn.prepareStatement(DELETE_VOTER_BY_ID);
			stat.setInt(1, id);
			stat.execute();
		} catch (Throwable t) {
			this.processDaoException(t, "Errore nella cancellazione dell'utente votante", "deleteVoterById");
		} finally {
			closeDaoResources(null, stat, conn);
		}
	}
	
	public void deleteVoterBySurveyId(int surveyId) {
		Connection conn = null;
		try {
			conn=this.getConnection();
			deleteVoterBySurveyId(conn, surveyId);
		} catch (Throwable t) {
			this.processDaoException(t, "Errore nella cancellazione degli utenti che hanno votato il survey dato ", "deleteVoterBySurveyId");
		} finally {
			closeConnection(conn);
		}
	}
	
	public void deleteVoterBySurveyId(Connection conn, int surveyId) {
		PreparedStatement stat=null;
		try {
			stat=conn.prepareStatement(DELETE_VOTER_BY_SURVEY);
			stat.setInt(1, surveyId);
			stat.execute();
		} catch (Throwable t) {
			this.processDaoException(t, "Error deleting users associated to the survey " + surveyId, "deleteVoterBySurveyId");
		} finally {
			closeDaoResources(null, stat);			
		}
	}
	
	public List<Integer> searchVotersByIds(Integer id, Integer age, String country, Character sex, Date date, Integer surveyId, String ipAddress) {
		List<Integer> list = new ArrayList<Integer>();
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet res = null;
		int idx = 1;
		try {
			conn = this.getConnection();
			stat = conn.prepareStatement(this.createStatement(id, age, country, sex, date, surveyId, ipAddress));
			if (null != id) {
				stat.setInt(idx++, id); // 1
			}
			if (null != age) {
				stat.setShort(idx++, Integer.valueOf(age).shortValue()); // 2				
			}
			if (null != country && country.length()>0) {
				stat.setString(idx++, country);
			}
			if (null != sex) {
				stat.setString(idx++, String.valueOf(sex)); // 4
			}
			if (null != date) {
				stat.setDate(idx++, new java.sql.Date(date.getTime())); // 5
			}
			if (null != surveyId) {
				stat.setInt(idx++, surveyId); // 6
			}
			if (null != ipAddress && ipAddress.length()<16 && ipAddress.length()>6) {
				stat.setString(idx++, ipAddress); // 7
			}
			res = stat.executeQuery();
			while (res.next()) {
				list.add(res.getInt(1));
			}
		} catch (Throwable t) {
			this.processDaoException(t, "Error building the list of voter users", "getVotersByIds");
		} finally {
			closeDaoResources(res, stat, conn);
		}
		if (list.isEmpty()) return null;
		return list;
	}
	
	private String createStatement(Integer id, Integer age, String country, Character sex, Date date,
			Integer surveyId, String ipAddress) {
		StringBuffer query= new StringBuffer(SEARCH_VOTERS_IDS);
		boolean whereInserted = false;
		if (null != id) {
			if (!whereInserted) {
				query.append(" WHERE ");
				whereInserted = true;
			}
			query.append(" id = ? ");
		}
		if (null != age) {
			if (!whereInserted) {
				query.append(" WHERE ");
				whereInserted=true;
			} else {
				query.append(" AND ");
			}
			query.append("age = ?");
		}
		if (null != country) {
			if (!whereInserted) {
				query.append(" WHERE ");
				whereInserted = true;
			} else {
				query.append(" AND ");
			}
			query.append("LOWER(country) = LOWER(?) ");
		}
		if (null != sex) {
			if (!whereInserted) {
				query.append(" WHERE ");
				whereInserted = true;
			} else {
				query.append(" AND ");
			}
			query.append(" LOWER(sex) = LOWER(?) ");
		}
		if (null != date) {
			if (!whereInserted) {
				query.append(" WHERE ");
				whereInserted = true;
			} else {
				query.append(" AND ");
			}
			query.append(" votedate = ? ");
		}
		if (null != surveyId) {
			if (!whereInserted) {
				query.append(" WHERE ");
				whereInserted = true;
			} else {
				query.append(" AND ");
			}
			query.append(" surveyid = ? ");
		}
		if (null != ipAddress && ipAddress.length() < 16 && ipAddress.length() > 6) {
			if (!whereInserted) {
				query.append(" WHERE ");
				whereInserted = true;
			} else {
				query.append(" AND ");
			}
			query.append(" ipaddress = ? ");
		}
		query.append(" ORDER BY ID ");
		return query.toString();
	}
	
	private final String GET_VOTER_BASE_QUERY_BLOCK =
		"SELECT id, age, country, sex, votedate, surveyid, username, ipaddress FROM jpsurvey_voters ";
	
	private final String GET_VOTER_BY_SURVEY =
		GET_VOTER_BASE_QUERY_BLOCK + " WHERE username = ? AND ipaddress = ? AND surveyid = ? ";
	
	private final String GET_VOTER_BY_ID =
		GET_VOTER_BASE_QUERY_BLOCK + " WHERE id = ? ";
	
	private final String SAVE_VOTER =
		"INSERT INTO jpsurvey_voters (id, age, country, sex, votedate, surveyid, username, ipaddress) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? ) ";
	
	private final String DELETE_VOTER_BY_ID	 =
		"DELETE FROM jpsurvey_voters WHERE id = ? ";
	
	private final String DELETE_VOTER_BY_SURVEY	 =
		"DELETE FROM jpsurvey_voters WHERE surveyid = ? ";
	
	private final String SEARCH_VOTERS_IDS = "SELECT id FROM jpsurvey_voters ";
	
}