private void listSelectionChaged(ListSelectionEvent e) {
dlmInc.clear();
if (tblPackages.getSelectedRow() == -1) {
// dlmInc.removeRange(0, dlmInc.size() - 1);
return;
}
if (tblPackages.getSelectedRow() == -1) {
setButtonState(false);
tblPackages.clearSelection();
return;
}
setButtonState(true);
String sql1 =
"SELECT ps.ProductSupplierId, pro.ProductId, ProdName, sup.SupplierId, SupName "
+ " FROM Packages pkg, Packages_Products_Suppliers pps, Products_Suppliers ps, Products pro, Suppliers sup "
+ " WHERE pkg.PackageId = pps.PackageId "
+ " AND pps.ProductSupplierId = ps.ProductSupplierId "
+ " AND ps.ProductId = pro.ProductId "
+ " AND ps.SupplierId = sup.SupplierId "
+ " AND pkg.PackageId = "
+ tblPackages.getValueAt(tblPackages.getSelectedRow(), PackagesTableModel.PACKAGE_ID)
+ " ORDER BY ProdName, SupName";
if (tblPackages.getValueAt(tblPackages.getSelectedRow(), PackagesTableModel.PACKAGE_ID) == null
|| tblPackages.getValueAt(tblPackages.getSelectedRow(), PackagesTableModel.PACKAGE_ID)
== "") {
return;
}
TXLogger.logger.debug("tblPackages listSelectionChaged:" + sql1);
// System.out.println(sql1);
try {
rs1 = stmt1.executeQuery(sql1);
while (rs1.next()) {
Products_Suppliers ps = new Products_Suppliers();
ps.setProductSupplierID(rs1.getInt(1));
ps.setProductID(rs1.getInt(2));
ps.setPsProdName(rs1.getString(3));
ps.setSupplierID(rs1.getInt(4));
ps.setPsSuppName(rs1.getString(5));
dlmInc.addElement(ps);
}
getAllProdList(cmbProdFilter.getSelectedItem().toString());
rs1.close();
} catch (SQLException ex) {
TXLogger.logger.error(ex.getMessage());
// ex.printStackTrace();
}
}
// get all records from table Products_Suppliers
private void getAllProdList(String filter) {
dlmAvi.clear();
String sql1 =
"SELECT ps.ProductSupplierId, pro.ProductId, ProdName, sup.SupplierId, SupName "
+ "FROM Products_Suppliers ps, Products pro, Suppliers sup "
+ "WHERE ps.ProductId = pro.ProductId "
+ " AND ProdName || SupName LIKE '%"
+ filter
+ "%' "
+ " AND ps.SupplierId = sup.SupplierId "
+ "ORDER BY ProdName, SupName";
TXLogger.logger.debug("getAllProdList:" + sql1);
// System.out.println(sql1);
try {
stmt1 = sqlConn.createStatement();
rs1 = stmt1.executeQuery(sql1);
while (rs1.next()) {
Products_Suppliers ps = new Products_Suppliers();
ps.setProductSupplierID(rs1.getInt(1));
ps.setProductID(rs1.getInt(2));
ps.setPsProdName(rs1.getString(3));
ps.setSupplierID(rs1.getInt(4));
ps.setPsSuppName(rs1.getString(5));
dlmAvi.addElement(ps);
// exclude products in lstProdInc
for (int i = 0; i < dlmInc.size(); i++) {
if (((Products_Suppliers) dlmInc.elementAt(i)).getProductSupplierID() == rs1.getInt(1)) {
dlmAvi.removeElement(ps);
}
}
}
rs1.close();
} catch (SQLException e) {
TXLogger.logger.error(e.getMessage());
// e.printStackTrace();
}
}