InfoCashLine


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.apps.search;
15  
16  import java.awt.*;
17  import java.awt.event.*;
18  
19  import javax.swing.*;
20  import javax.swing.event.*;
21  import javax.swing.table.*;
22  
23  import java.sql.*;
24  import java.text.*;
25  import java.util.*;
26  import java.math.*;
27  
28  import org.compiere.apps.*;
29  import org.compiere.grid.ed.*;
30  import org.compiere.minigrid.*;
31  import org.compiere.util.*;
32  import org.compiere.model.*;
33  import org.compiere.plaf.*;
34  import org.compiere.swing.*;
35  
36  /**
37   *  Info Payment
38   *
39   *  @author Jorg Janke
40   *  @version  $Id: InfoCashLine.java,v 1.7 2003/10/17 06:15:56 jjanke Exp $
41   */
42  public class InfoCashLine extends Info
43  {
44      /**
45       *  Detail Protected Contructor
46       *  @param frame parent frame
47       *  @param modal modal
48       *  @param WindowNo window no
49       *  @param value query value
50       *  @param multiSelection multiple selections
51       *  @param whereClause whwre clause
52       */
53      protected InfoCashLine(Frame frame, boolean modal, int WindowNo, String   value,
54          boolean multiSelection, String   whereClause)
55      {
56          super (frame, modal, WindowNo, "cl", "C_CashLine_ID", multiSelection, whereClause);
57          Log.trace(Log.l1_User, "InfoCashLine");
58          setTitle(Msg.getMsg(Env.getCtx(), "InfoCashLine"));
59          //
60          try
61          {
62              statInit();
63              p_loadedOK = initInfo ();
64          }
65          catch (Exception   e)
66          {
67              return;
68          }
69          //
70          int no = p_table.getRowCount();
71          setStatusLine(Integer.toString(no) + " " + Msg.getMsg(Env.getCtx(), "SearchRows_EnterQuery"), false);
72          setStatusDB(Integer.toString(no));
73          if (value != null && value.length() > 0)
74          {
75              fName.setValue(value);
76              executeQuery();
77          }
78          //
79          pack();
80          //  Focus
81          fName.requestFocus();
82      }   //  InfoCashLine
83  
84      /**  String Array of Column Info    */
85      private Info_Column[] m_generalLayout;
86      /** list of query columns           */
87      private ArrayList   m_queryColumns = new ArrayList();
88      /** Table Name              */
89      private String        m_tableName;
90      /** Key Column Name         */
91      private String        m_keyColumn;
92  
93      //  Static Info
94      private CLabel lName = new CLabel(Msg.translate(Env.getCtx(), "Name"));
95      private CTextField fName = new CTextField(10);
96      private CLabel lCashBook_ID = new CLabel(Msg.translate(Env.getCtx(), "C_CashBook_ID"));
97      private VLookup fCashBook_ID;
98  //  private CLabel lOrg_ID = new CLabel(Msg.translate(Env.getCtx(), "AD_Org_ID"));
99  //  private VLookup fOrg_ID;
100     private CLabel lInvoice_ID = new CLabel(Msg.translate(Env.getCtx(), "C_Invoice_ID"));
101     private VLookup fInvoice_ID;
102 //  private CLabel lCharge_ID = new CLabel(Msg.translate(Env.getCtx(), "C_Charge_ID"));
103 //  private VLookup fCharge_ID;
104     private CLabel lBankAccount_ID = new CLabel(Msg.translate(Env.getCtx(), "C_BankAccount_ID"));
105     private VLookup fBankAccount_ID;
106     private CCheckBox cbAbsolute = new CCheckBox (Msg.translate(Env.getCtx(), "AbsoluteAmt"));
107     //
108     private CLabel lDateFrom = new CLabel(Msg.translate(Env.getCtx(), "StatementDate"));
109     private VDate fDateFrom = new VDate("DateFrom", false, false, true, DisplayType.Date, Msg.translate(Env.getCtx(), "DateFrom"));
110     private CLabel lDateTo = new CLabel("-");
111     private VDate fDateTo = new VDate("DateTo", false, false, true, DisplayType.Date, Msg.translate(Env.getCtx(), "DateTo"));
112     private CLabel lAmtFrom = new CLabel(Msg.translate(Env.getCtx(), "Amount"));
113     private VNumber fAmtFrom = new VNumber("AmtFrom", false, false, true, DisplayType.Amount, Msg.translate(Env.getCtx(), "AmtFrom"));
114     private CLabel lAmtTo = new CLabel("-");
115     private VNumber fAmtTo = new VNumber("AmtTo", false, false, true, DisplayType.Amount, Msg.translate(Env.getCtx(), "AmtTo"));
116 
117     /**  Array of Column Info    */
118     private static final Info_Column[] s_cashLayout = {
119         new Info_Column(" ", "cl.C_CashLine_ID", IDColumn.class),
120         new Info_Column(Msg.translate(Env.getCtx(), "C_CashBook_ID"),
121             "(SELECT cb.Name FROM C_CashBook cb WHERE cb.C_CashBook_ID=c.C_CashBook_ID)", String  .class),
122         new Info_Column(Msg.translate(Env.getCtx(), "Name"),
123             "c.Name", String  .class),
124         new Info_Column(Msg.translate(Env.getCtx(), "StatementDate"),
125             "c.StatementDate", Timestamp.class),
126         new Info_Column(Msg.translate(Env.getCtx(), "Line"),
127             "cl.Line", Integer  .class),
128     //  new Info_Column(Msg.translate(Env.getCtx(), "C_Currency_ID"),
129     //      "(SELECT ISO_Code FROM C_Currency c WHERE c.C_Currency_ID=cl.C_Currency_ID)", String.class),
130         new Info_Column(Msg.translate(Env.getCtx(), "Amount"),
131             "cl.Amount",  BigDecimal.class, true, true, null),
132         //
133         new Info_Column(Msg.translate(Env.getCtx(), "C_Invoice_ID"),
134             "(SELECT i.DocumentNo||'_'||" + DB.TO_CHAR("i.DateInvoiced",DisplayType.Date,Env.getAD_Language(Env.getCtx()))
135                 + "||'_'||" + DB.TO_CHAR("i.GrandTotal",DisplayType.Amount,Env.getAD_Language(Env.getCtx()))
136                 + " FROM C_Invoice i WHERE i.C_Invoice_ID=cl.C_Invoice_ID)", String  .class),
137         new Info_Column(Msg.translate(Env.getCtx(), "C_BankAccount_ID"),
138             "(SELECT b.Name||' '||ba.AccountNo FROM C_Bank b, C_BankAccount ba WHERE b.C_Bank_ID=ba.C_Bank_ID AND ba.C_BankAccount_ID=cl.C_BankAccount_ID)", String  .class),
139         new Info_Column(Msg.translate(Env.getCtx(), "C_Charge_ID"),
140             "(SELECT ca.Name FROM C_Charge ca WHERE ca.C_Charge_ID=cl.C_Charge_ID)", String  .class),
141         //
142         new Info_Column(Msg.translate(Env.getCtx(), "DiscountAmt"),
143             "cl.DiscountAmt",  BigDecimal.class),
144         new Info_Column(Msg.translate(Env.getCtx(), "WriteOffAmt"),
145             "cl.WriteOffAmt",  BigDecimal.class),
146         new Info_Column(Msg.translate(Env.getCtx(), "Description"),
147             "cl.Description", String  .class)
148     };
149 
150     /**
151      *  Static Setup - add fields to parameterPanel
152      *  @throws Exception if Lookups cannot be created
153      */
154     private void statInit() throws Exception  
155     {
156         lName.setLabelFor(fName);
157         fName.setBackground(CompierePLAF.getInfoBackground());
158         fName.addActionListener(this);
159         //
160     //  fOrg_ID = new VLookup("AD_Org_ID", false, false, true,
161     //      MLookupFactory.create(Env.getCtx(), 3486, m_WindowNo, DisplayType.TableDir, false),
162     //      DisplayType.TableDir, m_WindowNo);
163     //  lOrg_ID.setLabelFor(fOrg_ID);
164     //  fOrg_ID.setBackground(CompierePLAF.getInfoBackground());
165         //  5249 - C_Cash.C_CashBook_ID
166         fCashBook_ID = new VLookup("C_CashBook_ID", false, false, true,
167             MLookupFactory.get (Env.getCtx(), p_WindowNo, 0, 5249, DisplayType.TableDir),
168             DisplayType.TableDir, p_WindowNo);
169         lCashBook_ID.setLabelFor(fCashBook_ID);
170         fCashBook_ID.setBackground(CompierePLAF.getInfoBackground());
171         //  5354 - C_CashLine.C_Invoice_ID
172         fInvoice_ID = new VLookup("C_Invoice_ID", false, false, true,
173             MLookupFactory.get (Env.getCtx(), p_WindowNo, 0, 5354, DisplayType.Search),
174             DisplayType.Search, p_WindowNo);
175         lInvoice_ID.setLabelFor(fInvoice_ID);
176         fInvoice_ID.setBackground(CompierePLAF.getInfoBackground());
177         //  5295 - C_CashLine.C_BankAccount_ID
178         fBankAccount_ID = new VLookup("C_BankAccount_ID", false, false, true,
179             MLookupFactory.get (Env.getCtx(), p_WindowNo, 0, 5295, DisplayType.TableDir),
180             DisplayType.TableDir, p_WindowNo);
181         lBankAccount_ID.setLabelFor(fBankAccount_ID);
182         fBankAccount_ID.setBackground(CompierePLAF.getInfoBackground());
183         //  5296 - C_CashLine.C_Charge_ID
184         //  5291 - C_CashLine.C_Cash_ID
185         //
186         lDateFrom.setLabelFor(fDateFrom);
187         fDateFrom.setBackground(CompierePLAF.getInfoBackground());
188         fDateFrom.setToolTipText(Msg.translate(Env.getCtx(), "DateFrom"));
189         lDateTo.setLabelFor(fDateTo);
190         fDateTo.setBackground(CompierePLAF.getInfoBackground());
191         fDateTo.setToolTipText(Msg.translate(Env.getCtx(), "DateTo"));
192         lAmtFrom.setLabelFor(fAmtFrom);
193         fAmtFrom.setBackground(CompierePLAF.getInfoBackground());
194         fAmtFrom.setToolTipText(Msg.translate(Env.getCtx(), "AmtFrom"));
195         lAmtTo.setLabelFor(fAmtTo);
196         fAmtTo.setBackground(CompierePLAF.getInfoBackground());
197         fAmtTo.setToolTipText(Msg.translate(Env.getCtx(), "AmtTo"));
198         //
199         parameterPanel.setLayout(new ALayout());
200         //  First Row
201         parameterPanel.add(lCashBook_ID, new ALayoutConstraint(0,0));
202         parameterPanel.add(fCashBook_ID, null);
203         parameterPanel.add(lName, null);
204         parameterPanel.add(fName, null);
205         parameterPanel.add(cbAbsolute, new ALayoutConstraint(0,5));
206         //  2nd Row
207         parameterPanel.add(lInvoice_ID, new ALayoutConstraint(1,0));
208         parameterPanel.add(fInvoice_ID, null);
209         parameterPanel.add(lDateFrom, null);
210         parameterPanel.add(fDateFrom, null);
211         parameterPanel.add(lDateTo, null);
212         parameterPanel.add(fDateTo, null);
213         //  3rd Row
214         parameterPanel.add(lBankAccount_ID, new ALayoutConstraint(2,0));
215         parameterPanel.add(fBankAccount_ID, null);
216         parameterPanel.add(lAmtFrom, null);
217         parameterPanel.add(fAmtFrom, null);
218         parameterPanel.add(lAmtTo, null);
219         parameterPanel.add(fAmtTo, null);
220     //  parameterPanel.add(lOrg_ID, null);
221     //  parameterPanel.add(fOrg_ID, null);
222     }   //  statInit
223 
224     /**
225      *  General Init
226      *  @return true, if success
227      */
228     private boolean initInfo ()
229     {
230         //  prepare table
231         StringBuffer   where = new StringBuffer  ("cl.C_Cash_ID=c.C_Cash_ID");
232         //  AND cl.IsActive='Y'
233         if (p_whereClause.length() > 0)
234             where.append(" AND ").append(Util.replace(p_whereClause, "C_CashLine.", "cl."));
235         prepareTable (s_cashLayout,
236             "C_Cash c, C_CashLine cl",
237             where.toString(),
238             "2,3,cl.Line");
239 
240         return true;
241     }   //  initInfo
242 
243     /*************************************************************************/
244 
245     /**
246      *  Construct SQL Where Clause and define parameters
247      *  (setParameters needs to set parameters)
248      *  Includes first AND
249      *  @return sql where clause
250      */
251     String   getSQLWhere()
252     {
253         StringBuffer   sql = new StringBuffer  ();
254         if (fName.getText().length() > 0)
255             sql.append(" AND UPPER(c.Name) LIKE ?");
256         //
257         if (fCashBook_ID.getValue() != null)
258             sql.append(" AND c.C_CashBook_ID=?");
259         //
260         if (fInvoice_ID.getValue() != null)
261             sql.append(" AND cl.C_Invoice_ID=?");
262         //
263         if (fDateFrom.getValue() != null || fDateTo.getValue() != null)
264         {
265             Timestamp from = (Timestamp)fDateFrom.getValue();
266             Timestamp to = (Timestamp)fDateTo.getValue();
267             if (from == null && to != null)
268                 sql.append(" AND TRUNC(c.StatementDate) <= ?");
269             else if (from != null && to == null)
270                 sql.append(" AND TRUNC(c.StatementDate) >= ?");
271             else if (from != null && to != null)
272                 sql.append(" AND TRUNC(c.StatementDate) BETWEEN ? AND ?");
273         }
274         //
275         if (fAmtFrom.getValue() != null || fAmtTo.getValue() != null)
276         {
277             BigDecimal from = (BigDecimal)fAmtFrom.getValue();
278             BigDecimal to = (BigDecimal)fAmtTo.getValue();
279             if (cbAbsolute.isSelected())
280                 sql.append(" AND ABS(cl.Amount)");
281             else
282                 sql.append(" AND cl.Amount");
283             //
284             if (from == null && to != null)
285                 sql.append(" <=?");
286             else if (from != null && to == null)
287                 sql.append(" >=?");
288             else if (from != null && to != null)
289             {
290                 if (from.compareTo(to) == 0)
291                     sql.append(" =?");
292                 else
293                     sql.append(" BETWEEN ? AND ?");
294             }
295         }
296 
297         Log.trace(Log.l6_Database, "InfoCashLine.getWhereClause", sql.toString());
298         return sql.toString();
299     }   //  getSQLWhere
300 
301     /**
302      *  Set Parameters for Query.
303      *  (as defined in getSQLWhere)
304      *  @param pstmt statement
305      *  @throws SQLException
306      */
307     void setParameters(PreparedStatement pstmt) throws SQLException
308     {
309         int index = 1;
310         if (fName.getText().length() > 0)
311             pstmt.setString(index++, getSQLText(fName));
312         //
313         if (fCashBook_ID.getValue() != null)
314         {
315             Integer   cb = (Integer  )fCashBook_ID.getValue();
316             pstmt.setInt(index++, cb.intValue());
317             Log.trace(Log.l5_DData, "CashBook=" + cb);
318         }
319         //
320         if (fInvoice_ID.getValue() != null)
321         {
322             Integer   i = (Integer  )fInvoice_ID.getValue();
323             pstmt.setInt(index++, i.intValue());
324             Log.trace(Log.l5_DData, "Invoice=" + i);
325         }
326         //
327         if (fDateFrom.getValue() != null || fDateTo.getValue() != null)
328         {
329             Timestamp from = (Timestamp)fDateFrom.getValue();
330             Timestamp to = (Timestamp)fDateTo.getValue();
331             Log.trace(Log.l5_DData, "Date From=" + from + ", To=" + to);
332             if (from == null && to != null)
333                 pstmt.setTimestamp(index++, to);
334             else if (from != null && to == null)
335                 pstmt.setTimestamp(index++, from);
336             else if (from != null && to != null)
337             {
338                 pstmt.setTimestamp(index++, from);
339                 pstmt.setTimestamp(index++, to);
340             }
341         }
342         //
343         if (fAmtFrom.getValue() != null || fAmtTo.getValue() != null)
344         {
345             BigDecimal from = (BigDecimal)fAmtFrom.getValue();
346             BigDecimal to = (BigDecimal)fAmtTo.getValue();
347             if (cbAbsolute.isSelected())
348             {
349                 if (from != null)
350                     from = from.abs();
351                 if (to != null)
352                     to = to.abs();
353             }
354             Log.trace(Log.l5_DData, "Amt From=" + from + ", To=" + to + ", Absolute=" + cbAbsolute.isSelected());
355             if (from == null && to != null)
356                 pstmt.setBigDecimal(index++, to);
357             else if (from != null && to == null)
358                 pstmt.setBigDecimal(index++, from);
359             else if (from != null && to != null)
360             {
361                 if (from.compareTo(to) == 0)
362                     pstmt.setBigDecimal(index++, from);
363                 else
364                 {
365                     pstmt.setBigDecimal(index++, to);
366                     pstmt.setBigDecimal(index++, from);
367                 }
368             }
369         }
370     }   //  setParameters
371 
372     /**
373      *  Get SQL WHERE parameter
374      *  @param f field
375      *  @return Upper case text with % at the end
376      */
377     private String   getSQLText (CTextField f)
378     {
379         String   s = f.getText().toUpperCase();
380         if (!s.endsWith("%"))
381             s += "%";
382         Log.trace(Log.l5_DData, "String=" + s);
383         return s;
384     }   //  getSQLText
385 }   //  InfoCashLine
386
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags