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.P06B25T00Datatype;
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: (21/02/2008)
 */
public class P06B25T00DAO {

	static private P06B25T00DAO instance = null;

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

	/**
    * Inserta un registro de la BD
    * @param obj.
    *            objeto a insertar
    * @return void
    * @throws SQLException
    */
	public void insert(P06B25T00Datatype obj) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B25T00DAO-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.getCorr().toUpperCase());
	    lstm.setString(3,obj.getDomici().toUpperCase());
	    lstm.setString(4,obj.getCodmun().toUpperCase());
	    lstm.setString(5,obj.getLocali().toUpperCase());
	    lstm.setString(6,obj.getCodpro().toUpperCase());
	    lstm.setString(7,obj.getCodpst().toUpperCase());
	    lstm.setString(8,obj.getTelefot().toUpperCase());
	    lstm.setString(9,obj.getTelefom().toUpperCase());
	    lstm.setString(10,obj.getTelefoc().toUpperCase());
	    lstm.setString(11,obj.getFax1().toUpperCase());
	    lstm.setString(12,obj.getFax2().toUpperCase());
	    lstm.setString(13,obj.getMail().toUpperCase());
	    lstm.setString(14,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 P06B25T00DAO-insert");
	}

	/**
    * Obtiene la lista sucursales asociadas al expediente
    * @param expediente.
    * @return Lista de objetos sucursales
	 * @throws Exception
    */
	public ArrayList selectAllByExp(String expediente) throws Exception{
		P06BLog.send(2,this, "Inicio P06B25T00Datatype-selectAllByExp");
		ArrayList lista = new ArrayList();
		P06B25T00Datatype 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());
        	lrst = lstm.executeQuery();
	        while (lrst.next()) {
	        	obj = new P06B25T00Datatype();
	        	obj.setNumExp(lrst.getString(P06BTablas.P06B_P06B25_NUMEXP));
			    obj.setCorr(lrst.getString(P06BTablas.P06B_P06B25_CORR));
			    obj.setDomici(lrst.getString(P06BTablas.P06B_P06B25_DOMICI)==null?"":lrst.getString(P06BTablas.P06B_P06B25_DOMICI));
			    obj.setCodmun(lrst.getString(P06BTablas.P06B_P06B25_CODMUN)==null?"":lrst.getString(P06BTablas.P06B_P06B25_CODMUN));
			    obj.setLocali(lrst.getString(P06BTablas.P06B_P06B25_LOCALI)==null?"":lrst.getString(P06BTablas.P06B_P06B25_LOCALI));
			    obj.setCodpro(lrst.getString(P06BTablas.P06B_P06B25_CODPRO)==null?"":lrst.getString(P06BTablas.P06B_P06B25_CODPRO));
			    obj.setCodpst(lrst.getString(P06BTablas.P06B_P06B25_CODPST)==null?"":lrst.getString(P06BTablas.P06B_P06B25_CODPST));
			    obj.setTelefot(lrst.getString(P06BTablas.P06B_P06B25_TELEFOT)==null?"":lrst.getString(P06BTablas.P06B_P06B25_TELEFOT));
			    obj.setTelefom(lrst.getString(P06BTablas.P06B_P06B25_TELEFOM)==null?"":lrst.getString(P06BTablas.P06B_P06B25_TELEFOM));
			    obj.setTelefoc(lrst.getString(P06BTablas.P06B_P06B25_TELEFOC)==null?"":lrst.getString(P06BTablas.P06B_P06B25_TELEFOC));
			    obj.setFax1(lrst.getString(P06BTablas.P06B_P06B25_FAX1)==null?"":lrst.getString(P06BTablas.P06B_P06B25_FAX1));
			    obj.setFax2(lrst.getString(P06BTablas.P06B_P06B25_FAX2)==null?"":lrst.getString(P06BTablas.P06B_P06B25_FAX2));
			    obj.setMail(lrst.getString(P06BTablas.P06B_P06B25_MAIL)==null?"":lrst.getString(P06BTablas.P06B_P06B25_MAIL));
			    obj.setWeb(lrst.getString(P06BTablas.P06B_P06B25_WEB)==null?"":lrst.getString(P06BTablas.P06B_P06B25_WEB));

	        	lista.add(obj);
	        }
	        P06BLog.send(2,this, "Fin P06B25T00Datatype-selectAllByExp");
	    } 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 sucursales asociadas a un expediente concreto
    * @param exp.
    *            el expediente cuyas sucursales eliminamos
    * @throws SQLException
    */
	public void deleteAllByExp(String exp) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B25T00DAO-deleteAllByExp");

		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = DELETEALL_BYEXP;
        lstm = lconConexion.prepareStatement(sql);
    	lstm.setString(1,exp.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 P06B25T00DAO-deleteAllByExp");
	}


	private static String INSERT_COMPLETA =
		"INSERT INTO "+ P06BTablas.P06B_P06B25T00_TABLA +
		" ("+
		P06BTablas.P06B_P06B25_NUMEXP + "," +
		P06BTablas.P06B_P06B25_CORR + "," +
		P06BTablas.P06B_P06B25_DOMICI + "," +
		P06BTablas.P06B_P06B25_CODMUN + "," +
		P06BTablas.P06B_P06B25_LOCALI + "," +
		P06BTablas.P06B_P06B25_CODPRO + "," +
		P06BTablas.P06B_P06B25_CODPST + "," +
		P06BTablas.P06B_P06B25_TELEFOT + "," +
		P06BTablas.P06B_P06B25_TELEFOM + "," +
		P06BTablas.P06B_P06B25_TELEFOC + "," +
		P06BTablas.P06B_P06B25_FAX1 + "," +
		P06BTablas.P06B_P06B25_FAX2 + "," +
		P06BTablas.P06B_P06B25_MAIL + "," +
		P06BTablas.P06B_P06B25_WEB +
		")"+
		" VALUES(?,TO_NUMBER(?),?,TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),?,?,?,?,?,?,?,?)";

	private static String SELECTALL_BY_EXP =
		"SELECT " +
		P06BTablas.P06B_P06B25_NUMEXP + "," +
		P06BTablas.P06B_P06B25_CORR + "," +
		P06BTablas.P06B_P06B25_DOMICI + "," +
		P06BTablas.P06B_P06B25_CODMUN + "," +
		P06BTablas.P06B_P06B25_LOCALI + "," +
		P06BTablas.P06B_P06B25_CODPRO + "," +
		P06BTablas.P06B_P06B25_CODPST + "," +
		P06BTablas.P06B_P06B25_TELEFOT + "," +
		P06BTablas.P06B_P06B25_TELEFOM + "," +
		P06BTablas.P06B_P06B25_TELEFOC + "," +
		P06BTablas.P06B_P06B25_FAX1 + "," +
		P06BTablas.P06B_P06B25_FAX2 + "," +
		P06BTablas.P06B_P06B25_MAIL + "," +
		P06BTablas.P06B_P06B25_WEB +
		" FROM " + P06BTablas.P06B_P06B25T00_TABLA
		+ " WHERE " + P06BTablas.P06B_P06B25_NUMEXP + "= ?"
		+" ORDER BY " + P06BTablas.P06B_P06B25_CORR;

	private static String DELETEALL_BYEXP =
		"DELETE FROM "+ P06BTablas.P06B_P06B25T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B25_NUMEXP + " = ?";





























}
