package vehiculosServlet;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;

import m05bClasses.m05bConexion;
import m05bClasses.m05bConstantes;
import m05bClasses.m05bFileLog;
import m05bClasses.m05bGenerarClave;


/**
 * Inserte aquí la descripción del tipo.
 * Fecha de creación: (10/11/04)
 */
public class m05bCambiarMatriculaServlet extends javax.servlet.http.HttpServlet {m05bFileLog miLog = new m05bFileLog();
/**
 * Process incoming HTTP POST requests
 *
 * @param request Object that encapsulates the request to the servlet
 * @param response Object that encapsulates the response from the servlet
 */    

	String MatricActual;
	String MatricNueva;
	java.sql.Connection conn=null;

public void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, java.io.IOException {

	performTask(request, response);

}
/**
 * Returns the servlet info string.
 */
public String getServletInfo() {

	return super.getServletInfo();

}
/**
 * Initializes the servlet.
 */
public void init() {
	// insert code to initialize the servlet here
		
}


/**
 * Initializes the servlet.
 */
    
public boolean fncMatricExiste(String matricula) throws java.lang.Exception  {
	   String sql;
	   PreparedStatement pstmt = null;
	   ResultSet rs = null;
	   boolean bExiste=false;

	   // Obtiene el siguiente ID que se va a almacenar en la tabla Historico de Vehiculos
	   try
	   {   
		   sql = "select  1" + 
				 " from " + m05bConstantes.TAB_VEHICULOS + 
				 " where " + m05bConstantes.COL_34_MATRICULA + " = '" + matricula + "'";
	       pstmt = conn.prepareStatement(sql);
		   rs = pstmt.executeQuery();
		   if (rs.next() == true){
	        	    bExiste=true;
	 	   } 
	  } catch (Exception ex) {
			try {
	  			conn.rollback();  // Ha habido problemas, se deshacen todos los cambios
	  			throw new Exception(ex);
	  		} catch (SQLException e1){
	  			System.err.println("m05b - Cambiar Matricula: EXCEPTION: OutletMaintenanceS.service: " + e1);
	  		}
	   } finally{
	   	    try {
	   	    	if (rs != null)
	   	    		rs.close();
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e2) {
				System.err.println("m05b - Cambiar Matricula: EXCEPTION: OutletMaintenanceS.service: " + e2);
	  		}
       } 
       return bExiste;	
}  // fin funcion fncMatricExiste
    
public void actualizarMatricula(String tabla, String campo) throws java.lang.Exception  {
	   
	   // Pone como matricula actual la nueva matricula
	   	   
	   String sql;
	   PreparedStatement pstmt = null;
	   
	   try
	   {   
		   sql = " update " + tabla  +
		 		 " set " + campo + " = '" + MatricNueva + "'" +
		 		 " where " + campo + " = '" + MatricActual + "'";
	       pstmt = conn.prepareStatement(sql);
		   pstmt.executeUpdate();
	   } catch (Exception ex) {
			try {
	  			conn.rollback();  // Ha habido problemas, se deshacen todos los cambios
	  			throw new Exception(ex);
	  		} catch (SQLException e1){
	  			System.err.println("m05b - Cambiar Matricula: EXCEPTION: OutletMaintenanceS.service: " + e1);
	  		}
	   } finally{
	   	    try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e2) {
				System.err.println("m05b - Cambiar Matricula: EXCEPTION: OutletMaintenanceS.service: " + e2);
	  		}
	   }
}


public void actualizarMatricAnterior( ) throws java.lang.Exception  {

	   //Poner matricula anterior en tabla vehiculos
	   
	   String sql;
	   PreparedStatement pstmt = null;
	   

	   try
	   {   
    	   sql = " update " + m05bConstantes.TAB_VEHICULOS  +
				 " set " + m05bConstantes.COL_34_MATRIC_ANT + " = '" + MatricActual + "'" +
		 		 " where " + m05bConstantes.COL_34_MATRICULA + " = '" + MatricNueva + "'" ;
	       pstmt = conn.prepareStatement(sql);
		   pstmt.executeUpdate();
	   } catch (Exception ex) {
			try {
	  			conn.rollback();  // Ha habido problemas, se deshacen todos los cambios
	  			throw new Exception(ex);
	  		} catch (SQLException e1){
	  			System.err.println("m05b - Cambiar Matricula: EXCEPTION: OutletMaintenanceS.service: " + e1);
	  		}
	   } finally{
	   	    try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e2) {
				System.err.println("m05b - Cambiar Matricula: EXCEPTION: OutletMaintenanceS.service: " + e2);
	  		}
	   }
}


public void grabarHistorico(int id) throws java.lang.Exception  {
	
	   // Graba un registro histórico a partir de los datos del vehiculo al que se ha cambiado la matricula

	   String sql;
	   java.sql.Statement pstmt = null;
	   PreparedStatement pstmtInsert = null;
	   ResultSet rs = null;

	   SimpleDateFormat formatterDate = new SimpleDateFormat ("dd/MM/yyyy");
	   
   	   String marca = "", rango = "", kmact = "", expte = "", color = "", serie = "", cv = "", poliza = "";
	   String vcontab = "", precio = "", vidautil = "", amoanual = "", amoacum = "", impbaja = "";
	   String situacion = "", coddpto = "", desdpto = "", regadquis = "", motbaja = "";
	   String destino = "", kmrevis = "", mesrevis = "", autaparc = "", rotul = "", aparcado = "";
	   String infadic1 = "", infadic2 = "", matricant = "", modelo = "", notas = "";
	   String fkmact = "", fgarantia = "", falta = "", fmatric = "", fbaja = "", fadquis = "", fcambio = "", ffin = "";

	   try
	   {   
		// Obtener los datos del vehiculo correspondiente a esa matrícula
			   sql = "select " + m05bConstantes.COL_34_MATRIC_ANT + ", " + m05bConstantes.COL_34_MARCA +
					", " + m05bConstantes.COL_34_RANGO + ", " + m05bConstantes.COL_34_MODELO + ", " + m05bConstantes.COL_34_KMACTUALES +
					", " + m05bConstantes.COL_34_FKMACT + ", " + m05bConstantes.COL_34_FGARANTIA + ", " + m05bConstantes.COL_34_EXPDTE +
					", " + m05bConstantes.COL_34_COLOR + ", " + m05bConstantes.COL_34_NSERIE + ", " + m05bConstantes.COL_34_FALTA +
					", " + m05bConstantes.COL_34_FMATRIC + ", " + m05bConstantes.COL_34_FBAJA + ", " + m05bConstantes.COL_34_CV +
					", " + m05bConstantes.COL_34_POLIZA + ", " + m05bConstantes.COL_34_VCONTAB + ", " + m05bConstantes.COL_34_PRECIO +
					", " + m05bConstantes.COL_34_VIDAUTIL + ", " + m05bConstantes.COL_34_AMOANUAL + ", " + m05bConstantes.COL_34_AMOACUM +
					", " + m05bConstantes.COL_34_IMPBAJA + ", " + m05bConstantes.COL_34_SITUACION + ", " + m05bConstantes.COL_34_CODDPTO +
					", " + m05bConstantes.COL_34_DESDPTO + ", " + m05bConstantes.COL_34_FADQUIS + ", " + m05bConstantes.COL_34_REGADQUIS +
					", " + m05bConstantes.COL_34_MOTIVBAJA + ", " + m05bConstantes.COL_34_DESTINO + ", " + m05bConstantes.COL_34_KMREVIS +
					", " + m05bConstantes.COL_34_MESREVIS + ", " + m05bConstantes.COL_34_AUTORI_APAR + ", " + m05bConstantes.COL_34_ROTULADO +
					", " + m05bConstantes.COL_34_APARCADO + ", " + m05bConstantes.COL_34_INF1 + ", " + m05bConstantes.COL_34_INF2 +
					", " + m05bConstantes.COL_34_NOTA +
					" from " + m05bConstantes.TAB_VEHICULOS  +
				    " where " +  m05bConstantes.COL_34_MATRICULA + " = '" + MatricNueva + "'" ;
		       	pstmt = conn.createStatement();
		   		pstmt.execute(sql);
		   		rs = pstmt.getResultSet();
		   		if (rs.next()){
	       	    			matricant = rs.getString(1);
	       	    			marca = rs.getString(2);
	       	    			rango = rs.getString(3);
	       	    			modelo = rs.getString(4);
	       	    			kmact = rs.getString(5);
	       	    			if (rs.getDate(6) != null)
	       	    				fkmact = formatterDate.format(rs.getDate(6));
	       	    			if (rs.getDate(7) != null)
	       	    				fgarantia = formatterDate.format(rs.getDate(7));
	       	    			expte = rs.getString(8);
	       	    			color = rs.getString(9);
	       	    			serie = rs.getString(10);
	       	    			if (rs.getDate(11) != null)
	       	    				falta = formatterDate.format(rs.getDate(11));
	       	    			if (rs.getDate(12) != null)
	       	    				fmatric = formatterDate.format(rs.getDate(12));
	       	    			if (rs.getDate(13) != null)
		       	    			fbaja = formatterDate.format(rs.getDate(13));
	       	    			cv = rs.getString(14);
	       	    			poliza = rs.getString(15);
	       	    			vcontab = rs.getString(16)==null?"":rs.getString(16);
	       	    			precio = rs.getString(17);
	       	    			vidautil = rs.getString(18);
							amoanual = rs.getString(19)==null?"":rs.getString(19);
							amoacum = rs.getString(20)==null?"":rs.getString(20);
							impbaja = rs.getString(21)==null?"0":rs.getString(21);
							situacion = rs.getString(22);
							coddpto = rs.getString(23);
							desdpto = rs.getString(24);
							if (rs.getDate(25) != null)
								fadquis = formatterDate.format(rs.getDate(25));
							regadquis = rs.getString(26)==null?"":rs.getString(26);
							motbaja = rs.getString(27)==null?"":rs.getString(27);
	   	  					destino = rs.getString(28)==null?"":rs.getString(28);
					   	    kmrevis = rs.getString(29);
	   						mesrevis = rs.getString(30);
	   						autaparc = rs.getString(31)==null?"":rs.getString(31);
	   						rotul = rs.getString(32)==null?"":rs.getString(32);
	   						aparcado = rs.getString(33)==null?"":rs.getString(33);
	   						infadic1 = rs.getString(34)==null?"":rs.getString(34);
	   						infadic2 = rs.getString(35)==null?"":rs.getString(35);
	   						notas = rs.getString(36)==null?"":rs.getString(36);
	           }
	    } catch (Exception ex) {
			   try {
					conn.rollback();  // Ha habido problemas, se deshacen todos los cambios
					throw new Exception(ex);
			   } catch (SQLException e1){
					System.err.println("m05b - Cambiar Matricula: EXCEPTION: OutletMaintenanceS.service: " + e1);
			   }
		}
		
		try 
		{
		// Ejecutar la inserción de los datos obtenidos
			   sql = " insert into " + m05bConstantes.TAB_HIST_VEHICULOS  + " (" +
			    	  m05bConstantes.COL_44_ID + ", " + m05bConstantes.COL_44_MATRICULA + ", " + m05bConstantes.COL_44_MARCA +
	 				  ", " + m05bConstantes.COL_44_RANGO + ", " + m05bConstantes.COL_44_KMACTUALES + 
					  ", " + m05bConstantes.COL_44_FKMACT + ", " + m05bConstantes.COL_44_FGARANTIA + ", " + m05bConstantes.COL_44_EXPDTE +
					  ", " + m05bConstantes.COL_44_COLOR + ", " + m05bConstantes.COL_44_NSERIE + ", " + m05bConstantes.COL_44_FALTA +
					  ", " + m05bConstantes.COL_44_FMATRIC + ", " + m05bConstantes.COL_44_FBAJA + ", " + m05bConstantes.COL_44_CV +
					  ", " + m05bConstantes.COL_44_POLIZA + ", " + m05bConstantes.COL_44_VCONTAB + ", " + m05bConstantes.COL_44_PRECIO +
					  ", " + m05bConstantes.COL_44_VIDAUTIL + ", " + m05bConstantes.COL_44_AMOANUAL + ", " + m05bConstantes.COL_44_AMOACUM +
					  ", " + m05bConstantes.COL_44_IMPBAJA + ", " + m05bConstantes.COL_44_SITUACION + ", " + m05bConstantes.COL_44_CODDPTO +
					  ", " + m05bConstantes.COL_44_DESDPTO + ", " + m05bConstantes.COL_44_FCAMBIO + ", " + m05bConstantes.COL_44_FFIN +
					  ", " + m05bConstantes.COL_44_FADQUIS + ", " + m05bConstantes.COL_44_REGADQUIS + ", " + m05bConstantes.COL_44_MOTIVBAJA + 
				      ", " + m05bConstantes.COL_44_DESTINO + ", " + m05bConstantes.COL_44_KMREVIS + ", " + m05bConstantes.COL_44_MESREVIS + 
				      ", " + m05bConstantes.COL_44_AUTORI_APAR + ", " + m05bConstantes.COL_44_ROTULADO + ", " + m05bConstantes.COL_44_APARCADO + 
				      ", " + m05bConstantes.COL_44_INF1 + ", " + m05bConstantes.COL_44_INF2 + ", " + m05bConstantes.COL_44_MATRIC_ANT + 
	  			      ", " + m05bConstantes.COL_44_MODELO + ", " + m05bConstantes.COL_44_NOTA + ") " +
				   	  " values (" + id + ", '" + MatricNueva + "', '" + marca + "', '" + rango + "', " + kmact + 
					  ", to_date('" + fkmact + "','dd/mm/yyyy'), to_date('" + fgarantia + "','dd/mm/yyyy'), '" + expte + 
					  "', '" + color + "', '" + serie + "', to_date('" + falta + "','dd/mm/yyyy'), to_date('" + fmatric + 
					  "','dd/mm/yyyy'), to_date('" + fbaja + "','dd/mm/yyyy'), '" + cv + "', '" + poliza + "', '" + vcontab + 
				      "', " + precio + ", '" + vidautil + "', '" + amoanual + "', '" + amoacum + "', " + impbaja + 
				      ", '" + situacion + "', '" + coddpto + "', '" + desdpto + "', sysdate, to_date('" + ffin + 
				   	  "','dd/mm/yyyy'), to_date('" + fadquis + "','dd/mm/yyyy'), '" + regadquis + "', '" + motbaja + 
					  "', '" + destino + "', " + kmrevis + ", " + mesrevis + ", '" + autaparc + "', '" + rotul + "', '" + aparcado + 
					  "', '" + infadic1 + "', '" + infadic2 + "', '" + matricant + "', '" + modelo + "', '" + notas + "' )";

			    pstmtInsert = conn.prepareStatement(sql);
				pstmtInsert.executeUpdate();
	   } catch (Exception ex) {
			try {
	  			conn.rollback();  // Ha habido problemas, se deshacen todos los cambios
	  			throw new Exception(ex);
	  		} catch (SQLException e1){
	  			System.err.println("m05b - Cambiar Matricula: EXCEPTION: OutletMaintenanceS.service: " + e1);
	  		}
	   } finally{
	   	    try {
	   	    	if (rs !=  null)
	   	    		rs.close();
				if (pstmt != null)
					pstmt.close();
				if (pstmtInsert != null)
					pstmtInsert.close();
			} catch (SQLException e2) {
				System.err.println("m05b - Cambiar Matricula: EXCEPTION: OutletMaintenanceS.service: " + e2);
	  		}
	   }
}


/**
 * Process incoming requests for information
 *
 * @param request Object that encapsulates the request to the servlet
 * @param response Object that encapsulates the response from the servlet
 */
public void performTask(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) {


       String sql;
	   int id;

       try   {
	   	   
		   m05bConexion miConex = new m05bConexion();
		   conn = miConex.getConnection();
		   conn.setAutoCommit(false);  // Al final si todo ha ido bien se hace commit
	   	   
	       MatricActual = request.getParameter("MatricActual").toUpperCase();
		   MatricNueva = request.getParameter("MatricNueva").toUpperCase();
			try{
				// Comprobar que la nueva matrícula no exista en la aplicación
				if (fncMatricExiste(MatricNueva)) {
						response.sendRedirect("m05bCambioMatriculaJsp.jsp?Mensaje="+m05bConstantes.MSG_EXISTE);
				} else {
					// Actualizar tablas que contienen el campo matricula
						actualizarMatricula( m05bConstantes.TAB_VEHICULOS, m05bConstantes.COL_34_MATRICULA);
						actualizarMatricula( m05bConstantes.TAB_REVISIONES, m05bConstantes.COL_35_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_ITV,m05bConstantes.COL_27_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_ASIGNACION, m05bConstantes.COL_01_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_GASTOS, m05bConstantes.COL_09_MATRICULA);
						actualizarMatricula( m05bConstantes.TAB_OPEBANCOS, m05bConstantes.COL_18_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_REFORMAS, m05bConstantes.COL_37_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_CUOTAS, m05bConstantes.COL_41_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_AVISOS, m05bConstantes.COL_02_MATRICULA);
						actualizarMatricula( m05bConstantes.TAB_AMORTIZACION, m05bConstantes.COL_39_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_TASAS, m05bConstantes.COL_32_MATRICULA);
						actualizarMatricula( m05bConstantes.TAB_TARJETAS, m05bConstantes.COL_31_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_HIST_TARJETAS, m05bConstantes.COL_14_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_HIST_SEGUROS, m05bConstantes.COL_13_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_SANCIONES, m05bConstantes.COL_45_MATRICULA);
						actualizarMatricula( m05bConstantes.TAB_ORDENESTRAB, m05bConstantes.COL_20_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_SINIESTROS, m05bConstantes.COL_29_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_HIST_OPEBANCOS, m05bConstantes.COL_46_MATRICULA );
						actualizarMatricula( m05bConstantes.TAB_TRANSFERENCIAS, m05bConstantes.COL_40_MATRICULA );
						//actualizarMatricula( m05bConstantes.TAB_HIST_VEHICULOS, m05bConstantes.COL_44_MATRICULA);
											
					//Poner matricula anterior en tabla vehiculos
						actualizarMatricAnterior();
							
					//Grabar historico vehiculos (incluir notas y fecha del cambio)
							// Obtener el siguiente ID que se va a grabar en la tabla historico de vehiculos
						m05bGenerarClave miID=new m05bGenerarClave();
						id=miID.getNextId(m05bConstantes.TAB_HIST_VEHICULOS,m05bConstantes.COL_44_ID);
						grabarHistorico(id);
							
					//Si todo ha ido bien se actualizan los cambios en la BD
					    conn.commit();
							
						response.sendRedirect("m05bCambioMatriculaJsp.jsp?Mensaje="+m05bConstantes.MSG_OK);
				}
			} catch (Exception SQLException) {
					try {
			  			conn.rollback();  // Ha habido problemas, se deshacen todos los cambios
			  		} catch (SQLException e2){
			  			throw new Exception(e2);
			  		}
				miLog.addTraza(SQLException);
				response.sendRedirect("m05bError.jsp");
			} 	finally{
					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);
		}
	}
}
}
