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: (26/01/06)
 */
    
public class m05bImpCTKRentingServlet 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")==null?"":request.getParameter("Rango");
	     String desde=request.getParameter("Desde")==null?"":request.getParameter("Desde");
	     String fDesde=desde.equals("")?"":(desde.substring(6,8)+"-"+desde.substring(4,6)+"-"+desde.substring(0,4));
	     String hasta=request.getParameter("Hasta")==null?"":request.getParameter("Hasta");
	     String fHasta=hasta.equals("")?"":(hasta.substring(6,8)+"-"+hasta.substring(4,6)+"-"+hasta.substring(0,4));
	     String matricula="",fecDesde="", fecHasta="", sDias="";
	     double importe = 0.0;
	     long dias;
     	 String persona = (String) session.getAttribute("persona");
		 miLog.addTraza(persona,"Impr. Renting Servlet","Desde: " + fDesde + "   -   Hasta: " + fHasta, m05bConstantes.LOG_NIVEL_BAJO);

		 // SQL para obtención de datos
				String	sql = "select " + m05bConstantes.COL_41_MATRICULA + 
								", min(" + m05bConstantes.COL_99_DESCRIPCION + ")" +
								", to_char(min(" + m05bConstantes.COL_41_FECHA + "),'dd/mm/yyyy')" +
								", to_char(max(" + m05bConstantes.COL_41_FECHA + "),'dd/mm/yyyy')" +
								", to_char(min(" + m05bConstantes.COL_41_FECHA + "),'yyyymmdd')" +
								", to_char(max(" + m05bConstantes.COL_41_FECHA + "),'yyyymmdd')" +
								", sum(" + m05bConstantes.COL_41_IMPORTE + ")" +
							" from " + m05bConstantes.TAB_CUOTAS + ", " + m05bConstantes.TAB_VEHICULOS +
								", " +  m05bConstantes.TAB_DESCRIPCIONES + "," + m05bConstantes.TAB_CTK +
							" where " + m05bConstantes.COL_41_MATRICULA + "=" + m05bConstantes.COL_34_MATRICULA +
							" and " + m05bConstantes.COL_41_MATRICULA + "=" + m05bConstantes.COL_97_MATRICULA +
							" and " + m05bConstantes.COL_97_USUARIO + "='" + persona + "' " +
							" and " + m05bConstantes.COL_41_IMPORTE + " >0" +
							" and  to_char(" + m05bConstantes.COL_41_FECHA + ",'yyyymmdd') between " + desde + " and " + hasta +
      						" and  " + m05bConstantes.COL_99_CODTABLA + "='" + m05bConstantes.DESC_RANGOS + "'" + 
      						" and " + m05bConstantes.COL_99_CODDESC + "=" + m05bConstantes.COL_34_RANGO;
				
				 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 += 	" group by  " + m05bConstantes.COL_41_MATRICULA ;
		
		 miLog.addTraza(m05bConstantes.LOG_NO_USER,m05bConstantes.LOG_NO_SRC,"SQL: " + 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 - RENTING" + "</fo:block>\n";
			        tmpFoFileStream.write(sLinea.getBytes());
                    /*sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"10pt\">\n " + "Grupo de Rangos: " + grupo + "</fo:block>\n";
			        tmpFoFileStream.write(sLinea.getBytes());*/
                    sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"10pt\">\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=\"30mm\"/>\n";
			            	tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-column column-width=\"45mm\"/>\n";
			            	tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-column column-width=\"25mm\"/>\n";
			            	tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-column column-width=\"25mm\"/>\n";
			            	tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-column column-width=\"25mm\"/>\n";
			            	tmpFoFileStream.write(sLinea.getBytes());
		            		sLinea = "	    <fo:table-column column-width=\"25mm\"/>\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 Inicio 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\">\nF. Inicio Rango</fo:block>\n";
								        tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			// Fecha Fin 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\">\nF. Fin Rango</fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			// Dias en 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\">\nDías</fo:block>\n";
								        tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			// Importe
			            			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\">\nImporte</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());
		 miLog.addTraza(m05bConstantes.LOG_NO_USER,m05bConstantes.LOG_NO_SRC,"ANTES WHILE", m05bConstantes.LOG_NIVEL_BAJO);
			            		
			            		// DATOS
				         while (rs.next())  {
				         		matricula=rs.getString(1);
				         		rango=rs.getString(2);
				         		fecDesde=rs.getString(3);
				         		fecHasta=rs.getString(4);
				         		importe=rs.getDouble(7);
								dias=miComun.getDiasEntreFechas(rs.getString(5),rs.getString(6));
		 miLog.addTraza(m05bConstantes.LOG_NO_USER,m05bConstantes.LOG_NO_SRC,"DESPUES RECOGER PARAM", m05bConstantes.LOG_NIVEL_BAJO);
								
								sDias=""+dias;
   		                        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=\"left\">\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 Inicio 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=\"center\">\n " + fecDesde + " </fo:block>\n";
								        tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
			            			// Fecha Fin 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=\"center\">\n " + fecHasta + " </fo:block>\n";
							            tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
		 miLog.addTraza(m05bConstantes.LOG_NO_USER,m05bConstantes.LOG_NO_SRC,"ANTES DIAS", m05bConstantes.LOG_NIVEL_BAJO);
			            			
			            			// Dias en 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=\"right\">\n " + miComun.formatearStrMiles(sDias) + " </fo:block>\n";
								        tmpFoFileStream.write(sLinea.getBytes());
			            			sLinea = "	    </fo:table-cell>\n";
			            			tmpFoFileStream.write(sLinea.getBytes());
		 miLog.addTraza(m05bConstantes.LOG_NO_USER,m05bConstantes.LOG_NO_SRC,"ANTES IMPORTE", m05bConstantes.LOG_NIVEL_BAJO);
			            			
			            			// Importe
			            			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 " + miComun.formatearDblMilesDec(importe) + " </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());
		 miLog.addTraza(m05bConstantes.LOG_NO_USER,m05bConstantes.LOG_NO_SRC,"DESPUES IMPORTE", m05bConstantes.LOG_NIVEL_BAJO);
			            		
				         }
			 	      	    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(m05bConstantes.LOG_NO_USER,m05bConstantes.LOG_NO_SRC,"ANTES RENDER", m05bConstantes.LOG_NIVEL_BAJO);
         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
}