package asignacionServlet;

import java.io.BufferedInputStream;
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.util.Locale;
import java.util.ResourceBundle;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
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.m05bConstantesInformes;
import m05bClasses.m05bFileLog;

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



public class m05bAsigEntreFechasServlet extends javax.servlet.http.HttpServlet
{
		java.sql.Connection conn=null;
		String sLinea;
        String sFoFileDir = "/datos/m05b/tmp/";
        File foFileDir ;
        File tmpFoFile ;
		FileOutputStream tmpFoFileStream ;
	    m05bComun miComun = new m05bComun();
		m05bFileLog miLog = new m05bFileLog();
	    
	    String sMatricula="";
	    String sFecDesde="";
	    String sFecHasta="";
	    String sSituacion="";
	    String sRango="";
	    String sDesDpto="";
	    String sDesRango="";
	    String sSituacionDesc="";
	    String sFinalizada="";

   public static final String FO_REQUEST_PARAM = "fo";
//    Logger log = null;

   public void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, java.io.IOException
   {
      performTask(request, response);
   }

   public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException
   {
      performTask(request, response);
   }
//        if(log == null)
//        {
//            Hierarchy hierarchy = Hierarchy.getDefaultHierarchy();
//            log = hierarchy.getLoggerFor("fop");
//            log.setPriority(Priority.WARN);
//        }

private void fncAbrirFicheroTemporal() throws java.io.IOException
{
         foFileDir = new File(sFoFileDir);
         tmpFoFile = File.createTempFile("m05b", "Fo", foFileDir);
		 tmpFoFileStream = new FileOutputStream(tmpFoFile);

}	
private void fncImprimirFinPagina()throws java.io.IOException
{
     	    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());
	  
}	
private void fncImprimirComienzoPagina(	ResourceBundle res )throws java.io.IOException, javax.naming.NamingException
{
	         sLinea = "<fo:page-sequence master-reference=\"general\">\n";
	         tmpFoFileStream.write(sLinea.getBytes());
             //REGION BEFORE
        	 sLinea = "		<fo:static-content flow-name=\"xsl-region-before\" >\n";
        	 tmpFoFileStream.write(sLinea.getBytes());
		     // --------------    CABECERA DEL LISTADO    --------------
             //Cabecera 1
		       sLinea = "      <fo:block text-align=\"end\">\n";
		       tmpFoFileStream.write(sLinea.getBytes());
		       sLinea = "      </fo:block>\n";
		       tmpFoFileStream.write(sLinea.getBytes());
               sLinea = "      <fo:block font-size=\"14pt\" font-weight=\"bold\" padding-top=\"10pt\">\n " + res.getString("infAsigDat.titulo")+ sMatricula;
			   tmpFoFileStream.write(sLinea.getBytes());
			   if (!(sFecDesde.equals(""))){
                sLinea = "     <fo:leader  font-size=\"9pt\" leader-pattern=\"space\" leader-length=\"0.5cm\"/> " + res.getString("infAsigDat.col2")+" : " + sFecDesde + res.getString("infAsigDat.col3")+": "+sFecHasta+ "</fo:block> \n";
			    tmpFoFileStream.write(sLinea.getBytes());
			   }else{
                sLinea =  "</fo:block> \n";
			    tmpFoFileStream.write(sLinea.getBytes());
			   }
               sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n <![CDATA["+ res.getString("infAsigDat.texto1") + sDesRango +"]]></fo:block>\n";
			   tmpFoFileStream.write(sLinea.getBytes());
               sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n <![CDATA["+ res.getString("infAsigDat.texto2") + sDesDpto +"]]></fo:block>\n";
			   tmpFoFileStream.write(sLinea.getBytes());
		       if (!(sSituacion.equals(""))){
                    sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n " + res.getString("infAsigDat.texto3") +sSituacionDesc+"</fo:block>\n";
			        tmpFoFileStream.write(sLinea.getBytes());}
		        if (sFinalizada.equals("1")){
			           sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\" >\n "+ res.getString("infAsigDat.texto4")+"</fo:block>\n";
					   tmpFoFileStream.write(sLinea.getBytes());
		        }else if (sFinalizada.equals("2")){
			           sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\" >\n "+ res.getString("infAsigDat.texto5")+"  </fo:block>\n";
					   tmpFoFileStream.write(sLinea.getBytes());
		        }
        	 sLinea = " 		</fo:static-content>\n"; 
        	 tmpFoFileStream.write(sLinea.getBytes());
            //PIE DE PAGINA Pie
        	 sLinea = "		<fo:static-content flow-name=\"xsl-region-after\">\n";
        	 tmpFoFileStream.write(sLinea.getBytes());
                    //PIE DE PAGINA Pie
		     		sLinea = "      <fo:block >\n";
		            tmpFoFileStream.write(sLinea.getBytes());
		            	sLinea= "       <fo:table table-layout=\"fixed\" width=\"160mm\">\n";
		            	tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-column column-width=\"80mm\"/>\n";
			            	tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-column column-width=\"80mm\"/>\n";
			            	tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-body font-family=\"Helvetica\" font-size=\"6pt\">\n";
			            	tmpFoFileStream.write(sLinea.getBytes());
			            		sLinea = "	    <fo:table-row>\n";
			            		tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    <fo:table-cell >\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block text-align=\"left\">" + miComun.getDetFechaActual(m05bConstantesInformes.IDIOMA_C)+ "</fo:block>";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    <fo:table-cell >\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			    sLinea = "      <fo:block text-align=\"right\">" ; 
                       		            tmpFoFileStream.write(sLinea.getBytes());
		                                sLinea = "Página <fo:page-number/> \n";
                        	            tmpFoFileStream.write(sLinea.getBytes());
							            sLinea=  "</fo:block>";
							            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>\n";
		            tmpFoFileStream.write(sLinea.getBytes());
        	sLinea = " 		</fo:static-content>\n"; 
        	tmpFoFileStream.write(sLinea.getBytes());
           //BODY
		         sLinea = "	<fo:flow flow-name=\"xsl-region-body\">\n";
		         tmpFoFileStream.write(sLinea.getBytes());
		         Context ctx =  new InitialContext(System.getProperties());
		           	sLinea= "        <fo:table table-layout=\"fixed\" width=\"160mm\" space-after.optimum=\"12pt\">\n";
		            tmpFoFileStream.write(sLinea.getBytes());
		            sLinea = "	    <fo:table-column column-width=\"19mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
		            sLinea = "	    <fo:table-column column-width=\"23mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
		            sLinea = "	    <fo:table-column column-width=\"23mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
			        sLinea = "   <fo:table-column column-width=\"15mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
			        sLinea = "   <fo:table-column column-width=\"50mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
		            sLinea = "	    <fo:table-column column-width=\"15mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
		            sLinea = "	    <fo:table-column column-width=\"10mm\"/>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
		            sLinea = "	    <fo:table-body font-family=\"Helvetica\" font-size=\"6pt\" position=\"relative\" top=\"14cm\" left=\"2cm\" width=\"12cm\" height=\"1.7cm\">\n";
			        tmpFoFileStream.write(sLinea.getBytes());
	       	    	  sLinea = "	    <fo:table-row>\n";
			          tmpFoFileStream.write(sLinea.getBytes());
		              // Matrícula
			          sLinea = "	    <fo:table-cell padding-top=\"2mm\">\n";
			          tmpFoFileStream.write(sLinea.getBytes());
					  sLinea = "      <fo:block font-weight=\"bold\" font-size=\"7pt\" text-align=\"center\">\n "+res.getString("infAsigDat.col1")+"</fo:block>\n";
					  tmpFoFileStream.write(sLinea.getBytes());
			          sLinea = "	    </fo:table-cell>\n";
			          tmpFoFileStream.write(sLinea.getBytes());	
			          // DEsde
			          sLinea = "	    <fo:table-cell padding-top=\"2mm\">\n";
			          tmpFoFileStream.write(sLinea.getBytes());
					  sLinea = "      <fo:block font-weight=\"bold\" font-size=\"7pt\" text-align=\"center\">\n "+res.getString("infAsigDat.col2")+ "</fo:block>";
					  tmpFoFileStream.write(sLinea.getBytes());
			          sLinea = "	    </fo:table-cell>\n";
			          tmpFoFileStream.write(sLinea.getBytes());
			          // Hasta
			          sLinea = "	    <fo:table-cell padding-top=\"2mm\">\n";
			          tmpFoFileStream.write(sLinea.getBytes());
					  sLinea = "      <fo:block font-weight=\"bold\"  font-size=\"7pt\"  text-align=\"center\">\n "+res.getString("infAsigDat.col3")+"</fo:block>\n";
					  tmpFoFileStream.write(sLinea.getBytes());
			          sLinea = "	    </fo:table-cell>\n";
			          tmpFoFileStream.write(sLinea.getBytes());
     			      //dni
			          sLinea = "	    <fo:table-cell padding-top=\"2mm\">\n";
			          tmpFoFileStream.write(sLinea.getBytes());
					  sLinea = "      <fo:block font-weight=\"bold\" font-size=\"7pt\" text-align=\"center\">\n </fo:block>\n";
					  tmpFoFileStream.write(sLinea.getBytes());
			          sLinea = "	    </fo:table-cell>\n";
			          tmpFoFileStream.write(sLinea.getBytes());	
                      //nombre
			          sLinea = "	    <fo:table-cell padding-top=\"2mm\">\n";
			          tmpFoFileStream.write(sLinea.getBytes());
					  sLinea = "      <fo:block font-weight=\"bold\" font-size=\"7pt\" text-align=\"center\">\n "+res.getString("infAsigDat.col4")+" </fo:block>\n";
					  tmpFoFileStream.write(sLinea.getBytes());
			          sLinea = "	    </fo:table-cell>\n";
			          tmpFoFileStream.write(sLinea.getBytes());				            			
     			      // Finalizada
			          sLinea = "	    <fo:table-cell padding-top=\"2mm\">\n";
			          tmpFoFileStream.write(sLinea.getBytes());
					  sLinea = "      <fo:block font-weight=\"bold\" font-size=\"7pt\" text-align=\"center\">\n "+res.getString("infAsigDat.col5")+"</fo:block>\n";
					  tmpFoFileStream.write(sLinea.getBytes());
			          sLinea = "	    </fo:table-cell>\n";
			          tmpFoFileStream.write(sLinea.getBytes());	
     			      // Nº Asig
			          sLinea = "	    <fo:table-cell padding-top=\"2mm\">\n";
			          tmpFoFileStream.write(sLinea.getBytes());
					  sLinea = "      <fo:block font-weight=\"bold\" font-size=\"8pt\" text-align=\"center\">\n "+res.getString("infAsigDat.col6")+"</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-row >\n";
			          tmpFoFileStream.write(sLinea.getBytes());
	

}
private void imprimirTotal(int intCont, boolean bolGeneral,	ResourceBundle res)throws java.io.IOException, javax.naming.NamingException
{
		sLinea = "	    <fo:table-row>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		//vacías
		for (int i=0;i<4;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());
		if(bolGeneral)
			sLinea = "      <fo:block font-weight=\"bold\" text-align=\"right\"  padding-top=\"26pt\">\n " +   res.getString("infAsigDat.texto6")  + intCont + "</fo:block>\n";
		else
			sLinea = "      <fo:block font-weight=\"bold\" text-align=\"right\"  padding-top=\"26pt\">\n " + res.getString("infAsigDat.texto7")  + intCont + "</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());	       	    	    			
		//intContParcial=0;	       	    	    	
}
/**
 * Initializes the servlet.
 */
   public void performTask(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws ServletException
   {
	   java.sql.Connection conn=null;
	   PreparedStatement pstmt = null;
	   ResultSet rs = null;
      try
      {  HttpSession session = request.getSession(true);
      
      /*Iker. idioma*/
		Locale idiomaLocal = (Locale)session.getAttribute("Local");
		String idiomaSes = ((idiomaLocal.getLanguage()==null)?"es":idiomaLocal.getLanguage());	
		String campoTexto="";		
		String campoTexto2="";
		String formatoFecha="dd/mm/YYYY ";
		String []textos=null;
		ResourceBundle res =  (ResourceBundle)session.getAttribute("resTit");
		if(!idiomaSes.equals("es")){
			campoTexto="E";
			campoTexto2="E_";
			formatoFecha="YYYY/mm/dd ";
			
		}
			m05bConexion miConex = new m05bConexion();
			conn = miConex.getConnection();
      	 // Obtención de información de BD
      	 	 	// Vehículo
      	 //Recogemos la situacion
          sSituacion=request.getParameter("Situacion");
            	if ((sSituacion==null)||(sSituacion.equals(""))){
					sSituacion="";}
			    else {
                    sSituacion=miComun.ListaCaracteres(sSituacion);} 
      	 //Recogemos el Grupo Rango
         String sGRango=request.getParameter("GRango");
            	if ((sGRango==null)||(sGRango.equals(""))){
					sGRango="";}
      	 //Recogemos el Rango
          sRango=request.getParameter("Rango");
            	if ((sRango==null)||(sRango.equals(""))){
					sRango="";}
      	 //Recogemos el Departamento
         String sCodDpto=request.getParameter("CodDpto");
            	if (sCodDpto==null){
					sCodDpto="";}
      	 //Recogemos Matricula
         sMatricula=request.getParameter("Matricula");
            	if (sMatricula==null){
					sMatricula="";}
	     //Recogemos FecDesde por:
         sFecDesde=request.getParameter("FecDesde");
            	if (sFecDesde==null){
					sFecDesde="";}
	     //Recogemos FecHasta por:
         sFecHasta=request.getParameter("FecHasta");
            	if (sFecHasta==null){
					sFecHasta="";}
         //Recogemos Finalizada
         sFinalizada=request.getParameter("Finalizada");
                if ((sFinalizada==null)||(sFinalizada.equals(""))){
                    sFinalizada="";}		

	     String sSituacionD="";
	     sSituacionDesc="";
	     
if (!(sSituacion.equals(""))){
     String sSentenciaSituacion=" SELECT NVL(" +
		campoTexto+m05bConstantes.COL_99_DESCRIPCION+
		","+m05bConstantes.COL_99_DESCRIPCION+
            ") FROM "+
		       m05bConstantes.TAB_DESCRIPCIONES+"  S "+
            " WHERE " +
               m05bConstantes.COL_99_CODDESC+ " in ("+sSituacion+") AND " + 
               m05bConstantes.COL_99_CODTABLA+ "='"+ m05bConstantes.DESC_SITUACIONES+"'";	
     pstmt = conn.prepareStatement(sSentenciaSituacion);
     rs = pstmt.executeQuery();
     while (rs.next()){
	     sSituacionD=rs.getString(1);
         if (!(sSituacionDesc.equals(""))){
    	    sSituacionDesc =sSituacionDesc+","+sSituacionD;}
         else{
    	    sSituacionDesc =sSituacionD;}
     }
     if (rs != null)
	 rs.close();
     if (pstmt != null)
	 pstmt.close();
}	 
					
String sSentencia= " SELECT TO_CHAR(" + 
m05bConstantes.COL_01_FDESDE + ",'"+formatoFecha+" HH24:mi:ss') ,TO_CHAR(" + 
m05bConstantes.COL_01_FHASTA + ",'"+formatoFecha+" HH24:mi:ss') ," +
m05bConstantes.COL_01_MATRICULA + " ," + 
m05bConstantes.COL_01_DNI + " ," + 
m05bConstantes.COL_21_NOMBRE + ", " + 
m05bConstantes.COL_01_FINALIZA + " , " + 
m05bConstantes.COL_01_CODASIG+ " , " + 
m05bConstantes.COL_01_CODRANGO + " , " + 
m05bConstantes.COL_01_CODDPTO + ", NVL("+
campoTexto2+m05bConstantes.COL_06_DESCDPTO+","+m05bConstantes.COL_06_DESCDPTO+ "),NVL("+
campoTexto+m05bConstantes.COL_99_DESCRIPCION+","+m05bConstantes.COL_99_DESCRIPCION+")"	+
" FROM " + 
m05bConstantes.TAB_ASIGNACION + " ," + 
m05bConstantes.TAB_PERSONAL + " ," +
m05bConstantes.TAB_VEHICULOS+ ","+
m05bConstantes.TAB_DEPARTAMENTOS+","+
m05bConstantes.TAB_DESCRIPCIONES +	
" WHERE " + 
m05bConstantes.COL_01_DNI + " = " + 
m05bConstantes.COL_21_DNI + " AND " + 
m05bConstantes.COL_01_MATRICULA + " = " + 
m05bConstantes.COL_34_MATRICULA + " AND "+
m05bConstantes.COL_06_CODDPTO+"=" +
m05bConstantes.COL_01_CODDPTO + " AND "+
m05bConstantes.COL_99_CODTABLA+ " = " +
m05bConstantes.DESC_RANGOS + " AND "+
m05bConstantes.COL_99_CODDESC + " = " + m05bConstantes.COL_01_CODRANGO;

if (!(sSituacion.equals(""))){
    	sSentencia= sSentencia + " AND " + m05bConstantes.COL_34_SITUACION+ " in ("+sSituacion+")";}
if (!(sFecDesde.equals(""))){
        String sFecDesde2=miComun.formatearFecha2(sFecDesde);
        String sFecHasta2=miComun.formatearFecha2(sFecHasta);

    	sSentencia= sSentencia + " AND (( TO_CHAR("+m05bConstantes.COL_01_FDESDE +",'YYYY/mm/dd')<='"+sFecHasta2+"')"+ 
" AND   (("+m05bConstantes.COL_01_FDEVOL+" Is Null )"+ 
" OR    ( TO_CHAR("+m05bConstantes.COL_01_FHASTA +",'YYYY/mm/dd')>='"+sFecDesde2+"')))";}
if (!(sCodDpto.equals(""))){
    	sSentencia= sSentencia + " AND " +  m05bConstantes.COL_01_CODDPTO+ " = '"+sCodDpto+"'" ;}
if (!(sMatricula.equals(""))){
    	sSentencia= sSentencia + " AND " +  m05bConstantes.COL_34_MATRICULA+ " = '"+sMatricula+"'" ;}
if (sFinalizada.equals("1")){
	sSentencia= sSentencia + " AND " + m05bConstantes.COL_01_FINALIZA+ " = 'S'" ;}  
else if (sFinalizada.equals("2")){
	sSentencia= sSentencia + " AND " + m05bConstantes.COL_01_FINALIZA+ " = 'N'" ;}     	
if (!(sRango.equals(""))){
    	sSentencia= sSentencia + " AND " + m05bConstantes.COL_01_CODRANGO+ " = '"+sRango+"'" ;}
else if (!(sGRango.equals(""))){
    	String sSentenciaGrupo=" SELECT "+ m05bConstantes.COL_36_RANGOS+
    	                " FROM " + m05bConstantes.TAB_GRUPOS + 
    	                " WHERE " + m05bConstantes.COL_36_CODIGO + " = '" +sGRango+"'";
    	pstmt = conn.prepareStatement(sSentenciaGrupo);
		rs = pstmt.executeQuery();
        if (rs.next()){
		    sGRango=rs.getString(1)==null ? "":rs.getString(1);	
            sGRango=miComun.ListaCaracteres(sGRango);
    	sSentencia= sSentencia + " AND " + m05bConstantes.COL_01_CODRANGO+ " in ( "+sGRango + ")";
            }
}

sSentencia=sSentencia + " ORDER BY "+m05bConstantes.COL_01_CODRANGO+","+ m05bConstantes.COL_01_CODDPTO;
miLog.addTraza("Bego", "m05b-m05bAsigentreFechasServlet Servlet", "SQL: " + sSentencia, m05bConstantes.LOG_NIVEL_BAJO);
         sDesDpto="";
         String coldesde ="";
	     String	colhasta ="";
	     String	colmatricula=""  ;
	     String coldni=""  ;
	     String colnombre=""  ;
	     String colfinalizada=""  ;
	     String colasignacion="" ;
	     String sRangoAnt="";
	     String sCodDptoAnt="";
	     
    	pstmt = conn.prepareStatement(sSentencia);
		rs = pstmt.executeQuery();
	     
		 
      	 m05bCamposInformes miVehiculo = new m05bCamposInformes();

	     fncAbrirFicheroTemporal();


         //String foParam = request.getParameter(FO_REQUEST_PARAM);


         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=\"21cm\" margin-top=\"2cm\" margin-bottom=\"2cm\" margin-left=\"2.5cm\" margin-right=\"2.5cm\">\n";
			       tmpFoFileStream.write(sLinea.getBytes());
			         sLinea = "		<fo:region-before extent=\"4cm\"/>\n";
			         tmpFoFileStream.write(sLinea.getBytes());
			         sLinea = "		<fo:region-body  margin-top=\"4.8cm\" margin-bottom=\"1.5cm\" />\n";
			         tmpFoFileStream.write(sLinea.getBytes());
			         sLinea = "		<fo:region-after  extent=\"1cm\"/>\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());
		 // PAGE-SEQUENCE DEL LISTADO 
			                int cont=0;
//			              Agurtzane Mar12: contador en listados
			                int intCont=0, intContParcial=0;
			                boolean bolReset=false, bolFin =false;
//			              Fin Agurtzane Mar12: contador en listados
		   		            while (rs.next()){
//		   		           Agurtzane Mar12: contador en listados
		   		            intCont++;
		   		            intContParcial++;
//		   		       Fin Agurtzane Mar12: contador en listados		   		            
		   		    	    sRango=rs.getString(8)==null ? "":rs.getString(8);
		   		    	    sDesRango=rs.getString(11)==null ? "":rs.getString(11);
		   		    	    sCodDpto=rs.getString(9)==null ? "":rs.getString(9);
		   		    	    sDesDpto=rs.getString(10)==null ? "":rs.getString(10);
	       	    			coldesde = rs.getString(1)==null ? "":rs.getString(1);
	       	    			colhasta= rs.getString(2)==null ? "":rs.getString(2);
	       	    			colmatricula = rs.getString(3)==null ? "":rs.getString(3);
	       	    		    coldni = rs.getString(4)==null ? "":rs.getString(4);
	       	    		    colnombre = rs.getString(5)==null ? "":rs.getString(5);
	       	    		    colfinalizada = rs.getString(6)==null ? "":rs.getString(6);
	       	    		    colasignacion = rs.getString(7)==null ? "":rs.getString(7);
	       	    			   if (((!(sCodDpto.equals(sCodDptoAnt)))  )||((!(sRango.equals(sRangoAnt))) ) ||(cont==40)){
	       	    			   	if (sCodDptoAnt!=""){
	       	    			   		
//	 	       	    			   Agurtzane Mar12: contador en listados
		       	    			   	bolReset=false;
		       	    				bolFin =false;	
		       	    			   	if (cont==40){
		       	    			   		bolFin =true;	
			       	    			   	if ((!(sCodDpto.equals(sCodDptoAnt))) ||(!(sRango.equals(sRangoAnt))) ){
			       	    			   		bolReset=true;
			       	    			   	}	       	    			   	
		       	    			   	}else bolReset=true;
		       	    			   	if(bolReset){ imprimirTotal(intContParcial, false,res);intContParcial=0;}
		       	    			   	//Fin Agurtzane Mar12: contador en listados   	           				   	           	
	       	    	           					       	    	    			       	    	           			       	    			   		
	       	    			   		
			                          fncImprimirFinPagina();
	       	    			   	}	
	       	    			    fncImprimirComienzoPagina(res);
	       	    			    cont=0;
	       	    			    if(!bolFin)intContParcial=0;//Agurtzane Mar12: contador en listados
			            		}else{
	       	    			   	cont=cont+1;   
   		                        sLinea = "	    <fo:table-row >\n";
			            		tmpFoFileStream.write(sLinea.getBytes());}
	       	    			    sCodDptoAnt=sCodDpto;
	    	    			    sRangoAnt=sRango;	
	    	    			    	// Matricula
			            			sLinea = "	    <fo:table-cell padding-top=\"0.2mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block  text-align=\"center\">\n<![CDATA["+colmatricula+"]]></fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());

			            			// Desde 
			            			sLinea = "	    <fo:table-cell padding-top=\"0.2mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block  text-align=\"center\">\n <![CDATA[" + coldesde +"]]></fo:block>";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			// Hasta
			            			sLinea = "	    <fo:table-cell  padding-top=\"0.2mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block  text-align=\"center\">\n <![CDATA["+ colhasta +"]]></fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			// dni
			            			sLinea = "	    <fo:table-cell padding-top=\"0.2mm\" border-width=\"0.1mm\" border-right-width=\"0pt\" border-style=\"solid\">\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block  text-align=\"center\">\n<![CDATA["+coldni +"]]></fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
	            			        // nombre
			            			sLinea = "	    <fo:table-cell padding-top=\"0.2mm\" border-left-width=\"0pt\" border-right-width=\"0.1pt\" border-top-width=\"0.1pt\" border-bottom-width=\"0.1pt\" border-style=\"solid\">\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block  text-align=\"center\">\n<![CDATA["+colnombre +"]]></fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());			            			
			            			// Finalizada
			            			sLinea = "	    <fo:table-cell padding-top=\"0.2mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block  text-align=\"center\">\n<![CDATA["+colfinalizada+"]]></fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
							            
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			// Nº asignacion
			            			sLinea = "	    <fo:table-cell padding-top=\"0.2mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
							            sLinea = "      <fo:block  text-align=\"center\">\n<![CDATA["+colasignacion+"]]></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());
			        }
		   		            		   		    
   		           //Agurtzane Mar12: contador en listados
		   		         intContParcial++;
		   		         imprimirTotal(intContParcial, false,res);
		   		         imprimirTotal(intCont, true,res);   	  
   	           		//Fin Agurtzane Mar12: contador en listados		   		               	    			
		   		            
		   		            
			        fncImprimirFinPagina();
			                   		rs.close();
             
         sLinea = "</fo:root>\n";
         tmpFoFileStream.write(sLinea.getBytes());
         

         tmpFoFileStream.close();

         //FileInputStream file = new FileInputStream(sFoFileDir + tmpFoFile.getName());

         //renderFO(new InputSource(file), response);
         generarPDFdesdeFO(sFoFileDir + tmpFoFile.getName(),sFoFileDir,response);

         //tmpFoFile.delete();

         //response.flushBuffer();


      }
      catch (Exception ex)
      {
      	 try {
			response.sendRedirect("m05bError.jsp");
		 } catch (IOException e) {
			// TODO Auto-generated catch block
				miLog.addTraza(e);
		 }
        //throw new ServletException(ex);
      }
      finally{
	   	    try {
				if (rs != null)
					rs.close();			
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e2) {
				System.err.println("Exception: OutletMaintenanceS.service: " + e2);
	  		}
	   }
    }


    /**
     * 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);
            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();
        }
        catch (Exception ex)
        {
            throw new ServletException(ex);
        }
    }
    
    public void generarPDFdesdeFO(String ficheroFO, String rutaPDF, HttpServletResponse response) throws FOPException {
        try {

            Driver driver = new Driver();

            driver.setRenderer(Driver.RENDER_PDF);

            //Si no existe el directrio lo crea
            File f = new File(rutaPDF);

            if (!f.exists()) {

                f.mkdir();


            }
            //Cargar los datos del .FO
            FileInputStream file = new FileInputStream(ficheroFO);

            BufferedInputStream bfile=new BufferedInputStream(file);

            driver.setInputSource(new InputSource(bfile));


            //Crear un temporal que va a ser el .pdf
            String sPdfFileDir = rutaPDF;
            File   pdfFileDir = new File(sPdfFileDir);
            File   tmpPdfFile = File.createTempFile("m05b", ".pdf", pdfFileDir);


            FileOutputStream fileout = new FileOutputStream(tmpPdfFile);

            BufferedOutputStream bfileout=new BufferedOutputStream(fileout);

            driver.setOutputStream(bfileout);

            driver.run();


 

            bfile.close();

            bfileout.close();
            //Longitus del Fichero PDF.
            Long longitud = new Long(tmpPdfFile.length());            
            String sLongitud=""+ longitud;
            int iLongitud=Integer.parseInt(sLongitud);

            response.setContentLength((int)iLongitud);
	        response.setContentType("application/pdf");
	        
            byte[] fileBytes = new byte[longitud.intValue()];
            FileInputStream fisficheroPdf = new FileInputStream(tmpPdfFile);
            fisficheroPdf.read(fileBytes);
            ServletOutputStream os = response.getOutputStream();
            ((OutputStream)os).write(fileBytes);
            ((OutputStream)os).flush();
            ((OutputStream)os).close();

            // BORRAR EL FICHERO FO

            File fichero = new File(ficheroFO);
            

            boolean borrado = fichero.delete();
            boolean borrado2 = tmpPdfFile.delete();

        } catch (Exception e) {

            miLog.addTraza("Bego", "EXCEPCION !!! : Error pasando de FO a PDF:","Exception: "+ e, m05bConstantes.LOG_NIVEL_BAJO);
            

        }


    }
	

}
