/** Dynamic Init for Price Tab */
private boolean dynInit() {
// Header
Vector<String> columnNames = new Vector<String>();
columnNames.add(
Msg.translate(Env.getCtx(), m_C_BPartner_ID == 0 ? "C_BPartner_ID" : "M_Product_ID"));
columnNames.add(Msg.translate(Env.getCtx(), "PriceActual"));
columnNames.add(Msg.translate(Env.getCtx(), "C_Currency_ID"));
columnNames.add(Msg.translate(Env.getCtx(), "QtyInvoiced"));
columnNames.add(Msg.translate(Env.getCtx(), "Discount"));
columnNames.add(Msg.translate(Env.getCtx(), "DocumentNo"));
columnNames.add(Msg.translate(Env.getCtx(), "DateInvoiced"));
columnNames.add(Msg.translate(Env.getCtx(), "AD_Org_ID"));
// Fill Data
Vector<Vector<Object>> data = null;
if (m_C_BPartner_ID == 0) data = queryBPartner(); // BPartner of Product
else data = queryProduct(); // Product of BPartner
// Table
m_modelPrice = new ListModelTable(data);
m_tablePrice.setData(m_modelPrice, columnNames);
//
m_tablePrice.setColumnClass(0, String.class, true); // Product/Partner
m_tablePrice.setColumnClass(1, Double.class, true); // Price
m_tablePrice.setColumnClass(2, String.class, true); // Currency
m_tablePrice.setColumnClass(3, Double.class, true); // Quantity
m_tablePrice.setColumnClass(4, BigDecimal.class, true); // Discount (%) to limit precision
m_tablePrice.setColumnClass(5, String.class, true); // DocNo
m_tablePrice.setColumnClass(6, Timestamp.class, true); // Date
m_tablePrice.setColumnClass(7, String.class, true); // Org
//
m_tablePrice.autoSize();
//
return data.size() != 0;
} // dynInit
/** Query ATP */
private void initAtpTab() {
// Done already
if (m_modelAtp != null) return;
// 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
String sql =
"SELECT s.QtyOnHand, s.QtyReserved, s.QtyOrdered,"
+ " productAttribute(s.M_AttributeSetInstance_ID), s.M_AttributeSetInstance_ID,";
if (!showDetailATP)
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)"
+ " LEFT JOIN M_LocatorType lt ON (l.M_LocatorType_ID=lt.M_LocatorType_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)";
sql += " AND COALESCE(lt.IsAvailableForReservation,'Y')='Y'";
if (!showDetailATP)
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 (showDetailATP && (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 o.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 (showDetailATP && (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
m_modelAtp = new ListModelTable(data);
m_tableAtp.setData(m_modelAtp, columnNames);
//
m_tableAtp.setColumnClass(0, Timestamp.class, true); // Date
m_tableAtp.setColumnClass(1, Double.class, true); // Quantity
m_tableAtp.setColumnClass(2, String.class, true); // Partner
m_tableAtp.setColumnClass(3, Double.class, true); // Quantity
m_tableAtp.setColumnClass(4, Double.class, true); // Quantity
m_tableAtp.setColumnClass(5, String.class, true); // Locator
m_tableAtp.setColumnClass(6, String.class, true); // ASI
m_tableAtp.setColumnClass(7, String.class, true); // DocNo
m_tableAtp.setColumnClass(8, String.class, true); // Warehouse
//
m_tableAtp.autoSize();
} // initAtpTab
/** Query Unconfirmed */
private void initUnconfirmedTab() {
// Done already
if (m_modelUnconfirmed != null) return;
// Header
Vector<String> columnNames = new Vector<String>();
columnNames.add(
Msg.translate(Env.getCtx(), m_C_BPartner_ID == 0 ? "C_BPartner_ID" : "M_Product_ID"));
columnNames.add(Msg.translate(Env.getCtx(), "MovementQty"));
columnNames.add(Msg.translate(Env.getCtx(), "MovementDate"));
columnNames.add(Msg.translate(Env.getCtx(), "IsSOTrx"));
columnNames.add(Msg.translate(Env.getCtx(), "DocumentNo"));
columnNames.add(Msg.translate(Env.getCtx(), "M_Warehouse_ID"));
// Fill Data
String sql = null;
int parameter = 0;
if (m_C_BPartner_ID == 0) {
sql =
"SELECT bp.Name,"
+ " CASE WHEN io.IsSOTrx='Y' THEN iol.MovementQty*-1 ELSE iol.MovementQty END AS MovementQty,"
+ " io.MovementDate,io.IsSOTrx,"
+ " dt.PrintName || ' ' || io.DocumentNo As DocumentNo,"
+ " w.Name "
+ "FROM M_InOutLine iol"
+ " INNER JOIN M_InOut io ON (iol.M_InOut_ID=io.M_InOut_ID)"
+ " INNER JOIN C_BPartner bp ON (io.C_BPartner_ID=bp.C_BPartner_ID)"
+ " INNER JOIN C_DocType dt ON (io.C_DocType_ID=dt.C_DocType_ID)"
+ " INNER JOIN M_Warehouse w ON (io.M_Warehouse_ID=w.M_Warehouse_ID)"
+ " INNER JOIN M_InOutLineConfirm lc ON (iol.M_InOutLine_ID=lc.M_InOutLine_ID) "
+ "WHERE iol.M_Product_ID=?"
+ " AND lc.Processed='N' "
+ "ORDER BY io.MovementDate,io.IsSOTrx";
parameter = m_M_Product_ID;
} else {
sql =
"SELECT p.Name,"
+ " CASE WHEN io.IsSOTrx='Y' THEN iol.MovementQty*-1 ELSE iol.MovementQty END AS MovementQty,"
+ " io.MovementDate,io.IsSOTrx,"
+ " dt.PrintName || ' ' || io.DocumentNo As DocumentNo,"
+ " w.Name "
+ "FROM M_InOutLine iol"
+ " INNER JOIN M_InOut io ON (iol.M_InOut_ID=io.M_InOut_ID)"
+ " INNER JOIN M_Product p ON (iol.M_Product_ID=p.M_Product_ID)"
+ " INNER JOIN C_DocType dt ON (io.C_DocType_ID=dt.C_DocType_ID)"
+ " INNER JOIN M_Warehouse w ON (io.M_Warehouse_ID=w.M_Warehouse_ID)"
+ " INNER JOIN M_InOutLineConfirm lc ON (iol.M_InOutLine_ID=lc.M_InOutLine_ID) "
+ "WHERE io.C_BPartner_ID=?"
+ " AND lc.Processed='N' "
+ "ORDER BY io.MovementDate,io.IsSOTrx";
parameter = m_C_BPartner_ID;
}
Vector<Vector<Object>> data = new Vector<Vector<Object>>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, parameter);
rs = pstmt.executeQuery();
while (rs.next()) {
Vector<Object> line = new Vector<Object>(6);
// 1-Name, 2-MovementQty, 3-MovementDate, 4-IsSOTrx, 5-DocumentNo
line.add(rs.getString(1)); // Name
line.add(new Double(rs.getDouble(2))); // Qty
line.add(rs.getTimestamp(3)); // Date
line.add(new Boolean("Y".equals(rs.getString(4)))); // IsSOTrx
line.add(rs.getString(5)); // DocNo
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;
}
if (log.isLoggable(Level.FINE)) log.fine("#" + data.size());
// Table
m_modelUnconfirmed = new ListModelTable(data);
m_tableUnconfirmed.setData(m_modelUnconfirmed, columnNames);
//
m_tableUnconfirmed.setColumnClass(0, String.class, true); // Product/Partner
m_tableUnconfirmed.setColumnClass(1, Double.class, true); // MovementQty
m_tableUnconfirmed.setColumnClass(2, Timestamp.class, true); // MovementDate
m_tableUnconfirmed.setColumnClass(3, Boolean.class, true); // IsSOTrx
m_tableUnconfirmed.setColumnClass(4, String.class, true); // DocNo
//
m_tableUnconfirmed.autoSize();
} // initUnconfirmedTab
/**
* Query Reserved/Ordered
*
* @param reserved po/so
*/
private void initReservedOrderedTab(boolean reserved) {
// Done already
if (reserved && m_modelReserved != null) return;
if (!reserved && m_modelOrdered != null) return;
// Header
Vector<String> columnNames = new Vector<String>();
columnNames.add(
Msg.translate(Env.getCtx(), m_C_BPartner_ID == 0 ? "C_BPartner_ID" : "M_Product_ID"));
columnNames.add(Msg.translate(Env.getCtx(), "PriceActual"));
columnNames.add(Msg.translate(Env.getCtx(), "C_Currency_ID"));
columnNames.add(Msg.translate(Env.getCtx(), reserved ? "QtyReserved" : "QtyOrdered"));
columnNames.add(Msg.translate(Env.getCtx(), "Discount"));
columnNames.add(Msg.translate(Env.getCtx(), "DocumentNo"));
columnNames.add(Msg.translate(Env.getCtx(), "DateOrdered"));
columnNames.add(Msg.translate(Env.getCtx(), "M_Warehouse_ID"));
// Fill Data
Vector<Vector<Object>> data = null;
if (m_C_BPartner_ID == 0) {
String sql =
"SELECT bp.Name, ol.PriceActual,c.Iso_Code,ol.PriceList,ol.QtyReserved,"
+ "o.DateOrdered,dt.PrintName || ' ' || o.DocumentNo As DocumentNo, "
+ "w.Name,"
+ "ol.Discount, 0 " // 8,9=M_PriceList_ID
+ "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) "
+ " INNER JOIN C_Currency c ON (o.C_Currency_ID=c.C_Currency_ID) "
+ "WHERE ol.QtyReserved<>0"
+ " AND ol.M_Product_ID=?"
+ " AND o.IsSOTrx="
+ (reserved ? "'Y'" : "'N'")
+ " ORDER BY o.DateOrdered";
data = fillTable(sql, m_M_Product_ID); // Product By BPartner
} else {
String sql =
"SELECT p.Name, ol.PriceActual,c.Iso_Code,ol.PriceList,ol.QtyReserved,"
+ "o.DateOrdered,dt.PrintName || ' ' || o.DocumentNo As DocumentNo, "
+ "w.Name,"
+ "ol.Discount, 0 " // 8,9=M_PriceList_ID
+ "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 M_Product p ON (ol.M_Product_ID=p.M_Product_ID) "
+ " INNER JOIN C_Currency c ON (o.C_Currency_ID=c.C_Currency_ID) "
+ "WHERE ol.QtyReserved<>0"
+ " AND o.C_BPartner_ID=?"
+ " AND o.IsSOTrx="
+ (reserved ? "'Y'" : "'N'")
+ " ORDER BY o.DateOrdered";
data = fillTable(sql, m_C_BPartner_ID); // Product of BP
}
// Table
if (reserved) {
m_modelReserved = new ListModelTable(data);
m_tableReserved.setData(m_modelReserved, columnNames);
//
m_tableReserved.setColumnClass(0, String.class, true); // Product/Partner
m_tableReserved.setColumnClass(1, BigDecimal.class, true); // Price
m_tableReserved.setColumnClass(2, String.class, true); // Currency
m_tableReserved.setColumnClass(3, Double.class, true); // Quantity
m_tableReserved.setColumnClass(4, BigDecimal.class, true); // Discount (%)
m_tableReserved.setColumnClass(5, String.class, true); // DocNo
m_tableReserved.setColumnClass(6, Timestamp.class, true); // Date
m_tableReserved.setColumnClass(7, String.class, true); // Warehouse
//
m_tableReserved.autoSize();
} else {
m_modelOrdered = new ListModelTable(data);
m_tableOrdered.setData(m_modelOrdered, columnNames);
//
m_tableOrdered.setColumnClass(0, String.class, true); // Product/Partner
m_tableOrdered.setColumnClass(1, BigDecimal.class, true); // Price
m_tableOrdered.setColumnClass(2, String.class, true); // Currency
m_tableOrdered.setColumnClass(3, Double.class, true); // Quantity
m_tableOrdered.setColumnClass(4, BigDecimal.class, true); // Discount (%)
m_tableOrdered.setColumnClass(5, String.class, true); // DocNo
m_tableOrdered.setColumnClass(6, Timestamp.class, true); // Date
m_tableOrdered.setColumnClass(7, String.class, true); // Warehouse
//
m_tableOrdered.autoSize();
}
} // initReservedOrderedTab
