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.P06B50T00Datatype;
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: (26/01/2012)
 */
public class P06B50T00DAO {
	
	static private P06B50T00DAO instance = null;

	public static P06B50T00DAO getInstance() {
	     if (instance == null) {
	          synchronized (P06B50T00DAO.class) {
	          		if (instance == null) {
	                     instance = new P06B50T00DAO();
	                }
	          }
	     }
	     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 P06B50T00Datatype o null si no se encuentra nada.
	* @throws Exception
    */
	public P06B50T00Datatype select(String ejer, String numExp) throws Exception{
		P06BLog.send(2,this, "Inicio P06B50T00DAO-select");
		P06B50T00Datatype 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 P06B50T00Datatype();
	        	
	        	result.setEjercicio(lrst.getString(P06BTablas.P06B_P06B50_EJERCICIO));
	        	result.setNumExp(lrst.getString(P06BTablas.P06B_P06B50_NUMEXP));
			    result.setImpNetoAfecComis(lrst.getString(P06BTablas.P06B_P06B50_IMPNETO_AFEC_COMIS)==null?"":lrst.getString(P06BTablas.P06B_P06B50_IMPNETO_AFEC_COMIS));
			    result.setImpNetoAfecHonor(lrst.getString(P06BTablas.P06B_P06B50_IMPNETO_AFEC_HONOR)==null?"":lrst.getString(P06BTablas.P06B_P06B50_IMPNETO_AFEC_HONOR));
			    result.setImpNetoResto(lrst.getString(P06BTablas.P06B_P06B50_IMPNETO_RESTO)==null?"":lrst.getString(P06BTablas.P06B_P06B50_IMPNETO_RESTO));
			    result.setVariacProduc(lrst.getString(P06BTablas.P06B_P06B50_VARIACPRODUC)==null?"":lrst.getString(P06BTablas.P06B_P06B50_VARIACPRODUC));
			    result.setTrabAct(lrst.getString(P06BTablas.P06B_P06B50_TRABACT)==null?"":lrst.getString(P06BTablas.P06B_P06B50_TRABACT));
			    result.setAprov(lrst.getString(P06BTablas.P06B_P06B50_APROV)==null?"":lrst.getString(P06BTablas.P06B_P06B50_APROV));
			    result.setIngrOtrosAfec(lrst.getString(P06BTablas.P06B_P06B50_INGR_OTROS_AFEC)==null?"":lrst.getString(P06BTablas.P06B_P06B50_INGR_OTROS_AFEC));
			    result.setIngrOtrosResto(lrst.getString(P06BTablas.P06B_P06B50_INGR_OTROS_RESTO)==null?"":lrst.getString(P06BTablas.P06B_P06B50_INGR_OTROS_RESTO));
			    result.setGastPerAfec(lrst.getString(P06BTablas.P06B_P06B50_GAST_PER_AFEC)==null?"":lrst.getString(P06BTablas.P06B_P06B50_GAST_PER_AFEC));
			    result.setGastPerResto(lrst.getString(P06BTablas.P06B_P06B50_GAST_PER_RESTO)==null?"":lrst.getString(P06BTablas.P06B_P06B50_GAST_PER_RESTO));
			    result.setGastOtrosAfec(lrst.getString(P06BTablas.P06B_P06B50_GAST_OTROS_AFEC)==null?"":lrst.getString(P06BTablas.P06B_P06B50_GAST_OTROS_AFEC));
			    result.setGastOtrosResto(lrst.getString(P06BTablas.P06B_P06B50_GAST_OTROS_RESTO)==null?"":lrst.getString(P06BTablas.P06B_P06B50_GAST_OTROS_RESTO));
			    result.setAmort(lrst.getString(P06BTablas.P06B_P06B50_AMORT)==null?"":lrst.getString(P06BTablas.P06B_P06B50_AMORT));
			    result.setImputSubv(lrst.getString(P06BTablas.P06B_P06B50_IMPUTSUBV)==null?"":lrst.getString(P06BTablas.P06B_P06B50_IMPUTSUBV));
			    result.setExcesoProv(lrst.getString(P06BTablas.P06B_P06B50_EXCESOPROV)==null?"":lrst.getString(P06BTablas.P06B_P06B50_EXCESOPROV));
			    result.setEnajInmov(lrst.getString(P06BTablas.P06B_P06B50_ENAJINMOV)==null?"":lrst.getString(P06BTablas.P06B_P06B50_ENAJINMOV));
			    result.setIngrFinanc(lrst.getString(P06BTablas.P06B_P06B50_INGR_FINANC)==null?"":lrst.getString(P06BTablas.P06B_P06B50_INGR_FINANC));
			    result.setGastFinanc(lrst.getString(P06BTablas.P06B_P06B50_GAST_FINANC)==null?"":lrst.getString(P06BTablas.P06B_P06B50_GAST_FINANC));
			    result.setVariacInstrFinanc(lrst.getString(P06BTablas.P06B_P06B50_VARIACINSTRFINANC)==null?"":lrst.getString(P06BTablas.P06B_P06B50_VARIACINSTRFINANC));
			    result.setDiferCambio(lrst.getString(P06BTablas.P06B_P06B50_DIFERCAMBIO)==null?"":lrst.getString(P06BTablas.P06B_P06B50_DIFERCAMBIO));
			    result.setEnajInstrFinanc(lrst.getString(P06BTablas.P06B_P06B50_ENAJINSTRFINANC)==null?"":lrst.getString(P06BTablas.P06B_P06B50_ENAJINSTRFINANC));
			    result.setImpuesBenef(lrst.getString(P06BTablas.P06B_P06B50_IMPUESBENEF)==null?"":lrst.getString(P06BTablas.P06B_P06B50_IMPUESBENEF));

	        }
	        P06BLog.send(2,this, "Fin P06B50T00DAO-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 selectDatContabMod32InformeDEC(String ejerDEC, P06BInformeDEC objInformeDEC) throws Exception{
		P06BLog.send(2,this, "Inicio P06B50T00DAO-selectDatContabMod32InformeDEC");
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_DATCONTABMOD32_INFORMEDEC;
        
        sql += " AND " + " (" + P06BTablas.P06B_P06B50_NUMEXP + " like '"+P06BConstantes.P06B_CORREDORES_TIPOPER_JURIDICA+"-%' or " + P06BTablas.P06B_P06B50_NUMEXP + " like '"+P06BConstantes.P06B_CORREDORES_TIPOPER_FISICA+"-%' or " + P06BTablas.P06B_P06B50_NUMEXP + " like '"+P06BConstantes.P06B_REASEGUROS_JURIDICA+"-%' or " + P06BTablas.P06B_P06B50_NUMEXP + " like '"+P06BConstantes.P06B_REASEGUROS_FISICA+"-%')";
    	
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setString(1,ejerDEC.toUpperCase());
        	lrst = lstm.executeQuery();
        	
	        if (lrst.next()) {
        		objInformeDEC.setIngAfecMod32CorrM3(lrst.getDouble("ING_AFEC_MOD32"));
        		objInformeDEC.setOtrosIngMod32CorrM3(lrst.getDouble("OTROS_ING_MOD32"));
        		objInformeDEC.setIngFinanMod32CorrM3(lrst.getDouble("ING_FINAN_MOD32"));
        		objInformeDEC.setIngRestoMod32CorrM3(lrst.getDouble("ING_RESTO_MOD32"));
        		objInformeDEC.setGastPerMod32CorrM3(lrst.getDouble("GAST_PER_MOD32"));
        		objInformeDEC.setOtrosGastMod32CorrM3(lrst.getDouble("OTROS_GAST_MOD32"));
        		objInformeDEC.setGastFinanMod32CorrM3(lrst.getDouble("GAST_FINAN_MOD32"));
        		objInformeDEC.setGastRestoMod32CorrM3(lrst.getDouble("GAST_RESTO_MOD32"));
	        }
	        P06BLog.send(2,this, "Fin P06B50T00DAO-selectDatContabMod32InformeDEC");
	    } 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_P06B50_EJERCICIO + ", " + 
		P06BTablas.P06B_P06B50_NUMEXP + ", " + 
		P06BTablas.P06B_P06B50_IMPNETO_AFEC_COMIS + ", " + 
		P06BTablas.P06B_P06B50_IMPNETO_AFEC_HONOR + ", " + 
		P06BTablas.P06B_P06B50_IMPNETO_RESTO + ", " + 
		P06BTablas.P06B_P06B50_VARIACPRODUC + ", " + 
		P06BTablas.P06B_P06B50_TRABACT + ", " + 
		P06BTablas.P06B_P06B50_APROV + ", " +
		P06BTablas.P06B_P06B50_INGR_OTROS_AFEC + ", " + 
		P06BTablas.P06B_P06B50_INGR_OTROS_RESTO + ", " + 
		P06BTablas.P06B_P06B50_GAST_PER_AFEC + ", " + 
		P06BTablas.P06B_P06B50_GAST_PER_RESTO + ", " + 
		P06BTablas.P06B_P06B50_GAST_OTROS_AFEC + ", " + 
		P06BTablas.P06B_P06B50_GAST_OTROS_RESTO + ", " + 
		P06BTablas.P06B_P06B50_AMORT + ", " + 
		P06BTablas.P06B_P06B50_IMPUTSUBV + ", " + 
		P06BTablas.P06B_P06B50_EXCESOPROV + ", " + 
		P06BTablas.P06B_P06B50_ENAJINMOV + ", " + 
		P06BTablas.P06B_P06B50_INGR_FINANC + ", " + 
		P06BTablas.P06B_P06B50_GAST_FINANC + ", " + 
		P06BTablas.P06B_P06B50_VARIACINSTRFINANC + ", " + 
		P06BTablas.P06B_P06B50_DIFERCAMBIO + ", " + 
		P06BTablas.P06B_P06B50_ENAJINSTRFINANC + ", " + 
		P06BTablas.P06B_P06B50_IMPUESBENEF +
		
		" FROM "+ P06BTablas.P06B_P06B50T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B50_EJERCICIO + " = ? and " + P06BTablas.P06B_P06B50_NUMEXP + " = ?";
	
	private static String SELECT_DATCONTABMOD32_INFORMEDEC =
		"SELECT (sum(nvl("+P06BTablas.P06B_P06B50_IMPNETO_AFEC_COMIS+",0)) + sum(nvl("+P06BTablas.P06B_P06B50_IMPNETO_AFEC_HONOR+",0))) as ING_AFEC_MOD32,"+
		" sum(nvl("+P06BTablas.P06B_P06B50_INGR_OTROS_AFEC+",0)) as OTROS_ING_MOD32,"+
		" sum(nvl("+P06BTablas.P06B_P06B50_INGR_FINANC+",0)) as ING_FINAN_MOD32,"+
		" (sum(nvl("+P06BTablas.P06B_P06B50_IMPNETO_RESTO+",0)) + sum(nvl("+P06BTablas.P06B_P06B50_INGR_OTROS_RESTO+",0))) as ING_RESTO_MOD32,"+
		" sum(nvl("+P06BTablas.P06B_P06B50_GAST_PER_AFEC+",0)) as GAST_PER_MOD32,"+
		" sum(nvl("+P06BTablas.P06B_P06B50_GAST_OTROS_AFEC+",0)) as OTROS_GAST_MOD32,"+
		" sum(nvl("+P06BTablas.P06B_P06B50_GAST_FINANC+",0)) as GAST_FINAN_MOD32,"+
		" (sum(nvl("+P06BTablas.P06B_P06B50_GAST_PER_RESTO+",0)) + sum(nvl("+P06BTablas.P06B_P06B50_GAST_OTROS_RESTO+",0))) as GAST_RESTO_MOD32"+
		" FROM "+ P06BTablas.P06B_P06B50T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B50_EJERCICIO + " = ?";

}
