<%@page import="java.util.*,javax.sql.*,java.sql.*,javax.naming.*,m05bClasses.*,java.io.PrintWriter" %>
<%@page contentType="text/html; charset=UTF-8" %>
<%@ include file="/m05bIdioma.inc"%>
<HTML>
<head>
	<script language="javascript1.2" src="<%=m05bConstantesPaginas.PATH_SCRIPTS%>/m05Funciones.js"></script>
	<script>
		var docu = opener.document.formAsignaciones;
	</script>
	
</head>
<BODY>
	<IFRAME src="" width="0" height="0" scrolling="no" frameborder="0" name="cargar">
	</IFRAME>
<%
	//Abrir la conexion
	String persona = (String) session.getAttribute("persona");
	java.sql.Connection m05bConn = null;
	PreparedStatement pstmt= null, m05bPstmt = null;
	ResultSet rs = null, m05bRS = null;
	String sql="", cadena="";
	int contTotal = 0;
	String rango = "", fecDesde="", fecHasta="";
	
	boolean esAsig = false;
	String matricula="", matriculaAsig = "", kmsalid="", marca="", color="",  kmact="", modelo="", ubicacion=""; 
	
	try{
			m05bFileLog miLog = new m05bFileLog();
			m05bCampos miCampo = new m05bCampos();			
			m05bConexion miConex = new m05bConexion();
			m05bConn = miConex.getConnection();

			String mensaje = request.getParameter("Mensaje")==null?"":request.getParameter("Mensaje");
			if (mensaje.equals("true")) {
				PrintWriter outMsg= response.getWriter();
				outMsg.println("<script>alert(dameAlerta(idioma, 3));</script>");
				outMsg.close();
			}
			String horDesde="", horHasta="";
			String tablas="", where = "", campos="", order="";
			String conRango="", conOT, conAsig; 
			String con2="", con3="", con4="", con5="", conDesc="", con7="";
			String subOT="", subAsig="", aux="";
			rango = request.getParameter("Rango")==null?"":request.getParameter("Rango");
			String permanente = request.getParameter("Perm")==null?"N":request.getParameter("Perm");
			miLog.addTraza(persona, "Coches Libres Asig", "PERMANENTE: " + permanente, m05bConstantes.LOG_NIVEL_BAJO);
			if ( (mensaje.equals("true")) || (mensaje.equals("Temporal")) ){
         			sql = "select MATRIC_96, KMACT_96, MARCA_96, COLOR_96, UBICACION_96, MODELO_96, RANGO_96  " +
					         "from " + m05bConstantes.TAB_COCHESLIBRES +
					    	" where USUARIO_96 = '" + persona + "'";
		 			order = " order  by  MATRIC_96";
				    if (rango.equals(""))
				       conRango="";
				    else
				       conRango=" AND RANGO_96 = '" + rango + "' ";
		 			sql += conRango + order;
					miLog.addTraza(persona, "Coches Libres Asig", "SQL-1: " + sql, m05bConstantes.LOG_NIVEL_BAJO);
			} else {
				// Se entra por primera vez
				    String forFecha="YYYYMMDDHH24MISS";
					rango = request.getParameter("Rango");
				    fecDesde=request.getParameter("Desde");
				    fecHasta=request.getParameter("Hasta");
				    horDesde=request.getParameter("HoraDesde");
				    horHasta=request.getParameter("HoraHasta");
				    matriculaAsig=request.getParameter("matriculaAsig");	
					if(matriculaAsig!=null)				    			    
				    	matriculaAsig = matriculaAsig.toUpperCase();
				    String strDesde=fecDesde.substring(fecDesde.lastIndexOf("/")+1,fecDesde.length());
				    fecDesde=fecDesde.substring(0,fecDesde.lastIndexOf("/"));
				    aux=fecDesde.substring(fecDesde.indexOf("/")+1,fecDesde.length());
				    if (aux.length()==1)
				        aux="0"+aux;
				    strDesde=strDesde+aux;
				    aux=fecDesde.substring(0,fecDesde.indexOf("/"));
				    if (aux.length()==1)
				        aux="0"+aux;
				    strDesde=strDesde+aux;
				    String strHasta=fecHasta.substring(fecHasta.lastIndexOf("/")+1,fecHasta.length());
				    fecHasta=fecHasta.substring(0,fecHasta.lastIndexOf("/"));
				    aux=fecHasta.substring(fecHasta.indexOf("/")+1,fecHasta.length());
				    if (aux.length()==1)
				        aux="0"+aux;
				    strHasta=strHasta+aux;
				    aux=fecHasta.substring(0,fecHasta.indexOf("/"));
				    if (aux.length()==1)
				        aux="0"+aux;
				    strHasta=strHasta+aux;
				    if (horDesde.indexOf(":")==1)
				        strDesde=strDesde+"0"+horDesde.substring(0,horDesde.indexOf(":"));
				    else
				        strDesde=strDesde+horDesde.substring(0,horDesde.indexOf(":"));
				    horDesde=horDesde.substring(horDesde.indexOf(":")+1,horDesde.length());
				    strDesde=strDesde+horDesde.substring(0,2)+"00";

				    if (horHasta.indexOf(":")==1)
				        strHasta=strHasta+"0"+horHasta.substring(0,horHasta.indexOf(":"));
				    else
				        strHasta=strHasta+horHasta.substring(0,horHasta.indexOf(":"));
				    horHasta=horHasta.substring(horHasta.indexOf(":")+1,horHasta.length());
				    strHasta=strHasta+horHasta.substring(0,2)+"00";
				    
					// Borrado de tabla temporal
					sql="delete from " + m05bConstantes.TAB_COCHESLIBRES + 
						" where " + m05bConstantes.COL_96_USUARIO + " = '" + persona + "'";
					
					pstmt = m05bConn.prepareStatement(sql);
					pstmt.executeUpdate();
			    
				    where =" where  V." + m05bConstantes.COL_34_SITUACION + " = '" + m05bConstantes.VEH_ALTA + "'";  

				    color=	" AND C." + m05bConstantes.COL_99_CODTABLA + " (+) = " + m05bConstantes.DESC_COLORES + " AND V." +
				    		m05bConstantes.COL_34_COLOR + " = C." + m05bConstantes.COL_99_CODDESC + " (+)" ;

				    marca=	" AND M." + m05bConstantes.COL_99_CODTABLA + " (+) = " + m05bConstantes.DESC_MARCAS + " AND V." +
				    		m05bConstantes.COL_34_MARCA + " = M." + m05bConstantes.COL_99_CODDESC + " (+)" ;

				    //ubicacion=	" AND P." + m05bConstantes.COL_99_CODTABLA + " (+) = " + m05bConstantes.DESC_UBICACIONES + " AND V." +
				    //		m05bConstantes.COL_34_APARCADO + " = P." + m05bConstantes.COL_99_CODDESC + " (+) ";

				    modelo = " AND MOD." + m05bConstantes.COL_17_CODMOD + " (+) = V." + m05bConstantes.COL_34_MODELO;
				    
				    if (rango.equals(""))
				       conRango="";
				    else
				       conRango="V." + m05bConstantes.COL_34_RANGO + " = '"+ rango + "' ";
				    
				    conOT = " and V." + m05bConstantes.COL_34_MATRICULA + " = OT." + m05bConstantes.COL_34_MATRICULA;
				    conAsig = " V." + m05bConstantes.COL_34_MATRICULA + " = ASIG." + m05bConstantes.COL_34_MATRICULA;

				    /*con2=" AND "+m05bConstantes.COL_34_MATRICULA+" NOT IN (select "+m05bConstantes.COL_01_MATRICULA+
				    	" FROM "+m05bConstantes.TAB_ASIGNACION+" WHERE "+m05bConstantes.COL_01_FINALIZA+"='N')";

				    con3=" AND "+m05bConstantes.COL_34_MATRICULA+" NOT IN (select "+m05bConstantes.COL_01_MATRICULA+
				    		" FROM "+m05bConstantes.TAB_ASIGNACION+" WHERE "+m05bConstantes.COL_01_FDESDE+" NOT "+
				    		"BETWEEN TO_DATE ('"+strDesde+"','"+forFecha+"') AND TO_DATE ('"+
				        	strHasta+"','"+forFecha+"'))";

				    con4 =" AND "+m05bConstantes.COL_34_MATRICULA+" NOT IN (select "+
				    		m05bConstantes.COL_20_MATRICULA+" FROM "+m05bConstantes.TAB_ORDENESTRAB+" WHERE "+
				    		m05bConstantes.COL_20_FENTRADA+" NOT BETWEEN TO_DATE ('"+strDesde+"','"+forFecha+"') AND "+
				    		" TO_DATE ('"+strHasta+"','"+forFecha+"') AND (("+m05bConstantes.COL_20_FSALIDA+")is null))";

				    con5=" AND "+m05bConstantes.COL_34_MATRICULA+" NOT IN (select "+m05bConstantes.COL_01_MATRICULA+
				    		" FROM "+m05bConstantes.TAB_ASIGNACION+" WHERE "+m05bConstantes.COL_01_FHASTA+" NOT "+
				    		"BETWEEN TO_DATE ('"+strDesde+"','"+forFecha+"') AND TO_DATE ('"+
				        	strHasta+"','"+forFecha+"'))";

				    con7 = " AND "+ m05bConstantes.COL_34_MATRICULA +" NOT IN (select "+m05bConstantes.COL_01_MATRICULA+
				           " FROM "+m05bConstantes.TAB_ASIGNACION +" WHERE "+m05bConstantes.COL_01_FDESDE+" <= TO_DATE ('"+strDesde+"','"+forFecha+"')"+
				           " AND "+m05bConstantes.COL_01_FHASTA +" <= TO_DATE ('"+strHasta+"','"+forFecha+"'))";

				    where = where + conRango+con2+con3+con4+con5+con7+conDesc;*/
				    
				    conDesc = modelo + marca + ubicacion + color;
					//where = where + conOT + " AND " + conAsig + conDesc;
					if (permanente.equals("N")) {
				    	where = where + " AND " + conRango + conOT + " AND " + conAsig + conDesc;
				    } else {
				    	where = where + " AND " + conRango + " AND " + conAsig + conDesc;
				    }

					
				    campos="select V." + m05bConstantes.COL_34_MATRICULA + ",	V." + m05bConstantes.COL_34_KMACTUALES +
				    		", M." + m05bConstantes.COL_99_DESCRIPCION + ", C." + m05bConstantes.COL_99_DESCRIPCION + 
				    		", V." + m05bConstantes.COL_34_APARCADO + ", MOD." + m05bConstantes.COL_17_DESCRIP +
				    		", V." + m05bConstantes.COL_34_COLOR;

				    tablas=" from " + m05bConstantes.TAB_DESCRIPCIONES + " C" + 
						//	", " + m05bConstantes.TAB_DESCRIPCIONES + " P " + 
							", " + m05bConstantes.TAB_DESCRIPCIONES + " M " + 
				    		", " + m05bConstantes.TAB_MODELOS + " MOD "+
							", " + m05bConstantes.TAB_VEHICULOS +  " V " ;
					
					    subOT=" (select " + m05bConstantes.COL_34_MATRICULA +
					    		" from " + m05bConstantes.TAB_VEHICULOS +
					    		" where " + m05bConstantes.COL_34_MATRICULA + 
					    				" not in (select " + m05bConstantes.COL_20_MATRICULA +
					    							" from " + m05bConstantes.TAB_ORDENESTRAB +
					    							" where (( " + m05bConstantes.COL_20_FSALIDA+" IS NULL )" +
													" OR ( " + m05bConstantes.COL_20_FENTRADA+" BETWEEN TO_DATE('"+strDesde+"','"+forFecha+"') " +
													" AND TO_DATE('" + strHasta+ "','" + forFecha + "') " +
													" or " + m05bConstantes.COL_20_FSALIDA+" BETWEEN TO_DATE('"+strDesde+"','"+forFecha+"') " +
													" AND TO_DATE('" + strHasta+ "','" + forFecha + "') " +
													" or (" + m05bConstantes.COL_20_FENTRADA+" <= TO_DATE ('"+strDesde+"','"+forFecha+"') " +
													" AND " + m05bConstantes.COL_20_FSALIDA+" >= TO_DATE ('" + strHasta+ "','" + forFecha + "') )" +
													" or (" + m05bConstantes.COL_20_FENTRADA+" >= TO_DATE ('"+strDesde+"','"+forFecha+"') " +
													" AND " + m05bConstantes.COL_20_FSALIDA+" <= TO_DATE ('" + strHasta+ "','" + forFecha + "')))))  ) OT";				    	
					    subAsig=" (select " + m05bConstantes.COL_34_MATRICULA +
					    		" from " + m05bConstantes.TAB_VEHICULOS +
					    		" where " + m05bConstantes.COL_34_MATRICULA + 
					    				" not in (select " + m05bConstantes.COL_01_MATRICULA +
					    							" from " + m05bConstantes.TAB_ASIGNACION +
					    							" where  " +  m05bConstantes.COL_01_FINALIZA + " = 'N' " + 
					    							" or ( " + m05bConstantes.COL_01_FDESDE + 
				    									" between to_date ('"+strDesde+"','"+forFecha+"') " +
				    									" and to_date ('" + strHasta+ "','" + forFecha + "')" +
					   								" or " + m05bConstantes.COL_01_FDEVOL + 
				    									" between to_date ('"+strDesde+"','"+forFecha+"') " +
				    									" and to_date ('" + strHasta+ "','" + forFecha + "') " +
													" or ( " + m05bConstantes.COL_01_FDESDE + " <= " +
				    									" to_date ('"+strDesde+"','"+forFecha+"') " +
					   									" and " + m05bConstantes.COL_01_FDEVOL + " >= " +
				    									" to_date ('" + strHasta+ "','" + forFecha + "')) " +				    		
					   								" or ( " + m05bConstantes.COL_01_FDESDE + " >= " +
				    									" to_date ('"+strDesde+"','"+forFecha+"') " +
					   									" and " + m05bConstantes.COL_01_FDEVOL + " <= " +
				    									" to_date ('" + strHasta+ "','" + forFecha + "')" + " )))) ASIG ";
					if (permanente.equals("N")) {
					    tablas = tablas + ", " + subOT + ", " + subAsig;
					} else {
					    tablas = tablas + ", " + subAsig;
					}
					
				    order =" order by V."+m05bConstantes.COL_34_MATRICULA ;
				    
					sql = campos+tablas+where+order;
					miLog.addTraza(persona, "Coches Libres Asig", "SQL-2: " + sql, m05bConstantes.LOG_NIVEL_BAJO);
			}
			m05bCombo miCombo = new m05bCombo();
		    m05bPstmt = m05bConn.prepareStatement(sql);
			m05bRS = m05bPstmt.executeQuery();
		    ubicacion=""; color=""; kmact=""; marca=""; modelo="";
		    kmsalid="";
			while (m05bRS.next() == true) {
		        //if (matAux.equals("")){
				    matricula= (m05bRS.getString(1)==null)?"":m05bRS.getString(1);
		            matricula=matricula.toUpperCase();
		            //matAux=matricula;
		            //tabAux= m05bRS.getString(m05bConstantes.COL_99_CODTABLA);
		            kmsalid= m05bRS.getString(2);
		            //if (tabAux.equals("1")) 
		            	color=m05bRS.getString(4)==null?"":m05bRS.getString(4);
		           	kmact=m05bRS.getString(2)==null?"":m05bRS.getString(2);
		            //else if (tabAux.equals("7")) 
		            marca=m05bRS.getString(3)==null?"":m05bRS.getString(3);
		            //else if (tabAux.equals("12")) 
		            ubicacion=m05bRS.getString(5)==null?"":m05bRS.getString(5).toUpperCase();
		            modelo= m05bRS.getString(6)==null?"":m05bRS.getString(6);
		            color= (m05bRS.getString(7)==null?"":m05bRS.getString(7));
		            if(matriculaAsig!=null &&
		            	(matriculaAsig.toUpperCase()).equals(matricula)){
		            	esAsig = true;
		            	break;
		            }
		            
		    }
} catch (Exception SQLException) {
	
	m05bClasses.m05bFileLog miLog = new m05bClasses.m05bFileLog();
	miLog.addTraza(SQLException);
} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
			if (m05bRS != null)
				m05bRS.close();
			if (m05bPstmt != null)
				m05bPstmt.close();
			if (m05bConn != null)
				m05bConn.close();
}
%>
<%
	if(esAsig){
%>	 
	<script language="JAVASCRIPT">
         docu.esMatricValid.value='S'; 
         docu.esMatricValid.focus();
         window.close();
	</script>
<%
	}else{
%>
	<script language="JAVASCRIPT">
		docu.esMatricValid.value='N'; 
		docu.esMatricValid.focus();
		window.close();
	</script>
<%
	}
%>
</BODY>
</HTML>