public void excluirLote(List<JobLote> listaExcluir) {
Connection conn = null;
PreparedStatement stmt = null;
String sql = "delete from joblote where job = ? and operacao = ? and lote =? ";
try {
conn = GerenciaConexaoSQLServer.abreConexao();
conn.setAutoCommit(false);
for (int i = 0; i <= listaExcluir.size() - 1; i++) {
JobLote job = listaExcluir.get(i);
stmt = conn.prepareStatement(sql);
stmt.setString(1, job.getJob().trim().replace(".", ""));
stmt.setInt(2, job.getOperNum());
stmt.setInt(3, job.getLote());
stmt.executeUpdate();
alteraJobAposExclusao(conn, job);
gravaJobsExcluidos(
conn, job.getJob().trim().replace(".", ""), job.getOperNum(), job.getLote());
}
// adiciona a op na tela inicial
for (int i = 0; i <= listaExcluir.size() - 1; i++) {
JobLote job = listaExcluir.get(i);
JobProtheus jobProtheus = retornaJob(conn, job);
adicionaOPTelaInicial(conn, jobProtheus, job);
}
JOptionPane.showMessageDialog(this, "Job Excluido com Sucesso!");
conn.commit();
} catch (SQLException e) {
if (conn != null) {
try {
conn.rollback();
conn.setAutoCommit(true);
JOptionPane.showMessageDialog(null, "Não foi possivel excluir! Descrição: " + e);
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null, "Erro ao fazer o rollback! Descrição: " + ex);
}
}
} finally {
GerenciaConexaoSQLServer.closeConexao(conn, stmt);
}
}
public void gravaJobsExcluidos(Connection conn, String job, int operacao, int lote)
throws SQLException {
PreparedStatement stmt = null;
String insert = "insert into joblotesexcluidos (job, operacao, lote) values (?,?,?)";
stmt = conn.prepareStatement(insert);
stmt.setString(1, job.trim());
stmt.setInt(2, operacao);
stmt.setInt(3, lote);
stmt.executeUpdate();
}
public ValoresAtualizaJob retornaNovosValores(Connection conn, JobLote job) throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
ValoresAtualizaJob obj = null;
String sql =
" select sum(qtd_transf) qtd_transf, sum(qtd_sucata) qtd_sucata, sum(hr_tot) hr_tot from joblote\n"
+ " where job = ? and operacao = ? ";
stmt = conn.prepareStatement(sql);
stmt.setString(1, job.getJob().trim().replace(".", ""));
stmt.setInt(2, job.getOperNum());
rs = stmt.executeQuery();
if (rs.next()) {
obj = new ValoresAtualizaJob();
obj.setQtdTransf(rs.getDouble("qtd_transf"));
obj.setQtdSucata(rs.getDouble("qtd_sucata"));
obj.setHoraTotal(rs.getDouble("hr_tot"));
}
return obj;
}
public JobIniciado retornaUltimoLote(Connection conn, String job, int operacao)
throws SQLException {
String sql =
" select job, operacao, tripulacao,recurso, max(data_fim) \n"
+ "from joblote where job = ? and operacao = ? \n"
+ "group by job, operacao, tripulacao, recurso ";
PreparedStatement stmt = null;
ResultSet rs = null;
JobIniciado iniciado = null;
stmt = conn.prepareStatement(sql);
stmt.setString(1, job.trim().replace(".", ""));
stmt.setInt(2, operacao);
rs = stmt.executeQuery();
if (rs.next()) {
iniciado = new JobIniciado();
iniciado.setJob(rs.getString(1));
iniciado.setOperacao(rs.getInt(2));
iniciado.setTripulacao(rs.getDouble(3));
iniciado.setRecurso(rs.getString(4));
Timestamp data = rs.getTimestamp(5);
iniciado.setData(Validacoes.getDataHoraString(data));
}
return iniciado;
}
public static int retornaQtdTotal(Connection conn, JobProtheus job) throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
int qtdTotal = 0;
int qtdSucata = 0;
String sqlVerQtd =
"select job, sum(qtd_transf), sum(hr_tot), sum(qtd_sucata) from jobLote where job = ? and operacao = ? group by job";
stmt = conn.prepareStatement(sqlVerQtd);
stmt.setString(1, job.getJob().trim());
stmt.setInt(2, job.getOperacao());
rs = stmt.executeQuery();
String qtdJob = "";
double totHora = 0;
while (rs.next()) {
qtdJob = rs.getString(1); // numero job
qtdTotal = rs.getInt(2); // quantidade total
totHora = rs.getInt(3); // hora total em minutos
qtdSucata = rs.getInt(4);
}
return qtdTotal + qtdSucata;
}
public void desativaIniciado(Connection conn, String op, int operacao) throws SQLException {
String sql = "update jobsiniciados set ativo = 0 where job = ? and operacao = ? ";
PreparedStatement stmt = null;
stmt = conn.prepareStatement(sql);
stmt.setString(1, op.trim().replace(".", ""));
stmt.setInt(2, operacao);
stmt.executeUpdate();
}
public JobProtheus retornaJob(Connection conn, JobLote lote) throws SQLException {
ResultSet rs = null;
PreparedStatement stmt = null;
JobProtheus job = null;
String sql = "select job , B1_DESC, operacao , produto, ";
sql += " dt_release, job_start_date , qtd_release, setor, qtd_transf ";
sql += " from job left join " + DB_PROTHEUS.trim() + ".dbo.SB1000 SB1 ";
sql += " on(produto collate SQL_Latin1_General_CP1_CI_AS = B1_COD) ";
sql += " where job = ? and operacao = ? and SB1.D_E_L_E_T_ = '' ";
sql += " order by dt_release, produto ";
stmt = conn.prepareStatement(sql);
stmt.setString(1, lote.getJob().trim());
stmt.setInt(2, lote.getOperNum());
rs = stmt.executeQuery();
while (rs.next()) {
String nJob = rs.getString("job");
int operacao = rs.getInt("operacao");
String produto = rs.getString("produto");
Date dataEmissao = rs.getDate("dt_release");
Date dataPrevisaoInicio = rs.getDate("job_start_date");
String descricaoProduto = rs.getString("B1_DESC");
double quantidade = rs.getDouble("qtd_release");
String wc = rs.getString("setor");
String emissao = dataEmissao != null ? getDateToString(dataEmissao) : "";
String previsaoInicio = dataPrevisaoInicio != null ? getDateToString(dataPrevisaoInicio) : "";
job = new JobProtheus();
job.setStatus("");
job.setJob(nJob);
job.setOperacao(operacao);
job.setProduto(produto.trim());
job.setDataEmissao(emissao);
job.setQuantidadeLiberada(quantidade);
job.setDescricaoProduto(descricaoProduto);
job.setCentroTrabalho(wc);
double qtdTotal = retornaQtdTotal(conn, job); // calcula o total transferido para o job
job.setQuantidadeCompleta(qtdTotal);
job.setDataPrivisaoInicio(previsaoInicio);
job.setQuantidadeFaltando(quantidade - qtdTotal); // seta o valor qtd faltando
}
return job; // retorna lista de jobs
}
public void incluiIniciado(Connection conn, JobIniciado job) throws SQLException {
String sql =
"insert into jobsiniciados(job, operacao, data, tripulacao, ativo, recurso) values(?,?,?,?,?,?) ";
PreparedStatement stmt = null;
stmt = conn.prepareStatement(sql);
stmt.setString(1, job.getJob().trim().replace(".", ""));
stmt.setInt(2, job.getOperacao());
stmt.setTimestamp(3, Validacoes.converteDataStringEmFormatoTimeStamp(job.getData().trim()));
stmt.setDouble(4, job.getTripulacao());
stmt.setBoolean(5, true);
stmt.setString(6, job.getRecurso().trim());
stmt.executeUpdate();
}
public void alteraJobAposExclusao(Connection conn, JobLote job) throws SQLException {
ValoresAtualizaJob valores = retornaNovosValores(conn, job);
PreparedStatement stmt = null;
String update =
"update job set qtd_transf = ?, hr_tot = ?, qtd_sucata = ?, status = ? where job = ? and operacao = ? ";
stmt = conn.prepareStatement(update);
stmt.setDouble(1, valores.getQtdTransf());
stmt.setDouble(2, valores.getHoraTotal());
stmt.setDouble(3, valores.getQtdSucata());
stmt.setString(4, "R");
stmt.setString(5, job.getJob().trim().replace(".", ""));
stmt.setInt(6, job.getOperNum());
stmt.executeUpdate();
}
public void alteraIniciado(Connection conn, JobIniciado job) throws SQLException {
String sql =
" update jobsiniciados set data = ?, ativo = ?, tripulacao = ?, recurso = ? "
+ " where job = ? and operacao = ? ";
PreparedStatement stmt = null;
stmt = conn.prepareStatement(sql);
stmt.setTimestamp(1, Validacoes.converteDataStringEmFormatoTimeStamp(job.getData().trim()));
stmt.setBoolean(2, true);
stmt.setDouble(3, job.getTripulacao());
stmt.setString(4, job.getRecurso().trim());
stmt.setString(5, job.getJob().trim().replace(".", ""));
stmt.setInt(6, job.getOperacao());
stmt.executeUpdate();
}
public boolean verificarSeJobEstaTabelaIniciado(Connection conn, String job, int operacao)
throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
String update = "select ativo from jobsiniciados where job = ? and operacao = ?";
stmt = conn.prepareStatement(update);
stmt.setString(1, job.trim().replace(".", ""));
stmt.setInt(2, operacao);
rs = stmt.executeQuery();
if (rs.next()) {
return true;
}
return false;
}