package gastostarjetaServlet;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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


/**
 * Inserte aquí la descripción del tipo.
 * Fecha de creación: (4/11/04)
 */

public class m05bBorrarGastosTarjetaServlet 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
 */
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
 *
*/

public void performTask(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws java.io.IOException {
	   String sMesBorrado, sEjerBorrado;
       String sCodigo, sTarjeta="", sMes, sEjercicio, sLitros, sImporte, sMatricula="", sKm, sTipo="";
       m05bComun comun= new m05bComun();
	   java.sql.Connection connSelect=null;
	   java.sql.Connection connOper=null;
	   PreparedStatement pstmtSelect = null;
	   PreparedStatement pstmtOper= null;
	   ResultSet rs = null;
       String sql="", mensaje;
       
   	   try{
			
			sMesBorrado=request.getParameter("Mes");
        	sEjerBorrado=request.getParameter("Ejer");
        	
        	//Abrir Conexiones
			m05bConexion miConex = new m05bConexion();
			connSelect = miConex.getConnection();
			connOper = miConex.getConnection();
			connOper.setAutoCommit(false);  // Al final si todo ha ido bien se hace commit
			
			// Acumular gastos de M0518T00 por
			//	    tarjeta, matricula, tipo de gasto, mes y año
			
			sql = " select " + m05bConstantes.COL_18_MATRICULA + ", " +
				    		m05bConstantes.COL_18_TARJETA + ", " +
							m05bConstantes.COL_18_TIPO + ", " +
				    		" sum(" + m05bConstantes.COL_18_IMPORTE + 
				    		"),sum( " + m05bConstantes.COL_18_KM + 
							"),sum( " + m05bConstantes.COL_18_LITROS + " )" +
							", to_char(" + m05bConstantes.COL_18_FECHA + ",'MM')," +
							" to_char(" + m05bConstantes.COL_18_FECHA + ",'YYYY')" +
					" from " + m05bConstantes.TAB_OPEBANCOS  +
				    " group by " +  m05bConstantes.COL_18_TARJETA + ", " +  m05bConstantes.COL_18_MATRICULA + " ," +
				    		m05bConstantes.COL_18_TIPO + ", to_char(" + m05bConstantes.COL_18_FECHA + ",'MM')," +
							" to_char(" + m05bConstantes.COL_18_FECHA + ",'YYYY')" +
				    " having min( to_char(" + m05bConstantes.COL_18_FECHA + ",'YYYYMM')) <= " + sEjerBorrado + sMesBorrado ;
            pstmtSelect = connSelect.prepareStatement(sql);
	        rs = pstmtSelect.executeQuery();
	        
			// Por cada registro obtenido
		   	//         Insertar importe acumulado en M0546T00 (Historico)
	        while (rs.next() == true){
	        	    sMatricula = rs.getString(1);
	        	    sTarjeta = rs.getString(2);
	        	    sTipo = rs.getString(3);
					sImporte = rs.getString(4);
					sKm = rs.getString(5)==null?"0":rs.getString(5);
					sLitros = rs.getString(6);
					sMes = rs.getString(7);
					sEjercicio = rs.getString(8);	
					sql = " insert into " + m05bConstantes.TAB_HIST_OPEBANCOS +
								" (" + m05bConstantes.COL_46_MATRICULA + ", " + m05bConstantes.COL_46_TARJETA + "," +
		                    	m05bConstantes.COL_46_TIPO + "," + m05bConstantes.COL_46_EJER + "," +
		                    	m05bConstantes.COL_46_MES + "," + m05bConstantes.COL_46_IMPORTE + ","+
		                    	m05bConstantes.COL_46_KM + "," + m05bConstantes.COL_46_LITROS +
		                    " ) values " + "('" + sMatricula + "' ,'" + sTarjeta + "','" + sTipo + "'," + sEjercicio +
		                    	","  + sMes + " , " + sImporte + ", " + sKm + ", " + sLitros + " )" ;
		            pstmtOper = connOper.prepareStatement(sql);
		   			pstmtOper.executeUpdate();
					pstmtOper.close();
            }
            
			// Borrado de la informacion de M0518T00
			if (sMatricula != "")  {
					mensaje=m05bConstantes.MSG_OK;
					sql = "delete from " + m05bConstantes.TAB_OPEBANCOS +
						" where to_char(" + m05bConstantes.COL_18_FECHA + ",'YYYYMM') <= " + sEjerBorrado + sMesBorrado;
		            pstmtOper = connOper.prepareStatement(sql);
					pstmtOper.executeUpdate();
			} else  {
					mensaje=m05bConstantes.MSG_NODATOS;
			}
			//Si todo ha ido bien se actualizan los cambios en la BD
			connOper.commit();

			response.sendRedirect("m05bBorradoGastosJsp.jsp?Mensaje="+mensaje);

		}	catch (SQLException e1) {
				try {
		  			connOper.rollback(); // Ha habido problemas, se deshacen todos los cambios
		  		} catch (SQLException e2) {
		  			miLog.addTraza(e2);
		  		}
		  		response.sendRedirect("m05bError.jsp");
		}  finally {
				try {
					if (rs != null)
						rs.close();
					if (pstmtSelect != null)
						pstmtSelect.close();
					if (pstmtOper != null)
						pstmtOper.close();
		    		if (connSelect != null)
						connSelect.close();
					if (connOper != null)
						connOper.close();
				} catch (SQLException ex) {
					response.sendRedirect("m05bError.jsp");
				}
		}
	}
// Fin clase
}