package m05bClasses;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.StringTokenizer;

import javax.naming.InitialContext;

public class m05bCamposInformes {
	m05bFileLog miLog = new m05bFileLog();
	// Marca y Modelo
    public String []  getDatosVehiculo (String matricula) throws javax.servlet.ServletException, java.io.IOException{
        String Vehiculo [];
        Vehiculo= new String[2];
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		javax.sql.DataSource ds = null;

		try{
			InitialContext ctx2 = new InitialContext();
			ds = (javax.sql.DataSource) ctx2.lookup("m05b.m05bDataSource");
			
			m05bConn = ds.getConnection();
			
			sql = "select "+ m05bConstantes.COL_99_DESCRIPCION + ", " + m05bConstantes.COL_17_DESCRIP ;
            sql = sql + " from  " + m05bConstantes.TAB_DESCRIPCIONES + ", " + m05bConstantes.TAB_MODELOS + ", " + m05bConstantes.TAB_VEHICULOS;
            sql = sql + " where " + m05bConstantes.COL_34_MODELO + " = " + m05bConstantes.COL_17_CODMOD;
            sql = sql + " and " + m05bConstantes.COL_99_CODTABLA + " = " + m05bConstantes.DESC_MARCAS +
            			" and " + m05bConstantes.COL_99_CODDESC + " = " + m05bConstantes.COL_34_MARCA ;
            sql = sql + " and " + m05bConstantes.COL_34_MATRICULA + " = '" + matricula + "' ";

            
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			while (m05bRS.next() == true){
	            Vehiculo[0]=m05bRS.getString(1);
	            Vehiculo[1]=m05bRS.getString(2);
			}
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		}
		finally {
			try{
				if (m05bRS != null)
					m05bRS.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
 			} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}				
 		} 
		return Vehiculo;
    }  // Fin funcion getDatosVehiculo
    

    public String  getTarjeta (String matricula) throws javax.servlet.ServletException, java.io.IOException{
        String tarjeta="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		javax.sql.DataSource ds = null;

		try{
			InitialContext ctx2 = new InitialContext();
			ds = (javax.sql.DataSource) ctx2.lookup("m05b.m05bDataSource");
			
			m05bConn = ds.getConnection();
			
			sql = "select "+ m05bConstantes.COL_31_TARJETA;
            sql = sql + " from  "+ m05bConstantes.TAB_TARJETAS;
            sql = sql + " where " + m05bConstantes.COL_31_MATRICULA + " = '" + matricula + "' and " + m05bConstantes.COL_31_TIPO +"='G'";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			while (m05bRS.next() == true){
	            tarjeta=m05bRS.getString(1);
			}
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		}
		finally {
			try{
				if (m05bRS != null)
					m05bRS.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
 			} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}				
 		} 
		return tarjeta;
    }  // Fin funcion getTarjeta


    public String  getViaT (String matricula) throws javax.servlet.ServletException, java.io.IOException{
        String tarjeta="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		javax.sql.DataSource ds = null;

		try{
			InitialContext ctx2 = new InitialContext();
			ds = (javax.sql.DataSource) ctx2.lookup("m05b.m05bDataSource");
			
			m05bConn = ds.getConnection();
			
			sql = "select "+ m05bConstantes.COL_31_TARJETA;
            sql = sql + " from  "+ m05bConstantes.TAB_TARJETAS;
            sql = sql + " where " + m05bConstantes.COL_31_MATRICULA + " = '" + matricula + "' and " + m05bConstantes.COL_31_TIPO +"='V'";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			while (m05bRS.next() == true){
	            tarjeta=m05bRS.getString(1);
			}
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		}
		finally {
			try{
				if (m05bRS != null)
					m05bRS.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
 			} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}				
 		} 
		return tarjeta;
    }  // Fin funcion getTarjeta    
    
    public String  getCargoEusk (String codCargo) throws javax.servlet.ServletException, java.io.IOException{
        String ecargo="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		javax.sql.DataSource ds = null;

		try{
			InitialContext ctx2 = new InitialContext();
			ds = (javax.sql.DataSource) ctx2.lookup("m05b.m05bDataSource");
			
			m05bConn = ds.getConnection();
			
			sql = "select "+ m05bConstantes.COL_04_EDESCCARG;
            sql = sql + " from  "+ m05bConstantes.TAB_CARGOS;
            sql = sql + " where " + m05bConstantes.COL_04_CODCARGO + " = '" + codCargo + "' ";
            
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			while (m05bRS.next() == true){
	            ecargo=m05bRS.getString(1);
			}
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		}
		finally {
			try{
				if (m05bRS != null)
					m05bRS.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
 			} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}				
 		} 
		return ecargo;
    }  // Fin funcion getCargoEusk
    public String  getDepartEusk (String codDepart) throws javax.servlet.ServletException, java.io.IOException{
        String edepart="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		javax.sql.DataSource ds = null;

		try{
			InitialContext ctx2 = new InitialContext();
			ds = (javax.sql.DataSource) ctx2.lookup("m05b.m05bDataSource");
			
			m05bConn = ds.getConnection();
			
			sql = "select "+ m05bConstantes.COL_06_EDESCDPTO;
            sql = sql + " from  "+ m05bConstantes.TAB_DEPARTAMENTOS;
            sql = sql + " where " + m05bConstantes.COL_06_CODDPTO + " = '" + codDepart + "' ";
            
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			while (m05bRS.next() == true){
	            edepart=m05bRS.getString(1);
			}
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		}
		finally {
			try{
				if (m05bRS != null)
					m05bRS.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
 			} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}				
 		} 
		return edepart;
    }  // Fin funcion getDepartEusk

    public String  getFirma (String cargo) throws javax.servlet.ServletException, java.io.IOException{
        String nombre="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		javax.sql.DataSource ds = null;

		try{
			InitialContext ctx2 = new InitialContext();
			ds = (javax.sql.DataSource) ctx2.lookup("m05b.m05bDataSource");
			
			m05bConn = ds.getConnection();
			
			sql = "select "+ m05bConstantes.COL_26_NOMBRE;
            sql = sql + " from  "+ m05bConstantes.TAB_FIRMAS;
            sql = sql + " where " + m05bConstantes.COL_26_CARGO + " = '" + cargo + "' ";
            
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			while (m05bRS.next() == true){
	            nombre=m05bRS.getString(1);
			}
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		}
		finally {
			try{
				if (m05bRS != null)
					m05bRS.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
 			} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}				
 		} 
		return nombre;
    }  // Fin funcion getFirma

	// Marca y Modelo
    public String []  getDatosTarjetaBanco (String codhistarjeta) throws javax.servlet.ServletException, java.io.IOException{
        String Tarjeta [];
        Tarjeta= new String[4];
        String Direccion="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		javax.sql.DataSource ds = null;

		try{
			InitialContext ctx2 = new InitialContext();
			ds = (javax.sql.DataSource) ctx2.lookup("m05b.m05bDataSource");
			
			m05bConn = ds.getConnection();
			sql = "select " + m05bConstantes.COL_03_NOMBRE + ", translate(" + m05bConstantes.COL_03_DIRECCION+",CHR(10)||CHR(13),'@')"  ;
            sql = sql + " from  " + m05bConstantes.TAB_BANCOS + ", " + m05bConstantes.TAB_HIST_TARJETAS;
            sql = sql + " where " + m05bConstantes.COL_14_BANCO + " = " + m05bConstantes.COL_03_NCUENTA;
            sql = sql + " and " + m05bConstantes.COL_14_TARJHIST + " = '" + codhistarjeta + "' ";
            
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			while (m05bRS.next() == true){
	            Tarjeta[0]=m05bRS.getString(1);
	            Direccion=m05bRS.getString(2);
		        StringTokenizer st = new StringTokenizer(Direccion,"@");
		        //"Nombre"
	            if (st.hasMoreTokens()) { 
                  Tarjeta[1]=(String)st.nextToken();
	            }
	            //"Calle"
	            if (st.hasMoreTokens()) { 
                  Tarjeta[2]=(String)st.nextToken();
	            }
	            //"Codigo Postal"
	            if (st.hasMoreTokens()) { 
                  Tarjeta[3]=(String)st.nextToken();
	            }
			}
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		}
		finally {
			try{
				if (m05bRS != null)
					m05bRS.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
 			} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}				
 		} 
		return Tarjeta;
    }  // Fin funcion getDatosVehiculo


}