/**
 * XXXX
 * Inserte aquí la descripción del tipo.
 * Fecha de creación: (31/05/07)
 */    
package com.ejie.p06b.util;

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

import com.ejie.p06b.datatype.logic.pagination.P06BIPagination;
import com.ejie.p06b.util.bd.P06BServidorConexionesBD;


public class P06BPaginator {
	
	
	public static P06BPage getElements(int page, int pageSize, String query, String codigoVuelta, String columnaVuelta, P06BIPagination datatype){
		
		int firstElement = P06BPage.getStartItemByPage(page, pageSize);

	    int lastElement = firstElement + pageSize - 1;
	    
	    java.sql.Connection conn = null;
	    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 = P06BServidorConexionesBD.getConexionConsultas();
	        
//	    	Añadido para volver al codigoVuelta
	    	if (codigoVuelta!=null && columnaVuelta!=null){
	    		int paginasTotales = 0;
	    		String posQuery = "select " + columnaVuelta + ", rnum from ( select a.*, rownum rnum from (" +
	    		query +       
	    		") a) where " + columnaVuelta + "=?";
	    		
	    		pstmt = conn.prepareStatement(posQuery);
	    		pstmt.setString(1, codigoVuelta);
	    		rs = pstmt.executeQuery();
	    		if (rs.next()) {
	    			int nPosicion = rs.getInt("rnum");
	    			int paginasCompletas = nPosicion/pageSize;
	    			int paginasParciales = 0;
	    			if (nPosicion%pageSize!=0){
	    				paginasParciales++;
	    			}
	    			paginasTotales = paginasCompletas + paginasParciales;
	    		}else{
	    			paginasTotales = 1;	
	    		}
	    		if (paginasTotales!=page){
		    		page = paginasTotales;
		    		firstElement = P06BPage.getStartItemByPage(page, pageSize);
		    	    lastElement = firstElement + pageSize - 1;	    			
	    		}
	    	}
//	    	Añadido para volver al codigoVuelta

	    	
	    	
    		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()) {
				datatype.setRS(rs);
				results.add(datatype.getObj());
	    	}
	        pstmt2 = conn.prepareStatement(queryCount);
	        rs2 = pstmt2.executeQuery();
	        rs2.next();
	        totalElements = rs2.getInt(1); 
	    } catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 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 P06BPage(results,  page, pageSize, totalElements);
	}

	public static P06BPage getElements(int page, int pageSize, String query, P06BIPagination datatype){
		
		int firstElement = P06BPage.getStartItemByPage(page, pageSize);

	    int lastElement = firstElement + pageSize - 1;
	    
	    java.sql.Connection conn = null;
	    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 = P06BServidorConexionesBD.getConexionConsultas();
	        
    		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()) {
				datatype.setRS(rs);
				results.add(datatype.getObj());
	    	}
	        pstmt2 = conn.prepareStatement(queryCount);
	        rs2 = pstmt2.executeQuery();
	        rs2.next();
	        totalElements = rs2.getInt(1); 
	    } catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 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 P06BPage(results,  page, pageSize, totalElements);
	}


}