package ordentrabajoServlet;

import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.Locale;
import java.util.ResourceBundle;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import m05bClasses.m05bCamposInformes;
import m05bClasses.m05bComun;
import m05bClasses.m05bConexion;
import m05bClasses.m05bConstantes;
import m05bClasses.m05bFileLog;

import org.apache.fop.apps.Driver;
import org.xml.sax.InputSource;


public class m05bImprimirOrdenesTrabajoServlet extends javax.servlet.http.HttpServlet
{
		java.sql.Connection conn=null;
		m05bFileLog miLog = new m05bFileLog();
   public static final String FO_REQUEST_PARAM = "fo";

   public void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, java.io.IOException
   {
 	   try{
		      doGet(request, response);
		    }catch (Exception e){
		      miLog.addTraza(e);
		    }
   }

   public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException
   {
      performTask(request, response);
   }

/**
 * Initializes the servlet.
 */
public void init() {
	// insert code to initialize the servlet here
}
   public void performTask(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws ServletException
   {	   
	   java.sql.Statement pstmt = null;
	   ResultSet rs = null;
       m05bFileLog miLog = new m05bFileLog();
       HttpSession session = request.getSession(true);     	 
       /*Iker. idioma*/
		Locale idiomaLocal = (Locale)session.getAttribute("Local");

		ResourceBundle res =  (ResourceBundle)session.getAttribute("resTit");
		String idiomaSes = ((idiomaLocal.getLanguage()==null)?"es":idiomaLocal.getLanguage());	
		String campoTexto3="C",campoTexto="";
		String formatoFecha="dd-mm-yyyy";
		if(!idiomaSes.equals("es")){
			campoTexto3="E";	campoTexto="E";
			formatoFecha="yyyy-mm-dd";
			
		}
       String persona = (String) session.getAttribute("persona");
       miLog.addTraza(persona , "m05bImprimirOrdenesTrabajoServlet", "Entramos en  m05bImprimirOrdenesTrabajoServlet" , m05bConstantes.LOG_NIVEL_BAJO);
       m05bComun comun = new m05bComun();
      try
      {        	             	       
		 m05bConexion miConex = new m05bConexion();
	     conn = miConex.getConnection();
         
     	String codigo=request.getParameter("NOrden")==null?"":request.getParameter("NOrden");
    	String matricula=request.getParameter("Matricula")==null?"":request.getParameter("Matricula").toUpperCase();    	
    	String FEntrada =request.getParameter("FEntrada")==null?"":request.getParameter("FEntrada");    	
    	String HEntrada =request.getParameter("HEntrada")==null?"":request.getParameter("HEntrada");   	
    	String FSalida =request.getParameter("FSalida")==null?"":request.getParameter("FSalida");
    	String HSalida =request.getParameter("HSalida")==null?"":request.getParameter("HSalida");
    	String KmEntrada = request.getParameter("kmEntrada")==null?"":request.getParameter("kmEntrada");
    	String Persona = request.getParameter("Abierta")==null?"":request.getParameter("Abierta");
    	String Conductor = request.getParameter("Conductor")==null?"":request.getParameter("Conductor");
    	String Estado = request.getParameter("Estado")==null?"":request.getParameter("Estado").toUpperCase();
    	String idAbierta = request.getParameter("idAbierta")==null?"":request.getParameter("idAbierta");
    	String sGRango = request.getParameter("GRango")==null?"":request.getParameter("GRango");
    	String strGRango=sGRango ;
    	String sRango = request.getParameter("CodRango")==null?"":request.getParameter("CodRango");
    	
    	/*
    	 sDescGrupo=request.getParameter("DescGrupo")==null?"":request.getParameter("DescGrupo");
			if (!sGrupo.equals("***"))  {
					      	sDescGrupo=miComun.ListaCaracteres(sDescGrupo);
							sTitGrupo=miDescrip.getNombreGrupo(sGrupo);
			}
         miLog.addTraza(persona, "Imp Asig Ptes Servlet", "TIT GRUPO: " + sTitGrupo, m05bConstantes.LOG_NIVEL_BAJO);
         sRango = request.getParameter("Rango");
			sDescRango = miDescrip.getDescripcion(m05bConstantes.DESC_RANGOS, sRango);
    	*/
    	
    	String forFecha="YYYYMMDDHH24MISS";    	
	    //if (HEntrada.equals("")) HEntrada="000000";
	    FEntrada=comun.FechaBD(FEntrada,HEntrada,campoTexto3);
	    //if (HSalida.equals("")) HSalida="235900";	    
	    FSalida=comun.FechaBD(FSalida,HSalida,campoTexto3);	    
	    
			

		    String sql = "select " + m05bConstantes.COL_20_CODIGO + "," + m05bConstantes.COL_20_MATRICULA +
		    		",to_char(" + m05bConstantes.COL_20_FENTRADA + ",'"+formatoFecha+"') as FENTRADA_20," + m05bConstantes.COL_20_KM_ENTRADA + 
		    		"," + m05bConstantes.COL_07_NOMBRE +
		    		",to_char(" + m05bConstantes.COL_20_FSALIDA + ",'"+formatoFecha+"') as FSALIDA_20," + m05bConstantes.COL_20_CONDUCTOR + 
		    		" FROM " + m05bConstantes.TAB_ORDENESTRAB +
		    		", " + m05bConstantes.TAB_EMPTALLER +
		    		" WHERE " + m05bConstantes.COL_07_CODIGO + "(+) = " + m05bConstantes.COL_20_PERSONA;

		    miLog.addTraza(persona , "m05bImprimirOrdenesTrabajoServlet", " m05bImprimirOrdenesTrabajoServlet ------5------", m05bConstantes.LOG_NIVEL_BAJO);
		    if (!codigo.equals("")){
		            sql += " AND " + m05bConstantes.COL_20_CODIGO + "='" + codigo + "' ";
		    }
		    if (!matricula.equals("")){
		          	sql += " AND "+m05bConstantes.COL_20_MATRICULA+"='"+matricula+"' ";
		    }
		    if (!Persona.equals("")){
		            sql += " AND "+m05bConstantes.COL_20_PERSONA+"='"+Persona+"' ";
		    }
		    if (!Conductor.equals("")){
		            sql += " AND "+m05bConstantes.COL_20_CONDUCTOR+" like '%"+Conductor.toUpperCase()+"%' ";
		    }				    
		    if (!FEntrada.equals("")){
	            	sql += " AND "+m05bConstantes.COL_20_FENTRADA + " >= TO_DATE('"+FEntrada+
		            	"','"+forFecha+"')";
		    }
		    if (!FSalida.equals("")){
	            	sql += " AND "+m05bConstantes.COL_20_FSALIDA + " <= TO_DATE('"+FSalida+
		            	"','"+forFecha+"')";
		    }	
		    if (!KmEntrada.equals("")){
	            	sql += " AND " + m05bConstantes.COL_20_KM_ENTRADA + " > " + KmEntrada ;
		    }
		    if (Estado.equals("A"))  {
	            	sql += " AND " + m05bConstantes.COL_20_FSALIDA + " is null ";
		    } else  if (Estado.equals("C")) {
	            	sql += " AND " + m05bConstantes.COL_20_FSALIDA + " is not null ";
		    }		 	
            PreparedStatement pstmtR = null;
		    ResultSet rsR = null;
		    miLog.addTraza(persona , "m05bImprimirOrdenesTrabajoServlet", " m05bImprimirOrdenesTrabajoServlet ------6------", m05bConstantes.LOG_NIVEL_BAJO);
			m05bComun miComun=new m05bComun();
		    if (!(sRango.equals(""))){
		    	sql += " AND (SELECT " + m05bConstantes.COL_34_RANGO+ 
		    					" FROM " + m05bConstantes.TAB_VEHICULOS + 
		    					" WHERE " + m05bConstantes.COL_34_MATRICULA + " = " + m05bConstantes.COL_20_MATRICULA +
		    					") = '"+sRango+"'" ;
			 	miLog.addTraza(persona , "m05bImprimirOrdenesTrabajoServlet", "SQL CON RANGO ÓRDENES DE TRABAJO --> " + sql, m05bConstantes.LOG_NIVEL_BAJO);				    					
		 	}
			else if (!(sGRango.equals(""))){
			    	String sSentenciaGrupo=" SELECT "+ m05bConstantes.COL_36_RANGOS+
			    	                " FROM " + m05bConstantes.TAB_GRUPOS + 
			    	                " WHERE " + m05bConstantes.COL_36_CODIGO + " = '" +sGRango+"'";
			    	pstmtR = conn.prepareStatement(sSentenciaGrupo);
					rsR = pstmtR.executeQuery();
					miLog.addTraza(persona , "m05bImprimirOrdenesTrabajoServlet", "sSentenciaGrupo  --> " + sSentenciaGrupo, m05bConstantes.LOG_NIVEL_BAJO);
			        if (rsR.next()){
					    sGRango=rsR.getString(1)==null ? "":rsR.getString(1);	
			            sGRango=miComun.ListaCaracteres(sGRango);
			            miLog.addTraza(persona , "m05bImprimirOrdenesTrabajoServlet", "Buscar para sGRango  --> " + sGRango, m05bConstantes.LOG_NIVEL_BAJO);
				    	sql+= " AND (SELECT " + m05bConstantes.COL_34_RANGO+ 
		    					" FROM " + m05bConstantes.TAB_VEHICULOS + 
		    					" WHERE " + m05bConstantes.COL_34_MATRICULA + " = " + m05bConstantes.COL_20_MATRICULA +
		    					") in ( "+sGRango + ")";						    									    							           
			       }
			       if (rsR != null)rsR.close();
				   if (pstmtR != null)pstmtR.close();

				      
	       }			       



		sql += " order by "+m05bConstantes.COL_20_CODIGO+" desc,"+m05bConstantes.COL_20_FENTRADA;				   

        miLog.addTraza(persona , "m05b - m05bImprimirOrdenesTrabajoServlet:  " , "sSentencia: " + sql, m05bConstantes.LOG_NIVEL_BAJO);        
        String colOrden ="", colMatricula ="", colFEnt = "", colFSal = "", colKm = "", colRep="", colConductor="";
	 
       	 pstmt = conn.createStatement();
		 pstmt.execute(sql);
		 rs = pstmt.getResultSet();
		 
      	 m05bCamposInformes miOrden = new m05bCamposInformes();

         String xlnetsini = miComun.leerPropiedad("xlnets");

         String sFoFileDir = "/datos/m05b/tmp/";
         File foFileDir = new File(sFoFileDir);
         File tmpFoFile = File.createTempFile("m05b", "Fo", foFileDir);

         FileOutputStream tmpFoFileStream = new FileOutputStream(tmpFoFile);

         String sLinea;

         sLinea = "<?xml version=\"1.0\" encoding=\"windows-1252\"?>\n";
         tmpFoFileStream.write(sLinea.getBytes());
         
         sLinea = "<fo:root xmlns:fo=\"http://www.w3.org/1999/XSL/Format\">\n";
         tmpFoFileStream.write(sLinea.getBytes());
		 // LAYOUT DEL LISTADO 
	         sLinea = "<fo:layout-master-set>\n";
	         tmpFoFileStream.write(sLinea.getBytes());
		         sLinea = "<fo:simple-page-master  master-name=\"general\" page-height=\"29.5cm\" page-width=\"21.5cm\" margin-top=\"0cm\" margin-bottom=\"3cm\" margin-left=\"2cm\" margin-right=\"1cm\">\n";
			     tmpFoFileStream.write(sLinea.getBytes());
			         sLinea = "		<fo:region-before extent=\"7cm\"/>\n";
			         tmpFoFileStream.write(sLinea.getBytes());
			         sLinea = "		<fo:region-body margin-bottom=\"1cm\" margin-top=\"3cm\"/>\n";
			         tmpFoFileStream.write(sLinea.getBytes());
			         sLinea = "		<fo:region-after extent=\"0.5cm\"/>\n";
			         tmpFoFileStream.write(sLinea.getBytes());
			         sLinea = "		<fo:region-start extent=\"0cm\"/>\n";
			         tmpFoFileStream.write(sLinea.getBytes());
			         sLinea = "		<fo:region-end extent=\"0cm\"/>\n";
			         tmpFoFileStream.write(sLinea.getBytes());
		         sLinea = "	</fo:simple-page-master>\n";
		         tmpFoFileStream.write(sLinea.getBytes());
		         
		         
 
		         	 
	         sLinea = "</fo:layout-master-set>\n";
	         tmpFoFileStream.write(sLinea.getBytes());
//------------------------------------------------------------------------------------------------------------------------
 	sLinea = "<fo:page-sequence master-reference=\"general\">\n";
        tmpFoFileStream.write(sLinea.getBytes());
//CABECERA
        	sLinea = "		<fo:static-content flow-name=\"xsl-region-before\">\n";
        	tmpFoFileStream.write(sLinea.getBytes());
        	//sLinea = "		     <fo:block position=\"absolute\" top=\"0cm\" left=\"2.4cm\" width=\"12cm\" height=\"1.7cm\">\n";
	  	sLinea = "		<fo:block-container position=\"absolute\" top=\"0cm\" left=\"2.4cm\" width=\"12cm\" height=\"1.7cm\">\n";
        	tmpFoFileStream.write(sLinea.getBytes());
		//  Escudo
        	sLinea = "			<fo:external-graphic src=\"" + "/datos/m05b/file/images/fop/cabeceraHacFin.jpg\" height=\"1.7cm\" width=\"12cm\"/>\n";
       		tmpFoFileStream.write(sLinea.getBytes());
       		//sLinea = "		     </fo:block>\n";
        	sLinea = "      </fo:block-container>\n";
        	tmpFoFileStream.write(sLinea.getBytes());
       sLinea = "		<fo:block-container position=\"absolute\" top=\"2.5cm\" left=\"2.4cm\" width=\"12cm\" height=\"1.7cm\">\n";
        	tmpFoFileStream.write(sLinea.getBytes()); 	
        	// Titulo		
       		sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"10pt\" text-align=\"center\"  >\n " + res.getString("infRelOt.titulo") + "</fo:block>\n";
	        tmpFoFileStream.write(sLinea.getBytes());
		//sLinea = "		     </fo:block>\n";
        	sLinea = "      </fo:block-container>\n";
        	tmpFoFileStream.write(sLinea.getBytes());
        	
        	
		            sLinea = "      <fo:block-container position=\"absolute\" top=\"2.5cm\" left=\"2.4cm\" width=\"15cm\" height=\"2.75cm\">\n";
		            tmpFoFileStream.write(sLinea.getBytes());
		            	// Detalle-1
		            	sLinea= "       <fo:table>\n";
		            	tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-column column-width=\"178mm\"/>\n";
			            	tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-body font-family=\"Helvetica\" font-size=\"10pt\">\n";
			            	tmpFoFileStream.write(sLinea.getBytes());
			            		sLinea = "	    <fo:table-row>\n";
			            		tmpFoFileStream.write(sLinea.getBytes());
			            			//  Blanco1
			            			sLinea = "	    <fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
                  		            	// Departamento
			                                sLinea = "			<fo:block text-align=\"left\"  font-family=\"Helvetica\" font-weight=\"bold\" font-size=\"8pt\"   >\n ";
			                                tmpFoFileStream.write(sLinea.getBytes());
                   			              sLinea = "	    	</fo:block>\n";
		                                      tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            		sLinea = "	    </fo:table-row>\n";
  			            		tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    </fo:table-body>\n";
			            	tmpFoFileStream.write(sLinea.getBytes());			            								            		
			            sLinea= "       </fo:table>\n";
		            	tmpFoFileStream.write(sLinea.getBytes());
		            sLinea = "      </fo:block-container>\n";
		            tmpFoFileStream.write(sLinea.getBytes());

        	sLinea = " 		</fo:static-content>\n"; 
        	tmpFoFileStream.write(sLinea.getBytes());
//PIE
        	sLinea = "		<fo:static-content flow-name=\"xsl-region-after\">\n";
        	tmpFoFileStream.write(sLinea.getBytes());
                sLinea = "                 <fo:block line-height=\"14pt\" font-size= \"8pt\" text-align=\"end\">"+res.getString("pagina")+"<fo:page-number/></fo:block>\n";
        	tmpFoFileStream.write(sLinea.getBytes());
        	sLinea = " 		</fo:static-content>\n"; 
        	tmpFoFileStream.write(sLinea.getBytes());
//------------------------------------------------------------------------------------------------------------------------------


		 // PAGE-SEQUENCE DEL LISTADO 
	
		         sLinea = "	<fo:flow flow-name=\"xsl-region-body\">\n";
		         tmpFoFileStream.write(sLinea.getBytes());

		         // --------------    CABECERA DEL LISTADO    --------------
                        //Cabecera 1
		        //bb    sLinea = "		<fo:block-container position=\"absolute\" top=\"0cm\" left=\"3.4cm\" width=\"12cm\" height=\"1.7cm\">\n";
		        //bb    tmpFoFileStream.write(sLinea.getBytes());
		        //bb    	//  Escudo
			//bb            sLinea = "			<fo:external-graphic src=\"" + urlprin + "/parquemovil/images/fop/cabeceraHacFin.jpg\" height=\"1.7cm\" width=\"12cm\"/>\n";
			 //bb           tmpFoFileStream.write(sLinea.getBytes());
		         //bb   sLinea = "      </fo:block-container>\n";
		         //bb   tmpFoFileStream.write(sLinea.getBytes());

		      	
		    		sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n </fo:block>\n";
		    		tmpFoFileStream.write(sLinea.getBytes());
		    	
		    	if(!codigo.equals(""))
		    	{
		    		sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n " + res.getString("infRelOt.col1")+": " + codigo + "</fo:block>\n";
		    		tmpFoFileStream.write(sLinea.getBytes());
		    	}
		    	if(!matricula.equals(""))
		    	{
		    		sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n " + res.getString("infRelOt.col3")+": "  + matricula + "</fo:block>\n";
		    		tmpFoFileStream.write(sLinea.getBytes());
		    	}
				String nDia =  "";
				String nMes =  "";
				String nEjer = "";
				String nHora = "";
				String nMinuto="";
                String strFecha="";
		    	if(!FEntrada.equals(""))
		    	{

		    		miLog.addTraza(persona , "Rel Ordenes", "FEntrada: " + FEntrada, m05bConstantes.LOG_NIVEL_BAJO);
		    		 nDia =  FEntrada.substring(6,8);
		    		 miLog.addTraza(persona , "Rel Ordenes", "FEntrada nDia: " + nDia, m05bConstantes.LOG_NIVEL_BAJO);		    		 
	                 nMes =  FEntrada.substring(4,6);
	                 miLog.addTraza(persona , "Rel Ordenes", "FEntrada nMes: " + nMes, m05bConstantes.LOG_NIVEL_BAJO);
	                 nEjer = FEntrada.substring(0,4);
	                 miLog.addTraza(persona , "Rel Ordenes", "FEntrada nEjer: " + nEjer, m05bConstantes.LOG_NIVEL_BAJO);
			         nHora =  FEntrada.substring(8,10);
			         miLog.addTraza(persona , "Rel Ordenes", "FEntrada nHora: " + nHora, m05bConstantes.LOG_NIVEL_BAJO);
	                 nMinuto =  FEntrada.substring(10,12);
	                 miLog.addTraza(persona , "Rel Ordenes", "FEntrada nMinuto: " + nMinuto, m05bConstantes.LOG_NIVEL_BAJO);

	             	if(!idiomaSes.equals("es")){
		                if (new Integer(nMinuto).intValue() < 10) {
		                	strFecha= nEjer + "/" + nMes + "/" +  nDia+ " " + nHora + ":0" + nMinuto;
		                } else {
		                	strFecha=nEjer+ "/" + nMes + "/" + nDia + " " + nHora + ":" + nMinuto;
		        		}
	             	}
	             	else{
	             	   if (new Integer(nMinuto).intValue() < 10) {
		                	strFecha=  nDia+ "/" + nMes + "/" + nEjer + " " + nHora + ":0" + nMinuto;
		                } else {
		                	strFecha=nDia+ "/" + nMes + "/" + nEjer + " " + nHora + ":" + nMinuto;
		        		}
	             	}
					miLog.addTraza(persona , "Rel Ordenes", "FEntrada comun.formatearFechaHora - strSql: " + strFecha, m05bConstantes.LOG_NIVEL_BAJO);
		    		sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n " + res.getString("infRelOt.col3")+": "  + strFecha + "</fo:block>\n";
		    		tmpFoFileStream.write(sLinea.getBytes());
		    	}	 
		    	if(!FSalida.equals(""))
		    	{
		    		 nDia =  FSalida.substring(6,8);
	                 nMes =  FSalida.substring(4,6);
	                 nEjer = FSalida.substring(0,4);
			         nHora = FSalida.substring(8,10);
	                 nMinuto =  FSalida.substring(10,12);
	                 if(!idiomaSes.equals("es")){
			                if (new Integer(nMinuto).intValue() < 10) {
			                	strFecha= nEjer + "/" + nMes + "/" +  nDia+ " " + nHora + ":0" + nMinuto;
			                } else {
			                	strFecha=nEjer+ "/" + nMes + "/" + nDia + " " + nHora + ":" + nMinuto;
			        		}
		             	}
		             	else{
		             	   if (new Integer(nMinuto).intValue() < 10) {
			                	strFecha=  nDia+ "/" + nMes + "/" + nEjer + " " + nHora + ":0" + nMinuto;
			                } else {
			                	strFecha=nDia+ "/" + nMes + "/" + nEjer + " " + nHora + ":" + nMinuto;
			        		}
		             	}
	
		    		sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n " +  res.getString("infRelOt.col4")+": " + strFecha + "</fo:block>\n";
		    		tmpFoFileStream.write(sLinea.getBytes());
		    	}
		         
		    	if(!KmEntrada.equals(""))
		    	{
		    		sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n " +  res.getString("infRelOt.col5")+": " +KmEntrada + "</fo:block>\n";
		    		tmpFoFileStream.write(sLinea.getBytes());
		    	}		         
		    	if(!Conductor.equals(""))
		    	{
		    		sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n " +  res.getString("infRelOt.col6")+": " + Conductor+ "</fo:block>\n";
		    		tmpFoFileStream.write(sLinea.getBytes());
		    	}	
		    	String strSql="";
		    	PreparedStatement pstmtRango = null;
		    	ResultSet rsRango=null;
		    	if(!Persona.equals(""))
		    	{
		    		pstmtRango = null;
		    		rsRango = null;				 	
					try{
						strSql = "select " + m05bConstantes.COL_07_NOMBRE +			    		
			    		" FROM " + m05bConstantes.TAB_EMPTALLER +
			    		" WHERE " + m05bConstantes.COL_07_CODIGO + "(+) ='"+Persona+"' ";
						
						miLog.addTraza(persona , "Rel Ordenes", "Persona - código: " + Persona, m05bConstantes.LOG_NIVEL_BAJO);
						
						pstmtRango = conn.prepareStatement(strSql);
						miLog.addTraza(persona , "Rel Ordenes", "Persona - strSql: " + strSql, m05bConstantes.LOG_NIVEL_BAJO);
						rsRango = pstmtRango.executeQuery();							 
						if (rsRango.next()){
							miLog.addTraza(persona , "Rel Ordenes", "Persona - nombre: " + rsRango.getString(1), m05bConstantes.LOG_NIVEL_BAJO);
							sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n " + res.getString("infRelOt.col7")+":  " + rsRango.getString(1) + "</fo:block>\n";
					    	tmpFoFileStream.write(sLinea.getBytes());
					    }
					}				    	
					 catch (Exception SQLException) {
					 		miLog.addTraza(persona , "Rel Ordenes", "Error al cargar  Persona en informe: " + SQLException.getMessage(), m05bConstantes.LOG_NIVEL_BAJO);
							miLog.addTraza(SQLException);
					} finally {
							if (rsRango != null)
								rsRango.close();
							if (pstmtRango != null)
								pstmtRango.close();
					}
	
		    	}
		    	if(!strGRango.equals(""))
		    	{		    		
		    		pstmtRango = null;
		    		rsRango = null;				 	
					try{
						 strSql = " SELECT NVL(" +campoTexto+ m05bConstantes.COL_36_NOMBRE +
						 	","+m05bConstantes.COL_36_NOMBRE +")"+
	    					" FROM " + m05bConstantes.TAB_GRUPOS + 
	    					" WHERE " + m05bConstantes.COL_36_CODIGO + " = '"+strGRango+"'" ;
						miLog.addTraza(persona , "Rel Ordenes", "SQL sGRango:" + strSql, m05bConstantes.LOG_NIVEL_BAJO);
						pstmtRango = conn.prepareStatement(strSql);
						rsRango = pstmtRango.executeQuery();							 
						if (rsRango.next()){
							sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n " + res.getString("infRelOt.busc2")+":  " + rsRango.getString(1) + "</fo:block>\n";
					    	tmpFoFileStream.write(sLinea.getBytes());
					    }
					}				    	
					 catch (Exception SQLException) {
					 		miLog.addTraza(persona , "Rel Ordenes", "Error al cargar rango sGRango en informe: " + SQLException.getMessage(), m05bConstantes.LOG_NIVEL_BAJO);
							miLog.addTraza(SQLException);
					} finally {
							if (rsRango != null)
								rsRango.close();
							if (pstmtRango != null)
								pstmtRango.close();
					}
			}
						         
		    if(!sRango.equals(""))
		    {
		    	try{
					strSql = " SELECT " + m05bConstantes.COL_99_DESCRIPCION + 
		    					" FROM " + m05bConstantes.TAB_DESCRIPCIONES + 
		    					" WHERE " + m05bConstantes.COL_99_CODTABLA + " = " + m05bConstantes.DESC_RANGOS +
		    					" AND " + m05bConstantes.COL_99_CODDESC + " = '" + sRango + "'";
					pstmtRango = conn.prepareStatement(strSql);
					rsRango = pstmtRango.executeQuery();
					if (rsRango.next()){		    		
						sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n " + res.getString("infRelOt.busc3")+":  "+ rsRango.getString(1) + "</fo:block>\n";
						tmpFoFileStream.write(sLinea.getBytes());}
					}				    	
				 catch (Exception SQLException) {
				 		miLog.addTraza(persona , "Rel Ordenes", "Error al cargar rango sRango en informe: " + SQLException.getMessage(), m05bConstantes.LOG_NIVEL_BAJO);
						miLog.addTraza(SQLException);
				} finally {
						if (rsRango != null)
							rsRango.close();
						if (pstmtRango != null)
							pstmtRango.close();
				} 
		    		

		    } 
		         // --------------    LISTADO    --------------
		         // Definicion de la tabla de descripcions.
		            sLinea= "       <fo:table>\n";
		            tmpFoFileStream.write(sLinea.getBytes());
		            sLinea = "	    <fo:table-column column-width=\"17mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
		            sLinea = "	    <fo:table-column column-width=\"19mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
			    sLinea = "	    <fo:table-column column-width=\"28mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
			    sLinea = "	    <fo:table-column column-width=\"28mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
				    sLinea = "	    <fo:table-column column-width=\"22mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
				    sLinea = "	    <fo:table-column column-width=\"5mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
				    sLinea = "	    <fo:table-column column-width=\"39mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
				    sLinea = "	    <fo:table-column column-width=\"34mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());


//TABLE HEADER
		            sLinea = "	    <fo:table-header font-family=\"Helvetica\" font-size=\"10pt\" position=\"relative\" top=\"14cm\" left=\"4.4cm\" width=\"12cm\" height=\"1.7cm\">\n";
			        tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-row>\n";
		            		tmpFoFileStream.write(sLinea.getBytes());
			            			//  Blanco1
			            			sLinea = "	    <fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"20pt\" text-align=\"left\"  >\n " + res.getString("infRelOt.col1") + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"0pt\" text-align=\"left\"  >\n " + "-----------" + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			//  Blanco1
			            			sLinea = "	    <fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"20pt\" text-align=\"left\"  >\n " + res.getString("infRelOt.col2") + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"0pt\" text-align=\"left\"  >\n " + "------------" + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			//  Blanco1
			            			sLinea = "	    <fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"20pt\" text-align=\"left\"  >\n " + res.getString("infRelOt.col3") + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"0pt\" text-align=\"left\"  >\n " + "---------------------" + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			//  Blanco1
			            			sLinea = "	    <fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"20pt\" text-align=\"left\"  >\n " + res.getString("infRelOt.col4") + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"0pt\" text-align=\"left\"  >\n " + "----------------------" + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());			            			
			            			//  Blanco1
			            			sLinea = "	    <fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"20pt\" text-align=\"left\" >\n " + res.getString("infRelOt.col5") + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"0pt\" text-align=\"left\"  >\n " + "     ---------------------" + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			//  Columna en blanco
			            			sLinea = "	    <fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"20pt\" text-align=\"left\" >\n " + "  " + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"0pt\" text-align=\"left\"  >\n " + "  " + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());				            			
			            			//  Blanco1
			            			sLinea = "	    <fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"20pt\" text-align=\"left\"  >\n " + res.getString("infRelOt.col6")+ "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"0pt\" text-align=\"left\"  >\n " + "------------------------" + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			//  Blanco1
			            			sLinea = "	    <fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"20pt\" text-align=\"left\"  >\n " + res.getString("infRelOt.col7")  + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"0pt\" text-align=\"left\"  >\n " + "------------------" + "</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			//  Blanco2
			            			sLinea = "	    <fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block border-width=\"0.05mm\" border-style=\"solid\" > </fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            		sLinea = "	    </fo:table-row>\n";
			            		tmpFoFileStream.write(sLinea.getBytes());
			        sLinea = "	    </fo:table-header>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
			        
		            sLinea = "	    <fo:table-body font-family=\"Helvetica\" font-size=\"9pt\" position=\"relative\" top=\"14cm\" left=\"4.4cm\" width=\"12cm\" height=\"1.7cm\">\n";
			        tmpFoFileStream.write(sLinea.getBytes());
			        			        
			        
			        NumberFormat formatter = new DecimalFormat("#0.000");			        
			        int intCont=0;//Agurtzane Mar12: contador en listados       
		   		    while (rs.next()){	       	    			
		   		    	intCont++;//Agurtzane Mar12: contador en listados
			    		colOrden = rs.getString(1)==null ? "":rs.getString(1);                            
			    		colMatricula= rs.getString(2)==null ? "":rs.getString(2);
			    		colFEnt = rs.getString(3)==null ? "":rs.getString(3);
			    		colKm = rs.getString(4)==null ? "":rs.getString(4)  ;
			    		colRep = rs.getString(5)==null ? "":  rs.getString(5);
			    		colFSal = rs.getString(6)==null ? "":rs.getString(6);
			    		colConductor = rs.getString(7)==null ? "":rs.getString(7);

	            		sLinea = "	    <fo:table-row>\n";
	            		tmpFoFileStream.write(sLinea.getBytes());
            			//  Blanco1
            			sLinea = "	    <fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
				            sLinea = "      <fo:block  padding-top=\"6pt\" text-align=\"left\"  >\n<![CDATA[ " + colOrden + "]]></fo:block>\n";
				            tmpFoFileStream.write(sLinea.getBytes());
            			sLinea = "	    </fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
            			//  Blanco2
            			sLinea = "	    <fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
				            sLinea = "      <fo:block text-align=\"left\"  padding-top=\"6pt\">\n<![CDATA[ " + colMatricula + "]]></fo:block>\n";
				            tmpFoFileStream.write(sLinea.getBytes());
            			sLinea = "	    </fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
            			//  Blanco3
            			sLinea = "	    <fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
				            sLinea = "      <fo:block text-align=\"left\"  padding-top=\"6pt\">\n <![CDATA[" + colFEnt + "]]></fo:block>\n";
				            tmpFoFileStream.write(sLinea.getBytes());
            			sLinea = "	    </fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
            			//  Blanco6
            			sLinea = "	    <fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
				            sLinea = "      <fo:block text-align=\"left\"  padding-top=\"6pt\">\n <![CDATA[" + colFSal + "]]></fo:block>\n";
				            tmpFoFileStream.write(sLinea.getBytes());
            			sLinea = "	    </fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());          			
            			//  Blanco4
            			sLinea = "	    <fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
				            sLinea = "      <fo:block text-align=\"right\" padding-top=\"6pt\">\n<![CDATA[ " + colKm + "]]></fo:block>\n";
				            tmpFoFileStream.write(sLinea.getBytes());
            			sLinea = "	    </fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
            			//  Columna en blanco
            			sLinea = "	    <fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
				            sLinea = "      <fo:block text-align=\"right\" padding-top=\"6pt\">\n " + "  " + "</fo:block>\n";
				            tmpFoFileStream.write(sLinea.getBytes());
            			sLinea = "	    </fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());             			
            			//  Blanco5
            			sLinea = "	    <fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
				            sLinea = "      <fo:block text-align=\"left\"  padding-top=\"6pt\">\n <![CDATA[" + colRep + "]]></fo:block>\n";
				            tmpFoFileStream.write(sLinea.getBytes());
            			sLinea = "	    </fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
            			//  Blanco7
            			sLinea = "	    <fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());
				        sLinea = "      <fo:block text-align=\"left\"  padding-top=\"6pt\">\n <![CDATA[" + colConductor + "]]></fo:block>\n";
				        tmpFoFileStream.write(sLinea.getBytes());
				        
            			sLinea = "	    </fo:table-cell>\n";
            			tmpFoFileStream.write(sLinea.getBytes());            			
            			sLinea = "	    </fo:table-row>\n";
            			tmpFoFileStream.write(sLinea.getBytes());

				}		   		 
           		rs.close();
           		
           		
           		//Agurtzane Mar12: contador en listados
           		
           		//Fila de separación
           		/*for (int i=0;i<3;i++){
	           		sLinea = "	    <fo:table-row>\n";
	        		tmpFoFileStream.write(sLinea.getBytes());
	        		for (int j=0;j<8;j++){
	        			sLinea = "	    <fo:table-cell>\n<fo:block>\n " + "     " + "</fo:block>\n</fo:table-cell>\n";
	        			tmpFoFileStream.write(sLinea.getBytes());
	        		}
	        		sLinea = "	    </fo:table-row>\n";
	    			tmpFoFileStream.write(sLinea.getBytes());
           		}*/
           		sLinea = "	    <fo:table-row>\n";
        		tmpFoFileStream.write(sLinea.getBytes());
        		//vacías
        		for (int i=0;i<7;i++){
        			sLinea = "	    <fo:table-cell>\n<fo:block>\n</fo:block>\n</fo:table-cell>\n";
        			tmpFoFileStream.write(sLinea.getBytes());
        		}
    			//Contador
    			sLinea = "	    <fo:table-cell>\n";
    			tmpFoFileStream.write(sLinea.getBytes());
		        sLinea = "      <fo:block font-weight=\"bold\" text-align=\"left\"  padding-top=\"26pt\">\n " + res.getString("infRelOt.total") + intCont + "</fo:block>\n";
		        tmpFoFileStream.write(sLinea.getBytes());
		        //sLinea = "      <fo:block text-align=\"right\"  padding-top=\"6pt\">\n " + intCont + "</fo:block>\n";
		        //tmpFoFileStream.write(sLinea.getBytes());
		        ////sLinea = "      <xsl:value-of select="+ intCont+ "/>\n"; 
    			sLinea = "	    </fo:table-cell>\n";
    			tmpFoFileStream.write(sLinea.getBytes());
    			sLinea = "	    </fo:table-row>\n";
    			tmpFoFileStream.write(sLinea.getBytes());
           		//Fin Agurtzane Mar12: contador en listados
           		
 
          		sLinea = "	    </fo:table-body>\n";
            	tmpFoFileStream.write(sLinea.getBytes());
            sLinea= "       </fo:table>\n";
           	tmpFoFileStream.write(sLinea.getBytes());
 
         sLinea = "      </fo:flow>\n";
         tmpFoFileStream.write(sLinea.getBytes());
   	 sLinea = "   </fo:page-sequence>\n";
     tmpFoFileStream.write(sLinea.getBytes());
   sLinea = "</fo:root>\n";
   tmpFoFileStream.write(sLinea.getBytes());
   
  tmpFoFileStream.close();
  		miLog.addTraza(persona , "m05b - m05bImprimirOrdenesTrabajoServlet:  " , "FileInputStream file : " + sFoFileDir + tmpFoFile.getName() , m05bConstantes.LOG_NIVEL_BAJO);  
         FileInputStream file = new FileInputStream(sFoFileDir + tmpFoFile.getName());
                         
         renderFO(new InputSource(file), response); //ésta da error
                  
         tmpFoFile.delete();
                  
         response.flushBuffer();                

      } catch (Exception ex) {
    	  miLog.addTraza(persona , "m05b - m05bImprimirOrdenesTrabajoServlet:  " , "Error generar informe: " + ex.getMessage(), m05bConstantes.LOG_NIVEL_BAJO);
      		try {
				response.sendRedirect("m05bError.jsp");
			} catch (IOException e) {
				miLog.addTraza(persona , "m05b - m05bImprimirOrdenesTrabajoServlet:  " , "Error generar informe IO: " + e.getMessage(), m05bConstantes.LOG_NIVEL_BAJO);
				// TODO Auto-generated catch block
				miLog.addTraza(e);
			}
      } finally{
	   	    try {
				if (rs != null)
        			rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e2) {
				try {
					response.sendRedirect("m05bError.jsp");
				} catch (IOException e) {
					// TODO Auto-generated catch block
					miLog.addTraza(e);
				}
	  		}
	   }
    }

    /**
     * renders an FO inputsource into a PDF file which is rendered
     * directly to the response object's OutputStream
     */
    public void renderFO(InputSource foFile, HttpServletResponse response) throws ServletException
    {
        try
        {
            ByteArrayOutputStream out = new ByteArrayOutputStream();

	    	response.reset();
            OutputStream oOut=response.getOutputStream();
            BufferedOutputStream bOut=new BufferedOutputStream(oOut);
            Driver driver = new Driver(foFile, out);
            org.apache.avalon.framework.logger.Logger logger = new org.apache.avalon.framework.logger.ConsoleLogger(org.apache.avalon.framework.logger.ConsoleLogger.LEVEL_FATAL);
            org.apache.fop.messaging.MessageHandler.setScreenLogger(logger);
            driver.setLogger(logger);
            driver.setRenderer(Driver.RENDER_PDF);
            driver.run();

            response.setContentLength(out.size());
	    response.setContentType("application/pdf");


            bOut.write(out.toByteArray());
	    out.close();
	    bOut.flush();
            bOut.close();
        
//	    response.flushBuffer();
        } catch (Exception ex) {
            throw new ServletException(ex);
        }
    }
    
    
}