package inukisoft.patronesj2ee.model.consulta.dao;

import inukisoft.patronesj2ee.model.consulta.vo.ConsultaVO;
import inukisoft.util.DateOperations;
import inukisoft.util.exceptions.InstanceNotFoundException;
import inukisoft.util.exceptions.InternalErrorException;
import inukisoft.util.sql.GeneralOperations;


import java.util.Calendar;
import java.util.Collection;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

public abstract class AbstractSQLConsultaDAO implements SQLConsultaDAO {    

    protected AbstractSQLConsultaDAO() {}

    public boolean exists(Connection connection, Long  consultaIdentifier) 
        throws InternalErrorException {
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            /* Define la sentencia SQL preparada */
            String queryString = "SELECT id FROM Consulta WHERE id = ?";
            preparedStatement = connection.prepareStatement(queryString);
            
            /* Completa los ? de la sentencia SQL */
            int i = 1;
            preparedStatement.setLong(i++,consultaIdentifier.longValue());            
            
            /* Execute query. */
            resultSet = preparedStatement.executeQuery();

            return resultSet.next();
            
        } catch (SQLException e) {
            throw new InternalErrorException(e);    
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }        
    }
    
    public ConsultaVO find(Connection connection, Long consultaIdentifier)
        throws InstanceNotFoundException, InternalErrorException {
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Crea la consulta con parametros 
        	String queryString = "SELECT id, consulta, fecha FROM Consulta WHERE id = ?";        	            
            preparedStatement = connection.prepareStatement(queryString);            
            
            // Completa los ?  
            int i = 1;
            preparedStatement.setLong(i++, consultaIdentifier.longValue());                        
            
            //Ejecuta la consulta 
            resultSet = preparedStatement.executeQuery();
            
            if (!resultSet.next()) {
            	// Envia una exception cuando no se encuentra el registro
                throw new InstanceNotFoundException(consultaIdentifier,
                    ConsultaVO.class.getName());
            }

            // Obtener los resultados
            i = 1;
            
            Long id = new Long(resultSet.getLong(i++));
            String consulta = new String(resultSet.getString(i++));
            
            // Obtiene la fecha de la consulta 
			Calendar fecha = Calendar.getInstance();
			fecha.setTime(resultSet.getTimestamp(i++));            
            
            // Retorna el objeto VO
            return new ConsultaVO(id,consulta,fecha);
            
        } catch (SQLException e) {
            throw new InternalErrorException(e);    
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }    
        
    }
        
	public Collection list(Connection connection,
		int startIndex, int count) 
		throws InternalErrorException {
        
        // Si count es -1, entonces muestra todos los registros
        
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {

			// Crear la consula SQL
			String queryString = "SELECT id, consulta, fecha FROM Consulta ORDER BY id";
		
            preparedStatement = connection.prepareStatement(queryString,
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);    
                                         
			// Ejecutar consulta
			resultSet = preparedStatement.executeQuery();

			// Leer objetos
			Collection consultaVOs = new ArrayList();
			int currentCount = 0;
            int i;
            			            
            while (resultSet.next()) {
				i = 1;					
	            Long id = new Long(resultSet.getLong(i++));                    
	            String consulta = new String(resultSet.getString(i++));
	            
	            // Obtiene la fecha de la consulta 
				Calendar fecha = Calendar.getInstance();
				fecha.setTime(resultSet.getTimestamp(i++)); 
				
	            ConsultaVO vo = new ConsultaVO(id,consulta,fecha);
				
				consultaVOs.add(vo);
				currentCount++;
			}                 			

			// Retorna la colección de VO
			              
			return consultaVOs;
            
		} catch (SQLException e) {
			throw new InternalErrorException(e);    
		} finally {
			GeneralOperations.closeResultSet(resultSet);
			GeneralOperations.closeStatement(preparedStatement);
		}        
	}

        
    public void update(Connection connection, ConsultaVO vo) 
        throws InstanceNotFoundException, InternalErrorException {
        
        PreparedStatement preparedStatement = null;

        try {
        	String queryString = "UPDATE Consulta SET " + 
				"consulta = ?," + 
				"fecha = ? " +
				"WHERE id = ?";
        				
            preparedStatement = connection.prepareStatement(queryString);
            
            // Completar la sentencia SQL 
            int i = 1;
            
            preparedStatement.setString(i++, vo.getConsulta());
			
            Calendar fecha = 
				DateOperations.getDateWithoutMilliSeconds(vo.getFecha());			
			preparedStatement.setTimestamp(i++, new Timestamp(fecha.getTime().getTime()));
            
			preparedStatement.setLong(i++,vo.getId().longValue());
			
                                 
            /* Execute query. */
            int updatedRows = preparedStatement.executeUpdate();

            if (updatedRows == 0) {
                throw new InstanceNotFoundException(
                    vo.getId(), 
                    ConsultaVO.class.getName());
            }

            if (updatedRows > 1) {
                throw new SQLException("Fila duplicada para el identificador = '" + 
                    vo.getId() + "' in table 'Consulta'");
            }        
            
        } catch (SQLException e) {
            throw new InternalErrorException(e);    
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }    
        
    }
        
    public void remove(Connection connection, Long consultaIdentifier) 
        throws InstanceNotFoundException, InternalErrorException {
        
        PreparedStatement preparedStatement = null;
        
        try {

            // Crear sentencia SQL
            String queryString = "DELETE FROM Consulta WHERE" +
                " id = ?";
            preparedStatement = connection.prepareStatement(queryString);
            
            // Completar SQL de la consulta
            int i = 1;
            preparedStatement.setLong(i++, consultaIdentifier.longValue());
            
            /* Execute query. */
            int removedRows = preparedStatement.executeUpdate();

            if (removedRows == 0) {
                throw new InstanceNotFoundException(consultaIdentifier,
                    ConsultaVO.class.getName());
            }

        } catch (SQLException e) {
            throw new InternalErrorException(e);    
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }
                        
    }
    
	public Long count(Connection connection) 
		throws InstanceNotFoundException, InternalErrorException {

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			/* Create "preparedStatement". */
			String queryString = "SELECT count(id) FROM Consulta";
			preparedStatement = connection.prepareStatement(queryString,
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);                                        
			/* Execute query. */
			resultSet = preparedStatement.executeQuery();
			/* Posicionarse en la fila 1 (única) */
			resultSet.absolute(1);
			/* Recuperar el valor */
			int i = 1;
			
			Long countTipoProyectos = new Long(resultSet.getLong(i++));
			return countTipoProyectos; 			
        
		} catch (SQLException e) {
			throw new InternalErrorException(e);    
		} finally {
			GeneralOperations.closeResultSet(resultSet);
			GeneralOperations.closeStatement(preparedStatement);
		}        			
	}

}