package m05bClasses.m05bPaginacion;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import m05bClasses.m05bComun;
import m05bClasses.m05bConexion;
import m05bClasses.m05bConstantes;
import m05bClasses.m05bFileLog;
import m05bClasses.m05bPaginacion.m05bValueObject.m05bCochesLibresHoy;
import m05bClasses.m05bPaginacion.m05bValueObject.m05bRelAsignaciones;
import m05bClasses.m05bPaginacion.m05bValueObject.m05bRelOrdenes;
import m05bClasses.m05bPaginacion.m05bValueObject.m05bRelRevisiones;
import m05bClasses.m05bPaginacion.m05bValueObject.m05bVehGastos;
import m05bClasses.m05bPaginacion.m05bValueObject.m05bRelVehiculos;

/* Fecha Creacion 18/01/2006 */
	


/**
 * Clase que define la salida del Log en un fichero definido en la configuracion de la aplicacion.
 */  
public class m05bPaginator {
	
	
	public static m05bPage getPageCochesLibresHoy(int page, int pageSize, String query){
		
		int firstElement = m05bPage.getStartItemByPage(page, pageSize);

	    int lastElement = firstElement + pageSize - 1;
	    
	    java.sql.Connection conn = null;
	    m05bConexion miConex = new m05bConexion();
	    PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
	    String queryCount = "SELECT COUNT(*) FROM ("+query+")";
	    Collection results = new ArrayList();
	    int totalElements = 0;
	    try {
	    	conn = miConex.getConnection();
	        
	    	
    		String realQuery = "select * from ( select a.*, rownum rnum from (" +
            query +       
			") a where rownum <= "+lastElement+" ) where rnum >= "+firstElement;
			
			pstmt = conn.prepareStatement(realQuery);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				String matricula= rs.getString(1);
				String rango= rs.getString(2)==null?"":rs.getString(2);
				String marca= rs.getString(3)==null?"":rs.getString(3);
				String color=rs.getString(4)==null?"":rs.getString(4);
				String kmact=rs.getString(5)==null?"":rs.getString(5);
				String ubicacion=rs.getString(6)==null?"":rs.getString(6).toUpperCase();
				String modelo= rs.getString(7)==null?"":rs.getString(7);
				String ubiOrdTrab= rs.getString(8)==null?null:rs.getString(8);
				String ubiAsig= rs.getString(9)==null?null:rs.getString(9);
				
				m05bCochesLibresHoy cocheLibre = new m05bCochesLibresHoy();
				cocheLibre.setMatricula(matricula);
				cocheLibre.setRango(rango);
				cocheLibre.setMarca(marca);
				cocheLibre.setColor(color);
				cocheLibre.setKmact(kmact);
				cocheLibre.setUbicacion(ubicacion);
				cocheLibre.setModelo(modelo);
				cocheLibre.setUbiOrdenTrab(ubiOrdTrab);
				cocheLibre.setUbiAsig(ubiAsig);
				results.add(cocheLibre);

	    	}
	        pstmt2 = conn.prepareStatement(queryCount);
	        rs2 = pstmt2.executeQuery();
	        rs2.next();
	        totalElements = rs2.getInt(1); 
	    } catch (Exception e) {
			// TODO Auto-generated catch block
	    	  m05bFileLog log= new m05bFileLog(); log.addTraza(e);
		} finally {
	    	try { if (rs != null) rs.close(); } catch (SQLException e) {}
	        try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
	        try { if (rs2 != null) rs2.close(); } catch (SQLException e) {}
	        try { if (pstmt2 != null) pstmt2.close(); } catch (SQLException e) {}
	        try { if (conn != null) conn.close(); } catch (SQLException e) {}
	    }
		
		return new m05bPage(results,  page, pageSize, totalElements);
	}
	
	public static m05bPage getVehGastos(int page, int pageSize, String query){
		
		int firstElement = m05bPage.getStartItemByPage(page, pageSize);

	    int lastElement = firstElement + pageSize - 1;
	    
	    java.sql.Connection conn = null;
	    m05bConexion miConex = new m05bConexion();
	    PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
	    String queryCount = "SELECT COUNT(*) FROM ("+query+")";
	    Collection results = new ArrayList();
	    int totalElements = 0;
	    try {
	    	conn = miConex.getConnection();
	        
	    	
    		String realQuery = "select * from ( select a.*, rownum rnum from (" +
            query +       
			") a where rownum <= "+lastElement+" ) where rnum >= "+firstElement;
			
			pstmt = conn.prepareStatement(realQuery);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				String Tarjeta= rs.getString(m05bConstantes.COL_18_TARJETA);
				String Tipo= rs.getString(m05bConstantes.COL_18_TIPO);
				String Concepto= (rs.getString(m05bConstantes.COL_18_CONCEPTO)==null?"":rs.getString(m05bConstantes.COL_18_CONCEPTO));
				String Nombre= (rs.getString(m05bConstantes.COL_18_NCOMERCIO)==null?"":rs.getString(m05bConstantes.COL_18_NCOMERCIO));
				String Fecha=rs.getString(m05bConstantes.COL_18_FECHA);
				String Control=rs.getString(m05bConstantes.COL_18_CONTROL);

				String Importe=rs.getString(m05bConstantes.COL_18_IMPORTE);
				String Km=rs.getString(m05bConstantes.COL_18_KM);
				String Litros=rs.getString(m05bConstantes.COL_18_LITROS);
				String Ver=rs.getString(m05bConstantes.COL_18_VERIFIC);
				String Cargabon=rs.getString(m05bConstantes.COL_18_CARGO);
				
				m05bVehGastos vehGastos = new m05bVehGastos();
				vehGastos.setTarjeta(Tarjeta);
				vehGastos.setTipo(Tipo);
				vehGastos.setConcepto(Concepto);
				vehGastos.setNombre(Nombre);
				vehGastos.setFecha(Fecha);
				vehGastos.setControl(Control);
				vehGastos.setImporte(Importe);
				vehGastos.setKm(Km);
				vehGastos.setLitros(Litros);
				vehGastos.setVer(Ver);
				vehGastos.setCargabon(Cargabon);
				results.add(vehGastos);

	    	}
	        pstmt2 = conn.prepareStatement(queryCount);
	        rs2 = pstmt2.executeQuery();
	        rs2.next();
	        totalElements = rs2.getInt(1); 
	    } catch (Exception e) {
			// TODO Auto-generated catch block
	    	m05bFileLog log= new m05bFileLog(); log.addTraza(e);
		} finally {
	    	try { if (rs != null) rs.close(); } catch (SQLException e) {}
	        try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
	        try { if (rs2 != null) rs2.close(); } catch (SQLException e) {}
	        try { if (pstmt2 != null) pstmt2.close(); } catch (SQLException e) {}
	        try { if (conn != null) conn.close(); } catch (SQLException e) {}
	    }
		
		return new m05bPage(results,  page, pageSize, totalElements);
	}
	
	public static m05bPage getRelOrdenes(int page, int pageSize, String query){
		
		int firstElement = m05bPage.getStartItemByPage(page, pageSize);

	    int lastElement = firstElement + pageSize - 1;
	    
	    java.sql.Connection conn = null;
	    m05bConexion miConex = new m05bConexion();
	    PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
	    String queryCount = "SELECT COUNT(*) FROM ("+query+")";
	    Collection results = new ArrayList();
	    int totalElements = 0;
	    try {
	    	conn = miConex.getConnection();
	        
	    	
    		String realQuery = "select * from ( select a.*, rownum rnum from (" +
            query +       
			") a where rownum <= "+lastElement+" ) where rnum >= "+firstElement;
			
			pstmt = conn.prepareStatement(realQuery);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				String codigo = rs.getString(m05bConstantes.COL_20_CODIGO);
				String matricula= rs.getString(m05bConstantes.COL_20_MATRICULA);
				String FEntrada= rs.getString(m05bConstantes.COL_20_FENTRADA);
				String KmEntrada= rs.getString(m05bConstantes.COL_20_KM_ENTRADA)==null?"":rs.getString(m05bConstantes.COL_20_KM_ENTRADA);
				String Persona=rs.getString(m05bConstantes.COL_07_NOMBRE);
				String FSalida=rs.getString(m05bConstantes.COL_20_FSALIDA);
				String Conductor=rs.getString(m05bConstantes.COL_20_CONDUCTOR)==null?"":rs.getString(m05bConstantes.COL_20_CONDUCTOR);
				
				m05bRelOrdenes relOrdenes = new m05bRelOrdenes();
				relOrdenes.setCodigo(codigo);
				relOrdenes.setMatricula(matricula);
				relOrdenes.setFEntrada(FEntrada);
				relOrdenes.setPersona(Persona);
				relOrdenes.setKmEntrada(KmEntrada);
				relOrdenes.setFSalida(FSalida);
				relOrdenes.setConductor(Conductor);
				results.add(relOrdenes);
	    	}
	        pstmt2 = conn.prepareStatement(queryCount);
	        rs2 = pstmt2.executeQuery();
	        rs2.next();
	        totalElements = rs2.getInt(1); 
	    } catch (Exception e) {
			// TODO Auto-generated catch block
	    	m05bFileLog log= new m05bFileLog(); log.addTraza(e);
		} finally {
	    	try { if (rs != null) rs.close(); } catch (SQLException e) {}
	        try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
	        try { if (rs2 != null) rs2.close(); } catch (SQLException e) {}
	        try { if (pstmt2 != null) pstmt2.close(); } catch (SQLException e) {}
	        try { if (conn != null) conn.close(); } catch (SQLException e) {}
	    }
		
		return new m05bPage(results,  page, pageSize, totalElements);
	}
	
	public static m05bPage getRelAsignaciones(int page, int pageSize, String query){
		
		int firstElement = m05bPage.getStartItemByPage(page, pageSize);

	    int lastElement = firstElement + pageSize - 1;
	    
	    java.sql.Connection conn = null;
	    m05bConexion miConex = new m05bConexion();
	    PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
	    String queryCount = "SELECT COUNT(*) FROM ("+query+")";
	    Collection results = new ArrayList();
	    int totalElements = 0;
	    try {
	    	conn = miConex.getConnection();
	        
	    	
    		String realQuery = "select * from ( select a.*, rownum rnum from (" +
            query +       
			") a where rownum <= "+lastElement+" ) where rnum >= "+firstElement;
			
			pstmt = conn.prepareStatement(realQuery);
			rs = pstmt.executeQuery();
			m05bComun comun = new m05bComun();
			while (rs.next()) {
				String codigo = rs.getString(m05bConstantes.COL_01_CODASIG);
				String matricula= rs.getString(m05bConstantes.COL_01_MATRICULA);
				String FEntrada= rs.getString(m05bConstantes.COL_01_FDESDE);
				String dni=rs.getString(m05bConstantes.COL_01_DNI);
				String nombre=rs.getString(m05bConstantes.COL_21_NOMBRE);
				String FSalida=rs.getString(m05bConstantes.COL_01_FHASTA);
				String finalizada=rs.getString(m05bConstantes.COL_01_FINALIZA);
				String FDevolucion=rs.getString(m05bConstantes.COL_01_FDEVOL);	
//				Agurtzane Feb12: columnas telemático
				String telematica=rs.getString("TELEMATICA");
				String firmada=rs.getString("FIRMADA");
//				Fin Agurtzane Feb12: columnas telemático
				String solicitud=rs.getString("SOLICITUD");//Agurtzane Mar12: relacionar solicitud y asignación

				if (FDevolucion == null)
		        	FDevolucion="";
		        else
					FDevolucion=comun.formatearFechaHora(FDevolucion);
		        if (finalizada==null || finalizada.equals(""))
		            finalizada="NO";
		        if (finalizada.equals("S"))
		            finalizada="S&Iacute;";
		        else if (finalizada.equals("N"))
		            finalizada="NO";
		        
		        m05bRelAsignaciones relAsignaciones = new m05bRelAsignaciones();
		        relAsignaciones.setCodigo(codigo);
		        relAsignaciones.setMatricula(matricula);
		        relAsignaciones.setFEntrada(FEntrada);
		        relAsignaciones.setFSalida(FSalida);
		        relAsignaciones.setDni(dni);
		        relAsignaciones.setNombre(nombre);
		        relAsignaciones.setFinalizada(finalizada);
		        relAsignaciones.setFDevolucion(FDevolucion);
//				Agurtzane Feb12: columnas telemático
		        relAsignaciones.setTelematica(telematica);
		        relAsignaciones.setFirmada(firmada);
//				Fin Agurtzane Feb12: columnas telemático
		        relAsignaciones.setSolicitud(solicitud);//Agurtzane Mar12: relacionar solicitud y asignación
				results.add(relAsignaciones);
	    	}
	        pstmt2 = conn.prepareStatement(queryCount);
	        rs2 = pstmt2.executeQuery();
	        rs2.next();
	        totalElements = rs2.getInt(1); 
	    } catch (Exception e) {
			// TODO Auto-generated catch block
	    	m05bFileLog log= new m05bFileLog(); log.addTraza(e);
		} finally {
	    	try { if (rs != null) rs.close(); } catch (SQLException e) {}
	        try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
	        try { if (rs2 != null) rs2.close(); } catch (SQLException e) {}
	        try { if (pstmt2 != null) pstmt2.close(); } catch (SQLException e) {}
	        try { if (conn != null) conn.close(); } catch (SQLException e) {}
	    }
		
		return new m05bPage(results,  page, pageSize, totalElements);
	}
	
	public static m05bPage getRelRevisiones(int page, int pageSize, String query){
		
		int firstElement = m05bPage.getStartItemByPage(page, pageSize);

	    int lastElement = firstElement + pageSize - 1;
	    
	    java.sql.Connection conn = null;
	    m05bConexion miConex = new m05bConexion();
	    PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
	    String queryCount = "SELECT COUNT(*) FROM ("+query+")";
	    Collection results = new ArrayList();
	    int totalElements = 0;
	    try {
	    	conn = miConex.getConnection();
	        
	    	
    		String realQuery = "select * from ( select a.*, rownum rnum from (" +
            query +       
			") a where rownum <= "+lastElement+" ) where rnum >= "+firstElement;
			
			pstmt = conn.prepareStatement(realQuery);
			rs = pstmt.executeQuery();
			m05bComun comun = new m05bComun();
			while (rs.next()) {
				String matricula = rs.getString(m05bConstantes.COL_35_MATRICULA);
				String marca= rs.getString(m05bConstantes.COL_99_DESCRIPCION)==null?"":rs.getString(m05bConstantes.COL_99_DESCRIPCION);
				String modelo= rs.getString(m05bConstantes.COL_17_DESCRIP);
				String realizada=rs.getString(m05bConstantes.COL_35_REALIZA);
				String codigo=rs.getString (m05bConstantes.COL_35_CODIGO);
	            if (realizada.equals("-"))
	                realizada="S&Iacute;";
	            else if (realizada.equals("0"))
	                realizada="NO";
	            String fecha=rs.getString(m05bConstantes.COL_35_FECHA);
	           // fecha=comun.formatearFecha(fecha);
	            String kmrevision=rs.getString(m05bConstantes.COL_35_KMRE)==null?"0":rs.getString(m05bConstantes.COL_35_KMRE);
		        
		        m05bRelRevisiones relRevisiones = new m05bRelRevisiones();
		        relRevisiones.setCodigo(codigo);
		        relRevisiones.setMatricula(matricula);
		        relRevisiones.setMarca(marca);
		        relRevisiones.setRealizada(realizada);
		        relRevisiones.setModelo(modelo);
		        relRevisiones.setKmrevision(kmrevision);
		        relRevisiones.setFecha(fecha);
				results.add(relRevisiones);
	    	}
	        pstmt2 = conn.prepareStatement(queryCount);
	        rs2 = pstmt2.executeQuery();
	        rs2.next();
	        totalElements = rs2.getInt(1); 
	    } catch (Exception e) {
			// TODO Auto-generated catch block
	    	m05bFileLog log= new m05bFileLog(); log.addTraza(e);
		} finally {
	    	try { if (rs != null) rs.close(); } catch (SQLException e) {}
	        try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
	        try { if (rs2 != null) rs2.close(); } catch (SQLException e) {}
	        try { if (pstmt2 != null) pstmt2.close(); } catch (SQLException e) {}
	        try { if (conn != null) conn.close(); } catch (SQLException e) {}
	    }
		
		return new m05bPage(results,  page, pageSize, totalElements);
	}

	public static m05bPage getRelVehiculos(int page, int pageSize, String query){
		
		int firstElement = m05bPage.getStartItemByPage(page, pageSize);

	    int lastElement = firstElement + pageSize - 1;
	    
	    java.sql.Connection conn = null;
	    m05bConexion miConex = new m05bConexion();
	    PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
	    String queryCount = "SELECT COUNT(*) FROM ("+query+")";
	    Collection results = new ArrayList();
	    int totalElements = 0;
	    try {
	    	conn = miConex.getConnection();
	        
	    	
    		String realQuery = "select * from ( select a.*, rownum rnum from (" +
            query +       
			") a where rownum <= "+lastElement+" ) where rnum >= "+firstElement;
			
			pstmt = conn.prepareStatement(realQuery);
			rs = pstmt.executeQuery();
			m05bFileLog miLog = new m05bFileLog(); 
			m05bComun comun = new m05bComun();
			while (rs.next()) {
				String nSerie = rs.getString(m05bConstantes.COL_34_NSERIE);
				String expdte= rs.getString(m05bConstantes.COL_34_EXPDTE);
				String autoriza= rs.getString(m05bConstantes.COL_34_AUTORI_APAR);
				String inf1=rs.getString(m05bConstantes.COL_34_INF1);
				String inf2=rs.getString(m05bConstantes.COL_34_INF2);
				String notas=rs.getString(m05bConstantes.COL_34_NOTA);
				String matriculaAnt=rs.getString(m05bConstantes.COL_34_MATRIC_ANT);
				String matricula=rs.getString(m05bConstantes.COL_34_MATRICULA);
				String tarjetaG=rs.getString(9);
				String tarjetaV=rs.getString(10);
				miLog.addTraza("tarjetaV" , "Grabar Vehiculos:  " , "tarjetaV: " + tarjetaV,m05bConstantes.LOG_NIVEL_BAJO);
		        
		        m05bRelVehiculos relVehiculos = new m05bRelVehiculos();
		        relVehiculos.setNSerie(nSerie);
		        relVehiculos.setExpdte(expdte);
		        relVehiculos.setAutoriza(autoriza);
		        relVehiculos.setInf1(inf1);
		        relVehiculos.setInf2(inf2);
		        relVehiculos.setNotas(notas);
		        relVehiculos.setMatriculaAnt(matriculaAnt);
		        relVehiculos.setMatricula(matricula);
		        relVehiculos.setTarjetaG(tarjetaG);
		        relVehiculos.setTarjetaV(tarjetaV);
				results.add(relVehiculos);
	    	}
	        pstmt2 = conn.prepareStatement(queryCount);
	        rs2 = pstmt2.executeQuery();
	        rs2.next();
	        totalElements = rs2.getInt(1); 
	    } catch (Exception e) {
			// TODO Auto-generated catch block
	    	m05bFileLog log= new m05bFileLog(); log.addTraza(e);
		} finally {
	    	try { if (rs != null) rs.close(); } catch (SQLException e) {}
	        try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
	        try { if (rs2 != null) rs2.close(); } catch (SQLException e) {}
	        try { if (pstmt2 != null) pstmt2.close(); } catch (SQLException e) {}
	        try { if (conn != null) conn.close(); } catch (SQLException e) {}
	    }
		
		return new m05bPage(results,  page, pageSize, totalElements);
	}	
	
}