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.P06B28T00Datatype;
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 P06B28T00DAO {

	static private P06B28T00DAO instance = null;

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

	/**
    * Inserta un registro de la BD
    * @param obj.
    *            objeto a insertar
    * @return void
    * @throws SQLException
    */
	public void insert(P06B28T00Datatype obj) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B28T00DAO-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.getTipo().toUpperCase());
		lstm.setString(5,obj.getRazsoc().toUpperCase());
	    lstm.setString(6,obj.getNombre().toUpperCase());
	    lstm.setString(7,obj.getApe1().toUpperCase());
	    lstm.setString(8,obj.getApe2().toUpperCase());
	    lstm.setString(9,obj.getDomici().toUpperCase());
	    lstm.setString(10,obj.getCodmun().toUpperCase());
	    lstm.setString(11,obj.getLocali().toUpperCase());
	    lstm.setString(12,obj.getCodpro().toUpperCase());
	    lstm.setString(13,obj.getCodpst().toUpperCase());
	    lstm.setString(14,obj.getTelefot().toUpperCase());
		lstm.setString(15,obj.getTelefom().toUpperCase());
		lstm.setString(16,obj.getTelefoc().toUpperCase());
		lstm.setString(17,obj.getFax1().toUpperCase());
		lstm.setString(18,obj.getFax2().toUpperCase());
		lstm.setString(19,obj.getMail().toUpperCase());
		lstm.setString(20,obj.getWeb().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 P06B28T00DAO-insert");
	}

	/**
    * Obtiene la lista de responsables asociados al departamento de atenci�n al cliente correspondiente, del expediente correspondiente
    * @param expediente.
    * @param identificador.
    * @return Lista de responsables del departamento de atenci�n al cliente
	 * @throws Exception
    */
	public ArrayList selectAllByExpIdentif(String expediente, String identificador) throws Exception{
		P06BLog.send(2,this, "Inicio P06B28T00Datatype-selectAllByExpIdentif");
		ArrayList lista = new ArrayList();
		P06B28T00Datatype 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 P06B28T00Datatype();
	        	obj.setNumExp(lrst.getString(P06BTablas.P06B_P06B28_NUMEXP));
			    obj.setIdentificador(lrst.getString(P06BTablas.P06B_P06B28_IDENTIFICADOR));
			    obj.setIdentificador1(lrst.getString(P06BTablas.P06B_P06B28_IDENTIFICADOR1));
			    obj.setTipo(lrst.getString(P06BTablas.P06B_P06B28_TIPO)==null?"":lrst.getString(P06BTablas.P06B_P06B28_TIPO));
				obj.setRazsoc(lrst.getString(P06BTablas.P06B_P06B28_RAZSOC)==null?"":lrst.getString(P06BTablas.P06B_P06B28_RAZSOC));
			    obj.setNombre(lrst.getString(P06BTablas.P06B_P06B28_NOMBRE)==null?"":lrst.getString(P06BTablas.P06B_P06B28_NOMBRE));
			    obj.setApe1(lrst.getString(P06BTablas.P06B_P06B28_APE1)==null?"":lrst.getString(P06BTablas.P06B_P06B28_APE1));
			    obj.setApe2(lrst.getString(P06BTablas.P06B_P06B28_APE2)==null?"":lrst.getString(P06BTablas.P06B_P06B28_APE2));
			    obj.setDomici(lrst.getString(P06BTablas.P06B_P06B28_DOMICI)==null?"":lrst.getString(P06BTablas.P06B_P06B28_DOMICI));
			    obj.setCodmun(lrst.getString(P06BTablas.P06B_P06B28_CODMUN)==null?"":lrst.getString(P06BTablas.P06B_P06B28_CODMUN));
			    obj.setLocali(lrst.getString(P06BTablas.P06B_P06B28_LOCALI)==null?"":lrst.getString(P06BTablas.P06B_P06B28_LOCALI));
			    obj.setCodpro(lrst.getString(P06BTablas.P06B_P06B28_CODPRO)==null?"":lrst.getString(P06BTablas.P06B_P06B28_CODPRO));
			    obj.setCodpst(lrst.getString(P06BTablas.P06B_P06B28_CODPST)==null?"":lrst.getString(P06BTablas.P06B_P06B28_CODPST));
			    obj.setTelefot(lrst.getString(P06BTablas.P06B_P06B28_TELEFOT)==null?"":lrst.getString(P06BTablas.P06B_P06B28_TELEFOT));
				obj.setTelefom(lrst.getString(P06BTablas.P06B_P06B28_TELEFOM)==null?"":lrst.getString(P06BTablas.P06B_P06B28_TELEFOM));
				obj.setTelefoc(lrst.getString(P06BTablas.P06B_P06B28_TELEFOC)==null?"":lrst.getString(P06BTablas.P06B_P06B28_TELEFOC));
				obj.setFax1(lrst.getString(P06BTablas.P06B_P06B28_FAX1)==null?"":lrst.getString(P06BTablas.P06B_P06B28_FAX1));
				obj.setFax2(lrst.getString(P06BTablas.P06B_P06B28_FAX2)==null?"":lrst.getString(P06BTablas.P06B_P06B28_FAX2));
				obj.setMail(lrst.getString(P06BTablas.P06B_P06B28_MAIL)==null?"":lrst.getString(P06BTablas.P06B_P06B28_MAIL));
				obj.setWeb(lrst.getString(P06BTablas.P06B_P06B28_WEB)==null?"":lrst.getString(P06BTablas.P06B_P06B28_WEB));

	        	lista.add(obj);
	        }
	        P06BLog.send(2,this, "Fin P06B28T00Datatype-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 todos los responsab�es de atenci�n al cliente asociados al expediente concreto
    * @param exp.
    *            el expediente cuyos responsab�es de departamento de atenci�n al cliente queremos eliminar
    * @throws SQLException
    */
	public void deleteAllByExp(String exp) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B28T00DAO-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 P06B28T00DAO-deleteAllByExp");
	}


	/**
	    * Elimina todos los responsab�es de atenci�n al cliente asociados al expediente concreto
	    * @param exp.
	    *            el expediente cuyos responsab�es de departamento de atenci�n al cliente queremos eliminar
	    * @throws SQLException
	    */
		public void deleteByExpId(String exp, String id) throws SQLException{
			P06BLog.send(2,this, "Inicio P06B28T00DAO-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 P06B28T00DAO-deleteByExpId");
		}


	private static String INSERT_COMPLETA =
		"INSERT INTO "+ P06BTablas.P06B_P06B28T00_TABLA +
		" ("+
		P06BTablas.P06B_P06B28_NUMEXP + "," +
		P06BTablas.P06B_P06B28_IDENTIFICADOR + "," +
		P06BTablas.P06B_P06B28_IDENTIFICADOR1 + "," +
		P06BTablas.P06B_P06B28_TIPO + "," +
		P06BTablas.P06B_P06B28_RAZSOC + "," +
		P06BTablas.P06B_P06B28_NOMBRE + "," +
		P06BTablas.P06B_P06B28_APE1 + "," +
		P06BTablas.P06B_P06B28_APE2 + "," +
		P06BTablas.P06B_P06B28_DOMICI + "," +
		P06BTablas.P06B_P06B28_CODMUN + "," +
		P06BTablas.P06B_P06B28_LOCALI + "," +
		P06BTablas.P06B_P06B28_CODPRO + "," +
		P06BTablas.P06B_P06B28_CODPST + "," +
		P06BTablas.P06B_P06B28_TELEFOT + "," +
		P06BTablas.P06B_P06B28_TELEFOM + "," +
		P06BTablas.P06B_P06B28_TELEFOC + "," +
		P06BTablas.P06B_P06B28_FAX1 + "," +
		P06BTablas.P06B_P06B28_FAX2 + "," +
		P06BTablas.P06B_P06B28_MAIL + "," +
		P06BTablas.P06B_P06B28_WEB +
		")"+
		" VALUES(?,?,?,?,?,?,?,?,?,TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),?,?,?,?,?,?,?,?)";

	private static String SELECTALL_BY_EXP =
		"SELECT " +
		P06BTablas.P06B_P06B28_NUMEXP + "," +
		P06BTablas.P06B_P06B28_IDENTIFICADOR + "," +
		P06BTablas.P06B_P06B28_IDENTIFICADOR1 + "," +
		P06BTablas.P06B_P06B28_TIPO + "," +
		P06BTablas.P06B_P06B28_RAZSOC + "," +
		P06BTablas.P06B_P06B28_NOMBRE + "," +
		P06BTablas.P06B_P06B28_APE1 + "," +
		P06BTablas.P06B_P06B28_APE2 + "," +
		P06BTablas.P06B_P06B28_DOMICI + "," +
		P06BTablas.P06B_P06B28_CODMUN + "," +
		P06BTablas.P06B_P06B28_LOCALI + "," +
		P06BTablas.P06B_P06B28_CODPRO + "," +
		P06BTablas.P06B_P06B28_CODPST + "," +
		P06BTablas.P06B_P06B28_TELEFOT + "," +
		P06BTablas.P06B_P06B28_TELEFOM + "," +
		P06BTablas.P06B_P06B28_TELEFOC + "," +
		P06BTablas.P06B_P06B28_FAX1 + "," +
		P06BTablas.P06B_P06B28_FAX2 + "," +
		P06BTablas.P06B_P06B28_MAIL + "," +
		P06BTablas.P06B_P06B28_WEB +
		" FROM " + P06BTablas.P06B_P06B28T00_TABLA
		+ " WHERE " + P06BTablas.P06B_P06B28_NUMEXP + "= ?"
			+ " AND " + P06BTablas.P06B_P06B28_IDENTIFICADOR + "= ?"
		+" ORDER BY " + P06BTablas.P06B_P06B28_IDENTIFICADOR1;

	private static String DELETEALL_BYEXP =
		"DELETE FROM "+ P06BTablas.P06B_P06B28T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B28_NUMEXP + " = ?";

	private static String DELETEBYEXPID =
		"DELETE FROM "+ P06BTablas.P06B_P06B28T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B28_NUMEXP + " = ?"
		+ " AND " + P06BTablas.P06B_P06B28_IDENTIFICADOR + "= ?";

}
