//	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
  }