package utiliServlet;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import m05bClasses.m05bComun;
import m05bClasses.m05bConexion;
import m05bClasses.m05bConstantes;
import m05bClasses.m05bConstantesInformes;
import m05bClasses.m05bFileLog;
import m05bClasses.m05bInformes;
import m05bObjects.m05bCTK;


/**
 * Inserte aquí la descripción del tipo.
 * Fecha de creación: (25/01/06)
 */
    
public class m05bImpCTKCombusServlet extends javax.servlet.http.HttpServlet    {

		java.sql.Connection conn=null;
		String sLinea;
        File tmpFoFile ;
		FileOutputStream tmpFoFileStream ;
	    m05bComun miComun = new m05bComun();
		m05bFileLog miLog = new m05bFileLog();
		m05bInformes miInforme = new m05bInformes();
//      Logger log = null;

	    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
   {
      performTask(request, response);
   }

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

   
   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 {
	   	try {  
      	 
	   		HttpSession session = request.getSession(false);
	   		m05bCTK objCTK = (m05bCTK)session.getAttribute("CTK");
	   		// Extraccion de parametros
	   		String grupo=request.getParameter("Grupo");
	   		String rango=request.getParameter("Rango");
	   		String desde=request.getParameter("Desde");
	   		String fDesde=desde.substring(6,8)+"-"+desde.substring(4,6)+"-"+desde.substring(0,4);
	   		String hasta=request.getParameter("Hasta");
	   		String fHasta=hasta.substring(6,8)+"-"+hasta.substring(4,6)+"-"+hasta.substring(0,4);
	   		String matricula="", fecDesde="", fecHasta="", dias="", combustible="";
	   		String persona = (String) session.getAttribute("persona");	 
	   		String Desde="", Hasta="";
	   		long DiasDesdeHasta=0;
	   		
	   		miLog.addTraza(persona,"Combustible Servlet","Grupo de Rangos: " + grupo, m05bConstantes.LOG_NIVEL_BAJO);
	   		miLog.addTraza(m05bConstantes.LOG_NO_USER,m05bConstantes.LOG_NO_SRC,"Desde: " + fDesde + " -- Hasta: " + fHasta, m05bConstantes.LOG_NIVEL_BAJO);
		 
	   		// SQL para obtención de datos
	   		String sql = "select " + m05bConstantes.COL_97_MATRICULA + ", " + m05bConstantes.COL_99_DESCRIPCION +
	   					 ", to_char(" + m05bConstantes.COL_97_FDESDE + ",'dd/mm/yyyy'), to_char(" + m05bConstantes.COL_97_FHASTA + ",'dd/mm/yyyy')" + 
						 ", " + m05bConstantes.COL_97_DIAS + ", " + m05bConstantes.COL_97_COMBUSTIBLE +
						 ", " + m05bConstantes.COL_97_FDESDE + ", " + m05bConstantes.COL_97_FHASTA +
						 " from " + m05bConstantes.TAB_CTK + ", " +  m05bConstantes.TAB_DESCRIPCIONES + 
						 " where " + m05bConstantes.COL_97_TOTAL + " = 'S'" +
      					 " and  " + m05bConstantes.COL_99_CODTABLA + "='" + m05bConstantes.DESC_RANGOS + "'" + 
      					 " and " + m05bConstantes.COL_99_CODDESC + "=" + m05bConstantes.COL_97_RANGO +
				    	 " and " +  m05bConstantes.COL_97_USUARIO + " = '" + persona + "'" ;
	   		if (grupo.equals("A")) { 
	   			sql += " and " +  m05bConstantes.COL_97_GRUPO + " = '" + m05bConstantes.GRUPO_CTK_A + "'";
	   		} else if (grupo.equals("B")) { 
	   			sql += " and " +  m05bConstantes.COL_97_GRUPO + " = '" + m05bConstantes.GRUPO_CTK_B + "'";
			} else if (grupo.equals("C")) { 
				sql += " and " +  m05bConstantes.COL_97_GRUPO + " = '" + m05bConstantes.GRUPO_CTK_C + "'";
			} else if (grupo.equals("D")) { 
				sql += " and " +  m05bConstantes.COL_97_GRUPO + " = '" + m05bConstantes.GRUPO_CTK_D + "'";
			} else if (grupo.equals("RA")) { 
				sql += " and " +  m05bConstantes.COL_97_GRUPO + " = '" + m05bConstantes.GRUPO_CTK_RA + "'";
			} else if (grupo.equals("RB")) {
				sql += " and " + m05bConstantes.COL_97_GRUPO + " = '" + m05bConstantes.GRUPO_CTK_RB + "'";
			}
			
	   		if (!(rango.equals(""))) { 
	   			sql += " and " +  m05bConstantes.COL_97_RANGO + " = '" + rango + "'";
			}
			
	   		sql += " order by " + m05bConstantes.COL_97_MATRICULA ;
	   	
	   		miLog.addTraza(m05bConstantes.LOG_NO_USER,m05bConstantes.LOG_NO_SRC,"SQL-COMBUS-2: " + sql, m05bConstantes.LOG_NIVEL_BAJO);
		 
	   		m05bConexion miConex = new m05bConexion();
			conn = miConex.getConnection();
	    	pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
	        //tmpFoFileStream = new FileOutputStream(tmpFoFile);
	        //String foParam = request.getParameter(FO_REQUEST_PARAM);
	        tmpFoFileStream=miInforme.fncAbrirFicheroTemporal();

	        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 
	        miInforme.fncGenerarLayout(tmpFoFileStream, m05bConstantesInformes.DIR_VERTICAL, m05bConstantesInformes.NUM_LINEAS_2);
	        // PAGE-SEQUENCE DEL LISTADO 
	        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());
        	sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"10pt\">\n " + "CTK - GASTOS DE COMBUSTIBLE" + "</fo:block>\n";
        	tmpFoFileStream.write(sLinea.getBytes());
        	sLinea = "      <fo:block font-size=\"9pt\" font-weight=\"bold\" padding-top=\"10pt\">\n " + "Grupo de Rangos: " + grupo + "</fo:block>\n";
        	tmpFoFileStream.write(sLinea.getBytes());
        	sLinea = "      <fo:block font-size=\"9pt\" font-weight=\"bold\" padding-top=\"5pt\">\n " + "Rango: " + rango + "</fo:block>\n";
        	tmpFoFileStream.write(sLinea.getBytes());
        	sLinea = "      <fo:block font-size=\"9pt\" font-weight=\"bold\" padding-top=\"5pt\">\n " + "Desde: " + fDesde + "    -    Hasta: " + fHasta + "</fo:block>\n";
        	tmpFoFileStream.write(sLinea.getBytes());
        	sLinea = " 		</fo:static-content>\n"; 
        	tmpFoFileStream.write(sLinea.getBytes());

            //PIE DE PAGINA
		    miInforme.fncGenerarPiePagina(tmpFoFileStream, m05bConstantesInformes.DIR_VERTICAL); 
		    
			//BODY
		    sLinea = "	<fo:flow flow-name=\"xsl-region-body\">\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    Context ctx =  new InitialContext(System.getProperties());
		    // --------------    DETALLE DEL LISTADO    --------------
		    // tabla CONCEPTOS
		    sLinea= "        <fo:table space-after.optimum=\"12pt\">\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    <fo:table-column column-width=\"25mm\"/>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    <fo:table-column column-width=\"20mm\"/>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    <fo:table-column column-width=\"20mm\"/>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    <fo:table-column column-width=\"50mm\"/>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    <fo:table-column column-width=\"25mm\"/>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    <fo:table-column column-width=\"30mm\"/>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    /*sLinea = "	    <fo:table-header font-family=\"Helvetica\" font-size=\"7pt\" >\n";
			tmpFoFileStream.write(sLinea.getBytes());
			sLinea = "	    </fo:table-header>\n";
			tmpFoFileStream.write(sLinea.getBytes());*/
		 	// DETALLE
		    sLinea = "	    <fo:table-body font-family=\"Helvetica\" font-size=\"8pt\">\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    // CABECERA
		    sLinea = "	    <fo:table-row background-color=\"rgb(200,200,200)\">\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    // Matricula
		    sLinea = "	    <fo:table-cell padding-top=\"1mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "      <fo:block  text-align=\"center\" font-weight=\"bold\">\nMatrícula</fo:block>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    </fo:table-cell>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    // Fecha Desde
		    sLinea = "	    <fo:table-cell padding-top=\"1mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "      <fo:block  text-align=\"center\" font-weight=\"bold\">\nFecha Desde</fo:block>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    </fo:table-cell>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    // Fecha Hasta
		    sLinea = "	    <fo:table-cell padding-top=\"1mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "      <fo:block  text-align=\"center\" font-weight=\"bold\">\nFecha Hasta</fo:block>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    </fo:table-cell>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    // Rango
		    sLinea = "	    <fo:table-cell padding-top=\"1mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "      <fo:block  text-align=\"center\" font-weight=\"bold\">\nRango</fo:block>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    </fo:table-cell>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    // Dias
		    sLinea = "	    <fo:table-cell padding-top=\"1mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "      <fo:block  text-align=\"center\" font-weight=\"bold\">\nDías</fo:block>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    </fo:table-cell>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    // Combustible
		    sLinea = "	    <fo:table-cell padding-top=\"1mm\" border-width=\"0.1mm\" border-style=\"solid\">\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "      <fo:block  text-align=\"center\" font-weight=\"bold\">\nCombustible</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());
		    // DATOS
			
		    while (rs.next())  {
		    	matricula=rs.getString(1);
		    	rango=rs.getString(2);
		    	fecDesde=rs.getString(3);
		    	fecHasta=rs.getString(4);
		    	dias=miComun.formatearStrMiles(rs.getString(5));
		    	combustible=miComun.formatearDblMilesDec(rs.getDouble(6));

		    	//20100528
		    	//EN LOS DÍAS DEL RANGO NO TENDREMOS EN CUENTA LA AMORTIZACÓN DEL VEHÍCULO
		    	Desde = rs.getString(7);
		    	Hasta = rs.getString(8);
		    	Desde = Desde.substring(0,4)+Desde.substring(5,7)+Desde.substring(8,10);
		    	Hasta = Hasta.substring(0,4)+Hasta.substring(5,7)+Hasta.substring(8,10);
		    	DiasDesdeHasta=miComun.getDiasEntreFechasInclude(Desde,Hasta);
		    	//
		    	
		    	sLinea = "	    <fo:table-row >\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	// 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 " + matricula + " </fo:block>\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	sLinea = "	    </fo:table-cell>\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	// Fecha 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 " + fecDesde + " </fo:block>\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	sLinea = "	    </fo:table-cell>\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	// Fecha 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 " + fecHasta + " </fo:block>\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	sLinea = "	    </fo:table-cell>\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	// Rango
		    	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=\"left\">\n " + rango + " </fo:block>\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	sLinea = "	    </fo:table-cell>\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	// Dias
		    	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=\"right\">\n " + dias + " </fo:block>\n";
		    	sLinea = "      <fo:block  text-align=\"right\">\n " + DiasDesdeHasta + " </fo:block>\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	sLinea = "	    </fo:table-cell>\n";
		    	tmpFoFileStream.write(sLinea.getBytes());
		    	// Combustible
		    	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=\"right\">\n " + combustible + " </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: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();
	     
		    miInforme.renderFO(response);
         
		    //tmpFoFile.delete();
		    //response.flushBuffer();

	   	} catch (Exception ex) {
	   		try {
	   			response.sendRedirect("m05bError.jsp");
	   		} catch (IOException e) {
	   			// TODO Auto-generated catch block
	   			miLog.addTraza(e);
	   		}
	   	} finally{
	   		if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				conn.close();
	   	}
	  
	   }catch(Throwable theException) {
		   // uncomment the following line when unexpected exceptions
		   // are occuring to aid in debugging the problem.
		   miLog.addTraza(theException);
		   try {
			   response.sendRedirect("m05bError.jsp");
		   } catch (IOException e) {
			   // TODO Auto-generated catch block
			   miLog.addTraza(e);
		   }
	   }
   }  // Fin funcion performTask

}