package Factory;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class FuncDAO_DB implements DAOFuncionario {

    Connection conn; 
    
    public FuncDAO_DB() {
        try {
            SimpleDataSource.init("bd.properties");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = SimpleDataSource.getConnection();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }

    public Funcionario createValueObject() {
        return new Funcionario();
    }

    public void update(Funcionario valueObject) {
    	
    	update(conn, valueObject);
    }

    public void update(Connection conn, Funcionario valueObject) throws Exception, SQLException {
        String sql = "UPDATE Funcionario SET nome = ?, profissao = ?, data = ? WHERE (codigo = ? ) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, valueObject.getNome());
            stmt.setString(2, valueObject.getProfissao());
            stmt.setString(3, valueObject.getData());

            stmt.setInt(4, valueObject.getCodigo());

            int rowcount = stmt.executeUpdate();
            if (rowcount == 0) {
                // System.out.println("Object could not be saved! (PrimaryKey
                // not found)");
                throw new Exception(
                        "Object could not be saved! (PrimaryKey not found)");
            }
            if (rowcount > 1) {
                // System.out.println("PrimaryKey Error when updating DB! (Many
                // objects were affected!)");
                throw new SQLException(
                        "PrimaryKey Error when updating DB! (Many objects were affected!)");
            }
        } finally {
            if (stmt != null)
                stmt.close();
        }
    }

    public void insert(Funcionario valueObject) throws Exception, SQLException {
        String sql = "";
        PreparedStatement stmt = null;
        ResultSet result = null;

        try {
             sql = "INSERT INTO Funcionario ( codigo, nome, profissao, "
             + "data) VALUES (?, ?, ?, ?) ";
             stmt = conn.prepareStatement(sql);

             stmt.setInt(1, valueObject.getCodigo()); 
             stmt.setString(2, valueObject.getNome()); 
             stmt.setString(3, valueObject.getProfissao()); 
             stmt.setString(4, valueObject.getData()); 

             int rowcount = stmt.executeUpdate();
             if (rowcount != 1) {
                  //System.out.println("PrimaryKey Error when updating DB!");
                  throw new SQLException("PrimaryKey Error when updating DB!");
             }

        } finally {
            if (stmt != null)
                stmt.close();
        }
    }

    public void delete(Funcionario valueObject) throws Exception, SQLException {
        String sql = "DELETE FROM Funcionario WHERE (codigo = ? ) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, valueObject.getCodigo());

            int rowcount = stmt.executeUpdate();
            if (rowcount == 0) {
                // System.out.println("Object could not be deleted (PrimaryKey
                // not found)");
                throw new Exception(
                        "Object could not be deleted! (PrimaryKey not found)");
            }
            if (rowcount > 1) {
                // System.out.println("PrimaryKey Error when updating DB! (Many
                // objects were deleted!)");
                throw new SQLException(
                        "PrimaryKey Error when updating DB! (Many objects were deleted!)");
            }
        } finally {
            if (stmt != null)
                stmt.close();
        }
    }

    public List<Funcionario> searchMatching(Funcionario valueObject) throws SQLException {
System.out.println("go /11");
        List searchResults;

        boolean first = true;
        StringBuffer sql = new StringBuffer(
                "SELECT * FROM Funcionario WHERE 1=1 ");
System.out.println("1");
        if (valueObject.getCodigo() != 0) {
            if (first) {
                first = false;
            }
            sql.append("AND codigo = ").append(valueObject.getCodigo()).append(
                    " ");
        }
System.out.println("2");
        if (valueObject.getNome() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND nome LIKE '").append(valueObject.getNome()).append(
                    "%' ");
        }

        if (valueObject.getProfissao() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND profissao LIKE '").append(
                    valueObject.getProfissao()).append("%' ");
        }

        if (valueObject.getData() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND data LIKE '").append(valueObject.getData()).append(
                    "%' ");
        }

        sql.append("ORDER BY codigo ASC ");
System.out.println(sql.toString());
        // Prevent accidential full table results.
        // Use loadAll if all rows must be returned.
        if (first)
            searchResults = new ArrayList();
        else
            searchResults = listQuery(conn.prepareStatement(sql.toString()));

        return searchResults;
    }

    protected List listQuery(PreparedStatement stmt) throws SQLException {

        ArrayList searchResults = new ArrayList();
        ResultSet result = null;

        try {
            result = stmt.executeQuery();

            while (result.next()) {
                Funcionario temp = createValueObject();

                temp.setCodigo(result.getInt("codigo"));
                temp.setNome(result.getString("nome"));
                temp.setProfissao(result.getString("profissao"));
                temp.setData(result.getString("data"));
                searchResults.add(temp);
            }

        } finally {
            if (result != null)
                result.close();
            if (stmt != null)
                stmt.close();
        }

        return (List) searchResults;
    }
}
