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