public String[][] getItemIdForHybrid(int menuid) {
DBConnect gettg = new DBConnect();
PreparedStatement stmtget;
ResultSet rs;
// String search = menuid+"%";
String strget =
"SELECT department_item_id,quantity*item_unit.unit_relative_quantity as total_qty from hybrid_menu INNER JOIN item_unit ON hybrid_menu.unit_id = item_unit.unit_id where parent_menu_id in (?)";
// String[] columnName = new String[]{"Menu Id","Menu Name"," Retail Rate","Wholesale
// Rate","Base Unit",};
ArrayList<String[]> data = new ArrayList<String[]>();
String[][] finaldata = null;
try {
gettg.initConnection();
stmtget = gettg.conn.prepareStatement(strget);
stmtget.setInt(1, menuid);
rs = stmtget.executeQuery();
while (rs.next()) {
String[] row = new String[] {rs.getString("department_item_id"), rs.getString("total_qty")};
data.add(row);
}
finaldata = data.toArray(new String[data.size()][]);
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e + "from getmenuinfolike");
} finally {
gettg.closeConnection();
}
return finaldata;
}
// retreiving menuname in respective department
public Object[][] getMenuInfo(int departmentid) {
PreparedStatement stmtget;
Object[][] MenuInfo = null;
ResultSet rsget;
ArrayList<Object[]> data = new ArrayList<Object[]>();
String strgetMenu =
"SELECT menu.menu_id,menu.menu_name,item_unit.unit_name,menu.retail_price FROM menu LEFT JOIN item_unit ON menu.unit_id = item_unit.unit_id WHERE department_id = ?";
DBConnect getMenu = new DBConnect();
try {
getMenu.initConnection();
stmtget = getMenu.conn.prepareStatement(strgetMenu);
stmtget.setInt(1, departmentid);
rsget = stmtget.executeQuery();
while (rsget.next()) {
Object[] row =
new Object[] {
rsget.getString("menu_id"),
rsget.getString("menu_name"),
rsget.getString("unit_name"),
rsget.getDouble("retail_price")
};
data.add(row);
}
MenuInfo = data.toArray(new Object[data.size()][]);
} catch (SQLException se) {
JOptionPane.showMessageDialog(null, se + "from getMenuInfo");
} finally {
getMenu.closeConnection();
}
return MenuInfo;
}
public BigDecimal getHybridItemAvailable(int menuid) {
PreparedStatement stmtget;
ResultSet rsget;
DBConnect dbget = new DBConnect();
BigDecimal Avaiable = BigDecimal.ZERO;
ArrayList<Object[]> data = new ArrayList<Object[]>();
ArrayList<BigDecimal> ItemStockData = new ArrayList<>();
BigDecimal MenuQuantity = BigDecimal.ZERO;
// String ColName[] = new String[]{"ItemName","Stock Available"};
String strget =
"SELECT centerstore_stock.item_name,(department_store_stock.total_qty/(item_unit.unit_relative_quantity)) as total_qty,item_unit.unit_name,hybrid_menu.quantity as hybridquantity,menu.quantity as menuquantity from hybrid_menu INNER JOIN department_store_stock ON hybrid_menu.department_item_id= department_store_stock.department_item_id INNER JOIN item_unit ON hybrid_menu.unit_id = item_unit.unit_id INNER JOIN centerstore_stock ON department_store_stock.item_id = centerstore_stock.item_id INNER JOIN menu ON hybrid_menu.parent_menu_id = menu.menu_id where parent_menu_id in (?)";
try {
dbget.initConnection();
stmtget = dbget.conn.prepareStatement(strget);
stmtget.setInt(1, menuid);
rsget = stmtget.executeQuery();
while (rsget.next()) {
BigDecimal TotalItem =
rsget
.getBigDecimal("total_qty")
.divide(rsget.getBigDecimal("hybridquantity"), 3, RoundingMode.HALF_UP);
ItemStockData.add(TotalItem);
// System.out.println(TotalItem);
MenuQuantity = rsget.getBigDecimal("menuquantity");
Object[] row =
new Object[] {
rsget.getString("item_name"),
rsget.getBigDecimal("total_qty") + rsget.getString("unit_name")
};
data.add(row);
}
// MenuInfo = data.toArray(new Object[data.size()][]);
} catch (SQLException se) {
JOptionPane.showMessageDialog(
null, se + "from getHybridItemAvailable " + getClass().getName());
} finally {
dbget.closeConnection();
}
Collections.sort(ItemStockData);
// System.out.println(ItemStockData.get(0));
MenuQuantity = ItemStockData.get(0).divide(MenuQuantity, 3, RoundingMode.HALF_UP);
return MenuQuantity;
}
public boolean checkHybrid(int menuid) {
// Boolean ExistingStatus = null;
Boolean id = false;
// String oid = tablename;
String strCheck = "SELECT hybrid_type FROM menu WHERE menu_id = ?";
DBConnect check = new DBConnect();
PreparedStatement stmtcheck;
try {
check.initConnection();
stmtcheck = check.conn.prepareStatement(strCheck);
stmtcheck.setInt(1, menuid);
ResultSet rs = stmtcheck.executeQuery();
while (rs.next()) {
id = rs.getBoolean("hybrid_type");
}
} catch (SQLException se) {
JOptionPane.showMessageDialog(null, se + "from checkHybrid");
} finally {
check.closeConnection();
}
return id;
}
// retreining staff info
public Object[][] getWaiterInfoObject() {
PreparedStatement stmtget;
ResultSet rsget;
DBConnect dbget = new DBConnect();
ArrayList<Object[]> data = new ArrayList<Object[]>();
Object[][] WaiterInfo = null;
String strget = "SELECT waiter_id,waiter_name FROM waiter_info ";
try {
dbget.initConnection();
stmtget = dbget.conn.prepareStatement(strget);
rsget = stmtget.executeQuery();
while (rsget.next()) {
Object[] row = new Object[] {rsget.getString("waiter_id"), rsget.getString("waiter_name")};
data.add(row);
}
WaiterInfo = data.toArray(new Object[data.size()][]);
} catch (SQLException se) {
JOptionPane.showMessageDialog(null, se + "from getWaiterInfoObject");
} finally {
dbget.closeConnection();
}
return WaiterInfo;
}
