package com.ejie.p06b.dao;

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

import com.ejie.p06b.datatype.logic.P06BInformeDEC;
import com.ejie.p06b.datatype.model.P06B37T00Datatype;
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;

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

	public static P06B37T00DAO getInstance() {
	     if (instance == null) {
	          synchronized (P06B37T00DAO.class) {
	          		if (instance == null) {
	                     instance = new P06B37T00DAO();
	                }
	          }
	     }
	     return instance;
	}
	
	
	/**
    * 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 P06B37T00Datatype o null si no se encuentra nada.
	* @throws Exception
    */
	public P06B37T00Datatype select(String ejer, String numExp) throws Exception{
		P06BLog.send(2,this, "Inicio P06B37T00DAO-select");
		P06B37T00Datatype 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 P06B37T00Datatype();
	        	
	        	result.setEjercicio(lrst.getString(P06BTablas.P06B_P06B37_EJERCICIO));
	        	result.setNumExp(lrst.getString(P06BTablas.P06B_P06B37_NUMEXP));
				result.setVolTotCodEnt(lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_CODENT)==null?"":lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_CODENT));
				result.setVolTotExtAux(lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_EXTAUX)==null?"":lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_EXTAUX));
				result.setVolTotExtOtros(lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_EXTOTROS)==null?"":lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_EXTOTROS));
				result.setVolTotPrimCobr(lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR)==null?"":lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR));
				result.setNuevaCodEnt(lrst.getString(P06BTablas.P06B_P06B37_NUEVA_CODENT)==null?"":lrst.getString(P06BTablas.P06B_P06B37_NUEVA_CODENT));
				result.setNuevaExtAux(lrst.getString(P06BTablas.P06B_P06B37_NUEVA_EXTAUX)==null?"":lrst.getString(P06BTablas.P06B_P06B37_NUEVA_EXTAUX));
				result.setNuevaExtOtros(lrst.getString(P06BTablas.P06B_P06B37_NUEVA_EXTOTROS)==null?"":lrst.getString(P06BTablas.P06B_P06B37_NUEVA_EXTOTROS));
				result.setNuevaPrimCobr(lrst.getString(P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR)==null?"":lrst.getString(P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR));
				result.setVoltotPrimcobrInmed(lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR_INMEDIATA)==null?"":lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR_INMEDIATA));
				result.setVoltotPrimcobrNoInmed(lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR_NOINMEDIATA)==null?"":lrst.getString(P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR_NOINMEDIATA));
				result.setNuevaPrimcobrInmed(lrst.getString(P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR_INMEDIATA)==null?"":lrst.getString(P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR_INMEDIATA));
				result.setNuevaPrimcobrNoInmed(lrst.getString(P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR_NOINMEDIATA)==null?"":lrst.getString(P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR_NOINMEDIATA));
	        }
	        P06BLog.send(2,this, "Fin P06B37T00DAO-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 los datos para el informe resumen de la DEC asociados al ejercicio indicado
    * @param ejerDEC.
    *            ejercicio de la DEC
    * @param objInformeDEC.
    *            objeto donde actualizar los datos obtenidos en la select
	* @throws Exception
    */
	public void selectRedPropiaExtInformeDEC(String ejerDEC, P06BInformeDEC objInformeDEC, String tipoMed) throws Exception{
		P06BLog.send(2,this, "Inicio P06B37T00DAO-selectRedPropiaExtInformeDEC");
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_REDPROPIAEXT_INFORMEDEC;
        
        //Dependiendo del tipo hacer una select u otra. Corredores: F, J, RF, RJ. Vinculados: AF, AJ. Operadores banca: OV.
        if(P06BConstantes.P06B_CORR.equals(tipoMed)){
        	sql += " AND " + " (" + P06BTablas.P06B_P06B37_NUMEXP + " like '"+P06BConstantes.P06B_CORREDORES_TIPOPER_JURIDICA+"-%' or " + P06BTablas.P06B_P06B37_NUMEXP + " like '"+P06BConstantes.P06B_CORREDORES_TIPOPER_FISICA+"-%' or " + P06BTablas.P06B_P06B37_NUMEXP + " like '"+P06BConstantes.P06B_REASEGUROS_JURIDICA+"-%' or " + P06BTablas.P06B_P06B37_NUMEXP + " like '"+P06BConstantes.P06B_REASEGUROS_FISICA+"-%')";
    	}else if(P06BConstantes.P06B_VINC.equals(tipoMed)){
    		sql += " AND " + " (" + P06BTablas.P06B_P06B37_NUMEXP + " like '"+P06BConstantes.P06B_AGENTEVINCULADO_FISICA+"-%' or " + P06BTablas.P06B_P06B37_NUMEXP + " like '"+P06BConstantes.P06B_AGENTEVINCULADO_JURIDICA+"-%')";
    	}else if(P06BConstantes.P06B_OBS.equals(tipoMed)){
    		sql += " AND " + " (" + P06BTablas.P06B_P06B37_NUMEXP + " like '"+P06BConstantes.P06B_OPERADORBANCA_VINCULADO+"-%' or " + P06BTablas.P06B_P06B37_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.setAuxVolTotCorrM2(lrst.getDouble("REDEXT_AUX_VOLTOT"));
		        	objInformeDEC.setOtrosVolTotCorrM2(lrst.getDouble("REDEXT_OTROS_VOLTOT"));
		        	objInformeDEC.setCoberInmedVolTotCorrM2(lrst.getDouble("PRIMCOBRINMED_VOLTOT"));
		        	objInformeDEC.setNoCoberInmedVolTotCorrM2(lrst.getDouble("PRIMCOBRNOINMED_VOLTOT"));
		        	objInformeDEC.setAuxNuevaCorrM2(lrst.getDouble("REDEXT_AUX_NUEVAPROD"));
		        	objInformeDEC.setOtrosNuevaCorrM2(lrst.getDouble("REDEXT_OTROS_NUEVAPROD"));
		        	objInformeDEC.setCoberInmedNuevaCorrM2(lrst.getDouble("PRIMCOBRINMED_NUEVAPROD"));
		        	objInformeDEC.setNoCoberInmedNuevaCorrM2(lrst.getDouble("PRIMCOBRNOINMED_NUEVAPROD"));
	        	}else if(P06BConstantes.P06B_VINC.equals(tipoMed)){
	        		objInformeDEC.setAuxVolTotVincM2(lrst.getDouble("REDEXT_AUX_VOLTOT"));
		        	objInformeDEC.setCoberInmedVolTotVincM2(lrst.getDouble("PRIMCOBRINMED_VOLTOT"));
		        	objInformeDEC.setNoCoberInmedVolTotVincM2(lrst.getDouble("PRIMCOBRNOINMED_VOLTOT"));
		        	objInformeDEC.setAuxNuevaVincM2(lrst.getDouble("REDEXT_AUX_NUEVAPROD"));
		        	objInformeDEC.setCoberInmedNuevaVincM2(lrst.getDouble("PRIMCOBRINMED_NUEVAPROD"));
		        	objInformeDEC.setNoCoberInmedNuevaVincM2(lrst.getDouble("PRIMCOBRNOINMED_NUEVAPROD"));
	        	}else if(P06BConstantes.P06B_OBS.equals(tipoMed)){
	        		objInformeDEC.setAuxVolTotObsM2(lrst.getDouble("REDEXT_AUX_VOLTOT"));
		        	objInformeDEC.setCoberInmedVolTotObsM2(lrst.getDouble("PRIMCOBRINMED_VOLTOT"));
		        	objInformeDEC.setNoCoberInmedVolTotObsM2(lrst.getDouble("PRIMCOBRNOINMED_VOLTOT"));
		        	objInformeDEC.setAuxNuevaObsM2(lrst.getDouble("REDEXT_AUX_NUEVAPROD"));
		        	objInformeDEC.setCoberInmedNuevaObsM2(lrst.getDouble("PRIMCOBRINMED_NUEVAPROD"));
		        	objInformeDEC.setNoCoberInmedNuevaObsM2(lrst.getDouble("PRIMCOBRNOINMED_NUEVAPROD"));
	        	}
	        }
	        P06BLog.send(2,this, "Fin P06B37T00DAO-selectRedPropiaExtInformeDEC");
	    } 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_BY_PK =
		"SELECT " +	
		P06BTablas.P06B_P06B37_EJERCICIO + ", " + 
		P06BTablas.P06B_P06B37_NUMEXP + ", " + 
		P06BTablas.P06B_P06B37_VOLTOT_CODENT + ", " + 
		P06BTablas.P06B_P06B37_VOLTOT_EXTAUX + ", " + 
		P06BTablas.P06B_P06B37_VOLTOT_EXTOTROS + ", " + 
		P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR + ", " + 
		P06BTablas.P06B_P06B37_NUEVA_CODENT + ", " + 
		P06BTablas.P06B_P06B37_NUEVA_EXTAUX + ", " + 
		P06BTablas.P06B_P06B37_NUEVA_EXTOTROS + ", " + 
		P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR + ", " + 
		P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR_INMEDIATA + ", " + 
		P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR_NOINMEDIATA + ", " + 
		P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR_INMEDIATA + ", " + 
		P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR_NOINMEDIATA +
		
		" FROM "+ P06BTablas.P06B_P06B37T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B37_EJERCICIO + " = ? and " + P06BTablas.P06B_P06B37_NUMEXP + " = ?";
	
	
	private static String SELECT_REDPROPIAEXT_INFORMEDEC =
		"SELECT sum(nvl("+P06BTablas.P06B_P06B37_VOLTOT_EXTAUX+",0)) as REDEXT_AUX_VOLTOT," +
		" sum(nvl("+P06BTablas.P06B_P06B37_VOLTOT_EXTOTROS+",0)) as REDEXT_OTROS_VOLTOT," +
		" sum(nvl("+P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR_INMEDIATA+",0)) as PRIMCOBRINMED_VOLTOT," +
		" sum(nvl("+P06BTablas.P06B_P06B37_VOLTOT_PRIMCOBR_NOINMEDIATA+",0)) as PRIMCOBRNOINMED_VOLTOT," +
		" sum(nvl("+P06BTablas.P06B_P06B37_NUEVA_EXTAUX+",0)) as REDEXT_AUX_NUEVAPROD," +
		" sum(nvl("+P06BTablas.P06B_P06B37_NUEVA_EXTOTROS+",0)) as REDEXT_OTROS_NUEVAPROD," +
		" sum(nvl("+P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR_INMEDIATA+",0)) as PRIMCOBRINMED_NUEVAPROD, " +
		" sum(nvl("+P06BTablas.P06B_P06B37_NUEVA_PRIMCOBR_NOINMEDIATA+",0)) as PRIMCOBRNOINMED_NUEVAPROD" +
		" FROM "+ P06BTablas.P06B_P06B37T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B37_EJERCICIO + " = ?";
	
}
