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.List;

import com.ejie.p06b.datatype.model.P06BA1T00Datatype;
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: (09/05/07)
 */
public class P06BA1T00DAO {

	static private P06BA1T00DAO instance = null;

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

	/**
    * Obtiene todos los registros ordenados por c�digo
    * @return List una lista de P06BA1T00Datatype o una lista sin elementos
    *
    * @throws SQLException
    */
	public List selectAll() throws SQLException{
		P06BLog.send(2,this, "Inicio P06BA1T00DAO-selectAll");
		ArrayList result = new ArrayList();
		P06BA1T00Datatype registro = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_ALL;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lrst = lstm.executeQuery();
	        while (lrst.next()) {
	        	registro = new P06BA1T00Datatype();
	        	registro.setCodigo(lrst.getString(P06BTablas.P06B_P06A1T00_CODIGO));
	        	String desc = lrst.getString(P06BTablas.P06B_P06A1T00_DESCRIPCION);
	        	if(desc!=null)
	        		registro.setDescripcion(desc);
	        	String valor = lrst.getString(P06BTablas.P06B_P06A1T00_VALOR);
	        	if(valor!=null)
	        		registro.setValor(valor);

	        	result.add(registro);
	        }
	        P06BLog.send(2,this, "Fin P06BA1T00DAO-selectAll");
	    } 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) {}
	    }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) {}
	    }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 result;
	}

	/**
    * Obtiene un registro de la BD a partir del c�digo
    * @param codigoPK.
    *            clave primaria
    * @return un objeto P06BA1T00Datatype o null si no se encuentra
    * nada.
    * @throws SQLException
    */
	public P06BA1T00Datatype select(String codigoPK) throws SQLException{
		P06BLog.send(2,this, "Inicio P06BA1T00DAO-select");
		P06BA1T00Datatype result = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_BY_PK;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setString(1,codigoPK);
        	lrst = lstm.executeQuery();
	        if (lrst.next()) {
	        	result = new P06BA1T00Datatype();
	        	result.setCodigo(lrst.getString(P06BTablas.P06B_P06A1T00_CODIGO));
	        	String desc = lrst.getString(P06BTablas.P06B_P06A1T00_DESCRIPCION);
	        	if(desc!=null)
	        		result.setDescripcion(desc);
	        	String valor = lrst.getString(P06BTablas.P06B_P06A1T00_VALOR);
	        	if(valor!=null)
	        		result.setValor(valor);
	        }
	        P06BLog.send(2,this, "Fin P06BA1T00DAO-select");
	    } 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) {}
	    }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) {}
	    }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 result;
	}

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

		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = INSERT_COMPLETA;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
    	lstm = lconConexion.prepareStatement(sql);
    	lstm.setString(1,obj.getCodigo());
    	lstm.setString(2,obj.getDescripcion());
    	lstm.setString(3,obj.getValor());
    	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 P06BA1T00DAO-insert");
	}

	/**
    * Elimina un registro de la BD
    * @param codigo.
    *            objeto a eliminar
    * @throws SQLException
    */
	public void delete(String codigo) throws SQLException{
		P06BLog.send(2,this, "Inicio P06BA1T00DAO-delete");

		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = DELETE_BY_PK;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
    	lstm = lconConexion.prepareStatement(sql);
    	lstm.setString(1,codigo);
    	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 P06BA1T00DAO-delete");
	}

	private static String SELECT_BY_PK =
		"SELECT * FROM "+ P06BTablas.P06B_P06A1T00_TABLA + " WHERE " +
		P06BTablas.P06B_P06A1T00_CODIGO + "= TO_NUMBER(?)";

	private static String SELECT_ALL =
		"SELECT * FROM "+ P06BTablas.P06B_P06A1T00_TABLA +
		" ORDER BY " + P06BTablas.P06B_P06A1T00_CODIGO ;

	private static String INSERT_COMPLETA =
		"INSERT INTO "+ P06BTablas.P06B_P06A1T00_TABLA +
		" VALUES(TO_NUMBER(?),?,?)";

	private static String DELETE_BY_PK =
		"DELETE FROM "+ P06BTablas.P06B_P06A1T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06A1T00_CODIGO + " = TO_NUMBER(?)";
}
