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: (20/01/06)
 */
    
public class m05bImpCTKMediaServlet 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);
      	 String persona = (String) session.getAttribute("persona");
		 m05bCTK objCTK = (m05bCTK)session.getAttribute("CTK");
		// Extraccion de parametros
	     String grupo=request.getParameter("Grupo");
	     String rango=request.getParameter("Rango")==null?"":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="",porcentaje="";
	 	 String Desde="", Hasta="";
		 long DiasDesdeHasta=0;
		 
		 // 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_PORCENT +
					  ", " + 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 ;
		 
		 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 - Media" + "</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=\"20mm\"/>\n";
		tmpFoFileStream.write(sLinea.getBytes());
	    sLinea = "	    <fo:table-column column-width=\"50mm\"/>\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=\"26mm\"/>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "	    <fo:table-column column-width=\"28mm\"/>\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());
		// 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());
		// 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());
		// 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());
		// Porcentaje
		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\">\nPorcentaje</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=rs.getString(5);
			porcentaje=rs.getString(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());
		    // 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());
		    // 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());
		    // 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=\"center\">\n " + dias + " </fo:block>\n";
		    sLinea = "      <fo:block  text-align=\"center\">\n " + DiasDesdeHasta + " </fo:block>\n";
			tmpFoFileStream.write(sLinea.getBytes());
		    sLinea = "	    </fo:table-cell>\n";
		    tmpFoFileStream.write(sLinea.getBytes());
		    // Porcentaje
		    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 " + porcentaje + " </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
   
}