package com.ejie.p06b.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Locale;

import com.ejie.p06b.datatype.model.P06B33T00Datatype;
import com.ejie.p06b.general.P06BConstantes;
import com.ejie.p06b.general.P06BTablas;
import com.ejie.p06b.log.P06BLog;
import com.ejie.p06b.util.P06BFecha;
import com.ejie.p06b.util.bd.P06BServidorConexionesBD;

/**
 * Fecha de creación: (05/07/2010)
 */
public class P06B33T00DAO {
	
	static private P06B33T00DAO instance = null;

	public static P06B33T00DAO getInstance() {
	     if (instance == null) {
	          synchronized (P06B33T00DAO.class) {
	          		if (instance == null) {
	                     instance = new P06B33T00DAO();
	                }
	          }
	     }
	     return instance;
	}
	
	/**
    * Obtiene la lista de socios DEC asociados al expediente y ejercicio
    * @param ejer.
    * @param numExp          
    * @return Lista de objetos socios DEC
	 * @throws Exception
    */
	public ArrayList selectAllByEjerExp(String ejer, String numExp) throws Exception{
		P06BLog.send(2,this, "Inicio P06B33T00DAO-selectAllByEjerExp");
		ArrayList lista = new ArrayList();
		P06B33T00Datatype obj = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECTALL_BY_EJEREXP;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setString(1,ejer.toUpperCase());
        	lstm.setString(2,numExp.toUpperCase());
        	lrst = lstm.executeQuery();
	        while (lrst.next()) {
	        	obj = new P06B33T00Datatype();
	        		        	
	        	obj.setEjercicio(lrst.getString(P06BTablas.P06B_P06B33_EJERCICIO));
	        	obj.setTipo(lrst.getString(P06BTablas.P06B_P06B33_TIPO));
			    obj.setNumExp(lrst.getString(P06BTablas.P06B_P06B33_NUMEXP));
			    obj.setIdentificador(lrst.getString(P06BTablas.P06B_P06B33_IDENTIFICADOR));
			    obj.setTipo1(lrst.getString(P06BTablas.P06B_P06B33_TIPO1)==null?"":lrst.getString(P06BTablas.P06B_P06B33_TIPO1));
			    obj.setRazSoc(lrst.getString(P06BTablas.P06B_P06B33_RAZSOC)==null?"":lrst.getString(P06BTablas.P06B_P06B33_RAZSOC));
			    obj.setNombre(lrst.getString(P06BTablas.P06B_P06B33_NOMBRE)==null?"":lrst.getString(P06BTablas.P06B_P06B33_NOMBRE));
			    obj.setApe1(lrst.getString(P06BTablas.P06B_P06B33_APE1)==null?"":lrst.getString(P06BTablas.P06B_P06B33_APE1));
			    obj.setApe2(lrst.getString(P06BTablas.P06B_P06B33_APE2)==null?"":lrst.getString(P06BTablas.P06B_P06B33_APE2));
			    obj.setFecAlta(lrst.getString(P06BTablas.P06B_P06B33_FECALTA)==null?"":P06BFecha.getFechaFormateada(lrst.getDate(P06BTablas.P06B_P06B33_FECALTA),new Locale(P06BConstantes.P06B_IDIOMA_ES)));
			    obj.setParticipa(lrst.getString(P06BTablas.P06B_P06B33_PARTICIPA)==null?"":lrst.getString(P06BTablas.P06B_P06B33_PARTICIPA));
	        	
	        	lista.add(obj);
	        }
	        P06BLog.send(2,this, "Fin P06B33T00DAO-selectAllByEjerExp");
	    } catch (SQLException e) {
	    	P06BLog.send(2,this, "error: " +e.getMessage());
        	try {
        		if(lrst!=null)
        			lrst.close();
        	}catch (Exception rse) {}
	        try {
	        	if(lstm!=null)
	        		lstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        //Propago la excepción
	        throw e;
	    }catch (Exception e2) {	 
        	P06BLog.send(2,this, "error: " +e2.getMessage());
        	try {
        		if(lrst!=null)
        			lrst.close();
        	}catch (Exception rse) {}
	        try {
	        	if(lstm!=null)
	        		lstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        //Propago la excepción
	        throw e2;
	    }finally {
	    	try {
        		if(lrst!=null)
        			lrst.close();
        	}catch (Exception rse) {}
	        try {
	        	if(lstm!=null)
	        		lstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	    }
		return lista;
	}
	
	
	
	private static String SELECTALL_BY_EJEREXP =
		"SELECT "
		+ P06BTablas.P06B_P06B33_EJERCICIO + ", "
		+ P06BTablas.P06B_P06B33_TIPO + ", "
		+ P06BTablas.P06B_P06B33_NUMEXP + ", "
		+ P06BTablas.P06B_P06B33_IDENTIFICADOR + ", "
		+ P06BTablas.P06B_P06B33_TIPO1 + ", "
		+ P06BTablas.P06B_P06B33_RAZSOC + ", "
		+ P06BTablas.P06B_P06B33_NOMBRE + ", "
		+ P06BTablas.P06B_P06B33_APE1 + ", "
		+ P06BTablas.P06B_P06B33_APE2 + ", "
		+ P06BTablas.P06B_P06B33_FECALTA + ", "
		+ P06BTablas.P06B_P06B33_PARTICIPA +
		" FROM " + P06BTablas.P06B_P06B33T00_TABLA 
		+ " WHERE " + P06BTablas.P06B_P06B33_EJERCICIO + "= ?" 
			+ " AND " + P06BTablas.P06B_P06B33_NUMEXP + "= ?"
		+" ORDER BY " + P06BTablas.P06B_P06B33_IDENTIFICADOR ;
	
	
}
