Java > Open Source Codes > org > compiere > grid > ed > VLookup


1 /******************************************************************************
2  * The contents of this file are subject to the Compiere License Version 1.1
3  * ("License"); You may not use this file except in compliance with the License
4  * You may obtain a copy of the License at http://www.compiere.org/license.html
5  * Software distributed under the License is distributed on an "AS IS" basis,
6  * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
7  * the specific language governing rights and limitations under the License.
8  * The Original Code is Compiere ERP & CRM Business Solution
9  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
10  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts
11  * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved.
12  * Contributor(s): ______________________________________.
13  *****************************************************************************/
14 package org.compiere.grid.ed;
15
16 import java.awt.*;
17 import java.awt.event.*;
18 import javax.swing.*;
19 import javax.swing.table.*;
20 import javax.swing.event.*;
21 import java.text.*;
22 import java.util.*;
23 import java.math.*;
24 import java.beans.*;
25 import java.sql.*;
26
27 import org.compiere.apps.*;
28 import org.compiere.util.*;
29 import org.compiere.model.*;
30 import org.compiere.apps.search.*;
31 import org.compiere.plaf.*;
32 import org.compiere.swing.*;
33
34 /**
35  * Lookup Field.
36  * <p>
37  * When r/o - display a Label
38  * When STABLE - display a ComboBox
39  * Otherwise show Selection Dialog
40  * <p>
41  * Sepecial handling of BPartner and Product
42  *
43  * @author Jorg Janke
44  * @version $Id: VLookup.java,v 1.49 2003/11/06 07:08:28 jjanke Exp $
45  */
46 public class VLookup extends JComponent
47     implements VEditor, ActionListener, FocusListener
48 {
49     /**
50      * Create BPartner Lookup
51      * @param WindowNo window
52      * @return VLookup
53      */
54     public static VLookup createBPartner (int WindowNo)
55     {
56         int AD_Column_ID = 3499; // C_Invoice.C_BPartner_ID
57 try
58         {
59             Lookup lookup = MLookupFactory.get (Env.getCtx(), WindowNo, 0, AD_Column_ID, DisplayType.Search);
60             return new VLookup ("C_BPartner_ID", false, false, true, lookup, DisplayType.Search, WindowNo);
61         }
62         catch (Exception e)
63         {
64             Log.error("VLookup.createBPartner", e);
65         }
66         return null;
67     } // createBPartner
68
69     /**
70      * Create Product Lookup
71      * @param WindowNo window
72      * @return VLookup
73      */
74     public static VLookup createProduct (int WindowNo)
75     {
76         int AD_Column_ID = 3840; // C_InvoiceLine.M_Product_ID
77 try
78         {
79             Lookup lookup = MLookupFactory.get (Env.getCtx(), WindowNo, 0, AD_Column_ID, DisplayType.Search);
80             return new VLookup ("M_Product_ID", false, false, true, lookup, DisplayType.Search, WindowNo);
81         }
82         catch (Exception e)
83         {
84             Log.error("VLookup.createProduct", e);
85         }
86         return null;
87     } // createProduct
88
89     /*************************************************************************/
90
91     /**
92      * IDE Default Constructor
93      */
94     public VLookup()
95     {
96         this("Lookup", false, false, true, null, 0, 0);
97     } // VLookup
98
99
100     /**
101      * Detail Constructor
102      *
103      * @param columnName column
104      * @param mandatory mandatory
105      * @param isReadOnly read only
106      * @param isUpdateable updateable
107      * @param lookup lookup
108      * @param displayType display type
109      * @param WindowNo window no
110      */
111     public VLookup (String columnName, boolean mandatory, boolean isReadOnly, boolean isUpdateable,
112         Lookup lookup, int displayType, int WindowNo)
113     {
114         super();
115         super.setName(columnName);
116         m_combo.setName(columnName);
117         m_columnName = columnName;
118         m_lookup = lookup;
119         if (lookup == null)
120             Log.trace(Log.l3_Util, "VLookup", "Lookup is NULL = " + columnName);
121         setMandatory(mandatory);
122         m_displayType = displayType;
123         m_WindowNo = WindowNo; // for Info
124 //
125 setLayout(new BorderLayout());
126         VLookup_mouseAdapter mouse = new VLookup_mouseAdapter(this); // popup
127
128         // *** Text & Button ***
129 m_text.addActionListener(this);
130         m_text.addMouseListener(mouse);
131         // Button
132 m_button.addActionListener(this);
133         m_button.addMouseListener(mouse);
134         m_button.setFocusable(false); // don't focus when tabbing
135 if (columnName.equals("C_BPartner_ID"))
136             m_button.setIcon(Env.getImageIcon("BPartner10.gif"));
137         else if (columnName.equals("M_Product_ID"))
138             m_button.setIcon(Env.getImageIcon("Product10.gif"));
139         else
140             m_button.setIcon(Env.getImageIcon("PickOpen10.gif"));
141
142         // *** VComboBox ***
143 if (m_lookup != null && displayType != DisplayType.Search) // No Search
144 {
145             // Memory Leak after executing the next two lines ??
146 m_lookup.fillComboBox (isMandatory(), false, false, false);
147             m_combo.setModel(m_lookup);
148             //
149 m_combo.addActionListener(this); // Selection
150 m_combo.addMouseListener(mouse); // popup
151 // FocusListener to refresh selection before opening
152 m_combo.addFocusListener(this);
153         }
154
155         setUI (true);
156         // ReadWrite - decides what components to show
157 if (isReadOnly || !isUpdateable || m_lookup == null)
158             setReadWrite(false);
159         else
160             setReadWrite(true);
161
162         // Popup
163 if (m_lookup != null)
164         {
165             if ((displayType == DisplayType.List && Env.getContextAsInt(Env.getCtx(), "#AD_Role_ID") == 0)
166                 || displayType != DisplayType.List) // only system admins can change lists, so no need to zoom for others
167 {
168                 mZoom = new JMenuItem(Msg.getMsg(Env.getCtx(), "Zoom"), Env.getImageIcon("Zoom16.gif"));
169                 mZoom.addActionListener(this);
170                 popupMenu.add(mZoom);
171             }
172             mRefresh = new JMenuItem(Msg.getMsg(Env.getCtx(), "Refresh"), Env.getImageIcon("Refresh16.gif"));
173             mRefresh.addActionListener(this);
174             popupMenu.add(mRefresh);
175         }
176         // VBPartner quick entry link
177 if (columnName.equals("C_BPartner_ID"))
178         {
179             mBPartnerNew = new JMenuItem (Msg.getMsg(Env.getCtx(), "New"), Env.getImageIcon("InfoBPartner16.gif"));
180             mBPartnerNew.addActionListener(this);
181             popupMenu.add(mBPartnerNew);
182             mBPartnerUpd = new JMenuItem (Msg.getMsg(Env.getCtx(), "Update"), Env.getImageIcon("InfoBPartner16.gif"));
183             mBPartnerUpd.addActionListener(this);
184             popupMenu.add(mBPartnerUpd);
185         }
186         //
187 if (m_lookup != null && m_lookup.getZoom() == 0)
188             mZoom.setEnabled(false);
189     } // VLookup
190
191     /**
192      * Dispose
193      */
194     public void dispose()
195     {
196         m_text = null;
197         m_button = null;
198         m_lookup = null;
199         m_mField = null;
200         m_combo.removeActionListener(this);
201         m_combo.setModel(new DefaultComboBoxModel()); // remove reference
202 // m_combo.removeAllItems();
203 m_combo = null;
204     } // dispose
205
206     /** Display Length for Lookups (16) */
207     public final static int DISPLAY_LENGTH = 16;
208
209     /** Search: The Editable Text Field */
210     private CTextField m_text = new CTextField (DISPLAY_LENGTH);
211     /** Search: The Button to open Editor */
212     private CButton m_button = new CButton();
213     /** The Combo Box if not a Search Lookup */
214     private VComboBox m_combo = new VComboBox();
215     /** Indicator that value is being set */
216     private volatile boolean m_settingValue = false;
217     private volatile boolean m_settingFocus = false;
218     /** Indicator that Lookup has focus */
219     private volatile boolean m_haveFocus = false;
220     /** Indicator - inserting new value */
221     private volatile boolean m_inserting = false;
222
223     //
224 private String m_columnName;
225     private Lookup m_lookup;
226     private int m_displayType;
227     private int m_WindowNo;
228
229     private boolean m_comboActive = true;
230     private Object m_value;
231
232     // Popup
233 JPopupMenu popupMenu = new JPopupMenu();
234     private JMenuItem mZoom;
235     private JMenuItem mRefresh;
236     private JMenuItem mBPartnerNew;
237     private JMenuItem mBPartnerUpd;
238
239     private MField m_mField = null;
240
241     /**
242      * Set Content and Size of Compoments
243      * @param initial if true, size and margins will be set
244      */
245     private void setUI (boolean initial)
246     {
247         if (initial)
248         {
249             Dimension size = m_text.getPreferredSize();
250             setPreferredSize(new Dimension(size)); // causes r/o to be the same length
251 m_combo.setPreferredSize(new Dimension(size));
252             //
253 m_text.setBorder(null);
254             Dimension bSize = new Dimension(size.height, size.height);
255             m_button.setPreferredSize (bSize);
256             m_button.setMargin(new Insets(0, 0, 0, 0));
257         }
258
259         // What to show
260 this.remove(m_combo);
261         this.remove(m_button);
262         this.remove(m_text);
263         //
264 if (!isReadWrite()) // r/o - show text only
265 {
266             LookAndFeel.installBorder(this, "TextField.border");
267             this.add(m_text, BorderLayout.CENTER);
268             m_text.setReadWrite(false);
269             m_combo.setReadWrite(false);
270             m_comboActive = false;
271         }
272         else if (m_displayType != DisplayType.Search) // show combo if not Search
273 {
274             this.setBorder(null);
275             this.add(m_combo, BorderLayout.CENTER);
276             m_comboActive = true;
277         }
278         else // Search or unstable - show text & button
279 {
280             LookAndFeel.installBorder(this, "TextField.border");
281             this.add(m_text, BorderLayout.CENTER);
282             this.add(m_button, BorderLayout.EAST);
283             m_text.setReadWrite (true);
284             m_comboActive = false;
285         }
286     } // setUI
287
288     /**
289      * Set ReadWrite
290      * @param value ReadWrite
291      */
292     public void setReadWrite (boolean value)
293     {
294         boolean rw = value;
295         if (m_lookup == null)
296             rw = false;
297         if (m_combo.isReadWrite() != value)
298         {
299             m_combo.setReadWrite(rw);
300             setUI (false);
301             if (m_comboActive)
302                 setValue (m_value);
303         }
304     } // setReadWrite
305
306     /**
307      * IsEditable
308      * @return is lookup ReadWrite
309      */
310     public boolean isReadWrite()
311     {
312         return m_combo.isReadWrite();
313     } // isReadWrite
314
315     /**
316      * Set Mandatory (and back bolor)
317      * @param mandatory mandatory
318      */
319     public void setMandatory (boolean mandatory)
320     {
321         m_combo.setMandatory(mandatory);
322         m_text.setMandatory(mandatory);
323     } // setMandatory
324
325     /**
326      * Is it mandatory
327      * @return true if mandatory
328      */
329     public boolean isMandatory()
330     {
331         return m_combo.isMandatory();
332     } // isMandatory
333
334     /**
335      * Set Background
336      * @param color color
337      */
338     public void setBackground(Color color)
339     {
340         m_text.setBackground(color);
341         m_combo.setBackground(color);
342     } // setBackground
343
344     /**
345      * Set Background
346      * @param error error
347      */
348     public void setBackground (boolean error)
349     {
350         m_text.setBackground(error);
351         m_combo.setBackground(error);
352     } // setBackground
353
354     /**
355      * Set Foreground
356      * @param fg Foreground color
357      */
358     public void setForeground(Color fg)
359     {
360         m_text.setForeground(fg);
361         m_combo.setForeground(fg);
362     } // setForeground
363
364     /**
365      * Set Editor to value
366      * @param value new Value
367      */
368     public void setValue (Object value)
369     {
370         Log.trace(Log.l6_Database, "VLookup.setValue", m_columnName + "=" + value);
371         m_settingValue = true; // disable actions
372 m_value = value;
373
374         // Set both for switching
375 m_combo.setValue (value);
376         if (value == null)
377         {
378             m_text.setText (null);
379             m_settingValue = false;
380             return;
381         }
382         if (m_lookup == null)
383         {
384             m_text.setText (value.toString());
385             m_settingValue = false;
386             return;
387         }
388
389         // Set Display
390 String display = m_lookup.getDisplay(value);
391         boolean notFound = display.startsWith("<") && display.startsWith(">");
392         m_text.setText (display);
393         m_text.setCaretPosition (0); // show beginning
394
395         // Nothing showing in Combo and should be showing
396 if (m_combo.getSelectedItem() == null
397             && (m_comboActive || (m_inserting && m_displayType != DisplayType.Search)))
398         {
399             // lookup found nothing too
400 if (notFound)
401             {
402                 Log.trace(8, "VLookup.setValue - Not found (1)", display);
403                 // we may have a new value
404 m_lookup.refresh();
405                 m_combo.setValue (value);
406                 display = m_lookup.getDisplay(value);
407                 m_text.setText (display);
408                 m_text.setCaretPosition (0); // show beginning
409 notFound = display.startsWith("<") && display.endsWith(">");
410             }
411             if (notFound) // <key>
412 {
413                 m_value = null;
414                 actionCombo (null); // data binding
415 Log.trace(Log.l6_Database, "VLookup.setValue - not found - " + value);
416             }
417             // we have lookup
418 else if (m_combo.getSelectedItem() == null)
419             {
420                 NamePair pp = m_lookup.get(value);
421                 if (pp != null)
422                 {
423                     Log.trace (Log.l6_Database, "VLookup.setValue - added to combo - " + pp);
424                     // Add to Combo
425 m_combo.addItem (pp);
426                     m_combo.setValue (value);
427                 }
428             }
429             // Not in Lookup - set to Null
430 if (m_combo.getSelectedItem() == null)
431             {
432                 Log.trace(Log.l1_User, "VLookup.setValue - not in Lookup - set to NULL");
433                 actionCombo (null); // data binding (calls setValue again)
434 m_value = null;
435             }
436         }
437         m_settingValue = false;
438     } // setValue
439
440     /**
441      * Property Change Listener
442      * @param evt PropertyChangeEvent
443      */
444     public void propertyChange (PropertyChangeEvent evt)
445     {
446     // Log.trace(Log.l5_DData, "VLookup.propertyChange", evt);
447 if (evt.getPropertyName().equals(MField.PROPERTY))
448         {
449             m_inserting = MField.INSERTING.equals(evt.getOldValue());
450             setValue(evt.getNewValue());
451             m_inserting = false;
452         }
453     } // propertyChange
454
455     /**
456      * Return Editor value (Integer)
457      * @return value
458      */
459     public Object getValue()
460     {
461         if (m_comboActive)
462             return m_combo.getValue ();
463         return m_value;
464     } // getValue
465
466     /**
467      * Return editor display
468      * @return display value
469      */
470     public String getDisplay()
471     {
472         String retValue = null;
473         if (m_comboActive)
474             retValue = m_combo.getDisplay();
475         // check lookup
476 else if (m_lookup == null)
477             retValue = m_value.toString();
478         else
479             retValue = m_lookup.getDisplay(m_value);
480     // Log.trace(Log.l6_Database, "VLookup.getDisplay - " + retValue, "ComboActive=" + m_comboActive);
481 return retValue;
482     } // getDisplay
483
484     /**
485      * Set Field/WindowNo for ValuePreference
486      * @param mField Model Field for Lookup
487      */
488     public void setField (MField mField)
489     {
490         m_mField = mField;
491         if (m_mField != null)
492             ValuePreference.addMenu (this, popupMenu);
493     } // setField
494
495     /*************************************************************************/
496
497     /**
498      * Action Listener - data binding
499      * @param e ActionEvent
500      */
501     public void actionPerformed (ActionEvent e)
502     {
503         if (m_settingValue || m_settingFocus)
504             return;
505         Log.trace(Log.l4_Data, "VLookup.actionPerformed", e.getActionCommand() + ", ComboValue=" + m_combo.getSelectedItem());
506     // Log.trace(Log.l5_DData, "VLookupHash=" + this.hashCode());
507
508         // Preference
509 if (e.getActionCommand().equals(ValuePreference.NAME))
510         {
511             ValuePreference.start (m_mField, getValue(), getDisplay());
512             return;
513         }
514
515         // Combo Selection
516 else if (e.getSource() == m_combo)
517         {
518             Object value = getValue();
519             Object o = m_combo.getSelectedItem();
520             if (o != null)
521             {
522                 String s = o.toString();
523                 // don't allow selection of inactive
524 if (s.startsWith(MLookup.INACTIVE_S) && s.endsWith(MLookup.INACTIVE_E))
525                 {
526                     Log.trace(Log.l1_User, "VLookup.actionPerformed - Selection inactive set to NULL");
527                     value = null;
528                 }
529             }
530             actionCombo (value); // data binding
531 }
532         // Button pressed
533 else if (e.getSource() == m_button)
534             actionButton ("");
535         // Text entered
536 else if (e.getSource() == m_text)
537             actionText();
538
539         // Popup Menu
540 else if (e.getSource() == mZoom)
541             actionZoom();
542         else if (e.getSource() == mRefresh)
543             actionRefresh();
544         else if (e.getSource() == mBPartnerNew)
545             actionBPartner(true);
546         else if (e.getSource() == mBPartnerUpd)
547             actionBPartner(false);
548     } // actionPerformed
549
550     /**
551      * Action Listener Interface
552      * @param listener listener
553      */
554     public void addActionListener(ActionListener listener)
555     {
556         m_combo.addActionListener(listener);
557         m_text.addActionListener(listener);
558     } // addActionListener
559
560     /**
561      * Action - Combo.
562      * <br>
563      * == dataBinding == inform of new value
564      * <pre>
565      * VLookup.actionCombo
566      * GridController.vetoableChange
567      * MTable.setValueAt
568      * MField.setValue
569      * VLookup.setValue
570      * MTab.dataStatusChanged
571      * </pre>
572      * @param value new value
573      */
574     private void actionCombo (Object value)
575     {
576     // Log.trace(Log.l6_Database, "VLookup.actionCombo", value==null ? "null" : value.toString());
577 try
578         {
579             fireVetoableChange (m_columnName, null, value);
580         }
581         catch (PropertyVetoException pve)
582         {
583             Log.error("VLookup.actionCombo", pve);
584         }
585         // is the value updated ?
586 boolean updated = false;
587         if (value == null && m_value == null)
588             updated = true;
589         else if (value != null && value.equals(m_value))
590             updated = true;
591         if (!updated)
592         {
593             // happens if VLookup is used outside of APanel/GridController (no property listener)
594 Log.trace(Log.l6_Database, "VLookup.actionCombo - Value explicitly set - new=" + value + ", old=" + m_value);
595             setValue(value);
596         }
597     } // actionCombo
598
599
600     /**
601      * Action - Button.
602      * - Call Info
603      * @param queryValue initial query value
604      */
605     private void actionButton (String queryValue)
606     {
607         m_button.setEnabled(false); // disable double click
608 requestFocus(); // closes other editors
609 Frame frame = Env.getFrame(this);
610
611         /**
612          * Three return options:
613          * - Value Selected & OK pressed => store result => result has value
614          * - Cancel pressed => store null => result == null && cancelled
615          * - Window closed -> ignore => result == null && !cancalled
616          */
617         Object result = null;
618         boolean cancelled = false;
619         //
620 String col = m_lookup.getColumnName(); // fully qualified name
621 if (col.indexOf(".") != -1)
622             col = col.substring(col.indexOf(".")+1);
623         // Zoom / Validation
624 String whereClause = whereClause = getWhereClause();
625         //
626 Log.trace(Log.l5_DData, "VLookup.actionButton - " + col
627             + ", Zoom=" + m_lookup.getZoom()
628             + " (" + whereClause + ")");
629         //
630 boolean resetValue = false; // reset value so that is always treated as new entry
631 if (col.equals("M_Product_ID"))
632         {
633             // Reset
634 Env.setContext(Env.getCtx(), Env.WINDOW_INFO, Env.TAB_INFO, "M_Product_ID", "0");
635             Env.setContext(Env.getCtx(), Env.WINDOW_INFO, Env.TAB_INFO, "M_AttributeSetInstance_ID", "0");
636             // Replace Value with name if no value exists
637 if (queryValue.length() == 0 && m_text.getText().length() > 0)
638                 queryValue = "@" + m_text.getText() + "@"; // Name indicator - otherwise Value
639 int M_Warehouse_ID = Env.getContextAsInt(Env.getCtx(), m_WindowNo, "M_Warehouse_ID");
640             int M_PriceList_ID = Env.getContextAsInt(Env.getCtx(), m_WindowNo, "M_PriceList_ID");
641             // Show Info
642 InfoProduct ip = new InfoProduct (frame, true, m_WindowNo,
643                 M_Warehouse_ID, M_PriceList_ID, queryValue, false, whereClause);
644             ip.show();
645             cancelled = ip.isCancelled();
646             result = ip.getSelectedKey();
647             resetValue = true;
648         }
649         else if (col.equals("C_BPartner_ID"))
650         {
651             // Replace Value with name if no value exists
652 if (queryValue.length() == 0 && m_text.getText().length() > 0)
653                 queryValue = m_text.getText();
654             boolean isSOTrx = true; // default
655 if (Env.getContext(Env.getCtx(), m_WindowNo, "IsSOTrx").equals("N"))
656                 isSOTrx = false;
657             InfoBPartner ip = new InfoBPartner (frame, true, m_WindowNo,
658                 queryValue, isSOTrx, false, whereClause);
659             ip.show();
660             cancelled = ip.isCancelled();
661             result = ip.getSelectedKey();
662         }
663         else
664         {
665             if (m_tableName == null) // sets table anem & key column
666 getDirectAccessSQL("*");
667             Info ig = Info.create (frame, true, m_WindowNo, m_tableName, m_keyColumnName, queryValue, false, whereClause);
668             ig.show();
669             cancelled = ig.isCancelled();
670             result = ig.getSelectedKey();
671         }
672         // Result
673 if (result != null)
674         {
675             Log.trace(Log.l4_Data, "VLookupactionButton", "Result = " + result.toString() + " " + result.getClass().getName());
676             // make sure that value is in cache
677 m_lookup.getDirect(result, false, true);
678             if (resetValue)
679                 actionCombo (null);
680             actionCombo (result);
681         }
682         else if (cancelled)
683         {
684             Log.trace(Log.l4_Data, "VLookup.actionButton", "Result = null (cancelled)");
685             actionCombo (null);
686         }
687         else
688         {
689             Log.trace(Log.l4_Data, "VLookup.actionButton", "Result = null (not cancelled)");
690             setValue(m_value); // to re-display value
691 }
692         //
693 m_button.setEnabled(true);
694     } // actionButton
695
696     /**
697      * Get Where Clause
698      * @return where clause or ""
699      */
700     private String getWhereClause()
701     {
702         String whereClause = "";
703         if (m_lookup.getZoomQuery() != null)
704             whereClause = m_lookup.getZoomQuery().getWhereClause();
705         if (whereClause.length() == 0)
706             whereClause = m_lookup.getValidation();
707         return whereClause;
708     } // getWhereClause
709
710     /**
711      * Check, if data returns unique entry, otherwise involve Info via Button
712      */
713     private void actionText()
714     {
715         String text = m_text.getText();
716         // Nothing entered
717 if (text == null || text.length() == 0 || text.equals("%"))
718         {
719             actionButton(text);
720             return;
721         }
722         // Always like
723 if (!text.endsWith("%"))
724             text += "%";
725         text = text.toUpperCase();
726
727         String finalSQL = getDirectAccessSQL(text);
728         int id = 0;
729         try
730         {
731             PreparedStatement pstmt = DB.prepareStatement(finalSQL);
732             ResultSet rs = pstmt.executeQuery();
733             if (rs.next())
734             {
735                 id = rs.getInt(1); // first
736 if (rs.next())
737                     id = -1; // only if unique
738 }
739             rs.close();
740             pstmt.close();
741         }
742         catch (SQLException e)
743         {
744             Log.error("VLookup.actionText\nSQL=" + finalSQL, e);
745             id = -2;
746         }
747         Log.trace(Log.l5_DData, "VLookup.actionText ID => " + id);
748
749         // No (unique) result
750 if (id <= 0)
751         {
752             Log.trace(Log.l6_Database, finalSQL);
753             actionButton(m_text.getText());
754             return;
755         }
756         m_value = null; // forces re-display if value is unchanged but text updated and still unique
757 actionCombo (new Integer (id)); // data binding
758 } // actionText
759
760
761     private String m_tableName = null;
762     private String m_keyColumnName = null;
763
764     /**
765      * Generate Access SQL for Search.
766      * The SQL returns the ID of the value entered
767      * Also sets m_tableName and m_keyColumnName
768      * @param text upper like text
769      * @return sql or ""
770      * Example
771      * SELECT C_Payment_ID FROM C_Payment WHERE UPPER(DocumentNo) LIKE x OR ...
772      */
773     private String getDirectAccessSQL (String text)
774     {
775         StringBuffer sql = new StringBuffer ();
776         m_tableName = m_columnName.substring(0, m_columnName.length()-3);
777         m_keyColumnName = m_columnName;
778         //
779 if (m_columnName.equals("M_Product_ID"))
780         {
781             // Reset
782 Env.setContext(Env.getCtx(), Env.WINDOW_INFO, Env.TAB_INFO, "M_Product_ID", "0");
783             Env.setContext(Env.getCtx(), Env.WINDOW_INFO, Env.TAB_INFO, "M_AttributeSetInstance_ID", "0");
784             //
785 sql.append("SELECT M_Product_ID FROM M_Product WHERE UPPER(Value) LIKE ")
786                 .append(DB.TO_STRING(text))
787                 .append(" OR UPC LIKE ").append(DB.TO_STRING(text));
788         }
789         else if (m_columnName.equals("C_BPartner_ID"))
790         {
791             sql.append("SELECT C_BPartner_ID FROM C_BPartner WHERE UPPER(Value) LIKE ")
792                 .append(DB.TO_STRING(text))
793                 .append(" OR UPPER(Name) LIKE ").append(DB.TO_STRING(text));
794         }
795         else if (m_columnName.equals("C_Order_ID"))
796         {
797             sql.append("SELECT C_Order_ID FROM C_Order WHERE UPPER(DocumentNo) LIKE ")
798                 .append(DB.TO_STRING(text));
799         }
800         else if (m_columnName.equals("C_Invoice_ID"))
801         {
802             sql.append("SELECT C_Invoice_ID FROM C_Invoice WHERE UPPER(DocumentNo) LIKE ")
803                 .append(DB.TO_STRING(text));
804         }
805         else if (m_columnName.equals("M_InOut_ID"))
806         {
807             sql.append("SELECT M_InOut_ID FROM M_InOut WHERE UPPER(DocumentNo) LIKE ")
808                 .append(DB.TO_STRING(text));
809         }
810         else if (m_columnName.equals("C_Payment_ID"))
811         {
812             sql.append("SELECT C_Payment_ID FROM C_Payment WHERE UPPER(DocumentNo) LIKE ")
813                 .append(DB.TO_STRING(text));
814         }
815         else if (m_columnName.equals("GL_JournalBatch_ID"))
816         {
817             sql.append("SELECT GL_JournalBatch_ID FROM GL_JournalBatch WHERE UPPER(DocumentNo) LIKE ")
818                 .append(DB.TO_STRING(text));
819         }
820         /**
821         else if (m_columnName.equals("SalesRep_ID"))
822         {
823             sql.append("SELECT AD_User_ID FROM AD_User WHERE UPPER(Name) LIKE ")
824                 .append(DB.TO_STRING(text));
825             m_tableName = "AD_User";
826             m_keyColumnName = "AD_User_ID";
827         }
828         **/
829         // Predefined
830 if (sql.length() > 0)
831         {
832             String wc = getWhereClause();
833             if (wc != null && wc.length() > 0)
834                 sql.append(" AND ").append(wc);
835             sql.append(" AND IsActive='Y'");
836             // ***
837 return MRole.getDefault().addAccessSQL(sql.toString(),
838                 m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
839         }
840         
841         // Check if it is a Table Reference
842 if (m_lookup instanceof MLookup)
843         {
844             int AD_Reference_ID = ((MLookup)m_lookup).getAD_Reference_Value_ID();
845             if (AD_Reference_ID != 0)
846             {
847                 String query = "SELECT kc.ColumnName, dc.ColumnName, t.TableName "
848                     + "FROM AD_Ref_Table rt"
849                     + " INNER JOIN AD_Column kc ON (rt.AD_Key=kc.AD_Column_ID)"
850                     + " INNER JOIN AD_Column dc ON (rt.AD_Display=dc.AD_Column_ID)"
851                     + " INNER JOIN AD_Table t ON (rt.AD_Table_ID=t.AD_Table_ID) "
852                     + "WHERE rt.AD_Reference_ID=?";
853                 String displayColumnName = null;
854                 PreparedStatement pstmt = null;
855                 try
856                 {
857                     pstmt = DB.prepareCall(query);
858                     pstmt.setInt(1, AD_Reference_ID);
859                     ResultSet rs = pstmt.executeQuery();
860                     if (rs.next())
861                     {
862                         m_keyColumnName = rs.getString(1);
863                         displayColumnName = rs.getString(2);
864                         m_tableName = rs.getString(3);
865                     }
866                     rs.close();
867                     pstmt.close();
868                     pstmt = null;
869                 }
870                 catch (Exception e)
871                 {
872                     Log.error("VLookup.getDirectAccessSQL", e);
873                 }
874                 try
875                 {
876                     if (pstmt != null)
877                         pstmt.close();
878                     pstmt = null;
879                 }
880                 catch (Exception e)
881                 {
882                     pstmt = null;
883                 }
884                 if (displayColumnName != null)
885                 {
886                     sql = new StringBuffer ();
887                     sql.append("SELECT ").append(m_keyColumnName)
888                         .append(" FROM ").append(m_tableName)
889                         .append(" WHERE UPPER(").append(displayColumnName)
890                         .append(") LIKE ").append(DB.TO_STRING(text))
891                         .append(" AND IsActive='Y'");
892                     String wc = getWhereClause();
893                     if (wc != null && wc.length() > 0)
894                         sql.append(" AND ").append(wc);
895                     // ***
896 return MRole.getDefault().addAccessSQL(sql.toString(),
897                                 m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
898                 }
899             } // Table Reference
900 } // MLookup
901
902         /** Check Well Known Columns of Table - assumes TableDir **/
903         String query = "SELECT t.TableName, c.ColumnName "
904             + "FROM AD_Column c "
905             + " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID AND t.IsView='N') "
906             + "WHERE c.ColumnName IN ('DocumentNo', 'Value', 'Name')"
907             + " AND EXISTS (SELECT * FROM AD_Column cc WHERE cc.AD_Table_ID=t.AD_Table_ID"
908                 + " AND cc.IsKey='Y' AND cc.ColumnName=?)";
909         sql = new StringBuffer ();
910         PreparedStatement pstmt = null;
911         try
912         {
913             pstmt = DB.prepareStatement(query);
914             pstmt.setString(1, m_columnName);
915             ResultSet rs = pstmt.executeQuery();
916             while (rs.next())
917             {
918                 if (sql.length() != 0)
919                     sql.append(" OR ");
920                 m_tableName = rs.getString(1);
921                 sql.append("UPPER(").append(rs.getString(2)).append(") LIKE ").append(DB.TO_STRING(text));
922             }
923             rs.close();
924             pstmt.close();
925             pstmt = null;
926         }
927         catch (SQLException ex)
928         {
929             Log.error("VLookup.getDirectAccessSQL", ex);
930         }
931         try
932         {
933             if (pstmt != null)
934                 pstmt.close();
935         }
936         catch (SQLException ex1)
937         {
938         }
939         pstmt = null;
940         if (sql.length() == 0)
941             return "";
942         //
943 StringBuffer retValue = new StringBuffer ("SELECT ")
944             .append(m_columnName).append(" FROM ").append(m_tableName)
945             .append(" WHERE ").append(sql)
946             .append(" AND IsActive='Y'");
947         String wc = getWhereClause();
948         if (wc != null && wc.length() > 0)
949             retValue.append(" AND ").append(wc);
950         // ***
951 return MRole.getDefault().addAccessSQL(retValue.toString(),
952                     m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
953     } // getDirectAccessSQL
954
955
956     /**
957      * Action - Special BPartner Screen
958      * @param newRecord true if new record should be created
959      */
960     private void actionBPartner (boolean newRecord)
961     {
962         VBPartner vbp = new VBPartner (Env.getFrame(this), m_WindowNo);
963         int BPartner_ID = 0;
964         // if update, get current value
965 if (!newRecord)
966         {
967             if (m_value instanceof Integer )
968                 BPartner_ID = ((Integer )m_value).intValue();
969             else if (m_value != null)
970                 BPartner_ID = Integer.parseInt(m_value.toString());
971         }
972
973         vbp.loadBPartner (BPartner_ID);
974         vbp.show();
975         // get result
976 int result = vbp.getBPartner_ID();
977         if (result == 0 // 0 = not saved
978 && result == BPartner_ID) // the same
979 return;
980         // Maybe new BPartner - put in cache
981 m_lookup.getDirect(new Integer (result), false, true);
982
983         actionCombo (new Integer (result)); // data binding
984 } // actionBPartner
985
986     /**
987      * Action - Zoom
988      */
989     private void actionZoom()
990     {
991         if (m_lookup == null)
992             return;
993         //
994 String IsSOTrx = Env.getContext(Env.getCtx(), m_WindowNo, "IsSOTrx");
995         int AD_Window_ID = m_lookup.getZoom(IsSOTrx);
996         MQuery zoomQuery = m_lookup.getZoomQuery();
997         Log.trace(Log.l1_User, "VLookup.actionZoom - " + AD_Window_ID + " - IsSOTrx=" + IsSOTrx + " - Query=" + zoomQuery + " - Value=" + getValue());
998         // If not already exist
999 if (zoomQuery == null || (!zoomQuery.isActive() && getValue() != null))
1000        {
1001            zoomQuery = new MQuery();
1002            zoomQuery.addRestriction(m_columnName, MQuery.EQUAL, getValue());
1003        }
1004        //
1005 setCursor(Cursor.getDefaultCursor());
1006        setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1007        //
1008 AWindow frame = new AWindow();
1009        if (!frame.initWindow(AD_Window_ID, zoomQuery, IsSOTrx.equals("Y")))
1010        {
1011            setCursor(Cursor.getDefaultCursor());
1012            ValueNamePair pp = Log.retrieveError();
1013            String msg = pp==null ? "AccessTableNoView" : pp.getValue();
1014            ADialog.error(m_WindowNo, this, msg, pp==null ? "" : pp.getName());
1015        }
1016        else
1017            AEnv.showCenterScreen(frame);
1018            // async window - not able to get feedback
1019 frame = null;
1020        //
1021 setCursor(Cursor.getDefaultCursor());
1022    } // actionZoom
1023
1024    /**
1025     * Action - Refresh
1026     */
1027    private void actionRefresh()
1028    {
1029        if (m_lookup == null)
1030            return;
1031        //
1032 setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
1033        //
1034 Object obj = m_combo.getSelectedItem();
1035        Log.trace(Log.l1_User, "VLookup.actionRefresh - #" + m_lookup.getSize(), "Selected=" + obj);
1036        m_lookup.refresh();
1037        if (m_lookup.isValidated())
1038            m_lookup.fillComboBox(isMandatory(), false, false, false);
1039        else
1040            m_lookup.fillComboBox(isMandatory(), true, false, false);
1041        m_combo.setSelectedItem(obj);
1042    // m_combo.revalidate();
1043 //
1044 setCursor(Cursor.getDefaultCursor());
1045        Log.trace(Log.l1_User, "VLookup.actionRefresh - #" + m_lookup.getSize(), "Selected=" + m_combo.getSelectedItem());
1046    } // actionRefresh
1047
1048    /*************************************************************************/
1049
1050    /**
1051     * Focus Listener for ComboBoxes with missing Validation or invalid entries
1052     * - Requery listener for updated list
1053     * @param e FocusEvent
1054     */
1055    public void focusGained (FocusEvent e)
1056    {
1057        if (e.getSource() != m_combo || e.isTemporary() || m_haveFocus)
1058            return;
1059        if (m_lookup.isValidated() && !m_lookup.hasInactive())
1060            return;
1061        //
1062 m_haveFocus = true; // prevents calling focus gained twice
1063 m_settingFocus = true; // prevents actionPerformed
1064 //
1065 Object obj = m_lookup.getSelectedItem();
1066        Log.trace(Log.l4_Data, "VLookup.focusGained", m_columnName + " Start Count=" + m_combo.getItemCount() + ", Selected=" + obj);
1067        Log.trace(Log.l5_DData, "VLookupHash=" + this.hashCode());
1068        m_lookup.fillComboBox(isMandatory(), true, true, true); // only validated & active & temporary
1069 Log.trace(Log.l4_Data, "VLookup.focusGained", m_columnName + " Update Count=" + m_combo.getItemCount() + ", Selected=" + m_lookup.getSelectedItem());
1070        m_lookup.setSelectedItem(obj);
1071        Log.trace(Log.l4_Data, "VLookup.focusGained", m_columnName + " Selected Count=" + m_combo.getItemCount() + ", Selected=" + m_lookup.getSelectedItem());
1072        //
1073 m_settingFocus = false;
1074    } // focusGained
1075
1076    /**
1077     * Reset Selection List
1078     * @param e FocusEvent
1079     */
1080    public void focusLost(FocusEvent e)
1081    {
1082        if (e.getSource() != m_combo || e.isTemporary())
1083            return;
1084        if (m_lookup.isValidated() && !m_lookup.hasInactive())
1085            return;
1086        //
1087 m_settingFocus = true; // prevents actionPerformed
1088 //
1089 Log.trace(Log.l4_Data, "VLookup.focusLost", m_columnName + " = " + m_combo.getSelectedItem());
1090        Object obj = m_combo.getSelectedItem();
1091        // set original model
1092 if (!m_lookup.isValidated())
1093            m_lookup.fillComboBox(true); // previous selection
1094 // Set value
1095 if (obj != null)
1096        {
1097            m_combo.setSelectedItem(obj);
1098            // original model may not have item
1099 if (!m_combo.getSelectedItem().equals(obj))
1100            {
1101                Log.trace(Log.l6_Database, "VLookup.focusLost " + m_columnName, "added to combo - " + obj);
1102                m_combo.addItem(obj);
1103                m_combo.setSelectedItem(obj);
1104            }
1105        }
1106    // actionCombo(getValue());
1107 m_settingFocus = false;
1108        m_haveFocus = false; // can gain focus again
1109 } // focusLost
1110
1111    /**
1112     * Set ToolTip
1113     * @param text tool tip text
1114     */
1115    public void setToolTipText(String text)
1116    {
1117        super.setToolTipText(text);
1118        m_button.setToolTipText(text);
1119        m_text.setToolTipText(text);
1120        m_combo.setToolTipText(text);
1121    } // setToolTipText
1122
1123} // VLookup
1124
1125/*****************************************************************************/
1126
1127/**
1128 * Mouse Listener for Popup Menu
1129 */
1130final class VLookup_mouseAdapter extends java.awt.event.MouseAdapter
1131{
1132    /**
1133     * Constructor
1134     * @param adaptee adaptee
1135     */
1136    VLookup_mouseAdapter(VLookup adaptee)
1137    {
1138        this.adaptee = adaptee;
1139    } // VLookup_mouseAdapter
1140
1141    private VLookup adaptee;
1142
1143    /**
1144     * Mouse Listener
1145     * @param e MouseEvent
1146     */
1147    public void mouseClicked(MouseEvent e)
1148    {
1149    // System.out.println("mouseClicked " + e.getID() + " " + e.getSource().getClass().toString());
1150 // popup menu
1151 if (SwingUtilities.isRightMouseButton(e))
1152            adaptee.popupMenu.show((Component)e.getSource(), e.getX(), e.getY());
1153    } // mouse Clicked
1154
1155} // VLookup_mouseAdapter
1156

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags