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 com.ejie.p06b.datatype.model.P06B27T00Datatype;
import com.ejie.p06b.general.P06BTablas;
import com.ejie.p06b.log.P06BLog;
import com.ejie.p06b.util.bd.P06BServidorConexionesBD;

/**
 * XXXX
 * Inserte aqu� la descripci�n del tipo.
 * Fecha de creaci�n: (28/02/2008)
 */
public class P06B27T00DAO {

	static private P06B27T00DAO instance = null;

	public static P06B27T00DAO getInstance() {
	     if (instance == null) {
	          synchronized (P06B27T00DAO.class) {
	          		if (instance == null) {
	                     instance = new P06B27T00DAO();
	                }
	          }
	     }
	     return instance;
	}

	/**
    * Inserta un registro de la BD
    * @param obj.
    *            objeto a insertar
    * @return void
    * @throws SQLException
    */
	public void insert(P06B27T00Datatype obj) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B27T00DAO-insert");

		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = INSERT_COMPLETA;
        lstm = lconConexion.prepareStatement(sql);

        lstm.setString(1,obj.getNumExp().toUpperCase());
	    lstm.setString(2,obj.getIdentificador().toUpperCase());
	    lstm.setString(3,obj.getIdentificador1().toUpperCase());
	    lstm.setString(4,obj.getNombre().toUpperCase());
	    lstm.setString(5,obj.getApe1().toUpperCase());
	    lstm.setString(6,obj.getApe2().toUpperCase());
	    lstm.setString(7,obj.getDomici().toUpperCase());
	    lstm.setString(8,obj.getCodmun().toUpperCase());
	    lstm.setString(9,obj.getLocali().toUpperCase());
	    lstm.setString(10,obj.getCodpro().toUpperCase());
	    lstm.setString(11,obj.getCodpst().toUpperCase());

    	lstm.executeUpdate();
    	//Cierro recursos
    	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) {}
        P06BLog.send(2,this, "Fin P06B27T00DAO-insert");
	}

	/**
    * Obtiene la lista de personas fisicas asociadas al departamento de atencion al cliente correspondiente, del expediente correspondiente
    * @param expediente.
    * @param identificador.
    * @return Lista de objetos personas f�sicas del departamento de atenci�n al cliente
	 * @throws Exception
    */
	public ArrayList selectAllByExpIdentif(String expediente, String identificador) throws Exception{
		P06BLog.send(2,this, "Inicio P06B27T00Datatype-selectAllByExpIdentif");
		ArrayList lista = new ArrayList();
		P06B27T00Datatype obj = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECTALL_BY_EXP;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setString(1,expediente.toUpperCase());
        	lstm.setString(2,identificador.toUpperCase());
        	lrst = lstm.executeQuery();
	        while (lrst.next()) {
	        	obj = new P06B27T00Datatype();
	        	obj.setNumExp(lrst.getString(P06BTablas.P06B_P06B27_NUMEXP));
			    obj.setIdentificador(lrst.getString(P06BTablas.P06B_P06B27_IDENTIFICADOR));
			    obj.setIdentificador1(lrst.getString(P06BTablas.P06B_P06B27_IDENTIFICADOR1));
			    obj.setNombre(lrst.getString(P06BTablas.P06B_P06B27_NOMBRE)==null?"":lrst.getString(P06BTablas.P06B_P06B27_NOMBRE));
			    obj.setApe1(lrst.getString(P06BTablas.P06B_P06B27_APE1)==null?"":lrst.getString(P06BTablas.P06B_P06B27_APE1));
			    obj.setApe2(lrst.getString(P06BTablas.P06B_P06B27_APE2)==null?"":lrst.getString(P06BTablas.P06B_P06B27_APE2));
			    obj.setDomici(lrst.getString(P06BTablas.P06B_P06B27_DOMICI)==null?"":lrst.getString(P06BTablas.P06B_P06B27_DOMICI));
			    obj.setCodmun(lrst.getString(P06BTablas.P06B_P06B27_CODMUN)==null?"":lrst.getString(P06BTablas.P06B_P06B27_CODMUN));
			    obj.setLocali(lrst.getString(P06BTablas.P06B_P06B27_LOCALI)==null?"":lrst.getString(P06BTablas.P06B_P06B27_LOCALI));
			    obj.setCodpro(lrst.getString(P06BTablas.P06B_P06B27_CODPRO)==null?"":lrst.getString(P06BTablas.P06B_P06B27_CODPRO));
			    obj.setCodpst(lrst.getString(P06BTablas.P06B_P06B27_CODPST)==null?"":lrst.getString(P06BTablas.P06B_P06B27_CODPST));

	        	lista.add(obj);
	        }
	        P06BLog.send(2,this, "Fin P06B27T00Datatype-selectAllByExpIdentif");
	    } 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;
	}


	/**
    * Elimina todas las personas f�sicas asociadas al expediente concreto
    * @param exp.
    *            el expediente cuyas personas f�sicas de los atencion al cliente queremos eliminar
    * @throws SQLException
    */
	public void deleteAllByExp(String exp) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B27T00DAO-deleteAllByExp");
		PreparedStatement lstm  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = DELETEALL_BYEXP;
        lstm = lconConexion.prepareStatement(sql);
    	lstm.setString(1,exp.toUpperCase());
    	lstm.executeUpdate();
    	//Cierro recursos
    	if(lstm!=null) lstm.close();
    	if(lconConexion!=null)lconConexion.close();
        P06BLog.send(2,this, "Fin P06B27T00DAO-deleteAllByExp");
	}


	/**
    * Elimina todas las personas f�sicas asociadas al expediente concreto
    * @param exp.
    *            el expediente cuyas personas f�sicas de los atencion al cliente queremos eliminar
    * @throws SQLException
    */
	public void deleteByExpId(String exp, String id) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B27T00DAO-deleteByExpId");
		PreparedStatement lstm  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = DELETEBYEXPID;
        lstm = lconConexion.prepareStatement(sql);
    	lstm.setString(1,exp.toUpperCase());
    	lstm.setString(2,id);
    	lstm.executeUpdate();
    	//Cierro recursos
    	if(lstm!=null) lstm.close();
    	if(lconConexion!=null)lconConexion.close();
        P06BLog.send(2,this, "Fin P06B27T00DAO-deleteByExpId");
	}


	private static String INSERT_COMPLETA =
		"INSERT INTO "+ P06BTablas.P06B_P06B27T00_TABLA +
		" ("+
		P06BTablas.P06B_P06B27_NUMEXP + "," +
		P06BTablas.P06B_P06B27_IDENTIFICADOR + "," +
		P06BTablas.P06B_P06B27_IDENTIFICADOR1 + "," +
		P06BTablas.P06B_P06B27_NOMBRE + "," +
		P06BTablas.P06B_P06B27_APE1 + "," +
		P06BTablas.P06B_P06B27_APE2 + "," +
		P06BTablas.P06B_P06B27_DOMICI + "," +
		P06BTablas.P06B_P06B27_CODMUN + "," +
		P06BTablas.P06B_P06B27_LOCALI + "," +
		P06BTablas.P06B_P06B27_CODPRO + "," +
		P06BTablas.P06B_P06B27_CODPST +
		")"+
		" VALUES(?,?,?,?,?,?,?,TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),?)";

	private static String SELECTALL_BY_EXP =
		"SELECT " +
		P06BTablas.P06B_P06B27_NUMEXP + "," +
		P06BTablas.P06B_P06B27_IDENTIFICADOR + "," +
		P06BTablas.P06B_P06B27_IDENTIFICADOR1 + "," +
		P06BTablas.P06B_P06B27_NOMBRE + "," +
		P06BTablas.P06B_P06B27_APE1 + "," +
		P06BTablas.P06B_P06B27_APE2 + "," +
		P06BTablas.P06B_P06B27_DOMICI + "," +
		P06BTablas.P06B_P06B27_CODMUN + "," +
		P06BTablas.P06B_P06B27_LOCALI + "," +
		P06BTablas.P06B_P06B27_CODPRO + "," +
		P06BTablas.P06B_P06B27_CODPST +
		" FROM " + P06BTablas.P06B_P06B27T00_TABLA
		+ " WHERE " + P06BTablas.P06B_P06B27_NUMEXP + "= ?"
			+ " AND " + P06BTablas.P06B_P06B27_IDENTIFICADOR + "= ?"
		+" ORDER BY " + P06BTablas.P06B_P06B27_IDENTIFICADOR1;

	private static String DELETEALL_BYEXP =
		"DELETE FROM "+ P06BTablas.P06B_P06B27T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B27_NUMEXP + " = ?";

	private static String DELETEBYEXPID =
		"DELETE FROM "+ P06BTablas.P06B_P06B27T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B27_NUMEXP + " = ?"
		+ " AND " + P06BTablas.P06B_P06B27_IDENTIFICADOR + "= ?";
}
