package tasasServlet;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Locale;

import javax.servlet.http.HttpSession;

import m05bBD.m05bTasas;
import m05bClasses.m05bComun;
import m05bClasses.m05bConexion;
import m05bClasses.m05bConstantes;
import m05bClasses.m05bFileLog;

public class m05bGrabarTasasServlet extends javax.servlet.http.HttpServlet{

       String sCodigo;
       String sConcepto;
       String sMatricula;
       String sqlAux1;
       java.sql.Connection conn=null;
       PreparedStatement pstmtAux1 = null;
       ResultSet rsAux1 = null;
       String persona="";
       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
 */

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

}
/**
 * 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
 */

  private boolean ExisteTasa()throws java.lang.Exception{
  String verified = "0";
  
  	try{
  	 sqlAux1 = "SELECT 1  FROM "+ m05bConstantes.TAB_TASAS +
  	 " WHERE " + m05bConstantes.COL_32_CONCEPTO+ "='" + sConcepto + 
  	 "' AND " + m05bConstantes.COL_32_CODIGO+ "!='" + sCodigo + "'" +
  	 " AND " + m05bConstantes.COL_32_MATRICULA+ "='" + sMatricula + "'" ;  	 	 
     miLog.addTraza(persona , "m05b - Grabar Vehiculos:  " , "Ejecutar Existe NoSerieo: sql: " + sqlAux1,m05bConstantes.LOG_NIVEL_BAJO);
  	 
	 pstmtAux1 = conn.prepareStatement(sqlAux1);
	 rsAux1 = pstmtAux1.executeQuery();
	 if (rsAux1.next()){ 
		 return true;}
     else { return false;}
		 
	   } catch (Exception ex) {
	   	      throw new Exception(ex);	
	   } finally{
	   	    try {
				if (rsAux1 != null)
					rsAux1.close();
				if (pstmtAux1 != null)
					pstmtAux1.close();
				
			} catch (SQLException e2) {
				throw new Exception(e2);          
	  		}
	   }
  }

public void performTask(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) {

       //String sCodigo;
       String sFecha;
       String sCV="";
       //String sConcepto;
       String sImporte;
       String sMunicipio;
       String sObserv;
       //String sMatricula;
       String accion;
       String sql="";
       String forward = "";      
	   //java.sql.Connection conn=null;
	   PreparedStatement pstmt = null;
	   boolean finalizado = false; 
	   m05bComun miComun = new m05bComun();
       
       try

	{
        HttpSession session = request.getSession(true);
    	Locale idiomaLocal = (Locale)session.getAttribute("Local");
 		String idiomaSes = ((idiomaLocal.getLanguage()==null)?"es":idiomaLocal.getLanguage());	
 	
         String campoTexto="C";
        
         if(!idiomaSes.equals("es")){
 			campoTexto="E";
 		
         }
        persona = (String) session.getAttribute("persona");	 
        m05bFileLog miLog = new m05bFileLog(); 
        m05bComun comun = new m05bComun();
		m05bTasas tasas = (m05bTasas)session.getAttribute("m05bTasas");
		sCodigo = request.getParameter("Codigo");
		tasas.setTas_codigo(sCodigo);
		sFecha=request.getParameter("Fecha");
        if (sFecha!=null)
        sFecha=comun.FechaBD(sFecha,"00:00",campoTexto);
		tasas.setTas_fecha(sFecha);
        sConcepto=request.getParameter("Concepto");
        tasas.setTas_concepto(sConcepto);
        sImporte=request.getParameter("Importe");
        if (sImporte==null || sImporte.equals("NULL") || sImporte.equals(""))
        	sImporte="0";
        //sImporte=request.getParameter("Importe")==null?"0":request.getParameter("Importe");        
        sImporte=miComun.ImportesABasedatos(sImporte);
        sCV=request.getParameter("CVFiscales")==null?"":request.getParameter("CVFiscales");
        miLog.addTraza(persona , "m05b - Grabar Tasas:  " , "CVFiscales: " + sCV,m05bConstantes.LOG_NIVEL_BAJO);
        
        //sCV=miComun.ImportesABasedatos(sCV);
        
        tasas.setTas_importe(sImporte);
        sMunicipio=request.getParameter("Municipio");
        tasas.setTas_municipio(sMunicipio);
        sObserv=request.getParameter("Observacion");
        if (sObserv != null)
        	sObserv= sObserv.toUpperCase();
        tasas.setTas_observacion(sObserv);
        sMatricula=request.getParameter("Matricula");
		accion=request.getParameter("Accion");

		
			m05bConexion miConex = new m05bConexion();
			conn = miConex.getConnection();
			  if (accion.equals(m05bConstantes.ACC_ACTUALIZAR))  {
		  	    if (ExisteTasa()){ 
               	 //conn.rollback(); 
			   	 forward="m05bTasasJsp.jsp?Matricula=" + sMatricula + "&Mensaje=" + m05bConstantes.MSG_EXISTETASA;
			    }else {
			      
			       sql = "update " +m05bConstantes.TAB_TASAS+
					" set "+m05bConstantes.COL_32_FECHA+" = TO_DATE('"+sFecha+"','YYYYMMDDHH24MISS')" +
                    " , "+m05bConstantes.COL_32_CONCEPTO+" =  '"+ sConcepto+
                    "' , "+m05bConstantes.COL_32_IMPORTE+" =  "+ sImporte+
                    " , "+m05bConstantes.COL_32_MUNICIPIO+" = '"+sMunicipio+
                    "' , "+m05bConstantes.COL_32_OBSERV+" =  '"+sObserv+
					"' where	"+m05bConstantes.COL_32_CODIGO+" = '"+sCodigo+"'";
		            miLog.addTraza(persona , "m05bGrabarTasasServlet" , "sql1: " + sql ,m05bConstantes.LOG_NIVEL_BAJO);			       
		            pstmt = conn.prepareStatement(sql);
				    pstmt.executeUpdate();
     				if (pstmt != null)
 	    				pstmt.close();
			        if (sConcepto.equals(m05bConstantes.TAS_OBLIG)){
                    sql = "update " + m05bConstantes.TAB_VEHICULOS +
	                       " set " + m05bConstantes.COL_34_CV + " = '" + sCV + "'" +
		                   " where "+m05bConstantes.COL_34_MATRICULA +" = '" + sMatricula + "'" ;
                    miLog.addTraza(persona , "m05b - Grabar TasasServlet:  " , "sql2: " + sql,m05bConstantes.LOG_NIVEL_BAJO);
		            pstmt = conn.prepareStatement(sql);
				    pstmt.executeUpdate();
     				if (pstmt != null)
 	    				pstmt.close();
 	    			}
					//if (conn != null)
					//	conn.close();
			        forward="m05bVehTasasJsp.jsp?Matricula=" + sMatricula + "&Mensaje=" + m05bConstantes.MSG_OK; 
			       }
				/*pstmt.setString(1, sFecha);
                pstmt.setString(2, sConcepto);
                pstmt.setString(3, sImporte);
                pstmt.setString(4, sMunicipio);
                pstmt.setString(6, sObserv);
				pstmt.setString(7, sCodigo);*/
			  }
			  else if (accion.equals(m05bConstantes.ACC_INSERTAR))  {
		  	    if (ExisteTasa()){ 
               	 //conn.rollback(); 
			   	 forward="m05bTasasJsp.jsp?Codigo="+sCodigo+"&Matricula=" + sMatricula + "&Mensaje=" + m05bConstantes.MSG_EXISTETASA;
			     }else {
				sql="insert into	 " +m05bConstantes.TAB_TASAS+
					"("+m05bConstantes.COL_32_CODIGO+","+m05bConstantes.COL_32_MATRICULA+","+m05bConstantes.COL_32_CONCEPTO+
                     ","+m05bConstantes.COL_32_FECHA+","+m05bConstantes.COL_32_IMPORTE+","+m05bConstantes.COL_32_MUNICIPIO+
                     ","+m05bConstantes.COL_32_OBSERV+") " +
					" values ('"+sCodigo+"','"+sMatricula+"','"+sConcepto+
				    "',TO_DATE('"+sFecha+"','YYYYMMDDHH24MISS'),"+sImporte+",'"+sMunicipio+"','"+sObserv+"')";
                    miLog.addTraza(persona , "m05b - Insettar Tasas:  " , "Ejecutar Insertar Tasa: sql: " + sql,m05bConstantes.LOG_NIVEL_BAJO);
				
                    pstmt = conn.prepareStatement(sql);
				    pstmt.executeUpdate(); 
     				if (pstmt != null)
 	    				pstmt.close();
			        if (sConcepto.equals(m05bConstantes.TAS_OBLIG)){
                    sql = "update " + m05bConstantes.TAB_VEHICULOS +
	                       " set " + m05bConstantes.COL_34_CV + " = '" + sCV + "'" +
		                   " where "+m05bConstantes.COL_34_MATRICULA +" = '" + sMatricula + "'" ;
                    miLog.addTraza(persona , "m05b - Grabar Tasas:  " , "Ejecutar Actualizar Vehiculo: sql: " + sql,m05bConstantes.LOG_NIVEL_BAJO);
		             
		            pstmt = conn.prepareStatement(sql);
				    pstmt.executeUpdate();
     				if (pstmt != null)
 	    				pstmt.close();}
					//if (conn != null)
					//	conn.close();
			        forward="m05bVehTasasJsp.jsp?Matricula=" + sMatricula + "&Mensaje=" + m05bConstantes.MSG_OK;
                }

			}
			else   {

				pstmt = conn.prepareStatement(
					"delete from  " +m05bConstantes.TAB_TASAS+
					" where "+m05bConstantes.COL_32_CODIGO+" =  ? " );
				pstmt.setString(1, sCodigo);
			    pstmt.executeUpdate();
				
			    if (sConcepto.equals(m05bConstantes.TAS_OBLIG)){
                     sql = "update " + m05bConstantes.TAB_VEHICULOS +
	                       " set " + m05bConstantes.COL_34_CV + " = ''" +
		                   " where "+m05bConstantes.COL_34_MATRICULA +" = '" + sMatricula + "'" ;
                     miLog.addTraza(persona , "m05b - Grabar Tasas:  " , "Ejecutar Actualizar Vehiculo: sql: " + sql,m05bConstantes.LOG_NIVEL_BAJO);
		             
		            pstmt = conn.prepareStatement(sql);
				    pstmt.executeUpdate();
     				if (pstmt != null)
 	    				pstmt.close();}
				    //if (conn != null)
					//   conn.close();
			    forward="m05bVehTasasJsp.jsp?Matricula=" + sMatricula + "&Mensaje=" + m05bConstantes.MSG_OK;     

	       }
		}catch (Exception ex) {
			try {
				forward = "m05bTasasJsp.jsp?Matricula=" + sMatricula + "&Mensaje=" +m05bConstantes.MSG_ERRORBD;
				conn.rollback();  
	  			// Ha habido problemas, se deshacen todos los cambios
	  		} catch (SQLException e2){
                miLog.addTraza(persona , "m05b - Grabar Vehiculos:  " , "Actualizar/Insertar Vehiculo: EXCEPTION-2:" + e2,m05bConstantes.LOG_NIVEL_BAJO);
                forward = "m05bError.jsp";
	  		}
		}finally{
  			try {
	  			conn.commit();  
	  			// Ha ido todo OK
				if (pstmt != null)
					pstmt.close();	  			
				if (conn != null)
					conn.close();
	  		} catch (SQLException e){
	  			forward = "m05bError.jsp";
	  	  	}
			try {
	  			//Si todo ha ido bien se actualizan los cambios en la BD
	  			response.sendRedirect(forward);
	  		} catch (IOException e1) {
	  			miLog.addTraza(persona , "m05b - Grabar Vehiculos:  " , "Actualizar/Insertar Vehiculo: EXCEPTION-redirect!!!:" + e1,m05bConstantes.LOG_NIVEL_BAJO);
	  		}
	  	
		}
		
		}
		}