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.logic.P06BInformeOtroDEC;
import com.ejie.p06b.datatype.model.P06B53T00Datatype;
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: (27/01/2012)
 */
public class P06B53T00DAO {
	
	static private P06B53T00DAO instance = null;

	public static P06B53T00DAO getInstance() {
	     if (instance == null) {
	          synchronized (P06B53T00DAO.class) {
	          		if (instance == null) {
	                     instance = new P06B53T00DAO();
	                }
	          }
	     }
	     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 P06B53T00Datatype o null si no se encuentra nada.
	* @throws Exception
    */
	public P06B53T00Datatype select(String ejer, String numExp) throws Exception{
		P06BLog.send(2,this, "Inicio P06B53T00DAO-select");
		P06B53T00Datatype 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 P06B53T00Datatype();
	        	
	        	result.setEjercicio(lrst.getString(P06BTablas.P06B_P06B53_EJERCICIO));
	        	result.setNumExp(lrst.getString(P06BTablas.P06B_P06B53_NUMEXP));
			    result.setIngrComis(lrst.getString(P06BTablas.P06B_P06B53_INGR_COMIS)==null?"":lrst.getString(P06BTablas.P06B_P06B53_INGR_COMIS));
			    result.setIngrFinanc(lrst.getString(P06BTablas.P06B_P06B53_INGR_FINANC)==null?"":lrst.getString(P06BTablas.P06B_P06B53_INGR_FINANC));
			    result.setIngrOtros(lrst.getString(P06BTablas.P06B_P06B53_INGR_OTROS)==null?"":lrst.getString(P06BTablas.P06B_P06B53_INGR_OTROS));
			    result.setGastCostesPer(lrst.getString(P06BTablas.P06B_P06B53_GAST_COSTESPER)==null?"":lrst.getString(P06BTablas.P06B_P06B53_GAST_COSTESPER));
			    result.setGastComisAuxExt(lrst.getString(P06BTablas.P06B_P06B53_GAST_COMISAUXEXT)==null?"":lrst.getString(P06BTablas.P06B_P06B53_GAST_COMISAUXEXT));
			    result.setGastFormacion(lrst.getString(P06BTablas.P06B_P06B53_GAST_FORMACION)==null?"":lrst.getString(P06BTablas.P06B_P06B53_GAST_FORMACION));
			    result.setGastFinanc(lrst.getString(P06BTablas.P06B_P06B53_GAST_FINANC)==null?"":lrst.getString(P06BTablas.P06B_P06B53_GAST_FINANC));
			    result.setGastOtros(lrst.getString(P06BTablas.P06B_P06B53_GAST_OTROS)==null?"":lrst.getString(P06BTablas.P06B_P06B53_GAST_OTROS));
			    result.setCtaIngrTotal(lrst.getString(P06BTablas.P06B_P06B53_CTA_INGRTOTAL)==null?"":lrst.getString(P06BTablas.P06B_P06B53_CTA_INGRTOTAL));
			    result.setCtaGastTotal(lrst.getString(P06BTablas.P06B_P06B53_CTA_GASTTOTAL)==null?"":lrst.getString(P06BTablas.P06B_P06B53_CTA_GASTTOTAL));
	        }
	        P06BLog.send(2,this, "Fin P06B53T00DAO-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 selectDatContabVincYObsInformeDEC(String ejerDEC, P06BInformeDEC objInformeDEC, String tipoMed) throws Exception{
		P06BLog.send(2,this, "Inicio P06B53T00DAO-selectDatContabVincYObsInformeDEC");
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_DATCONTAB_VINC_Y_OBS_INFORMEDEC;
        
        //Dependiendo del tipo hacer una select u otra. Vinculados: AF, AJ. Operadores banca: OV.
        //Los tipo Corredores F, J, RF, RJ: NO se alamacenan en esta tabla. 
        if(P06BConstantes.P06B_VINC.equals(tipoMed)){
    		sql += " AND " + " (" + P06BTablas.P06B_P06B53_NUMEXP + " like '"+P06BConstantes.P06B_AGENTEVINCULADO_FISICA+"-%' or " + P06BTablas.P06B_P06B53_NUMEXP + " like '"+P06BConstantes.P06B_AGENTEVINCULADO_JURIDICA+"-%')";
    	}else if(P06BConstantes.P06B_OBS.equals(tipoMed)){
    		sql += " AND " + " (" + P06BTablas.P06B_P06B53_NUMEXP + " like '"+P06BConstantes.P06B_OPERADORBANCA_VINCULADO+"-%' or " + P06BTablas.P06B_P06B53_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_VINC.equals(tipoMed)){
	        		objInformeDEC.setIngComisVincM3(lrst.getDouble("INGR_COMIS"));
		        	objInformeDEC.setIngFinanVincM3(lrst.getDouble("INGR_FINANC"));
		        	objInformeDEC.setOtrosIngVincM3(lrst.getDouble("INGR_OTROS"));
		        	objInformeDEC.setCostePerVincM3(lrst.getDouble("GAST_COSTESPER"));
		        	objInformeDEC.setGastComisVincM3(lrst.getDouble("GAST_COMISAUXEXT"));
		        	objInformeDEC.setFormContVincM3(lrst.getDouble("GAST_FORMACION"));
		        	objInformeDEC.setGastFinanVincM3(lrst.getDouble("GAST_FINANC"));
		        	objInformeDEC.setOtrosGastVincM3(lrst.getDouble("GAST_OTROS"));
		        	objInformeDEC.setCtaIngTotalVincM3(lrst.getDouble("CTA_INGRTOTAL"));
		        	objInformeDEC.setCtaGastTotalVincM3(lrst.getDouble("CTA_GASTTOTAL"));
	        	}else if(P06BConstantes.P06B_OBS.equals(tipoMed)){
	        		objInformeDEC.setIngComisObsM3(lrst.getDouble("INGR_COMIS"));
		        	objInformeDEC.setIngFinanObsM3(lrst.getDouble("INGR_FINANC"));
		        	objInformeDEC.setOtrosIngObsM3(lrst.getDouble("INGR_OTROS"));
		        	objInformeDEC.setCostePerObsM3(lrst.getDouble("GAST_COSTESPER"));
		        	objInformeDEC.setGastComisObsM3(lrst.getDouble("GAST_COMISAUXEXT"));
		        	objInformeDEC.setFormContObsM3(lrst.getDouble("GAST_FORMACION"));
		        	objInformeDEC.setGastFinanObsM3(lrst.getDouble("GAST_FINANC"));
		        	objInformeDEC.setOtrosGastObsM3(lrst.getDouble("GAST_OTROS"));
		        	objInformeDEC.setCtaIngTotalObsM3(lrst.getDouble("CTA_INGRTOTAL"));
		        	objInformeDEC.setCtaGastTotalObsM3(lrst.getDouble("CTA_GASTTOTAL"));
	        	}
	        }
	        P06BLog.send(2,this, "Fin P06B53T00DAO-selectDatContabVincYObsInformeDEC");
	    } 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) {}
	    }
	}
		
	
	/**
	    * Obtiene los datos para el informe resumen de la DEC asociados al ejercicio indicado
	    * @param ejerDEC.
	    *            ejercicio de la DEC
	    * @param objInformeOtroDEC.
	    *            objeto donde actualizar los datos obtenidos en la select
		* @throws Exception
	    */
		public void selectDatosInformeOtroDEC(String ejerDEC, P06BInformeOtroDEC objInformeOtroDEC) throws Exception{
			P06BLog.send(2,this, "Inicio P06B53T00DAO-selectDatosInformeOtroDEC");
			PreparedStatement lstm  = null;
	        ResultSet lrst  = null;
	        String sql = SELECT_DATOS_INFORMEOTRODEC;
	        String sTipoMediador;
	        
	        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
	        try {
	        	lstm = lconConexion.prepareStatement(sql);
	        	lstm.setString(1,ejerDEC.toUpperCase());
	        	lrst = lstm.executeQuery();
	        	
		        while (lrst.next()) {

	        		sTipoMediador = lrst.getString("TC");
		        	if(sTipoMediador.equals(P06BConstantes.P06B_CORREDORES_CTEEXP_FISICA)){
		        		objInformeOtroDEC.setD53_F1(lrst.getDouble("D1"));
		        		objInformeOtroDEC.setD53_F2(lrst.getDouble("D2"));
		        		objInformeOtroDEC.setD53_F3(lrst.getDouble("D3"));
		        		objInformeOtroDEC.setD53_F4(lrst.getDouble("D4"));
		        		objInformeOtroDEC.setD53_F5(lrst.getDouble("D5"));
		        	}else if(sTipoMediador.equals(P06BConstantes.P06B_CORREDORES_CTEEXP_JURIDICA)){
		        		objInformeOtroDEC.setD53_J1(lrst.getDouble("D1"));
		        		objInformeOtroDEC.setD53_J2(lrst.getDouble("D2"));
		        		objInformeOtroDEC.setD53_J3(lrst.getDouble("D3"));
		        		objInformeOtroDEC.setD53_J4(lrst.getDouble("D4"));
		        		objInformeOtroDEC.setD53_J5(lrst.getDouble("D5"));
		        	}else if(sTipoMediador.equals(P06BConstantes.P06B_AGENTEVINCULADO_CTEEXP_FISICA)){
		        		objInformeOtroDEC.setD53_AF1(lrst.getDouble("D1"));
		        		objInformeOtroDEC.setD53_AF2(lrst.getDouble("D2"));
		        		objInformeOtroDEC.setD53_AF3(lrst.getDouble("D3"));
		        		objInformeOtroDEC.setD53_AF4(lrst.getDouble("D4"));
		        		objInformeOtroDEC.setD53_AF5(lrst.getDouble("D5"));
		        	}else if(sTipoMediador.equals(P06BConstantes.P06B_AGENTEVINCULADO_CTEEXP_JURIDICA)){
		        		objInformeOtroDEC.setD53_AJ1(lrst.getDouble("D1"));
		        		objInformeOtroDEC.setD53_AJ2(lrst.getDouble("D2"));
		        		objInformeOtroDEC.setD53_AJ3(lrst.getDouble("D3"));
		        		objInformeOtroDEC.setD53_AJ4(lrst.getDouble("D4"));
		        		objInformeOtroDEC.setD53_AJ5(lrst.getDouble("D5"));
		        	}
		        }
		        P06BLog.send(2,this, "Fin P06B53T00DAO-selectDatosInformeOtroDEC");
		    } 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_P06B53_EJERCICIO + ", " + 
		P06BTablas.P06B_P06B53_NUMEXP + ", " + 
		P06BTablas.P06B_P06B53_INGR_COMIS + ", " + 
		P06BTablas.P06B_P06B53_INGR_FINANC + ", " + 
		P06BTablas.P06B_P06B53_INGR_OTROS + ", " + 
		P06BTablas.P06B_P06B53_GAST_COSTESPER + ", " + 
		P06BTablas.P06B_P06B53_GAST_COMISAUXEXT + ", " + 
		P06BTablas.P06B_P06B53_GAST_FORMACION + ", " + 
		P06BTablas.P06B_P06B53_GAST_FINANC + ", " +
		P06BTablas.P06B_P06B53_GAST_OTROS + ", " +
		P06BTablas.P06B_P06B53_CTA_INGRTOTAL + ", " +
		P06BTablas.P06B_P06B53_CTA_GASTTOTAL +
		
		" FROM "+ P06BTablas.P06B_P06B53T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B53_EJERCICIO + " = ? and " + P06BTablas.P06B_P06B53_NUMEXP + " = ?";

	private static String SELECT_DATCONTAB_VINC_Y_OBS_INFORMEDEC =
		"SELECT sum(nvl("+P06BTablas.P06B_P06B53_INGR_COMIS+",0)) as INGR_COMIS,"+
		" sum(nvl("+P06BTablas.P06B_P06B53_INGR_FINANC+",0)) as INGR_FINANC,"+
		" sum(nvl("+P06BTablas.P06B_P06B53_INGR_OTROS+",0)) as INGR_OTROS,"+
		" sum(nvl("+P06BTablas.P06B_P06B53_GAST_COSTESPER+",0)) as GAST_COSTESPER,"+
		" sum(nvl("+P06BTablas.P06B_P06B53_GAST_COMISAUXEXT+",0)) as GAST_COMISAUXEXT,"+
		" sum(nvl("+P06BTablas.P06B_P06B53_GAST_FORMACION+",0)) as GAST_FORMACION,"+
		" sum(nvl("+P06BTablas.P06B_P06B53_GAST_FINANC+",0)) as GAST_FINANC,"+
		" sum(nvl("+P06BTablas.P06B_P06B53_GAST_OTROS+",0)) as GAST_OTROS,"+
		" sum(nvl("+P06BTablas.P06B_P06B53_CTA_INGRTOTAL+",0)) as CTA_INGRTOTAL,"+
		" sum(nvl("+P06BTablas.P06B_P06B53_CTA_GASTTOTAL+",0)) as CTA_GASTTOTAL"+
		" FROM "+ P06BTablas.P06B_P06B53T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B53_EJERCICIO + " = ?";

	private static String SELECT_DATOS_INFORMEOTRODEC =
		"SELECT SUBSTR(" + P06BTablas.P06B_P06B53_NUMEXP + ", 0, INSTR(" + P06BTablas.P06B_P06B53_NUMEXP + ", '-')-1) as TC, SUM(NVL(" +
		P06BTablas.P06B_P06B53_INGR_COMIS + ",0)) as D1, SUM(NVL(" +
		P06BTablas.P06B_P06B53_INGR_COMIS + ",0)+NVL(" + P06BTablas.P06B_P06B53_INGR_FINANC + ",0)+NVL(" + P06BTablas.P06B_P06B53_INGR_OTROS + ",0)) as D2, SUM(NVL(" +
		P06BTablas.P06B_P06B53_GAST_COSTESPER + ",0)+NVL(" + P06BTablas.P06B_P06B53_GAST_COMISAUXEXT + ",0)+NVL(" + P06BTablas.P06B_P06B53_GAST_FORMACION + ",0)+NVL(" + 
		P06BTablas.P06B_P06B53_GAST_FINANC + ",0)+NVL(" + P06BTablas.P06B_P06B53_GAST_OTROS + ",0)) as D3, SUM(NVL(" +
		P06BTablas.P06B_P06B53_CTA_INGRTOTAL + ",0)) as D4, SUM(NVL(" +
		P06BTablas.P06B_P06B53_CTA_GASTTOTAL + ",0)) as D5 FROM " +
		P06BTablas.P06B_P06B53T00_TABLA + " WHERE " + P06BTablas.P06B_P06B53_EJERCICIO + " = ? GROUP BY SUBSTR(" + P06BTablas.P06B_P06B53_NUMEXP + ", 0, INSTR(" + P06BTablas.P06B_P06B53_NUMEXP + ", '-')-1)";
	
}
