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.model.P06B51T00Datatype;
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 P06B51T00DAO {
	
	static private P06B51T00DAO instance = null;

	public static P06B51T00DAO getInstance() {
	     if (instance == null) {
	          synchronized (P06B51T00DAO.class) {
	          		if (instance == null) {
	                     instance = new P06B51T00DAO();
	                }
	          }
	     }
	     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 P06B51T00Datatype o null si no se encuentra nada.
	* @throws Exception
    */
	public P06B51T00Datatype select(String ejer, String numExp) throws Exception{
		P06BLog.send(2,this, "Inicio P06B51T00DAO-select");
		P06B51T00Datatype 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 P06B51T00Datatype();
	        	
	        	result.setEjercicio(lrst.getString(P06BTablas.P06B_P06B51_EJERCICIO));
	        	result.setNumExp(lrst.getString(P06BTablas.P06B_P06B51_NUMEXP));
			    result.setNoCorrInmovIntang(lrst.getString(P06BTablas.P06B_P06B51_NOCORR_INMOVINTANG)==null?"":lrst.getString(P06BTablas.P06B_P06B51_NOCORR_INMOVINTANG));
			    result.setNoCorrInmovMat(lrst.getString(P06BTablas.P06B_P06B51_NOCORR_INMOVMAT)==null?"":lrst.getString(P06BTablas.P06B_P06B51_NOCORR_INMOVMAT));
			    result.setNoCorrInverInmob(lrst.getString(P06BTablas.P06B_P06B51_NOCORR_INVERINMOB)==null?"":lrst.getString(P06BTablas.P06B_P06B51_NOCORR_INVERINMOB));
			    result.setNoCorrInverEmp(lrst.getString(P06BTablas.P06B_P06B51_NOCORR_INVEREMP)==null?"":lrst.getString(P06BTablas.P06B_P06B51_NOCORR_INVEREMP));
			    result.setNoCorrInverFinanc(lrst.getString(P06BTablas.P06B_P06B51_NOCORR_INVERFINANC)==null?"":lrst.getString(P06BTablas.P06B_P06B51_NOCORR_INVERFINANC));
			    result.setNoCorrImpuesDif(lrst.getString(P06BTablas.P06B_P06B51_NOCORR_IMPUESDIF)==null?"":lrst.getString(P06BTablas.P06B_P06B51_NOCORR_IMPUESDIF));
			    result.setCorrNoCorr(lrst.getString(P06BTablas.P06B_P06B51_CORR_NOCORR)==null?"":lrst.getString(P06BTablas.P06B_P06B51_CORR_NOCORR));
			    result.setCorrExist(lrst.getString(P06BTablas.P06B_P06B51_CORR_EXIST)==null?"":lrst.getString(P06BTablas.P06B_P06B51_CORR_EXIST));
			    result.setCorrDeudClientes(lrst.getString(P06BTablas.P06B_P06B51_CORR_DEUD_CLIENTES)==null?"":lrst.getString(P06BTablas.P06B_P06B51_CORR_DEUD_CLIENTES));
			    result.setCorrDeudAccionis(lrst.getString(P06BTablas.P06B_P06B51_CORR_DEUD_ACCIONIS)==null?"":lrst.getString(P06BTablas.P06B_P06B51_CORR_DEUD_ACCIONIS));
			    result.setCorrDeudOtros(lrst.getString(P06BTablas.P06B_P06B51_CORR_DEUD_OTROS)==null?"":lrst.getString(P06BTablas.P06B_P06B51_CORR_DEUD_OTROS));
			    result.setCorrInverEmp(lrst.getString(P06BTablas.P06B_P06B51_CORR_INVEREMP)==null?"":lrst.getString(P06BTablas.P06B_P06B51_CORR_INVEREMP));
			    result.setCorrInverFinanc(lrst.getString(P06BTablas.P06B_P06B51_CORR_INVERFINANC)==null?"":lrst.getString(P06BTablas.P06B_P06B51_CORR_INVERFINANC));
			    result.setCorrPeriodif(lrst.getString(P06BTablas.P06B_P06B51_CORR_PERIODIF)==null?"":lrst.getString(P06BTablas.P06B_P06B51_CORR_PERIODIF));
			    result.setCorrEfect(lrst.getString(P06BTablas.P06B_P06B51_CORR_EFECT)==null?"":lrst.getString(P06BTablas.P06B_P06B51_CORR_EFECT));
	        }
	        P06BLog.send(2,this, "Fin P06B51T00DAO-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 un registro de la BD a partir de la clave primaria
	    * @param tipoMediador.
	    *            parte de la clave primaria
	    * @param ejer.
	    *            parte de la clave primaria
	    * @return un objeto P06B51T00Datatype o null si no se encuentra nada.
		* @throws Exception
	    */
		public P06B51T00Datatype selectTipoMediador(String ejer, String tipoMediador) throws Exception{
			P06BLog.send(2,this, "Inicio P06B51T00DAO-selectTipoMediador");
			P06B51T00Datatype result = null;
			PreparedStatement lstm  = null;
	        ResultSet lrst  = null;
	        String sql = SELECT_BY_PKTIPOMEDIADOR;
	        String tipoMediadorFmtd = tipoMediador.toUpperCase()+"-%";
	        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
	        try {
	        	lstm = lconConexion.prepareStatement(sql);
	        	lstm.setString(1,ejer);
	        	lstm.setString(2,tipoMediadorFmtd);
	        	lrst = lstm.executeQuery();
		        if (lrst.next()) {
		        	result = new P06B51T00Datatype();
		        	
		        	result.setEjercicio(ejer);
		        	result.setNumExp(tipoMediadorFmtd);
				    result.setNoCorrInmovIntang(lrst.getString(1)==null?"":lrst.getString(1));
				    result.setNoCorrInmovMat(lrst.getString(2)==null?"":lrst.getString(2));
				    result.setNoCorrInverInmob(lrst.getString(3)==null?"":lrst.getString(3));
				    result.setNoCorrInverEmp(lrst.getString(4)==null?"":lrst.getString(4));
				    result.setNoCorrInverFinanc(lrst.getString(5)==null?"":lrst.getString(5));
				    result.setNoCorrImpuesDif(lrst.getString(6)==null?"":lrst.getString(6));
				    result.setCorrNoCorr(lrst.getString(7)==null?"":lrst.getString(7));
				    result.setCorrExist(lrst.getString(8)==null?"":lrst.getString(8));
				    result.setCorrDeudClientes(lrst.getString(9)==null?"":lrst.getString(9));
				    result.setCorrDeudAccionis(lrst.getString(10)==null?"":lrst.getString(10));
				    result.setCorrDeudOtros(lrst.getString(11)==null?"":lrst.getString(11));
				    result.setCorrInverEmp(lrst.getString(12)==null?"":lrst.getString(12));
				    result.setCorrInverFinanc(lrst.getString(13)==null?"":lrst.getString(13));
				    result.setCorrPeriodif(lrst.getString(14)==null?"":lrst.getString(14));
				    result.setCorrEfect(lrst.getString(15)==null?"":lrst.getString(15));
		        }
		        P06BLog.send(2,this, "Fin P06B51T00DAO-selectTipoMediador");
		    } 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 un registro de la BD a partir de la clave primaria
		    * @param tipoMediador.
		    *            parte de la clave primaria
		    * @param ejer.
		    *            parte de la clave primaria
		    * @return un objeto P06B51T00Datatype o null si no se encuentra nada.
			* @throws Exception
		    */
			public P06B51T00Datatype selectSumOfEjer(String ejer) throws Exception{
				P06BLog.send(2,this, "Inicio P06B51T00DAO-selectAllByEjer");
				P06B51T00Datatype result = null;
				PreparedStatement lstm  = null;
		        ResultSet lrst  = null;
		        String sql = SELECTSUM_OFEJER;
		        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
		        try {
		        	lstm = lconConexion.prepareStatement(sql);
		        	lstm.setString(1,ejer);
		        	lrst = lstm.executeQuery();
			        if (lrst.next()) {
			        	result = new P06B51T00Datatype();
			        	
			        	result.setEjercicio(ejer);
					    result.setNoCorrInmovIntang(lrst.getString(1)==null?"":lrst.getString(1));
					    result.setNoCorrInmovMat(lrst.getString(2)==null?"":lrst.getString(2));
					    result.setNoCorrInverInmob(lrst.getString(3)==null?"":lrst.getString(3));
					    result.setNoCorrInverEmp(lrst.getString(4)==null?"":lrst.getString(4));
					    result.setNoCorrInverFinanc(lrst.getString(5)==null?"":lrst.getString(5));
					    result.setNoCorrImpuesDif(lrst.getString(6)==null?"":lrst.getString(6));
					    result.setCorrNoCorr(lrst.getString(7)==null?"":lrst.getString(7));
					    result.setCorrExist(lrst.getString(8)==null?"":lrst.getString(8));
					    result.setCorrDeudClientes(lrst.getString(9)==null?"":lrst.getString(9));
					    result.setCorrDeudAccionis(lrst.getString(10)==null?"":lrst.getString(10));
					    result.setCorrDeudOtros(lrst.getString(11)==null?"":lrst.getString(11));
					    result.setCorrInverEmp(lrst.getString(12)==null?"":lrst.getString(12));
					    result.setCorrInverFinanc(lrst.getString(13)==null?"":lrst.getString(13));
					    result.setCorrPeriodif(lrst.getString(14)==null?"":lrst.getString(14));
					    result.setCorrEfect(lrst.getString(15)==null?"":lrst.getString(15));
			        }
			        P06BLog.send(2,this, "Fin P06B51T00DAO-selectAllByEjer");
			    } 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;
			}
			
			
	private static String SELECT_BY_PK =
		"SELECT " +	
		P06BTablas.P06B_P06B51_EJERCICIO + ", " + 
		P06BTablas.P06B_P06B51_NUMEXP + ", " + 
		P06BTablas.P06B_P06B51_NOCORR_INMOVINTANG + ", " + 
		P06BTablas.P06B_P06B51_NOCORR_INMOVMAT + ", " + 
		P06BTablas.P06B_P06B51_NOCORR_INVERINMOB + ", " + 
		P06BTablas.P06B_P06B51_NOCORR_INVEREMP + ", " + 
		P06BTablas.P06B_P06B51_NOCORR_INVERFINANC + ", " + 
		P06BTablas.P06B_P06B51_NOCORR_IMPUESDIF + ", " +
		P06BTablas.P06B_P06B51_CORR_NOCORR + ", " + 
		P06BTablas.P06B_P06B51_CORR_EXIST + ", " + 
		P06BTablas.P06B_P06B51_CORR_DEUD_CLIENTES + ", " + 
		P06BTablas.P06B_P06B51_CORR_DEUD_ACCIONIS + ", " + 
		P06BTablas.P06B_P06B51_CORR_DEUD_OTROS + ", " + 
		P06BTablas.P06B_P06B51_CORR_INVEREMP + ", " +
		P06BTablas.P06B_P06B51_CORR_INVERFINANC + ", " + 
		P06BTablas.P06B_P06B51_CORR_PERIODIF + ", " + 
		P06BTablas.P06B_P06B51_CORR_EFECT +
				
		" FROM "+ P06BTablas.P06B_P06B51T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B51_EJERCICIO + " = ? and " + P06BTablas.P06B_P06B51_NUMEXP + " = ?";

	private static String SELECT_BY_PKTIPOMEDIADOR =
		"SELECT "	
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_INMOVINTANG + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_INMOVMAT + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_INVERINMOB + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_INVEREMP + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_INVERFINANC + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_IMPUESDIF + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_NOCORR + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_EXIST + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_DEUD_CLIENTES + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_DEUD_ACCIONIS + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_DEUD_OTROS + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_INVEREMP + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_INVERFINANC + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_PERIODIF + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_EFECT + ") " + 			
		" FROM "+ P06BTablas.P06B_P06B51T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B51_EJERCICIO + " = ? and " + P06BTablas.P06B_P06B51_NUMEXP + " LIKE ?";

	private static String SELECTSUM_OFEJER =
		"SELECT "	
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_INMOVINTANG + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_INMOVMAT + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_INVERINMOB + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_INVEREMP + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_INVERFINANC + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_NOCORR_IMPUESDIF + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_NOCORR + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_EXIST + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_DEUD_CLIENTES + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_DEUD_ACCIONIS + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_DEUD_OTROS + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_INVEREMP + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_INVERFINANC + "), " 
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_PERIODIF + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B51_CORR_EFECT + ") " + 			
		" FROM "+ P06BTablas.P06B_P06B51T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B51_EJERCICIO + " = ?";

}
