/******************************************************************************
* Copyright (C) 2008 Elaine Tan
* Copyright (C) 2008 Idalica *
* This program is free software; you can redistribute it and/or modify it *
* under the terms version 2 of the GNU General Public License as published *
* by the Free Software Foundation. This program is distributed in the hope *
* that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
* warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. *
* See the GNU General Public License for more details. *
* You should have received a copy of the GNU General Public License along *
* with this program; if not, write to the Free Software Foundation, Inc., *
* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. *
*****************************************************************************/
package org.adempiere.webui.panel;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Vector;
import java.util.logging.Level;
import org.adempiere.webui.apps.AEnv;
import org.adempiere.webui.component.ConfirmPanel;
import org.adempiere.webui.component.Label;
import org.adempiere.webui.component.ListModelTable;
import org.adempiere.webui.component.ListboxFactory;
import org.adempiere.webui.component.Tab;
import org.adempiere.webui.component.Tabbox;
import org.adempiere.webui.component.Tabpanel;
import org.adempiere.webui.component.Tabpanels;
import org.adempiere.webui.component.Tabs;
import org.adempiere.webui.component.WListbox;
import org.adempiere.webui.component.Window;
import org.compiere.model.MDocType;
import org.compiere.model.MPriceList;
import org.compiere.util.CLogMgt;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.event.Event;
import org.zkoss.zk.ui.event.EventListener;
import org.zkoss.zk.ui.event.Events;
import org.zkoss.zkex.zul.Borderlayout;
import org.zkoss.zkex.zul.Center;
import org.zkoss.zkex.zul.North;
import org.zkoss.zkex.zul.South;
/**
* Price History for BPartner/Product
* This class is based on org.compiere.apps.search.InvoiceHistory written by Jorg Janke
* @author <a href="mailto:elaine.tan@idalica.com">Elaine</a>
*/
public class InvoiceHistory extends Window implements EventListener
{
/**
*
*/
private static final long serialVersionUID = -7426909865199278220L;
/**
* Show History
* @param C_BPartner_ID partner
* @param M_Product_ID product
* @param M_Warehouse_ID warehouse
* @param M_AttributeSetInstance_ID ASI
*/
public InvoiceHistory (Window parent,
int C_BPartner_ID, int M_Product_ID, int M_Warehouse_ID, int M_AttributeSetInstance_ID)
{
super();
setTitle(Msg.getMsg(Env.getCtx(), "PriceHistory"));
log.config("C_BPartner_ID=" + C_BPartner_ID
+ ", M_Product_ID=" + M_Product_ID
+ ", M_Warehouse_ID=" + M_Warehouse_ID
+ ", M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID);
m_C_BPartner_ID = C_BPartner_ID;
m_M_Product_ID = M_Product_ID;
m_M_Warehouse_ID = M_Warehouse_ID;
m_M_AttributeSetInstance_ID = M_AttributeSetInstance_ID;
try
{
jbInit();
dynInit();
}
catch(Exception ex)
{
log.log(Level.SEVERE, "", ex);
}
AEnv.showCenterWindow(parent, this);
} // InvoiceHistory
private int m_C_BPartner_ID;
private int m_M_Product_ID;
private int m_M_Warehouse_ID;
private int m_M_AttributeSetInstance_ID;
/** Logger */
private static CLogger log = CLogger.getCLogger(InvoiceHistory.class);
private Label label = new Label();
//
private ConfirmPanel confirmPanel = new ConfirmPanel();
private Tabbox tabbox = new Tabbox();
//
private Tabpanel pricePane = new Tabpanel();
private WListbox m_tablePrice = ListboxFactory.newDataTable();
private ListModelTable m_modelPrice = null;
private Tabpanel reservedPane = new Tabpanel();
private WListbox m_tableReserved = ListboxFactory.newDataTable();
private ListModelTable m_modelReserved = null;
private Tabpanel orderedPane = new Tabpanel();
private WListbox m_tableOrdered = ListboxFactory.newDataTable();
private ListModelTable m_modelOrdered = null;
private Tabpanel unconfirmedPane = new Tabpanel();
private WListbox m_tableUnconfirmed = ListboxFactory.newDataTable();
private ListModelTable m_modelUnconfirmed = null;
private Tabpanel atpPane = new Tabpanel();
private WListbox m_tableAtp = ListboxFactory.newDataTable();
private ListModelTable m_modelAtp = null;
/**
* Ststic Init
*/
void jbInit() throws Exception
{
label.setText("Label");
Tabs tabs = new Tabs();
tabbox.appendChild(tabs);
Tabpanels tabpanels = new Tabpanels();
tabbox.appendChild(tabpanels);
tabs.appendChild(new Tab(Msg.getMsg(Env.getCtx(), "PriceHistory")));
tabs.appendChild(new Tab(Msg.translate(Env.getCtx(), "QtyReserved")));
tabs.appendChild(new Tab(Msg.translate(Env.getCtx(), "QtyOrdered")));
tabs.appendChild(new Tab(Msg.getMsg(Env.getCtx(), "QtyUnconfirmed")));
if (m_M_Product_ID != 0)
tabs.appendChild(new Tab(Msg.getMsg(Env.getCtx(), "ATP")));
pricePane.setHeight("100%");
pricePane.appendChild(m_tablePrice);
tabpanels.appendChild(pricePane);
reservedPane.setHeight("100%");
reservedPane.appendChild(m_tableReserved);
tabpanels.appendChild(reservedPane);
orderedPane.setHeight("100%");
orderedPane.appendChild(m_tableOrdered);
tabpanels.appendChild(orderedPane);
unconfirmedPane.setHeight("100%");
unconfirmedPane.appendChild(m_tableUnconfirmed);
tabpanels.appendChild(unconfirmedPane);
if (m_M_Product_ID != 0)
{
atpPane.setHeight("100%");
atpPane.appendChild(m_tableAtp);
tabpanels.appendChild(atpPane);
}
tabbox.setSelectedIndex(0);
tabbox.addEventListener(Events.ON_SELECT, this);
confirmPanel.addActionListener(this);
Borderlayout borderlayout = new Borderlayout();
borderlayout.setWidth("700px");
borderlayout.setHeight("400px");
borderlayout.setStyle("border: none; position: relative");
this.appendChild(borderlayout);
North north = new North();
north.setStyle("border: none");
borderlayout.appendChild(north);
north.appendChild(label);
Center center = new Center();
center.setStyle("border: none");
center.setAutoscroll(true);
center.setFlex(true);
borderlayout.appendChild(center);
center.appendChild(tabbox);
South south = new South();
south.setStyle("border: none");
borderlayout.appendChild(south);
south.appendChild(confirmPanel);
} // jbInit
/**
* 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(), "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, Double.class, true); // Quantity
m_tablePrice.setColumnClass(3, BigDecimal.class, true); // Discount (%) to limit precision
m_tablePrice.setColumnClass(4, String.class, true); // DocNo
m_tablePrice.setColumnClass(5, Timestamp.class, true); // Date
m_tablePrice.setColumnClass(6, String.class, true); // Org
//
m_tablePrice.autoSize();
//
return data.size() != 0;
} // dynInit
/**
* Get Info for Product for given Business Parner
*/
private Vector<Vector<Object>> queryProduct ()
{
String sql = "SELECT p.Name,l.PriceActual,l.PriceList,l.QtyInvoiced," // 1,2,3,4
+ "i.DateInvoiced,dt.PrintName || ' ' || i.DocumentNo As DocumentNo," // 5,6
+ "o.Name, " // 7
+ "NULL, i.M_PriceList_ID " // 8,9
+ "FROM C_Invoice i"
+ " INNER JOIN C_InvoiceLine l ON (i.C_Invoice_ID=l.C_Invoice_ID)"
+ " INNER JOIN C_DocType dt ON (i.C_DocType_ID=dt.C_DocType_ID)"
+ " INNER JOIN AD_Org o ON (i.AD_Org_ID=o.AD_Org_ID)"
+ " INNER JOIN M_Product p ON (l.M_Product_ID=p.M_Product_ID) "
+ "WHERE i.C_BPartner_ID=? "
+ "ORDER BY i.DateInvoiced DESC";
Vector<Vector<Object>> data = fillTable (sql, m_C_BPartner_ID);
sql = "SELECT Name from C_BPartner WHERE C_BPartner_ID=?";
fillLabel (sql, m_C_BPartner_ID);
return data;
} // queryProduct
/**
* Get Info for Business Partners for given Product
*/
private Vector<Vector<Object>> queryBPartner ()
{
String sql = "SELECT bp.Name,l.PriceActual,l.PriceList,l.QtyInvoiced," // 1,2,3,4
+ "i.DateInvoiced,dt.PrintName || ' ' || i.DocumentNo As DocumentNo," // 5,6
+ "o.Name," // 7
+ "NULL, i.M_PriceList_ID" // 8,9
+ " FROM C_Invoice i"
+ " INNER JOIN C_InvoiceLine l ON (i.C_Invoice_ID=l.C_Invoice_ID)"
+ " INNER JOIN C_DocType dt ON (i.C_DocType_ID=dt.C_DocType_ID)"
+ " INNER JOIN AD_Org o ON (i.AD_Org_ID=o.AD_Org_ID)"
+ " INNER JOIN C_BPartner bp ON (i.C_BPartner_ID=bp.C_BPartner_ID) "
+ "WHERE l.M_Product_ID=? "
+ "ORDER BY i.DateInvoiced DESC";
Vector<Vector<Object>> data = fillTable (sql, m_M_Product_ID);
sql = "SELECT Name from M_Product WHERE M_Product_ID=?";
fillLabel (sql, m_M_Product_ID);
return data;
} // qyeryBPartner
/**
* Fill Table
*/
private Vector<Vector<Object>> fillTable (String sql, int parameter)
{
log.fine(sql + "; Parameter=" + parameter);
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);
// 0-Name, 1-PriceActual, 2-QtyInvoiced, 3-Discount, 4-DocumentNo, 5-DateInvoiced
line.add(rs.getString(1)); // Name
line.add(rs.getBigDecimal(2)); // Price
line.add(new Double(rs.getDouble(4))); // Qty
BigDecimal discountBD = rs.getBigDecimal(8);
if (discountBD == null) {
double priceList = rs.getDouble(3);
double priceActual = rs.getDouble(2);
if (priceList != 0) {
discountBD = new BigDecimal((priceList - priceActual)/priceList * 100);
// Rounding:
int precision = MPriceList.getStandardPrecision(Env.getCtx(), rs.getInt(9));
if (discountBD.scale() > precision)
discountBD = discountBD.setScale(precision, RoundingMode.HALF_UP);
}
else
discountBD = Env.ZERO;
}
line.add(discountBD); // Discount
line.add(rs.getString(6)); // DocNo
line.add(rs.getTimestamp(5)); // Date
line.add(rs.getString(7)); // Org/Warehouse
data.add(line);
}
}
catch (SQLException e)
{
log.log(Level.SEVERE, sql, e);
}
finally {
DB.close(rs, pstmt);
rs = null; pstmt = null;
}
log.fine("#" + data.size());
return data;
} // fillTable
/**
* Set Label
* to product or bp name
*/
private void fillLabel (String sql, int parameter)
{
log.fine(sql + "; Parameter=" + parameter);
String retValue = DB.getSQLValueString(null, sql, parameter);
if (retValue != null)
label.setText(retValue);
} // fillLabel
public void onEvent(Event e) throws Exception {
Component component = e.getTarget();
if (component.equals(confirmPanel.getButton(ConfirmPanel.A_OK)))
dispose();
else if(component instanceof Tab)
{
if (tabbox.getSelectedIndex() == 1)
initReservedOrderedTab(true);
else if (tabbox.getSelectedIndex() == 2)
initReservedOrderedTab(false);
else if (tabbox.getSelectedIndex() == 3)
initUnconfirmedTab();
else if (tabbox.getSelectedIndex() == 4)
initAtpTab();
}
}
/**
* 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(), 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,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) "
+ "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,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) "
+ "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, Double.class, true); // Quantity
m_tableReserved.setColumnClass(3, BigDecimal.class, true); // Discount (%)
m_tableReserved.setColumnClass(4, String.class, true); // DocNo
m_tableReserved.setColumnClass(5, Timestamp.class, true); // Date
m_tableReserved.setColumnClass(6, 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, Double.class, true); // Quantity
m_tableOrdered.setColumnClass(3, BigDecimal.class, true); // Discount (%)
m_tableOrdered.setColumnClass(4, String.class, true); // DocNo
m_tableOrdered.setColumnClass(5, Timestamp.class, true); // Date
m_tableOrdered.setColumnClass(6, String.class, true); // Warehouse
//
m_tableOrdered.autoSize();
}
} // initReservedOrderedTab
/**
* 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;
}
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 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
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 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 (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
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
} // InvoiceHistory