<%@ page import="java.util.*,javax.sql.*,java.sql.*,javax.naming.*,java.text.*,m05bClasses.*" %>
<%	// _______________________________XLNETs______________________________________
	// Evaluar variable de session que indica si esta conectado a XLNets
	if (session.getAttribute("objetoSeguridad") == null)  { 
		// Verificar que no ha transcurrido el tiempo de sesion (web.xml) %>
			<%@ include file="/m05bControlAcceso.inc"%> 
<%	} 
	String persona = (String) session.getAttribute("persona");
%>
	<%@ include file="/m05bIdioma.inc"%>

<HTML>
<link rel="stylesheet" href="<%=m05bConstantesPaginas.PATH_ESTILOS%>/m05bestilos.css" type="text/css">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8">
<HEAD>
 <title><%= resTit.getString("titLstVehiSinGastos")%></title>
	
<SCRIPT language=JavaScript>
		function fncRetornar() {
			var docu = document.formPDFVehSinGastos;
			docu.action="/m05bParqueMovilWar/m05bVehSinGastosJsp.jsp" ;
			docu.submit();
		}
</SCRIPT>
</HEAD>	
<BODY style="cursor: wait" onkeydown="cancelaEnter();" onload="document.body.style.cursor='default';document.all['general'].style.visibility='visible';document.all['general'].style.cursor='default';"  onunload="document.body.style.cursor='wait';document.all['general'].style.cursor='wait';">
<div  id="general" style="POSITION: absolute;visibility:hidden;cursor: wait">		
<form NAME="formPDFVehSinGastos" METHOD="POST">
		  <% m05bFileLog miLog = new m05bFileLog(); %>
<%	m05bComun miComun = new m05bComun();
	String sVehSinGastos = miComun.leerPropiedad("PDF.VehiculosSinGastos"); 
	String fecha= request.getParameter("Fecha");
	String rango = request.getParameter("Rango")==null?"":request.getParameter("Rango");
	String srcPDF = sVehSinGastos + fecha + "&Rango=" + rango;
			java.sql.Connection conn=null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
		      try
		      {  //HttpSession session = request.getSession(true);
					m05bConexion miConex = new m05bConexion();
					conn = miConex.getConnection();

	   String sqlDeleteTemporal = "DELETE  " + m05bConstantes.TAB_TMPTOTCONSUMOLITROS +" WHERE " + m05bConstantes.COL_92_USUARIO+ " = '" + persona+"'";			  
       String sqlInsertTmp="INSERT into " + 
                     m05bConstantes.TAB_TMPTOTCONSUMOLITROS+"("+
                     m05bConstantes.COL_92_USUARIO+","+ 
                     m05bConstantes.COL_92_MATRICULA+","+
                     m05bConstantes.COL_92_LITROS+
					  ")select p,m,n from ( select '" + persona+"'as p,"+ m05bConstantes.COL_09_MATRICULA +	" as m,0 as n   from " + m05bConstantes.TAB_GASTOS +
					  "    where ((to_char (" + m05bConstantes.COL_09_FECHA + ",'yyyymmdd') > '" + fecha + "') AND ( "+
					  m05bConstantes.COL_09_CODCPTO+ " = 'C') " +
					  " and ((( " + m05bConstantes.COL_09_KMGASTO +" is not null) and ( "+ m05bConstantes.COL_09_KMGASTO+" <>0)) OR (( "+
					  m05bConstantes.COL_09_LITROS + " is not null) and (TO_NUMBER(REPLACE( " + m05bConstantes.COL_09_LITROS+ ",',',''),'9999.99')<>0 ) ) ) ) "+
                      " union select '"+
                      persona+"'as p,"+ m05bConstantes.COL_18_MATRICULA + 
					  " as m ,0  as n     from " + m05bConstantes.TAB_OPEBANCOS +
					  "        where ((to_char(" + m05bConstantes.COL_18_FECHA + ",'yyyymmdd') > '" + fecha + "') " +
					  " and ("+m05bConstantes.COL_18_TIPO+"='C')  and ((("+m05bConstantes.COL_18_KM +" is not null) and ("+
					  m05bConstantes.COL_18_KM+"<>0)) OR (("+m05bConstantes.COL_18_LITROS +" is not null) and ("+m05bConstantes.COL_18_LITROS+"<>0 ) ) ) ))";
                    miLog.addTraza(m05bConstantes.LOG_NO_USER , m05bConstantes.LOG_NO_SRC, "sqlInsertTmp"+sqlInsertTmp , m05bConstantes.LOG_NIVEL_BAJO);								  
                    pstmt = conn.prepareStatement(sqlDeleteTemporal);
                    rs = pstmt.executeQuery();
                    pstmt = conn.prepareStatement(sqlInsertTmp);
                    miLog.addTraza(m05bConstantes.LOG_NO_USER , m05bConstantes.LOG_NO_SRC, "pstmt"+sqlInsertTmp , m05bConstantes.LOG_NIVEL_BAJO);					  
                    rs = pstmt .executeQuery();
        	        if (pstmt != null)
		            pstmt .close();
                    if (rs != null)
                    rs .close();
                  //}
		 String  sSentencia="  select 1 " +
					  " from " + m05bConstantes.TAB_DESCRIPCIONES + 
					  "      ," + m05bConstantes.TAB_ASIGNACION + 
					  "      , " + m05bConstantes.TAB_VEHICULOS +
					  " 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 ;
             sSentencia+="  and " + m05bConstantes.COL_01_MATRICULA + " not in (SELECT "+m05bConstantes.COL_92_MATRICULA+" FROM "+ m05bConstantes.TAB_TMPTOTCONSUMOLITROS+
                         " WHERE "+m05bConstantes.COL_92_USUARIO+"='"+persona + "')";      				  
   		    if (rango.equals("")) {
				sSentencia+="  and " + m05bConstantes.COL_34_RANGO + " not in ('D1','D2','C','CA','CB','CV','AC')" ;
    	    } else {
				sSentencia+="  and " + m05bConstantes.COL_34_RANGO + " = '" + rango + "' "; 
    	    }
				sSentencia+=
					  "	   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(" + fecha + ",'yyyymmdd') GROUP BY "+
					           m05bConstantes.COL_01_MATRICULA+")";
					 
miLog.addTraza(m05bConstantes.LOG_NO_USER , m05bConstantes.LOG_NO_SRC, "sSentencia"+sSentencia , m05bConstantes.LOG_NIVEL_BAJO);
		                     pstmt = conn.prepareStatement(sSentencia);
				                     rs = pstmt.executeQuery();
		%>
<TABLE class="general">
  <tr>
       <!--td width="90%" align="right"><INPUT TYPE="IMAGE" style="cursor:hand" VALUE="Retornar" ONCLICK="JavaScript:fncRetornar();" onMouseOver="top.status='Retornar'; return true" onMouseOut="top.status=''; return true" src="<%=m05bConstantesPaginas.PATH_IMAGENES%>/retornar.gif"></td-->
       <td width="90%" align="right"><input type="button" class="boton" name="<%= res.getString("etBOT_RETORNAR")%>" value="<%= res.getString("etBOT_RETORNAR")%>" ONCLICK="JavaScript:fncRetornar();return false;" onMouseOver="top.status='<%= res.getString("etBOT_STATUSRETORNAR")%>'; return false" onMouseOut="top.status=''; return false" /></td>			       
  </tr>
  <tr>
  		    <td colspan="2">
		    <% if (rs.next() == true){ %>
				<embed name='pdfAsignacionSinGastos' src='<%=srcPDF%>' type="application/pdf" width='736' height='500'></embed>
		    <%} else {%>
				<embed name='pdfAsignacionSinGastos' src='/parquemovil/SinDatos.pdf' type="application/pdf" width='736' height='500'></embed>
		    <%}%>
			<%	} catch (Exception SQLException) {
					miLog.addTraza(SQLException);
				} 	finally{
						if (rs != null)
							rs.close();
						if (pstmt != null)
							pstmt.close();
						if (conn != null)
							conn.close();}%>
		 </td>
  </tr>
</TABLE>
</form>
<script type="text/javascript" src="<%=m05bConstantesPaginas.PATH_SCRIPTS%>/m05bMsControl.js"></script>
 
</div>	  
</body>
</html>