// for item wastage
public void AddItemWastage(Object[] wastageinfo, int userid, int departmentid) {
/*
wastage into
obj[0] = getItemID();
obj[1] = getComboBoxItemName();
obj[2] = getItemQuantity();
obj[3] = getItemAmount();
obj[4] = getItemReason();
obj[5] = getItemWaiterId();
obj[6] = getComboBoxItemStaffName();
obj[7] = getUnitID();
obj[8] = getComboBoxItemBaseUnit();
*/
PreparedStatement stmtwastage;
PreparedStatement stmtSubtractResturantStore;
String strwastage =
"INSERT INTO wastage (id,quantity,amount,reason,staff_id,department_id,user_id,date,menu_type_flag,unit_id) 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 strSubtractHybridResturantStore =
"UPDATE department_store_stock SET total_qty = department_store_stock.total_qty - ?*(select unit_relative_quantity FROM item_unit WHERE unit_id = ?) WHERE department_item_id = ? ";
try {
initConnection();
// for inserting into wastage table
conn.setAutoCommit(false);
stmtwastage = 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[3].toString()));
stmtwastage.setString(4, wastageinfo[4].toString());
stmtwastage.setInt(5, Integer.parseInt(wastageinfo[5].toString()));
stmtwastage.setInt(6, departmentid);
stmtwastage.setInt(7, userid);
stmtwastage.setDate(8, new java.sql.Date(Function.returnSystemDate().getTime()));
stmtwastage.setBoolean(9, Boolean.FALSE);
stmtwastage.setInt(10, Integer.parseInt(wastageinfo[7].toString()));
stmtwastage.executeUpdate();
// for reducing the wastage quantity from the department_store_stock
stmtSubtractResturantStore = conn.prepareStatement(strSubtractHybridResturantStore);
stmtSubtractResturantStore.setDouble(1, Double.parseDouble(wastageinfo[2].toString()));
stmtSubtractResturantStore.setInt(2, Integer.parseInt(wastageinfo[7].toString()));
stmtSubtractResturantStore.setInt(3, Integer.parseInt(wastageinfo[0].toString()));
stmtSubtractResturantStore.executeUpdate();
// if everthing goes well commit
conn.commit();
JOptionPane.showMessageDialog(null, "Item Wastage Saved Succesfully");
} catch (SQLException se) {
JOptionPane.showMessageDialog(null, se + "from additemwastage" + getClass().getName());
}
}
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());
}
}