/** set column names; */
public Stock_Check() {
Vector<String> columnNames = new Vector<String>();
columnNames.add("Ingredient_ID");
columnNames.add("Ingredient_Name");
columnNames.add("Ingredient_Stock");
/** this select query from database in ingredient */
String query = "SELECT ingrid, name, stock FROM ingredient";
/**
* connect to the Database using method in Database class. execute query and get the require
* query for the table to show.
*/
try {
conn = Database.staticGetConnection();
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
Vector<String> vstring = new Vector<String>();
vstring.add(rs.getString("ingrid"));
vstring.add(rs.getString("name"));
vstring.add(rs.getString("stock"));
data.add(vstring);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ex) {
Logger.getLogger(FlashBuilderGUI.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
/** this set up the model for table. such as size. */
DefaultTableModel model = new DefaultTableModel(data, columnNames);
final JTable table = new JTable(model);
table.setPreferredScrollableViewportSize(new Dimension(500, 70));
table.setFillsViewportHeight(true);
if (DEBUG) {
table.addMouseListener(
new MouseAdapter() {
public void mouseClicked(MouseEvent e) {
printDebugData(table);
}
});
}
/** Create a scroll pane and add table in it */
JScrollPane scrollPane = new JScrollPane(table);
scrollPane.setBounds(0, 0, 450, 300);
add(scrollPane);
}
/**
* Get the itemnames from orders where status = 4
*
* @param items the itemid's
* @return the itemnames
*/
static ArrayList<String> getItemName(ArrayList<Integer> items) throws Exception {
ArrayList<String> names = new ArrayList<String>();
Connection conn = Database.staticGetConnection();
String query = "SELECT itemname FROM menu WHERE itemid = ?";
PreparedStatement st = conn.prepareStatement(query);
for (int x = 0; x < items.size(); x++) {
st.setInt(1, items.get(x));
ResultSet rs = st.executeQuery();
rs.next();
names.add(rs.getString(1));
}
return names;
}
/**
* Get the orderid's of those with status = 4
*
* @return the orderid's
*/
static ArrayList<Integer> getOrderID() throws Exception {
ArrayList<Integer> orders = new ArrayList<Integer>();
Connection conn = Database.staticGetConnection();
String query = "SELECT orderid FROM orders WHERE status = 4";
PreparedStatement st = conn.prepareStatement(query);
ResultSet rs = st.executeQuery();
int x = 1;
while (rs.absolute(x)) {
orders.add(rs.getInt(1));
x++;
}
return orders;
}
/**
* Get the itemid's from the orders where status = 4
*
* @param orders the order numbers with status 4
* @return the itemid's
*/
static ArrayList<Integer> getItemID(ArrayList<Integer> orders) throws Exception {
ArrayList<Integer> items = new ArrayList<Integer>();
Connection conn = Database.staticGetConnection();
String query = "SELECT itemid FROM orderitems WHERE orderid = ?";
PreparedStatement st = conn.prepareStatement(query);
int x;
for (x = 0; x < orders.size(); x++) {
st.setInt(1, orders.get(x));
ResultSet rs = st.executeQuery();
rs.next();
items.add(rs.getInt(1));
}
return items;
}
/**
* Get the comments of the items in orders with status 4
*
* @param items the itemid's
* @return the comments
*/
static ArrayList<String> getComments(ArrayList<Integer> items, ArrayList<Integer> orders)
throws Exception {
ArrayList<String> comments = new ArrayList<String>();
// 255, 3; 256, 2
Connection conn = Database.staticGetConnection();
String query = "SELECT comments FROM orderitems WHERE orderid = ?";
PreparedStatement st = conn.prepareStatement(query);
int x;
for (x = 0; x < orders.size(); x++) {
st.setInt(1, orders.get(x));
ResultSet rs = st.executeQuery();
rs.next();
comments.add(rs.getString(1));
}
return comments;
}
public void setAllcustomer() throws Exception {
allcustomerid = new LinkedList<String>();
allpassword = new LinkedList<String>();
Connection conn = Database.staticGetConnection();
String query = "SELECT DISTINCT Username,Password FROM Accounts WHERE customer = 1";
PreparedStatement st = conn.prepareStatement(query);
ResultSet rs = st.executeQuery();
int x = 1;
while (rs.absolute(x)) {
allcustomerid.add(rs.getString("Username"));
allpassword.add(rs.getString("Password"));
x++;
}
conn.close();
}
/**
* @param id id of the item is passed as a parameter.
* @return returns true if item is available, otherwise false.
* @throws Exception
*/
public static final boolean checkAvailable(int id) throws Exception {
String itemtags = "SELECT * FROM menu where itemid = " + id;
Connection conn = Database.staticGetConnection();
PreparedStatement st = conn.prepareStatement(itemtags);
ResultSet result = st.executeQuery();
while (result.next()) {
int amount = result.getInt("amount");
int ingrID = result.getInt("ingredient_id");
String ing = "SELECT * from ingredient where ingrid =" + ingrID;
PreparedStatement stt = conn.prepareStatement(ing);
ResultSet resultTwo = stt.executeQuery();
while (resultTwo.next()) {
int stock = resultTwo.getInt("stock");
if (amount >= stock) {
return false;
}
}
}
return true;
}
/**
* @param query
* @return returns ResultSet of the query from database.
* @throws Exception
*/
private static ResultSet fetchInformation(String query) throws Exception {
Connection conn = Database.staticGetConnection();
PreparedStatement st = conn.prepareStatement(query);
ResultSet result = st.executeQuery();
return result;
}
