// 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 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;
}
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;
}
public void AddMenuWastage(Object[] wastageinfo, int userid, int departmentid) {
// ordermodel.AddMenuWastage(ordermodel.convertDefaultTableModelToObject(orderview.getTableOrderList()),orderview.getOrderId(),orderview.getTableId(),orderview.getWaiterId(),orderview.getCustomerId());
/*
wastage into
obj[0] = getMenuID();
obj[1] = getComboBoxMenuMenuName();
obj[2] = getMenuQuantity();
obj[3] = getMenuRate();
obj[4] = getMenuAmount();
obj[5] = getMenuReason();
obj[6] = getMenuStaffId();
obj[7] = getComboBoxMenuStaffName();
*/
PreparedStatement stmtwastage;
PreparedStatement stmtSubtractResturantStore;
PreparedStatement stmtSubtractHybridResturantStore;
BigDecimal total_amount = BigDecimal.ZERO;
String MenuId = new String();
String strwastage =
"INSERT INTO wastage (id,quantity,amount,reason,staff_id,department_id,user_id,date,menu_type_flag) VALUES(?,?,?,?,?,?,?,?,?)";
// String strSubtractSingleResturantStore = "UPDATE department_store_stock SET
// total_qty = department_store_stock.total_qty -(? * (select
// menu.quantity*item_unit.unit_relative_quantity from menu INNER JOIN item_unit ON menu.unit_id
// = item_unit.unit_id WHERE menu.menu_id = ?)) WHERE item_id = (select item_id from menu where
// menu_id = ?)";
String strSubtractSingleResturantStore =
"UPDATE department_store_stock SET total_qty = department_store_stock.total_qty -(? * (select menu.quantity*item_unit.unit_relative_quantity from menu INNER JOIN item_unit ON menu.unit_id = item_unit.unit_id WHERE menu.menu_id = ?)) WHERE department_item_id = (select department_item_id from menu where menu_id = ?)";
String strSubtractHybridResturantStore =
"UPDATE department_store_stock SET total_qty = department_store_stock.total_qty - ?*? WHERE department_item_id = ? ";
DBConnect addorder = new DBConnect();
try {
addorder.initConnection();
/*
* for inserting into wastage
*/
addorder.conn.setAutoCommit(false);
stmtwastage = addorder.conn.prepareStatement(strwastage);
stmtwastage.setInt(1, Integer.parseInt(wastageinfo[0].toString()));
stmtwastage.setDouble(2, Double.parseDouble(wastageinfo[2].toString()));
stmtwastage.setDouble(3, Double.parseDouble(wastageinfo[4].toString()));
stmtwastage.setString(4, wastageinfo[5].toString());
stmtwastage.setInt(5, Integer.parseInt(wastageinfo[6].toString()));
stmtwastage.setInt(6, departmentid);
stmtwastage.setInt(7, userid);
stmtwastage.setDate(8, new java.sql.Date(Function.returnSystemDate().getTime()));
stmtwastage.setBoolean(9, Boolean.TRUE);
stmtwastage.executeUpdate();
/*
* for reducing resturant
*/
// when tracable item
// if(checkTrackable(strdeladd))
/*
* separating the menu_id to thier type as singletracable and hybrid type
*/
MenuId = wastageinfo[0].toString();
if (checkTrackable(Integer.parseInt(MenuId))) {
if (checkHybrid(Integer.parseInt(MenuId))) {
// if it is hybrid menu
/*
* if only hybrid trackable
*/
// return the item-unit in row
ArrayList<String[]> HybridItem = new ArrayList<>();
String[][] strHybridItem = null;
String[][] data = getItemIdForHybrid(Integer.parseInt(wastageinfo[0].toString()));
for (String[] data1 : data) {
String[] row = new String[] {data1[0], data1[1], wastageinfo[2].toString()};
HybridItem.add(row);
}
strHybridItem = HybridItem.toArray(new String[HybridItem.size()][]);
/*
* start a query for hybrid
*/
stmtSubtractHybridResturantStore =
addorder.conn.prepareStatement(strSubtractHybridResturantStore);
for (String[] strHybridItem1 : strHybridItem) {
// stmtdelSubtractHybridResturantStore.setString(1, straddHybridItem[i][0]);
stmtSubtractHybridResturantStore.setBigDecimal(1, new BigDecimal(strHybridItem1[2]));
stmtSubtractHybridResturantStore.setBigDecimal(2, new BigDecimal(strHybridItem1[1]));
stmtSubtractHybridResturantStore.setString(3, strHybridItem1[0]);
stmtSubtractHybridResturantStore.executeUpdate();
}
} else {
// if it is single trackable menu
/*
* if only single trackable
*/
stmtSubtractResturantStore =
addorder.conn.prepareStatement(strSubtractSingleResturantStore);
addorder.conn.setAutoCommit(false);
// stmtdelSubtractResturantStore.setString(1, straddSingleTrackableItem[i][0]);
stmtSubtractResturantStore.setBigDecimal(1, new BigDecimal(wastageinfo[2].toString()));
stmtSubtractResturantStore.setInt(2, Integer.parseInt(wastageinfo[0].toString()));
stmtSubtractResturantStore.setInt(3, Integer.parseInt(wastageinfo[0].toString()));
stmtSubtractResturantStore.executeUpdate();
}
}
// if everything goes weel commit
addorder.conn.commit();
JOptionPane.showMessageDialog(null, "Wastage Saved Successfully");
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, e + "from Wastage Save" + getClass().getName());
}
}