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.P06B26T00Datatype;
import com.ejie.p06b.general.P06BConstantes;
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 P06B26T00DAO {

	static private P06B26T00DAO instance = null;

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

	/**
    * Inserta un registro de la BD
    * @param obj.
    *            objeto a insertar
    * @return void
    * @throws SQLException
    */
	public void insert(P06B26T00Datatype obj) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B26T00DAO-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.getTipo().toUpperCase());
	    lstm.setString(4,obj.getClase().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.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 P06B26T00DAO-insert");
	}

	/**
    * Obtiene la lista de departamentos de atencion al cliente asociada al expediente
    * @param expediente.
    * @return Lista de objetos de departamentos de atenci�n al cliente
	 * @throws Exception
    */
	public ArrayList<P06B26T00Datatype> selectAllByExp(String expediente, String tipo) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B26T00Datatype-selectAllByExp");
		ArrayList<P06B26T00Datatype> lista = new ArrayList<P06B26T00Datatype>();
		P06B26T00Datatype obj = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECTALL_BY_EXPTIPO;
        if (expediente!=null){
        	if (!"".equals(expediente.trim())){
        		sql = sql + " WHERE " + P06BTablas.P06B_P06B26_NUMEXP + "= ?";
        	}
        	if (tipo!=null){
            	if (!"".equals(tipo.trim())){
            		sql = sql + " AND " + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + "= ?";
            	}
        	}
        }else if (tipo!=null){
        	if (!"".equals(tipo.trim())){
        		sql = sql + " WHERE " + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + "= ?";
        	}
        }
        sql = sql + " ORDER BY " + P06BTablas.P06B_P06B26_NUMEXP + ", " + P06BTablas.P06B_P06B26_IDENTIFICADOR;

        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
    	lstm = lconConexion.prepareStatement(sql);
        if (expediente!=null){
        	if (!"".equals(expediente.trim())){
            	lstm.setString(1,expediente.toUpperCase());
        	}
        	if (tipo!=null){
            	if (!"".equals(tipo.trim())){
                	lstm.setString(2,tipo);
            	}
        	}
        }else if (tipo!=null){
        	if (!"".equals(tipo.trim())){
            	lstm.setString(1,tipo);
        	}
        }
    	lrst = lstm.executeQuery();
        while (lrst.next()) {
        	obj = new P06B26T00Datatype();
        	obj.setNumExp(lrst.getString(P06BTablas.P06B_P06B26_NUMEXP));
		    obj.setIdentificador(lrst.getString(P06BTablas.P06B_P06B26_IDENTIFICADOR));
		    obj.setTipo(lrst.getString(P06BTablas.P06B_P06B26_TIPO)==null?"":lrst.getString(P06BTablas.P06B_P06B26_TIPO));
		    obj.setClase(lrst.getString(P06BTablas.P06B_P06B26_CLASE)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CLASE));
		    obj.setRazsoc(lrst.getString(P06BTablas.P06B_P06B26_RAZSOC)==null?"":lrst.getString(P06BTablas.P06B_P06B26_RAZSOC));
		    obj.setNombre(lrst.getString(P06BTablas.P06B_P06B26_NOMBRE)==null?"":lrst.getString(P06BTablas.P06B_P06B26_NOMBRE));
		    obj.setApe1(lrst.getString(P06BTablas.P06B_P06B26_APE1)==null?"":lrst.getString(P06BTablas.P06B_P06B26_APE1));
		    obj.setApe2(lrst.getString(P06BTablas.P06B_P06B26_APE2)==null?"":lrst.getString(P06BTablas.P06B_P06B26_APE2));
		    obj.setDomici(lrst.getString(P06BTablas.P06B_P06B26_DOMICI)==null?"":lrst.getString(P06BTablas.P06B_P06B26_DOMICI));
		    obj.setCodmun(lrst.getString(P06BTablas.P06B_P06B26_CODMUN)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODMUN));
		    obj.setLocali(lrst.getString(P06BTablas.P06B_P06B26_LOCALI)==null?"":lrst.getString(P06BTablas.P06B_P06B26_LOCALI));
		    obj.setCodpro(lrst.getString(P06BTablas.P06B_P06B26_CODPRO)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODPRO));
		    obj.setCodpst(lrst.getString(P06BTablas.P06B_P06B26_CODPST)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODPST));

        	lista.add(obj);
        }
        P06BLog.send(2,this, "Fin P06B26T00Datatype-selectAllByExp");
		lrst.close();
		lstm.close();
		lconConexion.close();
		return lista;
	}

	/**
    * Obtiene la lista de departamentos de atencion al cliente asociada al expediente
    * @param expediente.
    * @return Lista de objetos de departamentos de atenci�n al cliente
	 * @throws Exception
    */
	public ArrayList<P06B26T00Datatype> selectInformeComunicarDGSFP2() throws SQLException{
		P06BLog.send(2,this, "Inicio P06B26T00Datatype-selectInformeComunicarDGSFP2");
		ArrayList<P06B26T00Datatype> lista = new ArrayList<P06B26T00Datatype>();
		P06B26T00Datatype obj = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_LISTADO2;

        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
    	lstm = lconConexion.prepareStatement(sql);
    	lrst = lstm.executeQuery();
        while (lrst.next()) {
        	obj = new P06B26T00Datatype();
        	obj.setNumExp(lrst.getString(1));
        	obj.setTipo(lrst.getString(2));
		    obj.setRazsoc(lrst.getString(3)==null?"":lrst.getString(3));
		    obj.setNombre(lrst.getString(4)==null?"":lrst.getString(4));
		    obj.setApe1(lrst.getString(5)==null?"":lrst.getString(5));
		    obj.setApe2(lrst.getString(6)==null?"":lrst.getString(6));
		    obj.setDomici(lrst.getString(7)==null?"":lrst.getString(7));

        	lista.add(obj);
        }
        P06BLog.send(2,this, "Fin P06B26T00Datatype-selectInformeComunicarDGSFP2");
		lrst.close();
		lstm.close();
		lconConexion.close();
		return lista;
	}

	/**
    * Obtiene la lista de departamentos de atencion al cliente asociada al expediente
    * @param expediente.
    * @return Lista de objetos de departamentos de atenci�n al cliente
	 * @throws Exception
    */
	public P06B26T00Datatype selectAtClienteAdherido(String adheridoAlColegioExpedienteFicticio, String adheridoAlColegio) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B26T00Datatype-selectAtClienteAdherido");
		P06B26T00Datatype p06B26T00Datatype = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECTALL_BY_EXPTIPO;
		sql = sql + " WHERE " + P06BTablas.P06B_P06B26_NUMEXP + "= ?";
    	sql = sql + " AND " + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + "= ?";

        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try{
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setString(1,adheridoAlColegioExpedienteFicticio);
        	lstm.setString(2,adheridoAlColegio);
        	lrst = lstm.executeQuery();
            if (lrst.next()) {
            	p06B26T00Datatype = new P06B26T00Datatype();
            	p06B26T00Datatype.setNumExp(lrst.getString(P06BTablas.P06B_P06B26_NUMEXP));
            	if (lrst.getString(P06BTablas.P06B_P06B26_IDENTIFICADOR).equals(P06BConstantes.P06B_AC_ADHERIDOALCOLEGIODNIFICTICIO) ||
            		lrst.getString(P06BTablas.P06B_P06B26_IDENTIFICADOR).equals(P06BConstantes.P06B_AC_ADHERIDOALCOLEGIOCIFFICTICIO)){
        			p06B26T00Datatype.setIdentificador("");
            	}else{
            		p06B26T00Datatype.setIdentificador(lrst.getString(P06BTablas.P06B_P06B26_IDENTIFICADOR));
            	}
    		    p06B26T00Datatype.setTipo(lrst.getString(P06BTablas.P06B_P06B26_TIPO)==null?"":lrst.getString(P06BTablas.P06B_P06B26_TIPO));
    		    p06B26T00Datatype.setClase(lrst.getString(P06BTablas.P06B_P06B26_CLASE)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CLASE));
    		    p06B26T00Datatype.setRazsoc(lrst.getString(P06BTablas.P06B_P06B26_RAZSOC)==null?"":lrst.getString(P06BTablas.P06B_P06B26_RAZSOC));
    		    p06B26T00Datatype.setNombre(lrst.getString(P06BTablas.P06B_P06B26_NOMBRE)==null?"":lrst.getString(P06BTablas.P06B_P06B26_NOMBRE));
    		    p06B26T00Datatype.setApe1(lrst.getString(P06BTablas.P06B_P06B26_APE1)==null?"":lrst.getString(P06BTablas.P06B_P06B26_APE1));
    		    p06B26T00Datatype.setApe2(lrst.getString(P06BTablas.P06B_P06B26_APE2)==null?"":lrst.getString(P06BTablas.P06B_P06B26_APE2));
    		    p06B26T00Datatype.setDomici(lrst.getString(P06BTablas.P06B_P06B26_DOMICI)==null?"":lrst.getString(P06BTablas.P06B_P06B26_DOMICI));
    		    p06B26T00Datatype.setCodmun(lrst.getString(P06BTablas.P06B_P06B26_CODMUN)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODMUN));
    		    p06B26T00Datatype.setLocali(lrst.getString(P06BTablas.P06B_P06B26_LOCALI)==null?"":lrst.getString(P06BTablas.P06B_P06B26_LOCALI));
    		    p06B26T00Datatype.setCodpro(lrst.getString(P06BTablas.P06B_P06B26_CODPRO)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODPRO));
    		    p06B26T00Datatype.setCodpst(lrst.getString(P06BTablas.P06B_P06B26_CODPST)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODPST));
            }
            P06BLog.send(2,this, "Fin P06B26T00Datatype-selectAtClienteAdherido");
        }
        finally{
    		if(lrst!=null)
    			lrst.close();
        	if(lstm!=null)
        		lstm.close();
        	if(lconConexion!=null)
        		lconConexion.close();
        }
		return p06B26T00Datatype;
	}


	/**
    */
	public void updateAtClienteAdherido(P06B26T00Datatype atCliente) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B26T00Datatype-updateAtClienteAdherido");

		PreparedStatement lstm  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = UPDATE_ADHERIDO;
        try{
	        lstm = lconConexion.prepareStatement(sql);

		    lstm.setString(2,atCliente.getTipo().toUpperCase());
		    lstm.setString(7,atCliente.getNumExp().toUpperCase());
			if ("".equals(atCliente.getIdentificador())){
			    if (atCliente.getTipo().toUpperCase().equals(P06BConstantes.P06B_PERSONA_FISICA)){
			    	atCliente.setIdentificador(P06BConstantes.P06B_AC_ADHERIDOALCOLEGIODNIFICTICIO.toUpperCase());
			    	lstm.setString(1,P06BConstantes.P06B_AC_ADHERIDOALCOLEGIODNIFICTICIO.toUpperCase());
			    }else{
			    	atCliente.setIdentificador(P06BConstantes.P06B_AC_ADHERIDOALCOLEGIODNIFICTICIO.toUpperCase());
			    	lstm.setString(1,P06BConstantes.P06B_AC_ADHERIDOALCOLEGIOCIFFICTICIO.toUpperCase());
			    }
		    	lstm.setNull(3, java.sql.Types.VARCHAR);
		    	lstm.setNull(4, java.sql.Types.VARCHAR);
		    	lstm.setNull(5, java.sql.Types.VARCHAR);
		    	lstm.setNull(6, java.sql.Types.VARCHAR);
		    	atCliente.setRazsoc(null);
		    	atCliente.setNombre(null);
		    	atCliente.setApe1(null);
		    	atCliente.setApe2(null);
			}else{
			    lstm.setString(1,atCliente.getIdentificador().toUpperCase());
		    	atCliente.setIdentificador(atCliente.getIdentificador().toUpperCase());
			    if (atCliente.getTipo().toUpperCase().equals(P06BConstantes.P06B_PERSONA_FISICA)){
			    	lstm.setNull(3, java.sql.Types.VARCHAR);
			    	lstm.setString(4,atCliente.getNombre().toUpperCase());
			    	lstm.setString(5,atCliente.getApe1().toUpperCase());
			    	lstm.setString(6,atCliente.getApe2().toUpperCase());
			    	atCliente.setRazsoc(null);
			    	atCliente.setNombre(atCliente.getNombre().toUpperCase());
			    	atCliente.setApe1(atCliente.getApe1().toUpperCase());
			    	atCliente.setApe2(atCliente.getApe2().toUpperCase());
			    }else{
			    	lstm.setString(3,atCliente.getRazsoc().toUpperCase());
			    	lstm.setNull(4, java.sql.Types.VARCHAR);
			    	lstm.setNull(5, java.sql.Types.VARCHAR);
			    	lstm.setNull(6, java.sql.Types.VARCHAR);
			    	atCliente.setRazsoc(atCliente.getRazsoc().toUpperCase());
			    	atCliente.setNombre(null);
			    	atCliente.setApe1(null);
			    	atCliente.setApe2(null);
			    }
			}
		    if (atCliente.getNumExp().toUpperCase().equals(P06BConstantes.P06B_AC_ADHERIDOALCOLEGIOCVEXPEDIENTEFICTICIO)){
			    lstm.setString(8,P06BConstantes.P06B_AC_ADHERIDOALCOLEGIOCV);
		    }else{
			    lstm.setString(8,P06BConstantes.P06B_AC_ADHERIDOALCOLEGIOCG);
		    }
	    	lstm.executeUpdate();
	        P06BLog.send(2,this, "Fin P06B26T00Datatype-updateAtClienteAdherido");
        }catch (SQLException sqle){
	        P06BLog.send(2,this, "updateAtClienteAdherido: " + sqle.getMessage());
	        throw(sqle);
	    }finally {
        	if(lstm!=null)
        		lstm.close();
        	if(lconConexion!=null)
        		lconConexion.close();
	    }
	}


	/**
    * Obtiene la lista de departamentos de atencion al cliente asociada al expediente
    * @param expediente.
    * @return Lista de objetos de departamentos de atenci�n al cliente
	 * @throws Exception
    */
	public ArrayList<P06B26T00Datatype> selectAllByExpIncorporacionModTramitagune(String expediente, int nCorrelAtencionPeticionEnRevision, String tipo, String expedienteEnAtencionDefensaCliente) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B26T00Datatype-selectAllByExpIncorporacionModTramitagune");
		ArrayList<P06B26T00Datatype> lista = new ArrayList<P06B26T00Datatype>();
		P06B26T00Datatype obj = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECTALL_INCORPORACIONMODTRAMITAGUNE_BY_EXPTIPO;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setString(1,expediente.toUpperCase());
        	lstm.setInt(2,nCorrelAtencionPeticionEnRevision);
        	lstm.setString(3,expedienteEnAtencionDefensaCliente.toUpperCase());
        	lstm.setString(4,tipo);
        	lstm.setString(5,expediente.toUpperCase());
        	lstm.setInt(6,nCorrelAtencionPeticionEnRevision);
        	lstm.setString(7,expedienteEnAtencionDefensaCliente.toUpperCase());
        	lstm.setString(8,tipo);
        	lstm.setString(9,expediente.toUpperCase());
        	lstm.setInt(10,nCorrelAtencionPeticionEnRevision);
        	lrst = lstm.executeQuery();
	        while (lrst.next()) {
	        	int operacion = lrst.getInt("operacion");

	        	obj = new P06B26T00Datatype();
	        	obj.setNumExp(expediente.toUpperCase());
	        	obj.setOperacion(operacion);
//			    obj.setAprobado(0);
	        	if (operacion==0){
				    obj.setIdentificador(lrst.getString(P06BTablas.P06B_P06B26_IDENTIFICADOR));
				    obj.setTipo(lrst.getString(P06BTablas.P06B_P06B26_TIPO)==null?"":lrst.getString(P06BTablas.P06B_P06B26_TIPO));
				    obj.setClase(lrst.getString(P06BTablas.P06B_P06B26_CLASE)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CLASE));
				    obj.setRazsoc(lrst.getString(P06BTablas.P06B_P06B26_RAZSOC)==null?"":lrst.getString(P06BTablas.P06B_P06B26_RAZSOC));
				    obj.setNombre(lrst.getString(P06BTablas.P06B_P06B26_NOMBRE)==null?"":lrst.getString(P06BTablas.P06B_P06B26_NOMBRE));
				    obj.setApe1(lrst.getString(P06BTablas.P06B_P06B26_APE1)==null?"":lrst.getString(P06BTablas.P06B_P06B26_APE1));
				    obj.setApe2(lrst.getString(P06BTablas.P06B_P06B26_APE2)==null?"":lrst.getString(P06BTablas.P06B_P06B26_APE2));
				    obj.setDomici(lrst.getString(P06BTablas.P06B_P06B26_DOMICI)==null?"":lrst.getString(P06BTablas.P06B_P06B26_DOMICI));
				    obj.setCodmun(lrst.getString(P06BTablas.P06B_P06B26_CODMUN)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODMUN));
				    obj.setLocali(lrst.getString(P06BTablas.P06B_P06B26_LOCALI)==null?"":lrst.getString(P06BTablas.P06B_P06B26_LOCALI));
				    obj.setCodpro(lrst.getString(P06BTablas.P06B_P06B26_CODPRO)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODPRO));
				    obj.setCodpst(lrst.getString(P06BTablas.P06B_P06B26_CODPST)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODPST));
	        	}

	        	if (operacion==1 || operacion==2){
				    obj.setIdentificador(lrst.getString(P06BTablas.P06B_P06B76_AC_IDENTIFICADOR));
				    obj.setTipo(lrst.getString(P06BTablas.P06B_P06B76_AC_TIPO)==null?"":lrst.getString(P06BTablas.P06B_P06B76_AC_TIPO));
				    obj.setRazsoc(lrst.getString(P06BTablas.P06B_P06B76_AC_RAZSOC)==null?"":lrst.getString(P06BTablas.P06B_P06B76_AC_RAZSOC));
				    obj.setNombre(lrst.getString(P06BTablas.P06B_P06B76_AC_NOMBRE)==null?"":lrst.getString(P06BTablas.P06B_P06B76_AC_NOMBRE));
				    obj.setApe1(lrst.getString(P06BTablas.P06B_P06B76_AC_APE1)==null?"":lrst.getString(P06BTablas.P06B_P06B76_AC_APE1));
				    obj.setApe2(lrst.getString(P06BTablas.P06B_P06B76_AC_APE2)==null?"":lrst.getString(P06BTablas.P06B_P06B76_AC_APE2));
				    if (operacion==2){
				    	//Inicializar campos no aportados por Tramitagune para que no se pierdan
					    obj.setClase(lrst.getString(P06BTablas.P06B_P06B26_CLASE)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CLASE));
					    obj.setDomici(lrst.getString(P06BTablas.P06B_P06B26_DOMICI)==null?"":lrst.getString(P06BTablas.P06B_P06B26_DOMICI));
					    obj.setCodmun(lrst.getString(P06BTablas.P06B_P06B26_CODMUN)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODMUN));
					    obj.setLocali(lrst.getString(P06BTablas.P06B_P06B26_LOCALI)==null?"":lrst.getString(P06BTablas.P06B_P06B26_LOCALI));
					    obj.setCodpro(lrst.getString(P06BTablas.P06B_P06B26_CODPRO)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODPRO));
					    obj.setCodpst(lrst.getString(P06BTablas.P06B_P06B26_CODPST)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODPST));
				    }
				}

			    if (operacion==0 || operacion==2){
			    	P06B26T00Datatype datos26Originales = new P06B26T00Datatype();
			    	datos26Originales.setNumExp(expediente.toUpperCase());
				    datos26Originales.setIdentificador(lrst.getString(P06BTablas.P06B_P06B26_IDENTIFICADOR));
				    datos26Originales.setTipo(lrst.getString(P06BTablas.P06B_P06B26_TIPO)==null?"":lrst.getString(P06BTablas.P06B_P06B26_TIPO));
				    datos26Originales.setClase(lrst.getString(P06BTablas.P06B_P06B26_CLASE)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CLASE));
				    datos26Originales.setRazsoc(lrst.getString(P06BTablas.P06B_P06B26_RAZSOC)==null?"":lrst.getString(P06BTablas.P06B_P06B26_RAZSOC));
				    datos26Originales.setNombre(lrst.getString(P06BTablas.P06B_P06B26_NOMBRE)==null?"":lrst.getString(P06BTablas.P06B_P06B26_NOMBRE));
				    datos26Originales.setApe1(lrst.getString(P06BTablas.P06B_P06B26_APE1)==null?"":lrst.getString(P06BTablas.P06B_P06B26_APE1));
				    datos26Originales.setApe2(lrst.getString(P06BTablas.P06B_P06B26_APE2)==null?"":lrst.getString(P06BTablas.P06B_P06B26_APE2));
				    datos26Originales.setDomici(lrst.getString(P06BTablas.P06B_P06B26_DOMICI)==null?"":lrst.getString(P06BTablas.P06B_P06B26_DOMICI));
				    datos26Originales.setCodmun(lrst.getString(P06BTablas.P06B_P06B26_CODMUN)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODMUN));
				    datos26Originales.setLocali(lrst.getString(P06BTablas.P06B_P06B26_LOCALI)==null?"":lrst.getString(P06BTablas.P06B_P06B26_LOCALI));
				    datos26Originales.setCodpro(lrst.getString(P06BTablas.P06B_P06B26_CODPRO)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODPRO));
				    datos26Originales.setCodpst(lrst.getString(P06BTablas.P06B_P06B26_CODPST)==null?"":lrst.getString(P06BTablas.P06B_P06B26_CODPST));
			    	obj.setDatos26Originales(datos26Originales);
        		}

	        	lista.add(obj);
	        }
	        P06BLog.send(2,this, "Fin P06B26T00Datatype-selectAllByExpIncorporacionModTramitagune");
	    }finally {
    		if(lrst!=null)
    			lrst.close();
        	if(lstm!=null)
        		lstm.close();
        	if(lconConexion!=null)
        		lconConexion.close();
	    }
		return lista;
	}


	/**
    * Elimina todos los departamentos de atenci�n al cliente asociados a un expediente concreto
    * @param exp.
    *            el expediente cuyos departamentos de atenci�n al cliente eliminamos
    * @throws SQLException
    */
	public void deleteAllByExp(String exp, String tipo) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B26T00DAO-deleteAllByExp");

		PreparedStatement lstm  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = DELETEALL_BYEXPTIPO;
        lstm = lconConexion.prepareStatement(sql);
    	lstm.setString(1,exp.toUpperCase());
    	lstm.setString(2,tipo);
    	lstm.executeUpdate();
    	//Cierro recursos
    	if(lstm!=null) lstm.close();
    	if(lconConexion!=null)lconConexion.close();
        P06BLog.send(2,this, "Fin P06B26T00DAO-deleteAllByExp");
	}


	/**
    * Elimina todos los departamentos de atenci�n al cliente asociados a un expediente concreto
    * @param exp.
    *            el expediente cuyos departamentos de atenci�n al cliente eliminamos
    * @throws SQLException
    */
	public void deleteByExpId(String exp, String id, String tipo) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B26T00DAO-deleteByExpId");

		PreparedStatement lstm  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = DELETEBYEXPIDTIPO;
        lstm = lconConexion.prepareStatement(sql);
    	lstm.setString(1,exp.toUpperCase());
    	lstm.setString(2,id);
    	lstm.setString(3,tipo);
    	lstm.executeUpdate();
    	//Cierro recursos
    	if(lstm!=null) lstm.close();
    	if(lconConexion!=null)lconConexion.close();
        P06BLog.send(2,this, "Fin P06B26T00DAO-deleteByExpId");
	}


	private static String INSERT_COMPLETA =
		"INSERT INTO "+ P06BTablas.P06B_P06B26T00_TABLA +
		" ("+
		P06BTablas.P06B_P06B26_NUMEXP + "," +
		P06BTablas.P06B_P06B26_IDENTIFICADOR + "," +
		P06BTablas.P06B_P06B26_TIPO + "," +
		P06BTablas.P06B_P06B26_CLASE + "," +
		P06BTablas.P06B_P06B26_RAZSOC + "," +
		P06BTablas.P06B_P06B26_NOMBRE + "," +
		P06BTablas.P06B_P06B26_APE1 + "," +
		P06BTablas.P06B_P06B26_APE2 + "," +
		P06BTablas.P06B_P06B26_DOMICI + "," +
		P06BTablas.P06B_P06B26_CODMUN + "," +
		P06BTablas.P06B_P06B26_LOCALI + "," +
		P06BTablas.P06B_P06B26_CODPRO + "," +
		P06BTablas.P06B_P06B26_CODPST +
		")"+
		" VALUES(?,?,?,?,?,?,?,?,?,TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),?)";

	private static String UPDATE_ADHERIDO =
		"UPDATE "+ P06BTablas.P06B_P06B26T00_TABLA +
		" SET " +
		P06BTablas.P06B_P06B26_IDENTIFICADOR + "=?, " +
		P06BTablas.P06B_P06B26_TIPO + "=?, " +
		P06BTablas.P06B_P06B26_RAZSOC + "=?, " +
		P06BTablas.P06B_P06B26_NOMBRE + "=?, " +
		P06BTablas.P06B_P06B26_APE1 + "=?, " +
		P06BTablas.P06B_P06B26_APE2 + "=? " +
		"WHERE " + P06BTablas.P06B_P06B26_NUMEXP + "=? " +
		"AND " + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + "= ?";

	private static String SELECTALL_BY_EXPTIPO =
		"SELECT " +
		P06BTablas.P06B_P06B26_NUMEXP + "," +
		P06BTablas.P06B_P06B26_IDENTIFICADOR + "," +
		P06BTablas.P06B_P06B26_TIPO + "," +
		P06BTablas.P06B_P06B26_CLASE + "," +
		P06BTablas.P06B_P06B26_RAZSOC + "," +
		P06BTablas.P06B_P06B26_NOMBRE + "," +
		P06BTablas.P06B_P06B26_APE1 + "," +
		P06BTablas.P06B_P06B26_APE2 + "," +
		P06BTablas.P06B_P06B26_DOMICI + "," +
		P06BTablas.P06B_P06B26_CODMUN + "," +
		P06BTablas.P06B_P06B26_LOCALI + "," +
		P06BTablas.P06B_P06B26_CODPRO + "," +
		P06BTablas.P06B_P06B26_CODPST +
		" FROM " + P06BTablas.P06B_P06B26T00_TABLA;

	private static String SELECT_LISTADO2 =
		"SELECT " +
		P06BTablas.P06B_P06B20_NUMEXP + "," +
		P06BTablas.P06B_P06B26_TIPO + "," +
		P06BTablas.P06B_P06B26_RAZSOC + "," +
		P06BTablas.P06B_P06B26_NOMBRE + "," +
		P06BTablas.P06B_P06B26_APE1 + "," +
		P06BTablas.P06B_P06B26_APE2 + "," +
		P06BTablas.P06B_P06B26_DOMICI + "||LPAD(" + P06BTablas.P06B_P06B26_CODPST + ",6)|| LPAD(UPPER(DS_O),LENGTH(UPPER(DS_O))+1)" +
		" FROM P06BENS00, " + P06BTablas.P06B_P06B26T00_TABLA + ", " + P06BTablas.P06B_P06B20T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B26_NUMEXP + " = " +
		"DECODE(" + P06BTablas.P06B_P06B20_TIPOATENCIONCLIENTE + "," + P06BConstantes.P06B_AC_CUENTACONSERVICIO + "," + P06BTablas.P06B_P06B20_NUMEXP + "," +
		"DECODE(" + P06BTablas.P06B_P06B20_TIPOATENCIONCLIENTE + "," + P06BConstantes.P06B_AC_ADHERIDOALCOLEGIOCV + ",'" + P06BConstantes.P06B_AC_ADHERIDOALCOLEGIOCVEXPEDIENTEFICTICIO + "','" + P06BConstantes.P06B_AC_ADHERIDOALCOLEGIOCGEXPEDIENTEFICTICIO + "')) AND " +
		P06BTablas.P06B_P06B20_TIPOATENCIONCLIENTE + " in (" + P06BConstantes.P06B_AC_ADHERIDOALCOLEGIOCV + "," + P06BConstantes.P06B_AC_ADHERIDOALCOLEGIOCG + "," + P06BConstantes.P06B_AC_CUENTACONSERVICIO + ") AND " +
		P06BTablas.P06B_P06B20_ESTADO + " in ('" + P06BConstantes.P06B_CORREDORES_ESTADO_ALTA + "','" + P06BConstantes.P06B_CORREDORES_ESTADO_SUSPENSION + "','" + P06BConstantes.P06B_CORREDORES_ESTADO_REVISION + "','" + P06BConstantes.P06B_CORREDORES_ESTADO_PDTE_BAJA + "','" + P06BConstantes.P06B_CORREDORES_ESTADO_BAJA + "') " +
		" AND TO_NUMBER(PROVINCIA_ID(+))= " + P06BTablas.P06B_P06B26_CODPRO +
		" AND TO_NUMBER(MUNICIPIO_ID(+)) = " + P06BTablas.P06B_P06B26_CODMUN +
		" AND TO_NUMBER(NUMERO(+)) = " + P06BTablas.P06B_P06B26_LOCALI +
		" ORDER BY " + P06BTablas.P06B_P06B20_NUMEXP + ", " + P06BTablas.P06B_P06B26_IDENTIFICADOR;

	private static String SELECTALL_INCORPORACIONMODTRAMITAGUNE_BY_EXPTIPO =
		"SELECT "
		+ "2 AS operacion, "
		+ "A." + P06BTablas.P06B_P06B26_IDENTIFICADOR + ","
		+ "A." + P06BTablas.P06B_P06B26_TIPO + ","
		+ "A." + P06BTablas.P06B_P06B26_CLASE + ","
		+ "A." + P06BTablas.P06B_P06B26_RAZSOC + ","
		+ "A." + P06BTablas.P06B_P06B26_NOMBRE + ","
		+ "A." + P06BTablas.P06B_P06B26_APE1 + ","
		+ "A." + P06BTablas.P06B_P06B26_APE2 + ","
		+ "A." + P06BTablas.P06B_P06B26_DOMICI + ","
		+ "A." + P06BTablas.P06B_P06B26_CODMUN + ","
		+ "A." + P06BTablas.P06B_P06B26_LOCALI + ","
		+ "A." + P06BTablas.P06B_P06B26_CODPRO + ","
		+ "A." + P06BTablas.P06B_P06B26_CODPST + ","
		+ "S." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR + ", "
		+ "S." + P06BTablas.P06B_P06B76_AC_TIPO + ", "
		+ "S." + P06BTablas.P06B_P06B76_AC_RAZSOC + ", "
		+ "S." + P06BTablas.P06B_P06B76_AC_NOMBRE + ", "
		+ "S." + P06BTablas.P06B_P06B76_AC_APE1 + ", "
		+ "S." + P06BTablas.P06B_P06B76_AC_APE2
		+ " FROM " + P06BTablas.P06B_P06B26T00_TABLA + " A,"
		+ P06BTablas.P06B_P06B76T00_TABLA + " S"
		+ " WHERE "
		+ "S." + P06BTablas.P06B_P06B76_AC_NUMEXP + "=?"
		+ " AND S." + P06BTablas.P06B_P06B76_AC_CORRELATENCPETIC + "=?"
		+ " AND A." + P06BTablas.P06B_P06B26_NUMEXP + "= ?"
		+ " AND A." + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + "= ?"
		+ " AND A." + P06BTablas.P06B_P06B26_NUMEXP + "=S." + P06BTablas.P06B_P06B76_AC_NUMEXP
		+ " AND A." + P06BTablas.P06B_P06B26_IDENTIFICADOR + "=S." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR
		+ " UNION"
		+ " SELECT "
		+ "1, "
		+ "NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, "
		+ "NULL, NULL, NULL, NULL, "
		+ "A." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR + ", "
		+ "A." + P06BTablas.P06B_P06B76_AC_TIPO + ", "
		+ "A." + P06BTablas.P06B_P06B76_AC_RAZSOC + ", "
		+ "A." + P06BTablas.P06B_P06B76_AC_NOMBRE + ", "
		+ "A." + P06BTablas.P06B_P06B76_AC_APE1 + ", "
		+ "A." + P06BTablas.P06B_P06B76_AC_APE2
		+ " FROM " + P06BTablas.P06B_P06B76T00_TABLA + " A,("
		+ "SELECT "
		+ "S." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR + " FROM " + P06BTablas.P06B_P06B76T00_TABLA + " S"
		+ " WHERE "
		+ "S." + P06BTablas.P06B_P06B76_AC_NUMEXP + "=?"
		+ " AND S." + P06BTablas.P06B_P06B76_AC_CORRELATENCPETIC + "=?"
		+ " MINUS "
		+ "SELECT "
		+ P06BTablas.P06B_P06B26_IDENTIFICADOR + " FROM " + P06BTablas.P06B_P06B26T00_TABLA + " T1"
		+ " WHERE "
		+ "T1." + P06BTablas.P06B_P06B26_NUMEXP + "=?"
		+ " AND T1." + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + "=?) C"
		+ " WHERE "
		+ "A." + P06BTablas.P06B_P06B76_AC_NUMEXP + "=?"
		+ " AND A." + P06BTablas.P06B_P06B76_AC_CORRELATENCPETIC + "=?"
		+ " AND C." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR + "=A." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR
		+ " ORDER BY 1 DESC,2";

	private static String SELECTALL_INCORPORACIONMODTRAMITAGUNE_BY_EXPTIPOOLD =
		"SELECT "
		+ "2 AS operacion, "
		+ "A." + P06BTablas.P06B_P06B26_IDENTIFICADOR + ","
		+ "A." + P06BTablas.P06B_P06B26_TIPO + ","
		+ "A." + P06BTablas.P06B_P06B26_CLASE + ","
		+ "A." + P06BTablas.P06B_P06B26_RAZSOC + ","
		+ "A." + P06BTablas.P06B_P06B26_NOMBRE + ","
		+ "A." + P06BTablas.P06B_P06B26_APE1 + ","
		+ "A." + P06BTablas.P06B_P06B26_APE2 + ","
		+ "A." + P06BTablas.P06B_P06B26_DOMICI + ","
		+ "A." + P06BTablas.P06B_P06B26_CODMUN + ","
		+ "A." + P06BTablas.P06B_P06B26_LOCALI + ","
		+ "A." + P06BTablas.P06B_P06B26_CODPRO + ","
		+ "A." + P06BTablas.P06B_P06B26_CODPST + ","
		+ "S." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR + ", "
		+ "S." + P06BTablas.P06B_P06B76_AC_TIPO + ", "
		+ "S." + P06BTablas.P06B_P06B76_AC_RAZSOC + ", "
		+ "S." + P06BTablas.P06B_P06B76_AC_NOMBRE + ", "
		+ "S." + P06BTablas.P06B_P06B76_AC_APE1 + ", "
		+ "S." + P06BTablas.P06B_P06B76_AC_APE2
		+ " FROM " + P06BTablas.P06B_P06B26T00_TABLA + " A,"
		+ P06BTablas.P06B_P06B76T00_TABLA + " S"
		+ " WHERE "
		+ "S." + P06BTablas.P06B_P06B76_AC_NUMEXP + "=?"
		+ " AND S." + P06BTablas.P06B_P06B76_AC_CORRELATENCPETIC + "=?"
		+ " AND A." + P06BTablas.P06B_P06B26_NUMEXP + "= ?"
		+ " AND A." + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + "= ?"
		+ " AND A." + P06BTablas.P06B_P06B26_NUMEXP + "=S." + P06BTablas.P06B_P06B76_AC_NUMEXP
		+ " AND A." + P06BTablas.P06B_P06B26_IDENTIFICADOR + "=S." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR
		+ " UNION"
		+ " SELECT 0, "
		+ "A." + P06BTablas.P06B_P06B26_IDENTIFICADOR + ","
		+ "A." + P06BTablas.P06B_P06B26_TIPO + ","
		+ "A." + P06BTablas.P06B_P06B26_CLASE + ","
		+ "A." + P06BTablas.P06B_P06B26_RAZSOC + ","
		+ "A." + P06BTablas.P06B_P06B26_NOMBRE + ","
		+ "A." + P06BTablas.P06B_P06B26_APE1 + ","
		+ "A." + P06BTablas.P06B_P06B26_APE2 + ","
		+ "A." + P06BTablas.P06B_P06B26_DOMICI + ","
		+ "A." + P06BTablas.P06B_P06B26_CODMUN + ","
		+ "A." + P06BTablas.P06B_P06B26_LOCALI + ","
		+ "A." + P06BTablas.P06B_P06B26_CODPRO + ","
		+ "A." + P06BTablas.P06B_P06B26_CODPST + ","
		+ "NULL, NULL, NULL, NULL, NULL, NULL"
		+ " FROM " + P06BTablas.P06B_P06B26T00_TABLA + " A,("
		+ "SELECT "
		+ P06BTablas.P06B_P06B26_IDENTIFICADOR + " FROM " + P06BTablas.P06B_P06B26T00_TABLA + " T1"
		+ " WHERE "
		+ "T1." + P06BTablas.P06B_P06B26_NUMEXP + "=?"
		+ " AND T1." + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + "=?"
		+ " MINUS "
		+ "SELECT "
		+ "S." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR + " FROM " + P06BTablas.P06B_P06B76T00_TABLA + " S"
		+ " WHERE "
		+ "S." + P06BTablas.P06B_P06B76_AC_NUMEXP + "=?"
		+ " AND S." + P06BTablas.P06B_P06B76_AC_CORRELATENCPETIC + "=?"
		+ " ) C"
		+ " WHERE "
		+ "A." + P06BTablas.P06B_P06B26_NUMEXP + "=?"
		+ " AND A." + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + "=?"
		+ " AND C." + P06BTablas.P06B_P06B26_IDENTIFICADOR + "=A." + P06BTablas.P06B_P06B26_IDENTIFICADOR
		+ " UNION"
		+ " SELECT "
		+ "1, "
		+ "NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, "
		+ "NULL, NULL, NULL, NULL, "
		+ "A." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR + ", "
		+ "A." + P06BTablas.P06B_P06B76_AC_TIPO + ", "
		+ "A." + P06BTablas.P06B_P06B76_AC_RAZSOC + ", "
		+ "A." + P06BTablas.P06B_P06B76_AC_NOMBRE + ", "
		+ "A." + P06BTablas.P06B_P06B76_AC_APE1 + ", "
		+ "A." + P06BTablas.P06B_P06B76_AC_APE2
		+ " FROM " + P06BTablas.P06B_P06B76T00_TABLA + " A,("
		+ "SELECT "
		+ "S." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR + " FROM " + P06BTablas.P06B_P06B76T00_TABLA + " S"
		+ " WHERE "
		+ "S." + P06BTablas.P06B_P06B76_AC_NUMEXP + "=?"
		+ " AND S." + P06BTablas.P06B_P06B76_AC_CORRELATENCPETIC + "=?"
		+ " MINUS "
		+ "SELECT "
		+ P06BTablas.P06B_P06B26_IDENTIFICADOR + " FROM " + P06BTablas.P06B_P06B26T00_TABLA + " T1"
		+ " WHERE "
		+ "T1." + P06BTablas.P06B_P06B26_NUMEXP + "=?"
		+ " AND T1." + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + "=?) C"
		+ " WHERE "
		+ "A." + P06BTablas.P06B_P06B76_AC_NUMEXP + "=?"
		+ " AND C." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR + "=A." + P06BTablas.P06B_P06B76_AC_IDENTIFICADOR
		+ " ORDER BY 1 DESC,2";

	private static String DELETEALL_BYEXPTIPO =
		"DELETE FROM "+ P06BTablas.P06B_P06B26T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B26_NUMEXP + " = ?" +
		" AND " + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + " = ?";

	private static String DELETEBYEXPIDTIPO =
		"DELETE FROM "+ P06BTablas.P06B_P06B26T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B26_NUMEXP + " = ?"
		+ " AND " + P06BTablas.P06B_P06B26_IDENTIFICADOR + "= ?"
		+ " AND " + P06BTablas.P06B_P06B26_TIPOATENCIONCLIENTE + " = ?";

}
