/******************************************************************************
 * 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.adempiere.webui.info.InfoProductWindow;
import org.adempiere.webui.theme.ThemeManager;
import org.adempiere.webui.util.ZKUpdateUtil;
import org.compiere.model.MDocType;
import org.compiere.model.MPriceList;
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.zul.Borderlayout;
import org.zkoss.zul.Center;
import org.zkoss.zul.North;
import org.zkoss.zul.South;

/**
 * Price History for BPartner/Product
 * @author <a href="mailto:elaine.tan@idalica.com">Elaine</a>
 */
public class InvoiceHistory extends Window implements EventListener<Event>
{
	/**
	 * generated serial id
	 */
	private static final long serialVersionUID = 8742214467478030802L;

	protected boolean showDetailATP = false;

	/**
	 *	Show History
	 *  @param parent
	 *	@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"));
		if (log.isLoggable(Level.CONFIG)) 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);
		}
		
		this.setSclass("popup-dialog invoice-history-dialog");
		AEnv.showCenterWindow(parent, this);
		if (parent instanceof InfoProductWindow)
			showDetailATP = ((InfoProductWindow)parent).isShowDetailATP();
	}	//	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 final 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;
	
	/**
	 * Layout window
	 */
	protected 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")));
		
		ZKUpdateUtil.setHeight(pricePane, "100%");
		pricePane.appendChild(m_tablePrice);
		tabpanels.appendChild(pricePane);
		
		ZKUpdateUtil.setHeight(reservedPane, "100%");
		reservedPane.appendChild(m_tableReserved);
		tabpanels.appendChild(reservedPane);
		
		ZKUpdateUtil.setHeight(orderedPane, "100%");
		orderedPane.appendChild(m_tableOrdered);
		tabpanels.appendChild(orderedPane);
		
		ZKUpdateUtil.setHeight(unconfirmedPane, "100%");
		unconfirmedPane.appendChild(m_tableUnconfirmed);
		tabpanels.appendChild(unconfirmedPane);
		
		if (m_M_Product_ID != 0)
		{
			ZKUpdateUtil.setHeight(atpPane, "100%");
			atpPane.appendChild(m_tableAtp);
			tabpanels.appendChild(atpPane);
		}
		
		tabbox.setSelectedIndex(0);
		tabbox.addEventListener(Events.ON_SELECT, this);
		confirmPanel.addActionListener(this);
        
		Borderlayout borderlayout = new Borderlayout();	
		if (!ThemeManager.isUseCSSForWindowSize())
		{
			ZKUpdateUtil.setWindowWidthX(this, 700);
			ZKUpdateUtil.setWindowHeightX(this, 400);
		}
		else
		{
			addCallback(AFTER_PAGE_ATTACHED, t-> {
				ZKUpdateUtil.setCSSHeight(this);
				ZKUpdateUtil.setCSSWidth(this);
				this.invalidate();
			});
		}
        borderlayout.setStyle("border: none; position: relative");
		this.appendChild(borderlayout);
		this.setClosable(true);
		this.setSizable(true);
		this.setMaximizable(true);
		this.setBorder("normal");
		
		North north = new North();
		north.setStyle("border: none");
		borderlayout.appendChild(north);
		north.appendChild(label);
		
		Center center = new Center();
		center.setSclass("dialog-content");
		center.setAutoscroll(true);
        borderlayout.appendChild(center);
		center.appendChild(tabbox);
		ZKUpdateUtil.setVflex(tabbox, "1");
		ZKUpdateUtil.setHflex(tabbox, "1");
		
		South south = new South();
		south.setSclass("dialog-footer");
		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(), "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
		
		return data.size() != 0;
	}	//	dynInit


	/**
	 * Get invoiced product details for a given Business Partner.
	 * @return invoiced product details
	 */
	private Vector<Vector<Object>> queryProduct ()
	{
		String sql = "SELECT p.Name,l.PriceActual,c.Iso_Code,l.PriceList,l.QtyInvoiced,"		//  1,2,3,4,5
			+ "i.DateInvoiced,dt.PrintName || ' ' || i.DocumentNo As DocumentNo,"	//  6,7
			+ "o.Name, "															//  8
			+ "NULL, i.M_PriceList_ID "												//  9,10
			+ "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) "
			+ " INNER JOIN C_Currency c ON (i.C_Currency_ID=c.C_Currency_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 invoiced Business Partners details for a given Product
	 * @return invoiced Business Partners details
	 */
	private Vector<Vector<Object>> queryBPartner ()
	{
		String sql = "SELECT bp.Name,l.PriceActual,c.Iso_Code,l.PriceList,l.QtyInvoiced,"		//	1,2,3,4,5
			+ "i.DateInvoiced,dt.PrintName || ' ' || i.DocumentNo As DocumentNo,"	//	6,7
			+ "o.Name,"																//  8
			+ "NULL, i.M_PriceList_ID"												//  9,10
			+ " 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) "
			+ " INNER JOIN C_Currency c ON (i.C_Currency_ID=c.C_Currency_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 list
	 * @param sql
	 * @param parameter
	 * @return List of records
	 */
	private Vector<Vector<Object>> fillTable (String sql, int parameter)
	{
		if (log.isLoggable(Level.FINE)) 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(rs.getString(3));  //	Currency
				line.add(Double.valueOf(rs.getDouble(5)));      //  Qty
				BigDecimal discountBD = rs.getBigDecimal(9);
				if (discountBD == null) {
					double priceList = rs.getDouble(4);
					double priceActual = rs.getDouble(2);
					if (priceList != 0) {
						discountBD = BigDecimal.valueOf((priceList - priceActual)/priceList * 100);
						// Rounding:
						int precision = MPriceList.getStandardPrecision(Env.getCtx(), rs.getInt(10));
						if (discountBD.scale() > precision)
							discountBD = discountBD.setScale(precision, RoundingMode.HALF_UP);
					}
					else
						discountBD = Env.ZERO;
				}
				line.add(discountBD);  //  Discount
				line.add(rs.getString(7));      //  DocNo
				line.add(rs.getTimestamp(6));   //  Date
				line.add(rs.getString(8));		//	Org/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());
		return data;
	}	//	fillTable

	/**
	 * Set Label to product or bp name.
	 * @param sql
	 * @param parameter
	 */
	private void fillLabel (String sql, int parameter)
	{
		if (log.isLoggable(Level.FINE)) log.fine(sql + "; Parameter=" + parameter);
		String retValue = DB.getSQLValueString(null, sql, parameter);
		if (retValue != null)
			label.setText(retValue);
	}	//	fillLabel

	@Override
	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(), "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
		}
		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
		}

	}	//	initReservedOrderedTab

	
	/**
	 *	Query Unconfirmed (Draft/In Progress M_InOut)
	 */
	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(Double.valueOf(rs.getDouble(2)));  //  Qty
				line.add(rs.getTimestamp(3));   		//  Date
				line.add(Boolean.valueOf("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
	}	//	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
		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(Double.valueOf(qtyOnHand));  		//  Qty
				line.add(null);							//  BPartner
				line.add(Double.valueOf(rs.getDouble(3)));  //  QtyOrdered
				line.add(Double.valueOf(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 = Double.valueOf(oq);
					qty += oq;
				}
				else
				{
					qtyReserved = Double.valueOf(oq);
					qty -= oq;
				}
				line.add(Double.valueOf(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
	}	//	initAtpTab	
}	//	InvoiceHistory