// Add product into show_room_products table public void prodCollect( String prodName, String prodCode, String barCode, String brandName, double purchaseRate, double sellingRate, int quantity) { try { conn = dbAccess.connectToDataBase(); stmt = conn.createStatement(); // insert product detail in show_room_products table sql = dbAccess.insertToCurrentStock( show_room_products, prodName, prodCode, barCode, brandName, purchaseRate, sellingRate, quantity); stmt.executeUpdate(sql); JOptionPane.showMessageDialog(null, "Product detail has been added to ShowRoom"); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) { conn.close(); } } catch (SQLException se) { } try { if (conn != null) { conn.close(); } } catch (SQLException se) { se.printStackTrace(); } } // end finally try }
// SHOW CURRENT STOCK/STOCK OUT INFO IN A SHOWROOM public Object[][] showCurrentStock(String ac) { Object[][] data = null; try { conn = dbAccess.connectToDataBase(); stmt = conn.createStatement(); /** Getting total number of rows to initialize an object data array to be sent to the view */ column[0] = "count(*) as totalRow"; // Get total number of rows in the table if (ac.equals(Product.AC_CURRENTSTOCK)) { column[1] = "sum(quantity) as totalNumOfProd"; // Get total number of products sql = dbAccess.selectAll(show_room_products, null, column, null); } else if (ac.equals(Product.AC_STOCKOUTINFO)) { sql = dbAccess.selectAll(show_room_products, showroom_stockout_info, column, "prodCode"); } else { } rs = stmt.executeQuery(sql); int totalRows = 0; int totalNumOfProd = 0; while (rs.next()) { // Retrieve by column name totalRows = rs.getInt("totalRow"); if (ac.equals(Product.AC_CURRENTSTOCK)) { totalNumOfProd = rs.getInt("totalNumOfProd"); } } rs.close(); /** ***************** * */ if (ac.equals(Product.AC_CURRENTSTOCK)) { // Generate an array and get all product info from show_room_products sql = dbAccess.selectAll(show_room_products, null, null, null); } else if (ac.equals(Product.AC_STOCKOUTINFO)) { column[0] = show_room_products + ".prodName"; column[1] = show_room_products + ".prodCode"; column[2] = show_room_products + ".brandName"; column[3] = showroom_stockout_info + ".purchaseRate"; column[4] = showroom_stockout_info + ".sellingRate"; column[5] = showroom_stockout_info + ".quantity"; column[6] = showroom_stockout_info + ".Date"; // Generate an array and get all product info from show_room_products joining // showroom_stockout_info sql = dbAccess.selectAll(show_room_products, showroom_stockout_info, column, "prodCode"); } // System.out.println("Sql "+sql); rs = stmt.executeQuery(sql); int j = 0; // using totalRows to instantiate and declare size of Object data array data = new Object[totalRows][8]; while (rs.next()) { data[j][0] = rs.getString("prodName"); data[j][1] = rs.getString("prodCode"); data[j][2] = rs.getString("brandName"); data[j][3] = rs.getDouble("purchaseRate"); data[j][4] = rs.getDouble("sellingRate"); data[j][5] = rs.getInt("quantity"); data[j][6] = rs.getString("Date"); j++; } rs.close(); // sending total number of products through this index if (ac.equals(Product.AC_CURRENTSTOCK)) { data[0][7] = totalNumOfProd; } } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (rs != null) { rs.close(); } } catch (Exception e) { } try { if (stmt != null) { conn.close(); } } catch (SQLException se) { } try { if (conn != null) { conn.close(); } } catch (SQLException se) { se.printStackTrace(); } } // end finally try return data; }
public void sellProduct(String prodCode, int stockOutQuantity, String sRoomName) { try { conn = dbAccess.connectToDataBase(); stmt = conn.createStatement(); // Get current state of this product from show_room_products int curQuantity = 0; // variable to hold the current stocked quantity double purchaseRate = 0.0; double sellingRate = 0.0; column[0] = "quantity"; column[1] = "purchaseRate"; column[2] = "sellingRate"; sql = dbAccess.selectAll(show_room_products, null, column, null); sql += " where prodCode='" + prodCode + "'"; // System.out.println(sql); rs = stmt.executeQuery(sql); while (rs.next()) { // Retrieve by column name curQuantity = rs.getInt("quantity"); purchaseRate = rs.getDouble("purchaseRate"); sellingRate = rs.getDouble("sellingRate"); } rs.close(); // update product detail in show_room_products table int new_quantity = curQuantity - stockOutQuantity; sql = dbAccess.updateCurrentStock(show_room_products, "quantity", new_quantity); sql += " where prodCode='" + prodCode + "'"; // System.out.println(sql); stmt.executeUpdate(sql); // insert relevant info in showroom_stockout_info table to keep stock out info*/ sql = dbAccess.insertToStockOUTInfo( showroom_stockout_info, prodCode, purchaseRate, sellingRate, stockOutQuantity, sRoomName); // System.out.println(sql); stmt.executeUpdate(sql); // System.out.println("Product sto"); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (rs != null) { rs.close(); } } catch (Exception e) { } try { if (stmt != null) { conn.close(); } } catch (SQLException se) { } try { if (conn != null) { conn.close(); } } catch (SQLException se) { se.printStackTrace(); } } // end finally try }