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 void executeQuery() throws SQLException {
log(
"select account, branch, balance, filler from "
+ m_Driver.getAccountName()
+ " where account < 101\n",
2);
Statement stmt = null;
try {
stmt = m_conn.createStatement();
ResultSet set =
stmt.executeQuery(
"select account, branch, balance, filler from "
+ m_Driver.getAccountName()
+ " where account < 101");
while (set.next()) {
set.getInt(1);
set.getInt(2);
set.getDouble(3);
set.getString(4);
}
set.close();
} finally {
if (stmt != null) stmt.close();
stmt = null;
}
}
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
}
/**
* _more_
*
* @return _more_
* @throws Exception _more_
*/
private boolean initConnection() throws Exception {
if (getConnection() == null) {
return false;
}
// jdbc:postgresql://eol-rt-data.guest.ucar.edu/real-time
// evaluate("CREATE RULE update AS ON UPDATE TO global_attributes DO NOTIFY current;");
Statement stmt;
ResultSet results;
SqlUtil.Iterator iter;
EolDbTrackInfo trackInfo = new EolDbTrackInfo(this, "TRACK");
Hashtable cats = new Hashtable();
try {
stmt = select("*", TABLE_CATEGORIES);
iter = SqlUtil.getIterator(stmt);
while ((results = iter.getNext()) != null) {
cats.put(results.getString(COL_VARIABLE), results.getString(COL_CATEGORY));
}
} catch (Exception exc) {
// exc.printStackTrace();
}
missingMap = new Hashtable();
stmt = select("*", TABLE_GLOBALS);
globals = new Hashtable();
boolean gotCoords = false;
description = "<b>Globals</b><br>";
iter = SqlUtil.getIterator(stmt);
while ((results = iter.getNext()) != null) {
String globalName = results.getString(1).trim();
String globalValue = results.getString(2).trim();
globals.put(globalName, globalValue);
description =
description
+ "<tr valign=\"top\"><td>"
+ globalName
+ "</td><td>"
+ globalValue
+ "</td></tr>";
// System.err.println(globalName +"=" + globalValue);
if (globalName.equals(GLOBAL_STARTTIME)) {
startTime = new DateTime(DateUtil.parse(globalValue));
} else if (globalName.equals(GLOBAL_ENDTIME)) {
endTime = new DateTime(DateUtil.parse(globalValue));
} else if (globalName.equals(GLOBAL_COORDINATES)) {
List toks = StringUtil.split(globalValue, " ", true, true);
if (toks.size() != 4) {
throw new BadDataException("Incorrect coordinates value in database:" + globalValue);
}
gotCoords = true;
System.err.println("coords:" + toks);
trackInfo.setCoordinateVars(
(String) toks.get(0), (String) toks.get(1), (String) toks.get(2), (String) toks.get(3));
trackInfo.setCoordinateVars("GGLON", "GGLAT", "GGALT", "datetime");
}
}
description = description + "</table>";
if (!gotCoords) {
throw new BadDataException("No coordinates found in database");
}
this.name = (String) globals.get(GLOBAL_PROJECTNAME);
String flight = (String) globals.get(GLOBAL_FLIGHTNUMBER);
if ((this.name != null) && (flight != null) && (flight.length() != 0)) {
this.name += " - " + flight;
}
stmt = select("*", TABLE_VARIABLE_LIST);
iter = SqlUtil.getIterator(stmt);
while ((results = iter.getNext()) != null) {
String name = results.getString(COL_NAME).trim();
String desc = results.getString(COL_LONG_NAME).trim();
Unit unit = DataUtil.parseUnit(results.getString(COL_UNITS).trim());
String cat = (String) cats.get(name);
double missing = results.getDouble(COL_MISSING_VALUE);
VarInfo variable = new VarInfo(name, desc, cat, unit, missing);
trackInfo.addVariable(variable);
}
addTrackInfo(trackInfo);
return true;
}
void runTextTest() {
Statement stmt = null;
try {
// System.out.println("Thread :" + getName() + " Text test entered");
setProgressMinMax(0, m_nNumRuns - 1);
int nProgressStep = m_nNumRuns / 10 + 1;
// System.out.println("Thread :" + getName() + " before commit state");
boolean m_bTransactionsUsed = m_bTrans && m_conn.getMetaData().supportsTransactions();
// System.out.println("Thread :" + getName() + " are transactions used");
boolean bCurrentAutoCommit = m_conn.getAutoCommit();
// System.out.println("Thread :" + getName() + "got currentCommit");
m_conn.setAutoCommit(!m_bTransactionsUsed);
// System.out.println("Thread :" + getName() + " commit state set");
stmt = m_conn.createStatement();
int nAccNum, nBranchNum, nTellerNum;
double dDelta, dBalance;
log(
"Starting SQL text benchmark for " + m_nNumRuns + ((m_time > 0) ? " min.\n" : " runs\n"),
0);
java.util.Random rand = new java.util.Random(m_nMaxAccount + m_nMaxBranch + m_nMaxTeller);
for (int nRun = 0; (m_time > 0) ? true : nRun < m_nNumRuns; nRun++) {
if (m_time > 0) {
java.util.Date current = new java.util.Date();
if ((current.getTime() - m_time) > (m_nNumRuns * 60000)) break;
}
try {
nAccNum = (int) (rand.nextFloat() * rand.nextFloat() * (m_nMaxAccount - 1)) + 1;
nBranchNum = (int) (rand.nextFloat() * (m_nMaxBranch - 1)) + 1;
nTellerNum = (int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1;
dDelta =
((double)
((long)
((((int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1)
* (rand.nextFloat() > 0.5 ? -1 : 1))
* 100)))
/ 100;
java.util.Date startTime = new java.util.Date();
log(
"UPDATE "
+ m_Driver.getAccountName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE account = "
+ nAccNum
+ "\n",
2);
stmt.executeUpdate(
"UPDATE "
+ m_Driver.getAccountName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE account = "
+ nAccNum);
log(
"SELECT balance FROM "
+ m_Driver.getAccountName()
+ " WHERE account = "
+ nAccNum
+ "\n",
2);
ResultSet balanceSet =
stmt.executeQuery(
"SELECT balance FROM "
+ m_Driver.getAccountName()
+ " WHERE account = "
+ nAccNum);
balanceSet.next();
dBalance = balanceSet.getDouble(1);
balanceSet.close();
log(
"UPDATE "
+ m_Driver.getTellerName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE teller = "
+ nTellerNum
+ "\n",
2);
stmt.executeUpdate(
"UPDATE "
+ m_Driver.getTellerName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE teller = "
+ nTellerNum);
log(
"UPDATE "
+ m_Driver.getBranchName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE branch = "
+ nBranchNum
+ "\n",
2);
stmt.executeUpdate(
"UPDATE "
+ m_Driver.getBranchName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE branch = "
+ nBranchNum);
log(
"INSERT INTO "
+ m_Driver.getHistoryName()
+ " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES ("
+ nRun
+ " , "
+ nAccNum
+ " , "
+ nTellerNum
+ " , "
+ nBranchNum
+ " , "
+ dDelta
+ " , "
+ m_nowFunction
+ " , \'"
+ BenchPanel.strFiller.substring(0, 21)
+ "\')\n",
2);
stmt.executeUpdate(
"INSERT INTO "
+ m_Driver.getHistoryName()
+ " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES ("
+ nRun
+ " , "
+ nAccNum
+ " , "
+ nTellerNum
+ " , "
+ nBranchNum
+ " , "
+ dDelta
+ " , "
+ m_nowFunction
+ " , \'"
+ BenchPanel.strFiller.substring(0, 21)
+ "\')");
if (m_bQuery) executeQuery();
// System.out.println("Done query");
if (m_bTransactionsUsed) m_conn.commit();
java.util.Date endTime = new java.util.Date();
// System.out.println("Done");
m_nTrans += 1;
long diff = endTime.getTime() - startTime.getTime();
if (diff < 1000) m_nTrans1Sec += 1;
else if (diff < 2000) m_nTrans2Sec += 1;
m_nTimeSum += ((double) diff) / 1000;
} catch (SQLException e1) {
// System.err.println(e1.getMessage());
// e1.printStackTrace();
break;
}
if (nRun % nProgressStep == 0) setProgressValue(nRun);
// yield();
}
setProgressValue(m_nNumRuns - 1);
m_conn.setAutoCommit(bCurrentAutoCommit);
} catch (SQLException e) {
// e.printStackTrace();
// JOptionPane.showMessageDialog(null, e.getMessage(), "SQL Error in Text test",
// JOptionPane.ERROR_MESSAGE);
log("SQLError in text test : " + e.getMessage(), 0);
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException e) {
}
}
}
