package vehiculosServlet;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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.m05bComun;
import m05bClasses.m05bConexion;
import m05bClasses.m05bConstantes;
import m05bClasses.m05bConstantesInformes;
import m05bClasses.m05bFileLog;
import m05bClasses.m05bInformes;

public class m05bImpVehSinGastosServlet extends javax.servlet.http.HttpServlet {m05bFileLog miLog = new m05bFileLog();
	java.sql.Connection conn = null;

	File foFileDir;
	File tmpFoFile;
	FileOutputStream tmpFoFileStream;

	String sLinea;
	String sFecha = "", sTitFecha = "", persona = "", sRango = "";

	
	m05bComun miComun = new m05bComun();
	m05bConexion miConex = new m05bConexion();
	m05bInformes miInforme = new m05bInformes();

	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 {
		try {
			doGet(request, response);
		} catch (Exception e) {
			miLog.addTraza(e);
		}
	}

	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);
	// }

	// Agurtzane Mar12: contador en listados
	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("infListVehSG.texto2") + intCont + "</fo:block>\n";
		else
			sLinea = "      <fo:block font-weight=\"bold\" text-align=\"right\"  padding-top=\"26pt\">\n "
					+ res.getString("infListVehSG.texto3")  + 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());
	}

	// Fin Agurtzane Mar12: contador en listados
	private void fncImprimirComienzoPagina(ResourceBundle res,String idioma) throws java.io.IOException,
			javax.naming.NamingException {
		// PAGE-SEQUENCE DEL LISTADO
		sLinea = "<fo:page-sequence master-reference=\"general\">\n";
		tmpFoFileStream.write(sLinea.getBytes());

		// PIE DE PAGINA
		miInforme.fncGenerarPiePagina(tmpFoFileStream,
				m05bConstantesInformes.DIR_VERTICAL,idioma,res.getString("pagina"));

		// REGION BEFORE
		sLinea = "		<fo:static-content flow-name=\"xsl-region-before\" >\n";
		tmpFoFileStream.write(sLinea.getBytes());
		// -------------- CABECERA DEL LISTADO --------------
		// Cabecera 1
		sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"10pt\">\n "
				+ res.getString("infListVehSG.titulo") + "</fo:block>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "      <fo:block font-weight=\"bold\" padding-top=\"5pt\" font-size=\"9pt\">\n "
				+ res.getString("infListVehSG.texto1") + sTitFecha + "</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());
		// -------------- DETALLE DEL LISTADO --------------
		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=\"15mm\"/>\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=\"25mm\"/>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "	    <fo:table-body font-family=\"Helvetica\" font-size=\"8pt\">\n";
		tmpFoFileStream.write(sLinea.getBytes());

		// Títulos Primera Fila
		sLinea = "	    <fo:table-row>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		// Matricula
		sLinea = "	    <fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "      <fo:block font-weight=\"bold\" text-align=\"center\">\n "+res.getString("infListVehSG.col1") 
				+ "</fo:block>";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "	    </fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		// Rango
		sLinea = "	    <fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "      <fo:block font-weight=\"bold\"  text-align=\"center\">\n "+res.getString("infListVehSG.col2")+"</fo:block>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "	    </fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		// Asignacion
		sLinea = "	    <fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "      <fo:block font-weight=\"bold\"  text-align=\"center\">\n "+res.getString("infListVehSG.col3")+"</fo:block>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "	    </fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		// Nombre
		sLinea = "	    <fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "      <fo:block font-weight=\"bold\"  text-align=\"center\">\n "+res.getString("infListVehSG.col4")+"</fo:block>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "	    </fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		// Fecha Desde
		sLinea = "	    <fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "      <fo:block font-weight=\"bold\"  text-align=\"center\">\n "+res.getString("infListVehSG.col5")+"</fo:block>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "	    </fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		// Fecha Hasta
		sLinea = "	    <fo:table-cell>\n";
		tmpFoFileStream.write(sLinea.getBytes());
		sLinea = "      <fo:block font-weight=\"bold\"  text-align=\"center\">\n "+res.getString("infListVehSG.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());

	}

	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;
		int cont = 0;

		String sFDesde = "", sFHasta = "", sCodAsig = "", sMatricula = "", sRango = "", sNombre = "";
		String sSQL = "";

		try {

			HttpSession session = request.getSession(true);
			ResourceBundle res = (ResourceBundle) session
					.getAttribute("resTit");

			Locale idiomaLocal = (Locale) session.getAttribute("Local");
			String idiomaSes = ((idiomaLocal.getLanguage() == null) ? "es"
					: idiomaLocal.getLanguage());
			String campoTexto = "", campoTexto2 = "", campoTexto3 = "C";
			String formatoFecha="dd/MM/yyyy";
			if (!idiomaSes.equals("es")) {
				campoTexto = "E";
				campoTexto2 = "E_";
				campoTexto3 = "E";
				formatoFecha = "yyyy/MM/dd";
			}
			conn = miConex.getConnection();

			persona = (String) session.getAttribute("persona");
			conn = miConex.getConnection();

			sFecha = request.getParameter("Fecha");
			sTitFecha = sFecha.substring(6, 8) + "/" + sFecha.substring(4, 6)
					+ "/" + sFecha.substring(0, 4);
			sRango = request.getParameter("Rango") == null ? "" : request
					.getParameter("Rango");
			String strRango = sRango;// Agurtzane Mar12: contador en listados
			miLog.addTraza(persona, "Veh Sin Gastos", "RANGO: " + sRango,
					m05bConstantes.LOG_NIVEL_BAJO);

			sSQL = " select " + m05bConstantes.COL_34_MATRICULA + ", NVL("
					+ campoTexto+m05bConstantes.COL_99_DESCRIPCION + ", "
					+m05bConstantes.COL_99_DESCRIPCION + "), "
					+ m05bConstantes.COL_01_CODASIG + ", to_char("
					+ m05bConstantes.COL_01_FDESDE + ",'"+formatoFecha+" hh24:mi')"
					+ ",decode(" + m05bConstantes.COL_01_FDEVOL + ", null ,"
					+ "to_char(" + m05bConstantes.COL_01_FHASTA
					+ ",'dd/mm/yyyy hh24:mi')" + ",to_char("
					+ m05bConstantes.COL_01_FDEVOL + ",'"+formatoFecha+" hh24:mi'))"
					+ ", " + m05bConstantes.COL_21_NOMBRE + " from "
					+ m05bConstantes.TAB_DESCRIPCIONES + "      ,"
					+ m05bConstantes.TAB_ASIGNACION + "      , "
					+ m05bConstantes.TAB_VEHICULOS + "      , "
					+ m05bConstantes.TAB_PERSONAL + " where "
					+ m05bConstantes.COL_34_SITUACION + "= '"
					+ m05bConstantes.VEH_ALTA + "'" + " and  "
					+ m05bConstantes.COL_01_MATRICULA + " =  "
					+ m05bConstantes.COL_34_MATRICULA + " and  "
					+ m05bConstantes.COL_99_CODTABLA + "='"
					+ m05bConstantes.DESC_RANGOS + "'" + " and "
					+ m05bConstantes.COL_99_CODDESC + "="
					+ m05bConstantes.COL_34_RANGO + " and "
					+ m05bConstantes.COL_01_DNI + "= "
					+ m05bConstantes.COL_21_DNI + "(+) ";
			if (sRango.equals("")) {
				sSQL += "  and " + m05bConstantes.COL_34_RANGO
						+ " not in ('D1','D2','C','CA','CB','CV','AC')";
			} else {
				sSQL += "  and " + m05bConstantes.COL_34_RANGO + " = '"
						+ sRango + "' ";
			}
			sSQL += "   and (" + m05bConstantes.COL_01_MATRICULA + " not in ( "
					+ "	       select " + m05bConstantes.COL_92_MATRICULA
					+ "        from " + m05bConstantes.TAB_TMPTOTCONSUMOLITROS
					+ " WHERE " + m05bConstantes.COL_92_USUARIO + "='"
					+ persona + "'" + "))" + "	   and  "
					+ m05bConstantes.COL_01_MATRICULA + "||"
					+ m05bConstantes.COL_01_FDESDE + " in ( " + "		   select "
					+ m05bConstantes.COL_01_MATRICULA + "|| MAX("
					+ m05bConstantes.COL_01_FDESDE + ")" + "        from "
					+ m05bConstantes.TAB_ASIGNACION + "        where "
					+ m05bConstantes.COL_01_FDESDE + "<= to_date('" + sFecha
					+ "','yyyymmdd') AND " + " (decode("
					+ m05bConstantes.COL_01_FDEVOL + ", null ," + "to_char("
					+ m05bConstantes.COL_01_FHASTA + ",'yyyymmdd')"
					+ ",to_char(" + m05bConstantes.COL_01_FDEVOL
					+ ",'yyyymmdd'))) >= '" + sFecha + "'" + " GROUP BY "
					+ m05bConstantes.COL_01_MATRICULA + ")" + " order by 1 ";

			miLog.addTraza(m05bConstantes.LOG_NO_USER,
					m05bConstantes.LOG_NO_SRC, "SQL: " + sSQL,
					m05bConstantes.LOG_NIVEL_BAJO);

			pstmt = conn.prepareStatement(sSQL);
			rs = pstmt.executeQuery();

			tmpFoFileStream = miInforme.fncAbrirFicheroTemporal();
			miLog.addTraza(m05bConstantes.LOG_NO_USER,
					m05bConstantes.LOG_NO_SRC,
					"Despues abrir fichero temporal",
					m05bConstantes.LOG_NIVEL_BAJO);

			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=\"2cm\" margin-right=\"2cm\">\n";
			tmpFoFileStream.write(sLinea.getBytes());
			sLinea = "		<fo:region-before extent=\"3cm\"/>\n";
			tmpFoFileStream.write(sLinea.getBytes());
			sLinea = "		<fo:region-body  margin-top=\"1.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());

			// Linea 1
			fncImprimirComienzoPagina(res,campoTexto3);
			miLog.addTraza(m05bConstantes.LOG_NO_USER,
					m05bConstantes.LOG_NO_SRC, "Antes while",
					m05bConstantes.LOG_NIVEL_BAJO);

			int intCont = 0, intContParcial = 0; // Agurtzane Mar12: contador en
													// listados
			while (rs.next()) {
				// Agurtzane Mar12: contador en listados
				intCont++;
				intContParcial++;
				// Fin Agurtzane Mar12: contador en listados
				sMatricula = rs.getString(1) == null ? "" : rs.getString(1);
				sRango = rs.getString(2) == null ? "" : rs.getString(2);
				sCodAsig = rs.getString(3) == null ? "" : rs.getString(3);
				sFDesde = rs.getString(4) == null ? "" : rs.getString(4);
				sFHasta = rs.getString(5) == null ? "" : rs.getString(5);
				sNombre = rs.getString(6) == null ? "" : rs.getString(6);
				if (cont == 36) {

					miInforme.fncImprimirFinPagina(tmpFoFileStream);
					fncImprimirComienzoPagina(res,campoTexto3);
					cont = 0;
				} else {
					++cont;
				}
				sLinea = "	    <fo:table-row>\n";
				tmpFoFileStream.write(sLinea.getBytes());
				// Matricula
				sLinea = "	    <fo:table-cell padding-top=\"2pt\" border-style=\"solid\">\n";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "      <fo:block font-weight=\"bold\" text-align=\"center\">\n<![CDATA["
						+ sMatricula + "]]></fo:block>";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "	    </fo:table-cell>\n";
				tmpFoFileStream.write(sLinea.getBytes());
				// Rango
				sLinea = "	    <fo:table-cell padding-top=\"2pt\" border-style=\"solid\">\n";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "      <fo:block font-weight=\"bold\" text-align=\"left\">\n<![CDATA[ "
						+ sRango + "]]></fo:block>\n";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "	    </fo:table-cell>\n";
				tmpFoFileStream.write(sLinea.getBytes());
				// Asignacion
				sLinea = "	    <fo:table-cell padding-top=\"2pt\" border-style=\"solid\">\n";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "      <fo:block font-weight=\"bold\"  text-align=\"center\">\n <![CDATA["
						+ sCodAsig + "]]></fo:block>\n";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "	    </fo:table-cell>\n";
				tmpFoFileStream.write(sLinea.getBytes());
				// Nombre
				sLinea = "	    <fo:table-cell padding-top=\"2pt\" border-style=\"solid\">\n";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "      <fo:block font-weight=\"bold\"  text-align=\"center\">\n<![CDATA[ "
						+ sNombre + "]]></fo:block>\n";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "	    </fo:table-cell>\n";
				tmpFoFileStream.write(sLinea.getBytes());
				// Fecha Desde
				sLinea = "	    <fo:table-cell padding-top=\"2pt\" border-style=\"solid\">\n";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "      <fo:block font-weight=\"bold\"  text-align=\"center\">\n <![CDATA["
						+ sFDesde + "]]></fo:block>\n";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "	    </fo:table-cell>\n";
				tmpFoFileStream.write(sLinea.getBytes());
				// Fecha Hasta
				sLinea = "	    <fo:table-cell padding-top=\"2pt\" border-style=\"solid\">\n";
				tmpFoFileStream.write(sLinea.getBytes());
				sLinea = "      <fo:block font-weight=\"bold\"  text-align=\"center\">\n<![CDATA[ "
						+ sFHasta + "]]></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());
			} // Fin while
			imprimirTotal(intCont, true,res);// Agurtzane Mar12: contador en
											// listados
			miInforme.fncImprimirFinPagina(tmpFoFileStream);

			sLinea = "</fo:root>\n";
			tmpFoFileStream.write(sLinea.getBytes());
			tmpFoFileStream.close();

			miLog.addTraza(m05bConstantes.LOG_NO_USER,
					m05bConstantes.LOG_NO_SRC, "Antes generar PDF desde FO",
					m05bConstantes.LOG_NIVEL_BAJO);
			miInforme.generarPDFdesdeFO(response);
			// generarPDFdesdeFO(sFoFileDir +
			// tmpFoFile.getName(),sFoFileDir,response);
			miLog.addTraza(m05bConstantes.LOG_NO_USER,
					m05bConstantes.LOG_NO_SRC, "Despues generar PDF desde FO",
					m05bConstantes.LOG_NIVEL_BAJO);

		} catch (Exception ex) {
			try {
				response.sendRedirect("m05bError.jsp");
			} catch (IOException e) {
				// 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);
				}
			}
		}
	}

}