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


public class  m05bCampos{
	
	m05bFileLog miLog = new m05bFileLog();
	
     public String getCampo (String Tabla, String Campo, String CampoFiltro, String Filtro )throws javax.servlet.ServletException, java.io.IOException {
     	 
		String descripcion="";
		m05bConexion miConex = new m05bConexion();
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		try{
			m05bConn = miConex.getConnection();
			sql="select "+ Campo ;
            sql=sql+ " from   "+Tabla;
            sql=sql+" where  "+CampoFiltro;
            sql = sql+" = '" + Filtro +"'";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
				descripcion = m05bRS.getString(Campo );
			}
 		} 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 getCampo
		
    public String getCodDpto (String Departamento) {
        String dep="";
        if (Departamento==null || Departamento.equals(""))
         	return  dep;
        dep=Departamento.substring(Departamento.lastIndexOf("-")+1,Departamento.length());
        return dep;
    }  // Fin funcion getCodDpto

    public boolean vehiculoLibre (String matricula, String desde, String hasta, String permanente) {

		boolean libre=true;
		m05bConexion miConex = new m05bConexion();
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		try{
			m05bConn = miConex.getConnection();
			if (permanente.equals("S"))  {
				  //  Mirar solo asignaciones				
				sql=     "  select MATRIC_01 FROM M0501T00 " +
				 	"			WHERE MATRIC_01 = '" +  matricula + "'" +
					"			and ( FINALIZ_01 = 'N'  " +
					" 			OR " +
					"			( FDESDE_01 BETWEEN TO_DATE('" + desde + "','YYYYMMDDHH24MISS') " +
					"				AND TO_DATE('" + hasta + "','YYYYMMDDHH24MISS') " +
					"			or FDEVOL_01 BETWEEN TO_DATE('" + desde + "','YYYYMMDDHH24MISS') " +
					"				AND TO_DATE('" + hasta + "','YYYYMMDDHH24MISS') " +
					"			or (FDESDE_01 <= TO_DATE ('" + desde + "','YYYYMMDDHH24MISS') " +
					"				AND FDEVOL_01 >= TO_DATE ('" + hasta + "','YYYYMMDDHH24MISS') )" +
					"			or (FDESDE_01 >= TO_DATE ('" + desde + "','YYYYMMDDHH24MISS') " +
					"				AND FDEVOL_01 <= TO_DATE ('" + hasta + "','YYYYMMDDHH24MISS')))  )";
				miLog.addTraza("Anabel","Vehiculo Libre", "SQL-ASIG: "+sql, m05bConstantes.LOG_NIVEL_BAJO);
				m05bPstmt = m05bConn.prepareStatement(sql);
				m05bRS = m05bPstmt.executeQuery();
				if (m05bRS.next() == true){
					// OCUPADO: existen asignaciones
					libre=false;
				} else {
					libre=true;
				}				
			} else if (libre = true) {
				sql =    " SELECT MATRIC_20 " +
			     	"                         FROM M0520T00 " +
					"                         WHERE MATRIC_20 = '" +  matricula + "'" +
								 "			and ( FSALIDA_20 IS NULL " +
								 " 			OR " +
							 	 "			( FENTRADA_20 BETWEEN TO_DATE('" + desde + "','YYYYMMDDHH24MISS') " +
								 "				AND TO_DATE('" + hasta + "','YYYYMMDDHH24MISS') " +
								 "			or FSALIDA_20 BETWEEN TO_DATE('" + desde + "','YYYYMMDDHH24MISS') " +
								 "				AND TO_DATE('" + hasta + "','YYYYMMDDHH24MISS') " +
								 "			or (FENTRADA_20 <= TO_DATE ('" + desde + "','YYYYMMDDHH24MISS') " +
								 "				AND FSALIDA_20 >= TO_DATE ('" + hasta + "','YYYYMMDDHH24MISS') )" +
								 "			or (FENTRADA_20 >= TO_DATE ('" + desde + "','YYYYMMDDHH24MISS') " +
								 "				AND FSALIDA_20 <= TO_DATE ('" + hasta + "','YYYYMMDDHH24MISS')))  )";
				miLog.addTraza("Anabel","Vehiculo Libre", "SQL-OT: "+sql, m05bConstantes.LOG_NIVEL_BAJO);				
				m05bPstmt = m05bConn.prepareStatement(sql);
				m05bRS = m05bPstmt.executeQuery();
				if (m05bRS.next() == true){
					// 	OCUPADO: existen ordenes de trabajo
					libre=false;
				} 
			}
 		} 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);
			}
		}
		miLog.addTraza("Anabel","Vehiculo Libre", "Permanente: "+permanente, m05bConstantes.LOG_NIVEL_BAJO);		
		miLog.addTraza("Anabel","Vehiculo Libre", "LIBRE: "+libre, m05bConstantes.LOG_NIVEL_BAJO);
		return libre;
		
    }  // Fin funcion vehiculoLibre
    	
    public boolean rangoOK (String matricula, String rango) {

		boolean OK=false;
		m05bConexion miConex = new m05bConexion();
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";


		try{
			m05bConn = miConex.getConnection();
			sql =    " SELECT 1 " +
				     "                         FROM M0534T00 " +
				     "                         WHERE MATRIC_34 = '" +  matricula + "'" +
				     "						   AND RANGO_34 = '" +  rango + "'" ;
			m05bPstmt = m05bConn.prepareStatement(sql);

			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
					OK=true;
			}
 		} 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 OK;
		
    }  // Fin funcion rangoOK
    	
    	
    public String tarifaEmpl (String empleado) {
    	
		String tarifa="";
		m05bConexion miConex = new m05bConexion();
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";


		try{
			m05bConn = miConex.getConnection();
			sql =    " SELECT PRECHORA_07 " +
				     "                         FROM M0507T00 " +
				     "                         WHERE COD_EMPL_07 = '" +  empleado + "'" ;
			m05bPstmt = m05bConn.prepareStatement(sql);

			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
					tarifa=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 tarifa;
		
    }  // Fin funcion tarifaEmpl
    	

    public void actImporteOT (String orden, String reparacion, String impRep) {

		m05bConexion miConex = new m05bConexion();
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		String sql="";

		try{
			m05bConn = miConex.getConnection();

			//Actualizar importe de la reparación
			if (reparacion != "") {				
					sql =    " UPDATE " + m05bConstantes.TAB_ORDEREPAR +
						     "             SET " + m05bConstantes.COL_19_IMPORTE + "= " + impRep + " + " +
							 "				 (select nvl(sum(nvl(" + m05bConstantes.COL_23_TOTAL + ",0)),0) from " + m05bConstantes.TAB_PIEZAREP + 
							     "             WHERE " + m05bConstantes.COL_23_ORDEN + " = '" + orden + "'" +
							     "             AND " + m05bConstantes.COL_23_CODIGO + " = '" + reparacion + "')" +
						     "             WHERE " + m05bConstantes.COL_19_COD_ORD + " = '" + orden + "'" +
						     "             AND " + m05bConstantes.COL_19_CODIGO + " = '" + reparacion + "'" ;
					m05bPstmt = m05bConn.prepareStatement(sql);
					m05bPstmt.executeUpdate();
			}
			
			//Actualizar importe de la orden de trabajo
			sql =    " UPDATE " + m05bConstantes.TAB_ORDENESTRAB +
				     "             SET " + m05bConstantes.COL_20_IMPORTE + "= " +
							 "				 (select nvl(sum(" + m05bConstantes.COL_19_IMPORTE + "),0) from " + m05bConstantes.TAB_ORDEREPAR + 
							     "             WHERE " + m05bConstantes.COL_19_COD_ORD + " = '" + orden + "')" +
				     "             WHERE " + m05bConstantes.COL_20_CODIGO + " = '" + orden + "'";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bPstmt.executeUpdate();
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		} finally {
			try{
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
	 		} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}
		}
		
    }  // Fin funcion actImporteOT


    public String getImpPiezas (String orden, String reparacion) {

		m05bConexion miConex = new m05bConexion();
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet rs = null;
		String sql="";
		String importe="";

		try{
			m05bConn = miConex.getConnection();
			
			//Actualizar importe de la reparación
					sql =    "SELECT SUM(" + m05bConstantes.COL_23_TOTAL + ") from " + m05bConstantes.TAB_PIEZAREP + 
							     "   WHERE " + m05bConstantes.COL_23_ORDEN + " = '" + orden + "'" +
							     "   AND " + m05bConstantes.COL_23_CODIGO + " = '" + reparacion + "'" ;
					m05bPstmt = m05bConn.prepareStatement(sql);
					rs = m05bPstmt.executeQuery();
					if (rs.next())  {
						importe = rs.getString(1);
					} 
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		} finally {
			try{
				if (rs != null)
					rs.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
	 		} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}
		}
		return importe;
    }  // Fin funcion getImpPiezas


    public String getAviso (String matricula, String indicador,String campoTexto) {
// Devuelve el aviso correspondiente a la matricula introducida
		m05bConexion miConex = new m05bConexion();
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet rs = null;
		String sql="";
		String txtAviso="";

		try{
			m05bConn = miConex.getConnection();
			
			//  Obtener avisos
				    sql="select NVL(" +campoTexto+ m05bConstantes.COL_02_TEXTO  +","+m05bConstantes.COL_02_TEXTO  +")"+ 
				     	  	" from " + m05bConstantes.TAB_AVISOS + 
				     		" where " + m05bConstantes.COL_02_MATRICULA + " = '" + matricula + "'" +
				     		" and " + indicador + " = 'S'" ;
			  		m05bPstmt = m05bConn.prepareStatement(sql);
			  		rs = m05bPstmt.executeQuery();
				    if (rs.next()) {
				     	  	txtAviso=rs.getString(1);
					} 
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		} finally {
			try{
				if (rs != null)
					rs.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
	 		} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}
		}
		return txtAviso;
    }  // Fin funcion getAviso


    public String getRestriccion (String dni) {
// Devuelve la restriccion correspondiente al dni introducido
		m05bConexion miConex = new m05bConexion();
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet rs = null;
		String sql="";
		String txtRestriccion="";

		try{
			m05bConn = miConex.getConnection();
			
			//  Obtener avisos
				    sql="select " + m05bConstantes.COL_47_DESCRIPCION  + 
				     	  	" from " + m05bConstantes.TAB_RESTRICCIONES + 
				     		" where " + m05bConstantes.COL_47_DNI + " = '" + dni + "'" ;
			  		m05bPstmt = m05bConn.prepareStatement(sql);
			  		rs = m05bPstmt.executeQuery();
				    if (rs.next()) {
				     	  	txtRestriccion=rs.getString(1);
					} 
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		} finally {
			try{
				if (rs != null)
					rs.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
	 		} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}
		}
		return txtRestriccion;
    }  // Fin funcion getRestriccion


    public String getAmortAcum (String matricula) {
// Devuelve la amortizacion acumulada de un vehiculo
		m05bConexion miConex = new m05bConexion();
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet rs = null;
		String sql="";
		String sAmorAcumulada="0";

		try{
			m05bConn = miConex.getConnection();
			
			//  Obtener amortizacion acumulada

            sql = "SELECT SUM(" + m05bConstantes.COL_39_IMPORTE + ") FROM  " + m05bConstantes.TAB_AMORTIZACION +
                       " WHERE " + m05bConstantes.COL_39_MATRICULA + "='" + matricula + "'";
            m05bPstmt = m05bConn.prepareStatement(sql);
            rs = m05bPstmt.executeQuery();
		    if (rs.next()){  
	       	      sAmorAcumulada = rs.getString(1);
				  if (sAmorAcumulada == null)
				       sAmorAcumulada= "0";
			} 
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		} finally {
			try{
				if (rs != null)
					rs.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
	 		} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}
		}
		return sAmorAcumulada;
    }  // Fin funcion getAmortAcum

    public String getGrupo (String rango) {
// Devuelve el grupo del CTK correspondiente al rango elegido
		m05bConexion miConex = new m05bConexion();
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet rs = null;
		String sql="";
		String grupo="", rangos="", valor="";
		String delimReg= ",";
	    StringTokenizer stReg=null;
	    boolean bEncontrado= false;
		
		try{
			m05bConn = miConex.getConnection();
			
			//  Obtener GRUPOS
				    sql="select " + m05bConstantes.COL_36_CODIGO  + ", " + m05bConstantes.COL_36_RANGOS  +
				     	  	" from " + m05bConstantes.TAB_GRUPOS + 
				     		" where " + m05bConstantes.COL_36_RANGOS + " like '%" + rango + "%'" +
				     		" and " + m05bConstantes.COL_36_NOMBRE + " like 'CTK-%'";
			  		m05bPstmt = m05bConn.prepareStatement(sql);
			  		rs = m05bPstmt.executeQuery();
				    while ( (rs.next()) && !(bEncontrado) ){
				     	  	grupo=rs.getString(1);
				     	  	rangos=rs.getString(2);
							stReg = new StringTokenizer(rangos,delimReg);
							while ( (stReg.hasMoreTokens()) && !(bEncontrado) ) {
			         				valor=stReg.nextToken();
			         				if (valor.equals(rango)) {
										bEncontrado= true;
			         				}
							    }  // Fin while
				     	  	
					} 
 		} catch (Exception ex1) {
			miLog.addTraza(ex1);
		} finally {
			try{
				if (rs != null)
					rs.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
	 		} catch (Exception ex2) {
				miLog.addTraza(ex2);
			}
		}
		return grupo;
    }  // Fin funcion getGrupo



}