AcctViewerData


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.acct;
15  
16  import java.util.*;
17  import javax.swing.*;
18  import java.sql.*;
19  
20  import org.compiere.util.*;
21  import org.compiere.report.core.*;
22  import org.compiere.model.*;
23  
24  
25  /**
26   *  Account Viewer State - maintaines State information for the Account Viewer
27   *
28   *  @author Jorg Janke
29   *  @version  $Id: AcctViewerData.java,v 1.13 2003/07/28 03:58:08 jjanke Exp $
30   */
31  class AcctViewerData
32  {
33      /**
34       *  Constructor
35       *
36       *  @param windowNo window no
37       *  @param ad_Client_ID client
38       */
39      public AcctViewerData (int windowNo, int ad_Client_ID)
40      {
41          WindowNo = windowNo;
42          AD_Client_ID = ad_Client_ID;
43          if (AD_Client_ID == 0)
44              AD_Client_ID = Env.getContextAsInt(Env.getCtx(), WindowNo, "AD_Client_ID");
45          if (AD_Client_ID == 0)
46              AD_Client_ID = Env.getContextAsInt(Env.getCtx(), "AD_Client_ID");
47          //
48          ASchemas = AcctSchema.getAcctSchemaArray(AD_Client_ID);
49          AS = ASchemas[0];
50      }   //  AcctViewerData
51  
52      /** Window              */
53      public int              WindowNo;
54      public int              AD_Client_ID;
55  
56      public AcctSchema[]     ASchemas = null;
57      public AcctSchema       AS = null;
58  
59      //  Selection Info
60      public boolean          documentQuery = false;
61      public int              C_AcctSchema_ID = 0;
62      public int              AD_Org_ID = 0;
63      public Timestamp        DateFrom = null;
64      public Timestamp        DateTo = null;
65  
66      //  Table Selection Info
67      public int              AD_Table_ID;
68      public int              Record_ID;
69  
70      /** Containing Column and Query     */
71      public HashMap          whereInfo = new HashMap();
72      /** Containing TableName and AD_Table_ID    */
73      public HashMap          tableInfo = new HashMap();
74  
75      //  Display Info
76      public boolean          displayQty = false;
77      public boolean          displaySourceAmt = false;
78      public boolean          displayDocumentInfo = false;
79      //
80      public String             sortBy1 = "";
81      public String             sortBy2 = "";
82      public String             sortBy3 = "";
83      public String             sortBy4 = "";
84      //
85      public boolean          group1 = false;
86      public boolean          group2 = false;
87      public boolean          group3 = false;
88      public boolean          group4 = false;
89  
90  
91      /**
92       *  Dispose
93       */
94      public void dispose()
95      {
96          ASchemas = null;
97          AS = null;
98          //
99          whereInfo.clear();
100         whereInfo = null;
101         //
102         Env.clearWinContext(WindowNo);
103     }   //  dispose
104 
105     /*************************************************************************/
106 
107     /**
108      *  Fill Accounting Schema
109      *  @param cb JComboBox to be filled
110      */
111     protected void fillAcctSchema (JComboBox cb)
112     {
113         KeyNamePair pp = new KeyNamePair(0, "");
114         cb.addItem(pp);
115         for (int i = 0; i < ASchemas.length; i++)
116             cb.addItem(new KeyNamePair(ASchemas[i].getC_AcctSchema_ID(),
117                 ASchemas[i].getName()));
118     }   //  fillAcctSchema
119 
120     /**
121      *  Fill Table with
122      *      ValueNamePair (TableName, translatedKeyColumnName)
123      *  and tableInfo with (TableName, AD_Table_ID)
124      *  and select the entry for AD_Table_ID
125      *
126      *  @param cb JComboBox to be filled
127      */
128     protected void fillTable (JComboBox cb)
129     {
130         ValueNamePair select = null;
131         //
132         String   sql = "SELECT AD_Table_ID, TableName FROM AD_Table t "
133             + "WHERE EXISTS (SELECT * FROM AD_Column c"
134             + " WHERE t.AD_Table_ID=c.AD_Table_ID AND c.ColumnName='Posted')"
135             + " AND IsView='N'";
136         try
137         {
138             PreparedStatement pstmt = DB.prepareStatement(sql);
139             ResultSet rs = pstmt.executeQuery();
140             while (rs.next())
141             {
142                 int id = rs.getInt(1);
143                 String   tableName = rs.getString(2);
144                 String   name = Msg.translate(Env.getCtx(), tableName+"_ID");
145                 //
146                 ValueNamePair pp = new ValueNamePair(tableName, name);
147                 cb.addItem(pp);
148                 tableInfo.put (tableName, new Integer  (id));
149                 if (id == AD_Table_ID)
150                     select = pp;
151             }
152             rs.close();
153             pstmt.close();
154         }
155         catch (SQLException e)
156         {
157             Log.error("AcctViewerData.fillTable", e);
158         }
159         if (select != null)
160             cb.setSelectedItem(select);
161     }   //  fillTable
162 
163     /**
164      *  Fill Org
165      *
166      *  @param cb JComboBox to be filled
167      */
168     protected void fillOrg (JComboBox cb)
169     {
170         KeyNamePair pp = new KeyNamePair(0, "");
171         cb.addItem(pp);
172         String   sql = "SELECT AD_Org_ID, Name FROM AD_Org WHERE AD_Client_ID=? ORDER BY Value";
173         try
174         {
175             PreparedStatement pstmt = DB.prepareStatement(sql);
176             pstmt.setInt(1, AD_Client_ID);
177             ResultSet rs = pstmt.executeQuery();
178             while (rs.next())
179                 cb.addItem(new KeyNamePair(rs.getInt(1), rs.getString(2)));
180             rs.close();
181             pstmt.close();
182         }
183         catch (SQLException e)
184         {
185             Log.error("AcctViewerData.fillOrg", e);
186         }
187     }   //  fillOrg
188 
189     /**
190      *  Get Button Text
191      *
192      *  @param tableName table
193      *  @param columnName column
194      *  @param selectSQL sql
195      *  @return Text on button
196      */
197     protected String   getButtonText (String   tableName, String   columnName, String   selectSQL)
198     {
199         //  SELECT (<embedded>) FROM tableName avd WHERE avd.<selectSQL>
200         StringBuffer   sql = new StringBuffer   ("SELECT (");
201         Language language = Env.getLanguage(Env.getCtx());
202         sql.append(MLookupFactory.getLookup_TableDirEmbed(language, columnName, "avd"))
203             .append(") FROM ").append(tableName).append(" avd WHERE avd.").append(selectSQL);
204         String   retValue = "<" + selectSQL + ">";
205         try
206         {
207             Statement stmt = DB.createStatement();
208             ResultSet rs = stmt.executeQuery(sql.toString());
209             if (rs.next())
210                 retValue = rs.getString(1);
211             rs.close();
212             stmt.close();
213         }
214         catch (SQLException e)
215         {
216             Log.error("AcctViewerData.actionButton", e);
217         }
218         return retValue;
219     }   //  getButtonText
220 
221     /*************************************************************************/
222 
223     /**
224      *  Create Query and submit
225      *  @return Report Model
226      */
227     protected RModel query()
228     {
229         //  Set Where Clause
230         StringBuffer   whereClause = new StringBuffer  ();
231         if (documentQuery)
232             whereClause.append(RModel.TABLE_ALIAS).append(".AD_Table_ID=").append(AD_Table_ID)
233                 .append(" AND ").append(RModel.TABLE_ALIAS).append(".Record_ID=").append(Record_ID);
234         else
235         {
236             //  get values (Queries)
237             Iterator it = whereInfo.values().iterator();
238             while (it.hasNext())
239             {
240                 String   where = (String  )it.next();
241                 if (where != null && where.length() > 0)    //  add only if not empty
242                 {
243                     if (whereClause.length() > 0)
244                         whereClause.append(" AND ");
245                     whereClause.append(RModel.TABLE_ALIAS).append(".").append(where);
246                 }
247             }
248             if (DateFrom != null || DateTo != null)
249             {
250                 if (whereClause.length() > 0)
251                     whereClause.append(" AND ");
252                 if (DateFrom != null && DateTo != null)
253                     whereClause.append("TRUNC(").append(RModel.TABLE_ALIAS).append(".DateAcct) BETWEEN ")
254                         .append(DB.TO_DATE(DateFrom)).append(" AND ").append(DB.TO_DATE(DateTo));
255                 else if (DateFrom != null)
256                     whereClause.append("TRUNC(").append(RModel.TABLE_ALIAS).append(".DateAcct) >= ")
257                         .append(DB.TO_DATE(DateFrom));
258                 else    //  DateTo != null
259                     whereClause.append("TRUNC(").append(RModel.TABLE_ALIAS).append(".DateAcct) <= ")
260                         .append(DB.TO_DATE(DateTo));
261             }
262             //  Add Organization
263             if (AD_Org_ID != 0)
264             {
265                 if (whereClause.length() > 0)
266                     whereClause.append(" AND ");
267                 whereClause.append(RModel.TABLE_ALIAS).append(".AD_Org_ID=").append(AD_Org_ID);
268             }
269         }
270 
271         //  Set Order By Clause
272         StringBuffer   orderClause = new StringBuffer  ();
273         if (sortBy1.length() > 0)
274             orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy1);
275         if (sortBy2.length() > 0)
276         {
277             if (orderClause.length() > 0)
278                 orderClause.append(",");
279             orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy2);
280         }
281         if (sortBy3.length() > 0)
282         {
283             if (orderClause.length() > 0)
284                 orderClause.append(",");
285             orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy3);
286         }
287         if (sortBy4.length() > 0)
288         {
289             if (orderClause.length() > 0)
290                 orderClause.append(",");
291             orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy4);
292         }
293         if (orderClause.length() == 0)
294             orderClause.append(RModel.TABLE_ALIAS).append(".Fact_Acct_ID");
295 
296         RModel rm = getRModel();
297 
298         //  Groups
299         if (group1 && sortBy1.length() > 0)
300             rm.setGroup(sortBy1);
301         if (group2 && sortBy2.length() > 0)
302             rm.setGroup(sortBy2);
303         if (group3 && sortBy3.length() > 0)
304             rm.setGroup(sortBy3);
305         if (group4 && sortBy4.length() > 0)
306             rm.setGroup(sortBy4);
307 
308         //  Totals
309         rm.setFunction("AmtAcctDR", RModel.FUNCTION_SUM);
310         rm.setFunction("AmtAcctCR", RModel.FUNCTION_SUM);
311 
312         rm.query (Env.getCtx(), whereClause.toString(), orderClause.toString());
313 
314         return rm;
315     }   //  query
316 
317     /**
318      *  Create Report Model (Columns)
319      *  @return Report Model
320      */
321     private RModel getRModel()
322     {
323         Properties ctx = Env.getCtx();
324         RModel rm = new RModel("Fact_Acct");
325         //  Add Key (Lookups)
326         ArrayList keys = createKeyColumns();
327         for (int i = 0; i < keys.size(); i++)
328         {
329             String   column = (String  )keys.get(i);
330             if (column != null && column.startsWith("Date"))
331                 rm.addColumn(new RColumn(ctx, column, DisplayType.Date));
332             else if (column != null && column.endsWith("_ID"))
333                 rm.addColumn(new RColumn(ctx, column, DisplayType.TableDir));
334         }
335         //  Info
336         if (!keys.contains("DateAcct"))
337             rm.addColumn(new RColumn(ctx, "DateAcct", DisplayType.Date));
338         if (!keys.contains("C_Period_ID"))
339             rm.addColumn(new RColumn(ctx, "C_Period_ID", DisplayType.TableDir));
340         rm.addColumn(new RColumn(ctx, "AmtAcctDR", DisplayType.Amount));
341         rm.addColumn(new RColumn(ctx, "AmtAcctCR", DisplayType.Amount));
342         if (displaySourceAmt)
343         {
344             if (!keys.contains("DateTrx"))
345                 rm.addColumn(new RColumn(ctx, "DateTrx", DisplayType.Date));
346             rm.addColumn(new RColumn(ctx, "C_Currency_ID", DisplayType.TableDir));
347             rm.addColumn(new RColumn(ctx, "AmtSourceDR", DisplayType.Amount));
348             rm.addColumn(new RColumn(ctx, "AmtSourceCR", DisplayType.Amount));
349         }
350         if (displayQty)
351         {
352             rm.addColumn(new RColumn(ctx, "C_UOM_ID", DisplayType.TableDir));
353             rm.addColumn(new RColumn(ctx, "Qty", DisplayType.Quantity));
354         }
355         if (displayDocumentInfo)
356         {
357             rm.addColumn(new RColumn(ctx, "AD_Table_ID", DisplayType.TableDir));
358         }
359         return rm;
360     }   //  createRModel
361 
362     /**
363      *  Create the key columns in sequence
364      *  @return List of Key Columns
365      */
366     private ArrayList createKeyColumns()
367     {
368         ArrayList columns = new ArrayList();
369         //  Sorting Fields
370         columns.add(sortBy1);               //  may add ""
371         if (!columns.contains(sortBy2))
372             columns.add(sortBy2);
373         if (!columns.contains(sortBy3))
374             columns.add(sortBy3);
375         if (!columns.contains(sortBy4))
376             columns.add(sortBy4);
377 
378         //  Add Account Segments
379         ArrayList elements = AS.getAcctSchemaElementList();
380         for (int i = 0; i < elements.size(); i++)
381         {
382             AcctSchemaElement ase = (AcctSchemaElement)elements.get(i);
383             String   columnName = ase.getColumnName();
384             if (!columns.contains(columnName))
385                 columns.add(columnName);
386         }
387         //
388         return columns;
389     }   //  createKeyColumns
390 
391 }   //  AcctViewerData
392
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags