package com.ejie.p06b.dao;

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

import com.ejie.p06b.datatype.logic.P06BInformeDEC;
import com.ejie.p06b.datatype.model.P06B36T00Datatype;
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 P06B36T00DAO {
	
	static private P06B36T00DAO instance = null;

	public static P06B36T00DAO getInstance() {
	     if (instance == null) {
	          synchronized (P06B36T00DAO.class) {
	          		if (instance == null) {
	                     instance = new P06B36T00DAO();
	                }
	          }
	     }
	     return instance;
	}
	
	
	/**
    * Obtiene la lista de medios personales del modelo 1 de la DEC asociados al expediente y ejercicio
    * @param ejer.
    * @param numExp          
    * @return Lista de objetos de medios personales DEC
	 * @throws Exception
    */
	public ArrayList selectAllByEjerExp(String ejer, String numExp) throws Exception{
		P06BLog.send(2,this, "Inicio P06B36T00DAO-selectAllByEjerExp");
		ArrayList lista = new ArrayList();
		P06B36T00Datatype obj = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECTALL_BY_EJEREXP;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setString(1,ejer.toUpperCase());
        	lstm.setString(2,numExp.toUpperCase());
        	lrst = lstm.executeQuery();
	        while (lrst.next()) {
	        	obj = new P06B36T00Datatype();
	        	
	        	obj.setEjercicio(lrst.getString(P06BTablas.P06B_P06B36_EJERCICIO));
			    obj.setNumExp(lrst.getString(P06BTablas.P06B_P06B36_NUMEXP));
			    obj.setCodMedPer(lrst.getString(P06BTablas.P06B_P06B36_CODMEDPER));
			    obj.setNumPer(lrst.getString(P06BTablas.P06B_P06B36_NUMPER)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NUMPER));
			    obj.setNivel1(lrst.getString(P06BTablas.P06B_P06B36_NIVEL1)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NIVEL1));
			    obj.setNivel2(lrst.getString(P06BTablas.P06B_P06B36_NIVEL2)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NIVEL2));
			    obj.setNivel3(lrst.getString(P06BTablas.P06B_P06B36_NIVEL3)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NIVEL3));
			    obj.setNivel4(lrst.getString(P06BTablas.P06B_P06B36_NIVEL4)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NIVEL4));
			    obj.setHoras(lrst.getString(P06BTablas.P06B_P06B36_HORAS)==null?"":lrst.getString(P06BTablas.P06B_P06B36_HORAS));
			    obj.setMedio1(lrst.getString(P06BTablas.P06B_P06B36_MEDIO1)==null?"":lrst.getString(P06BTablas.P06B_P06B36_MEDIO1));
			    obj.setMedio2(lrst.getString(P06BTablas.P06B_P06B36_MEDIO2)==null?"":lrst.getString(P06BTablas.P06B_P06B36_MEDIO2));
			    obj.setGasto(lrst.getString(P06BTablas.P06B_P06B36_GASTO)==null?"":lrst.getString(P06BTablas.P06B_P06B36_GASTO));
			    obj.setNumperA(lrst.getString(P06BTablas.P06B_P06B36_NUMPERA)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NUMPERA));
			    obj.setNumperB(lrst.getString(P06BTablas.P06B_P06B36_NUMPERB)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NUMPERB));
			    obj.setNumperC(lrst.getString(P06BTablas.P06B_P06B36_NUMPERC)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NUMPERC));
			    obj.setNumAfec(lrst.getString(P06BTablas.P06B_P06B36_NUMAFEC)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NUMAFEC));
			    obj.setNumNoAfec(lrst.getString(P06BTablas.P06B_P06B36_NUMNOAFEC)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NUMNOAFEC));
			    
	        	lista.add(obj);
	        }
	        P06BLog.send(2,this, "Fin P06B36T00DAO-selectAllByEjerExp");
	    } 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 lista;
	}
	
	
	
	/**
	    * Obtiene la lista de medios personales del modelo 1 de la DEC asociados al expediente y tipo de mediador
	    * @param ejer.
	    * @param tipoMediador          
	    * @return Lista de objetos de medios personales DEC
		 * @throws Exception
	    */
		public ArrayList selectAllByEjerTipoMediador(String ejer, String tipoMediador) throws Exception{
			P06BLog.send(2,this, "Inicio P06B36T00DAO-selectAllByEjerTipoMediador");
			ArrayList lista = new ArrayList();
			P06B36T00Datatype obj = null;
			PreparedStatement lstm  = null;
	        ResultSet lrst  = null;
	        String sql = SELECTALL_BY_EJERTIPOMEDIADOR;
	        String tipoMediadorFmtd = tipoMediador.toUpperCase()+"-%";
	        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
	        try {
	        	lstm = lconConexion.prepareStatement(sql);
	        	lstm.setString(1,ejer.toUpperCase());
	        	lstm.setString(2,tipoMediadorFmtd);
	        	lrst = lstm.executeQuery();
		        while (lrst.next()) {
		        	obj = new P06B36T00Datatype();
		        	
		        	obj.setEjercicio(ejer);
				    obj.setNumExp(tipoMediadorFmtd);
//				    obj.setNumAfec(lrst.getString(P06BTablas.P06B_P06B36_NUMAFEC)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NUMAFEC));
//				    obj.setNumNoAfec(lrst.getString(P06BTablas.P06B_P06B36_NUMNOAFEC)==null?"":lrst.getString(P06BTablas.P06B_P06B36_NUMNOAFEC));
				    obj.setCodMedPer(lrst.getString(1));
				    obj.setNumAfec(lrst.getString(2)==null?"":lrst.getString(2));
				    obj.setNumNoAfec(lrst.getString(3)==null?"":lrst.getString(3));
				    
		        	lista.add(obj);
		        }
		        P06BLog.send(2,this, "Fin P06B36T00DAO-selectAllByEjerTipoMediador");
		    } 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 lista;
		}	
		
		/**
		    * Obtiene la lista de medios personales del modelo 1 de la DEC asociados al expediente y tipo de mediador
		    * @param ejer.
		    * @param tipoMediador          
		    * @return Lista de objetos de medios personales DEC
			 * @throws Exception
		    */
			public ArrayList selectAllByEjer(String ejer) throws Exception{
				P06BLog.send(2,this, "Inicio P06B36T00DAO-selectAllByEjer");
				ArrayList lista = new ArrayList();
				P06B36T00Datatype obj = null;
				PreparedStatement lstm  = null;
		        ResultSet lrst  = null;
		        String sql = SELECTALL_BY_EJER;
		        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
		        try {
		        	lstm = lconConexion.prepareStatement(sql);
		        	lstm.setString(1,ejer.toUpperCase());
		        	lrst = lstm.executeQuery();
			        while (lrst.next()) {
			        	obj = new P06B36T00Datatype();
			        	
			        	obj.setEjercicio(ejer);
					    obj.setCodMedPer(lrst.getString(1));
					    obj.setNumAfec(lrst.getString(2)==null?"":lrst.getString(2));
					    obj.setNumNoAfec(lrst.getString(3)==null?"":lrst.getString(3));
					    
			        	lista.add(obj);
			        }
			        P06BLog.send(2,this, "Fin P06B36T00DAO-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 lista;
			}	
			
	/**
    * Obtiene los datos para el informe resumen de la DEC asociados al ejercicio y codigo indicados
    * @param ejerDEC
    * @param objInformeDEC
    * @param codMedPer          
	* @throws Exception
    */
	public void selectMedPerFormacInformeDEC(String ejerDEC, P06BInformeDEC objInformeDEC, String codMedPer, String tipoMed) throws Exception{
		P06BLog.send(2,this, "Inicio P06B36T00DAO-selectMedPerFormacInformeDEC");
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_MEDPER_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_P06B36_NUMEXP + " like '"+P06BConstantes.P06B_CORREDORES_TIPOPER_JURIDICA+"-%' or " + P06BTablas.P06B_P06B36_NUMEXP + " like '"+P06BConstantes.P06B_CORREDORES_TIPOPER_FISICA+"-%' or " + P06BTablas.P06B_P06B36_NUMEXP + " like '"+P06BConstantes.P06B_REASEGUROS_JURIDICA+"-%' or " + P06BTablas.P06B_P06B36_NUMEXP + " like '"+P06BConstantes.P06B_REASEGUROS_FISICA+"-%')";
    	}else if(P06BConstantes.P06B_VINC.equals(tipoMed)){
    		sql += " AND " + " (" + P06BTablas.P06B_P06B36_NUMEXP + " like '"+P06BConstantes.P06B_AGENTEVINCULADO_FISICA+"-%' or " + P06BTablas.P06B_P06B36_NUMEXP + " like '"+P06BConstantes.P06B_AGENTEVINCULADO_JURIDICA+"-%')";
    	}else if(P06BConstantes.P06B_OBS.equals(tipoMed)){
    		sql += " AND " + " (" + P06BTablas.P06B_P06B36_NUMEXP + " like '"+P06BConstantes.P06B_OPERADORBANCA_VINCULADO+"-%' or " + P06BTablas.P06B_P06B36_NUMEXP + " like '"+P06BConstantes.P06B_OPERADORBANCA_EXCLUSIVO+"-%')";
    	}
              
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setString(1,ejerDEC.toUpperCase());
        	lstm.setString(2,codMedPer);
        	lrst = lstm.executeQuery();
        	if (lrst.next()) {
	        	if(P06BConstantes.P06B_DEC_MEDPER_DIREC.equals(codMedPer)){
	        		if(P06BConstantes.P06B_CORR.equals(tipoMed)){
	        			objInformeDEC.setAfecDirCorrM1(lrst.getInt("NUMAFEC"));
		        		objInformeDEC.setNoAfecDirCorrM1(lrst.getInt("NUMNOAFEC"));
	            	}else if(P06BConstantes.P06B_VINC.equals(tipoMed)){
	            		objInformeDEC.setAfecDirVincM1(lrst.getInt("NUMAFEC"));
		        		objInformeDEC.setNoAfecDirVincM1(lrst.getInt("NUMNOAFEC"));
	            	}else if(P06BConstantes.P06B_OBS.equals(tipoMed)){
	            		objInformeDEC.setAfecDirObsM1(lrst.getInt("NUMAFEC"));
		        		objInformeDEC.setNoAfecDirObsM1(lrst.getInt("NUMNOAFEC"));
	            	}
	        	}else if(P06BConstantes.P06B_DEC_MEDPER_EMPL.equals(codMedPer)){
	        		if(P06BConstantes.P06B_CORR.equals(tipoMed)){
	        			objInformeDEC.setAfecEmpCorrM1(lrst.getInt("NUMAFEC"));
		        		objInformeDEC.setNoAfecEmpCorrM1(lrst.getInt("NUMNOAFEC"));
	            	}else if(P06BConstantes.P06B_VINC.equals(tipoMed)){
	            		objInformeDEC.setAfecEmpVincM1(lrst.getInt("NUMAFEC"));
		        		objInformeDEC.setNoAfecEmpVincM1(lrst.getInt("NUMNOAFEC"));
	            	}else if(P06BConstantes.P06B_OBS.equals(tipoMed)){
	            		objInformeDEC.setAfecEmpObsM1(lrst.getInt("NUMAFEC"));
		        		objInformeDEC.setNoAfecEmpObsM1(lrst.getInt("NUMNOAFEC"));
	            	}
	        	}else if(P06BConstantes.P06B_DEC_MEDPER_OTRO.equals(codMedPer)){
	        		if(P06BConstantes.P06B_CORR.equals(tipoMed)){
	        			objInformeDEC.setAfecOtrosCorrM1(lrst.getInt("NUMAFEC"));
		        		objInformeDEC.setNoAfecOtrosCorrM1(lrst.getInt("NUMNOAFEC"));
	            	}else if(P06BConstantes.P06B_VINC.equals(tipoMed)){
	            		objInformeDEC.setAfecOtrosVincM1(lrst.getInt("NUMAFEC"));
		        		objInformeDEC.setNoAfecOtrosVincM1(lrst.getInt("NUMNOAFEC"));
	            	}else if(P06BConstantes.P06B_OBS.equals(tipoMed)){
	            		objInformeDEC.setAfecOtrosObsM1(lrst.getInt("NUMAFEC"));
		        		objInformeDEC.setNoAfecOtrosObsM1(lrst.getInt("NUMNOAFEC"));
	            	}
	        	}
	        }
	        P06BLog.send(2,this, "Fin P06B36T00DAO-selectMedPerFormacInformeDEC");
	    } 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 SELECTALL_BY_EJEREXP =
		"SELECT "
		+ P06BTablas.P06B_P06B36_EJERCICIO + ", "
		+ P06BTablas.P06B_P06B36_NUMEXP + ", "
		+ P06BTablas.P06B_P06B36_CODMEDPER + ", "
		+ P06BTablas.P06B_P06B36_NUMPER + ", "
		+ P06BTablas.P06B_P06B36_NIVEL1 + ", "
		+ P06BTablas.P06B_P06B36_NIVEL2 + ", "
		+ P06BTablas.P06B_P06B36_NIVEL3 + ", "
		+ P06BTablas.P06B_P06B36_NIVEL4 + ", "
		+ P06BTablas.P06B_P06B36_HORAS + ", "
		+ P06BTablas.P06B_P06B36_MEDIO1 + ", "
		+ P06BTablas.P06B_P06B36_MEDIO2 + ", "
		+ P06BTablas.P06B_P06B36_GASTO + ", "
		+ P06BTablas.P06B_P06B36_NUMPERA + ", "
		+ P06BTablas.P06B_P06B36_NUMPERB + ", "
		+ P06BTablas.P06B_P06B36_NUMPERC + ", "
		+ P06BTablas.P06B_P06B36_NUMAFEC + ", "
		+ P06BTablas.P06B_P06B36_NUMNOAFEC +

		" FROM " + P06BTablas.P06B_P06B36T00_TABLA 
		+ " WHERE " + P06BTablas.P06B_P06B36_EJERCICIO + "= ?" 
			+ " AND " + P06BTablas.P06B_P06B36_NUMEXP + "= ?"
		+" ORDER BY " + P06BTablas.P06B_P06B36_CODMEDPER ;
		
	private static String SELECTALL_BY_EJERTIPOMEDIADOR =
		"SELECT "
		+ P06BTablas.P06B_P06B36_CODMEDPER + ", "
		+ "SUM("
		+ P06BTablas.P06B_P06B36_NUMAFEC + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B36_NUMNOAFEC + ") "

		+ " FROM " + P06BTablas.P06B_P06B36T00_TABLA 
		+ " WHERE " + P06BTablas.P06B_P06B36_EJERCICIO + "= ?" 
			+ " AND " + P06BTablas.P06B_P06B36_NUMEXP + " LIKE ?"
		+ " GROUP BY " + P06BTablas.P06B_P06B36_CODMEDPER
		+ " ORDER BY " + P06BTablas.P06B_P06B36_CODMEDPER ;
		
	private static String SELECTALL_BY_EJER =
		"SELECT "
		+ P06BTablas.P06B_P06B36_CODMEDPER + ", "
		+ "SUM("
		+ P06BTablas.P06B_P06B36_NUMAFEC + "), "
		+ "SUM("
		+ P06BTablas.P06B_P06B36_NUMNOAFEC + ") "

		+ " FROM " + P06BTablas.P06B_P06B36T00_TABLA 
		+ " WHERE " + P06BTablas.P06B_P06B36_EJERCICIO + "= ?"
		+ " GROUP BY " + P06BTablas.P06B_P06B36_CODMEDPER
		+ " ORDER BY " + P06BTablas.P06B_P06B36_CODMEDPER ;
		
	private static String SELECT_MEDPER_INFORMEDEC =
		"SELECT sum(nvl("+P06BTablas.P06B_P06B36_NUMAFEC+",0)) as NUMAFEC, sum(nvl("+P06BTablas.P06B_P06B36_NUMNOAFEC+",0)) as NUMNOAFEC "
		+ " FROM " + P06BTablas.P06B_P06B36T00_TABLA 
		+ " WHERE " + P06BTablas.P06B_P06B36_EJERCICIO + "= ?"
			+ " AND " + P06BTablas.P06B_P06B36_CODMEDPER + "= ?";
	
}
