VCreateFromStatement


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;
15  
16  import javax.swing.table.*;
17  import java.util.*;
18  import java.sql.*;
19  import java.math.*;
20  import java.text.*;
21  import java.beans.*;
22  
23  import org.compiere.apps.*;
24  import org.compiere.util.*;
25  import org.compiere.model.*;
26  import org.compiere.grid.ed.*;
27  
28  /**
29   *  Create Transactions for Bank Statements
30   *
31   *  @author Jorg Janke
32   *  @version  $Id: VCreateFromStatement.java,v 1.17 2003/09/05 04:58:59 jjanke Exp $
33   */
34  public class VCreateFromStatement extends VCreateFrom implements VetoableChangeListener
35  {
36      /**
37       *  Protected Constructor
38       *  @param mTab MTab
39       */
40      VCreateFromStatement(MTab mTab)
41      {
42          super (mTab);
43          Log.trace(Log.l1_User, "VCreateFromStatement");
44      }   //  VCreateFromStatement
45  
46      /**
47       *  Dynamic Init
48       *  @throws Exception if Lookups cannot be initialized
49       *  @return true if initialized
50       */
51      protected boolean dynInit() throws Exception  
52      {
53          Log.trace(Log.l3_Util, "VCreateFromStatement.dynInit");
54  
55          if (p_mTab.getValue("C_BankStatement_ID") == null)
56          {
57              ADialog.error(0, this, "SaveErrorRowNotFound");
58              return false;
59          }
60  
61          setTitle(Msg.translate(Env.getCtx(), "C_BankStatement_ID") + " .. " + Msg.translate(Env.getCtx(), "CreateFrom"));
62          parameterStdPanel.setVisible(false);
63  
64          int AD_Column_ID = 4917;        //  C_BankStatement.C_BankAccount_ID
65          MLookup lookup = MLookupFactory.get (Env.getCtx(), p_WindowNo, 0, AD_Column_ID, DisplayType.TableDir);
66          bankAccountField = new VLookup ("C_BankAccount_ID", true, false, true,
67              lookup, DisplayType.TableDir, p_WindowNo);
68          bankAccountField.addVetoableChangeListener(this);
69          //  Set Default
70          int C_BankAccount_ID = Env.getContextAsInt(Env.getCtx(), p_WindowNo, "C_BankAccount_ID");
71          bankAccountField.setValue(new Integer  (C_BankAccount_ID));
72          //  initial Loading
73          loadBankAccount(C_BankAccount_ID);
74  
75          return true;
76      }   //  dynInit
77  
78      /**
79       *  Init Details (never called)
80       *  @param C_BPartner_ID BPartner
81       */
82      protected void initBPDetails(int C_BPartner_ID)
83      {
84      }   //  initDetails
85  
86      /**
87       *  Change Listener
88       *  @param e event
89       */
90      public void vetoableChange (PropertyChangeEvent e)
91      {
92          Log.trace(Log.l3_Util, "VCreateFromStatement.vetoableChange " + e.getPropertyName() + "=" + e.getNewValue());
93  
94          //  BankAccount
95          if (e.getPropertyName() == "C_BankAccount_ID")
96          {
97              int C_BankAccount_ID = ((Integer  )e.getNewValue()).intValue();
98              loadBankAccount(C_BankAccount_ID);
99          }
100         tableChanged(null);
101     }   //  vetoableChange
102 
103     /**
104      *  Load Data - Bank Account
105      *  @param C_BankAccount_ID Bank Account
106      */
107     private void loadBankAccount (int C_BankAccount_ID)
108     {
109         Log.trace(Log.l3_Util, "VCreateFromStatement.loadBankAccount - " + C_BankAccount_ID);
110         /**
111          *  Selected        - 0
112          *  Date            - 1
113          *  C_Payment_ID    - 2
114          *  C_Currenncy     - 3
115          *  Amt             - 4
116          */
117         Vector data = new Vector();
118         String   sql = "SELECT p.DateTrx,p.C_Payment_ID,p.DocumentNo, p.C_Currency_ID,c.ISO_Code, p.PayAmt,"
119             + "C_Currency_Convert(p.PayAmt,p.C_Currency_ID,ba.C_Currency_ID,?,null,p.AD_Client_ID,p.AD_Org_ID) "   //  #1
120             + "FROM C_BankAccount ba, C_Payment_v p, C_Currency c "
121             + "WHERE p.C_BankAccount_ID=ba.C_BankAccount_ID"
122             + " AND p.C_Currency_ID=c.C_Currency_ID"
123             + " AND p.C_BankAccount_ID=?"                                               //  #2
124             + " AND p.Processed='Y' AND p.IsReconciled='N'"
125             + " AND NOT EXISTS (SELECT * FROM C_BankStatementLine l WHERE p.C_Payment_ID=l.C_Payment_ID)";
126 
127         //  Get StatementDate
128         Timestamp ts = (Timestamp)p_mTab.getValue("StatementDate");
129         if (ts == null)
130             ts = new Timestamp(System.currentTimeMillis());
131 
132         try
133         {
134             PreparedStatement pstmt = DB.prepareStatement(sql.toString());
135             pstmt.setTimestamp(1, ts);
136             pstmt.setInt(2, C_BankAccount_ID);
137             ResultSet rs = pstmt.executeQuery();
138             while (rs.next())
139             {
140                 Vector line = new Vector(6);
141                 line.add(new Boolean  (false));       //  0-Selection
142                 line.add(rs.getTimestamp(1));       //  1-DateTrx
143                 KeyNamePair pp = new KeyNamePair(rs.getInt(2), rs.getString(3));
144                 line.add(pp);                       //  2-C_Payment_ID
145                 pp = new KeyNamePair(rs.getInt(4), rs.getString(5));
146                 line.add(pp);                       //  3-Currency
147                 line.add(rs.getBigDecimal(6));      //  4-PayAmt
148                 line.add(rs.getBigDecimal(7));      //  5-Conv Amt
149                 data.add(line);
150             }
151             rs.close();
152             pstmt.close();
153         }
154         catch (SQLException e)
155         {
156             Log.error ("VCreateFromStatement.loadBankAccount", e);
157         }
158         //  Header Info
159         Vector columnNames = new Vector(6);
160         columnNames.add(Msg.getMsg(Env.getCtx(), "Select"));
161         columnNames.add(Msg.translate(Env.getCtx(), "Date"));
162         columnNames.add(Msg.getElement(Env.getCtx(), "C_Payment_ID"));
163         columnNames.add(Msg.translate(Env.getCtx(), "C_Currency_ID"));
164         columnNames.add(Msg.translate(Env.getCtx(), "Amount"));
165         columnNames.add(Msg.translate(Env.getCtx(), "ConvertedAmount"));
166 
167         //  Remove previous listeners
168         dataTable.getModel().removeTableModelListener(this);
169         //  Set Model
170         DefaultTableModel model = new DefaultTableModel(data, columnNames);
171         model.addTableModelListener(this);
172         dataTable.setModel(model);
173         //
174         dataTable.setColumnClass(0, Boolean  .class, false);      //  0-Selection
175         dataTable.setColumnClass(1, Timestamp.class, true);     //  1-TrxDate
176         dataTable.setColumnClass(2, String  .class, true);        //  2-Payment
177         dataTable.setColumnClass(3, String  .class, true);        //  3-Currency
178         dataTable.setColumnClass(4, BigDecimal.class, true);    //  4-Amount
179         dataTable.setColumnClass(5, BigDecimal.class, true);    //  5-ConvAmount
180         //  Table UI
181         dataTable.autoSize();
182     }   //  loadBankAccount
183 
184     /**
185      *  List total amount
186      */
187     protected void info()
188     {
189         DecimalFormat format = DisplayType.getNumberFormat(DisplayType.Amount);
190 
191         TableModel model = dataTable.getModel();
192         BigDecimal total = new BigDecimal(0.0);
193         int rows = model.getRowCount();
194         int count = 0;
195         for (int i = 0; i < rows; i++)
196         {
197             if (((Boolean  )model.getValueAt(i, 0)).booleanValue())
198             {
199                 total = total.add((BigDecimal)model.getValueAt(i, 4));
200                 count++;
201             }
202         }
203         statusBar.setStatusLine(String.valueOf(count) + " - " + Msg.getMsg(Env.getCtx(), "Sum") + "  " + format.format(total));
204     }   //  infoStatement
205 
206     /**
207      *  Save Statement - Insert Data
208      *  @return true if saved
209      */
210     protected boolean save()
211     {
212         Log.trace(Log.l3_Util, "VCreateFromStatement.save");
213         TableModel model = dataTable.getModel();
214         int rows = model.getRowCount();
215         if (rows == 0)
216             return false;
217 
218         //  fixed values
219         int AD_Client_ID = ((Integer  )p_mTab.getValue("AD_Client_ID")).intValue();
220         int AD_Org_ID = ((Integer  )p_mTab.getValue("AD_Org_ID")).intValue();
221         int CreatedBy = Env.getContextAsInt(Env.getCtx(), "#AD_User_ID");
222         int C_BankStatement_ID = ((Integer  )p_mTab.getValue("C_BankStatement_ID")).intValue();;
223         Log.trace(Log.l4_Data, "Client=" + AD_Client_ID + ", Org=" + AD_Org_ID
224             + ", User=" + CreatedBy + ", BankStatement=" + C_BankStatement_ID);
225 
226         //  Lines
227         for (int i = 0; i < rows; i++)
228         {
229             if (((Boolean  )model.getValueAt(i, 0)).booleanValue())
230             {
231                 //  variable values
232                 int C_BankStatementLine_ID = DB.getKeyNextNo(Env.getCtx(), p_WindowNo, "C_BankStatementLine");
233                 Timestamp trxDate = (Timestamp)model.getValueAt(i, 1);  //  1-DateTrx
234                 KeyNamePair pp = (KeyNamePair)model.getValueAt(i, 2);   //  2-C_Payment_ID
235                 int C_Payment_ID = pp.getKey();
236                 pp = (KeyNamePair)model.getValueAt(i, 3);               //  3-Currency
237                 int C_Currency_ID = pp.getKey();
238                 BigDecimal TrxAmt = (BigDecimal)model.getValueAt(i, 4); //  4-PayAmt
239                 BigDecimal StmtAmt = (BigDecimal)model.getValueAt(i, 5);//  5-Conv Amt
240                 //
241                 Log.trace(Log.l5_DData, "Line=" + C_BankStatementLine_ID + ", Date=" + trxDate
242                     + ", Payment=" + C_Payment_ID + ", Currency=" + C_Currency_ID + ", Amt=" + TrxAmt);
243                 //
244                 StringBuffer   sql = new StringBuffer  ("INSERT INTO C_BankStatementLine");
245                 sql.append("(C_BankStatementLine_ID,");
246                 sql.append("AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,");
247                 sql.append("C_BankStatement_ID,DateAcct,ValutaDate,");
248                 sql.append("Line,Description,IsReversal,");
249                 sql.append("C_Payment_ID,TrxAmt,");
250                 sql.append("C_Charge_ID,ChargeAmt,InterestAmt,");
251                 sql.append("C_Currency_ID,StmtAmt)");
252                 sql.append(" VALUES (");
253                 //
254                 sql.append(C_BankStatementLine_ID).append(",");
255                 sql.append(AD_Client_ID).append(",").append(AD_Org_ID).append(",'Y',");
256                 sql.append("SysDate,").append(CreatedBy).append(",SysDate,").append(CreatedBy).append(",");
257                 //  C_BankStatement_ID,DateAcct,ValutaDate,
258                 sql.append(C_BankStatement_ID).append(",");
259                 sql.append(DB.TO_DATE(trxDate)).append(",").append(DB.TO_DATE(trxDate)).append(",");
260                 //  Line,Description,IsReversal,
261                 sql.append("(SELECT (NVL(Max(Line),0))+10 FROM C_BankStatementLine WHERE C_BankStatement_ID=").append(C_BankStatement_ID).append("),");
262                 sql.append("NULL,'N',");
263                 //  C_Payment_ID,TrxAmt,
264                 sql.append(C_Payment_ID).append(",").append(TrxAmt).append(",");
265                 sql.append("NULL,0,0,");
266                 //  C_Currency_ID,StmtAmt
267                 sql.append(C_Currency_ID).append(",").append(StmtAmt);
268                 sql.append(")");
269                 //
270                 int no = DB.executeUpdate(sql.toString());
271                 if (no != 1)
272                     Log.error("VCreateFromStatement.save - Line created #" + no);
273             }   //   if selected
274         }   //  for all rows
275         return true;
276     }   //  save
277 
278 }   //  VCreateFromStatement
279
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags