/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package banco;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;


/**
 *
 * @author Admin
 */
public class bancoSQL {
    protected Connection conexionBD = null;

    public bancoSQL(){
     try
         {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
         }
         catch (Exception ex)
         {
            System.out.println(ex.getMessage());
         }
    }



    public String conectarBD(String usuario, String clave)
   {
      String error=null;
      if (this.conexionBD == null)
      {
         try
         {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
         }
         catch (Exception ex)
         {
            System.out.println(ex.getMessage());
         }

         try
         {
            String servidor = "localhost:3306";
            String baseDatos = "banco";
            String uriConexion = "jdbc:mysql://" + servidor + "/" + baseDatos;

            this.conexionBD = DriverManager.getConnection(uriConexion, usuario, clave);
         }
         catch (SQLException ex)
         {
            error="Se produjo un error al intentar conectarse a la base de datos.\n" + ex.getMessage();
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
         }
      }
      return error;
   }

   public void desconectarBD()
   {
      if (this.conexionBD != null)
      {
         try
         {
            this.conexionBD.close();
            this.conexionBD = null;
         }
         catch (SQLException ex)
         {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
         }
      }
   }


   public ArrayList<String> obtenerAtributos(String nbreTabla) throws SQLException{

         conectarBD("root", "inteldp35dp");
         String query="DESCRIBE "+nbreTabla;
         ArrayList<String> atrr=new ArrayList<String>();
         // se crea una sentencia o comando jdbc para realizar la consulta
         Statement stmt = this.conexionBD.createStatement();
         // se ejecuta la sentencia y se recibe un resultset
         ResultSet rs = stmt.executeQuery(query);

         while(rs.next()){
             atrr.add((String)rs.getObject(1));
         }
         stmt.close();
         rs.close();
         desconectarBD();
         return atrr;
   }


    public ArrayList<String> obtenerTablas() throws SQLException{

         conectarBD("root", "pillos");
         String query="SHOW TABLES FROM banco";
         ArrayList<String> atrr=new ArrayList<String>();
         // se crea una sentencia o comando jdbc para realizar la consulta
         Statement stmt = this.conexionBD.createStatement();
         // se ejecuta la sentencia y se recibe un resultset
         ResultSet rs = stmt.executeQuery(query);
         while(rs.next()){
             atrr.add((String)rs.getObject(1));
         }
         stmt.close();
         rs.close();
         desconectarBD();
         return atrr;
   }


    public ArrayList<ArrayList<String>> rsToArrayList(ResultSet rs) throws SQLException{

        ArrayList<ArrayList<String>> result=new ArrayList<ArrayList<String>>();
        ResultSetMetaData rsMetaData = rs.getMetaData();
        int cols = rsMetaData.getColumnCount();

         while(rs.next()){
             ArrayList<String> row=new ArrayList<String>();
             for (int j=1;j<=cols;j++)
             {
                 String cell=rs.getObject(j).toString();
                 row.add(cell);
             }
             result.add(row);
         }
        return result;
     }

    public boolean atm_verifPIN(String PIN, String nro_tarjeta) throws SQLException{
        //Debe estar la conexion con la base de datos establecida
        conectarBD("atm","atm");
        Statement stmt = this.conexionBD.createStatement();
        String Query="SELECT PIN, nro_tarjeta FROM Tarjeta"
                   + "WHERE nro_tarjeta="+nro_tarjeta+" AND "
                   + "      PIN="+PIN;
        ResultSet rs = stmt.executeQuery(Query);
        boolean vale = rs.next();
        stmt.close();
        desconectarBD();
        return vale;
    }

    public String atm_obtenerSaldoTarjeta(String PIN, String nro_tarjeta) throws SQLException{
        conectarBD("atm", "atm");
        //Obtener saldo
        Statement stmt=this.conexionBD.createStatement();

        String Query="SELECT Saldo FROM Tarjeta join Caja_Ahorro USING (nro_ca)"
            + "   WHERE nro_tarjeta="+nro_tarjeta;
        ResultSet rs = stmt.executeQuery(Query);
        rs.next();
        String Saldo=rs.getObject(1).toString();
        stmt.close();
        desconectarBD();
        return  Saldo;
    }

    public ArrayList<ArrayList<String>> atm_formatearMovimientos(ArrayList<ArrayList<String>> movimientos){

        //Montos para transferencia, extraccion y debito deben mostrarse negativos
        for (ArrayList<String> mov:movimientos){
             String tipo = mov.get(3);
            if (tipo.equals("Transferencia") ||
                tipo.equals("Extraccion") ||
                tipo.equals("Debito") ){
                    String monto=mov.get(4);
                    monto="-"+monto;
            }
        }
        return movimientos;
   }

    public ArrayList<ArrayList<String>> atm_obtenerUltimosMovimientos() throws SQLException{
        conectarBD("atm", "atm");
        Statement stmt=this.conexionBD.createStatement();

        String Query="SELECT fecha, hora, tipo, monto, cod_caja, destino"
                + "FROM trans_cajas_ahorro"
                + "ORDER BY fecha desc"
                + "limit 0,15";

        ResultSet rs = stmt.executeQuery(Query);
        ArrayList<ArrayList<String>> result=this.rsToArrayList(rs);
        stmt.close();
        desconectarBD();
        return  atm_formatearMovimientos(result);
    }

        public ArrayList<ArrayList<String>> atm_obtenerPeriodoMovimientos(Date inicio, Date fin) throws SQLException{
        conectarBD("atm", "atm");
        Statement stmt=this.conexionBD.createStatement();
        Fechas feca=new Fechas();
        String Query=""
                + "SELECT fecha, hora, tipo, monto, cod_caja, destino"
                + "FROM trans_cajas_ahorro"
                + "WHERE fecha<="+feca.convertirDateAStringDB(fin)+""
                + "AND fecha >="+feca.convertirDateAStringDB(inicio)
                + "ORDER BY fecha desc";

        ResultSet rs = stmt.executeQuery(Query);
        ArrayList<ArrayList<String>> result=this.rsToArrayList(rs);
        stmt.close();
        desconectarBD();
        return  atm_formatearMovimientos(result);
    }


     public ArrayList<ArrayList<String>> consulta(String query) throws SQLException{
        /* Debe ser una consulta (select) */
         ArrayList<ArrayList<String>>  result=new ArrayList<ArrayList<String>>();
         conectarBD("root", "");
         Statement stmt = this.conexionBD.createStatement();
         ResultSet rs = stmt.executeQuery(query);

         result=this.rsToArrayList(rs);

         stmt.close();
         rs.close();
         desconectarBD();
         return result;
   }




}