/** Query ATP */
private void initAtpTab(int m_M_Warehouse_ID) {
// Header
Vector<String> columnNames = new Vector<String>();
columnNames.add(Msg.translate(Env.getCtx(), "Date"));
columnNames.add(Msg.translate(Env.getCtx(), "QtyOnHand"));
columnNames.add(Msg.translate(Env.getCtx(), "C_BPartner_ID"));
columnNames.add(Msg.translate(Env.getCtx(), "QtyOrdered"));
columnNames.add(Msg.translate(Env.getCtx(), "QtyReserved"));
columnNames.add(Msg.translate(Env.getCtx(), "M_Locator_ID"));
columnNames.add(Msg.translate(Env.getCtx(), "M_AttributeSetInstance_ID"));
columnNames.add(Msg.translate(Env.getCtx(), "DocumentNo"));
columnNames.add(Msg.translate(Env.getCtx(), "M_Warehouse_ID"));
// Fill Storage Data
boolean showDetail = CLogMgt.isLevelFine();
String sql =
"SELECT s.QtyOnHand, s.QtyReserved, s.QtyOrdered,"
+ " productAttribute(s.M_AttributeSetInstance_ID), s.M_AttributeSetInstance_ID,";
if (!showDetail)
sql =
"SELECT SUM(s.QtyOnHand), SUM(s.QtyReserved), SUM(s.QtyOrdered),"
+ " productAttribute(s.M_AttributeSetInstance_ID), 0,";
sql +=
" w.Name, l.Value "
+ "FROM M_Storage s"
+ " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID)"
+ " INNER JOIN M_Warehouse w ON (l.M_Warehouse_ID=w.M_Warehouse_ID) "
+ "WHERE M_Product_ID=?";
if (m_M_Warehouse_ID != 0) sql += " AND l.M_Warehouse_ID=?";
if (m_M_AttributeSetInstance_ID > 0) sql += " AND s.M_AttributeSetInstance_ID=?";
sql += " AND (s.QtyOnHand<>0 OR s.QtyReserved<>0 OR s.QtyOrdered<>0)";
if (!showDetail)
sql += " GROUP BY productAttribute(s.M_AttributeSetInstance_ID), w.Name, l.Value";
sql += " ORDER BY l.Value";
Vector<Vector<Object>> data = new Vector<Vector<Object>>();
double qty = 0;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, m_M_Product_ID);
if (m_M_Warehouse_ID != 0) pstmt.setInt(2, m_M_Warehouse_ID);
if (m_M_AttributeSetInstance_ID > 0) pstmt.setInt(3, m_M_AttributeSetInstance_ID);
rs = pstmt.executeQuery();
while (rs.next()) {
Vector<Object> line = new Vector<Object>(9);
line.add(null); // Date
double qtyOnHand = rs.getDouble(1);
qty += qtyOnHand;
line.add(new Double(qtyOnHand)); // Qty
line.add(null); // BPartner
line.add(new Double(rs.getDouble(3))); // QtyOrdered
line.add(new Double(rs.getDouble(2))); // QtyReserved
line.add(rs.getString(7)); // Locator
String asi = rs.getString(4);
if (showDetail && (asi == null || asi.length() == 0)) asi = "{" + rs.getInt(5) + "}";
line.add(asi); // ASI
line.add(null); // DocumentNo
line.add(rs.getString(6)); // Warehouse
data.add(line);
}
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
// Orders
sql =
"SELECT ol.DatePromised, ol.QtyReserved,"
+ " productAttribute(ol.M_AttributeSetInstance_ID), ol.M_AttributeSetInstance_ID,"
+ " dt.DocBaseType, bp.Name,"
+ " dt.PrintName || ' ' || o.DocumentNo As DocumentNo, w.Name "
+ "FROM C_Order o"
+ " INNER JOIN C_OrderLine ol ON (o.C_Order_ID=ol.C_Order_ID)"
+ " INNER JOIN C_DocType dt ON (o.C_DocType_ID=dt.C_DocType_ID)"
+ " INNER JOIN M_Warehouse w ON (ol.M_Warehouse_ID=w.M_Warehouse_ID)"
+ " INNER JOIN C_BPartner bp ON (o.C_BPartner_ID=bp.C_BPartner_ID) "
+ "WHERE ol.QtyReserved<>0"
+ " AND ol.M_Product_ID=?";
if (m_M_Warehouse_ID != 0) sql += " AND ol.M_Warehouse_ID=?";
if (m_M_AttributeSetInstance_ID > 0) sql += " AND ol.M_AttributeSetInstance_ID=?";
sql += " ORDER BY o.DatePromised";
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, m_M_Product_ID);
if (m_M_Warehouse_ID != 0) pstmt.setInt(2, m_M_Warehouse_ID);
if (m_M_AttributeSetInstance_ID > 0) pstmt.setInt(3, m_M_AttributeSetInstance_ID);
rs = pstmt.executeQuery();
while (rs.next()) {
Vector<Object> line = new Vector<Object>(9);
line.add(rs.getTimestamp(1)); // Date
double oq = rs.getDouble(2);
String DocBaseType = rs.getString(5);
Double qtyReserved = null;
Double qtyOrdered = null;
if (MDocType.DOCBASETYPE_PurchaseOrder.equals(DocBaseType)) {
qtyOrdered = new Double(oq);
qty += oq;
} else {
qtyReserved = new Double(oq);
qty -= oq;
}
line.add(new Double(qty)); // Qty
line.add(rs.getString(6)); // BPartner
line.add(qtyOrdered); // QtyOrdered
line.add(qtyReserved); // QtyReserved
line.add(null); // Locator
String asi = rs.getString(3);
if (showDetail && (asi == null || asi.length() == 0)) asi = "{" + rs.getInt(4) + "}";
line.add(asi); // ASI
line.add(rs.getString(7)); // DocumentNo
line.add(rs.getString(8)); // Warehouse
data.add(line);
}
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
// Table
MiniTable table = null;
m_modelAtp = new DefaultTableModel(data, columnNames);
m_tableAtp.setModel(m_modelAtp);
table = m_tableAtp;
//
table.setColumnClass(0, Timestamp.class, true); // Date
table.setColumnClass(1, Double.class, true); // Quantity
table.setColumnClass(2, String.class, true); // Partner
table.setColumnClass(3, Double.class, true); // Quantity
table.setColumnClass(4, Double.class, true); // Quantity
table.setColumnClass(5, String.class, true); // Locator
table.setColumnClass(6, String.class, true); // ASI
table.setColumnClass(7, String.class, true); // DocNo
table.setColumnClass(8, String.class, true); // Warehouse
//
table.autoSize();
} // initAtpTab
/** Static Setup - add fields to parameterPanel */
private void statInit() {
labelValue.setText(Msg.getMsg(Env.getCtx(), "Value"));
fieldValue.setBackground(AdempierePLAF.getInfoBackground());
fieldValue.addActionListener(this);
labelName.setText(Msg.getMsg(Env.getCtx(), "Name"));
fieldName.setBackground(AdempierePLAF.getInfoBackground());
fieldName.addActionListener(this);
labelUPC.setText(Msg.translate(Env.getCtx(), "UPC"));
fieldUPC.setBackground(AdempierePLAF.getInfoBackground());
fieldUPC.addActionListener(this);
labelSKU.setText(Msg.translate(Env.getCtx(), "SKU"));
fieldSKU.setBackground(AdempierePLAF.getInfoBackground());
fieldSKU.addActionListener(this);
labelWarehouse.setText(Msg.getMsg(Env.getCtx(), "Warehouse"));
pickWarehouse.setBackground(AdempierePLAF.getInfoBackground());
labelPriceList.setText(Msg.getMsg(Env.getCtx(), "PriceListVersion"));
pickPriceList.setBackground(AdempierePLAF.getInfoBackground());
labelProductCategory.setText(Msg.translate(Env.getCtx(), "M_Product_Category_ID"));
pickProductCategory.setBackground(AdempierePLAF.getInfoBackground());
// @Trifon
labelAS.setText(Msg.translate(Env.getCtx(), "M_AttributeSet_ID"));
pickAS.setBackground(AdempierePLAF.getInfoBackground());
m_InfoPAttributeButton.setMargin(new Insets(2, 2, 2, 2));
m_InfoPAttributeButton.setToolTipText(Msg.getMsg(Env.getCtx(), "InfoPAttribute"));
m_InfoPAttributeButton.addActionListener(this);
labelVendor.setText(Msg.translate(Env.getCtx(), "Vendor"));
fieldVendor.setBackground(AdempierePLAF.getInfoBackground());
fieldVendor.addActionListener(this);
// Line 1
parameterPanel.setLayout(new ALayout());
parameterPanel.add(labelValue, new ALayoutConstraint(0, 0));
parameterPanel.add(fieldValue, null);
parameterPanel.add(labelUPC, null);
parameterPanel.add(fieldUPC, null);
parameterPanel.add(labelWarehouse, null);
parameterPanel.add(pickWarehouse, null);
parameterPanel.add(m_InfoPAttributeButton);
// Line 2
parameterPanel.add(labelName, new ALayoutConstraint(1, 0));
parameterPanel.add(fieldName, null);
parameterPanel.add(labelSKU, null);
parameterPanel.add(fieldSKU, null);
parameterPanel.add(labelVendor, null);
parameterPanel.add(fieldVendor, null);
// Line 3
parameterPanel.add(labelPriceList, new ALayoutConstraint(2, 0));
parameterPanel.add(pickPriceList, null);
parameterPanel.add(labelProductCategory, null);
parameterPanel.add(pickProductCategory, null);
parameterPanel.add(labelAS, null); // @Trifon
parameterPanel.add(pickAS, null); // @Trifon
// Product Attribute Instance
m_PAttributeButton = ConfirmPanel.createPAttributeButton(true);
confirmPanel.addButton(m_PAttributeButton);
m_PAttributeButton.addActionListener(this);
m_PAttributeButton.setEnabled(false);
// Begin - fer_luck @ centuryon
// add taskpane
fieldDescription.setBackground(AdempierePLAF.getInfoBackground());
fieldDescription.setEditable(false);
fieldDescription.setPreferredSize(new Dimension(INFO_WIDTH - 100, 100));
warehouseStockPanel.setTitle(Msg.translate(Env.getCtx(), "WarehouseStock"));
warehouseStockPanel.setUI(new AdempiereTaskPaneUI());
warehouseStockPanel.getContentPane().setBackground(new ColorUIResource(251, 248, 241));
warehouseStockPanel.getContentPane().setForeground(new ColorUIResource(251, 0, 0));
ColumnInfo[] s_layoutWarehouse =
new ColumnInfo[] {
new ColumnInfo(Msg.translate(Env.getCtx(), "Warehouse"), "Warehouse", String.class),
new ColumnInfo(
Msg.translate(Env.getCtx(), "QtyAvailable"), "sum(QtyAvailable)", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOnHand"), "sum(QtyOnHand)", Double.class),
new ColumnInfo(
Msg.translate(Env.getCtx(), "QtyReserved"), "sum(QtyReserved)", Double.class),
new ColumnInfo(
Msg.translate(Env.getCtx(), "QtyAllocated"), "sum(QtyAllocated)", Double.class)
};
/** From Clause */
String s_sqlFrom = " M_PRODUCT_STOCK_V ";
/** Where Clause */
String s_sqlWhere = "Value = ?";
m_sqlWarehouse =
warehouseTbl.prepareTable(
s_layoutWarehouse, s_sqlFrom, s_sqlWhere, false, "M_PRODUCT_STOCK_V");
m_sqlWarehouse += " Group By Warehouse, documentnote ";
warehouseTbl.setRowSelectionAllowed(true);
warehouseTbl.setMultiSelection(false);
warehouseTbl.addMouseListener(this);
warehouseTbl.getSelectionModel().addListSelectionListener(this);
warehouseTbl.setShowTotals(true);
warehouseTbl.autoSize();
ColumnInfo[] s_layoutSubstitute =
new ColumnInfo[] {
new ColumnInfo(Msg.translate(Env.getCtx(), "Warehouse"), "orgname", String.class),
new ColumnInfo(
Msg.translate(Env.getCtx(), "Value"),
"(Select Value from M_Product p where p.M_Product_ID=M_PRODUCT_SUBSTITUTERELATED_V.Substitute_ID)",
String.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "Name"), "Name", String.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyAvailable"), "QtyAvailable", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOnHand"), "QtyOnHand", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyReserved"), "QtyReserved", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "PriceStd"), "PriceStd", Double.class)
};
s_sqlFrom = "M_PRODUCT_SUBSTITUTERELATED_V";
s_sqlWhere = "M_Product_ID = ? AND M_PriceList_Version_ID = ? and RowType = 'S'";
m_sqlSubstitute =
substituteTbl.prepareTable(
s_layoutSubstitute, s_sqlFrom, s_sqlWhere, false, "M_PRODUCT_SUBSTITUTERELATED_V");
substituteTbl.setRowSelectionAllowed(false);
substituteTbl.setMultiSelection(false);
substituteTbl.addMouseListener(this);
substituteTbl.getSelectionModel().addListSelectionListener(this);
substituteTbl.autoSize();
ColumnInfo[] s_layoutRelated =
new ColumnInfo[] {
new ColumnInfo(Msg.translate(Env.getCtx(), "Warehouse"), "orgname", String.class),
new ColumnInfo(
Msg.translate(Env.getCtx(), "Value"),
"(Select Value from M_Product p where p.M_Product_ID=M_PRODUCT_SUBSTITUTERELATED_V.Substitute_ID)",
String.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "Name"), "Name", String.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyAvailable"), "QtyAvailable", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOnHand"), "QtyOnHand", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyReserved"), "QtyReserved", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "PriceStd"), "PriceStd", Double.class)
};
s_sqlFrom = "M_PRODUCT_SUBSTITUTERELATED_V";
s_sqlWhere = "M_Product_ID = ? AND M_PriceList_Version_ID = ? and RowType = 'R'";
m_sqlRelated =
relatedTbl.prepareTable(
s_layoutRelated, s_sqlFrom, s_sqlWhere, false, "M_PRODUCT_SUBSTITUTERELATED_V");
relatedTbl.setRowSelectionAllowed(false);
relatedTbl.setMultiSelection(false);
relatedTbl.addMouseListener(this);
relatedTbl.getSelectionModel().addListSelectionListener(this);
relatedTbl.autoSize();
// Available to Promise Tab
m_tableAtp.setRowSelectionAllowed(false);
m_tableAtp.setMultiSelection(false);
CTabbedPane jTab = new CTabbedPane();
jTab.addTab(Msg.translate(Env.getCtx(), "Warehouse"), new JScrollPane(warehouseTbl));
jTab.setPreferredSize(new Dimension(INFO_WIDTH, SCREEN_HEIGHT > 600 ? 250 : 105));
jTab.addTab(Msg.translate(Env.getCtx(), "Description"), new JScrollPane(fieldDescription));
jTab.addTab(Msg.translate(Env.getCtx(), "Substitute_ID"), new JScrollPane(substituteTbl));
jTab.addTab(Msg.translate(Env.getCtx(), "RelatedProduct_ID"), new JScrollPane(relatedTbl));
jTab.addTab(Msg.getMsg(Env.getCtx(), "ATP"), new JScrollPane(m_tableAtp));
jTab.addChangeListener(this);
tablePanel.setPreferredSize(new Dimension(INFO_WIDTH, SCREEN_HEIGHT > 600 ? 255 : 110));
tablePanel.add(jTab);
warehouseStockPanel.setCollapsed(true);
warehouseStockPanel.add(tablePanel);
this.addonPanel.add(warehouseStockPanel);
this.p_table.addKeyListener(
new KeyAdapter() {
public void keyReleased(KeyEvent ke) {
int row = ((MiniTable) ke.getSource()).getSelectedRow();
refresh(
((MiniTable) ke.getSource()).getValueAt(row, 2),
new BigDecimal(pickWarehouse.getValue().toString()).intValue(),
new BigDecimal(pickPriceList.getValue().toString()).intValue());
warehouseStockPanel.setCollapsed(false);
}
});
this.p_table.addMouseListener(
new MouseAdapter() {
public void mouseClicked(MouseEvent me) {
int row = ((MiniTable) me.getSource()).getSelectedRow();
refresh(
((MiniTable) me.getSource()).getValueAt(row, 2),
new BigDecimal(pickWarehouse.getValue().toString()).intValue(),
new BigDecimal(pickPriceList.getValue().toString()).intValue());
warehouseStockPanel.setCollapsed(false);
}
});
// End - fer_luck @ centuryon
} // statInit
/** Dynamic Init. Table Layout, Visual, Listener */
private void dynInit() {
// Add the column definition for the table
xProductTable.addColumn(" ");
xProductTable.addColumn(Msg.translate(Env.getCtx(), "ProductKey"));
xProductTable.addColumn(Msg.translate(Env.getCtx(), "VendorProductRef"));
xProductTable.addColumn(Msg.translate(Env.getCtx(), "XX_Product"));
xProductTable.addColumn(Msg.translate(Env.getCtx(), "XX_PriceConsecutive"));
xProductTable.addColumn(Msg.translate(Env.getCtx(), "XX_ProductQty"));
xProductTable.addColumn(Msg.translate(Env.getCtx(), "XX_LabelQty"));
xProductTable.addColumn(Msg.translate(Env.getCtx(), "XX_Category"));
xProductTable.addColumn(Msg.translate(Env.getCtx(), "XX_Department_I"));
xProductTable.addColumn(Msg.translate(Env.getCtx(), "XX_Line_I"));
xProductTable.addColumn(Msg.translate(Env.getCtx(), "XX_Section_I"));
xProductTable.addColumn(Msg.translate(Env.getCtx(), "AttributeSetInstance"));
xProductTable.setMultiSelection(true);
xProductTable.setColumnClass(0, IDColumn.class, false);
xProductTable.setColumnClass(1, String.class, true);
xProductTable.setColumnClass(2, KeyNamePair.class, true);
xProductTable.setColumnClass(3, KeyNamePair.class, true);
xProductTable.setColumnClass(4, String.class, true);
xProductTable.setColumnClass(5, Integer.class, true);
xProductTable.setColumnClass(6, Integer.class, false);
xProductTable.setColumnClass(7, KeyNamePair.class, true);
xProductTable.setColumnClass(8, KeyNamePair.class, true);
xProductTable.setColumnClass(9, KeyNamePair.class, true);
xProductTable.setColumnClass(10, KeyNamePair.class, true);
xProductTable.setColumnClass(11, KeyNamePair.class, true);
CompiereColor.setBackground(this);
xProductTable.setRowHeight(xProductTable.getRowHeight() + 2);
generate.addActionListener(this);
markall.addActionListener(this);
// Init
statusBar.setStatusLine("");
statusBar.setStatusDB(0);
fillProductTable();
xProductTable.addKeyListener(
new KeyListener() {
@Override
public void keyTyped(KeyEvent e) {}
@Override
public void keyReleased(KeyEvent e) {
int row = xProductTable.getSelectedColumn();
int column = xProductTable.getSelectedRow();
xProductTable.editCellAt(column, row);
}
@Override
public void keyPressed(KeyEvent e) {}
});
xProductTable.getTableHeader().setReorderingAllowed(false);
xProductTable.getModel().addTableModelListener(this);
xProductTable.autoSize(true);
xProductTable.repaint();
} // dynInit