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.logic.P06BInformeOtroDECDet;
import com.ejie.p06b.datatype.model.P06B39T00Datatype;
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 P06BJoinsDAO {
	
	static private P06BJoinsDAO instance = null;

	public static P06BJoinsDAO getInstance() {
	     if (instance == null) {
	          synchronized (P06BJoinsDAO.class) {
	          		if (instance == null) {
	                     instance = new P06BJoinsDAO();
	                }
	          }
	     }
	     return instance;
	}
	
	
	/**
	    * Obtiene los datos para el informe resumen de la DEC asociados al ejercicio indicado
	    * @param ejerDEC.
	    *            ejercicio de la DEC
	    * @param objInformeOtroDECDet.
	    *            objeto donde actualizar los datos obtenidos en la select
		* @throws Exception
	    */
		public void selectDatosInformeOtroDECDet(String ejerDEC, ArrayList<P06BInformeOtroDECDet> p06BInformeOtroDECDet) throws Exception{
			P06BLog.send(2,this, "Inicio P06BJoinsDAO-selectDatosInformeOtroDECDet");
			PreparedStatement lstm  = null;
	        ResultSet lrst  = null;
	        String sql = SELECT_DATOS_INFORMEOTRODECDET;
	        String sTipoMediador;
	        P06BInformeOtroDECDet objInformeOtroDECDet = null;
	        int indLinea = 0;
	        
	        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
	        try {
	        	lstm = lconConexion.prepareStatement(sql);
	        	lstm.setString(1,ejerDEC.toUpperCase());
	        	lstm.setString(2,ejerDEC.toUpperCase());
	        	lstm.setString(3,ejerDEC.toUpperCase());
	        	lstm.setString(4,ejerDEC.toUpperCase());
	        	lstm.setString(5,ejerDEC.toUpperCase());
	        	lstm.setString(6,ejerDEC.toUpperCase());
	        	lrst = lstm.executeQuery();
	        	
		        while (lrst.next()) {
		        	

			        objInformeOtroDECDet = new P06BInformeOtroDECDet();
		        	objInformeOtroDECDet.setTipoExpediente(lrst.getString("TE"));
		        	objInformeOtroDECDet.setNumeroExpediente(lrst.getString("NE"));
	        		objInformeOtroDECDet.setD38_F1(lrst.getDouble("D1_38"));
	        		objInformeOtroDECDet.setD38_F2(lrst.getDouble("D2_38"));
	        		objInformeOtroDECDet.setD38_F3(lrst.getDouble("D3_38"));
	        		objInformeOtroDECDet.setD38_F4(lrst.getDouble("D4_38"));
	        		objInformeOtroDECDet.setD38_F5(lrst.getDouble("D5_38"));
	        		objInformeOtroDECDet.setD38_F6(lrst.getDouble("D6_38"));
	        		objInformeOtroDECDet.setD39_F1(lrst.getDouble("D1_39"));
	        		objInformeOtroDECDet.setD39_F2(lrst.getDouble("D2_39"));
	        		objInformeOtroDECDet.setD39_F3(lrst.getDouble("D3_39"));
	        		objInformeOtroDECDet.setD39_F4(lrst.getDouble("D4_39"));
	        		objInformeOtroDECDet.setD39_F5(lrst.getDouble("D5_39"));
	        		objInformeOtroDECDet.setD39_F6(lrst.getDouble("D6_39"));
	        		objInformeOtroDECDet.setD49_F1(lrst.getDouble("D1_49"));
	        		objInformeOtroDECDet.setD49_F2(lrst.getDouble("D2_49"));
	        		objInformeOtroDECDet.setD53_F1(lrst.getDouble("D1_53"));
	        		objInformeOtroDECDet.setD53_F2(lrst.getDouble("D2_53"));
	        		objInformeOtroDECDet.setD53_F3(lrst.getDouble("D3_53"));
	        		objInformeOtroDECDet.setD53_F4(lrst.getDouble("D4_53"));
	        		objInformeOtroDECDet.setD53_F5(lrst.getDouble("D5_53"));
	        		objInformeOtroDECDet.setD48_F1(lrst.getDouble("D1_48"));
	        		objInformeOtroDECDet.setD50_F1(lrst.getDouble("D1_50"));
	        		objInformeOtroDECDet.setD50_F2(lrst.getDouble("D2_50"));
	        		
	        		p06BInformeOtroDECDet.add(indLinea, objInformeOtroDECDet);
	        		
	        		indLinea++;
		        }
		        P06BLog.send(2,this, "Fin P06BJoinsDAO-selectDatosInformeOtroDECDet");
		    } 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_DATOS_INFORMEOTRODECDET =
//		"select * from (select SUBSTR("+P06BTablas.P06B_P06B20_NUMEXP+",0,INSTR("+P06BTablas.P06B_P06B20_NUMEXP+",'-')-1) AS TE,"+P06BTablas.P06B_P06B20_NUMEXP+" AS NE,D1_38,D2_38,D3_38,D4_38,D5_38,D6_38,D1_39,D2_39,D3_39,D4_39,D5_39,D6_39,D1_49,D2_49,D1_53,D2_53,D3_53,D4_53,D5_53,D1_48 from "+P06BTablas.P06B_P06B20T00_TABLA+",(SELECT "+P06BTablas.P06B_P06B38_NUMEXP+" AS NE_38,SUM(NVL("+P06BTablas.P06B_P06B38_VOLTOT_PRIMINTER+",0)) as D1_38,SUM(NVL("+P06BTablas.P06B_P06B38_VOLTOT_COMISDEVEN+",0)) as D2_38,SUM(NVL("+P06BTablas.P06B_P06B38_VOLTOT_HONORPROF+",0)) as D3_38,SUM(NVL("+P06BTablas.P06B_P06B38_NUEVA_PRIMINTER+",0)) as D4_38,SUM(NVL("+P06BTablas.P06B_P06B38_NUEVA_COMISDEVEN+",0)) as D5_38,SUM(NVL("+P06BTablas.P06B_P06B38_NUEVA_HONORPROF+",0)) as D6_38 FROM "+P06BTablas.P06B_P06B38T00_TABLA+" WHERE "+P06BTablas.P06B_P06B38_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B38_NUMEXP+") T38,(SELECT "+P06BTablas.P06B_P06B39_NUMEXP+" AS NE_39,SUM(NVL("+P06BTablas.P06B_P06B39_VOLTOTIND_PRIMINTER+",0)+NVL("+P06BTablas.P06B_P06B39_VOLTOTCOL_PRIMINTER+",0)) as D1_39,SUM(NVL("+P06BTablas.P06B_P06B39_VOLTOTIND_COMISDEVEN+",0)+NVL("+P06BTablas.P06B_P06B39_VOLTOTCOL_COMISDEVEN+",0)) as D2_39,SUM(NVL("+P06BTablas.P06B_P06B39_VOLTOTIND_HONORPROF+",0)+NVL("+P06BTablas.P06B_P06B39_VOLTOTCOL_HONORPROF+",0)) as D3_39,SUM(NVL("+P06BTablas.P06B_P06B39_NUEVAIND_PRIMINTER+",0)+NVL("+P06BTablas.P06B_P06B39_NUEVACOL_PRIMINTER+",0)) as D4_39,SUM(NVL("+P06BTablas.P06B_P06B39_NUEVAIND_COMISDEVEN+",0)+NVL("+P06BTablas.P06B_P06B39_NUEVACOL_COMISDEVEN+",0)) as D5_39,SUM(NVL("+P06BTablas.P06B_P06B39_NUEVAIND_HONORPROF+",0)+NVL("+P06BTablas.P06B_P06B39_NUEVACOL_HONORPROF+",0)) as D6_39 FROM "+P06BTablas.P06B_P06B39T00_TABLA+" WHERE "+P06BTablas.P06B_P06B39_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B39_NUMEXP+") T39,(SELECT "+P06BTablas.P06B_P06B49_NUMEXP+" AS NE_49,SUM(NVL("+P06BTablas.P06B_P06B49_IMPNETO_AFEC_COMIS+",0)) as D1_49,SUM(NVL("+P06BTablas.P06B_P06B49_IMPNETO_AFEC_HONOR+",0)) as D2_49 FROM "+P06BTablas.P06B_P06B49T00_TABLA+" WHERE "+P06BTablas.P06B_P06B49_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B49_NUMEXP+") T49,(SELECT "+P06BTablas.P06B_P06B53_NUMEXP+" AS NE_53,SUM(NVL("+P06BTablas.P06B_P06B53_INGR_COMIS+",0)) as D1_53,SUM(NVL("+P06BTablas.P06B_P06B53_INGR_COMIS+",0)+NVL("+P06BTablas.P06B_P06B53_INGR_FINANC+",0)+NVL("+P06BTablas.P06B_P06B53_INGR_OTROS+",0)) as D2_53,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_53,SUM(NVL("+P06BTablas.P06B_P06B53_CTA_INGRTOTAL+",0)) as D4_53,SUM(NVL("+P06BTablas.P06B_P06B53_CTA_GASTTOTAL+",0)) as D5_53 FROM "+P06BTablas.P06B_P06B53T00_TABLA+" WHERE "+P06BTablas.P06B_P06B53_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B53_NUMEXP+") T53,(SELECT "+P06BTablas.P06B_P06B48_NUMEXP+" AS NE_48,SUM(NVL("+P06BTablas.P06B_P06B48_VALORANAD_COMIS+",0)) as D1_48 FROM "+P06BTablas.P06B_P06B48T00_TABLA+" WHERE "+P06BTablas.P06B_P06B48_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B48_NUMEXP+") T48 WHERE "+P06BTablas.P06B_P06B20_NUMEXP+" = T38.NE_38(+) AND "+P06BTablas.P06B_P06B20_NUMEXP+" = T39.NE_39(+) AND "+P06BTablas.P06B_P06B20_NUMEXP+" = T49.NE_49(+) AND "+P06BTablas.P06B_P06B20_NUMEXP+" = T53.NE_53(+) AND "+P06BTablas.P06B_P06B20_NUMEXP+" = T48.NE_48(+)) order by TE, NE";
	
	private static String SELECT_DATOS_INFORMEOTRODECDET =
		"select * from (select SUBSTR(NE_38,0,INSTR(NE_38,'-')-1) AS TE,NE_38 AS NE,D1_38,D2_38,D3_38,D4_38,D5_38,D6_38,D1_39,D2_39,D3_39,D4_39,D5_39,D6_39,D1_49,D2_49,D1_53,D2_53,D3_53,D4_53,D5_53,D1_48,D1_50,D2_50 from "+P06BTablas.P06B_P06B20T00_TABLA+",(SELECT "+P06BTablas.P06B_P06B38_NUMEXP+" AS NE_38,SUM(NVL("+P06BTablas.P06B_P06B38_VOLTOT_PRIMINTER+",0)) as D1_38,SUM(NVL("+P06BTablas.P06B_P06B38_VOLTOT_COMISDEVEN+",0)) as D2_38,SUM(NVL("+P06BTablas.P06B_P06B38_VOLTOT_HONORPROF+",0)) as D3_38,SUM(NVL("+P06BTablas.P06B_P06B38_NUEVA_PRIMINTER+",0)) as D4_38,SUM(NVL("+P06BTablas.P06B_P06B38_NUEVA_COMISDEVEN+",0)) as D5_38,SUM(NVL("+P06BTablas.P06B_P06B38_NUEVA_HONORPROF+",0)) as D6_38 FROM "+P06BTablas.P06B_P06B38T00_TABLA+" WHERE "+P06BTablas.P06B_P06B38_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B38_NUMEXP+") T38,(SELECT "+P06BTablas.P06B_P06B39_NUMEXP+" AS NE_39,SUM(NVL("+P06BTablas.P06B_P06B39_VOLTOTIND_PRIMINTER+",0)+NVL("+P06BTablas.P06B_P06B39_VOLTOTCOL_PRIMINTER+",0)) as D1_39,SUM(NVL("+P06BTablas.P06B_P06B39_VOLTOTIND_COMISDEVEN+",0)+NVL("+P06BTablas.P06B_P06B39_VOLTOTCOL_COMISDEVEN+",0)) as D2_39,SUM(NVL("+P06BTablas.P06B_P06B39_VOLTOTIND_HONORPROF+",0)+NVL("+P06BTablas.P06B_P06B39_VOLTOTCOL_HONORPROF+",0)) as D3_39,SUM(NVL("+P06BTablas.P06B_P06B39_NUEVAIND_PRIMINTER+",0)+NVL("+P06BTablas.P06B_P06B39_NUEVACOL_PRIMINTER+",0)) as D4_39,SUM(NVL("+P06BTablas.P06B_P06B39_NUEVAIND_COMISDEVEN+",0)+NVL("+P06BTablas.P06B_P06B39_NUEVACOL_COMISDEVEN+",0)) as D5_39,SUM(NVL("+P06BTablas.P06B_P06B39_NUEVAIND_HONORPROF+",0)+NVL("+P06BTablas.P06B_P06B39_NUEVACOL_HONORPROF+",0)) as D6_39 FROM "+P06BTablas.P06B_P06B39T00_TABLA+" WHERE "+P06BTablas.P06B_P06B39_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B39_NUMEXP+") T39,(SELECT "+P06BTablas.P06B_P06B49_NUMEXP+" AS NE_49,SUM(NVL("+P06BTablas.P06B_P06B49_IMPNETO_AFEC_COMIS+",0)) as D1_49,SUM(NVL("+P06BTablas.P06B_P06B49_IMPNETO_AFEC_HONOR+",0)) as D2_49 FROM "+P06BTablas.P06B_P06B49T00_TABLA+" WHERE "+P06BTablas.P06B_P06B49_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B49_NUMEXP+") T49,(SELECT "+P06BTablas.P06B_P06B53_NUMEXP+" AS NE_53,SUM(NVL("+P06BTablas.P06B_P06B53_INGR_COMIS+",0)) as D1_53,SUM(NVL("+P06BTablas.P06B_P06B53_INGR_COMIS+",0)+NVL("+P06BTablas.P06B_P06B53_INGR_FINANC+",0)+NVL("+P06BTablas.P06B_P06B53_INGR_OTROS+",0)) as D2_53,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_53,SUM(NVL("+P06BTablas.P06B_P06B53_CTA_INGRTOTAL+",0)) as D4_53,SUM(NVL("+P06BTablas.P06B_P06B53_CTA_GASTTOTAL+",0)) as D5_53 FROM "+P06BTablas.P06B_P06B53T00_TABLA+" WHERE "+P06BTablas.P06B_P06B53_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B53_NUMEXP+") T53,(SELECT "+P06BTablas.P06B_P06B48_NUMEXP+" AS NE_48,SUM(NVL("+P06BTablas.P06B_P06B48_VALORANAD_COMIS+",0)) as D1_48 FROM "+P06BTablas.P06B_P06B48T00_TABLA+" WHERE "+P06BTablas.P06B_P06B48_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B48_NUMEXP+") T48,(SELECT "+P06BTablas.P06B_P06B50_NUMEXP+" AS NE_50,SUM(NVL("+P06BTablas.P06B_P06B50_IMPNETO_AFEC_COMIS+",0)) as D1_50,SUM(NVL("+P06BTablas.P06B_P06B50_IMPNETO_AFEC_HONOR+",0)) as D2_50 FROM "+P06BTablas.P06B_P06B50T00_TABLA+" WHERE "+P06BTablas.P06B_P06B50_EJERCICIO+" = ? GROUP BY "+P06BTablas.P06B_P06B50_NUMEXP+") T50 WHERE "+P06BTablas.P06B_P06B20_NUMEXP+" = T38.NE_38(+) AND "+P06BTablas.P06B_P06B20_NUMEXP+" = T39.NE_39(+) AND "+P06BTablas.P06B_P06B20_NUMEXP+" = T49.NE_49(+) AND "+P06BTablas.P06B_P06B20_NUMEXP+" = T53.NE_53(+) AND "+P06BTablas.P06B_P06B20_NUMEXP+" = T48.NE_48(+) AND "+P06BTablas.P06B_P06B20_NUMEXP+" = T50.NE_50(+)) WHERE TE IS NOT NULL order by TE, NE";
}
