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.P06B31T00Datatype;
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 P06B31T00DAO {
	
	static private P06B31T00DAO instance = null;

	public static P06B31T00DAO getInstance() {
	     if (instance == null) {
	          synchronized (P06B31T00DAO.class) {
	          		if (instance == null) {
	                     instance = new P06B31T00DAO();
	                }
	          }
	     }
	     return instance;
	}
	
	/**
    * Inserta un registro de la BD
    * @param obj.
    *            objeto a insertar
    * @return P06B31T00Datatype el documento insertado
    * @throws SQLException
    */
	public P06B31T00Datatype insert(P06B31T00Datatype obj) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B31T00DAO-insert");
		
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        
        //Calcular el siguiente correlativo a asignar
        String correlativo = calcularSteCorrDocAdjunto(lconConexion, obj.getNumExp());
        
        //Calcular la fecha actual
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yyyy");
   		String fechaHoy = simpleDateFormat.format(new Date());
   		
   		obj.setCorr(correlativo);
   		obj.setFechaDoc(fechaHoy);
        
        String sql = INSERT_COMPLETA;
        lstm = lconConexion.prepareStatement(sql);
    	
        lstm.setString(1,obj.getNumExp().toUpperCase());
	    lstm.setString(2,correlativo);
	    lstm.setString(3,fechaHoy);
	    lstm.setString(4,obj.getTitulo());
	    lstm.setString(5,obj.getTipo());
	    
	    //Insertar el dato de tipo BLOB
	    if(obj.getDocumento() == null){
	    	lstm.setNull(6, Types.BLOB);
	    }else{
	    	InputStream is = new ByteArrayInputStream(obj.getDocumento());
	    	lstm.setBinaryStream(6, is, obj.getDocumento().length);
	    }
	    
    	lstm.executeUpdate();
    	//Cierro recursos
    	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) {}
        P06BLog.send(2,this, "Fin P06B31T00DAO-insert");
        return obj;
	}
	
	/**
    * Obtiene la lista de documentos asociados al expediente
    * @param expediente.       
    * @return Lista de objetos de documentos
	 * @throws Exception
    */
	public ArrayList selectAllByExp(String expediente) throws Exception{
		P06BLog.send(2,this, "Inicio P06B31T00Datatype-selectAllByExp");
		ArrayList lista = new ArrayList();
		P06B31T00Datatype 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,expediente.toUpperCase());
        	
        	lrst = lstm.executeQuery();
	        while (lrst.next()) {
	        	byte[] doc = null;
	    		ByteArrayOutputStream bos = new ByteArrayOutputStream();
	    		InputStream is = null;
	    		
	        	obj = new P06B31T00Datatype();
	        	obj.setNumExp(lrst.getString(P06BTablas.P06B_P06B31_NUMEXP));
			    obj.setCorr(lrst.getString(P06BTablas.P06B_P06B31_CORR));
			    obj.setFechaDoc(lrst.getString(P06BTablas.P06B_P06B31_FECHA_DOC)==null?"":P06BFecha.getFechaFormateada(lrst.getDate(P06BTablas.P06B_P06B31_FECHA_DOC),new Locale(P06BConstantes.P06B_IDIOMA_ES)));
			    obj.setTitulo(lrst.getString(P06BTablas.P06B_P06B31_TITULO)==null?"":lrst.getString(P06BTablas.P06B_P06B31_TITULO));
			    obj.setTipo(lrst.getString(P06BTablas.P06B_P06B31_TIPO)==null?"":lrst.getString(P06BTablas.P06B_P06B31_TIPO));
			    
			    //Descargar el dato de tipo BLOB
				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);
	        }
	        P06BLog.send(2,this, "Fin P06B31T00Datatype-selectAllByExp");
	    } 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;
	}

	
	/**
    * Elimina el documento adjunto asociado a un expediente concreto
    * @param obj.
    *            el documento adjunto a eliminar
    * @throws SQLException
    */
	public void deleteByExpCorr(P06B31T00Datatype obj) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B31T00DAO-deleteByExpCorr");
		
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = DELETE_BYEXPCORR;
        lstm = lconConexion.prepareStatement(sql);
    	lstm.setString(1,obj.getNumExp().toUpperCase());
    	lstm.setString(2,obj.getCorr());
    	    	
    	lstm.executeUpdate();
    	//Cierro recursos
    	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) {}
        P06BLog.send(2,this, "Fin P06B31T00DAO-deleteByExpCorr");
	}
	
	
	
	/**
	 * 
	 * Método que calcula el siguiente correlativo a asignar al documento adjunto de un determinado expediente
	 * @throws SQLException
	 * 
	 * */
	private String calcularSteCorrDocAdjunto(Connection lconConexion, String numExp) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B31T00DAO-calcularSteCorrDocAdjunto");
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_MAXCORR_EXPEDI;
        String maxVal = "";
       
        lstm = lconConexion.prepareStatement(sql);
        lstm.setString(1,numExp.toUpperCase());
        
        lrst = lstm.executeQuery();
    	if (lrst.next()) {
    		if(lrst.getString(1)!=null){
	    		maxVal = (new Long(lrst.getString(1)).longValue()+1)+"";
    		}else{
    			maxVal = "1";
    		}
    	}
    	//Cierro recursos
    	try {
    		if(lrst!=null) lrst.close();
    	}catch (Exception rse) {}
        try {
        	if(lstm!=null) lstm.close();
        }catch (Exception sse) {}
        P06BLog.send(2,this, "Fin P06B31T00DAO-calcularSteCorrDocAdjunto");
		return maxVal;
	}
	
	
	private static String SELECT_MAXCORR_EXPEDI = 
		"SELECT MAX(" + P06BTablas.P06B_P06B31_CORR + ") FROM "+ P06BTablas.P06B_P06B31T00_TABLA
		+ " WHERE " + P06BTablas.P06B_P06B31_NUMEXP + "= ?";
	
	
	private static String INSERT_COMPLETA =
		"INSERT INTO "+ P06BTablas.P06B_P06B31T00_TABLA + 
		" ("+
		P06BTablas.P06B_P06B31_NUMEXP + "," +
		P06BTablas.P06B_P06B31_CORR + "," +
		P06BTablas.P06B_P06B31_FECHA_DOC + "," +
		P06BTablas.P06B_P06B31_TITULO + "," +
		P06BTablas.P06B_P06B31_TIPO + "," +
		P06BTablas.P06B_P06B31_DOCUMENTO +
		")"+
		" VALUES(?,TO_NUMBER(?),TO_DATE(?,'dd/MM/yyyy'),?,?,?)";
	
	private static String SELECTALL_BY_EXP =
		"SELECT " +
		P06BTablas.P06B_P06B31_NUMEXP + "," +
		P06BTablas.P06B_P06B31_CORR + "," +
		P06BTablas.P06B_P06B31_FECHA_DOC + "," +
		P06BTablas.P06B_P06B31_TITULO + "," +
		P06BTablas.P06B_P06B31_TIPO + "," +
		P06BTablas.P06B_P06B31_DOCUMENTO + 
		" FROM " + P06BTablas.P06B_P06B31T00_TABLA 
		+ " WHERE " + P06BTablas.P06B_P06B31_NUMEXP + "= ?"
		+" ORDER BY " + P06BTablas.P06B_P06B31_CORR;
	
	private static String DELETE_BYEXPCORR =
		"DELETE FROM "+ P06BTablas.P06B_P06B31T00_TABLA + 
		" WHERE " + P06BTablas.P06B_P06B31_NUMEXP + " = ?" +
		" AND " + P06BTablas.P06B_P06B31_CORR + " = TO_NUMBER(?)";
	


}
