package com.ejie.p06b.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Locale;

import com.ejie.p06b.datatype.model.P06B62T00Datatype;
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: (12/04/2017)
 */
public class P06B62T00DAO {
	
	static private P06B62T00DAO instance = null;

	public static P06B62T00DAO getInstance() {
	     if (instance == null) {
	          synchronized (P06B62T00DAO.class) {
	          		if (instance == null) {
	                     instance = new P06B62T00DAO();
	                }
	          }
	     }
	     return instance;
	}

	/**
    * Obtiene un registro de la BD a partir del código
    * @param codigoPK.
    *            clave primaria
    * @return un objeto P06B62T00Datatype o null si no se encuentra
    * nada.
	 * @throws Exception
    */
	public P06B62T00Datatype select(int codigoPK) throws Exception{
		P06BLog.send(2,this, "Inicio P06B62T00Datatype-select");
		P06B62T00Datatype result = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_BY_PK;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lstm.setInt(1, codigoPK);
        	lrst = lstm.executeQuery();
	        if (lrst.next()) {
	        	result = new P06B62T00Datatype();
	        	result.setEjercicio(codigoPK);
	        	result.setFechaIni(lrst.getDate(P06BTablas.P06B_P06B62_RD_FECHAINI));
	        	result.setFechaFin(lrst.getDate(P06BTablas.P06B_P06B62_RD_FECHAFIN));
	        }
	        P06BLog.send(2,this, "Fin P06B62T00Datatype-select");
	    }catch (SQLException e){
	    	P06BLog.send(2,this, "error: " +e.getMessage());
	        //Propago la excepción
	        throw e;
	    }catch (Exception e2){	 
        	P06BLog.send(2,this, "error: " +e2.getMessage());
	        //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 la lista de periodos
    * @return Lista de objetos periodo
	 * @throws Exception
    */
	public ArrayList selectAll() throws Exception{
		P06BLog.send(2,this, "Inicio P06B62T00Datatype-selectAll");
		ArrayList lista = new ArrayList();
		P06B62T00Datatype obj = null;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_ALL;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        try {
        	lstm = lconConexion.prepareStatement(sql);
        	lrst = lstm.executeQuery();
	        while (lrst.next()) {
	        	obj = new P06B62T00Datatype();
	        	obj.setEjercicio(lrst.getInt(P06BTablas.P06B_P06B62_RD_EJERCICIO));
	        	obj.setFechaIni(lrst.getDate(P06BTablas.P06B_P06B62_RD_FECHAINI));
	        	obj.setFechaFin(lrst.getDate(P06BTablas.P06B_P06B62_RD_FECHAFIN));
	        	lista.add(obj);
	        }
	        P06BLog.send(2,this, "Fin P06B62T00Datatype-selectAll");
	    } catch (SQLException e) {
	    	P06BLog.send(2,this, "error: " +e.getMessage());
	        //Propago la excepción
	        throw e;
	    }catch (Exception e2) {	 
        	P06BLog.send(2,this, "error: " +e2.getMessage());
	        //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 el periodo de un ejercicio determinado
    * @param ejercicio          
    * @return P06B62T00Datatype
	* @throws Exception
    */
	public P06B62T00Datatype recepcionDECAbierta(int ejercicio) throws Exception{
		P06BLog.send(2,this, "Inicio P06B62T00DAO-recepcionDECAbierta");
		P06B62T00Datatype result = select(ejercicio);
		P06BLog.send(2,this, "Inicio P06B62T00DAO-recepcionDECAbierta");
		return result;
	}
	
	/**
    * Inserta un registro de la BD
    * @param obj.
    *            objeto a insertar
    * @return void
    * @throws SQLException
    */
	public void insert(P06B62T00Datatype obj) throws SQLException{
		P06BLog.send(2,this, "Inicio P06B62T00DAO-insert");
		
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = INSERT_COMPLETA;
        lstm = lconConexion.prepareStatement(sql);
    	
        lstm.setInt(1, obj.getEjercicio());
        String sFechaIni = P06BFecha.getFecha(obj.getFechaIni());
        String sFechaFin = P06BFecha.getFecha(obj.getFechaFin());
//        lstm.setString(2, sFechaIni);
//        lstm.setString(3, sFechaFin);
        lstm.setDate(2, new java.sql.Date(obj.getFechaIni().getTime()));
        lstm.setDate(3, new java.sql.Date(obj.getFechaFin().getTime()));
    	
    	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 P06B62T00DAO-insert");
	}

	/**
    * Elimina todos los socios asociados a un expediente concreto
    * @param exp.
    *            el expediente cuyos socios eliminamos
    * @param tipo 1:socio directo 2:socio indirecto 3:auxiliar externo
    * @throws SQLException
    */
	public void deleteAll() throws SQLException{
		P06BLog.send(2,this, "Inicio P06B62T00DAO-deleteAll");
		
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        String sql = DELETEALL;
        try{
            lstm = lconConexion.prepareStatement(sql);
        	lstm.executeUpdate();
        }catch(SQLException e){
	    	P06BLog.send(2,this, "error: " +e.getMessage());
	        //Propago la excepción
	        throw e;
        }finally{
    		if(lrst!=null)
    	        try{
        			lrst.close();
    	        }
    	        catch(SQLException e){
    		    	P06BLog.send(2,this, "error: " +e.getMessage());
    		        //Propago la excepción
    		        throw e;
    	        }
        	if(lstm!=null)
    	        try{
            		lstm.close();
    	        }
    	        catch(SQLException e){
    		    	P06BLog.send(2,this, "error: " +e.getMessage());
    		        //Propago la excepción
    		        throw e;
    	        }
        	if(lconConexion!=null)
    	        try{
            		lconConexion.close();
    	        }
    	        catch(SQLException e){
    		    	P06BLog.send(2,this, "error: " +e.getMessage());
    		        //Propago la excepción
    		        throw e;
    	        }
        }
        P06BLog.send(2,this, "Fin P06B62T00DAO-deleteAll");
	}
			
	/**
    * Obtiene el número de periodos
    * @return int con el número de periodos encontrados
	* @throws SQLException
    */
	public int selectNumObj() throws SQLException{
		P06BLog.send(2,this, "Inicio P06B62T00Datatype-selectNumObj");
		int numRegsEncontrados = 0;
		PreparedStatement lstm  = null;
        ResultSet lrst  = null;
        String sql = SELECT_NUMREGS;
        Connection lconConexion = P06BServidorConexionesBD.getConexionConsultas();
        
        try{
        	lstm = lconConexion.prepareStatement(sql);
        	lrst = lstm.executeQuery();
	        if (lrst.next()) {
	        	numRegsEncontrados = lrst.getInt(1);
	        }
	    }catch (SQLException e) {
	    	P06BLog.send(2,this, "error: " +e.getMessage());
	        //Propago la excepción
	        throw e;
        }finally{
    		if(lrst!=null)
    	        try{
        			lrst.close();
    	        }
    	        catch(SQLException e){
    		    	P06BLog.send(2,this, "error: " +e.getMessage());
    		        //Propago la excepción
    		        throw e;
    	        }
        	if(lstm!=null)
    	        try{
            		lstm.close();
    	        }
    	        catch(SQLException e){
    		    	P06BLog.send(2,this, "error: " +e.getMessage());
    		        //Propago la excepción
    		        throw e;
    	        }
        	if(lconConexion!=null)
    	        try{
            		lconConexion.close();
    	        }
    	        catch(SQLException e){
    		    	P06BLog.send(2,this, "error: " +e.getMessage());
    		        //Propago la excepción
    		        throw e;
    	        }
        }
        P06BLog.send(2,this, "Fin P06B62T00Datatype-selectNumObj");
		return numRegsEncontrados;
	}
			
	private static String DELETEALL =
		"DELETE FROM "+ P06BTablas.P06B_P06B62T00_TABLA;
	
	
	private static String SELECT_BY_PK =
		"SELECT " +		
		P06BTablas.P06B_P06B62_RD_FECHAINI + ", " + P06BTablas.P06B_P06B62_RD_FECHAFIN +
		" FROM P06B."+ P06BTablas.P06B_P06B62T00_TABLA +
		" WHERE " + P06BTablas.P06B_P06B62_RD_EJERCICIO + " = ?";
	
	private static String SELECT_ALL =
		"SELECT " +		
		P06BTablas.P06B_P06B62_RD_EJERCICIO + ", " + P06BTablas.P06B_P06B62_RD_FECHAINI + ", " + P06BTablas.P06B_P06B62_RD_FECHAFIN +
		" FROM P06B."+ P06BTablas.P06B_P06B62T00_TABLA;

	private static String INSERT_COMPLETA =
		"INSERT INTO "+ P06BTablas.P06B_P06B62T00_TABLA + 
		"("+
		P06BTablas.P06B_P06B62_RD_EJERCICIO + "," +
		P06BTablas.P06B_P06B62_RD_FECHAINI + "," +
		P06BTablas.P06B_P06B62_RD_FECHAFIN +
		")"+		
		" VALUES(?,?,?)";
//		" VALUES(TO_NUMBER(?),TO_DATE(?,'dd/MM/yyyy'),TO_DATE(?,'dd/MM/yyyy'))";
	
	private static String SELECT_NUMREGS =
		"SELECT COUNT(*) "
		+" FROM " + P06BTablas.P06B_P06B62T00_TABLA;

}
