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

package teste;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

public class Controle_1 {

    private Connection con;
    private Statement statement;
    private ResultSet rs;

        public String[][] procurarPessoa(String descricao,String estado) {
        rs = null;
        String[][] dados = null;
        String Hd = "C:\\Users\\Aluno\\Desktop\\banco.mdb";
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con= DriverManager.getConnection("jdbc:odbc:Driver=" +
"{Microsoft Access Driver (*.mdb)};" +
"DBQ=" + Hd ,"","");
            statement = con.createStatement();
            String s = null;
            if(descricao !=null || !descricao.equals(""))
                s= "select p.codigo,p.nome,endereco,e.nome from pessoa p,estado_civil e where p.estado_civil = e.codigo and p.nome like '%" + descricao + "%'";
            else
                s= "select p.codigo,p.nome,endereco,e.nome from pessoa p,estado_civil e where p.estado_civil = e.codigo";
            s = s + " and estado_civil =" + new Integer(estado).intValue();
            rs = statement.executeQuery(s);
            ResultSetTableModel rst = new ResultSetTableModel("sun.jdbc.odbc.JdbcOdbcDriver",
                    "jdbc:odbc:Driver=" +
"{Microsoft Access Driver (*.mdb)}; DBQ=" + Hd, "", "",
                   s);
            dados = new String[rst.getRowCount()][4];
            int aux = 0;
            while (rs.next()) {
                dados[aux][0] = rs.getString(1);
                dados[aux][1] = rs.getString(2);
                dados[aux][2] = rs.getString(3);
                dados[aux][3] = rs.getString(4);
                aux++;
            }
        } catch (Exception ex) {
            Logger.getLogger(Controle.class.getName()).log(Level.SEVERE, null, ex);
        }
        return dados;
    }


public String[][] procurarProduto(String descricao) {
        rs = null;
        String[][] dados = null;
        String Hd = "C:\\Users\\Aluno\\Desktop\\banco.mdb";
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con= DriverManager.getConnection("jdbc:odbc:Driver=" +
"{Microsoft Access Driver (*.mdb)};" +
"DBQ=" + Hd ,"","");
            statement = con.createStatement();
            String s = null;
            if(descricao !=null || !descricao.equals(""))
                s= "select * from Produto where nome like '%" + descricao + "%'";
            else
                s = "select * from Produto";
            rs = statement.executeQuery(s);
            ResultSetTableModel rst = new ResultSetTableModel("sun.jdbc.odbc.JdbcOdbcDriver",
                    "jdbc:odbc:Driver=" +
"{Microsoft Access Driver (*.mdb)}; DBQ=" + Hd, "", "",
                   s);
            dados = new String[rst.getRowCount()][5];
            int aux = 0;
            while (rs.next()) {
                dados[aux][0] = rs.getString(1);
                dados[aux][1] = rs.getString(2);
                dados[aux][2] = rs.getString(3);
                dados[aux][3] = rs.getString(4);
                dados[aux][4] = rs.getString(5);
                aux++;
            }
        } catch (Exception ex) {
            Logger.getLogger(Controle.class.getName()).log(Level.SEVERE, null, ex);
        }
        return dados;
    }

ResultSet recuperaEstado(){
    String Hd = "C:\\Users\\Aluno\\Desktop\\banco.mdb";
    try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con= DriverManager.getConnection("jdbc:odbc:Driver=" +
"{Microsoft Access Driver (*.mdb)};" +
"DBQ=" + Hd ,"","");
            statement = con.createStatement();
            String s = null;
            s = "select * from estado_civil";
            rs = statement.executeQuery(s);
    }catch(Exception e){
        e.printStackTrace();
    }
    return rs;
    }


 public String[][] procurarFuncionario(String descricao,String funcao, String setor) {
        rs = null;
        String[][] dados = null;
        String Hd = "C:\\Users\\Aluno\\Desktop\\banco.mdb";
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con= DriverManager.getConnection("jdbc:odbc:Driver=" +
"{Microsoft Access Driver (*.mdb)};" +
"DBQ=" + Hd ,"","");
            statement = con.createStatement();
            String s = null;
            if(descricao !=null || !descricao.equals(""))
                s= "select p.codigo,p.nome,status,e.nome from funcionario p,funcao e, setor s where s.codigo = p.setor and  p.funcao = e.codigo and p.nome like '%" + descricao + "%'";
            else
                s= "select p.codigo,p.nome,status,e.nome from funcionario p,funcao e where s.codigo = p.setor and p.funcao = e.codigo";
            s = s + " and funcao =" + new Integer(funcao).intValue();
            s = s + " and setor =" + new Integer(setor).intValue();
            rs = statement.executeQuery(s);
            ResultSetTableModel rst = new ResultSetTableModel("sun.jdbc.odbc.JdbcOdbcDriver",
                    "jdbc:odbc:Driver=" +
"{Microsoft Access Driver (*.mdb)}; DBQ=" + Hd, "", "",
                   s);
            dados = new String[rst.getRowCount()][5];
            int aux = 0;
            while (rs.next()) {
                dados[aux][0] = rs.getString(1);
                dados[aux][1] = rs.getString(2);
                dados[aux][2] = rs.getString(3);
                dados[aux][3] = rs.getString(4);
                dados[aux][4] = rs.getString(5);
                aux++;
            }
        } catch (Exception ex) {
            Logger.getLogger(Controle.class.getName()).log(Level.SEVERE, null, ex);
        }
        return dados;
    }

 ResultSet recuperaFuncao(){
    String Hd = "C:\\Users\\Aluno\\Desktop\\banco.mdb";
    try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con= DriverManager.getConnection("jdbc:odbc:Driver=" +
"{Microsoft Access Driver (*.mdb)};" +
"DBQ=" + Hd ,"","");
            statement = con.createStatement();
            String s = null;
            s = "select * from funcao";
            rs = statement.executeQuery(s);
    }catch(Exception e){
        e.printStackTrace();
    }
    return rs;
    }

  ResultSet recuperaSetor(){
    String Hd = "C:\\Users\\Aluno\\Desktop\\banco.mdb";
    try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con= DriverManager.getConnection("jdbc:odbc:Driver=" +
"{Microsoft Access Driver (*.mdb)};" +
"DBQ=" + Hd ,"","");
            statement = con.createStatement();
            String s = null;
            s = "select * from setor";
            rs = statement.executeQuery(s);
    }catch(Exception e){
        e.printStackTrace();
    }
    return rs;
    }

    void excluirRegistro(int z) {
            String Hd = "C:\\Users\\Aluno\\Desktop\\banco.mdb";
    try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con= DriverManager.getConnection("jdbc:odbc:Driver=" +
"{Microsoft Access Driver (*.mdb)};" +
"DBQ=" + Hd ,"","");
            statement = con.createStatement();
            String s = null;
            s = "delete from pessoa where codigo = " + z;
            statement.executeUpdate(s);
            JOptionPane.showMessageDialog(null, "Registro excluído com sucesso");
    }catch(Exception e){
        e.printStackTrace();
    }
    }
}