public static int getLastInsertId(String table) {
int id = -1;
try {
PreparedStatement st =
SQL.getConnection()
.prepareStatement(
"SELECT DISTINCT LAST_INSERT_ID() AS last_insert_id FROM " + table + ";");
ResultSet result = st.executeQuery();
result.next();
id = result.getInt("last_insert_id");
result.close();
st.close();
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
}
return id;
}
@Override
public void execute(DelegateExecution exec) throws Exception {
long docID = (long) exec.getVariable("doc_id");
Connection con = SQL.getConnection();
PreparedStatement ps1 = con.prepareStatement("SELECT * FROM `o_lines` WHERE doc_id = ?");
ps1.setLong(1, docID);
ResultSet rs1 = ps1.executeQuery();
PreparedStatement ps2 =
con.prepareStatement("UPDATE `product_sale` SET reserved = reserved + ? WHERE p_id = ?");
while (rs1.next()) {
ps2.setInt(1, rs1.getInt("quantity"));
ps2.setLong(2, rs1.getLong("p_id"));
ps2.execute();
}
ps2.close();
rs1.close();
ps1.close();
PreparedStatement ps =
con.prepareStatement("UPDATE `order` SET waitingForProducts = 1 WHERE doc_id = ?");
ps.setLong(1, docID);
ps.execute();
ps.close();
ps =
con.prepareStatement(
"SELECT * FROM `product_sale` WHERE stock + ordered < minimumStock + reserved");
ResultSet rs = ps.executeQuery();
exec.setVariable("belowMinimum", rs.next());
rs.close();
ps.close();
}