/****************************************************************************** * Copyright (C) 2008 Elaine Tan * * 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.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Timestamp; import java.util.logging.Level; import org.adempiere.webui.apps.AEnv; import org.adempiere.webui.component.Checkbox; import org.adempiere.webui.component.ConfirmPanel; import org.adempiere.webui.component.WListbox; import org.adempiere.webui.component.Window; import org.adempiere.webui.event.WTableModelEvent; import org.adempiere.webui.event.WTableModelListener; import org.compiere.apps.search.PAttributeInstance; import org.compiere.minigrid.ColumnInfo; import org.compiere.minigrid.IDColumn; import org.compiere.util.CLogger; import org.compiere.util.DB; import org.compiere.util.Env; import org.compiere.util.KeyNamePair; import org.compiere.util.Msg; import org.zkoss.zk.ui.event.Event; import org.zkoss.zk.ui.event.EventListener; import org.zkoss.zkex.zul.Borderlayout; import org.zkoss.zkex.zul.Center; import org.zkoss.zkex.zul.North; import org.zkoss.zkex.zul.South; import org.zkoss.zul.Div; /** * Display Product Attribute Instance Info * This class is based on org.compiere.apps.search.PAttributeInstance written by Jorg Janke * @author Elaine * */ public class InfoPAttributeInstancePanel extends Window implements EventListener, WTableModelListener { /** * */ private static final long serialVersionUID = -2883260173499157121L; /** * Constructor * @param parent frame parent * @param title title * @param M_Warehouse_ID warehouse key name pair * @param M_Locator_ID locator * @param M_Product_ID product key name pair * @param C_BPartner_ID bp */ public InfoPAttributeInstancePanel(Window parent, String title, int M_Warehouse_ID, int M_Locator_ID, int M_Product_ID, int C_BPartner_ID) { super(); setTitle(Msg.getMsg(Env.getCtx(), "PAttributeInstance")); init (M_Warehouse_ID, M_Locator_ID, M_Product_ID, C_BPartner_ID); AEnv.showCenterWindow(parent, this); } // PAttributeInstance /** * Initialization * @param M_Warehouse_ID wh * @param M_Locator_ID loc * @param M_Product_ID product * @param C_BPartner_ID partner */ private void init (int M_Warehouse_ID, int M_Locator_ID, int M_Product_ID, int C_BPartner_ID) { log.info("M_Warehouse_ID=" + M_Warehouse_ID + ", M_Locator_ID=" + M_Locator_ID + ", M_Product_ID=" + M_Product_ID); m_M_Warehouse_ID = M_Warehouse_ID; m_M_Locator_ID = M_Locator_ID; m_M_Product_ID = M_Product_ID; try { jbInit(); dynInit(C_BPartner_ID); } catch (Exception e) { log.log(Level.SEVERE, "", e); } } // init private ConfirmPanel confirmPanel = new ConfirmPanel (true); private Checkbox showAll = new Checkbox(); // private WListbox m_table = new WListbox(); // Parameter private int m_M_Warehouse_ID; private int m_M_Locator_ID; private int m_M_Product_ID; // private int m_M_AttributeSetInstance_ID = -1; private String m_M_AttributeSetInstanceName = null; private String m_sql; /** Logger */ private static CLogger log = CLogger.getCLogger(PAttributeInstance.class); /** * Static Init * @throws Exception */ private void jbInit() throws Exception { showAll.setText(Msg.getMsg(Env.getCtx(), "ShowAll")); Borderlayout borderlayout = new Borderlayout(); borderlayout.setWidth("700px"); borderlayout.setHeight("400px"); borderlayout.setStyle("border: none; position: relative"); this.appendChild(borderlayout); North north = new North(); borderlayout.appendChild(north); Div div = new Div(); div.setAlign("right"); div.appendChild(showAll); north.appendChild(div); Center center = new Center(); center.setAutoscroll(true); center.setFlex(true); borderlayout.appendChild(center); center.appendChild(m_table); South south = new South(); borderlayout.appendChild(south); south.appendChild(confirmPanel); // ConfirmPanel confirmPanel.addActionListener(this); } // jbInit /** Table Column Layout Info */ private static ColumnInfo[] s_layout = new ColumnInfo[] { new ColumnInfo(" ", "s.M_AttributeSetInstance_ID", IDColumn.class), new ColumnInfo(Msg.translate(Env.getCtx(), "Description"), "asi.Description", String.class), new ColumnInfo(Msg.translate(Env.getCtx(), "Lot"), "asi.Lot", String.class), new ColumnInfo(Msg.translate(Env.getCtx(), "SerNo"), "asi.SerNo", String.class), new ColumnInfo(Msg.translate(Env.getCtx(), "GuaranteeDate"), "asi.GuaranteeDate", Timestamp.class), new ColumnInfo(Msg.translate(Env.getCtx(), "M_Locator_ID"), "l.Value", KeyNamePair.class, "s.M_Locator_ID"), new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOnHand"), "s.QtyOnHand", Double.class), new ColumnInfo(Msg.translate(Env.getCtx(), "QtyReserved"), "s.QtyReserved", Double.class), new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOrdered"), "s.QtyOrdered", Double.class), // See RV_Storage new ColumnInfo(Msg.translate(Env.getCtx(), "GoodForDays"), "(daysbetween(asi.GuaranteeDate, SYSDATE))-p.GuaranteeDaysMin", Integer.class, true, true, null), new ColumnInfo(Msg.translate(Env.getCtx(), "ShelfLifeDays"), "daysbetween(asi.GuaranteeDate, SYSDATE)", Integer.class), new ColumnInfo(Msg.translate(Env.getCtx(), "ShelfLifeRemainingPct"), "CASE WHEN p.GuaranteeDays > 0 THEN TRUNC(((daysbetween(asi.GuaranteeDate, SYSDATE))/p.GuaranteeDays)*100) ELSE 0 END", Integer.class), }; /** From Clause */ private static String s_sqlFrom = "M_Storage s" + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID)" + " INNER JOIN M_Product p ON (s.M_Product_ID=p.M_Product_ID)" + " LEFT OUTER JOIN M_AttributeSetInstance asi ON (s.M_AttributeSetInstance_ID=asi.M_AttributeSetInstance_ID)"; /** Where Clause */ private static String s_sqlWhere = "s.M_Product_ID=? AND l.M_Warehouse_ID=?"; private static String s_sqlWhereWithoutWarehouse = " s.M_Product_ID=?"; private String m_sqlNonZero = " AND (s.QtyOnHand<>0 OR s.QtyReserved<>0 OR s.QtyOrdered<>0)"; private String m_sqlMinLife = ""; /** * Dynamic Init * @param C_BPartner_ID BP */ private void dynInit(int C_BPartner_ID) { log.config("C_BPartner_ID=" + C_BPartner_ID); if (C_BPartner_ID != 0) { int ShelfLifeMinPct = 0; int ShelfLifeMinDays = 0; String sql = "SELECT bp.ShelfLifeMinPct, bpp.ShelfLifeMinPct, bpp.ShelfLifeMinDays " + "FROM C_BPartner bp " + " LEFT OUTER JOIN C_BPartner_Product bpp" + " ON (bp.C_BPartner_ID=bpp.C_BPartner_ID AND bpp.M_Product_ID=?) " + "WHERE bp.C_BPartner_ID=?"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, null); pstmt.setInt(1, m_M_Product_ID); pstmt.setInt(2, C_BPartner_ID); rs = pstmt.executeQuery(); if (rs.next()) { ShelfLifeMinPct = rs.getInt(1); // BP int pct = rs.getInt(2); // BP_P if (pct > 0) // overwrite ShelfLifeMinDays = pct; ShelfLifeMinDays = rs.getInt(3); } } catch (Exception e) { log.log(Level.SEVERE, sql, e); } finally { DB.close(rs, pstmt); rs = null; pstmt = null; } if (ShelfLifeMinPct > 0) { m_sqlMinLife = " AND COALESCE(TRUNC(((daysbetween(asi.GuaranteeDate, SYSDATE))/p.GuaranteeDays)*100),0)>=" + ShelfLifeMinPct; log.config( "PAttributeInstance.dynInit - ShelfLifeMinPct=" + ShelfLifeMinPct); } if (ShelfLifeMinDays > 0) { m_sqlMinLife += " AND COALESCE((daysbetween(asi.GuaranteeDate, SYSDATE)),0)>=" + ShelfLifeMinDays; log.config( "PAttributeInstance.dynInit - ShelfLifeMinDays=" + ShelfLifeMinDays); } } // BPartner != 0 m_sql = m_table.prepareTable (s_layout, s_sqlFrom, m_M_Warehouse_ID == 0 ? s_sqlWhereWithoutWarehouse : s_sqlWhere, false, "s") + " ORDER BY asi.GuaranteeDate, s.QtyOnHand"; // oldest, smallest first // m_table.setMultiSelection(false); m_table.getModel().addTableModelListener(this); // refresh(); } // dynInit /** * Refresh Query */ private void refresh() { String sql = m_sql; int pos = m_sql.lastIndexOf(" ORDER BY "); if (!showAll.isSelected()) { sql = m_sql.substring(0, pos) + m_sqlNonZero; if (m_sqlMinLife.length() > 0) sql += m_sqlMinLife; sql += m_sql.substring(pos); } // log.finest(sql); 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); rs = pstmt.executeQuery(); m_table.loadTable(rs); } catch (Exception e) { log.log(Level.SEVERE, sql, e); } finally { DB.close(rs, pstmt); rs = null; pstmt = null; } enableButtons(); } // refresh /** * Action Listener * @param e event */ public void onEvent(Event e) throws Exception { if (e.getTarget().getId().equals(ConfirmPanel.A_OK)) { dispose(); } else if (e.getTarget().getId().equals(ConfirmPanel.A_CANCEL)) { dispose(); m_M_AttributeSetInstance_ID = -1; m_M_AttributeSetInstanceName = null; } else if (e.getTarget() == showAll) { refresh(); } } /** * Table selection changed * @param e event */ public void tableChanged(WTableModelEvent e) { enableButtons(); } /** * Enable/Set Buttons and set ID */ private void enableButtons() { m_M_AttributeSetInstance_ID = -1; m_M_AttributeSetInstanceName = null; m_M_Locator_ID = 0; int row = m_table.getSelectedRow(); boolean enabled = row != -1; if (enabled) { Integer ID = m_table.getSelectedRowKey(); if (ID != null) { m_M_AttributeSetInstance_ID = ID.intValue(); m_M_AttributeSetInstanceName = (String)m_table.getValueAt(row, 1); // Object oo = m_table.getValueAt(row, 5); if (oo instanceof KeyNamePair) { KeyNamePair pp = (KeyNamePair)oo; m_M_Locator_ID = pp.getKey(); } } } confirmPanel.getOKButton().setEnabled(enabled); log.fine("M_AttributeSetInstance_ID=" + m_M_AttributeSetInstance_ID + " - " + m_M_AttributeSetInstanceName + "; M_Locator_ID=" + m_M_Locator_ID); } // enableButtons /** * Get Attribute Set Instance * @return M_AttributeSetInstance_ID or -1 */ public int getM_AttributeSetInstance_ID() { return m_M_AttributeSetInstance_ID; } // getM_AttributeSetInstance_ID /** * Get Instance Name * @return Instance Name */ public String getM_AttributeSetInstanceName() { return m_M_AttributeSetInstanceName; } // getM_AttributeSetInstanceName /** * Get Locator * @return M_Locator_ID or 0 */ public int getM_Locator_ID() { return m_M_Locator_ID; } // getM_Locator_ID } // PAttributeInstance