package com.ejie.p06b.dao;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Locale;

import com.ejie.p06b.datatype.model.P06B81T00Datatype;
import com.ejie.p06b.general.P06BConstantes;
import com.ejie.p06b.general.P06BTablas;
import com.ejie.p06b.log.P06BLog;
import com.ejie.p06b.util.P06BFecha;
import com.ejie.p06b.util.bd.P06BServidorConexionesBD;

/**
 * Fecha de creación: (15/01/2010)
 */
public class P06B81T00DAO {
	
	static private P06B81T00DAO instance = null;

	public static P06B81T00DAO getInstance() {
	     if (instance == null) {
	          synchronized (P06B81T00DAO.class) {
	          		if (instance == null) {
	                     instance = new P06B81T00DAO();
	                }
	          }
	     }
	     return instance;
	}
	
	/**
    * Obtiene la lista de documentos asociados al expediente
    * @param expediente.       
    * @return Lista de objetos de documentos
	 * @throws Exception
    */
	public ArrayList selectAllByExp(String expediente, String idioma) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B81T00Datatype-selectAllByExp");
		ArrayList lista = new ArrayList();
		P06B81T00Datatype obj = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECTALL_BY_EXP;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setString(1,idioma);
        	lstm.setString(2,expediente.toUpperCase());
        	lstm.setString(3,idioma);
        	
        	lrst = lstm.executeQuery();
	        while (lrst.next()) {
	    		
	        	obj = new P06B81T00Datatype();
	        	obj.setExpediente(lrst.getString(P06BTablas.P06B_P06B81_DT_NUMEXP));
	        	obj.setFechaHora(P06BFecha.getFechaHoraFormateadaFromIdioma(lrst.getTimestamp(P06BTablas.P06B_P06B64_AP_FECHA), idioma));
			    obj.setRoleIdEnPid(lrst.getString(P06BTablas.P06B_P06B81_DT_ROLEIDPIDFICHERO));
			    obj.setRoleNameCasEnPid(lrst.getString(P06BTablas.P06B_P06B81_DT_ROLENAMECASPIDFICHERO));
			    obj.setRoleNameEusEnPid(lrst.getString(P06BTablas.P06B_P06B81_DT_ROLENAMEEUSPIDFICHERO));
			    obj.setRoleNameEnPidIdiomaActivo(lrst.getString("P06B_P06B81_DT_ROLENAMEIDIACT"));
			    obj.setReferenciaEnPid(lrst.getString(P06BTablas.P06B_P06B81_DT_CODPIDFICHERO));
			    obj.setNombreEnPid(lrst.getString(P06BTablas.P06B_P06B81_DT_NOMBREFICHERO));
			    obj.setExtensionEnPid(lrst.getString(P06BTablas.P06B_P06B81_DT_EXTENSIONFICHERO));
			    
			    //Descargar el dato de tipo BLOB
//	        	byte[] doc = null;
//	    		ByteArrayOutputStream bos = new ByteArrayOutputStream();
//	    		InputStream is = null;
//				is = lrst.getBinaryStream(P06BTablas.P06B_P06B31_DOCUMENTO);
//				if (is != null ){
//					int leido = -1;
//					while ((leido = is.read()) != -1 ){
//						bos.write( leido );
//					}
//					doc = bos.toByteArray();
//				}
//				obj.setDocumento(doc);

	        	lista.add(obj);
	        }
			lrst.close();
    		lstm.close();
    		lconConexion.close();
	        P06BLog.send(2,this, "Fin P06B81T00Datatype-selectAllByExp");
	    }catch (SQLException SQLe) {
	    	P06BLog.send(2, this, SQLe.getMessage());
	    	throw(SQLe);
	    }
		return lista;
	}

	
	/**
    * Elimina el documento adjunto asociado a un expediente concreto
    * @param obj.
    *            el documento adjunto a eliminar
    * @throws SQLException
    */
	public void deleteByExpCorr(P06B81T00Datatype obj) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B81T00DAO-deleteByExpCorr");
		
		PreparedStatement lstm  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = DELETE_BYEXPCODPID;
        try{
            lstm = lconConexion.prepareStatement(sql);        	
        	lstm.setString(1,obj.getExpediente().toUpperCase());
        	lstm.setString(2,obj.getReferenciaEnPid());        	    	
        	lstm.executeUpdate();
        	lstm.close();
        	lconConexion.close();
        }catch(SQLException SQLe){
    		P06BLog.send(0,this, SQLe.getMessage());
	    	throw(SQLe);
        }
        P06BLog.send(2,this, "Fin P06B81T00DAO-deleteByExpCorr");
	}
		
	/**
    * Elimina el documento adjunto asociado a un expediente concreto
    * @param obj.
    *            el documento adjunto a eliminar
    * @throws SQLException
    */
	public void insertAdjuntosExpFromCorrelAtencPetic(String expediente, int correlAtencPetic) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B81T00DAO-insertAllFromCorrelAtencPetic");
		
		PreparedStatement lstm  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = DELETEALL_FROM_CORRELATENCPETIC;
        try{
            lstm = lconConexion.prepareStatement(sql);        	
        	lstm.setString(1,expediente.toUpperCase());
        	lstm.setInt(2,correlAtencPetic);        	    	
        	lstm.executeUpdate();
        	lstm.close();
        }catch(SQLException SQLe){
    		P06BLog.send(0,this, SQLe.getMessage());
	    	throw(SQLe);
        }
        sql = INSERTALL_FROM_CORRELATENCPETIC;
        try{
            lstm = lconConexion.prepareStatement(sql);        	
        	lstm.setString(1,expediente.toUpperCase());
        	lstm.setInt(2,correlAtencPetic);        	    	
        	lstm.executeUpdate();
        	lstm.close();
        	lconConexion.close();
        }catch(SQLException SQLe){
    		P06BLog.send(0,this, SQLe.getMessage());
	    	throw(SQLe);
        }
        P06BLog.send(2,this, "Fin P06B81T00DAO-insertAllFromCorrelAtencPetic");
	}
			
	private static String SELECTALL_BY_EXP =
		"SELECT " +
		P06BTablas.P06B_P06B81_DT_NUMEXP + "," +
		P06BTablas.P06B_P06B64_AP_FECHA + "," +
		P06BTablas.P06B_P06B81_DT_ROLEIDPIDFICHERO + "," +
		P06BTablas.P06B_P06B81_DT_ROLENAMECASPIDFICHERO + "," +
		P06BTablas.P06B_P06B81_DT_ROLENAMEEUSPIDFICHERO + "," +
		"DECODE(?, '" + P06BConstantes.P06B_IDIOMA_EU + "'," + P06BTablas.P06B_P06B81_DT_ROLENAMEEUSPIDFICHERO + "," + P06BTablas.P06B_P06B81_DT_ROLENAMECASPIDFICHERO + ") AS P06B_P06B81_DT_ROLENAMEIDIACT, " +
		P06BTablas.P06B_P06B81_DT_CODPIDFICHERO + "," +
		P06BTablas.P06B_P06B81_DT_NOMBREFICHERO + "," +
		P06BTablas.P06B_P06B81_DT_EXTENSIONFICHERO +
		" FROM " + P06BTablas.P06B_P06B64T00_TABLA + ", " + P06BTablas.P06B_P06B81T00_TABLA 
		+ " WHERE " + P06BTablas.P06B_P06B81_DT_NUMEXP + "= ? AND "
		+ P06BTablas.P06B_P06B64_AP_CORRELATENCPETIC + "=" + P06BTablas.P06B_P06B81_DT_CORRELATENCPETIC
		+ " ORDER BY DECODE(?, '" + P06BConstantes.P06B_IDIOMA_EU + "'," + P06BTablas.P06B_P06B81_DT_ROLENAMEEUSPIDFICHERO + "," + P06BTablas.P06B_P06B81_DT_ROLENAMECASPIDFICHERO + "), " +
		P06BTablas.P06B_P06B64_AP_FECHA + " DESC";
	
	private static String INSERTALL_FROM_CORRELATENCPETIC =
		"INSERT INTO " + P06BTablas.P06B_P06B81T00_TABLA + " (" +  
		P06BTablas.P06B_P06B81_DT_NUMEXP + "," +
		P06BTablas.P06B_P06B81_DT_CORRELATENCPETIC + "," +
		P06BTablas.P06B_P06B81_DT_ROLEIDPIDFICHERO + "," +
		P06BTablas.P06B_P06B81_DT_ROLENAMECASPIDFICHERO + "," +
		P06BTablas.P06B_P06B81_DT_ROLENAMEEUSPIDFICHERO + "," +
		P06BTablas.P06B_P06B81_DT_CODPIDFICHERO + "," +
		P06BTablas.P06B_P06B81_DT_NOMBREFICHERO + "," +
		P06BTablas.P06B_P06B81_DT_EXTENSIONFICHERO +
		") " +
		" SELECT " +
		"?" + "," +
		P06BTablas.P06B_P06B82_DM_CORRELATENCPETIC + "," +
		P06BTablas.P06B_P06B82_DM_ROLEIDPIDFICHERO + "," +
		P06BTablas.P06B_P06B82_DM_ROLENAMECASPIDFICHERO + "," +
		P06BTablas.P06B_P06B82_DM_ROLENAMEEUSPIDFICHERO + "," +
		P06BTablas.P06B_P06B82_DM_CODPIDFICHERO + "," +
		P06BTablas.P06B_P06B82_DM_NOMBREFICHERO + "," +
		P06BTablas.P06B_P06B82_DM_EXTENSIONFICHERO +
		" FROM " + P06BTablas.P06B_P06B82T00_TABLA 
		+ " WHERE " + P06BTablas.P06B_P06B82_DM_CORRELATENCPETIC + "= ?";
	
	private static String DELETEALL_FROM_CORRELATENCPETIC =
		"DELETE FROM " + P06BTablas.P06B_P06B81T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B81_DT_NUMEXP + "= ?" +
		" AND " + P06BTablas.P06B_P06B81_DT_CODPIDFICHERO + " IN (SELECT DISTINCT " + P06BTablas.P06B_P06B82_DM_CODPIDFICHERO + 
		" FROM " + P06BTablas.P06B_P06B82T00_TABLA + " WHERE " + P06BTablas.P06B_P06B82_DM_CORRELATENCPETIC + "= ?)";
	
	private static String DELETE_BYEXPCODPID =
		"DELETE FROM "+ P06BTablas.P06B_P06B81T00_TABLA + 
		" WHERE " + P06BTablas.P06B_P06B81_DT_NUMEXP + " = ?" +
		" AND " + P06BTablas.P06B_P06B81_DT_CODPIDFICHERO + " = ?";
	


}
