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


public class m05bDescrip {
	m05bFileLog miLog = new m05bFileLog();
	m05bConexion miConex = new m05bConexion();
	public String getDescripcion (String tabla, String codigo )throws javax.servlet.ServletException, java.io.IOException {
		return getDescripcion ( tabla,  codigo,m05bConstantesInformes.IDIOMA_C );
	}
/*
    * Devuelve como string la descripción del codigo de marca,color, etc  
*/
	public String getDescripcion (String tabla, String codigo,String idioma )throws javax.servlet.ServletException, java.io.IOException { 

		String descripcion="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		String campoTexto="";
		if (!idioma.equals(m05bConstantesInformes.IDIOMA_C))
			campoTexto="E";
		try{
			m05bConn = miConex.getConnection();
			
			sql="select NVL("+campoTexto+ m05bConstantes.COL_99_DESCRIPCION
					+","+m05bConstantes.COL_99_DESCRIPCION+
					") as "+campoTexto+ m05bConstantes.COL_99_DESCRIPCION+
					" from "+m05bConstantes.TAB_DESCRIPCIONES+
					" where "+m05bConstantes.COL_99_CODTABLA+" = " +tabla +
					" and "+ m05bConstantes.COL_99_CODDESC+"= '"+codigo+"'";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
				descripcion = m05bRS.getString(campoTexto+m05bConstantes.COL_99_DESCRIPCION);
			}	
 		} 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 descripcion;		
	}  // Fin funcion getDescripcion
		
		
/*
    * Devuelve como string la descripciOn para el resto de objetos 
    * que no son de la tabla de descripciones 
    * y la columna 'codigo' es numerica
*/
	public String getDetalle (String tabla, String codigo, String descripcion, String clave )throws javax.servlet.ServletException, java.io.IOException {

		String detalle="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";

		try{
			m05bConn = miConex.getConnection();
			
			sql="select "+ descripcion+
					" from " + tabla+
					" where " + codigo + " = " + clave ;
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
				detalle = 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 detalle;		
	}  // Fin funcion getDetalle
		
		
/*
    * Devuelve como string la descripciOn para el resto de objetos 
    * que no son de la tabla de descripciones 
    * y la columna 'codigo' es string
*/
	public String getDetalleStr (String tabla, String codigo, String descripcion, String clave )throws javax.servlet.ServletException, java.io.IOException {

		String detalle="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";

		try{
			m05bConn = miConex.getConnection();
			
			sql="select "+ descripcion+
					" from " + tabla+
					" where " + codigo + " = '" + clave + "'";
		//	System.out.println(sql);
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
				detalle = 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 detalle;		
	}  // Fin funcion getDetalleStr
		
		
		
 /*   public String getNombre (String dni) throws javax.servlet.ServletException, java.io.IOException{


        String nombre="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		
		try{
			m05bConn = miConex.getConnection();
			
			sql="select "+ m05bConstantes.COL_21_NOMBRE ;
            sql=sql+ " from  "+m05bConstantes.TAB_PERSONAL;
            sql=sql+" where "+m05bConstantes.COL_21_DNI;
            sql = sql+" = '" + dni + "'";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
				nombre = m05bRS.getString(m05bConstantes.COL_21_NOMBRE );
			}
 		} 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 getNombre  */
	  public String []  getPropVehiculo (String matricula) throws javax.servlet.ServletException, java.io.IOException{
		  return getPropVehiculo(matricula,"");
	  }
    
    public String []  getPropVehiculo (String matricula,String idioma) throws javax.servlet.ServletException, java.io.IOException{
        String Vehiculo [];
        Vehiculo= new String[6];
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
        String sql="", tablas="", campos="", where="";
        String tab="", modelo="", color="", marca="", kmact="", fkmact="", rango="";
		try{
			m05bConn = miConex.getConnection();
            tablas=" "+m05bConstantes.TAB_VEHICULOS+","+m05bConstantes.TAB_DESCRIPCIONES;
            tablas=tablas+","+m05bConstantes.TAB_MODELOS;
            
            campos=" "+m05bConstantes.COL_17_DESCRIP+","+m05bConstantes.COL_99_CODTABLA+",";
            campos=campos+idioma+m05bConstantes.COL_99_DESCRIPCION+","+m05bConstantes.COL_34_KMACTUALES;
            campos=campos+",TO_CHAR("+m05bConstantes.COL_34_FKMACT +  ",'DD/MM/YYYY')" ;
            campos=campos+","+m05bConstantes.COL_34_RANGO;
            where=" WHERE  "+m05bConstantes.COL_34_MODELO+"= "+ m05bConstantes.COL_17_CODMOD;
            where=where+" AND (("+m05bConstantes.COL_99_CODTABLA+"="+m05bConstantes.DESC_COLORES;
            where= where+" AND "+m05bConstantes.COL_34_COLOR+"="+m05bConstantes.COL_99_CODDESC+")";
            where=where+" OR ("+m05bConstantes.COL_99_CODTABLA+"="+m05bConstantes.DESC_MARCAS;
            where=where+" AND "+ m05bConstantes.COL_99_CODDESC+"="+m05bConstantes.COL_34_MARCA+"))";
            where=where+" AND "+ m05bConstantes.COL_34_MATRICULA+"='"+matricula+"' ";
			
			sql="select "+ campos ;
            sql=sql+ " from  "+tablas;
            sql=sql+where+" order by "+m05bConstantes.COL_99_CODTABLA+" asc";

         //   System.out.println(sql);
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery(); 
			while (m05bRS.next() == true) {
					rango = m05bRS.getString(m05bConstantes.COL_34_RANGO );
					tab = m05bRS.getString(m05bConstantes.COL_99_CODTABLA );
	                modelo=m05bRS.getString(1);
	                if (tab.equals("7")){
	                    marca= m05bRS.getString(idioma+m05bConstantes.COL_99_DESCRIPCION);
	                }
	                else {
	                    color = m05bRS.getString(idioma+m05bConstantes.COL_99_DESCRIPCION);
	                }
	                
	                kmact= m05bRS.getString(4);
	                
	                if ( (kmact == null) || (kmact.equals("")) )
	                	kmact = "0";
	                fkmact= m05bRS.getString(5);
			}
            Vehiculo[0]=modelo;
            Vehiculo[1]=color;
            Vehiculo[2]=marca;
            Vehiculo[3]=kmact;
            Vehiculo[4]=fkmact;
            Vehiculo[5]=rango;
 		} 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 getPropVehiculo
    
    public String getCargo (String cargo) throws javax.servlet.ServletException, java.io.IOException{


        String desc="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";

		try{
			m05bConn = miConex.getConnection();
			
			sql="select "+ m05bConstantes.COL_04_DESCCARG ;
            sql=sql+ " from  "+m05bConstantes.TAB_CARGOS;
            sql=sql+" where "+m05bConstantes.COL_04_CODCARGO;
            sql = sql+" = '" + cargo + "'";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
				desc = m05bRS.getString(m05bConstantes.COL_04_DESCCARG );

			}
 		} 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 desc;
    }   // Fin funcion getCargo

    public String getNombreGrupo (String codigo) throws javax.servlet.ServletException, java.io.IOException{
    	return getNombreGrupo(codigo,m05bConstantesInformes.IDIOMA_C);
    }
    public String getNombreGrupo (String codigo,String idioma) throws javax.servlet.ServletException, java.io.IOException{


        String desc="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		String campoTexto="";
		if (!idioma.equals(m05bConstantesInformes.IDIOMA_C))
			campoTexto="E";
		try{
			m05bConn = miConex.getConnection();
			
			sql="select NVL("+campoTexto+ m05bConstantes.COL_36_NOMBRE ;
			sql+=","+m05bConstantes.COL_36_NOMBRE ;
			sql+=")";
            sql=sql+ " from  "+m05bConstantes.TAB_GRUPOS;
            sql=sql+" where "+m05bConstantes.COL_36_CODIGO;
            sql = sql+" = '" + codigo + "'";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
				desc = 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 desc;
    }   // Fin funcion getNombreGrupo
    public String getConceptoRepa (String codigo) throws javax.servlet.ServletException, java.io.IOException{
    return getConceptoRepa(codigo,"es");
    }

    public String getConceptoRepa (String codigo,String idioma) throws javax.servlet.ServletException, java.io.IOException{


        String desc="";
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";

		try{
			m05bConn = miConex.getConnection();
			String campoTexto="";
			if(!idioma.equals("es"))
				campoTexto="E";
			sql="select NVL("+campoTexto+ m05bConstantes.COL_25_DESCRIP ;
			sql+=","+m05bConstantes.COL_25_DESCRIP+")";
            sql=sql+ " from  "+m05bConstantes.TAB_REPARACIONES;
            sql=sql+" where "+m05bConstantes.COL_25_CODIGO;
            sql = sql+" = '" + codigo + "'";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
				desc = 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 desc;
    }   // Fin funcion getConceptoRepa


}   // Fin clase m05bDescrip