protected void insertResults(Logger l, Statement s, int run) {
ResultSet rs = null;
String sql = null;
try {
sql = getQuery(run);
rs = s.executeQuery(sql);
} catch (SQLException sqle) {
l.logMessage(
Logger.ERROR,
Logger.DB_WRITE,
"ManifestInfo.insertResults - Problem executing query : " + sql,
sqle);
}
try {
if (rs != null) {
double actual = 0;
double reserved = 0;
while (rs.next()) {
int wasReserved = rs.getInt(3);
double tons = rs.getDouble(4);
boolean isReserved = (wasReserved == 1);
insertRow(
l,
s,
run,
rs.getString(1),
rs.getString(2),
isReserved ? "reserved" : "actual",
tons);
if (isReserved) reserved += tons;
else actual += tons;
}
insertRow(l, s, run, "All Dodics", "All Kinds - total tons", "reserved", reserved);
insertRow(l, s, run, "All Dodics", "All Kinds - total tons", "actual", actual);
insertRow(l, s, run, "All Dodics", "All Kinds - total tons", "both", reserved + actual);
}
} catch (SQLException sqle) {
l.logMessage(
Logger.ERROR,
Logger.DB_WRITE,
"ManifestInfo.insertResults - Problem walking results.",
sqle);
}
}
private void insertRow(
Logger l,
Statement s,
int run,
String type,
String nomen,
String reservedLabel,
double weight)
throws SQLException {
String sql = null;
try {
StringBuffer sb = new StringBuffer();
sb.append("INSERT INTO ");
sb.append(getTableName(run));
sb.append(" (");
sb.append(DGPSPConstants.COL_ALP_TYPEID);
sb.append(",");
sb.append(DGPSPConstants.COL_ALP_NOMENCLATURE);
sb.append(",");
sb.append(COL_RESERVED);
sb.append(",");
sb.append(DGPSPConstants.COL_WEIGHT);
sb.append(")\nVALUES(");
sb.append("'");
sb.append(type);
sb.append("'");
sb.append(",");
sb.append("'");
sb.append(nomen);
sb.append("'");
sb.append(",");
sb.append("'");
sb.append(reservedLabel);
sb.append("'");
sb.append(",");
// weight is in grams, we want short tons = 2000 pounds
sb.append(
dbConfig.getDBDouble((weight / 1000000.0d) * CargoDimensionTest.METRIC_TO_SHORT_TON));
sb.append(")");
sql = sb.toString();
s.executeUpdate(sql);
} catch (SQLException sqle) {
l.logMessage(
Logger.ERROR,
Logger.DB_WRITE,
"ManifestInfo.insertRow - Problem inserting rows in table. Sql was " + sql,
sqle);
}
}