package com.ejie.p06b.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Locale;

import com.ejie.p06b.datatype.logic.P06BInformeDEC;
import com.ejie.p06b.datatype.model.P06B32T00Datatype;
import com.ejie.p06b.general.P06BConstantes;
import com.ejie.p06b.general.P06BTablas;
import com.ejie.p06b.log.P06BLog;
import com.ejie.p06b.util.P06BFecha;
import com.ejie.p06b.util.bd.P06BServidorConexionesBD;

/**
 * Fecha de creación: (05/07/2010)
 */
public class P06B32T00DAO {
	
	static private P06B32T00DAO instance = null;

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

	
	public String getStrListadoDEC(String where){
		return SELECT_LISTADO + " " + where + " ORDER BY " + P06BTablas.P06B_P06B32_NUMEXP;
	}
	
	
	/**
    * Obtiene un registro de la BD a partir de la clave primaria
    * @param numExp.
    *            parte de la clave primaria
    * @param ejer.
    *            parte de la clave primaria
    * @return un objeto P06B32T00Datatype o null si no se encuentra nada.
	* @throws Exception
    */
	public P06B32T00Datatype select(String numExp, String ejer) throws Exception{
		P06BLog.send(2,this, "Inicio P06B32T00DAO-select");
		P06B32T00Datatype 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,ejer.toUpperCase());
        	lstm.setString(2,numExp.toUpperCase());
        	lrst = lstm.executeQuery();
	        if (lrst.next()) {
	        	result = new P06B32T00Datatype();
	        	result.setEjercicio(lrst.getString(P06BTablas.P06B_P06B32_EJERCICIO));
	        	result.setNumExp(lrst.getString(P06BTablas.P06B_P06B32_NUMEXP));
				result.setClase(lrst.getString(P06BTablas.P06B_P06B32_CLASE)==null?"":lrst.getString(P06BTablas.P06B_P06B32_CLASE));
				result.setIdentificador(lrst.getString(P06BTablas.P06B_P06B32_IDENTIFICADOR)==null?"":lrst.getString(P06BTablas.P06B_P06B32_IDENTIFICADOR));
				result.setRazSoc(lrst.getString(P06BTablas.P06B_P06B32_RAZSOC)==null?"":lrst.getString(P06BTablas.P06B_P06B32_RAZSOC));
				result.setNombre(lrst.getString(P06BTablas.P06B_P06B32_NOMBRE)==null?"":lrst.getString(P06BTablas.P06B_P06B32_NOMBRE));
				result.setApe1(lrst.getString(P06BTablas.P06B_P06B32_APE1)==null?"":lrst.getString(P06BTablas.P06B_P06B32_APE1));
				result.setApe2(lrst.getString(P06BTablas.P06B_P06B32_APE2)==null?"":lrst.getString(P06BTablas.P06B_P06B32_APE2));
				result.setDomici(lrst.getString(P06BTablas.P06B_P06B32_DOMICI)==null?"":lrst.getString(P06BTablas.P06B_P06B32_DOMICI));
				result.setCodMun(lrst.getString(P06BTablas.P06B_P06B32_CODMUN)==null?"":lrst.getString(P06BTablas.P06B_P06B32_CODMUN));
				result.setLocali(lrst.getString(P06BTablas.P06B_P06B32_LOCALI)==null?"":lrst.getString(P06BTablas.P06B_P06B32_LOCALI));
				result.setCodPro(lrst.getString(P06BTablas.P06B_P06B32_CODPRO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_CODPRO));
				result.setCodPst(lrst.getString(P06BTablas.P06B_P06B32_CODPST)==null?"":lrst.getString(P06BTablas.P06B_P06B32_CODPST));
				result.setTelefono(lrst.getString(P06BTablas.P06B_P06B32_TELEFONO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_TELEFONO));
				result.setFax(lrst.getString(P06BTablas.P06B_P06B32_FAX)==null?"":lrst.getString(P06BTablas.P06B_P06B32_FAX));
				result.setMail(lrst.getString(P06BTablas.P06B_P06B32_MAIL)==null?"":lrst.getString(P06BTablas.P06B_P06B32_MAIL));
				result.setCapSoc(lrst.getString(P06BTablas.P06B_P06B32_CAPSOC)==null?"":lrst.getString(P06BTablas.P06B_P06B32_CAPSOC));
				result.setEntidadPoliza(lrst.getString(P06BTablas.P06B_P06B32_ENTIDADPOLIZA)==null?"":lrst.getString(P06BTablas.P06B_P06B32_ENTIDADPOLIZA));
				result.setGarantiaPoliza(lrst.getString(P06BTablas.P06B_P06B32_GARANTIAPOLIZA)==null?"":lrst.getString(P06BTablas.P06B_P06B32_GARANTIAPOLIZA));
				result.setEntidadAval(lrst.getString(P06BTablas.P06B_P06B32_ENTIDADAVAL)==null?"":lrst.getString(P06BTablas.P06B_P06B32_ENTIDADAVAL));
				result.setImporteAval(lrst.getString(P06BTablas.P06B_P06B32_IMPORTEAVAL)==null?"":lrst.getString(P06BTablas.P06B_P06B32_IMPORTEAVAL));
				result.setNomRepres(lrst.getString(P06BTablas.P06B_P06B32_NOMREPRES)==null?"":lrst.getString(P06BTablas.P06B_P06B32_NOMREPRES));
				result.setEmpRepres(lrst.getString(P06BTablas.P06B_P06B32_EMPREPRES)==null?"":lrst.getString(P06BTablas.P06B_P06B32_EMPREPRES));
				result.setCalidadRepres(lrst.getString(P06BTablas.P06B_P06B32_CALIDADREPRES)==null?"":lrst.getString(P06BTablas.P06B_P06B32_CALIDADREPRES));
				result.setFecDec(lrst.getString(P06BTablas.P06B_P06B32_FECDEC)==null?"":P06BFecha.getFechaFormateada(lrst.getDate(P06BTablas.P06B_P06B32_FECDEC),new Locale(P06BConstantes.P06B_IDIOMA_ES)));
				result.setFecEnvio(lrst.getString(P06BTablas.P06B_P06B32_FEC_ENVIO)==null?"":P06BFecha.getFechaFormateada(lrst.getDate(P06BTablas.P06B_P06B32_FEC_ENVIO),new Locale(P06BConstantes.P06B_IDIOMA_ES)));
				result.setCifNifEnvio(lrst.getString(P06BTablas.P06B_P06B32_CIFNIF_ENVIO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_CIFNIF_ENVIO));
				result.setNomFichEnvio(lrst.getString(P06BTablas.P06B_P06B32_NOMFICH_ENVIO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_NOMFICH_ENVIO));
				result.setMovil(lrst.getString(P06BTablas.P06B_P06B32_MOVIL)==null?"":lrst.getString(P06BTablas.P06B_P06B32_MOVIL));
				result.setDominioInternet(lrst.getString(P06BTablas.P06B_P06B32_DOMINIO_INTERNET)==null?"":lrst.getString(P06BTablas.P06B_P06B32_DOMINIO_INTERNET));
				result.setFhEfectoDesde(lrst.getString(P06BTablas.P06B_P06B32_FH_EFECTO_DESDE)==null?"":P06BFecha.getFechaFormateada(lrst.getDate(P06BTablas.P06B_P06B32_FH_EFECTO_DESDE),new Locale(P06BConstantes.P06B_IDIOMA_ES)));
				result.setFhEfectoHasta(lrst.getString(P06BTablas.P06B_P06B32_FH_EFECTO_HASTA)==null?"":P06BFecha.getFechaFormateada(lrst.getDate(P06BTablas.P06B_P06B32_FH_EFECTO_HASTA),new Locale(P06BConstantes.P06B_IDIOMA_ES)));
				result.setTipoCapacFinan(lrst.getString(P06BTablas.P06B_P06B32_TIPO_CAPAC_FINAN)==null?"":lrst.getString(P06BTablas.P06B_P06B32_TIPO_CAPAC_FINAN));
				result.setEnGrupo(lrst.getString(P06BTablas.P06B_P06B32_EN_GRUPO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_EN_GRUPO));
				result.setDenomGrupo(lrst.getString(P06BTablas.P06B_P06B32_DENOM_GRUPO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_DENOM_GRUPO));
				result.setDomiciGrupo(lrst.getString(P06BTablas.P06B_P06B32_DOMICI_GRUPO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_DOMICI_GRUPO));
				result.setPoblGrupo(lrst.getString(P06BTablas.P06B_P06B32_POBL_GRUPO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_POBL_GRUPO));
				result.setProvGrupo(lrst.getString(P06BTablas.P06B_P06B32_PROV_GRUPO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_PROV_GRUPO));
				result.setCodpostalGrupo(lrst.getString(P06BTablas.P06B_P06B32_CODPOSTAL_GRUPO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_CODPOSTAL_GRUPO));
				result.setPaisGrupo(lrst.getString(P06BTablas.P06B_P06B32_PAIS_GRUPO)==null?"":lrst.getString(P06BTablas.P06B_P06B32_PAIS_GRUPO));
				result.setTipoResponCivil(lrst.getString(P06BTablas.P06B_P06B32_TIPO_RESPON_CIVIL)==null?"":lrst.getString(P06BTablas.P06B_P06B32_TIPO_RESPON_CIVIL));
				result.setActControlada(lrst.getString(P06BTablas.P06B_P06B32_ACT_CONTROLADA)==null?"":lrst.getString(P06BTablas.P06B_P06B32_ACT_CONTROLADA));
	        }
	        P06BLog.send(2,this, "Fin P06B32T00DAO-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) {}
	        //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 result;
	}
	
	
	/**
    * Obtiene el número de mediadores que han enviado la DEC
    * @param ejerDEC.
    *            ejercicio de la DEC
    * @param objInformeDEC.
    *            objeto donde actualizar los datos obtenidos en la select
	* @throws Exception
    */
	public void selectNumMediadEnviadaDEC(String ejerDEC, P06BInformeDEC objInformeDEC, String tipoMed) throws Exception{
		P06BLog.send(2,this, "Inicio P06B32T00DAO-selectNumMediadEnviadaDEC");
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_NUM_MEDIAD_ENVIADA_DEC;
        
        //Dependiendo del tipo hacer una select u otra. Corredores: F, J, RF, RJ. Vinculados: AF, AJ. Operadores banca: OV.
        //si tipoMed es null, se calculará el total de mediadores que han presentado la DEC
        if(P06BConstantes.P06B_CORR.equals(tipoMed)){
        	sql += " AND " + " (" + P06BTablas.P06B_P06B32_NUMEXP + " like '"+P06BConstantes.P06B_CORREDORES_TIPOPER_JURIDICA+"-%' or " + P06BTablas.P06B_P06B32_NUMEXP + " like '"+P06BConstantes.P06B_CORREDORES_TIPOPER_FISICA+"-%' or " + P06BTablas.P06B_P06B32_NUMEXP + " like '"+P06BConstantes.P06B_REASEGUROS_JURIDICA+"-%' or " + P06BTablas.P06B_P06B32_NUMEXP + " like '"+P06BConstantes.P06B_REASEGUROS_FISICA+"-%')";
    	}else if(P06BConstantes.P06B_VINC.equals(tipoMed)){
    		sql += " AND " + " (" + P06BTablas.P06B_P06B32_NUMEXP + " like '"+P06BConstantes.P06B_AGENTEVINCULADO_FISICA+"-%' or " + P06BTablas.P06B_P06B32_NUMEXP + " like '"+P06BConstantes.P06B_AGENTEVINCULADO_JURIDICA+"-%')";
    	}else if(P06BConstantes.P06B_OBS.equals(tipoMed)){
    		sql += " AND " + " (" + P06BTablas.P06B_P06B32_NUMEXP + " like '"+P06BConstantes.P06B_OPERADORBANCA_VINCULADO+"-%' or " + P06BTablas.P06B_P06B32_NUMEXP + " like '"+P06BConstantes.P06B_OPERADORBANCA_EXCLUSIVO+"-%')";
    	}
        
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setString(1,ejerDEC.toUpperCase());
        	lrst = lstm.executeQuery();
	        if (lrst.next()) {
	        	if(P06BConstantes.P06B_CORR.equals(tipoMed)){
	        		objInformeDEC.setNumEmpCorrM1(lrst.getInt("NUM_MEDIAD_ENVIADA_DEC"));
	        	}else if(P06BConstantes.P06B_VINC.equals(tipoMed)){
	        		objInformeDEC.setNumEmpVincM1(lrst.getInt("NUM_MEDIAD_ENVIADA_DEC"));
	        	}else if(P06BConstantes.P06B_OBS.equals(tipoMed)){
	        		objInformeDEC.setNumEmpObsM1(lrst.getInt("NUM_MEDIAD_ENVIADA_DEC"));
	        	}else{//El nº total
	        		objInformeDEC.setNumMediadEnviadaDEC(lrst.getInt("NUM_MEDIAD_ENVIADA_DEC"));
	        	}			
	        }
	        P06BLog.send(2,this, "Fin P06B32T00DAO-selectNumMediadEnviadaDEC");
	    } 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) {}
	    }
	}
		

	
	private static String SELECT_LISTADO =
		"SELECT  " + 
		P06BTablas.P06B_P06B32_EJERCICIO + ", " +
		P06BTablas.P06B_P06B32_NUMEXP + ", " +
		P06BTablas.P06B_P06B32_CLASE + ", " + P06BTablas.P06B_P06B32_IDENTIFICADOR + ", " + P06BTablas.P06B_P06B32_RAZSOC + ", " +
		P06BTablas.P06B_P06B32_NOMBRE + ", " + P06BTablas.P06B_P06B32_APE1 + ", " + P06BTablas.P06B_P06B32_APE2 + " " +
		"FROM "+ P06BTablas.P06B_P06B32T00_TABLA;
	
	private static String SELECT_BY_PK =
		"SELECT " +	
		P06BTablas.P06B_P06B32_EJERCICIO + ", " + P06BTablas.P06B_P06B32_NUMEXP + ", " + P06BTablas.P06B_P06B32_CLASE + ", " + 
		P06BTablas.P06B_P06B32_IDENTIFICADOR + ", " + P06BTablas.P06B_P06B32_RAZSOC + ", " + P06BTablas.P06B_P06B32_NOMBRE + ", " + 
		P06BTablas.P06B_P06B32_APE1 + ", " + P06BTablas.P06B_P06B32_APE2 + ", " + P06BTablas.P06B_P06B32_DOMICI + ", " + 
		P06BTablas.P06B_P06B32_CODMUN + ", " + P06BTablas.P06B_P06B32_LOCALI + ", " + P06BTablas.P06B_P06B32_CODPRO + ", " + 
		P06BTablas.P06B_P06B32_CODPST + ", " + P06BTablas.P06B_P06B32_TELEFONO + ", " + P06BTablas.P06B_P06B32_FAX + ", " + 
		P06BTablas.P06B_P06B32_MAIL + ", " + P06BTablas.P06B_P06B32_CAPSOC + ", " + P06BTablas.P06B_P06B32_ENTIDADPOLIZA + ", " + 
		P06BTablas.P06B_P06B32_GARANTIAPOLIZA + ", " + P06BTablas.P06B_P06B32_ENTIDADAVAL + ", " + P06BTablas.P06B_P06B32_IMPORTEAVAL + ", " + 
		P06BTablas.P06B_P06B32_NOMREPRES + ", " + P06BTablas.P06B_P06B32_EMPREPRES + ", " + P06BTablas.P06B_P06B32_CALIDADREPRES + ", " + 
		P06BTablas.P06B_P06B32_FECDEC + ", " + P06BTablas.P06B_P06B32_FEC_ENVIO + ", " + P06BTablas.P06B_P06B32_CIFNIF_ENVIO + ", " + 
		P06BTablas.P06B_P06B32_NOMFICH_ENVIO + ", " + 
		P06BTablas.P06B_P06B32_MOVIL + ", " + 
		P06BTablas.P06B_P06B32_DOMINIO_INTERNET + ", " + 
		P06BTablas.P06B_P06B32_FH_EFECTO_DESDE + ", " + 
		P06BTablas.P06B_P06B32_FH_EFECTO_HASTA + ", " + 
		P06BTablas.P06B_P06B32_TIPO_CAPAC_FINAN + ", " + 
		P06BTablas.P06B_P06B32_EN_GRUPO + ", " + 
		P06BTablas.P06B_P06B32_DENOM_GRUPO + ", " + 
		P06BTablas.P06B_P06B32_DOMICI_GRUPO + ", " + 
		P06BTablas.P06B_P06B32_POBL_GRUPO + ", " + 
		P06BTablas.P06B_P06B32_PROV_GRUPO + ", " + 
		P06BTablas.P06B_P06B32_CODPOSTAL_GRUPO + ", " + 
		P06BTablas.P06B_P06B32_PAIS_GRUPO + ", " + 
		P06BTablas.P06B_P06B32_TIPO_RESPON_CIVIL + ", " + 
		P06BTablas.P06B_P06B32_ACT_CONTROLADA +

		" FROM "+ P06BTablas.P06B_P06B32T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B32_EJERCICIO + " = ? and " + P06BTablas.P06B_P06B32_NUMEXP + " = ?";
	
	private static String SELECT_NUM_MEDIAD_ENVIADA_DEC =
		"SELECT count("+ P06BTablas.P06B_P06B32_NUMEXP +") as NUM_MEDIAD_ENVIADA_DEC" +	
		" FROM "+ P06BTablas.P06B_P06B32T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B32_EJERCICIO + " = ?";
	
	
}
