/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package server;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.DefaultComboBoxModel;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Daryamz
 */
public class Operaciones extends UnicastRemoteObject implements Interface {

    private Conexion conn;
    private ResultSet rs;
    private Statement st;
    private DefaultTableModel modelTablaCHC;
    private DefaultComboBoxModel modelCombo;
    Connection cn;

    public Operaciones() throws RemoteException {
        super();
        conn = new Conexion();
        modelTablaCHC = new DefaultTableModel();
        modelCombo = new DefaultComboBoxModel();
        Connection cn = conn.getConexion();
    }

    private ResultSet executeQuery(String sql) throws SQLException {
        st = (Statement) conn.getConexion().createStatement();
        rs = st.executeQuery(sql);
        return rs;
    }

    private ResultSet UpdateQuery(String sql) throws SQLException {
        st = (Statement) conn.getConexion().createStatement();
        st.executeUpdate(sql);
        return rs;
    }

    public void clearTable(DefaultTableModel modelTablaCHC) {
        int count = modelTablaCHC.getRowCount();
        for (int i = 1; i <= count; i++) {
            modelTablaCHC.setColumnCount(i);
        };
    }

    @Override
    public DefaultTableModel fillTable(String sql) throws SQLException, RemoteException {
        //clearTable(modelTablaCHC);
        //rs.deleteRow();
        modelTablaCHC = new DefaultTableModel();
        executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        int col = rsmd.getColumnCount();
        //this.modeloTabla.setColumnIdentifiers(fields);
        for (int i = 1; i <= col; i++) {
            modelTablaCHC.addColumn(rsmd.getColumnLabel(i));
        };
        while (rs.next()) {
            String fila[] = new String[col];
            for (int j = 0; j < col; j++) {
                fila[j] = rs.getString(j + 1);
            }
            this.modelTablaCHC.addRow(fila);
        }
        modelTablaCHC.fireTableDataChanged();
        return modelTablaCHC;
    }

    @Override
    public String registrar(String value, String tabla, String campo) throws SQLException, RemoteException {
        //conn = new Conexion();
        try {
            String sql = "INSERT INTO " + tabla + "(" + campo + ") VALUES('" + value + "')";
            //String sql = "INSERT INTO " + tabla+"("+campo+") VALUES('" + value.get(0).toString() + "')";
            System.out.println(sql);
            st = (Statement) conn.getConexion().createStatement();
            st.executeUpdate(sql);
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return "Elemento Agregado";
    }

    @Override
    public String eliminar(Integer value, String tabla, String id) throws SQLException, RemoteException {
        String sql = "DELETE FROM " + tabla + " WHERE " + id + " = " + value + ";";
        System.out.println(sql);
        st = (Statement) conn.getConexion().createStatement();
        System.out.println(st);
        st.executeUpdate(sql);

        return "Elemento eliminado";
    }

    @Override
    public String actualizar(String value, String campo, String id, String tabla, String campoId) throws SQLException, RemoteException {
        //conn = new Conexion();
        try {
            String sql = "UPDATE " + tabla + " SET " + campo + " = " + value + " WHERE " + campoId + " = " + id + ";";
            //String sql = "UPDATE " + tabla+" SET "+campo+" = "+value.get(0).toString()+" WHERE "+campoId+" = "+id+";";
            System.out.println(sql);
            st = (Statement) conn.getConexion().createStatement();
            st.executeUpdate(sql);
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return "Elemento Agregado";
    }

    public ArrayList max(String sql) throws SQLException, RemoteException {
        st = (Statement) conn.getConexion().createStatement();
        rs = st.executeQuery(sql);
        ArrayList<String> res = new ArrayList();
        res.add(rs.getString(0));
        return res;
    }

    public void buscar(String value, String tabla, String campo) throws SQLException, RemoteException {
        String sql = "select "
                + "id_municipio, "
                + "municipio AS Municipio "
                + "from " + tabla + " "
                + "where " + campo + " like '" + value + "%' ;";
        System.out.println(sql);
        fillTable(sql);
    }

    ///////////////////////////////////////////////////////////////////////////////////////////////////
    @Override
    public String registrarPro(String value, String tabla, String campo) throws SQLException, RemoteException {
        //conn = new Conexion();
        try {
            //String sql = "INSERT INTO " + tabla+"("+campo+") VALUES('" + value + "')";
            String sql = "INSERT INTO " + tabla + "(" + campo + ") VALUES('" + value + "');";
            System.out.println(sql);
            st = (Statement) conn.getConexion().createStatement();
            st.executeUpdate(sql);
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return "Elemento Agregado";
    }

    @Override
    public String actualizarPro(ArrayList value, ArrayList campo, String id, String tabla, String campoId) throws SQLException, RemoteException {
        //conn = new Conexion();
        try {
            for (int i = 0; i < campo.size(); i++) {
                String sql = "UPDATE " + tabla + " SET " + campo.get(i)
                        //////////7
                        + " = '" + value.get(i) + "' WHERE " + campoId + " = " + id + ";";
                //String sql = "UPDATE " + tabla+" SET "+campo+" = "+value.get(0).toString()+" WHERE "+campoId+" = "+id+";";
                System.out.println(sql);
                st = (Statement) conn.getConexion().createStatement();
                st.executeUpdate(sql);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return "Elemento Agregado";
    }

    @Override
    public DefaultComboBoxModel fillCombo(String sql) throws SQLException {
        //modelCombo = new DefaultComboBoxModel();
        executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        int col = rsmd.getColumnCount();
        for (int i = 1; i <= col; i++) {
            this.modelCombo.addElement(rsmd.getColumnLabel(i));
        }
        return modelCombo;
    }

    public void IniciarHistorial(String fecha, String monto, String cliente) {
        try {
            String sql = "INSERT INTO ca_haberes "
                    + "(id_movimiento, fecha, monto, total_haberes, disponibilidad, id_cliente) "
                    + "VALUES ('2','" + fecha + "','" + monto + "','" + monto + "','APORTE INICIAL','" + cliente + "');";
            System.out.println(sql);
            st = (Statement) conn.getConexion().createStatement();
            st.executeUpdate(sql);
        } catch (Exception ex) {
            Logger.getLogger(Operaciones.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}
