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

import javax.naming.InitialContext;
public class m05bGenerarClave {
	m05bFileLog miLog = new m05bFileLog();
/*
    * Devuelve como string la descripción del codigo de marca,color, etc  
*/
	public String getClave (String tabla , String campoClave)throws javax.servlet.ServletException, java.io.IOException {

		String clave="";
		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");
		}
		catch (Exception NamingException) {
			
			
			miLog.addTraza(NamingException);		
		}

		try{
			m05bConn = ds.getConnection();
			
			sql="select "+ campoClave +" from "+ tabla +" order by to_number("+ campoClave +") desc";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
				clave = m05bRS.getString(campoClave);
			}
 		} catch (Exception ex1) {
 			m05bFileLog miLog = new m05bFileLog();
			miLog.addTraza(ex1);			
		} finally {
			try{
				if (m05bRS!=null)
					m05bRS.close();
				if (m05bPstmt!=null)
					m05bPstmt.close();
				if (m05bConn!=null)
					m05bConn.close();
	 		} catch (Exception ex2) {
	 			m05bFileLog miLog = new m05bFileLog();
				miLog.addTraza(ex2);			
			}
		}	
		return clave;
	}

    public boolean existeDni (String strDni) throws javax.servlet.ServletException, java.io.IOException {


        String aux ="";
		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");
		}
		catch (Exception NamingException) {
			miLog.addTraza(NamingException);
			
		}

		try{
			m05bConn = ds.getConnection();
			
			sql="select "+ m05bConstantes.COL_21_DNI +" from "+ m05bConstantes.TAB_PERSONAL +" where  "+ m05bConstantes.COL_21_DNI +" = "+strDni;
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			if (m05bRS.next() == true){
				 aux= m05bRS.getString(m05bConstantes.COL_21_DNI);
			}

 		} 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);		
			}	
		}
		 if (aux.equals(""))
                return false;
            else
                return true;

    }
    public boolean hayCoche (String matricula,String Desde,String Hasta,String Rango) throws javax.servlet.ServletException, java.io.IOException {


            String aux="";
            String con1="";
            String con2="";
            String con3="";
            String con4="";
            String con5="";
            String con6="";
            String con7="";
            String forFecha="YYYYMMDDHH24MISS";

            con1=" "+m05bConstantes.COL_34_RANGO+"='"+Rango+"' ";

            con2=" AND "+m05bConstantes.COL_34_MATRICULA+" NOT IN (select "+m05bConstantes.COL_01_MATRICULA+
            " FROM "+m05bConstantes.TAB_ASIGNACION+" WHERE "+m05bConstantes.COL_01_FINALIZA+"='N')";

            con3=" AND "+m05bConstantes.COL_34_MATRICULA+" NOT IN (select "+m05bConstantes.COL_01_MATRICULA+
            " FROM "+m05bConstantes.TAB_ASIGNACION+" WHERE "+m05bConstantes.COL_01_FDESDE+" NOT "+
            "BETWEEN TO_DATE ('"+Desde+"','"+forFecha+"') AND TO_DATE ('"+
                Hasta+"','"+forFecha+"'))";

            con4 =" AND "+m05bConstantes.COL_34_MATRICULA+" NOT IN (select "+
            m05bConstantes.COL_20_MATRICULA+" FROM "+m05bConstantes.TAB_ORDENESTRAB +" WHERE "+
            m05bConstantes.COL_20_FENTRADA+" NOT BETWEEN TO_DATE ('"+Desde+"','"+forFecha+"') AND "+
            " TO_DATE ('"+Hasta+"','"+forFecha+"') AND (("+m05bConstantes.COL_20_FSALIDA+")is null))";

            con5=" AND "+m05bConstantes.COL_34_MATRICULA+" NOT IN (select "+m05bConstantes.COL_01_MATRICULA+
            " FROM "+m05bConstantes.TAB_ASIGNACION+" WHERE "+m05bConstantes.COL_01_FHASTA+" NOT "+
            "BETWEEN TO_DATE ('"+Desde+"','"+forFecha+"') AND TO_DATE ('"+
                Hasta+"','"+forFecha+"'))";

            con6=" AND "+m05bConstantes.COL_34_MATRICULA+" ="+matricula;

            con7 = " AND "+ m05bConstantes.COL_34_MATRICULA +" NOT IN (select "+m05bConstantes.COL_01_MATRICULA+
                   " FROM "+m05bConstantes.TAB_ASIGNACION +" WHERE "+m05bConstantes.COL_01_FDESDE+" <= TO_DATE ('"+Desde+"','"+forFecha+"')"+
                   " AND "+m05bConstantes.COL_01_FHASTA +" <= TO_DATE ('"+Hasta+"','"+forFecha+"'))";

           con1=" WHERE "+con1+con2+con3+con4+con5+con7+con6;


                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");
                }
                catch (Exception NamingException) {
                    miLog.addTraza(NamingException);
                }

                try{
                    m05bConn = ds.getConnection();
                	
                    sql="select "+ m05bConstantes.COL_34_MATRICULA +" from "+ m05bConstantes.TAB_VEHICULOS +con1;
                    m05bPstmt = m05bConn.prepareStatement(sql);
                    m05bRS = m05bPstmt.executeQuery();
                    if (m05bRS.next() == true){
                         aux= m05bRS.getString(m05bConstantes.COL_34_MATRICULA);
                    }

                 }
                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);
	                }
	            }
                 if (aux.equals(""))
                        return false;
                    else
                        return true;




    }
    public String []  getPropPersona (String dni) throws javax.servlet.ServletException, java.io.IOException{

        String Persona [];
        Persona = new String[4];
		java.sql.Connection m05bConn = null;
		PreparedStatement m05bPstmt = null;
		ResultSet m05bRS = null;
		String sql="";
		javax.sql.DataSource ds = null;
        String tablas=m05bConstantes.TAB_PERSONAL+","+m05bConstantes.TAB_DEPARTAMENTOS;
        String campos=m05bConstantes.COL_21_CODCARGO+","+m05bConstantes.COL_21_CODDPTO;
        campos=campos+","+m05bConstantes.COL_06_DESCDPTO+","+m05bConstantes.COL_21_RANGO;
        String where=" "+m05bConstantes.COL_06_CODDPTO+"="+m05bConstantes.COL_21_CODDPTO;
        where=where+" AND "+m05bConstantes.COL_21_DNI+"="+dni;


		try{
			InitialContext ctx2 = new InitialContext();
			ds = (javax.sql.DataSource) ctx2.lookup("m05b.m05bDataSource");
		}
		catch (Exception NamingException) {
			miLog.addTraza(NamingException);
		}

		try{

			m05bConn = ds.getConnection();

			sql="select "+ campos ;
            sql=sql+ " from  "+tablas;

            sql=sql+where;
            String Cargo="";
            String Rango="";
            String Departamento="";
            String DescDep="";
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			while (m05bRS.next() == true){
				Cargo=m05bRS.getString(m05bConstantes.COL_21_CODCARGO);
                Rango=m05bRS.getString(m05bConstantes.COL_21_RANGO);
                Departamento=m05bRS.getString(m05bConstantes.COL_21_CODDPTO);
                DescDep=m05bRS.getString(m05bConstantes.COL_06_DESCDPTO);
			}
            Persona[0]=Cargo;
            Persona[1]=Rango;
            Persona[2]=Departamento;
            Persona[3]=DescDep;

 		} 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 Persona;

    }
    
public int getNextId(String tabla, String campo ) throws javax.servlet.ServletException, java.io.IOException {

		int id=0;
		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 max("+ campo + ") + 1 from " + tabla;
			m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
			
			if (m05bRS.next() == true){
				id = m05bRS.getInt(1);
			}
 		} catch (Exception SQLException) {
			
			miLog.addTraza(SQLException);		
		} finally {
			try { 
				if (m05bRS != null)
					m05bRS.close();
				if (m05bPstmt != null)
					m05bPstmt.close();
				if (m05bConn != null)
					m05bConn.close();
			} catch(Exception e) {
       				//System.err.println("m05b - Generar Clave: EXCEPTION FINALLY: " + e.toString());
       		}
 		} 
		return id;
}  // Fin función getNextId


}