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.model.P06BFCAGeneralDatatype;
import com.ejie.p06b.datatype.model.P06B23T00Datatype;
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;


public class P06BGeneralDAO {

	
	static private P06BGeneralDAO instance = null;

	/**
	 * Metodo que devuelve una instancia de la clase.
	 * De esta forma conseguimos que solo exista una haciendo que la 
	 * sobrecarga en del servidor sea menor. Patrón Singleton
	 */
	public static P06BGeneralDAO getInstance() {
		//si no hay ninguna instancia de esta clase tenemos que crear una
		if (instance == null) {
			// Necesitamos sincronizacion para serializar (no multithread)
			// Las invocaciones de este metodo
			synchronized (P06BGeneralDAO.class) {
				if (instance == null) {
					instance = new P06BGeneralDAO();
				}
			}
		}
		return instance;
	}
	
	
	
	/*
	 * Método que obtiene la lista de provincias de la Base de Datos
	 * @return ArrayList
	*/
	public ArrayList obtenerProvincias (String idioma) throws SQLException{
		
		ArrayList lasProvincias = new ArrayList();
		P06BFCAGeneralDatatype provinciaVO;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection lconConexion = null;
		try {
			
			lconConexion = P06BServidorConexionesBD.getConexionConsultas();
			
			// Generamos la sentencia sql
			String sql =  SQLProv;
			
			pstm = lconConexion.prepareStatement(sql);
			rs = pstm.executeQuery(sql);
		
			while(rs.next()){
				if(idioma.equals(P06BConstantes.P06B_IDIOMA_ES)){
					provinciaVO = new P06BFCAGeneralDatatype (
						rs.getInt(1),
						rs.getString(2)
					);
				}else{
					provinciaVO = new P06BFCAGeneralDatatype (
							rs.getInt(1),
							rs.getString(3)
						);
				}
				lasProvincias.add(provinciaVO);
			}
		}  catch (SQLException e) {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        P06BLog.send(2,this, "P06BGeneralDAO-obtenerProvincias"+e.getMessage());
	        throw e;
		} finally {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
		}		
		return lasProvincias;
		
	}

	/*
	 * Método que obtiene la lista de aseguradoras de la Base de Datos
	 * @return ArrayList
	*/
	public ArrayList obtenerAseguradoras (String idioma) throws SQLException{
		
		ArrayList lasAseguradoras = new ArrayList();
		P06B23T00Datatype aseguradoraVO;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection lconConexion = null;
		try {
			
			lconConexion = P06BServidorConexionesBD.getConexionConsultas();
			
			// Generamos la sentencia sql
			String sql =  SQLAseg;
			
			pstm = lconConexion.prepareStatement(sql);
			rs = pstm.executeQuery(sql);
		
			while(rs.next()){
				if(idioma.equals(P06BConstantes.P06B_IDIOMA_ES)){
					aseguradoraVO = new P06B23T00Datatype (
						rs.getString(1),
						rs.getString(2)
					);
				}else{
					aseguradoraVO = new P06B23T00Datatype (
							rs.getString(1),
							rs.getString(3)
						);
				}
				lasAseguradoras.add(aseguradoraVO);
			}
		}  catch (SQLException e) {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        P06BLog.send(2,this, "P06BGeneralDAO-obtenerAseguradoras"+e.getMessage());
	        throw e;
		} finally {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
		}		
		return lasAseguradoras;
		
	}

	/*
	 * Método que obtiene la localidad FCA a partir de la localidad NORA
	 * Eliminar cuando se convierta el código de localidad
	 * Supone que la columna NUMERO en la tabla de entidades de NORA
	 * corresponde con la localidad FCA.
	 * @return ArrayList
	*/
	public String sustituirLocNORAPorLocFCA (String localidadNORA){
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection lconConexion = null;
		String sLocFCA = "";
		try {
			
			lconConexion = P06BServidorConexionesBD.getConexionConsultas();
			
			// Generamos la sentencia sql
			String sql =  SQLSustLocNORA;
			
			pstm = lconConexion.prepareStatement(sql);
			pstm.setString(1,localidadNORA);			
			rs = pstm.executeQuery();
		
			if(rs.next()){
				sLocFCA = ((Integer)rs.getInt(1)).toString();
			}
		}catch (SQLException e) {
	        P06BLog.send(2,this, "P06BGeneralDAO-sustituirLocNORAPorLocFCA"+e.getMessage());
	        //throw e;
		}catch (Exception e) {
	        P06BLog.send(2,this, "P06BGeneralDAO-sustituirLocNORAPorLocFCA"+e.getMessage());
	        //throw e;
		} finally {
			try{
				if(rs!=null)
	    			rs.close();
	        	if(pstm!=null)
	        		pstm.close();
	        	if(lconConexion!=null)
	        		lconConexion.close();				
			}catch (SQLException e){
		        P06BLog.send(2,this, "P06BGeneralDAO-sustituirLocNORAPorLocFCA"+e.getMessage());
			}
		}		
    	return sLocFCA;
	}
	
	
	/*
	 * Método que obtiene la lista de municipios de la Base de datos en función de un código de provincia
	 * @return ArrayList
	*/
	public ArrayList obtenerMunicipios (int codProvincia) throws SQLException{
		
		ArrayList losMunicipios = new ArrayList();
		P06BFCAGeneralDatatype municipioVO;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection lconConexion = null;
		
		try {
			
			lconConexion = P06BServidorConexionesBD.getConexionConsultas();
			
			// Generamos la sentencia sql
			String sql =  SQLMun;
			
			pstm = lconConexion.prepareStatement(sql);
			pstm.setString(1,(codProvincia+""));
			rs = pstm.executeQuery();
		
			 while(rs.next()){
				municipioVO = new P06BFCAGeneralDatatype (
					rs.getInt(2),
					rs.getString(3)
				);
				losMunicipios.add(municipioVO);
			}
		}  catch (SQLException e) {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        P06BLog.send(2,this, "P06BGeneralDAO-obtenerMunicipios"+e.getMessage());
	        throw e;
		} finally {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
		}		
		return losMunicipios;
		
	}
	
	
	/*
	 * Método que obtiene la lista de localidad de la Base de datos en función de un código de provincia y municipio
	 * @return ArrayList
	*/
	public ArrayList obtenerLocalidades (int codProvincia, int codMunicipio) throws SQLException{
		
		ArrayList lasLocalidades = new ArrayList();
		P06BFCAGeneralDatatype localidadVO;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection lconConexion = null;
		
		try {
			
			lconConexion = P06BServidorConexionesBD.getConexionConsultas();
			
			// Generamos la sentencia sql
			String sql =  SQLLoc;
			
			pstm = lconConexion.prepareStatement(sql);
			pstm.setString(1,(codProvincia+""));
			pstm.setString(2,(codMunicipio+""));
			rs = pstm.executeQuery();
		
			 while(rs.next()){
				 localidadVO = new P06BFCAGeneralDatatype (
						 rs.getInt(3),
						 rs.getString(4));
				 lasLocalidades.add(localidadVO);
			}
		}  catch (SQLException e) {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        P06BLog.send(2,this, "P06BGeneralDAO-obtenerLocalidades"+e.getMessage());
	        throw e;
		} finally {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
		}		
		return lasLocalidades;
		
	}
	
	/*
	 * Método que obtiene la lista de códigos postales de la Base de datos en función de un código de provincia,municipio
	 * y localidad
	 * @return ArrayList
	*/
	public ArrayList obtenerCodPostales (int codProvincia, int codMunicipio, int codLocalidad) throws SQLException{
		
		ArrayList losCodPostales = new ArrayList();
		String codPostal = "";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection lconConexion = null;
		
		try {
			
			lconConexion = P06BServidorConexionesBD.getConexionConsultas();
			
			// Generamos la sentencia sql
			String sql =  SQLCodPost;
			
			pstm = lconConexion.prepareStatement(sql);
			pstm.setString(1,(codProvincia+""));
			pstm.setString(2,(codMunicipio+""));
			pstm.setString(3,(codLocalidad+""));
			rs = pstm.executeQuery();
		
			 while(rs.next()){
				 codPostal = rs.getString(1);				
				 losCodPostales.add(codPostal);
			}
		}  catch (SQLException e) {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        P06BLog.send(2,this, "P06BGeneralDAO-obtenerCodPostales"+e.getMessage());
	        throw e;
		} finally {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
		}		
		return losCodPostales;
	}
	
	
	/*
	 * Método que obtiene la lista de paises de la Base de Datos
	 * @return ArrayList
	*/
	public ArrayList obtenerPaises (String idioma) throws SQLException{
		
		ArrayList losPaises = new ArrayList();
		P06BFCAGeneralDatatype paisVO;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection lconConexion = null;
		try {
			
			lconConexion = P06BServidorConexionesBD.getConexionConsultas();
			
			// Generamos la sentencia sql
			String sql =  SQLPais;
			
			pstm = lconConexion.prepareStatement(sql);
			rs = pstm.executeQuery(sql);
		
			while(rs.next()){
				if(idioma.equals(P06BConstantes.P06B_IDIOMA_ES)){
					paisVO = new P06BFCAGeneralDatatype (
						rs.getInt(1),
						rs.getString(2)
					);
				}else{
					paisVO = new P06BFCAGeneralDatatype (
							rs.getInt(1),
							rs.getString(3)
						);
				}
				losPaises.add(paisVO);
			}
		}  catch (SQLException e) {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        P06BLog.send(2,this, "P06BGeneralDAO-obtenerPaises"+e.getMessage());
	        throw e;
		} finally {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
		}		
		return losPaises;
		
	}
	
	
	/*
	 * Método que obtiene el nombre de la provincia de la Base de Datos
	 * @return String
	*/
	public String obtenerNomProv(String idioma, String codProv) throws SQLException{
		String nomProv = "";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection lconConexion = null;
		try {
			lconConexion = P06BServidorConexionesBD.getConexionConsultas();
			
			// Generamos la sentencia sql
			String sql =  SQLNomProv;

			pstm = lconConexion.prepareStatement(sql);
			pstm.setString(1,codProv);			
			rs = pstm.executeQuery();
		
			if(rs.next()){				
				if(idioma.equals(P06BConstantes.P06B_IDIOMA_ES)){
					nomProv = rs.getString(1);
				}else{
					nomProv = rs.getString(2);
				}
			}			
		}  catch (SQLException e) {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        P06BLog.send(2,this, "P06BGeneralDAO-obtenerNomProv"+e.getMessage());
	        throw e;
		} finally {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
		}		
		return nomProv;
		
	}
	
	
	/*
	 * Método que obtiene el nombre del municipio de la Base de Datos
	 * @return String
	*/
	public String obtenerNomMun(String codProv, String codMun) throws SQLException{
		String nomMun = "";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection lconConexion = null;
		try {
			lconConexion = P06BServidorConexionesBD.getConexionConsultas();
			
			// Generamos la sentencia sql
			String sql =  SQLNomMun;
			pstm = lconConexion.prepareStatement(sql);
			pstm.setString(1,codProv);
			pstm.setString(2,codMun);
			rs = pstm.executeQuery();
		
			if(rs.next()){
				nomMun = rs.getString(1);
			}
		}  catch (SQLException e) {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        P06BLog.send(2,this, "P06BGeneralDAO-obtenerNomMun"+e.getMessage());
	        throw e;
		} finally {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
		}		
		return nomMun;
		
	}
	
	
	/*
	 * Método que obtiene el nombre de la localidad de la Base de Datos
	 * @return String
	*/
	public String obtenerNomLoc(String codProv, String codMun, String codLoc) throws SQLException{
		String nomLoc = "";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Connection lconConexion = null;
		try {
			lconConexion = P06BServidorConexionesBD.getConexionConsultas();
			
			// Generamos la sentencia sql
			String sql =  SQLNomLoc;
			pstm = lconConexion.prepareStatement(sql);
			pstm.setString(1,codProv);
			pstm.setString(2,codMun);
			pstm.setString(3,codLoc);
			rs = pstm.executeQuery();
		
			if(rs.next()){
				nomLoc = rs.getString(1);
			}
		}  catch (SQLException e) {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
	        P06BLog.send(2,this, "P06BGeneralDAO-obtenerNomLoc"+e.getMessage());
	        throw e;
		} finally {
			try {
        		if(rs!=null)
        			rs.close();
        	}catch (Exception rse) {}
	        try {
	        	if(pstm!=null)
	        		pstm.close();
	        }catch (Exception sse) {}
	        try {
	        	if(lconConexion!=null)
	        		lconConexion.close();
	        }catch (Exception cse) {}
		}		
		return nomLoc;
	}
		
	
	private String SQLAseg = "SELECT P06B23_EA_CLAVE, P06B23_EA_ENTIDAD AS P06B23_EA_ENTIDADC, P06B23_EA_ENTIDAD AS P06B23_EA_ENTIDADE FROM "+ P06BTablas.P06B_P06B23T00_TABLA + " ORDER BY decode(P06B23_EA_CIF,NULL,'0','1') DESC, P06B23_EA_ENTIDAD";
	//private String SQLPais = "SELECT CODPAIS, NOMPAIS, NOMPAISE FROM FCA03S00 ORDER BY NOMPAIS";
	private String SQLPais = "SELECT ID, UPPER(DS_C), UPPER(DS_E) FROM P06BPAS00 ORDER BY DS_O";
	//private String SQLProv = "SELECT CODPROVINC, NOMPROVINC, NOMPROVINCE FROM FCA01S00 ORDER BY NOMPROVINC";
	private String SQLProv = "SELECT TO_NUMBER(ID), UPPER(NVL(DS_C,DS_O)), UPPER(NVL(DS_E,DS_O)) FROM P06BPRS00 ORDER BY UPPER(DS_O)";
	//private String SQLNomProv = "SELECT NOMPROVINC, NOMPROVINCE FROM FCA01S00 WHERE CODPROVINC=?";
	private String SQLNomProv = "SELECT UPPER(NVL(DS_C,DS_O)), UPPER(NVL(DS_E,DS_O)) FROM P06BPRS00 WHERE TO_NUMBER(ID)=?";
	//private String SQLMun = "SELECT CODPROVINC, CODMUNICIP, NOMMUNICIP FROM FCA02S00 WHERE CODPROVINC=? ORDER BY NOMMUNICIP";
	private String SQLMun = "SELECT TO_NUMBER(PROVINCIA_ID), TO_NUMBER(ID), UPPER(DS_O) FROM P06BMUS00 WHERE TO_NUMBER(PROVINCIA_ID)=? ORDER BY UPPER(DS_O)";
	//private String SQLNomMun = "SELECT NOMMUNICIP FROM FCA02S00 WHERE CODPROVINC=? AND CODMUNICIP=?";
	private String SQLNomMun = "SELECT UPPER(DS_O) FROM P06BMUS00 WHERE TO_NUMBER(PROVINCIA_ID)=? AND TO_NUMBER(ID)=?";
	//private String SQLLoc = "SELECT CODPROVINC, CODMUNICIP, CODLOCALID, NOMLOCALID FROM FCA04S00 WHERE CODPROVINC=? AND CODMUNICIP=? ORDER BY NOMLOCALID";
	private String SQLLoc = "SELECT TO_NUMBER(PROVINCIA_ID), TO_NUMBER(MUNICIPIO_ID), TO_NUMBER(NUMERO), UPPER(DS_O) FROM P06BENS00 WHERE TO_NUMBER(PROVINCIA_ID)=? AND TO_NUMBER(MUNICIPIO_ID)=? ORDER BY UPPER(DS_O)";
	//private String SQLNomLoc = "SELECT NOMLOCALID FROM FCA04S00 WHERE CODPROVINC=? AND CODMUNICIP=? AND CODLOCALID=?";
	private String SQLNomLoc = "SELECT UPPER(DS_O) FROM P06BENS00 WHERE TO_NUMBER(PROVINCIA_ID)=? AND TO_NUMBER(MUNICIPIO_ID)=? AND TO_NUMBER(NUMERO)=?";
	//private String SQLCodPost = "SELECT DISTINCT(CODPOSTNUM) FROM FCA11S00 WHERE CODPROVINC=? AND CODMUNICIP=? AND CODLOCALID=? ORDER BY CODPOSTNUM";
	private String SQLCodPost = "select DISTINCT(C.CODIGO_POSTAL) from P06BTRS00 C, P06BLOS00 B, P06BENS00 A WHERE TO_NUMBER(A.PROVINCIA_ID)=? AND TO_NUMBER(A.MUNICIPIO_ID)=? AND TO_NUMBER(A.NUMERO)=? AND B.ENTIDAD_ID = A.ID AND C.LOCALIDAD_ID = B.ID AND C.CODIGO_POSTAL IS NOT NULL ORDER BY 1";
	//private String SQLCodPost = "SELECT DISTINCT(C.CODIGO_POSTAL) from P06BTRS00 C, P06BLOS00 B, P06BNUS00 D, P06BENS00 A WHERE TO_NUMBER(A.PROVINCIA_ID)=? AND TO_NUMBER(A.MUNICIPIO_ID)=? AND TO_NUMBER(A.NUMERO)=? AND D.ENTIDAD_ID = A.ID AND D.NUMERO != 99 AND B.ENTIDAD_ID = D.ENTIDAD_ID AND C.LOCALIDAD_ID = B.ID AND C.CODIGO_POSTAL IS NOT NULL ORDER BY 1";
	//private String SQLCodPost = "SELECT DISTINCT CODIGO_POSTAL from P06BTRS00 WHERE CALLE_ID IN (SELECT DISTINCT ID FROM P06BCAS00 WHERE TO_NUMBER(PROVINCIA_ID)=? AND TO_NUMBER(MUNICIPIO_ID)=?) ORDER BY 1";
	private String SQLSustLocNORA = "SELECT TO_NUMBER(E.NUMERO) FROM P06BLOS00 L, P06BENS00 E WHERE E.ID = L.ENTIDAD_ID AND L.ID = ?";
}